CREATE PROCEDURE [dbo].[ChangeCleanStatus] (@ARoomNumber INT, @ACleanStatus INT, @AUserIDNumber INT, @AForceUpdate BIT = 0) AS
BEGIN
	DECLARE @SystemDate VARCHAR(8),
	@SystemDateTime DATETIME,
	@HotelCustomerNumber INT,
	@MainSubInteraction INT,
	@ParentRoomNumber INT,
	@UpdateRoom BIT,
	@UpdateMainRoom BIT,
	@MainRoomReferenceNumber INT,
	@MaxCleanStatus INT

	SELECT @SystemDate = SYSTEMDATE FROM SYSTEM (NOLOCK)
  EXEC SystemDateTime '', @SystemDateTime OUTPUT

	SELECT @HotelCustomerNumber = C.CustomerNumber, @ParentRoomNumber = ISNULL(ParentRoomNumber, 0)
	FROM Rooms ro (NOLOCK) 
	INNER JOIN Customer C On ro.HotelNumber = c.HotelNumber AND ro.RoomNumber = @ARoomNumber

	SET @HotelCustomerNumber = ISNULL(@HotelCustomerNumber, 0)
	
	SET @UpdateRoom = 0
	SET @UpdateMainRoom = 0

	IF (@ParentRoomNumber = 0) OR @AForceUpdate = 1
		SET @UpdateRoom = 1

	IF @HotelCustomerNumber > 0
	BEGIN
		IF dbo.GetHotelSetupKeyValue('EnableRoomInRoom', @HotelCustomerNumber) = '1'
		BEGIN
			SET @MainSubInteraction = CAST(dbo.GetHotelSetupKeyValue('CleaningMainSubInteraction', @HotelCustomerNumber) AS INT)

			IF @MainSubInteraction > 0
			BEGIN
				IF @ParentRoomNumber = 0
				BEGIN
					select @MainRoomReferenceNumber = r.ReferenceNumber FROM Rooms ro (NOLOCK)
					INNER JOIN RESERVATIONROOMLINE rl (NOLOCK) ON ro.RoomNumber = rl.RoomNumber AND ro.RoomNumber = @ARoomNumber
					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 < 6
					WHERE rl.ArrivalDate <= @SystemDate AND rl.DepartureDate >= @SystemDate

					SET @MainRoomReferenceNumber = ISNULL(@MainRoomReferenceNumber, -99)

					IF dbo.GetHotelSetupKeyValue('CleaningOnlyUpdateInhouseSubRooms', @HotelCustomerNumber) = '1' AND @MainRoomReferenceNumber IN (-3, -4, -99)
						IF @ACleanStatus > 0
						BEGIN
							SELECT ro.RoomNumber INTO #T1 FROM Rooms ro (NOLOCK)
							INNER JOIN RESERVATIONROOMLINE rl (NOLOCK) ON ro.RoomNumber = rl.RoomNumber AND ro.ParentRoomNumber = @ARoomNumber
							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


							UPDATE ROOMS
							SET CleanStatus = @ACleanStatus
							WHERE RoomNumber IN (SELECT RoomNumber FROM #T1)

							INSERT INTO CleanStatusLog (DateStamp, FK_UserIdNumber, FK_RoomNumber, CleanStatus)
							SELECT @SystemDateTime, @AUserIDNumber, RoomNumber, @ACleanStatus FROM #T1

							DROP TABLE #T1
						END ELSE
						BEGIN
							SELECT ro.RoomNumber INTO #T2 FROM Rooms ro (NOLOCK)
							WHERE ParentRoomNumber = @ARoomNumber AND CleanStatus > 0

							UPDATE ROOMS
							SET CleanStatus = @ACleanStatus
							WHERE RoomNumber IN (SELECT RoomNumber FROM #T2)

							INSERT INTO CleanStatusLog (DateStamp, FK_UserIdNumber, FK_RoomNumber, CleanStatus)
							SELECT @SystemDateTime, @AUserIDNumber, RoomNumber, @ACleanStatus FROM #T2

							DROP TABLE #T2
						END

					ELSE
					BEGIN
						UPDATE ROOMS
						SET CleanStatus = @ACleanStatus
						WHERE ParentRoomNumber = @ARoomNumber

						INSERT INTO CleanStatusLog (DateStamp, FK_UserIdNumber, FK_RoomNumber, CleanStatus)
						SELECT @SystemDateTime, @AUserIDNumber, RoomNumber, @ACleanStatus FROM Rooms (NOLOCK)
						WHERE ParentRoomNumber = @ARoomNumber					
					END
					
				END ELSE
				BEGIN
					IF @MainSubInteraction = 2
					BEGIN
						SET @UpdateRoom = 1
						SELECT @MaxCleanStatus = Max(CleanStatus) FROM Rooms (NOLOCK)
						WHERE ParentRoomNumber = @ParentRoomNumber

						IF @MaxCleanStatus < @ACleanStatus
							Set @MaxCleanStatus = @ACleanStatus
						
						IF @MaxCleanStatus <> (Select CleanStatus From Rooms (NOLOCK) 
											   WHERE RoomNumber = @ParentRoomNumber)
							Set @UpdateMainRoom = 1

						
					END ELSE 
						IF @MainSubInteraction = 1 AND @AForceUpdate = 1 AND EXISTS (SELECT RoomNumber FROM ROOMS (NOLOCK) WHERE RoomNumber = @ParentRoomNumber AND CleanStatus = 0)
						BEGIN
 							SET @UpdateMainRoom = 1
							Set @MaxCleanStatus = @ACleanStatus
						END
				END
			END
		END
	END

	IF @UpdateRoom = 1	
	BEGIN
		UPDATE Rooms
		Set CleanStatus = @ACleanStatus
		WHERE RoomNumber = @ARoomNumber

		INSERT INTO CleanStatusLog (DateStamp, FK_UserIdNumber, FK_RoomNumber, CleanStatus)
		VALUES (@SystemDateTime, @AUserIDNumber, @ARoomNumber, @ACleanStatus)
	END

	IF @UpdateMainRoom = 1
	BEGIN
		UPDATE ROOMS
		SET CleanStatus = @MaxcleanStatus
		WHERE RoomNumber = @ParentRoomNumber

		INSERT INTO CleanStatusLog (DateStamp, FK_UserIdNumber, FK_RoomNumber, CleanStatus)
		VALUES (@SystemDateTime, @AUserIDNumber, @ParentRoomNumber, @ACleanStatus)
	END

END
