CREATE PROCEDURE dbo.WareTicketScanCheck @AArrangementLineUniqueId INT, @ATicketDate DATETIME, @AEndDate DATETIME, @AErrorCode INT OUTPUT, @ALastUsed DATETIME OUTPUT, @ARemainingUsages INT OUTPUT AS
/*
  Created by Kent Johansen, March 2024
  Used to check if ticket is valid
  PeriodStartType: 0:None, 1:Arr.Date 2:NextWeek 3:NextMonth 4:NextQuater 5:NextYear 6:CurrentWeek 7:CurrentMonth 8:CurrentQuater 9:CurrentYear
  @ATicketDate;
    NULL: Ticket scan
    NOT NULL: Usage (a reservation/arrangementline is about to be created

  ErrorCodes:
    0: Sucess
    1: Arragementline does not exists or no ticket period is set
    2: To many usages
    3: Period type does not match
    4: Period does not match
    5: Checkin performed after termination date
    6: To many active booking

  Changes:
    20250317: Ext: Added max number of active reservations
    20241204: Ext: Added AEndDate for validate on arrangement line
    20241121: Bug; Fix for "Number of usages not calculated correctly"
    20241119: Bug: Number of usages not calculated correctly
    20241011: Check for termination date
    20241003: Ext: Use for usage from reservation. Lübker project. Usage can be done from arrangementline on reservation (no ticket scan/usage).
    20240530: Bug: If period time not set, then error code 4 Period does not match is always returned.
*/

SET @AErrorCode = 0
SET @ARemainingUsages = 0

DECLARE @LShowPrint BIT
SET @LShowPrint = 0

IF @LShowPrint = 1
BEGIN
  PRINT 'TicketScanCheck'
  PRINT '@AArrangementLineUniqueId'
  PRINT @AArrangementLineUniqueId
  PRINT '@ATicketDate'
  PRINT @ATicketDate
  PRINT '@AEndDate'
  PRINT @AEndDate
END

DECLARE @SystemDate DATETIME
DECLARE @RealDateTime DATETIME

DECLARE @LArrLineNumber INT
DECLARE @LPieces INT
DECLARE @LArrDate DATETIME
DECLARE @LWareNumber INT
DECLARE @LNumberofUsages INT
DECLARE @LNumberOfDays INT
DECLARE @LPeriodStartType INT
DECLARE @LCntPeriod INT
DECLARE @LUsageUntilToday INT
DECLARE @LUsageUntilTodayReservations INT
DECLARE @LPeriodStart DATETIME
DECLARE @LPeriodEnd DATETIME
DECLARE @cnt INT
DECLARE @LTicketDate DATETIME
DECLARE @LTicketEndDate DATETIME
DECLARE @LTerminationDate DATETIME
DECLARE @LMaxBookings INT
DECLARE @LNumberOfBookings INT

SELECT @SystemDate = CONVERT(DATE, SystemDate) FROM System WITH (NOLOCK)
EXEC SystemDateTime '', @RealDateTime OUTPUT
SET @LTicketDate = ISNULL(@ATicketDate, @RealDateTime)
SET @LTicketEndDate = ISNULL(@AEndDate, @RealDateTime)

SELECT @LArrLineNumber = ArrangementLines.ArrangementLineNumber, @LPieces = ArrangementLines.Pieces, @LArrDate = CONVERT(DATETIME, ArrangementLines.Date, 112) + CONVERT(DATETIME, ArrangementLines.Time, 108), @LWareNumber = ArrangementLines.WareNumber,
  @LNumberOfUsages = WareTicketType.NumberOfUsages, @LNumberOfDays = WareTicketType.NumberOfDays, @LPeriodStartType = WareTicketType.PeriodStartType, @LMaxBookings = MaxNumberOfActiveBookings,
  @ALastUsed = (
    SELECT TOP 1 DateStamp
    FROM TicketStatistics WITH (NOLOCK)
    WHERE ArrangementLineUniqueID = @AArrangementLineUniqueId
    ORDER BY Id DESC
  ),
  @LCntPeriod = (
    SELECT COUNT(*)
    FROM WareTicketTypePeriod WITH (NOLOCK)
    WHERE WareTicketTypePeriod.FK_WareNumber = ArrangementLines.WareNumber
  ),
  @LNumberOfBookings = (
    SELECT COUNT(*)
    FROM ArrangementLineArrangementLineXTab WITH (NOLOCK)
    INNER JOIN ArrangementReservationsXtab WITH (NOLOCK) ON ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLineArrangementLineXTab.FK_ArrangementLineNumberSub
    INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ArrangementReservationsXtab.ReferenceNumber
      AND Reservations.GuestNumber = ArrangementReservationsXtab.GuestNumber
      AND Reservations.Status < 5 
    WHERE ArrangementLineArrangementLineXTab.FK_ArrangementLineNumber = ArrangementLines.ArrangementLineNumber
  )

FROM ArrangementLines WITH (NOLOCK)
INNER JOIN Ware WITH (NOLOCK) ON Ware.WareNumber = ArrangementLines.WareNumber
INNER JOIN WareTicketType WITH (NOLOCK) ON WareTicketType.FK_WareNumber = ArrangementLines.WareNumber
WHERE ArrangementLines.ArrangementLineUniqueID = @AArrangementLineUniqueId

IF @LShowPrint = 1
BEGIN
  PRINT '@LPieces'
  PRINT @LPieces
  PRINT '@LArrDate'
  PRINT @LArrDate
  PRINT '@LWareNumber'
  PRINT @LWareNumber
  PRINT '@LNumberofUsages'
  PRINT @LNumberofUsages
  PRINT '@LNumberOfDays'
  PRINT @LNumberOfDays
  PRINT '@LPeriodStartType'
  PRINT @LPeriodStartType
  PRINT '@ALastUsed'
  PRINT @ALastUsed
  PRINT '@LCntPeriod'
  PRINT @LCntPeriod
  PRINT '@LMaxBookings'
  PRINT @LMaxBookings
END

IF ISNULL(@LArrLineNumber, 0) = 0
  SET @AErrorCode = 1

-- Check for to many usages
IF @AErrorCode = 0 AND ISNULL(@LNumberOfUsages, 0) > 0
BEGIN
  -- Simple usage of coupon/ticket. Where ticket is scan at checkin
  SELECT @LUsageUntilToday = COUNT(*)
  FROM TicketStatistics WITH (NOLOCK)
  WHERE FK_ArrangementUniqueId = @AArrangementLineUniqueId

  -- Also see if any reservations has been made
  -- Booked with usage relation
  SELECT @LUsageUntilTodayReservations = SUM(ISNULL(ArrSub.Pieces, 1) * ISNULL(WareAddOn.NumberOfTickets, 1))
  FROM ArrangementLineArrangementLineXTab WITH (NOLOCK)
  INNER JOIN ArrangementLines WITH (NOLOCK) ON ArrangementLines.ArrangementLineNumber = ArrangementLineArrangementLineXTab.FK_ArrangementLineNumber
  INNER JOIN ArrangementLines ArrSub WITH (NOLOCK) ON ArrSub.ArrangementLineNumber = ArrangementLineArrangementLineXTab.FK_ArrangementLineNumberSub
  INNER JOIN WareAddon WITH (NOLOCK) ON WareAddon.FK_WareNumber = ArrangementLines.WareNumber
    AND WareAddon.FK_WareNumberRelated = ArrSub.WareNumber
    AND WareAddon.AddOnType = 3 -- WareTL -> TWareRelationType -> Usage
  WHERE ArrangementLineArrangementLineXTab.FK_ArrangementLineNumber = @LArrLineNumber
  AND ArrangementLineArrangementLineXTab.RelationType = 1 -- ArrangementTL -> TArrLineArrLineRelationType -> Usage
  
  SET @LUsageUntilToday = ISNULL(@LUsageUntilToday, 0) + ISNULL(@LUsageUntilTodayReservations, 0)
  SET @ARemainingUsages = @LNumberOfUsages - @LUsageUntilToday
  IF @ARemainingUsages <= 0
    SET @AErrorCode = 2
END

-- Check for number of days/PeriodType
IF @AErrorCode = 0
BEGIN
  IF @LPeriodStartType > 0 -- no period is set
  BEGIN
    DECLARE @DateFirst INT
    SELECT @DateFirst = @@DATEFIRST
    SET DATEFIRST 1 -- monday

    -- Calc period from ticket vare
    SELECT @LPeriodStart = PeriodStart, @LPeriodEnd = PeriodEnd FROM dbo.WareTicketGetPeriodStartEnd (@LPeriodStartType, @LArrDate)

    SET DATEFIRST @DateFirst

    IF @LShowPrint = 1
    BEGIN
      PRINT '@LPeriodStart1'
      PRINT @LPeriodStart
      PRINT '@LPeriodEnd1'
      PRINT @LPeriodEnd
    END

    IF ISNULL(@LNumberOfDays, 0) > 0
      SET @LPeriodEnd = DATEADD(DAY, @LNumberOfDays, @LPeriodStart)

    IF @LShowPrint = 1
    BEGIN
      PRINT '@LPeriodStart2'
      PRINT @LPeriodStart
      PRINT '@LPeriodEnd2'
      PRINT @LPeriodEnd
    END

    -- Is ticket date within calcualted period
    IF @LTicketDate < @LPeriodStart OR @LTicketEndDate > @LPeriodEnd
      SET @AErrorCode = 3
  END
END

-- Check for period
IF @AErrorCode = 0 AND ISNULL(@LCntPeriod, 0) > 0
BEGIN
  IF @LShowPrint = 1
    PRINT 'Check for period'

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

  SELECT @cnt = COUNT(*)
  FROM WareTicketTypePeriod WITH (NOLOCK)
  WHERE FK_WareNumber = @LWareNumber
  AND (ISNULL(WareTicketTypePeriod.DayType, 0) = 0 OR POWER(2, DATEPART(dw, @LTicketDate)-1) & WareTicketTypePeriod.DayType > 0) -- match weekday
  AND @LTicketDate >= WareTicketTypePeriod.PeriodFrom 
  AND @LTicketEndDate <= WareTicketTypePeriod.Periodto
  AND ((TimeFrom IS NULL AND TimeTo IS NULL) OR (
    (CAST(@LTicketDate AS TIME) >= WareTicketTypePeriod.TimeFrom) AND (CAST(@LTicketEndDate AS TIME) <= WareTicketTypePeriod.TimeTo)
  ))

  IF ISNULL(@cnt, 0) = 0
    SET @AErrorCode = 4

  SET DATEFIRST @DateFirst
END

-- Checkin performed after termination date
IF @AErrorCode = 0
BEGIN
  IF @LShowPrint = 1
    PRINT 'Check for Checkin performed after termination date'

  SELECT @LTerminationDate = TerminationDate
  FROM ReservationSubscription WITH (NOLOCK)
  WHERE ArrangementLineUniqueID = @AArrangementLineUniqueId

  IF ISNULL(@LTerminationDate, 0) > 0 AND @LTicketDate > @LTerminationDate
    SET @AErrorCode = 5
END

-- Check for max number of bookigs
IF @AErrorCode = 0 AND ISNULL(@LMaxBookings, 0) > 0
BEGIN
  IF @LShowPrint = 1
    PRINT 'Check for max number of active reservations'

  SELECT @LTerminationDate = TerminationDate
  FROM ReservationSubscription WITH (NOLOCK)
  WHERE ArrangementLineUniqueID = @AArrangementLineUniqueId

  IF ISNULL(@LNumberOfBookings, 0) >= ISNULL(@LMaxBookings, 0)
    SET @AErrorCode = 6
END