USP_DATALIST_CONSTITUENTGROUP_RECENTINTERACTIONSUMMARY

List of four most recent completed interactions for a constituent group.
Parameters

Parameter	Parameter Type	Mode	Description
@GROUPID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@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_CONSTITUENTGROUP_RECENTINTERACTIONSUMMARY
                (
                    @GROUPID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                )
                as
                    set nocount on;

                    declare @CURRENTDATE date;
                    set @CURRENTDATE = getdate();

                    with INTERACTIONS_CTE as
                    (
                            select
                            I.ID,
                            I.CONSTITUENTID,
                            I.DATE,
                            I.INTERACTIONTYPECODEID,
                            I.PROSPECTPLANID,
                            I.FUNDINGREQUESTID
                        from dbo.INTERACTION I
                            inner join dbo.GROUPMEMBER GM on I.CONSTITUENTID = GM.MEMBERID
                            left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                        where GM.GROUPID = @GROUPID
                            and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > I.DATE))
                                or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= I.DATE))
                                or (GMDR.DATEFROM <= I.DATE and GMDR.DATETO > I.DATE))
                            and I.COMPLETED = 1

                        union all

                        select
                            I.ID,
                            I.CONSTITUENTID,
                            I.DATE,
                            I.INTERACTIONTYPECODEID,
                            I.PROSPECTPLANID,
                            I.FUNDINGREQUESTID
                        from dbo.INTERACTION I
                        where I.CONSTITUENTID = @GROUPID
                            and I.COMPLETED = 1
                    )
                    select top(4)
                        I.ID,
                        NF.NAME,
                        I.DATE,
                        dbo.UFN_INTERACTIONTYPECODE_GETDESCRIPTION(I.INTERACTIONTYPECODEID) as 'TYPETRANSLATION'
                    from INTERACTIONS_CTE I
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.CONSTITUENTID) NF
                    where exists (
                            select 1
                            from 
                            (
                                -- Not using UFN_SITEID_MAPFROM_INTERACTIONID so we can avoid hitting the interaction table again.  Any 

                                -- updates to this site check should also be made to UFN_SITEID_MAPFROM_INTERACTIONID

                                select coalesce(PROSPECTPLANSITE.SITEID, FUNDINGPLAN.SITEID, INTERACTIONSITE.SITEID) as SITEID
                                from (select I.ID, I.PROSPECTPLANID, I.FUNDINGREQUESTID) as INTERACTION
                                left outer join dbo.PROSPECTPLANSITE on INTERACTION.PROSPECTPLANID = PROSPECTPLANSITE.PROSPECTPLANID
                                -- Checking if INTERACTION.PROSPECTPLANID is null is the same as calling UFN_INTERACTION_ISPLANSTEP but is much faster

                                left outer join dbo.INTERACTIONSITE on INTERACTIONSITE.INTERACTIONID = INTERACTION.ID and INTERACTION.PROSPECTPLANID is null
                                left outer join dbo.FUNDINGREQUEST on FUNDINGREQUEST.ID = INTERACTION.FUNDINGREQUESTID
                                left outer join dbo.FUNDINGPLAN on FUNDINGPLAN.ID = FUNDINGREQUEST.FUNDINGPLANID
                            ) as INTERACTIONSITE
                            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[INTERACTIONSITE].[SITEID] or (SITEID is null and [INTERACTIONSITE].[SITEID] is null)))
                        )
                    order by I.DATE desc