USP_DATAFORMTEMPLATE_VIEW_REGISTRANTPROFILE

The load procedure used by the view dataform template "Registrant Summary View 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.
@ISREGISTRANTPROFILE	bit	INOUT	ISREGISTRANTPROFILE
@ADDRESS	nvarchar(300)	INOUT	ADDRESS
@PHONENUMBER	nvarchar(100)	INOUT	PHONENUMBER
@PHONETYPE	nvarchar(100)	INOUT	PHONETYPE
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	EMAILADDRESS
@WEBADDRESS	UDT_WEBADDRESS	INOUT	WEBADDRESS
@PICTURE	varbinary	INOUT	PICTURE
@REGISTRANTGUESTCOUNT	int	INOUT	No. of guests
@REGISTRANTSTATUS	nvarchar(28)	INOUT	Status
@GROUPNAME	nvarchar(100)	INOUT	Group name
@CONSTITUENTNAME	nvarchar(700)	INOUT	Name
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@ISGUEST	bit	INOUT	ISGUEST
@GUESTOFREGISTRANTID	uniqueidentifier	INOUT	GUESTOFREGISTRANTID
@GUESTOFREGISTRANTNAME	nvarchar(700)	INOUT	GUESTOFREGISTRANTNAME
@EVENTID	uniqueidentifier	INOUT	EVENTID
@EVENTNAME	nvarchar(100)	INOUT	EVENTNAME
@ATTENDED	bit	INOUT	ATTENDED
@WILLNOTATTEND	bit	INOUT	WILLNOTATTEND
@ONLINEREGISTRANT	bit	INOUT	ONLINEREGISTRANT
@REGISTRANTSEAT	nvarchar(310)	INOUT	Seat

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REGISTRANTPROFILE
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @ISREGISTRANTPROFILE bit = null output,
                    @ADDRESS nvarchar(300) = null output,
                    @PHONENUMBER nvarchar(100) = null output,
                    @PHONETYPE nvarchar(100) = null output,
                    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
                    @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
                    @PICTURE varbinary(max) = null output,
                    @REGISTRANTGUESTCOUNT int = null output,
                    @REGISTRANTSTATUS nvarchar(28) = null output,
                    @GROUPNAME nvarchar(100) = null output,
                    @CONSTITUENTNAME nvarchar(700) = null output,
                    @CONSTITUENTID uniqueidentifier = null output,
                    @ISGUEST bit = null output,
                    @GUESTOFREGISTRANTID uniqueidentifier = null output,
                    @GUESTOFREGISTRANTNAME nvarchar(700) = null output,
                    @EVENTID uniqueidentifier = null output,
                    @EVENTNAME nvarchar(100) = null output,
                    @ATTENDED bit = null output,
                    @WILLNOTATTEND bit = null output,
                    @ONLINEREGISTRANT bit = null output,
                    @REGISTRANTSEAT nvarchar(310) = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

                    select
                        @DATALOADED = 1,
                        @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
                        @PHONENUMBER = PHONE.NUMBER,
                        @PHONETYPE = dbo.UFN_PHONETYPECODE_GETDESCRIPTION(PHONE.PHONETYPECODEID),
                        @EMAILADDRESS = EMAILADDRESS.EMAILADDRESS,
                        @WEBADDRESS = CONSTITUENT.WEBADDRESS,
                        @PICTURE = CONSTITUENT.PICTURETHUMBNAIL,       
                        @REGISTRANTGUESTCOUNT = coalesce((select count(GUESTS.ID) from dbo.REGISTRANT as GUESTS where GUESTS.GUESTOFREGISTRANTID = REGISTRANT.ID),0),
                        @REGISTRANTSTATUS =
                            case
                                when REGISTRANT.ATTENDED = 0 and REGISTRANT.WILLNOTATTEND = 1 then 'Registered (will not attend)'
                                when REGISTRANT.ATTENDED = 0 and REGISTRANT.WILLNOTATTEND = 0 then 'Registered'
                                else 'Attended'
                            end,
                        @CONSTITUENTNAME = NF.NAME,
                        @CONSTITUENTID = REGISTRANT.CONSTITUENTID,
                        @ISGUEST = case when REGISTRANT.GUESTOFREGISTRANTID is null then 0 else 1 end,
                        @GUESTOFREGISTRANTID = REGISTRANT.GUESTOFREGISTRANTID,
                        @GUESTOFREGISTRANTNAME = GUESTOF_NF.NAME,
                        @EVENTID = REGISTRANT.EVENTID,
                        @EVENTNAME = dbo.UFN_EVENT_GETNAME(REGISTRANT.EVENTID),
                        @ATTENDED = REGISTRANT.ATTENDED,
                        @WILLNOTATTEND = REGISTRANT.WILLNOTATTEND,
                        @ONLINEREGISTRANT = REGISTRANT.ONLINEREGISTRANT,
                        @ISREGISTRANTPROFILE = 1, 
                        @REGISTRANTSEAT = dbo.UFN_EVENTSEATING_GETREGISTRANTSEATPATH(REGISTRANT.ID)
                    from
                        dbo.REGISTRANT
                        inner join dbo.CONSTITUENT on REGISTRANT.CONSTITUENTID = CONSTITUENT.ID
                        left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                        left join dbo.PHONE on PHONE.CONSTITUENTID = CONSTITUENT.ID and PHONE.ISPRIMARY = 1
                        left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY = 1
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) NF
                        left join dbo.REGISTRANT as GUESTOF on GUESTOF.ID = REGISTRANT.GUESTOFREGISTRANTID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GUESTOF.CONSTITUENTID) GUESTOF_NF
                    where 
                        REGISTRANT.ID = @ID;

                    --Attempt to load data for an unknown guest if no constituent exists

                    if ((@DATALOADED is null) or (@DATALOADED = 0))
                    begin
                        select
                            @DATALOADED = 1,
                            @ADDRESS = null,
                            @PHONENUMBER = null,
                            @PHONETYPE = null,
                            @EMAILADDRESS = null,
                            @WEBADDRESS = null,
                            @PICTURE = null,
                            @REGISTRANTGUESTCOUNT = coalesce((select count(GUESTS.ID) from dbo.REGISTRANT as GUESTS where GUESTS.GUESTOFREGISTRANTID = REGISTRANT.ID),0),
                            @REGISTRANTSTATUS =
                                case
                                    when REGISTRANT.ATTENDED = 0 and REGISTRANT.WILLNOTATTEND = 1 then 'Registered (will not attend)'
                                    when REGISTRANT.ATTENDED = 0 and REGISTRANT.WILLNOTATTEND = 0 then 'Registered'
                                    else 'Attended'
                                end,
                            @CONSTITUENTNAME = dbo.UFN_REGISTRANT_GETNAME(REGISTRANT.ID),
                            @CONSTITUENTID = REGISTRANT.CONSTITUENTID,
                            @ISGUEST = case when REGISTRANT.GUESTOFREGISTRANTID is null then 0 else 1 end,
                            @GUESTOFREGISTRANTID = REGISTRANT.GUESTOFREGISTRANTID,
                            @GUESTOFREGISTRANTNAME = GUESTOF_NF.NAME,
                            @EVENTID = REGISTRANT.EVENTID,
                            @EVENTNAME = dbo.UFN_EVENT_GETNAME(REGISTRANT.EVENTID),
                            @ATTENDED = REGISTRANT.ATTENDED,
                            @WILLNOTATTEND = REGISTRANT.WILLNOTATTEND,
                            @ONLINEREGISTRANT = REGISTRANT.ONLINEREGISTRANT,
                            @ISREGISTRANTPROFILE = 1, 
                            @REGISTRANTSEAT = dbo.UFN_EVENTSEATING_GETREGISTRANTSEATPATH(REGISTRANT.ID)
                        from
                            dbo.REGISTRANT
                            inner join dbo.REGISTRANT HOST on REGISTRANT.GUESTOFREGISTRANTID = HOST.ID
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(HOST.CONSTITUENTID) GUESTOF_NF
                        where 
                            REGISTRANT.ID = @ID
                            and REGISTRANT.CONSTITUENTID is null;                        
                    end

                    if @DATALOADED = 1
                        select
                            @GROUPNAME = EVENTGROUP.NAME
                        from
                            dbo.EVENTGROUPMEMBER
                            inner join dbo.EVENTGROUP on EVENTGROUP.ID = EVENTGROUPMEMBER.EVENTGROUPID
                        where
                            EVENTGROUPMEMBER.REGISTRANTID = @ID;

                return 0;