"""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 *
[docs]class Cursor (Cursor):
max_course_term = make_execute_optional_value ("select max (term_id) from off_course")
term_ids = make_execute_values ("select term_id from uw_term where term_id <= %(max_course_term)s order by term_id desc limit %(offset)s")
building_list = make_execute_tuples ("select building_code, building_description from uw_building order by building_code")
group_list = make_execute_tuples ("select group_code, group_quest_name as name from uw_group order by group_code")
instructor_list = make_execute_tuples ("select distinct userid, surname, givennames from off_instruct_section_instructor_plus where userid is not null order by surname, givennames")
subject_list = make_execute_tuples ("select subject_code, subject_short_description from cal_subject order by subject_code")
unit_list = make_execute_tuples ("select * from uw_unit order by group_code, unit_code")
subject_list_by_group = make_execute_tuples ("select * from cal_subject natural join uw_unit order by group_code, subject_code")
degree_by_unit = make_execute_tuples ("select distinct degree_code, degree_title from pps_degree where unit_code = %(unit_code)s order by degree_code")
plan_by_unit = make_execute_tuples ("select distinct plan_code, plan_title from pps_plan where unit_code = %(unit_code)s order by plan_code")
program_by_unit = make_execute_tuples ("select distinct program_code, program_title from pps_program where unit_code = %(unit_code)s order by program_code")
subject_by_unit = make_execute_tuples ("select distinct subject_code from cal_subject where unit_code = %(unit_code)s order by subject_code")
degree_by_code = make_execute_optional_tuple ("select * from pps_degree where degree_code = %(degree_code)s")
plan_by_code = make_execute_optional_tuple ("select * from pps_plan where (unit_code, plan_code) = (%(unit_code)s, %(plan_code)s)")
plans_by_program = make_execute_tuples ("select * from pps_plan where (unit_code, program_code) = (%(unit_code)s, %(program_code)s) order by plan_code")
plan_history_by_code = make_execute_tuples ("select * from pps_plan_history where (unit_code, plan_code) = (%(unit_code)s, %(plan_code)s) order by plan_as_of desc")
program_by_code = make_execute_optional_tuple ("select * from pps_program where program_code = %(program_code)s")
program_history_by_code = make_execute_tuples ("select * from pps_program_history where (unit_code, program_code) = (%(unit_code)s, %(program_code)s) order by program_as_of desc")
subject_history_by_code = make_execute_tuples ("select * from cal_subject_history where (unit_code, subject_code) = (%(unit_code)s, %(subject_code)s) order by subject_as_of desc")
#building
campus_description = make_execute_optional_value ("select campus_description from uw_campus where campus_code = %(campus_code)s")
facility_location_by_id = make_execute_optional_tuple ("select room_code, room_description, building_code from uw_room natural join uw_building where facility_id = %(facility_id)s")
location_description = make_execute_optional_value ("select location_quest_description from uw_location where location_code = %(location_code)s")
room_list_by_building = make_execute_tuples ("select room_code, room_description from uw_room where building_id = %(building_id)s order by room_code")
#delegate
building_by_code = make_execute_optional_tuple ("select building_id, building_code, building_description from uw_building where building_code = %(building_code)s")
course_detail_by_id = make_execute_tuples ("select *, to_char (course_id) as id, cal_course_format_labels (course_id) as title from off_course_as_of natural join cal_course_history natural join cal_course_label_history where (term_id, course_id) = (%(term_id)s, %(course_id)s)")
course_id_by_catalog = make_execute_optional_value ("select distinct course_id from off_course natural join off_course_as_of natural join cal_course_label_history where (term_id, subject_code, catalog) = (%(term_id)s, upper(%(subject_code)s), %(catalog)s)")
course_ids_by_catalog_prefix = make_execute_values ("select distinct course_id from off_course natural join off_course_as_of natural join cal_course_label_history where catalog like %(prefix)s and (term_id, subject_code) = (%(term_id)s, upper(%(subject_code)s))")
course_ids_by_catalog_all_terms = make_execute_values ("select distinct course_id from off_course natural join off_course_as_of natural join cal_course_label_history where (subject_code, catalog) = (upper(%(subject_code)s), %(catalog)s)")
instructor_by_id = make_execute_optional_tuple ("select * from person_identity_complete where userid = %(userid)s")
room_detail = make_execute_optional_tuple ("select * from uw_room where (building_id, room_code) = (%(building_id)s, %(room_code)s)")
room_details_by_prefix = make_execute_tuples ("select * from uw_room where building_id = %(building_id)s and room_code like %(prefix)s order by room_code")
subject_by_code = make_execute_optional_tuple ("select * from cal_subject where subject_code = %(subject_code)s")
course_detail_by_ids = make_execute_tuples ("select *, to_char (course_id) as id, cal_course_format_labels (course_id) as title from off_course_as_of natural join cal_course_history natural join cal_course_label_history where course_id = any (%(course_ids)s) and (term_id, subject_code) = (%(term_id)s, %(subject_code)s) order by catalog, career_code")
course_detail_by_ids_all_terms = make_execute_tuples ("select distinct on (term_id) *, to_char (course_id) as id, cal_course_format_labels (course_id) as title from off_course natural join off_course_as_of natural join cal_course_history natural join cal_course_label_history where course_id = any (%(course_ids)s) and subject_code = %(subject_code)s order by term_id desc, catalog")
course_ids_by_subject = make_execute_values ("select distinct course_id from off_course natural join off_course_as_of natural join cal_course_label_history where (term_id, subject_code) = (%(term_id)s, %(subject_code)s)")
course_sections_by_ids = make_execute_tuples ("select off_course_section_format (term_id, class_id), off_course_section_format (term_id, class_id) as component_description, cs_enrol_total as enrol_tot, cs_enrol_limit as enrol_cap, * from off_course_section natural join off_course_as_of where term_id = %(term_id)s and section_id = any (%(section_ids)s) order by section_id")
instruct_sections_by_ids = make_execute_tuples ("select section_id, campus_code, location_code from off_instruct_section where term_id = %(term_id)s and section_id = any (%(section_ids)s) order by section_id")
instruct_section_ids_by_catalog = make_execute_values ("select section_id from off_course_section natural join off_course_as_of natural join cal_course_label_history where (term_id, subject_code, catalog) = (%(term_id)s, %(subject_code)s, %(catalog)s) and session_code <> 'PCS'")
instruct_section_ids_by_userid = make_execute_values ("select distinct section_id from off_instruct_section_instructor_plus where (term_id, userid) = (%(term_id)s, %(userid)s)")
instruct_section_ids_by_facility = make_execute_values ("select distinct section_id from off_instruct_section_instructor_plus natural join off_instruct_section_meet where (term_id, facility_id) = (%(term_id)s, %(facility_id)s)")
instruct_section_meets_by_ids = make_execute_tuples ("select section_id, course_format_meet_days(meet_days), upper (meet_times) as upper, lower (meet_times) as lower, upper (meet_dates) as upper_date, lower (meet_dates) as lower_date, facility_id from off_instruct_section_meet where term_id = %(term_id)s and section_id = any (%(section_ids)s) order by section_id")
instruct_section_meet_ids_by_ids= make_execute_values ("select section_id from off_instruct_section_meet where term_id = %(term_id)s and section_id = any (%(section_ids)s) order by section_id")
instruct_section_meets_by_instructor = make_execute_tuples ("select *, off_course_section_format (term_id, class_id) as meet_title, cal_course_format_labels (course_id) as title, course_format_meet_days(meet_days), upper (meet_times) as upper, lower (meet_times) as lower, (upper (meet_dates) - 1) as upper_date, lower (meet_dates) as lower_date from off_instruct_section_meet natural join off_course_section natural join (select distinct on (course_id) * from cal_course_label_history order by course_id, course_as_of desc) as label where term_id = %(term_id)s and section_id = any (%(section_ids)s) order by meet_title, lower_date, upper_date")
instruct_section_meets_by_room = make_execute_tuples ("select *, off_course_section_format (term_id, class_id) as meet_title, cal_course_format_labels (course_id) as title, course_format_meet_days(meet_days), upper (meet_times) as upper, lower (meet_times) as lower, (upper (meet_dates) - 1) as upper_date, lower (meet_dates) as lower_date from off_instruct_section_meet natural join off_course_section natural join (select distinct on (course_id) * from cal_course_label_history order by course_id, course_as_of desc) as label where (term_id, facility_id) = (%(term_id)s, %(facility_id)s) and section_id = any (%(section_ids)s) order by meet_title, lower_date, upper_date")
requisite_by_id = make_execute_optional_value ("select requisite_description from cal_requisite where requisite_id = %(requisite_id)s")
reserve_by_ids = make_execute_tuples ("select section_id, class_id, requisite_description as description, reserve_enrol_total as reserve_tot, reserve_enrol_limit as reserve_cap from off_course_section natural join off_course_section_reserve natural join cal_requisite where term_id = %(term_id)s and section_id = any (%(section_ids)s) order by section_id")
term_ids_by_instruct_sections = make_execute_values ("select distinct term_id from off_instruct_section_instructor_plus where userid = %(userid)s order by term_id desc")
instructor_by_fullname = make_execute_tuples ("select distinct userid, surname, givennames from off_instruct_section_instructor_plus where lower (surname) like lower(%(surname)s) and lower (givennames) like lower(%(givennames)s) order by surname, givennames")
instructor_by_part_name = make_execute_tuples ("select distinct userid, surname, givennames from off_instruct_section_instructor_plus where lower (surname) like lower(%(surname)s) or lower (givennames) like lower(%(givennames)s) order by surname, givennames")
instructors_by_term = make_execute_tuples ("select distinct userid, surname, givennames from off_instruct_section_instructor_plus where term_id = %(term_id)s and userid is not null order by surname, givennames")
instructors_by_section_ids = make_execute_tuples ("select * from off_instruct_section_instructor_plus where term_id = %(term_id)s and section_id = any (%(section_ids)s) order by section_id")
instructor_by_filter = make_execute_tuples ("select distinct userid, surname, givennames from off_instruct_section_instructor_plus where userid is not null and surname ilike %(filter)s order by surname, givennames")
default_meet_days_by_term = make_execute_tuples ("select meet_dates, count(*) from off_instruct_section_meet where term_id = %(term_id)s group by meet_dates order by count(*) desc limit 1")
table_by_name = make_execute_optional_value ("select distinct table_name from all_tab_columns where table_name = %(table_name)s")
owner_by_name = make_execute_optional_value ("select distinct owner from all_tab_columns where owner = %(owner)s")
column_by_name = make_execute_optional_value ("select distinct column_name from all_tab_columns where column_name = %(column_name)s")