GivingTypes Python Script

See also

View the entire code file

This report displays three tables for three different date ranges: Year to Date, Last 30 Days, and Last 365 Days.

The rows in each table show different Types of Contributions such as: Online, Recurring, Mobile, Offering plate, Mission Trips, Bank Drafts, Stock, and Non-Tax Deductible. The rows add up to a Total row at the bottom of each table.

There are three columns in each table for three different sets of Funds: All Funds, Main Fund, and Other.

Before this report can run, the GivingTypesData Python Script must run to collect and cache the data. Once done, the report can be run multiple times by multiple users and this pre-collected data allows each run to be fast.

Explanation of the Code

1
2
3
#Roles=Finance

from System import DateTime
Line 1 limits only those users with the role Finance to run this script.
Line 3 allows the code to use .NET DateTime functionality

Initialize

 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
# Initialize global data
IsAdmin = model.UserIsInRole("Admin")
Shell = model.TextContent('GivingTypesHtmlShell')
TablePattern = model.TextContent('GivingTypesHtmlTable')
Sql = model.SqlContent('GivingTypesData')
LookupData = q.SqlNameValues(Sql, 'FundsTypeRange', 'Total')
ValidateUrl = '/RunScript/GivingTypesValidate'
RowOrder = [
    'Online',
    'Mobile',
    'Recurring',
    'SundayMainWorship',
    'MissionTrip',
    'BankDrafts',
    'MailedChecksAndCash',
    'SecureGive',
    'Stock',
    'Hispanic',
    'Students',
    'NonTaxDeductible',
    'Unknown'
]
DateRanges = [
    ['Ytd', 'Year To Date'], 
    ['30', 'Last 30 Days'], 
    ['365', 'Last 365 Days']
]

This code initializes the following global variables.

IsAdmin

Allows the final report to have two versions: one for Admins who have links to special validation reports and the other for non-Admins without the links.

See also

model.UserIsInRole().

Shell

The main HTML template used to wrap the report. This fetches the text content from the file.

See also

model.TextContent().

TablePattern

The main HTML table template for the three date range tables in the report.

Sql

This SQL script generates the data used to populate the figures in the rows and columns of the report.

LookupData

A DynamicData object which holds the name/value pairs of the Sql above.

See also

q.SqlNameValues().

Base

A DynamicData object which will hold the common SearchParameters for every Contribution Type, including the date range.

RowOrder

An array of names which defines the order in which the various RowTypes (contribution categories) appear in the report tables.

DateRanges

An array of arrays which define the order of the date ranges, and their labels, uses for each table.

Utility Functions

31
32
33
34
35
36
37
38
def Percent(number, total):
    if number == None:
        return total * 0
    return number / total * 100

def FormatNumber(value, ndecimals):
    fmt = "N{}".format(ndecimals)
    return "" if value== None else value.ToString(fmt)

There are two utility functions defined: the first is Percent to compute a percentage of total and the second is FormatNumber which inserts commas and a number of decimal places of precision for display in the report.

Both functions take measures take care to prevent errors avoiding divide by zero and returning an empty string when the result is zero.

BuildRow

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
def BuildRow(rowType, dateRange):
    amt = LookupData['GtAllFunds-' + rowType + dateRange]
    total = LookupData['GtAllFunds-Total' + dateRange]
    pct = FormatNumber(Percent(amt, total), 1)
    amtallfunds = FormatNumber(amt, 0)
    amtmainfund = FormatNumber(LookupData['GtMainFund-' + rowType + dateRange], 0)
    amtother = FormatNumber(LookupData['GtOther-' + rowType + dateRange], 0)
    label = model.SpaceCamelCase(rowType)
    if rowType == 'Unknown' and amtallfunds == '' and amtmainfund == '' and amtother == '':
        return ''
    row = '''
        <tr>
            <td>{title}</td><td>{all}</td><td>{pct}</td><td>{main}</td><td>{other}</td>
        </tr>
    '''
    if IsAdmin:
        row = '''
        <tr>
            <td>{title}</td>
            <td><a href="{url}?row={type}&col=1&dtrange={range}" target="detail">{all}</a></td>
            <td>{pct}</td>
            <td><a href="{url}?row={type}&col=2&dtrange={range}" target="detail">{main}</a></td>
            <td><a href="{url}?row={type}&col=3&dtrange={range}" target="detail">{other}</a></td>
        </tr>
        '''
    return row.format(url=ValidateUrl, title=label, type=rowType, range=dateRange, 
        all=amtallfunds, pct=pct, main=amtmainfund, other=amtother)

The BuildRow function constructs an individual row for the report using the rowType and dateRange values passed into the function. The function uses the LookupData dictionary to find the appropriate numbers for the row. On Line 47, the rowType name is converted to a suitable label for the row using a builtin function to insert spaces for the report’s display.

See also

model.SpaceCamelCase().

Lines 48-49 avoids displaying rows with no values to display.

Lines 50-54 create a template for the HTML table row using named replacement sections. Note that a multi-line string is used, made possible with the triple quotes '''.

Lines 55-64 creates an expanded version of the row tempate for Admins with links to validation reports.

Line 65 delivers the populated row using the format function to do the named replacements. Note that the Python format function works for both the expanded row or for the simpler row, even though the simpler row has fewer replacements. This is possible because unused named arguments are ignored.

BuildTotalRow

68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
def BuildTotalRow(dateRange):
    amtallfunds = FormatNumber(LookupData['GtAllFunds-Total' + dateRange], 0)
    amtmainfund = FormatNumber(LookupData['GtMainFund-Total'+ dateRange], 0)
    amtother = FormatNumber(LookupData['GtOther-Total' + dateRange], 0)
    row = '<tfoot><tr><th>Total</th><th>{all}</th><th></th><th>{main}</th><th>{other}</th></tr></tfoot>'
    if IsAdmin:
        row = '''
        <tfoot><tr>
            <td>Total</th>
            <th><a href="{url}?fundset=1&rowtype=%&daterange={range}" target="detail">{all}</a></th>
            <th></th>
            <th><a href="{url}?fundset=2&&rowtype=%&daterange={range}" target="detail">{main}</a></th>
            <th><a href="{url}?fundset=3&&rowtype=%&daterange={range}" target="detail">{other}</a></th>
        </tr></tfoot>
        '''
    return row.format(url=ValidateUrl, range=dateRange, all=amtallfunds, main=amtmainfund, other=amtother)

The BuildTotalRow function works in a similar way to the BuildRow function but for the final Total row for the table for a particular dateRange.

BuildTableRows

85
86
87
88
89
90
91
def BuildTableRows(dateRange):
    rows = '<tbody>\n'
    for rowType in RowOrder:
        rows += BuildRow(rowType, dateRange)
    rows += '</tbody>\n'
    rows += BuildTotalRow(dateRange)
    return rows

The BuildTableRows function constructs all of the rows for a table for a particular dateRange. The function loops rowType through the RowOrder array constructing each row to append to the rows variable. The function returns all rows ready for insertion to the table for a particular dateRange.

BuildTables

93
94
95
96
97
98
def BuildTables():
    tables = ''
    for dateRange in DateRanges:
        rows = BuildTableRows(dateRange[0])
        tables += TablePattern.format(dateRange[1], rows)
    return tables

The BuildTables function loops through each dateRange in the DateRanges array, fetching all the rows with BuildTableRows using the DateRange name (dateRange[0]). Then, using the TablePattern, constructs the final table using the label (dateRange[1]) and the rows previously created. The function appends all the tables together and returns that result.