"""Application database access abstraction.
This module defines a class whose instances correspond to individual
graduate applications. Methods are provided to pull various pieces of
information about the application from the database.
"""
import datetime
from itertools import groupby
from operator import attrgetter
from urllib.parse import urljoin
from ll.xist.ns import html
from uw.local import termtools
from uw.emailutils import emailaddr
from uw.web.html.join import html_join
from ..webui.list_render import format_citizenship
from .unitapp_get import get_app_rating, get_app_accept_counts, get_app_info, get_app_applicant, get_app_plan_info, get_student_citizenship, get_student_education_gpa, get_student_education_degree, get_student_education_attendance, get_student_education
genders = {
'F': 'Female',
'M': 'Male',
}
levels = {
'GNL': "Graduate, Non-Degree",
'M': "Master's",
'D': "PhD",
}
attendance = {
'P': "Part Time",
'F': "Full Time",
}
[docs]class UnitApplication (object):
"""Class to represent a candidate application.
Instances of this class represent candidate applications, corresponding to
the table _grad.work_application. These are unique per
candidate/unit/term. Methods are provided to access various aspects of the
application stored in numerous tables. Many of the methods cache the
values returned so multiple calls to the same method will not hit the
database repeatedly.
"""
def __init__ (self, cursor, row):
self.__cursor = cursor
self.__uw_id = row.uw_id
self.__appl_id = row.appl_id
self.__group_code = row.group_code
self.__unit_code = row.unit_code
self.__status = row.status_code
self.__statusseq = row.status_sequence
self.__statusdesc = row.status_description
self.__deadline = row.deadline
self.__reason_code = row.deny_reason_code
self.__other_apps = None
self.__subplans = None
self.__requested_supervisor = None
self.__exchange_agreement =None
self.__research_areas = None
self.__title_components = None
self.__applicant = None
self.__phones = None
self.__addresses = None
self.__app_info = None
self.__plan_info = None
self.__reasondesc = None
self.__gsohistory = None
self.__conditions = None
self.__adm_conditions = None
self.__tests = None
self.__student_tests = None
self.__citizenship = None
self.__student_education = None
self.__languages = None
self.__student_languages = None
self.__employment = None
self.__student_employment = None
self.__finance = None
self.__adm_finance = None
self.__study_agreement = None
self.__history = None
self.__referees = None
self.__rating = None
self.__rating_loaded = False
self.__counts = None
self.__waitlist_count = None
self.__accept_count = None
@property
def cursor (self):
"""Database cursor"""
return self.__cursor
@property
def counts (self):
"""Named tuple with accepted and waitlist counts.
"""
return self.__counts
@counts.setter
def counts (self, value):
self.__counts = value
@property
def uw_id (self):
"""UW ID number associated with the application
:rtype: string
"""
return '%08d' % self.__uw_id
@property
def appl_id (self):
"""Application id for the application
:rtype: string
"""
return self.__appl_id
@property
def admit_term (self):
"""Application's admit term
:rtype: string
"""
return self.app_info.admit_term_id
@property
def group_code (self):
"""Application's group code for the applied program (e.g. 'MAT' for Mathematics).
:rtype: string
"""
return self.__group_code
@property
def unit_code (self):
"""Application's unit_code like 'CS'.
:rtype: string
"""
return self.__unit_code
@property
def status (self):
"""Application's current status
:rtype: string
"""
return self.__status
@property
def statusseq (self):
"""Application's current status sequence (a number assosciated with each status)
:rtype: int
"""
return self.__statusseq
@property
def statusdesc (self):
"""Application's current status description
:rtype: string
"""
return self.__statusdesc
@property
def faculty_can_waitlist (self):
"""Determine if faculty can waitlist this application based on its state.
:return: true if faculty can waitlist, false otherwise
:rtype: bool
"""
return self.status in ['NEW', 'HLD', 'INR', 'CIR']
[docs] def faculty_can_accept (self, view_state):
"""Determine if faculty can accept this application based on its state.
:param view_state: the current opinion of the faculty member of this
application.
:return: whether the faculty member can accept this application.
:rtype: bool
The current opinion is needed for the case of the FAC state
("Resolving Supervision"). In this state, a waitlisting can be
upgraded to an offer but other than that new offers are not allowed.
"""
return self.status in ['CIR'] or self.status in ['FAC'] and view_state == 'WTL'
@property
def deadline (self):
return self.__deadline
[docs] def find_due_days (self, now=None):
"""Find how many days from now until the current workflow deadline.
:param now: optionally use this date instead of today.
Essentially, computes the current workflow deadline minus the date now.
So positive results mean the deadline is that many days in the future
while negative results mean the application is overdue for a state
change.
"""
deadline = self.deadline
if deadline == datetime.datetime.max:
return None
if now is None:
now = datetime.date.today ()
return (deadline.date () - now).days
def __get_accept_counts (self):
"""Gets and sets the unitapp waitlist and accept counts.
:rtype: None
"""
if self.__counts is None:
self.__counts = get_app_accept_counts(self.cursor, self.where, self.vars)[0]
self.__waitlist_count = self.__counts.waitlist_count
self.__accept_count = self.__counts.accept_count
def __get_plan_info(self):
"""Gets the plan information (plan_code and plan_transcript_description)
and sets it in the unitapp.
:rtype: None
"""
self.__plan_info = get_app_plan_info(self.cursor, self.where, self.vars)[0]
@property
def waitlist_count (self):
"""Returns the number of faculty members that have waitlisted the application.
:rtype: int (>= 0)
"""
if self.__waitlist_count is None:
self.__get_accept_counts ()
return self.__waitlist_count
@property
def accept_count (self):
"""Returns the number of faculty members that have accepted the application.
:rtype: int (>= 0)
"""
if self.__accept_count is None:
self.__get_accept_counts ()
return self.__accept_count
@property
def other_apps (self):
"""Returns a tuple of all app_ids and term_code other applications an applicant has.
"""
if self.__other_apps is None:
rows = self.cursor.execute_tuples ("select r.appl_id, r.admit_term_id from work_application as l join work_application_term as r using (uw_id, group_code, unit_code) where (l.uw_id, l.appl_id) = (%(uw_id)s, %(appl_id)s) and r.appl_id <> l.appl_id order by r.admit_term_id", **self.vars)
self.__other_apps = tuple (row._replace (admit_term_id=termtools.fromCode (row.admit_term_id)) for row in rows)
return self.__other_apps
@property
def vars (self):
"""Returns a dictionary of properties that identify the application to
the database. This is the uw_id and the app_id.
"""
return {'uw_id': self.uw_id, 'appl_id': self.appl_id}
@property
def where (self):
"""Returns a string indented to be filled in with the variables from
the vars property for use in database strings.
"""
return "((wa.uw_id, wa.appl_id) = (%(uw_id)s, %(appl_id)s))"
@property
def subplans (self):
"""Returns a tuple of the titles of subplans
"""
if self.__subplans is None:
self.__subplans = tuple (self.cursor.execute_values ("select subplan_title from work_application_subplan as wa where %s" % self.where, **self.vars))
return self.__subplans
@property
def requested_supervisor (self):
"""Returns a tuple of the names of the application's requested supervisors
"""
if self.__requested_supervisor is None:
self.__requested_supervisor = tuple (self.cursor.execute_values ("select adm_comment_text from adm_appl_program_comment where (uw_id, appl_id) = (%(uw_id)s, %(appl_id)s) and adm_comment_code='SUPR'", **self.vars))
return self.__requested_supervisor
@property
def exchange_agreement (self):
"""Returns a tuple of the application's exchange agreement
"""
if self.__exchange_agreement is None:
self.__exchange_agreement = tuple (self.cursor.execute_values ("select adm_comment_text from adm_appl_program_comment where (uw_id, appl_id) = (%(uw_id)s, %(appl_id)s) and adm_comment_code='GEX'", **self.vars))
return self.__exchange_agreement
@property
def research_areas (self):
"""Returns a tuple of the research areas of intrest.
"""
if self.__research_areas is None:
# original had * rather than wa.* ?!
self.__research_areas = tuple (self.cursor.execute_tuples ("select wfia.*, wai.faculty_interest_area as checked from work_application as wa join work_faculty_interest_area as wfia using (group_code, unit_code) natural left join work_application_interest as wai where %s" % self.where, **self.vars))
return self.__research_areas
@property
def title_components (self):
"""Returns a tuple of the application title consisting of (in order):
uw_id, full name, gender, citizenship, attendance, admit_term, and plan_info.
This is primarly used to display application titles for the view pages.
"""
if self.__title_components is None:
self.__title_components = tuple ([
self.uw_id,
self.names,
genders.get (self.applicant.gender_code, self.applicant.gender_code),
str (format_citizenship (self)),
attendance.get (self.app_info.approved_load, self.app_info.approved_load) or 'NONE',
self.admit_term.description (),
self.plan_code or 'NONE',
])
return self.__title_components
@property
def title (self):
"""Return a string with title_components joined with em-dashes.
"""
return '—'.join (self.title_components)
@property
def copypaste_summary (self):
"""Return a string that is used when copying the title.
Same as the title property but joined with '--' instead of em-dashes.
"""
return '--'.join (self.title_components)
@property
def applicant (self):
"""Returns a named tuple with gso applicant information including:
name, gender, birth, address and contact infomation
"""
if self.__applicant is None:
self.__applicant = get_app_applicant(self.cursor, self.where, self.vars)[0]
return self.__applicant
@applicant.setter
def applicant(self, value):
self.__applicant = value
@property
def names (self):
"""Returns the applicant's full name in the format "Lastname, Firstname Middlename (Preferredname)"
"""
applicant = self.applicant
givennames = applicant.first_name
if applicant.middle_name:
givennames += ' ' + applicant.middle_name
if applicant.preferred_name and applicant.preferred_name != givennames:
givennames += ' (' + applicant.preferred_name + ')'
return applicant.last_name + ', ' + givennames
@property
def phones (self):
"""Return the applicant's telephone numbers.
"""
if self.__phones is None:
self.__phones = self.cursor.execute_tuples ("select * from std_phone natural join uw_phone_type where uw_id = %(uw_id)s order by phone_type_description", **self.vars)
return self.__phones
@property
def addresses (self):
"""Return the applicant's mailing addresses.
"""
if self.__addresses is None:
self.__addresses = self.cursor.execute_tuples ("select * from std_address join uw_address_type using (address_type_code) where uw_id = %(uw_id)s order by address_type_description", **self.vars)
return self.__addresses
@property
def app_info (self):
"""Return a named tuple of miscellaneous information like compliation document, reason_description for status etc.
"""
if self.__app_info is None:
self.__app_info = get_app_info(self.cursor, self.where, self.vars)[0]
return self.__app_info
@app_info.setter
def app_info (self, value):
self.__app_info = value
@property
def gsohistory (self):
"""Return a tuple of named tuples containing information about each status change in the gso history.
"""
if self.__gsohistory is None:
self.__gsohistory = tuple (self.cursor.execute_tuples ("select aaph.*, program_action_description, program_reason_description, plan_code, plan_transcript_description from work_application as wa join adm_appl_program_history as aaph USING (uw_id, appl_id) natural join adm_appl left join adm_appl_plan USING (uw_id, appl_id, program_order, program_as_of, program_sequence) natural left join uw_program_action as gd natural left join uw_program_reason left join pps_plan using (plan_code) where %s order by program_as_of desc, appl_id" % self.where, **self.vars))
return self.__gsohistory
@property
def adm_conditions (self):
"""Return a tuple of named tuples contraining information about gso requirement(s) to enter the next status
"""
if self.__adm_conditions is None:
self.__adm_conditions = tuple (self.cursor.execute_tuples ("select * from adm_appl_condition natural left join uw_condition natural left join uw_condition_status where (uw_id, appl_id) = (%(uw_id)s, %(appl_id)s) order by condition_code", **self.vars))
return self.__adm_conditions
@property
def student_tests (self):
"""Return a tuple of tuples containing information about test information.
"""
if self.__student_tests is None:
result = self.cursor.execute_tuples ("select * from (select distinct on (uw_id, test_type_code, test_component_code, test_date, test_source_code) * from adm_std_test natural left join lateral (select test_type_code, test_component_code, test_score, percentile, scale_date from test_percentile where scale_date <= test_date) tp where uw_id = any (%(uw_ids)s) order by uw_id, test_type_code, test_component_code, test_date, test_source_code, scale_date desc) t order by test_type_code = 'TOEFL' desc, test_type_code = 'GRE' desc, test_type_code, test_date, test_component_code", uw_ids = [self.__uw_id])
self.__student_tests = tuple ((test, tuple (component)) for test, component in groupby (result, attrgetter ('test_type_code', 'test_date')))
return self.__student_tests
@property
def citizenship (self):
if self.__citizenship is None:
self.__citizenship = get_student_citizenship (self.cursor, [self.__uw_id]).get (self.__uw_id)
return self.__citizenship
@property
def student_education (self):
"""Return a dictionary that maps uw_id to a tuple of tuples which contain a namedtuple of institution information and the list of dictionaries obtained from the corresponding attendance.
"""
if self.__student_education is None:
education = get_student_education(self.cursor, [self.__uw_id])
self.__student_education = education
return self.__student_education
@property
def student_languages (self):
"""Return a tuple of tuples containing preferred languages by the applicant
"""
if self.__student_languages is None:
self.__student_languages = tuple (self.cursor.execute_tuples ("select * from adm_std_language natural join iso_639_3_language where uw_id = any (%(uw_ids)s) order by language_code", uw_ids = [self.__uw_id]))
return self.__student_languages
@property
def joinlanguages (self):
"""Return a string of the preferred languages for the applicant
"""
return ", ".join (row.language for row in self.__languages)
@property
def student_employment (self):
"""Return a tuple of named tuples containing past work information (e.g. employer, title).
"""
if self.__student_employment is None:
self.__student_employment = tuple (self.cursor.execute_tuples ("select * from adm_std_employment natural join uw_country where uw_id = any (%(uw_ids)s) order by end_date desc", uw_ids = [self.__uw_id]))
return self.__student_employment
@property
def adm_finance (self):
"""Return a tuple of tuples containing information about funding sources.
"""
if self.__adm_finance is None:
self.__adm_finance = tuple (self.cursor.execute_tuples ("select * from adm_appl_program_financial_aid_history natural left join uw_aid_category natural join uw_aid_status where (uw_id, appl_id) = (%(uw_id)s, %(appl_id)s) and aid_status_code <> 'P' order by aid_category_description", **self.vars))
return self.__adm_finance
@property
def study_agreement (self):
"""Returns a tuple of the application's exchange agreement
"""
if self.__study_agreement is None:
self.__study_agreement = tuple (self.cursor.execute_values ("select study_agreement_description from adm_appl_study_agreement natural join uw_study_agreement where (uw_id, appl_id) = (%(uw_id)s, %(appl_id)s)", **self.vars))
if not self.__study_agreement:
self.__study_agreement = tuple (self.cursor.execute_values ("select adm_comment_text from adm_appl_program_comment where (uw_id, appl_id) = (%(uw_id)s, %(appl_id)s) and adm_comment_code='GIA'", **self.vars))
return self.__study_agreement
@property
def history (self):
"""Return a tuple of tuples containing information about the status changes throughout the application's history.
"""
if self.__history is None:
self.__history = tuple (self.cursor.execute_tuples ("select * from work_application as wa natural join work_application_status natural join work_application_status_code where %s order by eff_time desc" % self.where, **self.vars))
return self.__history
@property
def referees (self):
"""Return a tuple of tuples containing information about referal information.
"""
if self.__referees is None:
self.__referees = tuple (self.cursor.execute_tuples ("select * from work_application as wa natural join adm_appl_referee join uw_country on (country_code = referee_country) where %s order by referee_seq" % self.where, **self.vars))
return self.__referees
@property
def rating (self):
"""Return a named tuple for the rating for the application (e.g. A-C(+-), CWD) containing rating_code and other rating information.
"""
if not self.__rating_loaded:
rating = get_app_rating(self.cursor, self.where, self.vars)[0]
self.__rating = rating if rating and rating.rating_code else None
self.__rating_loaded = True
return self.__rating
@rating.setter
def rating (self, value):
self.__rating = value
@property
def reason_code (self):
"""Return the reason code if applicable
"""
return self.__reason_code
@property
def reasondesc (self):
"""Return the reason description for being in the current state, if applicable.
"""
if self.__reasondesc is None and self.reason_code is not None:
self.__reasondesc = self.cursor.execute_required_value ("select program_reason_description from uw_program_reason where (program_action_code, program_reason_code) = ('DENY', %(reason_code)s)", reason_code=self.reason_code)
return self.__reasondesc
@property
def plan_code (self):
"""Return the plan_code (like CSM).
"""
if self.__plan_info is None:
self.__get_plan_info()
return self.__plan_info.plan_code
@plan_code.setter
def plan_code (self, value):
self.__plan_info = value
@property
def plan_transcript_description (self):
"""Return the PeopleSoft plan_code transcript description
"""
if self.__plan_info is None:
self.__get_plan_info()
return self.__plan_info.plan_transcript_description
[docs] def find_supervisors (self):
"""Returns a list of the supervisor(s) person ids for the given application, can be of arbitrary length.
"""
return self.cursor.execute_values("select faculty_person_id from work_application_faculty_acceptance as wa where %s and acceptance_cancelled is null" % self.where, **self.vars)
[docs] def find_authorized (self, roles):
"""Find the individuals authorized for a given set of roles with respect to this application.
"""
result = set ()
for role in roles:
result.update (self.cursor.execute_values ("select person_id from work_role_assignment where (group_code, unit_code, role_id) = (%(group)s, %(unit)s, %(role_id)s)", group=self.group_code, unit=self.unit_code, role_id=role))
return result
[docs] def find_concerned (self, include_unviewed = True):
"""Find the faculty members concerned with this application.
This means the ones available through the
work_application_faculty_interest view that were not expressed as not intrested.
:param include_unviewed: Only include people that have not viewed the application
"""
where = self.where + " AND " + ("(view_state <> 'NOI' or view_state is NULL)" if include_unviewed else "view_state <> 'NOI'")
result = set (self.cursor.execute_values (("SELECT distinct faculty_person_id from work_application_faculty_interest as wa LEFT JOIN work_application_faculty USING (uw_id, appl_id, faculty_person_id)"
"WHERE %s") % where, **self.vars))
return result
[docs] def find_interested (self):
"""Find the faculty members interested in this application.
This means those who have expressed an opinion of "Interested" or who
have waitlisted or accepted the application. This is different from
the concept captured by the work_application_faculty_interest view,
which includes any application that a faculty member should be aware of.
"""
return set (self.cursor.execute_values ("select faculty_person_id from work_application_faculty as wa where %s and view_state in ('INT', 'WTL', 'ACC')" % self.where, **self.vars))
[docs] def base_url (self, request_uri='https://odyssey.uwaterloo.ca/grad/'):
"""Get the base URL for this application.
This is the URL of the main page for this application.
**TODO: get base URL from CGI variables instead of hardcoding.**
"""
return urljoin (request_uri, "view/%s/" % self.appl_id)
[docs] def director_email (self):
"""Get the email address of the Director for this application.
This is obtained from the work_application_instance record for the Unit
which owns this application.
"""
result = self.cursor.execute_optional_tuple ("select * from work_application_instance where (group_code, unit_code) = (%(group_code)s, %(unit_code)s)", group=self.group_code, unit=self.unit_code)
if result is None:
return None
else:
return emailaddr (result.director_email_name, result.director_email_addr)
[docs] def level_code (self):
"""Get the deemed level of this application.
"""
return self.cursor.execute_required_value ("select appl_academic_level from adm_appl where (uw_id, appl_id) = (%(uw_id)s, %(appl_id)s)", **self.vars)
[docs] def applicant_email (self):
"""Get the applicant's email address.
"""
result = self.cursor.execute_optional_tuple ("select first_name, last_name, applicant_email from work_application as wa join std_student using (uw_id) join std_name_preferred using (uw_id) join adm_std_email using (uw_id) where %s" % self.where, **self.vars)
if result is None:
return None
else:
return emailaddr ('%s %s' % (result.first_name, result.last_name), result.applicant_email)
[docs] def set_faculty_opinion (self, person_id, view_state):
"""Set a faculty member's opinion of this application.
:param person_id: the identity of the faculty member whose opinion
should be recorded.
:param view_state: the new opinion, an existing value in column
_grad.work_application_faculty_state.view_state.
"""
# Ensure that user is recorded as having seen this application
self.get_faculty_information (person_id)
# Update their opinion of this application
self.cursor.execute_none ("update work_application_faculty as wa set view_state=%%(view_state)s where %s and faculty_person_id = %%(person_id)s" % self.where, person_id=person_id, view_state=view_state, **self.vars)