public function display($quiz, $cm, $course) { global $CFG, $DB, $OUTPUT, $PAGE; list($currentgroup, $students, $groupstudents, $allowed) = $this->init('overview', 'quiz_overview_settings_form', $quiz, $cm, $course); $options = new quiz_overview_options('overview', $quiz, $cm, $course); if ($fromform = $this->form->get_data()) { $options->process_settings_from_form($fromform); } else { $options->process_settings_from_params(); } $this->form->set_data($options->get_initial_form_data()); if ($options->attempts == self::ALL_WITH) { // This option is only available to users who can access all groups in // groups mode, so setting allowed to empty (which means all quiz attempts // are accessible, is not a security porblem. $allowed = array(); } // Load the required questions. $questions = quiz_report_get_significant_questions($quiz); // Prepare for downloading, if applicable. $courseshortname = format_string($course->shortname, true, array('context' => context_course::instance($course->id))); $table = new quiz_overview_table($quiz, $this->context, $this->qmsubselect, $options, $groupstudents, $students, $questions, $options->get_url()); $filename = quiz_report_download_filename(get_string('overviewfilename', 'quiz_overview'), $courseshortname, $quiz->name); $table->is_downloading($options->download, $filename, $courseshortname . ' ' . format_string($quiz->name, true)); if ($table->is_downloading()) { raise_memory_limit(MEMORY_EXTRA); } $this->course = $course; // Hack to make this available in process_actions. $this->process_actions($quiz, $cm, $currentgroup, $groupstudents, $allowed, $options->get_url()); // Start output. if (!$table->is_downloading()) { // Only print headers if not asked to download data. $this->print_header_and_tabs($cm, $course, $quiz, $this->mode); } if ($groupmode = groups_get_activity_groupmode($cm)) { // Groups are being used, so output the group selector if we are not downloading. if (!$table->is_downloading()) { groups_print_activity_menu($cm, $options->get_url()); } } // Print information on the number of existing attempts. if (!$table->is_downloading()) { // Do not print notices when downloading. if ($strattemptnum = quiz_num_attempt_summary($quiz, $cm, true, $currentgroup)) { echo '<div class="quizattemptcounts">' . $strattemptnum . '</div>'; } } $hasquestions = quiz_questions_in_quiz($quiz->questions); if (!$table->is_downloading()) { if (!$hasquestions) { echo quiz_no_questions_message($quiz, $cm, $this->context); } else { if (!$students) { echo $OUTPUT->notification(get_string('nostudentsyet')); } else { if ($currentgroup && !$groupstudents) { echo $OUTPUT->notification(get_string('nostudentsingroup')); } } } // Print the display options. $this->form->display(); } $hasstudents = $students && (!$currentgroup || $groupstudents); if ($hasquestions && ($hasstudents || $options->attempts == self::ALL_WITH)) { // Construct the SQL. $fields = $DB->sql_concat('u.id', "'#'", 'COALESCE(quiza.attempt, 0)') . ' AS uniqueid, '; if ($this->qmsubselect) { $fields .= "(CASE " . " WHEN {$this->qmsubselect} THEN 1" . " ELSE 0 " . "END) AS gradedattempt, "; } list($fields, $from, $where, $params) = $table->base_sql($allowed); $table->set_count_sql("SELECT COUNT(1) FROM {$from} WHERE {$where}", $params); // Test to see if there are any regraded attempts to be listed. $fields .= ", COALESCE((\n SELECT MAX(qqr.regraded)\n FROM {quiz_overview_regrades} qqr\n WHERE qqr.questionusageid = quiza.uniqueid\n ), -1) AS regraded"; if ($options->onlyregraded) { $where .= " AND COALESCE((\n SELECT MAX(qqr.regraded)\n FROM {quiz_overview_regrades} qqr\n WHERE qqr.questionusageid = quiza.uniqueid\n ), -1) <> -1"; } $table->set_sql($fields, $from, $where, $params); if (!$table->is_downloading()) { // Output the regrade buttons. if (has_capability('mod/quiz:regrade', $this->context)) { $regradesneeded = $this->count_question_attempts_needing_regrade($quiz, $groupstudents); if ($currentgroup) { $a = new stdClass(); $a->groupname = groups_get_group_name($currentgroup); $a->coursestudents = get_string('participants'); $a->countregradeneeded = $regradesneeded; $regradealldrydolabel = get_string('regradealldrydogroup', 'quiz_overview', $a); $regradealldrylabel = get_string('regradealldrygroup', 'quiz_overview', $a); $regradealllabel = get_string('regradeallgroup', 'quiz_overview', $a); } else { $regradealldrydolabel = get_string('regradealldrydo', 'quiz_overview', $regradesneeded); $regradealldrylabel = get_string('regradealldry', 'quiz_overview'); $regradealllabel = get_string('regradeall', 'quiz_overview'); } $displayurl = new moodle_url($options->get_url(), array('sesskey' => sesskey())); echo '<div class="mdl-align">'; echo '<form action="' . $displayurl->out_omit_querystring() . '">'; echo '<div>'; echo html_writer::input_hidden_params($displayurl); echo '<input type="submit" name="regradeall" value="' . $regradealllabel . '"/>'; echo '<input type="submit" name="regradealldry" value="' . $regradealldrylabel . '"/>'; if ($regradesneeded) { echo '<input type="submit" name="regradealldrydo" value="' . $regradealldrydolabel . '"/>'; } echo '</div>'; echo '</form>'; echo '</div>'; } // Print information on the grading method. if ($strattempthighlight = quiz_report_highlighting_grading_method($quiz, $this->qmsubselect, $options->onlygraded)) { echo '<div class="quizattemptcounts">' . $strattempthighlight . '</div>'; } } // Define table columns. $columns = array(); $headers = array(); if (!$table->is_downloading() && $options->checkboxcolumn) { $columns[] = 'checkbox'; $headers[] = null; } $this->add_user_columns($table, $columns, $headers); $this->add_state_column($columns, $headers); $this->add_time_columns($columns, $headers); $this->add_grade_columns($quiz, $options->usercanseegrades, $columns, $headers, false); if (!$table->is_downloading() && has_capability('mod/quiz:regrade', $this->context) && $this->has_regraded_questions($from, $where, $params)) { $columns[] = 'regraded'; $headers[] = get_string('regrade', 'quiz_overview'); } if ($options->slotmarks) { foreach ($questions as $slot => $question) { // Ignore questions of zero length. $columns[] = 'qsgrade' . $slot; $header = get_string('qbrief', 'quiz', $question->number); if (!$table->is_downloading()) { $header .= '<br />'; } else { $header .= ' '; } $header .= '/' . quiz_rescale_grade($question->maxmark, $quiz, 'question'); $headers[] = $header; } } $this->set_up_table_columns($table, $columns, $headers, $this->get_base_url(), $options, false); $table->set_attribute('class', 'generaltable generalbox grades'); $table->out($options->pagesize, true); } if (!$table->is_downloading() && $options->usercanseegrades) { $output = $PAGE->get_renderer('mod_quiz'); if ($currentgroup && $groupstudents) { list($usql, $params) = $DB->get_in_or_equal($groupstudents); $params[] = $quiz->id; if ($DB->record_exists_select('quiz_grades', "userid {$usql} AND quiz = ?", $params)) { $imageurl = new moodle_url('/mod/quiz/report/overview/overviewgraph.php', array('id' => $quiz->id, 'groupid' => $currentgroup)); $graphname = get_string('overviewreportgraphgroup', 'quiz_overview', groups_get_group_name($currentgroup)); echo $output->graph($imageurl, $graphname); } } if ($DB->record_exists('quiz_grades', array('quiz' => $quiz->id))) { $imageurl = new moodle_url('/mod/quiz/report/overview/overviewgraph.php', array('id' => $quiz->id)); $graphname = get_string('overviewreportgraph', 'quiz_overview'); echo $output->graph($imageurl, $graphname); } } return true; }
public function test_report_sql() { global $DB, $SITE; $this->resetAfterTest(true); $generator = $this->getDataGenerator(); $quizgenerator = $generator->get_plugin_generator('mod_quiz'); $quiz = $quizgenerator->create_instance(array('course' => $SITE->id, 'grademethod' => QUIZ_GRADEHIGHEST, 'grade' => 100.0, 'sumgrades' => 10.0, 'attempts' => 10)); $student1 = $generator->create_user(); $student2 = $generator->create_user(); $student3 = $generator->create_user(); $quizid = 123; $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 quit 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); $reportstudents = array($student1->id, $student2->id, $student3->id); // 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, array(), $reportstudents, 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($reportstudents); $table->set_sql($fields, $from, $where, $params); $table->query_db(30, false); // Verify what was returned: Student 1's best and in progress attempts. // Stuent 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); }
public function display($quiz, $cm, $course) { global $CFG, $COURSE, $DB, $OUTPUT; $this->context = get_context_instance(CONTEXT_MODULE, $cm->id); $download = optional_param('download', '', PARAM_ALPHA); list($currentgroup, $students, $groupstudents, $allowed) = $this->load_relevant_students($cm, $course); $pageoptions = array(); $pageoptions['id'] = $cm->id; $pageoptions['mode'] = 'overview'; $reporturl = new moodle_url('/mod/quiz/report.php', $pageoptions); $qmsubselect = quiz_report_qm_filter_select($quiz); $mform = new mod_quiz_report_overview_settings($reporturl, array('qmsubselect' => $qmsubselect, 'quiz' => $quiz, 'currentgroup' => $currentgroup, 'context' => $this->context)); if ($fromform = $mform->get_data()) { $regradeall = false; $regradealldry = false; $regradealldrydo = false; $attemptsmode = $fromform->attemptsmode; if ($qmsubselect) { $qmfilter = $fromform->qmfilter; } else { $qmfilter = 0; } $regradefilter = !empty($fromform->regradefilter); set_user_preference('quiz_report_overview_detailedmarks', $fromform->detailedmarks); set_user_preference('quiz_report_pagesize', $fromform->pagesize); $detailedmarks = $fromform->detailedmarks; $pagesize = $fromform->pagesize; } else { $regradeall = optional_param('regradeall', 0, PARAM_BOOL); $regradealldry = optional_param('regradealldry', 0, PARAM_BOOL); $regradealldrydo = optional_param('regradealldrydo', 0, PARAM_BOOL); $attemptsmode = optional_param('attemptsmode', null, PARAM_INT); if ($qmsubselect) { $qmfilter = optional_param('qmfilter', 0, PARAM_INT); } else { $qmfilter = 0; } $regradefilter = optional_param('regradefilter', 0, PARAM_INT); $detailedmarks = get_user_preferences('quiz_report_overview_detailedmarks', 1); $pagesize = get_user_preferences('quiz_report_pagesize', 0); } $this->validate_common_options($attemptsmode, $pagesize, $course, $currentgroup); $displayoptions = array(); $displayoptions['attemptsmode'] = $attemptsmode; $displayoptions['qmfilter'] = $qmfilter; $displayoptions['regradefilter'] = $regradefilter; $mform->set_data($displayoptions + array('detailedmarks' => $detailedmarks, 'pagesize' => $pagesize)); if (!$this->should_show_grades($quiz)) { $detailedmarks = 0; } // We only want to show the checkbox to delete attempts // if the user has permissions and if the report mode is showing attempts. $includecheckboxes = has_any_capability( array('mod/quiz:regrade', 'mod/quiz:deleteattempts'), $this->context) && ($attemptsmode != QUIZ_REPORT_ATTEMPTS_STUDENTS_WITH_NO); if ($attemptsmode == QUIZ_REPORT_ATTEMPTS_ALL) { // This option is only available to users who can access all groups in // groups mode, so setting allowed to empty (which means all quiz attempts // are accessible, is not a security porblem. $allowed = array(); } $coursecontext = get_context_instance(CONTEXT_COURSE, $course->id); $courseshortname = format_string($course->shortname, true, array('context' => $coursecontext)); $displaycoursecontext = get_context_instance(CONTEXT_COURSE, $COURSE->id); $displaycourseshortname = format_string($COURSE->shortname, true, array('context' => $displaycoursecontext)); // Load the required questions. $questions = quiz_report_get_significant_questions($quiz); $table = new quiz_overview_table($quiz, $this->context, $qmsubselect, $qmfilter, $attemptsmode, $groupstudents, $students, $detailedmarks, $questions, $includecheckboxes, $reporturl, $displayoptions); $filename = quiz_report_download_filename(get_string('overviewfilename', 'quiz_overview'), $courseshortname, $quiz->name); $table->is_downloading($download, $filename, $displaycourseshortname . ' ' . format_string($quiz->name, true)); if ($table->is_downloading()) { raise_memory_limit(MEMORY_EXTRA); } // Process actions. if (empty($currentgroup) || $groupstudents) { if (optional_param('delete', 0, PARAM_BOOL) && confirm_sesskey()) { if ($attemptids = optional_param_array('attemptid', array(), PARAM_INT)) { require_capability('mod/quiz:deleteattempts', $this->context); $this->delete_selected_attempts($quiz, $cm, $attemptids, $allowed); redirect($reporturl->out(false, $displayoptions)); } } else if (optional_param('regrade', 0, PARAM_BOOL) && confirm_sesskey()) { if ($attemptids = optional_param_array('attemptid', array(), PARAM_INT)) { require_capability('mod/quiz:regrade', $this->context); $this->regrade_attempts($quiz, false, $groupstudents, $attemptids); redirect($reporturl->out(false, $displayoptions)); } } } if ($regradeall && confirm_sesskey()) { require_capability('mod/quiz:regrade', $this->context); $this->regrade_attempts($quiz, false, $groupstudents); redirect($reporturl->out(false, $displayoptions), '', 5); } else if ($regradealldry && confirm_sesskey()) { require_capability('mod/quiz:regrade', $this->context); $this->regrade_attempts($quiz, true, $groupstudents); redirect($reporturl->out(false, $displayoptions), '', 5); } else if ($regradealldrydo && confirm_sesskey()) { require_capability('mod/quiz:regrade', $this->context); $this->regrade_attempts_needing_it($quiz, $groupstudents); redirect($reporturl->out(false, $displayoptions), '', 5); } // Start output. if (!$table->is_downloading()) { // Only print headers if not asked to download data $this->print_header_and_tabs($cm, $course, $quiz, 'overview'); } if ($groupmode = groups_get_activity_groupmode($cm)) { // Groups are being used if (!$table->is_downloading()) { groups_print_activity_menu($cm, $reporturl->out(true, $displayoptions)); } } // Print information on the number of existing attempts if (!$table->is_downloading()) { //do not print notices when downloading if ($strattemptnum = quiz_num_attempt_summary($quiz, $cm, true, $currentgroup)) { echo '<div class="quizattemptcounts">' . $strattemptnum . '</div>'; } } $hasquestions = quiz_questions_in_quiz($quiz->questions); if (!$table->is_downloading()) { if (!$hasquestions) { echo quiz_no_questions_message($quiz, $cm, $this->context); } else if (!$students) { echo $OUTPUT->notification(get_string('nostudentsyet')); } else if ($currentgroup && !$groupstudents) { echo $OUTPUT->notification(get_string('nostudentsingroup')); } // Print display options $mform->display(); } $hasstudents = $students && (!$currentgroup || $groupstudents); if ($hasquestions && ($hasstudents || ($attemptsmode == QUIZ_REPORT_ATTEMPTS_ALL))) { // Construct the SQL $fields = $DB->sql_concat('u.id', "'#'", 'COALESCE(quiza.attempt, 0)') . ' AS uniqueid, '; if ($qmsubselect) { $fields .= "(CASE " . " WHEN $qmsubselect THEN 1" . " ELSE 0 " . "END) AS gradedattempt, "; } list($fields, $from, $where, $params) = $table->base_sql($allowed); $table->set_count_sql("SELECT COUNT(1) FROM $from WHERE $where", $params); // Test to see if there are any regraded attempts to be listed. $fields .= ", COALESCE(( SELECT MAX(qqr.regraded) FROM {quiz_overview_regrades} qqr WHERE qqr.questionusageid = quiza.uniqueid ), -1) AS regraded"; if ($regradefilter) { $where .= " AND COALESCE(( SELECT MAX(qqr.regraded) FROM {quiz_overview_regrades} qqr WHERE qqr.questionusageid = quiza.uniqueid ), -1) <> -1"; } $table->set_sql($fields, $from, $where, $params); if (!$table->is_downloading()) { // Regrade buttons if (has_capability('mod/quiz:regrade', $this->context)) { $regradesneeded = $this->count_question_attempts_needing_regrade( $quiz, $groupstudents); if ($currentgroup) { $a= new stdClass(); $a->groupname = groups_get_group_name($currentgroup); $a->coursestudents = get_string('participants'); $a->countregradeneeded = $regradesneeded; $regradealldrydolabel = get_string('regradealldrydogroup', 'quiz_overview', $a); $regradealldrylabel = get_string('regradealldrygroup', 'quiz_overview', $a); $regradealllabel = get_string('regradeallgroup', 'quiz_overview', $a); } else { $regradealldrydolabel = get_string('regradealldrydo', 'quiz_overview', $regradesneeded); $regradealldrylabel = get_string('regradealldry', 'quiz_overview'); $regradealllabel = get_string('regradeall', 'quiz_overview'); } $displayurl = new moodle_url($reporturl, $displayoptions + array('sesskey' => sesskey())); echo '<div class="mdl-align">'; echo '<form action="'.$displayurl->out_omit_querystring().'">'; echo '<div>'; echo html_writer::input_hidden_params($displayurl); echo '<input type="submit" name="regradeall" value="'.$regradealllabel.'"/>'; echo '<input type="submit" name="regradealldry" value="' . $regradealldrylabel . '"/>'; if ($regradesneeded) { echo '<input type="submit" name="regradealldrydo" value="' . $regradealldrydolabel . '"/>'; } echo '</div>'; echo '</form>'; echo '</div>'; } // Print information on the grading method if ($strattempthighlight = quiz_report_highlighting_grading_method( $quiz, $qmsubselect, $qmfilter)) { echo '<div class="quizattemptcounts">' . $strattempthighlight . '</div>'; } } // Define table columns $columns = array(); $headers = array(); if (!$table->is_downloading() && $includecheckboxes) { $columns[] = 'checkbox'; $headers[] = null; } $this->add_user_columns($table, $columns, $headers); $this->add_time_columns($columns, $headers); if ($detailedmarks) { foreach ($questions as $slot => $question) { // Ignore questions of zero length $columns[] = 'qsgrade' . $slot; $header = get_string('qbrief', 'quiz', $question->number); if (!$table->is_downloading()) { $header .= '<br />'; } else { $header .= ' '; } $header .= '/' . quiz_rescale_grade($question->maxmark, $quiz, 'question'); $headers[] = $header; } } if (!$table->is_downloading() && has_capability('mod/quiz:regrade', $this->context) && $this->has_regraded_questions($from, $where, $params)) { $columns[] = 'regraded'; $headers[] = get_string('regrade', 'quiz_overview'); } $this->add_grade_columns($quiz, $columns, $headers, false); $this->set_up_table_columns( $table, $columns, $headers, $reporturl, $displayoptions, false); $table->set_attribute('class', 'generaltable generalbox grades'); $table->out($pagesize, true); } if (!$table->is_downloading() && $this->should_show_grades($quiz)) { if ($currentgroup && $groupstudents) { list($usql, $params) = $DB->get_in_or_equal($groupstudents); $params[] = $quiz->id; if ($DB->record_exists_select('quiz_grades', "userid $usql AND quiz = ?", $params)) { $imageurl = new moodle_url('/mod/quiz/report/overview/overviewgraph.php', array('id' => $quiz->id, 'groupid' => $currentgroup)); $graphname = get_string('overviewreportgraphgroup', 'quiz_overview', groups_get_group_name($currentgroup)); echo $OUTPUT->heading($graphname); echo html_writer::tag('div', html_writer::empty_tag('img', array('src' => $imageurl, 'alt' => $graphname)), array('class' => 'graph')); } } if ($DB->record_exists('quiz_grades', array('quiz'=> $quiz->id))) { $graphname = get_string('overviewreportgraph', 'quiz_overview'); $imageurl = new moodle_url('/mod/quiz/report/overview/overviewgraph.php', array('id' => $quiz->id)); echo $OUTPUT->heading($graphname); echo html_writer::tag('div', html_writer::empty_tag('img', array('src' => $imageurl, 'alt' => $graphname)), array('class' => 'graph')); } } return true; }