CREATE PROCEDURE [dbo].[GetVacantRooms] @AStartDate DATETIME, @AEndDate DATETIME, @AHotelNumberCSV VARCHAR(500), @ARoomTypeNumberCSV VARCHAR(1000), @AOFSDecreaseVacantRooms BIT,
  @AInclAllotment BIT, @AAllotmentNumber int = 0, @ACalculateOccupied BIT = 1, @ASoldRoomType INT = 2, @AInclSegments BIT = 0, @AInclPointOfSale BIT = 0, @ASumResult INT = 0, @NotOverBooked INT = 1 OUTPUT AS
SET NOCOUNT ON 
/*
  Created by Kent Johansen and Kåre Christensen, November 2017
  Returns number of vacant rooms per roomtype per day
  Changes:
	PHJ/20220912: Added @AAllotmentNumber
    AMH/20220207: Fix for NULL values not allowed in column PaxAllotment
    AHE/20211006: Added @ASumResult and @NotOverBooked for use in AvailabilityRoutines
    AMH/20210624: Added PaxAllotment for use in TFrameAvailability
    AMH/20210617: Added possibility to calculate SoldRooms per Segment and PointOfSale for use in TFrameAvailability
    AMH/20210312: Added SoldPrice for use in TFrameAvailability
    AMH/20200528: RQ3762: Added HotelNumber for use in TFrameAvailability
    AMH/20200528: RQ3762: Added fields needed in TFrameAvailability
    AMH/20191129: CR174500: Take owner reservations into account when generating availability

*/

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
)

SELECT * INTO #HotelNumbers FROM DBO.iter_intlist_to_table_No_Dummy(@AHotelNumberCSV)
SELECT * INTO #RoomTypeNumbers FROM DBO.iter_intlist_to_table_No_Dummy(@ARoomTypeNumberCSV)

INSERT INTO #tmpAvailability
  EXEC GenerateAvailability @AStartDate, @AEndDate, 1, @AHotelNumberCSV, '', 1, 0, @ARoomTypeNumberCSV, '', @ASoldRoomType, 1, @ACalculateOccupied, 0, 0, @AAllotmentNumber

DECLARE @TotalRooms INT
DECLARE @TotalSubRooms INT

SELECT DISTINCT
dtRange.IndividualDate AS DateStamp,
RoomTypes.RoomTypeNumber,
RoomTypes.HotelNumber,
NULL AS SegmentNumber,
NULL AS PointOfSaleNumber,
(
    (SELECT dbo.NumberOfRoomsForRoomType(RoomTypes.RoomTypeNumber))
)
-
(
  SELECT ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) +
    CASE WHEN @AInclAllotment = 1 THEN ISNULL(SUM(#tmpAvailability.TotalRoomsAllotment), 0) ELSE 0 END +
    CASE WHEN @AOFSDecreaseVacantRooms = 1 THEN ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0) ELSE 0 END
) AS Vacant,
0 AS VacantOrig,
(
  SELECT ISNULL(SUM(#tmpAvailability.SoldRooms), 0) +
    CASE WHEN @AInclAllotment = 1 THEN ISNULL(SUM(#tmpAvailability.TotalRoomsAllotment), 0) ELSE 0 END 
) AS Sold,
0 SoldOrig,
0 SoldAndOccupied,
SUM(ISNULL(#tmpAvailability.SoldPrice, 0)) AS SoldPrice,
SUM(ISNULL(#tmpAvailability.NumberOfRoomsSoldCourse, 0)) AS MainCourse,
ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) AS Occupied,
ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) AS OutOfOrder,
CASE WHEN @AOFSDecreaseVacantRooms = 1 THEN ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0) ELSE 0 END AS OutForService,
ISNULL(SUM(#tmpAvailability.Adults), 0) + ISNULL(SUM(#tmpAvailability.Children1), 0) + ISNULL(SUM(#tmpAvailability.Children2), 0) AS PAX,
ISNULL(SUM(#tmpAvailability.NumberOfConfirmed), 0) AS Confirmed,
ISNULL(SUM(#tmpAvailability.NumberOfGuaranteed), 0) AS Guaranteed,
ISNULL(SUM(#tmpAvailability.NumberOfWaitinglists), 0) AS Waitinglists,
ISNULL(SUM(#tmpAvailability.NumberOfTentative), 0) AS Tentative,
ISNULL(SUM(#tmpAvailability.NumberOfProvisionals), 0) AS Provisionals,
ISNULL(SUM(#tmpAvailability.NumberOfDayrooms), 0) AS Dayrooms,
ISNULL(SUM(#tmpAvailability.FreeRoomsAllotment), 0) AS FreeRoomsAllotment,
ISNULL(SUM(#tmpAvailability.TotalRoomsAllotment), 0) AS TotalRoomsAllotment,
ISNULL(SUM(#tmpAvailability.PaxAllotment), 0) AS PaxAllotment,
CAST(CASE WHEN MAX(ISNULL(RoomTypes.ParentRoomTypeNumber, 0)) > 0 THEN 1 ELSE 0 END AS BIT) AS IsSubRoomType,
CONVERT(VARCHAR(8),dtRange.IndividualDate,112) AS DateStampStr,
MAX(ISNULL(Customer.CustomerNumber, 0)) AS HotelCustomerNUmber
INTO #TEMP
FROM RoomTypes WITH (NOLOCK) 
CROSS APPLY dbo.DateRange(@AStartDate, @AEndDate, RoomTypes.RoomTypeNumber) AS dtRange
LEFT OUTER JOIN #tmpAvailability ON RoomTypes.RoomTypeNumber = #tmpAvailability.RoomTypeNumber
  AND dtRange.IndividualDate = #tmpAvailability.DateStamp
INNER JOIN Customer (NOLOCK) ON RoomTypes.HotelNumber = Customer.HotelNumber 
	AND ISNULL(Customer.MArkAsDeleted, 0) = 0 
	AND CustomerTypeNumber = 7
WHERE (ISNULL(@ARoomTypeNumberCSV, '') = '' OR RoomTypes.RoomTypeNumber IN (SELECT DISTINCT Number FROM #RoomTypeNumbers)) 
AND (ISNULL(@AHotelNumberCSV, '') = '' OR RoomTypes.HotelNumber IN (SELECT DISTINCT Number FROM #HotelNumbers)) 
GROUP BY dtRange.IndividualDate, RoomTypes.RoomTypeNumber, RoomTypes.HotelNumber
ORDER BY dtRange.IndividualDate, RoomTypes.RoomTypeNumber, RoomTypes.HotelNumber


UPDATE #TEMP
SET VacantOrig = Vacant,
SoldOrig = Sold,
SoldAndOccupied = Sold + Occupied

IF @AInclSegments = 1
BEGIN
  SELECT DISTINCT SegmentNumber AS Number INTO #tmpSegments FROM #tmpAvailability

  INSERT INTO #TEMP
    (DateStamp, RoomTypeNumber, HotelNumber, SegmentNumber, Vacant, VacantOrig, Sold, SoldOrig, SoldAndOccupied, Occupied, OutOfOrder, OutForService, Confirmed, Guaranteed, Waitinglists, Tentative, Provisionals, Dayrooms, FreeRoomsAllotment, TotalRoomsAllotment, PaxAllotment, DateStampStr, HotelCustomerNumber)
  SELECT dtRange.IndividualDate, -1, RoomTypes.HotelNumber, #tmpSegments.Number, 0, 0, 0, SUM(#tmpAvailability.SoldRooms), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
    CONVERT(VARCHAR(8),dtRange.IndividualDate,112), MAX(ISNULL(Customer.CustomerNumber, 0))
  FROM #tmpSegments
  CROSS APPLY dbo.DateRange(@AStartDate, @AEndDate, #tmpSegments.Number) AS dtRange
  INNER JOIN #tmpAvailability ON #tmpSegments.Number = #tmpAvailability.SegmentNumber
    AND dtRange.IndividualDate = #tmpAvailability.DateStamp
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #tmpAvailability.RoomTypeNumber
  INNER JOIN Customer (NOLOCK) ON RoomTypes.HotelNumber = Customer.HotelNumber
  WHERE ISNULL(Customer.MarkAsDeleted, 0) = 0 
  AND (ISNULL(@ARoomTypeNumberCSV, '') = '' OR RoomTypes.RoomTypeNumber IN (SELECT DISTINCT Number FROM #RoomTypeNumbers)) 
  AND (ISNULL(@AHotelNumberCSV, '') = '' OR RoomTypes.HotelNumber IN (SELECT DISTINCT Number FROM #HotelNumbers)) 
  GROUP BY dtRange.IndividualDate, RoomTypes.HotelNumber, #tmpSegments.Number
  HAVING SUM(#tmpAvailability.SoldRooms) > 0
  ORDER BY dtRange.IndividualDate, RoomTypes.HotelNumber, #tmpSegments.Number

  DROP TABLE #tmpSegments
END

IF @AInclPointOfSale = 1
BEGIN
  SELECT DISTINCT PointOfSaleNumber AS Number INTO #tmpPointOfSale FROM #tmpAvailability

  INSERT INTO #TEMP
    (DateStamp, RoomTypeNumber, HotelNumber, PointOfSaleNumber, Vacant, VacantOrig, Sold, SoldOrig, SoldAndOccupied, Occupied, OutOfOrder, OutForService, Confirmed, Guaranteed, Waitinglists, Tentative, Provisionals, Dayrooms, FreeRoomsAllotment, TotalRoomsAllotment, PaxAllotment, DateStampStr, HotelCustomerNumber)
  SELECT dtRange.IndividualDate, -1, RoomTypes.HotelNumber, #tmpPointOfSale.Number, 0, 0, 0, SUM(#tmpAvailability.SoldRooms), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
    CONVERT(VARCHAR(8),dtRange.IndividualDate,112), MAX(ISNULL(Customer.CustomerNumber, 0))
  FROM #tmpPointOfSale
  CROSS APPLY dbo.DateRange(@AStartDate, @AEndDate, #tmpPointOfSale.Number) AS dtRange
  INNER JOIN #tmpAvailability ON #tmpPointOfSale.Number = #tmpAvailability.PointOfSaleNumber
    AND dtRange.IndividualDate = #tmpAvailability.DateStamp
  INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #tmpAvailability.RoomTypeNumber
  INNER JOIN Customer (NOLOCK) ON RoomTypes.HotelNumber = Customer.HotelNumber
  WHERE ISNULL(Customer.MarkAsDeleted, 0) = 0 
  AND (ISNULL(@ARoomTypeNumberCSV, '') = '' OR RoomTypes.RoomTypeNumber IN (SELECT DISTINCT Number FROM #RoomTypeNumbers)) 
  AND (ISNULL(@AHotelNumberCSV, '') = '' OR RoomTypes.HotelNumber IN (SELECT DISTINCT Number FROM #HotelNumbers)) 
  GROUP BY dtRange.IndividualDate, RoomTypes.HotelNumber, #tmpPointOfSale.Number
  HAVING SUM(#tmpAvailability.SoldRooms) > 0
  ORDER BY dtRange.IndividualDate, RoomTypes.HotelNumber, #tmpPointOfSale.Number

  DROP TABLE #tmpPointOfSale
END

IF ISNULL(@ASumResult, 0) = 0
BEGIN
  SELECT * FROM #TEMP
  ORDER BY DateStamp, RoomTypeNumber
END
ELSE
BEGIN   --QQAH20211006, Test for total overbooked
  Select @NotOverBooked=SUM(Vacant) FROM #TEMP WITH (NOLOCK)
END


DROP TABLE #HotelNumbers
DROP TABLE #RoomTypeNumbers
DROP TABLE #tmpAvailability
