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

/*
  Created by Kent Johansen, April 2020
  Changes:
  KJ 20210222: Optimized SQL
  KJ 20210219: Optimized SQL
  KJ 20210120: Ext: ADR calculated as in MIS (turnover ex VAT and Packages and manually charged).
  KJ 20201123: Bug: Fix for "Only include values from reservations"
*/

SET NOCOUNT ON

DECLARE @StartDateStr CHAR(8)
DECLARE @EndDateStr CHAR(8)
DECLARE @NumberOfRooms INT
DECLARE @CurrentDateStr CHAR(8)
SELECT @CurrentDateStr = SystemDate FROM System WITH (NOLOCK) WHERE Number = 1

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
)

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

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

SELECT
DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS MonthIdx,
DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS YearIdx, MISTableView.SegmentNumber,
SUM(ActualNumberOfRooms) + SUM(BookedNumberOfRooms) AS SoldRoomsADR
INTO #MISTmpADRRoom
FROM MISTableView WITH (NOLOCK)
WHERE MISTableView.DateStamp BETWEEN @StartDateStr AND @EndDateStr
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)))
GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)), 
  DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)),
  MISTableView.SegmentNumber

SELECT 
DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS MonthIdx,
DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS YearIdx,
MISTableView.SegmentNumber,
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 BETWEEN @StartDateStr AND @EndDateStr
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 (MISTableView.StaffID IN (SELECT UserIdNumber FROM #Users) OR (MISTableView.DateStamp >= @CurrentDateStr)) 
GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)), 
  DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)),
  MISTableView.ValueType, MISTableView.SegmentNumber

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

INSERT INTO #tmpAvailability 
  EXEC GenerateAvailability @StartDateStr, @EndDateStr, 1, @AHotelNumberCSV, '', 1, 0, '', @AIncludeOccupiedInOFS = 1

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,
(
  SELECT SUM(#tmpAvailability.NumberOfOutOfOrder)
  FROM #tmpAvailability
  WHERE DATEPART(MONTH, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112))
  AND DATEPART(YEAR, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112))
) 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
INTO #MISTmp 
FROM MISTableView WITH (NOLOCK)
LEFT OUTER JOIN Segments WITH (NOLOCK) ON Segments.Number = MISTableView.SegmentNumber
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
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
WHERE MISTableView.DateStamp BETWEEN @StartDateStr AND @EndDateStr
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)))
GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)), 
  DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)),
  MISTableView.ValueType, MISTableView.SegmentNumber, Segments.Text

--select * from #MISTmp order by yearidx, MonthIdx, SegmentNumber

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, MonthIdx, YearIdx,
SUM(Turnover) AS Turnover, SUM(Pax) AS Pax, SUM(SoldRooms) AS SoldRooms, 
ISNULL((
  SELECT SUM(#tmpAvailability.NumberOfOutOfOrder)
  FROM #tmpAvailability
  WHERE DATEPART(MONTH, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = MonthIdx
  AND DATEPART(Year, CONVERT(DATETIME, #tmpAvailability.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(#tmpAvailability.NumberOfOutOfOrder)
    FROM #tmpAvailability
    WHERE DATEPART(MONTH, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = MonthIdx
    AND DATEPART(Year, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = YearIdx
  ), 0), 0)
) AS Capacity,
SUM(Turnover) /
  CASE WHEN 
    (
      (@NumberOfRooms * dbo.DaysInMonth(YearIdx, MonthIdx)) -
        ISNULL((
        SELECT SUM(#tmpAvailability.NumberOfOutOfOrder)
        FROM #tmpAvailability
        WHERE DATEPART(MONTH, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = MonthIdx
        AND DATEPART(Year, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = YearIdx
      ), 0)
    ) = 0  
  THEN 
    1 
  ELSE 
    (
      (@NumberOfRooms * dbo.DaysInMonth(YearIdx, MonthIdx)) -
        ISNULL((
        SELECT SUM(#tmpAvailability.NumberOfOutOfOrder)
        FROM #tmpAvailability
        WHERE DATEPART(MONTH, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = MonthIdx
        AND DATEPART(Year, CONVERT(DATETIME, #tmpAvailability.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, MonthIdx, YearIdx,
Turnover AS Turnover, 0, SoldRooms, 0,
0,
0,
0,
0,
0
FROM #MISTmp
ORDER BY Total, MonthIdx

DROP TABLE #MISTmp
DROP TABLE #tmpAvailability
DROP TABLE #Users
DROP TABLE #tmpRef
DROP TABLE #MISTmpADRRoom
DROP TABLE #MISTmpADRTurnover