'''performs check and if not, creates an update file and exits'''

nsc_degrees = set(nsc_data.get_column(n.DEGREE_TITLE)) - set([''])

missing_degrees = nsc_degrees - set(degree_dict)

if missing_degrees:

degree_lookup = { 'A': "Associate's",

'B': "Bachelor's",

'C': "Certificate",

'M': "Master's"}

output_table = []

for miss in missing_degrees:

if miss:

if miss[0] in degree_lookup:

output_table.append([miss, degree_lookup[miss[0]]])

else:

output_table.append([miss, '?'])

print('Some degrees from the NSC file are not included in your\n'+

'degree list (default filename degreelist.csv). Please\n'+

'inspect the file "missing_degrees.csv" and copy the values\n'+

'to the bottom of degreelist.csv before running again.')

tt.table_to_csv('missing_degrees.csv',output_table)

'''performs check and if not, creates an update file and exits'''

nsc_colleges = set(nsc_data.get_column(

n.COLLEGE_CODE_BRANCH)) - set([''])

# The OPEIDs we're comparing have follow the format '001650-01' in NSC

# and 165001 in the IPEDS database. Also, if the two digit ending is

# not found, '00' will specify the same school at the main branch

opeid_missing=[]

for entry in nsc_colleges:

parts = entry.split('-')

opeid1 = str(int(parts[0]+parts[1]))

opeid2 = str(int(parts[0]+'00'))

if opeid1 not in college_dict and opeid2 not in college_dict:

opeid_missing.append(entry)

if opeid_missing:

nsc_dict = nsc_data.create_dict_list(n.COLLEGE_CODE_BRANCH,

[n.COLLEGE_NAME, n.COLLEGE_STATE,

n.PUBLIC_PRIVATE])

output = [['OPEID', 'NCESID', 'Name', 'State', 'Control']]

for college in opeid_missing:

data = nsc_dict[college]

parts = college.split('-')

opeid1 = str(int(parts[0]+parts[1]))

row = [ opeid1, '?', data[0], data[1], data[2]]

output.append(row)

print('Some colleges from the NSC file are not included in your\n'+

'college list (default filename collegelist.csv). Please\n'+

'inspect the file "missing_colleges.csv" and use it to\n'+

'add new colleges to the bottom of collegelist.csv\n'+

'before running again.')

tt.table_to_csv('missing_colleges.csv',output)

'''Loads the main CSV inputs and checks to ensure the records in the

college and degree tables cover all those given in the NSC data. (If

not, it creates an output with the missing records and provides

an error message.)'''

nsc_data = tc.Table(nsc_fn)

college_list = tc.Table(sch_fn)

degree_list = tc.Table(deg_fn)

college_dict = college_list.create_dict_list('OPEID',

['NCESID', 'Name'])

degree_dict = degree_list.create_dict('UpdateDegree', 'DegreeType')

# These two functions will abort the script if not complete

check_degrees_table_complete(nsc_data, degree_dict)

check_college_table_complete(nsc_data, college_dict)

'''Uses data in standard columns to add a few more useful columns to

the main NSC data table'''

sId = []

hs_class = []

NCESid = []

colName = []

startDate = []

endDate = []

gradDate = []

grab_columns = [n.YOUR_UNIQUE_IDENTIFIER,

n.HIGH_SCHOOL_GRAD_DATE,

n.COLLEGE_CODE_BRANCH,

n.ENROLLMENT_BEGIN,

n.ENROLLMENT_END,

n.GRADUATION_DATE,

]

# The following will yield lists with only the above columns

for row in nsc_data.get_columns(grab_columns):

# NSC Unique Identifier is our ID with a '_' appended

sId.append(row[0][:-1])

# HS Class is the same as the year part of the grad year

hs_class.append(row[1][0:4])

# We can OPEID to our college database in two different ways

opeid_raw = row[2]

if opeid_raw:

parts = opeid_raw.split('-')

opeid1 = str(int(parts[0]+parts[1]))

opeid2 = str(int(parts[0]+'00'))

if opeid1 in college_dict: opeid = opeid1

else: opeid = opeid2

NCESid.append(college_dict[opeid][0])

colName.append(college_dict[opeid][1])

else:

NCESid.append('')

colName.append('')

# These 3 date fields have the form YYYYMMDD

startD_raw = row[3]

if startD_raw:

startDate.append(date(int(startD_raw[0:4]), int(startD_raw[4:6]),

int(startD_raw[6:])))

else: startDate.append('')

endD_raw = row[4]

if endD_raw:

endDate.append(date(int(endD_raw[0:4]), int(endD_raw[4:6]),

int(endD_raw[6:])))

else: endDate.append('')

gradD_raw = row[5]

if gradD_raw:

gradDate.append(date(int(gradD_raw[0:4]), int(gradD_raw[4:6]),

int(gradD_raw[6:])))

else: gradDate.append('')

# After looping through all raw records, add the results to main table

nsc_data.add_column('sId', sId)

nsc_data.add_column('HS Class', hs_class)

nsc_data.add_column('NCES ID', NCESid)

nsc_data.add_column('College Name', colName)

nsc_data.add_column('Start Date', startDate)

nsc_data.add_column('End Date', endDate)

'''s_c_table is a list of lists containing all the enrollments for

a single student at a single column (with columns defined by the header

dictionary hd. This combines enrollments that occur within daysgap

of each other'''

#First, move "Grad Date" into start and end date if blank

for row in s_c_table:

if not row[hd['Start Date']]: #if true, always true for "End Date" also

row[hd['Start Date']] = row[hd['Grad Date']]

row[hd['End Date']] = row[hd['Grad Date']]

#Second combine the enrollments that are contiguous, bringing up (as

# (as common) the following fields: GRADUATED, DEGREE_TITLE, MAJOR

if len(s_c_table) > 1:

s_c_table.sort(key=lambda x: x[hd['Start Date']])

# Now create a mapping of which records are the same

enrollments = []

new_enr = [0] # The current enrollment is primed with the first one

for i in range(1,len(s_c_table)):

last_end = s_c_table[new_enr[-1]][hd['End Date']]

this_start = s_c_table[i][hd['Start Date']]

days_diff = (this_start - last_end).days

if (days_diff > daysgap or #too many days apart

( s_c_table[i-1][hd['Grad Date']] and #continuing after

not s_c_table[i][hd['Grad Date']])): #graduating

# New enrollment starting with this one

enrollments.append(new_enr)

new_enr = [i]

else: # same enrollment

new_enr.append(i)

enrollments.append(new_enr)

# Take the mapping of like records and create single enrollment for each

new_s_c_table = []

for enrollment in enrollments:

record = s_c_table[enrollment[0]]

# columns to copy the max value into

for col in [hd['End Date'], hd[n.GRADUATED],

hd[n.DEGREE_TITLE], hd[n.MAJOR]]:

record[col] = max([s_c_table[enr][col] for enr in enrollment])

# columns to copy the final value into

for col in [hd[n.ENROLLMENT_STATUS], ]:

record[col] = s_c_table[enrollment[-1]][col]

new_s_c_table.append(record)

else: # the case if there is only a single entry for the student/college

new_s_c_table = s_c_table

'''Takes the main table and combines contiguous enrollments for the

same college and student. Enrollments are judged to be contiguous

if the days between them is less than daysgap. Enrollments have no

end date if the end date is after effdate.'''

# First get a list of students with matches

students_raw = nsc_data.get_columns(['sId', n.RECORD_FOUND_Y_N])

students = {student[0] for student in students_raw if student[1]=='Y'}

hd = nsc_data.get_header_dict() #So we can reference the elements

results_table = []

# Now process the records for each student

print('Beginning to process %d students' % len(students))

sCount = 0 # For screen display

for sId in students:

sCount += 1

student_table = list(nsc_data.get_match_rows('sId',sId))

colleges = {s[hd['NCES ID']] for s in student_table}

for col in colleges:

s_c_table = [row for row in student_table

if row[hd['NCES ID']] == col

and row[hd['RECORD_FOUND_Y/N']] != 'N']

#Now need to process each college in student_table

s_c_condensed = combine_s_c_enrollments(s_c_table, hd,

daysgap)

results_table.extend(s_c_condensed)

#if not sCount % 10: print('.', end='', flush=True)

if not sCount %100: print('%d contacts processed.' % sCount, flush=True)

tt.add_header(results_table, hd)

#Also need to handle records with no date

'''Adds a few interpreted status fields to the combined records:

Status = Attending, Graduated, Transferred Out, Withdrew

DegreeType = Bachelor's, Associate's, Certificate, Associate's or

Certificate (TBD), Master's

DataSource = NSC (only option

LastVerified = Date provided by effdate

(Note that combined_nsc is changed in place)

'''

# Will pass through every record to compute this into columns for each

hd = combined_nsc.get_header_dict()

status = []

degree_type = []

data_source = []

last_verified = []

dlv = date(int(effdate[-4:]), int(effdate[0:2]), int(effdate[3:5]))

for row in combined_nsc.rows():

last_verified.append(dlv) # Always the same for every record

data_source.append('NSC') # Always the same for every record

# Status will be based off the end date and grad/withdraw indicators

date_gap = (dlv - row[hd['End Date']]).days

end_status = row[hd[n.ENROLLMENT_STATUS]]

if row[hd[n.GRADUATED]] == 'Y':

status.append('Graduated')

elif end_status == 'W' or date_gap > daysgap:

status.append('T/W')

else:

status.append('Attending')

# Degree type is found from "graduated" degree titles or guessed from

# type of college

degree_title = row[hd[n.DEGREE_TITLE]]

if degree_title:

degree_type.append(degree_dict[degree_title])

else:

if row[hd[n.TWO_YEAR_4_YEAR]][0] == '4':

degree_type.append("Bachelor's")

else:

degree_type.append("Associate's or Certificate (TBD)")

# Add new columns to table

combined_nsc.add_column('Status', status)

combined_nsc.add_column('Degree Type', degree_type)

combined_nsc.add_column('Data Source', data_source)

combined_nsc.add_column('Last Verified', last_verified)

hd = combined_nsc.get_header_dict()

print('Now cleaning up T/W statuses.')

# Pass through and determine if T/W fields are T or W

students = set(combined_nsc.get_column('sId'))

for student in students:

s_rows = list(combined_nsc.get_match_rows('sId', student))

if len(s_rows) > 1:

s_rows.sort(key=lambda x: x[hd['Start Date']])

big_s = date(1900, 1,1) # big_s and big_e are for checking if there's

big_e = date(1901, 1,1) # a large enrollment that encompasses small ones

for i in range(len(s_rows)):

sDate = s_rows[i][hd['Start Date']]

eDate = s_rows[i][hd['End Date']]

if sDate > big_e: # this enrollment occurs after the previous

big_s = sDate # "big" enrollment

big_e = eDate

if s_rows[i][hd['Status']] == 'T/W':

if i+1 == len(s_rows): # final row for this student

if eDate >= big_e: # not a summer/catchup class

s_rows[i][hd['Status']] = 'Withdrew'

else: # probably a summer/catchup class

s_rows[i][hd['Status']] = 'Transferred out'

else:

date_gap = (s_rows[i+1][hd['Start Date']] -

s_rows[i][hd['End Date']]).days

if date_gap > daysgap:

if eDate >= big_e: # not a summer/catchup class

s_rows[i][hd['Status']] = 'Withdrew'

else:

s_rows[i][hd['Status']] = 'Transferred out'

else:

'''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)

''' Adds the Salesforce names for alumni and colleges using the student

id "YOUR UNIQUE IDENTIFIER" from NSC and using the NCES code for college.

Will use "N/A" for any alumnus/alumnae or college not found in the lookup'''

# First check whether the user would prefer to add from Salesforce API

# calls directly or via file upload

choices = ['Grab directly from Salesforce',

'Import as CSV files (see README for details)']

response = tktools.get_buttons_answer(choices,

'How would you like to get the SF IDs for alumni and colleges?')

# Once the user responds, generate a dictionary lookup for both fields

if response == choices[0]: # user selected 'Grab directly from Salesforce'

alumni_dict, college_dict = import_ids_from_salesforce()

else: # user wants to enter the info as a set of CSV files

filenames = {

'alum':['alumniNames.csv',

'Alumni names file (two columns: Your ID/SF ID)', 'r'],

'coll':['collegeNames.csv',

'College names file (two columns: NCES ID/ SF ID)', 'r']

}

tktools.get_filenames(filenames)

alumni_table = tt.grab_csv_table(filenames['alum'][0])

alumni_dict = tt.create_dict(alumni_table[1:], 0, 1)

college_table = tt.grab_csv_table(filenames['coll'][0])

college_dict = tt.create_dict(college_table[1:], 0, 1)

# Now that we have a dictionary for both, generate the two new columns

college_ids =[]

alumni_ids =[]

for row in combined_nsc.rows():

try:

college_ids.append(college_dict[row[combined_nsc.c('NCES ID')]])

except:

college_ids.append('N/A')

try:

alumni_ids.append(alumni_dict[row[combined_nsc.c('sId')]])

except:

alumni_ids.append('N/A')

# Finally, add the new columns back to the master table

combined_nsc.add_column('College__c', college_ids)

'''Takes the result of all the prior functions and reorders/renames

columns for a slightly more user friendly/Salesforce expected set'''

output_col = [ 'Student ID',

'Last Name',

'First Name',

'HS Class',

'College NCES ID',

'College Name',

'Student__c',

'College__c',

'Start_Date__c',

'End_Date__c',

'Status__c',

'Degree_Type__c',

'Data_Source__c',

'Date_Last_Verified__c',

'Degree_Text__c',

'Major_Text__c',

]

source_col = [ 'sId',

n.LAST_NAME,

n.FIRST_NAME,

'HS Class',

'NCES ID',

'College Name',

'Student__c',

'College__c',

'Start Date',

'End Date',

'Status',

'Degree Type',

'Data Source',

'Last Verified',

n.DEGREE_TITLE,

n.MAJOR,

]

new_table = combined_nsc.new_subtable(source_col, output_col)

new_table.apply_func('Last Name', str.title)

new_table.apply_func('First Name', str.title)

for row in new_table.rows():

if row[new_table.c('Status__c')] == 'Attending':

row[new_table.c('End_Date__c')] = ''

'''Main control flow for processing NSC files'''

print('-'*40)

print('NSC file is %s' % nsc)

print('Schools file is %s' % sch)

print('Degrees file is %s' % deg)

print('Output file will be %s' % out)

print('Allowed days gap is %d' % int(daysgap))

print('NSC report produced on %s' % effdate)

print('-'*40)

# Load input files (aborts with error if college/degree incomplete)

nsc_data, college_dict, degree_dict = grab_inputs(nsc, sch, deg)

# Add a few interpreted columns to the nsc table

print('Beginning to process NSC file.')

add_columns_to_nsc_data(nsc_data, college_dict)

# Combine records that reflect contiguous enrollments

print('Beginning to combine contiguous records.')

combined_nsc = combine_contiguous_enrollments(nsc_data, daysgap)

print('-'*40)

# Add Status, DegreeType, Date Verified, DataSource fields

print('Enrollments combined, now adding status fields.')

# had previously forced daysgap=40 in the below; not sure why?

add_status_fields(combined_nsc, degree_dict, daysgap, effdate)

# Add in Salesforce names for students and for colleges

# ToDo: Pass a "silent" variable to this function based on a to-be-added

# command line option to supress the query for SF/files

print('-'*40)

print('Adding Salesforce IDs for alumni and colleges')

add_Salesforce_indices(combined_nsc)

# Throw out the columns we don't need anymore and reorder others

print('-'*40)

print('Creating final output and saving to %s.' % out)

final_output_table = finalize_output_columns(combined_nsc)

#Finally, save the output file