USP_DATAFORMTEMPLATE_VIEW_GROUPMEMBER

The load procedure used by the view dataform template "Group Member View Form"
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@NAME	nvarchar(700)	INOUT	Constituent Name
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@MEMBERSINCE	datetime	INOUT	Member since
@ISINACTIVE	bit	INOUT	Status
@ADDRESS	nvarchar(300)	INOUT	Address
@LOOKUPID	nvarchar(100)	INOUT	Lookup ID
@PHONENUMBER	nvarchar(100)	INOUT	Phone Number
@EMAILADDRESS	nvarchar(100)	INOUT	Email Address
@RELATIONSHIPWITHPRIMARY	nvarchar(max)	INOUT	Relationship with primary
@ACTIVECONSTITUENCIES	nvarchar(max)	INOUT	Active constituencies
@TOTALGIVING	money	INOUT	Total giving
@GROUPS	xml	INOUT	GROUPS
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ISBOARDMEMBER	bit	INOUT	Is board member
@ISSTAFF	bit	INOUT	Is staff
@ISDONOR	bit	INOUT	Is donor
@ISFUNDRAISER	bit	INOUT	Is fundraiser
@ISPROSPECT	bit	INOUT	Is prospect
@ISVOLUNTEER	bit	INOUT	Is volunteer
@ISREGISTRANT	bit	INOUT	Is registrant
@ISVENDOR	bit	INOUT	Is vendor
@ISALUMNUS	bit	INOUT	Is alumnus
@ISRECOGNITION	bit	INOUT	Is recognition constituent
@ISRELATION	bit	INOUT	Is relation
@USERDEFINEDCONSTITUENCIES	xml	INOUT	User-defined constituencies
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ISPLANNEDGIVER	bit	INOUT	Is planned giver
@ISCOMMITTEE	bit	INOUT	ISCOMMITTEE
@ISCOMMITTEEMEMBER	bit	INOUT	ISCOMMITTEEMEMBER
@ISLOYALDONOR	bit	INOUT	Is Loyal donor
@ISMAJORDONOR	bit	INOUT	Is Major donor
@CURRENCYID	uniqueidentifier	INOUT	Currency ID
@ISADVOCATE	bit	INOUT
@ISBANK	bit	INOUT
@ISFACULTY	bit	INOUT
@ISFUNDRAISINGGROUP	bit	INOUT
@ISGRANTOR	bit	INOUT
@ISMEMBER	bit	INOUT
@ISPATRON	bit	INOUT
@ISSPONSOR	bit	INOUT
@ISSTUDENT	bit	INOUT
@GROUPROLES	xml	INOUT
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_GROUPMEMBER (
                    @ID uniqueidentifier,
                    @NAME nvarchar(700) = null output,
                    @CONSTITUENTID uniqueidentifier = null output,
                    @MEMBERSINCE datetime = null output,
                    @ISINACTIVE bit = null output,
                    @ADDRESS nvarchar(300) = null output,
                    @LOOKUPID nvarchar(100) = null output,
                    @PHONENUMBER nvarchar(100) = null output,
                    @EMAILADDRESS nvarchar(100) = null output,
                    @RELATIONSHIPWITHPRIMARY nvarchar(max) = null output,
                    @ACTIVECONSTITUENCIES nvarchar(max) = null output,    
                    @TOTALGIVING money = null output,
                    @GROUPS xml = null output,
                    @DATALOADED bit = 0 output,
                    @ISBOARDMEMBER bit = null output,
                    @ISSTAFF bit = null output,
                    @ISDONOR bit = null output,
                    @ISFUNDRAISER bit = null output,
                    @ISPROSPECT bit = null output,
                    @ISVOLUNTEER bit = null output,
                    @ISREGISTRANT bit = null output,
                    @ISVENDOR bit = null output,
                    @ISALUMNUS bit = null output,
                    @ISRECOGNITION bit = null output,
                    @ISRELATION bit = null output,
                    @USERDEFINEDCONSTITUENCIES xml = null output,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @ISPLANNEDGIVER bit = null output,
                    @ISCOMMITTEE bit = null output,
                    @ISCOMMITTEEMEMBER bit = null output,
                    @ISLOYALDONOR bit = null output,
                    @ISMAJORDONOR bit = null output,
                    @CURRENCYID uniqueidentifier = null output,
                    @ISADVOCATE bit = null output,
                    @ISBANK bit = null output,
                    @ISFACULTY bit = null output,
                    @ISFUNDRAISINGGROUP bit = null output,
                    @ISGRANTOR bit = null output,
                    @ISMEMBER bit = null output,
                    @ISPATRON bit = null output,
                    @ISSPONSOR bit = null output,
                    @ISSTUDENT bit = null output,
                    @GROUPROLES xml = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                    declare @FEATUREID uniqueidentifier
                    set @FEATUREID = '225531f2-2ecb-47d5-8485-d1c17f06557b'
                    declare @FEATURETYPE tinyint 
                    set @FEATURETYPE = 1

                    declare @PRIMARYMEMBERID uniqueidentifier;
                    select  @PRIMARYMEMBERID = MEMBERID 
                    from dbo.GROUPMEMBER 
                    where GROUPID = (select GROUPID from dbo.GROUPMEMBER WHERE ID = @ID)
                        and ISPRIMARY = 1;

                    declare @MULTICURRENCYENABLED bit;
                    set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency'); 

                    if @MULTICURRENCYENABLED = 1
                    begin
                        set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
                    end
                    else
                    begin
                        set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                    end

                    select 
                        @DATALOADED = 1,
                        @NAME = DISPLAYNAME.NAME,
                        @CONSTITUENTID = CONSTITUENT.ID,
                        @MEMBERSINCE = (
                            select top 1 DATEFROM
                            from dbo.GROUPMEMBERDATERANGE 
                            where GROUPMEMBERID = @ID
                                and DATETO is null
                        ),
                        @ISINACTIVE = CONSTITUENT.ISINACTIVE,
                        @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
                        @LOOKUPID = CONSTITUENT.LOOKUPID,
                        @PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER),
                        @EMAILADDRESS = EMAILADDRESS.EMAILADDRESS,
                        @RELATIONSHIPWITHPRIMARY = 
                            case 
                                when GROUPMEMBER.ISPRIMARY = 1 
                                    then ''
                                else (    
                                    select 
                                        dbo.UDA_BUILDLIST(distinct RELATIONSHIPTYPECODE.DESCRIPTION)
                                    from dbo.RELATIONSHIP
                                        inner join dbo.RELATIONSHIPTYPECODE on RELATIONSHIP.RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODE.ID
                                    where CONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                                        and RELATIONSHIP.RECIPROCALCONSTITUENTID = @PRIMARYMEMBERID
                                )
                            end,
                        @ACTIVECONSTITUENCIES = ( --This is just the list of user-defined constituencies, as the others are links now.

                            select dbo.UDA_BUILDLISTWITHDELIMITER(distinct DESCRIPTION, '     ') 
                            from dbo.CONSTITUENCY
                                inner join dbo.CONSTITUENCYCODE on CONSTITUENCY.CONSTITUENCYCODEID = CONSTITUENCYCODE.ID
                            where CONSTITUENCY.CONSTITUENTID = CONSTITUENT.ID
                                and (
                                    (CONSTITUENCY.DATEFROM is null 
                                        and (CONSTITUENCY.DATETO is null 
                                            or CONSTITUENCY.DATETO > @CURRENTDATE
                                        )
                                    )
                                    or (CONSTITUENCY.DATETO is null 
                                        and (CONSTITUENCY.DATEFROM is null 
                                            or CONSTITUENCY.DATEFROM <= @CURRENTDATE
                                        )
                                    ) 
                                    or (CONSTITUENCY.DATEFROM <= @CURRENTDATE 
                                        and CONSTITUENCY.DATETO > @CURRENTDATE
                                    )
                                )
                        ),
                        @TOTALGIVING = dbo.UFN_CONSTITUENT_GETGIFTTOTALINCURRENCY_RESPECTSITE2(GROUPMEMBER.MEMBERID, @CURRENTAPPUSERID, @CURRENCYID, @FEATUREID, @FEATURETYPE),
                        @GROUPS = dbo.UFN_CONSTITUENT_GETGROUPS_TOITEMLISTXML(GROUPMEMBER.MEMBERID),
                        @USERDEFINEDCONSTITUENCIES = (    
                            select distinct CONSTITUENCYCODE.DESCRIPTION
                            from dbo.CONSTITUENCY
                                inner join dbo.CONSTITUENCYCODE on CONSTITUENCY.CONSTITUENCYCODEID = CONSTITUENCYCODE.ID
                            where CONSTITUENCY.CONSTITUENTID = CONSTITUENT.ID
                                and (
                                    (CONSTITUENCY.DATEFROM is null 
                                        and (CONSTITUENCY.DATETO is null 
                                            or CONSTITUENCY.DATETO > @CURRENTDATE
                                        )
                                    )
                                    or (CONSTITUENCY.DATETO is null 
                                        and (CONSTITUENCY.DATEFROM is null 
                                            or CONSTITUENCY.DATEFROM <= @CURRENTDATE
                                        )
                                    ) 
                                    or (CONSTITUENCY.DATEFROM <= @CURRENTDATE 
                                        and CONSTITUENCY.DATETO > @CURRENTDATE
                                    )
                                )
                            for xml raw('ITEM'),type,elements,root('USERDEFINEDCONSTITUENCIES'),BINARY BASE64
                        ),
                        @ISADVOCATE = dbo.UFN_CONSTITUENT_ISADVOCATE(CONSTITUENT.ID),
                        @ISALUMNUS = dbo.UFN_CONSTITUENT_ISALUMNUS(CONSTITUENT.ID),
                        @ISBANK = dbo.UFN_CONSTITUENT_ISBANK(CONSTITUENT.ID),
                        @ISBOARDMEMBER = dbo.UFN_CONSTITUENT_ISBOARDMEMBER(CONSTITUENT.ID),
                        @ISCOMMITTEE = dbo.UFN_CONSTITUENT_ISCOMMITTEE(GROUPMEMBER.MEMBERID),
                        @ISCOMMITTEEMEMBER = dbo.UFN_CONSTITUENT_ISCOMMITTEEMEMBER(GROUPMEMBER.MEMBERID),
                        @ISDONOR = dbo.UFN_CONSTITUENT_ISDONOR(CONSTITUENT.ID),
                        @ISFACULTY = dbo.UFN_CONSTITUENT_ISFACULTY(CONSTITUENT.ID),
                        @ISFUNDRAISER = dbo.UFN_CONSTITUENT_ISFUNDRAISER(CONSTITUENT.ID),
                        @ISFUNDRAISINGGROUP = dbo.UFN_CONSTITUENT_ISFUNDRAISINGGROUP(CONSTITUENT.ID),
                        @ISGRANTOR = dbo.UFN_CONSTITUENT_ISGRANTOR(CONSTITUENT.ID),
                        @ISLOYALDONOR = dbo.UFN_CONSTITUENT_ISLOYALDONOR(CONSTITUENT.ID),
                        @ISMAJORDONOR = dbo.UFN_CONSTITUENT_ISMAJORDONOR(CONSTITUENT.ID),
                        @ISMEMBER = dbo.UFN_CONSTITUENT_ISMEMBER(CONSTITUENT.ID, @CURRENTAPPUSERID),
                        @ISPATRON = dbo.UFN_CONSTITUENT_ISPATRON(CONSTITUENT.ID),
                        @ISPLANNEDGIVER = dbo.UFN_CONSTITUENT_ISPLANNEDGIVER(GROUPMEMBER.MEMBERID),
                        @ISPROSPECT = dbo.UFN_CONSTITUENT_ISPROSPECT(CONSTITUENT.ID),
                        @ISRECOGNITION = dbo.UFN_CONSTITUENT_ISRECOGNITION(CONSTITUENT.ID, @CURRENTAPPUSERID),
                        @ISREGISTRANT = dbo.UFN_CONSTITUENT_ISREGISTRANT(CONSTITUENT.ID),
                        @ISRELATION = dbo.UFN_CONSTITUENT_ISRELATION(CONSTITUENT.ID),
                        @ISSPONSOR = dbo.UFN_CONSTITUENT_ISSPONSOR(CONSTITUENT.ID),                        
                        @ISSTAFF = dbo.UFN_CONSTITUENT_ISSTAFF(CONSTITUENT.ID),
                        @ISSTUDENT = dbo.UFN_CONSTITUENT_ISSTUDENT(CONSTITUENT.ID),
                        @ISVENDOR = dbo.UFN_CONSTITUENT_ISVENDOR(CONSTITUENT.ID),
                        @ISVOLUNTEER = dbo.UFN_CONSTITUENT_ISVOLUNTEER(CONSTITUENT.ID),
                        @GROUPROLES = dbo.UFN_CONSTITUENT_GETCURRENTGROUPROLES_TOITEMLISTXML(GROUPMEMBER.ID)
                    from dbo.GROUPMEMBER
                        inner join dbo.CONSTITUENT on GROUPMEMBER.MEMBERID = CONSTITUENT.ID
                        left join dbo.PHONE on CONSTITUENT.ID = PHONE.CONSTITUENTID and PHONE.ISPRIMARY = 1
                        left join dbo.EMAILADDRESS on CONSTITUENT.ID = EMAILADDRESS.CONSTITUENTID and EMAILADDRESS.ISPRIMARY = 1
                        left join dbo.ADDRESS on CONSTITUENT.ID = ADDRESS.CONSTITUENTID and ADDRESS.ISPRIMARY = 1
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) DISPLAYNAME
                    where GROUPMEMBER.ID = @ID;

                    if @ISLOYALDONOR = 1 or @ISMAJORDONOR = 1
                    begin
                        set @ISDONOR = 0;
                    end