MDL-33788 completion: SQL optimisation
[moodle.git] / lib / completion / cron.php
CommitLineData
2be4d090 1<?php
2be4d090
MD
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
2be4d090
MD
17/**
18 * Cron job for reviewing and aggregating course completion criteria
19 *
836375ec
SH
20 * @package core_completion
21 * @category completion
2be4d090 22 * @copyright 2009 Catalyst IT Ltd
836375ec
SH
23 * @author Aaron Barnes <aaronb@catalyst.net.nz>
24 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
2be4d090 25 */
2be4d090 26
836375ec 27defined('MOODLE_INTERNAL') || die();
f55ff38a 28require_once($CFG->libdir.'/completionlib.php');
2be4d090
MD
29
30/**
31 * Update user's course completion statuses
32 *
836375ec
SH
33 * First update all criteria completions, then aggregate all criteria completions
34 * and update overall course completions
2be4d090
MD
35 */
36function completion_cron() {
37
38 completion_cron_mark_started();
39
40 completion_cron_criteria();
41
42 completion_cron_completions();
43}
44
45/**
46 * Mark users as started if the config option is set
47 *
836375ec 48 * @return void
2be4d090
MD
49 */
50function completion_cron_mark_started() {
51 global $CFG, $DB;
52
53 if (debugging()) {
54 mtrace('Marking users as started');
55 }
56
1c6ed505 57 if (!empty($CFG->gradebookroles)) {
58 $roles = ' AND ra.roleid IN ('.$CFG->gradebookroles.')';
24a3b341
AB
59 } else {
60 // This causes it to default to everyone (if there is no student role)
61 $roles = '';
62 }
63
89482538
AB
64 /**
65 * A quick explaination of this horrible looking query
66 *
67 * It's purpose is to locate all the active participants
68 * of a course with course completion enabled.
69 *
70 * We also only want the users with no course_completions
71 * record as this functions job is to create the missing
72 * ones :)
73 *
74 * We want to record the user's enrolment start time for the
75 * course. This gets tricky because there can be multiple
76 * enrolment plugins active in a course, hence the possibility
77 * of multiple records for each couse/user in the results
78 */
2be4d090 79 $sql = "
89482538 80 SELECT
2be4d090 81 c.id AS course,
89482538 82 u.id AS userid,
2be4d090 83 crc.id AS completionid,
24a3b341
AB
84 ue.timestart AS timeenrolled,
85 ue.timecreated
2be4d090 86 FROM
89482538
AB
87 {user} u
88 INNER JOIN
89 {user_enrolments} ue
90 ON ue.userid = u.id
2be4d090 91 INNER JOIN
89482538
AB
92 {enrol} e
93 ON e.id = ue.enrolid
2be4d090 94 INNER JOIN
89482538
AB
95 {course} c
96 ON c.id = e.courseid
24a3b341
AB
97 INNER JOIN
98 {role_assignments} ra
99 ON ra.userid = u.id
2be4d090
MD
100 LEFT JOIN
101 {course_completions} crc
102 ON crc.course = c.id
89482538 103 AND crc.userid = u.id
2be4d090 104 WHERE
89482538 105 c.enablecompletion = 1
2be4d090 106 AND crc.timeenrolled IS NULL
89482538
AB
107 AND ue.status = 0
108 AND e.status = 0
109 AND u.deleted = 0
110 AND ue.timestart < ?
111 AND (ue.timeend > ? OR ue.timeend = 0)
24a3b341 112 $roles
2be4d090
MD
113 ORDER BY
114 course,
115 userid
116 ";
117
89482538 118 $now = time();
419178d7
EL
119 $rs = $DB->get_recordset_sql($sql, array($now, $now, $now, $now));
120
121 // Check if result is empty
122 if (!$rs->valid()) {
123 $rs->close(); // Not going to iterate (but exit), close rs
2be4d090
MD
124 return;
125 }
126
89482538
AB
127 /**
128 * An explaination of the following loop
129 *
130 * We are essentially doing a group by in the code here (as I can't find
131 * a decent way of doing it in the sql).
132 *
133 * Since there can be multiple enrolment plugins for each course, we can have
134 * multiple rows for each particpant in the query result. This isn't really
135 * a problem until you combine it with the fact that the enrolment plugins
136 * can save the enrol start time in either timestart or timeenrolled.
137 *
138 * The purpose of this loop is to find the earliest enrolment start time for
139 * each participant in each course.
140 */
141 $prev = null;
142 while ($rs->valid() || $prev) {
143
144 $current = $rs->current();
145
146 if (!isset($current->course)) {
147 $current = false;
148 }
149 else {
150 // Not all enrol plugins fill out timestart correctly, so use whichever
151 // is non-zero
24a3b341 152 $current->timeenrolled = max($current->timecreated, $current->timeenrolled);
2be4d090
MD
153 }
154
89482538
AB
155 // If we are at the last record,
156 // or we aren't at the first and the record is for a diff user/course
157 if ($prev &&
158 (!$rs->valid() ||
159 ($current->course != $prev->course || $current->userid != $prev->userid))) {
160
161 $completion = new completion_completion();
162 $completion->userid = $prev->userid;
163 $completion->course = $prev->course;
164 $completion->timeenrolled = (string) $prev->timeenrolled;
165 $completion->timestarted = 0;
24a3b341 166 $completion->reaggregate = time();
89482538
AB
167
168 if ($prev->completionid) {
169 $completion->id = $prev->completionid;
170 }
2be4d090 171
89482538
AB
172 $completion->mark_enrolled();
173
174 if (debugging()) {
175 mtrace('Marked started user '.$prev->userid.' in course '.$prev->course);
176 }
2be4d090 177 }
89482538
AB
178 // Else, if this record is for the same user/course
179 elseif ($prev && $current) {
180 // Use oldest timeenrolled
181 $current->timeenrolled = min($current->timeenrolled, $prev->timeenrolled);
182 }
183
184 // Move current record to previous
185 $prev = $current;
186
187 // Move to next record
188 $rs->next();
2be4d090
MD
189 }
190
191 $rs->close();
192}
193
194/**
195 * Run installed criteria's data aggregation methods
196 *
197 * Loop through each installed criteria and run the
198 * cron() method if it exists
199 *
836375ec 200 * @return void
2be4d090
MD
201 */
202function completion_cron_criteria() {
203
204 // Process each criteria type
205 global $CFG, $COMPLETION_CRITERIA_TYPES;
206
207 foreach ($COMPLETION_CRITERIA_TYPES as $type) {
208
209 $object = 'completion_criteria_'.$type;
210 require_once $CFG->libdir.'/completion/'.$object.'.php';
211
212 $class = new $object();
213
214 // Run the criteria type's cron method, if it has one
215 if (method_exists($class, 'cron')) {
216
217 if (debugging()) {
218 mtrace('Running '.$object.'->cron()');
219 }
220 $class->cron();
221 }
222 }
223}
224
225/**
226 * Aggregate each user's criteria completions
2be4d090
MD
227 */
228function completion_cron_completions() {
229 global $DB;
230
231 if (debugging()) {
232 mtrace('Aggregating completions');
233 }
234
235 // Save time started
236 $timestarted = time();
237
238 // Grab all criteria and their associated criteria completions
239 $sql = '
240 SELECT DISTINCT
241 c.id AS course,
242 cr.id AS criteriaid,
24a3b341 243 crc.userid AS userid,
2be4d090
MD
244 cr.criteriatype AS criteriatype,
245 cc.timecompleted AS timecompleted
246 FROM
247 {course_completion_criteria} cr
248 INNER JOIN
249 {course} c
250 ON cr.course = c.id
251 INNER JOIN
89482538
AB
252 {course_completions} crc
253 ON crc.course = c.id
2be4d090
MD
254 LEFT JOIN
255 {course_completion_crit_compl} cc
256 ON cc.criteriaid = cr.id
89482538 257 AND crc.userid = cc.userid
2be4d090 258 WHERE
89482538 259 c.enablecompletion = 1
2be4d090
MD
260 AND crc.timecompleted IS NULL
261 AND crc.reaggregate > 0
24a3b341 262 AND crc.reaggregate < :timestarted
2be4d090
MD
263 ORDER BY
264 course,
265 userid
266 ';
267
419178d7
EL
268 $rs = $DB->get_recordset_sql($sql, array('timestarted' => $timestarted));
269
2be4d090 270 // Check if result is empty
419178d7
EL
271 if (!$rs->valid()) {
272 $rs->close(); // Not going to iterate (but exit), close rs
2be4d090
MD
273 return;
274 }
275
276 $current_user = null;
277 $current_course = null;
278 $completions = array();
279
280 while (1) {
281
282 // Grab records for current user/course
283 foreach ($rs as $record) {
284 // If we are still grabbing the same users completions
285 if ($record->userid === $current_user && $record->course === $current_course) {
286 $completions[$record->criteriaid] = $record;
287 } else {
288 break;
289 }
290 }
291
292 // Aggregate
293 if (!empty($completions)) {
294
295 if (debugging()) {
296 mtrace('Aggregating completions for user '.$current_user.' in course '.$current_course);
297 }
298
299 // Get course info object
300 $info = new completion_info((object)array('id' => $current_course));
301
302 // Setup aggregation
303 $overall = $info->get_aggregation_method();
304 $activity = $info->get_aggregation_method(COMPLETION_CRITERIA_TYPE_ACTIVITY);
305 $prerequisite = $info->get_aggregation_method(COMPLETION_CRITERIA_TYPE_COURSE);
306 $role = $info->get_aggregation_method(COMPLETION_CRITERIA_TYPE_ROLE);
307
308 $overall_status = null;
309 $activity_status = null;
310 $prerequisite_status = null;
311 $role_status = null;
312
313 // Get latest timecompleted
314 $timecompleted = null;
315
316 // Check each of the criteria
317 foreach ($completions as $params) {
318 $timecompleted = max($timecompleted, $params->timecompleted);
319
dbfcf440 320 $completion = new completion_criteria_completion((array)$params, false);
2be4d090
MD
321
322 // Handle aggregation special cases
323 if ($params->criteriatype == COMPLETION_CRITERIA_TYPE_ACTIVITY) {
324 completion_cron_aggregate($activity, $completion->is_complete(), $activity_status);
325 } else if ($params->criteriatype == COMPLETION_CRITERIA_TYPE_COURSE) {
326 completion_cron_aggregate($prerequisite, $completion->is_complete(), $prerequisite_status);
327 } else if ($params->criteriatype == COMPLETION_CRITERIA_TYPE_ROLE) {
328 completion_cron_aggregate($role, $completion->is_complete(), $role_status);
329 } else {
330 completion_cron_aggregate($overall, $completion->is_complete(), $overall_status);
331 }
332 }
333
334 // Include role criteria aggregation in overall aggregation
335 if ($role_status !== null) {
336 completion_cron_aggregate($overall, $role_status, $overall_status);
337 }
338
339 // Include activity criteria aggregation in overall aggregation
340 if ($activity_status !== null) {
341 completion_cron_aggregate($overall, $activity_status, $overall_status);
342 }
343
344 // Include prerequisite criteria aggregation in overall aggregation
345 if ($prerequisite_status !== null) {
346 completion_cron_aggregate($overall, $prerequisite_status, $overall_status);
347 }
348
349 // If aggregation status is true, mark course complete for user
350 if ($overall_status) {
351 if (debugging()) {
352 mtrace('Marking complete');
353 }
354
355 $ccompletion = new completion_completion(array('course' => $params->course, 'userid' => $params->userid));
356 $ccompletion->mark_complete($timecompleted);
357 }
358 }
359
360 // If this is the end of the recordset, break the loop
361 if (!$rs->valid()) {
362 $rs->close();
363 break;
364 }
365
366 // New/next user, update user details, reset completions
367 $current_user = $record->userid;
368 $current_course = $record->course;
369 $completions = array();
370 $completions[$record->criteriaid] = $record;
371 }
372
373 // Mark all users as aggregated
374 $sql = "
375 UPDATE
376 {course_completions}
377 SET
378 reaggregate = 0
379 WHERE
7ba1c665
AB
380 reaggregate < :timestarted
381 AND reaggregate > 0
2be4d090
MD
382 ";
383
7ba1c665 384 $DB->execute($sql, array('timestarted' => $timestarted));
2be4d090
MD
385}
386
387/**
388 * Aggregate criteria status's as per configured aggregation method
389 *
390 * @param int $method COMPLETION_AGGREGATION_* constant
391 * @param bool $data Criteria completion status
392 * @param bool|null $state Aggregation state
2be4d090
MD
393 */
394function completion_cron_aggregate($method, $data, &$state) {
395 if ($method == COMPLETION_AGGREGATION_ALL) {
396 if ($data && $state !== false) {
397 $state = true;
398 } else {
399 $state = false;
400 }
401 } elseif ($method == COMPLETION_AGGREGATION_ANY) {
402 if ($data) {
403 $state = true;
404 } else if (!$data && $state === null) {
405 $state = false;
406 }
407 }
408}