/**
* 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);
}
/**
* 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);
}
/**
* @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));
}