USP_DATALIST_CONSTITUENTPROFILEDASHBOARDREVENUEBYDESIGNATION
Returns a constituent's revenue by designation.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Constituent |
| @ISVISIBLE | bit | IN | Visible |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @SELECTEDCURRENCYID | uniqueidentifier | IN | Selected currency ID |
| @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. |
| @REVENUEFILTERID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROFILEDASHBOARDREVENUEBYDESIGNATION
(
@CONSTITUENTID uniqueidentifier,
@ISVISIBLE bit = 1,
@CURRENTAPPUSERID uniqueidentifier = null,
@SELECTEDCURRENCYID uniqueidentifier = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@REVENUEFILTERID uniqueidentifier = null
)
with execute as OWNER
as
set nocount on;
if @ISVISIBLE = 1
begin
declare
@CURRENCYDECIMALDIGITS tinyint = 0,
@ORGANIZATIONCURRENCYID uniqueidentifier = null,
@CURRENCYROUNDINGTYPECODE tinyint
set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @ORIGINCODE tinyint
select @ORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE
from dbo.MULTICURRENCYCONFIGURATION;
declare @CURRENCYCODE tinyint = 3
if @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID
set @CURRENCYCODE = 1;
select
@CURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
from dbo.CURRENCY where ID = @SELECTEDCURRENCYID;
declare @ISGROUP bit;
declare @INCLUDEMEMBERGIVING bit;
select
@ISGROUP = 1,
@INCLUDEMEMBERGIVING =
case
when GROUPDATA.GROUPTYPECODE = 0 or GROUPTYPE.INCLUDEMEMBERGIVING = 1 then 1
else 0
end
from dbo.GROUPDATA
left join dbo.GROUPTYPE on GROUPDATA.GROUPTYPEID = GROUPTYPE.ID
where GROUPDATA.ID = @CONSTITUENTID;
declare @CURRENTDATE datetime;
set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
/* Get RevSplit IDs */
if object_id('tempdb..#TMP_DATA_CONSTITUENTPROFILEDASHBOARDREVENUEBYDESIGNATION_REVENUESPLITIDS') is not null
drop table #TMP_DATA_CONSTITUENTPROFILEDASHBOARDREVENUEBYDESIGNATION_REVENUESPLITIDS;
create table #TMP_DATA_CONSTITUENTPROFILEDASHBOARDREVENUEBYDESIGNATION_REVENUESPLITIDS
(
ID uniqueidentifier,
ISGROUPMEMBER bit,
ISGROUP bit
);
declare @SQL nvarchar(max);
set @SQL = '
with CONSTITS_CTE as
(
select
@CONSTITUENTID as CONSTITUENTID,
null as DATEFROM,
null as DATETO,
0 ISGROUPMEMBER,
0 ISGROUP
'
if @INCLUDEMEMBERGIVING=1
set @SQL = @SQL + '
union all
select
GROUPMEMBER.MEMBERID as CONSTITUENTID,
GROUPMEMBERDATERANGE.DATEFROM,
GROUPMEMBERDATERANGE.DATETO,
1 ISGROUPMEMBER,
0 ISGROUP
from dbo.GROUPMEMBER
left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
where
GROUPMEMBER.GROUPID = @CONSTITUENTID
'
set @SQL = @SQL + '
union all
select GROUPMEMBER.GROUPID as CONSTITUENTID,
GROUPMEMBERDATERANGE.DATEFROM,
GROUPMEMBERDATERANGE.DATETO,
0 ISGROUPMEMBER,
1 ISGROUP
from dbo.GROUPMEMBER
left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
where
GROUPMEMBER.MEMBERID = @CONSTITUENTID
)
insert into #TMP_DATA_CONSTITUENTPROFILEDASHBOARDREVENUEBYDESIGNATION_REVENUESPLITIDS(ID,ISGROUPMEMBER,ISGROUP)
select REVENUESPLIT.ID,CONSTITS.ISGROUPMEMBER,CONSTITS.ISGROUP
from REVENUESPLIT ' + char(13);
if @REVENUEFILTERID is not null
set @SQL = @SQL + 'inner join dbo.UFN_REVENUEFILTER_BYID(@REVENUEFILTERID) FILTERED on REVENUESPLIT.ID = FILTERED.ID ' + char(13);
set @SQL = @SQL +
'inner join dbo.REVENUE
on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join CONSTITS_CTE CONSTITS
on CONSTITS.CONSTITUENTID = REVENUE.CONSTITUENTID
where REVENUE.TRANSACTIONTYPECODE not in (5, 2) ' + char(13);
exec sp_executesql @SQL, N'@CONSTITUENTID uniqueidentifier,@REVENUEFILTERID uniqueidentifier',@CONSTITUENTID,@REVENUEFILTERID;
/*DELETE FOR SECURITY AND SITEFILTER*/
delete FILTERED
from #TMP_DATA_CONSTITUENTPROFILEDASHBOARDREVENUEBYDESIGNATION_REVENUESPLITIDS FILTERED
where
not exists
(
select top 1 REVSITES.SITEID
from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FILTERED.ID) REVSITES
where
-- Using a case statement since the standard site extension filters
-- resulted in a poor plan
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
)
declare @REVLIST table
(
REVENUEID uniqueidentifier,
DESIGNATIONID uniqueidentifier,
AMOUNT money,
ISGROUPMEMBER bit,
ISGROUP bit
);
insert into @REVLIST
select REVENUESPLIT.REVENUEID,
REVENUESPLIT.DESIGNATIONID,
case
when REVENUE.TRANSACTIONTYPECODE in (
1, --Pledge
3, --Matching gift claim
4, --Planned gift
6, --Grant award
8 --Donor challenge claim
)
then
(select REVENUESPLITBALANCE.BALANCE
from dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY(
REVENUE.ID,
@SELECTEDCURRENCYID,
@ORGANIZATIONCURRENCYID,
@CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE,
@CURRENTDATE,
@ORIGINCODE,
@CURRENCYCODE
) as REVENUESPLITBALANCE
where REVENUESPLITBALANCE.ID = FILTERED.ID)
else REVENUESPLIT.AMOUNTINCURRENCY
end AMOUNT,
FILTERED.ISGROUP,
FILTERED.ISGROUPMEMBER
from #TMP_DATA_CONSTITUENTPROFILEDASHBOARDREVENUEBYDESIGNATION_REVENUESPLITIDS FILTERED
inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
@SELECTEDCURRENCYID,
@ORGANIZATIONCURRENCYID,
@CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE) as REVENUESPLIT on FILTERED.ID = REVENUESPLIT.ID
inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID;
with REV_CTE
as
(
select
DESIGNATIONID,
count(distinct REVENUEID) CNT,
sum(AMOUNT) AMOUNT,
ISGROUPMEMBER,
ISGROUP
from @REVLIST
group by DESIGNATIONID, ISGROUPMEMBER, ISGROUP
)
,DES_CTE as
(
select
DESIGNATIONID,
sum(case when REVSUB.ISGROUP=0 and REVSUB.ISGROUPMEMBER=0 then REVSUB.AMOUNT else 0 end) as CONSTITUENTAMOUNT,
sum(case when REVSUB.ISGROUP=0 and REVSUB.ISGROUPMEMBER=0 then REVSUB.CNT else 0 end) as CONSTITUENTNUMBER,
sum(case when REVSUB.ISGROUPMEMBER=1 then REVSUB.AMOUNT else 0 end) as MEMBERAMOUNT,
sum(case when REVSUB.ISGROUPMEMBER=1 then REVSUB.CNT else 0 end) as MEMBERNUMBER,
sum(case when REVSUB.ISGROUP=1 then REVSUB.AMOUNT else 0 end) as ASSOCIATEDGROUPSAMOUNT,
sum(case when REVSUB.ISGROUP=1 then REVSUB.CNT else 0 end) as ASSOCIATEDGROUPSNUMBER
from
REV_CTE REVSUB
group by DESIGNATIONID
)
select DESIGNATION.NAME,
DES_CTE.CONSTITUENTAMOUNT,
DES_CTE.CONSTITUENTNUMBER,
DES_CTE.MEMBERAMOUNT,
DES_CTE.MEMBERNUMBER,
DES_CTE.ASSOCIATEDGROUPSAMOUNT,
DES_CTE.ASSOCIATEDGROUPSNUMBER
from DES_CTE
inner join dbo.DESIGNATION on DESIGNATION.ID = DES_CTE.DESIGNATIONID
order by DESIGNATION.NAME
end