Recent Inactive Today =========================== This recipe will allow you to track people who have been active over the last year, but have recently become inactive for some reason. This presents an opportunity to minister to those persons. There may be a spiritual problem or perhaps they have moved, or joined another church. Of course, you will want to have policies in place and ministers who will reach out to these people. The code will Email a report to selected leaders. Installation ------------ #. 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 `F55:RecentInactive`, but you can use your own number and name. .. admonition:: 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. .. seealso:: :doc:`/People/StatusFlags` #. Create an `EmailInactiveToday` Python script file #. Copy and Paste the code from this text file (it will download onto your computer) :download:`Files/`. #. Modify the Constants section at the top of the script for your church. #. You can manually update your Status Flags from the menu with `Admin > Batch Actions > Update Status Flags` #. 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. #. 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. .. literalinclude:: Files/ :lines: 2-13 :linenos: :lineno-start: 2 * 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. .. literalinclude:: Files/ :lines: 16-18 :linenos: :lineno-start: 16 .. note:: If the ``DaysToLookBack`` variable is 7, then that means that you want to run the code on only one day a week, say on a Tuesday after all attendance has been recorded on Monday. Then, those for whom the status flag has been set on their record in the past 7 days, will be included on the report. If the ``DaysToLookBack`` is 0, then that means you will be running the code everyday, and you will only want to report on anybody for whom the status flag has just been set on that day. * 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. .. literalinclude:: Files/ :lines: 20-22 :linenos: :lineno-start: 20 .. seealso:: :func:`q.TagQueryList` and :func:`q.TagCount` 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. .. literalinclude:: Files/ :language: SQL :lines: 25-46 :linenos: :lineno-start: 25 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. .. literalinclude:: Files/ :lines: 48-49 :linenos: :lineno-start: 48 Lines 51-77 create an HTML template which will be used to render the report using ``{{handlebars}}`` as placeholders. .. literalinclude:: Files/ :language: HTML :lines: 52-76 :linenos: :lineno-start: 52 .. seealso:: :doc:`../Model/Handlebars` The ``page`` string creates an HTML wrapper for dividing the recent inactives into two reports, one for Single and the other for Married. .. literalinclude:: Files/ :language: HTML :lines: 79-86 :linenos: :lineno-start: 79 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. .. seealso:: :func:`q.QuerySql` * Lines 93 and 94 render the HTML reports for the Married and Single lists. .. seealso:: :func:`model.RenderTemplate` * Line 96 combines the two reports using the ``page`` wrapper and the resulting string becomes the return value of the function. .. literalinclude:: Files/ :lines: 89-96 :linenos: :lineno-start: 89 * 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. .. seealso:: :func:`model.Email` * Finally, the body of the email is printed on the screen for the interactive user. .. literalinclude:: Files/ :lines: 98-103 :linenos: :lineno-start: 98 .. seealso:: :doc:`Files/RecentInactiveScript`