USP_DATALIST_CONSTITUENT_PENDINGINTERACTIONS
List of pending interactions for a given constituent.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @DATEFILTER | tinyint | IN | Date |
| @STARTDATE | datetime | IN | Start date |
| @ENDDATE | datetime | IN | End date |
| @TYPEFILTER | uniqueidentifier | IN | Contact method |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @SITEFILTERMODE | tinyint | IN | Sites |
| @SITESSELECTED | xml | IN | |
| @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. |
| @SHOWCOMPLETED | bit | IN | Show completed interactions |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_CONSTITUENT_PENDINGINTERACTIONS(
@CONSTITUENTID uniqueidentifier,
@DATEFILTER tinyint = 0,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@TYPEFILTER uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@SHOWCOMPLETED bit = 0
)
as begin
set nocount on;
declare @DATE datetime;
set @DATE = getdate();
if @DATEFILTER = 0 -- all
begin
set @STARTDATE = null;
set @ENDDATE = null;
end
else if @DATEFILTER = 1 -- this year
begin
set @STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@DATE, 0);
end
else if @DATEFILTER = 2 -- this quarter
begin
set @STARTDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@DATE, 0);
end
else if @DATEFILTER = 3 -- this month
begin
set @STARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@DATE, 0);
end
else if @DATEFILTER = 4 -- this week
begin
set @STARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@DATE, 0);
end
else if @DATEFILTER = 5 -- next week
begin
set @STARTDATE = dbo.UFN_DATE_NEXTWEEK_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_NEXTWEEK_LASTDAY(@DATE, 0);
end
else if @DATEFILTER = 6 -- next month
begin
set @STARTDATE = dbo.UFN_DATE_NEXTMONTH_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_NEXTMONTH_LASTDAY(@DATE, 0);
end
else if @DATEFILTER = 7 -- next quarter
begin
set @STARTDATE = dbo.UFN_DATE_NEXTQUARTER_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_NEXTQUARTER_LASTDAY(@DATE, 0);
end
else if @DATEFILTER = 8 -- next year
begin
set @STARTDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_FIRSTDAY(@DATE, 0);
set @ENDDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_LASTDAY(@DATE, 0);
end;
-- Using a union on the four different types of data getting pulled in (instead of a view)
-- allows the compiler to choose a plan that filters on constituent ID first.
-- Interactions for the constituent
(
select
INTERACTION.ID,
INTERACTION.DATE,
INTERACTION.STATUS,
OWNERNAME.NAME as OWNER,
INTERACTION.FUNDRAISERID as OWNERID,
INTERACTION.OBJECTIVE,
INTERACTIONTYPECODE.DESCRIPTION as TYPE,
case
when exists(select 1 from dbo.INTERACTIONNOTE where INTERACTIONNOTE.INTERACTIONID = INTERACTION.ID)
or exists(select 1 from dbo.INTERACTIONATTACHMENT where INTERACTIONATTACHMENT.INTERACTIONID = INTERACTION.ID)
or exists(select 1 from dbo.INTERACTIONMEDIALINK where INTERACTIONMEDIALINK.INTERACTIONID = INTERACTION.ID) then 1
else 0
end as HASDOCUMENTATION,
case
when INTERACTION.PROSPECTPLANID is null then 0
else 1
end as ISMOVE,
EVENT.NAME EVENT,
case
when exists(select 1 from dbo.INTERACTIONRESPONSE where INTERACTIONRESPONSE.INTERACTIONID = INTERACTION.ID) then 1
else 0
end as HASRESPONSES,
INTERACTIONCATEGORY.NAME as CATEGORY,
INTERACTIONSUBCATEGORY.NAME as SUBCATEGORY,
case
when INTERACTION.PROSPECTPLANID is null then 'b525985b-be02-4f02-a9b8-c110411cf936'
else '6ce4b0e8-cfcf-4f57-94c3-00eea94fb499'
end as VIEWFORMID,
case
when INTERACTION.PROSPECTPLANID is null then dbo.UFN_CONSTITUENTINTERACTION_GETSITELIST(INTERACTION.ID)
else dbo.UFN_PROSPECTPLAN_GETSITELIST(INTERACTION.PROSPECTPLANID)
end as SITES,
0 as ISSTEWARDSHIPSTEP,
case
when CONSTITUENT.ISGROUP = 1 then 0
when CONSTITUENT.ISORGANIZATION = 1 then 0
else 1
end as ISINDIVIDUALSTEP
from
dbo.INTERACTION
left join dbo.INTERACTIONSUBCATEGORY on INTERACTIONSUBCATEGORY.ID = INTERACTION.INTERACTIONSUBCATEGORYID
left join dbo.INTERACTIONCATEGORY on INTERACTIONCATEGORY.ID = INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID
inner join dbo.INTERACTIONTYPECODE on INTERACTION.INTERACTIONTYPECODEID = INTERACTIONTYPECODE.ID
left join dbo.EVENT on EVENT.ID = INTERACTION.EVENTID
left join dbo.CONSTITUENT on CONSTITUENT.ID = INTERACTION.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTION.FUNDRAISERID) OWNERNAME
where
INTERACTION.ISINTERACTION = 1 and
INTERACTION.STATUSCODE <> 0 and
INTERACTION.CONSTITUENTID = @CONSTITUENTID and
(
(@STARTDATE is null or INTERACTION.DATE >= @STARTDATE)
and (@ENDDATE is null or INTERACTION.DATE <= @ENDDATE)
) and
(@TYPEFILTER is null or INTERACTION.INTERACTIONTYPECODEID = @TYPEFILTER) and
(
@SHOWCOMPLETED = 1
or INTERACTION.STATUSCODE <> 2 -- Completed
) and
exists(
select top 1 INTERACTIONSITE.SITEID from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(INTERACTION.ID) 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)))
) and
(
@SITEFILTERMODE = 0 or
(INTERACTION.PROSPECTPLANID is not null and INTERACTION.ID in
(
select INTERACTIONSITE.INTERACTIONID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.INTERACTIONSITE on INTERACTIONSITE.SITEID = SITEFILTER.SITEID
)
) or
(INTERACTION.PROSPECTPLANID is null and INTERACTION.PROSPECTPLANID in
(
select PROSPECTPLANSITE.PROSPECTPLANID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
)
)
)
union all
-- Interactions where the constituent is a participant
select
INTERACTION.ID,
INTERACTION.DATE,
INTERACTION.STATUS,
OWNERNAME.NAME as OWNER,
INTERACTION.FUNDRAISERID as OWNERID,
INTERACTION.OBJECTIVE,
INTERACTIONTYPECODE.DESCRIPTION as TYPE,
case
when exists(select 1 from dbo.INTERACTIONNOTE where INTERACTIONNOTE.INTERACTIONID = INTERACTION.ID)
or exists(select 1 from dbo.INTERACTIONATTACHMENT where INTERACTIONATTACHMENT.INTERACTIONID = INTERACTION.ID)
or exists(select 1 from dbo.INTERACTIONMEDIALINK where INTERACTIONMEDIALINK.INTERACTIONID = INTERACTION.ID) then 1
else 0
end as HASDOCUMENTATION,
case
when INTERACTION.PROSPECTPLANID is null then 0
else 1
end as ISMOVE,
EVENT.NAME EVENT,
case
when exists(select 1 from dbo.INTERACTIONRESPONSE where INTERACTIONRESPONSE.INTERACTIONID = INTERACTION.ID) then 1
else 0
end as HASRESPONSES,
INTERACTIONCATEGORY.NAME as CATEGORY,
INTERACTIONSUBCATEGORY.NAME as SUBCATEGORY,
case
when INTERACTION.PROSPECTPLANID is null then 'b525985b-be02-4f02-a9b8-c110411cf936'
else '6ce4b0e8-cfcf-4f57-94c3-00eea94fb499'
end as VIEWFORMID,
case
when INTERACTION.PROSPECTPLANID is null then dbo.UFN_CONSTITUENTINTERACTION_GETSITELIST(INTERACTION.ID)
else dbo.UFN_PROSPECTPLAN_GETSITELIST(INTERACTION.PROSPECTPLANID)
end as SITES,
0 as ISSTEWARDSHIPSTEP,
case
when CONSTITUENT.ISGROUP = 1 then 0
when CONSTITUENT.ISORGANIZATION = 1 then 0
else 1
end as ISINDIVIDUALSTEP
from
dbo.INTERACTION
left join dbo.INTERACTIONSUBCATEGORY on INTERACTIONSUBCATEGORY.ID = INTERACTION.INTERACTIONSUBCATEGORYID
left join dbo.INTERACTIONCATEGORY on INTERACTIONCATEGORY.ID = INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID
inner join dbo.INTERACTIONTYPECODE on INTERACTION.INTERACTIONTYPECODEID = INTERACTIONTYPECODE.ID
left join dbo.EVENT on EVENT.ID = INTERACTION.EVENTID
left join dbo.CONSTITUENT on CONSTITUENT.ID = INTERACTION.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTION.FUNDRAISERID) OWNERNAME
inner join dbo.INTERACTIONPARTICIPANT on INTERACTIONPARTICIPANT.INTERACTIONID = INTERACTION.ID
where
INTERACTION.ISINTERACTION = 1 and
INTERACTION.STATUSCODE <> 0 and
INTERACTIONPARTICIPANT.CONSTITUENTID = @CONSTITUENTID and
INTERACTION.CONSTITUENTID <> @CONSTITUENTID and
(
(@STARTDATE is null or INTERACTION.DATE >= @STARTDATE)
and (@ENDDATE is null or INTERACTION.DATE <= @ENDDATE)
) and
(@TYPEFILTER is null or INTERACTION.INTERACTIONTYPECODEID = @TYPEFILTER) and
(
@SHOWCOMPLETED = 1
or INTERACTION.STATUSCODE <> 2 -- Completed
) and
exists(
select top 1 INTERACTIONSITE.SITEID from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(INTERACTION.ID) 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)))
) and
(
@SITEFILTERMODE = 0 or
(INTERACTION.PROSPECTPLANID is not null and INTERACTION.ID in
(
select INTERACTIONSITE.INTERACTIONID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.INTERACTIONSITE on INTERACTIONSITE.SITEID = SITEFILTER.SITEID
)
) or
(INTERACTION.PROSPECTPLANID is null and INTERACTION.PROSPECTPLANID in
(
select PROSPECTPLANSITE.PROSPECTPLANID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
)
)
)
union all
-- Stewardship plan steps for the constituent
select
STEWARDSHIPPLANSTEP.ID,
STEWARDSHIPPLANSTEP.DATE,
STEWARDSHIPPLANSTEP.STATUS,
OWNERNAME.NAME as OWNER,
STEWARDSHIPPLANSTEP.CONSTITUENTID as OWNERID,
STEWARDSHIPPLANSTEP.OBJECTIVE,
INTERACTIONTYPECODE.DESCRIPTION as TYPE,
case
when exists(select 1 from dbo.STEWARDSHIPNOTE where STEWARDSHIPNOTE.STEPID = STEWARDSHIPPLANSTEP.ID)
or exists(select 1 from dbo.STEWARDSHIPATTACHMENT where STEWARDSHIPATTACHMENT.STEPID = STEWARDSHIPPLANSTEP.ID)
or exists(select 1 from dbo.STEWARDSHIPMEDIALINK where STEWARDSHIPMEDIALINK.STEPID = STEWARDSHIPPLANSTEP.ID) then 1
else 0
end as HASDOCUMENTATION,
0 as ISMOVE,
EVENT.NAME EVENT,
0 as HASRESPONSES,
STEWARDSHIPSTEPCATEGORYCODE.DESCRIPTION as CATEGORY,
null as SUBCATEGORY,
'0a9d7c4b-018d-48b5-aa14-589cf6c0d97b' as VIEWFORMID,
dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(STEWARDSHIPPLANSTEP.PLANID) as SITES,
1 as ISSTEWARDSHIPSTEP,
case
when CONSTITUENT.ISGROUP = 1 then 0
when CONSTITUENT.ISORGANIZATION = 1 then 0
else 1
end as ISINDIVIDUALSTEP
from
dbo.STEWARDSHIPPLANSTEP
inner join dbo.STEWARDSHIPPLAN on STEWARDSHIPPLAN.ID = STEWARDSHIPPLANSTEP.PLANID
left join dbo.STEWARDSHIPSTEPCATEGORYCODE on STEWARDSHIPSTEPCATEGORYCODE.ID = STEWARDSHIPPLANSTEP.CATEGORYCODEID
inner join dbo.INTERACTIONTYPECODE on STEWARDSHIPPLANSTEP.CONTACTMETHODCODEID = INTERACTIONTYPECODE.ID
left join dbo.EVENT on EVENT.ID = STEWARDSHIPPLANSTEP.EVENTID
left join dbo.CONSTITUENT on CONSTITUENT.ID = STEWARDSHIPPLAN.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLANSTEP.CONSTITUENTID) OWNERNAME
where
STEWARDSHIPPLANSTEP.ISINTERACTION = 1 and
STEWARDSHIPPLAN.CONSTITUENTID = @CONSTITUENTID and
(
(@STARTDATE is null or STEWARDSHIPPLANSTEP.DATE >= @STARTDATE)
and (@ENDDATE is null or STEWARDSHIPPLANSTEP.DATE <= @ENDDATE)
) and
(@TYPEFILTER is null or STEWARDSHIPPLANSTEP.CONTACTMETHODCODEID = @TYPEFILTER) and
(
@SHOWCOMPLETED = 1
or STEWARDSHIPPLANSTEP.STATUSCODE <> 1 -- Completed (Different for stewardship plans and interactions)
) and exists(
select top 1 STEWARDSHIPPLANSTEPSITE.SITEID from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(STEWARDSHIPPLANSTEP.ID) STEWARDSHIPPLANSTEPSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=STEWARDSHIPPLANSTEPSITE.[SITEID] or (SITEID is null and STEWARDSHIPPLANSTEPSITE.[SITEID] is null)))
) and
(
@SITEFILTERMODE = 0 or
STEWARDSHIPPLANSTEP.PLANID in
(
select STEWARDSHIPPLANSITE.STEWARDSHIPPLANID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.STEWARDSHIPPLANSITE on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
)
)
union all
-- Stewardship plan steps where the constituent is the contact person
select
STEWARDSHIPPLANSTEP.ID,
STEWARDSHIPPLANSTEP.DATE,
STEWARDSHIPPLANSTEP.STATUS,
OWNERNAME.NAME as OWNER,
STEWARDSHIPPLANSTEP.CONSTITUENTID as OWNERID,
STEWARDSHIPPLANSTEP.OBJECTIVE,
INTERACTIONTYPECODE.DESCRIPTION as TYPE,
case
when exists(select 1 from dbo.STEWARDSHIPNOTE where STEWARDSHIPNOTE.STEPID = STEWARDSHIPPLANSTEP.ID)
or exists(select 1 from dbo.STEWARDSHIPATTACHMENT where STEWARDSHIPATTACHMENT.STEPID = STEWARDSHIPPLANSTEP.ID)
or exists(select 1 from dbo.STEWARDSHIPMEDIALINK where STEWARDSHIPMEDIALINK.STEPID = STEWARDSHIPPLANSTEP.ID) then 1
else 0
end as HASDOCUMENTATION,
0 as ISMOVE,
EVENT.NAME EVENT,
0 as HASRESPONSES,
STEWARDSHIPSTEPCATEGORYCODE.DESCRIPTION as CATEGORY,
null as SUBCATEGORY,
'0a9d7c4b-018d-48b5-aa14-589cf6c0d97b' as VIEWFORMID,
dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(STEWARDSHIPPLANSTEP.PLANID) as SITES,
1 as ISSTEWARDSHIPSTEP,
case
when CONSTITUENT.ISGROUP = 1 then 0
when CONSTITUENT.ISORGANIZATION = 1 then 0
else 1
end as ISINDIVIDUALSTEP
from
dbo.STEWARDSHIPPLANSTEP
inner join dbo.STEWARDSHIPPLAN on STEWARDSHIPPLAN.ID = STEWARDSHIPPLANSTEP.PLANID
left join dbo.STEWARDSHIPSTEPCATEGORYCODE on STEWARDSHIPSTEPCATEGORYCODE.ID = STEWARDSHIPPLANSTEP.CATEGORYCODEID
inner join dbo.INTERACTIONTYPECODE on STEWARDSHIPPLANSTEP.CONTACTMETHODCODEID = INTERACTIONTYPECODE.ID
left join dbo.EVENT on EVENT.ID = STEWARDSHIPPLANSTEP.EVENTID
left join dbo.CONSTITUENT on CONSTITUENT.ID = STEWARDSHIPPLAN.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPPLANSTEP.CONSTITUENTID) OWNERNAME
where
STEWARDSHIPPLANSTEP.ISINTERACTION = 1 and
STEWARDSHIPPLANSTEP.CONTACTPERSONID = @CONSTITUENTID and
STEWARDSHIPPLAN.CONSTITUENTID <> @CONSTITUENTID and
(
(@STARTDATE is null or STEWARDSHIPPLANSTEP.DATE >= @STARTDATE)
and (@ENDDATE is null or STEWARDSHIPPLANSTEP.DATE <= @ENDDATE)
) and
(@TYPEFILTER is null or STEWARDSHIPPLANSTEP.CONTACTMETHODCODEID = @TYPEFILTER) and
(
@SHOWCOMPLETED = 1
or STEWARDSHIPPLANSTEP.STATUSCODE <> 1 -- Completed (Different for stewardship plans and interactions)
) and exists(
select top 1 STEWARDSHIPPLANSTEPSITE.SITEID from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(STEWARDSHIPPLANSTEP.ID) STEWARDSHIPPLANSTEPSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=STEWARDSHIPPLANSTEPSITE.[SITEID] or (SITEID is null and STEWARDSHIPPLANSTEPSITE.[SITEID] is null)))
) and
(
@SITEFILTERMODE = 0 or
STEWARDSHIPPLANSTEP.PLANID in
(
select STEWARDSHIPPLANSITE.STEWARDSHIPPLANID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.STEWARDSHIPPLANSITE on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
)
)
)
order by
[DATE];
end