Recurring Giving Widget¶
The Recurring Giving Forecast widget displays a pie chart that shows the percent of the annual budget for the designated fund as projected to be met by recurring giving based on the current giving schedules.
The widget utilizes an HTML file, SQL script and Python script as shown below. Since the giving forecast is the same for all users, Caching should be set to all users.
To adjust the information for your church, you will need to make a few changes in the Python script. Modify line 1 with the ID of the desired fund, line 2 with the Name of the desired fund, and line 3 with the annual budget for the fund you are using.
HTML Code¶
Below is the HTML code for the Recurring Giving Forecast widget. As supplied by TouchPoint, the file name is WidgetGivingForecastHTML.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | <div class="box">
<div class="box-title hidden-xs">
<h5><a href="#">{{WidgetName}}</a></h5>
</div>
<a class="visible-xs-block" id="giving-fc-collapse" data-toggle="collapse" href="#{{WidgetId}}-section" aria-expanded="true" aria-controls="{{WidgetId}}-section">
<div class="box-title">
<h5>
<i class="fa fa-chevron-circle-right"></i> {{WidgetName}}
</h5>
</div>
</a>
<div class="collapse in" id="{{WidgetId}}-section">
<div class="box-content center">
<h4 class="text-center">{{fund}}</h4>
<div class="chart">
</div>
<p class="text-center" style="margin-top:10px;">Based on annual budget of ${{FmtNumber budget}}</p>
</div>
</div>
</div>
<script type="text/javascript">
var data = {{{results}}};
var budget = {{budget}};
var projected = Math.round(data.Combined.MonthlyAmt * 12);
google.charts.load("current", {packages:["corechart"]});
google.charts.setOnLoadCallback(function() {
data = google.visualization.arrayToDataTable([
['Item', 'Dollars'],
['Projected Monthly Recurring', projected],
['Remaining Budget', budget - projected]
]);
var options = {
pieHole: 0.4,
legend: 'none',
chartArea: {
left: 0,
top: 0,
width: '100%',
height: '100%'
}
};
var chart = new google.visualization.PieChart(document.querySelector('#{{WidgetId}}-section .chart'));
chart.draw(data, options);
});
</script>
|
SQL Script¶
The SQL script to use depends on whether your church is still using the legacy giving system or has migrated to the new Giving Pages.
Below is the SQL script for the Recurring Giving Forecast widget if your church uses the legacy giving system. As supplied by TouchPoint, the file name is WidgetGivingForecastSQL.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 | with Members as (
select tp.PeopleId, p.FamilyId
from dbo.TagPerson tp
join dbo.Tag t on t.Id = tp.Id
join dbo.People p on p.PeopleId = tp.PeopleId
where t.Name = @TagMembers
and t.TypeId = 101 -- QueryTag
),
NonMembers as (
select tp.PeopleId, p.FamilyId
from dbo.TagPerson tp
join dbo.Tag t on t.Id = tp.Id
join dbo.People p on p.PeopleId = tp.PeopleId
where t.Name = @TagNonMembers
and t.TypeId = 101 -- QueryTag
),
Both as (
select PeopleId, FamilyId, 'Member' MemberStatus
from Members
union
select PeopleId, FamilyId, 'NonMember'
from NonMembers
),
FamilyCounts as (
select
MemberStatus = 'Member',
FamilyCnt = count(distinct FamilyId)
from Members
union all
select
MemberStatus = 'NonMember',
FamilyCnt = count(distinct FamilyId)
from NonMembers
),
RecurTable as(
select PeopleId
from dbo.RecurringAmounts
where (FundID = @FundId or @FundId is null)
and Amt > 0
),
RecurringCounts as (
select
MemberStatus = 'Member',
RecurringCnt = count(distinct FamilyId)
from Members m
where exists(select null from RecurTable where PeopleId = m.PeopleId)
union all
select
MemberStatus = 'NonMember',
RecurringCnt = count(distinct FamilyId)
from NonMembers n
where exists(select null from RecurTable where PeopleId = n.PeopleId)
),
RecurringAmts as (
select
RecurringType = 'Monthly',
b.MemberStatus,
MonthlyAmt = isnull(sum(Amt),0)
from dbo.RecurringAmounts a
join dbo.ManagedGiving mg on a.peopleid = mg.peopleid
join Both b on b.PeopleId = mg.PeopleId
where (a.fundid = @FundId or @FundId is null)
and SemiEvery='E' and EveryN=1 and Period='M'
group by b.MemberStatus
union all
select
RecurringType = 'Weekly',
b.MemberStatus,
MonthlyAmt = 4.345 * isnull(sum(Amt),0)
from dbo.RecurringAmounts a
join dbo.ManagedGiving mg on a.peopleid = mg.peopleid
join Both b on b.PeopleId = mg.PeopleId
where (a.fundid = @FundId or @FundId is null)
and SemiEvery='E' and EveryN=1 and Period='W'
group by b.MemberStatus
union all
select
RecurringType = 'BiWeeky',
b.MemberStatus,
MonthlyAmt = 2.18 * isnull(sum(Amt),0)
from dbo.RecurringAmounts a
join dbo.ManagedGiving mg on a.peopleid = mg.peopleid
join Both b on b.PeopleId = mg.PeopleId
where (a.fundid = @FundId or @FundId is null)
and SemiEvery='E' and EveryN=2 and Period='W'
group by b.MemberStatus
union all
select
RecurringType = 'SemiMonthly',
b.MemberStatus,
MonthlyAmt = 2 * isnull(sum(Amt),0)
from dbo.RecurringAmounts a
join dbo.ManagedGiving mg on a.peopleid = mg.peopleid
join Both b on b.PeopleId = mg.PeopleId
where (a.fundid = @FundId or @FundId is null)
and SemiEvery='S'
group by b.MemberStatus
),
Totals as (
select
MemberStatus,
MonthlyAmt = sum(MonthlyAmt)
from RecurringAmts
group by MemberStatus
)
select
RowType = isnull(t.MemberStatus, 'Combined'),
FamilyCnt = sum(FamilyCnt),
RecurringCnt = sum(RecurringCnt),
MonthlyAmt = sum(MonthlyAmt)
from Totals t
join FamilyCounts c on c.MemberStatus = t.MemberStatus
join RecurringCounts r on r.MemberStatus = t.MemberStatus
group by rollup(t.MemberStatus)
|
Below is the SQL script for the Recurring Giving Forecast widget if your church has migrated to the new Giving Pages. As supplied by TouchPoint, the file name is WidgetGivingForecastSQL.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 | ;with Members as (
select tp.PeopleId, p.FamilyId
from dbo.TagPerson tp
join dbo.Tag t on t.Id = tp.Id
join dbo.People p on p.PeopleId = tp.PeopleId
where t.Name = @TagMembers
and t.TypeId = 101 -- QueryTag
),
NonMembers as (
select tp.PeopleId, p.FamilyId
from dbo.TagPerson tp
join dbo.Tag t on t.Id = tp.Id
join dbo.People p on p.PeopleId = tp.PeopleId
where t.Name = @TagNonMembers
and t.TypeId = 101 -- QueryTag
),
Both as (
select PeopleId, FamilyId, 'Member' MemberStatus
from Members
union
select PeopleId, FamilyId, 'NonMember'
from NonMembers
),
FamilyCounts as (
select
MemberStatus = 'Member',
FamilyCnt = count(distinct FamilyId)
from Members
union all
select
MemberStatus = 'NonMember',
FamilyCnt = count(distinct FamilyId)
from NonMembers
),
RecurTable as(
select sg.PeopleId
from dbo.ScheduledGift sg
join dbo.ScheduledGiftAmount a on a.ScheduledGiftId = sg.ScheduledGiftId
where (a.FundID = @FundId or @FundId is null)
and a.Amount > 0
),
RecurringCounts as (
select
MemberStatus = 'Member',
RecurringCnt = count(distinct FamilyId)
from Members m
where exists(select null from RecurTable where PeopleId = m.PeopleId)
union all
select
MemberStatus = 'NonMember',
RecurringCnt = count(distinct FamilyId)
from NonMembers n
where exists(select null from RecurTable where PeopleId = n.PeopleId)
),
RecurringAmts as (
select
RecurringType = 'Monthly',
b.MemberStatus,
MonthlyAmt = isnull(sum(a.Amount),0)
from dbo.ScheduledGiftAmount a
join dbo.ScheduledGift sg on sg.ScheduledGiftId = a.ScheduledGiftId
join Both b on b.PeopleId = sg.PeopleId
where (a.fundid = @FundId or @FundId is null)
and sg.ScheduledGiftTypeId = 4
group by b.MemberStatus
union all
select
RecurringType = 'Weekly',
b.MemberStatus,
MonthlyAmt = 4.345 * isnull(sum(a.Amount),0)
from dbo.ScheduledGiftAmount a
join dbo.ScheduledGift sg on sg.ScheduledGiftId = a.ScheduledGiftId
join Both b on b.PeopleId = sg.PeopleId
where (a.fundid = @FundId or @FundId is null)
and sg.ScheduledGiftTypeId = 1
group by b.MemberStatus
union all
select
RecurringType = 'BiWeekly',
b.MemberStatus,
MonthlyAmt = 2.18 * isnull(sum(a.Amount),0)
from dbo.ScheduledGiftAmount a
join dbo.ScheduledGift sg on sg.ScheduledGiftId = a.ScheduledGiftId
join Both b on b.PeopleId = sg.PeopleId
where (a.fundid = @FundId or @FundId is null)
and sg.ScheduledGiftTypeId = 2
group by b.MemberStatus
union all
select
RecurringType = 'SemiMonthly',
b.MemberStatus,
MonthlyAmt = 2 * isnull(sum(a.Amount),0)
from dbo.ScheduledGiftAmount a
join dbo.ScheduledGift sg on sg.ScheduledGiftId = a.ScheduledGiftId
join Both b on b.PeopleId = sg.PeopleId
where (a.fundid = @FundId or @FundId is null)
and sg.ScheduledGiftTypeId = 3
group by b.MemberStatus
union all
select
RecurringType = 'Quarterly',
b.MemberStatus,
MonthlyAmt = isnull(sum(a.Amount),0) / 3
from dbo.ScheduledGiftAmount a
join dbo.ScheduledGift sg on sg.ScheduledGiftId = a.ScheduledGiftId
join Both b on b.PeopleId = sg.PeopleId
where (a.fundid = @FundId or @FundId is null)
and sg.ScheduledGiftTypeId = 5
group by b.MemberStatus
union all
select
RecurringType = 'Annually',
b.MemberStatus,
MonthlyAmt = isnull(sum(a.Amount),0) / 12
from dbo.ScheduledGiftAmount a
join dbo.ScheduledGift sg on sg.ScheduledGiftId = a.ScheduledGiftId
join Both b on b.PeopleId = sg.PeopleId
where (a.fundid = @FundId or @FundId is null)
and sg.ScheduledGiftTypeId = 6
group by b.MemberStatus
),
Totals as (
select
MemberStatus,
MonthlyAmt = sum(MonthlyAmt)
from RecurringAmts
group by MemberStatus
)
select
RowType = isnull(t.MemberStatus, 'Combined'),
FamilyCnt = sum(FamilyCnt),
RecurringCnt = sum(RecurringCnt),
MonthlyAmt = sum(MonthlyAmt)
from Totals t
join FamilyCounts c on c.MemberStatus = t.MemberStatus
join RecurringCounts r on r.MemberStatus = t.MemberStatus
group by rollup(t.MemberStatus)
|
Python Script¶
Below is the Python script for the Recurring Giving Forecast widget. As supplied by TouchPoint, the file name is WidgetGivingForecastPython.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | FundId = 9
FundName = 'Benevolence'
AnnualBudget = 48000 # in dollars
TagMembers = 'GivingForecast-Members'
TagNonMembers = 'GivingForecast-ActiveNonMembers'
def Get():
sql = Data.SQLContent
template = Data.HTMLContent
params = {'FundId': FundId, 'TagMembers': TagMembers, 'TagNonMembers': TagNonMembers}
model.CreateQueryTag(TagMembers,
'FamHasPrimAdultChurchMemb = 1 AND IncludeDeceased = 1')
model.CreateQueryTag(TagNonMembers,
'''
(
RecentFamilyAdultLastAttend( Days=365 ) = 1
OR IsFamilyGiver( Days=365 ) = 1
)
AND FamHasPrimAdultChurchMemb = 0
AND IncludeDeceased = 1
''')
results = q.SqlFirstColumnRowKey(sql, params)
Data.results = model.FormatJson(results)
Data.fund = FundName
Data.budget = AnnualBudget
print model.RenderTemplate(template)
Get()
|
Latest Update | 11/05/2020 |
Provided updated SQL script for church that have migrated to the new Giving Pages.