CREATE PROCEDURE [dbo].[DKNTAvailableRoom] @HotelCustomerNumber INT, @AStartDate CHAR(8), @AEndDate CHAR(8), @AShowOOO BIT = 0, @AShowOFS BIT = 0 AS
SET NOCOUNT ON
/*
  Created by Kent Johansen, Novemer 2022

  Changes:
    KJ 20230222: Ext: Added fields for FLC invoicing.
    KJ 20230222: Fix for "Number of rooms is 0 if no sold rooms on date"
*/

DECLARE @HotelNumber INT
DECLARE @NumberOfRooms INT

SELECT @HotelNumber = HotelNumber
FROM Customer WITH (NOLOCK)
WHERE CustomerNumber = @HotelCustomerNumber

CREATE TABLE #tmpAvailability
(
  DateStamp CHAR(8) NOT NULL,
  RoomTypeNumber INT NOT NULL,
  SegmentNumber INT NOT NULL,
  PointOfSaleNumber INT NOT NULL,
  HotelNumber INT NOT NULL,
  SoldRoomType INT NOT NULL,
  ProductNumber INT NOT NULL,
  ReservationGroup 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,
  NumberOfOccupied SMALLINT NULL,
  NumberOfOccupiedGroup SMALLINT NULL,
  SoldPrice MONEY NULL,
  RequestorID INT NULL,
  NumberOfRoomsSoldCourse smallint NULL,
  NumberOfRoomsSoldAttendee smallint NULL,
  NumberOfArrival SMALLINT NULL,
  NumberOfInhouse SMALLINT NULL,
  NumberOfStay SMALLINT NULL,
  NumberOfDeparture SMALLINT NULL,
  ArrivalPax SMALLINT NULL,
  InhousePax SMALLINT NULL,
  StayPax SMALLINT NULL,
  DeparturePax SMALLINT NULL,
  NumberOfWaitingArrival SMALLINT NULL,
  NumberOfTentativeArrival SMALLINT NULL,
  WaitingArrivalPax SMALLINT NULL, 
  TentativeArrivalPax SMALLINT NULL
)

INSERT INTO #tmpAvailability 
  EXEC GenerateAvailability @AStartDate, @AEndDate, 1, @HotelNumber, '', 1, 0, '', @AIncludeOccupiedInOFS = 1

SELECT @NumberOfRooms = COUNT(*)
FROM Rooms WITH (NOLOCK)
WHERE HotelNumber = @HotelNumber

SELECT dtRange.IndividualDate AS IndvDate, dtRange.PacketNumber AS NumberOfRooms 
INTO #tmp
FROM dbo.DateRange(@AStartDate, @AEndDate, @NumberOfRooms) AS dtRange

IF ISNULL(@AShowOOO, 0) = 1 OR ISNULL(@AShowOFS, 0) = 1
BEGIN
  SELECT #tmp.IndvDate AS DateStamp,
  #tmp.NumberOfRooms AS TotalNumberOfRooms,
  ISNULL((
    SELECT SUM(ISNULL(#tmpAvailability.NumberOfOutOfOrder, 0))
    FROM #tmpAvailability
    WHERE #tmpAvailability.DateStamp = #tmp.IndvDate
  ), 0) AS OOO,
  ISNULL((
    SELECT SUM(ISNULL(#tmpAvailability.NumberOfOutForService, 0))
    FROM #tmpAvailability
    WHERE #tmpAvailability.DateStamp = #tmp.IndvDate
  ), 0) AS OFS,
  #tmp.NumberOfRooms - 
  ISNULL((
    SELECT SUM(ISNULL(#tmpAvailability.NumberOfOutOfOrder, 0))
    FROM #tmpAvailability
    WHERE #tmpAvailability.DateStamp = #tmp.IndvDate
  ), 0) -
  ISNULL((
    SELECT SUM(ISNULL(#tmpAvailability.NumberOfOutForService, 0))
    FROM #tmpAvailability
    WHERE #tmpAvailability.DateStamp = #tmp.IndvDate
  ), 0) AS TotalActive
  FROM #tmp
  ORDER BY #tmp.IndvDate
END
ELSE
BEGIN
  SELECT #tmp.IndvDate AS DateStamp,
  #tmp.NumberOfRooms - ISNULL((
    SELECT SUM(ISNULL(#tmpAvailability.NumberOfOutOfOrder, 0))
    FROM #tmpAvailability
    WHERE #tmpAvailability.DateStamp = #tmp.IndvDate
  ), 0) AS AvailableRooms
  FROM #tmp
  ORDER BY #tmp.IndvDate
END

DROP TABLE #tmpAvailability
DROP TABLE #tmp