GivingHouseholdsData Python Script

To install this script, copy all of the code below. Create a new Python document in Special Content using the name GivingHouseholdsData. Then paste the code into editor and save.

 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
#roles=Finance

from System import DateTime

Sql = model.SqlContent('GivingHouseholds')

data = model.DynamicData()
data.QueryCode = model.DynamicData()

data.QueryCode.Members = 'FamHasPrimAdultChurchMemb = 1 AND IncludeDeceased = 1'
data.QueryCode.NonMembers = model.Replace('''
    ( 
        RecentFamilyAdultLastAttend( Days=365 ) = 1 
        OR IsFamilyGiver( Days=365 ) = 1
    ) 
    AND FamHasPrimAdultChurchMemb = 0 
    AND IncludeDeceased = 1
''', '\s+', ' ').strip()
data.QueryCode.Combined = model.Replace('''
    (
        QueryTag = 'GivingHouseholds-Members' 
        OR QueryTag = 'GivingHouseholds-ActiveNonMembers'
    ) 
    AND IncludeDeceased = 1
''', '\s+', ' ').strip()

model.DeleteQueryTags('GivingHouseholds%')
model.CreateQueryTag('GivingHouseholds-Members', data.QueryCode.Members)
model.CreateQueryTag('GivingHouseholds-ActiveNonMembers', data.QueryCode.NonMembers)
model.CreateQueryTag('GivingHouseholds-Combined', data.QueryCode.Combined)

data.RunDateTime = model.DateTime.ToString('g') # record when data was created

def FetchData(startdt):
    global Sql
    d = q.SqlFirstColumnRowKey(Sql, {'startdt': startdt, 'enddt': model.DateTime})
    d.Days = DateTime.Today.Subtract(startdt).Days
    return d

data.Ytd = FetchData(DateTime(DateTime.Today.Year, 1, 1))
data.D30 = FetchData(DateTime.Today.AddDays(-30))
data.D365 = FetchData(DateTime.Today.AddDays(-365))

savedata = model.FormatJson(data)
model.WriteContentText('GivingHouseholdsData', savedata)

print '<pre>'
print savedata
print '</pre>'

# the following is only used in development 
#Sql = model.Content('C:/dev/bvcmsdocs/source/CustomProgramming/Python/Scripts/Giving/Households/Files/Content/GivingHouseholds.sql')
#runfrom=C:/dev/bvcmsdocs/source/CustomProgramming/Python/Scripts/Giving/Households/Files/Content/GivingHouseholdsData.py