statistics MDL-25822 Fix get_records_sql returning non-unique first column
[moodle.git] / lib / statslib.php
CommitLineData
f3221af9 1<?php
2
b37eac91 3// This file is part of Moodle - http://moodle.org/
4//
5// Moodle is free software: you can redistribute it and/or modify
6// it under the terms of the GNU General Public License as published by
7// the Free Software Foundation, either version 3 of the License, or
8// (at your option) any later version.
9//
10// Moodle is distributed in the hope that it will be useful,
11// but WITHOUT ANY WARRANTY; without even the implied warranty of
12// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13// GNU General Public License for more details.
14//
15// You should have received a copy of the GNU General Public License
16// along with Moodle. If not, see <http://www.gnu.org/licenses/>.
17
18/**
78bfb562
PS
19 * @package core
20 * @subpackage stats
21 * @copyright 1999 onwards Martin Dougiamas {@link http://moodle.com}
22 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
b37eac91 23 */
24
78bfb562
PS
25defined('MOODLE_INTERNAL') || die();
26
a919ae89
PS
27/** THESE CONSTANTS ARE USED FOR THE REPORTING PAGE. */
28
29define('STATS_REPORT_LOGINS',1); // double impose logins and unique logins on a line graph. site course only.
30define('STATS_REPORT_READS',2); // double impose student reads and teacher reads on a line graph.
31define('STATS_REPORT_WRITES',3); // double impose student writes and teacher writes on a line graph.
32define('STATS_REPORT_ACTIVITY',4); // 2+3 added up, teacher vs student.
33define('STATS_REPORT_ACTIVITYBYROLE',5); // all activity, reads vs writes, selected by role.
34
35// user level stats reports.
36define('STATS_REPORT_USER_ACTIVITY',7);
37define('STATS_REPORT_USER_ALLACTIVITY',8);
38define('STATS_REPORT_USER_LOGINS',9);
39define('STATS_REPORT_USER_VIEW',10); // this is the report you see on the user profile.
40
41// admin only ranking stats reports
42define('STATS_REPORT_ACTIVE_COURSES',11);
43define('STATS_REPORT_ACTIVE_COURSES_WEIGHTED',12);
44define('STATS_REPORT_PARTICIPATORY_COURSES',13);
45define('STATS_REPORT_PARTICIPATORY_COURSES_RW',14);
46
47// start after 0 = show dailies.
48define('STATS_TIME_LASTWEEK',1);
49define('STATS_TIME_LAST2WEEKS',2);
50define('STATS_TIME_LAST3WEEKS',3);
51define('STATS_TIME_LAST4WEEKS',4);
52
53// start after 10 = show weeklies
54define('STATS_TIME_LAST2MONTHS',12);
55
56define('STATS_TIME_LAST3MONTHS',13);
57define('STATS_TIME_LAST4MONTHS',14);
58define('STATS_TIME_LAST5MONTHS',15);
59define('STATS_TIME_LAST6MONTHS',16);
60
61// start after 20 = show monthlies
62define('STATS_TIME_LAST7MONTHS',27);
63define('STATS_TIME_LAST8MONTHS',28);
64define('STATS_TIME_LAST9MONTHS',29);
65define('STATS_TIME_LAST10MONTHS',30);
66define('STATS_TIME_LAST11MONTHS',31);
67define('STATS_TIME_LASTYEAR',32);
68
69// different modes for what reports to offer
70define('STATS_MODE_GENERAL',1);
71define('STATS_MODE_DETAILED',2);
72define('STATS_MODE_RANKED',3); // admins only - ranks courses
f3221af9 73
61460dd6 74/**
75 * Print daily cron progress
117bd748 76 * @param string $ident
61460dd6 77 */
78function stats_daily_progress($ident) {
79 static $start = 0;
80 static $init = 0;
f3221af9 81
61460dd6 82 if ($ident == 'init') {
83 $init = $start = time();
84 return;
f3221af9 85 }
86
61460dd6 87 $elapsed = time() - $start;
88 $start = time();
89
90 if (debugging('', DEBUG_ALL)) {
91 mtrace("$ident:$elapsed ", '');
92 } else {
93 mtrace('.', '');
f3221af9 94 }
61460dd6 95}
f3221af9 96
61460dd6 97/**
98 * Execute daily statistics gathering
99 * @param int $maxdays maximum number of days to be processed
100 * @return boolean success
101 */
102function stats_cron_daily($maxdays=1) {
d23a798a 103 global $CFG, $DB;
f3221af9 104
61460dd6 105 $now = time();
f3221af9 106
a919ae89
PS
107 $fpcontext = get_context_instance(CONTEXT_COURSE, SITEID, MUST_EXIST);
108
61460dd6 109 // read last execution date from db
110 if (!$timestart = get_config(NULL, 'statslastdaily')) {
111 $timestart = stats_get_base_daily(stats_get_start_from('daily'));
112 set_config('statslastdaily', $timestart);
113 }
f3221af9 114
790bb5a3 115 // calculate scheduled time
116 $scheduledtime = stats_get_base_daily() + $CFG->statsruntimestarthour*60*60 + $CFG->statsruntimestartminute*60;
117
8660ac6e 118 // Note: This will work fine for sites running cron each 4 hours or less (hopefully, 99.99% of sites). MDL-16709
790bb5a3 119 // check to make sure we're due to run, at least 20 hours after last run
120 if (isset($CFG->statslastexecution) and ((time() - 20*60*60) < $CFG->statslastexecution)) {
121 mtrace("...preventing stats to run, last execution was less than 20 hours ago.");
122 return false;
123 // also check that we are a max of 4 hours after scheduled time, stats won't run after that
124 } else if (time() > $scheduledtime + 4*60*60) {
125 mtrace("...preventing stats to run, more than 4 hours since scheduled time.");
126 return false;
127 } else {
128 set_config('statslastexecution', time()); /// Grab this execution as last one
129 }
130
61460dd6 131 $nextmidnight = stats_get_next_day_start($timestart);
f3221af9 132
61460dd6 133 // are there any days that need to be processed?
134 if ($now < $nextmidnight) {
135 return true; // everything ok and up-to-date
f3221af9 136 }
137
790bb5a3 138
61460dd6 139 $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
f3221af9 140
61460dd6 141 if (!set_cron_lock('statsrunning', $now + $timeout)) {
142 return false;
f3221af9 143 }
61460dd6 144
8660ac6e 145 // first delete entries that should not be there yet
d23a798a 146 $DB->delete_records_select('stats_daily', "timeend > $timestart");
147 $DB->delete_records_select('stats_user_daily', "timeend > $timestart");
61460dd6 148
149 // Read in a few things we'll use later
a919ae89
PS
150 $viewactions = stats_get_action_names('view');
151 $postactions = stats_get_action_names('post');
61460dd6 152
a919ae89
PS
153 $guest = (int)$CFG->siteguest;
154 $guestrole = (int)$CFG->guestroleid;
155 $defaultfproleid = (int)$CFG->defaultfrontpageroleid;
61460dd6 156
157 mtrace("Running daily statistics gathering, starting at $timestart:");
158
f3221af9 159 $days = 0;
61460dd6 160 $failed = false; // failed stats flag
f3221af9 161
61460dd6 162 while ($now > $nextmidnight) {
163 if ($days >= $maxdays) {
164 mtrace("...stopping early, reached maximum number of $maxdays days - will continue next time.");
165 set_cron_lock('statsrunning', null);
166 return false;
167 }
168
169 $days++;
170 @set_time_limit($timeout - 200);
171
172 if ($days > 1) {
173 // move the lock
174 set_cron_lock('statsrunning', time() + $timeout, true);
175 }
176
67ce5270 177 $daystart = time();
178
61460dd6 179 $timesql = "l.time >= $timestart AND l.time < $nextmidnight";
180 $timesql1 = "l1.time >= $timestart AND l1.time < $nextmidnight";
181 $timesql2 = "l2.time >= $timestart AND l2.time < $nextmidnight";
182
183 stats_daily_progress('init');
184
67ce5270 185
186 /// find out if any logs available for this day
187 $sql = "SELECT 'x'
d23a798a 188 FROM {log} l
67ce5270 189 WHERE $timesql";
d23a798a 190 $logspresent = $DB->get_records_sql($sql, null, 0, 1);
67ce5270 191
61460dd6 192 /// process login info first
d23a798a 193 $sql = "INSERT INTO {stats_user_daily} (stattype, timeend, courseid, userid, statsreads)
61460dd6 194
98c00d75 195 SELECT 'logins', timeend, courseid, userid, count(statsreads)
196 FROM (
197 SELECT $nextmidnight AS timeend, ".SITEID." AS courseid, l.userid, l.id AS statsreads
198 FROM {log} l
199 WHERE action = 'login' AND $timesql
200 ) inline_view
201 GROUP BY timeend, courseid, userid
202 HAVING count(statsreads) > 0";
61460dd6 203
655b09ca 204 if ($logspresent and !$DB->execute($sql)) {
61460dd6 205 $failed = true;
206 break;
207 }
208 stats_daily_progress('1');
209
d23a798a 210 $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 211
212 SELECT 'logins' AS stattype, $nextmidnight AS timeend, ".SITEID." as courseid, 0,
213 COALESCE((SELECT SUM(statsreads)
d23a798a 214 FROM {stats_user_daily} s1
61460dd6 215 WHERE s1.stattype = 'logins' AND timeend = $nextmidnight), 0) AS stat1,
cdcd7283 216 (SELECT COUNT('x')
d23a798a 217 FROM {stats_user_daily} s2
98c00d75 218 WHERE s2.stattype = 'logins' AND timeend = $nextmidnight) AS stat2" .
219 $DB->sql_null_from_clause();
61460dd6 220
d23a798a 221 if ($logspresent and !$DB->execute($sql)) {
61460dd6 222 $failed = true;
223 break;
224 }
225 stats_daily_progress('2');
226
227
228 // Enrolments and active enrolled users
229 //
230 // Unfortunately, we do not know how many users were registered
231 // at given times in history :-(
232 // - stat1: enrolled users
233 // - stat2: enrolled users active in this period
8660ac6e 234 // - SITEID is special case here, because it's all about default enrolment
61460dd6 235 // in that case, we'll count non-deleted users.
236 //
237
d23a798a 238 $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 239
98c00d75 240 SELECT 'enrolments', timeend, courseid, roleid, COUNT(DISTINCT userid), 0
241 FROM (
a919ae89
PS
242 SELECT $nextmidnight AS timeend, e.courseid, ra.roleid, ue.userid
243 FROM {role_assignments} ra
244 JOIN {context} c ON (c.id = ra.contextid AND c.contextlevel = :courselevel)
245 JOIN {enrol} e ON e.courseid = c.instanceid
246 JOIN {user_enrolments} ue ON (ue.enrolid = e.id AND ue.userid = ra.userid)
247 ) inline_view
98c00d75 248 GROUP BY timeend, courseid, roleid";
61460dd6 249
a919ae89 250 if (!$DB->execute($sql, array('courselevel'=>CONTEXT_COURSE))) {
61460dd6 251 $failed = true;
252 break;
253 }
254 stats_daily_progress('3');
255
256 // using table alias in UPDATE does not work in pg < 8.2
d23a798a 257 $sql = "UPDATE {stats_daily}
61460dd6 258 SET stat2 = (SELECT COUNT(DISTINCT ra.userid)
a919ae89
PS
259 FROM {role_assignments} ra
260 JOIN {context} c ON (c.id = ra.contextid AND c.contextlevel = :courselevel)
261 JOIN {enrol} e ON e.courseid = c.instanceid
262 JOIN {user_enrolments} ue ON (ue.enrolid = e.id AND ue.userid = ra.userid)
263 WHERE ra.roleid = {stats_daily}.roleid AND
264 e.courseid = {stats_daily}.courseid AND
61460dd6 265 EXISTS (SELECT 'x'
d23a798a 266 FROM {log} l
267 WHERE l.course = {stats_daily}.courseid AND
61460dd6 268 l.userid = ra.userid AND $timesql))
d23a798a 269 WHERE {stats_daily}.stattype = 'enrolments' AND
270 {stats_daily}.timeend = $nextmidnight AND
271 {stats_daily}.courseid IN
61460dd6 272 (SELECT DISTINCT l.course
d23a798a 273 FROM {log} l
61460dd6 274 WHERE $timesql)";
275
a919ae89 276 if (!$DB->execute($sql, array('courselevel'=>CONTEXT_COURSE))) {
61460dd6 277 $failed = true;
278 break;
279 }
280 stats_daily_progress('4');
281
282 /// now get course total enrolments (roleid==0) - except frontpage
d23a798a 283 $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 284
98c00d75 285 SELECT 'enrolments', timeend, id, nroleid, COUNT(DISTINCT userid), 0
286 FROM (
a919ae89
PS
287 SELECT $nextmidnight AS timeend, e.courseid AS id, 0 AS nroleid, ue.userid
288 FROM {enrol} e
289 JOIN {user_enrolments} ue ON ue.enrolid = e.id
98c00d75 290 ) inline_view
291 GROUP BY timeend, id, nroleid
292 HAVING COUNT(DISTINCT userid) > 0";
61460dd6 293
a919ae89 294 if ($logspresent and !$DB->execute($sql)) {
61460dd6 295 $failed = true;
296 break;
297 }
298 stats_daily_progress('5');
299
d23a798a 300 $sql = "UPDATE {stats_daily}
a919ae89
PS
301 SET stat2 = (SELECT COUNT(DISTINCT ue.userid)
302 FROM {enrol} e
303 JOIN {user_enrolments} ue ON ue.enrolid = e.id
304 WHERE e.courseid = {stats_daily}.courseid AND
61460dd6 305 EXISTS (SELECT 'x'
d23a798a 306 FROM {log} l
307 WHERE l.course = {stats_daily}.courseid AND
a919ae89 308 l.userid = ue.userid AND $timesql))
d23a798a 309 WHERE {stats_daily}.stattype = 'enrolments' AND
310 {stats_daily}.timeend = $nextmidnight AND
311 {stats_daily}.roleid = 0 AND
312 {stats_daily}.courseid IN
61460dd6 313 (SELECT l.course
d23a798a 314 FROM {log} l
61460dd6 315 WHERE $timesql AND l.course <> ".SITEID.")";
316
a919ae89 317 if ($logspresent and !$DB->execute($sql, array())) {
61460dd6 318 $failed = true;
319 break;
320 }
321 stats_daily_progress('6');
322
323 /// frontapge(==site) enrolments total
d23a798a 324 $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 325
326 SELECT 'enrolments', $nextmidnight, ".SITEID.", 0,
cdcd7283 327 (SELECT COUNT('x')
d23a798a 328 FROM {user} u
cdcd7283 329 WHERE u.deleted = 0) AS stat1,
330 (SELECT COUNT(DISTINCT u.id)
d23a798a 331 FROM {user} u
332 JOIN {log} l ON l.userid = u.id
98c00d75 333 WHERE u.deleted = 0 AND $timesql) AS stat2" .
334 $DB->sql_null_from_clause();
61460dd6 335
d23a798a 336 if ($logspresent and !$DB->execute($sql)) {
61460dd6 337 $failed = true;
338 break;
339 }
340 stats_daily_progress('7');
341
61460dd6 342 /// Default frontpage role enrolments are all site users (not deleted)
343 if ($defaultfproleid) {
344 // first remove default frontpage role counts if created by previous query
345 $sql = "DELETE
d23a798a 346 FROM {stats_daily}
61460dd6 347 WHERE stattype = 'enrolments' AND courseid = ".SITEID." AND
348 roleid = $defaultfproleid AND timeend = $nextmidnight";
d23a798a 349 if ($logspresent and !$DB->execute($sql)) {
61460dd6 350 $failed = true;
351 break;
f3221af9 352 }
61460dd6 353 stats_daily_progress('8');
354
d23a798a 355 $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 356
357 SELECT 'enrolments', $nextmidnight, ".SITEID.", $defaultfproleid,
cdcd7283 358 (SELECT COUNT('x')
d23a798a 359 FROM {user} u
cdcd7283 360 WHERE u.deleted = 0) AS stat1,
361 (SELECT COUNT(DISTINCT u.id)
d23a798a 362 FROM {user} u
363 JOIN {log} l ON l.userid = u.id
98c00d75 364 WHERE u.deleted = 0 AND $timesql) AS stat2" .
365 $DB->sql_null_from_clause();;
61460dd6 366
d23a798a 367 if ($logspresent and !$DB->execute($sql)) {
61460dd6 368 $failed = true;
369 break;
f3221af9 370 }
61460dd6 371 stats_daily_progress('9');
372
373 } else {
374 stats_daily_progress('x');
375 stats_daily_progress('x');
f3221af9 376 }
f3221af9 377
61460dd6 378
379
380 /// individual user stats (including not-logged-in) in each course, this is slow - reuse this data if possible
a919ae89
PS
381 list($viewactionssql, $params1) = $DB->get_in_or_equal($viewactions, SQL_PARAMS_NAMED, 'view000');
382 list($postactionssql, $params2) = $DB->get_in_or_equal($postactions, SQL_PARAMS_NAMED, 'post000');
d23a798a 383 $sql = "INSERT INTO {stats_user_daily} (stattype, timeend, courseid, userid, statsreads, statswrites)
61460dd6 384
385 SELECT 'activity' AS stattype, $nextmidnight AS timeend, d.courseid, d.userid,
cdcd7283 386 (SELECT COUNT('x')
d23a798a 387 FROM {log} l
cdcd7283 388 WHERE l.userid = d.userid AND
389 l.course = d.courseid AND $timesql AND
a919ae89 390 l.action $viewactionssql) AS statsreads,
cdcd7283 391 (SELECT COUNT('x')
d23a798a 392 FROM {log} l
cdcd7283 393 WHERE l.userid = d.userid AND
394 l.course = d.courseid AND $timesql AND
a919ae89 395 l.action $postactionssql) AS statswrites
61460dd6 396 FROM (SELECT DISTINCT u.id AS userid, l.course AS courseid
d23a798a 397 FROM {user} u, {log} l
61460dd6 398 WHERE u.id = l.userid AND $timesql
399 UNION
98c00d75 400 SELECT 0 AS userid, ".SITEID." AS courseid" . $DB->sql_null_from_clause() . ") d";
61460dd6 401 // can not use group by here because pg can not handle it :-(
402
a919ae89 403 if ($logspresent and !$DB->execute($sql, array_merge($params1, $params2))) {
61460dd6 404 $failed = true;
f3221af9 405 break;
406 }
61460dd6 407 stats_daily_progress('10');
408
409
410 /// how many view/post actions in each course total
d23a798a 411 $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 412
413 SELECT 'activity' AS stattype, $nextmidnight AS timeend, c.id AS courseid, 0,
cdcd7283 414 (SELECT COUNT('x')
d23a798a 415 FROM {log} l1
a919ae89 416 WHERE l1.course = c.id AND l1.action $viewactionssql AND
cdcd7283 417 $timesql1) AS stat1,
418 (SELECT COUNT('x')
d23a798a 419 FROM {log} l2
a919ae89 420 WHERE l2.course = c.id AND l2.action $postactionssql AND
cdcd7283 421 $timesql2) AS stat2
d23a798a 422 FROM {course} c
61460dd6 423 WHERE EXISTS (SELECT 'x'
d23a798a 424 FROM {log} l
61460dd6 425 WHERE l.course = c.id and $timesql)";
426
a919ae89 427 if ($logspresent and !$DB->execute($sql, array_merge($params1, $params2))) {
61460dd6 428 $failed = true;
429 break;
430 }
431 stats_daily_progress('11');
f3221af9 432
f3221af9 433
61460dd6 434 /// how many view actions for each course+role - excluding guests and frontpage
f3221af9 435
d23a798a 436 $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
f3221af9 437
98c00d75 438 SELECT 'activity', timeend, courseid, roleid, SUM(statsreads), SUM(statswrites)
439 FROM (
440 SELECT $nextmidnight AS timeend, pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
441 FROM {stats_user_daily} sud,
a919ae89
PS
442 (SELECT DISTINCT ra.userid, ra.roleid, e.courseid
443 FROM {role_assignments} ra
444 JOIN {context} c ON (c.id = ra.contextid AND c.contextlevel = :courselevel)
445 JOIN {enrol} e ON e.courseid = c.instanceid
446 JOIN {user_enrolments} ue ON (ue.enrolid = e.id AND ue.userid = ra.userid)
447 WHERE ra.roleid <> $guestrole AND
448 ra.userid <> $guest
98c00d75 449 ) pl
450 WHERE sud.userid = pl.userid AND
451 sud.courseid = pl.courseid AND
452 sud.timeend = $nextmidnight AND
453 sud.stattype='activity'
454 ) inline_view
455 GROUP BY timeend, courseid, roleid
456 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
f3221af9 457
a919ae89 458 if ($logspresent and !$DB->execute($sql, array('courselevel'=>CONTEXT_COURSE))) {
61460dd6 459 $failed = true;
460 break;
461 }
462 stats_daily_progress('12');
463
464 /// how many view actions from guests only in each course - excluding frontpage
8660ac6e 465 /// normal users may enter course with temporary guest access too
61460dd6 466
d23a798a 467 $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 468
98c00d75 469 SELECT 'activity', timeend, courseid, nroleid, SUM(statsreads), SUM(statswrites)
470 FROM (
a919ae89 471 SELECT $nextmidnight AS timeend, sud.courseid, $guestrole AS nroleid, sud.statsreads, sud.statswrites
98c00d75 472 FROM {stats_user_daily} sud
473 WHERE sud.timeend = $nextmidnight AND sud.courseid <> ".SITEID." AND
474 sud.stattype='activity' AND
a919ae89
PS
475 (sud.userid = $guest OR sud.userid
476 NOT IN (SELECT ue.userid
477 FROM {user_enrolments} ue
478 JOIN {enrol} e ON ue.enrolid = e.id
479 WHERE e.courseid = sud.courseid))
98c00d75 480 ) inline_view
481 GROUP BY timeend, courseid, nroleid
482 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
61460dd6 483
a919ae89 484 if ($logspresent and !$DB->execute($sql, array())) {
61460dd6 485 $failed = true;
486 break;
487 }
488 stats_daily_progress('13');
489
490
491 /// how many view actions for each role on frontpage - excluding guests, not-logged-in and default frontpage role
d23a798a 492 $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 493
98c00d75 494 SELECT 'activity', timeend, courseid, roleid, SUM(statsreads), SUM(statswrites)
495 FROM (
496 SELECT $nextmidnight AS timeend, pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
497 FROM {stats_user_daily} sud,
a919ae89
PS
498 (SELECT DISTINCT ra.userid, ra.roleid, c.instanceid AS courseid
499 FROM {role_assignments} ra
500 JOIN {context} c ON c.id = ra.contextid
501 WHERE ra.contextid = :fpcontext AND
98c00d75 502 ra.roleid <> $defaultfproleid AND
a919ae89
PS
503 ra.roleid <> $guestrole AND
504 ra.userid <> $guest
98c00d75 505 ) pl
506 WHERE sud.userid = pl.userid AND
507 sud.courseid = pl.courseid AND
508 sud.timeend = $nextmidnight AND
509 sud.stattype='activity'
510 ) inline_view
511 GROUP BY timeend, courseid, roleid
512 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
61460dd6 513
a919ae89 514 if ($logspresent and !$DB->execute($sql, array('fpcontext'=>$fpcontext->id))) {
61460dd6 515 $failed = true;
516 break;
517 }
518 stats_daily_progress('14');
519
520
521 /// how many view actions for default frontpage role on frontpage only
d23a798a 522 $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 523
98c00d75 524 SELECT 'activity', timeend, courseid, nroleid, SUM(statsreads), SUM(statswrites)
525 FROM (
a919ae89 526 SELECT sud.timeend AS timeend, sud.courseid, $defaultfproleid AS nroleid, sud.statsreads, sud.statswrites
98c00d75 527 FROM {stats_user_daily} sud
a919ae89 528 WHERE sud.timeend = :nextm AND sud.courseid = :siteid AND
98c00d75 529 sud.stattype='activity' AND
a919ae89 530 sud.userid <> $guest AND sud.userid <> 0 AND sud.userid
98c00d75 531 NOT IN (SELECT ra.userid
a919ae89
PS
532 FROM {role_assignments} ra
533 WHERE ra.roleid <> $guestrole AND
534 ra.roleid <> $defaultfproleid AND ra.contextid = :fpcontext)
98c00d75 535 ) inline_view
536 GROUP BY timeend, courseid, nroleid
537 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
61460dd6 538
a919ae89 539 if ($logspresent and !$DB->execute($sql, array('fpcontext'=>$fpcontext->id, 'siteid'=>SITEID, 'nextm'=>$nextmidnight))) {
61460dd6 540 $failed = true;
541 break;
542 }
543 stats_daily_progress('15');
544
545 /// how many view actions for guests or not-logged-in on frontpage
d23a798a 546 $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 547
98c00d75 548 SELECT 'activity', timeend, courseid, nroleid, SUM(statsreads), SUM(statswrites)
549 FROM (
a919ae89 550 SELECT $nextmidnight AS timeend, ".SITEID." AS courseid, $guestrole AS nroleid, pl.statsreads, pl.statswrites
98c00d75 551 FROM (
552 SELECT sud.statsreads, sud.statswrites
553 FROM {stats_user_daily} sud
a919ae89 554 WHERE (sud.userid = $guest OR sud.userid = 0) AND
98c00d75 555 sud.timeend = $nextmidnight AND sud.courseid = ".SITEID." AND
556 sud.stattype='activity'
557 ) pl
558 ) inline_view
559 GROUP BY timeend, courseid, nroleid
560 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
61460dd6 561
d23a798a 562 if ($logspresent and !$DB->execute($sql)) {
61460dd6 563 $failed = true;
564 break;
565 }
566 stats_daily_progress('16');
f3221af9 567
61460dd6 568 // remember processed days
569 set_config('statslastdaily', $nextmidnight);
67ce5270 570 mtrace(" finished until $nextmidnight: ".userdate($nextmidnight)." (in ".(time()-$daystart)." s)");
61460dd6 571
572 $timestart = $nextmidnight;
573 $nextmidnight = stats_get_next_day_start($nextmidnight);
f3221af9 574 }
f3221af9 575
61460dd6 576 set_cron_lock('statsrunning', null);
577
578 if ($failed) {
579 $days--;
8660ac6e 580 mtrace("...error occurred, completed $days days of statistics.");
61460dd6 581 return false;
582
583 } else {
584 mtrace("...completed $days days of statistics.");
585 return true;
f3221af9 586 }
61460dd6 587}
588
589
590/**
591 * Execute weekly statistics gathering
592 * @return boolean success
593 */
594function stats_cron_weekly() {
d23a798a 595 global $CFG, $DB;
f3221af9 596
61460dd6 597 $now = time();
f3221af9 598
61460dd6 599 // read last execution date from db
600 if (!$timestart = get_config(NULL, 'statslastweekly')) {
601 $timestart = stats_get_base_daily(stats_get_start_from('weekly'));
602 set_config('statslastweekly', $timestart);
603 }
f3221af9 604
61460dd6 605 $nextstartweek = stats_get_next_week_start($timestart);
606
607 // are there any weeks that need to be processed?
608 if ($now < $nextstartweek) {
609 return true; // everything ok and up-to-date
f3221af9 610 }
611
61460dd6 612 $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
f3221af9 613
61460dd6 614 if (!set_cron_lock('statsrunning', $now + $timeout)) {
615 return false;
f3221af9 616 }
f3221af9 617
61460dd6 618 // fisrt delete entries that should not be there yet
d23a798a 619 $DB->delete_records_select('stats_weekly', "timeend > $timestart");
620 $DB->delete_records_select('stats_user_weekly', "timeend > $timestart");
61460dd6 621
622 mtrace("Running weekly statistics gathering, starting at $timestart:");
623
624 $weeks = 0;
625 while ($now > $nextstartweek) {
626 @set_time_limit($timeout - 200);
f3221af9 627 $weeks++;
628
61460dd6 629 if ($weeks > 1) {
630 // move the lock
631 set_cron_lock('statsrunning', time() + $timeout, true);
f3221af9 632 }
61460dd6 633
634 $logtimesql = "l.time >= $timestart AND l.time < $nextstartweek";
635 $stattimesql = "timeend > $timestart AND timeend <= $nextstartweek";
636
637 /// process login info first
d23a798a 638 $sql = "INSERT INTO {stats_user_weekly} (stattype, timeend, courseid, userid, statsreads)
61460dd6 639
98c00d75 640 SELECT 'logins', timeend, courseid, userid, COUNT(statsreads)
641 FROM (
642 SELECT $nextstartweek AS timeend, ".SITEID." as courseid, l.userid, l.id AS statsreads
643 FROM {log} l
644 WHERE action = 'login' AND $logtimesql
645 ) inline_view
646 GROUP BY timeend, courseid, userid
647 HAVING count(statsreads) > 0";
61460dd6 648
d23a798a 649 $DB->execute($sql);
61460dd6 650
d23a798a 651 $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 652
653 SELECT 'logins' AS stattype, $nextstartweek AS timeend, ".SITEID." as courseid, 0,
654 COALESCE((SELECT SUM(statsreads)
d23a798a 655 FROM {stats_user_weekly} s1
61460dd6 656 WHERE s1.stattype = 'logins' AND timeend = $nextstartweek), 0) AS nstat1,
cdcd7283 657 (SELECT COUNT('x')
d23a798a 658 FROM {stats_user_weekly} s2
98c00d75 659 WHERE s2.stattype = 'logins' AND timeend = $nextstartweek) AS nstat2" .
660 $DB->sql_null_from_clause();
61460dd6 661
d23a798a 662 $DB->execute($sql);
61460dd6 663
664
665 /// now enrolments averages
d23a798a 666 $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 667
dfc05efe 668 SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . "
98c00d75 669 FROM (
670 SELECT $nextstartweek AS ntimeend, courseid, roleid, stat1, stat2
671 FROM {stats_daily} sd
672 WHERE stattype = 'enrolments' AND $stattimesql
673 ) inline_view
674 GROUP BY ntimeend, courseid, roleid";
61460dd6 675
d23a798a 676 $DB->execute($sql);
61460dd6 677
678
679 /// activity read/write averages
d23a798a 680 $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 681
98c00d75 682 SELECT 'activity', ntimeend, courseid, roleid, SUM(stat1), SUM(stat2)
683 FROM (
684 SELECT $nextstartweek AS ntimeend, courseid, roleid, stat1, stat2
685 FROM {stats_daily}
686 WHERE stattype = 'activity' AND $stattimesql
687 ) inline_view
688 GROUP BY ntimeend, courseid, roleid";
61460dd6 689
d23a798a 690 $DB->execute($sql);
61460dd6 691
692
693 /// user read/write averages
d23a798a 694 $sql = "INSERT INTO {stats_user_weekly} (stattype, timeend, courseid, userid, statsreads, statswrites)
61460dd6 695
98c00d75 696 SELECT 'activity', ntimeend, courseid, userid, SUM(statsreads), SUM(statswrites)
697 FROM (
698 SELECT $nextstartweek AS ntimeend, courseid, userid, statsreads, statswrites
699 FROM {stats_user_daily}
700 WHERE stattype = 'activity' AND $stattimesql
701 ) inline_view
702 GROUP BY ntimeend, courseid, userid";
61460dd6 703
d23a798a 704 $DB->execute($sql);
61460dd6 705
706 set_config('statslastweekly', $nextstartweek);
707 mtrace(" finished until $nextstartweek: ".userdate($nextstartweek));
708
709 $timestart = $nextstartweek;
710 $nextstartweek = stats_get_next_week_start($nextstartweek);
f3221af9 711 }
61460dd6 712
713 set_cron_lock('statsrunning', null);
714 mtrace("...completed $weeks weeks of statistics.");
715 return true;
f3221af9 716}
f3221af9 717
61460dd6 718/**
719 * Execute monthly statistics gathering
720 * @return boolean success
721 */
722function stats_cron_monthly() {
d23a798a 723 global $CFG, $DB;
f3221af9 724
61460dd6 725 $now = time();
726
727 // read last execution date from db
728 if (!$timestart = get_config(NULL, 'statslastmonthly')) {
729 $timestart = stats_get_base_monthly(stats_get_start_from('monthly'));
730 set_config('statslastmonthly', $timestart);
f3221af9 731 }
732
61460dd6 733 $nextstartmonth = stats_get_next_month_start($timestart);
734
735 // are there any months that need to be processed?
736 if ($now < $nextstartmonth) {
737 return true; // everything ok and up-to-date
f3221af9 738 }
61460dd6 739
740 $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
741
742 if (!set_cron_lock('statsrunning', $now + $timeout)) {
743 return false;
f3221af9 744 }
f3221af9 745
61460dd6 746 // fisr delete entries that should not be there yet
d23a798a 747 $DB->delete_records_select('stats_monthly', "timeend > $timestart");
748 $DB->delete_records_select('stats_user_monthly', "timeend > $timestart");
f3221af9 749
61460dd6 750 $startmonth = stats_get_base_monthly($now);
751
752
753 mtrace("Running monthly statistics gathering, starting at $timestart:");
754
755 $months = 0;
756 while ($now > $nextstartmonth) {
757 @set_time_limit($timeout - 200);
758 $months++;
759
760 if ($months > 1) {
761 // move the lock
762 set_cron_lock('statsrunning', time() + $timeout, true);
f3221af9 763 }
61460dd6 764
765 $logtimesql = "l.time >= $timestart AND l.time < $nextstartmonth";
766 $stattimesql = "timeend > $timestart AND timeend <= $nextstartmonth";
767
768 /// process login info first
d23a798a 769 $sql = "INSERT INTO {stats_user_monthly} (stattype, timeend, courseid, userid, statsreads)
61460dd6 770
98c00d75 771 SELECT 'logins', timeend, courseid, userid, COUNT(statsreads)
772 FROM (
773 SELECT $nextstartmonth AS timeend, ".SITEID." as courseid, l.userid, l.id AS statsreads
774 FROM {log} l
775 WHERE action = 'login' AND $logtimesql
776 ) inline_view
777 GROUP BY timeend, courseid, userid";
61460dd6 778
d23a798a 779 $DB->execute($sql);
61460dd6 780
d23a798a 781 $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 782
783 SELECT 'logins' AS stattype, $nextstartmonth AS timeend, ".SITEID." as courseid, 0,
784 COALESCE((SELECT SUM(statsreads)
d23a798a 785 FROM {stats_user_monthly} s1
61460dd6 786 WHERE s1.stattype = 'logins' AND timeend = $nextstartmonth), 0) AS nstat1,
cdcd7283 787 (SELECT COUNT('x')
d23a798a 788 FROM {stats_user_monthly} s2
98c00d75 789 WHERE s2.stattype = 'logins' AND timeend = $nextstartmonth) AS nstat2" .
790 $DB->sql_null_from_clause();
61460dd6 791
d23a798a 792 $DB->execute($sql);
61460dd6 793
794
795 /// now enrolments averages
d23a798a 796 $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 797
dfc05efe 798 SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . "
98c00d75 799 FROM (
800 SELECT $nextstartmonth AS ntimeend, courseid, roleid, stat1, stat2
801 FROM {stats_daily} sd
802 WHERE stattype = 'enrolments' AND $stattimesql
803 ) inline_view
804 GROUP BY ntimeend, courseid, roleid";
61460dd6 805
d23a798a 806 $DB->execute($sql);
61460dd6 807
808
809 /// activity read/write averages
d23a798a 810 $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 811
98c00d75 812 SELECT 'activity', ntimeend, courseid, roleid, SUM(stat1), SUM(stat2)
813 FROM (
814 SELECT $nextstartmonth AS ntimeend, courseid, roleid, stat1, stat2
815 FROM {stats_daily}
816 WHERE stattype = 'activity' AND $stattimesql
817 ) inline_view
818 GROUP BY ntimeend, courseid, roleid";
61460dd6 819
d23a798a 820 $DB->execute($sql);
61460dd6 821
822
823 /// user read/write averages
d23a798a 824 $sql = "INSERT INTO {stats_user_monthly} (stattype, timeend, courseid, userid, statsreads, statswrites)
61460dd6 825
98c00d75 826 SELECT 'activity', ntimeend, courseid, userid, SUM(statsreads), SUM(statswrites)
827 FROM (
828 SELECT $nextstartmonth AS ntimeend, courseid, userid, statsreads, statswrites
829 FROM {stats_user_daily}
830 WHERE stattype = 'activity' AND $stattimesql
831 ) inline_view
832 GROUP BY ntimeend, courseid, userid";
61460dd6 833
d23a798a 834 $DB->execute($sql);
61460dd6 835
836 set_config('statslastmonthly', $nextstartmonth);
837 mtrace(" finished until $nextstartmonth: ".userdate($nextstartmonth));
838
839 $timestart = $nextstartmonth;
840 $nextstartmonth = stats_get_next_month_start($nextstartmonth);
f3221af9 841 }
f3221af9 842
61460dd6 843 set_cron_lock('statsrunning', null);
844 mtrace("...completed $months months of statistics.");
845 return true;
846}
847
61460dd6 848/**
849 * Return starting date of stats processing
850 * @param string $str name of table - daily, weekly or monthly
851 * @return int timestamp
852 */
f3221af9 853function stats_get_start_from($str) {
d23a798a 854 global $CFG, $DB;
f3221af9 855
61460dd6 856 // are there any data in stats table? Should not be...
5b903967 857 if ($timeend = $DB->get_field_sql('SELECT MAX(timeend) FROM {stats_'.$str.'}')) {
f3221af9 858 return $timeend;
859 }
f3221af9 860 // decide what to do based on our config setting (either all or none or a timestamp)
f3221af9 861 switch ($CFG->statsfirstrun) {
61460dd6 862 case 'all':
5b903967 863 if ($firstlog = $DB->get_field_sql('SELECT MIN(time) FROM {log}')) {
61460dd6 864 return $firstlog;
865 }
f3221af9 866 default:
867 if (is_numeric($CFG->statsfirstrun)) {
61460dd6 868 return time() - $CFG->statsfirstrun;
f3221af9 869 }
61460dd6 870 // not a number? use next instead
871 case 'none':
872 return strtotime('-3 day', time());
f3221af9 873 }
874}
875
61460dd6 876/**
877 * Start of day
878 * @param int $time timestamp
879 * @return start of day
880 */
f3221af9 881function stats_get_base_daily($time=0) {
61460dd6 882 global $CFG;
883
f3221af9 884 if (empty($time)) {
885 $time = time();
886 }
61460dd6 887 if ($CFG->timezone == 99) {
888 $time = strtotime(date('d-M-Y', $time));
889 return $time;
890 } else {
891 $offset = get_timezone_offset($CFG->timezone);
892 $gtime = $time + $offset;
893 $gtime = intval($gtime / (60*60*24)) * 60*60*24;
894 return $gtime - $offset;
895 }
f3221af9 896}
897
61460dd6 898/**
899 * Start of week
900 * @param int $time timestamp
901 * @return start of week
902 */
f3221af9 903function stats_get_base_weekly($time=0) {
61460dd6 904 global $CFG;
f3221af9 905
61460dd6 906 $time = stats_get_base_daily($time);
907 $startday = $CFG->calendar_startwday;
908 if ($CFG->timezone == 99) {
909 $thisday = date('w', $time);
910 } else {
911 $offset = get_timezone_offset($CFG->timezone);
912 $gtime = $time + $offset;
913 $thisday = gmdate('w', $gtime);
914 }
915 if ($thisday > $startday) {
916 $time = $time - (($thisday - $startday) * 60*60*24);
917 } else if ($thisday < $startday) {
918 $time = $time - ((7 + $thisday - $startday) * 60*60*24);
919 }
920 return $time;
f3221af9 921}
922
61460dd6 923/**
924 * Start of month
925 * @param int $time timestamp
926 * @return start of month
927 */
f3221af9 928function stats_get_base_monthly($time=0) {
61460dd6 929 global $CFG;
930
f3221af9 931 if (empty($time)) {
932 $time = time();
933 }
61460dd6 934 if ($CFG->timezone == 99) {
935 return strtotime(date('1-M-Y', $time));
936
937 } else {
938 $time = stats_get_base_daily($time);
939 $offset = get_timezone_offset($CFG->timezone);
940 $gtime = $time + $offset;
941 $day = gmdate('d', $gtime);
942 if ($day == 1) {
943 return $time;
944 }
945 return $gtime - (($day-1) * 60*60*24);
946 }
f3221af9 947}
948
61460dd6 949/**
950 * Start of next day
951 * @param int $time timestamp
952 * @return start of next day
953 */
954function stats_get_next_day_start($time) {
955 $next = stats_get_base_daily($time);
956 $next = $next + 60*60*26;
957 $next = stats_get_base_daily($next);
958 if ($next <= $time) {
959 //DST trouble - prevent infinite loops
960 $next = $next + 60*60*24;
961 }
962 return $next;
4d8e4c38 963}
964
61460dd6 965/**
966 * Start of next week
967 * @param int $time timestamp
968 * @return start of next week
969 */
970function stats_get_next_week_start($time) {
971 $next = stats_get_base_weekly($time);
972 $next = $next + 60*60*24*9;
973 $next = stats_get_base_weekly($next);
974 if ($next <= $time) {
975 //DST trouble - prevent infinite loops
976 $next = $next + 60*60*24*7;
977 }
978 return $next;
4d8e4c38 979}
980
61460dd6 981/**
982 * Start of next month
983 * @param int $time timestamp
984 * @return start of next month
985 */
986function stats_get_next_month_start($time) {
987 $next = stats_get_base_monthly($time);
988 $next = $next + 60*60*24*33;
989 $next = stats_get_base_monthly($next);
990 if ($next <= $time) {
991 //DST trouble - prevent infinite loops
992 $next = $next + 60*60*24*31;
993 }
994 return $next;
f3221af9 995}
996
61460dd6 997/**
998 * Remove old stats data
999 */
f3221af9 1000function stats_clean_old() {
d23a798a 1001 global $DB;
61460dd6 1002 mtrace("Running stats cleanup tasks...");
1003 $deletebefore = stats_get_base_monthly();
1004
1005 // delete dailies older than 3 months (to be safe)
1006 $deletebefore = strtotime('-3 months', $deletebefore);
d23a798a 1007 $DB->delete_records_select('stats_daily', "timeend < $deletebefore");
1008 $DB->delete_records_select('stats_user_daily', "timeend < $deletebefore");
61460dd6 1009
1010 // delete weeklies older than 9 months (to be safe)
1011 $deletebefore = strtotime('-6 months', $deletebefore);
d23a798a 1012 $DB->delete_records_select('stats_weekly', "timeend < $deletebefore");
1013 $DB->delete_records_select('stats_user_weekly', "timeend < $deletebefore");
f3221af9 1014
1015 // don't delete monthlies
61460dd6 1016
1017 mtrace("...stats cleanup finished");
f3221af9 1018}
1019
0f259f63 1020function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) {
29f83769 1021 global $CFG, $DB;
61460dd6 1022
365a5941 1023 $param = new stdClass();
f87cab2a 1024 $param->params = array();
61460dd6 1025
f3221af9 1026 if ($time < 10) { // dailies
1027 // number of days to go back = 7* time
f3221af9 1028 $param->table = 'daily';
1029 $param->timeafter = strtotime("-".($time*7)." days",stats_get_base_daily());
1030 } elseif ($time < 20) { // weeklies
1031 // number of weeks to go back = time - 10 * 4 (weeks) + base week
f3221af9 1032 $param->table = 'weekly';
8b658d8a 1033 $param->timeafter = strtotime("-".(($time - 10)*4)." weeks",stats_get_base_weekly());
f3221af9 1034 } else { // monthlies.
1035 // number of months to go back = time - 20 * months + base month
f3221af9 1036 $param->table = 'monthly';
1037 $param->timeafter = strtotime("-".($time - 20)." months",stats_get_base_monthly());
1038 }
1039
3a317277 1040 $param->extras = '';
1041
f3221af9 1042 switch ($report) {
96edb010 1043 // ******************** STATS_MODE_GENERAL ******************** //
1044 case STATS_REPORT_LOGINS:
0dd5bf42 1045 $param->fields = 'timeend,sum(stat1) as line1,sum(stat2) as line2';
1046 $param->fieldscomplete = true;
0f259f63 1047 $param->stattype = 'logins';
f3221af9 1048 $param->line1 = get_string('statslogins');
1049 $param->line2 = get_string('statsuniquelogins');
0dd5bf42 1050 if ($courseid == SITEID) {
1051 $param->extras = 'GROUP BY timeend';
1052 }
f3221af9 1053 break;
96edb010 1054
1055 case STATS_REPORT_READS:
29f83769 1056 $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat1 as line1';
0f259f63 1057 $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
0dd5bf42 1058 $param->aggregategroupby = 'roleid';
0f259f63 1059 $param->stattype = 'activity';
1060 $param->crosstab = true;
1061 $param->extras = 'GROUP BY timeend,roleid,stat1';
0dd5bf42 1062 if ($courseid == SITEID) {
29f83769 1063 $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1) as line1';
0dd5bf42 1064 $param->extras = 'GROUP BY timeend,roleid';
1065 }
f3221af9 1066 break;
96edb010 1067
61460dd6 1068 case STATS_REPORT_WRITES:
29f83769 1069 $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat2 as line1';
0f259f63 1070 $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
0dd5bf42 1071 $param->aggregategroupby = 'roleid';
0f259f63 1072 $param->stattype = 'activity';
1073 $param->crosstab = true;
1074 $param->extras = 'GROUP BY timeend,roleid,stat2';
0dd5bf42 1075 if ($courseid == SITEID) {
29f83769 1076 $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat2) as line1';
0dd5bf42 1077 $param->extras = 'GROUP BY timeend,roleid';
1078 }
f3221af9 1079 break;
96edb010 1080
1081 case STATS_REPORT_ACTIVITY:
29f83769 1082 $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1+stat2) as line1';
0f259f63 1083 $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
0dd5bf42 1084 $param->aggregategroupby = 'roleid';
0f259f63 1085 $param->stattype = 'activity';
1086 $param->crosstab = true;
1087 $param->extras = 'GROUP BY timeend,roleid';
0dd5bf42 1088 if ($courseid == SITEID) {
1089 $param->extras = 'GROUP BY timeend,roleid';
1090 }
f3221af9 1091 break;
96edb010 1092
0f259f63 1093 case STATS_REPORT_ACTIVITYBYROLE;
1094 $param->fields = 'stat1 AS line1, stat2 AS line2';
1095 $param->stattype = 'activity';
a5d424df 1096 $rolename = $DB->get_field('role','name', array('id'=>$roleid));
0f259f63 1097 $param->line1 = $rolename . get_string('statsreads');
1098 $param->line2 = $rolename . get_string('statswrites');
96edb010 1099 if ($courseid == SITEID) {
1100 $param->extras = 'GROUP BY timeend';
1101 }
f3221af9 1102 break;
96edb010 1103
1104 // ******************** STATS_MODE_DETAILED ******************** //
f3221af9 1105 case STATS_REPORT_USER_ACTIVITY:
addcde11 1106 $param->fields = 'statsreads as line1, statswrites as line2';
f3221af9 1107 $param->line1 = get_string('statsuserreads');
1108 $param->line2 = get_string('statsuserwrites');
1109 $param->stattype = 'activity';
1110 break;
96edb010 1111
f3221af9 1112 case STATS_REPORT_USER_ALLACTIVITY:
addcde11 1113 $param->fields = 'statsreads+statswrites as line1';
f3221af9 1114 $param->line1 = get_string('statsuseractivity');
1115 $param->stattype = 'activity';
1116 break;
96edb010 1117
f3221af9 1118 case STATS_REPORT_USER_LOGINS:
addcde11 1119 $param->fields = 'statsreads as line1';
f3221af9 1120 $param->line1 = get_string('statsuserlogins');
1121 $param->stattype = 'logins';
1122 break;
96edb010 1123
f3221af9 1124 case STATS_REPORT_USER_VIEW:
addcde11 1125 $param->fields = 'statsreads as line1, statswrites as line2, statsreads+statswrites as line3';
f3221af9 1126 $param->line1 = get_string('statsuserreads');
1127 $param->line2 = get_string('statsuserwrites');
1128 $param->line3 = get_string('statsuseractivity');
1129 $param->stattype = 'activity';
1130 break;
96edb010 1131
1132 // ******************** STATS_MODE_RANKED ******************** //
61460dd6 1133 case STATS_REPORT_ACTIVE_COURSES:
96edb010 1134 $param->fields = 'sum(stat1+stat2) AS line1';
1135 $param->stattype = 'activity';
3eb9babc 1136 $param->orderby = 'line1 DESC';
1137 $param->line1 = get_string('activity');
1138 $param->graphline = 'line1';
1139 break;
96edb010 1140
3eb9babc 1141 case STATS_REPORT_ACTIVE_COURSES_WEIGHTED:
96edb010 1142 $threshold = 0;
9afaefaa 1143 if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
96edb010 1144 $threshold = $CFG->statsuserthreshold;
9afaefaa 1145 }
96edb010 1146 $param->fields = '';
1147 $param->sql = 'SELECT activity.courseid, activity.all_activity AS line1, enrolments.highest_enrolments AS line2,
61460dd6 1148 activity.all_activity / enrolments.highest_enrolments as line3
96edb010 1149 FROM (
b555127f 1150 SELECT courseid, sum(stat1+stat2) AS all_activity
29f83769 1151 FROM {stats_'.$param->table.'}
a919ae89 1152 WHERE stattype=\'activity\' AND timeend >= '.(int)$param->timeafter.' AND roleid = 0 GROUP BY courseid
96edb010 1153 ) activity
61460dd6 1154 INNER JOIN
96edb010 1155 (
117bd748 1156 SELECT courseid, max(stat1) AS highest_enrolments
29f83769 1157 FROM {stats_'.$param->table.'}
a919ae89 1158 WHERE stattype=\'enrolments\' AND timeend >= '.(int)$param->timeafter.' AND stat1 > '.(int)$threshold.'
61460dd6 1159 GROUP BY courseid
96edb010 1160 ) enrolments
1161 ON (activity.courseid = enrolments.courseid)
96edb010 1162 ORDER BY line3 DESC';
3eb9babc 1163 $param->line1 = get_string('activity');
1164 $param->line2 = get_string('users');
1165 $param->line3 = get_string('activityweighted');
1166 $param->graphline = 'line3';
1167 break;
96edb010 1168
3eb9babc 1169 case STATS_REPORT_PARTICIPATORY_COURSES:
96edb010 1170 $threshold = 0;
7b89e189 1171 if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
96edb010 1172 $threshold = $CFG->statsuserthreshold;
7b89e189 1173 }
96edb010 1174 $param->fields = '';
dfc05efe 1175 $param->sql = 'SELECT courseid, ' . $DB->sql_ceil('avg(all_enrolments)') . ' as line1, ' .
1176 $DB->sql_ceil('avg(active_enrolments)') . ' as line2, avg(proportion_active) AS line3
96edb010 1177 FROM (
61460dd6 1178 SELECT courseid, timeend, stat2 as active_enrolments,
29f83769 1179 stat1 as all_enrolments, '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' AS proportion_active
1180 FROM {stats_'.$param->table.'}
a919ae89 1181 WHERE stattype=\'enrolments\' AND roleid = 0 AND stat1 > '.(int)$threshold.'
61460dd6 1182 ) aq
a919ae89 1183 WHERE timeend >= '.(int)$param->timeafter.'
96edb010 1184 GROUP BY courseid
1185 ORDER BY line3 DESC';
1186
3eb9babc 1187 $param->line1 = get_string('users');
1188 $param->line2 = get_string('activeusers');
1189 $param->line3 = get_string('participationratio');
1190 $param->graphline = 'line3';
1191 break;
96edb010 1192
3eb9babc 1193 case STATS_REPORT_PARTICIPATORY_COURSES_RW:
96edb010 1194 $param->fields = '';
1195 $param->sql = 'SELECT courseid, sum(views) AS line1, sum(posts) AS line2,
1196 avg(proportion_active) AS line3
1197 FROM (
61460dd6 1198 SELECT courseid, timeend, stat1 as views, stat2 AS posts,
29f83769 1199 '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' as proportion_active
1200 FROM {stats_'.$param->table.'}
61460dd6 1201 WHERE stattype=\'activity\' AND roleid = 0 AND stat1 > 0
1202 ) aq
a919ae89 1203 WHERE timeend >= '.(int)$param->timeafter.'
96edb010 1204 GROUP BY courseid
1205 ORDER BY line3 DESC';
3eb9babc 1206 $param->line1 = get_string('views');
1207 $param->line2 = get_string('posts');
1208 $param->line3 = get_string('participationratio');
1209 $param->graphline = 'line3';
1210 break;
f3221af9 1211 }
96edb010 1212
0dd5bf42 1213 /*
3eb9babc 1214 if ($courseid == SITEID && $mode != STATS_MODE_RANKED) { // just aggregate all courses.
0dd5bf42 1215 $param->fields = preg_replace('/(?:sum)([a-zA-Z0-9+_]*)\W+as\W+([a-zA-Z0-9_]*)/i','sum($1) as $2',$param->fields);
1216 $param->extras = ' GROUP BY timeend'.((!empty($param->aggregategroupby)) ? ','.$param->aggregategroupby : '');
3a317277 1217 }
0dd5bf42 1218 */
1219 //TODO must add the SITEID reports to the rest of the reports.
6c45af4f 1220 return $param;
61460dd6 1221}
f3221af9 1222
1223function stats_get_view_actions() {
1224 return array('view','view all','history');
1225}
1226
1227function stats_get_post_actions() {
1228 return array('add','delete','edit','add mod','delete mod','edit section'.'enrol','loginas','new','unenrol','update','update mod');
1229}
1230
61460dd6 1231function stats_get_action_names($str) {
d23a798a 1232 global $CFG, $DB;
61460dd6 1233
d23a798a 1234 $mods = $DB->get_records('modules');
f3221af9 1235 $function = 'stats_get_'.$str.'_actions';
1236 $actions = $function();
1237 foreach ($mods as $mod) {
bd903ae9 1238 $file = $CFG->dirroot.'/mod/'.$mod->name.'/lib.php';
1239 if (!is_readable($file)) {
1240 continue;
1241 }
1242 require_once($file);
f3221af9 1243 $function = $mod->name.'_get_'.$str.'_actions';
1244 if (function_exists($function)) {
553baa8f
DC
1245 $mod_actions = $function();
1246 if (is_array($mod_actions)) {
1247 $actions = array_merge($actions, $mod_actions);
1248 }
f3221af9 1249 }
1250 }
3eb9babc 1251
61460dd6 1252 // The array_values() forces a stack-like array
1253 // so we can later loop over safely...
1254 $actions = array_values(array_unique($actions));
1255 $c = count($actions);
1256 for ($n=0;$n<$c;$n++) {
a919ae89 1257 $actions[$n] = $actions[$n];
f3221af9 1258 }
61460dd6 1259 return $actions;
f3221af9 1260}
1261
1262function stats_get_time_options($now,$lastweekend,$lastmonthend,$earliestday,$earliestweek,$earliestmonth) {
1263
1264 $now = stats_get_base_daily(time());
1265 // it's really important that it's TIMEEND in the table. ie, tuesday 00:00:00 is monday night.
1266 // so we need to take a day off here (essentially add a day to $now
1267 $now += 60*60*24;
1268
3f73372f 1269 $timeoptions = array();
1270
f3221af9 1271 if ($now - (60*60*24*7) >= $earliestday) {
1272 $timeoptions[STATS_TIME_LASTWEEK] = get_string('numweeks','moodle',1);
1273 }
1274 if ($now - (60*60*24*14) >= $earliestday) {
1275 $timeoptions[STATS_TIME_LAST2WEEKS] = get_string('numweeks','moodle',2);
1276 }
1277 if ($now - (60*60*24*21) >= $earliestday) {
61460dd6 1278 $timeoptions[STATS_TIME_LAST3WEEKS] = get_string('numweeks','moodle',3);
f3221af9 1279 }
1280 if ($now - (60*60*24*28) >= $earliestday) {
1281 $timeoptions[STATS_TIME_LAST4WEEKS] = get_string('numweeks','moodle',4);// show dailies up to (including) here.
1282 }
1283 if ($lastweekend - (60*60*24*56) >= $earliestweek) {
1284 $timeoptions[STATS_TIME_LAST2MONTHS] = get_string('nummonths','moodle',2);
1285 }
1286 if ($lastweekend - (60*60*24*84) >= $earliestweek) {
1287 $timeoptions[STATS_TIME_LAST3MONTHS] = get_string('nummonths','moodle',3);
1288 }
1289 if ($lastweekend - (60*60*24*112) >= $earliestweek) {
1290 $timeoptions[STATS_TIME_LAST4MONTHS] = get_string('nummonths','moodle',4);
1291 }
1292 if ($lastweekend - (60*60*24*140) >= $earliestweek) {
1293 $timeoptions[STATS_TIME_LAST5MONTHS] = get_string('nummonths','moodle',5);
1294 }
1295 if ($lastweekend - (60*60*24*168) >= $earliestweek) {
1296 $timeoptions[STATS_TIME_LAST6MONTHS] = get_string('nummonths','moodle',6); // show weeklies up to (including) here
1297 }
1298 if (strtotime('-7 months',$lastmonthend) >= $earliestmonth) {
1299 $timeoptions[STATS_TIME_LAST7MONTHS] = get_string('nummonths','moodle',7);
1300 }
1301 if (strtotime('-8 months',$lastmonthend) >= $earliestmonth) {
1302 $timeoptions[STATS_TIME_LAST8MONTHS] = get_string('nummonths','moodle',8);
1303 }
1304 if (strtotime('-9 months',$lastmonthend) >= $earliestmonth) {
1305 $timeoptions[STATS_TIME_LAST9MONTHS] = get_string('nummonths','moodle',9);
1306 }
1307 if (strtotime('-10 months',$lastmonthend) >= $earliestmonth) {
1308 $timeoptions[STATS_TIME_LAST10MONTHS] = get_string('nummonths','moodle',10);
1309 }
1310 if (strtotime('-11 months',$lastmonthend) >= $earliestmonth) {
1311 $timeoptions[STATS_TIME_LAST11MONTHS] = get_string('nummonths','moodle',11);
1312 }
1313 if (strtotime('-1 year',$lastmonthend) >= $earliestmonth) {
1314 $timeoptions[STATS_TIME_LASTYEAR] = get_string('lastyear');
1315 }
1316
17fdbcac 1317 $years = (int)date('y', $now) - (int)date('y', $earliestmonth);
1318 if ($years > 1) {
1319 for($i = 2; $i <= $years; $i++) {
1320 $timeoptions[$i*12+20] = get_string('numyears', 'moodle', $i);
1321 }
1322 }
1323
f3221af9 1324 return $timeoptions;
1325}
1326
1327function stats_get_report_options($courseid,$mode) {
d23a798a 1328 global $CFG, $DB;
61460dd6 1329
f3221af9 1330 $reportoptions = array();
1331
1332 switch ($mode) {
1333 case STATS_MODE_GENERAL:
1334 $reportoptions[STATS_REPORT_ACTIVITY] = get_string('statsreport'.STATS_REPORT_ACTIVITY);
e6012928 1335 if ($courseid != SITEID && $context = get_context_instance(CONTEXT_COURSE, $courseid)) {
f9c2702e
DP
1336 $sql = 'SELECT r.id, r.name FROM {role} r JOIN {stats_daily} s ON s.roleid = r.id WHERE s.courseid = :courseid GROUP BY s.roleid';
1337 if ($roles = $DB->get_records_sql($sql, array('courseid' => $courseid))) {
0f259f63 1338 foreach ($roles as $role) {
1339 $reportoptions[STATS_REPORT_ACTIVITYBYROLE.$role->id] = get_string('statsreport'.STATS_REPORT_ACTIVITYBYROLE). ' '.$role->name;
1340 }
1341 }
1342 }
f3221af9 1343 $reportoptions[STATS_REPORT_READS] = get_string('statsreport'.STATS_REPORT_READS);
1344 $reportoptions[STATS_REPORT_WRITES] = get_string('statsreport'.STATS_REPORT_WRITES);
1345 if ($courseid == SITEID) {
1346 $reportoptions[STATS_REPORT_LOGINS] = get_string('statsreport'.STATS_REPORT_LOGINS);
1347 }
61460dd6 1348
f3221af9 1349 break;
1350 case STATS_MODE_DETAILED:
1351 $reportoptions[STATS_REPORT_USER_ACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ACTIVITY);
1352 $reportoptions[STATS_REPORT_USER_ALLACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ALLACTIVITY);
a2e4bf7f 1353 if (has_capability('coursereport/stats:view', get_context_instance(CONTEXT_SYSTEM))) {
f3221af9 1354 $site = get_site();
1355 $reportoptions[STATS_REPORT_USER_LOGINS] = get_string('statsreport'.STATS_REPORT_USER_LOGINS);
1356 }
3eb9babc 1357 break;
1358 case STATS_MODE_RANKED:
a2e4bf7f 1359 if (has_capability('coursereport/stats:view', get_context_instance(CONTEXT_SYSTEM))) {
3eb9babc 1360 $reportoptions[STATS_REPORT_ACTIVE_COURSES] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES);
1361 $reportoptions[STATS_REPORT_ACTIVE_COURSES_WEIGHTED] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES_WEIGHTED);
1362 $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES);
1363 $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES_RW] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES_RW);
1364 }
1365 break;
f3221af9 1366 }
61460dd6 1367
f3221af9 1368 return $reportoptions;
1369}
1370
1371function stats_fix_zeros($stats,$timeafter,$timestr,$line2=true,$line3=false) {
1372
3a317277 1373 if (empty($stats)) {
1374 return;
1375 }
1376
f3221af9 1377 $timestr = str_replace('user_','',$timestr); // just in case.
8b658d8a 1378 $fun = 'stats_get_base_'.$timestr;
61460dd6 1379
8b658d8a 1380 $now = $fun();
f3221af9 1381
1382 $times = array();
1383 // add something to timeafter since it is our absolute base
0f259f63 1384 $actualtimes = array();
61460dd6 1385 foreach ($stats as $statid=>$s) {
1386 //normalize the times in stats - those might have been created in different timezone, DST etc.
1387 $s->timeend = $fun($s->timeend + 60*60*5);
1388 $stats[$statid] = $s;
1389
0f259f63 1390 $actualtimes[] = $s->timeend;
1391 }
11887b6d 1392
af5d9f6a 1393 $timeafter = array_pop(array_values($actualtimes));
3eb9babc 1394
f3221af9 1395 while ($timeafter < $now) {
1396 $times[] = $timeafter;
1397 if ($timestr == 'daily') {
61460dd6 1398 $timeafter = stats_get_next_day_start($timeafter);
f3221af9 1399 } else if ($timestr == 'weekly') {
61460dd6 1400 $timeafter = stats_get_next_week_start($timeafter);
f3221af9 1401 } else if ($timestr == 'monthly') {
61460dd6 1402 $timeafter = stats_get_next_month_start($timeafter);
f3221af9 1403 } else {
1404 return $stats; // this will put us in a never ending loop.
1405 }
1406 }
1407
0f259f63 1408 foreach ($times as $count => $time) {
1409 if (!in_array($time,$actualtimes) && $count != count($times) -1) {
f3221af9 1410 $newobj = new StdClass;
1411 $newobj->timeend = $time;
1412 $newobj->id = 0;
11887b6d 1413 $newobj->roleid = 0;
f3221af9 1414 $newobj->line1 = 0;
1415 if (!empty($line2)) {
1416 $newobj->line2 = 0;
1417 }
1418 if (!empty($line3)) {
1419 $newobj->line3 = 0;
1420 }
11887b6d 1421 $newobj->zerofixed = true;
0f259f63 1422 $stats[] = $newobj;
f3221af9 1423 }
1424 }
61460dd6 1425
0f259f63 1426 usort($stats,"stats_compare_times");
f3221af9 1427 return $stats;
1428
1429}
1430
0f259f63 1431// helper function to sort arrays by $obj->timeend
1432function stats_compare_times($a,$b) {
1433 if ($a->timeend == $b->timeend) {
1434 return 0;
1435 }
1436 return ($a->timeend > $b->timeend) ? -1 : 1;
1437}
1438
140c139e 1439function stats_check_uptodate($courseid=0) {
d23a798a 1440 global $CFG, $DB;
140c139e 1441
1442 if (empty($courseid)) {
450a0a7d 1443 $courseid = SITEID;
140c139e 1444 }
1445
1446 $latestday = stats_get_start_from('daily');
1447
4900433c 1448 if ((time() - 60*60*24*2) < $latestday) { // we're ok
13d32e22 1449 return NULL;
140c139e 1450 }
1451
365a5941 1452 $a = new stdClass();
d23a798a 1453 $a->daysdone = $DB->get_field_sql("SELECT COUNT(DISTINCT(timeend)) FROM {stats_daily}");
140c139e 1454
1455 // how many days between the last day and now?
1456 $a->dayspending = ceil((stats_get_base_daily() - $latestday)/(60*60*24));
1457
1458 if ($a->dayspending == 0 && $a->daysdone != 0) {
13d32e22 1459 return NULL; // we've only just started...
140c139e 1460 }
13d32e22 1461
1462 //return error as string
1463 return get_string('statscatchupmode','error',$a);
140c139e 1464}