CREATE PROCEDURE dbo.SetOutOfOrderRoomAllFuture @TheRoomNumberID INT, @ToDate CHAR(8) = '', @FromDate CHAR(8) = '' AS
SET NOCOUNT ON
/*
   DEPENDENCIES: UpdateArrivalAndDeparture

    Special OutOfOrder routine for Skallerup Klit Feriecenter.

    Sets all free periods outoforder for specified room until a given date

    Changed:
    AV 20080322 FIX for locktimeout in the Truncate ooo up till today (use the key to update instead of inner selects)
    20060523 JP BUG: fix for overlapping OOO with bookings
    JP 20050526: changed to use GetNextNumber SP
    JP 2004-04-20 Disabled fast_forward to avoid re-selection of SQL.   Fix for out-for-service
    JP 2004-02-10 Check for SetOwnedRoomsOutOfOrder setting
    JP 2003-11-20 Handle OOO before today so it does not get deleted!
    JP 2003-11-13 Delete OOO on res 0 if PeriodiclyOOO=1 but no periods defined
    JP 2003-11-12 Check for new Skallerup OOO periods from Res -1
    JP 2003-11-06 If no bookings found it did not set rum outoforder in period
    JP 2003-09-17 Fixed Cursor name to not conflict with UpdateArrivalAndDeparture
    JP 2003-09-08 Created

    NOTE: * ToDate limitation has not been implemented

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

print 'SetOutOfOrderRoomAllFuture start'

SET @ToDate = ''  -- *

DECLARE @NewLineNumber INT

SELECT @NewLineNumber=CustomerNumber FROM Rooms WITH (NOLOCK) WHERE RoomNumber=@TheRoomNumberID
IF (@NewLineNumber = 0) OR (@NewLineNumber IS NULL) OR (@TheRoomNumberID = 0)
  RETURN

DECLARE @TimeOfArrivalForRes CHAR (5), --TimeOfArrivalForRes '18:00'
@TimeOfDepartureForRes CHAR (5), --TimeOfDepartureForRes '12:00'
@SystemDate CHAR (8)

SELECT @TimeOfArrivalForRes=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE UPPER (SearchKey) = UPPER ('TimeOfArrivalForRes')
SELECT @TimeOfDepartureForRes=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE UPPER (SearchKey) = UPPER ('TimeOfDepartureForRes')
SELECT @SystemDate = SYSTEMDATE FROM System  WITH (NOLOCK) WHERE NUMBER = 1  -- Only one record

IF (@FromDate = '') OR (@FromDate IS NULL)
  SET @FromDate = @SystemDate

IF @ToDate = ''
  SET @ToDate = '20991231'

DECLARE @LineNumber INT
DECLARE #fuckedup CURSOR /*FAST_FORWARD*/ FOR

  select reservationroomlinextab.linenumber from reservationroomlinextab WITH (nolock) 
  inner join ReservationRoomLine WITH (NOLOCK) 
	on reservationroomlinextab.LineNumber=ReservationRoomLine.LineNumber
  where referencenumber=0 and ArrivalDate <=@ToDate and 
		departuredate>@FromDate and 
		ArrivalDate >= @SystemDate and 
		RoomNumber = @TheRoomNumberID

OPEN #fuckedup

FETCH NEXT FROM #fuckedup
INTO @LineNumber

WHILE  (@@FETCH_STATUS <> -1)
BEGIN
  delete from reservationroomlinextab with (ROWLOCK) where linenumber = @LineNumber 
  delete from reservationroomline with (ROWLOCK) where linenumber = @LineNumber
  DELETE FROM InUseNoteXTab 
  WHERE ObjectType = 0 AND
		ObjectNumber = @LineNumber   

  FETCH NEXT FROM #fuckedup
  INTO @LineNumber
END

CLOSE #fuckedup
DEALLOCATE #fuckedup

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Truncate OOO up till today
/*
UPDATE ReservationRoomLine SET DepartureDate=@SystemDate, DepartureTime=@TimeOfDepartureForRes WHERE LineNumber IN (select reservationroomlinextab.linenumber from reservationroomlinextab (nolock) inner join ReservationRoomLine (NOLOCK) on reservationroomlinextab.LineNumber=ReservationRoomLine.LineNumber
  WHERE referencenumber=0 and RoomNumber=@TheRoomNumberID and ArrivalDate<=@SystemDate and DepartureDate>=@SystemDate)
*/ --removed AV 20080322

--AV 20080322 update with the key to avoid locktieout
DECLARE @LineNumber2 INT
DECLARE #Truncate CURSOR LOCAL FAST_FORWARD FOR
SELECT ReservationRoomLine.LineNumber FROM ReservationRoomLine WITH (NOLOCK)
WHERE LineNumber IN (select reservationroomlinextab.linenumber from reservationroomlinextab WITH (nolock) 
inner join ReservationRoomLine WITH (NOLOCK) on reservationroomlinextab.LineNumber=ReservationRoomLine.LineNumber
  WHERE referencenumber=0 and RoomNumber=@TheRoomNumberID and ArrivalDate<=@SystemDate and DepartureDate>=@SystemDate)
OPEN #Truncate

FETCH NEXT FROM #Truncate
INTO @LineNumber2

WHILE  (@@FETCH_STATUS <> -1)
BEGIN
  UPDATE ReservationRoomLine SET DepartureDate=@SystemDate, DepartureTime=@TimeOfDepartureForRes 
  WHERE LineNumber = @LineNumber2
  FETCH NEXT FROM #Truncate
  INTO @LineNumber2
END

CLOSE #Truncate
DEALLOCATE #Truncate
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- SELECT ALL BOOKINGS FOR ROOMNO AND SET FREE PERIODS OUTOFORDER
DECLARE @OOOArrivalDate CHAR(8)
DECLARE @OOODepartureDate CHAR(8)
DECLARE @OOOArrivalTime CHAR(5)
DECLARE @OOODepartureTime CHAR(5)

DECLARE @ArrivalDate CHAR(8)
DECLARE @DepartureDate CHAR(8)
DECLARE @ArrivalTime CHAR(5)
DECLARE @DepartureTime CHAR(5)

DECLARE @OPArrivalDate CHAR(8)
DECLARE @OPDepartureDate CHAR(8)

DECLARE #OOOPeriods CURSOR STATIC FOR

  SELECT ArrivalDate, DepartureDate FROM ReservationRoomLine WITH (NOLOCK) INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReferenceNumber=-1 AND RoomNumber=@TheRoomNumberID AND ReservationRoomLineXTab.LineNumber=ReservationRoomLine.LineNumber AND DepartureDate>=@SystemDate

OPEN #OOOPeriods
FETCH NEXT FROM #OOOPeriods
INTO @OPArrivalDate, @OPDepartureDate

IF (@@FETCH_STATUS = -1)
BEGIN
  DECLARE @PeriodiclyOutOfOrder BIT
  SELECT @PeriodiclyOutOfOrder=PeriodiclyOutOfOrder FROM Rooms WITH (NOLOCK) WHERE RoomNumber=@TheRoomNumberID
  IF (@PeriodiclyOutOfOrder=1)
  BEGIN
    CLOSE #OOOPeriods
    DEALLOCATE #OOOPeriods
    RETURN
  END
END

DECLARE @DontExit BIT -- used for making sure loop is run once if no periods
SET @DontExit = 1

WHILE  (@@FETCH_STATUS <> -1) OR (@DontExit = 1)
BEGIN
  IF @@FETCH_STATUS <> -1
  BEGIN
    SET @FromDate = @OPArrivalDate
    SET @ToDate = @OPDepartureDate
  END

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE #RoomLineCur2 CURSOR FAST_FORWARD FOR

select ReservationRoomLine.ArrivalDate, ReservationRoomLine.ArrivalTime, ReservationRoomLine.DepartureDate, 
ReservationRoomLine.DepartureTime 
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
where roomnumber=@TheRoomNumberID and 
	  ReservationRoomLine.DepartureDate > @FromDate and 
	  ReservationRoomLine.ArrivalDate <= @ToDate AND 
	  Reservations.Status < 7 AND 
	  Reservations.ReferenceNumber<>-2
order by ReservationRoomLine.arrivaldate, ReservationRoomLine.DepartureDate

OPEN #RoomLineCur2

FETCH NEXT FROM #RoomLineCur2
INTO @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime

IF @@FETCH_STATUS = -1  -- No reservations found!
BEGIN
  SET @OOOArrivalDate = @FromDate
  SET @OOOArrivalTime = @TimeOfArrivalForRes
  SET @OOODepartureDate = @ToDate
  SET @OOODepartureTime = @TimeOfDepartureForRes

  EXEC GetNextNumber 'ReservationRoomLineIdentity', 1, @NewLineNumber OUTPUT

  INSERT INTO ReservationRoomLine (LineNumber, NumberOfRooms, RoomNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime) VALUES (@NewLineNumber, 1, @TheRoomNumberID, @OOOArrivalDate, @OOOArrivalTime, @OOODepartureDate, @OOODepartureTime)
  INSERT INTO ReservationRoomLineXTab (ReferenceNumber, GuestNumber, LineNumber)  VALUES (0, 0, @NewLineNumber)
END
ELSE IF @ArrivalDate > @FromDate  -- Res found
BEGIN
  SET @OOOArrivalDate = @FromDate
  SET @OOOArrivalTime = @TimeOfArrivalForRes
  SET @OOODepartureDate = @ArrivalDate
  IF @ArrivalTime < @TimeOfDepartureForRes
    SET @OOODepartureTime = CONVERT(CHAR(5), DATEADD(MINUTE, -1, CONVERT(SMALLDATETIME, @ArrivalTime, 108)), 108)
  ELSE
    SET @OOODepartureTime = @TimeOfDepartureForRes

  EXEC GetNextNumber 'ReservationRoomLineIdentity', 1, @NewLineNumber OUTPUT

  INSERT INTO ReservationRoomLine (LineNumber, NumberOfRooms, RoomNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime) VALUES (@NewLineNumber, 1, @TheRoomNumberID, @OOOArrivalDate, @OOOArrivalTime, @OOODepartureDate, @OOODepartureTime)
  INSERT INTO ReservationRoomLineXTab (ReferenceNumber, GuestNumber, LineNumber)  VALUES (0, 0, @NewLineNumber)
END
ELSE
BEGIN
  print 'hmmm'
print @ArrivalDate
print @ArrivalTime
print @DepartureDate
print @DepartureTime
END

WHILE  (@@FETCH_STATUS <> -1)
BEGIN
  SET @OOOArrivalDate = @DepartureDate
  IF @DepartureTime < @TimeOfDepartureForRes
    SET @OOOArrivalTime = @TimeOfDepartureForRes
  ELSE
    SET @OOOArrivalTime = CONVERT(CHAR(5), DATEADD(MINUTE, +1, CONVERT(SMALLDATETIME, @DepartureTime, 108)), 108)

  FETCH NEXT FROM #RoomLineCur2
  INTO @ArrivalDate, @ArrivalTime, @DepartureDate, @DepartureTime

  IF @@FETCH_STATUS <> -1 -- An other resline was found
  BEGIN
    SET @OOODepartureDate = @ArrivalDate
    IF @ArrivalTime > @TimeOfArrivalForRes
      SET @OOODepartureTime = @TimeOfArrivalForRes
    ELSE
      SET @OOODepartureTime = CONVERT(CHAR(5), DATEADD(MINUTE, -1, CONVERT(SMALLDATETIME, @ArrivalTime, 108)), 108)
  END
  ELSE --IF @ToDate = '20991231' -- No res line was found
  BEGIN
    SET @OOODepartureDate = @ToDate
    SET @OOODepartureTime = @TimeOfArrivalForRes
  END
/*  ELSE -- make it skip!  -- No res line was found
  BEGIN
    SET @OOODepartureDate = @OOOArrivalDate
    SET @OOODepartureTime = @OOOArrivalTime
  END */

  -- check if ooo period is ok and not minus time/days
  IF (@OOODepartureDate > @OOOArrivalDate) OR ((@OOODepartureDate = @OOOArrivalDate) AND (@OOODepartureTime > @OOOArrivalTime))
  BEGIN
    -- INSERT NEW OOO Period
    EXEC GetNextNumber 'ReservationRoomLineIdentity', 1, @NewLineNumber OUTPUT

    INSERT INTO ReservationRoomLine (LineNumber, NumberOfRooms, RoomNumber, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime) VALUES (@NewLineNumber, 1, @TheRoomNumberID, @OOOArrivalDate, @OOOArrivalTime, @OOODepartureDate, @OOODepartureTime)
    INSERT INTO ReservationRoomLineXTab (ReferenceNumber, GuestNumber, LineNumber)  VALUES (0, 0, @NewLineNumber)
  END
END

CLOSE #RoomLineCur2
DEALLOCATE #RoomLineCur2

  FETCH NEXT FROM #OOOPeriods
  INTO @OPArrivalDate, @OPDepartureDate
  SET @DontExit = 0 -- Now only depend on FETCH_STATUS since we have made our first loop!
END

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CLOSE #OOOPeriods
DEALLOCATE #OOOPeriods
