USP_CONSTITUENT_GETGIVINGHISTORY
Returns the data for the Constituent Giving History List.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @INCLUDESOFTCREDIT | bit | IN | |
| @NUMBERTOSHOWCODE | smallint | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @SITEFILTERMODE | tinyint | IN | |
| @SITESSELECTED | xml | IN | |
| @SECURITYFEATUREID | uniqueidentifier | IN | |
| @SECURITYFEATURETYPE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_CONSTITUENT_GETGIVINGHISTORY
(
@CONSTITUENTID uniqueidentifier,
@INCLUDESOFTCREDIT bit,
@NUMBERTOSHOWCODE smallint,
@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();
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);
with MYSITESREV_CTE as
(
select
ID,
CONSTITUENTID,
AMOUNT,
TRANSACTIONTYPE,
TRANSACTIONTYPECODE,
DATE,
DATEADDED
from
dbo.REVENUE
where
REVENUE.CONSTITUENTID = @CONSTITUENTID
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
-- Using a case statement since the standard site extension filters
-- resulted in a poor plan
and case
when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
when exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)) then 1
else 0
end = 1
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.ID as RECORDID,
MYSITESREV_CTE.DATE,
MYSITESREV_CTE.DATEADDED,
MYSITESREV_CTE.AMOUNT,
case MYSITESREV_CTE.TRANSACTIONTYPECODE
when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT(MYSITESREV_CTE.ID)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(MYSITESREV_CTE.ID, 1, 0) -- Calculate using base currency for now
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 DESIGNATION,
(
select
dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
from
dbo.CAMPAIGN
inner join
dbo.REVENUESPLITCAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
inner join
dbo.REVENUESPLIT on REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
where
REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID
) as CAMPAIGNS,
dbo.UFN_REVENUE_BUILDSITELIST(MYSITESREV_CTE.ID) SITES
from MYSITESREV_CTE
left join
dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = MYSITESREV_CTE.ID
left join
dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
where
(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 gift
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
)
)
and (not @NUMBERTOSHOWCODE in (3,4,5)
or (MYSITESREV_CTE.DATE >= @STARTDATE and MYSITESREV_CTE.DATE <= @ENDDATE))
and MYSITESREV_CTE.CONSTITUENTID = @CONSTITUENTID
and exists(
select REVENUESPLIT.ID
from dbo.REVENUESPLIT
inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
where REVENUEID=MYSITESREV_CTE.ID
)
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
ID,
CONSTITUENTID,
AMOUNT,
TRANSACTIONTYPE,
TRANSACTIONTYPECODE,
DATE,
DATEADDED
from
dbo.REVENUE
where
REVENUE.CONSTITUENTID = @CONSTITUENTID
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
-- Using a case statement since the standard site extension filters
-- resulted in a poor plan
and case
when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
when exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)) then 1
else 0
end = 1
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)
MYSITESREV_CTE.ID,
MYSITESREV_CTE.ID as RECORDID,
MYSITESREV_CTE.DATE,
MYSITESREV_CTE.DATEADDED,
MYSITESREV_CTE.AMOUNT,
case MYSITESREV_CTE.TRANSACTIONTYPECODE
when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNT(MYSITESREV_CTE.ID)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(MYSITESREV_CTE.ID, 1, 0) -- Calculate using base currency for now
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 DESIGNATION,
(
select
dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
from
dbo.CAMPAIGN
inner join
dbo.REVENUESPLITCAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
inner join
dbo.REVENUESPLIT on REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
where
REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID
) as CAMPAIGNS,
dbo.UFN_REVENUE_BUILDSITELIST(MYSITESREV_CTE.ID) SITES
from
MYSITESREV_CTE
left join
dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = MYSITESREV_CTE.ID
left join
dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
where
(MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 or --Pledge
(MYSITESREV_CTE.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9,10,11,12,13)) or --Planned gift
(MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and exists (
select 1 from dbo.REVENUESPLIT RS where RS.REVENUEID = MYSITESREV_CTE.ID and RS.APPLICATIONCODE in (0, 3)))) and -- Payment
MYSITESREV_CTE.CONSTITUENTID = @CONSTITUENTID
and exists(
select ID
from dbo.REVENUESPLIT
where REVENUEID=MYSITESREV_CTE.ID
)
order by
DATE desc, DATEADDED desc, AMOUNT desc;
end