Batch Report by Campus¶

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="8">
                <div class="text-center">
                    <h2>{{Church}}</h2>
                    <h4>BatchId: {{BundleHeaderId}} - DepositDate: {{DepositDate}}</h4>
                    <h4>BatchType: {{Description}}</h4>
                    <h4 class="export"><button>
                        <a href=/RunScriptExcel/BatchReportCsql/{{BundleHeaderId}} target="_blank">Export to Excel</a>
                    </button></h4>
                </div
                <br>
            </td>
        </tr>
        {{/with}}
        <tr>
            <th>Name</th>
            <th>Date</th>
            <th>Campus</th>
            <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 ContributorName "Fund Total"}}style="font-weight:bold"{{/IfEqual}}  
            {{IfEqual ContributorName "Campus Total"}}style="font-weight:bold"{{/IfEqual}}
            {{IfEqual ContributorName "Grand Total"}}style="font-weight:bold"{{/IfEqual}}>
            <td>{{ContributorName}}</td>
            <td>{{ContributionDate}}</td>
            <td>{{Campus}}</td>
            <td>{{FundName}}</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('BatchReportCsql')
params = { 'P1' : Data.p1 }
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(100),
                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 = @P1)
    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),
	() )