def check_for_constituents(EARL):
try:
""""--------GET THE TOKEN------------------"""
current_token = fn_do_token()
"""-----Get a list of constituents with a custom field of
Student Status - STORE the id in cvid_rec-------"""
"""---We need this to match Carthage ID to Blackbaud ID------"""
"""
UPDATE 1/17/20 It will more likely be the case that we will get
a csv list from advancement of the students added. If so, we
can read that csv and find the BB_ID only for those students"""
"""The date of the last search will be stored in Cache"""
#searchtime = cache.get('last_const_date')
searchtime = cache.get('Sql_date')
# API call to get BB ID
x = get_constituents_custom_field_list(current_token, str(searchtime))
if x == 0:
print("No recent student entries in RE")
else:
print(x)
for i in x['value']:
bb_id = i["parent_id"]
# Look for ID in cvid_rec
chk_sql = '''select cx_id, re_api_id from cvid_rec
where re_api_id = {}'''.format(i['parent_id'])
connection = get_connection(EARL)
with connection:
data_result = xsql(chk_sql, connection, key='debug')
x = data_result.fetchone()
# Create the cvid_rec if it doesn't exist - Will require
# second call to API to retrieve the carthage id using
# the blackbaud id
if x is None:
carth_id = get_lookup_id(current_token, bb_id)
ret = fn_update_local(carth_id, bb_id, EARL)
else:
print("CVID Rec exists for" + str(x[0]))
# carth_id = x[0]
pass
except Exception as e:
# print("Error in main: " + repr(e))
sqlstate = e.args[1]
# print(sqlstate)
fn_write_error("Error in sky_constituent_list.py - Main: "
+ repr(e))
fn_send_mail(settings.BB_SKY_TO_EMAIL,
settings.BB_SKY_FROM_EMAIL, "SKY API ERROR", "Error in "
"sky_constituent_list.py - for: " + + repr(e))
def donors(request, appeal):
"""Display donors for an appeal campaign."""
earl_appeal = '{0}/fundraising/v1/appeals/{1}'.format(
settings.BB_SKY_API_URL,
appeal,
)
key_campaign = 'campaigns_{0}'.format(appeal)
earl_gift = '{0}/gift/v1/gifts?appeal_id={1}&limit=2500'.format(
settings.BB_SKY_API_URL,
appeal,
)
donors = []
current_token = fn_do_token()
campaign = cache.get(key_campaign)
if not campaign:
campaign = api_get(current_token, earl_appeal)
cache.set(key_campaign, campaign)
# fetch the appeal campaign
campaign = cache.get(key_campaign)
if not campaign:
campaign = api_get(current_token, earl_appeal)
cache.set(key_campaign, campaign)
# fetch all donations
gifts = api_get(current_token, earl_gift)
for gift in gifts['value']:
cid = gift['constituent_id']
key_constituent = 'constituents_{0}'.format(cid)
constituent = cache.get(key_constituent)
if not constituent:
constituent = api_get(
current_token, '{0}/constituent/v1/constituents/{1}'.format(
settings.BB_SKY_API_URL,
cid,
))
cache.set(key_constituent, constituent)
if constituent not in donors and constituent.get('last'):
donors.append(constituent)
return render(
request,
'donors/index.html',
{
'donors': donors,
'count': len(donors)
},
)
def main():
try:
# set global variable
global EARL
datetimestr = time.strftime("%Y%m%d%H%M%S")
# Defines file names and directory location
RE_STU_LOG = settings.BB_LOG_FOLDER + 'RE_student_status' \
+ datetimestr + ".txt"
# print(RE_STU_LOG)
# determines which database is being called from the command line
if database == 'cars':
EARL = settings.INFORMIX_ODBC
elif database == 'train':
EARL = settings.INFORMIX_ODBC_TRAIN
# print(EARL)
""""--------GET THE TOKEN------------------"""
current_token = fn_do_token()
# print(current_token)
"""
-----------------------------------------------------------
-1-GET STUDENTS WITH A STATUS CHANGE FROM PROG_ENR_REC-----
Assume all current blackbaud students have a re_api_id in the
cvid_rec table. This will be handled through a separate prior
process.
Look for status changes only for students who have the
re_api_id entry
-----------------------------------------------------------
"""
"""THis query looks for recent changes in the student status.
We do not want to use any records that do NOT have an re_api_id
value. It only applies to RE entered students at present"""
"""To get the last query date from cache"""
# last_sql_date = cache.get('Sql_date')
# print(last_sql_date)
"""For periodic multi student runs, only want status for the
current term"""
statquery = '''select SAR.id, SAR.ACST, '', '', CVR.cx_id,
SAR.acst, '','', CVR.cx_id, CVR.re_api_id, ''
,SAR.yr, SAR.sess, SAR.cl
from cvid_rec CVR
JOIN STU_ACAD_REC SAR
on CVR.cx_id = SAR.id
where CVR.re_api_id is not null
AND SAR.acst not in ('PAST')
and SAR.yr in (Select yr from cursessyr_vw)
and SAR.sess in (select sess from
cursessyr_vw)
AND SAR.cl = 'SR'
--and SAR.id = 1586698
'''
# print(statquery)
connection = get_connection(EARL)
with connection:
data_result = xsql(statquery, connection).fetchall()
ret = list(data_result)
if ret:
for i in ret:
# print(str(i[8]) + " " + i[5] + " " + str(i[9]))
carth_id = i[8]
acad_stat = i[5]
bb_id = i[9]
# print(bb_id)
"""
Look for student and status in local table
Else look for student and status at BB via API
Add to BB if necessary (THIS WILL BE DONE BY OMATIC)
Add or update status in BB
Update local table if necessary
"""
'''------------------------------------------------
--2-UPDATE THE CUSTOM STUDENT STATUS
FIELD-------
---------------------------------------------------
'''
if bb_id != 0:
# print("Update custom field")
# Get the row id of the custom field record
field_id = get_const_custom_fields(
current_token, bb_id, 'Student Status')
# print("set custom fields: " + str(carth_id) + ", "
# + acad_stat)
"""ret is the id of the custom record, not the
student"""
if field_id == 0:
# print("Error in student_status_term.py - for: "
# + str(carth_id) + ", Unable to get "
# "the custom field")
fn_write_error(
"Error in student_status_term.py - for: " +
str(carth_id) + ", Unable to get the "
"custom field")
fn_send_mail(
settings.BB_SKY_TO_EMAIL,
settings.BB_SKY_FROM_EMAIL, "SKY API ERROR",
"Error in student_status.py - for: " +
str(carth_id) +
", Unable to get the custom field")
pass
else:
ret1 = update_const_custom_fields(
current_token, str(field_id), 'CX Status '
'Update', acad_stat)
if ret1 == 0:
# print("set custom fields: " + str(carth_id)
# + ", " + acad_stat)
f = open(RE_STU_LOG, "a")
f.write("set custom fields: " + str(carth_id) +
", " + acad_stat + '\n')
f.close()
else:
print("Patch failed")
else:
print("Nobody home")
pass
# print("Process complete")
# fn_send_mail(settings.BB_SKY_TO_EMAIL,
# settings.BB_SKY_FROM_EMAIL, "SKY API",
# "New records processed for Blackbaud: ")
else:
print("No changes found")
fn_send_mail(settings.BB_SKY_TO_EMAIL,
settings.BB_SKY_FROM_EMAIL, "SKY API",
"No new records for Blackbaud: ")
except Exception as e:
print("Error in main: " + str(e))
fn_write_error("Error in student_status_term.py - Main: " + repr(e))
fn_send_mail(settings.BB_SKY_TO_EMAIL, settings.BB_SKY_FROM_EMAIL,
"SKY API ERROR", "Error in "
"student_status.py - for: " + +repr(e))
def main():
try:
# set global variable
global EARL
# determines which database is being called from the command line
if database == 'cars':
EARL = settings.INFORMIX_ODBC
if database == 'train':
EARL = settings.INFORMIX_ODBC_TRAIN
# if database == 'sandbox':
# EARL = settings.INFORMIX_ODBC_SANDBOX
else:
# # this will raise an error when we call get_engine()
# below but the argument parser should have taken
# care of this scenario and we will never arrive here.
EARL = None
# establish database connection
""""--------GET THE TOKEN------------------"""
current_token = fn_do_token()
# print("Current Token = ")
# print(current_token)
"""
-----------------------------------------------------------
-1-GET STUDENTS WITH A STATUS CHANGE FROM PROG_ENR_REC-----
-----------------------------------------------------------
"""
# for real..
# Two options. Get all changed records, look for local BB ID but ALSO
# look for BB ID via API. If there is a record in BB, then add the
# BB ID locally if it doesn't exist.
# OR
# Ignore all changes locally that do not match an existing local BB ID
# The latter would be the lowest hits on the API
statquery = '''select c.id, rr.constituent_id, rr.reciprocal_id,
rrt.value, rrt.code,
rt.code, rt.value
from constituent c
join relationship_role rr
on rr.constituent_id = c.id
join relationship r
on r.id = rr.relationship_id
join relationship_role_type rrt
on rrt.id= rr.reciprocal_type_id
join relationship_type rt
on rt.id = rrt.relationship_type_id
where c.id in ( select DISTINCT CR.cx_id --, SAR.yr, SAR.sess, SAR.acst
from train:cvid_rec CR
join stu_acad_rec SAR on SAR.id = CR.cx_id
where CR.re_api_id is not null
and SAR.yr = YEAR(TODAY)
and SAR.ACST IN ('GOOD' ,'LOC' ,'PROB' ,'PROC' ,'PROR' ,'READ' ,'RP' ,
'SAB' ,'SHAC' ,'SHOC'))
'''
connection = get_connection(EARL)
with connection:
data_result = xsql(statquery, connection).fetchall()
ret = list(data_result)
for i in ret:
print(str(i[0]) + " " + i[1])
'''-------------------------------------------------------
--3-UPDATE THE CUSTOM STUDENT STATUS
FIELD----------------
----------------------------------------------------------
'''
if bb_id != 0:
print("Update custom field")
# ret = get_const_custom_fields(current_token, bb_id,
# 'Student Status')
"""
def main():
try:
# set global variable
global EARL
# determines which database is being called from the command line
if database == 'cars':
EARL = settings.INFORMIX_ODBC
elif database == 'train':
EARL = settings.INFORMIX_ODBC_TRAIN
# To get the last query date from cache
last_sql_date = cache.get('Sql_date')
if not last_sql_date:
# set a new date in cache
a = datetime.datetime.now()
last_sql_date = a.strftime('%Y-%m-%d %H:%M:%S')
cache.set('Sql_date', last_sql_date)
# This calls sky_constituent list to grab any recently added IDs
check_for_constituents(EARL)
# print('student status 1')
datetimestr = time.strftime('%Y%m%d%H%M%S')
# Defines file names and directory location
RE_STU_LOG = settings.BB_LOG_FOLDER + 'RE_student_status' \
+ datetimestr + ".txt"
""""--------GET THE TOKEN------------------"""
current_token = fn_do_token()
"""
-----------------------------------------------------------
-1-GET STUDENTS WITH A STATUS CHANGE FROM PROG_ENR_REC-----
Assume all current blackbaud students have a re_api_id in the
cvid_rec table. This will be handled through a separate prior
process.
Look for status changes only for students who have the
re_api_id entry
-----------------------------------------------------------
"""
"""THis query looks for recent changes in the student status.
We do not want to use any records that do NOT have an re_api_id
value. It only applies to RE entered students at present"""
statquery = '''
select O.id, O.acst, O.audit_event, TO_CHAR(O.audit_timestamp),
N.id, N.acst, N.audit_event, N.audit_timestamp,
CR.cx_id, CR.re_api_id, max(N.audit_timestamp)
from cars_audit:prog_enr_rec N
left join cars_audit:prog_enr_rec O
on O.id = N.id
and O.acst != N.acst
and O.audit_event in ('BU', 'I')
left JOIN cvid_rec CR
ON CR.cx_id = O.id
where
(N.audit_event != 'BU'
and N.audit_timestamp = O.audit_timestamp)
and N.audit_timestamp >= "{0}"
and CR.re_api_id is not null
--and N.id = 1468587
group by O.id, O.acst, O.audit_event, O.audit_timestamp,
N.id, N.acst, N.audit_event, N.audit_timestamp,
CR.cx_id, CR.re_api_id
UNION
select id, '' acst, '' audit_event, '' audit_timestamp,
N.id, N.acst, N.audit_event, N.audit_timestamp,
CR.cx_id, CR.re_api_id, max(N.audit_timestamp)
from cars_audit:prog_enr_rec N
left JOIN cvid_rec CR
ON CR.cx_id = N.id
where
(N.audit_event = 'I')
and N.audit_timestamp >= "{0}"
and (CR.re_api_id is not null)
--and N.id = 1468649
group by id, acst, audit_event, audit_timestamp,
N.id, N.acst, N.audit_event, N.audit_timestamp,
CR.cx_id, CR.re_api_id;
'''.format(last_sql_date)
# print(statquery)
with get_connection(EARL) as connection:
data_result = xsql(statquery, connection).fetchall()
ret = list(data_result)
if ret:
for i in ret:
# print(str(i[8]) + " " + i[5] + " " + str(i[9]))
carth_id = i[8]
acad_stat = i[5]
bb_id = i[9]
"""
-----------------------------------------------------------
--2-FIND RAISERS EDGE ID IN LOCAL TABLE --------------------
-----------------------------------------------------------
Look for student and status in local table
Else look for student and status at BB via API
Add to BB if necessary (THIS WILL BE DONE BY OMATIC)
Add or update status in BB
Update local table if necessary
"""
if bb_id != 0:
# Get the row id of the custom field record
field_id = get_const_custom_fields(
current_token, bb_id, 'Student Status')
# print("set custom fields: " + str(carth_id) + ", "
# + acad_stat)
"""ret is the id of the custom record, not the student"""
if field_id == 0:
# print("Error in student_status.py - for: "
# + str(carth_id) + ", Unable to get
# the custom field")
fn_write_error(
"Error in student_status.py - for: " +
str(carth_id) + ", Unable to get the "
"custom field")
fn_send_mail(
settings.BB_SKY_TO_EMAIL,
settings.BB_SKY_FROM_EMAIL, "SKY API ERROR",
"Error in student_status.py - for: " +
str(carth_id) +
", Unable to get the custom field")
pass
else:
ret1 = update_const_custom_fields(
current_token, str(field_id),
'CX Status Update', acad_stat)
if ret1 == 0:
# print("set custom fields: " + str(carth_id)
# + ", " + acad_stat)
f = open(RE_STU_LOG, "a")
f.write("set custom fields: " + str(carth_id) +
", " + acad_stat + '\n')
f.close()
else:
print("Patch failed")
fn_write_error(
"Error in student_status.py - Main: "
"Patch API call failed " + repr(e))
fn_send_mail(
settings.BB_SKY_TO_EMAIL,
settings.BB_SKY_FROM_EMAIL,
"SKY API ERROR", "Error in "
"student_status.py - for: "
"Patch API call failed " + repr(e))
pass
else:
# print("Nobody home")
pass
print("Process complete")
fn_send_mail(
settings.BB_SKY_TO_EMAIL,
settings.BB_SKY_FROM_EMAIL,
"SKY API: Last run was: {0}".format(str(last_sql_date)),
"New records processed for Blackbaud.",
)
else:
# print("No changes found")
fn_send_mail(
settings.BB_SKY_TO_EMAIL,
settings.BB_SKY_FROM_EMAIL,
"SKY API: Last run was: {0}".format(str(last_sql_date)),
"No new records for Blackbaud.",
)
# set a new date in cache
a = datetime.datetime.now()
last_sql_date = a.strftime('%Y-%m-%d %H:%M:%S')
cache.set('Sql_date', last_sql_date)
except Exception as error:
print("Error in main: {0}".format(repr(error)))
stack = traceback.print_exc()
print(stack)
fn_write_error("Error in student_status.py - Main: %s" % repr(error))
fn_write_error("Stack trace: %s" % repr(stack))
fn_send_mail(
settings.BB_SKY_TO_EMAIL,
settings.BB_SKY_FROM_EMAIL,
"Error in student_status.py: {0}".format(repr(error)),
"SKY API ERROR: {0}".format(stack),
)