CREATE PROCEDURE dbo.BudgetGrid2 @ARunDate DATETIME, @ATurnOverGroupCSV VARCHAR(500), @AHotelCustomerNumberCSV VARCHAR(500), @AHotelNumberCSV VARCHAR(500) AS
/*
  Created by Kent Johansen March 2017
  Get data for budget grid 2 plan/report.

  Todo:
  This is first version where we do not have all data. 
    Lets say that report is generated 27. Marts 2017 then
    - ActualTurnoverLastYearSameDate: 
        For Jan+Feb this should contain actual turnover.
        For March this should contain actual turnover fra 1. march to 27. march and Booked from 28. march to 31. march
        For april-dec this should contain booked per 27. march

  Changes:
    KJ 20180220: Due to missing old data, then data is generated manually, then som strange compare to system date is needed
    KJ 20180126: Added Turnover last year same date
    KJ 20170530: Bug: Fix for "Wrong amount for date's in the future."
*/
SET NOCOUNT ON

CREATE TABLE #Result
(
  MonthIdx INT,
  TurnoverGroupNumber INT NULL,
  ActualTurnover INT NULL,
  BookedTurnover INT NULL,
  AdditionalTurnover INT NULL,
  ActualTurnoverLastYear INT NULL,
  ActualTurnoverLastYearSameDateAcc INT NULL,
  ActualTurnoverLastYearSameDateMIS INT NULL,
  Budget INT NULL
)

DECLARE @FinacialYearStart DATETIME
DECLARE @FinacialYearEnd DATETIME
DECLARE @LastFinacialYearStart DATETIME
DECLARE @LastFinacialYearEnd DATETIME

DECLARE @MonthIdx INT
DECLARE @Turnover INT
DECLARE @TurnoverGroupNumber INT
DECLARE @Ratio FLOAT
DECLARE @RatioInPercent BIT
DECLARE @YearMonth VARCHAR(8)
DECLARE @RunYear INT
DECLARE @CurrentYear INT
DECLARE @RunYearLastYear INT
DECLARE @CurrentYearLastYear INT

DECLARE @CurrentDateStr CHAR(8)
DECLARE @SystemDate DATETIME
DECLARE @SystemDateLastYear DATETIME
DECLARE @RunDateLastYear DATETIME
DECLARE @RunDateLastYearHistory DATETIME

CREATE TABLE #TurnoverGroups
(
  Number INT NULL
)

CREATE TABLE #HotelCustomerNumbers
(
  Number INT NULL
)
CREATE TABLE #HotelNumbers
(
  Number INT NULL
)

SELECT @CurrentDateStr = SystemDate FROM System WITH (NOLOCK) WHERE NUMBER = 1
SET @SystemDate = CONVERT(DATETIME, @CurrentDateStr)
SET @SystemDateLastYear = DATEADD(YEAR, -1, @SystemDate)

SET @FinacialYearStart = CAST(DATEPART(YEAR, @ARunDate) AS CHAR(4)) + '0101 00:00:00.000'
SET @FinacialYearEnd = CAST(DATEPART(YEAR, @ARunDate) AS CHAR(4)) + '1231 23:59:59.997'

SET @LastFinacialYearStart = DATEADD(YEAR, -1, @FinacialYearStart)
SET @LastFinacialYearEnd = DATEADD(YEAR, -1, @FinacialYearEnd)

SET @RunDateLastYear = DATEADD(YEAR, -1, @ARunDate)

SELECT TOP 1 @RunDateLastYearHistory = SystemDate 
FROM MISHistory WITH (NOLOCK) 
WHERE SystemDate <= CAST(@RunDateLastYear AS DATE)
ORDER BY SystemDate DESC

SET @RunYear = DATEPART(YEAR, @FinacialYearStart)
SET @CurrentYear = DATEPART(YEAR, @SystemDate)

SET @RunYearLastYear = @RunYear - 1
SET @CurrentYearLastYear = @CurrentYear - 1

SET @ATurnOverGroupCSV = ISNULL(@ATurnOverGroupCSV, '')

IF @ATurnOverGroupCSV <> ''
BEGIN
  INSERT INTO #TurnoverGroups
  SELECT Number FROM DBO.iter_intlist_to_table_No_Dummy(@ATurnOverGroupCSV)
END

IF @AHotelCustomerNumberCSV <> ''
BEGIN
  INSERT INTO #HotelCustomerNumbers
  SELECT Number FROM DBO.iter_intlist_to_table_No_Dummy(@AHotelCustomerNumberCSV)
END

IF @AHotelNumberCSV <> ''
BEGIN
  INSERT INTO #HotelNumbers
  SELECT Number FROM DBO.iter_intlist_to_table_No_Dummy(@AHotelNumberCSV)
END

SELECT SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.BookedOnlyPackageExVat) AS Amount,
  MISTableView.DateStamp, MISTableView.HotelCustomerNumber, MISTableView.DepartmentNumber, MISTableView.AccountNumber
INTO #MISTable
FROM MISTableView
INNER JOIN Accounts WITH (NOLOCK) ON Accounts.AccountNumber = MISTableView.AccountNumber
  AND (@ATurnOverGroupCSV = '' OR Accounts.TurnOverGroupNumber IN (SELECT Number FROM #TurnoverGroups))
WHERE DateStamp >= @CurrentDateStr
GROUP BY MISTableView.DateStamp, MISTableView.HotelCustomerNumber, MISTableView.DepartmentNumber, MISTableView.AccountNumber, MISTableView.ProductWareLinkNumber
HAVING ISNULL(SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.BookedOnlyPackageExVat), 0) <> 0

-- Year turnover actual
DECLARE #db_cur CURSOR LOCAL FAST_FORWARD FOR
  SELECT DATEPART(MONTH, CONVERT(DATETIME, AccountDaily.Date, 112)) AS MonthIdx, SUM(Turnoverdebit) - SUM(TurnoverCredit)
  FROM AccountDaily WITH (NOLOCK) 
  INNER JOIN Accounts WITH (NOLOCK) ON AccountDaily.AccountNumber = Accounts.AccountNumber 
    AND (@ATurnOverGroupCSV = '' OR Accounts.TurnoverGroupNumber IN (SELECT Number FROM #TurnoverGroups))
    AND Accounts.TurnoverGroupNumber > 0
  WHERE (@AHotelCustomerNumberCSV = '' OR AccountDaily.HotelCustomerNumber IN (SELECT Number FROM #HotelCustomerNumbers))
  AND ((@RunYear = @CurrentYear AND CONVERT(DATETIME, AccountDaily.Date, 112) < @SystemDate) or (@RunYear < @CurrentYear))
  AND CONVERT(DATETIME, AccountDaily.Date, 112) BETWEEN @FinacialYearStart AND @FinacialYearEnd
  GROUP BY DATEPART(MONTH, CONVERT(DATETIME, AccountDaily.Date, 112))
OPEN #db_cur  

FETCH NEXT FROM #db_cur INTO @MonthIdx, @Turnover

WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO #Result (MonthIdx, ActualTurnover) VALUES (@MonthIdx, @Turnover)
  FETCH NEXT FROM #db_cur INTO @MonthIdx, @Turnover
END

CLOSE #db_cur
DEALLOCATE #db_cur

-- Year turnover same date last year
DECLARE #db_cur CURSOR LOCAL FAST_FORWARD FOR
  SELECT DATEPART(MONTH, CONVERT(DATETIME, AccountDaily.Date, 112)) AS MonthIdx, SUM(Turnoverdebit) - SUM(TurnoverCredit)
  FROM AccountDaily WITH (NOLOCK) 
  INNER JOIN Accounts WITH (NOLOCK) ON AccountDaily.AccountNumber = Accounts.AccountNumber 
    AND (@ATurnOverGroupCSV = '' OR Accounts.TurnoverGroupNumber IN (SELECT Number FROM #TurnoverGroups))
    AND Accounts.TurnoverGroupNumber > 0
  WHERE (@AHotelCustomerNumberCSV = '' OR AccountDaily.HotelCustomerNumber IN (SELECT Number FROM #HotelCustomerNumbers))

  -- Due to missing old data, then data is generated manually, then som strange compare to system date is needed
  AND ((@CurrentDateStr <= '20190215' AND CONVERT(DATETIME, AccountDaily.Date, 112) < DATEADD(MONTH, DATEDIFF(MONTH, 0, @RunDateLastYearHistory), 0)) OR
    (@CurrentDateStr > '20190215' AND CONVERT(DATETIME, AccountDaily.Date, 112) < @RunDateLastYear)) 
  AND CONVERT(DATETIME, AccountDaily.Date, 112) BETWEEN @LastFinacialYearStart AND @LastFinacialYearEnd
  GROUP BY DATEPART(MONTH, CONVERT(DATETIME, AccountDaily.Date, 112))
OPEN #db_cur  

FETCH NEXT FROM #db_cur INTO @MonthIdx, @Turnover

WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO #Result (MonthIdx, ActualTurnoverLastYearSameDateAcc) VALUES (@MonthIdx, @Turnover)
  FETCH NEXT FROM #db_cur INTO @MonthIdx, @Turnover
END

CLOSE #db_cur
DEALLOCATE #db_cur

DECLARE #db_cur CURSOR LOCAL FAST_FORWARD FOR
  SELECT DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112)) AS MonthIdx, ISNULL(SUM(MISHistory.Price),0)-ISNULL(SUM(MISHistory.VATOfPrice),0)
  FROM MISHistory WITH (NOLOCK) 
  INNER JOIN Accounts WITH (NOLOCK) ON MISHistory.FK_AccountNumber = Accounts.AccountNumber 
    AND (@ATurnOverGroupCSV = '' OR Accounts.TurnoverGroupNumber IN (SELECT Number FROM #TurnoverGroups))
    AND Accounts.TurnoverGroupNumber > 0
  WHERE (@AHotelCustomerNumberCSV = '' OR MISHistory.FK_HotelCustomerNumber IN (SELECT Number FROM #HotelCustomerNumbers))
  AND MISHistory.SystemDate = @RunDateLastYearHistory

  -- Due to missing old data, then data is generated manually, then som strange compare to system date is needed
  AND ((@CurrentDateStr <= '20190215' AND MISHistory.DateStamp >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @RunDateLastYearHistory), 0)) OR
    (@CurrentDateStr > '20190215' AND MISHistory.DateStamp >= @RunDateLastYearHistory)) 

  AND MISHistory.DateStamp <= @LastFinacialYearEnd
  GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112))
OPEN #db_cur  

FETCH NEXT FROM #db_cur INTO @MonthIdx, @Turnover

WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO #Result (MonthIdx, ActualTurnoverLastYearSameDateMIS) VALUES (@MonthIdx, @Turnover)
  FETCH NEXT FROM #db_cur INTO @MonthIdx, @Turnover
END

CLOSE #db_cur
DEALLOCATE #db_cur

-- Year Booked turnover
DECLARE #db_cur CURSOR LOCAL FAST_FORWARD FOR
  SELECT DATEPART(MONTH, CONVERT(DATETIME, #MISTable.DateStamp, 112)) AS MonthIdx, ISNULL(SUM(Amount),0), TurnoverGroupNumber
  FROM #MISTable WITH (NOLOCK) 
  INNER JOIN Accounts WITH (NOLOCK) ON #MISTable.AccountNumber = Accounts.AccountNumber 
    AND (@ATurnOverGroupCSV = '' OR Accounts.TurnoverGroupNumber IN (SELECT Number FROM #TurnoverGroups))
    AND Accounts.TurnoverGroupNumber > 0
  WHERE (@AHotelCustomerNumberCSV = '' OR #MISTable.HotelCustomerNumber IN (SELECT Number FROM #HotelCustomerNumbers))
--  AND ((@RunYear = @CurrentYear AND CONVERT(DATETIME, #MISTable.DateStamp, 112) >= @ARunDate) OR (@RunYear > @CurrentYear))
  AND CONVERT(DATETIME, #MISTable.DateStamp, 112) BETWEEN @FinacialYearStart AND @FinacialYearEnd
  GROUP BY DATEPART(MONTH, CONVERT(DATETIME, #MISTable.DateStamp, 112)), TurnoverGroupNumber
OPEN #db_cur  

FETCH NEXT FROM #db_cur INTO @MonthIdx, @Turnover, @TurnoverGroupNumber

WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO #Result (MonthIdx, BookedTurnover, TurnoverGroupNumber) VALUES (@MonthIdx, @Turnover, @TurnoverGroupNumber)
  FETCH NEXT FROM #db_cur INTO @MonthIdx, @Turnover, @TurnoverGroupNumber
END

CLOSE #db_cur
DEALLOCATE #db_cur

-- Turnover Corrected
DECLARE #db_cur CURSOR LOCAL FAST_FORWARD FOR
  SELECT ISNULL(Ratio, 0), RatioInPercent, TurnOverGroup, SUBSTRING(YearMonth, 5, 2)
  FROM AdditionalSalesFromTurnover WITH (NOLOCK)  
  WHERE (@AHotelCustomerNumberCSV = '' OR AdditionalSalesFromTurnover.HotelCustomerNumber IN (SELECT Number FROM #HotelCustomerNumbers))
  AND (@ATurnOverGroupCSV = '' OR AdditionalSalesFromTurnover.TurnoverGroup IN (SELECT Number FROM #TurnoverGroups))
  AND SUBSTRING(YearMonth, 1, 4) = CAST(DATEPART(YEAR, @FinacialYearStart) AS CHAR(4)) 
OPEN #db_cur  

FETCH NEXT FROM #db_cur INTO @Ratio, @RatioInPercent, @TurnoverGroupNumber, @MonthIdx

WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE #Result SET #Result.AdditionalTurnover = CASE WHEN @RatioInPercent = 1 THEN #Result.BookedTurnover * (@Ratio/100) ELSE @Ratio END
  WHERE #Result.MonthIdx = @MonthIdx
  AND #Result.TurnoverGroupNumber = @TurnoverGroupNumber

  IF @@ROWCOUNT = 0 AND @RatioInPercent = 0
    INSERT INTO #Result (MonthIdx, TurnoverGroupNumber, AdditionalTurnover) VALUES (@MonthIdx, @TurnoverGroupNumber, @Ratio)

  FETCH NEXT FROM #db_cur INTO @Ratio, @RatioInPercent, @TurnoverGroupNumber, @MonthIdx
END

CLOSE #db_cur
DEALLOCATE #db_cur

-- Last Year turnover
DECLARE #db_cur CURSOR LOCAL FAST_FORWARD FOR
  SELECT DATEPART(MONTH, CONVERT(DATETIME, AccountDaily.Date, 112)) AS MonthIdx, SUM(Turnoverdebit) - SUM(TurnoverCredit)
  FROM AccountDaily WITH (NOLOCK) 
  INNER JOIN Accounts WITH (NOLOCK) ON AccountDaily.AccountNumber = Accounts.AccountNumber 
    AND (@ATurnOverGroupCSV = '' OR Accounts.TurnoverGroupNumber IN (SELECT Number FROM #TurnoverGroups))
    AND Accounts.TurnoverGroupNumber > 0
  WHERE (@AHotelCustomerNumberCSV = '' OR AccountDaily.HotelCustomerNumber IN (SELECT Number FROM #HotelCustomerNumbers))
  AND CONVERT(DATETIME, AccountDaily.Date, 112) BETWEEN @LastFinacialYearStart AND @LastFinacialYearEnd
  GROUP BY DATEPART(MONTH, CONVERT(DATETIME, AccountDaily.Date, 112))
OPEN #db_cur  

FETCH NEXT FROM #db_cur INTO @MonthIdx, @Turnover

WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO #Result (MonthIdx, ActualTurnoverLastYear) VALUES (@MonthIdx, @Turnover)
  FETCH NEXT FROM #db_cur INTO @MonthIdx, @Turnover
END

CLOSE #db_cur
DEALLOCATE #db_cur

-- Budget
DECLARE #db_cur CURSOR LOCAL FAST_FORWARD FOR
  SELECT Month, SUM(Amount)
  FROM BudgetMonth WITH (NOLOCK) 
  WHERE Year = DATEPART(YEAR, @FinacialYearStart)
  AND (@ATurnOverGroupCSV = '' OR BudgetMonth.TurnoverGroup IN (SELECT Number FROM #TurnoverGroups))
  AND (@AHotelNumberCSV = '' OR BudgetMonth.HotelNumber IN (SELECT Number FROM #HotelNumbers))
  GROUP BY Month
OPEN #db_cur  

FETCH NEXT FROM #db_cur INTO @MonthIdx, @Turnover

WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO #Result (MonthIdx, Budget) VALUES (@MonthIdx, @Turnover)
  FETCH NEXT FROM #db_cur INTO @MonthIdx, @Turnover
END

CLOSE #db_cur
DEALLOCATE #db_cur

--SELECT * FROM #Result

SELECT MonthIdx, 
  (ISNULL(SUM(ActualTurnover), 0)*-1) + ISNULL(SUM(BookedTurnover), 0) + ISNULL(SUM(AdditionalTurnover), 0) AS TotalTurnover,
--  SUM(ActualTurnoverLastYear)*-1 AS ActualTurnoverLastYearSameDate,  
  SUM(ISNULL(ActualTurnoverLastYearSameDateAcc,0))*-1+SUM(ISNULL(ActualTurnoverLastYearSameDateMIS,0)) AS ActualTurnoverLastYearSameDate, 
  SUM(ActualTurnoverLastYear)*-1 AS ActualTurnoverLastYear,          
--  0 AS AdditionalTurnoverLastYear,
  (SUM(ISNULL(ActualTurnoverLastYear, 0))*-1)-(SUM(ISNULL(ActualTurnoverLastYearSameDateAcc, 0))*-1+SUM(ISNULL(ActualTurnoverLastYearSameDateMIS, 0))) AS AdditionalTurnoverLastYear,

  ((ISNULL(SUM(ActualTurnover), 0)*-1) + ISNULL(SUM(BookedTurnover), 0) + ISNULL(SUM(AdditionalTurnover), 0) + 
    (SUM(ISNULL(ActualTurnoverLastYear, 0))*-1)-(SUM(ISNULL(ActualTurnoverLastYearSameDateAcc, 0))*-1+SUM(ISNULL(ActualTurnoverLastYearSameDateMIS, 0)))) AS YearForecast,
  SUM(Budget) AS Budget,
  (ISNULL(SUM(ActualTurnover), 0)*-1) + ISNULL(SUM(BookedTurnover), 0) + ISNULL(SUM(AdditionalTurnover), 0) - SUM(Budget) AS TurnoverCompBudget, -- Kolonne I
  CASE WHEN ISNULL(SUM(Budget), 0) = 0 THEN
    0
  ELSE
    (((ISNULL(SUM(ActualTurnover), 0)*-1) + ISNULL(SUM(BookedTurnover), 0) + ISNULL(SUM(AdditionalTurnover), 0)*1.0) / ISNULL(SUM(Budget), 1)*1.0)*100.0 
  END AS TurnoverBudgetPercent,
  CASE WHEN ISNULL(SUM(ActualTurnoverLastYear), 0) = 0 THEN
    0
  ELSE
    (((ISNULL(SUM(ActualTurnover), 0)*-1) + ISNULL(SUM(BookedTurnover), 0) + ISNULL(SUM(AdditionalTurnover), 0)-(SUM(ActualTurnoverLastYear)*-1.0))/ (SUM(ActualTurnoverLastYear)*-1.0))*100.0
  END AS TurnoverLastThisYearPercent
FROM #Result
GROUP BY MonthIdx

DROP TABLE #TurnoverGroups
DROP TABLE #HotelCustomerNumbers
DROP TABLE #HotelNumbers
DROP TABLE #Result
DROP TABLE #MISTable