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

Video

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


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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
<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 today = new Date();
        var currentWeekOfYear = getWeekNumber(today);
        var fiscalStart = new Date();
        fiscalStart.setFullYear({{startyear}},{{startmonth}} - 1, {{startday}});
        var fiscalWeekOfYear = getWeekNumber(fiscalStart);
        var endYear = new Date();
        endYear.setFullYear(fiscalWeekOfYear[0], 11, 31);
        var endWeekOfYear = getWeekNumber(endYear);
        var data = {{{results}}};
        var budget = {{{annualbudget}}};
        var givingTotalYTD = 0;

        if (currentWeekOfYear[0] === fiscalWeekOfYear[0]) {
            weekMultiplier = currentWeekOfYear[1] - fiscalWeekOfYear[1];
            }
        else {
            weekMultiplier = currentWeekOfYear[1] + (endWeekOfYear[1] - fiscalWeekOfYear[1]);
            }

        //When in first week of fiscal year, calculate weekMultiplier as a fraction based on number of days
        if (weekMultiplier == 0) {
            if (today.getDay() >= fiscalStart.getDay()) {
                weekMultiplier = (today.getDay()+1-fiscalStart.getDay())/7;
            } else {
                weekMultiplier = (today.getDay()+8-fiscalStart.getDay())/7;
            }
        }

        var budgetYTD = ((budget/52)*weekMultiplier);
        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 = givingTotalYTD - budgetYTD;
        var weeklyAverageYTD = (givingTotalYTD/weekMultiplier)
        var myCurrencyFormatter = new Intl.NumberFormat('en-US', {
          style: 'currency',
          currency: 'USD',
        });

        let p0 = document.createElement("p");
        p0.innerHTML = "Year Starting: " + {{startmonth}} + "/" + {{startday}} + "/" + {{startyear}};
        p0.className = "text-center";
        p0.style = "margin:0px;text-align:right;font-size:13px;font-weight:bold;";
        document.querySelector('#{{WidgetId}}-section .chartSummary').appendChild(p0);

        let p1 = document.createElement("p");
        p1.innerHTML = "Total Given YTD: " + myCurrencyFormatter.format(givingTotalYTD);
        p1.className = "text-center";
        p1.style = "margin:0px;text-align:right;font-size:13px;";
        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;text-align:right;font-size:13px;";
        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:0px;text-align:right;font-size:13px;";
        } else{
            p3.style = "margin:0px;text-align:right;font-size:13px;";
        }
        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;text-align:right;font-size:13px;";
        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;text-align:right;font-size:13px;";
        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);
    }
    function getWeekNumber(d) {
        // Copy date so don't modify original
        d = new Date(Date.UTC(d.getFullYear(), d.getMonth(), d.getDate()));
        // Set to nearest Thursday: current date + 4 - current day number
        // Make Sunday's day number 7
        d.setUTCDate(d.getUTCDate() + 4 - (d.getUTCDay()||7));
        // Get first day of year
        var yearStart = new Date(Date.UTC(d.getUTCFullYear(),0,1));
        // Calculate full weeks to nearest Thursday
        var weekNo = Math.ceil(( ( (d - yearStart) / 86400000) + 1)/7);
        // Return array of year and week number
        return [d.getUTCFullYear(), weekNo];
    }
    // 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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
# Change FundId to select the fund you wish to use for this widget. Don't delete the quotes.
FundId = '1'
# Change AnnualBudget to the target budget for this fund for this time frame.
AnnualBudget = 240000  # in dollars
# Change these start date variables to the beginning of time frame you wish to keep track of.
# date format = month/day
startMonth = "1"
startDay = "1"

from datetime import datetime
from datetime import date

today = date.today()
currentyear = today.year
fiscalstart = date(currentyear,int(startMonth),int(startDay))

if today < fiscalstart:
    startYear = str(currentyear - 1)
else:
    startYear = str(currentyear)


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
    StartDate = startMonth + "/" + startDay + "/" + startYear
    Data.results = GetData(FundId, StartDate)
    Data.dayofyear = datetime.now().timetuple().tm_yday
    Data.startmonth = startMonth
    Data.startday = startDay
    Data.startyear = startYear
    last7days = 0
    print model.RenderTemplate(template)


Get()


Latest Update

08/12/2020

Added video.