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

    Last Updated:
   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 @OutForService = ISNULL(@OutForService, 0) -- AV 2008 1029

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

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

IF IsNull (@SetOwnedRoomsOutOfOrder, 0) = 0
  Set @SetOwnedRoomsOutOfOrder = 0
  
IF (IsNull (@OFSBehaveLIKEOOO, 0) = 0) OR @NotSaleable = 1
  Set @OFSBehaveLIKEOOO = 0
  


DECLARE @LineNumber INT,
@ReferenceNumber INT,
@RoomOwned INT
SET @ReferenceNumber = 0
SET @RoomOwned = 0
IF @Skallerup = 1 AND @SetOwnedRoomsOutOfOrder = 1
BEGIN
  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
    SET @ReferenceNumber = -1
END
IF @OutForService = 1
  SET @ReferenceNumber = -2
  
IF @NotSaleable = 1
	SET @ReferenceNumber = -3

IF @Skallerup=1 AND @OutForService=0 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 = @RoomNumberID

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

IF @OutForService = 0 or (@OutForService = 1 and @OFSBehaveLikeOOO = 1)
BEGIN
  /* Do not convert this SQL to using joins over inner/sub selects, because it will find namelist res once per guest. instead of just once */
  SELECT @ErrorLevel=COUNT (*) FROM ReservationRoomLine WITH (NOLOCK) 
  WHERE RoomNumber=@RoomNumberID and 
  ((ArrivalDate<@FormatedEndDate AND DepartureDate>@FormatedStartDate) OR 
  ((ArrivalDate=@FormatedEndDate AND ArrivalTime<=@TimeOfDepartureForRes) OR 
  (DepartureDate=@FormatedStartDate AND DepartureTime>=@TimeOfArrivalForRes)))
  AND LineNumber IN (SELECT LineNumber FROM ReservationRoomLineXTab WITH (NOLOCK) 
					INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber=ReservationRoomLineXTab.ReferenceNumber AND Reservations.GuestNumber=ReservationRoomLineXTab.GuestNumber 
					WHERE (ReservationRoomLineXTab.ReferenceNumber>0 OR @OFSBehaveLikeOOO = 1 OR (@NotSaleable = 0 AND ReservationRoomLineXTab.ReferenceNumber = -3)) AND  
					Reservations.Status<7 AND 
					NOT (Reservations.Status in (5,6) AND 
					DepartureDate=(SELECT SystemDate FROM System WITH (NOLOCK) WHERE Number=1)))

  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

DECLARE #RoomLineCur CURSOR STATIC FORWARD_ONLY FOR
/*
SELECT LineNumber, ArrivalDate, DepartureDate FROM ReservationRoomLine (NOLOCK) WHERE RoomNumber=@RoomNumberID AND ArrivalDate<=@FormatedEndDate AND DepartureDate>=@FormatedStartDate
AND LineNumber IN (SELECT LineNumber FROM ReservationRoomLineXTab (NOLOCK) WHERE ReferenceNumber=@ReferenceNumber)
*/ -- JP 20050106 faster than subselect to use inner join
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 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

OPEN #RoomLineCur

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

IF @@FETCH_STATUS = -1  -- No outoforder/outforservice roomlines covers any part of the period. We need to create new roomline
BEGIN
print '1: no roomline found that covers period'
  DECLARE @NewLineNumber INT
/*
  BEGIN TRAN
    UPDATE ReservationRoomLineIdentity SET @NewLineNumber = IdentityField = IdentityField + 1  -- New LineNumber
  COMMIT TRAN
*/
  EXEC GetNextNumber 'ReservationRoomLineIdentity', 1, @NewLineNumber OUTPUT

  INSERT INTO ReservationRoomLine (LineNumber, NumberOfRooms, RoomNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime) VALUES (@NewLineNumber, 1, @RoomNumberID, @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)
--print @referencenumber
--print @newlinenumber
END
ELSE  -- outoforder/outforservice roomline(s) found
BEGIN
  WHILE  (@@FETCH_STATUS <> -1)
  BEGIN
    /* 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
      BREAK
    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
      BREAK
    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
      BREAK
    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
  END  -- WHILE
END

CLOSE #RoomLineCur
DEALLOCATE #RoomLineCur

EXEC MergeOutOfOrderPeriods @RoomNumberID

SET @ErrorLevel = 0

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

--SET lock_timeout 0

