Settlement Report¶

#Roles=Finance

model.Title = 'Batch Report'

sqlheader = '''
    SELECT TOP 1
        batch,
        batchref,
        batchtyp,
        Church = (SELECT Setting FROM dbo.Setting WHERE Id = 'NameOfChurch')
    FROM dbo.[Transaction]
    WHERE batchref = @P1
'''

sqlgifts = '''
DECLARE @Results TABLE (
                TranId int,
                PeopleId int,
                Name nvarchar(138),
                Bold nvarchar(100),
                Fund nvarchar(256),
                Date varchar(11),
                Ledger nvarchar(25),
                Amount numeric(11,2),
                Notes nvarchar(256),
                CustomNote nvarchar(256),
                BundleHeaderId int
)
;WITH t AS (
    SELECT Id
    FROM dbo.[Transaction]
    WHERE batchref = @P1
),
tc AS (
    SELECT DISTINCT TranId, PeopleId
    FROM dbo.Contribution
    WHERE TranId IN (SELECT Id FROM t)
    AND ContributionTypeId <> 99
),
tp AS (
    SELECT DISTINCT tp.Id, PeopleId
    FROM dbo.TransactionPeople tp
    JOIN t ON t.Id = tp.Id
),
regdata AS (
    SELECT 
        t.Id [TranId],
        COALESCE((SELECT TOP 1 tp.PeopleId
                  FROM tp
                  WHERE tp.Id = t.Id),
                  tl.LoginPeopleId) [PeopleId],
        COALESCE((SELECT TOP 1 p.Name
                  FROM tp
                  JOIN dbo.People p ON p.PeopleId = tp.PeopleId
                  WHERE tp.Id = t.Id),
                  tl.Name,
                  tl.First + ' ' + tl.Last,
                  'Anonymous') [Name],
        tl.TransactionDate [Date],
        'REGISTRATION' [Fund],
        ac.Code [GeneralLedger],
        ISNULL(tl.Amt, 0) - ISNULL(tl.donate, 0) [Amount],
        tl.Description [Notes],
        NULL [CustomNote],
        NULL [BundleHeaderId]
    FROM t
    JOIN dbo.TransactionList tl ON tl.Id = t.Id
    LEFT JOIN dbo.Organizations o ON o.OrganizationId = tl.OrgId
    LEFT JOIN lookup.AccountCode ac ON ac.Id = o.RegSettingXml.value('(/Settings/Fees/AccountingCode)[1]', 'varchar(12)')
    WHERE ((NOT EXISTS (SELECT NULL FROM tc WHERE tc.TranId = t.Id))
    OR (ISNULL(tl.donate, 0) > 0 AND ISNULL(tl.amt, 0) > ISNULL(tl.donate, 0)))
),
contdata AS (
    SELECT
        c.TranId,
        c.PeopleId,
        p.Name [Name],
        c.ContributionDate [Date],
        CASE WHEN c.ContributionTypeId = 99 THEN ISNULL(ac.Description, '') ELSE f.FundName END [Fund],
        CASE WHEN c.ContributionTypeId = 99 THEN ISNULL(ac.Code, '') ELSE f.FundIncomeAccount END [GeneralLedger],
        c.ContributionAmount [Amount],
        tl.Description [Notes],
        c.Notes [CustomNote],
        bd.BundleHeaderId
    FROM dbo.Contribution c
    JOIN t ON t.Id = c.TranId
    JOIN dbo.ContributionFund f ON f.FundId = c.FundId
    JOIN dbo.People p ON p.PeopleId = c.PeopleId
    JOIN dbo.TransactionList tl ON tl.Id = t.Id
    JOIN dbo.BundleDetail bd ON bd.ContributionId = c.ContributionId
    LEFT JOIN lookup.AccountCode ac ON ac.Id = c.AccountCodeId
    WHERE EXISTS (SELECT NULL FROM tc WHERE tc.TranId = t.Id)
    AND c.ContributionTypeId <> 99
),
data AS (
    SELECT * FROM regdata
    UNION
    SELECT * FROM contdata
)
INSERT INTO @Results (
                TranId,
                PeopleId,
                Name,
                Bold,
                Date,
                Fund,
                Ledger,
                Amount,
                Notes,
                CustomNote,
                BundleHeaderId
                )
SELECT
    TranId,
    PeopleId,
    CASE WHEN Name IS NOT NULL THEN Name
         WHEN Fund IS NOT NULL THEN 'Fund Total'
         ELSE 'Batch Total' END,
    CASE WHEN Name IS NULL THEN 'font-weight:bold' END,
    Date,
    Fund,
    GeneralLedger,
    SUM(Amount),
    Notes,
    CustomNote,
    BundleHeaderId
FROM data
GROUP BY GROUPING SETS((TranId, PeopleId, Name, Date, Fund, GeneralLedger, Notes, CustomNote, BundleHeaderId),(Fund, GeneralLedger),())
SELECT *
FROM @Results
'''

template = '''
<style>
    table.table.no-border td,
    table.table.no-border th {
        border-top: none;
    }
</style>
<table class="table centered">
    <thead>
        {{#with header}}
        <tr>
            <td colspan="7">
                <div class="text-center">
                    <h2>{{Church}}</h2>
                    <h4>BatchID: {{batchref}} - BatchType: {{batchtyp}}</h4>
                    <h4>BatchDate: {{batch}}</h4>
                </div>
                <br>
            </td>
        </tr>
        {{/with}}
        <tr>
            <th>Name</th>
            <th>Date</th>
            <th>Fund</th>
            <th>Ledger</th>
            <th class="right">Amount</th>
            <th>Notes</th>
            <th>Custom Note</th>
        </tr>
    </thead>
    <tbody>
    {{#each gifts}}
        <tr style="{{Bold}}">
            {{#if PeopleId}}
            <td><a href="/Person2/{{PeopleId}}" target="_blank">{{Name}}</a></td>
            {{else}}
            <td>{{Name}}</td>
            {{/if}}
            {{#if TranId}}
            <td><a href="/Transactions/{{TranId}}" target="_blank">{{Date}}</a></td>
            {{else}}
            <td>{{Date}}</td>
            {{/if}}
            <td>{{Fund}}</td>
            <td>{{Ledger}}</td>
            {{#if BundleHeaderId}}
            <td class="right"><a href="/Batches/Detail/{{BundleHeaderId}}" target="_blank">{{Fmt Amount 'N2'}}</a></td>
            {{else}}
            <td class="right">{{Fmt Amount 'N2'}}</td>
            {{/if}}
            <td>{{Notes}}</td>
            <td>{{CustomNote}}</td>
        </tr>
    {{/each}}
    </tbody>
</table>
'''

Data.header = q.QuerySqlTop1(sqlheader)
Data.gifts = q.QuerySql(sqlgifts)

print model.RenderTemplate(template)