CREATE PROCEDURE dbo.MISMonthReportHistory @AHotelCustomerNumberCSV VARCHAR(1000), @AHotelNumberCSV VARCHAR(1000), @ASegmentNumberCSV VARCHAR(1000), @APOSNumberCSV VARCHAR(1000), @ARunDate DATETIME, @AFromDate DATETIME, @AToDate DATETIME, 
  @AInclVAT BIT, @AInclPackage BIT, @AMISValueType VARCHAR(50), @AInclCorrection BIT AS

/*
  Created by Kent Johansen, May 2020
  Changes:
    KJ 20240119: Bug: Fix for "Wrong amounts when getting numbers for ADR calc."
    KJ 20231106: Bug: Fix for "Amount excl. VAT and excl. Package is wrong (VATOfPrice is the total VAT and not only VAT of Room/hall turnover)"
    KJ 20230929: Bug: Fix for "OOO wrong for historydate"
    KJ 20230914: Ext: Added POS. Req #4208
    KJ 20230123: Bug: Fix for "Numbers for last period same date only contains current month". Case #CR227726
    KJ 20210219: Optimized SQL
    KJ 20210120: Ext: ADR calculated as in MIS (turnover ex VAT and Packages and manually charged).
*/

SET NOCOUNT ON

DECLARE @StartDateStr CHAR(8)
DECLARE @EndDateStr CHAR(8)
DECLARE @NumberOfRooms INT
DECLARE @ARunDateRoomBookingStatus DATETIME

SET @StartDateStr = CONVERT(CHAR(8), @AFromDate, 112)
SET @EndDateStr = CONVERT(CHAR(8), @AToDate, 112)

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

SELECT TOP 1 @ARunDateRoomBookingStatus = SystemDate 
FROM RoomBookingStatusHist WITH (NOLOCK) 
WHERE SystemDate <= CAST(@ARunDate AS DATE)
ORDER BY SystemDate DESC

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 ((DateStamp >= @ARunDate) OR (StaffID IN (SELECT UserIdNumber FROM #Users)))
AND ValueType = 1

SELECT DISTINCT FK_ReferenceNumber
INTO #tmpRefHist
FROM MISHistory WITH (NOLOCK)
WHERE DateStamp BETWEEN @AFromDate AND @AToDate
AND ((DateStamp >= @ARunDate) OR (FK_StaffID IN (SELECT UserIdNumber FROM #Users)))
AND ValueType = 1

SELECT
DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112)) AS MonthIdx,
DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112)) AS YearIdx, MISHistory.FK_SegmentNumber, MISHistory.FK_PointOfSaleNumber,
SUM(NumberOfRooms) AS SoldRoomsADR
INTO #MISTmpADRRoomHist
FROM MISHistory WITH (NOLOCK)
WHERE MISHistory.DateStamp BETWEEN @AFromDate AND @AToDate
AND MISHistory.SystemDate = @ARunDate
AND MISHistory.FK_HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
AND MISHistory.FK_ReferenceNumber IN (SELECT FK_ReferenceNumber FROM #tmpRefHist)
AND MISHistory.FK_ReferenceNumber > 0
AND ValueType = 1
AND (@ASegmentNumberCSV = '' OR MISHistory.FK_SegmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ASegmentNumberCSV)))
AND (@APOSNumberCSV = '' OR MISHistory.FK_PointOfSaleNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@APOSNumberCSV)))
GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112)), 
  DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112)),
  MISHistory.FK_SegmentNumber, MISHistory.FK_PointOfSaleNumber

SELECT 
DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112)) AS MonthIdx,
DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112)) AS YearIdx,
MISHistory.FK_SegmentNumber, MISHistory.FK_PointOfSaleNumber,
CASE WHEN @AInclPackage = 1 THEN
  CASE WHEN @AInclVAT = 1 THEN
    SUM(MISHistory.Price)
  ELSE  
    SUM(MISHistory.Price) - SUM(VATOfPrice)
  END
ELSE
  CASE WHEN @AInclVAT = 1 THEN
    SUM(MISHistory.Price) - SUM(Package)
  ELSE  
    SUM(MISHistory.Price) - SUM(Package) - (SUM(VATOfPrice) -SUM(VATOfPackage))
  END
END AS TurnoverADR
INTO #MISTmpADRTurnoverHist
FROM MISHistory WITH (NOLOCK) 
WHERE MISHistory.DateStamp BETWEEN @AFromDate AND @AToDate
AND MISHistory.SystemDate = @ARunDate
AND MISHistory.FK_HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
AND MISHistory.ValueType = 1
AND MISHistory.FK_ReferenceNumber > 0
AND (@ASegmentNumberCSV = '' OR MISHistory.FK_SegmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ASegmentNumberCSV)))
AND (@APOSNumberCSV = '' OR MISHistory.FK_PointOfSaleNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@APOSNumberCSV)))
AND ((MISHistory.DateStamp >= @ARunDate) OR (MISHistory.FK_StaffID IN (SELECT UserIdNumber FROM #Users)))
GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112)), 
  DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112)),
  MISHistory.ValueType, MISHistory.FK_SegmentNumber, MISHistory.FK_PointOfSaleNumber

--select * from #MISTmpADRRoomHist order by yearidx, MonthIdx, SegmentNumber
--select * from #MISTmpADRTurnoverHist order by yearidx, MonthIdx, SegmentNumber

SELECT
DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS MonthIdx,
DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS YearIdx, MISTableView.SegmentNumber, MISTableView.PointOfSaleNumber,
SUM(ActualNumberOfRooms) + SUM(BookedNumberOfRooms) AS SoldRoomsADR
INTO #MISTmpADRRoom
FROM MISTableView WITH (NOLOCK)
WHERE MISTableView.DateStamp >= @AFromDate 
AND MISTableView.DateStamp < @ARunDate
AND MISTableView.DateStamp <= @AToDate
AND MISTableView.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
AND MISTableView.ReferenceNumber IN (SELECT ReferenceNumber FROM #tmpRef)
AND MISTableView.ReferenceNumber > 0
AND ValueType = 1
AND ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18)
AND (@ASegmentNumberCSV = '' OR MISTableView.SegmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ASegmentNumberCSV)))
AND (@APOSNumberCSV = '' OR MISTableView.PointOfSaleNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@APOSNumberCSV)))
GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)), 
  DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)),
  MISTableView.SegmentNumber, MISTableView.PointOfSaleNumber

SELECT 
DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS MonthIdx,
DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS YearIdx,
MISTableView.SegmentNumber, MISTableView.PointOfSaleNumber,
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 TurnoverADR
INTO #MISTmpADRTurnover
FROM MISTableView WITH (NOLOCK) 
WHERE MISTableView.DateStamp >= @AFromDate 
AND MISTableView.DateStamp < @ARunDate
AND MISTableView.DateStamp <= @AToDate
AND MISTableView.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
AND MISTableView.ValueType = 1
AND MISTableView.ReferenceNumber > 0
AND ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18)
AND (@ASegmentNumberCSV = '' OR MISTableView.SegmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ASegmentNumberCSV)))
AND (@APOSNumberCSV = '' OR MISTableView.PointOfSaleNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@APOSNumberCSV)))
AND MISTableView.StaffID IN (SELECT UserIdNumber FROM #Users) 
GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)), 
  DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)),
  MISTableView.ValueType, MISTableView.SegmentNumber, MISTableView.PointOfSaleNumber

--select * from #MISTmpADRRoom order by yearidx, MonthIdx, SegmentNumber
--select * from #MISTmpADRTurnover order by yearidx, MonthIdx, SegmentNumber

SELECT DateStamp, ISNULL(NumberOfOutOfOrder, 0) AS NumberOfOutOfOrder
INTO #tmpAvailabilityHist
FROM RoomBookingStatusHist
WHERE SystemDate = @ARunDateRoomBookingStatus

SELECT
CASE WHEN @AInclCorrection = 0 THEN
  MIN(#MISTmpADRTurnoverHist.TurnoverADR) 
ELSE
  CASE WHEN @AInclPackage = 1 THEN
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISHistory.Price)
    ELSE  
      SUM(MISHistory.Price) - SUM(VATOfPrice)
    END
  ELSE
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISHistory.Price) - SUM(Package)
    ELSE  
      SUM(MISHistory.Price) - SUM(Package) - (SUM(VATOfPrice) -SUM(VATOfPackage))
    END
  END
END AS Turnover,
SUM(MISHistory.Adult) + SUM(MISHistory.Children1) + SUM(MISHistory.Children2) AS Pax,
CASE WHEN @AInclCorrection = 0 THEN
  MIN(#MISTmpADRRoomHist.SoldRoomsADR)
ELSE
  SUM(MISHistory.NumberOfRooms)
END AS SoldRooms,
0 AS OOO,
DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112)) AS MonthIdx,
DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112)) AS YearIdx,
MISHistory.ValueType, MISHistory.FK_SegmentNumber AS SegmentNumber, Segments.Text AS SegmentName, MISHistory.FK_PointOfSaleNumber, PointOfSales.Text AS POSName
INTO #MISTmp
FROM MISHistory WITH (NOLOCK)
LEFT OUTER JOIN Segments WITH (NOLOCK) ON Segments.Number = MISHistory.FK_SegmentNumber
LEFT OUTER JOIN PointOfSales WITH (NOLOCK) ON PointOfSales.Number = MISHistory.FK_PointOfSaleNumber
LEFT OUTER JOIN #MISTmpADRRoomHist WITH (NOLOCK) ON #MISTmpADRRoomHist.YearIdx = DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112))
  AND #MISTmpADRRoomHist.MonthIdx = DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112))
  AND #MISTmpADRRoomHist.FK_SegmentNumber = MISHistory.FK_SegmentNumber
  AND #MISTmpADRRoomHist.FK_PointOfSaleNumber = MISHistory.FK_PointOfSaleNumber
LEFT OUTER JOIN #MISTmpADRTurnoverHist WITH (NOLOCK) ON #MISTmpADRTurnoverHist.YearIdx = DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112))
  AND #MISTmpADRTurnoverHist.MonthIdx = DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112))
  AND #MISTmpADRTurnoverHist.FK_SegmentNumber = MISHistory.FK_SegmentNumber
  AND #MISTmpADRTurnoverHist.FK_PointOfSaleNumber = MISHistory.FK_PointOfSaleNumber
WHERE MISHistory.DateStamp BETWEEN @AFromDate AND @AToDate
AND MISHistory.SystemDate = @ARunDate
AND MISHistory.FK_HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
AND MISHistory.ValueType IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AMISValueType))
AND (@ASegmentNumberCSV = '' OR MISHistory.FK_SegmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ASegmentNumberCSV)))
AND (@APOSNumberCSV = '' OR MISHistory.FK_PointOfSaleNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@APOSNumberCSV)))
GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112)), 
  DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112)),
  MISHistory.ValueType, MISHistory.FK_SegmentNumber, Segments.Text, MISHistory.FK_PointOfSaleNumber, PointOfSales.Text 

UNION

SELECT
CASE WHEN @AInclCorrection = 0 THEN
  MIN(#MISTmpADRTurnover.TurnoverADR) 
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,
SUM(MISTableView.BookedAdult) + SUM(MISTableView.ActualAdult) + SUM(MISTableView.BookedChildren1) + SUM(MISTableView.ActualChildren1) + SUM(MISTableView.BookedChildren2) + SUM(MISTableView.ActualChildren2) AS Pax,
CASE WHEN @AInclCorrection = 0 THEN
  MIN(#MISTmpADRRoom.SoldRoomsADR)
ELSE
  SUM(MISTableView.BookedNumberOfRooms) + SUM(MISTableView.ActualNumberOfRooms) 
END AS SoldRooms,
0 AS OOO,
DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS MonthIdx,
DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS YearIdx,
MISTableView.ValueType, MISTableView.SegmentNumber, Segments.Text AS SegmentName, MISTableView.PointOfSaleNumber, PointOfSales.Text AS POSName
FROM MISTableView WITH (NOLOCK)
LEFT OUTER JOIN Segments WITH (NOLOCK) ON Segments.Number = MISTableView.SegmentNumber
LEFT OUTER JOIN PointOfSales WITH (NOLOCK) ON PointOfSales.Number = MISTableView.PointOfSaleNumber
LEFT OUTER JOIN #MISTmpADRRoom WITH (NOLOCK) ON #MISTmpADRRoom.YearIdx = DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112))
  AND #MISTmpADRRoom.MonthIdx = DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112))
  AND #MISTmpADRRoom.SegmentNumber = MISTableView.SegmentNumber
  AND #MISTmpADRRoom.PointOfSaleNumber = MISTableView.PointOfSaleNumber
LEFT OUTER JOIN #MISTmpADRTurnover WITH (NOLOCK) ON #MISTmpADRTurnover.YearIdx = DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112))
  AND #MISTmpADRTurnover.MonthIdx = DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112))
  AND #MISTmpADRTurnover.SegmentNumber = MISTableView.SegmentNumber
  AND #MISTmpADRTurnover.PointOfSaleNumber = MISTableView.PointOfSaleNumber
WHERE MISTableView.DateStamp >= @AFromDate 
AND MISTableView.DateStamp < @ARunDate
AND MISTableView.DateStamp <= @AToDate
AND MISTableView.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
AND MISTableView.ReferenceNumber > 0
AND MISTableView.ValueType IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AMISValueType))
AND ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18)
AND (@ASegmentNumberCSV = '' OR MISTableView.SegmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ASegmentNumberCSV)))
AND (@APOSNumberCSV = '' OR MISTableView.PointOfSaleNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@APOSNumberCSV)))
GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)), 
  DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)),
  MISTableView.ValueType, MISTableView.SegmentNumber, Segments.Text, MISTableView.PointOfSaleNumber, PointOfSales.Text

SELECT @NumberOfRooms = COUNT(*) 
FROM Rooms WITH (NOLOCK) 
WHERE HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberCSV))

SELECT 1 AS Total, -1 AS SegmentNumber, '' AS SegmentName, -1 AS PointOfSaleNumber, '' AS POSName,
MonthIdx, YearIdx,
SUM(Turnover) AS Turnover, SUM(Pax) AS Pax, SUM(SoldRooms) AS SoldRooms, 
ISNULL((
  SELECT SUM(#tmpAvailabilityHist.NumberOfOutOfOrder)
  FROM #tmpAvailabilityHist
  WHERE DATEPART(MONTH, CONVERT(DATETIME, #tmpAvailabilityHist.DateStamp, 112)) = MonthIdx
  AND DATEPART(Year, CONVERT(DATETIME, #tmpAvailabilityHist.DateStamp, 112)) = YearIdx
), 0) AS OOO,
SUM(Turnover) / (CASE WHEN ISNULL(SUM(Pax), 0) = 0 THEN 1 ELSE SUM(Pax) END) AS TurnoverPerPax, 
@NumberOfRooms AS NumberOfRooms, 
(
  ISNULL((@NumberOfRooms * dbo.DaysInMonth(YearIdx, MonthIdx)) -
  ISNULL((
    SELECT SUM(#tmpAvailabilityHist.NumberOfOutOfOrder)
    FROM #tmpAvailabilityHist
    WHERE DATEPART(MONTH, CONVERT(DATETIME, #tmpAvailabilityHist.DateStamp, 112)) = MonthIdx
    AND DATEPART(Year, CONVERT(DATETIME, #tmpAvailabilityHist.DateStamp, 112)) = YearIdx
  ), 0), 0)
) AS Capacity,
SUM(Turnover) /
  CASE WHEN 
    (
      (@NumberOfRooms * dbo.DaysInMonth(YearIdx, MonthIdx)) -
        ISNULL((
        SELECT SUM(#tmpAvailabilityHist.NumberOfOutOfOrder)
        FROM #tmpAvailabilityHist
        WHERE DATEPART(MONTH, CONVERT(DATETIME, #tmpAvailabilityHist.DateStamp, 112)) = MonthIdx
        AND DATEPART(Year, CONVERT(DATETIME, #tmpAvailabilityHist.DateStamp, 112)) = YearIdx
      ), 0)
    ) = 0  
  THEN 
    1 
  ELSE 
    (
      (@NumberOfRooms * dbo.DaysInMonth(YearIdx, MonthIdx)) -
        ISNULL((
        SELECT SUM(#tmpAvailabilityHist.NumberOfOutOfOrder)
        FROM #tmpAvailabilityHist
        WHERE DATEPART(MONTH, CONVERT(DATETIME, #tmpAvailabilityHist.DateStamp, 112)) = MonthIdx
        AND DATEPART(Year, CONVERT(DATETIME, #tmpAvailabilityHist.DateStamp, 112)) = YearIdx
      ), 0)
    ) 
  END 
AS RevPAR,
SUM(Turnover) / (CASE WHEN ISNULL(SUM(SoldRooms), 0) = 0 THEN 1 ELSE SUM(SoldRooms) END) AS ADR
FROM #MISTmp
GROUP BY MonthIdx, YearIdx

UNION

SELECT 0 AS Total, SegmentNumber, SegmentName, FK_PointOfSaleNumber, POSName,
MonthIdx, YearIdx,
Turnover AS Turnover, 0, SoldRooms, 0,
0,
0,
0,
0,
0
FROM #MISTmp
ORDER BY Total, MonthIdx

DROP TABLE #MISTmp
DROP TABLE #tmpAvailabilityHist
DROP TABLE #Users
DROP TABLE #tmpRef
DROP TABLE #tmpRefHist
DROP TABLE #MISTmpADRRoomHist
DROP TABLE #MISTmpADRTurnoverHist
DROP TABLE #MISTmpADRRoom
DROP TABLE #MISTmpADRTurnover