USP_DATAFORMTEMPLATE_EDIT_PREREGISTEREDPROGRAMEVENTTICKET_PRELOAD

The load procedure used by the edit dataform template "Preregistered Program Event Ticket Edit Data Form"
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@SALESORDERID	uniqueidentifier	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT	Host
@SALESMETHODTYPECODE	tinyint	INOUT
@REGISTRANTS	xml	INOUT	Registrants
@EVENTID	uniqueidentifier	INOUT
@OCCURSTODAY	bit	INOUT
@MARKREGISTRANTSATTENDED	bit	INOUT	Mark registrants as attended
Definition

Copy

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PREREGISTEREDPROGRAMEVENTTICKET_PRELOAD
                    (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @TSLONG bigint = 0 output,
                        @SALESORDERID uniqueidentifier = null output,
                        @CONSTITUENTID uniqueidentifier = null output,
                        @SALESMETHODTYPECODE tinyint = null output,
                        @REGISTRANTS xml = null output,
                        @EVENTID uniqueidentifier = null output,
                        @OCCURSTODAY bit = null output,
                        @MARKREGISTRANTSATTENDED bit = null output
                    )
                    as
                        set nocount on;

                        set @DATALOADED = 0;
                        set @TSLONG = 0;

                        select
                            @SALESMETHODTYPECODE = SALESORDER.SALESMETHODTYPECODE,
                            @SALESORDERID = SALESORDER.ID,
                            @EVENTID = SALESORDERITEMTICKET.EVENTID,
                            @TSLONG = SALESORDERITEM.TSLONG,
                            @DATALOADED = 1
                        from dbo.SALESORDERITEM
                        inner join dbo.SALESORDERITEMTICKET
                            on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                        inner join dbo.SALESORDER
                            on SALESORDERITEM.SALESORDERID = SALESORDER.ID
                        where SALESORDERITEM.ID = @ID;

                        if @DATALOADED = 1
                        begin
                            --7/18/11: Constituent is the host of the registrants -- no longer the patron on the order
                            declare @HOSTREGISTRANTID uniqueidentifier
                            select top 1 @HOSTREGISTRANTID = coalesce([REGISTRANT].[GUESTOFREGISTRANTID], [REGISTRANT].[ID])
                            from dbo.[REGISTRANT]
                            inner join dbo.[SALESORDERITEMTICKETREGISTRANT]
                                on [REGISTRANT].[ID] = [SALESORDERITEMTICKETREGISTRANT].[REGISTRANTID]
                            inner join dbo.[SALESORDERITEMTICKET]
                                on [SALESORDERITEMTICKETREGISTRANT].[SALESORDERITEMTICKETID] = [SALESORDERITEMTICKET].[ID]
                            inner join dbo.[SALESORDERITEM]
                                on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEM].[ID]
                            cross apply (
                                select case 
                                    when [REGISTRANT].[GUESTOFREGISTRANTID] is null then 1
                                    else 0
                                end as [VALUE]
                            ) [ISHOST]
                            where
                                [SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
                                [SALESORDERITEMTICKET].[EVENTID] = @EVENTID
                            order by [ISHOST].[VALUE] asc

                            select @CONSTITUENTID = [CONSTITUENTID]
                            from dbo.[REGISTRANT]
                            where [ID] = @HOSTREGISTRANTID

                            set @REGISTRANTS = (
                                select 
                                    REGISTRANT.ID,
                                    SALESORDERITEMTICKET.PRICETYPECODEID,
                                    REGISTRANT.CONSTITUENTID,
                                    REGISTRANT.NOTES,
                                    (
                                        select
                                            REGISTRANTPREFERENCE.ID,
                                            REGISTRANTPREFERENCE.EVENTPREFERENCEID,
                                            EVENTPREFERENCE.EVENTPREFERENCEGROUPID
                                        from dbo.REGISTRANTPREFERENCE
                                        inner join dbo.EVENTPREFERENCE
                                            on REGISTRANTPREFERENCE.EVENTPREFERENCEID = EVENTPREFERENCE.ID
                                        where 
                                            REGISTRANTPREFERENCE.REGISTRANTID = REGISTRANT.ID
                                        for xml raw('ITEM'), type, elements, root('PREFERENCES'), binary base64
                                    )
                                from dbo.SALESORDERITEM
                                inner join dbo.SALESORDERITEMTICKET
                                    on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                                left join dbo.SALESORDERITEMTICKETREGISTRANT
                                    on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETREGISTRANT.SALESORDERITEMTICKETID
                                left join dbo.REGISTRANT
                                    on 
                                        SALESORDERITEMTICKETREGISTRANT.REGISTRANTID = REGISTRANT.ID and
                                        REGISTRANT.WILLNOTATTEND = 0
                                where 
                                    SALESORDERITEM.SALESORDERID = @SALESORDERID and
                                    SALESORDERITEMTICKET.EVENTID = @EVENTID
                                for xml raw('ITEM'), type, elements, root('REGISTRANTS'), binary base64
                            )

                            set @OCCURSTODAY = dbo.UFN_EVENT_OCCURSTODAY(@EVENTID)

                            if @OCCURSTODAY = 1
                            begin
                                -- If all of the registrants are marked as attended default checkbox to be checked
                                if not exists 
                                (
                                    select 
                                        1
                                    from dbo.REGISTRANT
                                    inner join dbo.SALESORDERITEMTICKETREGISTRANT
                                        on REGISTRANT.ID = SALESORDERITEMTICKETREGISTRANT.REGISTRANTID
                                    inner join dbo.SALESORDERITEMTICKET
                                        on SALESORDERITEMTICKETREGISTRANT.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
                                    inner join dbo.SALESORDERITEM
                                        on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
                                    where 
                                        SALESORDERITEM.SALESORDERID = @SALESORDERID and
                                        SALESORDERITEMTICKET.EVENTID = @EVENTID and
                                        REGISTRANT.WILLNOTATTEND = 0 and
                                        REGISTRANT.ATTENDED = 0
                                )
                                    set @MARKREGISTRANTSATTENDED = 1
                                else
                                    set @MARKREGISTRANTSATTENDED = 0

                            end
                            else
                                set @MARKREGISTRANTSATTENDED = 0
                        end

                        return 0;