Touchpoint Software Documentation | Bundle Report

Bundle Report

Bundle Report

This Python script will create a report for each contribution bundle that will present a header with the Bundle ID# and the Bundle Type, followed by the total for Currency, Coin and Checks and the dates associated with the bundle. This breakdown comes directly from the Bundle Header itself.

Below the header will be the details of the bundle - cash donations (including coin) first with a sub-total, followed by check donations with a sub-total, then a grand total for the bundle.

We also added signature line if you want to print this report and have your counters sign off on it.

Note

The way we can capture this information (cash or check) is as follows:

Cash - Leave the field empty or enter the word cash or Cash.

Check - Enter the check number or enter the word check or Check.

http://i.tpsdb.com/2017-10-06_10-43-46.png

Bundle Report Link

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

Bundle Report

Other Option

We also have another Bundle Report option, Bundle Report2, which list a different set of information.

See also

Bundle Report 2

Create a Bundle Report

Step 1
Go to Administration > Setup > Special Content > Python Scripts.
Step 2
Click the green + New Python Script File, enter name the file BundleReport and click Submit.
Step 3

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

Click Save Script.

Step 4
  • Add this Setting under Administration > Setup > Settings - CustomBundleReport
  • Paste this as the value - /PyScript/BundleReport?p1={bundleid}

To print the report, click the link in the individual bundle and use the print feature of your browser.

Run the Bundle Report

The link will display (as you see in the image above) in the blue bar on the bundle. The link does not display when you are in post bundle edit mode.

Click the link to display the report, and then use the print feature of your browser to print it.

sqlheader = '''
   SELECT
       BundleHeaderId,
       BundleHeaderType = bt.[Description],
       CreatedDate,
       ContributionDate,
       DepositDate,
       TotalChecks,
       TotalCash,
       TotalEnvelopes,
       Total = ISNULL(TotalChecks, 0) + ISNULL(TotalCash, 0) + ISNULL(TotalEnvelopes, 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 = f.FundDescription
            , Bold = ''
            , c.CheckNo
        FROM dbo.Contribution c
        JOIN dbo.People p ON p.PeopleId = c.PeopleId
        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', SUM(Amt), 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', SUM(Amt), NULL, 'font-weight:bold', NULL FROM gifts
    WHERE gifts.Type = 'Check'
    UNION ALL -----------------------
    SELECT 'TOTAL', 'ALL', SUM(Amt), 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="4">
                <div class="text-center">
                    <h2>{{Church}}</h2>
                    <h3>Bundle ID #: {{BundleHeaderId}}</h3>
                    <h4>BundleType: {{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>Bundle 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>Total Currency</td>
                                    <td class="right">{{Fmt TotalCash 'N2'}}</td>
                                </tr>
                                <tr>
                                    <td>Total Coins</td>
                                    <td class="right">{{Fmt TotalEnvelopes 'N2'}}</td>
                                </tr>
                                <tr>
                                    <td>Total Checks</td>
                                    <td class="right">{{Fmt TotalChecks '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)

© 2018 TouchPoint Software, LLC.  All rights reserved.