Ejemplo n.º 1
0
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)
Ejemplo n.º 2
0
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)
Ejemplo n.º 3
0
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]]
Ejemplo n.º 5
0
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)
Ejemplo n.º 6
0
def get_SF():
    '''The Analysis qualifier restricts the fields only to ones we will
    likely need'''
    return aDBi.grabThreeMainTables_Analysis()
Ejemplo n.º 7
0
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]] ==