CREATE PROCEDURE [dbo].[OnlineDominoAvailabilityAllotment] @ANumberOfRooms SMALLINT, @AAdults SMALLINT,
  @AChildren1 SMALLINT, @AChildren2 SMALLINT, @AStartDate DATETIME, @AEndDate DATETIME,
  @ProductTypeNumber INT , @ErrorLevel INTEGER OUTPUT, @ErrorFilterNumber INTEGER OUTPUT,
  @ErrorDateStamp DATETIME OUTPUT, @ACustomerNumber INTEGER = 0, @AOnlineRoomTypeNumber INTEGER = 0,
  @AHotelNumber VARCHAR(100) = '', @ACustomerRateNumber INT = 0, @AResource TINYINT = 0, @ACheckavailability BIT = 1, 
  @ACountryNumber INT = 1, @AvailabilitySummeryproductMode INT = 2, @IsOwnerBooking BIT = 0, @CampaignCodeText VARCHAR(50) = '', 
  @AChannelType INT = 2, @AAllotmentNumber INT = 0 AS

/*
Created by Kent Johansen March 2020

Changes:
  KC: 20250402: Bug: Records with empty allotments was not selected.
  KC: 20240319: Bug: Call to GenerateAvailability was removed during merge process.
  KC: 20240313: Bug: Availalable products was not always shown.
  KC: 20230824: Bug: Availability check was not correct.
  KC  20230822: Bug: Same room was shown several times.
  KC  20230711: Rewritten and filtering by allotment on product implemented.
  PHJ 20220615: Change: When CampaignCodeText <> '' now includes products that are not set to be online.
  KC  20210420: Bug: Fix for "Possible to book rooms Online without any availability on allotment"
  KJ  20210408: Bug: Fix for "Ambiguous column name 'MinPax'."

*/

SET NOCOUNT ON

DECLARE @TotPax INT
DECLARE @LEndDate DATETIME

CREATE TABLE #ProductPrice
(
  RoomTypeNumber INT,
  ProductNumber INT,
  RoomPriceFirstDay MONEY NULL,
  RoomPrice MONEY NULL,
  RoomPricePerDay MONEY NULL,
  RoomPriceMinTotal MONEY NULL
)

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
)

IF @AEndDate > @AStartDate 
	SELECT @LEndDate = DATEADD(DAY, -1, @AEndDate)
ELSE
	SET @LEndDate = @AEndDate

SET @TotPax = ISNULL(@AAdults, 0) + ISNULL(@AChildren1, 0) + ISNULL(@AChildren2, 0)

-- Get allotment availability
INSERT INTO #tmpAvailability
  EXEC GenerateAvailability @AStartDate, @LEndDate, 1, @AHotelNumber, '', 1, 0, '', '',  2, 0, 1, 0, 0, @AAllotmentNumber


-- Get sum availability per Room type, datestamp
SELECT SUM(ISNULL(FreeRoomsAllotment, 0)) AS FreeRoomAllotment, dtRange.individualDate, rt.RoomTypeNumber, rt.HotelNumber
INTO #SumAvailability
FROM Roomtypes rt
CROSS APPLY dbo.DateRange(@AStartDate,@LEndDate, RoomTypeNumber) AS dtRange
LEFT OUTER JOIN #tmpAvailability ON rt.RoomTypeNumber = #tmpAvailability.RoomTypeNumber
  AND dtRange.IndividualDate = #tmpAvailability.DateStamp
WHERE (@AHotelNumber = '' OR (@AHotelNumber <> '' AND #tmpAvailability.HotelNumber IN (SELECT number FROm iter_intlist_to_table_No_Dummy(@AHotelNumber))))
GROUP BY dtRange.individualDate, rt.RoomTypeNumber, rt.HotelNumber


-- Get prices
DECLARE @RTNumber INT
DECLARE @ProdNumber INT
DECLARE @ParentRoomtype INT

DECLARE @CalcTotalPrice MONEY
DECLARE @CalcArrivalPrice MONEY


SELECT DISTINCT #SumAvailability.RoomTypeNumber, Products.ProductNumber, ISNULL(RoomTypes.ParentRoomTypeNumber,0) AS ParentRoomTypeNumber,
 Products.Description, Products.ProductName,OnlineRoomTypes.OnlineRoomTypeNumber, RoomTypes.OnlinePriority,
 X.RoomTypeName, Customer.LastName AS HotelName, Customer.CustomerNumber AS HotelCustomerNumber, Customer.HotelNumber, FreeRoomAllotment,
ISNULL(ProductExtended.FK_AllotmentNumber, 0) AllotmentNumber
  INTO #Result
  FROM #SumAvailability  
INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #SumAvailability.RoomTypeNumber
INNER JOIN OnlineRoomTypes WITH (NOLOCK) ON OnlineRoomTypes.RoomTypeNumber = #SumAvailability.RoomTypeNumber  
    AND (ISNULL(OnlineRoomTypes.MaxPaxTotal, 0) >= @TotPax
    AND ISNULL(OnlineRoomTypes.MaxAdultPax, 0) >= @AAdults 
    AND ISNULL(OnlineRoomTypes.MaxChildren1Pax, 0) >= @AChildren1
    AND ISNULL(OnlineRoomTypes.MaxChildren2Pax, 0) >= @AChildren2
	  AND ISNULL(OnlineRoomTypes.MinPax, 0) <= @TotPax)
	  OR ISNULL(RoomTypes.ParentRoomTypeNumber, 0) > 0
INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = #SumAvailability.HotelNumber
    AND Customer.CustomerTypeNumber = 7
    AND ISNULL(Customer.MarkAsDeleted, 0) = 0
INNER JOIN ProductHotelXTAB WITH (NOLOCK) ON ProductHotelXTAB.HotelCustomerNumber = Customer.CustomerNumber
INNER JOIN Products WITH (NOLOCK) ON Products.ProductNumber = ProductHotelXTAB.ProductNumber
INNER JOIN ProductExtended WITH (NOLOCK) ON ProductExtended.FK_ProductNumber = Products.ProductNumber
CROSS APPLY (SELECT [Text] AS RoomTypeName FROM OnlineRoomTypesTranslated WITH (NOLOCK)
  WHERE OnlineRoomTypesTranslated.OnlineRoomTypeNumber = OnlineRoomTypes.OnlineRoomTypeNumber
  AND CountryNumber = @ACountryNumber AND Pax=0) X
WHERE (@CampaignCodeText <> '' or (@CampaignCodeText = '' AND  Products.AllowFilterPicassoOnline = 1))




DECLARE db_cur CURSOR READ_ONLY FAST_FORWARD FOR
	SELECT RoomTypeNumber, ProductNumber, ParentRoomTypeNumber
	FROm #Result

OPEN db_cur

FETCH NEXT FROM db_cur INTO @RTNumber, @ProdNumber, @ParentRoomtype

DECLARE @AAdultPriceIsZero BIT

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @ParentRoomType = 0
  BEGIN
    EXEC ProductPrices @ProdNumber, @AStartDate, @AEndDate, @RTNumber, @ANumberOfRooms, @AAdults, @AChildren1, @AChildren2, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT
      , 0, 0, 0, 0, 0, 0, 0, 0, -1, @AAdultPriceIsZero OUTPUT
  END
  ELSE
  BEGIN
    EXEC ProductPrices @ProdNumber, @AStartDate, @AEndDate, @RTNumber, @TotPax, 1, 0, 0, @CalcTotalPrice OUTPUT, @CalcArrivalPrice OUTPUT
      , 0, 0, 0, 0, 0, 0, 0, 0, -1, @AAdultPriceIsZero OUTPUT
  END

  IF ISNULL(@AAdultPriceIsZero, 0) = 0
    INSERT INTO #ProductPrice VALUES (@RTNumber, @ProdNumber, @CalcArrivalPrice, @CalcTotalPrice, 0, 0)

  FETCH NEXT FROM db_cur INTO @RTNumber, @ProdNumber, @ParentRoomtype
END

CLOSE db_cur
DEALLOCATE db_cur

-- Delete prices for products does not allow children prices
IF (@AChildren1 > 0 OR @AChildren2 > 0)
BEGIN
  DELETE FROM #ProductPrice 
  WHERE ProductNumber IN 
  (
    SELECT Productnumber 
    FROM Products p WITH (NOLOCK) 
    INNER JOIN ProductExtended pe WITH (NOLOCK) ON p.ProductNumber = pe.FK_ProductNumber 
    WHERE ISNULL(pe.NoChildren, 0) = 1
  )
END

SELECT R.RoomTypeNumber, R.ProductNumber, Description, 
  #ProductPrice.RoomPriceFirstDay,
  #ProductPrice.RoomPrice,
  ProductName,
  #ProductPrice.RoomPricePerDay,
  #ProductPrice.RoomPriceMinTotal,
  OnlineRoomTypeNumber, OnlinePriority,
  RoomTypeName, HotelName, HotelCustomerNumber, HotelNumber, FreeRoomAllotment, AllotmentNumber,
  ROW_NUMBER() OVER (PARTITION BY R.RoomTypeNumber, R.ProductNumber, HotelCustomerNumber ORDER BY R.RoomTypeNumber, HotelCustomerNumber,AllotmentNumber DESC) AllotRowNo,
  ROW_NUMBER() OVER (PARTITION BY R.ProductNumber, HotelCustomerNumber ORDER BY R.PRoductNumber) ProductRowNo
INTO #T1
FROM #Result R
INNER JOIN #ProductPrice ON #ProductPrice.ProductNumber = R.ProductNumber
  AND #ProductPrice.RoomTypeNumber = R.RoomTypeNumber 
  AND #ProductPrice.RoomPrice <> 0 
ORDER BY Description, #ProductPrice.RoomPrice

DECLARE @RowCount INT

SELECT @RowCount = Count(*) FROM #T1
WHERE ProductRowNo = 1

SELECT RoomTypeNumber, ProductNumber, Description, RoomPriceFirstDay,
  RoomPrice, ProductName, RoomPricePerDay, RoomPriceMinTotal,
  OnlineRoomTypeNumber, OnlinePriority,
  RoomTypeName, HotelName, HotelCustomerNumber, HotelNumber, MIN(FreeRoomAllotment) FreeRoomAllotment
FROM #T1
WHERE (@ROWCOUNT = 1) OR (@RowCount > 1 AND (AllotmentNumber = @AAllotmentNumber OR (AllotmentNumber <= 0 AND AllotRowNo = 1)))
GROUP BY 
RoomTypeNumber, ProductNumber, Description, RoomPriceFirstDay,
  RoomPrice, ProductName, RoomPricePerDay, RoomPriceMinTotal,
  OnlineRoomTypeNumber, OnlinePriority,
  RoomTypeName, HotelName, HotelCustomerNumber, HotelNumber
HAVING MIN(FreeRoomAllotment) - @ANumberOfRooms >= 0

DROP TABLE #SumAvailability
DROP TABLE #tmpAvailability
DROP TABLE #Result
DROP TABLE #ProductPrice
DROP TABLE #T1