CREATE PROCEDURE [dbo].[OTAGetInventoryMulti] @AStartDate DATETIME, @AEndDate DATETIME, @AHotelNumber INT, @ARateLevelId INT, @APerRoomType BIT, @ASpecRateLevelId INT AS
/*
  Created by Kent Johansen August 2023
  Used to get inventory count for OTA schema generation when running Multirestrictions
  Changes:
*/

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

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

SELECT DISTINCT
RateLevelChannelRateLevelXtab.SortNumber,
RateLevelRoomTypeRestrictionMulti.DateStamp, 
RateLevel.Name,
RateLevel.RateLevelId, 
0 AS Allocated,
RoomTypes.Type, RoomTypes.RoomTypeNumber, Products.ProductNumber, -1 AS RestrictionType, 0 AS RestrictionLength,
-1 AS RateLevelRestrictionId
FROM RateLevelRoomTypeRestrictionMulti WITH (NOLOCK)
INNER JOIN RateLevelRoomTypeXTab WITH (NOLOCK) ON RateLevelRoomTypeXTab.Id = RateLevelRoomTypeRestrictionMulti.FKRateLevelRoomTypeXTabId
  AND ((@APerRoomType = 1 AND RateLevelRoomTypeXTab.FKRoomTypeNumber > 0) or (@APerRoomType = 0 AND ISNULL(RateLevelRoomTypeXTab.FKRoomTypeNumber, 0) = 0))
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 RateLevelRoomTypeRestrictionMulti.DateStamp BETWEEN @AStartDate AND @AEndDate 
AND (@ASpecRateLevelId = -1 OR RateLevelChannelRateLevelXTab.FKRateLevelId = @ASpecRateLevelId)
AND ISNULL(RoomTypes.RoomTypeNumber, 0) > 0
ORDER BY RateLevelChannelRateLevelXtab.SortNumber, RoomTypes.RoomTypeNumber, RateLevelRoomTypeRestrictionMulti.DateStamp