def get_relationships(current_token, id):
urlst = 'https://api.sky.blackbaud.com/constituent/v1/constituents/' \
+ str(id) + '/relationships'
try:
x = api_get(current_token, urlst)
if x == 0:
print("NO DATA")
return 0
else:
for i in x['value']:
# print(i)
print(i['relation_id'])
print(i['type'])
print(i['constituent_id'])
print(i['relation_id'])
print(i['reciprocal_type'])
print(i['id'])
print(i['date_modified'])
print(i['is_organization_contact'])
print(i['is_primary_business'])
print(i['is_spouse'])
# print(i['start'])
return 1
except Exception as e:
print("Error in get_relationships: " + str(e))
fn_write_error("Error in sky_api_calls.py - get_relationships: " +
str(e))
return 0
def get_constituent_id(current_token, carthid):
try:
urlst = 'https://api.sky.blackbaud.com/constituent/v1/constituents/' \
'search?search_text=' + str(carthid) \
+ '&search_field=lookup_id'
x = api_get(current_token, urlst)
if x == 0:
# print("NO DATA")
return 0
else:
for i in x['value']:
# print(i['id'])
id = i['id']
# print(i['name'])
# print(i['lookup_id'])
# print(i['inactive'])
return id
except Exception as e:
print("Error in get_constituent_id: " + str(e))
fn_write_error("Error in sky_api_calls.py - "
"get_constituent_id: " + str(e))
return 0
def set_const_custom_field(current_token, id, value, category, comment):
# Not passing an item id - this is a create, one will be created
print("In set_const_custom_field")
urlst = 'https://api.sky.blackbaud.com/constituent/v1/constituents/' \
'customfields'
try:
# now = datetime.now()
# date_time = now.strftime("%Y-%m-%dT%H:%M:%S")
utc = arrow.utcnow()
# print(utc.to('US/Eastern'))
date_time = utc.to('US/Eastern')
# Constituent ID is passed in as Parent ID
body = {
'category': category,
'comment': comment,
'date': date_time,
'parent_id': id,
'value': value
}
# print(urlst, body)
# x = api_post(current_token, urlst, body)
# if x == 0:
# print("Post Failure")
# return 0
# else:
# return 1
except Exception as e:
print("Error in update_const_custom_fields: " + str(e))
fn_write_error("Error in sky_api_calls.py - "
"set_const_custom_field: " + str(e))
return 0
def api_patch(current_token, url, data):
# print("In api_patch")
try:
params = {'HOST': 'api.sky.blackbaud.com'}
# status = 'Initial Value'
headers = {
'Content-Type': 'application/json',
'Bb-Api-Subscription-Key': settings.BB_SKY_SUBSCRIPTION_KEY,
'Authorization': 'Bearer ' + current_token
}
response = requests.patch(url=url,
headers=headers,
params=params,
data=json.dumps(data))
status = response.status_code
stat_msg = response.text
# print(status)
# print(stat_msg)
return status
except Exception as e:
print("Error in api_patch: " + str(e))
fn_write_error("Error in sky_api_calls.py api_patch: " + str(e))
return 0
def get_constituent_custom_fields(current_token, bb_id):
urlst = 'https://api.sky.blackbaud.com/constituent/v1/constituents/' \
+ bb_id + '/customfields'
try:
print("In get_constituent_custom_fields")
x = api_get(current_token, urlst)
if x == 0:
print("NO DATA")
return 0
else:
# with open("id_list.csv", 'w') as id_lst:
# for i in x['value']:
# print(x)
# # print(i['id'])
# bb_id = i['id']
# carth_id = i['lookup_id']
# name = i['name']
# type = i['type']
# if type == 'Individual':
# # print('Name = ' + name + ', CarthID = ' + str(carth_id)
# # + ', BlackbaudID = ' + str(bb_id) + ', type = '
# # + type)
# csvwriter = csv.writer(id_lst, quoting=csv.QUOTE_NONE)
# csvwriter.writerow([carth_id, bb_id, name, type])
# return 1
return x
except Exception as e:
print("Error in get_constituent_id: " + str(e))
fn_write_error("Error in sky_api_calls.py - "
"get_constituent_custom_fields: " + str(e))
return 0
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 get_constituent_list(current_token, searchtime):
try:
print("In get_constituent_list")
a = datetime.strptime(str(searchtime), '%Y-%m-%d')
# print(a)
x = a.isoformat()
# print(x)
t = str(x)
# print(t)
z = x[:10] + 'T12:00:00.000-04:00'
# z = t[:20] + '.0000000-04:00'
# print(z)
# urlst = 'https://api.sky.blackbaud.com/constituent/v1/constituents?' \
# 'custom_field_category=Student Status' \
# '&date_added>2019-11-13T10:59:03.761-05:00&limit=3'
# print(urlst)
# 2020-01-01T17:59:31.1600745-04:00 THis works
# 2020-01-05T12:00:00.000-04:00 this fails
# 2020-01-05T12:00:00.0000000-04:00 THis works!
urlst = 'https://api.sky.blackbaud.com/constituent/v1/constituents?' \
'custom_field_category=Student Status' \
'&date_added>' + str(z) + '&limit=3'
# print(urlst)
# Student Status, Involvement
x = api_get(current_token, urlst)
# x = 0
if x == 0:
print("NO DATA")
return 0
else:
# for i in x['value']:
# # print(x)
# # print(i['id'])
# bb_id = i['id']
# carth_id = i['lookup_id']
# name = i['name']
# type = i['type']
# if type == 'Individual':
# # print('Name = ' + name + ', CarthID = ' + str(carth_id)
# # + ', BlackbaudID = ' + str(bb_id) + ', type = '
# # + type)
# return 1
return x
except Exception as e:
print("Error in get_constituent_id: " + str(e))
fn_write_error("Error in sky_api_calls.py - "
"get_constituent_list: " + str(e))
return 0
def delete_const_custom_fields(current_token, itemid):
try:
urlst = 'https://api.sky.blackbaud.com/constituent/v1/constituents/' \
'customfields/' + str(itemid)
print(urlst)
x = api_delete(current_token, urlst)
if x == 0:
print("Delete Failure")
return 1
else:
return 0
except Exception as e:
print("Error in delete_const_custom_fields: " + str(e))
fn_write_error("Error in sky_api_calls.py - "
"delete_const_custom_fields: " + str(e))
return 0
def get_relationship_types(current_token):
urlst = 'https://api.sky.blackbaud.com/constituent/v1/relationshiptypes'
try:
x = api_get(current_token, urlst)
if x == 0:
print("NO DATA")
return 0
else:
for i in x['value']:
print(i)
return 1
except Exception as e:
print("Error in get_relationships: " + str(e))
fn_write_error("Error in sky_api_calls.py - get_relationship_types: " +
str(e))
return 0
def get_constituents_custom_field_list(current_token, searchtime):
a = datetime.datetime.strptime(str(searchtime), '%Y-%m-%d %H:%M:%S')
# print(a)
x = a.isoformat()
# print(x)
t = str(x)
# print(t)
z = x[:10] + 'T12:00:00.000-04:00'
# z = t[:20] + '.0000000-04:00'
# print(z)
# 2020-01-05T12:00:00.0000000-04:00 THis works!
"""This calls the API Constituent Custom Field List (All Constituents)
Uses the date added as a search param, as well as the custom field
for student status. Can be limited to a particular number of records.
Offset param will skip the first N number of records
Date added must be in format 2020-01-05T12:00:00.0000000-04:00"""
urlst = "https://api.sky.blackbaud.com/constituent/v1/constituents/" \
"customfields?date_added=" + searchtime + "" \
"&category=Student Status" \
"&limit=1500&"
# urlst = "https://api.sky.blackbaud.com/constituent/v1/constituents/" \
# "customfields?category=Student Status&value=Blank"
try:
# print(urlst)
x = api_get(current_token, urlst)
return x
# if x == 0:
# print("NO DATA")
# return 0
# else:
# for i in x['value']:
# # print(i)
# print(i['parent_id'])
# print(i['value'])
# return 1
except Exception as e:
print("Error in get_relationships: " + str(e))
fn_write_error("Error in sky_api_calls.py - "
"get_constituents_custom_field_list: " + str(e))
return 0
def get_lookup_id(current_token, bb_id):
try:
urlst = 'https://api.sky.blackbaud.com/constituent/v1/constituents/' \
+ str(bb_id)
# print(urlst)
x = api_get(current_token, urlst)
if x == 0:
# print("NO DATA")
return 0
else:
# print(x)
# print(x['lookup_id'])
return x['lookup_id']
except Exception as e:
print("Error in get_lookup_id: " + str(e))
fn_write_error("Error in sky_api_calls.py - "
"get_lookup_id: " + str(e))
return 0
def api_get(current_token, url):
# print("In api_get")
# print(url)
try:
params = {'HOST': 'api.sky.blackbaud.com'}
status = 0
# Setting status to something other than 200 initially
# print(status)
while status != 200 or url != '':
time.sleep(.2) # SKY API Rate limited to 5 calls/sec
headers = {
'Bb-Api-Subscription-Key': settings.BB_SKY_SUBSCRIPTION_KEY,
'Authorization': 'Bearer ' + current_token
}
# print(headers)
response = requests.get(url=url, params=params, headers=headers)
status = response.status_code
# print(status)
if status == 400:
print('ERROR: ' + str(status) + ":" + response.text)
return 0
elif status == 403: # OUT OF API QUOTA - Quit
print('ERROR: ' + str(status) + ":" + response.text)
print('You\'re out of API Quota!')
return 0
else:
# if response_dict['count'] == 0:
response_dict = json.loads(response.text)
# if response_dict['count'] == 0:
# return 0
# else:
return response_dict
except Exception as e:
print("Error in api_get: " + str(e))
fn_write_error("Error in sky_api_calls.py api_get: " + str(e))
return 0
def get_custom_field_value(current_token, category):
try:
print("In get_custom_field_value")
urlst = 'https://api.sky.blackbaud.com/constituent/v1/constituents/' \
'customfields/categories/values?category_name=' + category
# print(urlst)
x = api_get(current_token, urlst)
# print(x)
if x == 0:
print("NO DATA")
return 0
else:
# for i in x['value']:
# print(x)
# print(i)
return 1
except Exception as e:
print("Error in get_custom_field_value: " + str(e))
fn_write_error("Error in sky_api_calls.py - "
"get_custom_field_value: " + str(e))
return 0
def update_const_custom_fields(current_token, itemid, comment, val):
try:
# print("In update_const_custom_fields")
# print(itemid)
urlst = 'https://api.sky.blackbaud.com/constituent/v1/constituents/' \
'customfields/' + itemid
# now = datetime.now()
# date_time = now.strftime("%Y-%m-%dT%H:%M:%S")
utc = arrow.utcnow()
# print(utc.to('US/Eastern'))
date_time = utc.to('US/Eastern')
# print(str(date_time))
dat = str(date_time)
body = {
"comment": comment,
"date_modified": dat,
"date": dat,
"value": val
}
# print(urlst, body)
x = api_patch(current_token, urlst, body)
if x == 0:
print("Patch Failure")
return 1
else:
# print("Patch Success")
return 0
except Exception as error:
print("Error in update_const_custom_fields: " + str(error))
stack = traceback.print_exc()
print(stack)
fn_write_error("Error in sky_api_calls.py - "
"update_const_custom_fields: " + str(error))
fn_write_error(stack)
return 0
def get_const_custom_fields(current_token, id, category):
# print("In get_const_custom_fields")
try:
urlst = 'https://api.sky.blackbaud.com/constituent/v1/constituents/' \
+ str(id) + '/customfields'
x = api_get(current_token, urlst)
# print(x)
# This will return multiple records...How to parse things to get the
# one item I want...
if x == 0:
print("NO DATA")
return 0
else:
for i in x['value']:
# print(i)
if i['category'] == category:
item_id = i['id']
# print("ID = " + i['id'])
# print("Category = " + i['category'])
# if 'comment' not in x['value']:
# print("Comment not entered")
# else:
# print("Comment = " + str(i['comment']))
# print("Date = " + i['date'])
# print("Date Added = " + i['date_added'])
# print("Date Modified = " + i['date_modified'])
# print("Parent id = " + i['parent_id'])
# print("Type = " + i['type'])
# print("Blackbaud Status Value = " + i['value'])
return item_id
except Exception as e:
print("Error in sky_api_calls.py - get_const_custom_fields "
"for: " + str(id) + ", " + str(e))
fn_write_error("Error in sky_api_calls.py - get_const_custom_fields "
"for: " + str(id) + ", " + str(e))
return 0
def fn_do_token():
"""--------REFRESH THE TOKEN------------------"""
""" Because the token only lasts for 60 minutes when things are idle
it will be necessary to refresh the token before attempting
anything else. The refresh token will be valid for 60 days,
so it should return a new token with no problem. All the API
calls will get new tokens, resetting the 60 minute clock,
so to avoid calling for a token every time, I may have to
either set a timer or see if I can read the date and time from the
cache files and compare to current time
"""
now = datetime.datetime.now()
try:
"""
Check to see if the token has expired, if so refresh it
the token expires in 60 minutes, but the refresh token
is good for 60 days
"""
time_refresh = cache.get(settings.BB_SKY_REFRESH_TIME_CACHE_KEY)
if time_refresh is None:
token_refresh = fn_token_refresh()
elif time_refresh < now - datetime.timedelta(minutes=59):
token_refresh = fn_token_refresh()
else:
pass
""""--------GET THE TOKEN------------------"""
current_token = cache.get(settings.BB_SKY_TOKEN_CACHE_KEY)
return current_token
except Exception as error:
fn_write_error("Error in fn_do_token() - Main: " + repr(error))
stack = traceback.print_exc()
print(stack)
fn_write_error("Stack trace: %s" % repr(stack))
fn_send_mail(settings.BB_SKY_TO_EMAIL, settings.BB_SKY_FROM_EMAIL,
"SKY API ERROR",
"Error in sky_pi_auth.py - fn_do_token: " + repr(error))
return 0
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 fn_token_refresh():
# print("In token_refresh")
now = datetime.datetime.now()
try:
# Generates a new OAUTH2 access token and refresh token using
# the current (unexpired) refresh token. Writes updated tokens
# to appropriate files for subsequent reference
# :return: Tuple containing (return_code, access_token, refresh_token)
refresh_token = cache.get(settings.BB_SKY_REFRESH_TOKEN_CACHE_KEY)
# print(refresh_token)
ref_token_call = requests.post(
url='https://oauth2.sky.blackbaud.com/token',
headers={'Content-Type': 'application/x-www-form-urlencoded'},
data={
'grant_type': 'refresh_token',
'refresh_token': refresh_token,
'client_id': settings.BB_SKY_CLIENT_ID,
## **** Can we enable this?
# ***'preserve_refresh_token': 'true',
'client_secret': settings.BB_SKY_CLIENT_SECRET,
# 'redirect_uri': settings.BB_SKY_CALLBACK_URI
},
)
status = ref_token_call.status_code
response = ref_token_call.text
if status == 200:
tokens_dict = dict(json.loads(ref_token_call.text))
refresh_token = tokens_dict['refresh_token']
access_token = tokens_dict['access_token']
cache.set(settings.BB_SKY_TOKEN_CACHE_KEY, access_token)
cache.set(settings.BB_SKY_REFRESH_TOKEN_CACHE_KEY, refresh_token)
cache.set(settings.BB_SKY_REFRESH_TIME_CACHE_KEY, now)
# print(access_token)
# print(refresh_token)
return 1
elif status == 403: # OUT OF API QUOTA - Quit
# Print HTML repsonse and exit function with empty DataFrame
# print('ERROR: ' + str(status) + ":" + response)
# print('You\'re out of API Quota!')
fn_write_error('ERROR: ' + str(status) + ":" +
' You\'re out of API Quota!')
fn_send_mail(
settings.BB_SKY_TO_EMAIL, settings.BB_SKY_FROM_EMAIL,
"SKY API ERROR",
'ERROR: ' + str(status) + ":" + ' You\'re out of API Quota!')
exit()
return 0
else:
print('here')
fn_write_error('ERROR: ' + str(status))
return 0
except Exception as error:
print("Error in fn_token_refresh(): " + error)
fn_write_error("Error in fn_token_refresh() - Main: " + repr(error))
stack = traceback.print_exc()
print(stack)
fn_write_error("Stack trace: %s" % repr(stack))
fn_send_mail(
settings.BB_SKY_TO_EMAIL,
settings.BB_SKY_FROM_EMAIL,
"SKY API ERROR",
"Error in sky_api_auth.py - fn_token_refresh: %s" % repr(error),
)
return 0
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),
)
