MDL-66268 forumreport_summary: Add filters handling incl groups filter
[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
6388b657
MH
47 /** @var \stdClass The various SQL segments that will be combined to form queries to fetch various information. */
48 public $sql;
49
50 /** @var int The number of rows to be displayed per page. */
51 protected $perpage = 25;
52
53 /** @var int The course ID being reported on. */
54 protected $courseid;
55
56 /** @var int The forum ID being reported on. */
57 protected $forumid;
58
b29de56d
MH
59 /** @var \stdClass The course module object of the forum being reported on. */
60 protected $cm;
61
6388b657
MH
62 /**
63 * @var int The user ID if only one user's summary will be generated.
64 * This will apply to users without permission to view others' summaries.
65 */
66 protected $userid;
67
b29de56d
MH
68 /**
69 * @var bool Whether the table should be overridden to show the 'nothing to display' message.
70 * False unless checks confirm there will be nothing to display.
71 */
72 protected $nothingtodisplay = false;
73
6388b657
MH
74 /**
75 * Forum report table constructor.
76 *
77 * @param int $courseid The ID of the course the forum(s) exist within.
b29de56d 78 * @param array $filters Report filters in the format 'type' => [values].
6388b657 79 */
b29de56d 80 public function __construct(int $courseid, array $filters) {
6388b657
MH
81 global $USER;
82
b29de56d
MH
83 $forumid = $filters['forums'][0];
84
6388b657
MH
85 parent::__construct("summaryreport_{$courseid}_{$forumid}");
86
b29de56d
MH
87 $this->cm = get_coursemodule_from_instance('forum', $forumid, $courseid);
88 $context = \context_module::instance($this->cm->id);
6388b657
MH
89
90 // Only show their own summary unless they have permission to view all.
91 if (!has_capability('forumreport/summary:viewall', $context)) {
92 $this->userid = $USER->id;
93 }
94
95 $this->courseid = intval($courseid);
96
97 $columnheaders = [
98 'fullname' => get_string('fullnameuser'),
99 'postcount' => get_string('postcount', 'forumreport_summary'),
100 'replycount' => get_string('replycount', 'forumreport_summary'),
d0e125cf 101 'attachmentcount' => get_string('attachmentcount', 'forumreport_summary'),
382168a0
MH
102 'earliestpost' => get_string('earliestpost', 'forumreport_summary'),
103 'latestpost' => get_string('latestpost', 'forumreport_summary'),
6388b657
MH
104 ];
105
106 $this->define_columns(array_keys($columnheaders));
107 $this->define_headers(array_values($columnheaders));
108
109 // Define configs.
110 $this->define_table_configs();
111
112 // Define the basic SQL data and object format.
113 $this->define_base_sql();
114
b29de56d
MH
115 // Apply relevant filters.
116 $this->apply_filters($filters);
6388b657
MH
117 }
118
119 /**
120 * Provides the string name of each filter type.
121 *
122 * @param int $filtertype Type of filter
123 * @return string Name of the filter
124 */
125 public function get_filter_name(int $filtertype): string {
126 $filternames = [
127 self::FILTER_FORUM => 'Forum',
b29de56d 128 self::FILTER_GROUPS => 'Groups',
6388b657
MH
129 ];
130
131 return $filternames[$filtertype];
132 }
133
134 /**
135 * Generate the fullname column.
136 *
137 * @param \stdClass $data The row data.
138 * @return string User's full name.
139 */
140 public function col_fullname($data): string {
141 $fullname = $data->firstname . ' ' . $data->lastname;
142
143 return $fullname;
144 }
145
146 /**
147 * Generate the postcount column.
148 *
149 * @param \stdClass $data The row data.
150 * @return int number of discussion posts made by user.
151 */
152 public function col_postcount(\stdClass $data): int {
153 return $data->postcount;
154 }
155
156 /**
157 * Generate the replycount column.
158 *
159 * @param \stdClass $data The row data.
160 * @return int number of replies made by user.
161 */
162 public function col_replycount(\stdClass $data): int {
163 return $data->replycount;
164 }
165
d0e125cf
MH
166 /**
167 * Generate the attachmentcount column.
168 *
169 * @param \stdClass $data The row data.
170 * @return int number of files attached to posts by user.
171 */
172 public function col_attachmentcount(\stdClass $data): int {
173 return $data->attachmentcount;
174 }
175
382168a0
MH
176 /**
177 * Generate the earliestpost column.
178 *
179 * @param \stdClass $data The row data.
180 * @return string Timestamp of user's earliest post, or a dash if no posts exist.
181 */
182 public function col_earliestpost(\stdClass $data): string {
183 global $USER;
184
185 return empty($data->earliestpost) ? '-' : userdate($data->earliestpost, "", \core_date::get_user_timezone($USER));
186 }
187
188 /**
189 * Generate the latestpost column.
190 *
191 * @param \stdClass $data The row data.
192 * @return string Timestamp of user's most recent post, or a dash if no posts exist.
193 */
194 public function col_latestpost(\stdClass $data): string {
195 global $USER;
196
197 return empty($data->latestpost) ? '-' : userdate($data->latestpost, "", \core_date::get_user_timezone($USER));
198 }
199
6388b657
MH
200 /**
201 * Override the default implementation to set a decent heading level.
202 *
203 * @return void.
204 */
205 public function print_nothing_to_display(): void {
206 global $OUTPUT;
207
208 echo $OUTPUT->heading(get_string('nothingtodisplay'), 4);
209 }
210
211 /**
212 * Query the db. Store results in the table object for use by build_table.
213 *
214 * @param int $pagesize Size of page for paginated displayed table.
215 * @param bool $useinitialsbar Overridden but unused.
216 * @return void
217 */
218 public function query_db($pagesize, $useinitialsbar = false): void {
219 global $DB;
220
221 // Set up pagination if not downloading the whole report.
222 if (!$this->is_downloading()) {
223 $totalsql = $this->get_full_sql(false);
224
225 // Set up pagination.
226 $totalrows = $DB->count_records_sql($totalsql, $this->sql->params);
227 $this->pagesize($pagesize, $totalrows);
228 }
229
230 // Fetch the data.
231 $sql = $this->get_full_sql();
232
233 // Only paginate when not downloading.
234 if (!$this->is_downloading()) {
235 $this->rawdata = $DB->get_records_sql($sql, $this->sql->params, $this->get_page_start(), $this->get_page_size());
236 } else {
237 $this->rawdata = $DB->get_records_sql($sql, $this->sql->params);
238 }
239 }
240
241 /**
242 * Adds the relevant SQL to apply a filter to the report.
243 *
244 * @param int $filtertype Filter type as defined by class constants.
245 * @param array $values Optional array of values passed into the filter type.
246 * @return void
247 * @throws coding_exception
248 */
249 public function add_filter(int $filtertype, array $values = []): void {
b29de56d
MH
250 global $DB;
251
6388b657
MH
252 $paramcounterror = false;
253
254 switch($filtertype) {
255 case self::FILTER_FORUM:
256 // Requires exactly one forum ID.
257 if (count($values) != 1) {
258 $paramcounterror = true;
259 } else {
260 // No select fields required - displayed in title.
261 // No extra joins required, forum is already joined.
262 $this->sql->filterwhere .= ' AND f.id = :forumid';
263 $this->sql->params['forumid'] = $values[0];
264 }
265
266 break;
267
b29de56d
MH
268 case self::FILTER_GROUPS:
269 // Find total number of options available (groups plus 'no groups').
270 $availablegroups = groups_get_activity_allowed_groups($this->cm);
271 $alloptionscount = 1 + count($availablegroups);
272
273 // Skip adding filter if not applied, or all options are selected.
274 if (!empty($values) && count($values) < $alloptionscount) {
275 // Include users without groups if that option (-1) is selected.
276 $nonekey = array_search(-1, $values, true);
277
278 // Users within selected groups or not in any groups are included.
279 if ($nonekey !== false && count($values) > 1) {
280 unset($values[$nonekey]);
281 list($groupidin, $groupidparams) = $DB->get_in_or_equal($values, SQL_PARAMS_NAMED, 'groupid');
282
283 // No select fields required.
284 // No joins required (handled by where to prevent data duplication).
285 $this->sql->filterwhere .= "
286 AND (u.id =
287 (SELECT gm.userid
288 FROM {groups_members} gm
289 WHERE gm.userid = u.id
290 AND gm.groupid {$groupidin}
291 GROUP BY gm.userid
292 LIMIT 1)
293 OR
294 (SELECT nogm.userid
295 FROM mdl_groups_members nogm
296 WHERE nogm.userid = u.id
297 GROUP BY nogm.userid
298 LIMIT 1)
299 IS NULL)";
300 $this->sql->params += $groupidparams;
301
302 } else if ($nonekey !== false) {
303 // Only users within no groups are included.
304 unset($values[$nonekey]);
305
306 // No select fields required.
307 $this->sql->filterfromjoins .= " LEFT JOIN {groups_members} nogm ON nogm.userid = u.id";
308 $this->sql->filterwhere .= " AND nogm.id IS NULL";
309
310 } else if (!empty($values)) {
311 // Only users within selected groups are included.
312 list($groupidin, $groupidparams) = $DB->get_in_or_equal($values, SQL_PARAMS_NAMED, 'groupid');
313
314 // No select fields required.
315 // No joins required (handled by where to prevent data duplication).
316 $this->sql->filterwhere .= "
317 AND u.id = (
318 SELECT gm.userid
319 FROM {groups_members} gm
320 WHERE gm.userid = u.id
321 AND gm.groupid {$groupidin}
322 GROUP BY gm.userid
323 LIMIT 1)";
324 $this->sql->params += $groupidparams;
325 }
326 }
327
328 break;
329
6388b657
MH
330 default:
331 throw new coding_exception("Report filter type '{$filtertype}' not found.");
332 break;
333 }
334
335 if ($paramcounterror) {
336 $filtername = $this->get_filter_name($filtertype);
337 throw new coding_exception("An invalid number of values have been passed for the '{$filtername}' filter.");
338 }
339 }
340
341 /**
342 * Define various table config options.
343 *
344 * @return void.
345 */
346 protected function define_table_configs(): void {
347 $this->collapsible(false);
348 $this->sortable(true, 'firstname', SORT_ASC);
349 $this->pageable(true);
350 $this->no_sorting('select');
d0e125cf 351 $this->set_attribute('id', 'forumreport_summary_table');
6388b657
MH
352 }
353
354 /**
355 * Define the object to store all for the table SQL and initialises the base SQL required.
356 *
357 * @return void.
358 */
359 protected function define_base_sql(): void {
360 $this->sql = new \stdClass();
361
362 // Define base SQL query format.
363 // Ignores private replies as they are not visible to all participants.
364 $this->sql->basefields = ' ue.userid AS userid,
365 e.courseid AS courseid,
366 f.id as forumid,
367 SUM(CASE WHEN p.parent = 0 THEN 1 ELSE 0 END) AS postcount,
368 SUM(CASE WHEN p.parent != 0 THEN 1 ELSE 0 END) AS replycount,
369 u.firstname,
d0e125cf 370 u.lastname,
382168a0
MH
371 SUM(CASE WHEN att.attcount IS NULL THEN 0 ELSE att.attcount END) AS attachmentcount,
372 MIN(p.created) AS earliestpost,
373 MAX(p.created) AS latestpost';
6388b657
MH
374
375 $this->sql->basefromjoins = ' {enrol} e
376 JOIN {user_enrolments} ue ON ue.enrolid = e.id
377 JOIN {user} u ON u.id = ue.userid
378 JOIN {forum} f ON f.course = e.courseid
379 JOIN {forum_discussions} d ON d.forum = f.id
380 LEFT JOIN {forum_posts} p ON p.discussion = d.id
381 AND p.userid = ue.userid
d0e125cf
MH
382 AND p.privatereplyto = 0
383 LEFT JOIN (
384 SELECT COUNT(fi.id) AS attcount, fi.itemid AS postid, fi.userid
385 FROM {files} fi
386 WHERE fi.component = :component
387 AND fi.filesize > 0
388 GROUP BY fi.itemid, fi.userid
389 ) att ON att.postid = p.id
390 AND att.userid = ue.userid';
6388b657
MH
391
392 $this->sql->basewhere = 'e.courseid = :courseid';
393
394 $this->sql->basegroupby = 'ue.userid, e.courseid, f.id, u.firstname, u.lastname';
395
d0e125cf
MH
396 $this->sql->params = [
397 'component' => 'mod_forum',
398 'courseid' => $this->courseid,
399 ];
6388b657
MH
400
401 // Handle if a user is limited to viewing their own summary.
402 if (!empty($this->userid)) {
403 $this->sql->basewhere .= ' AND ue.userid = :userid';
404 $this->sql->params['userid'] = $this->userid;
405 }
406
407 // Filter values will be populated separately where required.
408 $this->sql->filterfields = '';
409 $this->sql->filterfromjoins = '';
410 $this->sql->filterwhere = '';
411 $this->sql->filtergroupby = '';
412 }
413
414 /**
415 * Overriding the parent method because it should not be used here.
416 * Filters are applied, so the structure of $this->sql is now different to the way this is set up in the parent.
417 *
418 * @param string $fields Unused.
419 * @param string $from Unused.
420 * @param string $where Unused.
421 * @param array $params Unused.
422 * @return void.
423 *
424 * @throws coding_exception
425 */
426 public function set_sql($fields, $from, $where, array $params = []) {
427 throw new coding_exception('The set_sql method should not be used by the summary_table class.');
428 }
429
430 /**
431 * Convenience method to call a number of methods for you to display the table.
432 * Overrides the parent so SQL for filters is handled.
433 *
434 * @param int $pagesize Number of rows to fetch.
435 * @param bool $useinitialsbar Whether to include the initials bar with the table.
436 * @param string $downloadhelpbutton Unused.
437 *
438 * @return void.
439 */
440 public function out($pagesize, $useinitialsbar, $downloadhelpbutton = ''): void {
441 global $DB;
442
b29de56d
MH
443 // If there is nothing to display, print the relevant string and return, no further action is required.
444 if ($this->nothingtodisplay) {
445 $this->print_nothing_to_display();
446 return;
447 }
448
6388b657
MH
449 if (!$this->columns) {
450 $sql = $this->get_full_sql();
451
452 $onerow = $DB->get_record_sql($sql, $this->sql->params, IGNORE_MULTIPLE);
453
454 // If columns is not set, define columns as the keys of the rows returned from the db.
455 $this->define_columns(array_keys((array)$onerow));
456 $this->define_headers(array_keys((array)$onerow));
457 }
458
459 $this->setup();
460 $this->query_db($pagesize, $useinitialsbar);
461 $this->build_table();
462 $this->close_recordset();
463 $this->finish_output();
464 }
465
b29de56d
MH
466 /**
467 * Apply the relevant filters to the report.
468 *
469 * @param array $filters Report filters in the format 'type' => [values].
470 * @return void.
471 */
472 protected function apply_filters(array $filters): void {
473 // Apply the forums filter.
474 $this->add_filter(self::FILTER_FORUM, $filters['forums']);
475
476 // Apply groups filter.
477 $this->add_filter(self::FILTER_GROUPS, $filters['groups']);
478 }
479
6388b657
MH
480 /**
481 * Prepares a complete SQL statement from the base query and any filters defined.
482 *
483 * @param bool $fullselect Whether to select all relevant columns.
484 * False selects a count only (used to calculate pagination).
485 * @return string The complete SQL statement.
486 */
487 protected function get_full_sql(bool $fullselect = true): string {
488 $selectfields = '';
489 $groupby = '';
490 $orderby = '';
491
492 if ($fullselect) {
493 $selectfields = "{$this->sql->basefields}
494 {$this->sql->filterfields}";
495
496 $groupby = ' GROUP BY ' . $this->sql->basegroupby . $this->sql->filtergroupby;
497
498 if (($sort = $this->get_sql_sort())) {
499 $orderby = " ORDER BY {$sort}";
500 }
501 } else {
502 $selectfields = 'COUNT(DISTINCT(ue.userid))';
503 }
504
505 $sql = "SELECT {$selectfields}
506 FROM {$this->sql->basefromjoins}
507 {$this->sql->filterfromjoins}
508 WHERE {$this->sql->basewhere}
509 {$this->sql->filterwhere}
510 {$groupby}
511 {$orderby}";
512
513 return $sql;
514 }
515}