Prayer Digest¶

The Prayer Digest can function as an automated email or as a stand-alone report or as both. The report shows prayer requests that have been made online over the past specified number of days and can be added to the Reports menu for easy access. The automated email contains the same information as the report and is sent on the specified schedule to the specified recipient(s).

The Prayer Digest requires a SQL script and a Python script as shown below. The report can be customized as described in the following two sections.

SQL Script¶

Below is the SQL script for the Prayer Digest. Because it is called by the corresponding Python script, it is important to name the file you create exactly PrayerDigest (no space between the words and both words capitalized).

Add the SQL Script¶

Step 1

Go to Administration > Setup > Special Content > SQL Scripts.

Step 2

Click the green + New SQL Script File, enter name the file PrayerDigest and click Submit.

Step 3

Copy all the code below and paste it into the new file.

Click Save Script.

--roles=Admin

declare @fd date = dateadd(day, -convert(int,@daysback), getdate())

select
	format(tn.CreatedDate, 'MMM d h:mm tt') Date,
	tn.AboutPersonId AboutId,
	case when exists (select null from dbo.TaskNoteKeyword nk 
		join dbo.Keyword k on k.KeywordId = nk.KeywordId
		where k.Description in ('Prayer Request Unauthenticated','Anonymous Prayer Request')
		and nk.TaskNoteId = tn.TaskNoteId) then 'Anonymous' else a.Name end Submitter,
	coalesce(tn.Instructions,tn.Notes) Request
from dbo.TaskNote tn
join dbo.People c on c.PeopleId = tn.CreatedBy
join dbo.People a on a.PeopleId = tn.AboutPersonId
where exists (select null from dbo.TaskNoteKeyword nk 
		join dbo.Keyword k on k.KeywordId = nk.KeywordId
		where k.Description in ('Mobile Prayer Request','Prayer Request Unauthenticated','Anonymous Prayer Request')
		and nk.TaskNoteId = tn.TaskNoteId)
and tn.CreatedDate > @fd
order by tn.CreatedDate

Customize the SQL Script¶

The only change you might need to make to the SQL script concerns the role(s) required to run the script. This is configured on the first line. As written, the script is limited to users who have the Admin role, but you can change this to another role. For example, you may have created a custom role called PrayerRequests that a user must have in order to view prayer requests. If so, you will want to replace Admin on line one with the name of your custom role.

Python Script¶

Below is the Python script for the Prayer Digest.

Add the Python Script¶

Step 1

Go to Administration > Setup > Special Content > Python Scripts.

Step 2

Click the green + New Python Script File, enter name the file PrayerDigestReport and click Submit.

Step 3

Copy all the code below and paste it into the new file.

Click Save Script.

#roles=Admin

#---------------------------------------------------
# Constants
Subject     = 'Prayer Digest'
MailToIds   = '1234'  # a single peopleid in quotes or the name of a saved search in quotes
SenderId    = 3456    # the peopleid of the sender (may be an assistant)
FromEmail   = 'PrayerMinistry@mychurch.com'  # the reply to address
FromName    = 'Prayer Ministry'  # the person's name who the email is from
DayToRun    = 1  # 0=Sun,6=Sat. 7=run every day
DaysBack    = 30  # include prayer requests this many days back
#---------------------------------------------------

CorrectDay = model.DayOfWeek == DayToRun or DayToRun == 7

template = '''
<div>
    <h3>The following prayer requests have been received in the past 
        {{daysBack}} day{{IfNotEqual daysBack 1}}s{{/IfNotEqual}}:</h3>
</div>
<div>
  {{#each prayerList}}
  <p>   
    {{IfEqual Submitter 'Anonymous'}}
        <strong>{{Submitter}}</strong>
    {{else}}
        <strong><a href='{{ServerLink}}/Person2/{{AboutId}}#tab-touchpoints' target="_blank">
            {{Submitter}}</a></strong>
    {{/IfEqual}}<br><em>{{Date}}</em>
    <br>{{Request}}<br>
  </p>
  {{/each}}
</div>
'''

def ConstructReport():
    Data.daysBack = DaysBack
    psql = model.Content("PrayerDigest")
    params = { 'daysback' : DaysBack }
    Data.prayerList = q.QuerySql(psql, params)
    Requests = model.RenderTemplate(template, Data)

    return Requests

body = ConstructReport()
if model.FromMorningBatch and CorrectDay:
    model.Email(MailToIds, SenderId, FromEmail, FromName, Subject, body)
else:
    print body

Customize the Python Script¶

With this python script, you can configure the role(s) necessary for a user to run it. If you want to change the role, simply replace Admin on line one with the name of the role to which you wish to limit this script.

On lines 5 - 10 of the script, you can customize the following settings for the automated email:

Subject: Enter the subject the automated email should have.
MailToIds: Enter here the PeopleId(s) of the recipient(s) of the automated email. Make sure to retain the quotation marks around the list.
SenderId: Enter the PeopleId of the person the email should come from.
FromEmail: Enter the email address of the person the email should come from.
FromName: Enter the name of the person the email should come from.
DayToRun: If the automated email should be run every day, enter 7. Otherwise, enter the day of the week it should be sent, with 0 = Sunday, 1 = Monday, … , 6 = Saturday.
DaysBack: Enter the number of days back to include in the report / automated email.

Manually Run the Report¶

To run the report manually, open the Python script and click the Run Script button. After running the report, you can click the Add Report to Menu link to add the report to the Reports menu. Thereafter, you can run the report by clicking its option in the menu.

Automate the email¶

To finalize the setup for the automated email, add the following line to your MorningBatch python script: model.CallScript("PrayerDigestReport"). (If you do not already have a MorningBatch script, you can create it on the Special Content > Python Scripts tab.)

You should also ensure that the setting RunMorningBatch is set to True. This setting is found at Administration > Settings, on the System tab, and in the Administration section.

Latest Update

05/02/2022

Added this article.