RecentInactiveToday.py

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
#---------------------------------------------------
# Constants
StatusFlag = 'F55:RecentInactive' # put in your status flag saved query
StatusFlagDescription = '''
    They have attended something 12 or more times in the past year,
    but have not attended anything in the past 60 days.
'''
Subject     = 'Classified Inactive Today'
MailToIds   = '828612,819918' # a list of peopleids of recipients (comma separated)
SenderId    = 819918 # the peopleid of the sender (may be an assistant)
FromEmail   = 'karen@tpsdb.com' # the reply to address
FromName    = 'Karen Worrell' # the person's name who the email is from
DayToRun    = 1 # 0=Sun,6=Sat. 7=run every day
#---------------------------------------------------

MailTo = "peopleids='{}'".format(MailToIds)
CorrectDay = model.DayOfWeek == DayToRun or DayToRun == 7
DaysToLookBack = 7 if DayToRun < 7 else 0

query = "RecentFlagAdded( Days={0} ) = '{1}'".format(DaysToLookBack, StatusFlag)
tagid = q.TagQueryList(query)
count = q.TagCount(tagid)

sql = '''
SELECT
    la.PeopleId
    ,la.Name2
    ,la.LastAttend
    ,la.OrganizationId
    ,la.OrganizationName
    ,o.LeaderId
    ,o.LeaderName
    ,p.Age
    ,ms.Description AS MemberStatus
    ,ma.Description AS MaritalStatus
    ,la.HomePhone
    ,la.CellPhone
    ,la.EmailAddress AS Email
FROM LastAttends la
JOIN dbo.People p ON p.PeopleId = la.PeopleId
JOIN dbo.Organizations o ON o.OrganizationId = la.OrganizationId
JOIN lookup.MemberStatus ms ON ms.Id = p.MemberStatusId
JOIN lookup.MaritalStatus ma ON ma.Id = p.MaritalStatusId
JOIN TagPerson tp ON la.PeopleId = tp.PeopleId AND tp.Id = @p1
--whereclause
ORDER BY p.Age, p.Name2
'''
marriedSql = sql.replace('--whereclause', "WHERE ma.Description = 'Married'")
singleSql = sql.replace('--whereclause', "WHERE ma.Description <> 'Married'")

template = '''
<div>
  {{#each this}}
  <p>
    <a href='{{ServerLink}}/Person2/{{PeopleId}}' target="person">
        <strong>{{Name2}}</strong>
    </a> &nbsp;&nbsp; {{Age}}, {{MaritalStatus}}, {{MemberStatus}}
    <br>
    <div style="margin-left:1em;">
        Last Attend: <b>{{LastAttend}}</b>
        <a href='{{ServerLink}}/Org/{{OrganizationId}}'>
            <em>{{OrganizationName}}</em>
        </a> &mdash;
        {{#if LeaderId}}
            <a href='{{ServerLink}}/Person2/{{LeaderId}}' target="person">
            {{LeaderName}}</a>
        {{else}}
            No Leader
        {{/if}}
        {{#if HomePhone}}<br>H {{HomePhone}}{{/if}}
        {{#if CellPhone}}<br>C {{CellPhone}}{{/if}}
        {{#if Email}}<br><a href='mailto:{{Email}}'>{{Email}}</a>{{/if}}
    </div>
  </p>
  {{/each}}
</div>
'''
page = '''
<div>
    <h2>The following have been classified Inactive today</h2>
    <p>{0}</p>
    <h3>Single</h3>
    {1}
    <h3>Married</h3>
    {2}
</div>
'''

def ConstructReport():
    marriedList = q.QuerySql(marriedSql, tagid)
    singleList = q.QuerySql(singleSql, tagid)

    Single = model.RenderTemplate(template, singleList)
    Married = model.RenderTemplate(template, marriedList)

    return page.format(StatusFlagDescription, Single, Married)

print "<p>Count: {}</p>".format(count)
if count > 0:
    body = ConstructReport()
    if model.FromMorningBatch and CorrectDay:
        model.Email(MailTo, SenderId, FromEmail, FromName, Subject, body)
    print body

© 2019 TouchPoint Software, LLC.  All rights reserved.