New Guest History Report¶

The following is the SearchBuilder code template that is used to build the counts for each row and column. This code has three placeholders (the empty braces {} ). These will be replaced with the appropriate division id and start/end dates for the query. In the current form, with the placeholders, it is in essence a template and is not executable yet.

4
5
6
7
8

query = '''
    AttendTypeAsOf( Prog=101[Life Groups], Div={}, 
        StartDate='{}', EndDate='{}' ) = 60[New Guest]
    AND IncludeDeceased = 1[True]
'''

str = 'a string'
str = "a string"

We use a statement like query2 = query.format(123, '7/1/14', '7/1/15') to convert this query into a string that is executable. This statement will result in a query like the following. You can see how the placeholders {} have been replaced:

AttendTypeAsOf( Prog=101[Life Groups], Div=123,
    StartDate='7/1/14', EndDate='7/1/15' ) = 60[New Guest]
AND IncludeDeceased = 1[True]

The following code builds a list of dates that will represent the starting dates for each column of counts in the results.

 9
10
11
12
13
14
15
16
17
18
19

dates = [
    "7/1/2007",
    "7/1/2008",
    "7/1/2009",
    "7/1/2010",
    "7/1/2011",
    "7/1/2012",
    "7/1/2013",
    "7/1/2014",
    "7/1/2015"
]

The next list is each row representing a division of of Sunday School classes within the Life Groups program.

21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

divisions = [
    "201[Younger Pre-School]",
    "202[Older Pre-School]",
    "239[Special Needs]",
    "203[Children Grades 1-3]",
    "6450[Grades 4-5]",
    "6451[Middle School]",
    "6452[High School]",
    "205[College]",
    "240[Young Adult Singles]",
    "210[Young Couples]",
    "211[Young Marrieds]",
    "212[Adult 1]",
    "213[Adult 2]",
    "214[Adult 3]",
    "215[Senior Adults]",
    "6477[Off Campus Ministries]"
]

This code declares a class called Row that will be used to hold the data in the Rows and Columns in the report. Each Row has a name and a list of columns.

40
41
42
43

class Row:
    def __init__(self, name):
        self.name = name
        self.cols = []

The following code creates and initializes the header row and the footer row for the results. An instance of a Row is constructed with a statement like those on lines 48 and 49. Lines 52-54 initialize the cols list in the Row object. There are as many columns as there are dates so we loop through the list of dates to make the assignments.

45
46
47
48
49
50
51
52
53
54

Data.rows = [] # create a list of rows

# create the header and footer rows
Data.header = Row("Divisions")
Data.footer = Row("Totals")

# now we initialize the header and footer rows
for startdt in dates:
    Data.header.cols.append(startdt)
    Data.footer.cols.append(0)

The next section of code loops through the list of divisions and builds/runs the query needed for each column of each row/division.

58. The name of the division is parsed from this pattern: 123[this is the name] out of the division list using split and strip Python functions.

59. Each division gets it own row object

60. Then we have another date loop for each column in the row.

61. The enddt is computed from the startdt using the DateAddDays function which is available in the model used to execute your script.

62. The specific query needed for this division and date range is built from the template query, and placed into the rowcolquery variable.

63. The ready-to-execute query string is used by the q.QueryCodeCount(rowcolquery) function call which returns the count of people who satisfy the conditions of the query.

64. The len function returns the current length of the list and we use that to get the correct column in the footer row.

65. The footer is updated with the new total for that date’s column.

66. The new column for that row is added to the list of cols.

67. Finally, now that all the dates/columns are done for the Row, the row for this division is added to the list of rows.

57
58
59
60
61
62
63
64
65
66
67

for div in divisions:
    name = div.split('[')[1].strip(']')
    row = Row(name)
    for startdt in dates:
        enddt = model.DateAddDays(startdt, 365)
        rowcolquery = query.format(div, startdt, enddt)
        count = q.QueryCount(rowcolquery)
        i = len(row.cols)
        Data.footer.cols[i] += count
        row.cols.append(count)
    Data.rows.append(row)

This next code is the template which will be used to render the final report. This type of template is called a HandleBars template because of the use of double sets of curly braces {{}} (they look like bicycle handle bars). This template is a multi-line string stored in a variable called template which will be used in the last statement to render the report.

Each use of the handlebars is documented. The HTML used will not be documented.

74. This will replace the handlebars with the value of the header Row object’s name attribute.

75. The {{#each header.cols}} begins a loop iterating over each column in the cols list on the header Row object.

76. The this keyword is the current value in the iteration in the loop, a date string in this case.

77. The {{/each}} marks the end of the loop.

81. This begins the loop over each row in the rows list (which we created as an object on the built-in Data object).

83. The name of the current Row object (row) is displayed here.

84. For this row, we need to comlete the row by iterating over each col in the row’s cols list.

85. We use a special Fmt helper available in the BVCMS implementation of handlebars. The this keyword is the value of the current item in the cols list of the row being worked on. The "N0" is a formating string indicating that we want to display an integer number with no decimal places. The displayed number will be displayed with commas if greater than 999.

86. Indicate the end of the loop through each of the cols.

87. Indicate the end of the loop through each the rows.

92. Line numbers 92-95 work the same as each division row, but for the footer object.

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

<h3>Life Group New Guests, FY report (starting dates)</h3>
<table class="table" style="width: auto">
    <thead>
    <tr>
        <th>{{header.name}}</th>
        {{#each header.cols}}
            <td align="right">{{this}}</td>
        {{/each}}
    </tr>
    </thead>
    <tbody>
    {{#each rows}}
    <tr>
        <th>{{name}}</th>
        {{#each cols}}
            <td align="right">{{Fmt this "N0"}}</td>
        {{/each}}
    </tr>
    {{/each}}
    </tbody>
    <tfoot>
    <tr>
        <th>{{footer.name}}</th>
        {{#each footer.cols}}
            <td align="right">{{Fmt this "N0"}}</td>
        {{/each}}
    </tr>
    </tfoot>
</table>

Finally, the table of results is printed on the page using the RenderTemplate function of BVCMS Python model.

101

print model.RenderTemplate(template)