NewGuestHistory.py

  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
# This script will produce a report showing various divisions of Sunday School classes
# and total numbers of guests for each for a given year starting on a date.

query = '''
    AttendTypeAsOf( Prog=101[Life Groups], Div={}, 
        StartDate='{}', EndDate='{}' ) = 60[New Guest]
    AND IncludeDeceased = 1[True]
'''
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"
]

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]"
]

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

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)

# now build all the rows between header and footer
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)

template = '''
<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>
'''

print model.RenderTemplate(template)