MDL-66695 forumreport_summary: New viewcount column
[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
DM
117 // Only include viewcount column when no groups filter is applied.
118 if (empty($filters['groups'])) {
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
MH
290 case self::FILTER_GROUPS:
291 // Find total number of options available (groups plus 'no groups').
292 $availablegroups = groups_get_activity_allowed_groups($this->cm);
293 $alloptionscount = 1 + count($availablegroups);
294
295 // Skip adding filter if not applied, or all options are selected.
296 if (!empty($values) && count($values) < $alloptionscount) {
297 // Include users without groups if that option (-1) is selected.
298 $nonekey = array_search(-1, $values, true);
299
300 // Users within selected groups or not in any groups are included.
301 if ($nonekey !== false && count($values) > 1) {
302 unset($values[$nonekey]);
303 list($groupidin, $groupidparams) = $DB->get_in_or_equal($values, SQL_PARAMS_NAMED, 'groupid');
304
305 // No select fields required.
306 // No joins required (handled by where to prevent data duplication).
307 $this->sql->filterwhere .= "
308 AND (u.id =
309 (SELECT gm.userid
310 FROM {groups_members} gm
311 WHERE gm.userid = u.id
312 AND gm.groupid {$groupidin}
313 GROUP BY gm.userid
314 LIMIT 1)
315 OR
316 (SELECT nogm.userid
317 FROM mdl_groups_members nogm
318 WHERE nogm.userid = u.id
319 GROUP BY nogm.userid
320 LIMIT 1)
321 IS NULL)";
322 $this->sql->params += $groupidparams;
323
324 } else if ($nonekey !== false) {
325 // Only users within no groups are included.
326 unset($values[$nonekey]);
327
328 // No select fields required.
329 $this->sql->filterfromjoins .= " LEFT JOIN {groups_members} nogm ON nogm.userid = u.id";
330 $this->sql->filterwhere .= " AND nogm.id IS NULL";
331
332 } else if (!empty($values)) {
333 // Only users within selected groups are included.
334 list($groupidin, $groupidparams) = $DB->get_in_or_equal($values, SQL_PARAMS_NAMED, 'groupid');
335
336 // No select fields required.
337 // No joins required (handled by where to prevent data duplication).
338 $this->sql->filterwhere .= "
339 AND u.id = (
340 SELECT gm.userid
341 FROM {groups_members} gm
342 WHERE gm.userid = u.id
343 AND gm.groupid {$groupidin}
344 GROUP BY gm.userid
345 LIMIT 1)";
346 $this->sql->params += $groupidparams;
347 }
348 }
349
350 break;
351
6388b657
MH
352 default:
353 throw new coding_exception("Report filter type '{$filtertype}' not found.");
354 break;
355 }
356
357 if ($paramcounterror) {
358 $filtername = $this->get_filter_name($filtertype);
359 throw new coding_exception("An invalid number of values have been passed for the '{$filtername}' filter.");
360 }
361 }
362
363 /**
364 * Define various table config options.
365 *
366 * @return void.
367 */
368 protected function define_table_configs(): void {
369 $this->collapsible(false);
370 $this->sortable(true, 'firstname', SORT_ASC);
371 $this->pageable(true);
372 $this->no_sorting('select');
d0e125cf 373 $this->set_attribute('id', 'forumreport_summary_table');
6388b657
MH
374 }
375
376 /**
377 * Define the object to store all for the table SQL and initialises the base SQL required.
378 *
379 * @return void.
380 */
381 protected function define_base_sql(): void {
382 $this->sql = new \stdClass();
383
384 // Define base SQL query format.
385 // Ignores private replies as they are not visible to all participants.
386 $this->sql->basefields = ' ue.userid AS userid,
387 e.courseid AS courseid,
388 f.id as forumid,
389 SUM(CASE WHEN p.parent = 0 THEN 1 ELSE 0 END) AS postcount,
390 SUM(CASE WHEN p.parent != 0 THEN 1 ELSE 0 END) AS replycount,
391 u.firstname,
d0e125cf 392 u.lastname,
382168a0
MH
393 SUM(CASE WHEN att.attcount IS NULL THEN 0 ELSE att.attcount END) AS attachmentcount,
394 MIN(p.created) AS earliestpost,
395 MAX(p.created) AS latestpost';
6388b657
MH
396
397 $this->sql->basefromjoins = ' {enrol} e
398 JOIN {user_enrolments} ue ON ue.enrolid = e.id
399 JOIN {user} u ON u.id = ue.userid
400 JOIN {forum} f ON f.course = e.courseid
401 JOIN {forum_discussions} d ON d.forum = f.id
402 LEFT JOIN {forum_posts} p ON p.discussion = d.id
403 AND p.userid = ue.userid
d0e125cf
MH
404 AND p.privatereplyto = 0
405 LEFT JOIN (
406 SELECT COUNT(fi.id) AS attcount, fi.itemid AS postid, fi.userid
407 FROM {files} fi
408 WHERE fi.component = :component
409 AND fi.filesize > 0
410 GROUP BY fi.itemid, fi.userid
411 ) att ON att.postid = p.id
412 AND att.userid = ue.userid';
6388b657
MH
413
414 $this->sql->basewhere = 'e.courseid = :courseid';
415
416 $this->sql->basegroupby = 'ue.userid, e.courseid, f.id, u.firstname, u.lastname';
417
235a6db7
DM
418 if ($this->logreader) {
419 $this->fill_log_summary_temp_table($this->context->id);
420
421 $this->sql->basefields .= ', CASE WHEN tmp.viewcount IS NOT NULL THEN tmp.viewcount ELSE 0 END AS viewcount';
422 $this->sql->basefromjoins .= ' LEFT JOIN {' . self::LOG_SUMMARY_TEMP_TABLE . '} tmp ON tmp.userid = u.id';
423 $this->sql->basegroupby .= ', tmp.viewcount';
424 }
425
d0e125cf
MH
426 $this->sql->params = [
427 'component' => 'mod_forum',
428 'courseid' => $this->courseid,
429 ];
6388b657
MH
430
431 // Handle if a user is limited to viewing their own summary.
432 if (!empty($this->userid)) {
433 $this->sql->basewhere .= ' AND ue.userid = :userid';
434 $this->sql->params['userid'] = $this->userid;
435 }
436
437 // Filter values will be populated separately where required.
438 $this->sql->filterfields = '';
439 $this->sql->filterfromjoins = '';
440 $this->sql->filterwhere = '';
441 $this->sql->filtergroupby = '';
442 }
443
444 /**
445 * Overriding the parent method because it should not be used here.
446 * Filters are applied, so the structure of $this->sql is now different to the way this is set up in the parent.
447 *
448 * @param string $fields Unused.
449 * @param string $from Unused.
450 * @param string $where Unused.
451 * @param array $params Unused.
452 * @return void.
453 *
454 * @throws coding_exception
455 */
456 public function set_sql($fields, $from, $where, array $params = []) {
457 throw new coding_exception('The set_sql method should not be used by the summary_table class.');
458 }
459
460 /**
461 * Convenience method to call a number of methods for you to display the table.
462 * Overrides the parent so SQL for filters is handled.
463 *
464 * @param int $pagesize Number of rows to fetch.
465 * @param bool $useinitialsbar Whether to include the initials bar with the table.
466 * @param string $downloadhelpbutton Unused.
467 *
468 * @return void.
469 */
470 public function out($pagesize, $useinitialsbar, $downloadhelpbutton = ''): void {
471 global $DB;
472
b29de56d
MH
473 // If there is nothing to display, print the relevant string and return, no further action is required.
474 if ($this->nothingtodisplay) {
475 $this->print_nothing_to_display();
476 return;
477 }
478
6388b657
MH
479 if (!$this->columns) {
480 $sql = $this->get_full_sql();
481
482 $onerow = $DB->get_record_sql($sql, $this->sql->params, IGNORE_MULTIPLE);
483
484 // If columns is not set, define columns as the keys of the rows returned from the db.
485 $this->define_columns(array_keys((array)$onerow));
486 $this->define_headers(array_keys((array)$onerow));
487 }
488
489 $this->setup();
490 $this->query_db($pagesize, $useinitialsbar);
491 $this->build_table();
492 $this->close_recordset();
493 $this->finish_output();
494 }
495
b29de56d
MH
496 /**
497 * Apply the relevant filters to the report.
498 *
499 * @param array $filters Report filters in the format 'type' => [values].
500 * @return void.
501 */
502 protected function apply_filters(array $filters): void {
503 // Apply the forums filter.
504 $this->add_filter(self::FILTER_FORUM, $filters['forums']);
505
506 // Apply groups filter.
507 $this->add_filter(self::FILTER_GROUPS, $filters['groups']);
508 }
509
6388b657
MH
510 /**
511 * Prepares a complete SQL statement from the base query and any filters defined.
512 *
513 * @param bool $fullselect Whether to select all relevant columns.
514 * False selects a count only (used to calculate pagination).
515 * @return string The complete SQL statement.
516 */
517 protected function get_full_sql(bool $fullselect = true): string {
518 $selectfields = '';
519 $groupby = '';
520 $orderby = '';
521
522 if ($fullselect) {
523 $selectfields = "{$this->sql->basefields}
524 {$this->sql->filterfields}";
525
526 $groupby = ' GROUP BY ' . $this->sql->basegroupby . $this->sql->filtergroupby;
527
528 if (($sort = $this->get_sql_sort())) {
529 $orderby = " ORDER BY {$sort}";
530 }
531 } else {
532 $selectfields = 'COUNT(DISTINCT(ue.userid))';
533 }
534
535 $sql = "SELECT {$selectfields}
536 FROM {$this->sql->basefromjoins}
537 {$this->sql->filterfromjoins}
538 WHERE {$this->sql->basewhere}
539 {$this->sql->filterwhere}
540 {$groupby}
541 {$orderby}";
542
543 return $sql;
544 }
235a6db7
DM
545
546 /**
547 * Returns an internal and enabled log reader.
548 *
549 * @return \core\log\sql_reader|false
550 */
551 protected function get_internal_log_reader(): ?\core\log\sql_reader {
552 global $DB;
553
554 $readers = get_log_manager()->get_readers('core\log\sql_reader');
555 foreach ($readers as $reader) {
556
557 // If reader is not a sql_internal_table_reader and not legacy store then return.
558 if (!($reader instanceof \core\log\sql_internal_table_reader) && !($reader instanceof logstore_legacy\log\store)) {
559 continue;
560 }
561 $logreader = $reader;
562 }
563
564 if (empty($logreader)) {
565 return null;
566 }
567
568 return $logreader;
569 }
570
571 /**
572 * Fills the log summary temp table.
573 *
574 * @param int $contextid
575 * @return null
576 */
577 protected function fill_log_summary_temp_table(int $contextid) {
578 global $DB;
579
580 $this->create_log_summary_temp_table();
581
582 if ($this->logreader instanceof logstore_legacy\log\store) {
583 $logtable = 'log';
584 // Anonymous actions are never logged in legacy log.
585 $nonanonymous = '';
586 } else {
587 $logtable = $this->logreader->get_internal_log_table_name();
588 $nonanonymous = 'AND anonymous = 0';
589 }
590 $params = ['contextid' => $contextid];
591 $sql = "INSERT INTO {" . self::LOG_SUMMARY_TEMP_TABLE . "} (userid, viewcount)
592 SELECT userid, COUNT(*) AS viewcount
593 FROM {" . $logtable . "}
594 WHERE contextid = :contextid
595 $nonanonymous
596 GROUP BY userid";
597 $DB->execute($sql, $params);
598 }
599
600 /**
601 * Creates a temp table to store summary data from the log table for this request.
602 *
603 * @return null
604 */
605 protected function create_log_summary_temp_table() {
606 global $DB;
607
608 $dbman = $DB->get_manager();
609 $temptablename = self::LOG_SUMMARY_TEMP_TABLE;
610 $xmldbtable = new \xmldb_table($temptablename);
611 $xmldbtable->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null);
612 $xmldbtable->add_field('viewcount', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null);
613 $xmldbtable->add_key('primary', XMLDB_KEY_PRIMARY, array('userid'));
614
615 $dbman->create_temp_table($xmldbtable);
616 }
6388b657 617}