CREATE PROCEDURE dbo.GetOutOfOrder @ARoomTypeCSV VARCHAR(8000), @AStartDate datetime, @AEndDate datetime, @AHall BIT
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

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
	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

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
		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
	ELSE IF @ReferenceNumber = -2
	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
	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