USP_TICKET_SCAN

CREATE procedure dbo.USP_TICKET_SCAN (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as
    set nocount on;

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @CURRENTDATE datetime = getdate();
    declare @CURRENTDATEWITHTIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(getutcdate(), 1);

    declare @STATUSCODE tinyint;
    declare @CURRENTSCANDATEWITHTIMEOFFSET datetimeoffset;
    declare @SWAPREFUNDTICKETID uniqueidentifier;

    select
        @STATUSCODE = STATUSCODE,
        @CURRENTSCANDATEWITHTIMEOFFSET = SCANDATEWITHTIMEOFFSET
    from
        dbo.TICKET
    where
        ID = @ID;

    begin try
        if @STATUSCODE = 2  -- Refunded

        begin
            set @SWAPREFUNDTICKETID = dbo.UFN_TICKET_GETNEXTACTIVE(@ID);

            if @SWAPREFUNDTICKETID is null
                raiserror('BBERR_NOACTIVETICKETAVAILABLE', 13, 1);

            -- Swap monetary details between scanned and swapped ticket. Updates both at the same time.

            -- Be careful; if you mess with the where clause, you might accidentally update all the tickets in the entire DB. Possibly more than once.

            update dbo.TICKET
            set
                STATUSCODE = SWAPPEDTICKET.STATUSCODE,
                SCANDATEWITHTIMEOFFSET = case when TICKET.ID = @SWAPREFUNDTICKETID then null else @CURRENTDATEWITHTIMEOFFSET end,
                AMOUNTPAID = SWAPPEDTICKET.AMOUNTPAID,
                ITEMLEVELDISCOUNTSAPPLIED = SWAPPEDTICKET.ITEMLEVELDISCOUNTSAPPLIED,
                ORDERLEVELDISCOUNTSAPPLIED = SWAPPEDTICKET.ORDERLEVELDISCOUNTSAPPLIED,
                APPLIEDTOMEMBERSHIP = SWAPPEDTICKET.APPLIEDTOMEMBERSHIP,
                APPLIEDTOMEMBERSHIPSALESORDERID = SWAPPEDTICKET.APPLIEDTOMEMBERSHIPSALESORDERID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from dbo.TICKET
            inner join dbo.TICKET SWAPPEDTICKET on TICKET.ID <> SWAPPEDTICKET.ID
            where
                TICKET.ID in (@ID, @SWAPREFUNDTICKETID)
                and SWAPPEDTICKET.ID in (@ID, @SWAPREFUNDTICKETID);

            -- Move existing refunds also (no financial repercussions, because all relevant money values were moved above).

            update dbo.CREDITITEM_EXT
            set SALESORDERITEMIZEDITEMID =
                case SALESORDERITEMIZEDITEMID
                    when @SWAPREFUNDTICKETID then @ID
                    when @ID then @SWAPREFUNDTICKETID
                    else SALESORDERITEMIZEDITEMID
                end
            where SALESORDERITEMIZEDITEMID in (@ID, @SWAPREFUNDTICKETID);
        end
        else begin
            if @CURRENTSCANDATEWITHTIMEOFFSET is not null
                raiserror('BBERR_ALREADYSCANNED', 13, 1);

            update dbo.TICKET set
                STATUSCODE = 0,  -- Active

                SCANDATEWITHTIMEOFFSET = @CURRENTDATEWITHTIMEOFFSET,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where
                ID = @ID;
        end
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;