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