CREATE PROCEDURE [dbo].[BudgetChart] @AStartDate CHAR(8), @AEndDate CHAR(8), @AHotelCustomerNumberStr VARCHAR(100) AS
SET NOCOUNT ON

/*
Changes:
  SG 20130325: Bug: Error in booked and budget when adding a value to NULL
  KJ 20120710: Bug: MISTableView rewrite
  KJ 20120216: Ext: Use MISTableView to select data.
  KJ 20120112: Ext: Use MIS to get booked data.
  KJ 20120112: Bug: Fix for "Amount wrong if turnovergroup or account has been deleted"
*/

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

DECLARE @Month INT
DECLARE @HotelCustomerNumber INT
DECLARE @Amount MONEY

DECLARE @CurrentDateStr CHAR(8)

CREATE TABLE #BudgetChart (
  Month INT NOT NULL,
  HotelCustomerNumber INT NOT NULL,
  Actually MONEY NULL,
  Booked MONEY NULL,
  Budget MONEY NULL
)

SELECT TOP 1 @CurrentDateStr = SystemDate FROM System WITH (NOLOCK)

SELECT SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.BookedOnlyPackageExVat) AS Amount,
DateStamp, HotelCustomerNumber, DepartmentNumber, AccountNumber
INTO #MISTable
FROM MISTableView
GROUP BY DateStamp, HotelCustomerNumber, DepartmentNumber, AccountNumber

-- Actually
DECLARE db_cur CURSOR LOCAl FAST_FORWARD FOR
  SELECT SUBSTRING(Date, 5, 2), AccountDaily.HotelCustomerNumber, (SUM(TurnoverCredit) - SUM(Turnoverdebit))
  FROM AccountDaily WITH (NOLOCK) 
  INNER JOIN Accounts WITH (NOLOCK) ON Accounts.AccountNumber = AccountDaily.AccountNumber
    AND ISNULL(Accounts.TurnoverGroupNumber, 0) <> 0
  INNER JOIN TurnOverGroups WITH (NOLOCK) ON TurnOverGroups.Number = Accounts.TurnOverGroupNumber
  WHERE Date >= @AStartDate
  AND Date <= @AEndDate
  AND Date <= @CurrentDateStr
  AND HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AHotelCustomerNumberStr))
  GROUP BY SUBSTRING(Date, 5, 2), AccountDaily.HotelCustomerNumber
OPEN db_cur

FETCH NEXT FROM db_cur INTO @Month, @HotelCustomerNumber, @Amount

WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO #BudgetChart (Month, HotelCustomerNumber, Actually) VALUES (@Month, @HotelCustomerNumber, @Amount)
  
  FETCH NEXT FROM db_cur INTO @Month, @HotelCustomerNumber, @Amount
END

CLOSE db_cur
DEALLOCATE db_cur

-- Booked
DECLARE db_cur CURSOR LOCAl FAST_FORWARD FOR
  SELECT DATEPART(MONTH, DateStamp), HotelCustomerNumber, SUM(Amount)
  FROM #MISTable WITH (NOLOCK)
  INNER JOIN Accounts WITH (NOLOCK) ON #MISTable.AccountNumber = Accounts.AccountNumber
  INNER JOIN TurnOverGroups WITH (NOLOCK) ON TurnOverGroups.Number = Accounts.TurnOverGroupNumber
  WHERE DateStamp >= @AStartDate
  AND DateStamp <= @AEndDate
  AND DateStamp > @CurrentDateStr
  AND HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AHotelCustomerNumberStr))
  GROUP BY DATEPART(MONTH, DateStamp), HotelCustomerNumber
OPEN db_cur

FETCH NEXT FROM db_cur INTO @Month, @HotelCustomerNumber, @Amount

WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE #BudgetChart SET Booked = ISNULL(Booked, 0) + @Amount 
  WHERE HotelCustomerNumber = @HotelCustomerNumber AND Month = @Month
  
  IF @@ROWCOUNT = 0
    INSERT INTO #BudgetChart (Month, HotelCustomerNumber, Booked) VALUES (@Month, @HotelCustomerNumber, @Amount)
  
  FETCH NEXT FROM db_cur INTO @Month, @HotelCustomerNumber, @Amount
END

CLOSE db_cur
DEALLOCATE db_cur

-- Budget
DECLARE @StartYear INT
DECLARE @StartMonth INT
DECLARE @EndYear INT
DECLARE @EndMonth INT

SET @StartYear = SUBSTRING(@AStartDate, 1, 4)
SET @StartMonth = SUBSTRING(@AStartDate, 5, 2)
SET @EndYear = SUBSTRING(@AEndDate, 1, 4)
SET @EndMonth = SUBSTRING(@AEndDate, 5, 2)

PRINT @StartYear
PRINT @StartMonth
PRINT @EndYear
PRINT @EndMonth

DECLARE db_cur CURSOR LOCAl FAST_FORWARD FOR
  SELECT Month, Customer.CustomerNumber, SUM(Amount)
  FROM BudgetMonth WITH (NOLOCK)
  INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = BudgetMonth.HotelNumber
    AND Customer.CustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AHotelCustomerNumberStr))
  INNER JOIN TurnOverGroups WITH (NOLOCK) ON TurnOverGroups.Number = BudgetMonth.TurnOverGroup
  WHERE ((Year = @StartYear AND Month >= @StartMonth) OR (Year = @EndYear AND Month <= @EndMonth))
  GROUP BY Month, Customer.CustomerNumber
OPEN db_cur

FETCH NEXT FROM db_cur INTO @Month, @HotelCustomerNumber, @Amount

WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE #BudgetChart SET Budget = ISNULL(Budget, 0) + @Amount 
  WHERE HotelCustomerNumber = @HotelCustomerNumber AND Month = @Month
  
  IF @@ROWCOUNT = 0
    INSERT INTO #BudgetChart (Month, HotelCustomerNumber, Budget) VALUES (@Month, @HotelCustomerNumber, @Amount)
  
  FETCH NEXT FROM db_cur INTO @Month, @HotelCustomerNumber, @Amount
END

SELECT *, CONVERT(VARCHAR(100), '') AS MonthName, -1 AS Year
FROM #BudgetChart

DROP TABLE #BudgetChart
DROP TABLE #MISTable