CREATE PROCEDURE dbo.SameSystem @AHotelCustomerNumberCSV VARCHAR(500), @AFromDate DATETIME, @AToDate DATETIME, @AShowForEveryHour BIT AS
/*
  Created by Kent Johansen, December 2024
  If @AShowForEveryHour is set to True, then a ware sold between 10:00 and 12:30 apears sold at 10, 11 and 12.

  Changes:
    KJ 20250314: Even if MIS is setup to include, then do not include Tentative.
    KJ 20250217: Hour format
    KJ 20250217: Added Prefix for department number
*/
SET NOCOUNT ON;
IF @AShowForEveryHour = 1
BEGIN
  WITH HourlySales AS 
  (
    SELECT 
      DateStamp,
      WareNumber,
      ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'SameSystemPrefix'), '') + CAST(DepartmentNumber AS varchar(10)) AS DepartmentNumber,
      ISNULL(ActualPriceInclPackageExVat, 0) + ISNULL(BookedPriceInclPackageExVat, 0) AS TurnoverExVAT,
      ISNULL(ActualPriceInclPackage, 0) + ISNULL(BookedPriceInclPackage, 0) AS TurnoverInclVAT,
      ISNULL(ActualWareCount, 0) + ISNULL(BookedWareCount, 0) AS Sold,
      CASE WHEN ISNULL(FromTime, 0) = 0 THEN 0 ELSE DATEPART(HOUR, FromTime) END AS StartHour,
      CASE WHEN ISNULL(ToTime, 0) = 0 THEN 23 ELSE DATEPART(HOUR, FromTime) END AS EndHour
    FROM MISTableViewWithTime
    WHERE (@AHotelCustomerNumberCSV = '' OR HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV)))
    AND ReservationStatus <> 4
    AND DateStamp BETWEEN @AFromDate AND @AToDate
  )
  --select * from HourlySales

  SELECT DateStamp, DepartmentNumber, WareNumber, Hour, SUM(Sold) AS Sold, SUM(TurnoverExVAT) AS TurnoverExVAT, SUM(TurnoverInclVAT) AS TurnoverInclVAT
  FROM HourlySales
  CROSS APPLY (
    SELECT Number AS Hour
    FROM master.dbo.spt_values 
    WHERE type = 'P' AND Number BETWEEN StartHour AND EndHour
  ) AS HourRange
  GROUP BY DateStamp, Hour, DepartmentNumber, WareNumber
  ORDER BY DateStamp, Hour, DepartmentNumber, WareNumber
END
ELSE
BEGIN
  SELECT 
    DateStamp,
    ISNULL((SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'SameSystemPrefix'), '') + CAST(DepartmentNumber AS varchar(10)) AS DepartmentNumber,
    CONVERT(VARCHAR, CAST(DATEADD(HOUR, DATEDIFF(HOUR, 0, FromTime), 0) AS TIME), 108) AS Hour,
    SUM(ISNULL(ActualWareCount, 0)) + SUM(ISNULL(BookedWareCount, 0)) AS Sold,
    SUM(ISNULL(ActualPriceInclPackageExVat, 0)) + SUM(ISNULL(BookedPriceInclPackageExVat, 0)) AS TurnoverExVAT,
    SUM(ISNULL(ActualPriceInclPackage, 0)) + SUM(ISNULL(BookedPriceInclPackage, 0)) AS TurnoverInclVAT
  FROM MISTableViewWithTime
  WHERE (@AHotelCustomerNumberCSV = '' OR HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV)))
  AND DateStamp BETWEEN @AFromDate AND @AToDate
  AND ReservationStatus <> 4
  GROUP BY DateStamp, DepartmentNumber, DATEADD(HOUR, DATEDIFF(HOUR, 0, FromTime), 0)
END