CREATE PROCEDURE dbo.SubscriptionGetReservation @AHotelCustomerNumber INT, @APeriodStartType INT AS
/*
  Created by: Kent Johansen, July 2024
  Return reservations that has subscription that should be renewed.

  PeriodStartType: 0: None, 1: wttpsArrDate, 2: wttpsNextWeek, 3: wttpsNextMonth, 4: wttpsNextQuarter, 5: wttpsNextYear, 6: wttpsCurrentWeek, 7: wttpsCurrentMonth, 8: wttpsCurrentQuarter, 9: wttpsCurrentYear
*/
DECLARE @LShowPrint BIT
SET @LShowPrint = 0

DECLARE @LPeriodStart DATETIME
DECLARE @LPeriodEnd DATETIME

DECLARE @SystemDateTime DATETIME
DECLARE @SystemDateTimeNewPeriod DATETIME
EXEC SystemDateTime '', @SystemDateTime OUTPUT

SET @SystemDateTimeNewPeriod = 
  CASE 
    WHEN @APeriodStartType = 6 THEN DATEADD(WEEK, 1, @SystemDateTime)
    WHEN @APeriodStartType = 7 THEN DATEADD(MONTH, 1, @SystemDateTime)
    WHEN @APeriodStartType = 8 THEN DATEADD(QUARTER, 1, @SystemDateTime)
    WHEN @APeriodStartType = 9 THEN DATEADD(YEAR, 1, @SystemDateTime)
  END

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

SELECT @LPeriodStart = PeriodStart, @LPeriodEnd = PeriodEnd FROM dbo.WareTicketGetPeriodStartEnd (@APeriodStartType, @SystemDateTimeNewPeriod)

SET DATEFIRST @DateFirst

IF @LShowPrint = 1
BEGIN
  PRINT '@SystemDateTime'
  PRINT @SystemDateTime
  PRINT '@SystemDateTimeNewPeriod'
  PRINT @SystemDateTimeNewPeriod
  PRINT '@LPeriodStart'
  PRINT @LPeriodStart
  PRINT '@LPeriodEnd'
  PRINT @LPeriodEnd
END

SELECT 
--CAST(DATEADD(DAY, (WareTicketType.CutOffDays*-1), @LPeriodStart) AS DATE),
ReservationSubscription.*, WareTicketType.*, ReservationMain.HotelNumber, ArrangementLines.Pieces, ArrangementLines.WareNumber,
Ware.WareName, Ware.Price, Ware.Note, @LPeriodStart AS PeriodStart
FROM ReservationSubscription WITH (NOLOCK)
INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationSubscription.FK_ReferenceNumber
  AND Reservations.GuestNumber = ReservationSubscription.FK_GuestNumber
  AND Reservations.Status = 5
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementLines.ArrangementLineUniqueID = ReservationSubscription.ArrangementLineUniqueID
INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = ArrangementLines.WareNumber
INNER JOIN WareTicketType WITH (NOLOCK) ON WareTicketType.FK_WareNumber = Ware.WareNumber
  AND WareTicketType.IsSubscription = 1
  AND WareTicketType.PeriodStartType = @APeriodStartType
WHERE 1=1
AND CAST(@SystemDateTime AS DATE) = CAST(DATEADD(DAY, (WareTicketType.CutOffDays*-1), @LPeriodStart) AS DATE)
AND (TerminationDate IS NULL OR CAST(TerminationDate AS DATE) >= CAST(@LPeriodStart AS DATE))