Skip to content

Instantly share code, notes, and snippets.

@hkouns

hkouns/Data Verify Test

Created January 4, 2016 21:57
Show Gist options
  • Save hkouns/369e6f737eaf586d2736 to your computer and use it in GitHub Desktop.
Save hkouns/369e6f737eaf586d2736 to your computer and use it in GitHub Desktop.
Download ZIP
Testing handlebars solution to DataVerification Report
Raw
Data Verify Test
import datetime
D1 = datetime.date.today()
sqlscript = '''
SELECT
p.PeopleId,
p.FamilyId,
p.Name2,
p.FirstName,
p.PreferredName,
p.MiddleName,
p.LastName,
p.SuffixCode,
p.NickName,
p.GenderId,
p.MaritalStatusId,
p.BDate,
p.WeddingDate,
p.AddressLineOne,
p.AddressLineTwo,
p.CityName,
p.StateCode,
p.ZipCode,
f.AddressLineOne AS FamilyAddressLineOne,
f.AddressLineTwo AS FamilyAddressLineTwo,
f.CityName AS FamilyCityName,
f.StateCode AS FamilyStateCode,
f.ZipCode AS FamilyZipCode,
p.HomePhone,
p.CellPhone,
p.WorkPhone,
p.ReceiveSMS,
p.EmailAddress,
p.EmailAddress2,
p.DecisionTypeId,
p.DecisionDate,
p.BaptismTypeId,
p.BaptismDate,
p.MemberStatusId,
p.JoinDate,
p.ContributionsStatement,
p.ElectronicStatement,
p.SpouseId,
p.DoNotPublishPhones,
rr.MedicalDescription Medical,
sp.PreferredName AS SpouseName,
CAST(CASE WHEN EXISTS(SELECT 1 FROM dbo.OrganizationMembers o WHERE o.PeopleId = p.PeopleId AND o.OrganizationId = 144 )
THEN 1
ELSE 0 END AS BIT) AS InPrayerChain,
CAST(CASE WHEN EXISTS(SELECT 1 FROM dbo.OrganizationMembers o WHERE o.PeopleId = p.PeopleId AND o.OrganizationId = 145 )
THEN 1
ELSE 0 END AS BIT) AS InGSBCNews,
CAST(CASE WHEN EXISTS(SELECT 1 FROM dbo.OrganizationMembers o WHERE o.PeopleId = p.PeopleId AND o.OrganizationId = 154 )
THEN 1
ELSE 0 END AS BIT) AS InMenEmail,
CAST(CASE WHEN EXISTS(SELECT 1 FROM dbo.OrganizationMembers o WHERE o.PeopleId = p.PeopleId AND o.OrganizationId = 156 )
THEN 1
ELSE 0 END AS BIT) AS InSOGEmail,
CAST(CASE WHEN EXISTS(SELECT 1 FROM dbo.OrganizationMembers o WHERE o.PeopleId = p.PeopleId AND o.OrganizationId = 160 )
THEN 1
ELSE 0 END AS BIT) AS InNurseryEmail,
CAST(CASE WHEN EXISTS(SELECT 1 FROM dbo.OrganizationMembers o WHERE o.PeopleId = p.PeopleId AND o.OrganizationId = 164 )
THEN 1
ELSE 0 END AS BIT) AS InKKEmail,
CAST(CASE WHEN EXISTS(SELECT 1 FROM dbo.OrganizationMembers o WHERE o.PeopleId = p.PeopleId AND o.OrganizationId = 155 )
THEN 1
ELSE 0 END AS BIT) AS InYouthEmail
FROM dbo.People p
JOIN dbo.Families f ON f.FamilyId = p.FamilyId
LEFT JOIN dbo.People sp ON sp.PeopleId = p.SpouseId
LEFT JOIN dbo.RecReg rr ON rr.PeopleId = p.PeopleId
JOIN dbo.TagPerson tp ON tp.PeopleId = p.PeopleId
WHERE tp.Id = @qtagid
OR p.PeopleId = 339
ORDER BY p.Name2
'''
template = '''
{{{D1}}}
{{#each this}}
<p align="right"><small> FAMILY ID:{{FamilyId}} - PERSON ID:{{PeopleId}}</small></p>
<h4 align="center"><strong>Contact Information Verification</strong></h4>
<table border-bottom: thin solid" cellspacing="0" cellpadding="0" align="left" width=100%>
<tbody>
<tr>
<td width="113" rowspan="2">
<p>
<strong>Name</strong>
</p>
</td>
<td style="border-bottom: thin solid" width="168" valign="top">
<p align="center">{{FirstName}}
</p>
</td>
<td style="border-bottom: thin solid" width="98" valign="top">
<p align="center">{{#if MiddleName}} {{MiddleName}}{{/if}}</p>
</td>
<td style="border-bottom: thin solid" width="166" valign="top">
<p align="center">{{LastName}}{{#if SuffixCode}}, {{SuffixCode}} &nbsp; &nbsp;{{/if}}</p>
</td>
<td width="39" valign="top">
</td>
<td style="border-bottom: thin solid" width="167" valign="top">
<p align="center">{{#if NickName}}{{NickName}}{{/if}}</p>
</td>
<td width="8">
</td>
</tr>
<tr>
<td width="168" valign="top">
<p align="center">
<em><small>First</small></em>
</p>
</td>
<td width="98" valign="top">
<p align="center">
<em><small>Middle</small></em>
</p>
</td>
<td width="166" valign="top">
<p align="center">
<em><small>Last</small></em>
</p>
</td>
<td width="39" valign="top">
<p align="center">
<em></em>
</p>
</td>
<td width="175" colspan="2" valign="top">
<p align="center">
<em><small>Nickname (if Different)</small></em>
</p>
</td>
</tr>
</tbody>
</table>
<table border="0" cellspacing="0" cellpadding="0" align="left" width=100%>
<tbody>
<tr><td>
<br>
</td></tr>
</tbody>
</table>
<table border="0" cellspacing="0" cellpadding="0" align="left" width=100%>
<tbody>
<tr>
<td width="106">
<p>
<strong>Birthdate:</strong>
<br/>
<u></u>
</p>
</td>
<td style="border-bottom: thin solid" width="164">
<p align="center">{{Fmt BDate "d"}}</p>
</td>
<td width="90">
<p>
<strong>Gender</strong>:
<br/>
<u></u>
</p>
</td>
<td style="border-bottom: thin solid" width="112">
<p align="center">
{{#IfEqual GenderId 1}}Male{{/IfEqual}}
{{#IfEqual GenderId 2}}Female{{/IfEqual}}
</p>
</td>
<td width="165" valign="top">
<p align="center">
<strong>Marital Status</strong>
:
<br/>
<strong> </strong>
Anniversary Date:
</p>
</td>
<td style="border-bottom: thin solid" width="135" valign="top">
<p align="center">
{{#IfEqual MaritalStatusId 10}}Single{{/IfEqual}}
{{#IfEqual MaritalStatusId 20}}
Married {{#if SpouseName}}&nbsp;<i> ({{SpouseName}}) </i> {{/if}}
</br> <small>{{Fmt WeddingDate "d"}} </small>
{{/IfEqual}}
{{#IfEqual MaritalStatusId 30}}Separated{{/IfEqual}}
{{#IfEqual MaritalStatusId 40}}Divorced{{/IfEqual}}
{{#IfEqual MaritalStatusId 50}}Widowed{{/IfEqual}}
<br>
</p>
</td>
</tr>
</tbody>
</table>
<table border="0" cellspacing="0" cellpadding="0" align="left" width=100%>
<tbody>
<tr>
<td><br></td>
</tr>
</tbody>
</table>
<table border="1" cellspacing="0" cellpadding="0" align="left" width=100%>
<tbody>
<tr>
<td width="390" valign="top">
<p align="center">
<strong>Family Address</strong>
</p>
</td>
<td width="390" valign="top">
<p align="center">
<strong>Personal Address </strong>
(if different)<strong></strong>
</p>
</td>
</tr>
<tr>
<td width="390">
<p align="center">
{{FamilyAddressLineOne}}<br>
{{#if FamilyZipCode}}
{{FamilyCityName}}, {{FamilyStateCode}} {{FmtZip FamilyZipCode}}
{{/if}}
</p>
</td>
<td width="390">
<p align="center">
{{AddressLineOne}} </BR>
{{AddressLineTwo}} </BR>
{{#if ZipCode}}
{{CityName}}, {{StateCode}} {{FmtZip ZipCode}}
{{/if}}
</p>
</td>
</tr>
</tbody>
</table>
<table border="0" cellspacing="0" cellpadding="0" align="left" width=100%>
<tbody>
<tr height="40">
<td style="border-bottom: thin solid" width="187">
<p>
<strong>Home Phone: </strong>
</p>
</td>
<td style="border-bottom: thin solid" width="270">
<p align="center">
<em>
{{#if HomePhone}} {{FmtPhone HomePhone ""}} {{/if}}
</em>
</p>
</td>
<td width="21" valign="top">
<p align="center">
<em></em>
</p>
</td>
<td width="242">
<p align="center">
<em></em>
</p>
</td>
<td width="68" valign="top">
<p align="center">
<em></em>
</p>
</td>
</tr>
<tr height="40">
<td style="border-bottom: thin solid" width="187">
<p>
<strong>Cell Phone:</strong>
</p>
</td>
<td style="border-bottom: thin solid" width="270">
<p align="center">
<em>
{{#if CellPhone}} {{FmtPhone CellPhone ""}} {{/if}}
</em>
</p>
</td>
<td width="21" valign="top">
<p align="center">
<em></em>
</p>
</td>
<td width="242">
<p align="center">
<em>Are you Willing to receive Text Messages <small>(Infrequent)</small>: </em>
</p>
</td>
<td style="border-bottom: thin solid" width="68">
<p align="center">
<strong>
{{#if ReceiveSMS}} Yes {{/if}}
</strong></p>
</td>
</tr>
<tr height="40">
<td style="border-bottom: thin solid" width="187">
<p>
<strong>Work/Other Phone:</strong>
</p>
</td>
<td style="border-bottom: thin solid" width="270">
<p align="center">
<em>
{{#if WorkPhone}} {{FmtPhone WorkPhone ""}} {{/if}}
</em>
</p>
</td>
<td width="21" valign="top">
<p align="center">
<em></em>
</p>
</td>
<td width="242">
<p align="center">
<em>Check if you do NOT want your Phone Numbers in the Church Directory:</em>
</p>
</td>
<td style="border-bottom: thin solid" width="68">
<p align="center">
<strong></em>
{{#if DoNotPublishPhones}} &#8730; {{/if}}
</strong>
</p>
</td>
</tr>
<tr height="40">
<td style="border-bottom: thin solid" width="187">
<p>
<strong>Primary Email:</strong>
</p>
</td>
<td style="border-bottom: thin solid" width="270">
<p align="center">
<em>
{{EmailAddress}}
</em>
</p>
</td>
<td width="21" valign="top">
<p align="center">
<em></em>
</p>
</td>
<td width="242" valign="top">
<p align="center">
<em></em>
</p>
</td>
<td width="68">
<p align="center">
<em></em>
</p>
</td>
</tr>
<tr height="40">
<td style="border-bottom: thin solid" width="187">
<p>
<strong>Alternate Email:</strong>
</p>
</td>
<td style="border-bottom: thin solid" width="270">
<p align="center">
<em>
{{EmailAddress2}}
</em>
</p>
</td>
<td width="21" valign="top">
<p align="center">
<em></em>
</p>
</td>
<td width="242" valign="top">
<p align="center">
<em></em>
</p>
</td>
<td width="68">
<p align="center">
<em></em>
</p>
</td>
</tr>
</tbody>
</table>
<table border="0" cellspacing="0" cellpadding="0" align="left" width=100%>
<tbody>
<tr><td>
<br>
</td></tr>
</tbody>
</table>
<table border="0" cellspacing="0" cellpadding="0" align="left" width=100%>
<tbody>
<tr height="25">
<td width="307">
<p>
Have you been Saved<em> : </em>
</p>
</td>
<td style="border-bottom: thin solid" width="165">
<p align="center">
<em>
{{#IfEqual DecisionTypeId 10}}Yes{{/IfEqual}}
{{#IfEqual DecisionTypeId 20}}Yes{{/IfEqual}}
{{#IfEqual DecisionTypeId 30}}Yes{{/IfEqual}}
{{#IfEqual DecisionTypeId 40}}Yes{{/IfEqual}}
{{#IfEqual DecisionTypeId 50}}Yes{{/IfEqual}}
</em></p>
</td>
<td width="150">
<p align="center">
Date*:
</p>
</td>
<td style="border-bottom: thin solid" width="150">
<p align="center">
{{Fmt DecisionDate "d"}}
</p>
</td>
</tr>
<tr height="25">
<td width="307">
<p>
Have you been Baptized (Immersion)<em>: </em>
</p>
</td>
<td style="border-bottom: thin solid" width="165">
<p align="center">
<em>
{{#IfEqual BaptismTypeId 10}}Yes{{/IfEqual}}
{{#IfEqual BaptismTypeId 20}}Yes{{/IfEqual}}
{{#IfEqual BaptismTypeId 30}}Yes{{/IfEqual}}
{{#IfEqual BaptismTypeId 40}}Yes{{/IfEqual}}
</em></p>
</td>
<td width="150">
<p align="center">
Date*:
</p>
</td>
<td style="border-bottom: thin solid" width="150">
<p align="center">
{{Fmt BaptismDate "d"}}
</p>
</td>
</tr>
<tr height="30">
<td width="307">
<p>
Church Membership Status<em>: </em>
</p>
<p>
<em><small>If you are not yet a Member at GSBC, Are you at another Church:</small></em>
<em></em>
</p>
</td>
<td style="border-bottom: thin solid" width="165">
<p align="center">
{{#IfEqual MemberStatusId 10}}Member{{/IfEqual}}
</p>
</td>
<td width="150">
<p align="center">
Date*:
</p>
</td>
<td style="border-bottom: thin solid" width="150">
<p align="center">
{{Fmt JoinDate "d"}}
</p>
</td>
</tr>
</tbody>
</table>
<table border="0" cellspacing="0" cellpadding="0" align="left" width=100%>
<tbody>
<tr><td>
<p align="right">
<em><small>*Approximate Dates are acceptable</small></em>
</p>
<br>
</td></tr>
</tbody>
</table>
<table border="0" cellspacing="0" cellpadding="0" align="left" width=100%>
<tbody>
<tr>
<td width="30%">
<table cellpadding="0" cellspacing="0" width="100%">
<tbody>
<tr>
<td>
<div>
<tr>
<td>
<strong>Please register me for:</strong>
</td></tr>
<tr><td>
{{#if InGSBCNews}} <p> &nbsp; &#8730; &nbsp; GSBC News Email List</p> {{else}} <p> ____ GSBC News Email List</p> {{/if}}
</td></tr>
<tr><td>
{{#if InPrayerChain}} <p> &nbsp; &#8730; &nbsp; GSBC Prayer Chain Email List</p> {{else}} <p> ____ GSBC Prayer Chain Email List</p> {{/if}}
</td></tr>'
<tr><td>
{{#if InMenEmail}} <p> &nbsp; &#8730; &nbsp; Men\'s Ministry Email List</p> {{else}} <p> ____ Men\'s Ministry Email List</p> {{/if}}
</td></tr>
<tr><td>
{{#if InSOGEmail}} <p> &nbsp; &#8730; &nbsp; Sisters of Grace Email List</p> {{else}} <p> ____ Sisters of Grace Email List</p> {{/if}}
</td></tr>
<tr><td>
{{#if InNurseryEmail}} <p> &nbsp; &#8730; &nbsp; Nursery Email List</p> {{else}} <p> ____ Nursery Email List</p> {{/if}}
</td></tr>
<tr><td>
{{#if InKKEmail}} <p> &nbsp; &#8730; &nbsp; Kingdom Kids Email List</p> {{else}} <p> ____ Kingdom Kids Email List</p> {{/if}}
</td></tr>
<tr><td>
{{#if InYouthEmail}} <p> &nbsp; &#8730; &nbsp; Youth Ministry Email List</p> {{else}} <p> ____ Youth (Parents) Email List</p> {{/if}}
</td></tr>
</div>
</td>
</tbody>
</table>
<td style="border: thin solid" valign="top">
<div>
<p>
<strong> &nbsp; Notes </strong><em>(Incl. Medical/Allergies)</em>:
</p> &nbsp;
{{Medical}}
</div>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
<table border="0" cellspacing="0" cellpadding="0" align="left" width=100%>
<tbody>
<tr><td>
<p align="center">
<img
width="125"
height="37"
src="https://c4265878.ssl.cf2.rackcdn.com/gsbcfamily.1505071802.GSBC_cms.png"
alt="http://www.gsbcfamily.org/GSBC_Logos/GSBC_cms.png"
/>
<em>
Note: This data is also accessible at GSBCfamily.TPSDB.com
</em>
</p>
</td></tr>
</tbody>
</table>
<p style="page-break-before: always">
{{/each}}
'''
sqlpeople = q.QuerySql(sqlscript)
body = model.RenderTemplate(template, sqlpeople)
peopleold = q.BlueToolbarReport()
print body
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment