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.

Video

Below is a short video demonstrating some of the features of this widget.


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>&nbsp;&nbsp;{{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

Below is the SQL script for the Recurring Giving Forecast widget. 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)

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
34
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 08/12/2020

Added video.

© 2020 TouchPoint Software, LLC.  All rights reserved.