CREATE PROCEDURE dbo.UpdateCleanStatusOOO @ADateTime DATETIME AS
/*
  Created by Kåre Christensen 20230804
  Update clean status for OOO/OFS rooms if clean statushas been clean for x days.

*/

SET NOCOUNT ON
DECLARE @CleanDays INT
DECLARE @CleanStatus INT
DECLARE @DateTimeStr VARCHAR(8)
SET @DateTimeStr = CONVERT(VARCHAR(8), @ADateTime, 112)

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

IF @CleanDays < 0
BEGIN
  SELECT @CleanStatus = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'CleanRoomOOOStatus'
  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 CAST(RoomCleaningDate.CleaningDate AS DATE) <= CAST(DATEADD(DAY, @CleanDays, @ADateTime) AS DATE)
	INNER JOIN ReservationRoomline rl (NOLOCK) ON #tmp.RoomNumber = rl.RoomNumber
	AND rl.ArrivalDate <= @DateTimeStr and rl.DepartureDate > @DateTimeStr
	INNER JOIN RESERVATIONROOMLINEXTAB rlx (NOLOCK) ON rl.LINENUMBER = rlx.LINENUMBER
	AND ReferenceNumber in (0,-2)
  )
  

  DROP TABLE #tmp
END