Skip to content

Instantly share code, notes, and snippets.

@hkouns

hkouns/Counting List

Created February 25, 2016 15:39
Show Gist options
  • Save hkouns/fbfac7e4e6ba84938f40 to your computer and use it in GitHub Desktop.
Save hkouns/fbfac7e4e6ba84938f40 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Counting List
GivingUnits = '''
DECLARE
@startdt DATETIME
, @enddt DATETIME
, @campusid INT = 0
, @pledges INT = 0
, @nontaxded INT = 0
, @includeUnclosed INT = 1;
SET @enddt = GETDATE();
SET @startdt = DATEADD(day, -90, @enddt);
WITH givers AS (
SELECT
FamilyId
, CreditGiverId
, SpouseId
, SUM(Amount) AS Total
FROM Contributions2(@startdt, @enddt, @campusid, @pledges, @nontaxded, @includeUnclosed)
GROUP BY FamilyId, CreditGiverId, SpouseId
)
SELECT
p.Name2
,p.IsDeceased
,p.preferredname
,p.PeopleId
,sp.preferredname Spouse
,sp.PeopleId SpouseID
,givers.SpouseId GiverSpouseId
,givers.Total
,givers.FamilyId
,p.ContributionOptionsId
,p.PositionInFamilyId
FROM givers
JOIN dbo.People p ON p.PeopleId = givers.CreditGiverId
LEFT JOIN dbo.People sp ON sp.PeopleId = givers.SpouseId
WHERE givers.Total > 50
AND p.IsDeceased = 0
AND CAST(CASE WHEN EXISTS(SELECT 1 FROM dbo.OrganizationMembers o WHERE o.PeopleId = p.PeopleId AND o.OrganizationId = 233 )
THEN 1
ELSE 0 END AS BIT) = 0
ORDER BY p.Name2
'''
template = '''
<html>
<style>
p {
font-size:12px;
}
table, th, td {
border: 1px solid black;
}
th, td {
padding: 2px;
}
td {
border: 1px solid black;
}
</style>
<body>
<table width="960">
<tbody>
<tr>
<th colspan="4" width="440">
<p>
<small><i>Report based on donors for 3 months prior to {{Fmt Now "d"}}</i></BR>
Page: 1</small>
</p>
</th>
<th width="110">
<p align="center"><strong>Tithe</strong></p>
</th>
<th width="110">
<p align="center"><strong>Bldg Fund</strong></p>
</th>
<th colspan="3" width="300">
<p align="center"><strong>Other Funds </strong><em>(enter Fund Name)</em></p>
</th>
</tr>
<tr>
<th width="50">
<p align="center"><strong>Id</strong></p>
</th>
<th width="190">
<p><strong>Name</strong></p>
</th>
<th width="150">
<p><strong>Spouse</strong></p>
</th>
<th width="50">
<p align="center"><small>Stmt</small></p>
</th>
<th width="110">
<p align="center"><strong>Amount</strong></p>
</th>
<th width="110">
<p align="center"><strong>Amount</strong></p>
</th>
<th width="100">
<p align="center"><strong>______________</strong></p>
</th>
<th width="100">
<p align="center"><strong>______________</strong></p>
</th>
<th width="100">
<p align="center"><strong>______________</strong></p>
</th>
</tr>
{{#each sqlpeople}}
<tr>
<td width="50">
<p align="center">{{PeopleId}}</p>
</td>
<td width="190">
<p>{{Name2}}</p>
</td>
<td width="150">
<p>
{{#IfEqual SpouseID PeopleId}}
{{else}}
{{Spouse}}
{{/IfEqual}}
{{#IfEqual ContributionOptionsId 2}}<small>({{SpouseID}})</small>{{/IfEqual}}
</p>
</td>
<td width="50" >
<p align="center"><small>
{{#IfEqual ContributionOptionsId 1}}Ind.{{/IfEqual}}
{{#IfEqual ContributionOptionsId 2}}Joint{{/IfEqual}}
</small></p>
</td>
<td width="110">
<p>&nbsp;</p>
</td>
<td width="110">
<p>&nbsp;</p>
</td>
<td width="100">
<p>&nbsp;</p>
</td>
<td width="100">
<p>&nbsp;</p>
</td>
<td width="100">
<p>&nbsp;</p>
</td>
</tr>
{{#IfEqual @Index 35}}
</tbody>
</table>
<p style="page-break-before: always">
<table width="960">
<tbody>
<tr>
<th colspan="4" width="440">
<p>
<small><i>Report based on donors for 6 months prior to {{Fmt ../../Now "d"}}</i></BR>
Page: 2</small></p>
</th>
<th width="110">
<p align="center"><strong>Tithe</strong></p>
</th>
<th width="110">
<p align="center"><strong>Bldg Fund</strong></p>
</th>
<th colspan="3" width="300">
<p align="center"><strong>Other Funds </strong><em>(enter Fund Name)</em></p>
</th>
</tr>
<tr>
<th width="50">
<p align="center"><strong>Id</strong></p>
</th>
<th width="190">
<p><strong>Name</strong></p>
</th>
<th width="150">
<p><strong>Spouse</strong></p>
</th>
<th width="50">
<p align="center"><small>Stmt</br>Type</small></p>
</th>
<th width="110">
<p align="center"><strong>Amount</strong></p>
</th>
<th width="110">
<p align="center"><strong>Amount</strong></p>
</th>
<th width="100">
<p align="center"><strong>______________</strong></p>
</th>
<th width="100">
<p align="center"><strong>______________</strong></p>
</th>
<th width="100">
<p align="center"><strong>______________</strong></p>
</th>
</tr>
{{/IfEqual}}
{{#IfEqual @Index 70}}
</tbody>
</table>
<p style="page-break-before: always">
<table width="960">
<tbody>
<tr>
<th colspan="4" width="440">
<p>
<small><i>Report based on donors for 6 months prior to {{Fmt ../../Now "d"}}</i></BR>
Page: 3</small></p>
</th>
<th width="110">
<p align="center"><strong>Tithe</strong></p>
</th>
<th width="110">
<p align="center"><strong>Bldg Fund</strong></p>
</th>
<th colspan="3" width="300">
<p align="center"><strong>Other Funds </strong><em>(enter Fund Name)</em></p>
</th>
</tr>
<tr>
<th width="50">
<p align="center"><strong>Id</strong></p>
</th>
<th width="190">
<p><strong>Name</strong></p>
</th>
<th width="150">
<p><strong>Spouse</strong></p>
</th>
<th width="50">
<p align="center"><small>Stmt</br>Type</small></p>
</th>
<th width="110">
<p align="center"><strong>Amount</strong></p>
</th>
<th width="110">
<p align="center"><strong>Amount</strong></p>
</th>
<th width="100">
<p align="center"><strong>______________</strong></p>
</th>
<th width="100">
<p align="center"><strong>______________</strong></p>
</th>
<th width="100">
<p align="center"><strong>______________</strong></p>
</th>
</tr>
{{/IfEqual}}
{{#IfEqual @Index 105}}
</tbody>
</table>
<p style="page-break-before: always">
<table width="960">
<tbody>
<tr>
<th colspan="4" width="440">
<p>
<small><i>Report based on donors for 6 months prior to {{Fmt ../../Now "d"}}</i></BR>
Page: 4</small></p>
</th>
<th width="110">
<p align="center"><strong>Tithe</strong></p>
</th>
<th width="110">
<p align="center"><strong>Bldg Fund</strong></p>
</th>
<th colspan="3" width="300">
<p align="center"><strong>Other Funds </strong><em>(enter Fund Name)</em></p>
</th>
</tr>
<tr>
<th width="50">
<p align="center"><strong>Id</strong></p>
</th>
<th width="190">
<p><strong>Name</strong></p>
</th>
<th width="150">
<p><strong>Spouse</strong></p>
</th>
<th width="50">
<p align="center"><small>Stmt</br>Type</small></p>
</th>
<th width="110">
<p align="center"><strong>Amount</strong></p>
</th>
<th width="110">
<p align="center"><strong>Amount</strong></p>
</th>
<th width="100">
<p align="center"><strong>______________</strong></p>
</th>
<th width="100">
<p align="center"><strong>______________</strong></p>
</th>
<th width="100">
<p align="center"><strong>______________</strong></p>
</th>
</tr>
{{/IfEqual}}
{{/each}}
<tr>
<td width="50">
<p>&nbsp;</p>
</td>
<td width="190">
<p>&nbsp;</p>
</td>
<td width="150">
<p>&nbsp;</p>
</td>
<td width="50" >
<p>&nbsp;</p>
</td>
<td width="110">
<p>&nbsp;</p>
</td>
<td width="110">
<p>&nbsp;</p>
</td>
<td width="100">
<p>&nbsp;</p>
</td>
<td width="100">
<p>&nbsp;</p>
</td>
<td width="100">
<p>&nbsp;</p>
</td>
</tr>
<tr>
<td width="50">
<p>&nbsp;</p>
</td>
<td width="190">
<p>&nbsp;</p>
</td>
<td width="150">
<p>&nbsp;</p>
</td>
<td width="50" >
<p>&nbsp;</p>
</td>
<td width="110">
<p>&nbsp;</p>
</td>
<td width="110">
<p>&nbsp;</p>
</td>
<td width="100">
<p>&nbsp;</p>
</td>
<td width="100">
<p>&nbsp;</p>
</td>
<td width="100">
<p>&nbsp;</p>
</td>
</tr>
<tr>
<td width="50">
<p>&nbsp;</p>
</td>
<td width="190">
<p>&nbsp;</p>
</td>
<td width="150">
<p>&nbsp;</p>
</td>
<td width="50" >
<p>&nbsp;</p>
</td>
<td width="110">
<p>&nbsp;</p>
</td>
<td width="110">
<p>&nbsp;</p>
</td>
<td width="100">
<p>&nbsp;</p>
</td>
<td width="100">
<p>&nbsp;</p>
</td>
<td width="100">
<p>&nbsp;</p>
</td>
</tr>
</tbody>
</table>
</body>
</html>
'''
Data.sqlpeople = q.QuerySql(GivingUnits)
Data.Now = model.DateTime
body = model.RenderTemplate(template)
print body
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment