CREATE FUNCTION dbo.SubscriptionGetTerminateDate (@AReferenceNumber INT, @AGuestNumber INT, @AArrangementLineUniqueId INT)
RETURNS @Result TABLE (TerminationDate DATETIME, SubscriptionEnd DATETIME) AS
/*
  Created by Kent Johansen, June 2024
  Return the earliest termination date (and end of subscription period).

  TerminationType: 
    1: AtCutOffDay: Can bee terminated at cut off day
    2: NumberOfDays: Can be terminated a number of days before renew date
    3: NumberOfMonths: Can be terminated a number of months before renew date
    4: NextPeriodStart: Can be terminated after next period. Danish: Løbende måned plus x-måneder
*/
BEGIN
--  DECLARE @LShowPrint BIT
--  SET @LShowPrint = 0

  DECLARE @LTerminationDate DATETIME

  DECLARE @LSubscriptionFirst DATETIME
  DECLARE @LSubscriptionLast DATETIME

  DECLARE @LPeriodStartType INT
  DECLARE @LTerminationType INT
  DECLARE @LTerminationCount INT
  DECLARE @LTerminationInitMonths INT
  DECLARE @LCutOffDays INT
  DECLARE @LCutOffDate DATETIME

  DECLARE @LCurDT DATETIME
  DECLARE @LDT DATE
  DECLARE @LPeriodStart DATETIME
  DECLARE @LWareNumber INT

  SELECT @LSubscriptionFirst = SubscriptionDate, @LWareNumber = ArrangementLines.WareNumber
  FROM ReservationSubscription WITH (NOLOCK)
  INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementLines.ArrangementLineUniqueId = ReservationSubscription.ArrangementLineUniqueId
  WHERE ReservationSubscription.ArrangementLineUniqueId = @AArrangementLineUniqueId

  SELECT @LSubscriptionFirst = CONVERT(DATE, MIN(ArrangementLines.Date), 112), @LSubscriptionLast = CONVERT(DATE, MAX(ArrangementLines.Date), 112)
  FROM ArrangementLines WITH (NOLOCK)
  INNER JOIN ArrangementReservationsXtab WITH (NOLOCK) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
    AND ArrangementReservationsXtab.ReferenceNumber = @AReferenceNumber
    AND ArrangementReservationsXtab.GuestNumber = @AGuestNumber
  WHERE ArrangementLines.WareNumber = @LWareNumber

  SELECT @LTerminationType = ISNULL(TerminationType, 0), @LTerminationCount = ISNULL(TerminationCount, 0), @LTerminationInitMonths = ISNULL(TerminationInitMonths, 0), 
    @LCutOffDays = ISNULL(WareTicketType.CutOffDays, 0), @LPeriodStartType = WareTicketType.PeriodStartType
  FROM WareTicketType WITH (NOLOCK)
  WHERE WareTicketType.FK_WareNumber = @LWareNumber
  
  EXEC @LCurDT = dbo.SystemDateTimeF ''
  SET @LCurDT = CAST(@LCurDT AS DATE)

  -- Check for Termination init
  IF @LTerminationInitMonths > 0
  BEGIN
    SET @LTerminationDate = DATEADD(MONTH, @LTerminationInitMonths, @LSubscriptionFirst)
    /*
    IF @LShowPrint = 1
    BEGIN
      PRINT '@LTerminationDate by TerminationInitMonths'
      PRINT @LTerminationDate
    END
    */
  END

  SELECT @LPeriodStart = PeriodStart, @LSubscriptionLast = PeriodEnd FROM dbo.WareTicketGetPeriodStartEnd (@LPeriodStartType, @LSubscriptionLast)

  SET @LPeriodStart = CAST(@LPeriodStart AS DATE)
  SET @LSubscriptionLast = CAST(@LSubscriptionLast AS DATE)
  SET @LCutOffDate = CAST(DATEADD(DAY, (@LCutOffDays*-1), @LSubscriptionLast) AS DATE)

  /*
  IF @LShowPrint = 1
  BEGIN
    PRINT '@LPeriodStart'
    PRINT @LPeriodStart
    PRINT '@LSubscriptionLast'
    PRINT @LSubscriptionLast
    PRINT '@LCutOffDate'
    PRINT @LCutOffDate
    PRINT '@LSubscriptionEnd'
    PRINT @LSubscriptionEnd
    PRINT '@LCurDT'
    PRINT @LCurDT
  END
  */

  IF @LTerminationType = 0 -- No
  BEGIN
    IF @LCurDT > ISNULL(@LTerminationDate, 0)
      SET @LTerminationDate = CAST(@LCurDT AS DATE)
  END
  ELSE IF @LTerminationType = 1 -- At cut off
  BEGIN
    IF @LCutOffDate > ISNULL(@LTerminationDate, 0)
      SET @LTerminationDate = @LCutOffDate
  END
  ELSE IF @LTerminationType IN (2, 3) -- NumberOfDays / NumberOfMonths
  BEGIN
    IF @LTerminationType = 2
      SET @LDT = DATEADD(DAY, (@LTerminationCount), @LSubscriptionLast)
    ELSE
      SET @LDT = DATEADD(MONTH, (@LTerminationCount), @LSubscriptionLast)

    /*
    IF @LShowPrint = 1
    BEGIN
      PRINT '@LDT BEFORE'
      PRINT @LDT
    END
    */

    IF @LDT < @LCurDT -- Termination date calculated is before today -> add a period length
    BEGIN
      SET @LDT =
        CASE @LPeriodStartType
          WHEN 6 THEN DATEADD(WEEK, 1, @LDT) -- Week
          WHEN 7 THEN DATEADD(MONTH, 1, @LDT) -- Month
          WHEN 8 THEN DATEADD(QUARTER, 1, @LDT) -- Quarter
          WHEN 9 THEN DATEADD(YEAR, 1, @LDT) -- Year
          ELSE
            @LDT
        END

      /*
      IF @LShowPrint = 1
      BEGIN
        PRINT '@LDT AFTER'
        PRINT @LDT
      END
      */
    END
    IF @LDT > ISNULL(@LTerminationDate, 0)
      SET @LTerminationDate = @LDT
  END
  ELSE IF @LTerminationType = 4 -- Before start of period
  BEGIN
    IF @LSubscriptionLast > ISNULL(@LTerminationDate, 0)
      SET @LTerminationDate = @LSubscriptionLast
  END
  INSERT INTO @Result (TerminationDate, SubscriptionEnd)
  VALUES (@LTerminationDate, @LSubscriptionLast)
  RETURN
END