USP_SALESORDER_GENERATETICKETS

CREATE procedure dbo.USP_SALESORDER_GENERATETICKETS
(
    @SALESORDERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTDATE datetime,
    @RECREATEALLTICKETS bit = 0
)
as
begin
    set nocount on;

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

    declare @TICKETATTENDANCECODE tinyint;
    select @TICKETATTENDANCECODE = TICKETATTENDANCECODE from dbo.UFN_TICKETSCANNINGCONFIGURATION();

    declare @contextCache varbinary(128);

    begin try
        if exists (
            select *
                from dbo.TICKET
            where 
                SALESORDERITEMTICKETID in (
                    select ID 
                    from dbo.SALESORDERITEM
                    where SALESORDERID = @SALESORDERID
                )
                and STATUSCODE in (0,1)
        ) and @RECREATEALLTICKETS = 0 begin
            raiserror('BBERR_TICKETS_TICKETEXISTS', 13, 1);
        end

        if @RECREATEALLTICKETS = 1 begin
            set @contextCache = CONTEXT_INFO();
            set CONTEXT_INFO @CHANGEAGENTID;

            delete from dbo.TICKET
            where
                TICKET.SALESORDERITEMTICKETID in (
                    select
                        SALESORDERITEM.ID
                    from 
                        dbo.SALESORDERITEM
                    where
                        SALESORDERID = @SALESORDERID
                );

            /* reset CONTEXT_INFO to previous value  */
            if not @contextCache is null
                set CONTEXT_INFO @contextCache;
        end

        insert into dbo.TICKET
        (
            ID,
            SALESORDERITEMTICKETID,
            PROGRAMID,
            EVENTID,
            PRICETYPECODEID,
            PRICE,
            STATUSCODE,
            SCANDATEWITHTIMEOFFSET,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED, 
            DATECHANGED
        )
        select
            newid(), 
            SALESORDERITEMTICKET.ID,
            SALESORDERITEMTICKET.PROGRAMID,
            SALESORDERITEMTICKET.EVENTID,
            SALESORDERITEMTICKET.PRICETYPECODEID,
            SALESORDERITEMTICKET.PRICE,
            0 as STATUSCODE,  -- Active

            case
                when @TICKETATTENDANCECODE = 0 and SALESORDER.SALESMETHODTYPECODE = 0 and (  -- Once the ticket is sold and Daily Sales

                    EVENT.ID is null
                    or EVENT.STARTDATE = convert(date, SALESORDER.TRANSACTIONDATEWITHTIMEOFFSET)
                ) then
                    SALESORDER.TRANSACTIONDATEWITHTIMEOFFSET
                else
                    null
            end as SCANDATEWITHTIMEOFFSET,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from
            dbo.SALESORDERITEMTICKET
        inner join
            dbo.SALESORDERITEM on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
        inner join
            dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
        left outer join
            dbo.EVENT on EVENT.ID = SALESORDERITEMTICKET.EVENTID
        inner join  -- Quantity could be greater than what is in the NUMBERS table, but that isn't realistic in sales

            dbo.NUMBERS on NUMBERS.NUM <= SALESORDERITEM.QUANTITY - 1
        where
            SALESORDERITEM.SALESORDERID = @SALESORDERID;

        --update any associated resources attached to the order which are per ticket

        update dbo.[RESOURCE] set
            [RESOURCE].QUANTITY -= qty,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from (
            select [RESOURCE].ID, sum([SALESORDERITEM].QUANTITY * [RESOURCE].PERTICKETQUANTITY) as qty
            from dbo.[SALESORDERITEM]
                inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
                inner join dbo.[EVENT] on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
                inner join dbo.[EVENTRESOURCE] on [EVENT].[ID] = [EVENTRESOURCE].[EVENTID]
                inner join dbo.[RESOURCE] on [EVENTRESOURCE].RESOURCEID = dbo.[RESOURCE].ID and [RESOURCE].ISPERTICKETITEM <> 0 and [RESOURCE].TYPECODE = 1
            where [SALESORDERITEM].[SALESORDERID] = @SALESORDERID
            group by [RESOURCE].ID) as VT
        where [RESOURCE].ID = VT.ID
    end try

    begin catch
        if not @contextCache is null
            set CONTEXT_INFO @contextCache;

        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

    return 0;                
end