/**
 * Efficiently update check state time on all open attempts
 *
 * @param array $conditions optional restrictions on which attempts to update
 *                    Allowed conditions:
 *                      courseid => (array|int) attempts in given course(s)
 *                      userid   => (array|int) attempts for given user(s)
 *                      quizid   => (array|int) attempts in given quiz(s)
 *                      groupid  => (array|int) quizzes with some override for given group(s)
 *
 */
function quiz_update_open_attempts(array $conditions)
{
    global $DB;
    foreach ($conditions as &$value) {
        if (!is_array($value)) {
            $value = array($value);
        }
    }
    $params = array();
    $coursecond = '';
    $usercond = '';
    $quizcond = '';
    $groupcond = '';
    if (isset($conditions['courseid'])) {
        list($incond, $inparams) = $DB->get_in_or_equal($conditions['courseid'], SQL_PARAMS_NAMED, 'cid');
        $params = array_merge($params, $inparams);
        $coursecond = "AND quiza.quiz IN (SELECT q.id FROM {quiz} q WHERE q.course {$incond})";
    }
    if (isset($conditions['userid'])) {
        list($incond, $inparams) = $DB->get_in_or_equal($conditions['userid'], SQL_PARAMS_NAMED, 'uid');
        $params = array_merge($params, $inparams);
        $usercond = "AND quiza.userid {$incond}";
    }
    if (isset($conditions['quizid'])) {
        list($incond, $inparams) = $DB->get_in_or_equal($conditions['quizid'], SQL_PARAMS_NAMED, 'qid');
        $params = array_merge($params, $inparams);
        $quizcond = "AND quiza.quiz {$incond}";
    }
    if (isset($conditions['groupid'])) {
        list($incond, $inparams) = $DB->get_in_or_equal($conditions['groupid'], SQL_PARAMS_NAMED, 'gid');
        $params = array_merge($params, $inparams);
        $groupcond = "AND quiza.quiz IN (SELECT qo.quiz FROM {quiz_overrides} qo WHERE qo.groupid {$incond})";
    }
    // SQL to compute timeclose and timelimit for each attempt:
    $quizausersql = quiz_get_attempt_usertime_sql();
    // SQL to compute the new timecheckstate
    $timecheckstatesql = "\n          CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL\n               WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose\n               WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit\n               WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit\n               ELSE quizauser.usertimeclose END +\n          CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END";
    // SQL to select which attempts to process
    $attemptselect = " quiza.state IN ('inprogress', 'overdue')\n                       {$coursecond}\n                       {$usercond}\n                       {$quizcond}\n                       {$groupcond}";
    /*
     * Each database handles updates with inner joins differently:
     *  - mysql does not allow a FROM clause
     *  - postgres and mssql allow FROM but handle table aliases differently
     *  - oracle requires a subquery
     *
     * Different code for each database.
     */
    $dbfamily = $DB->get_dbfamily();
    if ($dbfamily == 'mysql') {
        $updatesql = "UPDATE {quiz_attempts} quiza\n                        JOIN {quiz} quiz ON quiz.id = quiza.quiz\n                        JOIN ( {$quizausersql} ) quizauser ON quizauser.id = quiza.id\n                         SET quiza.timecheckstate = {$timecheckstatesql}\n                       WHERE {$attemptselect}";
    } else {
        if ($dbfamily == 'postgres') {
            $updatesql = "UPDATE {quiz_attempts} quiza\n                         SET timecheckstate = {$timecheckstatesql}\n                        FROM {quiz} quiz, ( {$quizausersql} ) quizauser\n                       WHERE quiz.id = quiza.quiz\n                         AND quizauser.id = quiza.id\n                         AND {$attemptselect}";
        } else {
            if ($dbfamily == 'mssql') {
                $updatesql = "UPDATE quiza\n                         SET timecheckstate = {$timecheckstatesql}\n                        FROM {quiz_attempts} quiza\n                        JOIN {quiz} quiz ON quiz.id = quiza.quiz\n                        JOIN ( {$quizausersql} ) quizauser ON quizauser.id = quiza.id\n                       WHERE {$attemptselect}";
            } else {
                // oracle, sqlite and others
                $updatesql = "UPDATE {quiz_attempts} quiza\n                         SET timecheckstate = (\n                           SELECT {$timecheckstatesql}\n                             FROM {quiz} quiz, ( {$quizausersql} ) quizauser\n                            WHERE quiz.id = quiza.quiz\n                              AND quizauser.id = quiza.id\n                         )\n                         WHERE {$attemptselect}";
            }
        }
    }
    $DB->execute($updatesql, $params);
}
Exemple #2
0
/**
 * Efficiently update check state time on all open attempts
 *
 * @param array $conditions optional restrictions on which attempts to update
 *                    Allowed conditions:
 *                      courseid => (array|int) attempts in given course(s)
 *                      userid   => (array|int) attempts for given user(s)
 *                      quizid   => (array|int) attempts in given quiz(s)
 *                      groupid  => (array|int) quizzes with some override for given group(s)
 *
 */
function quiz_update_open_attempts(array $conditions) {
    global $DB;

    foreach ($conditions as &$value) {
        if (!is_array($value)) {
            $value = array($value);
        }
    }

    $params = array();
    $wheres = array("quiza.state IN ('inprogress', 'overdue')");
    $iwheres = array("iquiza.state IN ('inprogress', 'overdue')");

    if (isset($conditions['courseid'])) {
        list ($incond, $inparams) = $DB->get_in_or_equal($conditions['courseid'], SQL_PARAMS_NAMED, 'cid');
        $params = array_merge($params, $inparams);
        $wheres[] = "quiza.quiz IN (SELECT q.id FROM {quiz} q WHERE q.course $incond)";
        list ($incond, $inparams) = $DB->get_in_or_equal($conditions['courseid'], SQL_PARAMS_NAMED, 'icid');
        $params = array_merge($params, $inparams);
        $iwheres[] = "iquiza.quiz IN (SELECT q.id FROM {quiz} q WHERE q.course $incond)";
    }

    if (isset($conditions['userid'])) {
        list ($incond, $inparams) = $DB->get_in_or_equal($conditions['userid'], SQL_PARAMS_NAMED, 'uid');
        $params = array_merge($params, $inparams);
        $wheres[] = "quiza.userid $incond";
        list ($incond, $inparams) = $DB->get_in_or_equal($conditions['userid'], SQL_PARAMS_NAMED, 'iuid');
        $params = array_merge($params, $inparams);
        $iwheres[] = "iquiza.userid $incond";
    }

    if (isset($conditions['quizid'])) {
        list ($incond, $inparams) = $DB->get_in_or_equal($conditions['quizid'], SQL_PARAMS_NAMED, 'qid');
        $params = array_merge($params, $inparams);
        $wheres[] = "quiza.quiz $incond";
        list ($incond, $inparams) = $DB->get_in_or_equal($conditions['quizid'], SQL_PARAMS_NAMED, 'iqid');
        $params = array_merge($params, $inparams);
        $iwheres[] = "iquiza.quiz $incond";
    }

    if (isset($conditions['groupid'])) {
        list ($incond, $inparams) = $DB->get_in_or_equal($conditions['groupid'], SQL_PARAMS_NAMED, 'gid');
        $params = array_merge($params, $inparams);
        $wheres[] = "quiza.quiz IN (SELECT qo.quiz FROM {quiz_overrides} qo WHERE qo.groupid $incond)";
        list ($incond, $inparams) = $DB->get_in_or_equal($conditions['groupid'], SQL_PARAMS_NAMED, 'igid');
        $params = array_merge($params, $inparams);
        $iwheres[] = "iquiza.quiz IN (SELECT qo.quiz FROM {quiz_overrides} qo WHERE qo.groupid $incond)";
    }

    // SQL to compute timeclose and timelimit for each attempt:
    $quizausersql = quiz_get_attempt_usertime_sql(
            implode("\n                AND ", $iwheres));

    // SQL to compute the new timecheckstate
    $timecheckstatesql = "
          CASE WHEN quizauser.usertimelimit = 0 AND quizauser.usertimeclose = 0 THEN NULL
               WHEN quizauser.usertimelimit = 0 THEN quizauser.usertimeclose
               WHEN quizauser.usertimeclose = 0 THEN quiza.timestart + quizauser.usertimelimit
               WHEN quiza.timestart + quizauser.usertimelimit < quizauser.usertimeclose THEN quiza.timestart + quizauser.usertimelimit
               ELSE quizauser.usertimeclose END +
          CASE WHEN quiza.state = 'overdue' THEN quiz.graceperiod ELSE 0 END";

    // SQL to select which attempts to process
    $attemptselect = implode("\n                         AND ", $wheres);

   /*
    * Each database handles updates with inner joins differently:
    *  - mysql does not allow a FROM clause
    *  - postgres and mssql allow FROM but handle table aliases differently
    *  - oracle requires a subquery
    *
    * Different code for each database.
    */

    $dbfamily = $DB->get_dbfamily();
    if ($dbfamily == 'mysql') {
        $updatesql = "UPDATE {quiz_attempts} quiza
                        JOIN {quiz} quiz ON quiz.id = quiza.quiz
                        JOIN ( $quizausersql ) quizauser ON quizauser.id = quiza.id
                         SET quiza.timecheckstate = $timecheckstatesql
                       WHERE $attemptselect";
    } else if ($dbfamily == 'postgres') {
        $updatesql = "UPDATE {quiz_attempts} quiza
                         SET timecheckstate = $timecheckstatesql
                        FROM {quiz} quiz, ( $quizausersql ) quizauser
                       WHERE quiz.id = quiza.quiz
                         AND quizauser.id = quiza.id
                         AND $attemptselect";
    } else if ($dbfamily == 'mssql') {
        $updatesql = "UPDATE quiza
                         SET timecheckstate = $timecheckstatesql
                        FROM {quiz_attempts} quiza
                        JOIN {quiz} quiz ON quiz.id = quiza.quiz
                        JOIN ( $quizausersql ) quizauser ON quizauser.id = quiza.id
                       WHERE $attemptselect";
    } else {
        // oracle, sqlite and others
        $updatesql = "UPDATE {quiz_attempts} quiza
                         SET timecheckstate = (
                           SELECT $timecheckstatesql
                             FROM {quiz} quiz, ( $quizausersql ) quizauser
                            WHERE quiz.id = quiza.quiz
                              AND quizauser.id = quiza.id
                         )
                         WHERE $attemptselect";
    }

    $DB->execute($updatesql, $params);
}
Exemple #3
0
 /**
  * @return moodle_recordset of quiz_attempts that need to be processed because time has
  *     passed. The array is sorted by courseid then quizid.
  */
 public function get_list_of_overdue_attempts($processto)
 {
     global $DB;
     // SQL to compute timeclose and timelimit for each attempt:
     $quizausersql = quiz_get_attempt_usertime_sql();
     // This query should have all the quiz_attempts columns.
     return $DB->get_recordset_sql("\n         SELECT quiza.*,\n                quizauser.usertimeclose,\n                quizauser.usertimelimit\n\n           FROM {quiz_attempts} quiza\n           JOIN {quiz} quiz ON quiz.id = quiza.quiz\n           JOIN ( {$quizausersql} ) quizauser ON quizauser.id = quiza.id\n\n          WHERE quiza.state IN ('inprogress', 'overdue')\n            AND quiza.timecheckstate <= :processto\n       ORDER BY quiz.course, quiza.quiz", array('processto' => $processto));
 }