CREATE PROCEDURE dbo.OTAGetInventoryByCountType @AStartDate DATETIME, @AEndDate DATETIME, @AHotelNumber INT AS
SET NOCOUNT ON
/*
  Created By Kent Johansen October 2016
  Returns inventory/availability per count type (Physical(1), Definitive Avail(2), Tentative Avail(3), Definitive Sold(4), Tentative Sold(5), OOO(6), OFS(8))
  Changes:
    KJ 20170803: Ext: For Room-In-Room only send for Beds.
    KJ 20170531: Bug: Fix for "Wrong number of sold rooms when some rooms are available in an allotment".
    KJ 20170531: Bug: Fix for "Room types from all hotels is included"
    KJ 20170314: Bug: Fix for "Wrong number of sold rooms".

*/
DECLARE @EnableSubRooms BIT
SELECT @EnableSubRooms = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'EnableSubRooms'
SET @EnableSubRooms = ISNULL(@EnableSubRooms, 0)

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,
  NumberOfArrival SMALLINT NULL,
  NumberOfInhouse SMALLINT NULL,
  NumberOfStay SMALLINT NULL,
  NumberOfDeparture SMALLINT NULL,
  ArrivalPax SMALLINT NULL,
  InhousePax SMALLINT NULL,
  StayPax SMALLINT NULL,
  DeparturePax SMALLINT NULL,
  NumberOfWaitingArrival SMALLINT NULL,
  NumberOfTentativeArrival SMALLINT NULL,
  WaitingArrivalPax SMALLINT NULL, 
  TentativeArrivalPax SMALLINT NULL
)

INSERT INTO #tmpAvailability
  EXEC GenerateAvailability @AStartDate, @AEndDate, 1, @AHotelNumber, '', 1, 0, '', @AIncludeOccupiedInOFS = 1

SELECT dtRange.IndividualDate AS DateStamp, RoomTypes.RoomTypeNumber,
(SELECT dbo.NumberOfRoomsForRoomType(RoomTypes.RoomTypeNumber)) AS Physical,
(SELECT dbo.NumberOfRoomsForRoomType(RoomTypes.RoomTypeNumber)-ISNULL(SUM(#tmpAvailability.SoldRooms), 0)-
ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0)-ISNULL(SUM(#tmpAvailability.TotalRoomsAllotment), 0)) AS DefinitiveAvail,
ISNULL(SUM(#tmpAvailability.FreeRoomsAllotment), 0) AS TentativeAvail,
(ISNULL(SUM(#tmpAvailability.SoldRooms), 0)-ISNULL(SUM(#tmpAvailability.NumberOfProvisionals), 0)-ISNULL(SUM(#tmpAvailability.NumberOfTentative), 0)-
  ISNULL(SUM(#tmpAvailability.NumberOfWaitinglists), 0)+ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0)+ISNULL(SUM(#tmpAvailability.TotalRoomsAllotment), 0)-ISNULL(SUM(#tmpAvailability.FreeRoomsAllotment), 0)) AS DefinitiveSold,
(ISNULL(SUM(#tmpAvailability.NumberOfProvisionals), 0)+ISNULL(SUM(#tmpAvailability.NumberOfTentative), 0)+ISNULL(SUM(#tmpAvailability.NumberOfWaitinglists), 0)) AS TentativeSold,
ISNULL(SUM(#tmpAvailability.NumberOfOutOfOrder), 0) AS OOO,
ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0) AS OFS
FROM RoomTypes WITH (NOLOCK) 
CROSS APPLY dbo.DateRange(@AStartDate, @AEndDate, RoomTypes.RoomTypeNumber) AS dtRange
LEFT OUTER JOIN #tmpAvailability ON RoomTypes.RoomTypeNumber = #tmpAvailability.RoomTypeNumber
  AND dtRange.IndividualDate = #tmpAvailability.DateStamp
WHERE RoomTypes.HotelNumber = @AHotelNumber
AND (@EnableSubRooms = 0 OR ISNULL(RoomTypes.ParentRoomTypeNumber, 0) > 0)
GROUP BY dtRange.IndividualDate, RoomTypes.RoomTypeNumber

DROP TABLE #tmpAvailability