Multi-Batch Report by Campus¶

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;
    }
    
    .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}}
    </tbody>
</table>
<div class="header">
    <h3>Batch Contents</h3>
</div>
<div class="export">
    <h4>
        <button>
            <a href=/RunScriptExcel/MultiBatchReportCsql?batchids={{cleanlist}} target="_blank">Export to Excel</a>
        </button>
    </h4>
</div>
<table class="table centered">
    <thead>
        <tr>
            <th>Name</th>
            <th>Campus</th>
            <th>Fund</th>
            <th>Batch</th>
            <th>ContributionDate</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 {{#unless BundleHeaderId}}style="font-weight:bold"{{/unless}}>
            <td>{{ContributorName}}</td>
            <td>{{Campus}}</td>
            <td>{{FundName}}</td>
            <td><a href="/Batches/Detail/{{BundleHeaderId}}" target="_blank">{{BundleHeaderId}}</a></td>
            <td>{{ContributionDate}}</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))
sqlgifts = model.Content('MultiBatchReportCsql')
params = { 'batchids' : cleanlist }
Data.gifts = q.QuerySql(sqlgifts, params)

print model.RenderTemplate(template)

DECLARE @Results1 TABLE (
                ContributorName nvarchar(138),
				Campus nvarchar(100),
                FundName nvarchar(256),
                ContributionDate varchar(10),
                BundleHeaderId INT,
                BundleDetailId INT,
                GeneralLedger NVARCHAR(25),
                Amount numeric(11,2),
                Base numeric(11,2),
                Fees numeric(11,2)
)
INSERT INTO @Results1 (
                ContributorName,
				Campus,
                FundName,
                ContributionDate,
                BundleHeaderId,
                BundleDetailId,
                GeneralLedger,
                Amount,
                Base,
                Fees
                )
SELECT  p.Name AS ContributorName
		,ISNULL(cam.Description, '(no campus)') AS Campus
        ,CASE WHEN cb.ContributionTypeId = 99 THEN isnull(accnt.Description, '') ELSE cf.FundName END
        ,CONVERT(varchar(10), cb.ContributionDate, 101) AS ContributionDate
        ,d.BundleHeaderId
        ,d.BundleDetailId
        ,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
    INNER JOIN dbo.ContributionFund AS cf ON cf.FundId = cb.FundId
	LEFT JOIN lookup.Campus cam ON cam.Id = cb.CampusId
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 ContributorName IS NULL THEN
			CASE WHEN Campus IS NULL THEN 'Grand Total'
			     WHEN FundName IS NULL THEN 'Campus Total'
				 ELSE 'Fund Total' END
		 ELSE ContributorName END AS ContributorName
	,Campus
	,FundName
	,ContributionDate
    ,BundleHeaderId
	,BundleDetailId
	,GeneralLedger
	,SUM(Amount) AS Amount
	,SUM(Base) AS Base
	,SUM(Fees) AS Fees
FROM @Results1
GROUP BY GROUPING SETS(
	(Campus, FundName, GeneralLedger, BundleHeaderId, BundleDetailId, ContributionDate, ContributorName), 
	(Campus, FundName, GeneralLedger), 
	(Campus),
	() )