CREATE PROCEDURE dbo.OTAInsight @AHotelNumberCSV VARCHAR(500), @AHotelCustomerNumberCSV VARCHAR(500), @AFromDate DATETIME, @AToDate DATETIME AS
/*
  Created by Kent Johansen, April 2021

  Changes:
*/
SET NOCOUNT ON

DECLARE @StartDateStr CHAR(8)
DECLARE @EndDateStr CHAR(8)
DECLARE @DefaultCurrencyCode CHAR(3)
DECLARE @HotelCountryNumber INT

SELECT @DefaultCurrencyCode = CurrencyCode 
FROM Countries WITH (NOLOCK) 
WHERE Countrynumber = (Select CONVERT(INT,KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='HotelCurrency')

IF ISNULL(@DefaultCurrencyCode, '') = ''
BEGIN
  SELECT @HotelCountryNumber = CONVERT(INT,KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='CountryCode'
  IF ISNULL(@HotelCountryNumber, 0) > 0
    SELECT TOP 1 @DefaultCurrencyCode = CurrencyCode FROM Countries WITH (NOLOCK) WHERE CountryNumber=@HotelCountryNumber ORDER BY SortNumber
  IF ISNULL(@DefaultCurrencyCode, '') = ''
    SELECT TOP 1 @DefaultCurrencyCode = CurrencyCode FROM Countries WITH (NOLOCK) ORDER BY SortNumber    
END

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
)

SET @StartDateStr = CONVERT(CHAR(8), @AFromDate, 112)
SET @EndDateStr = CONVERT(CHAR(8), @AToDate, 112)
IF @AHotelNumberCSV = '-1'
	SET @AHotelNumberCSV = ''

INSERT INTO #tmpAvailability 
  EXEC GenerateAvailability @StartDateStr, @EndDateStr, 1, @AHotelNumberCSV, '', 1, 0, '', @AIncludeOccupiedInOFS = 1

SELECT DateStamp, ReservationStatus, ValueType, RoomTypeNumber,
SUM(ActualNumberOfRooms) + SUM(BookedNumberOfRooms) AS NumberOfRooms,
SUM(ActualPriceInclPackageExVat) + SUM(BookedPriceInclPackageExVat) AS TurnoverExVAT,
SUM(ActualPriceInclPackage) + SUM(BookedPriceInclPackage) AS TurnoverInclVAT
INTO #MIS
FROM MISTableView WITH (NOLOCK)
WHERE (@AHotelCustomerNumberCSV = '' OR MISTableView.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV)))
AND MISTableView.DateStamp BETWEEN @AFromDate AND @AToDate
AND MISTableView.ReferenceNumber > 0
AND MISTableView.ValueType IN (1, 4, 5) -- Rooms, Restarant, Other
GROUP BY DateStamp, ReservationStatus, ValueType, RoomTypeNumber

SELECT GETDATE() AS EXTRACT_DATE,
CONVERT(VARCHAR(8), dtRange.IndividualDate, 112) AS STAY_DATE,
RoomTypes.RoomTypeNumber AS ROOM_TYPE_NUMBER,
(
  SELECT ISNULL(SUM(ISNULL(#tmpAvailability.NumberOfOutOfOrder, 0)), 0)
  FROM #tmpAvailability 
  WHERE #tmpAvailability.DateStamp = dtRange.IndividualDate
  AND #tmpAvailability.RoomTypeNumber = RoomTypes.RoomTypeNumber
) AS OOO,
(
  SELECT COUNT(*) 
  FROM Rooms WITH (NOLOCK) 
  WHERE Rooms.RoomTypeNumber = RoomTypes.RoomTypeNumber
) AS INVENTORY,
(
  SELECT ISNULL(SUM(NumberOfRooms), 0)
  FROM #MIS
  WHERE #MIS.DateStamp = dtRange.IndividualDate
  AND #MIS.RoomTypeNumber = RoomTypes.RoomTypeNumber
  AND #MIS.ValueType = 1
  AND ReservationStatus <> 3 -- No waitinglist
) AS ROOMS_DEDUCTED_TRANSIENT,
(
  SELECT ISNULL(SUM(NumberOfRooms), 0)
  FROM #MIS
  WHERE #MIS.DateStamp = dtRange.IndividualDate
  AND #MIS.RoomTypeNumber = RoomTypes.RoomTypeNumber
  AND #MIS.ValueType = 1
) AS ROOMS_DEDUCTED_BLOCKS,

@DefaultCurrencyCode AS REVENUE_CURRENCY,

(
  SELECT ISNULL(SUM(TurnoverExVAT), 0)
  FROM #MIS
  WHERE #MIS.DateStamp = dtRange.IndividualDate
  AND #MIS.RoomTypeNumber = RoomTypes.RoomTypeNumber
  AND #MIS.ValueType = 1
  AND ReservationStatus <> 3 -- No waitinglist
) AS NET_ROOM_REVENUE_TRANSIENT,
(
  SELECT ISNULL(SUM(TurnoverInclVAT), 0)
  FROM #MIS
  WHERE #MIS.DateStamp = dtRange.IndividualDate
  AND #MIS.RoomTypeNumber = RoomTypes.RoomTypeNumber
  AND #MIS.ValueType = 1
  AND ReservationStatus <> 3 -- No waitinglist
) AS GROSS_ROOM_REVENUE_TRANSIENT,
(
  SELECT ISNULL(SUM(TurnoverExVAT), 0)
  FROM #MIS
  WHERE #MIS.DateStamp = dtRange.IndividualDate
  AND #MIS.RoomTypeNumber = RoomTypes.RoomTypeNumber
  AND #MIS.ValueType = 4
  AND ReservationStatus <> 3 -- No waitinglist
) AS NET_FB_REVENUE_TRANSIENT,
(
  SELECT ISNULL(SUM(TurnoverInclVAT), 0)
  FROM #MIS
  WHERE #MIS.DateStamp = dtRange.IndividualDate
  AND #MIS.RoomTypeNumber = RoomTypes.RoomTypeNumber
  AND #MIS.ValueType = 4
  AND ReservationStatus <> 3 -- No waitinglist
) AS GROSS_FB_REVENUE_TRANSIENT,
(
  SELECT ISNULL(SUM(TurnoverExVAT), 0)
  FROM #MIS
  WHERE #MIS.DateStamp = dtRange.IndividualDate
  AND #MIS.RoomTypeNumber = RoomTypes.RoomTypeNumber
  AND #MIS.ValueType = 5
  AND ReservationStatus <> 3 -- No waitinglist
) AS NET_OTHER_REVENUE_TRANSIENT,
(
  SELECT ISNULL(SUM(TurnoverInclVAT), 0)
  FROM #MIS
  WHERE #MIS.DateStamp = dtRange.IndividualDate
  AND #MIS.RoomTypeNumber = RoomTypes.RoomTypeNumber
  AND #MIS.ValueType = 5
  AND ReservationStatus <> 3 -- No waitinglist
) AS GROSS_OTHER_REVENUE_TRANSIENT

FROM RoomTypes WITH (NOLOCK)
CROSS APPLY dbo.DateRange(@AFromDate, @AToDate, RoomTypes.RoomTypeNumber) AS dtRange
ORDER BY dtRange.IndividualDate, RoomTypes.RoomTypeNumber

DROP TABLE #MIS
DROP TABLE #tmpAvailability