USP_DATAFORMTEMPLATE_VIEW_TICKETSCANNINGVALIDATION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_TICKETSCANNINGVALIDATION
(
    @ID nvarchar(max),
    @DATALOADED bit = 0 output,
    @TICKETID uniqueidentifier = null output,
    @RESULTCODE tinyint = null output,
    @PROGRAMNAME nvarchar(100) = null output,
    @EVENTNAME nvarchar(100) = null output,
    @EVENTSTARTDATETIME datetime = null output,
    @PRICETYPE nvarchar(100) = null output,
    @CONSTITUENTNAME nvarchar(max) = null output,
    @TICKETSCANDATETIME datetime = null output,
    @TICKETNUMBER int = null output
)
as
    set nocount on;

    declare @SEPARATORINDEX int;
    declare @LOOKUPID nvarchar(max);
    declare @ONLINESUFFIX nvarchar(4);

    declare @LATESTTICKETNUMBER int;
    declare @TICKETSTATUSCODE tinyint;
    declare @EVENTSTARTDATEWITHTIMEOFFSET datetimeoffset;

    declare @SWAPREFUNDTICKETID uniqueidentifier;

    declare @MINUTESBEFORECUTOFF int;
    declare @MINUTESAFTERCUTOFF int;
    declare @CURRENTDATEWITHTIMEOFFSET datetimeoffset;

    -- Normally you wouldn't do this, but one of the result
    -- codes of this view form for not finding a ticket.
    set @DATALOADED = 1;
    set @TICKETID = null;
    set @RESULTCODE = 0;  -- Valid

    set @SEPARATORINDEX = charindex('~', @ID);

    if @SEPARATORINDEX > 0
    begin
        set @LOOKUPID = left(@ID, @SEPARATORINDEX - 1);
        -- For now, online barcodes will have the last for of the ID
        set @ONLINESUFFIX = right(@ID, 4);
    end

    else
    begin
        set @LOOKUPID = @ID
    end

    set @LOOKUPID = '8-' + @LOOKUPID;

    select
        @TICKETID = TICKETPRINTINFO.TICKETID,
        @TICKETNUMBER = TICKETPRINTINFO.SEQUENCEID,
        @LATESTTICKETNUMBER = TICKET.TICKETNUMBER,
        @TICKETSTATUSCODE = TICKET.STATUSCODE,
        @TICKETSCANDATETIME = TICKET.SCANDATEWITHTIMEOFFSET,
        @PRICETYPE = PRICETYPECODE.DESCRIPTION,
        @PROGRAMNAME = PROGRAM.NAME,
        @EVENTNAME = EVENT.NAME,
        @EVENTSTARTDATETIME = EVENT.STARTDATETIME,
        @EVENTSTARTDATEWITHTIMEOFFSET = EVENT.STARTDATETIMEWITHOFFSET,
        @CONSTITUENTNAME = NF.NAME
    from
        dbo.TICKETPRINTINFO
    inner join
        dbo.TICKET on TICKET.ID = TICKETPRINTINFO.TICKETID
    inner join
        dbo.SALESORDERITEM on SALESORDERITEM.ID = TICKET.SALESORDERITEMTICKETID
    inner join
        dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
    inner join
        dbo.PRICETYPECODE on PRICETYPECODE.ID = TICKET.PRICETYPECODEID
    left outer join
        dbo.PROGRAM on PROGRAM.ID = TICKET.PROGRAMID
    left outer join
        dbo.EVENT on EVENT.ID = TICKET.EVENTID
    outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(SALESORDER.RECIPIENTID) as NF
    where
        TICKETPRINTINFO.LOOKUPID = @LOOKUPID
        and (
            @ONLINESUFFIX is null
            or @ONLINESUFFIX = right(convert(nvarchar(36), TICKET.ID), 4)
        );

    if @TICKETID is null
    begin
        set @RESULTCODE = 1;  -- Not found
    end

    else
    begin
        if @TICKETSTATUSCODE = 2  -- Refunded
        begin
            set @SWAPREFUNDTICKETID = dbo.UFN_TICKET_GETNEXTACTIVE(@TICKETID);

            if @SWAPREFUNDTICKETID is null
            begin
                set @RESULTCODE = 5;  -- Ticket was refunded
                return 0;
            end

            -- We set that there is a valid replacement,
            -- but don't return so we can continue validating
            set @RESULTCODE = 6;  -- Refunded with valid replacement
        end

        if @TICKETNUMBER <> @LATESTTICKETNUMBER
        begin
            set @RESULTCODE = 2;  -- Ticket was reprinted
        end

        else if @TICKETSCANDATETIME is not null
        begin
            set @RESULTCODE = 3;  -- Already scanned
        end

        else
        begin
            if @EVENTSTARTDATETIME is not null
            begin
                select
                    @CURRENTDATEWITHTIMEOFFSET = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(getutcdate(), 1),
                    @MINUTESBEFORECUTOFF = MINUTESBEFORECUTOFF,
                    @MINUTESAFTERCUTOFF = MINUTESAFTERCUTOFF
                from
                    dbo.UFN_TICKETSCANNINGCONFIGURATION();

                -- Adding an extra minute and subracting a millisecond to include the full minute of the minute after start setting
                if not @CURRENTDATEWITHTIMEOFFSET between dateadd(minute, -@MINUTESBEFORECUTOFF, @EVENTSTARTDATEWITHTIMEOFFSET) and dateadd(millisecond, -1, dateadd(minute, @MINUTESAFTERCUTOFF + 1, @EVENTSTARTDATEWITHTIMEOFFSET))
                begin
                    set @RESULTCODE = 4  -- Outside of entry times
                end
            end
        end
    end

    return 0;