CREATE PROCEDURE [dbo].[SelectCustomerOrderBook] 
  @ACustomerNumber INT, 
  @ADateFrom VARCHAR(8),
  @ADateTo VARCHAR(8), 
  @ASpecBy BIT, 
  @AYearSort BIT, 
  @AMonthSort BIT, 
  @ASegments VARCHAR(MAX), 
  @AHotels VARCHAR(MAX), 
  @AReservationStatuses VARCHAR(128), 
  @ACorrection BIT AS
SET NOCOUNT ON 
/*
  ACustomerNumber : Customer to select OrderBook for
  ADateFrom       : Start OrderBook
  ADateTo         : End OrderBook
  ASpecBy         : 0 = Month, 1 = Year
  AYearSort       : 0 = Ascending, 1 = Descending
  AMonthSort      : 0 = Ascending, 1 = Descending
  AHotels         : OrderBook per hotels
  ASegments       : OrderBook per segments
*/
/*
  Latest changes:
    SG 20161101: CR 113756: Segments and Hotels -> VarChar(MAX)
    VD 20121119: CLN. Housekeeping and optimizing
    VD 20121119: CLN. Housekeeping and optimizing
    OF 20091029: Ext: ContactPersonCustomerNumber in where clause.
    OF 20091005: DownLinks added in query.
    OF 20091002: Changed made for detailed orderbook. Corrections added.
    KJ 20090331: ReservationStatus added
*/

DECLARE 
  @SegmentNumber INT,
  @HotelStr VARCHAR(1000),
  @HotelNumber INT,
  @CorrectionStr VARCHAR(3),
  @HotelCustomerNumber VARCHAR(10)

SET @CorrectionStr = '0,1'
IF @ACorrection = 0
  SET @CorrectionStr = '0'
  
IF @AReservationStatuses = ''  
  SET @AReservationStatuses = '-2'
 
SET @HotelStr = @AHotels
CREATE Table #HotelNumbers(CustomerNumber INT NOT NULL)
CREATE Table #CustomerLinks(CustomerNumber INT NOT NULL)
INSERT INTO #CustomerLinks(CustomerNumber) EXEC FindOrderBookCustomerLinks @ACustomerNumber
INSERT INTO #CustomerLinks(CustomerNumber) VALUES (@ACustomerNumber)

IF @HotelStr <> ''
begin
  Declare @Pos INT

  SET @HotelStr = LTRIM(RTRIM(@HotelStr))+ ','
  SET @Pos = CHARINDEX(',', @HotelStr, 1)

  IF REPLACE(@HotelStr, ',', '') <> ''
  BEGIN
    WHILE @Pos > 0
    BEGIN
      SET @HotelCustomerNumber = LTRIM(RTRIM(LEFT(@HotelStr, @Pos - 1)))

      IF @HotelCustomerNumber <> ''
        INSERT INTO #HotelNumbers (CustomerNumber) VALUES (CAST(@HotelCustomerNumber AS INT)) --Use Appropriate conversion
      SET @HotelStr = RIGHT(@HotelStr, LEN(@HotelStr) - @Pos)
      SET @Pos = CHARINDEX(',', @HotelStr, 1)
    END
  END
end
else
begin
  Declare CustnumCurs CURSOR FAST_FORWARD FOR 
    SELECT convert(VARCHAR, Customer.CustomerNumber) FROM Customer WITH (NOLOCK) WHERE CustomerTypeNumber=7	
  Open CustnumCurs

  Fetch next from CustnumCurs INTO @HotelCustomerNumber

  while @@fetch_status <> -1
  begin
    INSERT INTO #HotelNumbers (CustomerNumber) VALUES (CAST(@HotelCustomerNumber AS INT))

    Fetch next from CustnumCurs INTO @HotelCustomerNumber
  end
  CLOSE CustnumCurs
  DEALLOCATE CustnumCurs
end

IF NOT EXISTS(SELECT * FROM #HotelNumbers WHERE CustomerNumber = -1)
  INSERT INTO #HotelNumbers VALUES (-1) 

SELECT 
  CustomerOrdreBook.CustomerNumber, 
  CustomerOrdreBook.SegmentNumber, 
  CustomerOrdreBook.DateForValue,
  CustomerOrdreBook.HotelCustomerNumber,
  DATEPART(YEAR, DateForValue) AS Year, 
  DATEPART(MONTH, DateForValue) AS Month,
  SUM(TurnOverRooms) AS TurnOverRooms, 
  SUM(NumberOfNightRooms) AS NumberOfNightRooms, 
  SUM(NumberOfPaxRooms) AS NumberOfPaxRooms, 
  SUM(NumberOfStaysRooms) AS NumberOfStaysRooms, 
  SUM(NumberOfCXLRooms) AS NumberOfCXLRooms, 
  SUM(NumberOfNoShowRooms) AS NumberOfNoShowRooms, 
  (CASE WHEN SUM(NumberOfNightRooms) = 0 THEN 0 ELSE SUM(TurnOverRooms) / SUM(NumberOfNightRooms) END) AS AVGTurnOverRooms, 
  SUM(TurnOverDayMeetings) AS TurnOverDayMeetings, 
  SUM(NumberOfStaysDayMeetings) AS NumberOfStaysDayMeetings, 
  SUM(NumberOfPaxDayMeetings) AS NumberOfPaxDayMeetings, 
  SUM(TurnOverHalls) AS TurnOverHalls, 
  SUM(NumberOfHalls) AS NumberOfHalls, 
  SUM(NumberOfPaxHalls) AS NumberOfPaxHalls, 
  SUM(TurnOverRestaurant) AS TurnOverRestaurant, 
  SUM(TurnOverOther) AS TurnOverOther,
  SUM(TurnOverRooms + TurnOverDayMeetings + TurnOverHalls + TurnOverRestaurant + TurnOverOther) AS TurnOverTotal
INTO #CustomerOrderBook
FROM CustomerOrdreBook WITH (NOLOCK) 
  JOIN Segments WITH (NOLOCK) ON Segments.Number = CustomerOrdreBook.SegmentNumber
  JOIN #CustomerLinks WITH (NOLOCK) ON #CustomerLinks.CustomerNumber IN 
   (CustomerOrdreBook.CustomerNumber, CustomerOrdreBook.ContactPersonCustomerNumber)  
WHERE 
  CustomerOrdreBook.DateForValue BETWEEN @ADateFrom AND @ADateTo 
  AND CustomerOrdreBook.SegmentNumber IN (SELECT Value FROM SplitNumberList(@ASegments))     
  AND CustomerOrdreBook.HotelCustomerNumber IN (SELECT CustomerNumber FROM #HotelNumbers)
  AND CustomerOrdreBook.ReservationStatus IN (SELECT Value FROM SplitNumberList(@AReservationStatuses))
  AND ISNULL(CustomerOrdreBook.Correction, 0) IN (SELECT Value FROM SplitNumberList(@CorrectionStr))     
GROUP BY 
  CustomerOrdreBook.SegmentNumber, 
  DATEPART(YEAR, DateForValue), 
  DATEPART(MONTH, DateForValue),
  CustomerOrdreBook.CustomerNumber,
  Segments.Text,
  DateForValue,
  HotelCustomerNumber

DROP TABLE #HotelNumbers
DROP TABLE #CustomerLinks

/***** FILL IN THE BLANKS *****/

DECLARE
  @TmpSegmentNumber INT,
  @TmpYear INT, 
  @TmpMonth INT,
  @CurSegmentNumber INT,
  @CurYear INT,
  @CurMonth INT,
  @FirstPass BIT,
  @CurDateForValue VARCHAR(8),
  @CurHotelCustomerNumber INT

DECLARE CurCustomerOrderBook CURSOR FAST_FORWARD FOR 
  SELECT SegmentNumber, Year, Month, DateForValue, HotelCustomerNumber 
  FROM #CustomerOrderBook WITH (NOLOCK) 
  ORDER BY SegmentNumber, Year, Month

SET @TmpSegmentNumber = 0
SET @TmpYear = DATEPART(YEAR, @ADateFrom)
SET @TmpMonth = 1
SET @FirstPass = 1

OPEN CurCustomerOrderBook

FETCH NEXT FROM CurCustomerOrderBook INTO @CurSegmentNumber, @CurYear, @CurMonth, @CurDateForValue, @CurHotelCustomerNumber

WHILE @@FETCH_STATUS<>-1
BEGIN
  IF @TmpSegmentNumber <> @CurSegmentNumber 
  BEGIN
    -- Segment changed! 
    IF @FirstPass = 0
    BEGIN
      -- Fill in the missing last part of the previous segment
	  WHILE (@TmpYear <> DATEPART(YEAR, @ADateTo)) OR (@TmpMonth <> 12)
	  BEGIN
	    IF (@TmpMonth = 12)
	    BEGIN
	      SET @TmpYear = @TmpYear + 1
	      SET @TmpMonth = 1
	    END
	    ELSE
	      SET @TmpMonth = @TmpMonth + 1
	
	    INSERT INTO #CustomerOrderBook VALUES (@ACustomerNumber, @TmpSegmentNumber, @CurDateForValue, @CurHotelCustomerNumber, @TmpYear, @TmpMonth, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
	  END
    END

    SET @TmpSegmentNumber = @CurSegmentNumber
    SET @TmpYear = DATEPART(YEAR, @ADateFrom)
    SET @TmpMonth = 1
    SET @FirstPass = 1
  END

  -- Fill in the missing parts
  WHILE (@CurYear <> @TmpYear) OR (@CurMonth <> @TmpMonth)
  BEGIN
    IF @FirstPass = 1
      INSERT INTO #CustomerOrderBook VALUES (@ACustomerNumber, @TmpSegmentNumber, @CurDateForValue, @CurHotelCustomerNumber, @TmpYear, @TmpMonth, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

    IF (@TmpMonth = 12)
    BEGIN
      SET @TmpYear = @TmpYear + 1
      SET @TmpMonth = 1
    END
    ELSE
      SET @TmpMonth = @TmpMonth + 1

    IF (@FirstPass = 0) AND ((@CurYear <> @TmpYear) OR (@CurMonth <> @TmpMonth))
      INSERT INTO #CustomerOrderBook VALUES (@ACustomerNumber, @TmpSegmentNumber, @CurDateForValue, @CurHotelCustomerNumber, @TmpYear, @TmpMonth, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
  END

  SET @TmpSegmentNumber = @CurSegmentNumber
  SET @TmpYear = @CurYear
  SET @TmpMonth = @CurMonth  
  SET @FirstPass = 0
  
  FETCH NEXT FROM CurCustomerOrderBook INTO @CurSegmentNumber, @CurYear, @CurMonth, @CurDateForValue, @CurHotelCustomerNumber
END

IF @TmpSegmentNumber <> 0
BEGIN
  WHILE (@TmpYear <> DATEPART(YEAR, @ADateTo)) OR (@TmpMonth <> 12)
  BEGIN
    IF (@TmpMonth = 12)
    BEGIN
      SET @TmpYear = @TmpYear + 1
      SET @TmpMonth = 1
    END
    ELSE
      SET @TmpMonth = @TmpMonth + 1
	
    INSERT INTO #CustomerOrderBook VALUES (@ACustomerNumber, @TmpSegmentNumber, @CurDateForValue, @CurHotelCustomerNumber, @TmpYear, @TmpMonth, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
  END 
END

CLOSE CurCustomerOrderBook
DEALLOCATE CurCustomerOrderBook

IF @ASpecBy = 1
BEGIN
  DECLARE 
    @CustomerNumber INT,
    @Total money,
    @Year INT

  DECLARE CurCustomerOrderBook CURSOR FAST_FORWARD FOR 
    SELECT CustomerNumber, SegmentNumber, Year, 
      SUM(TurnOverRooms + NumberOfNightRooms + NumberOfPaxRooms + NumberOfStaysRooms + NumberOfCXLRooms + NumberOfNoShowRooms + 
        TurnOverDayMeetings + NumberOfStaysDayMeetings + NumberOfPaxDayMeetings + TurnOverHalls + NumberOfHalls + NumberOfPaxHalls +
        TurnOverRestaurant + TurnOverOther) AS Total 
    FROM #CustomerOrderBook WITH (NOLOCK)
    GROUP BY CustomerNumber, SegmentNumber, Year

  OPEN CurCustomerOrderBook

  FETCH NEXT FROM CurCustomerOrderBook INTO @CustomerNumber, @SegmentNumber, @Year, @Total
  WHILE @@FETCH_STATUS <> -1
  BEGIN
    IF @Total = 0
      DELETE FROM #CustomerOrderBook WHERE CustomerNumber = @CustomerNumber AND SegmentNumber = @SegmentNumber AND Year = @Year
    FETCH NEXT FROM CurCustomerOrderBook INTO @CustomerNumber, @SegmentNumber, @Year, @Total
  END

  CLOSE CurCustomerOrderBook
  DEALLOCATE CurCustomerOrderBook
END 

/***** SELECT AND ORDER THE SPECIFIED RESULT *****/

DECLARE
  @SelectStmt VARCHAR(1000),
  @FromStmt VARCHAR(1000),
  @WhereStmt VARCHAR(1000),
  @GroupByStmt VARCHAR(1000),
  @OrderByStmt VARCHAR(1000),
  @MemTableName VARCHAR(100)

SET @MemTableName = '#CustomerOrderBook'
SET @SelectStmt = 'SELECT '
--SET @SelectStmt = @SelectStmt + '#CustomerOrderBook.CustomerNumber, '
IF @ASegments <> ''
  SET @SelectStmt = @SelectStmt + '#CustomerOrderBook.SegmentNumber, ' +
    'Segments.Text AS SegmentName, '
SET @SelectStmt = @SelectStmt + 'Year, '
IF @ASpecBy = 1
  SET @SelectStmt = @SelectStmt + 'Month, '

SET @SelectStmt = @SelectStmt +
  'SUM(TurnOverRooms) AS TurnOverRooms, ' +
  'SUM(NumberOfNightRooms) AS NumberOfNightRooms, ' +
  'SUM(NumberOfPaxRooms) AS NumberOfPaxRooms, ' +
  'SUM(NumberOfStaysRooms) AS NumberOfStaysRooms, ' +
  'SUM(NumberOfCXLRooms) AS NumberOfCXLRooms, ' +
  'SUM(NumberOfNoShowRooms) AS NumberOfNoShowRooms, ' +
  '(CASE WHEN SUM(NumberOfNightRooms) = 0 THEN 0 ELSE SUM(TurnOverRooms) / SUM(NumberOfNightRooms) END) AS AVGTurnOverRooms, ' +
  'SUM(TurnOverDayMeetings) AS TurnOverDayMeetings, ' +
  'SUM(NumberOfStaysDayMeetings) AS NumberOfStaysDayMeetings, ' +
  'SUM(NumberOfPaxDayMeetings) AS NumberOfPaxDayMeetings, ' +
  'SUM(TurnOverHalls) AS TurnOverHalls, ' +
  'SUM(NumberOfHalls) AS NumberOfHalls, ' +
  'SUM(NumberOfPaxHalls) AS NumberOfPaxHalls, ' +
  'SUM(TurnOverRestaurant) AS TurnOverRestaurant, ' +
  'SUM(TurnOverOther) AS TurnOverOther, ' +
  'SUM(TurnOverTotal) AS TurnOverTotal , ' +
  '0 AS BonusPoints'

SET @FromStmt = 'FROM #CustomerOrderBook WITH (NOLOCK) ' 
IF @ASegments <> ''
  SET @FromStmt = @FromStmt + 'JOIN Segments WITH (NOLOCK) ON #CustomerOrderBook.SegmentNumber = Segments.Number'

SET @GroupByStmt = 'GROUP BY '
IF @ASegments <> ''
  SET @GroupByStmt = @GroupByStmt + 'SegmentNumber, Segments.Text, '
SET @GroupByStmt = @GroupByStmt + 'Year '
IF @ASpecBy = 1
  SET @GroupByStmt = @GroupByStmt + ', Month '
  
--SET @GroupByStmt = @GroupByStmt + ', #CustomerOrderBook.CustomerNumber '
--IF @ASegments <> ''
--  SET @GroupByStmt = @GroupByStmt + ', Segments.Text '

SET @OrderByStmt = 'ORDER BY '
IF @ASegments <> ''
  SET @OrderByStmt = @OrderByStmt + '#CustomerOrderBook.SegmentNumber, '
SET @OrderByStmt = @OrderByStmt + 'YEAR '
IF @AYearSort = 1
  SET @OrderByStmt = @OrderByStmt + 'DESC '

IF @ASpecBy = 1
BEGIN
  SET @OrderByStmt = @OrderByStmt + ', MONTH '
  IF @AMonthSort = 1
    SET @OrderByStmt = @OrderByStmt + 'DESC '
END

EXEC (@SelectStmt + ' ' + @FromStmt + ' ' + @WhereStmt + ' ' + @GroupByStmt + ' ' + @OrderByStmt)
DROP TABLE #CustomerOrderBook
SET NOCOUNT OFF
