CREATE PROCEDURE dbo.DoGetHotelTrendBenchMark @AStartDate DATETIME, @AEndDate DATETIME, @ATheHotelNumber INT, @AInclAllotment BIT, @ADefaultCurrencyCode CHAR(3) AS
SET NOCOUNT ON

/*
  Created july 2012 by Kent Johansen
  Requires that table #HotelTrendResult is created.

  ValueType: TMISValueType = (mvtNotDefined, mvtRooms, mvtHalls, mvtDaymeetings, mvtRestaurant, mvtOther)
  
  Changes:
    KJ 20230926: Bug: Fix for "Changes in GenerateAvailability not changed here"
    KJ 20220325: Ext: Setting for get data incl/excl VAT and Package. Req. 4050
    KJ 20200428: Ext: Added provision in MIS. Req #3788
    KJ 20190926: Bug: Fix for "Roomturnover does not include package".
    KJ 20180723: Bug: Fix for "Cannot insert the value NULL into column 'HotelId'"
    KJ 20180209: Ext: Support for hall.
    KJ 20180121: Ext: Added NumberOfGuests
    KJ 20180118: Bug: Fix for "Total revenue should be with package include"
    KJ 20160919: Bug: Fix for "Wrong field names from availability"
    KJ 20151007: Bug: Fix for "Read alloment setting from hotelsetup". Case #CR093170
    KJ 20151006: Bug: Fix for "Don't include allotment if not set"
    KJ 20150331: Ext: AIncludeOccupiedInOFS added in GenerateAvailability
    KJ 20150317: Bug: Fit changes made in GenerateAvailability
    KJ 20131202: Ext: Added TotalRevenue Req #2160
    KJ 20120709: Bug: MISTableView rewrite
*/

DECLARE @LInclProvision BIT
DECLARE @RoomTurnoverInclVAT BIT
DECLARE @RoomTurnoverInclPackage BIT
DECLARE @OtherTurnoverInclVAT BIT
DECLARE @OtherTurnoverInclPackage BIT

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

SELECT @LInclProvision = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey='IncludeProvisionInMIS'
SET @LInclProvision = ISNULL(@LInclProvision, 0)

SELECT @RoomTurnoverInclVAT = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'BMARoomTurnoverIncludeVAT'
SET @RoomTurnoverInclVAT = ISNULL(@RoomTurnoverInclVAT, 0)
SELECT @RoomTurnoverInclPackage = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'BMARoomTurnoverIncludePackage'
SET @RoomTurnoverInclPackage = ISNULL(@RoomTurnoverInclPackage, 0)
SELECT @OtherTurnoverInclVAT = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'BMAOtherTurnoverIncludeVAT'
SET @OtherTurnoverInclVAT = ISNULL(@OtherTurnoverInclVAT, 0)
SELECT @OtherTurnoverInclPackage = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'BMAOtherTurnoverIncludePackage'
SET @OtherTurnoverInclPackage = ISNULL(@OtherTurnoverInclPackage, 1)

DECLARE @TotalRooms INT
DECLARE @CurDate DATETIME
DECLARE @StartDateStr CHAR(8)
DECLARE @EndDateStr CHAR(8)
DECLARE @Date DATETIME
DECLARE @SoldRooms INT
DECLARE @OOORooms INT
DECLARE @RevenueHotelRooms MONEY
DECLARE @TotalRevenue MONEY
DECLARE @HotelNumberStr VARCHAR(100)
DECLARE @NumberOfGuests INT
DECLARE @ConferenceDelegates INT
DECLARE @ConferenceRoomRevenue MONEY
DECLARE @ConferenceFBRevenue MONEY
DECLARE @ConferenceTotalRevenue MONEY
DECLARE @HotelNumber INT
DECLARE @HotelName VARCHAR(60)

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), @AStartDate, 112)
SET @EndDateStr = CONVERT(CHAR(8), @AEndDate, 112)
SET @HotelNumberStr = CAST(@ATheHotelNumber AS VARCHAR(100))
IF @HotelNumberStr = '-1'
	SET @HotelNumberStr = ''

DECLARE #db_cur_hotel CURSOR LOCAL FAST_FORWARD FOR
  SELECT 
    (
      SELECT COUNT(*) FROM Rooms (NOLOCK) 
      INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
        AND RoomTypes.HotelNumber = Customer.HotelNumber
    ), Customer.HotelNumber, Customer.LastName
  FROM Customer WITH (NOLOCK)
  WHERE Customer.CustomerTypeNumber = 7
    AND ISNULL(Customer.MarkAsDeleted, 0) = 0
    AND (@HotelNumberStr = '' OR Customer.HotelNumber IN (SELECT Number FROM iter_intlist_to_table_No_Dummy(@HotelNumberStr)))
    AND HotelNumber >= 0
OPEN #db_cur_hotel

FETCH NEXT FROM #db_cur_hotel INTO @TotalRooms, @HotelNumber, @HotelName

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @CurDate = @AStartDate
      
  WHILE @CurDate <= @AEndDate
  BEGIN
    INSERT INTO #HotelTrendResult VALUES (CONVERT(VARCHAR(10), @CurDate, 20), @HotelName, @HotelNumber, @TotalRooms, 0, 0, 0, @ADefaultCurrencyCode, NULL, 0, 0, 0, 0, 0, 0, 0)
      
    SET @CurDate = @CurDate + 1
  END

  FETCH NEXT FROM #db_cur_hotel INTO @TotalRooms, @HotelNumber, @HotelName
END

CLOSE #db_cur_hotel
DEALLOCATE #db_cur_hotel  

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

SELECT 
  #tmpAvailability.DateStamp,
  Customer.LastName, 
  #tmpAvailability.HotelNumber,
  SUM(ISNULL(#tmpAvailability.SoldRooms, 0)) + CASE WHEN @AInclAllotment = 1 THEN SUM(ISNULL(#tmpAvailability.TotalRoomsAllotment, 0)) ELSE 0 END AS SoldRooms,
  SUM(ISNULL(#tmpAvailability.NumberOfOutOfOrder, 0)) AS OOO
INTO #tmp
FROM #tmpAvailability
LEFT OUTER JOIN Customer WITH (NOLOCK) ON #tmpAvailability.HotelNumber = Customer.HotelNumber 
  AND Customer.CustomerTypeNumber = 7
WHERE ISNULL(Customer.MarkAsDeleted, 0) = 0
GROUP BY #tmpAvailability.HotelNumber, Customer.LastName, #tmpAvailability.DateStamp

UPDATE #HotelTrendResult
SET SoldHotelRooms = tmp.SoldRooms,
  OutOfOrderHotelRooms = tmp.OOO
FROM #HotelTrendResult HTR
INNER JOIN #tmp tmp ON CONVERT(VARCHAR(10), CONVERT(DATETIME, tmp.DateStamp, 112), 20) = htr.Date
  AND tmp.HotelNumber = htr.HotelId

SELECT 
  ValueType,
  MISTableView.Datestamp,
  Customer.HotelNumber,

  CASE WHEN (ValueType = 1 AND @RoomTurnoverInclVAT = 1) OR (ValueType <> 1 AND @OtherTurnoverInclVAT = 1) THEN 
    CASE WHEN (ValueType = 1 AND @RoomTurnoverInclPackage = 1) OR (ValueType <> 1 AND @OtherTurnoverInclPackage = 1) THEN
      SUM(MISTableView.BookedPriceInclPackage) + SUM(MISTableView.ActualPriceInclPackage)  -- incl VAT incl Package
    ELSE  
      SUM(MISTableView.BookedPriceExPackage) + SUM(MISTableView.ActualPriceExPackage) -- incl VAT excl Package
    END
  ELSE -- excl VAT
    CASE WHEN (ValueType = 1 AND @RoomTurnoverInclPackage = 1) OR (ValueType <> 1 AND @OtherTurnoverInclPackage = 1) THEN
      SUM(MISTableView.BookedPriceInclPackageExVat) + SUM(MISTableView.ActualPriceInclPackageExVat)  -- Excl VAT incl Package
    ELSE  
      SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.ActualPriceExPackageExVat) -- Excl VAT excl Package
    END
  END AS Turnover,
  SUM(ActualAdult)+SUM(ActualChildren1)+SUM(ActualChildren2)+SUM(BookedAdult)+SUM(BookedChildren1)+SUM(BookedChildren2) AS PAX
INTO #MIS
FROM MISTableView WITH (NOLOCK)
INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = MISTableView.HotelCustomerNumber
  AND (@ATheHotelNumber = -1 OR Customer.HotelNumber = @ATheHotelNumber)  
WHERE MISTableView.Datestamp BETWEEN @AStartDate AND DATEADD(MS, -3, DATEADD(DAY, DATEDIFF(DAY, 0, @AEndDate), 1)) 
AND (@LInclProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
GROUP BY MISTableView.Datestamp, ValueType, Customer.HotelNumber

UPDATE #HotelTrendResult
SET 
  #HotelTrendResult.NumberOfGuests = 
  ISNULL((
    SELECT SUM(PAX) 
    FROM #MIS
    WHERE CONVERT(VARCHAR(10), CONVERT(DATETIME, #MIS.DateStamp, 112), 20) = #HotelTrendResult.Date
    AND #MIS.HotelNumber = #HotelTrendResult.HotelId
    AND ValueType = 1
  ), 0),
  #HotelTrendResult.RevenueHotelRooms = 
  ISNULL((
    SELECT SUM(Turnover)
    FROM #MIS
    WHERE CONVERT(VARCHAR(10), CONVERT(DATETIME, #MIS.DateStamp, 112), 20) = #HotelTrendResult.Date
    AND #MIS.HotelNumber = #HotelTrendResult.HotelId
    AND ValueType = 1
  ), 0),
  #HotelTrendResult.TotalRevenue = 
  ISNULL((
    SELECT SUM(Turnover)
    FROM #MIS
    WHERE CONVERT(VARCHAR(10), CONVERT(DATETIME, #MIS.DateStamp, 112), 20) = #HotelTrendResult.Date
    AND #MIS.HotelNumber = #HotelTrendResult.HotelId
  ), 0),
  #HotelTrendResult.ConferenceDelegates =
  ISNULL((
    SELECT SUM(PAX) 
    FROM #MIS
    WHERE CONVERT(VARCHAR(10), CONVERT(DATETIME, #MIS.DateStamp, 112), 20) = #HotelTrendResult.Date
    AND #MIS.HotelNumber = #HotelTrendResult.HotelId
    AND ValueType IN (2,3)
  ), 0),
  #HotelTrendResult.ConferenceRoomRevenue = 
  ISNULL((
    SELECT SUM(Turnover)
    FROM #MIS
    WHERE CONVERT(VARCHAR(10), CONVERT(DATETIME, #MIS.DateStamp, 112), 20) = #HotelTrendResult.Date
    AND #MIS.HotelNumber = #HotelTrendResult.HotelId
    AND ValueType IN (2,3)
  ), 0),
  #HotelTrendResult.ConferenceFBRevenue =
  ISNULL((
    SELECT SUM(Turnover)
    FROM #MIS
    WHERE CONVERT(VARCHAR(10), CONVERT(DATETIME, #MIS.DateStamp, 112), 20) = #HotelTrendResult.Date
    AND #MIS.HotelNumber = #HotelTrendResult.HotelId
    AND ValueType = 4
  ), 0),
  #HotelTrendResult.ConferenceOtherRevenue = 
  ISNULL((
    SELECT SUM(Turnover)
    FROM #MIS
    WHERE CONVERT(VARCHAR(10), CONVERT(DATETIME, #MIS.DateStamp, 112), 20) = #HotelTrendResult.Date
    AND #MIS.HotelNumber = #HotelTrendResult.HotelId
    AND ValueType = 5
  ), 0)

DROP TABLE #MIS
DROP TABLE #tmp
DROP TABLE #tmpAvailability