USP_DATALIST_CONSTITUENTGROUP_GIVINGHISTORY
This datalist returns all of a constituent group and its members' gifts.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @INCLUDESOFTCREDIT | bit | IN | Include soft credits |
| @NUMBERTOSHOWCODE | smallint | IN | Show |
| @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. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_CONSTITUENTGROUP_GIVINGHISTORY
(
@CONSTITUENTID uniqueidentifier,
@INCLUDESOFTCREDIT bit = 0,
@NUMBERTOSHOWCODE smallint = 2,
@CURRENTAPPUSERID uniqueidentifier = null,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
) as
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
declare @GROUPINCLUDESMEMBERGIVING bit;
-- households are always donors and include member giving, other types get looked up
select
@GROUPINCLUDESMEMBERGIVING =
case
when GD.GROUPTYPECODE = 0 then 1
when GD.GROUPTYPECODE = 1 then GT.INCLUDEMEMBERGIVING
end
from dbo.GROUPDATA GD
left join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID
where GD.ID=@CONSTITUENTID;
if @NUMBERTOSHOWCODE in (0,3,4,5) begin
declare @STARTDATE datetime;
if @NUMBERTOSHOWCODE = 3 --Last 30 Days
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-29,@CURRENTDATE));
else if @NUMBERTOSHOWCODE = 4 --Last 90 Days
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-89,@CURRENTDATE));
else if @NUMBERTOSHOWCODE = 5 --Last Year
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(year,-1,@CURRENTDATE)))
else
set @STARTDATE = @CURRENTDATE;
declare @ENDDATE datetime;
set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);
-- group member pledges and payments (gifts or recurring gift payments)
with MYSITESREV_CTE as
(
select
ID,
CONSTITUENTID,
AMOUNT,
TRANSACTIONTYPE,
TRANSACTIONTYPECODE,
DATE,
DATEADDED
from
dbo.REVENUE
where
(not @NUMBERTOSHOWCODE in (3,4,5) or (REVENUE.DATE >= @STARTDATE and REVENUE.DATE <= @ENDDATE))
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = REVENUE.ID
/*next line is #SITEEXTENTION code*/
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
and
(
@SITEFILTERMODE = 0
or
exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
)
)
)
select
MYSITESREV_CTE.ID,
MYSITESREV_CTE.CONSTITUENTID,
(select NAME from dbo.CONSTITUENT where ID=MYSITESREV_CTE.CONSTITUENTID) as CONSTITUENTNAME,
MYSITESREV_CTE.ID as RECORDID,
MYSITESREV_CTE.DATE,
MYSITESREV_CTE.DATEADDED,
case when MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 then MYSITESREV_CTE.AMOUNT
else (select sum(RS.AMOUNT) from dbo.REVENUESPLIT RS where RS.REVENUEID = MYSITESREV_CTE.ID and RS.APPLICATIONCODE in (0, 3))
end AMOUNT,
case MYSITESREV_CTE.TRANSACTIONTYPECODE when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT(MYSITESREV_CTE.ID)
else (select sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT(RS.ID, 1)) from dbo.REVENUESPLIT RS where RS.REVENUEID = MYSITESREV_CTE.ID and RS.APPLICATIONCODE in (0,3)) end as GROSSAMOUNT,
case MYSITESREV_CTE.TRANSACTIONTYPECODE when 1 then dbo.UFN_PLEDGE_GETBALANCE(MYSITESREV_CTE.ID) else null end as BALANCE,
MYSITESREV_CTE.TRANSACTIONTYPECODE as Typecode,
MYSITESREV_CTE.TRANSACTIONTYPE as Type,
dbo.UFN_REVENUE_DESIGNATIONLIST(MYSITESREV_CTE.ID) as DESIGNATIONS,
(
select dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME) from REVENUESPLIT, REVENUESPLITCAMPAIGN, CAMPAIGN where REVENUESPLIT.REVENUEID=MYSITESREV_CTE.id and REVENUESPLITCAMPAIGN.REVENUESPLITID=REVENUESPLIT.id and REVENUESPLITCAMPAIGN.CAMPAIGNID=CAMPAIGN.ID
) as CAMPAIGNS,
dbo.UFN_REVENUE_BUILDSITELIST(MYSITESREV_CTE.ID) SITES
from
MYSITESREV_CTE
inner join dbo.GROUPMEMBER GM on GM.MEMBERID = MYSITESREV_CTE.CONSTITUENTID
left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
left join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = MYSITESREV_CTE.ID
left join dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
where @GROUPINCLUDESMEMBERGIVING = 1
and GM.GROUPID = @CONSTITUENTID
and (MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 --Pledge
or (MYSITESREV_CTE.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9,10,11,12,13)) --Planned gifts
or (MYSITESREV_CTE.TRANSACTIONTYPECODE = 0
and exists(select 1
from dbo.REVENUESPLIT RS
where RS.REVENUEID = MYSITESREV_CTE.ID
and RS.APPLICATIONCODE in (0,3)
) --Payment (Gift or recurring gift payment)
)
)
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= MYSITESREV_CTE.DATE))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= MYSITESREV_CTE.DATE))
or (GMDR.DATEFROM <= MYSITESREV_CTE.DATE and GMDR.DATETO >= MYSITESREV_CTE.DATE))
union all
-- group pledges and payments (gifts or recurring gift payments)
select
MYSITESREV_CTE.ID,
MYSITESREV_CTE.CONSTITUENTID,
(select NAME from dbo.CONSTITUENT where ID=MYSITESREV_CTE.CONSTITUENTID) as CONSTITUENTNAME,
MYSITESREV_CTE.ID as RECORDID,
MYSITESREV_CTE.DATE,
MYSITESREV_CTE.DATEADDED,
case
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 then MYSITESREV_CTE.AMOUNT
else (select sum(RS.AMOUNT) from dbo.REVENUESPLIT RS where RS.REVENUEID = MYSITESREV_CTE.ID and RS.APPLICATIONCODE in (0, 3))
end AMOUNT,
case
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 then MYSITESREV_CTE.AMOUNT
else (select sum(RS.AMOUNT) from dbo.REVENUESPLIT RS where RS.REVENUEID = MYSITESREV_CTE.ID and RS.APPLICATIONCODE in (0, 3))
end as GROSSAMOUNT, --Groups do not get Gift Aid
case MYSITESREV_CTE.TRANSACTIONTYPECODE when 1 then dbo.UFN_PLEDGE_GETBALANCE(MYSITESREV_CTE.ID) else null end as BALANCE,
MYSITESREV_CTE.TRANSACTIONTYPECODE,
MYSITESREV_CTE.TRANSACTIONTYPE,
dbo.UFN_REVENUE_DESIGNATIONLIST(MYSITESREV_CTE.ID) as DESIGNATIONS,
(
select dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME) from REVENUESPLIT, REVENUESPLITCAMPAIGN, CAMPAIGN where REVENUESPLIT.REVENUEID=MYSITESREV_CTE.id and REVENUESPLITCAMPAIGN.REVENUESPLITID=REVENUESPLIT.id and REVENUESPLITCAMPAIGN.CAMPAIGNID=CAMPAIGN.ID
) as CAMPAIGNS,
dbo.UFN_REVENUE_BUILDSITELIST(MYSITESREV_CTE.ID) SITES
from
MYSITESREV_CTE
left join dbo.PLANNEDGIFTREVENUE PGR on PGR.REVENUEID = MYSITESREV_CTE.ID
left join dbo.PLANNEDGIFT PG on PG.ID = PGR.ID
where MYSITESREV_CTE.CONSTITUENTID = @CONSTITUENTID
and (MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 --Pledge
or (MYSITESREV_CTE.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9,10,11,12,13)) --Planned gifts
or (MYSITESREV_CTE.TRANSACTIONTYPECODE = 0
and exists(select 1
from dbo.REVENUESPLIT RS
where RS.REVENUEID = MYSITESREV_CTE.ID
and RS.APPLICATIONCODE in (0,3)
) --Payment (Gift or recurring gift payment)
)
)
order by
DATE desc, DATEADDED desc, AMOUNT desc;
end
else begin
declare @NUMBERTOSHOW int;
if @NUMBERTOSHOWCODE = 1
set @NUMBERTOSHOW = 5;
else if @NUMBERTOSHOWCODE = 2
set @NUMBERTOSHOW = 10;
else
set @NUMBERTOSHOW = 0;
with MYSITESREV_CTE as
(
select
REVENUE.ID,
REVENUE.CONSTITUENTID,
REVENUE.AMOUNT,
REVENUE.TRANSACTIONTYPE,
REVENUE.TRANSACTIONTYPECODE,
REVENUE.DATE,
REVENUE.DATEADDED
from
dbo.REVENUE
left join dbo.PLANNEDGIFTREVENUE PGR on PGR.REVENUEID = REVENUE.ID
left join dbo.PLANNEDGIFT PG on PG.ID = PGR.ID
where
(REVENUE.TRANSACTIONTYPECODE = 1 --Pledge
or (REVENUE.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9,10,11,12,13)) --Planned gift
or (REVENUE.TRANSACTIONTYPECODE = 0
and exists(select 1
from dbo.REVENUESPLIT RS
where RS.REVENUEID = REVENUE.ID
and RS.APPLICATIONCODE in (0,3)
) --Payment (Gift or recurring gift payment)
)
)
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = REVENUE.ID
/*next line is #SITEEXTENTION code*/
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
and
(
@SITEFILTERMODE = 0
or
exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
)
)
)
select top(@NUMBERTOSHOW)
ID,
CONSTITUENTID,
CONSTITUENTNAME,
RECORDID,
DATE,
DATEADDED,
AMOUNT,
GROSSAMOUNT,
BALANCE,
TRANSACTIONTYPECODE as TYPECODE,
TRANSACTIONTYPE as TYPE,
DESIGNATIONS,
CAMPAIGNS,
SITES
from
(
-- group member pledges and payments
select top(@NUMBERTOSHOW)
MYSITESREV_CTE.ID,
MYSITESREV_CTE.CONSTITUENTID,
(select NAME from dbo.CONSTITUENT where ID=MYSITESREV_CTE.CONSTITUENTID) as CONSTITUENTNAME,
MYSITESREV_CTE.ID as RECORDID,
MYSITESREV_CTE.DATE,
MYSITESREV_CTE.DATEADDED,
case
when MYSITESREV_CTE.TRANSACTIONTYPECODE in (1,4) then MYSITESREV_CTE.AMOUNT
else (select sum(RS.AMOUNT) from dbo.REVENUESPLIT RS where RS.REVENUEID = MYSITESREV_CTE.ID and RS.APPLICATIONCODE in (0, 3))
end AMOUNT,
case MYSITESREV_CTE.TRANSACTIONTYPECODE when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT(MYSITESREV_CTE.ID)
else (select sum(dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT(RS.ID, 1)) from dbo.REVENUESPLIT RS where RS.REVENUEID = MYSITESREV_CTE.ID and RS.APPLICATIONCODE in (0,3)) end as GROSSAMOUNT,
case MYSITESREV_CTE.TRANSACTIONTYPECODE when 1 then dbo.UFN_PLEDGE_GETBALANCE(MYSITESREV_CTE.ID) else null end as BALANCE,
MYSITESREV_CTE.TRANSACTIONTYPECODE,
MYSITESREV_CTE.TRANSACTIONTYPE,
dbo.UFN_REVENUE_DESIGNATIONLIST(MYSITESREV_CTE.ID) as DESIGNATIONS,
(
select dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME) from REVENUESPLIT, REVENUESPLITCAMPAIGN, CAMPAIGN where REVENUESPLIT.REVENUEID=MYSITESREV_CTE.id and REVENUESPLITCAMPAIGN.REVENUESPLITID=REVENUESPLIT.id and REVENUESPLITCAMPAIGN.CAMPAIGNID=CAMPAIGN.ID
) as CAMPAIGNS,
dbo.UFN_REVENUE_BUILDSITELIST(MYSITESREV_CTE.ID) SITES
from MYSITESREV_CTE
inner join dbo.GROUPMEMBER GM on MYSITESREV_CTE.CONSTITUENTID = GM.MEMBERID
left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
where @GROUPINCLUDESMEMBERGIVING = 1
and GM.GROUPID = @CONSTITUENTID
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= MYSITESREV_CTE.DATE))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= MYSITESREV_CTE.DATE))
or (GMDR.DATEFROM <= MYSITESREV_CTE.DATE and GMDR.DATETO >= MYSITESREV_CTE.DATE))
order by
DATE desc, DATEADDED desc, AMOUNT desc
union all
-- group pledges and payments
select top(@NUMBERTOSHOW)
MYSITESREV_CTE.ID,
MYSITESREV_CTE.CONSTITUENTID,
(select NAME from dbo.CONSTITUENT where ID=MYSITESREV_CTE.CONSTITUENTID) as CONSTITUENTNAME,
MYSITESREV_CTE.ID as RECORDID,
MYSITESREV_CTE.DATE,
MYSITESREV_CTE.DATEADDED,
case
when MYSITESREV_CTE.TRANSACTIONTYPECODE in (1,4) then MYSITESREV_CTE.AMOUNT
else (select sum(RS.AMOUNT) from dbo.REVENUESPLIT RS where RS.REVENUEID = MYSITESREV_CTE.ID and RS.APPLICATIONCODE in (0, 3))
end AMOUNT,
case
when MYSITESREV_CTE.TRANSACTIONTYPECODE in (1,4) then MYSITESREV_CTE.AMOUNT
else (select sum(RS.AMOUNT) from dbo.REVENUESPLIT RS where RS.REVENUEID = MYSITESREV_CTE.ID and RS.APPLICATIONCODE in (0, 3))
end as GROSSAMOUNT, --groups do not get Gift Aid
case MYSITESREV_CTE.TRANSACTIONTYPECODE when 1 then dbo.UFN_PLEDGE_GETBALANCE(MYSITESREV_CTE.ID) else null end as BALANCE,
MYSITESREV_CTE.TRANSACTIONTYPECODE as TYPECODE,
MYSITESREV_CTE.TRANSACTIONTYPE,
dbo.UFN_REVENUE_DESIGNATIONLIST(MYSITESREV_CTE.ID) as DESIGNATIONS,
(
select dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME) from REVENUESPLIT, REVENUESPLITCAMPAIGN, CAMPAIGN where REVENUESPLIT.REVENUEID=MYSITESREV_CTE.id and REVENUESPLITCAMPAIGN.REVENUESPLITID=REVENUESPLIT.id and REVENUESPLITCAMPAIGN.CAMPAIGNID=CAMPAIGN.ID
) as CAMPAIGNS,
dbo.UFN_REVENUE_BUILDSITELIST(MYSITESREV_CTE.ID) SITES
from
MYSITESREV_CTE
where
MYSITESREV_CTE.CONSTITUENTID = @CONSTITUENTID
order by
DATE desc, DATEADDED desc, AMOUNT desc
) as RESULT
order by
RESULT.DATE desc, RESULT.DATEADDED desc, RESULT.AMOUNT desc;
end