MDL-66298 forumreport_summary: Moved table generation out of renderer
[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
56 /** @var int The course ID being reported on. */
57 protected $courseid;
58
59 /** @var int The forum ID being reported on. */
60 protected $forumid;
61
b29de56d
MH
62 /** @var \stdClass The course module object of the forum being reported on. */
63 protected $cm;
64
6388b657
MH
65 /**
66 * @var int The user ID if only one user's summary will be generated.
67 * This will apply to users without permission to view others' summaries.
68 */
69 protected $userid;
70
b29de56d
MH
71 /**
72 * @var bool Whether the table should be overridden to show the 'nothing to display' message.
73 * False unless checks confirm there will be nothing to display.
74 */
75 protected $nothingtodisplay = false;
76
235a6db7
DM
77 /**
78 * @var \core\log\sql_reader|null
79 */
80 protected $logreader = null;
81
82 /**
83 * @var \context|null
84 */
85 protected $context = null;
86
6388b657
MH
87 /**
88 * Forum report table constructor.
89 *
90 * @param int $courseid The ID of the course the forum(s) exist within.
b29de56d 91 * @param array $filters Report filters in the format 'type' => [values].
6388b657 92 */
b29de56d 93 public function __construct(int $courseid, array $filters) {
6388b657
MH
94 global $USER;
95
b29de56d
MH
96 $forumid = $filters['forums'][0];
97
6388b657
MH
98 parent::__construct("summaryreport_{$courseid}_{$forumid}");
99
b29de56d 100 $this->cm = get_coursemodule_from_instance('forum', $forumid, $courseid);
235a6db7 101 $this->context = \context_module::instance($this->cm->id);
6388b657
MH
102
103 // Only show their own summary unless they have permission to view all.
235a6db7 104 if (!has_capability('forumreport/summary:viewall', $this->context)) {
6388b657
MH
105 $this->userid = $USER->id;
106 }
107
108 $this->courseid = intval($courseid);
109
110 $columnheaders = [
111 'fullname' => get_string('fullnameuser'),
112 'postcount' => get_string('postcount', 'forumreport_summary'),
113 'replycount' => get_string('replycount', 'forumreport_summary'),
d0e125cf 114 'attachmentcount' => get_string('attachmentcount', 'forumreport_summary'),
6388b657
MH
115 ];
116
235a6db7 117 // Only include viewcount column when no groups filter is applied.
9eebd58a 118 if (!$this->is_filtered_by_groups($filters['groups'])) {
235a6db7
DM
119 $this->logreader = $this->get_internal_log_reader();
120 if ($this->logreader) {
121 $columnheaders['viewcount'] = get_string('viewcount', 'forumreport_summary');
122 }
123 }
124
125 $columnheaders['earliestpost'] = get_string('earliestpost', 'forumreport_summary');
126 $columnheaders['latestpost'] = get_string('latestpost', 'forumreport_summary');
127
6388b657
MH
128 $this->define_columns(array_keys($columnheaders));
129 $this->define_headers(array_values($columnheaders));
130
131 // Define configs.
132 $this->define_table_configs();
133
134 // Define the basic SQL data and object format.
135 $this->define_base_sql();
136
b29de56d
MH
137 // Apply relevant filters.
138 $this->apply_filters($filters);
6388b657
MH
139 }
140
141 /**
142 * Provides the string name of each filter type.
143 *
144 * @param int $filtertype Type of filter
145 * @return string Name of the filter
146 */
147 public function get_filter_name(int $filtertype): string {
148 $filternames = [
149 self::FILTER_FORUM => 'Forum',
b29de56d 150 self::FILTER_GROUPS => 'Groups',
6388b657
MH
151 ];
152
153 return $filternames[$filtertype];
154 }
155
156 /**
157 * Generate the fullname column.
158 *
159 * @param \stdClass $data The row data.
160 * @return string User's full name.
161 */
162 public function col_fullname($data): string {
806d6343 163 global $OUTPUT;
6388b657 164
806d6343 165 return $OUTPUT->user_picture($data, array('size' => 35, 'courseid' => $this->cm->course, 'includefullname' => true));
6388b657
MH
166 }
167
168 /**
169 * Generate the postcount column.
170 *
171 * @param \stdClass $data The row data.
172 * @return int number of discussion posts made by user.
173 */
174 public function col_postcount(\stdClass $data): int {
175 return $data->postcount;
176 }
177
178 /**
179 * Generate the replycount column.
180 *
181 * @param \stdClass $data The row data.
182 * @return int number of replies made by user.
183 */
184 public function col_replycount(\stdClass $data): int {
185 return $data->replycount;
186 }
187
d0e125cf
MH
188 /**
189 * Generate the attachmentcount column.
190 *
191 * @param \stdClass $data The row data.
192 * @return int number of files attached to posts by user.
193 */
194 public function col_attachmentcount(\stdClass $data): int {
195 return $data->attachmentcount;
196 }
197
382168a0
MH
198 /**
199 * Generate the earliestpost column.
200 *
201 * @param \stdClass $data The row data.
202 * @return string Timestamp of user's earliest post, or a dash if no posts exist.
203 */
204 public function col_earliestpost(\stdClass $data): string {
205 global $USER;
206
207 return empty($data->earliestpost) ? '-' : userdate($data->earliestpost, "", \core_date::get_user_timezone($USER));
208 }
209
210 /**
211 * Generate the latestpost column.
212 *
213 * @param \stdClass $data The row data.
214 * @return string Timestamp of user's most recent post, or a dash if no posts exist.
215 */
216 public function col_latestpost(\stdClass $data): string {
217 global $USER;
218
219 return empty($data->latestpost) ? '-' : userdate($data->latestpost, "", \core_date::get_user_timezone($USER));
220 }
221
6388b657
MH
222 /**
223 * Override the default implementation to set a decent heading level.
224 *
225 * @return void.
226 */
227 public function print_nothing_to_display(): void {
228 global $OUTPUT;
229
230 echo $OUTPUT->heading(get_string('nothingtodisplay'), 4);
231 }
232
233 /**
234 * Query the db. Store results in the table object for use by build_table.
235 *
236 * @param int $pagesize Size of page for paginated displayed table.
237 * @param bool $useinitialsbar Overridden but unused.
238 * @return void
239 */
240 public function query_db($pagesize, $useinitialsbar = false): void {
241 global $DB;
242
243 // Set up pagination if not downloading the whole report.
244 if (!$this->is_downloading()) {
245 $totalsql = $this->get_full_sql(false);
246
247 // Set up pagination.
248 $totalrows = $DB->count_records_sql($totalsql, $this->sql->params);
249 $this->pagesize($pagesize, $totalrows);
250 }
251
252 // Fetch the data.
253 $sql = $this->get_full_sql();
254
255 // Only paginate when not downloading.
256 if (!$this->is_downloading()) {
257 $this->rawdata = $DB->get_records_sql($sql, $this->sql->params, $this->get_page_start(), $this->get_page_size());
258 } else {
259 $this->rawdata = $DB->get_records_sql($sql, $this->sql->params);
260 }
261 }
262
263 /**
264 * Adds the relevant SQL to apply a filter to the report.
265 *
266 * @param int $filtertype Filter type as defined by class constants.
267 * @param array $values Optional array of values passed into the filter type.
268 * @return void
269 * @throws coding_exception
270 */
271 public function add_filter(int $filtertype, array $values = []): void {
b29de56d
MH
272 global $DB;
273
6388b657
MH
274 $paramcounterror = false;
275
276 switch($filtertype) {
277 case self::FILTER_FORUM:
278 // Requires exactly one forum ID.
279 if (count($values) != 1) {
280 $paramcounterror = true;
281 } else {
282 // No select fields required - displayed in title.
283 // No extra joins required, forum is already joined.
284 $this->sql->filterwhere .= ' AND f.id = :forumid';
285 $this->sql->params['forumid'] = $values[0];
286 }
287
288 break;
289
b29de56d 290 case self::FILTER_GROUPS:
b29de56d 291 // Skip adding filter if not applied, or all options are selected.
9eebd58a 292 if ($this->is_filtered_by_groups($values)) {
b29de56d
MH
293 // Include users without groups if that option (-1) is selected.
294 $nonekey = array_search(-1, $values, true);
295
296 // Users within selected groups or not in any groups are included.
297 if ($nonekey !== false && count($values) > 1) {
298 unset($values[$nonekey]);
299 list($groupidin, $groupidparams) = $DB->get_in_or_equal($values, SQL_PARAMS_NAMED, 'groupid');
300
301 // No select fields required.
302 // No joins required (handled by where to prevent data duplication).
303 $this->sql->filterwhere .= "
304 AND (u.id =
305 (SELECT gm.userid
306 FROM {groups_members} gm
307 WHERE gm.userid = u.id
308 AND gm.groupid {$groupidin}
309 GROUP BY gm.userid
310 LIMIT 1)
311 OR
312 (SELECT nogm.userid
313 FROM mdl_groups_members nogm
314 WHERE nogm.userid = u.id
315 GROUP BY nogm.userid
316 LIMIT 1)
317 IS NULL)";
318 $this->sql->params += $groupidparams;
319
320 } else if ($nonekey !== false) {
321 // Only users within no groups are included.
322 unset($values[$nonekey]);
323
324 // No select fields required.
325 $this->sql->filterfromjoins .= " LEFT JOIN {groups_members} nogm ON nogm.userid = u.id";
326 $this->sql->filterwhere .= " AND nogm.id IS NULL";
327
328 } else if (!empty($values)) {
329 // Only users within selected groups are included.
330 list($groupidin, $groupidparams) = $DB->get_in_or_equal($values, SQL_PARAMS_NAMED, 'groupid');
331
332 // No select fields required.
333 // No joins required (handled by where to prevent data duplication).
334 $this->sql->filterwhere .= "
335 AND u.id = (
336 SELECT gm.userid
337 FROM {groups_members} gm
338 WHERE gm.userid = u.id
339 AND gm.groupid {$groupidin}
340 GROUP BY gm.userid
341 LIMIT 1)";
342 $this->sql->params += $groupidparams;
343 }
344 }
345
346 break;
347
6388b657
MH
348 default:
349 throw new coding_exception("Report filter type '{$filtertype}' not found.");
350 break;
351 }
352
353 if ($paramcounterror) {
354 $filtername = $this->get_filter_name($filtertype);
355 throw new coding_exception("An invalid number of values have been passed for the '{$filtername}' filter.");
356 }
357 }
358
359 /**
360 * Define various table config options.
361 *
362 * @return void.
363 */
364 protected function define_table_configs(): void {
365 $this->collapsible(false);
366 $this->sortable(true, 'firstname', SORT_ASC);
367 $this->pageable(true);
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
806d6343 414 $this->sql->basegroupby = 'ue.userid, e.courseid, f.id, u.id';
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',
426 'courseid' => $this->courseid,
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
b29de56d
MH
471 // If there is nothing to display, print the relevant string and return, no further action is required.
472 if ($this->nothingtodisplay) {
473 $this->print_nothing_to_display();
474 return;
475 }
476
6388b657
MH
477 if (!$this->columns) {
478 $sql = $this->get_full_sql();
479
480 $onerow = $DB->get_record_sql($sql, $this->sql->params, IGNORE_MULTIPLE);
481
482 // If columns is not set, define columns as the keys of the rows returned from the db.
483 $this->define_columns(array_keys((array)$onerow));
484 $this->define_headers(array_keys((array)$onerow));
485 }
486
487 $this->setup();
488 $this->query_db($pagesize, $useinitialsbar);
489 $this->build_table();
490 $this->close_recordset();
491 $this->finish_output();
492 }
493
b29de56d
MH
494 /**
495 * Apply the relevant filters to the report.
496 *
497 * @param array $filters Report filters in the format 'type' => [values].
498 * @return void.
499 */
500 protected function apply_filters(array $filters): void {
501 // Apply the forums filter.
502 $this->add_filter(self::FILTER_FORUM, $filters['forums']);
503
504 // Apply groups filter.
505 $this->add_filter(self::FILTER_GROUPS, $filters['groups']);
506 }
507
6388b657
MH
508 /**
509 * Prepares a complete SQL statement from the base query and any filters defined.
510 *
511 * @param bool $fullselect Whether to select all relevant columns.
512 * False selects a count only (used to calculate pagination).
513 * @return string The complete SQL statement.
514 */
515 protected function get_full_sql(bool $fullselect = true): string {
516 $selectfields = '';
517 $groupby = '';
518 $orderby = '';
519
520 if ($fullselect) {
521 $selectfields = "{$this->sql->basefields}
522 {$this->sql->filterfields}";
523
524 $groupby = ' GROUP BY ' . $this->sql->basegroupby . $this->sql->filtergroupby;
525
526 if (($sort = $this->get_sql_sort())) {
527 $orderby = " ORDER BY {$sort}";
528 }
529 } else {
530 $selectfields = 'COUNT(DISTINCT(ue.userid))';
531 }
532
533 $sql = "SELECT {$selectfields}
534 FROM {$this->sql->basefromjoins}
535 {$this->sql->filterfromjoins}
536 WHERE {$this->sql->basewhere}
537 {$this->sql->filterwhere}
538 {$groupby}
539 {$orderby}";
540
541 return $sql;
542 }
235a6db7
DM
543
544 /**
545 * Returns an internal and enabled log reader.
546 *
547 * @return \core\log\sql_reader|false
548 */
549 protected function get_internal_log_reader(): ?\core\log\sql_reader {
550 global $DB;
551
552 $readers = get_log_manager()->get_readers('core\log\sql_reader');
553 foreach ($readers as $reader) {
554
555 // If reader is not a sql_internal_table_reader and not legacy store then return.
556 if (!($reader instanceof \core\log\sql_internal_table_reader) && !($reader instanceof logstore_legacy\log\store)) {
557 continue;
558 }
559 $logreader = $reader;
560 }
561
562 if (empty($logreader)) {
563 return null;
564 }
565
566 return $logreader;
567 }
568
569 /**
570 * Fills the log summary temp table.
571 *
572 * @param int $contextid
573 * @return null
574 */
575 protected function fill_log_summary_temp_table(int $contextid) {
576 global $DB;
577
578 $this->create_log_summary_temp_table();
579
580 if ($this->logreader instanceof logstore_legacy\log\store) {
581 $logtable = 'log';
582 // Anonymous actions are never logged in legacy log.
583 $nonanonymous = '';
584 } else {
585 $logtable = $this->logreader->get_internal_log_table_name();
586 $nonanonymous = 'AND anonymous = 0';
587 }
588 $params = ['contextid' => $contextid];
589 $sql = "INSERT INTO {" . self::LOG_SUMMARY_TEMP_TABLE . "} (userid, viewcount)
590 SELECT userid, COUNT(*) AS viewcount
591 FROM {" . $logtable . "}
592 WHERE contextid = :contextid
593 $nonanonymous
594 GROUP BY userid";
595 $DB->execute($sql, $params);
596 }
597
598 /**
599 * Creates a temp table to store summary data from the log table for this request.
600 *
601 * @return null
602 */
603 protected function create_log_summary_temp_table() {
604 global $DB;
605
606 $dbman = $DB->get_manager();
607 $temptablename = self::LOG_SUMMARY_TEMP_TABLE;
608 $xmldbtable = new \xmldb_table($temptablename);
609 $xmldbtable->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null);
610 $xmldbtable->add_field('viewcount', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null);
611 $xmldbtable->add_key('primary', XMLDB_KEY_PRIMARY, array('userid'));
612
613 $dbman->create_temp_table($xmldbtable);
614 }
9eebd58a
MH
615
616 /**
617 * Check whether the groups filter will be applied by checking whether the number of groups selected
618 * matches the total number of options available (all groups plus no groups option).
619 *
620 * @param array $groups The group IDs selected.
621 * @return bool
622 */
623 protected function is_filtered_by_groups(array $groups): bool {
624 static $groupsavailablecount = null;
625
626 if (empty($groups)) {
627 return false;
628 }
629
630 // Find total number of options available (groups plus 'no groups'), if not already fetched.
631 if (is_null($groupsavailablecount)) {
632 $groupsavailablecount = 1 + count(groups_get_activity_allowed_groups($this->cm));
633 }
634
635 return (count($groups) < $groupsavailablecount);
636 }
6388b657 637}