Source code for uw.local.teaching.webui.exam_schedule

'''Assessment scheduling form implementation.

Implementation of Web UI for final assessment scheduling request form.  This
also takes care of initial configuration of an offering for midterm creation
later.
'''
from datetime import date, datetime, time, timedelta
from itertools import groupby
from operator import attrgetter

from ll.xist.ns import html

from uw.dbtools import copy_to_csv

from uw.web.html.form import render_checkbox, render_radio, render_select, render_hidden, render_time_selector_1, parse_time_1, parse_time_2, render_date_selector_1, parse_date_1, parse_datetime
from uw.web.html.format import make_table_format, format_return, format_datetime, format_date, format_email
from uw.web.html.join import html_join, english_join_or

from uw.web.wsgi.delegate import delegate_action, delegate_get_post, status
from uw.web.wsgi.form import use_form_param
from uw.web.wsgi.function import return_html, return_csv

from uw.local.termtools import fromCode
from uw.local.util.format import format_person

from ...util.identity import use_remote_identity

from .ui import format_duration, format_date_helpful

from .administer import format_masters_uploaded, format_exam_column
from .enrolment import get_sections, format_sections


[docs]def render_section_confirmation (cursor, term, admin):
    """Render as HTML the section confirmation part of the form.

    :param cursor: DB connection cursor.
    :param term: Object representing a UW term.
    :param admin: A database row representing an admin unit.

    The returned page fragment explains the purpose of the form and shows the
    sections associated with this course offering.
    """
    sections = get_sections (cursor, term, admin)

    deadline = cursor.execute_required_value ("select min (schedule_course_deadline) - 1 from teaching_admin_term_exam_scheduler where term_id = %(term_id)s", term_id=term.code ())

    dates = cursor.execute_optional_tuple ("select lower (term_exam_dates) as start, upper (term_exam_dates) - '1 day'::interval as end from uw_term join teaching_admin_term_eligible_option using (term_id) where (term_id, admin_id, schedule_option_code) = (%(term_id)s, %(admin_id)s, 'SLFSLF')", term_id=term.code (), admin_id=admin.admin_id)
    if dates is None:
        date_blurb = None
    else:
        date_blurb = html.p ('Final assessments may only be scheduled during the official examination period, ', html.b (format_date_helpful (dates.start)), ' to ', html.b (format_date_helpful (dates.end)), ' inclusive.')

    return [
        html.p (
            'It is now time to decide on arrangements for ',
            html.b (term.description ()),
            ' final assessments. ',
            'Please complete this form no later than ',
            html.b (format_date_helpful (deadline)),
            '.'
        ),
        date_blurb,
        html.p ('Filling out this form is a two step process:'),
        html.ol (
            html.li ('Verify and confirm the sections which will be coordinated and managed as a single course;'),
            html.li ('Choose whether or not your course will have a final assessment, and, if so, indicate its duration and your scheduling and administration options.'),
        ),
        html.p ('The following sections are planned to be coordinated and managed as a single course, with common assessments and assignments:'),
        [render_hidden ("section", section.section_id) for section in sections],
        format_sections (sections),
    ]



[docs]def render_section_form (cursor, term, admin):
    result = html.form (
        render_section_confirmation (cursor, term, admin),
        html.p ('If any of the above sections should be coordinated separately, with separate assessments and assignments, or if any additional sections should be coordinated with the above ones, please email ', format_email ('rt-ist-cs-exammgmtsystem@rt.uwaterloo.ca', subject="%s %s Section Changes (%d)" % (term.description (), admin.admin_description, admin.admin_id)), ' and explain clearly what changes are required.'),
        html.p ('If the above set of sections is correct, please tick the box to confirm:'),
        html.p (
            render_checkbox ("approve", ""), ' ',
            html.input (type="submit", name="!section-approve", value="Confirm!")
        ),
        method="post", action=""
    )

    return result


bool_choices = (('f', 'No'), ('t', 'Yes'))

bool_write = {None: None, False: 'f', True: 't'}
bool_read = dict ((v, k) for k, v in bool_write.items ())


[docs]def get_duration_choices (cursor, opt):
    return cursor.execute_tuples ("select extract (epoch from duration)::integer as duration_seconds, exam_time_format_duration (duration) as formatted from (select d * interval '30 minutes' as duration from generate_series (2, 5) as t (d)) t order by duration desc")



[docs]def render_duration_select (cursor, exam, opt):

    if opt == 'sadept':
        total_seconds = exam and exam.exam_duration and int (exam.exam_duration.total_seconds ())
        result = [
            '\t', 'Hours: ',
            render_select ('%sh' % opt, [(x, x) for x in range (0, 6)], None if not exam or not exam.exam_duration else total_seconds//3600),
            '\t', 'Minutes: ',
            render_select ('%sm' % opt, [(x, x) for x in ('%02d' % x for x in range (0, 60, 5))], None if not exam or not exam.exam_duration else '%02d' % ((total_seconds - ((total_seconds)//3600)*3600)//60))
        ]
    else:
        result = render_select ("duration-%s" % (opt), get_duration_choices (cursor, opt), value=exam and exam.exam_duration and int (exam.exam_duration.total_seconds ()))
    return result


integration_choices = (
    ('M', 'Markbox', ['Markbox supports automatic multiple-choice grading.  For more information please visit ', html.a ('the Markbox site', href="https://fast.uwaterloo.ca/18/"), '.  If you choose this option, an answer page will be automatically appended to your uploaded master PDF and a Markbox assessment will be created when the examination is sent for printing.']),
    ('C', 'Crowdmark', ['Crowdmark allows you to grade written answers on the Web.  After the examination has been written, the papers are scanned.  Crowdmark also supports automatic multiple-choice grading.  For more information please visit ', html.a ('the uWaterloo Crowdmark site', href="https://uwaterloo.ca/crowdmark/"), '.  If you choose this option, Crowdmark information will be automatically added to your uploaded master PDF and a Crowdmark assessment will be created when the examination is sent for printing.']),
    ('N', 'None of the above', ['None of the above options will be enabled for this examination. If you are using only Akindi please choose this option and visit ', html.a ('the Akindi help site', href="//uwaterloo.ca/learn-help/instructors/akindi"), ' for details.']),
)


[docs]def get_exam_representatives (cursor, term, admin):
    return cursor.execute_values ("select person_id from auth_offering_personnel_complete where (term_id, admin_id, role_code) = (%(term_id)s, %(admin_id)s, 'EXAM') and not auth_backup and role_current", term_id=term.code (), admin_id=admin.admin_id)



[docs]def render_scheduling_form (cursor, term, admin, schedule, roles):
    term_row = cursor.execute_required_tuple ("select * from uw_term where term_id = %(term_id)s", term_id=term.code ())

    result = []

    result.append (render_section_confirmation (cursor, term, admin))

    result.append (html.form (
        html.p (
            'Sections approved ',
            format_datetime (schedule.sections_approved),
            ' by ',
            format_person (cursor, schedule.sections_approved_by),
            '. If these are not correct, please ',
            html.input (type="submit", name="!section-unapprove", value="Unapprove!"),
        ),
        method="post", action=""
    ))

    exam = cursor.execute_optional_tuple ("select exam_duration, primary_start_time, exam_assigned, schedule_admin_id, administer_admin_id, scanning_integration, nonsynchronous, master_count, teaching_admin_term_find_exam_option (term_id, admin_id) as schedule_option_code from teaching_admin_term_exam_schedule_plus where (term_id, admin_id) = (%(term_id)s, %(admin_id)s)", term_id=term.code (), admin_id=admin.admin_id)

    no_exam_status = cursor.execute_optional_value ("select no_exam from teaching_admin_exam_schedule_complete where admin_id = %(admin_id)s", admin_id=admin.admin_id)

    if ('EXAM' in roles or no_exam_status):
        result.append (
            html.form (
            html.p (
                html.label (
                    render_checkbox ("no_exam", checked=no_exam_status or None, value="no_exam"),
                    ' Check the box if this course has no exam permanently. ',
                ),
                html.input (type="submit", name="!no-exam", value="Confirm!")
            ),

            method="post", action=""
        ))
    # Hide everything if no exam
    if no_exam_status is not True:
        query = f"select tateo.*, current_date < {'schedule_examrep_deadline' if 'EXAM' in roles else 'schedule_course_deadline'} as ontime from teaching_admin_term_eligible_options (%(term_id)s, %(admin_id)s) tateo left join teaching_admin_term_exam_scheduler tates on ((tates.term_id, tates.admin_id) = (tateo.term_id, tateo.schedule_admin_id))"
        options = cursor.execute_tuples (query, term_id=term.code (), admin_id=admin.admin_id)

        closed_options = set ()
        overdue_options = set ()
        for o in options:
            if o.schedule_admin_id is not None:
                if o.ontime is None:
                    closed_options.add (o.schedule_option_code)
                elif o.ontime is False:
                    overdue_options.add (o.schedule_option_code)

        if closed_options:
            result.append (html.p (html.i ('Note: At least one examination scheduler is not yet ready to accept scheduling requests for this term.  Some options are disabled and may become available closer to the beginning of the term.')))
        if overdue_options:
            result.append (html.p (
                html.i ('Note: At least one examination scheduler’s submission deadline has passed for this term.  Some options are disabled and are no longer available.'),
                    '  Please contact your assessment scheduling representative ',
                    english_join_or (*[format_person (cursor, person_id) for person_id in get_exam_representatives (cursor, term, admin)]),
                    ' for assistance.',
                )
            )

        disabled_options = overdue_options | closed_options

        ul = html.ul (class_="uw-ofs", **{'data-name': "opt"})
        for option in options:
            chosen = exam.schedule_option_code == option.schedule_option_code
            disabled = option.schedule_option_code in disabled_options and not chosen
            ul.append (
                html.li (
                    html.label (
                        html.span (render_radio ("opt", value=option.schedule_option_code, checked=chosen or None, fixed=disabled)),
                        html.b (option.schedule_option_name),
                        html.br (),
                        option.schedule_option_description,
                    ), style="opacity: 0.3;" if disabled else None
                )
            )

        form = html.form (
            ul,
            method="post"
        )

        option_codes = set (option.schedule_option_code for option in options)

        form_components = tuple (
            ({option}, html.p (
                'Duration: ',
                render_duration_select (cursor, exam, option),
            )) for option in option_codes - {'NONE', 'HOME', 'WLU'}
        ) + (
            ({'SLFSLF'}, html.p (
                'Start Time (if known): ',
                render_date_selector_1 ("startd", exam and exam.primary_start_time and exam.primary_start_time.date (), term_row.term_exam_dates.lower, (term_row.term_exam_dates.upper - term_row.term_exam_dates.lower).days),
                ' ',
                render_time_selector_1 ("startt", exam and exam.primary_start_time and exam.primary_start_time.time (), [time (9), time (12, 30), time (16), time (19, 30)]),
            )),
            ({'CEL', 'REGSLF'}, html.p (
                'Writing Option: ',
                render_select ("times", ((0, '24-hour window'), (1, '1 synchronous start time'), (2, '2 synchronous start times'), (3, '3 synchronous start times'),), value=exam and (0 if exam.nonsynchronous else exam.master_count)),
                html.i (' Note: If you choose multiple synchronous start times, only one will be scheduled conflict-free.'),
            )),
            ({'HOME'}, html.p (
                'Due Date: ',
                render_date_selector_1 ("due", schedule and schedule.final_assessment_deadline, term_row.term_exam_dates.lower, (term_row.term_exam_dates.upper - term_row.term_exam_dates.lower).days),
            )),
            ({'REGREG'}, html.p (
                # Never ask question; explain that all on-campus examinations
                # will use assigned seating.
                'All in-person examinations will use assigned seating to assist with contact tracing in the event it is needed.',
            )),
            ({'CEL', 'REGREG'}, html.div (
                html.p ('Please choose from the following options for grading your assessment:'),
                html.ul (
                    [html.li (html.label (html.span (
                        render_radio ("scanning", value=int_code, checked=(exam and exam.scanning_integration) == int_code or None)),
                        html.b (int_head),
                        html.br (),
                        int_text,
                    )) for int_code, int_head, int_text in integration_choices],
                    class_="uw-ofs",
                    **{'data-name': "scanning"}
                ),
            )),
            ({'REGREG', 'REGSLF'}, html.div (
                html.p (html.label (
                    'To make any special requests or notes for the scheduling process, please tick: ',
                    render_checkbox ("specialreq",
                        checked=schedule and schedule.schedule_special_requests or None,
                        value="",
                        ),
                    class_="uw-ofs",
                    **{'data-name': "specialreq"}
                )),
                html.div (
                    html.p ('Accommodations for special requests are made for attendance at a learned conference, medical procedure, or similar rationale only. If you need the assessment scheduled at the same time/location as another assessment or you have location preferences, please list the details here. The Registrar’s Office will try their best to accommodate your request, however please kindly note that this is not always possible. Your Assessment Representative will be notified if a request cannot be accommodated at the time of final assessment scheduling.'),
                    html.textarea (
                        schedule and schedule.schedule_special_requests,
                        name="special-textarea", rows=3, cols=60, maxlength=200,
                    ),
                    class_="uw-ofs-specialreq-",
                ),
            )),
        )

        for options, display in form_components:
            options &= option_codes
            if options:
                display["class"] = " ".join ("uw-ofs-opt-" + option for option in options)
                form.append (display)

        form.append (html.p (html.input (type="submit", name="!schedule", value="Submit!")))

        result.append (form)

    return result



[docs]def personnel_query (people_query):
    return 'with t as (%s) select * from t natural join person_identity_complete order by surname, givennames' % people_query



[docs]def offering_instructors (cursor, term_id, admin_id):
    return cursor.execute_tuples (personnel_query ("with s as (select person_id, role_code, auth_admin_id from auth_offering_personnel_complete where role_current and (term_id, admin_id) = (%(term_id)s, %(admin_id)s)) select person_id from s where role_code = 'INST' and auth_admin_id is null except select person_id from s where role_code = 'ISC'"), term_id=term_id, admin_id=admin_id)


@return_html
def exam_schedule_get_handler (cursor, term, admin, roles):
    result = [format_return ('Main Menu', None, None, dot='Offering')]

    schedule = cursor.execute_optional_tuple ("select * from teaching_admin_term_exam_schedule where (term_id, admin_id) = (%(term_id)s, %(admin_id)s)", term_id=term.code (), admin_id=admin.admin_id)

    if 'EXAM' in roles:
        instructors = offering_instructors (cursor, term.code (), admin.admin_id)
        if instructors:
            result.append (html.form (
                html.p (
                    'Authorize Coordinator: ',
                    render_select ("person_id", ((i.person_id, '%s, %s (%s)' % (i.surname, i.givennames, i.userid)) for i in instructors)),
                    ' ',
                    html.input (type="submit", name="!auth-coordinator", value="Authorize!"),
                ),
                method="post", action=""
            ))
        coordinators = cursor.execute_tuples (personnel_query ("select person_id from auth_offering_personnel_complete where role_current and auth_admin_id is null and (term_id, admin_id, role_code, maintainer_code) = (%(term_id)s, %(admin_id)s, 'ISC', 'M')"), term_id=term.code (), admin_id=admin.admin_id)
        if coordinators:
            result.append (html.form (
                html.p (
                    'Remove Coordinator: ',
                    render_select ("person_id", ((i.person_id, '%s, %s (%s)' % (i.surname, i.givennames, i.userid)) for i in coordinators)),
                    ' ',
                    html.input (type="submit", name="!remove-coordinator", value="Remove!"),
                ),
                method="post", action=""
            ))

    if schedule is None or schedule.sections_approved is None:
        # Sections have not yet been approved
        result.append (render_section_form (cursor, term, admin))
    else:
        # Sections are approved
        result.append (render_scheduling_form (cursor, term, admin, schedule, roles))
        if schedule.scheduling_submitted is not None:
            # Sections approved and scheduling submitted, allow creating assessments
            result.append (html.p (
                'Final assessment scheduling submitted ',
                format_datetime (schedule.scheduling_submitted),
                ' by ',
                format_person (cursor, schedule.scheduling_submitted_by),
                '.',
            ))

    return "%s (%s) Final Assessment Scheduling Request" % (admin.admin_description, term.description ()), result


[docs]def strip_field (field):
    if field is None:
        return None
    return field.strip () or None


@use_form_param
@use_remote_identity
@return_html
def exam_schedule_post_handler (cursor, remote_identity, term, admin, roles, form):
    return_link = html.a ('Go back.', href="javascript:history.go(-1)")

    if not {'ISC', 'EXAM'} & roles:
        raise status.HTTPForbidden ()

    if '!section-approve' in form:
        sections = sorted (int (s) for s in form.multiple_field_value ("section"))
        if 'approve' in form:
            cursor.callproc_none ("teaching_admin_term_approve_sections", term.code (), admin.admin_id, sections, remote_identity.person_id)
        else:
            return 'Error: Please select “Approve”', html.p ('Please tick the “Approve” box and try again. ', return_link)
    elif '!section-unapprove' in form:
        cursor.callproc_none ("teaching_admin_term_unapprove_sections", term.code (), admin.admin_id)
    elif '!auth-coordinator' in form or '!remove-coordinator' in form:
        person_id = form.optional_field_value ("person_id")
        if person_id:
            person_id = int (person_id)
            cursor.callproc_none ("auth_offering_manual_authorize", '!auth-coordinator' in form, term.code (), admin.admin_id, person_id, 'ISC')
    elif '!no-exam' in form:
        no_exam = form.optional_field_value ("no_exam")
        if no_exam == 'no_exam':
            cursor.callproc_none ("teaching_admin_exam_schedule_set", admin.admin_id, True)
            no_exam_result = "The exam is now set as no exam permanetly. "
        else:
            cursor.callproc_none ("teaching_admin_exam_schedule_set", admin.admin_id, False)
            no_exam_result = "The exam is now set as have an exam. "
        result = html.p (
            no_exam_result,
            'Thank you for submitting the assessment scheduling request.  You may ',
            html.a ('review your submission', href=""), ' or ',
            html.a ('return to main page for this course offering', href="."), '.',
        )
        return "%s (%s) Final Assessment Scheduling Request" % (admin.admin_description, term.description ()), result   
    elif '!schedule' in form:
        schedule_option = form.optional_field_value ("opt")
        if schedule_option is None:
            return 'Error: Please select a scheduling option', html.p ('Please select an examination scheduling option and try again.')

        due = None

        query = f"select schedule_admin_id is null or current_date < {'schedule_examrep_deadline' if 'EXAM' in roles else 'schedule_course_deadline'} from teaching_admin_term_eligible_options (%(term_id)s, %(admin_id)s) tateo left join teaching_admin_term_exam_scheduler tates on ((tates.term_id, tates.admin_id) = (tateo.term_id, tateo.schedule_admin_id)) where schedule_option_code = %(schedule_option_code)s"
        option_status = cursor.execute_optional_value (query, term_id=term.code (), admin_id=admin.admin_id, schedule_option_code=schedule_option)

        if option_status is None:
            return 'Error: Scheduling not available', html.p ('The scheduler is not ready to accept form submissions yet.')
        elif option_status is False:
            return 'Error: Deadline passed', html.p ('The deadline has passed for choosing the chosen option.')

        if schedule_option in {'NONE', 'HOME'}:
            # No final examination; ensure non-existence of final examination
            if schedule_option in {'HOME'}:
                due = parse_date_1 (form, "due")
                if due is None:
                    return 'Error: Please select due date', html.p ('Please select a final assessment due date and try again.')

            cursor.callproc_none ("teaching_admin_term_delete_final_exam", term.code (), admin.admin_id)
        else:
            # Ensure existence of appropriate final assessment
            if schedule_option in {'SLFSLF'}:
                exam_duration = parse_time_2 (form, schedule_option)
                start_time = parse_datetime (form, "start", parse_date_1, parse_time_1)
            else:
                exam_duration = form.optional_field_value ("duration-%s" %(schedule_option))
                start_time = None

            if not exam_duration and schedule_option not in {'WLU', 'SLFSLF'}:
                return 'Error: Please select a duration', html.p ('Please select an examination duration and try again.')

            times = form.optional_field_value ("times")
            if schedule_option in {'CEL', 'REGSLF'}:
                if times:
                    times = int (times)
                else:
                    return 'Error: Please select a writing option', html.p ('Please select a writing option and try again.')
            else:
                times = 1

            if schedule_option in {'CEL', 'REGREG'}:
                exam_assigned = True
            elif schedule_option in {'WLU', 'REGSLF', 'SLFSLF'}:
                exam_assigned = None
            else:
                exam_assigned = bool_read.get (form.optional_field_value ("assigned"))
                if exam_assigned is None:
                    return 'Error: No assigned seating option chosen', html.p ('Please select whether or not you want assigned seating and try again.')

            cursor.execute_none ("update teaching_admin_term set admin_assigned = %(admin_assigned)s where (term_id, admin_id) = (%(term_id)s, %(admin_id)s)", term_id=term.code (), admin_id=admin.admin_id, admin_assigned=exam_assigned)

            exam_scanning = form.optional_field_value ("scanning")
            if not exam_scanning and schedule_option in {'REGREG'}:
                return 'Error: No scanning integration option chosen', html.p ('Please select whether or not you want Crowdmark or Markbox and try again.')

            exam_id = cursor.callproc_required_value ("teaching_admin_term_create_final_exam", term.code (), admin.admin_id, exam_duration, exam_assigned, schedule_option, times or 1, times == 0)
            if start_time is None:
                cursor.execute_none ("update exam_exam set primary_start_time = null where exam_id = %(exam_id)s", exam_id=exam_id)
            else:
                cursor.callproc_none ("exam_update_primary_sitting", exam_id, None)
                cursor.callproc_none ("exam_create_primary_sitting", exam_id, start_time)
            cursor.callproc_none ("exam_exam_crowdmark_set", exam_id, exam_scanning == 'C')
            cursor.callproc_none ("exam_exam_markbox_set", exam_id, exam_scanning == 'M')

        if "specialreq" in form:
            specialreq = strip_field (form.optional_field_value ("special-textarea"))
        else:
            specialreq = None
        cursor.callproc_none ("teaching_admin_term_set_scheduling", term.code (), admin.admin_id, specialreq, remote_identity.person_id, due)

        result = html.p (
            'Thank you for submitting the final assessment scheduling request.  You may ',
            html.a ('review your submission', href=""), ' or ',
            html.a ('return to main page for this course offering', href="."), '.',
        )

        return "%s (%s) Final Assessment Scheduling Request" % (admin.admin_description, term.description ()), result

    raise status.HTTPFound ("")

exam_schedule_handler = delegate_get_post (exam_schedule_get_handler, exam_schedule_post_handler)


[docs]def format_course_column (r):
    return html.a (r.admin_description, target="_blank", href="../../%d/scheduling" % r.admin_id) 



[docs]def format_sections_column (r):
    sections = r.sections
    if sections is None:
        return None
    else:
        return html_join (sections.replace (' ', ' ').split ('; '), sep=html.br ())



[docs]def format_enrolment_column (r):
    return '%s/%s' % (r.quest_enrol_total, r.quest_enrol_limit)



[docs]def make_format_coordinators_column (cursor):
    def format_coordinators_column (r):
        if r.isc_person_ids is None:
            return None
        else:
            return html_join ((format_person (cursor, person_id) for person_id in r.isc_person_ids), sep=html.br ())
    return format_coordinators_column



[docs]def make_format_schedule_status_column (cursor):
    def format_schedule_status_column (r):
        if r.sections_approved is None:
            return None
        elif r.scheduling_submitted is None:
            return [
                'Sections approved ',
                format_datetime (r.sections_approved),
                html.br (), 'by ',
                format_person (cursor, r.sections_approved_by),
            ]
        else:
            return [
                'Submitted ',
                format_datetime (r.scheduling_submitted),
                html.br (), 'by ',
                format_person (cursor, r.scheduling_submitted_by),
            ]
    return format_schedule_status_column



[docs]def format_admin_id (admin_descriptions, admin_id):
    if admin_id is None:
        return None
    else:
        return admin_descriptions[admin_id]



[docs]def format_no_exam (no_exam):
    return 'Yes' if no_exam else None


@delegate_get_post
@return_html
def schedule_no_coordinator_list_handler (cursor, term, admin, roles):
    if not {'ISC', 'EXAM'} & roles:
        raise status.HTTPForbidden ()

    result = [format_return ('Main Menu', None, None, 'Offering', dot='Assessment Scheduling Requests')]

    result.append (html.p ('The following courses still require a coordinator assigned.  To assign an instructor as coordinator, click through to the course below; for more complicated situations or if you run into trouble, please email ', format_email ('rt-ist-cs-exammgmtsystem@rt.uwaterloo.ca', subject="%s %s Coordinator Assignments (%d)" % (term.description (), admin.admin_description, admin.admin_id)), ' with precise details of who should be authorized for which courses.  Please include the WatIAM/Quest userid of the proposed coordinators for clarity.'))

    courses = cursor.execute_tuples ("select * from teaching_admin_term_exam_schedule_request where (term_id, outer_admin_id) = (%(term_id)s, %(admin_id)s) and quest_enrol_limit is not null and no_exam is not true and scheduling_submitted is null and isc_person_ids is null order by admin_description", term_id=term.code (), admin_id=admin.admin_id)

    def format_instructors_column (r):
        instructors = offering_instructors (cursor, r.term_id, r.admin_id)
        return html_join ((format_person (cursor, p.person_id) for p in instructors), sep=html.br ())

    result.append (make_table_format (
        ('Course', format_course_column),
        ('Sections', format_sections_column),
        ('Instructors', format_instructors_column),
        ('Enrolment', format_enrolment_column),
    ) (courses))

    return "%s (%s) Courses with no Coordinator" % (admin.admin_description, term.description ()), result

@delegate_get_post
@return_html
def schedule_not_submitted_list_handler (cursor, term, admin, roles):
    if not {'ISC', 'EXAM'} & roles:
        raise status.HTTPForbidden ()

    result = [format_return ('Main Menu', None, None, 'Offering', dot='Assessment Scheduling Requests')]

    courses = cursor.execute_tuples ("select * from teaching_admin_term_exam_schedule_request where (term_id, outer_admin_id) = (%(term_id)s, %(admin_id)s) and quest_enrol_limit is not null and no_exam is not true and scheduling_submitted is null order by admin_description", term_id=term.code (), admin_id=admin.admin_id)

    result.append (make_table_format (
        ('Course', format_course_column),
        ('Sections', format_sections_column),
        ('Enrolment', format_enrolment_column),
        ('Coordinator', make_format_coordinators_column (cursor)),
        ('Status', make_format_schedule_status_column (cursor)),
    ) (courses))

    return "%s (%s) Missing Requests" % (admin.admin_description, term.description ()), result

@delegate_get_post
@return_html
def exam_summary_handler (cursor, term, admin, roles):
    if not {'ISC', 'EXAM'} & roles:
        raise status.HTTPForbidden ()

    result = [format_return ('Main Menu', None, None, 'Offering', dot='Assessment Scheduling Requests')]

    courses = cursor.execute_tuples ("select *, exam_print_deadline (exam_id) as deadline from teaching_admin_term_exam_schedule_request natural left join exam_exam_plus left join (select exam_id, count(*) as masters_upload, count(*) filter (where master_pdf_raw is not null) as masters_uploaded from exam_exam_master group by exam_id) as eem using (exam_id) where (term_id, outer_admin_id) = (%(term_id)s, %(admin_id)s) and quest_enrol_limit is not null and exam_id is not null order by deadline nulls last, administer_admin_id, admin_description", term_id=term.code (), admin_id=admin.admin_id)

    admin_descriptions = dict (cursor.execute_tuples ("select admin_id, admin_description from teaching_admin where admin_id = any (%(admin_ids)s::integer[])", admin_ids=list (set (map (attrgetter ('schedule_admin_id'), courses)).union (set (map (attrgetter ('administer_admin_id'), courses))))))

    for deadline, deadline_courses in groupby (courses, attrgetter ('deadline')):
        if deadline:
            result.append (html.h2 ('Deadline: ', format_date (deadline)))
        else:
            result.append (html.h2 ('Exams with no Deadline'))

        result.append (make_table_format (
            ('Course', format_course_column),
            ('Assessment', format_exam_column),
            ('Sections', format_sections_column),
            ('Enrolment', format_enrolment_column),
            ('Coordinator', make_format_coordinators_column (cursor)),
            ('Start Time', lambda c: format_datetime (c.primary_start_time)),
            ('Duration', lambda c: format_duration (c.exam_duration)),
            ('Scheduled', lambda c: format_admin_id (admin_descriptions, c.schedule_admin_id)),
            ('Administered', lambda c: format_admin_id (admin_descriptions, c.administer_admin_id)),
            ('Assigned', attrgetter ("exam_assigned")),
            ('Finalized', lambda c: format_datetime (c.sequence_assigned)),
            ('Versions', attrgetter ('master_count')),
            ('Uploaded', format_masters_uploaded),
            ('Approved', lambda c: format_datetime (c.master_approved)),
            ('Approver', lambda c: format_person (cursor, c.master_approver_person_id)),
            ('Accepted', lambda c: format_datetime (c.master_accepted)),
        ) (deadline_courses))

    return "%s (%s) Assessment Summary" % (admin.admin_description, term.description ()), result

@delegate_get_post
@return_html
def schedule_submission_handler (cursor, term, admin, roles):
    if not {'ISC', 'EXAM'} & roles:
        raise status.HTTPForbidden ()

    result = [format_return ('Main Menu', None, None, 'Offering')]

    result.append (html.p (html.a ('Courses with no Coordinator', href="no-coordinator")))
    result.append (html.p (html.a ('Missing Requests', href="not-submitted")))
    result.append (html.p (html.a ('Assessment Summary', href="exam-summary")))

    courses = cursor.execute_tuples ("select * from teaching_admin_term_exam_schedule_request natural left join teaching_admin_exam_schedule \
        where (term_id, outer_admin_id) = (%(term_id)s, %(admin_id)s) and quest_enrol_limit \
        is not null order by admin_description", term_id=term.code (), admin_id=admin.admin_id)

    admin_descriptions = dict (cursor.execute_tuples ("select admin_id, admin_description \
        from teaching_admin where admin_id = any (%(admin_ids)s::integer[])", \
        admin_ids=list (set (map (attrgetter ('schedule_admin_id'), courses)).union (set (map (attrgetter ('administer_admin_id'), courses))).union(set(map(attrgetter('no_exam'), courses))))))

    result.append (make_table_format (
        ('Course', format_course_column),
        ('Sections', format_sections_column),
        ('Enrolment', format_enrolment_column),
        ('Coordinator', make_format_coordinators_column (cursor)),
        ('Status', make_format_schedule_status_column (cursor)),
        ('Option', attrgetter ('schedule_option_code')),
        ('Scheduled', lambda c: format_admin_id (admin_descriptions, c.schedule_admin_id)),
        ('Administered', lambda c: format_admin_id (admin_descriptions, c.administer_admin_id)),
        ('Permanently no exam', lambda c: format_no_exam (c.no_exam)),
        ('Duration', lambda c: format_duration (c.exam_duration)),
        ('Special Request', attrgetter ('schedule_special_requests')),
    ) (courses))

    result.append (html.p (html.a ('Download CSV (experimental)', href="csv")))

    return "%s (%s) Assessment Scheduling Requests" % (admin.admin_description, term.description ()), result

@delegate_get_post
@return_csv
def schedule_submission_csv_handler (cursor, term, admin, roles):
    result = copy_to_csv (cursor, f"select admin_description, replace (sections, '; ', E'\n') as sections, quest_enrol_total, quest_enrol_limit, schedule_option_code, no_exam, exam_duration from teaching_admin_term_exam_schedule_request natural left join teaching_admin_exam_schedule where (term_id, outer_admin_id) = ({term.code ()}, {admin.admin_id}) and quest_enrol_limit is not null order by admin_description")

    return [result.getvalue (), f'{admin.admin_description} ({term.description ()}) Assessment Scheduling Requests.csv']

schedule_submission_handler = delegate_action (schedule_submission_handler, {
    "csv": schedule_submission_csv_handler,
    "no-coordinator": schedule_no_coordinator_list_handler,
    "not-submitted": schedule_not_submitted_list_handler,
    "exam-summary": exam_summary_handler,
})