Merge branch 'MDL-50169' of https://github.com/sk-unikent/moodle
[moodle.git] / grade / report / history / classes / output / tablelog.php
CommitLineData
2864abd8
AA
1<?php
2// This file is part of Moodle - http://moodle.org/
3//
4// Moodle is free software: you can redistribute it and/or modify
5// it under the terms of the GNU General Public License as published by
6// the Free Software Foundation, either version 3 of the License, or
7// (at your option) any later version.
8//
9// Moodle is distributed in the hope that it will be useful,
10// but WITHOUT ANY WARRANTY; without even the implied warranty of
11// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12// GNU General Public License for more details.
13//
14// You should have received a copy of the GNU General Public License
15// along with Moodle. If not, see <http://www.gnu.org/licenses/>.
16
17/**
18 * Renderable class for gradehistory report.
19 *
20 * @package gradereport_history
21 * @copyright 2014 onwards Ankit Agarwal <ankit.agrr@gmail.com>
22 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
23 */
24
25namespace gradereport_history\output;
26
27defined('MOODLE_INTERNAL') || die;
28
29require_once($CFG->libdir . '/tablelib.php');
30
31/**
32 * Renderable class for gradehistory report.
33 *
34 * @since Moodle 2.8
35 * @package gradereport_history
36 * @copyright 2014 onwards Ankit Agarwal <ankit.agrr@gmail.com>
37 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
38 */
39class tablelog extends \table_sql implements \renderable {
40
41 /**
42 * @var int course id.
43 */
44 protected $courseid;
45
46 /**
47 * @var \context context of the page to be rendered.
48 */
49 protected $context;
50
51 /**
52 * @var \stdClass A list of filters to be applied to the sql query.
53 */
54 protected $filters;
55
56 /**
57 * @var array A list of grade items present in the course.
58 */
59 protected $gradeitems = array();
60
61 /**
62 * @var \course_modinfo|null A list of cm instances in course.
63 */
64 protected $cms;
65
a07ec456
AN
66 /**
67 * @var int The default number of decimal points to use in this course
68 * when a grade item does not itself define the number of decimal points.
69 */
70 protected $defaultdecimalpoints;
71
2864abd8
AA
72 /**
73 * Sets up the table_log parameters.
74 *
75 * @param string $uniqueid unique id of table.
76 * @param \context_course $context Context of the report.
77 * @param \moodle_url $url url of the page where this table would be displayed.
78 * @param array $filters options are:
cf6bc1b1
FM
79 * userids : limit to specific users (default: none)
80 * itemid : limit to specific grade item (default: all)
2864abd8
AA
81 * grader : limit to specific graders (default: all)
82 * datefrom : start of date range
83 * datetill : end of date range
84 * revisedonly : only show revised grades (default: false)
85 * format : page | csv | excel (default: page)
86 * @param string $download Represents download format, pass '' no download at this time.
87 * @param int $page The current page being displayed.
88 * @param int $perpage Number of rules to display per page.
89 */
90 public function __construct($uniqueid, \context_course $context, $url, $filters = array(), $download = '', $page = 0,
91 $perpage = 100) {
a07ec456 92 global $CFG;
2864abd8
AA
93 parent::__construct($uniqueid);
94
95 $this->set_attribute('class', 'gradereport_history generaltable generalbox');
96
97 // Set protected properties.
98 $this->context = $context;
99 $this->courseid = $this->context->instanceid;
100 $this->pagesize = $perpage;
101 $this->page = $page;
102 $this->filters = (object)$filters;
103 $this->gradeitems = \grade_item::fetch_all(array('courseid' => $this->courseid));
104 $this->cms = get_fast_modinfo($this->courseid);
105 $this->useridfield = 'userid';
a07ec456 106 $this->defaultdecimalpoints = grade_get_setting($this->courseid, 'decimalpoints', $CFG->grade_decimalpoints);
2864abd8
AA
107
108 // Define columns in the table.
109 $this->define_table_columns();
110
111 // Define configs.
112 $this->define_table_configs($url);
113
a2bf8897
FM
114 // Set download status.
115 $this->is_downloading($download, get_string('exportfilename', 'gradereport_history'));
2864abd8
AA
116 }
117
118 /**
119 * Define table configs.
120 *
121 * @param \moodle_url $url url of the page where this table would be displayed.
122 */
123 protected function define_table_configs(\moodle_url $url) {
124
125 // Set table url.
126 $urlparams = (array)$this->filters;
127 unset($urlparams['submitbutton']);
128 unset($urlparams['userfullnames']);
129 $url->params($urlparams);
130 $this->define_baseurl($url);
131
132 // Set table configs.
133 $this->collapsible(true);
07f827ac 134 $this->sortable(true, 'timemodified', SORT_DESC);
2864abd8
AA
135 $this->pageable(true);
136 $this->no_sorting('grader');
137 }
138
139 /**
140 * Setup the headers for the html table.
141 */
142 protected function define_table_columns() {
143 $extrafields = get_extra_user_fields($this->context);
144
145 // Define headers and columns.
146 $cols = array(
147 'timemodified' => get_string('datetime', 'gradereport_history'),
148 'fullname' => get_string('name')
149 );
150
151 // Add headers for extra user fields.
152 foreach ($extrafields as $field) {
153 if (get_string_manager()->string_exists($field, 'moodle')) {
154 $cols[$field] = get_string($field);
155 } else {
156 $cols[$field] = $field;
157 }
158 }
159
160 // Add remaining headers.
161 $cols = array_merge($cols, array(
07f827ac 162 'itemname' => get_string('gradeitem', 'grades'),
2864abd8
AA
163 'prevgrade' => get_string('gradeold', 'gradereport_history'),
164 'finalgrade' => get_string('gradenew', 'gradereport_history'),
165 'grader' => get_string('grader', 'gradereport_history'),
166 'source' => get_string('source', 'gradereport_history'),
07f827ac
FM
167 'overridden' => get_string('overridden', 'grades'),
168 'locked' => get_string('locked', 'grades'),
2864abd8
AA
169 'excluded' => get_string('excluded', 'gradereport_history'),
170 'feedback' => get_string('feedbacktext', 'gradereport_history')
171 )
172 );
173
174 $this->define_columns(array_keys($cols));
175 $this->define_headers(array_values($cols));
176 }
177
a07ec456
AN
178 /**
179 * Method to display the final grade.
180 *
181 * @param \stdClass $history an entry of history record.
182 *
183 * @return string HTML to display
184 */
185 public function col_finalgrade(\stdClass $history) {
186 if (!empty($this->gradeitems[$history->itemid])) {
187 $decimalpoints = $this->gradeitems[$history->itemid]->get_decimals();
188 } else {
189 $decimalpoints = $this->defaultdecimalpoints;
190 }
191
192 return format_float($history->finalgrade, $decimalpoints);
193 }
194
195 /**
196 * Method to display the previous grade.
197 *
198 * @param \stdClass $history an entry of history record.
199 *
200 * @return string HTML to display
201 */
202 public function col_prevgrade(\stdClass $history) {
203 if (!empty($this->gradeitems[$history->itemid])) {
204 $decimalpoints = $this->gradeitems[$history->itemid]->get_decimals();
205 } else {
206 $decimalpoints = $this->defaultdecimalpoints;
207 }
208
209 return format_float($history->prevgrade, $decimalpoints);
210 }
211
2864abd8
AA
212 /**
213 * Method to display column timemodifed.
214 *
215 * @param \stdClass $history an entry of history record.
216 *
217 * @return string HTML to display
218 */
219 public function col_timemodified(\stdClass $history) {
9e7a84e5 220 return userdate($history->timemodified);
2864abd8
AA
221 }
222
223 /**
224 * Method to display column itemname.
225 *
226 * @param \stdClass $history an entry of history record.
227 *
228 * @return string HTML to display
229 */
230 public function col_itemname(\stdClass $history) {
231 // Make sure grade item is still present and link it to the module if possible.
232 $itemid = $history->itemid;
233 if (!empty($this->gradeitems[$itemid])) {
234 if ($history->itemtype === 'mod' && !$this->is_downloading()) {
235 if (!empty($this->cms->instances[$history->itemmodule][$history->iteminstance])) {
236 $cm = $this->cms->instances[$history->itemmodule][$history->iteminstance];
237 $url = new \moodle_url('/mod/' . $history->itemmodule . '/view.php', array('id' => $cm->id));
238 return \html_writer::link($url, $this->gradeitems[$itemid]->get_name());
239 }
240 }
241 return $this->gradeitems[$itemid]->get_name();
242 }
243 return get_string('deleteditemid', 'gradereport_history', $history->itemid);
244 }
245
246 /**
247 * Method to display column grader.
248 *
249 * @param \stdClass $history an entry of history record.
250 *
251 * @return string HTML to display
252 */
253 public function col_grader(\stdClass $history) {
655e377c
FM
254 if (empty($history->usermodified)) {
255 // Not every row has a valid usermodified.
256 return '';
257 }
258
2864abd8
AA
259 $grader = new \stdClass();
260 $grader = username_load_fields_from_object($grader, $history, 'grader');
261 $name = fullname($grader);
262
263 if ($this->download) {
264 return $name;
265 }
266
267 $userid = $history->usermodified;
268 $profileurl = new \moodle_url('/user/view.php', array('id' => $userid, 'course' => $this->courseid));
269
270 return \html_writer::link($profileurl, $name);
271 }
272
273 /**
274 * Method to display column overridden.
275 *
276 * @param \stdClass $history an entry of history record.
277 *
278 * @return string HTML to display
279 */
280 public function col_overridden(\stdClass $history) {
07f827ac 281 return $history->overridden ? get_string('yes') : get_string('no');
2864abd8
AA
282 }
283
284 /**
285 * Method to display column locked.
286 *
287 * @param \stdClass $history an entry of history record.
288 *
289 * @return string HTML to display
290 */
291 public function col_locked(\stdClass $history) {
07f827ac 292 return $history->locked ? get_string('yes') : get_string('no');
2864abd8
AA
293 }
294
295 /**
296 * Method to display column excluded.
297 *
298 * @param \stdClass $history an entry of history record.
299 *
300 * @return string HTML to display
301 */
302 public function col_excluded(\stdClass $history) {
07f827ac 303 return $history->excluded ? get_string('yes') : get_string('no');
2864abd8
AA
304 }
305
306 /**
307 * Method to display column feedback.
308 *
309 * @param \stdClass $history an entry of history record.
310 *
311 * @return string HTML to display
312 */
313 public function col_feedback(\stdClass $history) {
314 if ($this->is_downloading()) {
315 return $history->feedback;
316 } else {
317 return format_text($history->feedback, $history->feedbackformat, array('context' => $this->context));
318 }
319 }
320
321 /**
322 * Builds the sql and param list needed, based on the user selected filters.
323 *
324 * @return array containing sql to use and an array of params.
325 */
326 protected function get_filters_sql_and_params() {
327 global $DB;
328
329 $coursecontext = $this->context;
330 $filter = 'gi.courseid = :courseid';
331 $params = array(
332 'courseid' => $coursecontext->instanceid,
333 );
334
335 if (!empty($this->filters->itemid)) {
336 $filter .= ' AND ggh.itemid = :itemid';
337 $params['itemid'] = $this->filters->itemid;
338 }
339 if (!empty($this->filters->userids)) {
340 $list = explode(',', $this->filters->userids);
341 list($insql, $plist) = $DB->get_in_or_equal($list, SQL_PARAMS_NAMED);
342 $filter .= " AND ggh.userid $insql";
343 $params += $plist;
344 }
345 if (!empty($this->filters->datefrom)) {
346 $filter .= " AND ggh.timemodified >= :datefrom";
347 $params += array('datefrom' => $this->filters->datefrom);
348 }
349 if (!empty($this->filters->datetill)) {
350 $filter .= " AND ggh.timemodified <= :datetill";
351 $params += array('datetill' => $this->filters->datetill);
352 }
353 if (!empty($this->filters->grader)) {
354 $filter .= " AND ggh.usermodified = :grader";
355 $params += array('grader' => $this->filters->grader);
356 }
4870363d 357
2864abd8
AA
358 return array($filter, $params);
359 }
360
361 /**
362 * Builds the complete sql with all the joins to get the grade history data.
363 *
364 * @param bool $count setting this to true, returns an sql to get count only instead of the complete data records.
365 *
366 * @return array containing sql to use and an array of params.
367 */
368 protected function get_sql_and_params($count = false) {
4870363d 369 $fields = 'ggh.id, ggh.timemodified, ggh.itemid, ggh.userid, ggh.finalgrade, ggh.usermodified,
2864abd8
AA
370 ggh.source, ggh.overridden, ggh.locked, ggh.excluded, ggh.feedback, ggh.feedbackformat,
371 gi.itemtype, gi.itemmodule, gi.iteminstance, gi.itemnumber, ';
372
373 // Add extra user fields that we need for the graded user.
374 $extrafields = get_extra_user_fields($this->context);
375 foreach ($extrafields as $field) {
376 $fields .= 'u.' . $field . ', ';
377 }
378 $gradeduserfields = get_all_user_name_fields(true, 'u');
379 $fields .= $gradeduserfields . ', ';
380 $groupby = $fields;
381
382 // Add extra user fields that we need for the grader user.
383 $fields .= get_all_user_name_fields(true, 'ug', '', 'grader');
384 $groupby .= get_all_user_name_fields(true, 'ug');
385
4870363d
FM
386 // Filtering on revised grades only.
387 $revisedonly = !empty($this->filters->revisedonly);
388
389 if ($count && !$revisedonly) {
390 // We can only directly use count when not using the filter revised only.
391 $select = "COUNT(1)";
2864abd8 392 } else {
4870363d
FM
393 // Fetching the previous grade. We use MAX() to ensure that we only get one result if
394 // more than one histories happened at the same second.
395 $prevgrade = "SELECT MAX(finalgrade)
396 FROM {grade_grades_history} h
397 WHERE h.itemid = ggh.itemid
398 AND h.userid = ggh.userid
399 AND h.timemodified < ggh.timemodified
400 AND NOT EXISTS (
401 SELECT 1
402 FROM {grade_grades_history} h2
403 WHERE h2.itemid = ggh.itemid
404 AND h2.userid = ggh.userid
405 AND h2.timemodified < ggh.timemodified
406 AND h.timemodified < h2.timemodified)";
407
408 $select = "$fields, ($prevgrade) AS prevgrade,
409 CASE WHEN gi.itemname IS NULL THEN gi.itemtype ELSE gi.itemname END AS itemname";
2864abd8
AA
410 }
411
412 list($where, $params) = $this->get_filters_sql_and_params();
413
4870363d
FM
414 $sql = "SELECT $select
415 FROM {grade_grades_history} ggh
6e60e995 416 JOIN {grade_items} gi ON gi.id = ggh.itemid
4870363d 417 JOIN {user} u ON u.id = ggh.userid
655e377c 418 LEFT JOIN {user} ug ON ug.id = ggh.usermodified
4870363d
FM
419 WHERE $where";
420
421 // As prevgrade is a dynamic field, we need to wrap the query. This is the only filtering
422 // that should be defined outside the method self::get_filters_sql_and_params().
423 if ($revisedonly) {
424 $allorcount = $count ? 'COUNT(1)' : '*';
425 $sql = "SELECT $allorcount FROM ($sql) pg
426 WHERE pg.finalgrade != pg.prevgrade
427 OR (pg.prevgrade IS NULL AND pg.finalgrade IS NOT NULL)
428 OR (pg.prevgrade IS NOT NULL AND pg.finalgrade IS NULL)";
429 }
2864abd8
AA
430
431 // Add order by if needed.
a5e706e8
FM
432 if (!$count && $sqlsort = $this->get_sql_sort()) {
433 $sql .= " ORDER BY " . $sqlsort;
2864abd8
AA
434 }
435
2864abd8
AA
436 return array($sql, $params);
437 }
438
a5e706e8
FM
439 /**
440 * Get the SQL fragment to sort by.
441 *
442 * This is overridden to sort by timemodified and ID by default. Many items happen at the same time
443 * and a second sorting by ID is valuable to distinguish the order in which the history happened.
444 *
445 * @return string SQL fragment.
446 */
447 public function get_sql_sort() {
448 $columns = $this->get_sort_columns();
449 if (count($columns) == 1 && isset($columns['timemodified']) && $columns['timemodified'] == SORT_DESC) {
450 // Add the 'id' column when we are using the default sorting.
451 $columns['id'] = SORT_DESC;
452 return self::construct_order_by($columns);
453 }
454 return parent::get_sql_sort();
455 }
456
2864abd8
AA
457 /**
458 * Query the reader. Store results in the object for use by build_table.
459 *
460 * @param int $pagesize size of page for paginated displayed table.
461 * @param bool $useinitialsbar do you want to use the initials bar.
462 */
463 public function query_db($pagesize, $useinitialsbar = true) {
464 global $DB;
465
466 list($countsql, $countparams) = $this->get_sql_and_params(true);
467 list($sql, $params) = $this->get_sql_and_params();
468 $total = $DB->count_records_sql($countsql, $countparams);
469 $this->pagesize($pagesize, $total);
8e63ce4e
EM
470 if ($this->is_downloading()) {
471 $histories = $DB->get_records_sql($sql, $params);
472 } else {
473 $histories = $DB->get_records_sql($sql, $params, $this->pagesize * $this->page, $this->pagesize);
474 }
2864abd8
AA
475 foreach ($histories as $history) {
476 $this->rawdata[] = $history;
477 }
478 // Set initial bars.
479 if ($useinitialsbar) {
480 $this->initialbars($total > $pagesize);
481 }
482 }
483
484 /**
485 * Returns a list of selected users.
486 *
487 * @return array returns an array in the format $userid => $userid
488 */
489 public function get_selected_users() {
490 global $DB;
491 $idlist = array();
492 if (!empty($this->filters->userids)) {
493
494 $idlist = explode(',', $this->filters->userids);
495 list($where, $params) = $DB->get_in_or_equal($idlist);
496 return $DB->get_records_select('user', "id $where", $params);
497
498 }
499 return $idlist;
500 }
501
502}