
CREATE PROCEDURE DBO.SetInOrderRoom @RoomNumberID INT, @StartDate DATETIME, @EndDate DATETIME, @Skallerup BIT = 0, @OutForService BIT = 0, @NotSaleable BIT = 0  AS
SET NOCOUNT ON
/*
    Created By: Jacob Pedersen 24/6-2002

    Last Updated:	
    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 @Skallerup = 1 AND @SetOwnedRoomsOutOfOrder = 1
BEGIN
  DECLARE @RoomOwned INT
  DECLARE @PeriodiclyOutOfOrder BIT
  SELECT @RoomOwned = CustomerNumber, @PeriodiclyOutOfOrder = PeriodiclyOutOfOrder 
  FROM Rooms WITH (NOLOCK) WHERE RoomNumber=@RoomNumberID
  
  IF IsNull (@PeriodiclyOutOfOrder, 0) = 0
    SET @PeriodiclyOutOfOrder = 0

  IF (@PeriodiclyOutOfOrder > 0) AND (@RoomOwned > 0)
     SET @ReferenceNumber = -1
END

IF @OutForService = 1
  SET @ReferenceNumber = -2
  
IF @NotSaleable = 1
  SET @ReferenceNumber = -3

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

DECLARE #RoomLineCur CURSOR FORWARD_ONLY FOR

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

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
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 -- Is time needed here?
BEGIN
  DECLARE @InuseStatus SMALLINT
  IF @OutForService = 0
  BEGIN
    SELECT @InuseStatus=CONVERT (SMALLINT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE Upper (SearchKey)=Upper('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=@RoomNumberID
END

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

