Recent Inactive Today¶

Installation¶

1. Create a StatusFlag query such as the following:

   RecentAttendCount( Days=365 ) >= 12
   AND RecentAttendCount( Days=60 ) = 0
   

   This query finds those people who have had attendance recorded at any event or meeting 12 or more times in the past year. But have not had any attendance recorded in the past 60 days. You can use whatever numbers and criteria you want. This must be a StatusFlag query so we can track when the flag became active for a person. In this script, we assume the Flag is named RecentInactive, but you can use your own number and name.

   Why use a Status Flag?

   The reason a StatusFlag query is used is because they run every day and they use a special Tag behind the scenes. When a person is tagged with a Tag, the date and time they are added to the tag is stored and that is how we know when they became inactive.

   See also

   Status Flags

2. Create an EmailInactiveToday Python script file

3. Copy and Paste the code from this text file (it will download onto your computer) Files/RecentInactiveToday.py.

4. Modify the Constants section at the top of the script for your church.

5. You can manually update your Status Flags from the menu with Admin > Batch Actions > Update Status Flags

6. Test the script by running it interactively. It will only print the list on the screen and will not email recipients unless it is run from the morning batch process.

7. Put the following somewhere in the MorningBatch Python script:

   model.CallScript('EmailInactiveToday')
   

Explanation of the code¶

The top section of the code is where all the constants for your church are located. You will want to modify these. Clients may contact support for help with these settings if you need it.

 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# 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

• Line 16 below creates the query which will get the list of recipients

• Line 17 saves an indicator in CorrectDay whether the report should be emailed this day or not.

• Line 18 determines whether the report is weekly or daily.

16
17
18

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

• Line 20 below constructs the query using DaysToLookBack and your StatusFlag.

• Then line 21 executes the query, creating a temporary tag which is saved in tagid.

• And line 22, gets the count of people in the tag which will be used later to determine whether to email anything or not.

20
21
22

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

The SQL script below will be used to return a list data about the inactive people. Note two items to be replaced in this SQL string:

• The @p1 parameter on line 44. This will use the tagid we created and will serve to filter the query to just those inactive today.

• The --whereclause comment text on line 45. This will be used to further filter the results into two sets, married and single.

25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

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

Next, on lines 48 and 49, we create two different SQL statements, both using the original SQL as the starting point. The --whereclause comment is replaced with code to select just marrieds, and then just singles.

48
49

marriedSql = sql.replace('--whereclause', "WHERE ma.Description = 'Married'")
singleSql = sql.replace('--whereclause', "WHERE ma.Description <> 'Married'")

Lines 51-77 create an HTML template which will be used to render the report using {{handlebars}} as placeholders.

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

<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>

The page string creates an HTML wrapper for dividing the recent inactives into two reports, one for Single and the other for Married.

79
80
81
82
83
84
85
86

<div>
    <h2>The following have been classified Inactive today</h2>
    <p>{0}</p>
    <h3>Single</h3>
    {1}
    <h3>Married</h3>
    {2}
</div>

Next a function called ConstructEmailReport is defined.

• Lines 90 and 91 retrieve the data for the married and single lists from the two SQL queries created earlier on line 45. They are also filtered by the tag created on line 18.

  See also

  q.QuerySql()

• Lines 93 and 94 render the HTML reports for the Married and Single lists.

  See also

  model.RenderTemplate()

• Line 96 combines the two reports using the page wrapper and the resulting string becomes the return value of the function.

89
90
91
92
93
94
95
96

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)

• Line 98 prints the count which is displayed if the report is run interactively (not in batch)

• Then if there will results to show (count > 0), The Report is constructed on line 100 and saved in body

• Line 101 determines whether the email should be sent on line 102.

  See also

  model.Email()

• Finally, the body of the email is printed on the screen for the interactive user.

 98
 99
100
101
102
103

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

Removed F55 since no longer applicable.