MDL-29199 dml: fix query logging on Oracle
[moodle.git] / mod / lesson / report.php
blobf2d1e059f539c085bf7d9522e935acbef4bd0d40
1 <?php
3 // This file is part of Moodle - http://moodle.org/
4 //
5 // Moodle is free software: you can redistribute it and/or modify
6 // it under the terms of the GNU General Public License as published by
7 // the Free Software Foundation, either version 3 of the License, or
8 // (at your option) any later version.
9 //
10 // Moodle is distributed in the hope that it will be useful,
11 // but WITHOUT ANY WARRANTY; without even the implied warranty of
12 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 // GNU General Public License for more details.
15 // You should have received a copy of the GNU General Public License
16 // along with Moodle. If not, see <http://www.gnu.org/licenses/>.
18 /**
19 * Displays the lesson statistics.
21 * @package mod
22 * @subpackage lesson
23 * @copyright 1999 onwards Martin Dougiamas {@link http://moodle.com}
24 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or late
25 **/
27 require_once('../../config.php');
28 require_once($CFG->dirroot.'/mod/lesson/locallib.php');
30 $id = required_param('id', PARAM_INT); // Course Module ID
31 $pageid = optional_param('pageid', NULL, PARAM_INT); // Lesson Page ID
32 $action = optional_param('action', 'reportoverview', PARAM_ALPHA); // action to take
33 $nothingtodisplay = false;
35 $cm = get_coursemodule_from_id('lesson', $id, 0, false, MUST_EXIST);
36 $course = $DB->get_record('course', array('id' => $cm->course), '*', MUST_EXIST);
37 $lesson = new lesson($DB->get_record('lesson', array('id' => $cm->instance), '*', MUST_EXIST));
39 require_login($course, false, $cm);
41 $context = get_context_instance(CONTEXT_MODULE, $cm->id);
42 require_capability('mod/lesson:manage', $context);
44 $ufields = user_picture::fields('u'); // These fields are enough
45 $params = array("lessonid" => $lesson->id);
46 // TODO: Improve this. Fetching all students always is crazy!
47 if (!empty($cm->groupingid)) {
48 $params["groupid"] = $cm->groupingid;
49 $sql = "SELECT DISTINCT $ufields
50 FROM {lesson_attempts} a
51 INNER JOIN {user} u ON u.id = a.userid
52 INNER JOIN {groups_members} gm ON gm.userid = u.id
53 INNER JOIN {groupings_groups} gg ON gm.groupid = :groupid
54 WHERE a.lessonid = :lessonid
55 ORDER BY u.lastname";
56 } else {
57 $sql = "SELECT DISTINCT $ufields
58 FROM {user} u,
59 {lesson_attempts} a
60 WHERE a.lessonid = :lessonid and
61 u.id = a.userid
62 ORDER BY u.lastname";
65 if (! $students = $DB->get_records_sql($sql, $params)) {
66 $nothingtodisplay = true;
69 $url = new moodle_url('/mod/lesson/report.php', array('id'=>$id));
70 if ($action !== 'reportoverview') {
71 $url->param('action', $action);
73 if ($pageid !== NULL) {
74 $url->param('pageid', $pageid);
76 $PAGE->set_url($url);
77 if ($action == 'reportoverview') {
78 $PAGE->navbar->add(get_string('reports', 'lesson'));
79 $PAGE->navbar->add(get_string('overview', 'lesson'));
82 $lessonoutput = $PAGE->get_renderer('mod_lesson');
84 if (! $attempts = $DB->get_records('lesson_attempts', array('lessonid' => $lesson->id), 'timeseen')) {
85 $nothingtodisplay = true;
88 if (! $grades = $DB->get_records('lesson_grades', array('lessonid' => $lesson->id), 'completed')) {
89 $grades = array();
92 if (! $times = $DB->get_records('lesson_timer', array('lessonid' => $lesson->id), 'starttime')) {
93 $times = array();
96 if ($nothingtodisplay) {
97 echo $lessonoutput->header($lesson, $cm, $action);
98 echo $OUTPUT->notification(get_string('nolessonattempts', 'lesson'));
99 echo $OUTPUT->footer();
100 exit();
103 if ($action === 'delete') {
104 /// Process any form data before fetching attempts, grades and times
105 if (has_capability('mod/lesson:edit', $context) and $form = data_submitted() and confirm_sesskey()) {
106 /// Cycle through array of userids with nested arrays of tries
107 if (!empty($form->attempts)) {
108 foreach ($form->attempts as $userid => $tries) {
109 // Modifier IS VERY IMPORTANT! What does it do?
110 // Well, it is for when you delete multiple attempts for the same user.
111 // If you delete try 1 and 3 for a user, then after deleting try 1, try 3 then
112 // becomes try 2 (because try 1 is gone and all tries after try 1 get decremented).
113 // So, the modifier makes sure that the submitted try refers to the current try in the
114 // database - hope this all makes sense :)
115 $modifier = 0;
117 foreach ($tries as $try => $junk) {
118 $try -= $modifier;
120 /// Clean up the timer table by removing using the order - this is silly, it should be linked to specific attempt (skodak)
121 $params = array ("userid" => $userid, "lessonid" => $lesson->id);
122 $timers = $DB->get_records_sql("SELECT id FROM {lesson_timer}
123 WHERE userid = :userid AND lessonid = :lessonid
124 ORDER BY starttime", $params, $try, 1);
125 if ($timers) {
126 $timer = reset($timers);
127 $DB->delete_records('lesson_timer', array('id' => $timer->id));
130 /// Remove the grade from the grades and high_scores tables - this is silly, it should be linked to specific attempt (skodak)
131 $grades = $DB->get_records_sql("SELECT id FROM {lesson_grades}
132 WHERE userid = :userid AND lessonid = :lessonid
133 ORDER BY completed", $params, $try, 1);
135 if ($grades) {
136 $grade = reset($grades);
137 $DB->delete_records('lesson_grades', array('id' => $grade->id));
138 $DB->delete_records('lesson_high_scores', array('gradeid' => $grade->id, 'lessonid' => $lesson->id, 'userid' => $userid));
141 /// Remove attempts and update the retry number
142 $DB->delete_records('lesson_attempts', array('userid' => $userid, 'lessonid' => $lesson->id, 'retry' => $try));
143 $DB->execute("UPDATE {lesson_attempts} SET retry = retry - 1 WHERE userid = ? AND lessonid = ? AND retry > ?", array($userid, $lesson->id, $try));
145 /// Remove seen branches and update the retry number
146 $DB->delete_records('lesson_branch', array('userid' => $userid, 'lessonid' => $lesson->id, 'retry' => $try));
147 $DB->execute("UPDATE {lesson_branch} SET retry = retry - 1 WHERE userid = ? AND lessonid = ? AND retry > ?", array($userid, $lesson->id, $try));
149 /// update central gradebook
150 lesson_update_grades($lesson, $userid);
152 $modifier++;
157 redirect(new moodle_url($PAGE->url, array('action'=>'reportoverview')));
159 } else if ($action === 'reportoverview') {
160 /**************************************************************************
161 this action is for default view and overview view
162 **************************************************************************/
163 echo $lessonoutput->header($lesson, $cm, $action);
165 $course_context = get_context_instance(CONTEXT_COURSE, $course->id);
166 if (has_capability('gradereport/grader:view', $course_context) && has_capability('moodle/grade:viewall', $course_context)) {
167 $seeallgradeslink = new moodle_url('/grade/report/grader/index.php', array('id'=>$course->id));
168 $seeallgradeslink = html_writer::link($seeallgradeslink, get_string('seeallcoursegrades', 'grades'));
169 echo $OUTPUT->box($seeallgradeslink, 'allcoursegrades');
172 $studentdata = array();
174 // build an array for output
175 foreach ($attempts as $attempt) {
176 // if the user is not in the array or if the retry number is not in the sub array, add the data for that try.
177 if (!array_key_exists($attempt->userid, $studentdata) || !array_key_exists($attempt->retry, $studentdata[$attempt->userid])) {
178 // restore/setup defaults
179 $n = 0;
180 $timestart = 0;
181 $timeend = 0;
182 $usergrade = NULL;
184 // search for the grade record for this try. if not there, the nulls defined above will be used.
185 foreach($grades as $grade) {
186 // check to see if the grade matches the correct user
187 if ($grade->userid == $attempt->userid) {
188 // see if n is = to the retry
189 if ($n == $attempt->retry) {
190 // get grade info
191 $usergrade = round($grade->grade, 2); // round it here so we only have to do it once
192 break;
194 $n++; // if not equal, then increment n
197 $n = 0;
198 // search for the time record for this try. if not there, the nulls defined above will be used.
199 foreach($times as $time) {
200 // check to see if the grade matches the correct user
201 if ($time->userid == $attempt->userid) {
202 // see if n is = to the retry
203 if ($n == $attempt->retry) {
204 // get grade info
205 $timeend = $time->lessontime;
206 $timestart = $time->starttime;
207 break;
209 $n++; // if not equal, then increment n
213 // build up the array.
214 // this array represents each student and all of their tries at the lesson
215 $studentdata[$attempt->userid][$attempt->retry] = array( "timestart" => $timestart,
216 "timeend" => $timeend,
217 "grade" => $usergrade,
218 "try" => $attempt->retry,
219 "userid" => $attempt->userid);
222 // set all the stats variables
223 $numofattempts = 0;
224 $avescore = 0;
225 $avetime = 0;
226 $highscore = NULL;
227 $lowscore = NULL;
228 $hightime = NULL;
229 $lowtime = NULL;
231 $table = new html_table();
233 // set up the table object
234 $table->head = array(get_string('name'), get_string('attempts', 'lesson'), get_string('highscore', 'lesson'));
235 $table->align = array('center', 'left', 'left');
236 $table->wrap = array('nowrap', 'nowrap', 'nowrap');
237 $table->attributes['class'] = 'standardtable generaltable';
238 $table->size = array(null, '70%', null);
240 // print out the $studentdata array
241 // going through each student that has attempted the lesson, so, each student should have something to be displayed
242 foreach ($students as $student) {
243 // check to see if the student has attempts to print out
244 if (array_key_exists($student->id, $studentdata)) {
245 // set/reset some variables
246 $attempts = array();
247 // gather the data for each user attempt
248 $bestgrade = 0;
249 $bestgradefound = false;
250 // $tries holds all the tries/retries a student has done
251 $tries = $studentdata[$student->id];
252 $studentname = "{$student->lastname},&nbsp;$student->firstname";
253 foreach ($tries as $try) {
254 // start to build up the checkbox and link
255 if (has_capability('mod/lesson:edit', $context)) {
256 $temp = '<input type="checkbox" id="attempts" name="attempts['.$try['userid'].']['.$try['try'].']" /> ';
257 } else {
258 $temp = '';
261 $temp .= "<a href=\"report.php?id=$cm->id&amp;action=reportdetail&amp;userid=".$try['userid'].'&amp;try='.$try['try'].'">';
262 if ($try["grade"] !== NULL) { // if NULL then not done yet
263 // this is what the link does when the user has completed the try
264 $timetotake = $try["timeend"] - $try["timestart"];
266 $temp .= $try["grade"]."%";
267 $bestgradefound = true;
268 if ($try["grade"] > $bestgrade) {
269 $bestgrade = $try["grade"];
271 $temp .= "&nbsp;".userdate($try["timestart"]);
272 $temp .= ",&nbsp;(".format_time($timetotake).")</a>";
273 } else {
274 // this is what the link does/looks like when the user has not completed the try
275 $temp .= get_string("notcompleted", "lesson");
276 $temp .= "&nbsp;".userdate($try["timestart"])."</a>";
277 $timetotake = NULL;
279 // build up the attempts array
280 $attempts[] = $temp;
282 // run these lines for the stats only if the user finnished the lesson
283 if ($try["grade"] !== NULL) {
284 $numofattempts++;
285 $avescore += $try["grade"];
286 $avetime += $timetotake;
287 if ($try["grade"] > $highscore || $highscore == NULL) {
288 $highscore = $try["grade"];
290 if ($try["grade"] < $lowscore || $lowscore == NULL) {
291 $lowscore = $try["grade"];
293 if ($timetotake > $hightime || $hightime == NULL) {
294 $hightime = $timetotake;
296 if ($timetotake < $lowtime || $lowtime == NULL) {
297 $lowtime = $timetotake;
301 // get line breaks in after each attempt
302 $attempts = implode("<br />\n", $attempts);
303 // add it to the table data[] object
304 $table->data[] = array($studentname, $attempts, $bestgrade."%");
307 // print it all out !
308 if (has_capability('mod/lesson:edit', $context)) {
309 echo "<form id=\"theform\" method=\"post\" action=\"report.php\">\n
310 <input type=\"hidden\" name=\"sesskey\" value=\"".sesskey()."\" />\n
311 <input type=\"hidden\" name=\"id\" value=\"$cm->id\" />\n";
313 echo html_writer::table($table);
314 if (has_capability('mod/lesson:edit', $context)) {
315 $checklinks = '<a href="javascript: checkall();">'.get_string('selectall').'</a> / ';
316 $checklinks .= '<a href="javascript: checknone();">'.get_string('deselectall').'</a>';
317 $checklinks .= html_writer::select(array('delete' => get_string('deleteselected')), 'action', 0, array(''=>'choosedots'), array('id'=>'actionid'));
318 $PAGE->requires->js_init_call('M.util.init_select_autosubmit', array('theform', 'actionid', ''));
319 echo $OUTPUT->box($checklinks, 'center');
320 echo '</form>';
323 // some stat calculations
324 if ($numofattempts == 0) {
325 $avescore = get_string("notcompleted", "lesson");
326 } else {
327 $avescore = format_float($avescore/$numofattempts, 2);
329 if ($avetime == NULL) {
330 $avetime = get_string("notcompleted", "lesson");
331 } else {
332 $avetime = format_float($avetime/$numofattempts, 0);
333 $avetime = format_time($avetime);
335 if ($hightime == NULL) {
336 $hightime = get_string("notcompleted", "lesson");
337 } else {
338 $hightime = format_time($hightime);
340 if ($lowtime == NULL) {
341 $lowtime = get_string("notcompleted", "lesson");
342 } else {
343 $lowtime = format_time($lowtime);
345 if ($highscore == NULL) {
346 $highscore = get_string("notcompleted", "lesson");
348 if ($lowscore == NULL) {
349 $lowscore = get_string("notcompleted", "lesson");
352 // output the stats
353 echo $OUTPUT->heading(get_string('lessonstats', 'lesson'));
354 $stattable = new html_table();
355 $stattable->head = array(get_string('averagescore', 'lesson'), get_string('averagetime', 'lesson'),
356 get_string('highscore', 'lesson'), get_string('lowscore', 'lesson'),
357 get_string('hightime', 'lesson'), get_string('lowtime', 'lesson'));
358 $stattable->align = array('center', 'center', 'center', 'center', 'center', 'center');
359 $stattable->wrap = array('nowrap', 'nowrap', 'nowrap', 'nowrap', 'nowrap', 'nowrap');
360 $stattable->attributes['class'] = 'standardtable generaltable';
361 $stattable->data[] = array($avescore.'%', $avetime, $highscore.'%', $lowscore.'%', $hightime, $lowtime);
363 echo html_writer::table($stattable);
364 } else if ($action === 'reportdetail') {
365 /**************************************************************************
366 this action is for a student detailed view and for the general detailed view
368 General flow of this section of the code
369 1. Generate a object which holds values for the statistics for each question/answer
370 2. Cycle through all the pages to create a object. Foreach page, see if the student actually answered
371 the page. Then process the page appropriatly. Display all info about the question,
372 Highlight correct answers, show how the user answered the question, and display statistics
373 about each page
374 3. Print out info about the try (if needed)
375 4. Print out the object which contains all the try info
377 **************************************************************************/
378 echo $lessonoutput->header($lesson, $cm, $action);
380 $course_context = get_context_instance(CONTEXT_COURSE, $course->id);
381 if (has_capability('gradereport/grader:view', $course_context) && has_capability('moodle/grade:viewall', $course_context)) {
382 $seeallgradeslink = new moodle_url('/grade/report/grader/index.php', array('id'=>$course->id));
383 $seeallgradeslink = html_writer::link($seeallgradeslink, get_string('seeallcoursegrades', 'grades'));
384 echo $OUTPUT->box($seeallgradeslink, 'allcoursegrades');
387 $formattextdefoptions = new stdClass;
388 $formattextdefoptions->para = false; //I'll use it widely in this page
389 $formattextdefoptions->overflowdiv = true;
391 $userid = optional_param('userid', NULL, PARAM_INT); // if empty, then will display the general detailed view
392 $try = optional_param('try', NULL, PARAM_INT);
394 $lessonpages = $lesson->load_all_pages();
395 foreach ($lessonpages as $lessonpage) {
396 if ($lessonpage->prevpageid == 0) {
397 $pageid = $lessonpage->id;
401 // now gather the stats into an object
402 $firstpageid = $pageid;
403 $pagestats = array();
404 while ($pageid != 0) { // EOL
405 $page = $lessonpages[$pageid];
406 $params = array ("lessonid" => $lesson->id, "pageid" => $page->id);
407 if ($allanswers = $DB->get_records_select("lesson_attempts", "lessonid = :lessonid AND pageid = :pageid", $params, "timeseen")) {
408 // get them ready for processing
409 $orderedanswers = array();
410 foreach ($allanswers as $singleanswer) {
411 // ordering them like this, will help to find the single attempt record that we want to keep.
412 $orderedanswers[$singleanswer->userid][$singleanswer->retry][] = $singleanswer;
414 // this is foreach user and for each try for that user, keep one attempt record
415 foreach ($orderedanswers as $orderedanswer) {
416 foreach($orderedanswer as $tries) {
417 $page->stats($pagestats, $tries);
420 } else {
421 // no one answered yet...
423 //unset($orderedanswers); initialized above now
424 $pageid = $page->nextpageid;
427 $manager = lesson_page_type_manager::get($lesson);
428 $qtypes = $manager->get_page_type_strings();
430 $answerpages = array();
431 $answerpage = "";
432 $pageid = $firstpageid;
433 // cycle through all the pages
434 // foreach page, add to the $answerpages[] array all the data that is needed
435 // from the question, the users attempt, and the statistics
436 // grayout pages that the user did not answer and Branch, end of branch, cluster
437 // and end of cluster pages
438 while ($pageid != 0) { // EOL
439 $page = $lessonpages[$pageid];
440 $answerpage = new stdClass;
441 $data ='';
443 $answerdata = new stdClass;
444 // Set some defaults for the answer data.
445 $answerdata->score = NULL;
446 $answerdata->response = NULL;
447 $answerdata->responseformat = FORMAT_PLAIN;
449 $answerpage->title = format_string($page->title);
451 $options = new stdClass;
452 $options->noclean = true;
453 $options->overflowdiv = true;
454 $answerpage->contents = format_text($page->contents, $page->contentsformat, $options);
456 $answerpage->qtype = $qtypes[$page->qtype].$page->option_description_string();
457 $answerpage->grayout = $page->grayout;
458 $answerpage->context = $context;
460 if (empty($userid)) {
461 // there is no userid, so set these vars and display stats.
462 $answerpage->grayout = 0;
463 $useranswer = NULL;
464 } elseif ($useranswers = $DB->get_records("lesson_attempts",array("lessonid"=>$lesson->id, "userid"=>$userid, "retry"=>$try,"pageid"=>$page->id), "timeseen")) {
465 // get the user's answer for this page
466 // need to find the right one
467 $i = 0;
468 foreach ($useranswers as $userattempt) {
469 $useranswer = $userattempt;
470 $i++;
471 if ($lesson->maxattempts == $i) {
472 break; // reached maxattempts, break out
475 } else {
476 // user did not answer this page, gray it out and set some nulls
477 $answerpage->grayout = 1;
478 $useranswer = NULL;
480 $i = 0;
481 $n = 0;
482 $answerpages[] = $page->report_answers(clone($answerpage), clone($answerdata), $useranswer, $pagestats, $i, $n);
483 $pageid = $page->nextpageid;
486 /// actually start printing something
487 $table = new html_table();
488 $table->wrap = array();
489 $table->width = "60%";
490 if (!empty($userid)) {
491 // if looking at a students try, print out some basic stats at the top
493 // print out users name
494 //$headingobject->lastname = $students[$userid]->lastname;
495 //$headingobject->firstname = $students[$userid]->firstname;
496 //$headingobject->attempt = $try + 1;
497 //print_heading(get_string("studentattemptlesson", "lesson", $headingobject));
498 echo $OUTPUT->heading(get_string('attempt', 'lesson', $try+1));
500 $table->head = array();
501 $table->align = array('right', 'left');
502 $table->attributes['class'] = 'compacttable generaltable';
504 $params = array("lessonid"=>$lesson->id, "userid"=>$userid);
505 if (!$grades = $DB->get_records_select("lesson_grades", "lessonid = :lessonid and userid = :userid", $params, "completed", "*", $try, 1)) {
506 $grade = -1;
507 $completed = -1;
508 } else {
509 $grade = current($grades);
510 $completed = $grade->completed;
511 $grade = round($grade->grade, 2);
513 if (!$times = $DB->get_records_select("lesson_timer", "lessonid = :lessonid and userid = :userid", $params, "starttime", "*", $try, 1)) {
514 $timetotake = -1;
515 } else {
516 $timetotake = current($times);
517 $timetotake = $timetotake->lessontime - $timetotake->starttime;
520 if ($timetotake == -1 || $completed == -1 || $grade == -1) {
521 $table->align = array("center");
523 $table->data[] = array(get_string("notcompleted", "lesson"));
524 } else {
525 $user = $students[$userid];
527 $gradeinfo = lesson_grade($lesson, $try, $user->id);
529 $table->data[] = array(get_string('name').':', $OUTPUT->user_picture($user, array('courseid'=>$course->id)).fullname($user, true));
530 $table->data[] = array(get_string("timetaken", "lesson").":", format_time($timetotake));
531 $table->data[] = array(get_string("completed", "lesson").":", userdate($completed));
532 $table->data[] = array(get_string('rawgrade', 'lesson').':', $gradeinfo->earned.'/'.$gradeinfo->total);
533 $table->data[] = array(get_string("grade", "lesson").":", $grade."%");
535 echo html_writer::table($table);
537 // Don't want this class for later tables
538 $table->attributes['class'] = '';
542 $table->align = array('left', 'left');
543 $table->size = array('70%', null);
544 $table->attributes['class'] = 'compacttable generaltable';
546 foreach ($answerpages as $page) {
547 unset($table->data);
548 if ($page->grayout) { // set the color of text
549 $fontstart = "<span class=\"dimmed\">";
550 $fontend = "</font>";
551 $fontstart2 = $fontstart;
552 $fontend2 = $fontend;
553 } else {
554 $fontstart = "";
555 $fontend = "";
556 $fontstart2 = "";
557 $fontend2 = "";
560 $table->head = array($fontstart2.$page->qtype.": ".format_string($page->title).$fontend2, $fontstart2.get_string("classstats", "lesson").$fontend2);
561 $table->data[] = array($fontstart.get_string("question", "lesson").": <br />".$fontend.$fontstart2.$page->contents.$fontend2, " ");
562 $table->data[] = array($fontstart.get_string("answer", "lesson").":".$fontend, ' ');
563 // apply the font to each answer
564 if (!empty($page->answerdata)) {
565 foreach ($page->answerdata->answers as $answer){
566 $modified = array();
567 foreach ($answer as $single) {
568 // need to apply a font to each one
569 $modified[] = $fontstart2.$single.$fontend2;
571 $table->data[] = $modified;
573 if (isset($page->answerdata->response)) {
574 $table->data[] = array($fontstart.get_string("response", "lesson").": <br />".$fontend.$fontstart2.format_text($page->answerdata->response,$page->answerdata->responseformat,$formattextdefoptions).$fontend2, " ");
576 $table->data[] = array($page->answerdata->score, " ");
577 } else {
578 $table->data[] = array(get_string('didnotanswerquestion', 'lesson'), " ");
580 echo html_writer::table($table);
582 } else {
583 print_error('unknowaction');
586 /// Finish the page
587 echo $OUTPUT->footer();