Touchpoint Software Documentation | Bundle Report 2

Bundle Report 2

You have 2 options for Bundle Reports. You can choose to use the original Bundle Report, which gives a breakdown by Cash, Check, Coin in the header with details below.

See also

Bundle Report

Or you can use this Bundle Report2 that provides different information.

Note

Both reports have the title Bundle Report on the Bundle page. See image below.

Bundle Report2

This report provides the following information for each Bundle. As with the original Bundle Report, the link to run this is on the Bundle page, but not on Post Bundle Edit.

Header
  • Church Name
  • Bundle ID #
  • Deposit Date
  • Bundle Type
Details
  • Name
  • Fund Name
  • Contribution Date
  • General Ledger Account #
  • Amount

In the details, the contributions are grouped by Fund with a sub-total per Fund and a grand total at the bottom of the report.

http://i.tpsdb.com/./2018-01-29_12-01-44.png

Bundle Report2 Link

http://i.tpsdb.com/./2018-01-29_11-53-41.png

Sample Report

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 BundleReport2 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/BundleReport2?p1={bundleid}

Note

Each database can have only one BundleReport. So, the Setting is just named CustomBundleReport and the Value determines which is used.

Generate the Bundle Report2

To generate the report, click the Bundle Report link in each individual bundle.

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

Python Code

    sqlheader = '''
    SELECT
        h.BundleHeaderId,
        h.CreatedDate,
        h.ContributionDate,
        CONVERT(varchar(10), h.DepositDate, 101) AS DepositDate,
        h.TotalChecks,
        h.TotalCash,
        h.TotalEnvelopes,
        Total = ISNULL(TotalChecks, 0) + ISNULL(TotalCash, 0) + ISNULL(TotalEnvelopes, 0),
        Church = (SELECT Setting FROM dbo.Setting WHERE Id = 'NameOfChurch'),
        CurrentTime = GETDATE(),
        bt.Description
    FROM dbo.BundleHeader h
    RIGHT JOIN lookup.BundleHeaderTypes bt ON bt.Id = h.BundleHeaderTypeId
    WHERE BundleHeaderId = @P1
'''

sqlgifts = '''
DECLARE @Results1 TABLE (
                ContributorName nvarchar(138),
                Bold nvarchar(100),
                FundName nvarchar(256),
                ContributionDate varchar(10),
                BundleId INT,
                GeneralLedger NVARCHAR(25),
                Amount numeric(11,2)
)
INSERT INTO @Results1 (
                ContributorName,
                Bold,
                FundName,
                ContributionDate,
                BundleId,
                GeneralLedger,
                Amount
                )
SELECT      CASE WHEN cf.FundName IS NULL THEN 'Total'
            WHEN p.Name IS NULL THEN
               CASE WHEN d.BundleDetailId IS NULL THEN 'Fund Total' END
            ELSE p.Name END AS ContributorName
        ,CASE WHEN cf.FundName IS NULL THEN 'font-weight:bold'
            WHEN p.Name IS NULL THEN
                CASE WHEN d.BundleDetailId IS NULL THEN 'font-weight:bold' END
            ELSE p.Name END AS Bold
        ,cf.FundName
        ,CONVERT(varchar(10), cb.ContributionDate, 101) AS ContributionDate
        ,d.BundleDetailId
        ,cf.FundIncomeAccount
        ,SUM(cb.ContributionAmount) AS ContributionAmount
FROM dbo.Contribution  AS cb
LEFT JOIN dbo.People AS p ON p.PeopleId = cb.PeopleId
JOIN dbo.BundleDetail d ON d.ContributionId = cb.ContributionId
JOIN dbo.BundleHeader h ON h.BundleHeaderId = d.BundleHeaderId
RIGHT JOIN lookup.BundleHeaderTypes bt ON bt.Id = h.BundleHeaderTypeId
INNER JOIN dbo.ContributionFund AS cf ON cf.FundId = cb.FundId
WHERE (d.BundleHeaderId = @P1)
        AND ISNULL(cb.PledgeFlag, 0) = 0
        AND cb.ContributionStatusId = 0
        AND cb.ContributionTypeId NOT IN (6,7,8)
GROUP BY GROUPING SETS((cf.FundName, cf.FundIncomeAccount, h.DepositDate, cb.ContributionDate, d.BundleDetailId, bt.Description, p.Name), (cf.FundName, cf.FundIncomeAccount), ())


SELECT *
FROM @Results1
'''

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="4">
                <div class="text-center">
                    <h2>{{Church}}</h2>
                    <h4>BundleId: {{BundleHeaderId}} - DepositDate: {{DepositDate}}</h4>
                    <h4>BundleType: {{Description}}</h4>
                </div
                <br>
            </td>
        </tr>
        {{/with}}
        <tr>
            <th>Name</th>
            <th>Fund</th>
            <th>ContributionDate</th>
            <th>GeneralLedger</th>
            <th class="right">Amount</th>
        </tr>
    </thead>
    <tbody>
    {{#each gifts}}
        <tr style="{{Bold}}">
            <td>{{ContributorName}}</td>
            <td>{{FundName}}</td>
            <td>{{ContributionDate}}</td>
            <td>{{GeneralLedger}}</td>
            <td class="right">
                {{Fmt Amount 'N2'}}</td>
        </tr>
    {{/each}}
    </tbody>
</table>
'''

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

print model.RenderTemplate(template)

© 2018 TouchPoint Software, LLC.  All rights reserved.