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 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
)

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
GROUP BY dtRange.individualDate, rt.RoomTypeNumber, rt.HotelNumber


-- Get Room types, that isn't available whole period
SELECT DISTINCT RoomTypeNumber
INTO #RtDelete
FROM #SumAvailability
WHERE FreeRoomAllotment - @ANumberOfRooms < 0 

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

DECLARE @CalcTotalPrice MONEY
DECLARE @CalcArrivalPrice MONEY

DECLARE db_cur CURSOR READ_ONLY FAST_FORWARD FOR
  SELECT DISTINCT #SumAvailability.RoomTypeNumber, Products.ProductNumber, ISNULL(RoomTypes.ParentRoomTypeNumber,0) AS ParentRoomTypeNumber 
  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
    AND ProductExtended.FK_AllotmentNumber = @AAllotmentNumber */
  WHERE RoomTypes.RoomTypeNumber NOT IN (SELECT RoomTypeNumber FROM #RtDelete)
  AND Products.AllowFilterPicassoOnline = 1
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

-- Get output where only room types available whole period is added
SELECT DISTINCT RoomTypes.RoomTypeNumber, Products.ProductNumber, Products.Description, 
  #ProductPrice.RoomPriceFirstDay,
  #ProductPrice.RoomPrice,
  Products.ProductName,
  #ProductPrice.RoomPricePerDay,
  #ProductPrice.RoomPriceMinTotal,
  OnlineRoomTypes.OnlineRoomTypeNumber, RoomTypes.OnlinePriority,
  (SELECT [Text] FROM OnlineRoomTypesTranslated WITH (NOLOCK)
  WHERE OnlineRoomTypesTranslated.OnlineRoomTypeNumber = OnlineRoomTypes.OnlineRoomTypeNumber
  AND CountryNumber = @ACountryNumber AND Pax=0) AS RoomTypeName,
  Customer.LastName AS HotelName, Customer.CustomerNumber AS HotelCustomerNumber, Customer.HotelNumber
FROM #SumAvailability
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
  AND ProductExtended.FK_AllotmentNumber = @AAllotmentNumber*/
INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #SumAvailability.RoomTypeNumber
INNER JOIN OnlineRoomTypes WITH (NOLOCK) ON OnlineRoomTypes.OnlineRoomTypeNumber = RoomTypes.OnlineRoomTypeNumber
  AND (@AOnlineRoomTypeNumber <= 0 OR OnlineRoomTypes.RoomTypeNumber = @AOnlineRoomTypeNumber)
INNER JOIN #ProductPrice ON #ProductPrice.ProductNumber = Products.ProductNumber
  AND #ProductPrice.RoomTypeNumber = #SumAvailability.RoomTypeNumber 
  AND #ProductPrice.RoomPrice <> 0 
WHERE #SumAvailability.RoomTypeNumber NOT IN (SELECT RoomTypeNumber FROM #RtDelete)
ORDER BY Products.Description, #ProductPrice.RoomPrice

DROP TABLE #SumAvailability
DROP TABLE #tmpAvailability
DROP TABLE #RtDelete
DROP TABLE #ProductPrice