USP_DATALIST_CONSTITUENTPROFILEDASHBOARDGROUPMEMBERPROSPECTPLANS

List of prospect plans for a constituent group's members for use in the constituent profile report.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Constituent
@ISVISIBLE	bit	IN	Visible
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
Definition

Copy


                    CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROFILEDASHBOARDGROUPMEMBERPROSPECTPLANS
                    (
                        @CONSTITUENTID uniqueidentifier,
                        @ISVISIBLE bit = 1,
                        @CURRENTAPPUSERID uniqueidentifier,
                        @SECURITYFEATUREID uniqueidentifier = null,
                        @SECURITYFEATURETYPE tinyint = null
                    )
                    as
                        set nocount on;

                        declare @ISADMIN bit;
                        declare @APPUSER_IN_NONRACROLE bit;
                        declare @APPUSER_IN_NOSECGROUPROLE bit;
                        declare @APPUSER_IN_NONSITEROLE bit;
                        declare @APPUSER_IN_NOSITEROLE bit;

                        set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                        set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                        set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
                        set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
                        set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

                        if @ISVISIBLE = 1
                        begin
                            declare @CURRENTDATE date;
                            set @CURRENTDATE = getdate();

                            select 
                                C.ID as [PROSPECTID],
                                C_NF.NAME as [PROSPECTNAME],
                                PPT.DESCRIPTION as [PROSPECTPLANTYPE],
                                PP.NAME as [PROSPECTPLANNAME],
                                dbo.UFN_PROSPECTPLAN_GETSITELIST(PP.ID) as [SITENAME],
                                PM_NF.NAME as [PRIMARYMANAGERNAME],
                                PSC.DESCRIPTION as [CURRENTSTAGE],
                                LI.ACTUALDATE as [LASTDATE],
                                NI.EXPECTEDDATE as [NEXTDATE]
                            from dbo.GROUPMEMBER GM
                            inner join dbo.PROSPECTPLAN PP on PP.PROSPECTID = GM.MEMBERID
                            left outer join dbo.CONSTITUENT C on C.ID = PP.PROSPECTID
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) C_NF
                            left outer join dbo.PROSPECTPLANTYPECODE PPT on PPT.ID = PP.PROSPECTPLANTYPECODEID
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PRIMARYMANAGERFUNDRAISERID) PM_NF
                            left outer join dbo.PROSPECTPLANSTATUSCODE PSC on PSC.ID = PP.PROSPECTPLANSTATUSCODEID
                            left outer join dbo.INTERACTION LI on LI.ID = dbo.UFN_PROSPECTPLAN_GETLASTSTEP(PP.ID)
                            left outer join dbo.INTERACTION NI on NI.ID = dbo.UFN_PROSPECTPLAN_GETNEXTSTEP(PP.ID)
                            left outer join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                            where 
                                GM.GROUPID = @CONSTITUENTID
                                and (
                                    select count(*) 
                                    from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as PROSPECTPLANSITE 
                                    where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
                                ) > 0
                                and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                                or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                                or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
                                and (@ISADMIN = 1 or 
                                        (@APPUSER_IN_NONRACROLE = 1 or
                                        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, GM.MEMBERID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                                        and
                                        (@APPUSER_IN_NONSITEROLE = 1 or
                                        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, GM.MEMBERID, @APPUSER_IN_NOSITEROLE) = 1)
                                    )
                            order by C.KEYNAME, C.FIRSTNAME, PPT.DESCRIPTION
                        end