public function test_report_sql()
{
global $DB;
$this->resetAfterTest(true);
$generator = $this->getDataGenerator();
$course = $generator->create_course();
$quizgenerator = $generator->get_plugin_generator('mod_quiz');
$quiz = $quizgenerator->create_instance(array('course' => $course->id, 'grademethod' => QUIZ_GRADEHIGHEST, 'grade' => 100.0, 'sumgrades' => 10.0, 'attempts' => 10));
$student1 = $generator->create_user();
$student2 = $generator->create_user();
$student3 = $generator->create_user();
$generator->enrol_user($student1->id, $course->id);
$generator->enrol_user($student2->id, $course->id);
$generator->enrol_user($student3->id, $course->id);
$timestamp = 1234567890;
// The test data.
$fields = array('quiz', 'userid', 'attempt', 'sumgrades', 'state');
$attempts = array(array($quiz->id, $student1->id, 1, 0.0, quiz_attempt::FINISHED), array($quiz->id, $student1->id, 2, 5.0, quiz_attempt::FINISHED), array($quiz->id, $student1->id, 3, 8.0, quiz_attempt::FINISHED), array($quiz->id, $student1->id, 4, null, quiz_attempt::ABANDONED), array($quiz->id, $student1->id, 5, null, quiz_attempt::IN_PROGRESS), array($quiz->id, $student2->id, 1, null, quiz_attempt::ABANDONED), array($quiz->id, $student2->id, 2, null, quiz_attempt::ABANDONED), array($quiz->id, $student2->id, 3, 7.0, quiz_attempt::FINISHED), array($quiz->id, $student2->id, 4, null, quiz_attempt::ABANDONED), array($quiz->id, $student2->id, 5, null, quiz_attempt::ABANDONED));
// Load it in to quiz attempts table.
$uniqueid = 1;
foreach ($attempts as $attempt) {
$data = array_combine($fields, $attempt);
$data['timestart'] = $timestamp + 3600 * $data['attempt'];
$data['timemodifed'] = $data['timestart'];
if ($data['state'] == quiz_attempt::FINISHED) {
$data['timefinish'] = $data['timestart'] + 600;
$data['timemodifed'] = $data['timefinish'];
}
$data['layout'] = '';
// Not used, but cannot be null.
$data['uniqueid'] = $uniqueid++;
$data['preview'] = 0;
$DB->insert_record('quiz_attempts', $data);
}
// Actually getting the SQL to run is quite hard. Do a minimal set up of
// some objects.
$context = context_module::instance($quiz->cmid);
$cm = get_coursemodule_from_id('quiz', $quiz->cmid);
$qmsubselect = quiz_report_qm_filter_select($quiz);
$studentsjoins = get_enrolled_with_capabilities_join($context);
$empty = new \core\dml\sql_join();
// Set the options.
$reportoptions = new quiz_overview_options('overview', $quiz, $cm, null);
$reportoptions->attempts = quiz_attempts_report::ENROLLED_ALL;
$reportoptions->onlygraded = true;
$reportoptions->states = array(quiz_attempt::IN_PROGRESS, quiz_attempt::OVERDUE, quiz_attempt::FINISHED);
// Now do a minimal set-up of the table class.
$table = new quiz_overview_table($quiz, $context, $qmsubselect, $reportoptions, $empty, $studentsjoins, array(1), null);
$table->define_columns(array('attempt'));
$table->sortable(true, 'uniqueid');
$table->define_baseurl(new moodle_url('/mod/quiz/report.php'));
$table->setup();
// Run the query.
list($fields, $from, $where, $params) = $table->base_sql($studentsjoins);
$table->set_sql($fields, $from, $where, $params);
$table->query_db(30, false);
// Verify what was returned: Student 1's best and in progress attempts.
// Student 2's finshed attempt, and Student 3 with no attempt.
// The array key is {student id}#{attempt number}.
$this->assertEquals(4, count($table->rawdata));
$this->assertArrayHasKey($student1->id . '#3', $table->rawdata);
$this->assertEquals(1, $table->rawdata[$student1->id . '#3']->gradedattempt);
$this->assertArrayHasKey($student1->id . '#3', $table->rawdata);
$this->assertEquals(0, $table->rawdata[$student1->id . '#5']->gradedattempt);
$this->assertArrayHasKey($student2->id . '#3', $table->rawdata);
$this->assertEquals(1, $table->rawdata[$student2->id . '#3']->gradedattempt);
$this->assertArrayHasKey($student3->id . '#0', $table->rawdata);
$this->assertEquals(0, $table->rawdata[$student3->id . '#0']->gradedattempt);
}
/**
* Counts list of users enrolled into course (as per above function)
*
* @param context $context
* @param string $withcapability
* @param int $groupid 0 means ignore groups, any other value limits the result by group id
* @param bool $onlyactive consider only active enrolments in enabled plugins and time restrictions
* @return array of user records
*/
function count_enrolled_users(context $context, $withcapability = '', $groupid = 0, $onlyactive = false)
{
global $DB;
$capjoin = get_enrolled_with_capabilities_join($context, '', $withcapability, $groupid, $onlyactive);
$sql = "SELECT count(u.id)\n FROM {user} u\n {$capjoin->joins}\n WHERE {$capjoin->wheres} AND u.deleted = 0";
return $DB->count_records_sql($sql, $capjoin->params);
}
/**
* Get sql fragments (joins) which can be used to build queries that
* will select an appropriate set of students to show in the reports.
*
* @param object $cm the course module.
* @param object $course the course settings.
* @return array with four elements:
* 0 => integer the current group id (0 for none).
* 1 => \core\dml\sql_join Contains joins, wheres, params for all the students in this course.
* 2 => \core\dml\sql_join Contains joins, wheres, params for all the students in the current group.
* 3 => \core\dml\sql_join Contains joins, wheres, params for all the students to show in the report.
* Will be the same as either element 1 or 2.
*/
protected function get_students_joins($cm, $course = null)
{
$currentgroup = $this->get_current_group($cm, $course, $this->context);
$empty = new \core\dml\sql_join();
if ($currentgroup == self::NO_GROUPS_ALLOWED) {
return array($currentgroup, $empty, $empty, $empty);
}
$studentsjoins = get_enrolled_with_capabilities_join($this->context);
if (empty($currentgroup)) {
return array($currentgroup, $studentsjoins, $empty, $studentsjoins);
}
// We have a currently selected group.
$groupstudentsjoins = get_enrolled_with_capabilities_join($this->context, '', array('mod/quiz:attempt', 'mod/quiz:reviewmyattempts'), $currentgroup);
return array($currentgroup, $studentsjoins, $groupstudentsjoins, $groupstudentsjoins);
}
/**
* Display the report.
*/
public function display($quiz, $cm, $course)
{
global $OUTPUT, $DB;
raise_memory_limit(MEMORY_HUGE);
$this->context = context_module::instance($cm->id);
if (!quiz_has_questions($quiz->id)) {
$this->print_header_and_tabs($cm, $course, $quiz, 'statistics');
echo quiz_no_questions_message($quiz, $cm, $this->context);
return true;
}
// Work out the display options.
$download = optional_param('download', '', PARAM_ALPHA);
$everything = optional_param('everything', 0, PARAM_BOOL);
$recalculate = optional_param('recalculate', 0, PARAM_BOOL);
// A qid paramter indicates we should display the detailed analysis of a sub question.
$qid = optional_param('qid', 0, PARAM_INT);
$slot = optional_param('slot', 0, PARAM_INT);
$variantno = optional_param('variant', null, PARAM_INT);
$whichattempts = optional_param('whichattempts', $quiz->grademethod, PARAM_INT);
$whichtries = optional_param('whichtries', question_attempt::LAST_TRY, PARAM_ALPHA);
$pageoptions = array();
$pageoptions['id'] = $cm->id;
$pageoptions['mode'] = 'statistics';
$reporturl = new moodle_url('/mod/quiz/report.php', $pageoptions);
$mform = new quiz_statistics_settings_form($reporturl, compact('quiz'));
$mform->set_data(array('whichattempts' => $whichattempts, 'whichtries' => $whichtries));
if ($whichattempts != $quiz->grademethod) {
$reporturl->param('whichattempts', $whichattempts);
}
if ($whichtries != question_attempt::LAST_TRY) {
$reporturl->param('whichtries', $whichtries);
}
// Find out current groups mode.
$currentgroup = $this->get_current_group($cm, $course, $this->context);
$nostudentsingroup = false;
// True if a group is selected and there is no one in it.
if (empty($currentgroup)) {
$currentgroup = 0;
$groupstudentsjoins = new \core\dml\sql_join();
} else {
if ($currentgroup == self::NO_GROUPS_ALLOWED) {
$groupstudentsjoins = new \core\dml\sql_join();
$nostudentsingroup = true;
} else {
// All users who can attempt quizzes and who are in the currently selected group.
$groupstudentsjoins = get_enrolled_with_capabilities_join($this->context, '', array('mod/quiz:reviewmyattempts', 'mod/quiz:attempt'), $currentgroup);
if (!empty($groupstudentsjoins->joins)) {
$sql = "SELECT DISTINCT u.id\n FROM {user} u\n {$groupstudentsjoins->joins}\n WHERE {$groupstudentsjoins->wheres}";
if (!$DB->record_exists_sql($sql, $groupstudentsjoins->params)) {
$nostudentsingroup = true;
}
}
}
}
$qubaids = quiz_statistics_qubaids_condition($quiz->id, $groupstudentsjoins, $whichattempts);
// If recalculate was requested, handle that.
if ($recalculate && confirm_sesskey()) {
$this->clear_cached_data($qubaids);
redirect($reporturl);
}
// Set up the main table.
$this->table = new quiz_statistics_table();
if ($everything) {
$report = get_string('completestatsfilename', 'quiz_statistics');
} else {
$report = get_string('questionstatsfilename', 'quiz_statistics');
}
$courseshortname = format_string($course->shortname, true, array('context' => context_course::instance($course->id)));
$filename = quiz_report_download_filename($report, $courseshortname, $quiz->name);
$this->table->is_downloading($download, $filename, get_string('quizstructureanalysis', 'quiz_statistics'));
$questions = $this->load_and_initialise_questions_for_calculations($quiz);
// Print the page header stuff (if not downloading.
if (!$this->table->is_downloading()) {
$this->print_header_and_tabs($cm, $course, $quiz, 'statistics');
}
if (!$nostudentsingroup) {
// Get the data to be displayed.
$progress = $this->get_progress_trace_instance();
list($quizstats, $questionstats) = $this->get_all_stats_and_analysis($quiz, $whichattempts, $whichtries, $groupstudentsjoins, $questions, $progress);
} else {
// Or create empty stats containers.
$quizstats = new \quiz_statistics\calculated($whichattempts);
$questionstats = new \core_question\statistics\questions\all_calculated_for_qubaid_condition();
}
// Set up the table.
$this->table->statistics_setup($quiz, $cm->id, $reporturl, $quizstats->s());
// Print the rest of the page header stuff (if not downloading.
if (!$this->table->is_downloading()) {
if (groups_get_activity_groupmode($cm)) {
groups_print_activity_menu($cm, $reporturl->out());
if ($currentgroup && $nostudentsingroup) {
$OUTPUT->notification(get_string('nostudentsingroup', 'quiz_statistics'));
}
}
if (!$this->table->is_downloading() && $quizstats->s() == 0) {
echo $OUTPUT->notification(get_string('nogradedattempts', 'quiz_statistics'));
}
foreach ($questionstats->any_error_messages() as $errormessage) {
echo $OUTPUT->notification($errormessage);
}
// Print display options form.
$mform->display();
}
if ($everything) {
// Implies is downloading.
// Overall report, then the analysis of each question.
$quizinfo = $quizstats->get_formatted_quiz_info_data($course, $cm, $quiz);
$this->download_quiz_info_table($quizinfo);
if ($quizstats->s()) {
$this->output_quiz_structure_analysis_table($questionstats);
if ($this->table->is_downloading() == 'xhtml' && $quizstats->s() != 0) {
$this->output_statistics_graph($quiz->id, $qubaids);
}
$this->output_all_question_response_analysis($qubaids, $questions, $questionstats, $reporturl, $whichtries);
}
$this->table->export_class_instance()->finish_document();
} else {
if ($qid) {
// Report on an individual sub-question indexed questionid.
if (is_null($questionstats->for_subq($qid, $variantno))) {
print_error('questiondoesnotexist', 'question');
}
$this->output_individual_question_data($quiz, $questionstats->for_subq($qid, $variantno));
$this->output_individual_question_response_analysis($questionstats->for_subq($qid, $variantno)->question, $variantno, $questionstats->for_subq($qid, $variantno)->s, $reporturl, $qubaids, $whichtries);
// Back to overview link.
echo $OUTPUT->box('<a href="' . $reporturl->out() . '">' . get_string('backtoquizreport', 'quiz_statistics') . '</a>', 'boxaligncenter generalbox boxwidthnormal mdl-align');
} else {
if ($slot) {
// Report on an individual question indexed by position.
if (!isset($questions[$slot])) {
print_error('questiondoesnotexist', 'question');
}
if ($variantno === null && ($questionstats->for_slot($slot)->get_sub_question_ids() || $questionstats->for_slot($slot)->get_variants())) {
if (!$this->table->is_downloading()) {
$number = $questionstats->for_slot($slot)->question->number;
echo $OUTPUT->heading(get_string('slotstructureanalysis', 'quiz_statistics', $number), 3);
}
$this->table->define_baseurl(new moodle_url($reporturl, array('slot' => $slot)));
$this->table->format_and_add_array_of_rows($questionstats->structure_analysis_for_one_slot($slot));
} else {
$this->output_individual_question_data($quiz, $questionstats->for_slot($slot, $variantno));
$this->output_individual_question_response_analysis($questions[$slot], $variantno, $questionstats->for_slot($slot, $variantno)->s, $reporturl, $qubaids, $whichtries);
}
if (!$this->table->is_downloading()) {
// Back to overview link.
echo $OUTPUT->box('<a href="' . $reporturl->out() . '">' . get_string('backtoquizreport', 'quiz_statistics') . '</a>', 'backtomainstats boxaligncenter generalbox boxwidthnormal mdl-align');
} else {
$this->table->finish_output();
}
} else {
if ($this->table->is_downloading()) {
// Downloading overview report.
$quizinfo = $quizstats->get_formatted_quiz_info_data($course, $cm, $quiz);
$this->download_quiz_info_table($quizinfo);
if ($quizstats->s()) {
$this->output_quiz_structure_analysis_table($questionstats);
}
$this->table->finish_output();
} else {
// On-screen display of overview report.
echo $OUTPUT->heading(get_string('quizinformation', 'quiz_statistics'), 3);
echo $this->output_caching_info($quizstats->timemodified, $quiz->id, $groupstudentsjoins, $whichattempts, $reporturl);
echo $this->everything_download_options($reporturl);
$quizinfo = $quizstats->get_formatted_quiz_info_data($course, $cm, $quiz);
echo $this->output_quiz_info_table($quizinfo);
if ($quizstats->s()) {
echo $OUTPUT->heading(get_string('quizstructureanalysis', 'quiz_statistics'), 3);
$this->output_quiz_structure_analysis_table($questionstats);
$this->output_statistics_graph($quiz, $qubaids);
}
}
}
}
}
return true;
}
