CREATE PROCEDURE dbo.HotelTrendBenchMark @AStartDate DATETIME, @AEndDate DATETIME, @AHotelNumberCSV VARCHAR(100), @AInclAllotment BIT = 1 AS
SET NOCOUNT ON
/*
  Created by Kent Johansen april 2011
  Used by c# Service
  
  Changes:
    KJ 20180221: Ext: Added HotelTrendLog
    KJ 20180210: Bug: Available rooms should bee the total number of rooms.
    KJ 20180209: Ext: Support for hall.
    KJ 20180121: Ext: Added NumberOfGuests
    KJ 20131202: Ext: Added TotalRevenue Req #2160
    KJ 20120705: Opt: Major optimization. No need to call GenerateAvailability per hotel if all hotels should be added.
    KJ 20120627: Bug: Fix for "Export not giving any records when running Domino".
    KJ 20120625: Ext: Also add roomtypes with no rooms attached.
    KJ 20120621: Bug: Fix for "Available rooms wrong".
    KJ 20120614: Ext: Added AIncludeMISData - used for optimize use by RevParGuru req #1269
    KJ 20120511: Ext: Added out for service.
    KJ 20120417: Bug: Fix for "Wrong number of sold rooms". Case #CR0??
    KJ 20120216: Ext: Use MISTableView to select data.
    KJ 20110523: Ext: Exclude Berling Hotel - Hotel can not be deleted in picasso.  
    KJ 20110519: Ext: "Changed the format of date". case# CR024125
    KJ 20110510: Bug: Fix for "Revenue not calculated per hotel"
  
*/

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

SET @AStartDate = CAST(@AStartDate AS DATE)
SET @AEndDate = CAST(@AEndDate AS DATE)

INSERT INTO HotelTrendLog VALUES (GETDATE(), @AStartDate, @AEndDate, @AHotelNumberCSV, @MISCalcRunning)  

CREATE TABLE #HotelTrendResult
(
  Date VARCHAR(10),
  HotelName VARCHAR(500),
  HotelId INT NOT NULL,
  TotalHotelRooms INT,
  SoldHotelRooms INT,
  OutOfOrderHotelRooms INT,
  RevenueHotelRooms MONEY,
  Currency CHAR(3),
  AvailableHotelRooms INT NULL,
  TotalRevenue MONEY,
  NumberOfGuests INT,
  ConferenceDelegates INT,
  ConferenceRoomRevenue MONEY,
  ConferenceFBRevenue MONEY,
  ConferenceOtherRevenue MONEY,
  ConferenceTotalRevenue MONEY
)

DECLARE @TheHotelNumber INT
DECLARE @DefaultCurrencyCode CHAR(3)
DECLARE @HotelCountryNumber INT

IF @AHotelNumberCSV = ''
  SET @AHotelNumberCSV = '-1'

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

IF @AHotelNumberCSV <> '-1'
BEGIN
  DECLARE db_hotelnumber CURSOR FOR
    SELECT DISTINCT Number, LastName
    FROM iter_intlist_to_table_No_Dummy(@AHotelNumberCSV)
    LEFT OUTER JOIN Customer WITH (NOLOCK) ON iter_intlist_to_table_No_Dummy.Number = Customer.HotelNumber
      AND CustomerTypeNumber = 7
  OPEN db_hotelnumber
  
  FETCH NEXT FROM db_hotelnumber INTO @TheHotelNumber
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC DoGetHotelTrendBenchMark @AStartDate, @AEndDate, @TheHotelNumber, @AInclAllotment, @DefaultCurrencyCode
    FETCH NEXT FROM db_hotelnumber INTO @TheHotelNumber
  END

  CLOSE db_hotelnumber
  DEALLOCATE db_hotelnumber
END
ELSE
BEGIN
  EXEC DoGetHotelTrendBenchMark @AStartDate, @AEndDate, -1, @AInclAllotment, @DefaultCurrencyCode
END

SELECT Date, HotelName, HotelId, TotalHotelRooms AS AvailableHotelRooms, OutOfOrderHotelRooms,
  CASE WHEN @MISCalcRunning = 1 THEN
    -1
  ELSE
    RevenueHotelRooms
  END AS RevenueHotelRooms, 
  Currency, 
  SoldHotelRooms, 
  CASE WHEN @MISCalcRunning = 1 THEN
    -1
  ELSE
    ISNULL(RevenueHotelRooms, 0) + ISNULL(ConferenceRoomRevenue, 0) + ISNULL(ConferenceFBRevenue, 0) + ISNULL(ConferenceOtherRevenue, 0) 
  END AS TotalRevenue, 
  NumberOfGuests, 
  ConferenceDelegates, 
  CASE WHEN @MISCalcRunning = 1 THEN
    -1
  ELSE
    ConferenceRoomRevenue
  END AS ConferenceRoomRevenue, 
  CASE WHEN @MISCalcRunning = 1 THEN
    -1
  ELSE
    ConferenceFBRevenue
  END AS ConferenceFBRevenue,
  CASE WHEN @MISCalcRunning = 1 THEN
    -1
  ELSE
    ConferenceOtherRevenue 
  END AS ConferenceOtherRevenue,
  CASE WHEN @MISCalcRunning = 1 THEN
    -1
  ELSE
    ISNULL(ConferenceRoomRevenue, 0) + ISNULL(ConferenceFBRevenue, 0) + ISNULL(ConferenceOtherRevenue, 0)
  END AS ConferenceTotalRevenue
FROM #HotelTrendResult WHERE HotelId <> 15
ORDER BY Date

DROP TABLE #HotelTrendResult