CREATE PROCEDURE dbo.SubscriptionReservations @ACustomerNumber INT, @AAddressType INT, @AGetMaster BIT AS
/*
  Created by: Kent Johansen, October 2024
  List reservations for subscription plan (Subscription is considerd to be IsSubscriptions = true, NumberOfUseages (klippekort), wares with period (hold)
  Changes: Added UsedTickets
*/

DECLARE @DateFirst INT
SELECT @DateFirst = @@DATEFIRST
SET DATEFIRST 1 -- monday

SELECT DISTINCT Ware.WareNumber, Ware.WareName, WareTicketType.IsSubscription, CASE WHEN ISNULL(WareTicketTypePeriod.Id, -1) > 0 THEN 1 ELSE 0 END AS TeamTraning, CASE WHEN ISNULL(WareTicketType.NumberOfUsages, 0) > 0 THEN 1 ELSE 0 END AS Coupon
INTO #Waretmp
FROM Ware WITH (NOLOCK)
LEFT OUTER JOIN WareTicketType WITH (NOLOCK) ON WareTicketType.FK_WareNumber = Ware.WareNumber
LEFT OUTER JOIN WareTicketTypePeriod WITH (NOLOCK) ON WareTicketTypePeriod.FK_WareNumber = Ware.WareNumber
WHERE (WareTicketType.IsSubscription = 1 OR ISNULL(WareTicketTypePeriod.Id, -1) > 0 OR ISNULL(WareTicketType.NumberOfUsages, 0) > 0) 

IF @AGetMaster = 1
BEGIN
  SELECT DISTINCT CASE 
    WHEN #Waretmp.IsSubscription = 1 THEN 0
    WHEN #Waretmp.Coupon = 1 THEN 2
    WHEN #Waretmp.TeamTraning = 1 THEN 1
  END AS SubscriptionType,
  CASE WHEN #Waretmp.IsSubscription = 1 THEN ReservationSubscription.SubscriptionDate ELSE ArrangementLines.Date END AS SubscriptionDate, Reservations.ReferenceNumber, Reservations.GuestNumber,
  CASE WHEN ReservationSubscription.TerminationDate = '1899-12-30' THEN NULL ELSE ReservationSubscription.TerminationDate END AS TerminationDate,
  #Waretmp.WareName, Reservations.Status, COALESCE(ReservationAddress.FirstName  + ' ' +  ReservationAddress.LastName, ReservationAddress.LastName) AS Name,
  CAST(CAST(Reservations.ReferenceNumber AS VARCHAR) + '/' + CAST(Reservations.GuestNumber AS VARCHAR) AS VARCHAR) AS ConcRes,
  Customer.Lastname AS HotelName, ReservationSubscription.ArrangementLineUniqueId,
  ResSub.TerminationDate AS TerminationDateCalculated, ResSub.SubscriptionEnd
  FROM Reservations WITH (NOLOCK)
  INNER JOIN ArrangementReservationsXtab WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = Reservations.ReferenceNumber
    AND ArrangementReservationsXtab.GuestNumber = Reservations.GuestNumber
  INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber
  INNER JOIN #Waretmp WITH (NOLOCK) ON #Waretmp.WareNumber = ArrangementLines.WareNumber
  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
  INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
  INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = ReservationMain.FkCustomerHotel
  LEFT OUTER JOIN ReservationSubscription WITH (NOLOCK) ON ReservationSubscription.FK_ReferenceNumber = Reservations.ReferenceNumber
    AND ReservationSubscription.FK_GuestNumber = Reservations.GuestNumber
  OUTER APPLY dbo.SubscriptionGetTerminateDate(ReservationSubscription.FK_ReferenceNumber, ReservationSubscription.FK_GuestNumber, ReservationSubscription.ArrangementLineUniqueId) ResSub
  WHERE ReservationAddress.Link = @ACustomerNumber AND ReservationAddress.AddressType = @AAddressType
  ORDER BY Reservations.ReferenceNumber DESC, Reservations.GuestNumber ASC
END
ELSE
BEGIN
  SELECT ReservationSubscription.ArrangementLineUniqueId, #Waretmp.WareNumber,
  CAST(CAST(Reservations.ReferenceNumber AS VARCHAR) + '/' + CAST(Reservations.GuestNumber AS VARCHAR) AS VARCHAR) AS ConcRes, 
  CAST(CAST(Reservations.ReferenceNumber AS VARCHAR) + '/' + CAST(Reservations.GuestNumber AS VARCHAR) AS VARCHAR) AS RefGuest
  INTO #TicketScan
  FROM Reservations WITH (NOLOCK)
  INNER JOIN ArrangementReservationsXtab WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = Reservations.ReferenceNumber
    AND ArrangementReservationsXtab.GuestNumber = Reservations.GuestNumber
  INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber
  INNER JOIN #Waretmp WITH (NOLOCK) ON #Waretmp.WareNumber = ArrangementLines.WareNumber
  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
  INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
  INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = ReservationMain.FkCustomerHotel
  LEFT OUTER JOIN ReservationSubscription WITH (NOLOCK) ON ReservationSubscription.FK_ReferenceNumber = Reservations.ReferenceNumber
    AND ReservationSubscription.FK_GuestNumber = Reservations.GuestNumber
  OUTER APPLY dbo.SubscriptionGetTerminateDate(ReservationSubscription.FK_ReferenceNumber, ReservationSubscription.FK_GuestNumber, ReservationSubscription.ArrangementLineUniqueId) ResSub
  WHERE ReservationAddress.Link = @ACustomerNumber AND ReservationAddress.AddressType = @AAddressType

  SELECT DISTINCT ArrangementLineArrangementLineXTab.FK_ArrangementLineNumber, #Waretmp.WareNumber,
  CAST(CAST(Reservations.ReferenceNumber AS VARCHAR) + '/' + CAST(Reservations.GuestNumber AS VARCHAR) AS VARCHAR) AS ConcRes
  INTO #Usage
  FROM Reservations WITH (NOLOCK)
  INNER JOIN ArrangementReservationsXtab WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = Reservations.ReferenceNumber
    AND ArrangementReservationsXtab.GuestNumber = Reservations.GuestNumber
  INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber
  INNER JOIN #Waretmp WITH (NOLOCK) ON #Waretmp.WareNumber = ArrangementLines.WareNumber
  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
  INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
  INNER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = ReservationMain.FkCustomerHotel
  LEFT OUTER JOIN ReservationSubscription WITH (NOLOCK) ON ReservationSubscription.FK_ReferenceNumber = Reservations.ReferenceNumber
    AND ReservationSubscription.FK_GuestNumber = Reservations.GuestNumber
  OUTER APPLY dbo.SubscriptionGetTerminateDate(ReservationSubscription.FK_ReferenceNumber, ReservationSubscription.FK_GuestNumber, ReservationSubscription.ArrangementLineUniqueId) ResSub
  LEFT OUTER JOIN ArrangementLineArrangementLineXTab WITH (NOLOCK) ON ArrangementLineArrangementLineXTab.FK_ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
  WHERE ReservationAddress.Link = @ACustomerNumber AND ReservationAddress.AddressType = @AAddressType

-- Ticket scan
  SELECT DateStamp, Ware.WareName, -1 AS ArrangementLineNumber, #TicketScan.ConcRes, #TicketScan.RefGuest, 1 AS UsedTickets
  FROM TicketStatistics WITH (NOLOCK)
  INNER JOIN #TicketScan WITH (NOLOCK) ON #TicketScan.ArrangementLineUniqueID = TicketStatistics.FK_ArrangementUniqueId
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = #TicketScan.WareNumber

  UNION

  -- Usage
  SELECT CONVERT(DATETIME, ArrLinesSub.Date, 112) + CONVERT(DATETIME, ArrLinesSub.Time, 108), Ware.WareName, ArrangementLineArrangementLineXTab.FK_ArrangementLineNumberSub, #usage.ConcRes,
  CAST(CAST(ResSub.ReferenceNumber AS VARCHAR) + '/' + CAST(ResSub.GuestNumber AS VARCHAR) AS VARCHAR) AS RefGuest, 
  ( 
    (
      SELECT ISNULL(WareAddOn.NumberOfTickets, 1)
      FROM WareAddon WITH (NOLOCK)
      WHERE WareAddon.FK_WareNumber = ArrangementLines.WareNumber      
      AND WareAddon.FK_WareNumberRelated = ArrLinesSub.WareNumber
      AND WareAddon.AddOnType = 3 -- WareTL -> TWareRelationType -> Usage
    ) * ISNULL(ArrLinesSub.Pieces, 1)  
  ) AS UsedTickets
  FROM Reservations WITH (NOLOCK)
  INNER JOIN ArrangementReservationsXtab WITH (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = Reservations.ReferenceNumber
    AND ArrangementReservationsXtab.GuestNumber = Reservations.GuestNumber
  INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementReservationsXtab.ArrangementLineNumber
  INNER JOIN ArrangementLineArrangementLineXTab WITH (NOLOCK) ON ArrangementLineArrangementLineXTab.FK_ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
    AND ArrangementLineArrangementLineXTab.RelationType = 1
  INNER JOIN #usage ON #Usage.FK_ArrangementLineNumber = ArrangementLineArrangementLineXTab.FK_ArrangementLineNumber
  INNER JOIN ArrangementLines ArrLinesSub WITH (NOLOCK) ON ArrLinesSub.ArrangementLineNumber = ArrangementLineArrangementLineXTab.FK_ArrangementLineNumberSub
  INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = ArrLinesSub.WareNumber
  INNER JOIN ArrangementReservationsXtab ArrResSub WITH (NOLOCK) ON ArrResSub.ArrangementLineNumber = ArrangementLineArrangementLineXTab.FK_ArrangementLineNumberSub
  INNER JOIN Reservations ResSub WITH (NOLOCK) ON ResSub.ReferenceNumber = ArrResSub.ReferenceNumber
    AND ResSub.GuestNumber = ArrResSub.GuestNumber
  ORDER BY ConcRes DESC, RefGuest 

  DROP TABLE #TicketScan
  DROP TABLE #Usage
END

SET DATEFIRST @DateFirst

DROP TABLE #Waretmp