Giving to Budget Comparison Widget

The Giving to Budget Comparison widget displays a circular gauge that gives a visual indication of how nearly your church is on track for the budget of a specified fund. Below the gauge is displayed:

  • the total amount given to the fund Year to Date
  • the budget amount Year to Date
  • how much over or under budget has been given
  • the weekly average given Year to Date
  • the amount given in the last seven days

The widget utilizes an HTML file, SQL script and Python script as shown below. Since the giving data is the same for all users, Caching should be set to all users.

To customize the widget, you will need to make a few changes in the Python script.

  • On line 2 of the Python script, change the fund ID if necessary to the desired
  • On line 4, change the annual budget amount for the specified fund
  • On line 6, change the start date to the beginning of the current calendar year or, if different, the beginning of your fiscal year

HTML Code

Below is the HTML code for the Giving to Budget Comparison widget. As supplied by TouchPoint, the file name is WidgetGivingToBudgetComparisonHTML.

  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
<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" style="text-align: -webkit-center;">
            <div class="chart" style="display: inline-block;width:200px;"></div>
            <div class="chartSummary" style="display: inline-block;width:200px;vertical-align: top;margin-top: 50px;"></div>
        </div>
    </div>
</div>
<script type="text/javascript">
    var {{WidgetId}} = function() {
        document.querySelector('#{{WidgetId}}-section .chartSummary').innerHTML = "";
        var data = {{{results}}};
        var budget = {{{annualbudget}}};
        var weekOfYear = Math.round({{{dayofyear}}} / 7);
        var givingTotalYTD = 0;
        var budgetYTD = ((budget/52)*weekOfYear);
        var sevenDayaAgo = new Date();
        sevenDayaAgo.setDate(sevenDayaAgo.getDate() - 7);
        var givingLastSevenDays = 0;

        for(let i = 0; i < data.length; i++){
            givingTotalYTD += data[i][0];
            var d = new Date(data[i][1]);
            if(d > sevenDayaAgo){
                givingLastSevenDays += data[i][0];
            }
        }
        var overUnderYTD = budgetYTD - givingTotalYTD;
        var weeklyAverageYTD = (givingTotalYTD/weekOfYear)
        var myCurrencyFormatter = new Intl.NumberFormat('en-US', {
          style: 'currency',
          currency: 'USD',
        });

        let p1 = document.createElement("p");
        p1.innerHTML = "Total Given YTD: " + myCurrencyFormatter.format(givingTotalYTD);
        p1.className = "text-center";
        p1.style = "margin:0px;";
        document.querySelector('#{{WidgetId}}-section .chartSummary').appendChild(p1);

        let p2 = document.createElement("p");
        p2.innerHTML = "Budget YTD: " + myCurrencyFormatter.format(budgetYTD);
        p2.className = "text-center";
        p2.style = "margin:0px;";
        document.querySelector('#{{WidgetId}}-section .chartSummary').appendChild(p2);

        let p3 = document.createElement("p");
        p3.innerHTML = "Over/Under YTD: " + myCurrencyFormatter.format(overUnderYTD);
        if(overUnderYTD < 0){
            p3.style = "color:red;margin-top:10px;";
        } else{
            p3.style = "margin:0px;";
        }
        p3.className = "text-center";
        document.querySelector('#{{WidgetId}}-section .chartSummary').appendChild(p3);

        let p4 = document.createElement("p");
        p4.innerHTML = "Weekly Average YTD: " + myCurrencyFormatter.format(weeklyAverageYTD);
        p4.className = "text-center";
        p4.style = "margin:0px;";
        document.querySelector('#{{WidgetId}}-section .chartSummary').appendChild(p4);

        let p5 = document.createElement("p");
        p5.innerHTML = "Last 7 Days: " + myCurrencyFormatter.format(givingLastSevenDays);
        p5.className = "text-center";
        p5.style = "margin:0px;";
        document.querySelector('#{{WidgetId}}-section .chartSummary').appendChild(p5);

        var YTDgiving = Math.round(givingTotalYTD);
        var YTDbudget = Math.round(budgetYTD);


        var data = google.visualization.arrayToDataTable([
          ['Label', 'Value'],
          ['', 0]
        ]);
        var googleNumFormatter = new google.visualization.NumberFormat({
            prefix: '$',
            fractionDigits: 1
        });
        googleNumFormatter.format(data, 1);

        var oneThird = (budgetYTD * 0.33)/1000;
        var oneThirdString = '$' + oneThird.toFixed(1);
        var twoThird = (budgetYTD * 0.66)/1000;
        var twoThirdString = '$' + twoThird.toFixed(1);
        var threeThird = budgetYTD/1000;
        var threeThirdString = '$' + threeThird.toFixed(1);
        var options = {
          height: 200, max: (Math.round(YTDbudget*1.33)/1000),
          yellowFrom: (Math.round(YTDbudget*0.665)/1000), yellowTo: (Math.round(YTDbudget*0.85)/1000), yellowColor: '#F5D762',
          greenFrom: (Math.round(YTDbudget*0.85)/1000), greenTo: (Math.round(YTDbudget)/1000), greenColor: '#37968D',
          redFrom: (Math.round(YTDbudget)/1000), redTo: (Math.round(YTDbudget*1.33)/1000), redColor: '#757375',
          majorTicks: ['',oneThirdString,twoThirdString,threeThirdString,''],
          animation:{
            duration: 2000,
            easing: 'out',
          }
        };

        var chart = new google.visualization.Gauge(document.querySelector('#{{WidgetId}}-section .chart'));
        chart.draw(data, options);

        givingTotalYTD = givingTotalYTD/1000;
        var data2 = google.visualization.arrayToDataTable([
          ['Label', 'Value'],
          ['', givingTotalYTD]
        ]);
        googleNumFormatter.format(data2, 1);

        setInterval(function() {
          chart.draw(data2, options);
        }, 2000);

        let p6 = document.createElement("p");
        p6.innerHTML = "(Dollars In Thousands)";
        p6.className = "text-center";
        p6.style = "margin:0px;";
        document.querySelector('#{{WidgetId}}-section .chart').appendChild(p6);
    }
    // load and register the chart
    google.charts.load("current", {packages:["gauge"]});
    google.charts.setOnLoadCallback({{WidgetId}});
    WidgetCharts.{{WidgetId}} = {{WidgetId}};
</script>

SQL Script

Below is the SQL script for the Giving to Budget Comparison widget. As supplied by TouchPoint, the file name is WidgetGivingToBudgetComparisonSQL.

1
2
3
4
5
6
7
8
select
    ContributionAmount,
    ContributionDate
from Contribution
where FundId = @FundId
and ContributionDate >= '@StartDate'
and ContributionStatusId = 0
and ContributionTypeId NOT IN (6,7,8);

Python Script

Below is the Python script for the Giving to Budget Comparison widget. As supplied by TouchPoint, the file name is WidgetGivingToBudgetComparisonPython.

 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
# Change FundId to select the fund you wish to use for this widget. Don't delete the quotes.
FundId = '9'
# Change AnnualBudget to the target budget for this fund for this time frame.
AnnualBudget = 240000  # in dollars
# Change StartDate to the beginning of time frame you wish to keep track of.
StartDate = '01/01/2020'

from datetime import datetime


def GetData(FundId, StartDate):
    sql = Data.SQLContent
    sql = sql.replace('@FundId', FundId)
    sql = sql.replace('@StartDate', StartDate)
    return q.QuerySqlJsonArray(sql)


def Get():
    sql = Data.SQLContent
    template = Data.HTMLContent
    Data.annualbudget = AnnualBudget
    Data.results = GetData(FundId, StartDate)
    Data.dayofyear = datetime.now().timetuple().tm_yday
    last7days = 0
    print
    model.RenderTemplate(template)


Get()


Latest Update 05/28/2020

Added this article.

© 2020 TouchPoint Software, LLC.  All rights reserved.