"""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 collections import namedtuple
from itertools import groupby
from operator import attrgetter
from ll.xist.ns import html
from uw.local import termtools
from uw.web.html.join import html_join
[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]class citizenship (namedtuple ('citizenship', ['citizenship_code', 'citizenship_description', 'countries'])):
@property
def format_countries (self):
return html_join (
(html.span (country.country_code, title=country.country_name)
for country in self.countries),
', '
)
citizenship_country = namedtuple ('citizenship_country', ['country_code', 'country_name', 'country_short_code'])
[docs]def get_student_citizenship (cursor, uw_ids):
status = cursor.execute_tuples ("select uw_id, citizenship_code, citizenship_description from std_citizenship natural left join uw_citizenship where country_code = 'CAN' AND uw_id = any (%(uw_ids)s)", uw_ids=uw_ids)
countries = dict (groupby (cursor.execute_tuples ("select uw_id, country_code, country_name, country_short_code from std_citizenship natural join uw_country where country_code <> 'CAN' AND uw_id = any (%(uw_ids)s) order by uw_id, country_code", uw_ids=uw_ids), attrgetter ('uw_id')))
return {r.uw_id: citizenship (citizenship_code=r.citizenship_code, citizenship_description=r.citizenship_description, countries=[citizenship_country (c.country_code, c.country_name, c.country_short_code) for c in countries.get (r.uw_id, [])]) for r in status}
[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 std_student natural join std_name_primary natural join (select uw_id, first_name as preferred_name from std_name_preferred) s 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_program using (uw_id, appl_id) left join adm_appl_plan using (uw_id, appl_id, program_order, program_as_of, program_sequence) left 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.*, appl_academic_level, ga.appl_application_method, appl_admit_type, ga.ouac_reference, program_action_description, program_reason_description, wa.unit_code, group_code, compilation_document is not null as compilation_document, compilation_uploader, compilation_uploaded from work_application as wa natural left join adm_appl_program natural left join adm_appl_program_history as aaph natural left join adm_appl as ga left join work_application_compilation_document as wacd using (uw_id, appl_id) natural left join uw_program_action natural left join uw_program_reason 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)