CREATE PROCEDURE [dbo].[SalesReportOrdreBook] @AFromDate CHAR(8), @AToDate CHAR(8), @AHotelCustomerNumberStr VARCHAR(8000), @ACustomerNumberStr VARCHAR(8000), @AGroupByCustomer BIT, @AGroupByYear BIT, @AGroupByMonth BIT AS
SET NOCOUNT ON

/*
  Created by Kent Johansen November 2008

  Changes:
    KJ 20090529: Bug: Fix for "A cursor with the name 'db_cur_tentativ' already exists"
    KJ 20090528: Tentativ only shown for month with other res. status
    KJ 20090331: ReservationStatus added

*/

DECLARE @CustomerNumber INT
DECLARE @Year INT
DECLARE @Month INT
DECLARE @SalesManNumber INT

DECLARE @NumberOfRoomsTentativ INT
DECLARE @RoomTurnoverTentativ MONEY
DECLARE @MeetingTentativ MONEY

DECLARE @YearTentativ INT
DECLARE @MonthTentativ INT

SELECT 
  Customer.SalesManNumber,
  CustomerOrdreBook.CustomerNumber,
--  CustomerOrdreBook.DateForValue,
--  CustomerOrdreBook.HotelCustomerNumber,
  DATEPART(year, DateForValue) AS Year, 
  DATEPART(month, DateForValue) AS Month,
  0 AS NumberOfRoomsTentativ,
  0 AS RoomTurnoverTentativ,
  0 AS MeetingTentativ,
  SUM(NumberOfNightRooms) AS NumberOfRooms, 
  SUM(TurnOverRooms) AS RoomTurnover, 
  (SUM(TurnOverDayMeetings) + SUM(TurnOverHalls)) AS TurnoverMeeting,
  SUM(NumberOfCXLRooms) AS NumberOfRoomsCXL
INTO #CustomerOrderBook
FROM CustomerOrdreBook WITH (NOLOCK) 
INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = CustomerOrdreBook.CustomerNumber
  AND ISNULL(Customer.SalesManNumber, 0) > 0
WHERE 1=1 
AND CustomerOrdreBook.CustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ACustomerNumberStr))
AND ((@AHotelCustomerNumberStr = '') OR (CustomerOrdreBook.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberStr))))
AND DateForValue BETWEEN @AFromDate AND @AToDate 
AND CustomerOrdreBook.ReservationStatus NOT IN (3,4) -- no WaitingList 
GROUP BY 
  DATEPART(year, DateForValue), 
  DATEPART(month, DateForValue),
  CustomerOrdreBook.CustomerNumber,
  Customer.SalesManNumber

DECLARE db_cur CURSOR READ_ONLY FOR
  SELECT DISTINCT SalesManNumber, CustomerNumber
  FROM #CustomerOrderBook
OPEN db_cur

FETCH NEXT FROM db_cur INTO @SalesManNumber, @CustomerNumber

WHILE @@FETCH_STATUS = 0
BEGIN
  DECLARE db_cur_tentativ CURSOR FOR
    SELECT 
      SUM(NumberOfNightRooms), 
      SUM(TurnOverRooms), 
      (SUM(TurnOverDayMeetings) + SUM(TurnOverHalls)),
      DATEPART(year, DateForValue) AS Year, 
      DATEPART(month, DateForValue) AS Month
    FROM CustomerOrdreBook WITH (NOLOCK) 
    WHERE CustomerOrdreBook.CustomerNumber = @CustomerNumber
    AND ((@AHotelCustomerNumberStr = '') OR (CustomerOrdreBook.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberStr))))
--    AND DATEPART(year, DateForValue) = @Year
--    AND DATEPART(month, DateForValue) = @Month
    AND CustomerOrdreBook.ReservationStatus = 4 -- Tentativ
    AND DateForValue BETWEEN @AFromDate AND @AToDate 
    GROUP BY 
      DATEPART(year, DateForValue), 
      DATEPART(month, DateForValue)
  OPEN db_cur_tentativ

  FETCH NEXT FROM db_cur_tentativ INTO @NumberOfRoomsTentativ, @RoomTurnoverTentativ, @MeetingTentativ, @YearTentativ, @MonthTentativ

  WHILE @@FETCH_STATUS = 0
  BEGIN
    UPDATE #CustomerOrderBook SET 
      NumberOfRoomsTentativ = ISNULL(NumberOfRoomsTentativ, 0) + ISNULL(@NumberOfRoomsTentativ, 0), 
      RoomTurnoverTentativ = ISNULL(RoomTurnoverTentativ, 0) + ISNULL(@RoomTurnoverTentativ, 0), 
      MeetingTentativ = ISNULL(MeetingTentativ, 0) + ISNULL(@MeetingTentativ, 0)
    WHERE SalesManNumber = @SalesManNumber
    AND CustomerNumber = @CustomerNumber
    AND Year = @YearTentativ
    AND Month = @MonthTentativ

    IF @@rowcount = 0
      INSERT INTO #CustomerOrderBook (SalesManNumber, CustomerNumber, Year, Month, NumberOfRoomsTentativ, RoomTurnoverTentativ, MeetingTentativ, NumberOfRooms, RoomTurnover, TurnoverMeeting, NumberOfRoomsCXL) 
      VALUES (@SalesManNumber, @CustomerNumber, @YearTentativ, @MonthTentativ, @NumberOfRoomsTentativ, @RoomTurnoverTentativ, @MeetingTentativ, 0, 0, 0, 0) 

    FETCH NEXT FROM db_cur_tentativ INTO @NumberOfRoomsTentativ, @RoomTurnoverTentativ, @MeetingTentativ, @YearTentativ, @MonthTentativ
  END  

  FETCH NEXT FROM db_cur INTO @SalesManNumber, @CustomerNumber

  CLOSE db_cur_tentativ
  DEALLOCATE db_cur_tentativ
END

CLOSE db_cur
DEALLOCATE db_cur

IF @AGroupByCustomer = 1
BEGIN
  IF @AGroupByYear = 1
  BEGIN
    SELECT 
      Year,
      #CustomerOrderBook.SalesManNumber,
      #CustomerOrderBook.CustomerNumber,
      (ISNULL(c.FirstName, '') + ' ' + c.LastName) AS CustomerName,
      SUM(NumberOfRoomsTentativ) AS NumberOfRoomsTentativ,
      SUM(RoomTurnoverTentativ) AS RoomTurnoverTentativ,
      SUM(MeetingTentativ) AS MeetingTentativ,
      SUM(NumberOfRooms) AS NumberOfRooms, 
      SUM(RoomTurnover) AS RoomTurnover, 
      SUM(TurnoverMeeting) AS TurnoverMeeting,
      SUM(NumberOfRoomsCXL) AS NumberOfRoomsCXL
    FROM #CustomerOrderBook 
    INNER JOIN Customer c WITH (NOLOCK) On c.CustomerNumber = #CustomerOrderBook.CustomerNumber
    GROUP BY #CustomerOrderBook.CustomerNumber, #CustomerOrderBook.SalesManNumber, Year, c.FirstName, c.LastName
  END
  ELSE
  BEGIN
    SELECT 
      Year, 
      Month,
      #CustomerOrderBook.SalesManNumber,
      #CustomerOrderBook.CustomerNumber,
      (ISNULL(c.FirstName, '') + ' ' + c.LastName) AS CustomerName,
      SUM(NumberOfRoomsTentativ) AS NumberOfRoomsTentativ,
      SUM(RoomTurnoverTentativ) AS RoomTurnoverTentativ,
      SUM(MeetingTentativ) AS MeetingTentativ,
      SUM(NumberOfRooms) AS NumberOfRooms, 
      SUM(RoomTurnover) AS RoomTurnover, 
      SUM(TurnoverMeeting) AS TurnoverMeeting,
      SUM(NumberOfRoomsCXL) AS NumberOfRoomsCXL
    FROM #CustomerOrderBook 
    INNER JOIN Customer c WITH (NOLOCK) On c.CustomerNumber = #CustomerOrderBook.CustomerNumber
    GROUP BY #CustomerOrderBook.CustomerNumber, #CustomerOrderBook.SalesManNumber, Year, Month, c.FirstName, c.LastName
  END
END
ELSE
BEGIN
  IF @AGroupByYear = 1
  BEGIN
    SELECT 
      Year,
      SalesManNumber,
      0 AS CustomerNumber,
      '' AS CustomerName,
      SUM(NumberOfRoomsTentativ) AS NumberOfRoomsTentativ,
      SUM(RoomTurnoverTentativ) AS RoomTurnoverTentativ,
      SUM(MeetingTentativ) AS MeetingTentativ,
      SUM(NumberOfRooms) AS NumberOfRooms, 
      SUM(RoomTurnover) AS RoomTurnover, 
      SUM(TurnoverMeeting) AS TurnoverMeeting,
      SUM(NumberOfRoomsCXL) AS NumberOfRoomsCXL
    FROM #CustomerOrderBook
    GROUP BY SalesManNumber, Year
  END
  ELSE
  BEGIN
    SELECT 
      Year,
      Month,
      SalesManNumber,
      0 AS CustomerNumber,
      '' AS CustomerName,
      SUM(NumberOfRoomsTentativ) AS NumberOfRoomsTentativ,
      SUM(RoomTurnoverTentativ) AS RoomTurnoverTentativ,
      SUM(MeetingTentativ) AS MeetingTentativ,
      SUM(NumberOfRooms) AS NumberOfRooms, 
      SUM(RoomTurnover) AS RoomTurnover, 
      SUM(TurnoverMeeting) AS TurnoverMeeting,
      SUM(NumberOfRoomsCXL) AS NumberOfRoomsCXL
    FROM #CustomerOrderBook
    GROUP BY SalesManNumber, Year, Month
  END
END

DROP TABLE #CustomerOrderBook