CREATE PROCEDURE [dbo].[DKNTRoomTurnover] @HotelCustomerNumber INT, @AStartDate CHAR(8), @AEndDate CHAR(8) AS
SET NOCOUNT ON
/*
  Created by Kent Johansen, October 2022

  Changes:
    KJ 20231122: Bug: Fix for "Wrong number of sold rooms".
    KJ 20231018: Bug: Fix for "Number of rooms wrong"
    KJ 20230607: Added Arr+Dep+Ref
    KJ 20221219: Added PurposeCode
    20221121: Added StayLength, ReservationCreateDate
    20221109: Added a meta data sp. Hotel type moved to this
*/

DECLARE @Enable BIT
DECLARE @LInclProvision BIT

SELECT @Enable = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'DKNTEnable'
SET @Enable = ISNULL(@Enable, 0)

IF @Enable = 1
BEGIN
  SELECT @LInclProvision = CONVERT(BIT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'IncludeProvisionInMIS'
  SET @LInclProvision = ISNULL(@LInclProvision, 0)

  SELECT DISTINCT MISTableView.DateStamp, 
  SUM(MISTableView.ActualPriceExPackageExVat) + SUM(MISTableView.BookedPriceExPackageExVat) AS PriceExclVATExclPackage,
  SUM(MISTableView.ActualPriceExPackage) + SUM(MISTableView.BookedPriceExPackage) AS PriceInclVATExclPackage,
  SUM(MISTableView.ActualNumberOfRooms) + SUM(MISTableView.BookedNumberOfRooms) AS SoldRooms,
  SUM(MISTableView.ActualAdult) + SUM(MISTableView.BookedAdult) AS Adults,
  SUM(MISTableView.ActualChildren1) + SUM(MISTableView.BookedChildren1) + SUM(MISTableView.ActualChildren2) + SUM(MISTableView.BookedChildren2) AS Children,
  MISTableView.SegmentNumber AS SegmentId, Segments.Text AS SegmentName,
  MISTableView.PointOfSaleNumber AS POSId, PointOfSales.Text AS POSName,
  Reservations.GuestType AS GuestTypeId, GuestTypes.Text AS GuestTypeName,
  Reservations.PurposeCode AS PurposeCodeId, PurposeCodes.Text AS PurposeCodeName,
  ReservationAddress.ZipCode, ISNULL(CTRY1.CountryCode, CTRY2.CountryCode) AS CountryCode,
  CASE WHEN Reservations.GuestNumber = 0 THEN 
    (
      SELECT MIN(ArrivalDate)
      FROM ReservationRoomLine WITH (NOLOCK)
      INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLineXTab.LineNumber = ReservationRoomLine.LineNumber
        AND ReservationRoomLineXTab.ReferenceNumber = MISTableView.ReferenceNumber
        AND ReservationRoomLineXTab.GuestNumber = MISTableView.GuestNumber
    )
  ELSE
    Reservations.ArrivalDate
  END AS ArrivalDate1,
  CASE WHEN Reservations.GuestNumber = 0 THEN 
    (
      SELECT MAX(DepartureDate)
      FROM ReservationRoomLine WITH (NOLOCK)
      INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLineXTab.LineNumber = ReservationRoomLine.LineNumber
        AND ReservationRoomLineXTab.ReferenceNumber = MISTableView.ReferenceNumber
        AND ReservationRoomLineXTab.GuestNumber = MISTableView.GuestNumber
    )
  ELSE
    Reservations.DepartureDate
  END AS DepartureDate1,
  Reservations.ReservationCreateDate,
  CONVERT(DATE, Reservations.ArrivalDate, 112) AS ArrivalDate,
  CONVERT(DATE, Reservations.DepartureDate, 112) AS DepartureDate,
  CAST(Reservations.ReferenceNumber AS VARCHAR(15)) + '/' + CAST(Reservations.GuestNumber AS VARCHAR(4)) AS ReferenceNumber
  INTO #tmp
  FROM MISTableView WITH (NOLOCK)
  INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = MISTableView.ReferenceNumber
    AND Reservations.GuestNumber = MISTableView.GuestNumber
  INNER JOIN ReservationAddressXtab WITH (NOLOCK) ON ReservationAddressXtab.ReferenceNumber = MISTableView.ReferenceNumber
    AND ReservationAddressXtab.GuestNumber = MISTableView.GuestNumber
  INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXtab.AddressNumber
    AND ReservationAddress.AddressType = 1
  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 GuestTypes WITH (NOLOCK) ON GuestTypes.TypeNumber = Reservations.GuestType
  LEFT OUTER JOIN PurposeCodes WITH (NOLOCK) ON PurposeCodes.PurposeCodeNumber = Reservations.PurposeCode
  LEFT OUTER JOIN Countries CTRY1 WITH (NOLOCK) ON CTRY1.CountryNumber = ReservationAddress.CountryNumber
  OUTER APPLY (SELECT TOP 1 CountryCode FROM Countries WITH (NOLOCK) WHERE Countries.CountryCode = ReservationAddress.CountryCode) CTRY2
  WHERE MISTableView.Datestamp >= @AStartDate
  AND MISTableView.DateStamp <= @AEndDate
  AND MISTableView.ValueType = 1 --AND MISTableView.MISData IN (1)
  AND MISTableView.ReservationStatus <> 7 -- Cancelled
  AND MISTableView.HotelCustomerNumber = @HotelCustomerNumber
  AND (@LInclProvision = 1 OR ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  GROUP BY MISTableView.DateStamp, MISTableView.SegmentNumber, Segments.Text, MISTableView.PointOfSaleNumber, PointOfSales.Text,
  Reservations.GuestType, GuestTypes.Text, Reservations.PurposeCode, PurposeCodes.Text, ReservationAddress.ZipCode, CTRY1.CountryCode, CTRY2.CountryCode, 
  Reservations.ReservationCreateDate, Reservations.GuestNumber, Reservations.ArrivalDate, Reservations.DepartureDate, Reservations.ReferenceNumber, MISTableView.ReferenceNumber, MISTableView.GuestNumber

  SELECT #tmp.DateStamp, 
  #tmp.PriceExclVATExclPackage,
  #tmp.PriceInclVATExclPackage,
  #tmp.SoldRooms,
  #tmp.Adults,
  #tmp.Children,
  #tmp.SegmentId, #tmp.SegmentName,
  #tmp.POSId, #tmp.POSName,
  #tmp.GuestTypeId, #tmp.GuestTypeName,
  #tmp.PurposeCodeId, #tmp.PurposeCodeName,
  #tmp.ZipCode, #tmp.CountryCode,
  DATEDIFF(DAY, #tmp.ReservationCreateDate, CONVERT(DATETIME, #tmp.ArrivalDate1, 112)) AS LeadDays,
  DATEDIFF(DAY, CONVERT(DATETIME, #tmp.ArrivalDate1, 112), CONVERT(DATETIME, #tmp.DepartureDate1, 112)) AS StayLength,
  #tmp.ReservationCreateDate,
  #tmp.ArrivalDate,
  #tmp.DepartureDate,
  #tmp.ReferenceNumber
  FROM #tmp

  DROP TABLE #tmp
END