CREATE PROCEDURE dbo.OTAGetStatus @AStartDate DATETIME, @AEndDate DATETIME, @AHotelNumber INT, @ARateLevelId INT, @APerRoomType BIT, @ASpecRateLevelId INT, @ACentralizedAllotment BIT = 0, @AOutboundOverbookData BIT = 0 AS
/*
  Created by Kent Johansen April 2014
  Used to get status for OTA schema generation
  Changes:
    KJ 20180413: Ext: Use close restriction if fully booked
    KJ 20171113: Bug: Fix for "Wrong number of beds when all rooms is sold"
    KJ 20170921: Bug: Fix for "wrong number of rooms when using rate intelligence". Case #CR133088
    KJ 20170831: Ext: Handle for hotel is fully booked. Case #CR131774
    KJ 20170810: Bug: Fix for "Wrong number of rooms when using rate inteligence rules". Case #CR130658
    KJ 20170731: Bug: Fix for "Wrong numbers of rooms"
    KJ 20170301: Bug: Fix for "Room-In-Room not supported". Case #CR121524
    KJ 20160929: Ext: Added RateLevelInvRule Req #3190
    KJ 20160919: Bug: Fix for "Wrong number of Rooms"
    KJ 20160317: Ext: Get number of available number of rooms by subtracting sold rooms per requestorid.
    KJ 20160308: Ext: Added number of rooms.
    KJ 20160222: Bug: Fix for "No data returned if ratelevel and specratelevelid is not the same".
    KJ 20151112: Ext: Implement use for other than BW hotels.
    KJ 20151109: Ext: Implement use for other than BW hotels.
    KJ 20141028: Bug: Fix for "Changed the delete extra line - now using correct keys"
    KJ 20140913: Bug: Fix for "If specified, then only level A"
    KJ 20140808: Bug: Fix for "No data when @ASpecRateLevelId is -1"
    KJ 20140630: Ext: Added @ASpecRateLevelId ofr TW BR 5.2.5.7  
*/
SET NOCOUNT ON 
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 @ProductName VARCHAR(100)
DECLARE @ProductNumber INT
DECLARE @RateLevelChannelId INT
DECLARE @NumberOfRooms INT

DECLARE @CurFKRateLevelId1 INT
DECLARE @CurFKRoomTypeNumber1 INT
DECLARE @CurId1 INT

DECLARE @TopRateLevelId INT
DECLARE @IsBW BIT

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @Infinity BIT
DECLARE @NumberOfDays INT
DECLARE @FK_RoomTypeNumber INT
DECLARE @RateInteligenceUpdated BIT
DECLARE @Availability INT
DECLARE @TotalNumberOfRooms INT
DECLARE @TotalNumberOfVacantRooms INT

DECLARE @SystemDate DATETIME

SET @SystemDate = CONVERT(DATETIME, (SELECT SystemDate FROM SYSTEM WITH (NOLOCK)))

SELECT TOP 1 @TopRateLevelId = FKRateLevelId 
FROM RateLevelChannelRateLevelXTab WITH (NOLOCK)
INNER JOIN RateLevelChannel WITH (NOLOCK) ON RateLevelChannel.RateLevelChannelId = RateLevelChannelRateLevelXTab.FKRateLevelId
INNER JOIN RateLevelChannelCustomerXTab WITH (NOLOCK) ON RateLevelChannelCustomerXTab.FKRateLevelChannelId = RateLevelChannel.RateLevelChannelId
INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = RateLevelChannelCustomerXTab.FKCustomerNumber
  AND Customer.HotelNumber = @AHotelNumber
  AND Customer.CustomerTypeNumber = 7
ORDER BY SortNumber

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

DECLARE @AInclAllotment BIT
DECLARE @OFSDecreaseVacantRooms BIT

SELECT @AInclAllotment = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='VacantRoomsInclAllotment'
SET @AInclAllotment = ISNULL(@AInclAllotment, 1)

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

SELECT @TotalNumberOfRooms = COUNT(*)
FROM Rooms WITH (NOLOCK)
INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
  AND RoomTypes.HotelNumber = @AHotelNumber
  AND ISNULL(RoomTypes.ParentRoomTypeNumber, 0) = 0

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

SELECT DISTINCT
RateLevelChannelRateLevelXtab.SortNumber,
RateLevelRoomTypeRestrictionXTab.DateStamp, 
RateLevel.Name,
RateLevel.RateLevelId, 
RoomTypes.Type, RoomTypes.RoomTypeNumber,
--RateLevelRoomTypeRestrictionXTab.FKRateLevelRestrictionId,
CASE WHEN @ACentralizedAllotment = 1 THEN
  CASE WHEN 
    @TotalNumberOfRooms - ISNULL((
      SELECT 
        CASE WHEN @AInclAllotment = 1 THEN
          ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.TotalRoomsAllotment), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) + 
          CASE WHEN @OFSDecreaseVacantRooms = 1 THEN
            ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0)
          ELSE
            ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0)
          END
        ELSE
          ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) + 
          CASE WHEN @OFSDecreaseVacantRooms = 1 THEN
            ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0)
          ELSE
            ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0)
          END
        END
      FROM #tmpAvailability
      INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #tmpAvailability.RoomTypeNumber
        AND RoomTypes.ParentRoomTypeNumber IS NULL
      WHERE #tmpAvailability.DateStamp = CONVERT(VARCHAR(8), RateLevelRoomTypeRestrictionXTab.DateStamp, 112)
      ), 0)  <= 0 THEN
    (SELECT Id FROM RateLevelRestriction (NOLOCK) WHERE RestrictionType = 1) -- Fully booked always use restiction type Close
  ELSE
    RateLevelRoomTypeRestrictionXTab.FKRateLevelRestrictionId
  END
ELSE
  RateLevelRoomTypeRestrictionXTab.FKRateLevelRestrictionId
END AS FKRateLevelRestrictionId,

RateLevelRoomTypeRestrictionXTab.RestrictionLength,
RateLevelRestriction.RestrictionType,
RateLevelRestrictionRateChannelXTab.Priority,
RateLevelRoomTypeXTab.FKRateLevelId AS FKRateLevelId1,
RateLevelRoomTypeXTab.FKRoomTypeNumber AS FKRoomTypeNumber1,
RateLevelRoomTypeXTab.Id AS Id1,
Products.ProductName, Products.ProductNumber, 
RateLevelChannel.RateLevelChannelId,

CASE WHEN @IsBW = 1 THEN
  RateLevelRoomTypeRestrictionXTab.RoomsAllocated
ELSE
  CASE WHEN @ACentralizedAllotment = 1 THEN
    (SELECT dbo.NumberOfRoomsForRoomType(RoomTypes.RoomTypeNumber)) - ISNULL((
      SELECT 
        CASE WHEN @AInclAllotment = 1 THEN
          ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.TotalRoomsAllotment), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) + 
          CASE WHEN @OFSDecreaseVacantRooms = 1 THEN
            ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0)
          ELSE
            ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0)
          END
        ELSE
          ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) + 
          CASE WHEN @OFSDecreaseVacantRooms = 1 THEN
            ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0)
          ELSE
            ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0)
          END
        END
      FROM #tmpAvailability
      WHERE #tmpAvailability.DateStamp = CONVERT(VARCHAR(8), RateLevelRoomTypeRestrictionXTab.DateStamp, 112)
      AND #tmpAvailability.RoomTypeNumber = RoomTypes.RoomTypeNumber
    ), 0)
  ELSE
    RateLevelRoomTypeRestrictionXTab.RoomsAllocated - (
      SELECT ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0)
      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 NumberOfRooms,
CAST(0 AS BIT) AS RateInteligenceUpdated,
CASE WHEN @ACentralizedAllotment = 1 THEN
  @TotalNumberOfRooms - ISNULL((
    SELECT 
      CASE WHEN @AInclAllotment = 1 THEN
        ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.TotalRoomsAllotment), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) + 
        CASE WHEN @OFSDecreaseVacantRooms = 1 THEN
          ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0)
        ELSE
          ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0)
        END
      ELSE
        ISNULL(SUM(#tmpAvailability.SoldRooms), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) + 
        CASE WHEN @OFSDecreaseVacantRooms = 1 THEN
          ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) + ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0)
        ELSE
          ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0)
        END
      END
    FROM #tmpAvailability
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #tmpAvailability.RoomTypeNumber
      AND RoomTypes.ParentRoomTypeNumber IS NULL
    WHERE #tmpAvailability.DateStamp = CONVERT(VARCHAR(8), RateLevelRoomTypeRestrictionXTab.DateStamp, 112)
    ), 0)  
ELSE
  99999
END AS TotalNumberOfVacantRooms
INTO #Status
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
  AND (@ASpecRateLevelId = -1 OR (RateLevel.RateLevelId = @ASpecRateLevelId))
INNER JOIN RateLevelChannelRateLevelXTab WITH (NOLOCK) ON RateLevelChannelRateLevelXTab.FKRateLevelId = RateLevel.RateLevelId
  AND (RateLevelChannelRateLevelXTab.FKRateLevelChannelId = @ARateLevelId)
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
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 (@APerRoomType = 0 OR RateLevel.RateLevelId = @TopRateLevelId)

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, @ProductName, @ProductNumber, @RateLevelChannelId, @NumberOfRooms, @RateInteligenceUpdated, @TotalNumberOfVacantRooms

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

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @CurFKRateLevelId1 = @FKRateLevelId1 AND @CurFKRoomTypeNumber1 = @FKRoomTypeNumber1 AND @CurId1 = @Id1
  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

  FETCH NEXT FROM db_cur INTO @SortNumber, @DateStamp, @Name, @RateLevelId, @Type, @RoomTypeNumber, @FKRateLevelRestrictionId, @RestrictionLength, @RestrictionType, @Priority,
    @FKRateLevelId1, @FKRoomTypeNumber1, @Id1, @ProductName, @ProductNumber, @RateLevelChannelId, @NumberOfRooms, @RateInteligenceUpdated, @TotalNumberOfVacantRooms
END

DECLARE db_cur_InvRule CURSOR FORWARD_ONLY READ_ONLY LOCAL FOR
  SELECT RateLevelInvRule.StartDate, RateLevelInvRule.EndDate, RateLevelInvRule.Infinity, RateLevelInvRule.NumberOfDays, RateLevelInvRule.NumberOfRooms,
  RateLevelInvRuleRoomTypeXtab.FK_RoomTypeNumber, ISNULL(RateLevelInvRule.Availability, 0)
  FROM RateLevelInvRule WITH (NOLOCK)
  INNER JOIN RateLevelInvRuleRateLevelChannelXtab WITH (NOLOCK) ON RateLevelInvRuleRateLevelChannelXtab.FK_RateLevelInvRuleId = RateLevelInvRule.Id
    AND RateLevelInvRuleRateLevelChannelXtab.FK_RateLevelChannelId IN (SELECT DISTINCT RateLevelChannelId FROM #Status)
  INNER JOIN RateLevelInvRuleRoomTypeXtab WITH (NOLOCK) ON RateLevelInvRuleRoomTypeXtab.FK_RateLevelInvRuleId = RateLevelInvRule.Id
  WHERE Active = 1
  ORDER BY RateLevelInvRule.Availability DESC
OPEN db_cur_InvRule
FETCH NEXT FROM db_cur_InvRule INTO @StartDate, @EndDate, @Infinity, @NumberOfDays, @NumberOfRooms, @FK_RoomTypeNumber, @Availability

WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE #Status
  SET #Status.NumberOfRooms = #Status.NumberOfRooms - @NumberOfRooms, #Status.RateInteligenceUpdated = 1
  WHERE #Status.DateStamp <= @SystemDate + @NumberOfDays
  AND ((@Infinity = 1) OR (#Status.DateStamp BETWEEN @StartDate AND @EndDate))
  AND #Status.RoomTypeNumber = @FK_RoomTypeNumber
  AND #Status.NumberOfRooms <= @Availability
  AND #Status.RateInteligenceUpdated = 0

  FETCH NEXT FROM db_cur_InvRule INTO @StartDate, @EndDate, @Infinity, @NumberOfDays, @NumberOfRooms, @FK_RoomTypeNumber, @Availability
END

CLOSE db_cur_InvRule
DEALLOCATE db_cur_InvRule

IF ISNULL(@AOutboundOverbookData, 0) = 0
BEGIN
  IF @ACentralizedAllotment = 1
  BEGIN
    UPDATE #Status
    SET #Status.NumberOfRooms = 0
    FROM #Status
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #Status.RoomTypeNumber
        AND RoomTypes.ParentRoomTypeNumber IS NULL
    WHERE #Status.NumberOfRooms > 0
    AND #Status.TotalNumberOfVacantRooms <= 0
  END

  UPDATE #Status
  SET #Status.NumberOfRooms = 0
  WHERE #Status.NumberOfRooms < 0
END

SELECT * 
FROM #Status
ORDER BY SortNumber, RoomTypeNumber, DateStamp

DROP TABLE #Status