CREATE PROCEDURE dbo.DashBoard2 @AStartDateTime DATETIME, @AEndDateTime DATETIME, @AHotelNumberCSV VARCHAR(500) AS
/*
  Created by Kent Johansen, July 2023
  Changes:
*/

DECLARE @IncludeAllotment BIT
DECLARE @DependsOnOOO BIT
DECLARE @DependsOnOOS BIT
DECLARE @PossibleRooms INT

SELECT @IncludeAllotment = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'VacantRoomsInclAllotment'
SET @IncludeAllotment = ISNULL(@IncludeAllotment, 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)

SELECT HotelNumber, CustomerNumber
INTO #HotelNumbers 
FROM dbo.Customer WITH (NOLOCK)
WHERE CustomerTypeNumber = 7
AND HotelNumber IN (SELECT number FROM DBO.iter_intlist_to_table_No_Dummy(@AHotelNumberCSV))

SELECT @PossibleRooms = COUNT(*)
FROM Rooms WITH (NOLOCK)
INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = Rooms.RoomTypeNumber
WHERE ISNULL(@AHotelNumberCSV, '') = '' OR RoomTypes.HotelNumber IN (SELECT DISTINCT HotelNumber FROM #HotelNumbers)

SELECT 
  MISTableView.Datestamp,
  SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.ActualPriceExPackageExVat) AS Turnover
INTO #MIS
FROM MISTableView WITH (NOLOCK)
WHERE HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM #HotelNumbers)
AND MISTableView.Datestamp BETWEEN @AStartDateTime AND DATEADD(MS, -3, DATEADD(DAY, DATEDIFF(DAY, 0, @AEndDateTime), 1)) 
AND ValueType = 1
GROUP BY MISTableView.Datestamp, ValueType

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 @AStartDateTime, @AEndDateTime, 1, -1, '', 1, 0, '', @AIncludeOccupiedInOFS = 1

SELECT DISTINCT
dtRange.IndividualDate AS DateStamp,
ISNULL((
  SELECT SUM(ISNULL(#tmpAvailability.SoldRooms, 0)) + CASE WHEN @IncludeAllotment = 1 THEN SUM(ISNULL(#tmpAvailability.TotalRoomsAllotment, 0)) ELSE 0 END
  FROM #tmpAvailability
  WHERE RoomTypes.HotelNumber = #tmpAvailability.HotelNumber
  AND #tmpAvailability.DateStamp = dtRange.IndividualDate
), 0) AS SoldRooms,
@PossibleRooms AS PossibleRooms,
(SELECT SUM(Turnover)
FROM #MIS
WHERE #MIS.DateStamp = dtRange.IndividualDate
) AS RoomTurnOver,
CAST(0.00 AS MONEY) AS OccPercent,
0 AS ADR,
0 AS RevPar
INTO #tmp
FROM #tmpAvailability
INNER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = #tmpAvailability.RoomTypeNumber
CROSS APPLY dbo.DateRange(@AStartDateTime, @AEndDateTime, RoomTypes.RoomTypeNumber) AS dtRange
INNER JOIN Customer (NOLOCK) ON RoomTypes.HotelNumber = Customer.HotelNumber 
	AND ISNULL(Customer.MArkAsDeleted, 0) = 0 
	AND CustomerTypeNumber = 7
WHERE ISNULL(@AHotelNumberCSV, '') = '' OR RoomTypes.HotelNumber IN (SELECT DISTINCT HotelNumber FROM #HotelNumbers)

UPDATE #tmp SET 
  OccPercent = CASE WHEN ISNULL(PossibleRooms, 0) > 0 THEN (CAST(SoldRooms AS MONEY) / CAST(PossibleRooms AS MONEY)) * 100.0 ELSE 0 END,
  ADR = ROUND(CASE WHEN ISNULL(SoldRooms, 0) > 0 THEN CAST(RoomTurnOver AS MONEY) / CAST(SoldRooms AS MONEY) ELSE 0 END, 0),
  RevPar = ROUND(CASE WHEN ISNULL(PossibleRooms, 0) > 0 THEN CAST(RoomTurnOver AS MONEY) / CAST(PossibleRooms AS MONEY) ELSE 0 END, 0)

SELECT * FROM #tmp
ORDER BY DateStamp

DROP TABLE #tmpAvailability
DROP TABLE #tmp
DROP TABLE #HotelNumbers
DROP TABLE #MIS