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