CREATE PROCEDURE DoGetHotelTrendBenchMark @AStartDate DATETIME, @AEndDate DATETIME, @ATheHotelNumber INT, @AHotelName VARCHAR(500), @APerRoomType BIT, 
  @AInclAllotment BIT, @AIncludeMISData BIT, @ADefaultCurrencyCode CHAR(3) AS
SET NOCOUNT ON

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

    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 20150317: Bug: Fit changes made in GenerateAvailability
    KJ 20131202: Ext: Added TotalRevenue Req #2160
*/

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

DECLARE @TotalRooms INT
DECLARE @RoomTypeName VARCHAR(10)
DECLARE @CurDate DATETIME
DECLARE @StartDateStr CHAR(8)
DECLARE @EndDateStr CHAR(8)
DECLARE @Date DATETIME
DECLARE @HotelNumber INT
DECLARE @SoldRooms INT
DECLARE @OOORooms INT
DECLARE @RoomTypeNumber INT
DECLARE @RevenueHotelRooms MONEY
DECLARE @TotalRevenue MONEY
DECLARE @HotelNumberStr VARCHAR(100)

CREATE TABLE #tmpAvailability
(
  DateStamp CHAR(8) COLLATE DATABASE_DEFAULT NOT NULL,
  RoomTypeNumber INT NOT NULL,
  SegmentNumber INT NOT NULL,
  PointOfSaleNumber INT NOT NULL,
  HotelNumber INT NOT NULL,
  SoldRoomType 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,
  NumberOfNotSaleable SMALLINT NULL ,
  NumberOfOccupiedGroup SMALLINT NULL,
  SoldPrice MONEY 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 = ''

IF @APerRoomType = 1
BEGIN
  DECLARE db_cur_roomtypes CURSOR READ_ONLY FOR
    SELECT (SELECT COUNT(*) FROM Rooms r WITH (NOLOCK) WHERE r.RoomTypeNumber = Rooms.RoomTypeNumber), RoomTypes.Type
    FROM RoomTypes WITH (NOLOCK)
    LEFT OUTER JOIN Rooms WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
    WHERE RoomTypes.HotelNumber = @ATheHotelNumber 
    GROUP BY RoomTypes.Type, Rooms.RoomTypeNumber
  OPEN db_cur_roomtypes
      
  FETCH NEXT FROM db_cur_roomtypes INTO @TotalRooms, @RoomTypeName
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @CurDate = @AStartDate
      
    WHILE @CurDate <= @AEndDate
    BEGIN
      INSERT INTO #HotelTrendResult VALUES (CONVERT(VARCHAR(10), @CurDate, 20), @AHotelName, @ATheHotelNumber, @TotalRooms, 0, 0, 0, @ADefaultCurrencyCode, @RoomTypeName, NULL, 0)
      
      SET @CurDate = @CurDate + 1
    END
      
    FETCH NEXT FROM db_cur_roomtypes INTO @TotalRooms, @RoomTypeName
  END
  CLOSE db_cur_roomtypes
  DEALLOCATE db_cur_roomtypes
END
  
DELETE FROM #tmpAvailability
  
INSERT INTO #tmpAvailability
  EXEC GenerateAvailability @StartDateStr, @EndDateStr, 1, @HotelNumberStr, '', 1, 0, ''

IF @APerRoomType = 0
BEGIN
  IF @AInclAllotment = 1
  BEGIN
    DECLARE db_cur CURSOR READ_ONLY FOR
      SELECT 
        #tmpAvailability.DateStamp,
        Customer.LastName, 
        #tmpAvailability.HotelNumber,
        SUM(ISNULL(#tmpAvailability.SoldRooms, 0))+SUM(ISNULL(#tmpAvailability.TotalRoomsAllotment, 0)),
        SUM(ISNULL(#tmpAvailability.NumberOfOutOfOrder, 0))
      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
  END
  ELSE
  BEGIN
    DECLARE db_cur CURSOR READ_ONLY FOR
      SELECT 
        #tmpAvailability.DateStamp,
        Customer.LastName, 
        #tmpAvailability.HotelNumber,
        SUM(ISNULL(#tmpAvailability.SoldRooms, 0)),
        SUM(ISNULL(#tmpAvailability.NumberOfOutOfOrder, 0))
      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
  END    
END
ELSE
BEGIN
  IF @AInclAllotment = 1
  BEGIN
    DECLARE db_cur CURSOR READ_ONLY FOR
      SELECT 
        #tmpAvailability.DateStamp,
        #tmpAvailability.HotelNumber,
        SUM(ISNULL(#tmpAvailability.SoldRooms, 0))+SUM(ISNULL(#tmpAvailability.TotalRoomsAllotment, 0)),
        SUM(ISNULL(#tmpAvailability.NumberOfOutOfOrder, 0)),
        #tmpAvailability.RoomTypeNumber,
        RoomTypes.Type
      FROM #tmpAvailability
      INNER JOIN RoomTypes ON RoomTypes.RoomTypeNumber = #tmpAvailability.RoomTypeNumber
      GROUP BY #tmpAvailability.HotelNumber, #tmpAvailability.DateStamp, #tmpAvailability.RoomTypeNumber, RoomTypes.Type
  END
  ELSE
  BEGIN
    DECLARE db_cur CURSOR READ_ONLY FOR
      SELECT 
        #tmpAvailability.DateStamp,
        #tmpAvailability.HotelNumber,
        SUM(ISNULL(#tmpAvailability.SoldRooms, 0)),
        SUM(ISNULL(#tmpAvailability.NumberOfOutOfOrder, 0)),
        #tmpAvailability.RoomTypeNumber,
        RoomTypes.Type
      FROM #tmpAvailability
      INNER JOIN RoomTypes ON RoomTypes.RoomTypeNumber = #tmpAvailability.RoomTypeNumber
      WHERE ISNULL(Customer.MarkAsDeleted, 0) = 0
      GROUP BY #tmpAvailability.HotelNumber, #tmpAvailability.DateStamp, #tmpAvailability.RoomTypeNumber, RoomTypes.Type
  END    
END
  
OPEN db_cur

IF @APerRoomType = 0
  FETCH NEXT FROM db_cur INTO @Date, @AHotelName, @HotelNumber, @SoldRooms, @OOORooms
ELSE
  FETCH NEXT FROM db_cur INTO @Date, @HotelNumber, @SoldRooms, @OOORooms, @RoomTypeNumber, @RoomTypeName
  
WHILE @@FETCH_STATUS = 0
BEGIN
  IF @APerRoomType = 0
    SELECT @TotalRooms = COUNT(*)
    FROM Rooms WITH (NOLOCK)
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
      AND RoomTypes.HotelNumber = @HotelNumber 
  ELSE
    SELECT @TotalRooms = COUNT(*)
    FROM Rooms WITH (NOLOCK)
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
      AND RoomTypes.HotelNumber = @HotelNumber 
      AND RoomTypes.RoomTypeNumber = @RoomTypeNumber
    
  IF @AIncludeMISData = 1
  BEGIN
    SELECT @RevenueHotelRooms = SUM(ActualPriceExPackageExVat) + SUM(BookedPriceExPackageExVat)  
    FROM MISTableView WITH (NOLOCK)
    INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = MISTableView.HotelCustomerNumber
      AND (@HotelNumber = -1 OR Customer.HotelNumber = @HotelNumber)
    WHERE MISTableView.Datestamp = @Date
    AND ValueType = 1 --AND MISTableView.MISData IN (1)
    
    SELECT @TotalRevenue = SUM(ActualPriceExPackageExVat) + SUM(BookedPriceExPackageExVat)  
    FROM MISTableView WITH (NOLOCK)
    INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = MISTableView.HotelCustomerNumber
      AND (@HotelNumber = -1 OR Customer.HotelNumber = @HotelNumber)
    WHERE MISTableView.Datestamp = @Date
  END
  ELSE
  BEGIN
    SET @RevenueHotelRooms = 0
    SET @TotalRevenue = 0
  END    

  IF @APerRoomType = 0
  BEGIN
    INSERT INTO #HotelTrendResult VALUES (CONVERT(VARCHAR(10), @Date, 20), @AHotelName, @HotelNumber, @TotalRooms, @SoldRooms, @OOORooms, @RevenueHotelRooms, @ADefaultCurrencyCode, '', NULL, @TotalRevenue)
    FETCH NEXT FROM db_cur INTO @Date, @AHotelName, @HotelNumber, @SoldRooms, @OOORooms
  END  
  ELSE
  BEGIN
    UPDATE #HotelTrendResult SET SoldHotelRooms = @SoldRooms, OutOfOrderHotelRooms = @OOORooms, RevenueHotelRooms = @RevenueHotelRooms, TotalRevenue = @TotalRevenue
    WHERE Date = CONVERT(VARCHAR(10), @Date, 20) 
    AND HotelId = @HotelNumber
    AND RoomType = @RoomTypeName

    FETCH NEXT FROM db_cur INTO @Date, @HotelNumber, @SoldRooms, @OOORooms, @RoomTypeNumber, @RoomTypeName
  END
END  
  
CLOSE db_cur
DEALLOCATE db_cur

DROP TABLE #tmpAvailability