CREATE PROCEDURE dbo.GenerateAvailabilityTime @Arrival DATETIME, @Departure DATETIME, @HotelNumber INT = -1 AS
SET NOCOUNT ON
/*
  Created 2007-04-30 by Jacob Pedersen

  Changed:
  2012-04-03: EXT: KC: Out for service added.
  2008-12-18: BUG: KJ: OnlineTimeBookingGab wrong 
  2007-05-16 EXT: JP: use reservation period and write data for departure day v3
  2007-05-01 BUG: JP: fix for missing hotelsettings resulting in null values
  2007-05-01 EXT: JP: added Gap handling

  TODO: 
  * NumberOfRoomsSoldToday, SoldPrice
  * DayRoom on departure date is not supported. See note in code
*/

CREATE TABLE #ROOMBOOKINGSTATUS2 (
  	[DateStamp] [char] (8) COLLATE DATABASE_DEFAULT NOT NULL ,
	[RoomTypeNumber] [int] NOT NULL ,
	[HotelNumber] [int] NOT NULL ,
	[SoldRooms] [smallint] NULL ,
	[Adults] [smallint] NULL ,
	[Children1] [smallint] NULL ,
	[Children2] [smallint] NULL ,
	[NumberOfConfirmed] [smallint] NULL ,
	[NumberOfProvisionals] [smallint] NULL ,
	[NumberOfGuaranteed] [smallint] NULL ,
	[NumberOfTentative] [smallint] NULL ,
	[NumberOfWaitinglists] [smallint] NULL ,
	[NumberOfDayrooms] [smallint] NULL ,
	[FreeRoomsAllotment] [smallint] NULL ,
	[TotalRoomsAllotment] [smallint] NULL ,
	[PaxAllotment] [smallint] NULL,
	[NumberOfRoomsSoldToday] [smallint] NULL,
	[NumberOfOutOfOrder] [smallint] NULL,
	[NumberOfOutForService] [smallint] NULL,
	[SoldPrice] [MONEY] NULL
	)

DECLARE 
@AllotmentNumber INT,
@NumberOfRooms INT,
@BookedTypeNumber INT,
@RoomTypeNumber INT,
@Adults INT,
@Children1 INT,
@Children2 INT,
@CurrentDate DATETIME,
@AllotNumberOfRooms INT,
@AllotPax INT,
@Status TINYINT,
@NumberOfConfirmed INT,
@NumberOfProvisionals INT,
@NumberOfGuaranteed INT,
@NumberOfTentative INT,
@NumberOfWaitinglist INT,
@NumberOfOutOfOrder INT,
@NumberOfOutForService INT,
@IsDayRoom BIT,
@IncludeTentative BIT,
@VacantRoomsInclAllotment BIT,
@IncludeDayroomsInSoldRooms BIT,
@OnlineTimeBookingGap DATETIME,
@ResArrival DATETIME,
@ResDeparture DATETIME

SELECT @VacantRoomsInclAllotment=ISNULL(KeyValue, 0) FROM dbo.HotelSetup WITH (NOLOCK) WHERE SearchKey = 'VacantRoomsInclAllotment'
IF @VacantRoomsInclAllotment = NULL
  SET @VacantRoomsInclAllotment = 0
SELECT @IncludeTentative=ISNULL(KeyValue, 0) FROM dbo.HotelSetup WITH (NOLOCK) WHERE SearchKey = 'IncludeTentative'
IF @IncludeTentative = NULL
  SET @IncludeTentative = 0
SELECT @IncludeDayroomsInSoldRooms=ISNULL(KeyValue, 0) FROM dbo.HotelSetup WITH (NOLOCK) WHERE SearchKey = 'IncludeDayroomsInSoldRooms'
IF @IncludeDayroomsInSoldRooms = NULL
  SET @IncludeDayroomsInSoldRooms = 0
SELECT @OnlineTimeBookingGap=CONVERT(DATETIME,ISNULL(KeyValue, '01:00'),108) FROM dbo.HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OnlineTimeBookingGap'
--IF @OnlineTimeBookingGap = NULL
IF ISNULL(@OnlineTimeBookingGap, '') = ''
  SET @OnlineTimeBookingGap = '01:00'

SET @Arrival = @Arrival - @OnlineTimeBookingGap
SET @Departure = @Departure + @OnlineTimeBookingGap

-- B O O K I N G S T A T U S ----------------------------------------------------------------------------------------------------------------

DECLARE #ConfirmedCur CURSOR LOCAL FAST_FORWARD FOR

SELECT Status,CASE WHEN RLX.ReferenceNumber = 0 THEN 0 ELSE NumberOfRooms END AS NumberOfRooms,RL.BookedTypeNumber,RM.RoomTypeNumber,RL.Adults,RL.Children1,RL.Children2,ReservationMain.AllotmentNumber,
CONVERT(DATETIME, RL.ArrivalDate, 112)+CONVERT(DATETIME, RL.ArrivalTime, 108) AS Arrival,
CONVERT(DATETIME, RL.DepartureDate, 112)+CONVERT(DATETIME, RL.DepartureTime, 108) AS Departure,
CASE WHEN Status = 0 THEN (CASE WHEN RLX.ReferenceNumber = 0 THEN 0 ELSE NumberOfRooms END) ELSE 0 END AS NumberOfConfirmed,
CASE WHEN Status = 1 THEN (CASE WHEN RLX.ReferenceNumber = 0 THEN 0 ELSE NumberOfRooms END) ELSE 0 END AS NumberOfProvisionals,
CASE WHEN Status = 2 THEN (CASE WHEN RLX.ReferenceNumber = 0 THEN 0 ELSE NumberOfRooms END) ELSE 0 END AS NumberOfGuaranteed,
CASE WHEN Status = 3 THEN (CASE WHEN RLX.ReferenceNumber = 0 THEN 0 ELSE NumberOfRooms END) ELSE 0 END AS NumberOfWaitinglist,
CASE WHEN Status = 4 THEN (CASE WHEN RLX.ReferenceNumber = 0 THEN 0 ELSE NumberOfRooms END) ELSE 0 END AS NumberOfTentative,
CASE WHEN RLX.ReferenceNumber = 0 THEN NumberOfRooms ELSE 0 END AS NumberOfOutOfOrder,
CASE WHEN RLX.ReferenceNumber = -2 THEN NumberOfRooms ELSE 0 END AS NumberOfOutForService,
dbo.IsDayRoom(RL.ArrivalDate, RL.ArrivalTime, RL.DepartureDate, RL.DepartureTime, '') -- TODO: how to determin if dayroom on departure? probably only possible within loop
FROM dbo.ReservationRoomLine RL WITH (NOLOCK) 
INNER JOIN dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber=RLX.LineNumber
AND CONVERT(DATETIME, ArrivalDate, 112)+CONVERT(DATETIME, ArrivalTime, 108)<@Departure
AND CONVERT(DATETIME, DepartureDate, 112)+CONVERT(DATETIME, DepartureTime, 108)>=@Arrival
INNER JOIN dbo.Reservations R WITH (NOLOCK) ON RLX.Referencenumber=R.ReferenceNumber AND RLX.GuestNumber=R.GuestNumber AND Status < 7
INNER JOIN dbo.ReservationMain WITH (NOLOCK) ON dbo.ReservationMain.ReferenceNumber=R.ReferenceNumber
LEFT OUTER JOIN dbo.Rooms RM WITH (NOLOCK) ON RM.RoomNumber=RL.RoomNumber
LEFT OUTER JOIN dbo.RoomTypes RT WITH (NOLOCK) ON RT.RoomTypeNumber=RL.BookedTypeNumber
WHERE (@HotelNumber = -1) OR (RM.HotelNumber = @HotelNumber OR RT.HotelNumber = @HotelNumber)

OPEN #ConfirmedCur

FETCH NEXT FROM #ConfirmedCur
INTO @Status,@NumberOfRooms,@BookedTypeNumber,@RoomTypeNumber,@Adults,@Children1,@Children2,@AllotmentNumber,@ResArrival,@ResDeparture,@NumberOfConfirmed,@NumberOfProvisionals,@NumberOfGuaranteed,@NumberOfWaitinglist,@NumberOfTentative,@NumberOfOutOfOrder,@NumberOfOutForService, @IsDayRoom

WHILE (@@FETCH_STATUS <> -1)
BEGIN

  IF @@FETCH_STATUS <> -2
  BEGIN
    IF @RoomTypeNumber > 0
      SET @BookedTypeNumber = @RoomTypeNumber

    IF ((@Status = 4) and (@IncludeTentative = 0)) OR ((@IsDayRoom = 1) AND (@IncludeDayroomsInSoldRooms = 0)) -- tentative or dayroom
      SET @NumberOfRooms = 0

    SET @AllotNumberOfRooms=@NumberOfRooms
    SET @AllotPax=0
    IF @AllotmentNumber > 0
    BEGIN
      IF @VacantRoomsInclAllotment = 1
        SET @AllotNumberOfRooms = 0
      SET @AllotPax = (@Adults + @Children1 + @Children2) * @NumberOfRooms
    END

--    SET @CurrentDate = @Arrival
--    WHILE @CurrentDate < @Departure
    SET @CurrentDate = @ResArrival

    WHILE CONVERT(CHAR(8), @CurrentDate, 112) <= CONVERT(CHAR(8), @ResDeparture, 112)
    BEGIN
      IF CONVERT(CHAR(8), @CurrentDate, 112) >=  CONVERT(CHAR(8), @Arrival, 112) AND CONVERT(CHAR(8), @CurrentDate, 112) <= CONVERT(CHAR(8), @Departure, 112)
      BEGIN
        UPDATE #ROOMBOOKINGSTATUS2
        SET
          SoldRooms = SoldRooms + @AllotNumberOfRooms,
          Adults = Adults + @Adults * @NumberOfRooms,
          Children1 = Children1 + @Children1 * @NumberOfRooms,
          Children2 = Children2 + @Children2 * @NumberOfRooms,
          NumberOfConfirmed = NumberOfConfirmed + @NumberOfConfirmed,
          NumberOfProvisionals = NumberOfProvisionals + @NumberOfProvisionals,
          NumberOfGuaranteed = NumberOfGuaranteed + @NumberOfGuaranteed,
          NumberOfTentative = NumberOfTentative + @NumberOfTentative,
          NumberOfWaitinglists = NumberOfWaitinglists + @NumberOfWaitinglist,
          NumberOfOutOfOrder = NumberOfOutOfOrder + @NumberOfOutOfOrder,
          NumberOfOutForService = NumberOfOutForService + @NumberOfOutForService,
          NumberOfDayRooms = NumberOfDayRooms + (@NumberOfRooms * @IsDayRoom),
          PaxAllotment = PaxAllotment + @AllotPax
        WHERE RoomTypeNumber = @BookedTypeNumber AND HotelNumber = @HotelNumber AND DateStamp = CONVERT(CHAR(8), @CurrentDate, 112)
        IF @@rowcount = 0
          INSERT INTO #ROOMBOOKINGSTATUS2 (DateStamp, HotelNumber, RoomTypeNumber, SoldRooms, Adults, Children1, Children2, NumberOfConfirmed, NumberOfProvisionals, NumberOfGuaranteed, NumberOfTentative, NumberOfWaitinglists, NumberOfDayrooms, FreeRoomsAllotment, TotalRoomsAllotment, PaxAllotment, NumberOfRoomsSoldToday, NumberOfOutOfOrder, NumberOfOutForService) 
          VALUES (CONVERT(CHAR(8), @CurrentDate, 112), @HotelNumber, @BookedTypeNumber, @NumberOfRooms, @Adults * @NumberOfRooms, @Children1 * @NumberOfRooms, @Children2 * @NumberOfRooms, @NumberOfConfirmed, @NumberOfProvisionals, @NumberOfGuaranteed, @NumberOfTentative, @NumberOfWaitinglist, @NumberOfRooms * @IsDayRoom, 0, 0, @AllotPax, 0, @NumberOfOutOfOrder, @NumberOfOutForService)      
        END

      SET @CurrentDate = @CurrentDate + 1
    END
  END

  FETCH NEXT FROM #ConfirmedCur
  INTO @Status,@NumberOfRooms,@BookedTypeNumber,@RoomTypeNumber,@Adults,@Children1,@Children2,@AllotmentNumber,@ResArrival,@ResDeparture,@NumberOfConfirmed,@NumberOfProvisionals,@NumberOfGuaranteed,@NumberOfWaitinglist,@NumberOfTentative,@NumberOfOutOfOrder, @NumberOfOutForService, @IsDayRoom
END

CLOSE #ConfirmedCur
DEALLOCATE #ConfirmedCur

-- A L L O T M E N T ------------------------------------------------------------------------------------------------------------------------

DECLARE
@DateStamp CHAR(8),
@TotalAllotment INT,
@CurrentCount INT,
@TimeOfArrivalForRes DATETIME,
@TimeOfDepartureForRes DATETIME
SELECT @TimeOfArrivalForRes=CONVERT(DATETIME, ISNULL(KeyValue, '18:00'), 108) FROM dbo.HotelSetup WITH (NOLOCK) WHERE SearchKey = 'TimeOfArrivalForRes'
SELECT @TimeOfDepartureForRes=CONVERT(DATETIME, ISNULL(KeyValue, '10:00'), 108) FROM dbo.HotelSetup WITH (NOLOCK) WHERE SearchKey = 'TimeOfDepartureForRes'

DECLARE #ConfirmedCur CURSOR LOCAL FAST_FORWARD FOR

SELECT DateStamp,RoomTypeNumber,InitialCount+TotalChange,dbo.GetCurrentCount(AL.AllotmentNumber,RoomTypeNumber,DateStamp) 
FROM AllotmentLines AL WITH (NOLOCK)
INNER JOIN Allotments A WITH (NOLOCK) ON AL.AllotmentNumber=A.AllotmentNumber
INNER JOIN AllotmentHotelXTAB AHX (NOLOCK) ON A.AllotmentNumber = AHX.AllotmentNumber AND ISNULL(A.MarkAsDeleted, 0) = 0 
INNER JOIN Customer C (NOLOCK) ON AHX.HotelCustomerNumber = C.CustomerNumber
AND ((@HotelNumber = -1) OR (HotelNumber = @HotelNumber)) 
--AND CONVERT(DATETIME, DateStamp, 112) + @TimeOfArrivalForRes < @Departure
AND CONVERT(DATETIME, DateStamp, 112) + @TimeOfArrivalForRes <= @Departure
AND CONVERT(DATETIME, DateStamp, 112) + @TimeOfDepartureForRes + 1 >= @Arrival

OPEN #ConfirmedCur

FETCH NEXT FROM #ConfirmedCur
INTO @DateStamp,@BookedTypeNumber,@TotalAllotment,@CurrentCount

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  IF @@FETCH_STATUS <> -2
  BEGIN
    UPDATE #ROOMBOOKINGSTATUS2 SET
      TotalRoomsAllotment = TotalRoomsAllotment + @TotalAllotment,
      FreeRoomsAllotment = FreeRoomsAllotment + @TotalAllotment - @CurrentCount
    WHERE RoomTypeNumber = @BookedTypeNumber AND HotelNumber = @HotelNumber AND DateStamp = CONVERT(CHAR(8), @DateStamp, 112)
    IF @@rowcount = 0
      INSERT INTO #ROOMBOOKINGSTATUS2 (DateStamp, HotelNumber, RoomTypeNumber, SoldRooms, Adults, Children1, Children2, NumberOfConfirmed, NumberOfProvisionals, NumberOfGuaranteed, NumberOfTentative, NumberOfWaitinglists, NumberOfDayrooms, FreeRoomsAllotment, TotalRoomsAllotment, PaxAllotment, NumberOfRoomsSoldToday, NumberOfOutOfOrder,NumberOfOutForService, NumberOfConfirmed) 
      VALUES (CONVERT(CHAR(8), @DateStamp, 112), @HotelNumber, @BookedTypeNumber, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @TotalAllotment - @CurrentCount, @TotalAllotment, 0, 0, 0, 0, 0)
  END

  FETCH NEXT FROM #ConfirmedCur
  INTO @DateStamp,@BookedTypeNumber,@TotalAllotment,@CurrentCount
END

CLOSE #ConfirmedCur
DEALLOCATE #ConfirmedCur

-- R E S U L T ------------------------------------------------------------------------------------------------------------------------------

SELECT * FROM #ROOMBOOKINGSTATUS2
ORDER BY HotelNumber,DateStamp,RoomTypeNumber  -- DO NOT DELETE! Used for overbook check in booking. Dates must be in order!

DROP TABLE #ROOMBOOKINGSTATUS2
GO
