CREATE PROCEDURE dbo.GetOutOfOrder @ARoomTypeCSV VARCHAR(8000), @AStartDate datetime, @AEndDate datetime, @AHall BIT, @ASummarize BIT = 1
AS
BEGIN
SET NOCOUNT ON

DECLARE @SystemDate DATE,
@FirstDate DATETIME, 
@LastDate DATETIME,
@CurrentStatus INT,
@ReferenceNumber INT,
@Status INT,
@RoomNumberID INT,
@ArrivalDate DATETIME,
@DepartureDate DATETIME,
@RoomNumber INT,
@RoomType VARCHAR(10),
@Note VARCHAR(8000),
@tmpStatus INT,
@PeriodMarker VARCHAR(1),
@First BIT,
@SQL VARCHAR(MAX)

CREATE TABLE #tmpTable (
		RoomNumber INT Not NULL,
		Number INT NULL,
		RoomType VARCHAR(10) NULL,
		[Status] INT NULL,
		FirstOOODate DATETIME NULL,
		PeriodMarker VARCHAR(1) NULL,
		LastOOODate DATETIME NULL,
		FirstOFSDate DATETIME NULL,
		PeriodMarkerOFS VARCHAR(1) NULL,
		LastOFSDate DateTime NULL,
		OutOfOrderNote VARCHAR(8000) NULL,
		OutForServiceNote VARCHAR(8000) NULL
		
	)
IF @ASummarize = 1
	ALTER TABLE #tmpTable WITH NOCHECK
	ADD PRIMARY KEY CLUSTERED (RoomNumber ASC)

SELECT @SystemDate = CONVERT(DATETIME,SystemDate,112) FROM dbo.System (NOLOCK) WHERE Number = 1

IF @AHall = 0
	
	DECLARE #CUR CURSOR FOR

		SELECT DISTINCT Reservations.ReferenceNumber, Reservations.Status, ReservationRoomLine.RoomNumber,
		CONVERT(DATETIME,ReservationRoomLine.ArrivalDate,112)+CONVERT(DATETIME,ReservationRoomLine.ArrivalTime,108) AS ArrivalDate,
		CONVERT(DATETIME,ReservationRoomLine.DepartureDate,112)+CONVERT(DATETIME,ReservationRoomLine.DepartureTime,108) AS DepartureDate,
		Rooms.Number, RoomTypes.Type,
		iu.Note
		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
		  AND Reservations.Status < 7
		INNER JOIN Rooms  WITH (NOLOCK) ON Rooms.RoomNumber = ReservationRoomLine.RoomNumber
		INNER JOIN RoomTypes  WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
		  AND (@ARoomTypeCSV = '' OR RoomTypes.RoomTypeNumber IN  (SELECT DISTINCT Number FROM iter_intlist_to_table_no_dummy(@ARoomTypeCSV)))
		LEFT OUTER JOIN InUseNoteXTAB iux WITH (NOLOCK) ON ReservationRoomLine.LineNumber = iux.ObjectNumber and iux.ObjectType = 0
		LEFT OUTER JOIN InUseNote iu WITH (NOLOCK) ON iux.NoteNumber = iu.NoteNumber
		WHERE CONVERT(DATETIME, ReservationRoomLine.ArrivalDate, 112) <  @AEndDate 
		AND CONVERT(DATETIME, ReservationRoomLine.DepartureDate, 112) >= @AStartDate
		UNION
		SELECT -1 AS ReferenceNumber, -1 AS Status, Rooms.RoomNumber, 0 AS ArrivalDate, 0 AS DEpartureDate, Rooms.Number, RoomTypes.Type, '''' AS Note
		FROM ROOMS WITH (NOLOCK)
		INNER JOIN RoomTypes  WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
		  AND (@ARoomTypeCSV = '' OR RoomTypes.RoomTypeNumber IN  (SELECT DISTINCT Number FROM iter_intlist_to_table_no_dummy(@ARoomTypeCSV)))
		WHERE NOT EXISTS
		(SELECT RESERVATIONROOMLINE.LINENUMBER FROM RESERVATIONROOMLINE WITH (NOLOCK)
		INNER JOIN RESERVATIONROOMLINEXTAB  WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER
		INNER JOIN RESERVATIONS  WITH (NOLOCK) ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber 
			AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber 
			AND RESERVATIONS.Status < 7
		WHERE ReservationRoomLine.RoomNumber = ROOMS.RoomNumber
		AND CONVERT(DATETIME, ReservationRoomLine.ArrivalDate, 112) <  @AEndDate 
		AND CONVERT(DATETIME, ReservationRoomLine.DepartureDate, 112) >= @AStartDate)
		ORDER BY ROOMS.Number, CONVERT(DATETIME,ReservationRoomLine.ArrivalDate,112)+CONVERT(DATETIME,ReservationRoomLine.ArrivalTime,108)

ELSE

	DECLARE #CUR CURSOR FOR

		SELECT DISTINCT Reservations.ReferenceNumber, Reservations.Status, RESERVATIONCONFERENCELINE.ConferenceRoomNumber,
		CONVERT(DATETIME,ReservationConferenceLine.ArrivalDate,112)+CONVERT(DATETIME,ReservationConferenceLine.ArrivalTime,108) AS ArrivalDate,
		CONVERT(DATETIME,ReservationConferenceLine.DepartureDate,112)+CONVERT(DATETIME,ReservationConferenceLine.DepartureTime,108) AS DepartureDate,
		ConferenceRooms.Number, ConferenceRoomTypes.Type,
		iu.Note
		FROM ReservationConferenceLine  WITH (NOLOCK)
		INNER JOIN Reservations  WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber=Reservations.ReferenceNumber
		  AND Status < 7
		  AND Reservations.GuestNumber = 0
		INNER JOIN ConferenceRoomsXtab  WITH (NOLOCK) ON ConferenceRoomsXtab.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber
		  AND ConferenceRoomsXtab.ConferenceRoomNumber > 0
		  AND ISNULL(SchemePattern, 0) = 0
		INNER JOIN ConferenceRooms  WITH (NOLOCK) ON ConferenceRooms.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber
		  AND ISNULL(ConferenceRooms.Istable, 0) = 0
		INNER JOIN ConferenceRoomTypes  WITH (NOLOCK) ON ConferenceRoomTypes.ConferenceRoomTypeNumber = ConferenceRooms.ConferenceRoomTypeNumber
		 AND (@ARoomTypeCSV = '' OR ConferenceRoomTypes.ConferenceRoomTypeNumber IN  (SELECT DISTINCT Number FROM iter_intlist_to_table_no_dummy(@ARoomTypeCSV)))
		LEFT OUTER JOIN InUseNoteXTAB iux WITH (NOLOCK) ON ReservationConferenceLine.ReservationConfLineNumber = iux.ObjectNumber and iux.ObjectType = 1
		LEFT OUTER JOIN InUseNote iu WITH (NOLOCK) ON iux.NoteNumber = iu.NoteNumber 
		WHERE CONVERT(DATETIME, ReservationConferenceLine.ArrivalDate, 112) < @AEndDate
		AND CONVERT(DATETIME, ReservationConferenceLine.DepartureDate, 112) >= @AStartDate
		UNION
		SELECT -1 AS referencenumber, -1 as Status, CONFERENCEROOMS.ConferenceRoomNumber, 0 AS ArrivalDate, 0 AS DepartureDate, Number,  ConferenceRoomTypes.Type,
		'' AS Note   
		FROM dbo.ConferenceRooms WITH (NOLOCK) 
		INNER JOIN ConferenceRoomTypes  WITH (NOLOCK) ON ConferenceRoomTypes.ConferenceRoomTypeNumber = ConferenceRooms.ConferenceRoomTypeNumber
			AND (@ARoomTypeCSV = '' OR ConferenceRoomTypes.ConferenceRoomTypeNumber IN  (SELECT DISTINCT Number FROM iter_intlist_to_table_no_dummy(@ARoomTypeCSV)))
		WHERE ISNULL(IsTable, 0) = 0
		AND ISNULL(MarkAsDeleted, 0) = 0
		AND NOT EXISTS
			(SELECT ReservationConfLineNumber FROM ReservationConferenceLine  WITH (NOLOCK)
			INNER JOIN Reservations  WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber=Reservations.ReferenceNumber
				AND Status < 7
			WHERE ReservationConferenceLine.ConferenceRoomNumber = ConferenceRooms.ConferenceRoomNumber
			AND CONVERT(DATETIME, ReservationConferenceLine.ArrivalDate, 112) < @AEndDate
			AND CONVERT(DATETIME, ReservationConferenceLine.DepartureDate, 112) >= @AStartDate)
		ORDER BY dbo.ConferenceRooms.Number

Open #CUR

FETCH NEXT FROM #CUR 
INTO @ReferenceNumber, @Status, @RoomNumberID, @ArrivalDate, @DepartureDate,@RoomNumber,@RoomType,@Note

WHILE (@@FETCH_STATUS = 0)
BEGIN
	IF @ReferenceNumber = 0
	BEGIN
		IF @ASummarize = 0
		BEGIN
			INSERT INTO #tmpTable(RoomNumber, Number, RoomType, [Status], FirstOOODate, PeriodMarker,  LastOOODate, OutOfOrderNote)
			VALUES (@RoomNumberID, @RoomNumber, @RoomType, 7, @ArrivalDate, '-', @DepartureDate, @Note) 	
		END
		ELSE
		BEGIN
			SELECT @FirstDate = FirstOOODate, @LastDate = LastOOODate, @tmpStatus = [Status] FROM #tmpTable WHERE RoomNumber = @RoomNumberID
			IF @@rowcount > 0
			BEGIN 
			  IF @tmpStatus <> 7
			  BEGIN
				SET @FirstDate = @ArrivalDate
				SET @LastDate = @DepartureDate
				SET @PeriodMarker = '-'
				SET @tmpStatus = 7
			  END ELSE
			  BEGIN
				IF @ArrivalDate < ISNULL(@FirstDate, 0) OR ISNULL(@FirstDate, 0) = 0
					SET @FirstDate = @ArrivalDate
				IF @DEpartureDate > ISNULL(@LastDate, 0)
					SET @LastDate = @DepartureDate
				SET @PeriodMarker = '/'
			  END
				UPDATE #tmpTable
					SET FirstOOODate = @FirstDate,
					LastOOODate = @LastDate,
					PeriodMarker = @PeriodMarker,
					Status = @tmpStatus
				WHERE RoomNumber = @RoomNumberID
			END ELSE
				INSERT INTO #tmpTable(RoomNumber, Number, RoomType, [Status], FirstOOODate, PeriodMarker,  LastOOODate, OutOfOrderNote)
				VALUES (@RoomNumberID, @RoomNumber, @RoomType, 7, @ArrivalDate, '-', @DepartureDate, @Note)  
		END
	END
	ELSE IF @ReferenceNumber = -2
	BEGIN
		IF @ASummarize = 0
		BEGIN
			INSERT INTO #tmpTable(RoomNumber, Number, RoomType, [Status], FirstOFSDate, 	PeriodMarkerOFS,  LastOFSDate, OutForServiceNote)
		  VALUES (@RoomNumberID, @RoomNumber, @RoomType, 7, @ArrivalDate, '-', @DepartureDate, @Note)  
		END
		ELSE
		BEGIN
			SELECT @FirstDate = FirstOFSDate, @LastDate = LastOFSDate FROM #tmpTable WHERE RoomNumber = @RoomNumberID
			IF @@rowcount > 0 
			BEGIN
				IF ISNULL(@FirstDate, 0) = 0
					SET @First = 1
				ELSE
					SET @First = 0
				IF @ArrivalDate < ISNULL(@FirstDate, 0) OR @First = 1
					SET @FirstDate = @ArrivalDate
				IF @DEpartureDate > ISNULL(@LastDate, 0)
					SET @LastDate = @DepartureDate
				IF @First = 1
					SET @PeriodMarker = '-'
				ELSE
					SET @PeriodMarker = '/'
				UPDATE #tmpTable
					SET FirstOFSDate = @FirstDate,
					LastOFSDate = @LastDate,
					PeriodMarkerOFS = @PeriodMarker
				WHERE RoomNumber = @RoomNumberID
			END ELSE
			  INSERT INTO #tmpTable(RoomNumber, Number, RoomType, [Status], FirstOFSDate, 	PeriodMarkerOFS,  LastOFSDate, OutForServiceNote)
			  VALUES (@RoomNumberID, @RoomNumber, @RoomType, 7, @ArrivalDate, '-', @DepartureDate, @Note)  
		END
	END
	ELSE IF @ReferenceNumber IN (-3, -4)
	BEGIN
		SELECT @CurrentStatus = Status FROM #tmpTable WHERE RoomNumber = @RoomNumberID	
		IF @@rowcount = 0 
			INSERT INTO #tmpTable(RoomNumber, Number, RoomType, [Status])
			VALUES (@RoomNumberID, @RoomNumber, @RoomType, 8)  
	END
	ELSE
	BEGIN
		SELECT @CurrentStatus = Status FROM #tmpTable WHERE RoomNumber = @RoomNumberID	
		IF @@ROWCOUNT > 0
		BEGIN
			IF @Status = 5 --Inhouse
			BEGIN
				IF (@CurrentStatus IN (0, 1, 2, 3, 4, 6)) AND (CAST(@departureDate AS DATETIME) <> @SystemDate)
					SET @CurrentStatus = @Status
			END ELSE
			IF @Status = 2 --Guaranteed
			BEGIN
				IF @CurrentStatus IN (0, 1, 3, 4, 6)
					SET @CurrentStatus = @Status
			END ELSE
			IF @Status = 0 --Confirmed
			BEGIN
				IF @CurrentStatus IN (1, 3, 4, 6)
					SET @CurrentStatus = @Status
			END
			IF @Status = 1 --Provinsional
			BEGIN
				IF @CurrentStatus IN (3, 4, 6)
					SET @CurrentStatus = @Status
			END
			IF @Status = 3 --Waitinglist
			BEGIN
				IF @CurrentStatus IN (4, 6)
					SET @CurrentStatus = @Status
			END
			IF @Status = 4 --Tentative
			BEGIN
				IF @CurrentStatus = 6
					SET @CurrentStatus = @Status
			END
			IF @Status = 6 --OutOfHouse
				SET @CurrentStatus = @Status
			
			UPDATE #tmpTable
			Set Status = @CurrentStatus
			WHERE RoomNumber = @RoomNumberID
						
		END ELSE	
		  INSERT INTO #tmpTable(RoomNumber, Number, RoomType, [Status])
		  VALUES (@RoomNumberID, @RoomNumber, @RoomType, @Status) 		
	END
	FETCH NEXT FROM #CUR 
	INTO @ReferenceNumber, @Status, @RoomNumberID, @ArrivalDate, @DepartureDate,@RoomNumber,@RoomType,@Note
END

CLOSE #cur
DEALLOCATE #cur

SELECT * From #tmpTable
ORDER BY Number

END