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

  Changes:
    KJ 20230607: Added Arr+Dep+Ref
    KJ 20221219: Added PurposeCode
*/
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 MISTableView.DateStamp, 
  SUM(MISTableView.ActualOnlyPackageExVat) + SUM(MISTableView.BookedOnlyPackageExVat) AS PriceExclVAT,
  SUM(MISTableView.ActualOnlyPackage) + SUM(MISTableView.BookedOnlyPackage) AS PriceInclVAT,
  SUM(MISTableView.ActualWareCount) + SUM(MISTableView.BookedWareCount) AS Pieces,
  WareGroups.Text AS WareGroupName, Ware.WareName As WareName,
  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,
  DATEDIFF(
    DAY,
    Reservations.ReservationCreateDate,
    CASE WHEN Reservations.GuestNumber = 0 THEN 
      CONVERT(DATETIME, ReservationRoomLine.ArrivalDate, 112) 
    ELSE 
      CONVERT(DATETIME, Reservations.ArrivalDate, 112) 
    END
  ) AS LeadDays, 0 AS IsAdditional,
  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
  FROM MISTableView WITH (NOLOCK)
  INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = MISTableView.ReferenceNumber
    AND Reservations.GuestNumber = MISTableView.GuestNumber
  INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber
    AND ReservationRoomLineXTab.GuestNumber = Reservations.GuestNumber
  INNER JOIN ReservationRoomLine WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber
  INNER JOIN ReservationAddressXtab WITH (NOLOCK) ON ReservationAddressXtab.ReferenceNumber = Reservations.ReferenceNumber
    AND ReservationAddressXtab.GuestNumber = Reservations.GuestNumber
  INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXtab.AddressNumber
    AND ReservationAddress.AddressType = 1
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = MISTableView.WareNumber
  INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
  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.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))
  AND IsPackage = 1
  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, ReservationRoomLine.ArrivalDate, Reservations.ArrivalDate,
  WareGroups.Text, Ware.WareName, Reservations.ReferenceNumber, Reservations.DepartureDate

  UNION

  SELECT MISTableView.DateStamp, 
  SUM(MISTableView.ActualPriceInclPackageExVat) + SUM(MISTableView.BookedPriceInclPackageExVat) AS PriceExclVAT,
  SUM(MISTableView.ActualPriceInclPackage) + SUM(MISTableView.BookedPriceInclPackage) AS PriceInclVAT,
  SUM(MISTableView.ActualWareCount) + SUM(MISTableView.BookedWareCount) AS Pieces,
  WareGroups.Text AS WareGroupName, Ware.WareName As WareName,
  MISTableView.SegmentNumber AS SegmentId, Segments.Text AS SegmentName,
  MISTableView.PointOfSaleNumber AS POSId, PointOfSales.Text AS POSName,
  -1 AS GuestTypeId, '' AS GuestTypeName,
  -1, '',
  '', '' AS CountryCode,
  0 LeadDays, 1 AS IsAdditional, NULL, NULL, CAST(-1 AS VARCHAR)
  FROM MISTableView WITH (NOLOCK)
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = MISTableView.WareNumber
  INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
  LEFT OUTER JOIN Segments WITH (NOLOCK) ON Segments.Number = MISTableView.SegmentNumber
  LEFT OUTER JOIN PointOfSales WITH (NOLOCK) ON PointOfSales.Number = MISTableView.PointOfSaleNumber
  WHERE MISTableView.Datestamp >= @AStartDate
  AND MISTableView.DateStamp <= @AEndDate
  AND MISTableView.ValueType IN (3,4) -- Restaurant/Other
  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,
    WareGroups.Text, Ware.WareName
END