def import_ids_from_salesforce():
'''Interfaces with the Salesforce database to grab the translation from
NCES ID->SF ID for colleges and Student Number->SF ID for alumni and
returns the translations as a tuple of two dictionarys'''
alumni_table, college_table = aDBi.grabAccountContactTranslations()
alumni_dict = tt.create_dict(alumni_table[1:], 0, 1)
college_dict = tt.create_dict(college_table[1:], 0, 1)
return (alumni_dict, college_dict)
def import_ids_from_salesforce():
"""Interfaces with the Salesforce database to grab the translation from
NCES ID->SF ID for colleges and Student Number->SF ID for alumni and
returns the translations as a tuple of two dictionarys"""
alumni_table, college_table = aDBi.grabAccountContactTranslations()
alumni_dict = tt.create_dict(alumni_table[1:], 0, 1)
college_dict = tt.create_dict(college_table[1:], 0, 1)
return (alumni_dict, college_dict)
def get_SF_contacts():
'''Returns a list of list with a specific set of columns:
Id, LastName, FirstName, HS_Class__c, High_School__c
'''
try:
contact_table = aDBi.getContactFields_Roster()
except:
print('Error attempting to grab data from Salesforce:',
sys.exc_info()[0])
print('#### Detailed error message follows ####')
raise
return contact_table
#!python3
'''
This module fixes errors in the "Currently Enrolled At" field in the alumni
database
'''
from botutils.ADB import AlumniDatabaseInterface as aDBi
from botutils.ADB import ContactNamespace as c
from botutils.ADB import AccountNamespace as a
from botutils.ADB import EnrollmentNamespace as e
from botutils.tabletools import tabletools as tt
from botutils.ADB import ssf
#Grab the data tables
restriction = ''
sf = ssf.getSF()
contacts, accounts, enrollments = aDBi.grabThreeMainTables_Analysis(
restriction, sf)
#Lop off headers
dC = tt.slice_header(contacts)
dA = tt.slice_header(accounts)
dE = tt.slice_header(enrollments)
print('Beginning to parse %d contacts.' % len(contacts))
soFar = 0 #index of number of contacts covered
for student in contacts:
soFar += 1
if not soFar % 10: print('.', end='')
if not soFar % 100: print('%d contacts processed.' % soFar)
records = [
row for row in enrollments
if row[dE[e.Student__c]] == student[dC[c.Id]]
def main(nsc, db_flag, enr, con, acc):
'''Main control flow for merging new enrollments with old in database'''
print('-' * 40)
print('Output file from intake_nsc.py is %s' % nsc)
if db_flag:
print('Grabbing data tables directly from database')
else:
print('Accounts file: %s' % acc)
print('Contacts file: %s' % con)
print('Enrollments file: %s' % enr)
print('-' * 40)
# Load the intake_nsc file
intake_raw = tc.Table(nsc)
nsc_enr = em.get_enrollments_and_chg_vartype(intake_raw)
year_range = set(intake_raw.get_column('HS Class'))
print('Year range of %s-%s.' % (min(year_range), max(year_range)))
# Get the database information
if db_flag:
restr = c.HS_Class__c + ' IN '
restr += "('" + "','".join(year_range) + "')"
db_res = aDBi.grabThreeMainTables_Analysis(contactRestriction=restr,
mode='NSCmerge')
con_raw = tc.Table(db_res[0])
acc_raw = tc.Table(db_res[1])
enr_raw = tc.Table(db_res[2])
else:
acc_raw = tc.Table(acc)
con_raw = tc.Table(con)
enr_raw = tc.Table(enr)
# Cleanup the database info
db_enr = em.get_enrollments_and_chg_vartype(enr_raw, True)
acc_dict = get_acc_dict(acc_raw)
con_dict = get_con_dict(con_raw)
# Prior to cycling through, append an index column to the two
# enrollment tables so we can reference the original row even
# if we're inspecting a subset of rows
db_enr.add_column('Index', list(range(len(db_enr))))
nsc_enr.add_column('Index', list(range(len(nsc_enr))))
# Do many passes through tables to find matches
print('-' * 40)
print('Looking for matches: %d from db, %d from nsc' %
(len(db_enr), len(nsc_enr)))
db_enr_map, nsc_enr_map, match_table = em.find_matches(db_enr, nsc_enr)
unmatched_db = [x for x in db_enr.rows() if x[-1] not in db_enr_map]
unmatched_nsc = [x for x in nsc_enr.rows() if x[-1] not in nsc_enr_map]
print('Still not matched: %d from db, %d from nsc' %
(len(unmatched_db), len(unmatched_nsc)))
# Use matching information to generate output tables
print('-' * 40)
print('Generating output files')
enr_update = [[
e.Id,
e.Start_Date__c,
e.End_Date__c,
e.Date_Last_Verified__c,
e.Status__c,
e.Degree_Type__c,
e.Data_Source__c,
e.Degree_Text__c,
e.Major_Text__c,
]]
new_enr = [em.get_enr_field_list()]
con_flag = [[c.Id, c.Needs_NSC_Review__c, c.NSC_Review_Reason__c]]
for row in match_table:
# row[0] is always a MatchCase subclass that will have a custom
# function for each of these three lines if pertinent for this
# case. Otherwise, the function will return the default None
enr_update.append(row[0].enr_update(row[1:], acc_dict))
new_enr.append(row[0].new_enr(row[1:]))
con_flag.append(row[0].con_flag(row[1:], acc_dict))
# Scrub out the empty rows if a MatchCase had nothing to say
enr_update = [x for x in enr_update if x]
new_enr = [x for x in new_enr if x]
con_flag = [x for x in con_flag if x]
#Rollup the contact flags to have a single row per student
con_update = []
for student in set(x[0] for x in con_flag[1:]):
student_set = [x for x in con_flag if x[0] == student]
new_flags = '; '.join([x[2] for x in student_set])
con_update.append([student, True, new_flags])
con_update.insert(0, con_flag[0])
# Write output tables to files
today_ending = date.today().strftime('%m_%d_%Y')
new_enr_fn = 'new_enr_' + today_ending + '.csv'
enr_update_fn = 'enr_update_' + today_ending + '.csv'
con_update_fn = 'con_update_' + today_ending + '.csv'
tt.table_to_csv(enr_update_fn, enr_update)
tt.table_to_csv(new_enr_fn, new_enr)
tt.table_to_csv(con_update_fn, con_update)
# debugging lines
case_list = Counter([x[0] for x in match_table]).most_common()
case_table = [['Matching case', 'Frequency']]
case_table.extend([[c[0], c[1]] for c in case_list])
nsc_h = nsc_enr.get_full_table()[0]
db_h = db_enr.get_full_table()[0]
unmatched_nsc.insert(0, nsc_h)
unmatched_db.insert(0, db_h)
match_h = ['Case']
match_h.extend(nsc_h)
match_h.extend(db_h)
match_table.insert(0, match_h)
if not os.path.exists('debugging_output'):
os.makedirs('debugging_output')
tt.table_to_csv('debugging_output/__match_table.csv', match_table)
tt.table_to_csv('debugging_output/__unmatched_nsc.csv', unmatched_nsc)
tt.table_to_csv('debugging_output/__unmatched_db.csv', unmatched_db)
tt.table_to_csv('debugging_output/__matching_cases.csv', case_table)
def get_SF():
'''The Analysis qualifier restricts the fields only to ones we will
likely need'''
return aDBi.grabThreeMainTables_Analysis()
def main(nsc, db_flag, enr, con, acc):
'''Main control flow for merging new enrollments with old in database'''
print('-'*40)
print('Output file from intake_nsc.py is %s' % nsc)
if db_flag:
print('Grabbing data tables directly from database')
else:
print('Accounts file: %s' % acc)
print('Contacts file: %s' % con)
print('Enrollments file: %s' % enr)
print('-'*40)
# Load the intake_nsc file
intake_raw = tc.Table(nsc)
nsc_enr = em.get_enrollments_and_chg_vartype(intake_raw)
year_range = set(intake_raw.get_column('HS Class'))
print('Year range of %s-%s.' % (min(year_range), max(year_range)))
# Get the database information
if db_flag:
restr = c.HS_Class__c + ' IN '
restr += "('" + "','".join(year_range) + "')"
db_res = aDBi.grabThreeMainTables_Analysis(
contactRestriction=restr,
mode='NSCmerge')
con_raw = tc.Table(db_res[0])
acc_raw = tc.Table(db_res[1])
enr_raw = tc.Table(db_res[2])
else:
acc_raw = tc.Table(acc)
con_raw = tc.Table(con)
enr_raw = tc.Table(enr)
# Cleanup the database info
db_enr = em.get_enrollments_and_chg_vartype(enr_raw, True)
acc_dict = get_acc_dict(acc_raw)
con_dict = get_con_dict(con_raw)
# Prior to cycling through, append an index column to the two
# enrollment tables so we can reference the original row even
# if we're inspecting a subset of rows
db_enr.add_column('Index',list(range(len(db_enr))))
nsc_enr.add_column('Index',list(range(len(nsc_enr))))
# Do many passes through tables to find matches
print('-'*40)
print('Looking for matches: %d from db, %d from nsc'
% (len(db_enr), len(nsc_enr)))
db_enr_map, nsc_enr_map, match_table = em.find_matches(db_enr, nsc_enr)
unmatched_db = [x for x in db_enr.rows() if x[-1] not in db_enr_map]
unmatched_nsc = [x for x in nsc_enr.rows() if x[-1] not in nsc_enr_map]
print('Still not matched: %d from db, %d from nsc' %
(len(unmatched_db), len(unmatched_nsc)))
# Use matching information to generate output tables
print('-'*40)
print('Generating output files')
enr_update = [[e.Id, e.Start_Date__c, e.End_Date__c,
e.Date_Last_Verified__c, e.Status__c,
e.Degree_Type__c, e.Data_Source__c,
e.Degree_Text__c, e.Major_Text__c, ]]
new_enr = [em.get_enr_field_list()]
con_flag = [[c.Id, c.Needs_NSC_Review__c, c.NSC_Review_Reason__c]]
for row in match_table:
# row[0] is always a MatchCase subclass that will have a custom
# function for each of these three lines if pertinent for this
# case. Otherwise, the function will return the default None
enr_update.append(row[0].enr_update(row[1:], acc_dict))
new_enr.append(row[0].new_enr(row[1:]))
con_flag.append(row[0].con_flag(row[1:], acc_dict))
# Scrub out the empty rows if a MatchCase had nothing to say
enr_update = [x for x in enr_update if x]
new_enr = [x for x in new_enr if x]
con_flag = [x for x in con_flag if x]
#Rollup the contact flags to have a single row per student
con_update = []
for student in set(x[0] for x in con_flag[1:]):
student_set = [x for x in con_flag if x[0] == student]
new_flags = '; '.join([x[2] for x in student_set])
con_update.append([student, True, new_flags])
con_update.insert(0,con_flag[0])
# Write output tables to files
today_ending = date.today().strftime('%m_%d_%Y')
new_enr_fn = 'new_enr_' + today_ending + '.csv'
enr_update_fn = 'enr_update_' + today_ending + '.csv'
con_update_fn = 'con_update_' + today_ending + '.csv'
tt.table_to_csv(enr_update_fn, enr_update)
tt.table_to_csv(new_enr_fn, new_enr)
tt.table_to_csv(con_update_fn, con_update)
# debugging lines
case_list = Counter([x[0] for x in match_table]).most_common()
case_table = [['Matching case','Frequency']]
case_table.extend([[c[0], c[1]] for c in case_list])
nsc_h = nsc_enr.get_full_table()[0]
db_h = db_enr.get_full_table()[0]
unmatched_nsc.insert(0,nsc_h)
unmatched_db.insert(0,db_h)
match_h = ['Case']
match_h.extend(nsc_h)
match_h.extend(db_h)
match_table.insert(0,match_h)
if not os.path.exists('debugging_output'):
os.makedirs('debugging_output')
tt.table_to_csv('debugging_output/__match_table.csv',match_table)
tt.table_to_csv('debugging_output/__unmatched_nsc.csv',unmatched_nsc)
tt.table_to_csv('debugging_output/__unmatched_db.csv',unmatched_db)
tt.table_to_csv('debugging_output/__matching_cases.csv',case_table)
#!python3
'''
This module fixes errors in the "Currently Enrolled At" field in the alumni
database
'''
from botutils.ADB import AlumniDatabaseInterface as aDBi
from botutils.ADB import ContactNamespace as c
from botutils.ADB import AccountNamespace as a
from botutils.ADB import EnrollmentNamespace as e
from botutils.tabletools import tabletools as tt
from botutils.ADB import ssf
#Grab the data tables
restriction = ''
sf = ssf.getSF()
contacts, accounts, enrollments = aDBi.grabThreeMainTables_Analysis(
restriction,sf)
#Lop off headers
dC = tt.slice_header(contacts)
dA = tt.slice_header(accounts)
dE = tt.slice_header(enrollments)
print('Beginning to parse %d contacts.' % len(contacts))
soFar = 0 #index of number of contacts covered
for student in contacts:
soFar+=1
if not soFar % 10: print('.', end='')
if not soFar % 100: print('%d contacts processed.' % soFar)
records = [row for row in enrollments if row[dE[e.Student__c]] ==
student[dC[c.Id]] and
row[dE[e.Status__c]] ==