/**
* SQL to fetch relevant 'quiz_attempts' records.
*
* @param int $quizid quiz id to get attempts for
* @param \core\dml\sql_join $groupstudentsjoins Contains joins, wheres, params, empty if not using groups
* @param string $whichattempts which attempts to use, represented internally as one of the constants as used in
* $quiz->grademethod ie.
* QUIZ_GRADEAVERAGE, QUIZ_GRADEHIGHEST, QUIZ_ATTEMPTLAST or QUIZ_ATTEMPTFIRST
* we calculate stats based on which attempts would affect the grade for each student.
* @param bool $includeungraded whether to fetch ungraded attempts too
* @return array FROM and WHERE sql fragments and sql params
*/
function quiz_statistics_attempts_sql($quizid, \core\dml\sql_join $groupstudentsjoins, $whichattempts = QUIZ_GRADEAVERAGE, $includeungraded = false)
{
$fromqa = "{quiz_attempts} quiza ";
$whereqa = 'quiza.quiz = :quizid AND quiza.preview = 0 AND quiza.state = :quizstatefinished';
$qaparams = array('quizid' => (int) $quizid, 'quizstatefinished' => quiz_attempt::FINISHED);
if (!empty($groupstudentsjoins->joins)) {
$fromqa .= "\nJOIN {user} u ON u.id = quiza.userid\n {$groupstudentsjoins->joins} ";
$whereqa .= " AND {$groupstudentsjoins->wheres}";
$qaparams += $groupstudentsjoins->params;
}
$whichattemptsql = quiz_report_grade_method_sql($whichattempts);
if ($whichattemptsql) {
$whereqa .= ' AND ' . $whichattemptsql;
}
if (!$includeungraded) {
$whereqa .= ' AND quiza.sumgrades IS NOT NULL';
}
return array($fromqa, $whereqa, $qaparams);
}
/**
* SQL to fetch relevant 'quiz_attempts' records.
*
* @param int $quizid quiz id to get attempts for
* @param array $groupstudents empty array if not using groups or array of students in current group.
* @param string $whichattempts which attempts to use, represented internally as one of the constants as used in
* $quiz->grademethod ie.
* QUIZ_GRADEAVERAGE, QUIZ_GRADEHIGHEST, QUIZ_ATTEMPTLAST or QUIZ_ATTEMPTFIRST
* we calculate stats based on which attempts would affect the grade for each student.
* @param bool $includeungraded whether to fetch ungraded attempts too
* @return array FROM and WHERE sql fragments and sql params
*/
function quiz_statistics_attempts_sql($quizid, $groupstudents, $whichattempts = QUIZ_GRADEAVERAGE, $includeungraded = false)
{
global $DB;
$fromqa = '{quiz_attempts} quiza ';
$whereqa = 'quiza.quiz = :quizid AND quiza.preview = 0 AND quiza.state = :quizstatefinished';
$qaparams = array('quizid' => (int) $quizid, 'quizstatefinished' => quiz_attempt::FINISHED);
if ($groupstudents) {
ksort($groupstudents);
list($grpsql, $grpparams) = $DB->get_in_or_equal(array_keys($groupstudents), SQL_PARAMS_NAMED, 'u');
$whereqa .= " AND quiza.userid {$grpsql}";
$qaparams += $grpparams;
}
$whichattemptsql = quiz_report_grade_method_sql($whichattempts);
if ($whichattemptsql) {
$whereqa .= ' AND ' . $whichattemptsql;
}
if (!$includeungraded) {
$whereqa .= ' AND quiza.sumgrades IS NOT NULL';
}
return array($fromqa, $whereqa, $qaparams);
}
/**
* This is a wrapper for {@link quiz_report_grade_method_sql} that takes the whole quiz object instead of just the grading method
* as a param. See definition for {@link quiz_report_grade_method_sql} below.
*
* @param object $quiz
* @param string $quizattemptsalias sql alias for 'quiz_attempts' table
* @return string sql to test if this is an attempt that will contribute towards the grade of the user
*/
function quiz_report_qm_filter_select($quiz, $quizattemptsalias = 'quiza')
{
if ($quiz->attempts == 1) {
// This quiz only allows one attempt.
return '';
}
return quiz_report_grade_method_sql($quiz->grademethod, $quizattemptsalias);
}
/**
* Calculating count and mean of marks for first and ALL attempts by students.
*
* See : http://docs.moodle.org/dev/Quiz_item_analysis_calculations_in_practise
* #Calculating_MEAN_of_grades_for_all_attempts_by_students
* @param int $quizid
* @param array $groupstudents
* @return stdClass with properties with count and avg with prefixes firstattempts, highestattempts, etc.
*/
protected function attempt_counts_and_averages($quizid, $groupstudents)
{
global $DB;
list($fromqa, $whereqa, $qaparams) = quiz_statistics_attempts_sql($quizid, $groupstudents);
$selects = array();
foreach (array_keys(quiz_get_grading_options()) as $which) {
$fieldprefix = static::using_attempts_string_id($which);
$condition = quiz_report_grade_method_sql($which);
if ($condition == '') {
$case = '1';
} else {
$case = "CASE WHEN ({$condition}) THEN 1 ELSE 0 END";
}
$selects[] = "\n SUM({$case}) AS {$fieldprefix}count,\n SUM(sumgrades * {$case}) AS {$fieldprefix}total";
}
$select = join(',', $selects);
$attempttotals = $DB->get_record_sql("\n SELECT {$select}\n FROM {$fromqa}\n WHERE {$whereqa}", $qaparams);
foreach (array_keys(quiz_get_grading_options()) as $which) {
$fieldprefix = static::using_attempts_string_id($which);
$attempttotals->{$fieldprefix . 'avg'} = $attempttotals->{$fieldprefix . 'total'} / $attempttotals->{$fieldprefix . 'count'};
unset($attempttotals->{$fieldprefix . 'total'});
}
return $attempttotals;
}
