Batch Report

Currently there are three standard options for the single batch report and three specialized options. Below is a summary of each:

Note

When configured, the report can be run by clicking the report icon for the desired batch. See image below.

Batch Report

This report provides the following information for each batch.

Header

  • Church Name

  • Batch ID

  • Batch Type

  • Collected date (Contribution Date for batch)

  • Created date

  • Printed date (current date)

  • Deposited date

  • totals for Cash and for Checks

Details

  • Contributor Name

  • Type (Cash or Check)

  • Amount

  • Fund

  • Check Number

In the details, cash donations are listed first with a sub-total, followed by check donations with a sub-total, then a grand total for the batch.

There is also a signature line, so you can print this report and have your counters sign off on it.

https://i.tpsdb.com/2023-06-22_16-05-02.png

Report Icon

https://i.tpsdb.com/./2018-02-05_08-26-35.png

Sample Report

Create the Batch Report

Step 1

Go to Administration > Setup > Special Content > Python Scripts.

Step 2

Click the green + New Python Script File, enter the file name BatchReport and click Submit.

Step 3

Copy all the Python code below and paste it into the new file.

Click Save Script.

Step 4

  • Find the Setting Custom Batch Report at Administration > Setup > Settings on the Finance tab and in the Batches category

  • Paste this as the value - /PyScript/BatchReport?p1={bundleid}

Note

Each database can have only one Batch Report. So, the Setting is just named CustomBatchReport and the Value determines which is used.

Generate the Batch Report

To generate the report, click the report icon for the desired batch.

Use the print feature of your browser to print the report.

Python Code

  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
model.Title = 'Batch Report'

sqlheader = '''
   SELECT
       BundleHeaderId,
       BundleHeaderType = bt.[Description],
       CreatedDate,
       ContributionDate,
       DepositDate,
       CheckTotal = ISNULL((SELECT SUM(c.ContributionAmount) FROM dbo.Contribution c
                                                       JOIN dbo.BundleDetail bd ON bd.ContributionId = c.ContributionId
                                                       WHERE bd.BundleHeaderId = @P1
                                                       AND ISNULL(c.CheckNo, '') <> ''), 0),
       CashTotal = ISNULL((SELECT SUM(c.ContributionAmount) FROM dbo.Contribution c
                                                       JOIN dbo.BundleDetail bd ON bd.ContributionId = c.ContributionId
                                                       WHERE bd.BundleHeaderId = @P1
                                                       AND ISNULL(c.CheckNo, '') = ''), 0),
       Total = ISNULL((SELECT SUM(c.ContributionAmount) FROM dbo.Contribution c
                                                       JOIN dbo.BundleDetail bd ON bd.ContributionId = c.ContributionId
                                                       WHERE bd.BundleHeaderId = @P1), 0),
       Church = (SELECT Setting FROM dbo.Setting WHERE Id = 'NameOfChurch'),
       CurrentTime = GETDATE()
   FROM dbo.BundleHeader bh
   JOIN lookup.BundleHeaderTypes bt ON bt.Id = bh.BundleHeaderTypeId
   WHERE BundleHeaderId = @P1
'''

sqlgifts = '''
    ;WITH gifts AS (
        SELECT p.Name
            , Type = IIF(ISNULL(NULLIF(c.CheckNo, ''), 'cash') = 'cash', 'Cash', 'Check')
            , Amt = c.ContributionAmount
            , Fund = CASE c.ContributionTypeId WHEN 99 THEN ISNULL(accnt.Description, '') ELSE f.FundDescription END
            , Bold = ''
            , c.CheckNo
        FROM dbo.Contribution c
        JOIN dbo.People p ON p.PeopleId = c.PeopleId
        LEFT JOIN lookup.AccountCode accnt ON accnt.Id = c.AccountCodeId
        JOIN dbo.ContributionFund f ON f.FundId = c.FundId
        JOIN dbo.BundleDetail bd ON bd.ContributionId = c.ContributionId
        WHERE bd.BundleHeaderId = @P1
    )
    SELECT * FROM gifts
    WHERE gifts.Type = 'Cash'
    UNION ALL -----------------------
    SELECT 'SUB-TOTAL', 'CASH', ISNULL(SUM(Amt), 0), NULL, 'font-weight:bold', NULL FROM gifts
    WHERE gifts.Type = 'Cash'
    UNION ALL -----------------------
    SELECT * FROM gifts
    WHERE gifts.Type = 'Check'
    UNION ALL -----------------------
    SELECT 'SUB-TOTAL', 'CHECK', ISNULL(SUM(Amt), 0), NULL, 'font-weight:bold', NULL FROM gifts
    WHERE gifts.Type = 'Check'
    UNION ALL -----------------------
    SELECT 'TOTAL', 'ALL', ISNULL(SUM(Amt), 0), NULL, 'font-weight:bold', NULL FROM gifts
'''

template = '''
<style>
    table.table.no-border td,
    table.table.no-border th {
        border-top: none;
    }
</style>
<table class="table notwide centered">
    <thead>
        {{#with header}}
        <tr>
            <td colspan="5">
                <div class="text-center">
                    <h2>{{Church}}</h2>
                    <h3>Batch ID #: {{BundleHeaderId}}</h3>
                    <h4>BatchType: {{BundleHeaderType}}</h4>
                </div
                <br>
                <table width="100%" border=1>
                    <tr>
                        <td>
                            <table class="table notwide no-border" style="margin-bottom:0">
                                <tr>
                                    <td>Collected</td>
                                    <td>{{Fmt ContributionDate 'd'}}</td>
                                    <td></td>
                                </tr>
                                <tr>
                                    <td>Batch Created</td>
                                    <td>{{Fmt CreatedDate 'd'}}</td>
                                    <td>{{Fmt CreatedDate 't'}}</td>
                                </tr>
                                <tr>
                                    <td>Printed</td>
                                    <td>{{Fmt CurrentTime 'd'}}</td>
                                    <td>{{Fmt CurrentTime 't'}}</td>
                                </tr>
                                <tr>
                                    <td>Deposited</td>
                                    <td>{{Fmt DepositDate 'd'}}</td>
                                    <td></td>
                                </tr>
                            </table>
                        </td>
                        <td align="right">
                            <table class="table notwide no-border" style="margin-bottom:0">

                                <tr>
                                    <td></td>
                                    <td class="right"></td>
                                </tr>
                                <tr>
                                    <td>Total Cash</td>
                                    <td class="right">{{Fmt CashTotal 'N2'}}</td>
                                </tr>
                                <tr>
                                    <td>Total Checks</td>
                                    <td class="right">{{Fmt CheckTotal 'N2'}}</td>
                                </tr>
                                {{/if}}
                                <tr style="font-weight:bold">
                                    <td>Total</td>
                                    <td class="right">{{Fmt Total 'N2'}}</td>
                                </tr>
                            </table>
                        </td>
                    </tr>
                </table>
            </td>
        </tr>
        {{/with}}
        <tr>
            <th>Name</th>
            <th>Type</th>
            <th class="right">Amount</th>
            <th>Fund</th>
            <th>CheckNo</th>
        </tr>
    </thead>
    <tbody>
    {{#each gifts}}
        <tr style="{{Bold}}">
            <td>{{Name}}</td>
            <td>{{Type}}</td>
            <td class="right">
                {{Fmt Amt 'N2'}}</td>
            <td>{{Fund}}</td>
            <td>{{CheckNo}}</td>
        </tr>
    {{/each}}
    </tbody>
</table>
<div>
-    <h3>Counter Signatures</h3>

<h3></h3>
<br>
<br>
<br>
-    ________________________________________________________

</div>
'''

Data.header = q.QuerySqlTop1(sqlheader)
Data.gifts = q.QuerySql(sqlgifts)

print model.RenderTemplate(template)


Latest Update

10/06/2023

Update links to related articles