CREATE PROCEDURE dbo.RateLevelGetAllocation @AArrivalDate DATETIME, @ADepartureDate DATETIME, @ABookingDate DATETIME, @ARoomTypeNumber INT, @AProductNumberCSV VARCHAR(MAX), @ANumberOfRooms INT, @AChannelType INT = 1, @AHotelNumberCSV VARCHAR(100) = '' AS
/*
  Created by Kent Johansen febrary 2014
  Returns a list of allowed products to book
  Changes:
    KJ 20231019: Ext: Support for multirestriction
    KJ 20220210: Bug: Fix for "Releasedays and cutoff is mixed". Case# CR214739  
      ReleaseDays: If set to 7, then arrivaldate has to be at least 7 days into the future
      CutOff: If set to 7, then arrival date has to be within the next 7 days.
    KJ 20211103: Bug: Fix for "Not showing data - RateLevelRoomTypeRestrictionXTab.RoomsAllocated is sometims NULL". Case #CR210760
    KJ 20211101: Bug: Fix for "Release days restriction not correct. Release is the same as last minute, so you can only book from x-number of days before arrival"
    KJ 20201029: Ext: Added Release days restriction. Req #3897 
    KJ 20201023: Ext: Added Cut-offs restriction. Req #3681
    KJ 20201021: Ext: Added Exact Length of Stay. Req #3786
    KJ 20201019: Bug: Fix for "SP returns records for all hotels"
    KJ 20180723: Ext: Added Max Length of Stay
    KJ 20170811: Bug: Fix for "Number of rooms not relevant for departure date. Only use restriction on departure date".
    KJ 20170704: Bug: Fix for "Product xx doesn't match any rate level in the given period. Do you wish to use it anyway? shown even if allocation has been made". Case #CR129170
    KJ 20161207: If closed on departure date then it is ok.
    KJ 20160819: Ext: Fixed multiple errors.
    KJ 20160708: Bug: Fix for "Channeltype not working".
    KJ 20160613: Bug: Fix for "All rate levels returned in result set"
    KJ 20160606: Ext: Added support for not BW hotel.
    KJ 20160606: Ext: Added support for CTD
    KJ 20160512: UseInPicasso removed. Now using ChannelType (0: None. 1: Picasso 2: Online)
    KJ 20141103: Major rewrite to fix "MLOS Restriction is not enforced (reference defect 4110)". Defect #4142 #CR078768
    KJ 20140912: Ext: Added ATopLevel to output
    KJ 20140912: Various bugfixes.
  
*/
SET NOCOUNT ON

DECLARE @AnyMulti INT

SELECT @AnyMulti = COUNT(*)
FROM RateLevelChannel WITH (NOLOCK) 
WHERE RateLevelChannel.ChannelType & @AChannelType > 0
AND RateLevelChannel.MultipleRestrictions = 1

IF ISNULL(@AnyMulti, 0) > 0
BEGIN
  SELECT DISTINCT RateLevelRoomTypeRestrictionMulti.DateStamp, RoomTypes.RoomTypeNumber, RateLevelRoomTypeXTab.FKRateLevelId AS FKRateLevelId1, Products.ProductNumber,
  ROW_NUMBER() OVER (PARTITION BY RateLevel.RateLevelId, RoomTypes.RoomTypeNumber ORDER BY RateLevel.RateLevelId, RoomTypes.RoomTypeNumber) AS num
  INTO #tmp
  FROM RateLevelRoomTypeRestrictionMulti WITH (NOLOCK)
  INNER JOIN RateLevelRoomTypeXTab WITH (NOLOCK) ON RateLevelRoomTypeXTab.Id = RateLevelRoomTypeRestrictionMulti.FKRateLevelRoomTypeXTabId
    AND RateLevelRoomTypeXTab.FKRoomTypeNumber > 0
  LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RateLevelRoomTypeXTab.FKRoomTypeNumber
  INNER JOIN RateLevel WITH (NOLOCK) ON RateLevel.RateLevelId = RateLevelRoomTypeXTab.FKRateLevelId
  INNER JOIN Products WITH (NOLOCK) ON Products.FKRateLevelId = RateLevel.RateLevelId
    AND ((@AProductNumberCSV = '') OR (Products.ProductNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AProductNumberCSV))))
  INNER JOIN RateLevelChannelRateLevelXTab WITH (NOLOCK) ON RateLevelChannelRateLevelXTab.FKRateLevelId = RateLevel.RateLevelId
  INNER JOIN RateLevelChannel WITH (NOLOCK) ON RateLevelChannel.RateLevelChannelId = RateLevelChannelRateLevelXTab.FKRateLevelChannelId
    AND RateLevelChannel.ChannelType & @AChannelType > 0 --bitwise compare
  WHERE RateLevelRoomTypeRestrictionMulti.DateStamp BETWEEN @AArrivalDate AND @ADepartureDate 
  AND (ISNULL(@ARoomTypeNumber, -1) <= -1 or RoomTypes.RoomTypeNumber = @ARoomTypeNumber)
  AND RateLevelChannel.RateLevelChannelId IN (
    SELECT FKRateLevelChannelId FROM RateLevelChannelCustomerXTab WITH (NOLOCK) 
    INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = RateLevelChannelCustomerXTab.FKCustomerNumber
      AND Customer.CustomerTypeNumber = 7
	  AND ISNULL(Customer.MarkAsDeleted, 0) = 0
    WHERE (ISNULL(@AHotelNumberCSV, '') = '' OR Customer.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AHotelNumberCSV)))
  )
  AND StopSell = 0
  AND (ISNULL(CTA, 0) = 0 OR @AArrivalDate <> RateLevelRoomTypeRestrictionMulti.DateStamp)
  AND (ISNULL(CTD, 0) = 0 OR @ADepartureDate <> RateLevelRoomTypeRestrictionMulti.DateStamp)
  AND (ISNULL(MLOS, 0) = 0 OR DATEDIFF(DAY, @AArrivalDate, @ADepartureDate) >= MLOS)
  AND (ISNULL(MaxLOS, 9999) = 9999 OR DATEDIFF(DAY, @AArrivalDate, @ADepartureDate) <= MaxLOS)
  AND (ISNULL(ExactLOS, 0) = 0 OR DATEDIFF(DAY, @AArrivalDate, @ADepartureDate) = ExactLOS)
  AND (ISNULL(ReleaseDays, 0) = 0 OR DATEDIFF(DAY, @ABookingDate, @ADepartureDate) > ReleaseDays)
  AND (ISNULL(CutOffDays, 0) = 0 OR DATEDIFF(DAY, @ABookingDate, @ADepartureDate) < CutOffDays)

  SELECT dtRange.IndividualDate AS DateStamp, -999 AS RoomsAllocate, #tmp.FKRateLevelId1, #tmp.RoomTypeNumber, #tmp.ProductNumber, 0 AS TopLevelId
  FROM #tmp
  CROSS APPLY dbo.DateRange(@AArrivalDate, @ADepartureDate, #tmp.FKRateLevelId1) AS dtRange
  WHERE num > DATEDIFF(DAY, @AArrivalDate, @ADepartureDate)

  DROP TABLE #tmp
END
ELSE
BEGIN
  CREATE TABLE #tbl
  (
    DateStamp DATETIME,
    RoomsAllocated INT,
    RateLevelId INT,
    RoomTypeNumber INT,
    ProductNumber INT,
    ATopLevel BIT
  )

  DECLARE @cnt INT

  DECLARE @TopRateLevelId INT
  DECLARE @TotalNumberOfRooms INT
  DECLARE @AHotelNumber INT
  DECLARE @SortNumber INT
  DECLARE @DateStamp DATETIME
  DECLARE @Name VARCHAR(50)
  DECLARE @RateLevelId INT
  DECLARE @Type VARCHAR(50)
  DECLARE @RoomTypeNumber INT
  DECLARE @FKRateLevelRestrictionId INT
  DECLARE @RestrictionLength INT
  DECLARE @RestrictionType SMALLINT
  DECLARE @Priority INT
  DECLARE @FKRateLevelId1 INT
  DECLARE @FKRoomTypeNumber1 INT
  DECLARE @Id1 INT

  DECLARE @CurFKRateLevelId1 INT
  DECLARE @CurFKRoomTypeNumber1 INT
  DECLARE @CurId1 INT
  DECLARE @CurDT DATETIME

  DECLARE @IsBW BIT

  SELECT @AHotelNumber = HotelNumber FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = @ARoomTypeNumber
  SELECT @TotalNumberOfRooms = COUNT(*) FROM Rooms WITH (NOLOCK) WHERE HotelNumber = @AHotelNumber

  SELECT @IsBW = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'IsBestWestern'
  SET @IsBW = ISNULL(@IsBW, 0)

  IF @IsBW = 1
  BEGIN
    SELECT TOP 1 @TopRateLevelId = FKRateLevelId 
    FROM RateLevelChannelRateLevelXTab WITH (NOLOCK)
    INNER JOIN RateLevelChannel WITH (NOLOCK) ON RateLevelChannel.RateLevelChannelId = RateLevelChannelRateLevelXTab.FKRateLevelId
      --AND RateLevelChannel.UseInPicasso = 1
      AND RateLevelChannel.ChannelType & @AChannelType > 0 --bitwise compare
    ORDER BY SortNumber
  END
  ELSE
    SET @TopRateLevelId = -1

  SELECT DISTINCT
  RateLevelChannelRateLevelXtab.SortNumber,
  RateLevelRoomTypeRestrictionXTab.DateStamp, 
  RateLevel.Name,
  RateLevel.RateLevelId, 
  RoomTypes.Type, RoomTypes.RoomTypeNumber,
  RateLevelRoomTypeRestrictionXTab.FKRateLevelRestrictionId,
  RateLevelRoomTypeRestrictionXTab.RestrictionLength,
  RateLevelRestriction.RestrictionType,
  RateLevelRestrictionRateChannelXTab.Priority,
  RateLevelRoomTypeXTab.FKRateLevelId AS FKRateLevelId1,
  RateLevelRoomTypeXTab.FKRoomTypeNumber AS FKRoomTypeNumber1,
  RateLevelRoomTypeXTab.Id AS Id1
  INTO #Status
  FROM RateLevelRoomTypeRestrictionXTab WITH (NOLOCK)
  INNER JOIN RateLevelRoomTypeXTab WITH (NOLOCK) ON RateLevelRoomTypeXTab.Id = RateLevelRoomTypeRestrictionXTab.FKRateLevelRoomTypeXTabId
    AND RateLevelRoomTypeXTab.FKRoomTypeNumber > 0
  INNER JOIN RateLevelRestriction WITH (NOLOCK) ON RateLevelRestriction.Id = RateLevelRoomTypeRestrictionXTab.FKRateLevelRestrictionId
  INNER JOIN RateLevel WITH (NOLOCK) ON RateLevel.RateLevelId = RateLevelRoomTypeXTab.FKRateLevelId
  INNER JOIN Products WITH (NOLOCK) ON Products.FKRateLevelId = RateLevel.RateLevelId
    AND ((@AProductNumberCSV = '') OR (Products.ProductNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AProductNumberCSV))))
  INNER JOIN RateLevelChannelRateLevelXTab WITH (NOLOCK) ON RateLevelChannelRateLevelXTab.FKRateLevelId = RateLevel.RateLevelId
  INNER JOIN RateLevelChannel WITH (NOLOCK) ON RateLevelChannel.RateLevelChannelId = RateLevelChannelRateLevelXTab.FKRateLevelChannelId
  --  AND RateLevelChannel.UseInPicasso = 1
    AND RateLevelChannel.ChannelType & @AChannelType > 0 --bitwise compare
  LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RateLevelRoomTypeXTab.FKRoomTypeNumber
  INNER JOIN RateLevelRestrictionRateChannelXTab WITH (NOLOCK) ON RateLevelRestrictionRateChannelXTab.FKRateLevelChannelId = RateLevelChannelRateLevelXTab.FKRateLevelChannelId
    AND RateLevelRestrictionRateChannelXTab.FKRateLevelRestrictionId = RateLevelRoomTypeRestrictionXTab.FKRateLevelRestrictionId
  WHERE RateLevelRoomTypeRestrictionXTab.DateStamp BETWEEN @AArrivalDate AND @ADepartureDate 
  AND (ISNULL(@ARoomTypeNumber, -1) <= -1 or RoomTypes.RoomTypeNumber = @ARoomTypeNumber)
  AND 
  (
    (
      RateLevel.RateLevelId = @TopRateLevelId AND
      (
         (ISNULL(RoomTypes.RoomTypeNumber, 0) = 0 AND (@TotalNumberOfRooms >= @ANumberOfRooms))
         OR
        (RoomTypes.RoomTypeNumber > 0 AND ((SELECT COUNT(*) FROM Rooms WITH (NOLOCK) WHERE RoomTypeNumber = RoomTypes.RoomTypeNumber) >= @ANumberOfRooms))
      )
    )
  
    OR (RateLevel.RateLevelId <> @TopRateLevelId AND ((ISNULL(RateLevelRoomTypeRestrictionXTab.RoomsAllocated, 0) >= @ANumberOfRooms) OR (RateLevelRoomTypeRestrictionXTab.DateStamp= @ADepartureDate)))
  )
  AND RateLevelChannel.RateLevelChannelId IN (
    SELECT FKRateLevelChannelId FROM RateLevelChannelCustomerXTab WITH (NOLOCK) 
    INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = RateLevelChannelCustomerXTab.FKCustomerNumber
      AND Customer.CustomerTypeNumber = 7
	  AND ISNULL(Customer.MarkAsDeleted, 0) = 0
    WHERE (ISNULL(@AHotelNumberCSV, '') = '' OR Customer.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AHotelNumberCSV)))
  )

  --SELECT * FROM #Status

  DECLARE db_cur CURSOR FORWARD_ONLY READ_ONLY LOCAL FOR
    SELECT * FROM #Status
    ORDER BY SortNumber, RoomTypeNumber, DateStamp, Priority
  OPEN db_cur
  FETCH NEXT FROM db_cur INTO @SortNumber, @DateStamp, @Name, @RateLevelId, @Type, @RoomTypeNumber, @FKRateLevelRestrictionId, @RestrictionLength, @RestrictionType, @Priority,
    @FKRateLevelId1, @FKRoomTypeNumber1, @Id1 

  SET @CurFKRateLevelId1 = -1
  SET @CurFKRoomTypeNumber1 = -1
  SET @CurId1 = -1
  SET @CurDT = -1

  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @CurFKRateLevelId1 = @FKRateLevelId1 AND @CurFKRoomTypeNumber1 = @FKRoomTypeNumber1 AND @CurId1 = @Id1 AND @CurDT = @DateStamp
    BEGIN
      DELETE TOP (1) #Status 
      WHERE FKRateLevelId1 = @CurFKRateLevelId1 
      AND FKRoomTypeNumber1 = @CurFKRoomTypeNumber1 
      AND Id1 = @CurId1 
      AND Priority < @Priority
    END

    SET @CurFKRateLevelId1 = @FKRateLevelId1
    SET @CurFKRoomTypeNumber1 = @FKRoomTypeNumber1
    SET @CurId1 = @Id1
    SET @CurDT = @DateStamp

    FETCH NEXT FROM db_cur INTO @SortNumber, @DateStamp, @Name, @RateLevelId, @Type, @RoomTypeNumber, @FKRateLevelRestrictionId, @RestrictionLength, @RestrictionType, @Priority,
      @FKRateLevelId1, @FKRoomTypeNumber1, @Id1 
  END

  CLOSE db_cur
  DEALLOCATE db_cur

  --SELECT * FROM #Status

  DECLARE db_cur1 CURSOR FOR
    SELECT COUNT(*),
    #Status.RateLevelId, #Status.RoomTypeNumber
    FROM #Status
    WHERE ((#Status.RestrictionType <> 3) OR (@AArrivalDate <> #Status.DateStamp)) -- Closed to arrival
    AND ((#Status.RestrictionType <> 5) OR (@ADepartureDate <> #Status.DateStamp)) -- Closed to departure
    AND (#Status.RestrictionType <> 1 OR ((@ADepartureDate = #Status.DateStamp))) -- not closed - closed on departure date is ok
    AND ((#Status.RestrictionType <> 4) OR (@AArrivalDate <> #Status.DateStamp) 
     OR ((#Status.RestrictionType = 4) AND (@AArrivalDate = #Status.DateStamp AND #Status.RestrictionLength <= DATEDIFF(DAY, @AArrivalDate, @ADepartureDate)))) -- MLOS
    AND ((#Status.RestrictionType <> 6) OR (@AArrivalDate <> #Status.DateStamp) 
     OR ((#Status.RestrictionType = 6) AND (@AArrivalDate = #Status.DateStamp AND #Status.RestrictionLength >= DATEDIFF(DAY, @AArrivalDate, @ADepartureDate)))) -- MaxLOS
    AND ((#Status.RestrictionType <> 7) OR (@AArrivalDate <> #Status.DateStamp) 
     OR ((#Status.RestrictionType = 7) AND (@AArrivalDate = #Status.DateStamp AND #Status.RestrictionLength = DATEDIFF(DAY, @AArrivalDate, @ADepartureDate)))) -- ExactLOS
    AND ((#Status.RestrictionType <> 8) OR (@AArrivalDate <> #Status.DateStamp) 
     OR ((#Status.RestrictionType = 8) AND (@AArrivalDate = #Status.DateStamp AND #Status.RestrictionLength > DATEDIFF(DAY, @ABookingDate, @AArrivalDate)))) -- CufOffs
    AND ((#Status.RestrictionType <> 9) OR (@AArrivalDate <> #Status.DateStamp) 
     OR ((#Status.RestrictionType = 9) AND (@AArrivalDate = #Status.DateStamp AND #Status.RestrictionLength < DATEDIFF(DAY, @ABookingDate, @AArrivalDate)))) -- Release days
    GROUP BY #Status.RateLevelId, #Status.RoomTypeNumber
    HAVING COUNT(*) > DATEDIFF(DAY, @AArrivalDate, @ADepartureDate)
  OPEN db_cur1

  FETCH NEXT FROM db_cur1 INTO @cnt, @RateLevelId, @RoomTypeNumber

  WHILE @@FETCH_STATUS = 0
  BEGIN
    INSERT INTO #tbl 
    SELECT 
      RateLevelRoomTypeRestrictionXTab.DateStamp,
      (
        SELECT CASE WHEN @TopRateLevelId = RateLevelChannelRateLevelXTab.FKRateLevelId THEN
          (SELECT dbo.NumberOfRoomsForRoomType(RoomTypes.RoomTypeNumber))
        ELSE
          RateLevelRoomTypeRestrictionXTab.RoomsAllocated
        END  
      ) AS RoomsAllocated,
      RateLevel.RateLevelId, 
      RoomTypes.RoomTypeNumber,
      Products.ProductNumber,
	  CASE WHEN RateLevel.RateLevelId = @TopRateLevelId THEN 1 ELSE 0 END
      FROM RateLevelRoomTypeRestrictionXTab WITH (NOLOCK)
      INNER JOIN RateLevelRoomTypeXTab WITH (NOLOCK) ON RateLevelRoomTypeXTab.Id = RateLevelRoomTypeRestrictionXTab.FKRateLevelRoomTypeXTabId
      INNER JOIN RateLevelRestriction WITH (NOLOCK) ON RateLevelRestriction.Id = RateLevelRoomTypeRestrictionXTab.FKRateLevelRestrictionId
      INNER JOIN RateLevel WITH (NOLOCK) ON RateLevel.RateLevelId = RateLevelRoomTypeXTab.FKRateLevelId
	    AND RateLevel.RateLevelId = @RateLevelId
      INNER JOIN Products WITH (NOLOCK) ON Products.FKRateLevelId = RateLevel.RateLevelId
	     AND ((@AProductNumberCSV = '') OR (Products.ProductNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AProductNumberCSV))))
      INNER JOIN RateLevelChannelRateLevelXTab WITH (NOLOCK) ON RateLevelChannelRateLevelXTab.FKRateLevelId = RateLevel.RateLevelId
      LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RateLevelRoomTypeXTab.FKRoomTypeNumber
      WHERE RateLevelRoomTypeRestrictionXTab.DateStamp BETWEEN @AArrivalDate AND @ADepartureDate
      AND RoomTypes.RoomTypeNumber = @RoomTypeNumber
  
    FETCH NEXT FROM db_cur1 INTO @cnt, @RateLevelId, @RoomTypeNumber
  END

  CLOSE db_cur1
  DEALLOCATE db_cur1

  SELECT #tbl.* FROM #tbl
  LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #tbl.RoomTypeNumber
  WHERE (ISNULL(@AHotelNumberCSV, '') = '' OR @AHotelNumberCSV = '-1' OR RoomTypes.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AHotelNumberCSV)))

  DROP TABLE #tbl
  DROP TABLE #Status
END