CREATE PROCEDURE dbo.KeyFigureReport @AHotelCustomerNumberCSV VARCHAR(1000), @AFromDate VARCHAR(8), @AToDate VARCHAR(8), 
  @AInclVAT BIT, @AInclPackage BIT, @AIncludeProvision BIT, @AInclCorrection BIT, @ABudget BIT AS
/*
  Created by Kent Johansen, Februar 2021
*/
SET NOCOUNT ON

IF @AInclCorrection = 0
BEGIN
  SELECT Customer.HotelNumber, MISTableView.HotelCustomerNumber,
  MISTableView.DateStamp, ValueType,
  SUM(ActualNumberOfRooms) + SUM(BookedNumberOfRooms) AS NumberOfRooms,
  CASE WHEN @ABudget = 1 THEN
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISTableView.BookedPriceExPackage) + SUM(MISTableView.BookedOnlyPackage) +
      SUM(MISTableView.ActualPriceExPackage) + SUM(MISTableView.ActualOnlyPackage)
    ELSE  
      SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.BookedOnlyPackageExVat) +
      SUM(MISTableView.ActualPriceExPackageExVat) + SUM(MISTableView.ActualOnlyPackageExVat)
    END
  ELSE
    CASE WHEN @AInclPackage = 1 THEN
      CASE WHEN @AInclVAT = 1 THEN
        SUM(MISTableView.BookedPriceInclPackage) + SUM(MISTableView.ActualPriceInclPackage) 
      ELSE  
        SUM(MISTableView.BookedPriceInclPackageExVat) + SUM(MISTableView.ActualPriceInclPackageExVat) 
      END
    ELSE
      CASE WHEN @AInclVAT = 1 THEN
        SUM(MISTableView.BookedPriceExPackage) + SUM(MISTableView.ActualPriceExPackage) 
      ELSE  
        SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.ActualPriceExPackageExVat) 
      END
    END
  END AS Turnover
  FROM MISTableView WITH (NOLOCK)
  INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = MISTableView.HotelCustomerNumber
  WHERE MISTableView.DateStamp BETWEEN @AFromDate AND @AToDate
  AND MISTableView.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
  AND (@AIncludeProvision = 1 OR (ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18)))
  GROUP BY MISTableView.HotelCustomerNumber, Customer.HotelNumber, MISTableView.DateStamp, ValueType
END
ELSE
BEGIN
  DECLARE @CurrentDateStr CHAR(8)
  SELECT @CurrentDateStr = SystemDate FROM System WITH (NOLOCK) WHERE Number = 1

  SELECT UserIdNumber
  INTO #Users
  FROM Personel WITH (NOLOCK)
  WHERE UserTypeNumber IN (3,4)

  SELECT DISTINCT ReferenceNumber
  INTO #tmpRef
  FROM MISTableView WITH (NOLOCK)
  WHERE DateStamp BETWEEN @AFromDate AND @AToDate
  AND (StaffID IN (2,3) OR (DateStamp >= @CurrentDateStr)) 
  AND ValueType = 1

  SELECT Customer.HotelNumber, MISTableView.HotelCustomerNumber, MISTableView.DateStamp, MISTableView.ValueType,
  SUM(MISTableView.ActualNumberOfRooms) + SUM(MISTableView.BookedNumberOfRooms) AS NumberOfRooms,
  0 AS Turnover
  FROM MISTableView WITH (NOLOCK)
  INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = MISTableView.HotelCustomerNumber
  WHERE MISTableView.DateStamp BETWEEN @AFromDate AND @AToDate
  AND MISTableView.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
  AND (MISTableView.ValueType <> 1 OR MISTableView.ReferenceNumber IN (SELECT DISTINCT ReferenceNumber FROM #tmpRef))
  AND MISTableView.ReferenceNumber > 0
  AND (@AIncludeProvision = 1 OR (ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18)))
  GROUP BY MISTableView.HotelCustomerNumber, Customer.HotelNumber, MISTableView.DateStamp, ValueType

  UNION

  SELECT Customer.HotelNumber, MISTableView.HotelCustomerNumber, MISTableView.DateStamp, MISTableView.ValueType,
  0 AS NumberOfRooms,
  CASE WHEN @AInclPackage = 1 THEN
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISTableView.BookedPriceInclPackage) + SUM(MISTableView.ActualPriceInclPackage) 
    ELSE  
      SUM(MISTableView.BookedPriceInclPackageExVat) + SUM(MISTableView.ActualPriceInclPackageExVat) 
    END
  ELSE
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISTableView.BookedPriceExPackage) + SUM(MISTableView.ActualPriceExPackage) 
    ELSE  
      SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.ActualPriceExPackageExVat) 
    END
  END AS Turnover
  FROM MISTableView WITH (NOLOCK) 
  INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = MISTableView.HotelCustomerNumber
  WHERE MISTableView.DateStamp BETWEEN @AFromDate AND @AToDate
  AND MISTableView.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
  AND MISTableView.ReferenceNumber > 0
  AND (@AIncludeProvision = 1 OR (ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18)))
  AND (MISTableView.StaffID IN (SELECT UserIdNumber FROM #Users) OR (MISTableView.DateStamp >= @CurrentDateStr)) 
  GROUP BY MISTableView.HotelCustomerNumber, Customer.HotelNumber, MISTableView.DateStamp, ValueType

  DROP TABLE #Users
  DROP TABLE #tmpRef
END