"""Unit Application functions.
This module defines helper functions to get certain information for the UnitApplication class from the database. It is also used in
the bulk_app_loader to load information for multiple application in one query.
"""
from itertools import groupby
from operator import attrgetter
from uw.local import termtools
[docs]def get_app_accept_counts(cursor, where, vardict):
"""Returns a named tuple containing the accept_count and waitlist_count
:param cursor: Database connection cursor
:param where: identification string for the unitapp (e.g. '((wa.uw_id, wa.appl_id) = (%(uw_id)s, %(appl_id)s))'
:param vardict: dictionnary of variables to pass to the query, the appl_id and uw_id keys are required
"""
counts = cursor.execute_tuples ("select appl_id, count( view_state ) FILTER ( WHERE view_state = 'ACC') as accept_count, count( view_state ) FILTER ( WHERE view_state = 'WTL') as waitlist_count FROM work_application as wa LEFT JOIN work_application_faculty USING (appl_id) WHERE %s GROUP BY appl_id ORDER BY appl_id" % where, **vardict)
return counts
[docs]def get_app_rating(cursor, where, vardict):
"""Returns a named tuple containing the rating_code (A-C(+/-), CWD), rating_sequence, rating_colour
:param cursor: Database connection cursor
:param where: identification string for the unitapp (e.g. '((wa.uw_id, wa.appl_id) = (%(uw_id)s, %(appl_id)s))'
:param vardict: dictionnary of variables to pass to the query, the appl_id and uw_id keys are required
"""
ratings = cursor.execute_tuples ("select * from work_application_current_rating as wa natural left join work_application_rating_code where %s ORDER by appl_id" % where, **vardict)
return ratings
[docs]def get_app_education(cursor, where, vardict):
"""Returns a list of tuples with the following output for every past institution attended:
[(('institution_id', 'degree_id'), (named_tuple)) ... ]
:param cursor: Database connection cursor
:param where: identification string for the unitapp (e.g. '((wa.uw_id, wa.appl_id) = (%(uw_id)s, %(appl_id)s))'
:param vardict: dictionnary of variables to pass to the query, the appl_id and uw_id keys are required
"""
result = cursor.execute_tuples (("select ge.*, gi.institution_country, gi.institution_name, geg.summary_type, geg.gpa, wa.appl_id from work_application as wa natural join gso_education as ge "
"natural join gso_institution as gi natural left join gso_education_gpa as geg where %s"
"order by appl_id ASC, degree_date desc, institution_name, degree_id, summary_type") % where, **vardict)
grouper = attrgetter ('institution_id', 'degree_id')
return [[(inst, tuple (gpa)) for inst, gpa in groupby (res, grouper)] for appl_id, res in groupby(result, attrgetter('appl_id'))]
[docs]def get_student_education_gpa (cursor, uw_ids):
"""Query adm_std_academic_gpa to obtain the student's or a group of students' gpa (gpa, gpa_type) associated with one attendance record.
:param cursor: database connection cursor
:param uw_ids: array of uw_ids
:return: A dictionary that maps (uw_id, ext_org_id, ext_career_code, ext_career_seq) to a tuple of corresponding gpa namedtuples.
Primarily intended for use with :func:`get_student_education_attendance`.
"""
result = cursor.execute_tuples("select uw_id, ext_org_id, ext_career_code, ext_career_seq, ext_gpa, ext_gpa_type_code from adm_std_academic_gpa where uw_id = any (%(uw_ids)s)", uw_ids=uw_ids)
grouper = attrgetter ('uw_id', 'ext_org_id', 'ext_career_code', 'ext_career_seq')
return dict ((attendance, tuple(gpa)) for attendance, gpa in groupby(result, grouper))
[docs]def get_student_education_degree (cursor, uw_ids):
"""Query adm_std_academic_degree to obtain the student's or a group of students' degrees (description, date, status, specialization) associated with one attendance record.
:param cursor: database connection cursor
:param uw_ids: array of uw_ids
:return: A dictionary that maps (uw_id, ext_org_id, ext_career_code, ext_career_seq) to a tuple of corresponding degree namedtuples.
Primarily intended for use with :func:`get_student_education_attendance`.
"""
result = cursor.execute_tuples("select asad.uw_id, asad.ext_org_id, asad.ext_career_code, asad.ext_career_seq, pd.degree_full_title, ueds.degree_status_description, asad.degree_date, asad.degree_specialization from adm_std_academic_degree as asad natural join uw_ext_degree_status as ueds natural join pps_degree as pd where uw_id = any (%(uw_ids)s) order by uw_id, ext_org_id, ext_career_code, ext_career_seq, degree_date DESC", uw_ids=uw_ids)
grouper = attrgetter ('uw_id', 'ext_org_id', 'ext_career_code', 'ext_career_seq')
return dict ((attendance, tuple(degree)) for attendance, degree in groupby(result, grouper))
[docs]def get_student_education_attendance (cursor, uw_ids):
"""Query adm_std_academic_attendance to obtain the student's or a group of students' attendance(s) (description, date, status, specialization) associated with one institution, conbined with gpa and degree.
:param cursor: database connection cursor
:param uw_ids: array of uw_ids
:return: A dictionary that maps (uw_id, ext_org_id) to a list of dictionaries that contain 'date', 'gpa' and 'degree'.
Primarily intended for use with :func:`get_student_education`.
"""
gpa = get_student_education_gpa (cursor, uw_ids)
degree = get_student_education_degree (cursor, uw_ids)
attendance = cursor.execute_tuples("select * from adm_std_academic_attendance where uw_id = any (%(uw_ids)s) order by uw_id, ext_org_id, end_date DESC", uw_ids=uw_ids)
result = {}
for ue, res in groupby (attendance, attrgetter ('uw_id', 'ext_org_id')):
ins = []
result[ue] = ins
for att in res:
info = {}
ins.append (info)
info['gpa'] = gpa[(ue[0], ue[1], att.ext_career_code, att.ext_career_seq)]
info['degree'] = degree[(ue[0], ue[1], att.ext_career_code, att.ext_career_seq)]
info['date'] = att
return result
[docs]def get_student_education (cursor, uw_ids):
"""Query adm_std_academic, uw_ext_org and uw_country to obtain the student's or a group of students' institution(s) id as well as the name and country, conbined with attendance, gpa and degree.
:param cursor: database connection cursor
:param uw_ids: array of uw_ids
:return: A dictionary that maps uw_id to a tuple of tuples which contain a namedtuple of institution information and the list of dictionaries that obtained from the corresponding attendance.
"""
education = cursor.execute_tuples ("select * from (select uw_id, ext_org_id from (select distinct on (uw_id, ext_org_id) * from adm_std_academic natural left join adm_std_academic_attendance where uw_id = any (%(uw_ids)s) order by uw_id, ext_org_id, end_date desc) t order by end_date desc) s natural join uw_ext_org natural join uw_country where uw_id = any (%(uw_ids)s) order by uw_id", uw_ids=uw_ids)
# get attendance (including gpa, degree)
attendance = get_student_education_attendance (cursor, uw_ids)
result = {}
for uw_id, orgs in groupby (education, attrgetter ('uw_id')):
result[uw_id] = tuple ((org, attendance.get ((uw_id, org.ext_org_id), [])) for org in orgs)
return result
[docs]def get_app_applicant(cursor, where, vardict):
"""Returns a named tuple with general applicant information including:
name, gender, birth, address and contact infomation
:param cursor: Database connection cursor
:param where: identification string for the unitapp (e.g. '((wa.uw_id, wa.appl_id) = (%(uw_id)s, %(appl_id)s))'
:param vardict: dictionnary of variables to pass to the query, the appl_id and uw_id keys are required
"""
applicants = cursor.execute_tuples (("select * from work_application as wa natural join gso_applicant natural left join "
"(select country_code as citizen_country, country_name from uw_country) t where "
"%s ORDER BY appl_id ASC") % where, **vardict)
return applicants
[docs]def get_app_plan_info(cursor, where, vardict):
"""Returns a named tuple containing plan_code and plan_transcript_description information.
:param cursor: Database connection cursor
:param where: identification string for the unitapp (e.g. '((wa.uw_id, wa.appl_id) = (%(uw_id)s, %(appl_id)s))'
:param vardict: dictionnary of variables to pass to the query, the appl_id and uw_id keys are required
"""
plan_infos = cursor.execute_tuples("select plan_code, plan_transcript_description from work_application as wa join adm_appl_plan using (uw_id, appl_id) join pps_plan using (plan_code) where %s order by appl_id ASC"
% where, **vardict)
return plan_infos
[docs]def get_app_info(cursor, where, vardict):
"""Return a named tuple of miscellaneous information like compilation document, reason_description for status etc.
admit_term is replaced with the termtool object.
:param cursor: Database connection cursor
:param where: identification string for the unitapp (e.g. '((wa.uw_id, wa.appl_id) = (%(uw_id)s, %(appl_id)s))'
:param vardict: dictionnary of variables to pass to the query, the appl_id and uw_id keys are required
"""
apps_list = cursor.execute_tuples ("select aaph.*, ga.appl_method, appl_admit_type, ga.ouac_id, gd.decision_code, gd.decision_description, gdr.reason_code, gdr.reason_description, ua.attendance_code, ua.attendance_description, wa.unit_code, group_code, level_code, ul.level_description, compilation_document is not null as compilation_document, compilation_uploader, compilation_uploaded from work_application as wa natural join adm_appl_program natural left join adm_appl_program_history as aaph natural left join adm_appl left join work_application_compilation_document as wacd ON (wacd.uw_id = wa.uw_id AND wacd.gso_app_id = wa.appl_id) left join gso_app as ga ON (ga.uw_id = wa.uw_id AND ga.gso_app_id = wa.appl_id) left join gso_decision as gd ON (gd.decision_code = aaph.program_action_code) left join gso_decisionreason as gdr ON (gdr.decision_code = aaph.program_action_code AND gdr.reason_code = aaph.program_reason) left join uw_attendance as ua ON (ua.attendance_code = aaph.approved_load) left join uw_level as ul ON (appl_academic_level = ul.level_code) where %s order by appl_id" % where, **vardict)
return tuple(row._replace (admit_term_id=termtools.fromCode (row.admit_term_id)) for row in apps_list)