MDL-66695 forumreport_summary: Added handling for all groups view count
[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 {
163 $fullname = $data->firstname . ' ' . $data->lastname;
164
165 return $fullname;
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
380 // Define base SQL query format.
381 // Ignores private replies as they are not visible to all participants.
382 $this->sql->basefields = ' ue.userid AS userid,
383 e.courseid AS courseid,
384 f.id as forumid,
385 SUM(CASE WHEN p.parent = 0 THEN 1 ELSE 0 END) AS postcount,
386 SUM(CASE WHEN p.parent != 0 THEN 1 ELSE 0 END) AS replycount,
387 u.firstname,
d0e125cf 388 u.lastname,
382168a0
MH
389 SUM(CASE WHEN att.attcount IS NULL THEN 0 ELSE att.attcount END) AS attachmentcount,
390 MIN(p.created) AS earliestpost,
391 MAX(p.created) AS latestpost';
6388b657
MH
392
393 $this->sql->basefromjoins = ' {enrol} e
394 JOIN {user_enrolments} ue ON ue.enrolid = e.id
395 JOIN {user} u ON u.id = ue.userid
396 JOIN {forum} f ON f.course = e.courseid
397 JOIN {forum_discussions} d ON d.forum = f.id
398 LEFT JOIN {forum_posts} p ON p.discussion = d.id
399 AND p.userid = ue.userid
d0e125cf
MH
400 AND p.privatereplyto = 0
401 LEFT JOIN (
402 SELECT COUNT(fi.id) AS attcount, fi.itemid AS postid, fi.userid
403 FROM {files} fi
404 WHERE fi.component = :component
405 AND fi.filesize > 0
406 GROUP BY fi.itemid, fi.userid
407 ) att ON att.postid = p.id
408 AND att.userid = ue.userid';
6388b657
MH
409
410 $this->sql->basewhere = 'e.courseid = :courseid';
411
412 $this->sql->basegroupby = 'ue.userid, e.courseid, f.id, u.firstname, u.lastname';
413
235a6db7
DM
414 if ($this->logreader) {
415 $this->fill_log_summary_temp_table($this->context->id);
416
417 $this->sql->basefields .= ', CASE WHEN tmp.viewcount IS NOT NULL THEN tmp.viewcount ELSE 0 END AS viewcount';
418 $this->sql->basefromjoins .= ' LEFT JOIN {' . self::LOG_SUMMARY_TEMP_TABLE . '} tmp ON tmp.userid = u.id';
419 $this->sql->basegroupby .= ', tmp.viewcount';
420 }
421
d0e125cf
MH
422 $this->sql->params = [
423 'component' => 'mod_forum',
424 'courseid' => $this->courseid,
425 ];
6388b657
MH
426
427 // Handle if a user is limited to viewing their own summary.
428 if (!empty($this->userid)) {
429 $this->sql->basewhere .= ' AND ue.userid = :userid';
430 $this->sql->params['userid'] = $this->userid;
431 }
432
433 // Filter values will be populated separately where required.
434 $this->sql->filterfields = '';
435 $this->sql->filterfromjoins = '';
436 $this->sql->filterwhere = '';
437 $this->sql->filtergroupby = '';
438 }
439
440 /**
441 * Overriding the parent method because it should not be used here.
442 * Filters are applied, so the structure of $this->sql is now different to the way this is set up in the parent.
443 *
444 * @param string $fields Unused.
445 * @param string $from Unused.
446 * @param string $where Unused.
447 * @param array $params Unused.
448 * @return void.
449 *
450 * @throws coding_exception
451 */
452 public function set_sql($fields, $from, $where, array $params = []) {
453 throw new coding_exception('The set_sql method should not be used by the summary_table class.');
454 }
455
456 /**
457 * Convenience method to call a number of methods for you to display the table.
458 * Overrides the parent so SQL for filters is handled.
459 *
460 * @param int $pagesize Number of rows to fetch.
461 * @param bool $useinitialsbar Whether to include the initials bar with the table.
462 * @param string $downloadhelpbutton Unused.
463 *
464 * @return void.
465 */
466 public function out($pagesize, $useinitialsbar, $downloadhelpbutton = ''): void {
467 global $DB;
468
b29de56d
MH
469 // If there is nothing to display, print the relevant string and return, no further action is required.
470 if ($this->nothingtodisplay) {
471 $this->print_nothing_to_display();
472 return;
473 }
474
6388b657
MH
475 if (!$this->columns) {
476 $sql = $this->get_full_sql();
477
478 $onerow = $DB->get_record_sql($sql, $this->sql->params, IGNORE_MULTIPLE);
479
480 // If columns is not set, define columns as the keys of the rows returned from the db.
481 $this->define_columns(array_keys((array)$onerow));
482 $this->define_headers(array_keys((array)$onerow));
483 }
484
485 $this->setup();
486 $this->query_db($pagesize, $useinitialsbar);
487 $this->build_table();
488 $this->close_recordset();
489 $this->finish_output();
490 }
491
b29de56d
MH
492 /**
493 * Apply the relevant filters to the report.
494 *
495 * @param array $filters Report filters in the format 'type' => [values].
496 * @return void.
497 */
498 protected function apply_filters(array $filters): void {
499 // Apply the forums filter.
500 $this->add_filter(self::FILTER_FORUM, $filters['forums']);
501
502 // Apply groups filter.
503 $this->add_filter(self::FILTER_GROUPS, $filters['groups']);
504 }
505
6388b657
MH
506 /**
507 * Prepares a complete SQL statement from the base query and any filters defined.
508 *
509 * @param bool $fullselect Whether to select all relevant columns.
510 * False selects a count only (used to calculate pagination).
511 * @return string The complete SQL statement.
512 */
513 protected function get_full_sql(bool $fullselect = true): string {
514 $selectfields = '';
515 $groupby = '';
516 $orderby = '';
517
518 if ($fullselect) {
519 $selectfields = "{$this->sql->basefields}
520 {$this->sql->filterfields}";
521
522 $groupby = ' GROUP BY ' . $this->sql->basegroupby . $this->sql->filtergroupby;
523
524 if (($sort = $this->get_sql_sort())) {
525 $orderby = " ORDER BY {$sort}";
526 }
527 } else {
528 $selectfields = 'COUNT(DISTINCT(ue.userid))';
529 }
530
531 $sql = "SELECT {$selectfields}
532 FROM {$this->sql->basefromjoins}
533 {$this->sql->filterfromjoins}
534 WHERE {$this->sql->basewhere}
535 {$this->sql->filterwhere}
536 {$groupby}
537 {$orderby}";
538
539 return $sql;
540 }
235a6db7
DM
541
542 /**
543 * Returns an internal and enabled log reader.
544 *
545 * @return \core\log\sql_reader|false
546 */
547 protected function get_internal_log_reader(): ?\core\log\sql_reader {
548 global $DB;
549
550 $readers = get_log_manager()->get_readers('core\log\sql_reader');
551 foreach ($readers as $reader) {
552
553 // If reader is not a sql_internal_table_reader and not legacy store then return.
554 if (!($reader instanceof \core\log\sql_internal_table_reader) && !($reader instanceof logstore_legacy\log\store)) {
555 continue;
556 }
557 $logreader = $reader;
558 }
559
560 if (empty($logreader)) {
561 return null;
562 }
563
564 return $logreader;
565 }
566
567 /**
568 * Fills the log summary temp table.
569 *
570 * @param int $contextid
571 * @return null
572 */
573 protected function fill_log_summary_temp_table(int $contextid) {
574 global $DB;
575
576 $this->create_log_summary_temp_table();
577
578 if ($this->logreader instanceof logstore_legacy\log\store) {
579 $logtable = 'log';
580 // Anonymous actions are never logged in legacy log.
581 $nonanonymous = '';
582 } else {
583 $logtable = $this->logreader->get_internal_log_table_name();
584 $nonanonymous = 'AND anonymous = 0';
585 }
586 $params = ['contextid' => $contextid];
587 $sql = "INSERT INTO {" . self::LOG_SUMMARY_TEMP_TABLE . "} (userid, viewcount)
588 SELECT userid, COUNT(*) AS viewcount
589 FROM {" . $logtable . "}
590 WHERE contextid = :contextid
591 $nonanonymous
592 GROUP BY userid";
593 $DB->execute($sql, $params);
594 }
595
596 /**
597 * Creates a temp table to store summary data from the log table for this request.
598 *
599 * @return null
600 */
601 protected function create_log_summary_temp_table() {
602 global $DB;
603
604 $dbman = $DB->get_manager();
605 $temptablename = self::LOG_SUMMARY_TEMP_TABLE;
606 $xmldbtable = new \xmldb_table($temptablename);
607 $xmldbtable->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null);
608 $xmldbtable->add_field('viewcount', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null);
609 $xmldbtable->add_key('primary', XMLDB_KEY_PRIMARY, array('userid'));
610
611 $dbman->create_temp_table($xmldbtable);
612 }
9eebd58a
MH
613
614 /**
615 * Check whether the groups filter will be applied by checking whether the number of groups selected
616 * matches the total number of options available (all groups plus no groups option).
617 *
618 * @param array $groups The group IDs selected.
619 * @return bool
620 */
621 protected function is_filtered_by_groups(array $groups): bool {
622 static $groupsavailablecount = null;
623
624 if (empty($groups)) {
625 return false;
626 }
627
628 // Find total number of options available (groups plus 'no groups'), if not already fetched.
629 if (is_null($groupsavailablecount)) {
630 $groupsavailablecount = 1 + count(groups_get_activity_allowed_groups($this->cm));
631 }
632
633 return (count($groups) < $groupsavailablecount);
634 }
6388b657 635}