Batch Report

Batch Report

The Batch Report shows details with fund subtotals for a batch. The report can be used as an aid in reconciliation and in general ledger entry. It includes a column for the fund’s Income Account (labeled as Ledger).

This Python script will create a report for a single batch from your payment gateway. The report can be accessed from the Reconcile Report by clicking on the Total for the desired batch. The Batch Report will present a header with the Batch Reference ID and the Batch Type (e.g., eft or bankcard) followed by the date and time the batch was completed by the payment gateway.

Below the header will be the details of the batch with subtotals for each included fund. Registration payments will be in their own section with a subtotal. (For general ledger entry of registration payments, we recommend either the Totals by Description or the Totals by Batch/Description report.)

Some of the information shown on the report acts as links to further information. Links can be recognized by the blue color and the underlining.

  • Name links will open a new tab with that person’s profile.
  • Date links will open a new tab with the online transaction for that entry.
  • Amount links will open a new tab with the bundle containing that contribution entry.
https://i.tpsdb.com/BatchReportLink.png

Batch Report Link in Reconcile Report

https://i.tpsdb.com/BatchReport.png

Batch Report

Configure the Batch Report

The standard form of the Batch Report is already loaded on your database. The administration setting CustomBatchReport, found under the Finance tab and in the Contribution Search section, points the system to the report. The value of this setting should be the URL to the Batch Report, /PyScript/BatchReport?p1={batchref}. Or, if you have created an alternate batch report, you can substitute into the setting the name of your batch report (in place of BatchReport).

Run the Batch Report

The link will display (as you see in the image above) in the Total column of the Reconcile Report. Click the link to display the report for that batch. The report will include information about online transactions with that batch reference.

If desired, you can use the print feature of your browser to print the report.

Code for the Batch Report Python Script

  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
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
#Roles=Finance

sqlheader = '''
    SELECT TOP 1
        batch,
        batchref,
        batchtyp,
        Church = (SELECT Setting FROM dbo.Setting WHERE Id = 'NameOfChurch')
    FROM dbo.[Transaction]
    WHERE batchref = @P1
'''

sqlgifts = '''
DECLARE @Results TABLE (
                TranId int,
                PeopleId int,
                Name nvarchar(138),
                Bold nvarchar(100),
                Fund nvarchar(256),
                Date varchar(11),
                Ledger nvarchar(25),
                Amount numeric(11,2),
                Notes nvarchar(256),
                CustomNote nvarchar(256),
                BundleHeaderId int
)
;WITH t AS (
    SELECT Id
    FROM dbo.[Transaction]
    WHERE batchref = @P1
),
tc AS (
    SELECT DISTINCT TranId, PeopleId
    FROM dbo.Contribution
    WHERE TranId IN (SELECT Id FROM t)
),
tp AS (
    SELECT DISTINCT tp.Id, PeopleId
    FROM dbo.TransactionPeople tp
    JOIN t ON t.Id = tp.Id
),
regdata AS (
    SELECT 
        t.Id [TranId],
        COALESCE((SELECT TOP 1 tp.PeopleId
                  FROM tp
                  WHERE tp.Id = t.Id),
                  tl.LoginPeopleId) [PeopleId],
        COALESCE((SELECT TOP 1 p.Name
                  FROM tp
                  JOIN dbo.People p ON p.PeopleId = tp.PeopleId
                  WHERE tp.Id = t.Id),
                  tl.Name,
                  tl.First + ' ' + tl.Last,
                  'Anonymous') [Name],
        tl.TransactionDate [Date],
        'REGISTRATION' [Fund],
        NULL [GeneralLedger],
        ISNULL(tl.Amt, 0) - ISNULL(tl.donate, 0) [Amount],
        tl.Description [Notes],
        NULL [CustomNote],
        NULL [BundleHeaderId]
    FROM t
    JOIN dbo.TransactionList tl ON tl.Id = t.Id
    WHERE ((NOT EXISTS (SELECT NULL FROM tc WHERE tc.TranId = t.Id))
    OR (ISNULL(tl.donate, 0) > 0 AND ISNULL(tl.amt, 0) > ISNULL(tl.donate, 0)))
),
contdata AS (
    SELECT
        c.TranId,
        c.PeopleId,
        p.Name [Name],
        c.ContributionDate [Date],
        f.FundName [Fund],
        f.FundIncomeAccount [GeneralLedger],
        c.ContributionAmount [Amount],
        tl.Description [Notes],
        c.Notes [CustomNote],
        bd.BundleHeaderId
    FROM dbo.Contribution c
    JOIN t ON t.Id = c.TranId
    JOIN dbo.ContributionFund f ON f.FundId = c.FundId
    JOIN dbo.People p ON p.PeopleId = c.PeopleId
    JOIN dbo.TransactionList tl ON tl.Id = t.Id
    JOIN dbo.BundleDetail bd ON bd.ContributionId = c.ContributionId
    WHERE EXISTS (SELECT NULL FROM tc WHERE tc.TranId = t.Id)
),
data AS (
    SELECT * FROM regdata
    UNION
    SELECT * FROM contdata
)
INSERT INTO @Results (
                TranId,
                PeopleId,
                Name,
                Bold,
                Date,
                Fund,
                Ledger,
                Amount,
                Notes,
                CustomNote,
                BundleHeaderId
                )
SELECT
    TranId,
    PeopleId,
    CASE WHEN Name IS NOT NULL THEN Name
         WHEN Fund IS NOT NULL THEN 'Fund Total'
         ELSE 'Batch Total' END,
    CASE WHEN Name IS NULL THEN 'font-weight:bold' END,
    Date,
    Fund,
    GeneralLedger,
    SUM(Amount),
    Notes,
    CustomNote,
    BundleHeaderId
FROM data
GROUP BY GROUPING SETS((TranId, PeopleId, Name, Date, Fund, GeneralLedger, Notes, CustomNote, BundleHeaderId),(Fund, GeneralLedger),())

SELECT *
FROM @Results
'''

template = '''
<style>
    table.table.no-border td,
    table.table.no-border th {
        border-top: none;
    }
</style>
<table class="table centered">
    <thead>
        {{#with header}}
        <tr>
            <td colspan="7">
                <div class="text-center">
                    <h2>{{Church}}</h2>
                    <h4>BatchID: {{batchref}} - BatchType: {{batchtyp}}</h4>
                    <h4>BatchDate: {{batch}}</h4>
                </div>
                <br>
            </td>
        </tr>
        {{/with}}
        <tr>
            <th>Name</th>
            <th>Date</th>
            <th>Fund</th>
            <th>Ledger</th>
            <th class="right">Amount</th>
            <th>Notes</th>
            <th>Custom Note</th>
        </tr>
    </thead>
    <tbody>
    {{#each gifts}}
        <tr style="{{Bold}}">
            {{#if PeopleId}}
            <td><a href="/Person2/{{PeopleId}}" target="_blank">{{Name}}</a></td>
            {{else}}
            <td>{{Name}}</td>
            {{/if}}
            {{#if TranId}}
            <td><a href="/Transactions/{{TranId}}" target="_blank">{{Date}}</a></td>
            {{else}}
            <td>{{Date}}</td>
            {{/if}}
            <td>{{Fund}}</td>
            <td>{{Ledger}}</td>
            {{#if BundleHeaderId}}
            <td class="right"><a href="/Bundle/{{BundleHeaderId}}" target="_blank">{{Fmt Amount 'N2'}}</a></td>
            {{else}}
            <td class="right">{{Fmt Amount 'N2'}}</td>
            {{/if}}
            <td>{{Notes}}</td>
            <td>{{CustomNote}}</td>
        </tr>
    {{/each}}
    </tbody>
</table>
'''

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

print model.RenderTemplate(template)


Latest Update 02/18/2021

Added this article.

© 2021 TouchPoint Software, LLC.  All rights reserved.