GivingHouseholdsData Python Script

View the Code

Suggestion: Right-click the link and use the “Open link in a new window” selection, then arrange the windows so you can view them side by side.

Automate this Script

To get this script to run every morning automatically, you can put it into the MorningBatch Python script.

Open the existing or create a new Python script called MorningBatch.

Add the following line of code.

model.CallScript("GivingHouseholdsData")

Save the script. It will run the next morning during the 4:30 - 5:30 time window. Of course you can always manually run this script whenever you want.

Explanation of the Code

Line 1 allows only those with the role Finance to run this script.

Line 3 allows the code to use .NET DateTime functionality.

Line 5 loads the SQL script used to collect the data for the report. See model.SqlContent().

Lines 7-8 create a new instance of a top-level DynamicData object called data, and a new instance of a second-level DynamicData object called QueryCode.

Lines 10-25 store three versions of QueryCode used to identify Members, NonMembers and Combined. All three use the IncludeDeceased = 1 directive. This way givers in the last year who have since deceased will have their families and gifts included.

The NonMembers query finds those family members where either of the primary adults has attended in the last year or given in the last year and neither primary adult is a member of the church.

The data.QueryCode.Combined query utilizes the QueryTags created for Members and NonMembers and combines them.

Note

The triple ‘’’ quotation marks allow for multi-line text for readability. But the model.Replace removes all whitespace (including newlines) and replaces it with a single space. Finally the strip() function removes leading and trailing spaces. This way the code can be uses as part of a QueryString as a single line.

Line 27 deletes all QueryTags using a wild card (%) character to match multiple QueryTags.

Lines 28-30 creates the three QueryTags using the QueryCode created above.

Line 32 stores the current date and time in the data structure.

Lines 34-38 define a function that uses the SQL code and startdt and endt parameters to fetch data.

Lines 40-42 create three sets of data for Year-to-date, past 30 days, and past 365 days.

Lines 44-45 convert the data to a JSON string and save it into a TextFile called GivingHouseholdsData. This file is used as a cache to make the report run quickly throughout the day.

Lines 47-49 print the JSON text to the screen to indicate the process is finished.