USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGROUP_REVENUEHISTORY
The load procedure used by the view dataform template "Constituent Group Revenue History View"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter used to load the fields defined on the form. |
| @DATALOADED | bit | INOUT | Output parameter indicating whether or not data was actually loaded. |
| @GROUPINCLUDESMEMBERREVENUE | bit | INOUT | Group includes member revenue |
| @TOTALNUMBERFROMGROUP | int | INOUT | Total number of transactions from group |
| @TOTALNUMBERFROMGROUPMEMBERS | int | INOUT | Total number of transactions from members |
| @TOTALGROUPAMOUNT | money | INOUT | Total revenue from group |
| @TOTALGROUPMEMBERAMOUNT | money | INOUT | Total revenue from members |
| @ISHOUSEHOLD | bit | INOUT | Is household |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @TOTALGROUPMEMBERAMOUNTGIFTAID | money | INOUT | Total revenue from members with Gift Aid |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGROUP_REVENUEHISTORY
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@GROUPINCLUDESMEMBERREVENUE bit = null output,
@TOTALNUMBERFROMGROUP int = null output,
@TOTALNUMBERFROMGROUPMEMBERS int = null output,
@TOTALGROUPAMOUNT money = null output,
@TOTALGROUPMEMBERAMOUNT money = null output,
@ISHOUSEHOLD bit = null output,
@CURRENTAPPUSERID uniqueidentifier = null,
@TOTALGROUPMEMBERAMOUNTGIFTAID money = null output
)
as
set nocount on;
set @DATALOADED = 0;
declare @CURRENTDATE datetime;
set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
declare @SECURITYFEATUREID uniqueidentifier;
declare @SECURITYFEATURETYPE tinyint;
set @SECURITYFEATUREID = '85de7bec-b9d3-44c9-9f2b-df0ae31ad853';
set @SECURITYFEATURETYPE = 1;
-- 'Revenue' includes
-- Gifts, Pledges, Recurring gift payments, Event registration fees, Matching gift payments,
-- Other revenue, Membership fees (2.0?)
-- households always include member giving, other types get looked up
select
@GROUPINCLUDESMEMBERREVENUE =
case
when GD.GROUPTYPECODE = 0 then 1
when GD.GROUPTYPECODE = 1 then GT.INCLUDEMEMBERGIVING
end,
@ISHOUSEHOLD =
case
when GD.GROUPTYPECODE = 0 then 1
when GD.GROUPTYPECODE = 1 then 0
end,
@DATALOADED = 1
from dbo.GROUPDATA GD
left join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID
where GD.ID=@ID;
-- calculate the member giving
if @GROUPINCLUDESMEMBERREVENUE = 1
begin
select
@DATALOADED = 1,
@TOTALGROUPMEMBERAMOUNT = cast(sum(cast(RS.AMOUNT as decimal(20,5)) - cast(coalesce(WO.AMOUNT, 0) as decimal(20,5))) as money),
@TOTALGROUPMEMBERAMOUNTGIFTAID = coalesce(cast(sum(cast(case R.TRANSACTIONTYPECODE when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT(RS.ID, 1)
when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT(RS.ID)
else RS.AMOUNT end as decimal(20,5)) - cast(coalesce(WO.AMOUNT, 0) as decimal(20,5))) as money), 0)
from dbo.REVENUE R
inner join dbo.REVENUESPLIT RS
on RS.REVENUEID = R.ID
left join
(select
INSTALLMENTSPLIT.PLEDGEID,
INSTALLMENTSPLIT.DESIGNATIONID,
sum(coalesce(INSTALLMENTSPLITWRITEOFF.AMOUNT,0)) AMOUNT
from
dbo.INSTALLMENTSPLITWRITEOFF
inner join
dbo.INSTALLMENTSPLIT
on
INSTALLMENTSPLIT.ID = INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID
group by
INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID) WO
on
WO.PLEDGEID = R.ID and WO.DESIGNATIONID = RS.DESIGNATIONID
inner join dbo.GROUPMEMBER GM
on R.CONSTITUENTID = GM.MEMBERID
left join dbo.GROUPMEMBERDATERANGE GMDR
on GMDR.GROUPMEMBERID = GM.ID
where
GM.GROUPID = @ID and
((R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0,1,3,5,7)) or
R.TRANSACTIONTYPECODE = 1) and
((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= R.DATE)) or
(GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= R.DATE)) or
(GMDR.DATEFROM <= R.DATE and GMDR.DATETO >= R.DATE))
and (
(R.TRANSACTIONTYPECODE = 0
and RS.APPLICATIONCODE=1
and RS.TYPECODE=1
and dbo.UFN_EVENTREGISTRANTPAYMENT_USERHASSITEACCESS(RS.ID,@CURRENTAPPUSERID) = 1
)
or(
not(R.TRANSACTIONTYPECODE = 0
and RS.APPLICATIONCODE=1
and RS.TYPECODE=1
)
and exists
(
select top 1 REVSITES.SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RS.ID) REVSITES
where (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)))
)
)
);
-- Total Transaction count includes all revenue types
-- Gifts, Pledges, Pledge Payments, Recurring Gifts, Recurring Gift Payments,
-- Matching Gift Claims, Matching Gift Payments
-- Event Registration Fees, Membership Fees (2.0?)
select
@TOTALNUMBERFROMGROUPMEMBERS = Count(distinct R.ID)
from dbo.REVENUE R
inner join dbo.REVENUESPLIT RS
on R.ID = RS.REVENUEID
inner join dbo.GROUPMEMBER GM
on R.CONSTITUENTID = GM.MEMBERID
left join dbo.GROUPMEMBERDATERANGE GMDR
on GMDR.GROUPMEMBERID = GM.ID
where
GM.GROUPID = @ID and
RS.APPLICATIONCODE <> 2 and
((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= R.DATE)) or
(GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= R.DATE)) or
(GMDR.DATEFROM <= R.DATE and GMDR.DATETO >= R.DATE))
and exists(
select ID
from dbo.REVENUESPLIT
where REVENUEID=R.ID
and (
(R.TRANSACTIONTYPECODE = 0
and APPLICATIONCODE=1
and TYPECODE=1
and dbo.UFN_EVENTREGISTRANTPAYMENT_USERHASSITEACCESS(ID,@CURRENTAPPUSERID) = 1
)
or(
not(R.TRANSACTIONTYPECODE = 0
and APPLICATIONCODE=1
and TYPECODE=1
)
and exists
(
select top 1 REVSITES.SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RS.ID) REVSITES
where (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)))
)
)
)
);
-- Pledge payments
select
@TOTALNUMBERFROMGROUPMEMBERS = @TOTALNUMBERFROMGROUPMEMBERS + count(RS.ID)
from dbo.REVENUESPLIT RS
inner join dbo.REVENUE R
on R.ID = RS.ID
inner join dbo.GROUPMEMBER GM
on R.CONSTITUENTID = GM.MEMBERID
left join dbo.GROUPMEMBERDATERANGE GMDR
on GMDR.GROUPMEMBERID = GM.ID
inner join dbo.INSTALLMENTSPLITPAYMENT ISP
on ISP.PAYMENTID = RS.ID
where
R.TRANSACTIONTYPECODE = 0 and
RS.APPLICATIONCODE = 2 and
GM.GROUPID = @ID and
((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= R.DATE)) or
(GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= R.DATE)) or
(GMDR.DATEFROM <= R.DATE and GMDR.DATETO >= R.DATE))
and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1;
-- Write-offs
select
@TOTALNUMBERFROMGROUPMEMBERS = @TOTALNUMBERFROMGROUPMEMBERS + count(R.ID)
from dbo.Revenue R
inner join dbo.GROUPMEMBER GM
on R.CONSTITUENTID = GM.MEMBERID
left join dbo.GROUPMEMBERDATERANGE GMDR
on GMDR.GROUPMEMBERID = GM.ID
inner join dbo.WRITEOFF WO
on R.ID = WO.REVENUEID
where GM.GROUPID = @ID and
((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= R.DATE)) or
(GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= R.DATE)) or
(GMDR.DATEFROM <= R.DATE and GMDR.DATETO >= R.DATE))
and exists(
select ID
from dbo.REVENUESPLIT
where REVENUEID=R.ID
and (
(R.TRANSACTIONTYPECODE = 0
and APPLICATIONCODE=1
and TYPECODE=1
and dbo.UFN_EVENTREGISTRANTPAYMENT_USERHASSITEACCESS(ID,@CURRENTAPPUSERID) = 1
)
or(
not(R.TRANSACTIONTYPECODE = 0
and APPLICATIONCODE=1
and TYPECODE=1
)
and exists
(
select top 1 REVSITES.SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
where (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)))
)
)
)
);
-- Matching gift claims
select
@TOTALNUMBERFROMGROUPMEMBERS = @TOTALNUMBERFROMGROUPMEMBERS + count(R.ID)
from dbo.REVENUE R
inner join dbo.REVENUEMATCHINGGIFT RMG
on RMG.ID = R.ID
inner join dbo.REVENUE R2
on R2.ID = RMG.MGSOURCEREVENUEID
inner join dbo.GROUPMEMBER GM
on R2.CONSTITUENTID = GM.MEMBERID
left join dbo.GROUPMEMBERDATERANGE GMDR
on GMDR.GROUPMEMBERID = GM.ID
where R.TRANSACTIONTYPECODE = 3 and
GM.GROUPID = @ID and
((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= R.DATE)) or
(GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= R.DATE)) or
(GMDR.DATEFROM <= R.DATE and GMDR.DATETO >= R.DATE))
and exists(
select ID
from dbo.REVENUESPLIT
where REVENUEID=R.ID
and (
(R.TRANSACTIONTYPECODE = 0
and APPLICATIONCODE=1
and TYPECODE=1
and dbo.UFN_EVENTREGISTRANTPAYMENT_USERHASSITEACCESS(ID,@CURRENTAPPUSERID) = 1
)
or(
not(R.TRANSACTIONTYPECODE = 0
and APPLICATIONCODE=1
and TYPECODE=1
)
and exists
(
select top 1 REVSITES.SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
where (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)))
)
)
)
);
end
-- calculate the revenue from the group itself
select
@DATALOADED = 1,
@TOTALGROUPAMOUNT = cast(sum(cast(RS.AMOUNT as decimal(20,5)) - cast(coalesce(WO.AMOUNT, 0) as decimal(20,5))) as money)
from
dbo.REVENUE R
inner join dbo.REVENUESPLIT RS
on RS.REVENUEID = R.ID
left join
(select
INSTALLMENTSPLIT.PLEDGEID,
INSTALLMENTSPLIT.DESIGNATIONID,
sum(coalesce(INSTALLMENTSPLITWRITEOFF.AMOUNT,0)) AMOUNT
from
dbo.INSTALLMENTSPLITWRITEOFF
inner join
dbo.INSTALLMENTSPLIT
on
INSTALLMENTSPLIT.ID = INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID
group by
INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID) WO
on
WO.PLEDGEID = R.ID and WO.DESIGNATIONID = RS.DESIGNATIONID
where
((R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0,1,3,5,7)) or
R.TRANSACTIONTYPECODE = 1) and
R.CONSTITUENTID = @ID
and (
(R.TRANSACTIONTYPECODE = 0
and RS.APPLICATIONCODE=1
and RS.TYPECODE=1
and dbo.UFN_EVENTREGISTRANTPAYMENT_USERHASSITEACCESS(RS.ID,@CURRENTAPPUSERID) = 1
)
or(
not(R.TRANSACTIONTYPECODE = 0
and RS.APPLICATIONCODE=1
and RS.TYPECODE=1
)
and exists
(
select top 1 REVSITES.SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RS.ID) REVSITES
where (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)))
)
)
);
-- Total Transaction count includes all revenue types
-- Gifts, Pledges, Pledge Payments, Recurring Gifts, Recurring Gift Payments,
-- Matching Gift Claims, Matching Gift Payments
-- Event Registration Fees, Membership Fees (2.0?)
select
@TOTALNUMBERFROMGROUP = Count(distinct R.ID)
from dbo.REVENUE R
inner join dbo.REVENUESPLIT RS
on R.ID = RS.REVENUEID
where
RS.APPLICATIONCODE <> 2 and
R.CONSTITUENTID = @ID
and exists(
select ID
from dbo.REVENUESPLIT
where REVENUEID=R.ID
and (
(R.TRANSACTIONTYPECODE = 0
and APPLICATIONCODE=1
and TYPECODE=1
and dbo.UFN_EVENTREGISTRANTPAYMENT_USERHASSITEACCESS(ID,@CURRENTAPPUSERID) = 1
)
or(
not(R.TRANSACTIONTYPECODE = 0
and APPLICATIONCODE=1
and TYPECODE=1
)
and exists
(
select top 1 REVSITES.SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
where (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)))
)
)
)
);
-- Pledge payments
select
@TOTALNUMBERFROMGROUP = @TOTALNUMBERFROMGROUP + count(RS.ID)
from dbo.REVENUESPLIT RS
inner join dbo.INSTALLMENTSPLITPAYMENT ISP
on ISP.PAYMENTID = RS.ID
inner join dbo.REVENUE R
on R.ID = RS.REVENUEID
where
R.TRANSACTIONTYPECODE = 0 and
RS.APPLICATIONCODE = 2 and
R.CONSTITUENTID = @ID
and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1;
-- Write-offs
select
@TOTALNUMBERFROMGROUP = @TOTALNUMBERFROMGROUP + count(R.ID)
from dbo.Revenue R
inner join dbo.WRITEOFF WO
on R.ID = WO.REVENUEID
where R.CONSTITUENTID = @ID
and exists(
select ID
from dbo.REVENUESPLIT
where REVENUEID=R.ID
and (
(R.TRANSACTIONTYPECODE = 0
and APPLICATIONCODE=1
and TYPECODE=1
and dbo.UFN_EVENTREGISTRANTPAYMENT_USERHASSITEACCESS(ID,@CURRENTAPPUSERID) = 1
)
or(
not(R.TRANSACTIONTYPECODE = 0
and APPLICATIONCODE=1
and TYPECODE=1
)
and exists
(
select top 1 REVSITES.SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
where (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)))
)
)
)
);;
-- Matching gift claims
select
@TOTALNUMBERFROMGROUP = @TOTALNUMBERFROMGROUP + count(R.ID)
from dbo.REVENUE R
inner join dbo.REVENUEMATCHINGGIFT RMG
on RMG.ID = R.ID
inner join dbo.REVENUE R2
on R2.ID = RMG.MGSOURCEREVENUEID
where R.TRANSACTIONTYPECODE = 3 and
R2.CONSTITUENTID = @ID
and exists(
select ID
from dbo.REVENUESPLIT
where REVENUEID=R.ID
and (
(R.TRANSACTIONTYPECODE = 0
and APPLICATIONCODE=1
and TYPECODE=1
and dbo.UFN_EVENTREGISTRANTPAYMENT_USERHASSITEACCESS(ID,@CURRENTAPPUSERID) = 1
)
or(
not(R.TRANSACTIONTYPECODE = 0
and APPLICATIONCODE=1
and TYPECODE=1
)
and exists
(
select top 1 REVSITES.SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
where (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)))
)
)
)
);
select @TOTALGROUPMEMBERAMOUNT = coalesce(@TOTALGROUPMEMBERAMOUNT, 0);
select @TOTALGROUPAMOUNT = coalesce(@TOTALGROUPAMOUNT, 0);
return 0;