CREATE PROCEDURE DBO.SetInOrderRoom @ARoomNumberID INT, @StartDate DATETIME, @EndDate DATETIME, @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
    JP 20050518 Use GetNextNumber instead of directly updating table
    JP 20050107 use period times from parameters instead of hotelsetup. Huge rewrite
    JP 20050103 delete Notes
    JP 20040908 Fix cleanstatus for outforservice
    JP 20040405 Out-for-Service on res -2
		Jacob Pedersen	10/02-2004 Check for SetOwnedRoomsOutOfOrder setting
		Jacob Pedersen 11/11-2003 RoomOwned stuff for Skallerup. Store on res -1
		Jacob Pedersen 08/04-2003 fix for multible rows
		Jacob Pedersen 29/01-2003 fix for skallerup
		Jacob Pedersen 02/01-2003 uses inuse status from hotelsetup
		Jacob Pedersen 09/10-2002 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
*/

-- Pseudocode:
-- check if any res has departure > startdate and arrivaldate <startdate , if so change departuredate to startdate. if arrivaldate > departuredate then delete row

-- check if any res has arrivaldate < enddate and departuredate>enddate, if so change arrivaldate to enddate. if departuredate < arrivaldate then delete row

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

SET @StartDate = DATEADD(MINUTE, -1, @StartDate)
SET @EndDate = DATEADD(MINUTE, 1, @EndDate)

DECLARE @SetOwnedRoomsOutOfOrder BIT
SELECT @SetOwnedRoomsOutOfOrder=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE UPPER (SearchKey) = UPPER ('SetOwnedRoomsOutOfOrder')
IF IsNull (@SetOwnedRoomsOutOfOrder, 0) = 0
  Set @SetOwnedRoomsOutOfOrder = 0

DECLARE @ReferenceNumber INT
SET @ReferenceNumber = 0
IF @OutOfOrderMode = 4 AND @SetOwnedRoomsOutOfOrder = 1
BEGIN
  DECLARE @RoomOwned INT
  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) AND (@RoomOwned > 0)
     SET @ReferenceNumber = -1
END

IF @OutOfOrderMode = 1
  SET @ReferenceNumber = -2
  
IF @OutOfOrderMode = 2
  SET @ReferenceNumber = -3

IF @OutOfOrderMode = 3
  SET @ReferenceNumber = -4

DECLARE @LineNumber INT,
@NoteNumber INT,
@ArrivalDate CHAR (8),
@ArrivalTime CHAR (5),
@DepartureDate CHAR (8),
@DepartureTime CHAR (5),
@FormatedStartDate CHAR (8),
@FormatedEndDate CHAR (8),
@TimeOfArrivalForRes CHAR (5), --TimeOfArrivalForRes '18:00'
@TimeOfDepartureForRes CHAR (5), --TimeOfDepartureForRes '12:00'
@RoomNumberID INT,
@SQL VARCHAR(8000)
SET @FormatedStartDate = CONVERT (CHAR (8), @StartDate, 112)
SET @FormatedEndDate = CONVERT (CHAR (8), @EndDate, 112)
SET @TimeOfArrivalForRes = CONVERT (CHAR (5), @StartDate, 108)
SET @TimeOfDepartureForRes = CONVERT (CHAR (5), @EndDate, 108)

SET @SQL = 'DECLARE #RoomLineCur CURSOR FORWARD_ONLY FOR ' +

'SELECT rl.LineNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime ' 

IF @ParentRoomNumber > 0 
	SET @SQL = @SQL + ', ro.RoomNumber '
ELSE 
	SET @SQL = @SQL + ', ' + CAST(@ARoomNumberID AS VARCHAR(10)) + ' '
SET @SQL = @SQL + 'FROM ReservationRoomLine rl WITH (NOLOCK)' +
		   'INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON '
IF @ReferenceNumber = -3 OR @ReferenceNumber = -4
	SET @SQL = @SQL + 'ReferenceNumber IN (-3, -4) '
ELSE
	SET @SQL = @SQL + 'ReferenceNumber = ' + CAST(@ReferenceNumber AS VARCHAR(10)) + ' '

SET @SQL = @SQL + ' AND rl.LineNumber=ReservationRoomLineXTab.LineNumber '
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 ' +
				  '((ArrivalDate < ''' + @FormatedEndDate + ''')  OR ' +
				  '(ArrivalDate = ''' + @FormatedEndDate + ''' AND ArrivalTime <= ''' + @TimeOfDepartureForRes + ''')) AND ' +
				  '((DepartureDate > ''' + @FormatedStartDate + ''') OR ' + 
				  '(DepartureDate = ''' + @FormatedStartDate + ''' AND DepartureTime >= ''' + @TimeOfArrivalForRes + '''))'
				    
	

Print @SQL
EXEC (@SQL)

/*
SELECT ReservationRoomLine.LineNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime 
FROM ReservationRoomLine WITH (NOLOCK) 
INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReferenceNumber=@ReferenceNumber AND RoomNumber=@RoomNumberID 
	AND ReservationRoomLine.LineNumber=ReservationRoomLineXTab.LineNumber
WHERE
((ArrivalDate < @FormatedEndDate) OR (ArrivalDate = @FormatedEndDate AND ArrivalTime <= @TimeOfDepartureForRes)) AND -- condition 1:
((DepartureDate > @FormatedStartDate) OR (DepartureDate = @FormatedStartDate AND DepartureTime >= @TimeOfArrivalForRes)) -- condition 2:
*/

OPEN #RoomLineCur

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

WHILE  (@@FETCH_STATUS <> -1)
BEGIN
print 'ad:'+@arrivaldate+' '+@arrivaltime+' - '+ @departuredate+' '+@departuretime
print 'fse:'+@FormatedStartDate+' '+@TimeOfArrivalForRes+' - '+@FormatedEndDate+' '+@TimeOfDepartureForRes

  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 'in4: extend existing period at both start and end since our period is overlapping existing:'+convert(varchar, @LineNumber)+' '+@ArrivalDate+' '+@ArrivalTime+' - '+@DepartureDate+' '+@DepartureTime
    DELETE FROM ReservationRoomLineXTab WHERE LineNumber=@LineNumber
    DELETE FROM ReservationRoomLine WHERE LineNumber=@LineNumber
    DELETE FROM	InUseNoteXTab WHERE ObjectType = 0 AND ObjectNumber = @LineNumber
    -- NO BREAK! because there can be more than one lines in the period!
  END
  ELSE IF (@FormatedStartDate > @ArrivalDate OR (@FormatedStartDate = @ArrivalDate AND @TimeOfArrivalForRes >= @ArrivalTime)) AND
                (@FormatedEndDate < @DepartureDate OR (@FormatedEndDate = @DepartureDate AND @TimeOfDepartureForRes <= @DepartureTime))
  BEGIN
print 'in3: setting inorder within an existing outoforder. splitting existing in two. shorten one and create an other: '+convert(varchar, @LineNumber)+' '+@ArrivalDate+' '+@ArrivalTime+' - '+@DepartureDate+' '+@DepartureTime
    -- Shorten existing Roomline
    UPDATE ReservationRoomLine 
    SET DepartureDate=@FormatedStartDate, 
		DepartureTime=@TimeOfArrivalForRes 
	WHERE LineNumber=@LineNumber

    -- Create new roomline with remaining period
    SELECT @NoteNumber = ISNULL(NoteNumber,0) 
    FROM InUseNoteXTab WITH (NOLOCK)
    WHERE ObjectType = 0 AND
		  ObjectNumber = @LineNumber 
		   
    DECLARE @NewLineNumber INT

    EXEC GetNextNumber 'ReservationRoomLineIdentity', 1, @NewLineNumber OUTPUT

    INSERT INTO ReservationRoomLine (LineNumber, NumberOfRooms, RoomNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime)
    VALUES (@NewLineNumber, 1, @RoomNumberID, @FormatedEndDate, @TimeOfDepartureForRes, @DepartureDate, @DepartureTime)

    INSERT INTO ReservationRoomLineXTab (ReferenceNumber, GuestNumber, LineNumber)
    VALUES (@ReferenceNumber, 0, @NewLineNumber)
	
	IF @NoteNumber > 0
		INSERT INTO InUseNoteXTab (ObjectType, ObjectNumber, NoteNumber)
		VALUES (0, @NewLineNumber, @NoteNumber)  

   -- BREAK -- Break is OK. There should not be any other, if MergeOOO SP has been called from SetOutOfOrder SP
  END
  ELSE 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
print 'in1: shortening existing period at the end:'+convert(varchar, @LineNumber)+' '+@ArrivalDate+' '+@ArrivalTime+' - '+@DepartureDate+' '+@DepartureTime
    UPDATE ReservationRoomLine 
    SET DepartureDate=@FormatedStartDate, 
		DepartureTime=@TimeOfArrivalForRes 
	WHERE LineNumber=@LineNumber
--    BREAK   do not break. there might be more!
  END
  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 'in2: shortening existing period at the start:'+convert(varchar, @LineNumber)+' '+@ArrivalDate+' '+@ArrivalTime+' - '+@DepartureDate+' '+@DepartureTime
    UPDATE ReservationRoomLine 
    SET ArrivalDate=@FormatedEndDate, 
		ArrivalTime=@TimeOfDepartureForRes 
	WHERE LineNumber=@LineNumber
--    BREAK   do not break. there might be more!
  END

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

CLOSE #RoomLineCur
DEALLOCATE #RoomLineCur

---- CLEAN STATUS ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Set cleanstatus if today is within period
DECLARE @SystemDateStr CHAR (8)
SELECT @SystemDateStr = SYSTEMDATE FROM System  WITH (NOLOCK) WHERE NUMBER = 1  -- Only one record

IF @FormatedStartDate <= @SystemDateStr AND @FormatedEndDate >= @SystemDateStr AND @ARoomNumberID > 0-- Is time needed here?
BEGIN
  DECLARE @InuseStatus SMALLINT
  IF @OutOfOrderMode <> 1
  BEGIN
    SELECT @InuseStatus=CONVERT (SMALLINT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='InuseStatusRoom'    --  TCleaningStatus = (csClean, // 0     csDirty, // 1
    if IsNull (@InuseStatus, 0) = 0
      Set @InuseStatus = 0
  END
  ELSE
  BEGIN
    SELECT @InuseStatus=CONVERT (SMALLINT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey)=Upper('InserviceStatusRoom')    --  TCleaningStatus = (csClean, // 0     csDirty, // 1 ... // Dont change=5
    if IsNull (@InuseStatus, 5) = 5
      Set @InuseStatus = 5
  END

  IF @InuseStatus < 5
    UPDATE Rooms SET CleanStatus=@InuseStatus WHERE RoomNumber=@ARoomNumberID
END

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

