Batch Report 2¶

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 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/BatchReport2sql/{{BundleHeaderId}} target="_blank">Export to Excel</a>
                    </button></h4>
                </div
                <br>
            </td>
        </tr>
        {{/with}}
        <tr>
            <th>Name</th>
            <th>Fund</th>
            <th>Date</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 BundleId}}style="font-weight:bold"{{/unless}}>
            <td>{{ContributorName}}</td>
            <td>{{FundName}}</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.header = q.QuerySqlTop1(sqlheader)
sqlgifts = model.Content('BatchReport2sql')
params = { 'P1' : Data.p1 }
Data.gifts = q.QuerySql(sqlgifts, params)

print model.RenderTemplate(template)

DECLARE @Results1 TABLE (
                ContributorName nvarchar(138),
                FundName nvarchar(256),
                ContributionDate varchar(10),
                BundleId INT,
                GeneralLedger NVARCHAR(25),
                Amount numeric(11,2),
                Base numeric(11,2),
                Fees numeric(11,2)
)
INSERT INTO @Results1 (
                ContributorName,
                FundName,
                ContributionDate,
                BundleId,
                GeneralLedger,
                Amount,
                Base,
                Fees
                )
SELECT      p.Name AS ContributorName
        ,CASE WHEN cb.ContributionTypeId = 99 THEN isnull(accnt.Description, '') ELSE cf.FundName END
        ,CONVERT(varchar(10), cb.ContributionDate, 101) AS ContributionDate
        ,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
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 
	CASE WHEN FundName IS NULL THEN 'Total'
		 WHEN ContributorName IS NULL THEN
			CASE WHEN BundleId IS NULL THEN 'Fund Total' END
		 ELSE ContributorName END AS ContributorName
	,FundName
	,ContributionDate
	,BundleId
	,GeneralLedger
	,SUM(Amount) AS Amount
	,SUM(Base) AS Base
	,SUM(Fees) AS Fees
FROM @Results1
GROUP BY GROUPING SETS((FundName, GeneralLedger, ContributionDate, BundleId, ContributorName), (FundName, GeneralLedger), ())