USP_DATAFORMTEMPLATE_VIEW_SALESORDER_ONLINE_ETICKETS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SALESORDER_ONLINE_ETICKETS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @CURRENTAPPUSERID uniqueidentifier,
    @ORDERNUMBER integer = null output,
    @ORDERSTATUSCODE tinyint = null output,
    @ORGANIZATIONNAME nvarchar(100) = null output,
    @TICKETS xml = null output
)
as
    set nocount on;
    set @DATALOADED = 0;

    declare @SALESMETHODTYPECODE tinyint = 0;

    select 
        @DATALOADED = 1,
        @ORDERNUMBER = SEQUENCEID,
        @ORDERSTATUSCODE = STATUSCODE,
        @SALESMETHODTYPECODE = SALESMETHODTYPECODE
    from dbo.SALESORDER
    where ID = @ID;

    select top 1 @ORGANIZATIONNAME = NAME
    from dbo.ORGANIZATIONINFORMATION

    if @DATALOADED = 1 and @ORDERSTATUSCODE = 1 and @SALESMETHODTYPECODE in (0,1,2) --Complete Advance, Daily, or Online sale
    begin
        --If tickets haven't been generated for this order, we need to
        if exists(select 1 from dbo.SALESORDERITEM with (nolock) where SALESORDERITEM.SALESORDERID = @ID) --Order has tickets
        begin
            declare @CHANGEAGENTID uniqueidentifier
            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

            declare @CURRENTDATE datetime
            set @CURRENTDATE = getdate()

            insert into dbo.TICKETPRINTINFO
            (
                ID,
                TICKETID,
                APPUSERID,
                ONLINEPRINT,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            select
                newid(),
                TICKET.ID,
                @CURRENTAPPUSERID,
                1,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from dbo.TICKET
            inner join dbo.SALESORDERITEM
                on TICKET.SALESORDERITEMTICKETID = SALESORDERITEM.ID
            where 
                SALESORDERITEM.SALESORDERID = @ID and
                not exists (
                    select 1 
                    from dbo.TICKETPRINTINFO
                    where TICKETID = TICKET.ID
                ) and
                TICKET.STATUSCODE in (0,1) --Not refunded or exchanged
        end

        set @TICKETS = (
            select
                TICKET.ID as TICKETID,
                TICKET.TICKETNUMBER,
                isnull(EVENT.NAME, PROGRAM.NAME) as EVENTNAME,
                EVENT.STARTDATE as EVENTSTARTDATE,
                EVENT.STARTTIME as EVENTSTARTTIME,
                case 
                    when EVENT.ID is not null then
                        (
                            select dbo.UDA_BUILDLISTWITHDELIMITER(EVENTLOCATION.NAME, ', ')
                            from dbo.PROGRAMEVENTLOCATION
                            inner join dbo.EVENTLOCATION
                                on PROGRAMEVENTLOCATION.EVENTLOCATIONID = EVENTLOCATION.ID
                            where PROGRAMEVENTLOCATION.EVENTID = EVENT.ID
                        ) 
                    else
                        (
                            select dbo.UDA_BUILDLISTWITHDELIMITER(EVENTLOCATION.NAME, ', ')
                            from dbo.PROGRAMLOCATION
                            inner join dbo.EVENTLOCATION
                                on PROGRAMLOCATION.EVENTLOCATIONID = EVENTLOCATION.ID
                            where PROGRAMLOCATION.PROGRAMID = PROGRAM.ID
                        )
                end as LOCATIONS,
                PRICETYPECODE.DESCRIPTION as PRICETYPE,
                SALESORDERITEMTICKET.PRICE,
                TICKET.STATUSCODE
            from dbo.TICKET with (nolock)
            inner join dbo.SALESORDERITEMTICKET with (nolock)
                on TICKET.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
            inner join dbo.PRICETYPECODE with (nolock)
                on SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
            inner join dbo.SALESORDERITEM with (nolock)
                on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
            left join dbo.PROGRAM with (nolock)
                on SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
            left join dbo.EVENT with (nolock)
                on SALESORDERITEMTICKET.EVENTID = EVENT.ID
            where SALESORDERITEM.SALESORDERID = @ID
            order by
                EVENTSTARTDATE,
                EVENTSTARTTIME,
                EVENTNAME,
                PRICETYPE,
                TICKETNUMBER
            for xml raw('ITEM'),type,elements,root('TICKETS'),binary base64
        )
    end

    return 0;