Proctor Package investigation

ST#107173: Caroline noted that sometimes the Proctor Package isn't automatically generated. Isaac says that this results in someone having to ask him to fix it; which requires him manually updating the database, which automatically generates the files when they are requested via the web-ui.

• the cause is that the course is not recorded as "Exams Independent" by the Registrar's office.
• the database records new courses with a "sitting owner". Three possibilities:
  • Distance Ed: should never be "exams independent".
  • Course: sometimes should be "exams independent" - presumably will be set by course data
  • Registrar's Office: should always be "exams independent" - however sometimes the system doesn't necessarily set the right value.

The fix is: on the exam import process, when creating the row, it calls ro.exam{something}import to create the sitting. looking for function exam_seating_require()

Searching for this might give us just enough info to propose a fix?

From exam_view.sql:

CREATE OR REPLACE FUNCTION exam_sitting_can_print_proctor_package (
    sitting_id_p                integer
) RETURNS boolean
    LANGUAGE SQL
AS $$
SELECT exam_independent IS FALSE AND (
        SELECT coalesce (bool_and (sequence_assigned IS NOT NULL), TRUE)
            FROM exam_exam_sitting NATURAL JOIN exam_exam
            WHERE sitting_id = sitting_id_p)
    FROM exam_sitting
    WHERE sitting_id = sitting_id_p
$$;
COMMENT ON FUNCTION exam_sitting_can_print_proctor_package (integer) IS 'Compute whether it is appropriate to generate an entire-sitting proctor package for the specified sitting';

[...]

CREATE OR REPLACE FUNCTION exam_require_special_sitting (
    exam_id_p                   integer,
    start_time_p                timestamp
) RETURNS integer
    LANGUAGE PLPGSQL
    VOLATILE STRICT
AS $$
DECLARE
    result                      integer;
BEGIN
    SELECT exam_require_exam_sitting (exam_id, admin_id, start_time_p)
        FROM exam_exam
        WHERE exam_id = exam_id_p
        INTO STRICT result;
    UPDATE exam_sitting
        SET exam_independent = TRUE
        WHERE sitting_id = result AND exam_independent IS NULL;
    RETURN result;
END;
$$;
COMMENT ON FUNCTION exam_require_special_sitting (integer, timestamp) IS 'Ensure that the specified examination is attached to a sitting at the given time belonging to the admin unit of the examination';

-- FranciscoMejia - 2016-09-29