"""Cursor class for Instructional Support.
Inherits from the usual cursor class with added methods for commonly-needed
queries.
"""
from uw.local.dbtools import Cursor
from uw.sql.dbapi import *
from uw.color import hues
[docs]class Cursor (Cursor):
admin_by_id = make_execute_optional_tuple ("select * from teaching_admin where admin_id=%(admin_id)s")
admin_info_by_id = make_execute_optional_tuple ("select admin_id, group_code, unit_code, subject_code, career_code, course_id, (select array_agg (format ('%%s (%%s)', cal_course_format_labels (course_id), to_char (course_id))) from (select t::quest_course_id from unnest (coalesce (course_ids, array[course_id::integer])) as t) as t (course_id)) as course_descriptions, course_ids, coalesce (tac.session_code, tach.session_code) as session_code, teaching_admin_import_check_admin_split (admin_id) as split_by_instructor, parent_admin_id, instructor_person_ids from teaching_admin natural left join teaching_admin_group natural left join teaching_admin_unit natural left join teaching_admin_subject natural left join teaching_admin_course tac left join teaching_admin_course_heldwith tach using (admin_id) left join teaching_admin_course_instructors using (admin_id) where admin_id=%(admin_id)s")
admins_by_term = make_execute_tuples ("select * from teaching_admin natural join teaching_admin_term where term_id=%(term_id)s order by admin_description")
offering_by_id = make_execute_optional_tuple ("select *, not ata is null as admin_accommodation from teaching_admin_term_plus natural join teaching_admin left join accom_teaching_admin as ata on (accom_admin_id=admin_id) where (term_id, admin_id) = (%(term_id)s, %(admin_id)s)")
offering_select = "select distinct term_id, admin_id, admin_description from auth_offering_personnel_complete natural join teaching_admin where %s order by term_id desc, admin_description"
offerings_by_person = make_execute_tuples (offering_select % "person_id=%(person_id)s and role_current")
offerings_by_person_recent = make_execute_tuples (offering_select % "person_id=%(person_id)s and term_id >= uw_term_from_time (now ()::timestamp - %(interval)s::interval) and role_current")
offerings_by_person_term = make_execute_tuples (offering_select % "(term_id, person_id) = (%(term_id)s, %(person_id)s) and role_current")
sections_by_offering = make_execute_tuples ("select *, off_instruct_section_format (term_id, section_id) AS section_description, teaching_admin_import_find_section_instructors (term_id, section_id) as instructors from teaching_admin join teaching_admin_section_mapping using (admin_id) join teaching_admin_contains_complete on (admin_id = inner_admin_id) natural left join teaching_admin_term_exam_schedule where (term_id, outer_admin_id) = (%(term_id)s, %(admin_id)s) order by section_description")
divisions_by_offering = make_execute_tuples ("select * from division_division natural join auth_role_maintainer where (term_id, admin_id) = (%(term_id)s, %(admin_id)s) order by division_description")
order_by_exam = make_execute_tuples ("select * from exam_sort_order_complete where exam_id = %(exam_id)s and (in_use or %(all)s) order by division_order_index")
sitting_by_id = make_execute_optional_tuple ("select * from exam_sitting_plus where sitting_id = %(sitting_id)s")
offering_sitting_by_id = make_execute_optional_tuple ("select * from exam_sitting_plus where (sitting_term_id, sitting_admin_id, sitting_id)=(%(term_id)s, %(admin_id)s, %(sitting_id)s)")
exam_sitting_by_id = make_execute_optional_tuple ("select es.* from exam_exam_sitting natural join exam_exam_plus join exam_sitting_plus as es using (sitting_id) where (exam_id, sitting_id) = (%(exam_id)s, %(sitting_id)s)")
sittings_by_exam = make_execute_tuples ("select ees.*, es.sitting_admin_id, es.start_time, es.exam_independent, admin_description from exam_exam_sitting_plus as ees join exam_sitting as es using (sitting_id) join teaching_admin on (sitting_admin_id=admin_id) where exam_id = %(exam_id)s order by start_time, admin_description")
sittings_by_offering = make_execute_tuples ("select * from exam_sitting_plus where (sitting_term_id, sitting_admin_id) = (%(term_id)s, %(admin_id)s) order by start_time")
exam_by_id = make_execute_optional_tuple ("select * from exam_exam_plus where exam_id=%(exam_id)s")
[docs] def exams_by_offering (self, term_id, admin_id):
# This is the only way I've found to get Postgres to use a reasonable
# query plan when querying exam_exam_plus.
exam_ids = self.execute_values ("select exam_id from exam_exam where (term_id, admin_id) = (%(term_id)s, %(admin_id)s)", term_id=term_id, admin_id=admin_id)
return self.execute_tuples ("select * from exam_exam_plus where exam_id = any (%(exam_ids)s) order by primary_start_time", exam_ids=exam_ids)
offering_exam_by_id = make_execute_optional_tuple ("select * from exam_exam_plus where (term_id, admin_id, exam_id)=(%(term_id)s, %(admin_id)s, %(sitting_id)s)")
sitting_exam_by_id = make_execute_optional_tuple ("select ee.* from exam_exam_sitting natural join exam_exam_plus as ee natural join exam_sitting where (exam_id, sitting_id) = (%(exam_id)s, %(sitting_id)s)")
sitting_room_by_id = make_execute_optional_tuple ("select * from exam_sitting_room_plus natural join room_room as rr where (sitting_id, room_id) = (%(sitting_id)s, %(room_id)s)")
unit_get_permissions = make_execute_values ("select distinct role_code from teaching_admin_unit natural join auth_admin_personnel_complete where role_current and (unit_code, person_id) = (%(unit_code)s, %(person_id)s)")
admin_get_active_roles = make_execute_tuples ("select * from auth_role where role_code in ('INST', 'ISA', 'ISC', 'ADMIN', 'EXAM', 'TAMAN', 'TAAST') order by role_description")
admin_get_permissions = make_execute_values ("select distinct role_code from auth_admin_personnel_complete where role_current and (admin_id, person_id) = (%(admin_id)s, %(person_id)s)")
offering_get_permissions = make_execute_values ("select distinct role_code from auth_offering_personnel_complete where role_current and (term_id, admin_id, person_id) = (%(term_id)s, %(admin_id)s, %(person_id)s)")
user_get_global_permissions = make_execute_values ("select distinct role_code from auth_global_personnel_complete where role_current and person_id = %(person_id)s")
personnel_by_admin = make_execute_tuples ("select * from auth_admin_personnel_complete natural join auth_role_maintainer natural join person_identity_complete natural left join auth_role natural join (select admin_id as auth_admin_id, admin_description as auth_admin_description from teaching_admin) as t where (admin_id) = (%(admin_id)s) and role_current order by surname, givennames, role_description")
personnel_by_offering = make_execute_tuples ("select * from auth_offering_personnel_complete natural join auth_role_maintainer natural join person_identity_complete natural left join auth_role natural left join (select admin_id as auth_admin_id, admin_description as auth_admin_description from teaching_admin) as t where (term_id, admin_id) = (%(term_id)s, %(admin_id)s) and role_current order by surname, givennames")
exam_sitting_room_by_id = make_execute_optional_tuple ("select * from exam_exam_sitting_room_plus natural join room_room where (exam_id, sitting_id, room_id) = (%(exam_id)s, %(sitting_id)s, %(room_id)s)")
exam_series_by_code = make_execute_optional_tuple ("select * from exam_exam_series where series_code=%(series_code)s")
exam_default_sequence_choices = make_execute_tuples ("select n, exam_format_sequence_number (exam_id, n) from generate_series (0, 9500, 500) as n, exam_exam where exam_id = %(exam_id)s")
offering_uploader_choices = make_execute_tuples ("select person_id, surname || ', ' || givennames || ' (' || userid || ')' as names from exam_eligible_uploader natural join person_identity_complete where (term_id, admin_id) = (%(term_id)s, %(admin_id)s) order by names")
exams_by_admin_id = make_execute_tuples ("select * from exam_course_series natural join exam_exam_series where (term_id, admin_id) = (%(term_id)s, %(admin_id)s) order by series_description")
exam_series = make_execute_optional_tuple ("select * from exam_course_series natural join exam_exam_series where (term_id, admin_id, series_code) = (%(term_id)s, %(admin_id)s, %(series_code)s)")
create_exams_by_admin_id = make_execute_tuples ("select ees.* from exam_exam_series as ees natural left join (select * from exam_course_series where (term_id, admin_id) = (%(term_id)s, %(admin_id)s)) as ecds where ecds is null order by series_description")
exam_master_select = "select * from exam_exam_master_plus natural left join exam_master_type natural left join exam_master_size"
exam_master_by_exam = make_execute_optional_tuple (exam_master_select + " where (exam_id, type_code) = (%(exam_id)s, %(type_code)s)")
exam_masters_by_exam = make_execute_tuples (exam_master_select + " where exam_id = %(exam_id)s order by type_code = 'R', version_seq, type_code")
exam_master_types_by_exam = make_execute_tuples ("select exam_id, type_code, same_agg (master_pages_gross) as master_pages_gross, same_agg (size_code) as size_code, same_agg (master_duplex) as master_duplex, lower (same_agg (type_description)) as type_description from exam_exam_master_plus join exam_master_type using (type_code) where exam_id = %(exam_id)s group by exam_id, type_code order by type_code")
exam_candidate_details_ordered = make_execute_tuples ("select eessc.*, not eessc is null as found from (select person_id, row_number () over () from unnest (%(candidates)s::integer[]) as person_id) t natural left join (select * from exam_exam_student_sitting_complete where exam_id = %(exam_id)s) eessc")
exam_special_candidate_details_ordered = make_execute_tuples ("select person_id, sitting_id, uw_id, start_time, admin_description as sitting_admin_description, extra_candidate, candidate_reserved, candidate_assigned, building_code, room_code, room_seat_number (seat_col, seat_row) as seat_code from (select person_id, row_number () over () from unnest (%(candidates)s::integer[]) as person_id) t natural join person_identity_complete natural join accom_candidate_sitting_plus join teaching_admin on (accom_admin_id=admin_id) natural left join exam_sitting left join (select * from room_room_plus where room_current) as rrp using (room_id) where exam_id=%(exam_id)s and (extra_candidate or not unselected) order by start_time, sitting_admin_description, surname, givennames, uw_id")
update_exam_auth = make_execute_none ("update exam_exam set exam_author_person_id = %(id)s where exam_id = %(exam_id)s")
# Only pulls recent examinations
# Hides start time of scheduled examinations until release date
exams_by_candidate = make_execute_tuples ("select sitting_id, exam_full_title, case when schedule_admin_id is null or current_date >= schedule_release then start_time else null end as start_time, exam_time_format_duration (duration) as duration_text, duration, eessc.building_code, eessc.room_code, room_note, seat_col, seat_row, seat_code, sequence_text, candidate_assigned, case when candidate_assigned then null else exam_assign_candidate_write_rooms (exam_id, person_id) end as rooms, nonsynchronous from exam_exam_student_sitting_complete as eessc join exam_exam ee using (exam_id) join uw_term using (term_id) left join room_room using (room_id) left join teaching_admin_term_exam_scheduler tates on ((tates.term_id, tates.admin_id) = (ee.term_id, ee.schedule_admin_id)) where person_id=%(person_id)s and (start_time >= now () - '6 weeks'::interval or ee.term_id = uw_term_current ()) order by start_time, exam_full_title")
## Exam Version Assignments
eva_basis = make_execute_optional_tuple ("select * from exam_exam_version_assignment_basis where (exam_id, division_seq) = (%(exam_id)s, %(division_seq)s)")
eva_details = make_execute_tuples ("select * from exam_version_assignment_plus where (exam_id, division_seq) = (%(exam_id)s, %(division_seq)s) order by assignment_display")
## TA Evaluation
eval_approved = make_execute_required_value ("select exists (select * from ta_term_unit_admin where (unit_code, term_id, admin_id) = (%(unit_code)s, %(term_id)s, %(admin_id)s))")
check_eval_rating = make_execute_optional_value ("select rating = %(rating)s from ta_rating_eval where (eval_id, rating_code) = (%(eval_id)s, %(rating_code)s)")
update_taeval = make_execute_none ("update ta_eval set (weeks, hours, feedback, remarks, award) = (%(weeks)s, %(hours)s, %(feedback)s, %(remarks)s, %(award)s) where eval_id = %(eval_id)s")
update_eval_history = make_execute_none ("insert into ta_eval_history VALUES (%(eval_id)s, %(person_id)s, now()) on conflict (eval_id, update_person_id) do update set update_time = now ()")
room_select = "select *, exam_room_capacity (room_id, exam_assign_build_loading_standard (%(allow_tablet_seats)s, %(allow_single_seating)s, case when %(checkerboard)s then 0 else -1 end)) as room_capacity from room_room_plus where "
room_by_id = make_execute_optional_tuple (room_select + "room_id = %(room_id)s")
rooms_by_building = make_execute_tuples (room_select + "building_code = %(building_code)s and room_current order by room_code")
buildings = make_execute_values ("select distinct building_code from room_room_plus where room_current order by building_code")
building_by_code = make_execute_optional_tuple ("select distinct building_code from room_room where building_code=%(building_code)s")
room_by_code = make_execute_optional_tuple ("select * from room_room where (building_code, room_code) = (%(building_code)s, %(room_code)s)")
rooms_sorted = make_execute_tuples (room_select + "room_current order by building_code, room_code")
rooms_inactive_sorted = make_execute_tuples (room_select + "room_effective is null order by building_code, room_code")
rooms_by_exam_sitting = make_execute_tuples ("select * from room_room natural join exam_exam_sitting_room_plus where (exam_id, sitting_id) = (%(exam_id)s, %(sitting_id)s) order by building_code, room_code")
rooms_by_sitting = make_execute_tuples ("select * from room_room natural join exam_sitting_room_plus where sitting_id = %(sitting_id)s order by building_code, room_code")
rooms_select = make_execute_tuples ("select room_id, building_code || ' ' || room_code as room from room_room_plus where room_current order by building_code, room_code")
room_sitting_seats = make_execute_tuples ("with t as (select * from exam_sitting_room_seat_complete natural left join exam_sitting_room_seat_division where (room_id, sitting_id) = (%(room_id)s, %(sitting_id)s)) select rs.*, room_seat_number (seat_col, seat_row) as seat_code, userid, sequence_text, t.exam_id, division_value, division_seq, not t is null as seat_chosen, seat_in_use, seat_reserved, seat_assigned from room_seat as rs left join t using (room_id, seat_col, seat_row) where room_id = %(room_id)s")
exam_exam_sitting_by_id = make_execute_optional_tuple ("select * from exam_exam_sitting_plus where (sitting_id, exam_id) = (%(sitting_id)s, %(exam_id)s)")
exams_by_sitting = make_execute_tuples ("select exam_exam_full_title (exam_id) AS full_title, ee.exam_duration, ees.* from exam_exam_sitting_plus as ees join exam_exam as ee using (exam_id) where sitting_id = %(sitting_id)s order by full_title")
exams_by_sitting_room = make_execute_tuples ("select exam_exam_full_title (exam_id) AS full_title, ee.exam_duration, ee.sequence_assigned, eesrp.* from exam_exam as ee join exam_exam_sitting_room_plus as eesrp using (exam_id) where (sitting_id, room_id) = (%(sitting_id)s, %(room_id)s) order by full_title")
print_by_id = make_execute_optional_tuple ("select pj.*, admin_description from print_job pj join teaching_admin on (admin_id=print_admin_id) where print_id = %(print_id)s")
exam_aids = make_execute_tuples ("select * from exam_exam_aid_plus where exam_id = %(exam_id)s order by aid_type_code")
exam_print_deadline = make_execute_required_value ("select case when administer_admin_id is not null then exam_print_deadline_find_deadline_date (exam_id) else uw_business_day_offset (uw_this_prev_business_day (primary_start_time::date), -%(business_days)s) end from exam_exam where exam_id = %(exam_id)s")
get_course_id = make_execute_required_tuple ("select course_id from cal_course_label_history where subject_code = %(subject_code)s and catalog = %(catalog)s order by course_as_of desc limit 1")
get_section_id = make_execute_tuples ("select section_id from off_course_section where course_id = %(course_id)s and term_id = %(term_id)s and component_code = 'TST'")
get_meet_times_and_dates = make_execute_tuples ("select meet_times, meet_dates from off_instruct_section_meet where section_id = %(section_id)s and term_id = %(term_id)s")
[docs] def sitting_exam_hues (self, sitting_id):
result = {}
for row, hue in zip (self.exams_by_sitting (sitting_id=sitting_id), hues ()):
result[row.exam_id] = hue
return result