-
-
Save nonsensecreativity/775e82ecb8b333f12a49d8b689ba137f to your computer and use it in GitHub Desktop.
Testing handlebars solution to DataVerification Report
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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}} {{/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}} <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}} √ {{/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> √ GSBC News Email List</p> {{else}} <p> ____ GSBC News Email List</p> {{/if}} | |
| </td></tr> | |
| <tr><td> | |
| {{#if InPrayerChain}} <p> √ GSBC Prayer Chain Email List</p> {{else}} <p> ____ GSBC Prayer Chain Email List</p> {{/if}} | |
| </td></tr>' | |
| <tr><td> | |
| {{#if InMenEmail}} <p> √ Men\'s Ministry Email List</p> {{else}} <p> ____ Men\'s Ministry Email List</p> {{/if}} | |
| </td></tr> | |
| <tr><td> | |
| {{#if InSOGEmail}} <p> √ Sisters of Grace Email List</p> {{else}} <p> ____ Sisters of Grace Email List</p> {{/if}} | |
| </td></tr> | |
| <tr><td> | |
| {{#if InNurseryEmail}} <p> √ Nursery Email List</p> {{else}} <p> ____ Nursery Email List</p> {{/if}} | |
| </td></tr> | |
| <tr><td> | |
| {{#if InKKEmail}} <p> √ Kingdom Kids Email List</p> {{else}} <p> ____ Kingdom Kids Email List</p> {{/if}} | |
| </td></tr> | |
| <tr><td> | |
| {{#if InYouthEmail}} <p> √ 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> Notes </strong><em>(Incl. Medical/Allergies)</em>: | |
| </p> | |
| {{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