CREATE PROCEDURE CalculateCleaningStatus @ARoomNumber INT, @ADate DATETIME, @ACleaningStatus INT OUTPUT

AS

DECLARE 
@DateStr CHAR(8),
@NumberOfRes INT,
@NumberOfDeparting INT,
@RoomNumber INT,
@StayCleaningInterval INT,
@DepartureDate CHAR(8),
@LastStayCleaningDate DATETIME,
@LastDepartureCleaningDate DATETIME,
@SetupStayDays INT,
@SetupDepDays INT,
@SetupDepGuest INT,
@Continue BIT,
@LeaveRoomCount INT

SET @ACleaningStatus = 0
SET @NumberOfDeparting = 0
SET @DateStr = CONVERT (CHAR(8), @ADate, 112)

 

select rl.LineNUmber, rl.departureDate, rl.RoomNumber, ro.ParentRoomNumber, ISNULL(ro.LeaveRoomCount, 0) AS LeaveRoomCount into #TEMP
from RESERVATIONROOMLINE rl (NOLOCK)
inner join RESERVATIONROOMLINEXTAB rlx (NOLOCK) on rl.LINENUMBER = rlx.LINENUMBER and rlx.ReferenceNumber > 0
inner join RESERVATIONS r (NOLOCK) on rlx.ReferenceNumber = r.ReferenceNumber and rlx.GuestNumber = r.GuestNumber and r.Status = 5
inner join ROOMS ro (NOLOCK) on rl.RoomNumber = ro.RoomNumber and (ro.RoomNumber = @ARoomNumber or ro.ParentRoomNumber = @ARoomNumber)
where rl.ArrivalDate <= @DateStr and rl.DepartureDate >= @DateStr
Union
Select 0 AS linenumber, 0 AS departureDate, ro2.RoomNumber, ro2.ParentRoomNumber, ISNULL(ro2.LeaveRoomCount, 0) AS LeaveRoomCount
FROM ROOMS ro2 (NOLOCK)
WHERE ISNULL(ro2.LeaveRoomCount,0) > 0
AND ro2.RoomNumber = @ARoomNumber
AND ro2.RoomNumber Not IN 
(select rl2.RoomNumber
from RESERVATIONROOMLINE rl2 (NOLOCK)
inner join RESERVATIONROOMLINEXTAB rlx2 (NOLOCK) on rl2.LINENUMBER = rlx2.LINENUMBER and rlx2.ReferenceNumber > 0
inner join RESERVATIONS r2 (NOLOCK) on rlx2.ReferenceNumber = r2.ReferenceNumber and rlx2.GuestNumber = r2.GuestNumber and r2.Status = 5
inner join ROOMS ro3 (NOLOCK) on rl2.RoomNumber = ro3.RoomNumber and (ro3.RoomNumber = @ARoomNumber or ro3.ParentRoomNumber = @ARoomNumber)
where rl2.ArrivalDate <= @DateStr and rl2.DepartureDate >= @DateStr)


select @NumberOfRes = COUNT(*) from #TEMP
SET @Continue = 1

IF @NumberOfRes > 0
BEGIN
	SELECT @StayCleaningInterval = StayCleanInterval 
	FROM ROOMTYPES rt (NOLOCK)
	INNER JOIN ROOMS r (NOLOCK) ON rt.RoomTypeNumber = r.RoomTypeNumber AND r.RoomNumber = @ARoomNumber

	SET @StayCleaningInterval = ISNULL(@StayCleaningInterval, 0) 

	Exec GetLastCleaningDate @ARoomNumber, 6, @LastStayCleaningDate OUTPUT
	Exec GetLastCleaningDate @ARoomNumber, 7, @LastDepartureCleaningDate OUTPUT

	IF @NumberOfRes = 1
	BEGIN
		SELECT @RoomNumber = RoomNumber, @DepartureDate = DepartureDate, @LeaveRoomCount = LeaveRoomCount FROM #TEMP
		IF @RoomNumber = @ARoomNumber
		BEGIN
			IF @DateStr = @DepartureDate OR (@DepartureDate = 0 AND @LeaveRoomCount > 0)
				SET @ACleaningStatus = 6
			ELSE
				IF @StayCleaningInterval > 0
				BEGIN
					IF DATEADD(DAY, @StayCleaningInterval, @LastStayCleaningDate) = @ADate
						SET @ACleaningStatus = 7
				END
			SET @Continue = 0
		END
	END
	IF @Continue = 1
	BEGIN
		SELECT @SetupStayDays =  KeyValue FROM HotelSetup (NOLOCK) WHERE SEARCHKEY = 'CleaningSubRoomStayDays'
		SELECT @SetupDepDays =  KeyValue FROM HotelSetup (NOLOCK) WHERE SEARCHKEY = 'CleaningSubRoomDepDays'
		SELECT @SetupDepGuest =  KeyValue FROM HotelSetup (NOLOCK) WHERE SEARCHKEY = 'CleaningSubRoomDepGuests'

		SET @SetupDepGuest = ISNULL(@SetupDepGuest, 0)

		SELECT @NumberOfDeparting = COUNT(*), @LeaveRoomCount = MAX(LeaveRoomCount)
		FROM #TEMP
		WHERE DepartureDate = @DateStr OR DepartureDate = 0

		SET @NumberOfDeparting = ISNULL(@NumberOfDeparting, 0) + ISNULL(@LeaveRoomCount, 0)

		IF (@NumberOfDeparting = @NumberOfRes) OR  (@NumberOfDeparting >= @SetupDepGuest)
			Set @ACleaningStatus = 6
		ELSE
			IF DATEADD(DAY, @SetupDepDays, @LastDepartureCleaningDate) = @ADate
				SET @ACleaningStatus = 6
			ELSE
				IF DATEADD(DAY, @SetupStayDays, @LastStayCleaningDate) = @ADate
					SET @ACleaningStatus = 7
	END

	UPDATE ROOMS
	SET LeaveRoomCount = 0
	WHERE RoomNumber = @ARoomNumber

END