CREATE PROCEDURE DBO.SetOutOfOrderRoom @ARoomNumberID INT, @StartDate DATETIME, @EndDate DATETIME, @ErrorLevel INT OUTPUT, @Note VARCHAR(8000), @OutOfOrderMode INT, @ParentRoomNumber INT = 0 AS
SET NOCOUNT ON
/*
    Created By: Jacob Pedersen 24/6-2002

    Last Updated:
	KC 20171127: Replacing @Skallerup, @OutForService and @Occupied with @utOfOrderMode
    KJ 20161230: Using UPPER results in table scan. removed to use index
   AV 2008 1029 fix for error when inserting notetype into inusenotextab
   BA/AV 20080115 IF (@Note <> '') AND (@Note <> NULL) Changed to IF (@Note <> '') AND (@Note IS NOT NULL)
    JP 20050518 Use GetNextNumber instead of directly updating table
    JP 20050107 Use parameter times instead of from hotelsetup. huge rewrite was needed
    JP 20050103 Insert Note
    JP 20041117 @PeriodiclyOutOfOrder and @ReferenceNumber not set correctly
    JP 20041101 fix for normal houses not being able to be set outoforder at skallerup
    JP 20041029 fix for normal houses not being able to be set outoforder at skallerup
    JP 20041027 fix for if something went wrong at Skallerup and they tried to set a owned house (not periodicly) outoforder, then they would become periodicly
    JP 20041027 fix for wrong check for adding dates at start or end of existing record
    JP 20040405 Out-for-Service on res -2	
		10/02-2004 Jacob Pedersen Check for SetOwnedRoomsOutOfOrder setting
		11/11-2003 Jacob Pedersen RoomOwned stuff for Skallerup. Store on res -1
		07/04-2003 Jacob Pedersen fix for merge code
		09/10-2002 Jacob Pedersen went from using 00:00 to 23 on the same day, to using hotel setup 18:00 to 12:00 on two days

    NOTE: @Skallerup is only set from In-Use plan, so initial OOO status is stored on res -1
	   @Out-For-Service is stored on res -2

    ERROR LEVELS:
    00 : No problems
  >00 : Number of reservations in specified period. Cannot set room outoforder
    
*/

-- Pseudocode:
-- check if any roomline which has a departuredate > startdate. if so change it's departuredate to @EndDate
-- else
--   check if any roomline has an arrivaldate < @enddate, if so change it's arrivaldate to @startdate
-- else
--   create new roomline which has an arrival and depature equal to startdate and enddate


--SET lock_timeout 50000  -- give updates and inserts a chance to complete! 

SET @OutOfOrderMode = ISNULL(@OutOfOrderMode, 0)

DECLARE @SetOwnedRoomsOutOfOrder BIT
SELECT @SetOwnedRoomsOutOfOrder=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE UPPER (SearchKey) = UPPER ('SetOwnedRoomsOutOfOrder')

DECLARE @OFSBehaveLIKEOOO BIT
SELECT @OFSBehaveLIKEOOO=ISNULL(KeyValue,0) FROM HotelSetup WITH (NOLOCK) WHERE UPPER (SearchKey) = UPPER ('OOSDecreaseVacantRooms')

IF IsNull (@SetOwnedRoomsOutOfOrder, 0) = 0
  Set @SetOwnedRoomsOutOfOrder = 0
  
IF (IsNull (@OFSBehaveLIKEOOO, 0) = 0) OR @OutOfOrderMode <> 1
  Set @OFSBehaveLIKEOOO = 0

CREATE TABLE #TEMP
(RoomNumber INT)
  

DECLARE @LineNumber INT,
@ReferenceNumber INT,
@RoomOwned INT,
@RoomNumber INT
SET @ReferenceNumber = 0
SET @RoomOwned = 0
IF @OutOfOrderMode = 4 AND @SetOwnedRoomsOutOfOrder = 1
BEGIN
  DECLARE @PeriodiclyOutOfOrder BIT
  SELECT @RoomOwned = CustomerNumber, @PeriodiclyOutOfOrder = PeriodiclyOutOfOrder FROM Rooms WITH (NOLOCK) WHERE RoomNumber=@ARoomNumberID

  IF IsNull (@PeriodiclyOutOfOrder, 0) = 0
    SET @PeriodiclyOutOfOrder = 0

  IF @PeriodiclyOutOfOrder > 0
    SET @ReferenceNumber = -1
END
IF @OutOfOrderMode = 1
  SET @ReferenceNumber = -2
  
IF @OutOfOrderMode = 2
  SET @ReferenceNumber = -3

IF @OutOfOrderMode = 3
  SET @ReferenceNumber = -4

IF @OutOfOrderMode = 4 AND @RoomOwned > 0
BEGIN
  IF @PeriodiclyOutOfOrder = 0  -- delete old incorrect posts at referencenumber -1 and then fill the holes correctly
  BEGIN
    DECLARE #fuckedup CURSOR /*FAST_FORWARD*/ FOR
/*
      select reservationroomlinextab.linenumber from reservationroomlinextab WITH (nolock) inner join ReservationRoomLine (NOLOCK) on reservationroomlinextab.LineNumber=ReservationRoomLine.LineNumber
      where referencenumber=-1 and RoomNumber = @RoomNumberID
*/ -- JP 20050106 faster than subselect to use inner join
      SELECT reservationroomlinextab.linenumber FROM reservationroomlinextab WITH (NOLOCK) INNER JOIN ReservationRoomLine WITH (NOLOCK) ON referencenumber=-1 and reservationroomlinextab.LineNumber=ReservationRoomLine.LineNumber
      WHERE RoomNumber = @ARoomNumberID

    OPEN #fuckedup

    FETCH NEXT FROM #fuckedup
    INTO @LineNumber

    WHILE  (@@FETCH_STATUS <> -1)

    BEGIN
      delete from reservationroomline with (ROWLOCK) where linenumber = @LineNumber 
      delete from reservationroomlinextab with (ROWLOCK) where linenumber = @LineNumber 

      FETCH NEXT FROM #fuckedup
      INTO @LineNumber
   END

    CLOSE #fuckedup
    DEALLOCATE #fuckedup

    EXEC SetOutOfOrderRoomAllFuture @ARoomNumberID
    SET @ErrorLevel = 0
    RETURN
  END
END -- JP 20041027

DECLARE @TimeOfArrivalForRes CHAR (5) --TimeOfArrivalForRes '18:00'
DECLARE @TimeOfDepartureForRes CHAR (5) --TimeOfDepartureForRes '12:00'
/*
SELECT @TimeOfArrivalForRes=KeyValue FROM HotelSetup (NOLOCK) WHERE UPPER (SearchKey) = UPPER ('TimeOfArrivalForRes')
SELECT @TimeOfDepartureForRes=KeyValue FROM HotelSetup (NOLOCK) WHERE UPPER (SearchKey) = UPPER ('TimeOfDepartureForRes')
*/
IF @EndDate=0
  SET @EndDate=@StartDate

DECLARE @FormatedStartDate CHAR (8)
DECLARE @FormatedEndDate CHAR (8)
SET @FormatedStartDate = CONVERT (CHAR (8), @StartDate, 112)
--SET @FormatedEndDate = CONVERT (CHAR (8), @EndDate+1, 112)
SET @FormatedEndDate = CONVERT (CHAR (8), @EndDate, 112)
SET @TimeOfArrivalForRes = CONVERT (CHAR (5), @StartDate, 108)
SET @TimeOfDepartureForRes = CONVERT (CHAR (5), @EndDate, 108)

DECLARE @ArrivalDate CHAR (8)
DECLARE @DepartureDate CHAR (8)
DECLARE @ArrivalTime CHAR (5)
DECLARE @DepartureTime CHAR (5)
DECLARE @SetNote BIT
DECLARE @Status INT
DECLARE @RoomNumberID INT

IF @OutOfOrderMode <> 1 or (@OutOfOrderMode = 1 and @OFSBehaveLikeOOO = 1)
BEGIN
  SET @ErrorLevel = 0
  DECLARE @SystemDate CHAR(8)
  SELECT @SystemDate = SystemDate FROM System WITH (NOLOCK) WHERE Number=1
  DECLARE #cur CURSOR FOR	
	  SELECT rl.DepartureDate, rl.DepartureTime, r.Status FROM ReservationRoomLine rl WITH (NOLOCK) 
	  INNER JOIN RESERVATIONROOMLINEXTAB rlx (nolock) on rl.LINENUMBER = rlx.LINENUMBER AND @ReferenceNumber <> rlx.ReferenceNumber
	  INNER JOIN RESERVATIONS r (NOLOCK) ON rlx.ReferenceNumber = r.ReferenceNumber AND rlx.GuestNumber = r.GuestNumber AND
						r.Status<7 AND 
						NOT (r.Status in (5,6) AND 
						r.DepartureDate=@SystemDate)
	  WHERE RoomNumber=@ARoomNumberID and 
	  ((rl.ArrivalDate<@FormatedEndDate AND rl.DepartureDate>@FormatedStartDate) OR 
	  ((rl.ArrivalDate=@FormatedEndDate AND rl.ArrivalTime<=@TimeOfDepartureForRes) OR 
	  (rl.DepartureDate=@FormatedStartDate AND rl.DepartureTime>=@TimeOfArrivalForRes)))

  OPEN #cur
  FETCH NEXT FROM #cur
  INTO @DepartureDate, @DepartureTime, @Status

  WHILE  (@@FETCH_STATUS = 0)
  BEGIN
	IF @Status = 6
	BEGIN
	  IF (@DepartureDate > @FormatedStartDate) OR (@DepartureDate = @FormatedStartDate AND @DepartureTime > @TimeOfArrivalForRes)
	  BEGIN
		SET @FormatedStartDate = @DepartureDate
		SET @TimeOfArrivalForRes =  CAST(DATEADD(minute,1,CAST(@DepartureTime as TIME)) AS CHAR(5))
	  END
   	END ELSE
	  SET @ErrorLevel = 1

	FETCH NEXT FROM #cur
	INTO @DepartureDate, @DepartureTime, @Status
  END
  CLOSE #cur
  DEALLOCATE #cur

  IF @ErrorLevel>0
    RETURN  -- Room cannot be set out of order in period as it is booked
END
ELSE
  SET @ErrorLevel = 0
  
IF (@Note <> '') AND (@Note IS NOT NULL)
	SET @SetNote = 1
ELSE
	SET @SetNote = 0 
  
IF @SetNote = 1
BEGIN
 
  DECLARE @NoteNumber INT
  INSERT INTO InUseNote (Note) VALUES (@Note)
  SELECT @NoteNumber = @@identity

END



IF @ParentRoomNumber > 0
	INSERT INTO #TEMP
	SELECT RoomNumber 
	FROM Rooms (NOLOCK)
	WHERE ParentRoomNumber =  @ParentRoomNumber
ELSE
	INSERT INTO #TEMP
	VALUES(@ARoomNumberID)

DECLARE @SQL VARCHAR(MAX)

SET @SQL = 'DECLARE #RoomLineCur CURSOR STATIC FORWARD_ONLY FOR ' +
	'SELECT rl.LineNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime'
IF @ParentRoomNumber > 0
	SET @SQL += ', ro.RoomNumber '
ELSE 
	SET @SQL += ', rl.RoomNumber '
SET @SQL += 'FROM ReservationRoomLine rl WITH (NOLOCK) ' + 
	'INNER JOIN ReservationRoomLineXTab rlx WITH (NOLOCK) ON rl.LineNumber=rlx.LineNumber AND ReferenceNumber=' + CAST(@ReferenceNumber AS VARCHAR(10))
IF @ParentRoomNumber > 0
	SET @SQL += ' INNER JOIN Rooms ro (NOLOCK) ON rl.RoomNumber = ro.RoomNumber AND ParentRoomNumber = ' + CAST(@ParentRoomNumber  AS VARCHAR(10))
ELSE
    SET @SQL += ' AND RoomNumber=' + CAST(@ARoomNumberID  AS VARCHAR(10))
SET @SQL += ' WHERE CONVERT(DATETIME, ArrivalDate, 112)+CONVERT(DATETIME, ArrivalTime, 108) <= ''' + CAST(@EndDate as VARCHAR(20)) + ''' AND CONVERT(DATETIME, DepartureDate, 112)+CONVERT(DATETIME, DepartureTime, 108) >= ''' + CAST(@StartDate AS VARCHAR(20)) + ''''

/*
DECLARE #RoomLineCur CURSOR STATIC FORWARD_ONLY FOR
	SELECT ReservationRoomLine.LineNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime
	FROM ReservationRoomLine WITH (NOLOCK) 
	INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLine.LineNumber=ReservationRoomLineXTab.LineNumber AND ReferenceNumber=@ReferenceNumber AND RoomNumber=@RoomNumberID 
	--WHERE RoomNumber=@RoomNumberID AND ArrivalDate<=@FormatedEndDate AND DepartureDate>=@FormatedStartDate
	--WHERE (ArrivalDate<@FormatedEndDate AND DepartureDate>@FormatedStartDate) OR ((ArrivalDate=@FormatedEndDate AND ArrivalTime<=@TimeOfDepartureForRes) OR (DepartureDate=@FormatedStartDate AND DepartureTime>=@TimeOfArrivalForRes))
	WHERE CONVERT(DATETIME, ArrivalDate, 112)+CONVERT(DATETIME, ArrivalTime, 108) <= @EndDate AND CONVERT(DATETIME, DepartureDate, 112)+CONVERT(DATETIME, DepartureTime, 108) >= @StartDate
*/
EXEC(@SQL)
OPEN #RoomLineCur

FETCH NEXT FROM #RoomLineCur
INTO @LineNumber, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @RoomNumber


WHILE  (@@FETCH_STATUS <> -1)
BEGIN
	DELETE FROM #TEMP
	WHERE RoomNumber = @RoomNumber
	/* Scenario 2 - Our new period's arrival is within an existing one, departure is outside. then departuredate+time is set */
	--    IF @FormatedStartDate <= @DepartureDate AND @FormatedStartDate >= @ArrivalDate AND @FormatedEndDate > @DepartureDate  -- JP 20041027 no need to set it to the same value
	IF ((@FormatedStartDate < @DepartureDate) OR (@FormatedStartDate = @DepartureDate AND @TimeOfArrivalForRes <= @DepartureTime)) AND -- condition 1: new period is before end of existing one
		((@FormatedStartDate > @ArrivalDate) OR (@FormatedStartDate = @ArrivalDate AND @TimeOfArrivalForRes >= @ArrivalTime)) AND -- condition 2: new period is after start of existing one
		((@FormatedEndDate > @DepartureDate) OR (@FormatedEndDate = @DepartureDate AND @TimeOfDepartureForRes > @DepartureTime)) -- condition 3 new period is ending after end of existing period. NB. No need for >= @DepartureTime since it would be within period
	BEGIN -- we have to extend existing period to include our new period's enddate+time
	print '2: extend existing period at the end:'+convert(varchar, @LineNumber)+' '+@DepartureDate+' '+@DepartureTime
	--      UPDATE ReservationRoomLine SET DepartureDate=@FormatedEndDate WHERE LineNumber=@LineNumber
		UPDATE ReservationRoomLine 
		SET DepartureDate=@FormatedEndDate, DepartureTime=@TimeOfDepartureForRes 
		WHERE LineNumber=@LineNumber
      
		IF @SetNote = 1
			Update InUseNoteXTAB
			SET NoteNumber = @NoteNumber
			WHERE ObjectType = 0 AND ObjectNumber = @LineNumber
	END
/* Scenario 3 - Our new period's departure is within an existing one, arrival is outside. then arrivaldate+time is set */
--    ELSE IF @FormatedEndDate >= @ArrivalDate AND @FormatedEndDate <= @DepartureDate AND @FormatedStartDate < @ArrivalDate  -- JP 20041027 no need to set it to the same value
	ELSE IF ((@FormatedEndDate > @ArrivalDate) OR (@FormatedEndDate = @ArrivalDate AND @TimeOfDepartureForRes >= @ArrivalTime)) AND -- condition 1: new period is after start of existing one
                ((@FormatedEndDate < @DepartureDate) OR (@FormatedEndDate = @DepartureDate AND @TimeOfDepartureForRes <= @DepartureTime)) AND -- condition 2: new period is before end of existing one
                ((@FormatedStartDate < @ArrivalDate) OR (@FormatedStartDate = @ArrivalDate AND @TimeOfArrivalForRes < @ArrivalTime)) -- condition 3 new period is starting before start of existing period. NB. No need for <= @ArrivalTime since it would be within period
	BEGIN
	print '3: extend existing period at the start:'+convert(varchar, @LineNumber)+' '+@ArrivalDate+' '+@ArrivalTime
	--      UPDATE ReservationRoomLine SET ArrivalDate=@FormatedStartDate WHERE LineNumber=@LineNumber
		UPDATE ReservationRoomLine 
		SET ArrivalDate=@FormatedStartDate, 
			ArrivalTime=@TimeOfArrivalForRes 
		WHERE LineNumber=@LineNumber
	  
		IF @SetNote = 1
		Update InUseNoteXTAB
		SET NoteNumber = @NoteNumber
		WHERE ObjectType = 0 AND ObjectNumber = @LineNumber
	END
/* Scenario 4 - Our new period's arrival and departure are both outside an existing period. then update both arrival+departure is set */
--    ELSE IF @FormatedStartDate <= @ArrivalDate AND @FormatedEndDate >= @DepartureDate AND @DepartureDate <> @FormatedEndDate AND @ArrivalDate <> @FormatedStartDate  -- JP 20040414 no need to set it to the same value
	ELSE IF ((@FormatedStartDate < @ArrivalDate) OR (@FormatedStartDate = @ArrivalDate AND @TimeOfArrivalForRes < @ArrivalTime)) AND -- condition 1: arrival is before existing arrival
					((@FormatedEndDate > @DepartureDate) OR (@FormatedEndDate = @DepartureDate AND @TimeOfDepartureForRes > @DepartureTime)) -- condition 2: departure is after existing departure
	BEGIN
	print '4: extend existing period at both start and end since our period is overlapping existing:'+convert(varchar, @LineNumber)+' '+@ArrivalDate+' '+@ArrivalTime+' - '+@DepartureDate+' '+@DepartureTime
	--      UPDATE ReservationRoomLine SET ArrivalDate=@FormatedStartDate, DepartureDate=@FormatedEndDate WHERE LineNumber=@LineNumber
		UPDATE ReservationRoomLine 
		SET ArrivalDate=@FormatedStartDate, 
			ArrivalTime=@TimeOfArrivalForRes, 
			DepartureDate=@FormatedEndDate, 
			DepartureTime=@TimeOfDepartureForRes 
		WHERE LineNumber=@LineNumber
	  
		IF @SetNote = 1
		Update InUseNoteXTAB
		SET NoteNumber = @NoteNumber
		WHERE ObjectType = 0 AND ObjectNumber = @LineNumber
	END
	ELSE
	begin
	print '5: unhandled. might be sql which selected a row it should not!' -- might be a row with the exact same data!
	end

	FETCH NEXT FROM #RoomLineCur
	INTO @LineNumber, @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime, @RoomNumber
END  -- WHILE


CLOSE #RoomLineCur
DEALLOCATE #RoomLineCur

DECLARE @NewLineNumber INT
DECLARE #tmpCur CURSOR FOR
	SELECT RoomNumber FROM #TEMP

OPEN #tmpCur

FETCH NEXT FROM #tmpCur
INTO @RoomNumber
WHILE @@FETCH_STATUS = 0
BEGIN
	print '1: no roomline found that covers period'
  
  IF NOT EXISTS (SELECT rl.LINEnumber FROM RESERVATIONROOMLINE rl (NOLOCK)
				 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 status < 6
				 WHERE RoomNumber = @RoomNumber AND
				       NumberOfRooms = 1 AND
					   rl.ArrivalDate = @FormatedStartDate AND
					   rl.ArrivalTime = @TimeOfArrivalForRes AND
					   rl.DepartureDate = @FormatedEndDate AND
					   rl.DepartureTime = @TimeOfDepartureForRes)
  BEGIN
	EXEC GetNextNumber 'ReservationRoomLineIdentity', 1, @NewLineNumber OUTPUT

	INSERT INTO ReservationRoomLine (LineNumber, NumberOfRooms, RoomNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime) 
	VALUES (@NewLineNumber, 1, @RoomNumber, @FormatedStartDate, @TimeOfArrivalForRes, @FormatedEndDate, @TimeOfDepartureForRes)
	INSERT INTO ReservationRoomLineXTab (ReferenceNumber, GuestNumber, LineNumber)  VALUES (@ReferenceNumber, 0, @NewLineNumber)
	IF @SetNote = 1
		INSERT INTO InUseNoteXTAB (ObjectType, ObjectNumber, NoteNumber)
		VALUES (0, @NewLineNumber, @NoteNumber)
  END
  FETCH NEXT FROM #tmpCur
  INTO @RoomNumber
END

CLOSE #tmpcur
DEALLOCATE #tmpcur

DROP TABLE #TEMP

EXEC MergeOutOfOrderPeriods @ARoomNumberID

SET @ErrorLevel = 0

IF @ReferenceNumber = -1
  EXEC SetOutOfOrderRoomAllFuture @ARoomNumberID --, @FormatedStartDate, @FormatedEndDate

--SET lock_timeout 0

