CREATE PROCEDURE dbo.Dashboard @ARunDate DATETIME AS
/*
  Created by Kent Johansen, May 2022
  Used by a webservice ().
  CountType: 0: Total turnover, 1: Room turnover, 2: Breakfast turnover, 3: F&B turnover, 4: Avg price, 5: OCC Percent, 6: Avg per sold room, 7: Deleted transactions
  Shows turnover for Today, Month to date, Month same date last year, forecast month, budget.
  Today, month to date: AccountDaily
  Month same date last year:  Lets say that report is generated 10. May 2022 then this field should contain accountdaily from 1. May to 9. May and mis history from 10. May to 31. May.
  Forecast month: MIS
  Budget: BudgetMonth

  Todo:
    If Room-in-room, then include occupied in sold rooms. Observe TUseOccupied = (uoAddToSold, uoSubtractFromTotal, uoDontUse)

  Changes:
    KJ 20220517: Bug: Fix for "Occ% wrong. OOO setting not used."

*/
SET NOCOUNT ON

DECLARE @StartDateOfMonthStr CHAR(8)
DECLARE @EndDateOfMonthStr CHAR(8)
DECLARE @RunDateStr CHAR(8)
DECLARE @Year CHAR(4)
DECLARE @Month CHAR(2)
DECLARE @TGRoomTurnoverCSV VARCHAR(500)
DECLARE @TGBreakfastTurnoverCSV VARCHAR(500)
DECLARE @TGFAndBTurnoverCSV VARCHAR(500)
DECLARE @RunDateLastYear DATETIME
DECLARE @RunDateLastYearHistory DATETIME
DECLARE @StartOfMonthLastYear DATETIME
DECLARE @EndOfMonthLastYear DATETIME
DECLARE @StartOfMonthLastYearMIS DATETIME
DECLARE @EndOfMonthLastYearMIS DATETIME

DECLARE @AIncludeAllotment BIT
DECLARE @DependsOnOOO BIT
DECLARE @DependsOnOOS BIT

SELECT @TGRoomTurnoverCSV = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'DashboardRoomToG'
SELECT @TGBreakfastTurnoverCSV = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'DashboardBreakfastToG'
SELECT @TGFAndBTurnoverCSV = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'DashboardFAndBToG'

SET @RunDateStr = CONVERT(CHAR(8), @ARunDate, 112)
SELECT @StartDateOfMonthStr = CONVERT(CHAR(8), DATEADD(DAY, 1, EOMONTH(@ARunDate, -1)), 112)
SELECT @EndDateOfMonthStr = CONVERT(CHAR(8), EOMONTH(@ARunDate), 112)
SET @Year = SUBSTRING(@RunDateStr, 1, 4)
SET @Month = SUBSTRING(@RunDateStr, 5, 2)
SET @RunDateLastYear = DATEADD(YEAR, -1, @ARunDate)
SELECT @StartOfMonthLastYear = DATEADD(DAY, 1, EOMONTH(@RunDateLastYear, -1))
SELECT @EndOfMonthLastYear = EOMONTH(@RunDateLastYear)

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

SELECT CustomerNumber AS HCN, LastName, intlist.number AS CountType, CAST('' AS varchar(50)) AS CountName, CAST(0 AS MONEY) AS TypeDay, CAST(0 AS MONEY) AS TypeMonth, CAST(0 AS MONEY) AS TypeLastYear, CAST(0 AS MONEY) AS TypeForecast, CAST(0 AS MONEY) AS TypeBudget
INTO #res
FROM Customer WITH (NOLOCK)
CROSS APPLY dbo.iter_intlist_to_table_No_Dummy('0,1,2,3,4,5,6,7') intlist
WHERE CustomerTypeNumber = 7
AND (Customer.HotelNumber>0 OR LocalHotel=1)
AND ISNULL(Customer.MarkAsDeleted, 0) = 0

SELECT CustomerNumber AS HCN, Customer.HotelNumber,
  0 AS SoldDay, 0 AS SoldMonth, 0 AS SoldLastYear, 0 AS SoldForecast,
  0 AS TotalTODay, 0 AS TotalTOMonth, 0 AS TotalTOLastYear, 0 AS TotalTOForecast,
  0 AS RoomTODay, 0 AS RoomTOMonth, 0 AS RoomTOLastYear, 0 AS RoomTOForecast,
  (
    SELECT COUNT(*)
    FROM Rooms WITH (NOLOCK)
    WHERE ROOMS.HotelNumber = Customer.HotelNumber
  ) AS PossibleDay, 
  0 AS PossibleMonth, 0 AS PossibleLastYear, 0 AS PossibleForecast
INTO #avg
FROM Customer WITH (NOLOCK)
WHERE CustomerTypeNumber = 7
AND (Customer.HotelNumber>0 OR LocalHotel=1)
AND ISNULL(Customer.MarkAsDeleted, 0) = 0

SELECT Date, CustomerNumber, TurnOverGroupNumber, (SUM(Turnoverdebit) - SUM(TurnoverCredit)) * -1 AS TotalTurnover
INTO #to
FROM AccountDaily WITH (NOLOCK) 
INNER JOIN Accounts WITH (NOLOCK) ON AccountDaily.AccountNumber = Accounts.AccountNumber 
INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = AccountDaily.HotelCustomerNumber
WHERE Date BETWEEN @StartDateOfMonthStr AND @RunDateStr
AND ISNULL(TurnOverGroupNumber, 0) > 0
GROUP BY Date, CustomerNumber, TurnOverGroupNumber

--Get sold rooms
SELECT @AIncludeAllotment = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'VacantRoomsInclAllotment'
SET @AIncludeAllotment = ISNULL(@AIncludeAllotment, 1)

SELECT @DependsOnOOO = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'ProcentOfOccupiedBedsDependsOnUOO'
SET @DependsOnOOO = ISNULL(@DependsOnOOO, 0)

SELECT @DependsOnOOS = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'PercentOfOccupiedBedsDependsOnOOS'
SET @DependsOnOOS = ISNULL(@DependsOnOOS, 0)

UPDATE #avg
SET PossibleMonth = ((DATEDIFF(DAY, @StartDateOfMonthStr, @RunDateStr)+1)*PossibleDay),
PossibleLastYear = ((DATEDIFF(DAY, @StartDateOfMonthStr, @EndDateOfMonthStr)+1)*PossibleDay),
PossibleForecast = ((DATEDIFF(DAY, @StartDateOfMonthStr, @EndDateOfMonthStr)+1)*PossibleDay)

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,
  NumberOfArrival SMALLINT NULL,
  NumberOfInhouse SMALLINT NULL,
  NumberOfStay SMALLINT NULL,
  NumberOfDeparture SMALLINT NULL,
  ArrivalPax SMALLINT NULL,
  InhousePax SMALLINT NULL,
  StayPax SMALLINT NULL,
  DeparturePax SMALLINT NULL,
  NumberOfWaitingArrival SMALLINT NULL,
  NumberOfTentativeArrival SMALLINT NULL,
  WaitingArrivalPax SMALLINT NULL, 
  TentativeArrivalPax SMALLINT NULL
)
INSERT INTO #tmpAvailability
  EXEC GenerateAvailability @StartDateOfMonthStr, @EndDateOfMonthStr, 1, -1, '', 1, 0, '', @AIncludeOccupiedInOFS = 1

UPDATE #avg 
SET #avg.SoldDay = ISNULL((
  SELECT SUM(ISNULL(#tmpAvailability.SoldRooms, 0)) + CASE WHEN @AIncludeAllotment = 1 THEN SUM(ISNULL(#tmpAvailability.TotalRoomsAllotment, 0)) ELSE 0 END
  FROM #tmpAvailability
  WHERE #avg.HotelNumber = #tmpAvailability.HotelNumber
  AND #tmpAvailability.DateStamp = @RunDateStr
), 0),
#avg.PossibleDay = ISNULL((SELECT #avg.PossibleDay -
  CASE WHEN @DependsOnOOO = 1 THEN SUM(ISNULL(#tmpAvailability.NumberOfOutOfOrder, 0)) ELSE 0 END -
  CASE WHEN @DependsOnOOS = 1 THEN SUM(ISNULL(#tmpAvailability.NumberOfOutForService, 0)) ELSE 0 END
  FROM #tmpAvailability
  WHERE #avg.HotelNumber = #tmpAvailability.HotelNumber
  AND #tmpAvailability.DateStamp = @RunDateStr), 0)
FROM #tmpAvailability

UPDATE #avg 
SET #avg.SoldMonth = ISNULL((
  SELECT SUM(ISNULL(#tmpAvailability.SoldRooms, 0)) + CASE WHEN @AIncludeAllotment = 1 THEN SUM(ISNULL(#tmpAvailability.TotalRoomsAllotment, 0)) ELSE 0 END
  FROM #tmpAvailability
  WHERE #avg.HotelNumber = #tmpAvailability.HotelNumber
  AND #tmpAvailability.DateStamp >= @StartDateOfMonthStr
  AND #tmpAvailability.DateStamp <= @RunDateStr
), 0),
#avg.PossibleMonth = ISNULL((SELECT #avg.PossibleMonth -
  CASE WHEN @DependsOnOOO = 1 THEN SUM(ISNULL(#tmpAvailability.NumberOfOutOfOrder, 0)) ELSE 0 END -
  CASE WHEN @DependsOnOOS = 1 THEN SUM(ISNULL(#tmpAvailability.NumberOfOutForService, 0)) ELSE 0 END
  FROM #tmpAvailability
  WHERE #avg.HotelNumber = #tmpAvailability.HotelNumber
  AND #tmpAvailability.DateStamp >= @StartDateOfMonthStr
  AND #tmpAvailability.DateStamp <= @RunDateStr), 0)
FROM #tmpAvailability

UPDATE #avg 
SET #avg.SoldForecast = ISNULL((
  SELECT SUM(ISNULL(#tmpAvailability.SoldRooms, 0)) + CASE WHEN @AIncludeAllotment = 1 THEN SUM(ISNULL(#tmpAvailability.TotalRoomsAllotment, 0)) ELSE 0 END
  FROM #tmpAvailability
  WHERE #avg.HotelNumber = #tmpAvailability.HotelNumber
), 0),
#avg.PossibleForecast = ISNULL((SELECT #avg.PossibleForecast -
  CASE WHEN @DependsOnOOO = 1 THEN SUM(ISNULL(#tmpAvailability.NumberOfOutOfOrder, 0)) ELSE 0 END -
  CASE WHEN @DependsOnOOS = 1 THEN SUM(ISNULL(#tmpAvailability.NumberOfOutForService, 0)) ELSE 0 END
  FROM #tmpAvailability
  WHERE #avg.HotelNumber = #tmpAvailability.HotelNumber), 0)
FROM #tmpAvailability

DELETE #tmpAvailability
INSERT INTO #tmpAvailability
  EXEC GenerateAvailability @StartOfMonthLastYear, @EndOfMonthLastYear, 1, -1, '', 1, 0, '', @AIncludeOccupiedInOFS = 1

UPDATE #avg 
SET #avg.SoldLastYear = ISNULL((
  SELECT SUM(ISNULL(#tmpAvailability.SoldRooms, 0)) + CASE WHEN @AIncludeAllotment = 1 THEN SUM(ISNULL(#tmpAvailability.TotalRoomsAllotment, 0)) ELSE 0 END
  FROM #tmpAvailability
  WHERE #avg.HotelNumber = #tmpAvailability.HotelNumber
), 0),
#avg.PossibleLastYear = ISNULL((SELECT #avg.PossibleLastYear -
  CASE WHEN @DependsOnOOO = 1 THEN SUM(ISNULL(#tmpAvailability.NumberOfOutOfOrder, 0)) ELSE 0 END -
  CASE WHEN @DependsOnOOS = 1 THEN SUM(ISNULL(#tmpAvailability.NumberOfOutForService, 0)) ELSE 0 END
  FROM #tmpAvailability
  WHERE #avg.HotelNumber = #tmpAvailability.HotelNumber), 0)
FROM #tmpAvailability

DROP TABLE #tmpAvailability

--Day turnover (1)
--Total
UPDATE #res 
SET #res.TypeDay = ISNULL((
  SELECT SUM(#to.TotalTurnover)
  FROM #to
  WHERE #res.HCN = #to.CustomerNumber
  AND #to.Date = @RunDateStr
), 0), CountName = 'TotalTurnover'
FROM #res
WHERE #res.CountType = 0

UPDATE #avg 
SET #avg.TotalTODay = ISNULL((
  SELECT SUM(#to.TotalTurnover)
  FROM #to
  WHERE #avg.HCN = #to.CustomerNumber
  AND #to.Date = @RunDateStr
), 0)
FROM #avg

--Room Turnover
UPDATE #res 
SET #res.TypeDay = ISNULL((
  SELECT SUM(#to.TotalTurnover)
  FROM #to
  WHERE #res.CountType = 1
  AND #res.HCN = #to.CustomerNumber
  AND #to.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGRoomTurnoverCSV))
  AND #to.Date = @RunDateStr
), 0), CountName = 'RoomTurnover'
FROM #res
WHERE #res.CountType = 1

UPDATE #avg 
SET #avg.RoomTODay = ISNULL((
  SELECT SUM(#to.TotalTurnover)
  FROM #to
  WHERE #avg.HCN = #to.CustomerNumber
  AND #to.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGRoomTurnoverCSV))
  AND #to.Date = @RunDateStr
), 0)
FROM #avg

--Breakfast Turnover
UPDATE #res 
SET #res.TypeDay = ISNULL((
  SELECT SUM(#to.TotalTurnover)
  FROM #to
  WHERE #res.CountType = 2
  AND #res.HCN = #to.CustomerNumber
  AND #to.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGBreakfastTurnoverCSV))
  AND #to.Date = @RunDateStr
), 0), CountName = 'BreakfastTurnover'
FROM #res
WHERE #res.CountType = 2

--F&B Turnover
UPDATE #res 
SET #res.TypeDay = ISNULL((
  SELECT SUM(#to.TotalTurnover)
  FROM #to
  WHERE #res.CountType = 3
  AND #res.HCN = #to.CustomerNumber
  AND #to.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGFAndBTurnoverCSV))
  AND #to.Date = @RunDateStr
), 0), CountName = 'F&BTurnover'
FROM #res
WHERE #res.CountType = 3

--Month turnover (2)
--Total
UPDATE #res 
SET #res.TypeMonth = ISNULL((
  SELECT SUM(#to.TotalTurnover)
  FROM #to
  WHERE #res.HCN = #to.CustomerNumber
), 0)
FROM #res
WHERE #res.CountType = 0

UPDATE #avg 
SET #avg.TotalTOMonth = ISNULL((
  SELECT SUM(#to.TotalTurnover)
  FROM #to
  WHERE #avg.HCN = #to.CustomerNumber
), 0)
FROM #avg

--Room Turnover
UPDATE #res 
SET #res.TypeMonth = ISNULL((
  SELECT SUM(#to.TotalTurnover)
  FROM #to
  WHERE #res.CountType = 1
  AND #res.HCN = #to.CustomerNumber
  AND #to.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGRoomTurnoverCSV))
), 0)
FROM #res
WHERE #res.CountType = 1

UPDATE #avg 
SET #avg.RoomTOMonth = ISNULL((
  SELECT SUM(#to.TotalTurnover)
  FROM #to
  WHERE #avg.HCN = #to.CustomerNumber
  AND #to.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGRoomTurnoverCSV))
), 0)
FROM #avg

--Breakfast Turnover
UPDATE #res 
SET #res.TypeMonth = ISNULL((
  SELECT SUM(#to.TotalTurnover)
  FROM #to
  WHERE #res.CountType = 2
  AND #res.HCN = #to.CustomerNumber
  AND #to.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGBreakfastTurnoverCSV))
), 0)
FROM #res
WHERE #res.CountType = 2

--F&B Turnover
UPDATE #res 
SET #res.TypeMonth = ISNULL((
  SELECT SUM(#to.TotalTurnover)
  FROM #to
  WHERE #res.CountType = 3
  AND #res.HCN = #to.CustomerNumber
  AND #to.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGFAndBTurnoverCSV))
), 0)
FROM #res
WHERE #res.CountType = 3

DROP TABLE #to

--Last year same period (3)
IF @RunDateLastYearHistory < @StartOfMonthLastYear
BEGIN
  SET @StartOfMonthLastYearMIS = @StartOfMonthLastYear
  SET @EndOfMonthLastYearMIS = @EndOfMonthLastYear
END
ELSE
BEGIN
  SET @StartOfMonthLastYearMIS = @RunDateLastYearHistory
  SET @EndOfMonthLastYearMIS = @EndOfMonthLastYear
END

SELECT Date, CustomerNumber, TurnOverGroupNumber, (SUM(Turnoverdebit) - SUM(TurnoverCredit)) * -1 AS TotalTurnover
INTO #toLastYear
FROM AccountDaily WITH (NOLOCK) 
INNER JOIN Accounts WITH (NOLOCK) ON AccountDaily.AccountNumber = Accounts.AccountNumber 
INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = AccountDaily.HotelCustomerNumber
WHERE Date >= @StartOfMonthLastYear 
AND Date < @RunDateLastYearHistory
AND ISNULL(TurnOverGroupNumber, 0) > 0
GROUP BY Date, CustomerNumber, TurnOverGroupNumber

UNION

SELECT CONVERT(CHAR(8), MISHistory.DateStamp, 112), MISHistory.FK_HotelCustomerNumber, Accounts.TurnOverGroupNumber, 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 
WHERE MISHistory.DateStamp >= @StartOfMonthLastYearMIS
AND MISHistory.DateStamp <= @EndOfMonthLastYearMIS
AND ISNULL(Accounts.TurnOverGroupNumber, 0) > 0
AND MISHistory.SystemDate = @RunDateLastYearHistory
GROUP BY MISHistory.DateStamp, MISHistory.FK_HotelCustomerNumber, Accounts.TurnOverGroupNumber

--Total
UPDATE #res 
SET #res.TypeLastYear = ISNULL((
  SELECT SUM(#toLastYear.TotalTurnover)
  FROM #toLastYear
  WHERE #res.HCN = #toLastYear.CustomerNumber
), 0)
FROM #res
WHERE #res.CountType = 0

UPDATE #avg 
SET #avg.TotalTOLastYear = ISNULL((
  SELECT SUM(#toLastYear.TotalTurnover)
  FROM #toLastYear
  WHERE #avg.HCN = #toLastYear.CustomerNumber
), 0)
FROM #avg

--Room Turnover
UPDATE #res 
SET #res.TypeLastYear = ISNULL((
  SELECT SUM(#toLastYear.TotalTurnover)
  FROM #toLastYear
  WHERE #res.CountType = 1
  AND #res.HCN = #toLastYear.CustomerNumber
  AND #toLastYear.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGRoomTurnoverCSV))
), 0)
FROM #res
WHERE #res.CountType = 1

UPDATE #avg 
SET #avg.RoomTOLastYear = ISNULL((
  SELECT SUM(#toLastYear.TotalTurnover)
  FROM #toLastYear
  WHERE #avg.HCN = #toLastYear.CustomerNumber
  AND #toLastYear.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGRoomTurnoverCSV))
), 0)
FROM #avg

--Breakfast Turnover
UPDATE #res 
SET #res.TypeLastYear = ISNULL((
  SELECT SUM(#toLastYear.TotalTurnover)
  FROM #toLastYear
  WHERE #res.CountType = 2
  AND #res.HCN = #toLastYear.CustomerNumber
  AND #toLastYear.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGBreakfastTurnoverCSV))
), 0)
FROM #res
WHERE #res.CountType = 2

--F&B Turnover
UPDATE #res 
SET #res.TypeLastYear = ISNULL((
  SELECT SUM(#toLastYear.TotalTurnover)
  FROM #toLastYear
  WHERE #res.CountType = 3
  AND #res.HCN = #toLastYear.CustomerNumber
  AND #toLastYear.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGFAndBTurnoverCSV))
), 0)
FROM #res
WHERE #res.CountType = 3

DROP TABLE #toLastYear

--Forecast month (4)
SELECT MISTableView.DateStamp, MISTableView.HotelCustomerNumber, Accounts.TurnOverGroupNumber, SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.BookedOnlyPackageExVat) + 
SUM(MISTableView.ActualPriceExPackageExVat) + SUM(MISTableView.ActualOnlyPackageExVat) AS Amount
INTO #forecast
FROM MISTableView WITH (NOLOCK) 
INNER JOIN Accounts WITH (NOLOCK) ON MISTableView.AccountNumber = Accounts.AccountNumber 
WHERE DateStamp BETWEEN @StartDateOfMonthStr AND @EndDateOfMonthStr
AND ISNULL(Accounts.TurnOverGroupNumber, 0) > 0
GROUP BY MISTableView.DateStamp, MISTableView.HotelCustomerNumber, Accounts.TurnOverGroupNumber
HAVING ISNULL(SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.BookedOnlyPackageExVat), 0) + SUM(MISTableView.ActualPriceExPackageExVat) + SUM(MISTableView.ActualOnlyPackageExVat) <> 0

--Total
UPDATE #res 
SET #res.TypeForecast = ISNULL((
  SELECT SUM(#forecast.Amount)
  FROM #forecast
  WHERE #res.HCN = #forecast.HotelCustomerNumber
), 0)
FROM #res
WHERE #res.CountType = 0

UPDATE #avg 
SET #avg.TotalTOForecast = ISNULL((
  SELECT SUM(#forecast.Amount)
  FROM #forecast
  WHERE #avg.HCN = #forecast.HotelCustomerNumber
), 0)
FROM #avg

--Room Turnover
UPDATE #res 
SET #res.TypeForecast = ISNULL((
  SELECT SUM(#forecast.Amount)
  FROM #forecast
  WHERE #res.CountType = 1
  AND #res.HCN = #forecast.HotelCustomerNumber
  AND #forecast.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGRoomTurnoverCSV))
), 0)
FROM #res
WHERE #res.CountType = 1

UPDATE #avg 
SET #avg.RoomTOForecast = ISNULL((
  SELECT SUM(#forecast.Amount)
  FROM #forecast
  WHERE #avg.HCN = #forecast.HotelCustomerNumber
  AND #forecast.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGRoomTurnoverCSV))
), 0)
FROM #avg

--Breakfast Turnover
UPDATE #res 
SET #res.TypeForecast = ISNULL((
  SELECT SUM(#forecast.Amount)
  FROM #forecast
  WHERE #res.CountType = 2
  AND #res.HCN = #forecast.HotelCustomerNumber
  AND #forecast.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGBreakfastTurnoverCSV))
), 0)
FROM #res
WHERE #res.CountType = 2

--F&B Turnover
UPDATE #res 
SET #res.TypeForecast = ISNULL((
  SELECT SUM(#forecast.Amount)
  FROM #forecast
  WHERE #res.CountType = 3
  AND #res.HCN = #forecast.HotelCustomerNumber
  AND #forecast.TurnOverGroupNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGFAndBTurnoverCSV))
), 0)
FROM #res
WHERE #res.CountType = 3

DROP TABLE #forecast

--Budget (5)
SELECT Customer.CustomerNumber AS HCN, BudgetMonth.TurnoverGroup, BudgetMonth.Amount
INTO #budget
FROM BudgetMonth WITH (NOLOCK) 
INNER JOIN Customer WITH (NOLOCK) ON Customer.HotelNumber = BudgetMonth.HotelNumber
  AND  Customer.CustomerTypeNumber = 7
  AND (Customer.HotelNumber>0 OR LocalHotel=1)
  AND ISNULL(Customer.MarkAsDeleted, 0) = 0
WHERE BudgetMonth.Year = @Year
AND BudgetMonth.Month = @Month

--Total
UPDATE #res 
SET #res.TypeBudget = ISNULL((
  SELECT SUM(#budget.Amount)
  FROM #budget
  WHERE #res.HCN = #budget.HCN
), 0)
FROM #res
WHERE #res.CountType = 0

--Room Turnover
UPDATE #res 
SET #res.TypeBudget = ISNULL((
  SELECT SUM(#budget.Amount)
  FROM #budget
  WHERE #res.CountType = 1
  AND #res.HCN = #budget.HCN
  AND #budget.TurnOverGroup IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGRoomTurnoverCSV))
), 0)
FROM #res
WHERE #res.CountType = 1

--Breakfast Turnover
UPDATE #res 
SET #res.TypeBudget = ISNULL((
  SELECT SUM(#budget.Amount)
  FROM #budget
  WHERE #res.CountType = 2
  AND #res.HCN = #budget.HCN
  AND #budget.TurnOverGroup IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGBreakfastTurnoverCSV))
), 0)
FROM #res
WHERE #res.CountType = 2

--F&B Turnover
UPDATE #res 
SET #res.TypeBudget = ISNULL((
  SELECT SUM(#budget.Amount)
  FROM #budget
  WHERE #res.CountType = 3
  AND #res.HCN = #budget.HCN
  AND #budget.TurnOverGroup IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@TGFAndBTurnoverCSV))
), 0)
FROM #res
WHERE #res.CountType = 3

DROP TABLE #budget

--Avg Price (Room turnover/Number of sold rooms
UPDATE #res
SET CountName = 'AvgRoomPricePerSoldRoom',
TypeDay = CASE WHEN ISNULL(#avg.SoldDay, 0) > 0 THEN #avg.RoomTODay / #avg.SoldDay ELSE 0 END,
TypeMonth = CASE WHEN ISNULL(SoldMonth, 0) > 0 THEN #avg.RoomTOMonth / #avg.SoldMonth ELSE 0 END,
TypeLastYear = CASE WHEN ISNULL(#avg.SoldLastYear, 0) > 0 THEN #avg.RoomTOLastYear / #avg.SoldLastYear ELSE 0 END,
TypeForecast = CASE WHEN ISNULL(#avg.SoldForecast, 0) > 0 THEN #avg.RoomTOForecast / #avg.SoldForecast ELSE 0 END,
TypeBudget = NULL
FROM #res
INNER JOIN #avg ON #avg.HCN = #res.HCN
WHERE CountType = 4

--Occ percent
UPDATE #res
SET CountName = 'OccPercent',
TypeDay = CASE WHEN ISNULL(#avg.PossibleDay, 0) > 0 THEN (CAST(#avg.SoldDay AS MONEY) / CAST(#avg.PossibleDay AS MONEY)) * 100.0 ELSE 0 END,
TypeMonth = CASE WHEN ISNULL(#avg.PossibleMonth, 0) > 0 THEN (CAST(#avg.SoldMonth AS MONEY) / CAST(#avg.Possiblemonth AS MONEY)) * 100.0 ELSE 0 END,
TypeLastYear = CASE WHEN ISNULL(#avg.PossibleLastYear, 0) > 0 THEN (CAST(#avg.SoldLastYear AS MONEY) / CAST(#avg.PossibleLastYear AS MONEY)) * 100.0 ELSE 0 END,
TypeForecast = CASE WHEN ISNULL(#avg.PossibleForecast, 0) > 0 THEN (CAST(#avg.SoldForecast AS MONEY) / CAST(#avg.PossibleForecast AS MONEY)) * 100.0 ELSE 0 END,
TypeBudget = NULL
FROM #res
INNER JOIN #avg ON #avg.HCN = #res.HCN
WHERE CountType = 5

--Avg. per sold room (Total turnover / Number of Sold rooms
UPDATE #res
SET CountName = 'AvgTotalPricePerSoldRoom',
TypeDay = CASE WHEN ISNULL(#avg.SoldDay, 0) > 0 THEN #avg.TotalTODay / #avg.SoldDay ELSE 0 END,
TypeMonth = CASE WHEN ISNULL(#avg.SoldMonth, 0) > 0 THEN #avg.TotalTOMonth / #avg.SoldMonth ELSE 0 END,
TypeLastYear = CASE WHEN ISNULL(#avg.SoldLastYear, 0) > 0 THEN #avg.TotalTOLastYear / #avg.SoldLastYear ELSE 0 END,
TypeForecast = CASE WHEN ISNULL(#avg.SoldForecast, 0) > 0 THEN #avg.TotalTOForecast / #avg.SoldForecast ELSE 0 END,
TypeBudget = NULL
FROM #res
INNER JOIN #avg ON #avg.HCN = #res.HCN
WHERE CountType = 6

--Deleted tranactions
SELECT Customer.CustomerNumber, CONVERT(CHAR(8), Transactions.DateStamp, 112) AS dt,
SUM(Pieces)*-1 AS PCS,
SUM(CASE WHEN CASE WHEN Ware.Type <> 4 THEN Transactions.Payment ELSE 5 END = 5 THEN
  CASE WHEN Transactions.Debit = 1 THEN
    Transactions.PriceOverwrite * -1
  ELSE
    Transactions.PriceOverwrite
  END
ELSE
  CASE WHEN Transactions.Debit = 1 THEN
    Transactions.PriceOverwrite
  ELSE
    Transactions.PriceOverwrite * -1
  END
END)*-1 AS PriceOverwrite
INTO #delTrans
FROM Transactions WITH (NOLOCK)
INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = Transactions.HotelCustomerNumber
INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = Transactions.WareNumber
INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
  AND WareGroups.Payment <> 1
WHERE Transactions.ShowOnBillFlag = 1
AND Transactions.MovedDeleted = 1 
AND ISNULL(Transactions.MovedFolionr, 0)=0
AND Transactions.DateStamp BETWEEN CONVERT(DATETIME, @StartDateOfMonthStr + ' 00:00:00:000') AND CONVERT(DATETIME, @RunDateStr + ' 23:59:59:997') 
GROUP BY Customer.CustomerNumber, CONVERT(CHAR(8), Transactions.DateStamp, 112)

--Today
UPDATE #res 
SET #res.TypeDay = ISNULL((
  SELECT SUM(#delTrans.PriceOverwrite)
  FROM #delTrans
  WHERE #res.HCN = #delTrans.CustomerNumber
  AND #delTrans.dt = @RunDateStr
), 0)
FROM #res
WHERE #res.CountType = 7

--Month
UPDATE #res 
SET #res.TypeMonth = ISNULL((
  SELECT SUM(#delTrans.PriceOverwrite)
  FROM #delTrans
  WHERE #res.HCN = #delTrans.CustomerNumber
), 0), CountName = 'DeletedTransactions',
TypeLastYear = NULL, TypeForecast = NULL, TypeBudget = NULL
FROM #res
WHERE #res.CountType = 7

DROP TABLE #delTrans
DROP TABLE #avg

SELECT * FROM #res

DROP TABLE #res