MDL-66767 forumreport_summary: Updated groups filter to apply to data
[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 {
850ce974
SL
182 if ($this->is_downloading()) {
183 return fullname($data);
184 }
6388b657 185
850ce974 186 global $OUTPUT;
806d6343 187 return $OUTPUT->user_picture($data, array('size' => 35, 'courseid' => $this->cm->course, 'includefullname' => true));
6388b657
MH
188 }
189
190 /**
191 * Generate the postcount column.
192 *
193 * @param \stdClass $data The row data.
194 * @return int number of discussion posts made by user.
195 */
196 public function col_postcount(\stdClass $data): int {
197 return $data->postcount;
198 }
199
200 /**
201 * Generate the replycount column.
202 *
203 * @param \stdClass $data The row data.
204 * @return int number of replies made by user.
205 */
206 public function col_replycount(\stdClass $data): int {
207 return $data->replycount;
208 }
209
d0e125cf
MH
210 /**
211 * Generate the attachmentcount column.
212 *
213 * @param \stdClass $data The row data.
214 * @return int number of files attached to posts by user.
215 */
216 public function col_attachmentcount(\stdClass $data): int {
217 return $data->attachmentcount;
218 }
219
382168a0
MH
220 /**
221 * Generate the earliestpost column.
222 *
223 * @param \stdClass $data The row data.
224 * @return string Timestamp of user's earliest post, or a dash if no posts exist.
225 */
226 public function col_earliestpost(\stdClass $data): string {
227 global $USER;
228
229 return empty($data->earliestpost) ? '-' : userdate($data->earliestpost, "", \core_date::get_user_timezone($USER));
230 }
231
232 /**
233 * Generate the latestpost column.
234 *
235 * @param \stdClass $data The row data.
236 * @return string Timestamp of user's most recent post, or a dash if no posts exist.
237 */
238 public function col_latestpost(\stdClass $data): string {
239 global $USER;
240
241 return empty($data->latestpost) ? '-' : userdate($data->latestpost, "", \core_date::get_user_timezone($USER));
242 }
243
6388b657
MH
244 /**
245 * Override the default implementation to set a decent heading level.
246 *
247 * @return void.
248 */
249 public function print_nothing_to_display(): void {
250 global $OUTPUT;
251
252 echo $OUTPUT->heading(get_string('nothingtodisplay'), 4);
253 }
254
255 /**
256 * Query the db. Store results in the table object for use by build_table.
257 *
258 * @param int $pagesize Size of page for paginated displayed table.
259 * @param bool $useinitialsbar Overridden but unused.
260 * @return void
261 */
262 public function query_db($pagesize, $useinitialsbar = false): void {
263 global $DB;
264
265 // Set up pagination if not downloading the whole report.
266 if (!$this->is_downloading()) {
267 $totalsql = $this->get_full_sql(false);
268
269 // Set up pagination.
270 $totalrows = $DB->count_records_sql($totalsql, $this->sql->params);
271 $this->pagesize($pagesize, $totalrows);
272 }
273
274 // Fetch the data.
275 $sql = $this->get_full_sql();
276
277 // Only paginate when not downloading.
278 if (!$this->is_downloading()) {
279 $this->rawdata = $DB->get_records_sql($sql, $this->sql->params, $this->get_page_start(), $this->get_page_size());
280 } else {
281 $this->rawdata = $DB->get_records_sql($sql, $this->sql->params);
282 }
283 }
284
285 /**
286 * Adds the relevant SQL to apply a filter to the report.
287 *
288 * @param int $filtertype Filter type as defined by class constants.
289 * @param array $values Optional array of values passed into the filter type.
290 * @return void
291 * @throws coding_exception
292 */
293 public function add_filter(int $filtertype, array $values = []): void {
b29de56d
MH
294 global $DB;
295
6388b657
MH
296 $paramcounterror = false;
297
298 switch($filtertype) {
299 case self::FILTER_FORUM:
300 // Requires exactly one forum ID.
301 if (count($values) != 1) {
302 $paramcounterror = true;
303 } else {
304 // No select fields required - displayed in title.
305 // No extra joins required, forum is already joined.
306 $this->sql->filterwhere .= ' AND f.id = :forumid';
307 $this->sql->params['forumid'] = $values[0];
308 }
309
310 break;
311
b29de56d 312 case self::FILTER_GROUPS:
5e43937c
MH
313 // Filter data to only include content within specified groups (and/or no groups).
314 // Additionally, only display users who can post within the selected option(s).
315
b29de56d 316 // Skip adding filter if not applied, or all options are selected.
9eebd58a 317 if ($this->is_filtered_by_groups($values)) {
5e43937c
MH
318 // Posts within selected groups and/or not in any groups (group ID -1) are included.
319 // No user filtering as anyone enrolled can potentially post to unrestricted discussions.
320 if (array_search(-1, $values, true) !== false) {
b29de56d
MH
321 list($groupidin, $groupidparams) = $DB->get_in_or_equal($values, SQL_PARAMS_NAMED, 'groupid');
322
5e43937c 323 $this->sql->filterwhere .= " AND d.groupid {$groupidin}";
b29de56d
MH
324 $this->sql->params += $groupidparams;
325
b29de56d 326 } else if (!empty($values)) {
5e43937c
MH
327 // Only posts and users within selected groups are included.
328 list($groupusersin, $groupusersparams) = $DB->get_in_or_equal($values, SQL_PARAMS_NAMED, 'groupusers');
b29de56d
MH
329 list($groupidin, $groupidparams) = $DB->get_in_or_equal($values, SQL_PARAMS_NAMED, 'groupid');
330
b29de56d
MH
331 // No joins required (handled by where to prevent data duplication).
332 $this->sql->filterwhere .= "
333 AND u.id = (
334 SELECT gm.userid
335 FROM {groups_members} gm
336 WHERE gm.userid = u.id
5e43937c 337 AND gm.groupid {$groupusersin}
b29de56d 338 GROUP BY gm.userid
5e43937c
MH
339 LIMIT 1)
340 AND d.groupid {$groupidin}";
341 $this->sql->params += $groupusersparams + $groupidparams;
b29de56d
MH
342 }
343 }
344
345 break;
346
6388b657
MH
347 default:
348 throw new coding_exception("Report filter type '{$filtertype}' not found.");
349 break;
350 }
351
352 if ($paramcounterror) {
353 $filtername = $this->get_filter_name($filtertype);
354 throw new coding_exception("An invalid number of values have been passed for the '{$filtername}' filter.");
355 }
356 }
357
358 /**
359 * Define various table config options.
360 *
361 * @return void.
362 */
363 protected function define_table_configs(): void {
364 $this->collapsible(false);
365 $this->sortable(true, 'firstname', SORT_ASC);
366 $this->pageable(true);
850ce974 367 $this->is_downloadable(true);
6388b657 368 $this->no_sorting('select');
d0e125cf 369 $this->set_attribute('id', 'forumreport_summary_table');
6388b657
MH
370 }
371
372 /**
373 * Define the object to store all for the table SQL and initialises the base SQL required.
374 *
375 * @return void.
376 */
377 protected function define_base_sql(): void {
378 $this->sql = new \stdClass();
379
806d6343
SR
380 $userfields = get_extra_user_fields($this->context);
381 $userfieldssql = \user_picture::fields('u', $userfields);
382
6388b657
MH
383 // Define base SQL query format.
384 // Ignores private replies as they are not visible to all participants.
385 $this->sql->basefields = ' ue.userid AS userid,
806d6343
SR
386 e.courseid AS courseid,
387 f.id as forumid,
388 SUM(CASE WHEN p.parent = 0 THEN 1 ELSE 0 END) AS postcount,
389 SUM(CASE WHEN p.parent != 0 THEN 1 ELSE 0 END) AS replycount,
390 ' . $userfieldssql . ',
391 SUM(CASE WHEN att.attcount IS NULL THEN 0 ELSE att.attcount END) AS attachmentcount,
392 MIN(p.created) AS earliestpost,
393 MAX(p.created) AS latestpost';
6388b657
MH
394
395 $this->sql->basefromjoins = ' {enrol} e
396 JOIN {user_enrolments} ue ON ue.enrolid = e.id
397 JOIN {user} u ON u.id = ue.userid
398 JOIN {forum} f ON f.course = e.courseid
399 JOIN {forum_discussions} d ON d.forum = f.id
400 LEFT JOIN {forum_posts} p ON p.discussion = d.id
401 AND p.userid = ue.userid
d0e125cf
MH
402 AND p.privatereplyto = 0
403 LEFT JOIN (
404 SELECT COUNT(fi.id) AS attcount, fi.itemid AS postid, fi.userid
405 FROM {files} fi
406 WHERE fi.component = :component
407 AND fi.filesize > 0
408 GROUP BY fi.itemid, fi.userid
409 ) att ON att.postid = p.id
410 AND att.userid = ue.userid';
6388b657
MH
411
412 $this->sql->basewhere = 'e.courseid = :courseid';
413
9523f0e0 414 $this->sql->basegroupby = 'ue.userid, e.courseid, f.id, u.id, ' . $userfieldssql;
6388b657 415
235a6db7
DM
416 if ($this->logreader) {
417 $this->fill_log_summary_temp_table($this->context->id);
418
419 $this->sql->basefields .= ', CASE WHEN tmp.viewcount IS NOT NULL THEN tmp.viewcount ELSE 0 END AS viewcount';
420 $this->sql->basefromjoins .= ' LEFT JOIN {' . self::LOG_SUMMARY_TEMP_TABLE . '} tmp ON tmp.userid = u.id';
421 $this->sql->basegroupby .= ', tmp.viewcount';
422 }
423
d0e125cf
MH
424 $this->sql->params = [
425 'component' => 'mod_forum',
14f92164 426 'courseid' => $this->cm->course,
d0e125cf 427 ];
6388b657
MH
428
429 // Handle if a user is limited to viewing their own summary.
430 if (!empty($this->userid)) {
431 $this->sql->basewhere .= ' AND ue.userid = :userid';
432 $this->sql->params['userid'] = $this->userid;
433 }
434
435 // Filter values will be populated separately where required.
436 $this->sql->filterfields = '';
437 $this->sql->filterfromjoins = '';
438 $this->sql->filterwhere = '';
439 $this->sql->filtergroupby = '';
440 }
441
442 /**
443 * Overriding the parent method because it should not be used here.
444 * Filters are applied, so the structure of $this->sql is now different to the way this is set up in the parent.
445 *
446 * @param string $fields Unused.
447 * @param string $from Unused.
448 * @param string $where Unused.
449 * @param array $params Unused.
450 * @return void.
451 *
452 * @throws coding_exception
453 */
454 public function set_sql($fields, $from, $where, array $params = []) {
455 throw new coding_exception('The set_sql method should not be used by the summary_table class.');
456 }
457
458 /**
459 * Convenience method to call a number of methods for you to display the table.
460 * Overrides the parent so SQL for filters is handled.
461 *
462 * @param int $pagesize Number of rows to fetch.
463 * @param bool $useinitialsbar Whether to include the initials bar with the table.
464 * @param string $downloadhelpbutton Unused.
465 *
466 * @return void.
467 */
468 public function out($pagesize, $useinitialsbar, $downloadhelpbutton = ''): void {
469 global $DB;
470
471 if (!$this->columns) {
472 $sql = $this->get_full_sql();
473
474 $onerow = $DB->get_record_sql($sql, $this->sql->params, IGNORE_MULTIPLE);
475
476 // If columns is not set, define columns as the keys of the rows returned from the db.
477 $this->define_columns(array_keys((array)$onerow));
478 $this->define_headers(array_keys((array)$onerow));
479 }
480
481 $this->setup();
482 $this->query_db($pagesize, $useinitialsbar);
483 $this->build_table();
484 $this->close_recordset();
485 $this->finish_output();
486 }
487
b29de56d
MH
488 /**
489 * Apply the relevant filters to the report.
490 *
491 * @param array $filters Report filters in the format 'type' => [values].
492 * @return void.
493 */
494 protected function apply_filters(array $filters): void {
495 // Apply the forums filter.
496 $this->add_filter(self::FILTER_FORUM, $filters['forums']);
497
498 // Apply groups filter.
499 $this->add_filter(self::FILTER_GROUPS, $filters['groups']);
500 }
501
6388b657
MH
502 /**
503 * Prepares a complete SQL statement from the base query and any filters defined.
504 *
505 * @param bool $fullselect Whether to select all relevant columns.
506 * False selects a count only (used to calculate pagination).
507 * @return string The complete SQL statement.
508 */
509 protected function get_full_sql(bool $fullselect = true): string {
6388b657
MH
510 $groupby = '';
511 $orderby = '';
512
513 if ($fullselect) {
514 $selectfields = "{$this->sql->basefields}
515 {$this->sql->filterfields}";
516
517 $groupby = ' GROUP BY ' . $this->sql->basegroupby . $this->sql->filtergroupby;
518
519 if (($sort = $this->get_sql_sort())) {
520 $orderby = " ORDER BY {$sort}";
521 }
522 } else {
523 $selectfields = 'COUNT(DISTINCT(ue.userid))';
524 }
525
526 $sql = "SELECT {$selectfields}
527 FROM {$this->sql->basefromjoins}
528 {$this->sql->filterfromjoins}
529 WHERE {$this->sql->basewhere}
530 {$this->sql->filterwhere}
531 {$groupby}
532 {$orderby}";
533
534 return $sql;
535 }
235a6db7
DM
536
537 /**
538 * Returns an internal and enabled log reader.
539 *
540 * @return \core\log\sql_reader|false
541 */
542 protected function get_internal_log_reader(): ?\core\log\sql_reader {
543 global $DB;
544
545 $readers = get_log_manager()->get_readers('core\log\sql_reader');
546 foreach ($readers as $reader) {
547
548 // If reader is not a sql_internal_table_reader and not legacy store then return.
549 if (!($reader instanceof \core\log\sql_internal_table_reader) && !($reader instanceof logstore_legacy\log\store)) {
550 continue;
551 }
552 $logreader = $reader;
553 }
554
555 if (empty($logreader)) {
556 return null;
557 }
558
559 return $logreader;
560 }
561
562 /**
563 * Fills the log summary temp table.
564 *
565 * @param int $contextid
566 * @return null
567 */
568 protected function fill_log_summary_temp_table(int $contextid) {
569 global $DB;
570
571 $this->create_log_summary_temp_table();
572
573 if ($this->logreader instanceof logstore_legacy\log\store) {
574 $logtable = 'log';
575 // Anonymous actions are never logged in legacy log.
576 $nonanonymous = '';
577 } else {
578 $logtable = $this->logreader->get_internal_log_table_name();
579 $nonanonymous = 'AND anonymous = 0';
580 }
581 $params = ['contextid' => $contextid];
582 $sql = "INSERT INTO {" . self::LOG_SUMMARY_TEMP_TABLE . "} (userid, viewcount)
583 SELECT userid, COUNT(*) AS viewcount
584 FROM {" . $logtable . "}
585 WHERE contextid = :contextid
586 $nonanonymous
587 GROUP BY userid";
588 $DB->execute($sql, $params);
589 }
590
591 /**
592 * Creates a temp table to store summary data from the log table for this request.
593 *
594 * @return null
595 */
596 protected function create_log_summary_temp_table() {
597 global $DB;
598
599 $dbman = $DB->get_manager();
600 $temptablename = self::LOG_SUMMARY_TEMP_TABLE;
601 $xmldbtable = new \xmldb_table($temptablename);
602 $xmldbtable->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null);
603 $xmldbtable->add_field('viewcount', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null);
604 $xmldbtable->add_key('primary', XMLDB_KEY_PRIMARY, array('userid'));
605
606 $dbman->create_temp_table($xmldbtable);
607 }
9eebd58a
MH
608
609 /**
610 * Check whether the groups filter will be applied by checking whether the number of groups selected
611 * matches the total number of options available (all groups plus no groups option).
612 *
613 * @param array $groups The group IDs selected.
614 * @return bool
615 */
616 protected function is_filtered_by_groups(array $groups): bool {
617 static $groupsavailablecount = null;
618
619 if (empty($groups)) {
620 return false;
621 }
622
623 // Find total number of options available (groups plus 'no groups'), if not already fetched.
624 if (is_null($groupsavailablecount)) {
625 $groupsavailablecount = 1 + count(groups_get_activity_allowed_groups($this->cm));
626 }
627
628 return (count($groups) < $groupsavailablecount);
629 }
850ce974
SL
630
631 /**
632 * Download the summary report in the selected format.
633 *
634 * @param string $format The format to download the report.
635 */
636 public function download($format) {
637 $filename = 'summary_report_' . userdate(time(), get_string('backupnameformat', 'langconfig'),
638 99, false);
639
640 $this->is_downloading($format, $filename);
641 $this->out($this->perpage, false);
642 }
6388b657 643}