Multi-Batch Report 3¶

import re

model.Title = 'Batch Report'

def Disinfect(str):
    idlist = str.split(',')
    idstr = ''
    for id in idlist:
        idInt = re.sub('[^\d]', '', id)
        idstr = idstr + ',' + idInt
    return idstr[1:]

cleanlist = Disinfect(Data.p1)
Data.cleanlist = cleanlist


sqlheader = '''
    SELECT Setting 
    FROM dbo.Setting 
    WHERE Id = 'NameOfChurch'
'''

sqlbatches = '''
;WITH gifts AS (
	SELECT 
		bh.BundleHeaderId,
		c.ContributionAmount,
		c.ContributionSourceId,
		c.CheckNo
	FROM dbo.BundleHeader bh
	JOIN dbo.BundleDetail bd ON bd.BundleHeaderId = bh.BundleHeaderId
	JOIN dbo.Contribution c ON c.ContributionId = bd.ContributionId
	WHERE bh.BundleHeaderId IN (SELECT Value FROM dbo.SplitInts('{0}'))
	AND c.ContributionStatusId = 0
	AND c.ContributionTypeId NOT IN (6,7,8)
)
SELECT
	bh.BundleHeaderId,
	CONVERT(VARCHAR(10), bh.DepositDate, 101) AS DepositDate,
	bt.Description,
	ISNULL(bh.BundleTotal, 0) EstTotal,
	ISNULL(bh.BundleCount, 0) EstCount,
	(SELECT ISNULL(SUM(ContributionAmount), 0) FROM gifts WHERE BundleHeaderId = bh.BundleHeaderId) AS Amount,
	(SELECT COUNT(*) FROM gifts WHERE BundleHeaderId = bh.BundleHeaderId) AS [Count],
	(SELECT ISNULL(SUM(ContributionAmount), 0) FROM gifts WHERE BundleHeaderId = bh.BundleHeaderId
															AND ContributionSourceId = 2) AS [CARD],
	(SELECT ISNULL(SUM(ContributionAmount), 0) FROM gifts WHERE BundleHeaderId = bh.BundleHeaderId
															AND ContributionSourceId = 1) AS ACH,
	(SELECT ISNULL(SUM(ContributionAmount), 0) FROM gifts WHERE BundleHeaderId = bh.BundleHeaderId
															AND ISNULL(ContributionSourceId, 0) NOT IN (1,2)
															AND ISNULL(CheckNo, '') = '') AS CASH,
	(SELECT ISNULL(SUM(ContributionAmount), 0) FROM gifts WHERE BundleHeaderId = bh.BundleHeaderId
															AND ISNULL(ContributionSourceId, 0) NOT IN (1,2)
															AND ISNULL(CheckNo, '') <> '') AS CHECKS
FROM dbo.BundleHeader bh
JOIN lookup.BundleHeaderTypes bt ON bt.Id = bh.BundleHeaderTypeId
WHERE bh.BundleHeaderId IN (SELECT Value FROM dbo.SplitInts('{0}'))
'''

template = '''
<style>
    button > a:visited,
    button > a:hover {
        text-decoration: none;
    }
    
    .header, .export {
    text-align: center;
    }
    
    @media print {
        .export {display: none}
    }
    
    table.table.no-border td,
    table.table.no-border th {
        border-top: none;
    }
    
    .strong {
        font-weight: bold;
    }
    
    .box p {display: none;}
</style>
<div class="header">
<h2>{{Church}}</h2>
<h3>Batch List</h3>
</div>
<table class="table notwide centered">
    <thead>
        <tr>
            <th>Batch</th>
            <th>Deposit Date</th>
            <th>Batch Type</th>
            <th>Est Amount</th>
            <th>Est Count</th>
            <th>Amount</th>
            <th>Count</th>
            <th>Card</th>
            <th>ACH</th>
            <th>Cash</th>
            <th>Checks</th>
        </tr>
    </thead>
    <tbody>
        {{#each batches}}
        <tr>
            <td>{{BundleHeaderId}}</td>
            <td>{{DepositDate}}</td>
            <td>{{Description}}</td>
            <td class="right">{{EstTotal}}</td>
            <td class="right">{{EstCount}}</td>
            <td class="right">{{Amount}}</td>
            <td class="right">{{Count}}</td>
            <td class="right">{{CARD}}</td>
            <td class="right">{{ACH}}</td>
            <td class="right">{{CASH}}</td>
            <td class="right">{{CHECKS}}</td>
        </tr>
        {{/each}}
        <tr>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td class="right strong">{{AmountTotal}}</td>
            <td class="right strong">{{CountTotal}}</td>
            <td class="right strong">{{CardTotal}}</td>
            <td class="right strong">{{AchTotal}}</td>
            <td class="right strong">{{CashTotal}}</td>
            <td class="right strong">{{ChecksTotal}}</td>
        </tr>
    </tbody>
</table>
<div class="header">
    <h3>Batch Contents</h3>
</div>
<div class="export">
    <h4>
        <button>
            <a href=/RunScriptExcel/MultiBatchReport3sql?batchids={{cleanlist}} target="_blank">Export to Excel</a>
        </button>
    </h4>
</div>
<table class="table notwide centered">
    <thead>
        <tr>
            <th>Fund</th>
            <th>GeneralLedger</th>
            <th class="right">Total</th>
            <th class="right">Base</th>
            <th class="right">Fees</th>
        </tr>
    </thead>
    <tbody>
    {{#each gifts}}
        <tr {{IfEqual Fund "Total"}}style="font-weight:bold"{{/IfEqual}}>
            <td>{{Fund}}</td>
            <td>{{GeneralLedger}}</td>
            <td class="right">{{Fmt Amount 'N2'}}</td>
            <td class="right">{{Fmt Base 'N2'}}</td>
            <td class="right">{{Fmt Fees 'N2'}}</td>
        </tr>
    {{/each}}
    </tbody>
</table>
'''

Data.Church = q.QuerySqlScalar(sqlheader)
Data.batches = q.QuerySql(sqlbatches.format(cleanlist))
Data.AmountTotal = 0
Data.CountTotal = 0
Data.CardTotal = 0
Data.AchTotal = 0
Data.CashTotal = 0
Data.ChecksTotal = 0
for b in Data.batches:
    Data.AmountTotal += b.Amount
    Data.CountTotal += b.Count
    Data.CardTotal += b.CARD
    Data.AchTotal += b.ACH
    Data.CashTotal += b.CASH
    Data.ChecksTotal += b.CHECKS
sqlgifts = model.Content('MultiBatchReport3sql')
params = { 'batchids' : cleanlist }
Data.gifts = q.QuerySql(sqlgifts, params)

print model.RenderTemplate(template)

DECLARE @Results1 TABLE (
                FundName nvarchar(256),
                GeneralLedger NVARCHAR(100),
                Amount numeric(11,2),
                Base numeric(11,2),
                Fees numeric(11,2)
)
INSERT INTO @Results1 (
                FundName,
                GeneralLedger,
                Amount,
                Base,
                Fees
                )
SELECT
         CASE WHEN cb.ContributionTypeId = 99 THEN isnull(accnt.Description, '') 
              ELSE cf.FundName + ' (' + CONVERT(NVARCHAR(12), cb.FundId) + ')' END
        ,CASE WHEN cb.ContributionTypeId = 99 THEN isnull(accnt.Code, '') ELSE cf.FundIncomeAccount END
        ,cb.ContributionAmount
        ,CASE WHEN cb.DonorCoveredFee = 0 THEN cb.ContributionAmount
              ELSE cb.ContributionAmount - ISNULL(cb.ChargedFee, 0) END
        ,CASE WHEN cb.DonorCoveredFee = 0 THEN 0
              ELSE ISNULL(cb.ChargedFee, 0) END
FROM dbo.Contribution  AS cb
LEFT JOIN dbo.People AS p ON p.PeopleId = cb.PeopleId
LEFT JOIN lookup.AccountCode accnt ON accnt.Id = cb.AccountCodeId
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 in (SELECT Value FROM dbo.SplitInts(@batchids)))
        AND ISNULL(cb.PledgeFlag, 0) = 0
        AND cb.ContributionStatusId = 0
        AND cb.ContributionTypeId NOT IN (6,7,8)
--
SELECT 
	CASE WHEN FundName IS NULL THEN 'Total'
		 ELSE FundName END AS Fund
	,GeneralLedger
	,SUM(Amount) AS Amount
	,SUM(Base) AS Base
	,SUM(Fees) AS Fees
FROM @Results1
GROUP BY GROUPING SETS ( (FundName, GeneralLedger), () )