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