Giving Type Breakdown Widget

The Giving Type Breakdown widget displays a bar chart showing the past eight weeks of different giving types. The types included are those you specify on the basis of bundle types. Giving types are indicated by color according to the legend at the bottom of the chart. The date is also shown at the bottom, along the horizontal axis, under each bar of the chart.

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 change line 2 of the Python script with the fund ID desired.

Video

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


HTML Code

Below is the HTML code for the Giving Type Breakdown widget. As supplied by TouchPoint, the file name is WidgetGivingTypeBreakdownHTML.

  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
<div class="box">
    <div class="box-title hidden-xs">
        <h5>{{WidgetName}}</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">
            <div class="chart"></div>
        </div>
    </div>
</div>
<script type="text/javascript">
    var myData = [];
    var weekCount = [];
    var weeksDate = [];
    var myResultsArrayofArrays = [];
    var {{WidgetId}} = function() {
        myData = {{{results}}};
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Week');
        {{{addcolumns}}}

        var i = 0;
        $.each(myData,function(index, value){
            var myResultsArray = [];
            for(let j = 0; j < value.results.length; j++){
                myResultsArray[j] = value.results[j].amount;
                if(i == 0){
                    weekCount[j] = value.results[j].w.toString();
                }
            }
            myResultsArrayofArrays[i] = myResultsArray;
            i++;
        });

        var thisSunday = new Date();
        for(let i =0; i < 7; i++){
            if(thisSunday.getDay() != 0){
                thisSunday.setDate(thisSunday.getDate() + 1);
            } else{
                break;
            }
        }

        thisSunday.setDate(thisSunday.getDate() + 7);
        for(let i = 7; i >= 0; i--){
            thisSunday.setDate(thisSunday.getDate() - 7);
            let day = thisSunday.getDate().toString();
            let month = thisSunday.getMonth() + 1;
            month = month.toString();
            weekCount[i] = month + '/' + day;
        }

        switch(myResultsArrayofArrays.length)
        {
            case 1:
                for(let x = 0; x < 8; x++){
                    data.addRow([weekCount[x], myResultsArrayofArrays[0][x]]);
                }
                break;
            case 2:
                for(let x = 0; x < 8; x++){
                    data.addRow([weekCount[x], myResultsArrayofArrays[0][x], myResultsArrayofArrays[1][x]]);
                }
                break;
            case 3:
                for(let x = 0; x < 8; x++){
                    data.addRow([weekCount[x], myResultsArrayofArrays[0][x], myResultsArrayofArrays[1][x], myResultsArrayofArrays[2][x] ]);
                }
                break;
            case 4:
                for(let x = 0; x < 8; x++){
                    data.addRow([weekCount[x], myResultsArrayofArrays[0][x], myResultsArrayofArrays[1][x], myResultsArrayofArrays[2][x], myResultsArrayofArrays[3][x]]);
                }
                break;
            case 5:
                for(let x = 0; x < 8; x++){
                    data.addRow([weekCount[x], myResultsArrayofArrays[0][x], myResultsArrayofArrays[1][x], myResultsArrayofArrays[2][x], myResultsArrayofArrays[3][x], myResultsArrayofArrays[4][x]]);
                }
                break;
            case 6:
                for(let x = 0; x < 8; x++){
                    data.addRow([weekCount[x], myResultsArrayofArrays[0][x], myResultsArrayofArrays[1][x], myResultsArrayofArrays[2][x], myResultsArrayofArrays[3][x], myResultsArrayofArrays[4][x], myResultsArrayofArrays[5][x]]);
                }
                break;
            default:
                break;
        }

        var formatter = new google.visualization.NumberFormat(
            {negativeColor: 'red', negativeParens: true, pattern: '$###,###'});
        formatter.format(data, 1);
        formatter.format(data, 2);
        formatter.format(data, 3);

        var options = {
            colors: ['#CC0300', '#5CA12B', '#345CAD', '#F9B710', '#9E4EBC', '#74746D'],
            seriesType: 'bars',
            series: {5: {type: 'line'}},
            legend: {
                position: "bottom",
                textStyle: {fontSize: 8},
                alignment: 'center'
            },
            isStacked: true,
            vAxis: {format: 'currency'},
            hAxis: {
                textStyle: {
                    fontSize: 10
                }
            },
        };
        var chart = new google.visualization.ComboChart(document.querySelector('#{{WidgetId}}-section .chart'));
        chart.draw(data, options);
    }
    // load and register the chart
    google.charts.load("current", {packages:["corechart"]});
    google.charts.setOnLoadCallback({{WidgetId}});
    WidgetCharts.{{WidgetId}} = {{WidgetId}};
</script>

SQL Script

Below is the SQL script for the Giving Type Breakdown widget. As supplied by TouchPoint, the file name is WidgetGivingTypeBreakdownSQL.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
set datefirst 1;
with weeks as (
select DATEPART(week, getdate()) w UNION ALL
select DATEPART(week, getdate() - 7) w UNION ALL
select DATEPART(week, getdate() - 14) w UNION ALL
select DATEPART(week, getdate() - 21) w UNION ALL
select DATEPART(week, getdate() - 28) w UNION ALL
select DATEPART(week, getdate() - 35) w UNION ALL
select DATEPART(week, getdate() - 42) w UNION ALL
select DATEPART(week, getdate() - 49) w
),
tags as (
select weeks.w, SUM(coalesce(c.ContributionAmount,0)) as amount, TagName from weeks
cross join ContributionTag ct
left join Contribution c on datepart(week, c.ContributionDate) = weeks.w
                            and c.ContributionId = ct.ContributionId
                            and c.ContributionStatusId = 0
                            and c.ContributionTypeId NOT IN (6,7,8)
where TagName = @Tag
group by weeks.w, TagName
)
select * from tags
order by (case when datepart(week, getdate()) < 10 then (case when w < 10 then w + 53 else w end) else w end)

Python Script

Below is the Python script for the Giving Type Breakdown widget. As supplied by TouchPoint, the file name is WidgetGivingTypeBreakdownPython.

 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
# You can change FundIds to select the Fund you wish to view data for
FundIds = '9'

from System import DateTime

# Initialize global data
MaxDate = DateTime.Today
GivingTypesData = model.DynamicData()
Priority = 0
Days = 56
selectedTags = []

Base = model.DynamicData()
Base.MinDate = MaxDate.AddDays(-Days).ToString("d")
Base.NonTaxDed = -1  # both tax and non tax
Base.MaxDate = MaxDate.ToString("d")
Base.FundIds = FundIds


def NextPriority():
    global Priority
    Priority += 1
    return Priority


def GetResults(tagName):
    results = q.QuerySql(Data.SQLContent, {'Tag': tagName})
    return results


def CreateTag(fundSet, typeCategory, searchParameters):
    TagName = 'Gt{}-{}'.format(fundSet, typeCategory)
    NamePriority = '{}-{}'.format(TagName, Priority)
    dd = model.DynamicData()
    dd.params = searchParameters
    dd.name = typeCategory
    selectedTags.append(typeCategory)
    dd.link = '/ContributionsJsonSearch/{}/{}'.format('GivingTypesData', NamePriority)
    tag = model.CreateContributionTag(TagName, searchParameters)
    dd.results = GetResults(TagName)
    GivingTypesData.AddValue(NamePriority, dd)


def CreateTags(fundSet):
    RowType = 'Online'  # Name this set of data
    SearchParameters = model.DynamicData(Base)  # don't change
    SearchParameters.Priority = NextPriority()  # don't change
    SearchParameters.BundleTypes = ['Online']  # name of type you want to retrieve data for
    CreateTag(fundSet, RowType, SearchParameters)  # don't change

    RowType = 'Cash'  # Name this set of data
    SearchParameters = model.DynamicData(Base)  # don't change
    SearchParameters.Priority = NextPriority()  # don't change
    SearchParameters.BundleTypes = ['Loose Checks and Cash']  # name of type you want to retrieve data for
    CreateTag(fundSet, RowType, SearchParameters)  # don't change

    RowType = 'Envelopes'  # Name this set of data
    SearchParameters = model.DynamicData(Base)  # don't change
    SearchParameters.Priority = NextPriority()  # don't change
    SearchParameters.BundleTypes = ['Preprinted Envelopes']  # name of type you want to retrieve data for
    CreateTag(fundSet, RowType, SearchParameters)  # don't change


Priority = 100
CreateTags('MainFund')
Data.days = Days
Data.results = model.FormatJson(GivingTypesData)
addcolumn = "data.addColumn('number', '{}');"
alist = [addcolumn.format(x) for x in selectedTags]
Data.addcolumns = '\n'.join(alist)
print model.RenderTemplate(Data.HTMLContent)


Latest Update 08/12/2020

Added video.