Batch Report by Type¶

model.Title = 'Batch Report'

sqlheader = '''
    SELECT
        h.BundleHeaderId,
        CONVERT(varchar(10), h.DepositDate, 101) AS DepositDate,
        Church = (SELECT Setting FROM dbo.Setting WHERE Id = 'NameOfChurch'),
        bt.Description
    FROM dbo.BundleHeader h
    RIGHT JOIN lookup.BundleHeaderTypes bt ON bt.Id = h.BundleHeaderTypeId
    WHERE BundleHeaderId = @P1
'''

template = '''
<style>
    button > a:visited,
    button > a:hover {
        text-decoration: none;
    }
    
    @media print {
        .export {display: none}
    }
    
    table.table.no-border td,
    table.table.no-border th {
        border-top: none;
    }
    
    .box p {display: none;}
</style>
<table class="table notwide centered">
    <thead>
        {{#with header}}
        <tr>
            <td colspan="7">
                <div class="text-center">
                    <h2>{{Church}}</h2>
                    <h4>BatchId: {{BundleHeaderId}} - DepositDate: {{DepositDate}}</h4>
                    <h4>BatchType: {{Description}}</h4>
                    <h4 class="export"><button>
                        <a href=/RunScriptExcel/BatchReportTsql/{{BundleHeaderId}} target="_blank">Export to Excel</a>
                    </button></h4>
                </div
                <br>
            </td>
        </tr>
        {{/with}}
        <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 {{#unless Fund}}style="font-weight:bold"{{/unless}}>
            <td>{{#if Fund}}{{Fund}}
                {{else}}{{#if Type}}{{Type}}{{else}}Grand{{/if}} Total{{/if}}</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.header = q.QuerySqlTop1(sqlheader)
sqlgifts = model.Content('BatchReportTsql')
params = { 'P1' : Data.p1 }
Data.gifts = q.QuerySql(sqlgifts, params)

print model.RenderTemplate(template)

DECLARE @Results1 TABLE (
				Type nvarchar(12),
                FundName nvarchar(256),
                GeneralLedger NVARCHAR(100),
                Amount numeric(11,2),
                Base numeric(11,2),
                Fees numeric(11,2)
)
INSERT INTO @Results1 (
				Type,
                FundName,
                GeneralLedger,
                Amount,
                Base,
                Fees
                )
SELECT
		 CASE WHEN cb.ContributionTypeId = 99 THEN 'Registration' ELSE 'Contribution' END
        ,CASE WHEN cb.ContributionTypeId = 99 THEN isnull(accnt.Description, '--no account code--') 
              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 = @P1)
        AND ISNULL(cb.PledgeFlag, 0) = 0
        AND cb.ContributionStatusId = 0
        AND cb.ContributionTypeId NOT IN (6,7,8)
--
SELECT 
	 Type
	,FundName Fund
	,GeneralLedger
	,SUM(Amount) AS Amount
	,SUM(Base) AS Base
	,SUM(Fees) AS Fees
FROM @Results1
GROUP BY GROUPING SETS ( (Type, FundName, GeneralLedger), 
						 (Type),
						 () )