CREATE PROCEDURE dbo.OTAGetInventory @AStartDate DATETIME, @AEndDate DATETIME, @AHotelNumber INT, @ARateLevelId INT, @APerRoomType BIT, @AOnlyTopLevel BIT, @ASpecRateLevelId INT, 
  @ACentralizedAllotment BIT = 0, @AIncludeAvailability BIT = 1 AS
/*
  Created by Kent Johansen march 2014
  Used to get inventory count for OTA schema generation
  Changes:
    KJ 20200518: Ext: Added RateLevelRestrictionId to OTAGetInventory
    KJ 20190208: Ext: Handle for show info on room types plan
    KJ 20180308: Bug: Fix for "Room number is null".
    KJ 20170808: Bug: Fix for "Wrong number of available"
    KJ 20170626: Bug: Fix for number of days for MLOS not set.
    KJ 20170227: Ext: Support for Room-In-Room.
    KJ 20160317: Ext: Get number of available number of rooms by subtracting sold rooms per requestorid.
    KJ 20160307: Ext: Added RestrictionType to output
    KJ 20151117: Ext: Implement use for other than BW hotels.
    KJ 20150317: Bug: Fit changes made in GenerateAvailability
    20140918: Bug: Fix for "Only top level inventory when setting room OOO and Inuse". Bug #182
    20140904: Bug: Fix for "Wrong numbers when select for levels other than toplevel"
    20140902: Bug: Fix for "start and end date sometimes not at 00:00:00:000".
    20140825: Bug: Fix for "Number of rooms sometimes NULL".
    20140808: Bug: Fix for "Column name or number of supplied values does not match table definition."
    20140630: Ext: Added @ASpecRateLevelId for TW BR 5.2.2.9 + 5.2.3.11
    20140401: Ext: Added AOnlyTopLevel
  
*/

DECLARE @OFSDecreaseVacantRooms BIT
DECLARE @TopRateLevelId INT
DECLARE @NumberOfRooms INT
DECLARE @IsBW BIT

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

SET @ASpecRateLevelId = ISNULL(@ASpecRateLevelId, -1)

SET @ACentralizedAllotment = ISNULL(@ACentralizedAllotment, 0)

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)
  WHERE FKRateLevelChannelId = @ARateLevelId
  ORDER BY SortNumber
END
ELSE 
  SET @TopRateLevelId = -1

SELECT @NumberOfRooms = COUNT(*) FROM Rooms WITH (NOLOCK) WHERE HotelNumber = @AHotelNumber

IF @AOnlyTopLevel = 1 AND ISNULL(@ASpecRateLevelId, -1) <= 0
BEGIN
  SELECT TOP 1 @ASpecRateLevelId = RateLevelChannelRateLevelXTab.FKRateLevelId 
  FROM RateLevelChannelRateLevelXTab WITH (NOLOCK)
  INNER JOIN RateLevelChannelCustomerXTab WITH (NOLOCK) ON RateLevelChannelCustomerXTab.FKRateLevelChannelId = RateLevelChannelRateLevelXTab.FKRateLevelChannelId
  INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = RateLevelChannelCustomerXTab.FKCustomerNumber
    AND Customer.HotelNumber = @AHotelNumber AND Customer.CustomerTypeNumber = 7
  ORDER BY RateLevelChannelRateLevelXTab.SortNumber
END

SET @AStartDate = CAST(FLOOR(CAST(@AStartDate AS FLOAT)) AS DATETIME)
SET @AEndDate = CAST(FLOOR(CAST(@AEndDate AS FLOAT)) AS DATETIME)

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 ISNULL(@AIncludeAvailability, 1) = 1
  INSERT INTO #tmpAvailability
    EXEC GenerateAvailability @AStartDate, @AEndDate, 1, @AHotelNumber, '', 1, 0, '', @AIncludeOccupiedInOFS = 1

SELECT DISTINCT
RateLevelChannelRateLevelXtab.SortNumber,
RateLevelRoomTypeRestrictionXTab.DateStamp, 
RateLevel.Name,
RateLevel.RateLevelId, 
(
  CASE WHEN @ACentralizedAllotment = 1 THEN
    (SELECT dbo.NumberOfRoomsForRoomType(RoomTypes.RoomTypeNumber))
  ELSE
    (SELECT CASE WHEN @TopRateLevelId = RateLevelChannelRateLevelXTab.FKRateLevelId THEN
      CASE WHEN @APerRoomType = 0 THEN
        @NumberOfRooms
      ELSE
        (SELECT dbo.NumberOfRoomsForRoomType(RoomTypes.RoomTypeNumber))
      END
    ELSE
      RateLevelRoomTypeRestrictionXTab.RoomsAllocated
    END)
  END 
)
-
CASE WHEN @IsBW = 1 THEN
  ISNULL((
  SELECT CASE WHEN @TopRateLevelId = RateLevelChannelRateLevelXTab.FKRateLevelId THEN
    CASE WHEN @APerRoomType = 0 THEN
      (
        SELECT 
        CASE WHEN @OFSDecreaseVacantRooms = 1 THEN
          ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0)
        ELSE
          SUM(#tmpAvailability.NumberOfOutOfOrder)
        END
        FROM #tmpAvailability (NOLOCK) 
        WHERE #tmpAvailability.DateStamp = RateLevelRoomTypeRestrictionXTab.DateStamp
      )
    ELSE
      (
        SELECT 
        CASE WHEN @OFSDecreaseVacantRooms = 1 THEN
          ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0)
        ELSE
          SUM(#tmpAvailability.NumberOfOutOfOrder)
        END
        FROM #tmpAvailability (NOLOCK) 
        WHERE #tmpAvailability.DateStamp = RateLevelRoomTypeRestrictionXTab.DateStamp
        AND #tmpAvailability.RoomTypeNumber = RateLevelRoomTypeXTab.FKRoomTypeNumber
      )
    END
  ELSE 
    0
  END
  ), 0)
ELSE
  CASE WHEN @ACentralizedAllotment = 1 THEN
    (
      SELECT ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) + 
	    CASE WHEN @OFSDecreaseVacantRooms = 1 THEN ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0) ELSE 0 END
      FROM #tmpAvailability
      WHERE #tmpAvailability.DateStamp = CONVERT(VARCHAR(8), RateLevelRoomTypeRestrictionXTab.DateStamp, 112)
      AND #tmpAvailability.RoomTypeNumber = RoomTypes.RoomTypeNumber
    )
  ELSE
    (
      SELECT ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) + 
	    CASE WHEN @OFSDecreaseVacantRooms = 1 THEN ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0) ELSE 0 END
      FROM #tmpAvailability
      WHERE #tmpAvailability.RequestorId = FK_OTARequestorId
      AND #tmpAvailability.DateStamp = CONVERT(VARCHAR(8), RateLevelRoomTypeRestrictionXTab.DateStamp, 112)
      AND #tmpAvailability.RoomTypeNumber = RoomTypes.RoomTypeNumber
      AND #tmpAvailability.ProductNumber IN ((SELECT ProductNumber FROM Products (NOLOCK) WHERE Products.FKRateLevelId = RateLevel.RateLevelId))
    )
  END
END AS Allocated,
RoomTypes.Type, RoomTypes.RoomTypeNumber, Products.ProductNumber, RateLevelRestriction.RestrictionType, RateLevelRoomTypeRestrictionXTab.RestrictionLength,
RateLevelRestriction.Id AS RateLevelRestrictionId
FROM RateLevelRoomTypeRestrictionXTab WITH (NOLOCK)
INNER JOIN RateLevelRoomTypeXTab WITH (NOLOCK) ON RateLevelRoomTypeXTab.Id = RateLevelRoomTypeRestrictionXTab.FKRateLevelRoomTypeXTabId
  AND ((@APerRoomType = 1 AND RateLevelRoomTypeXTab.FKRoomTypeNumber > 0) or (@APerRoomType = 0 AND ISNULL(RateLevelRoomTypeXTab.FKRoomTypeNumber, 0) = 0))
INNER JOIN RateLevelRestriction WITH (NOLOCK) ON RateLevelRestriction.Id = RateLevelRoomTypeRestrictionXTab.FKRateLevelRestrictionId
INNER JOIN RateLevel WITH (NOLOCK) ON RateLevel.RateLevelId = RateLevelRoomTypeXTab.FKRateLevelId
INNER JOIN RateLevelChannelRateLevelXTab WITH (NOLOCK) ON RateLevelChannelRateLevelXTab.FKRateLevelId = RateLevel.RateLevelId
  AND (@ARateLevelId = -1 OR RateLevelChannelRateLevelXTab.FKRateLevelChannelId = @ARateLevelId)
LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RateLevelRoomTypeXTab.FKRoomTypeNumber
INNER JOIN Products WITH (NOLOCK) ON Products.FKRateLevelId = RateLevel.RateLevelId
INNER JOIN RateLevelChannel WITH (NOLOCK) ON RateLevelChannel.RateLevelChannelId = RateLevelChannelRateLevelXTab.FKRateLevelChannelId
WHERE RateLevelRoomTypeRestrictionXTab.DateStamp BETWEEN @AStartDate AND @AEndDate 
AND (@ASpecRateLevelId = -1 OR RateLevelChannelRateLevelXTab.FKRateLevelId = @ASpecRateLevelId)
AND (@IsBW = 1 OR ISNULL(RoomTypes.RoomTypeNumber, 0) > 0)
ORDER BY RateLevelChannelRateLevelXtab.SortNumber, RoomTypes.RoomTypeNumber, RateLevelRoomTypeRestrictionXTab.DateStamp

DROP TABLE #tmpAvailability