CREATE PROCEDURE dbo.UpdateCleanStatusPickup @ADateTime DATETIME AS
/*
  Created by Kent Johansen, Oktober 2019
  Update clean status if clean statushas been clean for x days.

*/

SET NOCOUNT ON
DECLARE @CleanDays INT
DECLARE @CleanStatus INT

SELECT @CleanDays = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'CleanRoomUnusedDays'
SET @CleanDays = ISNULL(@CleanDays, 0)*-1

IF @CleanDays < 0
BEGIN
  SELECT @CleanStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'CleanRoomUnusedStatus'
  SET @CleanStatus = ISNULL(@CleanStatus, 2) -- default is csPickup

  SELECT MAX(Id) AS Id, RoomNumber
  INTO #tmp
  FROM RoomCleaningDate WITH (NOLOCK)
  GROUP BY RoomNumber

  UPDATE Rooms SET CleanStatus = @CleanStatus 
  WHERE Rooms.RoomNumber IN 
  (
    SELECT #tmp.RoomNumber
    FROM #tmp WITH (NOLOCK)
    INNER JOIN RoomCleaningDate WITH (NOLOCK) ON RoomCleaningDate.Id = #tmp.Id 
      AND RoomCleaningDate.CleanStatus = 0
      AND RoomCleaningDate.CleaningDate <= CAST(DATEADD(DAY, @CleanDays, GETDATE()) AS DATE)
    WHERE 1 = 1
    AND #tmp.RoomNumber NOT IN -- Exclude inhouse rooms
    (
      SELECT RoomNumber
      FROM ReservationRoomLine WITH (NOLOCK)
      WHERE StatusRL = 5
      AND NOT RoomNumber IS NULL
      UNION
      SELECT DISTINCT pr.RoomNumber
      FROM ReservationRoomLine WITH (NOLOCK)
      INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLineXTab.LineNumber = ReservationRoomLine.LineNumber
      INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationRoomLineXTab.ReferenceNumber
        AND Reservations.GuestNumber = ReservationRoomLineXTab.GuestNumber
      INNER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber
      INNER JOIN Rooms pr WITH (NOLOCK) ON pr.RoomNumber = Rooms.ParentRoomNumber
      WHERE ((Reservations.GuestNumber = 0 AND ReservationRoomLine.DepartureDate >= '20190304' AND ReservationRoomLine.ArrivalDate <= '20190304') OR 
        (Reservations.GuestNumber > 0 AND ReservationRoomLine.DepartureDate >= '20190304' AND ReservationRoomLine.ArrivalDate <= '20190304'))
      AND ReservationRoomLine.RoomNumber > 0
      AND ReservationRoomLine.StatusRL = 5

    )
  )

  DROP TABLE #tmp
END