CREATE PROCEDURE dbo.OTAGetInventorySold @AStartDate DATETIME, @AEndDate DATETIME, @AHotelNumber INT AS
/*
  Created by Kent Johansen march 2014
  Used to get inventory count for OTA schema generation
  Changes:
    KJ 20170227: Ext: Support for Room-In-Room.
    KJ 20160919: Bug: Fix for "Wrong field names from availability"
    KJ 20150317: Bug: Fit changes made in GenerateAvailability
    20140808: Bug: Fix for "Column name or number of supplied values does not match table definition."
  
*/

DECLARE @OFSDecreaseVacantRooms BIT
SELECT @OFSDecreaseVacantRooms = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'OOSDecreaseVacantRooms'
SET @OFSDecreaseVacantRooms = ISNULL(@OFSDecreaseVacantRooms, 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,
  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 
  CONVERT(DATETIME, DateStamp, 112) AS DateStamp,
  RoomTypeNumber,
  SUM(ISNULL(SoldRooms, 0))-(SUM(ISNULL(NumberOfTentative, 0))+SUM(ISNULL(NumberOfWaitingLists, 0)))+ISNULL(SUM(#tmpAvailability.NumberOfOccupied), 0) AS Sold,
  SUM(ISNULL(NumberOfTentative, 0))+SUM(ISNULL(NumberOfWaitingLists, 0)) AS Tentative,
  SUM(ISNULL(NumberOfOutOfOrder, 0)) + CASE WHEN @OFSDecreaseVacantRooms = 1 THEN ISNULL(SUM(#tmpAvailability.NumberOfOutForService), 0) ELSE 0 END AS OOO
FROM #tmpAvailability
GROUP BY DateStamp, RoomTypeNumber
ORDER BY RoomTypeNumber, DateStamp

DROP TABLE #tmpAvailability