USP_DATALIST_CONSTITUENTPROFILEDASHBOARDLARGESTGIFT
This datalist returns information about a constituent's largest gift that is used by the constituent profile dashboard.
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 |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROFILEDASHBOARDLARGESTGIFT
(
@CONSTITUENTID uniqueidentifier,
@ISVISIBLE bit = 1,
@CURRENTAPPUSERID uniqueidentifier,
@SELECTEDCURRENCYID uniqueidentifier = null
)
as
set nocount on;
declare @ISADMIN bit;
declare @APPUSER_IN_NONRACROLE bit;
declare @APPUSER_IN_NOSECGROUPROLE bit;
set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
if @ISVISIBLE = 1
begin
declare @ISGROUP bit, @INCLUDEMEMBERGIVING bit
select
@ISGROUP = C.ISGROUP,
@INCLUDEMEMBERGIVING = case when GD.GROUPTYPECODE = 0 then 1 else coalesce(GT.INCLUDEMEMBERGIVING, 0) end
from dbo.CONSTITUENT C
left join dbo.GROUPDATA GD on C.ID = GD.ID
left join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID
where C.ID = @CONSTITUENTID;
-- DUPLICATED FROM USP_CONSTITUENTGROUP_CUMULATIVEGIVINGSUMMARYGET
if @ISGROUP = 1
begin
declare @DATE datetime, @TYPE nvarchar(22), @AMOUNT money, @PURPOSE nvarchar(512), @SPLITAMOUNT money, @CONSTITUENTNAME nvarchar(154)
--group member largest gift
select top 1
@CONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=R.CONSTITUENTID),
@DATE = R.DATE,
@TYPE = R.TRANSACTIONTYPE,
@AMOUNT = sum(RS.AMOUNT) - sum(coalesce(IWO.AMOUNT,0)),
@PURPOSE = dbo.UFN_REVENUE_DESIGNATIONLIST(R.ID),
@SPLITAMOUNT = RS.AMOUNT
from dbo.REVENUE R
inner join
(select
REVENUESPLIT.ID,
REVENUESPLIT.REVENUEID,
REVENUESPLIT.DESIGNATIONID,
REVENUESPLIT.APPLICATIONCODE,
dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @SELECTEDCURRENCYID) as AMOUNT
from dbo.REVENUESPLIT) RS
on RS.REVENUEID = R.ID
inner join dbo.GROUPMEMBER GM on R.CONSTITUENTID = GM.MEMBERID
left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
left join
(select
INSTALLMENTSPLIT.PLEDGEID,
INSTALLMENTSPLIT.DESIGNATIONID,
sum(dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTINCURRENCY(IWO.ID, @SELECTEDCURRENCYID)) as AMOUNT
from dbo.INSTALLMENTSPLIT
inner join dbo.INSTALLMENTSPLITWRITEOFF IWO on IWO.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
group by INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID) IWO
on IWO.PLEDGEID = RS.REVENUEID and IWO.DESIGNATIONID = RS.DESIGNATIONID
where @INCLUDEMEMBERGIVING = 1
and GM.GROUPID = @CONSTITUENTID
and
(R.TRANSACTIONTYPECODE = 1 or
(R.TRANSACTIONTYPECODE = 5 and RS.APPLICATIONCODE = 0) or -- Donations on orders
(R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3))) --Pledge or Payment (Gift or Recurring gift payment)
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 top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = R.ID
/*next line is #SITEEXTENTION code*/
and dbo.UFN_DESIGNATION_USERHASSITEACCESS(RS.DESIGNATIONID,@CURRENTAPPUSERID) = 1
)
group by
R.ID, R.CONSTITUENTID, R.DATE, R.TRANSACTIONTYPE, RS.AMOUNT, IWO.AMOUNT, R.DATEADDED
order by
(sum(RS.AMOUNT) - sum(coalesce(IWO.AMOUNT,0))) desc, R.DATE desc, R.DATEADDED desc;
-- group largest gift
select top 1
@CONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=R.CONSTITUENTID),
@DATE = R.DATE,
@TYPE = R.TRANSACTIONTYPE,
@AMOUNT = sum(RS.AMOUNT) - sum(coalesce(IWO.AMOUNT,0)),
@PURPOSE = dbo.UFN_REVENUE_DESIGNATIONLIST(R.ID),
@SPLITAMOUNT = RS.AMOUNT
from dbo.REVENUE R
inner join
(select
REVENUESPLIT.ID,
REVENUESPLIT.REVENUEID,
REVENUESPLIT.DESIGNATIONID,
REVENUESPLIT.APPLICATIONCODE,
dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @SELECTEDCURRENCYID) as AMOUNT
from dbo.REVENUESPLIT) RS
on RS.REVENUEID = R.ID
left join
(select
INSTALLMENTSPLIT.PLEDGEID,
INSTALLMENTSPLIT.DESIGNATIONID,
sum(dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTINCURRENCY(IWO.ID, @SELECTEDCURRENCYID)) as AMOUNT
from dbo.INSTALLMENTSPLIT
inner join dbo.INSTALLMENTSPLITWRITEOFF IWO on IWO.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
group by INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID) IWO
on IWO.PLEDGEID = RS.REVENUEID and IWO.DESIGNATIONID = RS.DESIGNATIONID
where R.CONSTITUENTID = @CONSTITUENTID
and
(R.TRANSACTIONTYPECODE = 1 or
(R.TRANSACTIONTYPECODE = 5 and RS.APPLICATIONCODE = 0) or -- Donations on orders
(R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3))) --Pledge or 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 = R.ID
and dbo.UFN_DESIGNATION_USERHASSITEACCESS(RS.DESIGNATIONID,@CURRENTAPPUSERID) = 1
)
group by
R.ID, R.CONSTITUENTID, R.DATE, R.TRANSACTIONTYPE, RS.AMOUNT, IWO.AMOUNT, R.DATEADDED
having (@AMOUNT is null or (sum(RS.AMOUNT) - sum(coalesce(IWO.AMOUNT, 0))) > @AMOUNT)
order by
(sum(RS.AMOUNT) - sum(coalesce(IWO.AMOUNT,0))) desc, R.DATE desc, R.DATEADDED desc;
end
else
begin
-- DUPLICATED FROM USP_CONSTITUENT_CUMULATIVEGIVINGSUMMARYGET
select top 1
@CONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=R.CONSTITUENTID),
@DATE = R.DATE,
@TYPE = R.TRANSACTIONTYPE,
@AMOUNT = sum(RS.AMOUNT) - coalesce(IWO.AMOUNT,0),
@PURPOSE = dbo.UFN_REVENUE_DESIGNATIONLIST(R.ID),
@SPLITAMOUNT = RS.AMOUNT
from
dbo.REVENUE R
inner join
(select
REVENUESPLIT.ID,
REVENUESPLIT.REVENUEID,
REVENUESPLIT.DESIGNATIONID,
REVENUESPLIT.APPLICATIONCODE,
dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @SELECTEDCURRENCYID) as AMOUNT
from dbo.REVENUESPLIT) RS
on RS.REVENUEID = R.ID
left join
(select
INSTALLMENTSPLIT.PLEDGEID,
INSTALLMENTSPLIT.DESIGNATIONID,
sum(dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTINCURRENCY(IWO.ID, @SELECTEDCURRENCYID)) as AMOUNT
from dbo.INSTALLMENTSPLIT
inner join dbo.INSTALLMENTSPLITWRITEOFF IWO on IWO.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
group by INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID) IWO
on IWO.PLEDGEID = RS.REVENUEID and IWO.DESIGNATIONID = RS.DESIGNATIONID
left join
dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = R.ID
left join
dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
where
(R.TRANSACTIONTYPECODE = 1 or --Pledge
(R.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9)) or --Planned gift
(R.TRANSACTIONTYPECODE = 5 and RS.APPLICATIONCODE = 0) or -- Donations on orders
(R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3))) and --Payment (Gift and Recurring Gift)
R.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 = R.ID
and dbo.UFN_DESIGNATION_USERHASSITEACCESS(RS.DESIGNATIONID,@CURRENTAPPUSERID) = 1
)
group by
R.ID, R.CONSTITUENTID, R.DATE, R.TRANSACTIONTYPE, RS.AMOUNT, IWO.AMOUNT, R.DATEADDED
order by
sum(RS.AMOUNT) - coalesce(IWO.AMOUNT, 0) desc, R.DATE desc, R.DATEADDED desc;
end
select @DATE, @TYPE, @AMOUNT, @PURPOSE, @SPLITAMOUNT, @CONSTITUENTNAME
end