CREATE PROCEDURE dbo.RoomsPerChainRoomType @AStartDate CHAR(8), @AEndDate CHAR(8), @AHotelNumberCSV VARCHAR(100), @ASoldRooms SMALLINT AS
SET NOCOUNT ON

/*
  Created by Kent Johansen September 2009

  Changes: 
    KJ 20150317: Bug: Fit changes made in GenerateAvailability
    KJ 20100602: Bug: Fix for "Customer/hotel sometimes marked as deleted"
    KJ 20090929: Allotment wrong.
*/

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
)

CREATE TABLE #Result
(
  HotelNumber INT NOT NULL,
  HotelName VARCHAR(50) NOT NULL,
  Date CHAR(8) COLLATE DATABASE_DEFAULT NOT NULL,
  Total INT
)

DECLARE @ChainRoomTypeNumber INT
DECLARE @sql VARCHAR(8000)

DECLARE db_cur_roomtypes CURSOR READ_ONLY FOR
  SELECT ChainRoomTypeNumber
  FROM ChainRoomTypes WITH (NOLOCK)
  ORDER BY SortOrder
OPEN db_cur_roomtypes

FETCH NEXT FROM db_cur_roomtypes INTO @ChainRoomTypeNumber

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = 'ALTER TABLE #Result ADD ChainRoomType' + CONVERT(VARCHAR, @ChainRoomTypeNumber) + ' SMALLINT'
  EXEC sp_sqlexec @sql

  FETCH NEXT FROM db_cur_roomtypes INTO @ChainRoomTypeNumber
END

CLOSE db_cur_roomtypes
DEALLOCATE db_cur_roomtypes

DECLARE @HotelNumber INT
DECLARE @DateStamp CHAR(8)
DECLARE @RoomTypeNumber INT
DECLARE @LastName VARCHAR(50)
DECLARE @Amount INT
DECLARE @AllotmentTotalChange INT

INSERT INTO #tmpAvailability
  EXEC GenerateAvailability @AStartDate, @AEndDate, 1,  @AHotelNumberCSV, '', 1, 0, ''

IF @ASoldRooms = 1
BEGIN
  DECLARE db_cur CURSOR READ_ONLY FOR
    SELECT #tmpAvailability.HotelNumber, 
      Customer.LastName, 
      #tmpAvailability.DateStamp, 
      SUM(ISNULL(#tmpAvailability.SoldRooms, 0)), 
      ChainRoomTypeNumber,
      RoomTypes.RoomTypeNumber
    FROM #tmpAvailability
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #tmpAvailability.RoomTypeNumber
    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, ChainRoomTypeNumber, RoomTypes.RoomTypeNumber
END
ELSE
BEGIN
  DECLARE db_cur CURSOR READ_ONLY FOR
    SELECT #tmpAvailability.HotelNumber, 
      Customer.LastName, 
      #tmpAvailability.DateStamp, 
      SUM(ISNULL(#tmpAvailability.Adults, 0) + ISNULL(#tmpAvailability.Children1, 0) + ISNULL(#tmpAvailability.Children2, 0)), 
      ChainRoomTypeNumber,
      RoomTypes.RoomTypeNumber
    FROM #tmpAvailability
    INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #tmpAvailability.RoomTypeNumber
    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, ChainRoomTypeNumber, RoomTypes.RoomTypeNumber
END

OPEN db_cur

FETCH NEXT FROM db_cur INTO @HotelNumber, @LastName, @DateStamp, @Amount, @ChainRoomTypeNumber, @RoomTypeNumber

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @ASoldRooms = 1
  BEGIN
    SELECT @AllotmentTotalChange = SUM(InitialCount) + SUM(TotalChange) FROM AllotmentLines WITH (NOLOCK) WHERE DateStamp = @DateStamp AND RoomTypeNumber = @RoomTypeNumber
    SET @Amount = @Amount + ISNULL(@AllotmentTotalChange, 0)
  END
  SET @sql = 'UPDATE #Result SET Total = ISNULL(Total, 0) + ' + CONVERT(VARCHAR, @Amount) + ', ChainRoomType' + CONVERT(VARCHAR, @ChainRoomTypeNumber) + ' = ISNULL(ChainRoomType' + CONVERT(VARCHAR, @ChainRoomTypeNumber) + ', 0) + ' + CONVERT(VARCHAR, @Amount) + ' WHERE Date = ' + @DateStamp + ' AND HotelNumber = ' + CONVERT(VARCHAR, @HotelNumber)
  EXEC sp_sqlexec @sql

  IF @@ROWCOUNT = 0
  BEGIN
    SET @sql = 'INSERT INTO #Result (HotelNumber, HotelName, Date, Total, ChainRoomType' + CONVERT(VARCHAR, @ChainRoomTypeNumber) + ') VALUES (' + CONVERT(VARCHAR, @HotelNumber) + ', ''' + @LastName + ''', ''' + @DateStamp + ''', ' + CONVERT(VARCHAR, @Amount) + ', ' + CONVERT(VARCHAR, @Amount) + ')'
    EXEC sp_sqlexec @sql    
  END
  
  FETCH NEXT FROM db_cur INTO @HotelNumber, @LastName, @DateStamp, @Amount, @ChainRoomTypeNumber, @RoomTypeNumber
END

CLOSE db_cur
DEALLOCATE db_cur

DROP TABLE #tmpAvailability

SELECT * FROM #Result
DROP TABLE #Result