CREATE PROCEDURE [dbo].[UpdateSubRoomCleanStatus]  AS
SET NOCOUNT ON

	DECLARE @CustomerNumber INT,
	@HotelNumber INT,
	@RoomNumber INT,
	@CleanStatus INT,
	@SystemDate VARCHAR(8)

	SELECT @SystemDate = SYSTEMDATE FROM SYSTEM (NOLOCK)

	DECLARE #CUR Cursor FOR
		Select customerNumber, HotelNumber FROM customer (nolock)
		WHERE ISNULL(HotelNumber, -1) > -1 AND MarkAsDeleted = 0

	OPEN #CUR

	FETCH NEXT FROM #CUR INTO
	@CustomerNumber, @HotelNumber

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF dbo.GetHotelSetupKeyValue('EnableRoomInRoom', @CustomerNumber) = '1'
		BEGIN
		  IF dbo.GetHotelSetupKeyValue('CleaningMainSubInteraction', @CustomerNumber) = '1'
		  BEGIN
			UPDATE Rooms
			SET CleanStatus = 0
			WHERE ISNULL(ParentRoomNumber, 0) > 0

			DECLARE #CUR2 CURSOR FOR
				SELECT RoomNumber, CleanStatus FROM Rooms (NOLOCK)
				WHERE ISNULL(ParentRoomNumber,0) = 0

			OPEN #CUR2
			FETCH NEXT FROM #CUR2 INTO
			@RoomNumber, @CleanStatus
			WHILE @@FETCH_STATUS = 0
			BEGIN
				IF @CleanStatus > 0
				BEGIN
					IF dbo.GetHotelSetupKeyValue('CleaningOnlyUpdateInhouseSubRooms', @CustomerNumber) = '1' AND
					   EXISTS (SELECT rl.LineNumber FROM RESERVATIONROOMLINE rl (NOLOCK)
							   INNER JOIN ReservationRoomlineXTAB rlx (NOLOCK) ON rl.LineNumber = rlx.LineNumber AND RoomNumber = @RoomNumber AND ReferenceNumber In (-3,-4)
							   WHERE ArrivalDate <= @SystemDate AND DepartureDate >= @SystemDate)
						UPDATE Rooms
						SET CleanStatus = @CleanStatus
						FROM Rooms ro (NOLOCK)
						INNER JOIN RESERVATIONROOMLINE rl (NOLOCK) ON ro.RoomNumber = rl.RoomNumber AND ro.ParentRoomNumber = @RoomNumber
						INNER JOIN RESERVATIONROOMLINEXTAB rlx (NOLOCK) ON rl.LINENUMBER = rlx.LINENUMBER
						INNER JOIN Reservations r (NOLOCK) ON rlx.ReferenceNumber = r.ReferenceNumber and rlx.GuestNumber = r.GuestNumber AND r.Status = 5
						WHERE rl.ArrivalDate <= @SystemDate AND rl.DepartureDate >= @SystemDate

					ELSE
						IF EXISTS (SELECT Roomnumber FROM ROOMS (NOLOCK) WHERE ParentRoomNumber = @RoomNumber AND CleanStatus <> @CleanStatus)
							UPDATE Rooms
							SET CleanStatus = @CleanStatus
							WHERE ParentRoomNumber = @RoomNumber
				END
				FETCH NEXT FROM #CUR2 INTO
				@RoomNumber, @CleanStatus
			END

			CLOSE #CUR2
			DEALLOCATE #CUR2
		  END
		END

		FETCH NEXT FROM #CUR INTO
		@CustomerNumber, @HotelNumber
	END

	CLOSE #CUR
	DEALLOCATE #CUR