USP_DATAFORMTEMPLATE_VIEW_COMMUNITYMEMBERTEAMFUNDRAISINGGIFTSUMMARY
The load procedure used by the view dataform template "Community Member Team Fundraising Gift Summary View Form"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | nvarchar(72) | 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. |
| @TEAMID | uniqueidentifier | INOUT | Team ID |
| @TEAMNAME | nvarchar(100) | INOUT | Team |
| @INDIVIDUALGOAL | money | INOUT | Individual goal |
| @TEAMGOAL | money | INOUT | Team goal |
| @APPEALGOAL | money | INOUT | Appeal goal |
| @TOTALRAISED | money | INOUT | Total raised |
| @GOALDIFFERENCE | money | INOUT | Difference |
| @GOALPERCENT | decimal(6, 2) | INOUT | Percent of individual goal |
| @TOTALGIFTCOUNT | int | INOUT | Total donations received |
| @ONLINEGIFTCOUNT | int | INOUT | Online donations received |
| @OFFLINEGIFTCOUNT | int | INOUT | Offline donations received |
| @OTHERGIFTCOUNT | int | INOUT | Other donations received |
| @ONLINEGIFTTOTAL | money | INOUT | Online donations |
| @OFFLINEGIFTTOTAL | money | INOUT | Offline donations |
| @OTHERGIFTTOTAL | money | INOUT | Other donations |
| @GIFTAVERAGE | money | INOUT | Average donation |
| @LARGESTGIFTDONORNAME | nvarchar(154) | INOUT | Largest single donation sponsor |
| @LARGESTGIFTAMOUNT | money | INOUT | Largest single donation |
| @EMAILSENTCOUNT | int | INOUT | Number of emails sent |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_COMMUNITYMEMBERTEAMFUNDRAISINGGIFTSUMMARY
(
@ID nvarchar(72),
@DATALOADED bit = 0 output,
@TEAMID uniqueidentifier = null output,
@TEAMNAME nvarchar(100) = null output,
@INDIVIDUALGOAL money = null output,
@TEAMGOAL money = null output,
@APPEALGOAL money = null output,
@TOTALRAISED money = null output,
@GOALDIFFERENCE money = null output,
@GOALPERCENT decimal(6,2) = null output,
@TOTALGIFTCOUNT int = null output,
@ONLINEGIFTCOUNT int = null output,
@OFFLINEGIFTCOUNT int = null output,
@OTHERGIFTCOUNT int = null output,
@ONLINEGIFTTOTAL money = null output,
@OFFLINEGIFTTOTAL money = null output,
@OTHERGIFTTOTAL money = null output,
@GIFTAVERAGE money = null output,
@LARGESTGIFTDONORNAME nvarchar(154) = null output,
@LARGESTGIFTAMOUNT money = null output,
@EMAILSENTCOUNT int = null output
)
as
set nocount on;
set @DATALOADED = 0;
declare @CONSTITUENTID uniqueidentifier;
declare @APPEALID uniqueidentifier;
set @CONSTITUENTID = left(@ID, 36);
if len(@ID) = 72
set @APPEALID = right(@ID, 36);
select
@DATALOADED = 1,
@TEAMID = TEAMFUNDRAISINGTEAM.ID,
@TEAMNAME = TEAMFUNDRAISINGTEAM.NAME,
@INDIVIDUALGOAL = TEAMFUNDRAISER.GOAL,
@TEAMGOAL = TEAMFUNDRAISINGTEAM.GOAL,
@APPEALGOAL = APPEAL.GOAL,
@TOTALRAISED = coalesce(sum(REVENUESOLICITOR.AMOUNT), 0),
@GOALDIFFERENCE = (coalesce(sum(REVENUESOLICITOR.AMOUNT), 0) - TEAMFUNDRAISER.GOAL),
@GOALPERCENT = (coalesce(sum(REVENUESOLICITOR.AMOUNT), 0) / TEAMFUNDRAISER.GOAL),
@TOTALGIFTCOUNT = count(REVENUESOLICITOR.ID),
@ONLINEGIFTCOUNT =
count
(
case
when REVENUEBBNC.ISTEAMFUNDRAISINGOFFLINEGIFT = 0 and REVENUESOLICITOR.ID is not null then 1
else null
end
),
@OFFLINEGIFTCOUNT =
count
(
case
when REVENUEBBNC.ISTEAMFUNDRAISINGOFFLINEGIFT = 1 and REVENUESOLICITOR.ID is not null then 1
else null
end
),
@OTHERGIFTCOUNT =
count
(
case
when REVENUEBBNC.ID is null and REVENUESOLICITOR.ID is not null then 1
else null
end
),
@ONLINEGIFTTOTAL =
coalesce
(
sum
(
case
when REVENUEBBNC.ISTEAMFUNDRAISINGOFFLINEGIFT = 0 then REVENUESOLICITOR.AMOUNT
else null
end
),
0
),
@OFFLINEGIFTTOTAL =
coalesce
(
sum
(
case
when REVENUEBBNC.ISTEAMFUNDRAISINGOFFLINEGIFT = 1 then REVENUESOLICITOR.AMOUNT
else null
end
),
0
),
@OTHERGIFTTOTAL =
coalesce
(
sum
(
case
when REVENUEBBNC.ID is null then REVENUESOLICITOR.AMOUNT
else null
end
),
0
),
@GIFTAVERAGE = coalesce(avg(REVENUESOLICITOR.AMOUNT), 0)
from
dbo.TEAMFUNDRAISER
inner join dbo.APPEAL on TEAMFUNDRAISER.APPEALID = APPEAL.ID
left join dbo.REVENUE on TEAMFUNDRAISER.APPEALID = REVENUE.APPEALID
left join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
left join dbo.REVENUESOLICITOR on
TEAMFUNDRAISER.CONSTITUENTID = REVENUESOLICITOR.CONSTITUENTID
and REVENUESPLIT.ID = REVENUESOLICITOR.REVENUESPLITID
left join dbo.REVENUEBBNC on REVENUE.ID = REVENUEBBNC.ID
outer apply
(
select top (1)
TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID
from
dbo.TEAMFUNDRAISINGTEAMMEMBER
where
TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID = TEAMFUNDRAISER.ID
order by
TEAMFUNDRAISINGTEAMMEMBER.DATECHANGED desc
) [TEAMFUNDRAISINGTEAMMEMBERFORBBNC]
left join dbo.TEAMFUNDRAISINGTEAM on [TEAMFUNDRAISINGTEAMMEMBERFORBBNC].TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAM.ID
where
TEAMFUNDRAISER.CONSTITUENTID = @CONSTITUENTID
and APPEAL.ID = @APPEALID
and
(
REVENUESPLIT.ID is null --TMV 10/05/2007 CR284921-100507 Return a row for the constituent even if there is no associated revenue
or
( --TMV 10/04/2007 CR284708-100307 Don't double count pledge payments
(REVENUE.TRANSACTIONTYPECODE = 1 and REVENUESPLIT.APPLICATIONCODE = 0) --Pledge
or
(REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE in (0, 3)) --Payment (Gift or Recurring gift payment)
)
)
group by
APPEAL.ID,
APPEAL.NAME,
TEAMFUNDRAISINGTEAM.ID,
TEAMFUNDRAISINGTEAM.NAME,
TEAMFUNDRAISER.GOAL,
TEAMFUNDRAISINGTEAM.GOAL,
APPEAL.GOAL;
select top (1)
@LARGESTGIFTDONORNAME = dbo.UFN_CONSTITUENT_BUILDNAME(REVENUE.CONSTITUENTID),
@LARGESTGIFTAMOUNT = REVENUESOLICITOR.AMOUNT
from
dbo.REVENUESOLICITOR
inner join dbo.REVENUESPLIT on REVENUESOLICITOR.REVENUESPLITID = REVENUESPLIT.ID
inner join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
where
REVENUESOLICITOR.CONSTITUENTID = @CONSTITUENTID
and REVENUE.APPEALID = @APPEALID
and
( -- TMV 10/04/2007 CR284708-100307 Don't double count pledge payments
(REVENUE.TRANSACTIONTYPECODE = 1 and REVENUESPLIT.APPLICATIONCODE = 0) --Pledge
or
(REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE in (0, 3)) --Payment (Gift or Recurring gift payment)
)
order by
REVENUESOLICITOR.AMOUNT desc;
select top (1)
@EMAILSENTCOUNT = count([TFEMAILDISTINCT].[EMAILRECIPIENTMAPID])
from
(
select distinct
[NETCOMMUNITYCLIENTUSER].[ID],
[NETCOMMUNITYCLIENTUSER].[USERUPDATEDATE],
[NETCOMMUNITYTEAMFUNDRAISEREMAIL].[EMAILRECIPIENTMAPID]
from
dbo.NETCOMMUNITYCLIENTUSER
left join dbo.NETCOMMUNITYTEAMFUNDRAISEREMAIL on
[NETCOMMUNITYCLIENTUSER].[ID] = [NETCOMMUNITYTEAMFUNDRAISEREMAIL].[NETCOMMUNITYCLIENTUSERID]
and @APPEALID = [NETCOMMUNITYTEAMFUNDRAISEREMAIL].[APPEALID]
where
[NETCOMMUNITYCLIENTUSER].[CONSTITUENTID] = @CONSTITUENTID
and [NETCOMMUNITYCLIENTUSER].[ACTIVE] = 1
and [NETCOMMUNITYCLIENTUSER].[DELETED] = 0
) [TFEMAILDISTINCT]
group by
[TFEMAILDISTINCT].[ID],
[TFEMAILDISTINCT].[USERUPDATEDATE]
order by
[TFEMAILDISTINCT].[USERUPDATEDATE];
return 0;