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 = '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> {{Age}}, {{MaritalStatus}}, {{MemberStatus}}
<br>
<div style="margin-left:1em;">
Last Attend: <b>{{LastAttend}}</b>
<a href='{{ServerLink}}/Org/{{OrganizationId}}'>
<em>{{OrganizationName}}</em>
</a> —
{{#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
|
