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