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

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