USP_DATALIST_CONSTITUENTINTERACTIONRECENT
Returns a list of recent activities a constituent has completed.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @MAXROWS | int | IN | Input parameter indicating the maximum number of rows to return. |
| @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_CONSTITUENTINTERACTIONRECENT (
@CURRENTAPPUSERID uniqueidentifier,
@CONSTITUENTID uniqueidentifier,
@MAXROWS int,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
) as begin
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
/*
MAXROWS is passed in by the server as MAXROWS + 1. That means that the server will
always use one fewer row than the data list returns. This is used to determine if
the data list would have returned more rows if allowed. Unfortunately, if the
display order is different from the selection order, the row removed might be one
of the ten selected rows. In order to handle this, this data list will subtract
one from the MAXROWS if the value is not the default (501).
*/
if @MAXROWS <> 501 and @MAXROWS > 0
set @MAXROWS = @MAXROWS - 1;
/*
Please keep list updated
TYPCODES returned
0 - Plan step
1 - Interaction
2 - Event registration
3 - Volunteer occurrence
4 - Admission
5 - Membership
6 - Preregistered program
7 - Reserved (Pay on Arrival) Admission
8 - Pending group sales order
*/
declare @ISSYSADMIN bit = 0;
select @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
declare @SITESFORUSER table
(
SITEID uniqueidentifier
)
insert into @SITESFORUSER(SITEID)
select
SITEID
from
dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)
declare @RESULTS_NOSECURITY table
(
ID uniqueidentifier,
DATE datetime,
TYPE nvarchar(50),
DETAIL nvarchar(200),
RECENCY int,
TYPECODE tinyint,
STATUSCODE tinyint,
ISINTERACTION bit,
PROSPECTPLANID uniqueidentifier
);
insert into
@RESULTS_NOSECURITY
select
ID,
DATE,
TYPE,
DETAIL,
RECENCY,
TYPECODE,
STATUSCODE,
ISINTERACTION,
PROSPECTPLANID
from (
-- interactions
select
INTERACTION.ID,
INTERACTION.DATE,
case
when PROSPECTPLANID is not null then 'Plan step'
else 'Interaction'
end [TYPE],
INTERACTIONTYPECODE.DESCRIPTION [DETAIL],
abs(datediff(dd, INTERACTION.DATE, @CURRENTDATE)) [RECENCY],
case
when PROSPECTPLANID is not null then 0
else 1
end as TYPECODE,
STATUSCODE,
1 as ISINTERACTION,
INTERACTION.PROSPECTPLANID
from
dbo.INTERACTION
left outer join
dbo.INTERACTIONTYPECODE on INTERACTION.INTERACTIONTYPECODEID = INTERACTIONTYPECODE.ID
where
INTERACTION.CONSTITUENTID = @CONSTITUENTID
union all
-- interactions where the constituent is a participant
-- tried adding this logic into the above SQL statement, but it caused a performance issue
-- the redundant code is actually faster than adding complexity to the where clause
select
INTERACTION.ID,
INTERACTION.DATE,
case
when PROSPECTPLANID is not null then 'Plan step'
else 'Interaction'
end [TYPE],
INTERACTIONTYPECODE.DESCRIPTION [DETAIL],
abs(datediff(dd, INTERACTION.DATE, @CURRENTDATE)) [RECENCY],
case
when PROSPECTPLANID is not null then 0
else 1
end as TYPECODE,
STATUSCODE,
1 as ISINTERACTION,
INTERACTION.PROSPECTPLANID
from
dbo.INTERACTION
inner join
dbo.INTERACTIONPARTICIPANT on INTERACTION.ID = INTERACTIONPARTICIPANT.INTERACTIONID
left outer join
dbo.INTERACTIONTYPECODE on INTERACTION.INTERACTIONTYPECODEID = INTERACTIONTYPECODE.ID
where
INTERACTION.CONSTITUENTID <> @CONSTITUENTID and
INTERACTIONPARTICIPANT.CONSTITUENTID = @CONSTITUENTID
union all
-- event attendance
select
EVENTREG.REGISTRANTID as ID,
EVENTREG.EVENTSTARTDATE as DATE,
'Event registration' as TYPE,
EVENTREG.EVENTNAME as DETAIL,
abs(datediff(dd, EVENTREG.EVENTSTARTDATE, @CURRENTDATE)) as RECENCY,
2 as TYPECODE,
null as STATUSCODE,
0 as ISINTERACTION,
null as PROSPECTPLANID
from
dbo.UFN_EVENT_GETNONPROGRAMEVENTREGISTRANTS(@CONSTITUENTID) EVENTREG
where
dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENTREG.EVENTID) = 1
union all
-- volunteering
select
VOLUNTEERASSIGNMENT.ID,
VOLUNTEERASSIGNMENT.DATE,
'Volunteer occurrence',
JOB.NAME,
abs(datediff(dd, VOLUNTEERASSIGNMENT.DATE, @CURRENTDATE)),
3 as TYPECODE,
null as STATUSCODE,
0 as ISINTERACTION,
null as PROSPECTPLANID
from
dbo.VOLUNTEERASSIGNMENT
left outer join
dbo.JOBOCCURRENCE on VOLUNTEERASSIGNMENT.JOBOCCURRENCEID = JOBOCCURRENCE.ID
left outer join
dbo.JOB on JOBOCCURRENCE.JOBID = JOB.ID
where
VOLUNTEERASSIGNMENT.VOLUNTEERID = @CONSTITUENTID and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, JOB.SITEID) = 1
union all
-- Sales activity: Ticketing
select distinct
[SALESORDER].ID as [ID],
-- Get the start date of the event if possible, else use the ticket sale date.
coalesce([EVENT].STARTDATE,[SALESORDER].TRANSACTIONDATE,RESERVATION.ARRIVALDATE) as [DATE],
'Admission' as [TYPE],
PROGRAM.NAME as [DETAIL],
abs(datediff(dd, coalesce([EVENT].STARTDATE,[salesorder].transactiondate), @CURRENTDATE)) as [RECENCY],
case SALESORDER.STATUSCODE
when 0 then -- Pending order (must be group sales due to where clause)
8
when 6 then -- Reserved (Pay on Arrival)
7
else
4
end as TYPECODE,
null as STATUSCODE,
0 as ISINTERACTION,
null as PROSPECTPLANID
from
dbo.CONSTITUENT
inner join
dbo.SALESORDER on SALESORDER.RECIPIENTID = CONSTITUENT.ID
inner join
dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDER.ID
inner join
dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
left join
dbo.[EVENT] on [EVENT].ID = SALESORDERITEMTICKET.EVENTID
left join
dbo.RESERVATION on RESERVATION.ID = SALESORDER.ID
inner join
-- General admission relates to program via salesorderitemticket, scheduled admission relates to program via event
dbo.[PROGRAM] on [PROGRAM].ID = [EVENT].PROGRAMID or [PROGRAM].ID = [SALESORDERITEMTICKET].PROGRAMID
where
CONSTITUENT.ID = @CONSTITUENTID
and
(SALESORDER.STATUSCODE not in (0, 5, 7) -- exclude tickets on pending, cancelled, and unresolved orders
or (SALESORDER.STATUSCODE = 0 and SALESORDER.SALESMETHODTYPECODE = 3))
and
PROGRAM.ISPREREGISTERED = 0
union all
-- Membership
select
MEMBERSHIP.ID as [ID],
MEMBERSHIPTRANSACTION.[TRANSACTIONDATE] as [DATE],
'Membership' as [TYPE],
MEMBERSHIPPROGRAM.NAME + ' - ' + MEMBERSHIPTRANSACTION.[ACTION] as [DETAIL],
abs(datediff(dd, MEMBERSHIPTRANSACTION.[TRANSACTIONDATE], @CURRENTDATE)) as [RECENCY],
5 as TYPECODE,
null as STATUSCODE,
0 as ISINTERACTION,
null as PROSPECTPLANID
from
dbo.MEMBER
inner join
dbo.CONSTITUENT on CONSTITUENT.ID = CONSTITUENTID
inner join
dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERSHIPID
inner join
dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIP.MEMBERSHIPPROGRAMID
inner join
dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID
where
MEMBER.CONSTITUENTID = @CONSTITUENTID and MEMBER.ISDROPPED = 0
union all
-- Preregistered Program Events
select
EVENT.ID as [ID],
EVENT.STARTDATE as [DATE],
'Preregistered program' as [TYPE],
EVENT.NAME as [DETAIL],
abs(datediff(dd, EVENT.STARTDATE, @CURRENTDATE)) as [RECENCY],
6 as [TYPECODE],
null as STATUSCODE,
0 as ISINTERACTION,
null as PROSPECTPLANID
from
dbo.REGISTRANT
inner join
dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
inner join
dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
inner join
dbo.SALESORDERITEMTICKETREGISTRANT on SALESORDERITEMTICKETREGISTRANT.REGISTRANTID = REGISTRANT.ID
where
REGISTRANT.CONSTITUENTID = @CONSTITUENTID
and dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENT.ID) = 1
and REGISTRANT.ISCANCELLED = 0
) [ORGANIZATIONINTERACTION]
--Inserting the results into a table variable and then performing the INTERACTION site security checks on the results in the table
--produces a better execution plan than the previous version of this datalist.
select
RESULTS.ID,
RESULTS.DATE,
RESULTS.TYPE,
RESULTS.DETAIL,
RESULTS.TYPECODE,
RESULTS.STATUSCODE
from
(
select top (@MAXROWS)
*
from
@RESULTS_NOSECURITY RESULTS_NOSECURITY
where
ISINTERACTION = 0
or
(
@ISSYSADMIN = 1
or
(
-- Handle interactions not tied to a plan. They use sites set directly on the interaction.
(
RESULTS_NOSECURITY.PROSPECTPLANID is null and
(
select count(*)
from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(RESULTS_NOSECURITY.ID) INTERACTIONSITE
where (exists (select 1 from @SITESFORUSER SITESFORUSER where SITESFORUSER.SITEID=[INTERACTIONSITE].[SITEID] or (SITESFORUSER.SITEID is null and [INTERACTIONSITE].[SITEID] is null)))
) > 0
)
)
or
(
-- Handle interactions tied to a plan. They use sites set on the plan.
(
RESULTS_NOSECURITY.PROSPECTPLANID is not null and
(
select count(*)
from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(RESULTS_NOSECURITY.PROSPECTPLANID) INTERACTIONSITE
where (exists (select 1 from @SITESFORUSER SITESFORUSER where SITESFORUSER.SITEID=[INTERACTIONSITE].[SITEID] or (SITESFORUSER.SITEID is null and [INTERACTIONSITE].[SITEID] is null)))
) > 0
)
)
)
order by
RESULTS_NOSECURITY.RECENCY,
RESULTS_NOSECURITY.DATE desc
) RESULTS
order by
RESULTS.DATE desc
return 0;
end