"""Application list UI implementation.
This implements the main application list page.
"""
from datetime import date, timedelta
from ll.xist.ns import html
from uw.web.html.form import render_select, render_checkbox, render_multi_select
from uw.web.wsgi.form import use_form_param
from uw.web.html.bootstrapform import render_bootstrap_form_row, render_bootstrap_form, render_bootstrap_submit
from uw.web.wsgi.function import return_html
from uw.local import termtools
from ...util.identity import use_remote_identity
from ..db.bulk_unitapp import bulk_app_loader
from .list_render import make_application_link_column, application_list_columns, render_sort_table
[docs]def get_distinct_inst_country_codes (cursor, staff_person_id, termcode):
"""Returns a list of distinct institution country codes and names for the given
termcode like : (('CAN', 'Canada') ...)
"""
return cursor.execute_tuples ("select country_code, format('%%s (%%s)', country_name, count(country_code)) AS desc from (select distinct on (uw_id, appl_id, country_code) uw_id, appl_id, country_code, country_name from work_application_staff_lookup join adm_std_academic using (uw_id) join uw_ext_org using (ext_org_id) join uw_country using (country_code) WHERE (admit_term_id, staff_person_id) = (%(termcode)s, %(staff_person_id)s)) t GROUP BY country_code, country_name ORDER BY country_name ASC", termcode=termcode, staff_person_id=staff_person_id)
[docs]def get_distinct_status_codes (cursor, staff_person_id, termcode):
"""Returns a list of distinct status codes for the given
termcode like : (('REJ', 'Denied') ...)
"""
return cursor.execute_tuples ("select status_code, format('%%s (%%s)', status_description, count(status_code)) AS desc FROM (select distinct on (uw_id, appl_id, status_code, eff_time) status_code, status_description FROM work_application_staff_lookup JOIN work_application_status_current USING (uw_id, appl_id) join work_application_status_code using (status_code) WHERE (admit_term_id, staff_person_id) = (%(termcode)s, %(staff_person_id)s) ORDER BY eff_time DESC) t GROUP BY status_code, status_description", termcode = termcode, staff_person_id=staff_person_id)
[docs]def get_distinct_plans_codes (cursor, staff_person_id, termcode):
"""Returns a list of distinct plan codes for the given
termcode like : (('CSM', 'Computer Science') ...)
"""
return cursor.execute_tuples ("select plan_code, format('%%s (%%s, %%s)', plan_transcript_description, plan_code, count(plan_code)) as desc from (select uw_id, appl_id, plan_code from work_application_staff_lookup wasl left join adm_appl_program using (uw_id, appl_id, program_order) left join adm_appl_plan using (uw_id, appl_id, program_order, program_as_of, program_sequence) where plan_code is not null and (wasl.admit_term_id, staff_person_id) = (%(termcode)s, %(staff_person_id)s)) t natural join pps_plan group by plan_code, plan_transcript_description order by plan_code", termcode=termcode, staff_person_id=staff_person_id)
[docs]def get_distinct_inst_ids(cursor, staff_person_id, termcode, inst, instcoun, limit = None):
"""Returns a list of distinct insitution names matching either an institution string or institution country code.
:param cursor: database cursor
:param inst: institution string
:param instcoun: institution country code (e.g. 'CAN')
:param limit: Integer or None, the number of results to show, at maximum.
"""
limit_str = "" if limit is None else " LIMIT {}".format (limit)
instswhere = ""
if inst:
inst = '%' + inst.replace ("%", r"\%").replace ('*', '%') + '%'
instswhere += " and ext_org_name ilike %(inst)s"
if instcoun:
instswhere += " and country_code=%(instcoun)s"
insts = cursor.execute_tuples ("select distinct on (ext_org_id) ext_org_id, ext_org_name from adm_std_academic join work_application_staff_lookup USING (uw_id) JOIN uw_ext_org USING (ext_org_id) where (admit_term_id, staff_person_id) = (%(termcode)s, %(staff_person_id)s){}{}".format(instswhere, limit_str), termcode = termcode, staff_person_id=staff_person_id, inst=inst, instcoun=instcoun)
return insts
[docs]def render_term_select (cursor, name, staff_person_id, default_term=None, class_=None):
"""Render a term selection input element.
:param cursor: DB connection cursor.
:param name: the form control name.
:param staff_person_id: the person whose terms should be included.
:param default_term: the default term, or None to pick a default based on
today's date.
:param class_: A css class to be applied to the select element
Generates an HTML <select> for selecting terms. The relevant terms to be
included are obtained by looking in the database to see which terms have
applications to which the person has access.
"""
terms = (termtools.fromCode (t) for t in cursor.execute_values ("select distinct admit_term_id from work_application_staff_lookup as wa where staff_person_id=%(staff_person_id)s order by admit_term_id desc", staff_person_id=staff_person_id))
if default_term is None:
default_term = termtools.fromDate (
date.today () + timedelta (days=9*30))
return termtools.render_term_select (name, terms, default_term, class_=class_)
[docs]def render_status_select (cursor, name, status=None, all=False, staff_person_id=False, termcode=False, class_=None):
"""Render a status selection input element.
:param cursor: DB connection cursor.
:param name: the form control name.
:param staff_person_id: the person whose terms should be included. Expected alongside termcode.
:param termcode: a term code if the statuses should be limited to those available
:param status: the current status, or None.
:param all: whether the "OLD" status should be included (will be ignored if termcode is specified)
:param class_: A css class to be applied to the select element
Returns an HTML <select> for selecting status values.
"""
if termcode and staff_person_id:
status_codes = get_distinct_status_codes (cursor, staff_person_id, termcode)
else:
where = "true" if all else "status_code <> 'OLD'"
status_codes = cursor.execute_tuples ("select status_code, status_description from work_application_status_code where %s order by status_sequence" % where)
return render_select (name, status_codes, status, blank=True, class_=class_)
[docs]def render_request_select (cursor, name, default_staff=None, class_=None):
"""Render a requested supervisor selection input element.
:param cursor: DB connection cursor.
:param name: the form control name.
:param default_staff: the default staff or None.
:param class_: A css class to be applied to the select element
Returns an HTML <select> for selecting requested supervisor values.
"""
faculty_members = ((f.person_id, f.name) for f in cursor.execute_tuples ("select distinct person_id, givennames || ' ' || surname as name from work_role_assignment natural join person_person order by name, person_id"))
return termtools.render_select (name, faculty_members, default_staff, class_=class_)
[docs]def render_inst_options (cursor, values, staff_person_id, termcode):
"""Render a status selection input element.
:param cursor: DB connection cursor.
:param values: a dictionary containing the default values for 'inst', 'instcoun', and 'citizen'
:param staff_person_id: the person whose terms should be included.
:param termcode: a term code if the statuses should be limited to those available
Returns an HTML <div> formatted with bootstrap for the institution and citizenship inputs.
"""
country_codes = get_distinct_inst_country_codes (cursor, staff_person_id, termcode)
citizen_status = (("DOM", "Domestic"), ("INT", "International"))
form_tuples = [
('Previous institution (“*” matches any string):',
html.input (type="text", name="inst", id="inst", size="21", value=values['inst'], class_="form-control inst"),
"col-xs-5"),
('Previous institution country:',
render_select ("instcoun", country_codes, value=values['instcoun'], blank=True, class_="form-control instcoun"),
"col-xs-offset-1 col-xs-3"),
('Canadian citizenship status:',
render_select ("citizen", citizen_status, value=values['citizen'], blank=True, class_="form-control citizen"),
"col-xs-3"),
]
return render_bootstrap_form_row(form_tuples, tail_class="bottom-buffer")
[docs]def render_plan_options(cursor, plan, staff_person_id, termcode, selected=[]):
"""Render a plan selection input element.
:param cursor: DB connection cursor.
:param plan: a dictionary containing the default values for 'inst', 'instcoun', and 'citizen'
:param staff_person_id: the person whose terms should be included.
:param termcode: a term code if the statuses should be limited to those available
Returns an HTML <select> for selecting status values.
"""
plan_fieldnames = get_distinct_plans_codes (cursor, staff_person_id, termcode)
select = render_multi_select('plans', plan_fieldnames, values=selected, class_="form-control plans")
return select
@use_remote_identity
@use_form_param
@return_html
def list_index_handler (cursor, remote_identity, form):
"""Application search form URL handler.
Displays the application list form. If form submission values are
provided, also displays the matching applications.
**TODO: why doesn't this check for permissions?
Should be in ['ADC', 'DIR', 'FAC']. I think it's OK because
unauthorized people just won't find anything when they search.**
"""
term = form.optional_field_value ('term')
if term is not None:
term = termtools.fromCode (term)
person_id = remote_identity.person_id
default_term = termtools.fromDate (date.today () + timedelta (days=9*30)) if term is None else term
term_select = render_term_select (cursor, "term", person_id, default_term=default_term, class_="term form-control")
selected_term_code = default_term.code()
state = form.optional_field_value ('state') or None
state_select = render_status_select (cursor, "state", staff_person_id=person_id, termcode=selected_term_code, status=state, class_="form-control state")
request = form.optional_field_value ('request') or None
request_select = render_request_select (cursor, "request", default_staff=None, class_="request form-control")
overdue_only = "overdue" in form
overdue_only_control = html.label (render_checkbox
("overdue", overdue_only), ' Only show overdue applications')
quantum_user = cursor.execute_required_value ("select exists (select from work_role_assignment where (role_id, person_id) = ('IQC', %(person_id)s))", person_id=person_id)
quantum_only = "quantum" in form
quantum_only_control = html.label (render_checkbox
("quantum", quantum_only), ' Only show quantum applications')
inst_dict = {n : form.optional_field_value (n) for n in ('inst', 'instcoun', 'citizen')}
inst_options = render_inst_options(cursor, inst_dict, person_id, selected_term_code)
insts = None
citizenship_codes = None
plans = form.multiple_field_value('plans')
plan_options = render_plan_options(cursor, plans, person_id, selected_term_code, selected=plans)
plan_single_only = "singleplans" in form
plan_multi_only = "multiplans" in form
plan_single_only_control = html.label (render_checkbox("singleplans", plan_single_only), ' Only show single-plan applications')
plan_multi_only_control = html.label (render_checkbox("multiplans", plan_multi_only), ' Only show multi-plan applications')
plan_app_ids = None
uwids = None
if term is None:
# Initial form request
apps = None
requests = None
else:
# Search for applications
conditions = []
parameters = {}
conditions.append ("from work_application_staff_lookup where (admit_term_id, staff_person_id) = (%(termcode)s, %(staff_person_id)s)")
parameters['termcode'] = term.code ()
parameters['staff_person_id'] = person_id
if state or overdue_only or quantum_only:
where = []
if state is not None:
where.append ("status_code = %(state)s")
if overdue_only:
where.append ("deadline < current_timestamp")
if quantum_only:
where.append ("(exists (select from (adm_appl_program natural join adm_appl_plan) as aap where (aap.uw_id, aap.appl_id) = (wasc.uw_id, wasc.appl_id) and plan_code in ('CSQID', 'CSQID3', 'CSQIM', 'PHYSQID', 'PHYSQID3', 'PHYSQID3Y1', 'PHYSQIM', 'AMQID', 'AMQIM', 'COQID', 'COQIM')) or exists (select from work_application_interest AS wai where (wai.uw_id, wai.appl_id) = (wasc.uw_id, wasc.appl_id) and faculty_interest_area in ('QUANCOMP', 'QNTMTHRY', 'QUANTUMCOM', 'QUANTCOM-E', 'QUANTCOM-T', 'QUANTUMCOM')))")
conditions.append ("from work_application_status_current wasc where " + " and ".join (where))
parameters['state'] = state
if request:
conditions.append ("from work_application_request where requested_person_id = %(requested_person_id)s")
parameters['requested_person_id'] = request
if inst_dict['inst'] or inst_dict['instcoun']:
insts = [i.ext_org_id for i in get_distinct_inst_ids (cursor, person_id, term.code (), inst_dict['inst'], inst_dict['instcoun'])]
conditions.append ("from work_application natural join adm_std_academic where ext_org_id = any (%(insts)s)")
parameters['insts'] = insts
if inst_dict['citizen']:
conditions.append ("from work_application natural join (select * from std_citizenship where country_code = 'CAN' and citizenship_code = any (%(citizenship_codes)s)) _")
parameters['citizenship_codes'] = ['CAN-SP'] if inst_dict['citizen'] == 'INT' else ['CTZ', 'PRR']
if plans:
conditions.append ("from adm_appl_program join adm_appl_plan using (uw_id, appl_id, program_order, program_as_of, program_sequence) where plan_code = any (%(plans)s)")
parameters['plans'] = plans
if plan_single_only != plan_multi_only: # XOR
conditions.append ("from (select *, count(*) over (partition by uw_id, group_code, unit_code, admit_term_id) as count from work_application_term) t where count %s" % ("= 1" if plan_single_only else "> 1"))
query = "with t as (%s) select appl_id from t natural join std_name_primary order by last_name, first_name, middle_name, uw_id, appl_id" % " intersect ".join ("(select uw_id, appl_id %s)" % c for c in conditions)
apps = cursor.execute_values (query, **parameters)
if apps is None:
app_list = html.p ()
elif apps:
apps = bulk_app_loader (cursor, apps)
table_columns = [make_application_link_column ("../")]
table_columns.extend (application_list_columns)
table = render_sort_table (table_columns, apps)
table["class"] += " col-xs-12 table"
app_list = [
html.p ("%s applications found." % len (apps)),
html.div (table, class_ = "row")
]
else:
app_list = html.p ('No matching applications found.')
term_app = render_bootstrap_form_row ([
('Term:', term_select, "col-xs-5"), ('Application State:', state_select, "col-xs-offset-1 col-xs-3"), ('Requested Supervisor:', request_select, "col-xs-3")
], tail_class="bottom-buffer")
plan_options = render_bootstrap_form_row ([
('Plans:', plan_options, "col-xs-12")
])
plan_checkboxes = render_bootstrap_form_row ([
(False, html.div (plan_single_only_control, class_="checkbox"), "col-xs-5"),
(False, html.div (plan_multi_only_control, class_="checkbox"), "col-xs-offset-2 col-xs-5")
])
checkboxes = render_bootstrap_form_row ([
(False, html.div (overdue_only_control, class_="checkbox"), "col-xs-5"),
(False, html.div (quantum_only_control, class_="checkbox"), "col-xs-offset-2 col-xs-5") if quantum_user else None
])
search_button = render_bootstrap_form_row ([(False, html.div(render_bootstrap_submit(" Search…")), "col-xs-12 text-center")])
return 'List Applications', [
html.div(
html.p (html.a ('Main Menu', href="../")),
html.p ('This page is for obtaining lists of applications meeting certain criteria. To search for a specific application, please use the ', html.a ('view search form', href="../view/"), '.'),
render_bootstrap_form ([
term_app,
inst_options,
plan_options,
plan_checkboxes,
checkboxes,
search_button
]),
app_list,
class_ = "main container")
]