USP_GROUP_5YEARGIVINGGET
Returns the 5 year giving history for a group.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @DESIGNATIONLEVELCATEGORYCODEID | uniqueidentifier | IN | |
| @DESIGNATIONID | uniqueidentifier | IN | |
| @CAMPAIGNID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @SITEFILTERMODE | tinyint | IN | |
| @SITESSELECTED | xml | IN | |
| @SECURITYFEATUREID | uniqueidentifier | IN | |
| @SECURITYFEATURETYPE | tinyint | IN | |
| @CURRENCYCODE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_GROUP_5YEARGIVINGGET
(
@CONSTITUENTID uniqueidentifier,
@DESIGNATIONLEVELCATEGORYCODEID uniqueidentifier = null,
@DESIGNATIONID uniqueidentifier = null,
@CAMPAIGNID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@CURRENCYCODE tinyint = 1 -- (1, null) = organization, 3 = my base
)
as
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
declare @USERISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID)
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @SELECTEDCURRENCYID uniqueidentifier;
if @CURRENCYCODE = 3
begin
if dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID) is not null
begin
select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
from dbo.CURRENCYSET
where
CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID);
end
else
begin
select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
from dbo.CURRENCYSET
where
CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET();
end
end
else
set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;
declare @CURRENCYISOCODE nvarchar(3);
declare @CURRENCYDECIMALDIGITS tinyint;
declare @CURRENCYSYMBOL nvarchar(5);
declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
declare @CURRENCYROUNDINGTYPECODE tinyint;
select
@CURRENCYISOCODE = CURRENCYPROPERTIES.ISO4217,
@CURRENCYDECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS,
@CURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
@CURRENCYROUNDINGTYPECODE = CURRENCYPROPERTIES.ROUNDINGTYPECODE
from
dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES;
declare @ISHOUSEHOLD bit, @GROUPINCLUDESMEMBERGIVING bit;
select
@GROUPINCLUDESMEMBERGIVING =
case
when GD.GROUPTYPECODE = 0 then 1
when GD.GROUPTYPECODE = 1 then GT.INCLUDEMEMBERGIVING
end,
@ISHOUSEHOLD =
case
when GD.GROUPTYPECODE = 0 then 1
else 0
end
from dbo.GROUPDATA GD
left join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID
where GD.ID=@CONSTITUENTID;
declare @STARTDATE datetime = dateadd(year,-5,dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@CURRENTDATE,1));
declare @ENDDATE datetime = dateadd(year,1,dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE,0));
with MYSITESREV_CTE as
(
select
ID,
CONSTITUENTID,
TRANSACTIONTYPECODE,
DATE,
dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(DATE,0) FISCALYEAR
from
dbo.REVENUE
where
(
REVENUE.CONSTITUENTID = @CONSTITUENTID
or
REVENUE.CONSTITUENTID in
(
select GROUPMEMBER.MEMBERID
from dbo.GROUPMEMBER
where GROUPMEMBER.GROUPID = @CONSTITUENTID
)
)
and DATE > @STARTDATE
and DATE < @ENDDATE
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
left join dbo.UFN_SITEID_MAPFROM_REVENUESPLITID_BULK() REVSITES on REVSITES.ID = RSSUB.ID
where RSSUB.REVENUEID = REVENUE.ID
/*next line is #SITEEXTENTION code*/
and (@USERISSYSADMIN = 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)
)
)
and exists
(
select top 1 1
from dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS_BULK(@CURRENTAPPUSERID) REVGRANTED
where
REVGRANTED.ID = REVENUE.ID
and REVGRANTED.ACCESSGRANTED = 1
)
)
select
FISCALYEAR,
sum(case when TYPE in (0) then AMOUNT else 0 end) as GIFTS, -- (Gift)
sum(case when TYPE in (1) then AMOUNT else 0 end) as PLEDGES, -- (Pledge)
sum(case when TYPE in (2) then AMOUNT else 0 end) as PAYMENTS, -- (Pledge Payments)
sum(case when TYPE = (3) then AMOUNT else 0 end) as WRITEOFFS,
count(distinct case when TYPE in (0, 1) then REVENUEID else null end) as NUMBER,
sum(PLEDGEBALANCE) as PLEDGEBALANCE,
isnull(sum(
case
when @ISHOUSEHOLD = 1 and (TYPE = 0 or TYPE = 1) then TAXCLAIMAMOUNT
else 0
end
), 0) TAXCLAIMAMOUNT,
@CURRENCYISOCODE ISO4217,
@CURRENCYDECIMALDIGITS DECIMALDIGITS,
@CURRENCYSYMBOL CURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE SYMBOLDISPLAYSETTINGCODE
from
(
-- group member gifts, pledges, or payments
select
MYSITESREV_CTE.ID as REVENUEID,
case
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE in (0, 3) then 0 -- Gift
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 then 1 --Pledge
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 2 then 2 --Payment (Pledge payment)
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 7 then 0 --Auction donation
end as TYPE,
REVENUESPLIT.AMOUNTINCURRENCY AMOUNT,
MYSITESREV_CTE.FISCALYEAR,
case when MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 then (select dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID, @SELECTEDCURRENCYID)) else 0 end as PLEDGEBALANCE,
isnull(REVENUESPLITGIFTAID.TAXCLAIMAMOUNTINCURRENCY, 0) TAXCLAIMAMOUNT
from MYSITESREV_CTE
left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) REVENUESPLIT on REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID
inner join dbo.GROUPMEMBER GM on MYSITESREV_CTE.CONSTITUENTID = GM.MEMBERID
left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
left join dbo.DESIGNATION DESIGNATION on DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID
left join dbo.DESIGNATIONLEVEL DESIGNATIONLEVEL1 on DESIGNATIONLEVEL1.ID =
case
when DESIGNATION.DESIGNATIONLEVEL5ID is not null then DESIGNATION.DESIGNATIONLEVEL5ID
when DESIGNATION.DESIGNATIONLEVEL4ID is not null then DESIGNATION.DESIGNATIONLEVEL4ID
when DESIGNATION.DESIGNATIONLEVEL3ID is not null then DESIGNATION.DESIGNATIONLEVEL3ID
when DESIGNATION.DESIGNATIONLEVEL2ID is not null then DESIGNATION.DESIGNATIONLEVEL2ID
else DESIGNATION.DESIGNATIONLEVEL1ID
end
left join dbo.REVENUESPLITCAMPAIGN on REVENUESPLIT.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID
left join dbo.UFN_REVENUESPLITGIFTAID_GETELIGIBLE(1) ELIGIBLEREVENUESPLITGIFTAID on ELIGIBLEREVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
left join dbo.UFN_REVENUESPLITGIFTAID_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = ELIGIBLEREVENUESPLITGIFTAID.ID
left join dbo.UFN_PLEDGE_GETDESIGNATIONBALANCEINCURRENCY_BULK(@SELECTEDCURRENCYID) DESIGNATIONPLEDGEBALANCE on DESIGNATIONPLEDGEBALANCE.ID = MYSITESREV_CTE.ID and DESIGNATIONPLEDGEBALANCE.DESIGNATIONID = DESIGNATION.ID
where
@GROUPINCLUDESMEMBERGIVING = 1
and GM.GROUPID = @CONSTITUENTID
and (@DESIGNATIONLEVELCATEGORYCODEID is null or @DESIGNATIONLEVELCATEGORYCODEID = DESIGNATIONLEVEL1.DESIGNATIONLEVELCATEGORYCODEID)
and (@DESIGNATIONID is null or DESIGNATION.ID = @DESIGNATIONID)
and (@CAMPAIGNID is null or REVENUESPLITCAMPAIGN.CAMPAIGNID = @CAMPAIGNID)
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 gifts, pledges, or payments
select
MYSITESREV_CTE.ID as REVENUEID,
case
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE in (0, 3) then 0 -- Gift
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 then 1 --Pledge
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 2 then 2 --Payment (Pledge payment)
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 7 then 0 --Auction donation
end as TYPE,
REVENUESPLIT.AMOUNTINCURRENCY AMOUNT,
MYSITESREV_CTE.FISCALYEAR,
case when MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 then (select dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID, @SELECTEDCURRENCYID)) else 0 end as PLEDGEBALANCE,
0 TAXCLAIMAMOUNT
from MYSITESREV_CTE
left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) REVENUESPLIT on REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID
left join dbo.DESIGNATION DESIGNATION on DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID
left join dbo.DESIGNATIONLEVEL DESIGNATIONLEVEL1 on DESIGNATIONLEVEL1.ID =
case
when DESIGNATION.DESIGNATIONLEVEL5ID is not null then DESIGNATION.DESIGNATIONLEVEL5ID
when DESIGNATION.DESIGNATIONLEVEL4ID is not null then DESIGNATION.DESIGNATIONLEVEL4ID
when DESIGNATION.DESIGNATIONLEVEL3ID is not null then DESIGNATION.DESIGNATIONLEVEL3ID
when DESIGNATION.DESIGNATIONLEVEL2ID is not null then DESIGNATION.DESIGNATIONLEVEL2ID
else DESIGNATION.DESIGNATIONLEVEL1ID
end
left join dbo.REVENUESPLITCAMPAIGN on REVENUESPLIT.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID
left join dbo.UFN_REVENUESPLITGIFTAID_GETELIGIBLE(1) ELIGIBLEREVENUESPLITGIFTAID on ELIGIBLEREVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
left join dbo.UFN_PLEDGE_GETDESIGNATIONBALANCEINCURRENCY_BULK(@SELECTEDCURRENCYID) DESIGNATIONPLEDGEBALANCE on DESIGNATIONPLEDGEBALANCE.ID = MYSITESREV_CTE.ID and DESIGNATIONPLEDGEBALANCE.DESIGNATIONID = DESIGNATION.ID
where
MYSITESREV_CTE.CONSTITUENTID = @CONSTITUENTID
and (@DESIGNATIONLEVELCATEGORYCODEID is null or @DESIGNATIONLEVELCATEGORYCODEID = DESIGNATIONLEVEL1.DESIGNATIONLEVELCATEGORYCODEID)
and (@DESIGNATIONID is null or DESIGNATION.ID = @DESIGNATIONID)
and (@CAMPAIGNID is null or REVENUESPLITCAMPAIGN.CAMPAIGNID = @CAMPAIGNID)
union all
-- group member write offs
select
MYSITESREV_CTE.ID as REVENUEID,
3 as TYPE, -- WRITE-OFF
WRITEOFFSPLIT.AMOUNTINCURRENCY as AMOUNT,
MYSITESREV_CTE.FISCALYEAR,
0 as PLEDGEBALANCE,
0 TAXCLAIMAMOUNT
from dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) WRITEOFFSPLIT
left join dbo.WRITEOFF WO on WRITEOFFSPLIT.WRITEOFFID = WO.ID
left join MYSITESREV_CTE on WO.REVENUEID = MYSITESREV_CTE.ID
inner join dbo.GROUPMEMBER GM on MYSITESREV_CTE.CONSTITUENTID = GM.MEMBERID
left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
left join dbo.DESIGNATION DESIGNATION on DESIGNATION.ID = WRITEOFFSPLIT.DESIGNATIONID
left join dbo.DESIGNATIONLEVEL DESIGNATIONLEVEL1 on DESIGNATIONLEVEL1.ID =
case
when DESIGNATION.DESIGNATIONLEVEL5ID is not null then DESIGNATION.DESIGNATIONLEVEL5ID
when DESIGNATION.DESIGNATIONLEVEL4ID is not null then DESIGNATION.DESIGNATIONLEVEL4ID
when DESIGNATION.DESIGNATIONLEVEL3ID is not null then DESIGNATION.DESIGNATIONLEVEL3ID
when DESIGNATION.DESIGNATIONLEVEL2ID is not null then DESIGNATION.DESIGNATIONLEVEL2ID
else DESIGNATION.DESIGNATIONLEVEL1ID
end
where
@GROUPINCLUDESMEMBERGIVING = 1
and GM.GROUPID = @CONSTITUENTID
and (@DESIGNATIONLEVELCATEGORYCODEID is null or @DESIGNATIONLEVELCATEGORYCODEID = DESIGNATIONLEVEL1.DESIGNATIONLEVELCATEGORYCODEID)
and (@DESIGNATIONID is null or DESIGNATION.ID = @DESIGNATIONID)
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 write offs
select
MYSITESREV_CTE.ID as REVENUEID,
3 as TYPE, -- WRITE-OFF
WRITEOFFSPLIT.AMOUNTINCURRENCY as AMOUNT,
MYSITESREV_CTE.FISCALYEAR,
0 as PLEDGEBALANCE,
0 TAXCLAIMAMOUNT
from dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) WRITEOFFSPLIT
left join dbo.WRITEOFF WO on WRITEOFFSPLIT.WRITEOFFID = WO.ID
left join MYSITESREV_CTE on WO.REVENUEID = MYSITESREV_CTE.ID
left join dbo.DESIGNATION DESIGNATION on DESIGNATION.ID = WRITEOFFSPLIT.DESIGNATIONID
left join dbo.DESIGNATIONLEVEL DESIGNATIONLEVEL1 on DESIGNATIONLEVEL1.ID =
case
when DESIGNATION.DESIGNATIONLEVEL5ID is not null then DESIGNATION.DESIGNATIONLEVEL5ID
when DESIGNATION.DESIGNATIONLEVEL4ID is not null then DESIGNATION.DESIGNATIONLEVEL4ID
when DESIGNATION.DESIGNATIONLEVEL3ID is not null then DESIGNATION.DESIGNATIONLEVEL3ID
when DESIGNATION.DESIGNATIONLEVEL2ID is not null then DESIGNATION.DESIGNATIONLEVEL2ID
else DESIGNATION.DESIGNATIONLEVEL1ID
end
where
MYSITESREV_CTE.CONSTITUENTID = @CONSTITUENTID
and (@DESIGNATIONLEVELCATEGORYCODEID is null or @DESIGNATIONLEVELCATEGORYCODEID = DESIGNATIONLEVEL1.DESIGNATIONLEVELCATEGORYCODEID)
and (@DESIGNATIONID is null or DESIGNATION.ID = @DESIGNATIONID)
) as DATA
where TYPE is not NULL
group by FISCALYEAR;