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