MDL-66076 forumreport_summary: Added missing SQL group by fields
[moodle.git] / mod / forum / report / summary / classes / summary_table.php
CommitLineData
6388b657
MH
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 * The class for displaying the forum report table.
19 *
20 * @package forumreport_summary
21 * @copyright 2019 Michael Hawkins <michaelh@moodle.com>
22 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
23 */
24
25namespace forumreport_summary;
26defined('MOODLE_INTERNAL') || die();
27
28require_once($CFG->libdir . '/tablelib.php');
29
30use coding_exception;
31use table_sql;
32
33/**
34 * The class for displaying the forum report table.
35 *
36 * @copyright 2019 Michael Hawkins <michaelh@moodle.com>
37 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
38 */
39class summary_table extends table_sql {
40
41 /** Forum filter type */
42 const FILTER_FORUM = 1;
43
b29de56d
MH
44 /** Groups filter type */
45 const FILTER_GROUPS = 2;
46
235a6db7
DM
47 /** Table to store summary data extracted from the log table */
48 const LOG_SUMMARY_TEMP_TABLE = 'forum_report_summary_counts';
49
6388b657
MH
50 /** @var \stdClass The various SQL segments that will be combined to form queries to fetch various information. */
51 public $sql;
52
53 /** @var int The number of rows to be displayed per page. */
54 protected $perpage = 25;
55
b29de56d
MH
56 /** @var \stdClass The course module object of the forum being reported on. */
57 protected $cm;
58
6388b657
MH
59 /**
60 * @var int The user ID if only one user's summary will be generated.
61 * This will apply to users without permission to view others' summaries.
62 */
63 protected $userid;
64
235a6db7
DM
65 /**
66 * @var \core\log\sql_reader|null
67 */
68 protected $logreader = null;
69
70 /**
71 * @var \context|null
72 */
73 protected $context = null;
74
6388b657
MH
75 /**
76 * Forum report table constructor.
77 *
78 * @param int $courseid The ID of the course the forum(s) exist within.
b29de56d 79 * @param array $filters Report filters in the format 'type' => [values].
0942e58c 80 * @param bool $bulkoperations Is the user allowed to perform bulk operations?
6388b657 81 */
0942e58c
SR
82 public function __construct(int $courseid, array $filters, bool $bulkoperations) {
83 global $USER, $OUTPUT;
6388b657 84
b29de56d
MH
85 $forumid = $filters['forums'][0];
86
6388b657
MH
87 parent::__construct("summaryreport_{$courseid}_{$forumid}");
88
b29de56d 89 $this->cm = get_coursemodule_from_instance('forum', $forumid, $courseid);
235a6db7 90 $this->context = \context_module::instance($this->cm->id);
6388b657
MH
91
92 // Only show their own summary unless they have permission to view all.
235a6db7 93 if (!has_capability('forumreport/summary:viewall', $this->context)) {
6388b657
MH
94 $this->userid = $USER->id;
95 }
96
0942e58c
SR
97 $columnheaders = [];
98
99 if ($bulkoperations) {
100 $mastercheckbox = new \core\output\checkbox_toggleall('summaryreport-table', true, [
101 'id' => 'select-all-users',
102 'name' => 'select-all-users',
103 'label' => get_string('selectall'),
104 'labelclasses' => 'sr-only',
105 'classes' => 'm-1',
106 'checked' => false
107 ]);
108 $columnheaders['select'] = $OUTPUT->render($mastercheckbox);
109 }
110
111 $columnheaders += [
6388b657
MH
112 'fullname' => get_string('fullnameuser'),
113 'postcount' => get_string('postcount', 'forumreport_summary'),
114 'replycount' => get_string('replycount', 'forumreport_summary'),
d0e125cf 115 'attachmentcount' => get_string('attachmentcount', 'forumreport_summary'),
6388b657
MH
116 ];
117
b9669dd3
MH
118 $this->logreader = $this->get_internal_log_reader();
119 if ($this->logreader) {
120 $columnheaders['viewcount'] = get_string('viewcount', 'forumreport_summary');
235a6db7
DM
121 }
122
123 $columnheaders['earliestpost'] = get_string('earliestpost', 'forumreport_summary');
124 $columnheaders['latestpost'] = get_string('latestpost', 'forumreport_summary');
125
6388b657
MH
126 $this->define_columns(array_keys($columnheaders));
127 $this->define_headers(array_values($columnheaders));
128
129 // Define configs.
130 $this->define_table_configs();
131
132 // Define the basic SQL data and object format.
133 $this->define_base_sql();
134
b29de56d
MH
135 // Apply relevant filters.
136 $this->apply_filters($filters);
6388b657
MH
137 }
138
139 /**
140 * Provides the string name of each filter type.
141 *
142 * @param int $filtertype Type of filter
143 * @return string Name of the filter
144 */
145 public function get_filter_name(int $filtertype): string {
146 $filternames = [
147 self::FILTER_FORUM => 'Forum',
b29de56d 148 self::FILTER_GROUPS => 'Groups',
6388b657
MH
149 ];
150
151 return $filternames[$filtertype];
152 }
153
0942e58c
SR
154 /**
155 * Generate the select column.
156 *
157 * @param \stdClass $data
158 * @return string
159 */
160 public function col_select($data) {
161 global $OUTPUT;
162
163 $checkbox = new \core\output\checkbox_toggleall('summaryreport-table', false, [
164 'classes' => 'usercheckbox m-1',
165 'id' => 'user' . $data->userid,
166 'name' => 'user' . $data->userid,
167 'checked' => false,
168 'label' => get_string('selectitem', 'moodle', fullname($data)),
169 'labelclasses' => 'accesshide',
170 ]);
171
172 return $OUTPUT->render($checkbox);
173 }
174
6388b657
MH
175 /**
176 * Generate the fullname column.
177 *
178 * @param \stdClass $data The row data.
179 * @return string User's full name.
180 */
181 public function col_fullname($data): string {
806d6343 182 global $OUTPUT;
6388b657 183
806d6343 184 return $OUTPUT->user_picture($data, array('size' => 35, 'courseid' => $this->cm->course, 'includefullname' => true));
6388b657
MH
185 }
186
187 /**
188 * Generate the postcount column.
189 *
190 * @param \stdClass $data The row data.
191 * @return int number of discussion posts made by user.
192 */
193 public function col_postcount(\stdClass $data): int {
194 return $data->postcount;
195 }
196
197 /**
198 * Generate the replycount column.
199 *
200 * @param \stdClass $data The row data.
201 * @return int number of replies made by user.
202 */
203 public function col_replycount(\stdClass $data): int {
204 return $data->replycount;
205 }
206
d0e125cf
MH
207 /**
208 * Generate the attachmentcount column.
209 *
210 * @param \stdClass $data The row data.
211 * @return int number of files attached to posts by user.
212 */
213 public function col_attachmentcount(\stdClass $data): int {
214 return $data->attachmentcount;
215 }
216
382168a0
MH
217 /**
218 * Generate the earliestpost column.
219 *
220 * @param \stdClass $data The row data.
221 * @return string Timestamp of user's earliest post, or a dash if no posts exist.
222 */
223 public function col_earliestpost(\stdClass $data): string {
224 global $USER;
225
226 return empty($data->earliestpost) ? '-' : userdate($data->earliestpost, "", \core_date::get_user_timezone($USER));
227 }
228
229 /**
230 * Generate the latestpost column.
231 *
232 * @param \stdClass $data The row data.
233 * @return string Timestamp of user's most recent post, or a dash if no posts exist.
234 */
235 public function col_latestpost(\stdClass $data): string {
236 global $USER;
237
238 return empty($data->latestpost) ? '-' : userdate($data->latestpost, "", \core_date::get_user_timezone($USER));
239 }
240
6388b657
MH
241 /**
242 * Override the default implementation to set a decent heading level.
243 *
244 * @return void.
245 */
246 public function print_nothing_to_display(): void {
247 global $OUTPUT;
248
249 echo $OUTPUT->heading(get_string('nothingtodisplay'), 4);
250 }
251
252 /**
253 * Query the db. Store results in the table object for use by build_table.
254 *
255 * @param int $pagesize Size of page for paginated displayed table.
256 * @param bool $useinitialsbar Overridden but unused.
257 * @return void
258 */
259 public function query_db($pagesize, $useinitialsbar = false): void {
260 global $DB;
261
262 // Set up pagination if not downloading the whole report.
263 if (!$this->is_downloading()) {
264 $totalsql = $this->get_full_sql(false);
265
266 // Set up pagination.
267 $totalrows = $DB->count_records_sql($totalsql, $this->sql->params);
268 $this->pagesize($pagesize, $totalrows);
269 }
270
271 // Fetch the data.
272 $sql = $this->get_full_sql();
273
274 // Only paginate when not downloading.
275 if (!$this->is_downloading()) {
276 $this->rawdata = $DB->get_records_sql($sql, $this->sql->params, $this->get_page_start(), $this->get_page_size());
277 } else {
278 $this->rawdata = $DB->get_records_sql($sql, $this->sql->params);
279 }
280 }
281
282 /**
283 * Adds the relevant SQL to apply a filter to the report.
284 *
285 * @param int $filtertype Filter type as defined by class constants.
286 * @param array $values Optional array of values passed into the filter type.
287 * @return void
288 * @throws coding_exception
289 */
290 public function add_filter(int $filtertype, array $values = []): void {
b29de56d
MH
291 global $DB;
292
6388b657
MH
293 $paramcounterror = false;
294
295 switch($filtertype) {
296 case self::FILTER_FORUM:
297 // Requires exactly one forum ID.
298 if (count($values) != 1) {
299 $paramcounterror = true;
300 } else {
301 // No select fields required - displayed in title.
302 // No extra joins required, forum is already joined.
303 $this->sql->filterwhere .= ' AND f.id = :forumid';
304 $this->sql->params['forumid'] = $values[0];
305 }
306
307 break;
308
b29de56d 309 case self::FILTER_GROUPS:
b29de56d 310 // Skip adding filter if not applied, or all options are selected.
9eebd58a 311 if ($this->is_filtered_by_groups($values)) {
b29de56d
MH
312 // Include users without groups if that option (-1) is selected.
313 $nonekey = array_search(-1, $values, true);
314
315 // Users within selected groups or not in any groups are included.
316 if ($nonekey !== false && count($values) > 1) {
317 unset($values[$nonekey]);
318 list($groupidin, $groupidparams) = $DB->get_in_or_equal($values, SQL_PARAMS_NAMED, 'groupid');
319
320 // No select fields required.
321 // No joins required (handled by where to prevent data duplication).
322 $this->sql->filterwhere .= "
323 AND (u.id =
324 (SELECT gm.userid
325 FROM {groups_members} gm
326 WHERE gm.userid = u.id
327 AND gm.groupid {$groupidin}
328 GROUP BY gm.userid
329 LIMIT 1)
330 OR
331 (SELECT nogm.userid
332 FROM mdl_groups_members nogm
333 WHERE nogm.userid = u.id
334 GROUP BY nogm.userid
335 LIMIT 1)
336 IS NULL)";
337 $this->sql->params += $groupidparams;
338
339 } else if ($nonekey !== false) {
340 // Only users within no groups are included.
341 unset($values[$nonekey]);
342
343 // No select fields required.
344 $this->sql->filterfromjoins .= " LEFT JOIN {groups_members} nogm ON nogm.userid = u.id";
345 $this->sql->filterwhere .= " AND nogm.id IS NULL";
346
347 } else if (!empty($values)) {
348 // Only users within selected groups are included.
349 list($groupidin, $groupidparams) = $DB->get_in_or_equal($values, SQL_PARAMS_NAMED, 'groupid');
350
351 // No select fields required.
352 // No joins required (handled by where to prevent data duplication).
353 $this->sql->filterwhere .= "
354 AND u.id = (
355 SELECT gm.userid
356 FROM {groups_members} gm
357 WHERE gm.userid = u.id
358 AND gm.groupid {$groupidin}
359 GROUP BY gm.userid
360 LIMIT 1)";
361 $this->sql->params += $groupidparams;
362 }
363 }
364
365 break;
366
6388b657
MH
367 default:
368 throw new coding_exception("Report filter type '{$filtertype}' not found.");
369 break;
370 }
371
372 if ($paramcounterror) {
373 $filtername = $this->get_filter_name($filtertype);
374 throw new coding_exception("An invalid number of values have been passed for the '{$filtername}' filter.");
375 }
376 }
377
378 /**
379 * Define various table config options.
380 *
381 * @return void.
382 */
383 protected function define_table_configs(): void {
384 $this->collapsible(false);
385 $this->sortable(true, 'firstname', SORT_ASC);
386 $this->pageable(true);
387 $this->no_sorting('select');
d0e125cf 388 $this->set_attribute('id', 'forumreport_summary_table');
6388b657
MH
389 }
390
391 /**
392 * Define the object to store all for the table SQL and initialises the base SQL required.
393 *
394 * @return void.
395 */
396 protected function define_base_sql(): void {
397 $this->sql = new \stdClass();
398
806d6343
SR
399 $userfields = get_extra_user_fields($this->context);
400 $userfieldssql = \user_picture::fields('u', $userfields);
401
6388b657
MH
402 // Define base SQL query format.
403 // Ignores private replies as they are not visible to all participants.
404 $this->sql->basefields = ' ue.userid AS userid,
806d6343
SR
405 e.courseid AS courseid,
406 f.id as forumid,
407 SUM(CASE WHEN p.parent = 0 THEN 1 ELSE 0 END) AS postcount,
408 SUM(CASE WHEN p.parent != 0 THEN 1 ELSE 0 END) AS replycount,
409 ' . $userfieldssql . ',
410 SUM(CASE WHEN att.attcount IS NULL THEN 0 ELSE att.attcount END) AS attachmentcount,
411 MIN(p.created) AS earliestpost,
412 MAX(p.created) AS latestpost';
6388b657
MH
413
414 $this->sql->basefromjoins = ' {enrol} e
415 JOIN {user_enrolments} ue ON ue.enrolid = e.id
416 JOIN {user} u ON u.id = ue.userid
417 JOIN {forum} f ON f.course = e.courseid
418 JOIN {forum_discussions} d ON d.forum = f.id
419 LEFT JOIN {forum_posts} p ON p.discussion = d.id
420 AND p.userid = ue.userid
d0e125cf
MH
421 AND p.privatereplyto = 0
422 LEFT JOIN (
423 SELECT COUNT(fi.id) AS attcount, fi.itemid AS postid, fi.userid
424 FROM {files} fi
425 WHERE fi.component = :component
426 AND fi.filesize > 0
427 GROUP BY fi.itemid, fi.userid
428 ) att ON att.postid = p.id
429 AND att.userid = ue.userid';
6388b657
MH
430
431 $this->sql->basewhere = 'e.courseid = :courseid';
432
9523f0e0 433 $this->sql->basegroupby = 'ue.userid, e.courseid, f.id, u.id, ' . $userfieldssql;
6388b657 434
235a6db7
DM
435 if ($this->logreader) {
436 $this->fill_log_summary_temp_table($this->context->id);
437
438 $this->sql->basefields .= ', CASE WHEN tmp.viewcount IS NOT NULL THEN tmp.viewcount ELSE 0 END AS viewcount';
439 $this->sql->basefromjoins .= ' LEFT JOIN {' . self::LOG_SUMMARY_TEMP_TABLE . '} tmp ON tmp.userid = u.id';
440 $this->sql->basegroupby .= ', tmp.viewcount';
441 }
442
d0e125cf
MH
443 $this->sql->params = [
444 'component' => 'mod_forum',
14f92164 445 'courseid' => $this->cm->course,
d0e125cf 446 ];
6388b657
MH
447
448 // Handle if a user is limited to viewing their own summary.
449 if (!empty($this->userid)) {
450 $this->sql->basewhere .= ' AND ue.userid = :userid';
451 $this->sql->params['userid'] = $this->userid;
452 }
453
454 // Filter values will be populated separately where required.
455 $this->sql->filterfields = '';
456 $this->sql->filterfromjoins = '';
457 $this->sql->filterwhere = '';
458 $this->sql->filtergroupby = '';
459 }
460
461 /**
462 * Overriding the parent method because it should not be used here.
463 * Filters are applied, so the structure of $this->sql is now different to the way this is set up in the parent.
464 *
465 * @param string $fields Unused.
466 * @param string $from Unused.
467 * @param string $where Unused.
468 * @param array $params Unused.
469 * @return void.
470 *
471 * @throws coding_exception
472 */
473 public function set_sql($fields, $from, $where, array $params = []) {
474 throw new coding_exception('The set_sql method should not be used by the summary_table class.');
475 }
476
477 /**
478 * Convenience method to call a number of methods for you to display the table.
479 * Overrides the parent so SQL for filters is handled.
480 *
481 * @param int $pagesize Number of rows to fetch.
482 * @param bool $useinitialsbar Whether to include the initials bar with the table.
483 * @param string $downloadhelpbutton Unused.
484 *
485 * @return void.
486 */
487 public function out($pagesize, $useinitialsbar, $downloadhelpbutton = ''): void {
488 global $DB;
489
490 if (!$this->columns) {
491 $sql = $this->get_full_sql();
492
493 $onerow = $DB->get_record_sql($sql, $this->sql->params, IGNORE_MULTIPLE);
494
495 // If columns is not set, define columns as the keys of the rows returned from the db.
496 $this->define_columns(array_keys((array)$onerow));
497 $this->define_headers(array_keys((array)$onerow));
498 }
499
500 $this->setup();
501 $this->query_db($pagesize, $useinitialsbar);
502 $this->build_table();
503 $this->close_recordset();
504 $this->finish_output();
505 }
506
b29de56d
MH
507 /**
508 * Apply the relevant filters to the report.
509 *
510 * @param array $filters Report filters in the format 'type' => [values].
511 * @return void.
512 */
513 protected function apply_filters(array $filters): void {
514 // Apply the forums filter.
515 $this->add_filter(self::FILTER_FORUM, $filters['forums']);
516
517 // Apply groups filter.
518 $this->add_filter(self::FILTER_GROUPS, $filters['groups']);
519 }
520
6388b657
MH
521 /**
522 * Prepares a complete SQL statement from the base query and any filters defined.
523 *
524 * @param bool $fullselect Whether to select all relevant columns.
525 * False selects a count only (used to calculate pagination).
526 * @return string The complete SQL statement.
527 */
528 protected function get_full_sql(bool $fullselect = true): string {
6388b657
MH
529 $groupby = '';
530 $orderby = '';
531
532 if ($fullselect) {
533 $selectfields = "{$this->sql->basefields}
534 {$this->sql->filterfields}";
535
536 $groupby = ' GROUP BY ' . $this->sql->basegroupby . $this->sql->filtergroupby;
537
538 if (($sort = $this->get_sql_sort())) {
539 $orderby = " ORDER BY {$sort}";
540 }
541 } else {
542 $selectfields = 'COUNT(DISTINCT(ue.userid))';
543 }
544
545 $sql = "SELECT {$selectfields}
546 FROM {$this->sql->basefromjoins}
547 {$this->sql->filterfromjoins}
548 WHERE {$this->sql->basewhere}
549 {$this->sql->filterwhere}
550 {$groupby}
551 {$orderby}";
552
553 return $sql;
554 }
235a6db7
DM
555
556 /**
557 * Returns an internal and enabled log reader.
558 *
559 * @return \core\log\sql_reader|false
560 */
561 protected function get_internal_log_reader(): ?\core\log\sql_reader {
562 global $DB;
563
564 $readers = get_log_manager()->get_readers('core\log\sql_reader');
565 foreach ($readers as $reader) {
566
567 // If reader is not a sql_internal_table_reader and not legacy store then return.
568 if (!($reader instanceof \core\log\sql_internal_table_reader) && !($reader instanceof logstore_legacy\log\store)) {
569 continue;
570 }
571 $logreader = $reader;
572 }
573
574 if (empty($logreader)) {
575 return null;
576 }
577
578 return $logreader;
579 }
580
581 /**
582 * Fills the log summary temp table.
583 *
584 * @param int $contextid
585 * @return null
586 */
587 protected function fill_log_summary_temp_table(int $contextid) {
588 global $DB;
589
590 $this->create_log_summary_temp_table();
591
592 if ($this->logreader instanceof logstore_legacy\log\store) {
593 $logtable = 'log';
594 // Anonymous actions are never logged in legacy log.
595 $nonanonymous = '';
596 } else {
597 $logtable = $this->logreader->get_internal_log_table_name();
598 $nonanonymous = 'AND anonymous = 0';
599 }
600 $params = ['contextid' => $contextid];
601 $sql = "INSERT INTO {" . self::LOG_SUMMARY_TEMP_TABLE . "} (userid, viewcount)
602 SELECT userid, COUNT(*) AS viewcount
603 FROM {" . $logtable . "}
604 WHERE contextid = :contextid
605 $nonanonymous
606 GROUP BY userid";
607 $DB->execute($sql, $params);
608 }
609
610 /**
611 * Creates a temp table to store summary data from the log table for this request.
612 *
613 * @return null
614 */
615 protected function create_log_summary_temp_table() {
616 global $DB;
617
618 $dbman = $DB->get_manager();
619 $temptablename = self::LOG_SUMMARY_TEMP_TABLE;
620 $xmldbtable = new \xmldb_table($temptablename);
621 $xmldbtable->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null);
622 $xmldbtable->add_field('viewcount', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null);
623 $xmldbtable->add_key('primary', XMLDB_KEY_PRIMARY, array('userid'));
624
625 $dbman->create_temp_table($xmldbtable);
626 }
9eebd58a
MH
627
628 /**
629 * Check whether the groups filter will be applied by checking whether the number of groups selected
630 * matches the total number of options available (all groups plus no groups option).
631 *
632 * @param array $groups The group IDs selected.
633 * @return bool
634 */
635 protected function is_filtered_by_groups(array $groups): bool {
636 static $groupsavailablecount = null;
637
638 if (empty($groups)) {
639 return false;
640 }
641
642 // Find total number of options available (groups plus 'no groups'), if not already fetched.
643 if (is_null($groupsavailablecount)) {
644 $groupsavailablecount = 1 + count(groups_get_activity_allowed_groups($this->cm));
645 }
646
647 return (count($groups) < $groupsavailablecount);
648 }
6388b657 649}