USP_DATALIST_CONSTITUENTGROUPINTERACTION
List of interactions belonging to a constituent group and its current members.
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. |
| @DATEFILTER | tinyint | IN | Date |
| @STARTDATE | datetime | IN | Start date |
| @ENDDATE | datetime | IN | End date |
| @TYPEFILTER | uniqueidentifier | IN | Contact method |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_CONSTITUENTGROUPINTERACTION
(
@GROUPID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@DATEFILTER tinyint = 0,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@TYPEFILTER uniqueidentifier = null
)
as begin
set nocount on;
declare @DATE datetime
set @DATE = getdate();
if @DATEFILTER = 0 -- all
begin
set @STARTDATE = null
set @ENDDATE = null
end
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
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
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
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
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
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
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
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
declare @DATALISTID uniqueidentifier;
set @DATALISTID = '9dbf2aca-66da-443d-bfc9-89cad0d53e72';
with INTERACTION_CTE as
(
-- group interactions
select
I.ID,
I.DATE DATE,
case I.COMPLETED when 1 then 'Completed' else I.STATUS end STATUS,
I.OBJECTIVE OBJECTIVE,
dbo.UFN_INTERACTIONTYPECODE_GETDESCRIPTION(I.INTERACTIONTYPECODEID) INTERACTIONTYPE,
case when I.PROSPECTPLANID is null then 0 else 1 end ISSTEP,
case
when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
else dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, I.CONSTITUENTID)
end HASPERMISSIONS,
I.FUNDRAISERID FUNDRAISERID,
I.CONSTITUENTID CONSTITUENTID,
I.ISINTERACTION,
I.STATUSCODE,
I.INTERACTIONTYPECODEID,
I.INTERACTIONSUBCATEGORYID,
'0' GROUPORPRIMARYSORT,
C.NAME GROUPORPRIMARYSORTVALUE
from
dbo.INTERACTION I
left outer join
dbo.CONSTITUENT C on C.ID = I.CONSTITUENTID
where
I.CONSTITUENTID = @GROUPID
union all
-- group as an interaction participant
select
I.ID,
I.DATE DATE,
case I.COMPLETED when 1 then 'Completed' else I.STATUS end STATUS,
I.OBJECTIVE OBJECTIVE,
dbo.UFN_INTERACTIONTYPECODE_GETDESCRIPTION(I.INTERACTIONTYPECODEID) INTERACTIONTYPE,
case when I.PROSPECTPLANID is null then 0 else 1 end ISSTEP,
case
when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
else dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, IP.CONSTITUENTID)
end HASPERMISSIONS,
I.FUNDRAISERID FUNDRAISERID,
I.CONSTITUENTID CONSTITUENTID,
I.ISINTERACTION,
I.STATUSCODE,
I.INTERACTIONTYPECODEID,
I.INTERACTIONSUBCATEGORYID,
'0' GROUPORPRIMARYSORT,
C.NAME GROUPORPRIMARYSORTVALUE
from
dbo.INTERACTION I
inner join
dbo.INTERACTIONPARTICIPANT IP on IP.INTERACTIONID = I.ID
left outer join
dbo.CONSTITUENT C on C.ID = I.CONSTITUENTID
where
IP.CONSTITUENTID = @GROUPID
union all
-- group member interactions
select
I.ID,
I.DATE DATE,
case I.COMPLETED when 1 then 'Completed' else I.STATUS end STATUS,
I.OBJECTIVE OBJECTIVE,
dbo.UFN_INTERACTIONTYPECODE_GETDESCRIPTION(I.INTERACTIONTYPECODEID) INTERACTIONTYPE,
case when I.PROSPECTPLANID is null then 0 else 1 end ISSTEP,
case
when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
else dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, GM.MEMBERID)
end HASPERMISSIONS,
I.FUNDRAISERID FUNDRAISERID,
I.CONSTITUENTID CONSTITUENTID,
I.ISINTERACTION,
I.STATUSCODE,
I.INTERACTIONTYPECODEID,
I.INTERACTIONSUBCATEGORYID,
case
when GM.ISPRIMARY = 1 then '1'
else '2' + (select CONSTITUENT.KEYNAME + ' ' + CONSTITUENT.FIRSTNAME + ' ' + cast(CONSTITUENT.ID as nvarchar(36)) from dbo.CONSTITUENT where ID=GM.MEMBERID)
end GROUPORPRIMARYSORT,
C.NAME GROUPORPRIMARYSORTVALUE
from
dbo.INTERACTION I
inner join
dbo.GROUPMEMBER GM on GM.MEMBERID = I.CONSTITUENTID
left outer join
dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
left outer join
dbo.CONSTITUENT C on C.ID = GM.MEMBERID
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))
union all
-- group members as interaction participants
select
I.ID,
I.DATE DATE,
case I.COMPLETED when 1 then 'Completed' else I.STATUS end STATUS,
I.OBJECTIVE OBJECTIVE,
dbo.UFN_INTERACTIONTYPECODE_GETDESCRIPTION(I.INTERACTIONTYPECODEID) INTERACTIONTYPE,
case when I.PROSPECTPLANID is null then 0 else 1 end ISSTEP,
case
when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
else dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, GM.MEMBERID)
end HASPERMISSIONS,
I.FUNDRAISERID FUNDRAISERID,
I.CONSTITUENTID CONSTITUENTID,
I.ISINTERACTION,
I.STATUSCODE,
I.INTERACTIONTYPECODEID,
I.INTERACTIONSUBCATEGORYID,
case
when GM.ISPRIMARY = 1 then '1'
else '2' + (select CONSTITUENT.KEYNAME + ' ' + CONSTITUENT.FIRSTNAME + ' ' + cast(CONSTITUENT.ID as nvarchar(36)) from dbo.CONSTITUENT where ID=GM.MEMBERID)
end GROUPORPRIMARYSORT,
C.NAME GROUPORPRIMARYSORTVALUE
from
dbo.INTERACTION I
inner join
dbo.INTERACTIONPARTICIPANT IP on IP.INTERACTIONID = I.ID
inner join
dbo.GROUPMEMBER GM on GM.MEMBERID = IP.CONSTITUENTID
left outer join
dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
left outer join
dbo.CONSTITUENT C on C.ID = GM.MEMBERID
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))
)
select
I.ID ID,
I.DATE DATE,
I.GROUPORPRIMARYSORTVALUE,
I.STATUS,
FC.ID OWNERID,
FC.NAME FUNDRAISER,
I.OBJECTIVE OBJECTIVE,
I.INTERACTIONTYPE,
cast(I.ISSTEP as bit) ISSTEP,
case when (INOTE.ID is not null) or (IA.ID is not null) or (IML.ID is not null)
then convert(bit,1) else convert(bit,0) end HASDOCUMENTATION,
I.HASPERMISSIONS,
I.GROUPORPRIMARYSORT,
IC.NAME,
ISC.NAME,
case when I.ISSTEP = 1 then '6ce4b0e8-cfcf-4f57-94c3-00eea94fb499'
else 'b525985b-be02-4f02-a9b8-c110411cf936'
end VIEWFORMID
from
INTERACTION_CTE I
left outer join
dbo.CONSTITUENT FC on FC.ID=I.FUNDRAISERID
left outer join
dbo.INTERACTIONNOTE INOTE on INOTE.INTERACTIONID = I.ID
left outer join
dbo.INTERACTIONATTACHMENT IA on IA.INTERACTIONID = I.ID
left outer join
dbo.INTERACTIONMEDIALINK IML on IML.INTERACTIONID = I.ID
left outer join
dbo.INTERACTIONSUBCATEGORY ISC on ISC.ID = I.INTERACTIONSUBCATEGORYID
left outer join
dbo.INTERACTIONCATEGORY IC on IC.ID = ISC.INTERACTIONCATEGORYID
where I.ISINTERACTION = 1 and
I.STATUSCODE != 0
and ((I.DATE between @STARTDATE and @ENDDATE)
or (@STARTDATE is null and @ENDDATE is null)
or (I.DATE >= @STARTDATE and @ENDDATE is null)
or (@STARTDATE is null and I.DATE <= @ENDDATE))
and
((I.INTERACTIONTYPECODEID = @TYPEFILTER)
or
(@TYPEFILTER is null))
order by
I.GROUPORPRIMARYSORT asc, I.DATE asc
end