MDL-58756 stats: Use multilang with role.
[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
9c1600f1
TB
74// Output string when nodebug is on
75define('STATS_PLACEHOLDER_OUTPUT', '.');
76
61460dd6 77/**
78 * Print daily cron progress
117bd748 79 * @param string $ident
61460dd6 80 */
9c1600f1 81function stats_progress($ident) {
61460dd6 82 static $start = 0;
83 static $init = 0;
f3221af9 84
61460dd6 85 if ($ident == 'init') {
9c1600f1 86 $init = $start = microtime(true);
61460dd6 87 return;
f3221af9 88 }
89
9c1600f1
TB
90 $elapsed = round(microtime(true) - $start);
91 $start = microtime(true);
61460dd6 92
93 if (debugging('', DEBUG_ALL)) {
94 mtrace("$ident:$elapsed ", '');
95 } else {
9c1600f1 96 mtrace(STATS_PLACEHOLDER_OUTPUT, '');
f3221af9 97 }
61460dd6 98}
f3221af9 99
9c1600f1
TB
100/**
101 * Execute individual daily statistics queries
102 *
103 * @param string $sql The query to run
104 * @return boolean success
105 */
aa600c47 106function stats_run_query($sql, $parameters = array()) {
9c1600f1
TB
107 global $DB;
108
109 try {
110 $DB->execute($sql, $parameters);
111 } catch (dml_exception $e) {
112
113 if (debugging('', DEBUG_ALL)) {
4614b029 114 mtrace($e->getMessage());
9c1600f1 115 }
4614b029
TB
116 return false;
117 }
9c1600f1
TB
118 return true;
119}
120
61460dd6 121/**
122 * Execute daily statistics gathering
9c1600f1 123 *
61460dd6 124 * @param int $maxdays maximum number of days to be processed
125 * @return boolean success
126 */
127function stats_cron_daily($maxdays=1) {
d23a798a 128 global $CFG, $DB;
6bb1f0bf 129 require_once($CFG->libdir.'/adminlib.php');
f3221af9 130
61460dd6 131 $now = time();
f3221af9 132
6a7fb9f7 133 $fpcontext = context_course::instance(SITEID, MUST_EXIST);
a919ae89 134
61460dd6 135 // read last execution date from db
136 if (!$timestart = get_config(NULL, 'statslastdaily')) {
137 $timestart = stats_get_base_daily(stats_get_start_from('daily'));
138 set_config('statslastdaily', $timestart);
139 }
f3221af9 140
61460dd6 141 $nextmidnight = stats_get_next_day_start($timestart);
f3221af9 142
61460dd6 143 // are there any days that need to be processed?
144 if ($now < $nextmidnight) {
145 return true; // everything ok and up-to-date
f3221af9 146 }
147
61460dd6 148 $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
f3221af9 149
61460dd6 150 if (!set_cron_lock('statsrunning', $now + $timeout)) {
151 return false;
f3221af9 152 }
61460dd6 153
8660ac6e 154 // first delete entries that should not be there yet
d23a798a 155 $DB->delete_records_select('stats_daily', "timeend > $timestart");
156 $DB->delete_records_select('stats_user_daily', "timeend > $timestart");
61460dd6 157
158 // Read in a few things we'll use later
a919ae89
PS
159 $viewactions = stats_get_action_names('view');
160 $postactions = stats_get_action_names('post');
61460dd6 161
a919ae89
PS
162 $guest = (int)$CFG->siteguest;
163 $guestrole = (int)$CFG->guestroleid;
164 $defaultfproleid = (int)$CFG->defaultfrontpageroleid;
61460dd6 165
166 mtrace("Running daily statistics gathering, starting at $timestart:");
658b9372 167 cron_trace_time_and_memory();
61460dd6 168
9c1600f1
TB
169 $days = 0;
170 $total = 0;
171 $failed = false; // failed stats flag
172 $timeout = false;
173
174 if (!stats_temp_table_create()) {
175 $days = 1;
176 $failed = true;
177 }
aa600c47
TB
178 mtrace('Temporary tables created');
179
180 if(!stats_temp_table_setup()) {
181 $days = 1;
182 $failed = true;
183 }
184 mtrace('Enrolments calculated');
f3221af9 185
9c1600f1
TB
186 $totalactiveusers = $DB->count_records('user', array('deleted' => '0'));
187
188 while (!$failed && ($now > $nextmidnight)) {
61460dd6 189 if ($days >= $maxdays) {
9c1600f1
TB
190 $timeout = true;
191 break;
61460dd6 192 }
193
194 $days++;
3ef7279f 195 core_php_time_limit::raise($timeout - 200);
61460dd6 196
197 if ($days > 1) {
198 // move the lock
199 set_cron_lock('statsrunning', time() + $timeout, true);
200 }
201
67ce5270 202 $daystart = time();
203
9c1600f1 204 stats_progress('init');
61460dd6 205
9c1600f1
TB
206 if (!stats_temp_table_fill($timestart, $nextmidnight)) {
207 $failed = true;
208 break;
209 }
583a19d7 210
9c1600f1
TB
211 // Find out if any logs available for this day
212 $sql = "SELECT 'x' FROM {temp_log1} l";
d23a798a 213 $logspresent = $DB->get_records_sql($sql, null, 0, 1);
67ce5270 214
c0f00c5f
TB
215 if ($logspresent) {
216 // Insert blank record to force Query 10 to generate additional row when no logs for
217 // the site with userid 0 exist. Added for backwards compatibility.
218 $DB->insert_record('temp_log1', array('userid' => 0, 'course' => SITEID, 'action' => ''));
219 }
220
9c1600f1
TB
221 // Calculate the number of active users today
222 $sql = 'SELECT COUNT(DISTINCT u.id)
223 FROM {user} u
224 JOIN {temp_log1} l ON l.userid = u.id
225 WHERE u.deleted = 0';
226 $dailyactiveusers = $DB->count_records_sql($sql);
227
c0f00c5f
TB
228 stats_progress('0');
229
9c1600f1
TB
230 // Process login info first
231 // Note: PostgreSQL doesn't like aliases in HAVING clauses
0934f5c3 232 $sql = "INSERT INTO {temp_stats_user_daily}
9c1600f1
TB
233 (stattype, timeend, courseid, userid, statsreads)
234
235 SELECT 'logins', $nextmidnight AS timeend, ".SITEID." AS courseid,
236 userid, COUNT(id) AS statsreads
237 FROM {temp_log1} l
238 WHERE action = 'login'
68203f45 239 GROUP BY userid
9c1600f1 240 HAVING COUNT(id) > 0";
61460dd6 241
9c1600f1 242 if ($logspresent && !stats_run_query($sql)) {
61460dd6 243 $failed = true;
244 break;
245 }
cc67c6db 246 $DB->update_temp_table_stats();
9c1600f1
TB
247
248 stats_progress('1');
61460dd6 249
0934f5c3 250 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 251
9c1600f1
TB
252 SELECT 'logins' AS stattype, $nextmidnight AS timeend, ".SITEID." AS courseid, 0,
253 COALESCE(SUM(statsreads), 0) as stat1, COUNT('x') as stat2
0934f5c3 254 FROM {temp_stats_user_daily}
9c1600f1 255 WHERE stattype = 'logins' AND timeend = $nextmidnight";
61460dd6 256
9c1600f1 257 if ($logspresent && !stats_run_query($sql)) {
61460dd6 258 $failed = true;
259 break;
260 }
9c1600f1 261 stats_progress('2');
61460dd6 262
263
264 // Enrolments and active enrolled users
265 //
266 // Unfortunately, we do not know how many users were registered
267 // at given times in history :-(
268 // - stat1: enrolled users
269 // - stat2: enrolled users active in this period
8660ac6e 270 // - SITEID is special case here, because it's all about default enrolment
61460dd6 271 // in that case, we'll count non-deleted users.
272 //
273
0934f5c3 274 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 275
aa600c47
TB
276 SELECT 'enrolments' as stattype, $nextmidnight as timeend, courseid, roleid,
277 COUNT(DISTINCT userid) as stat1, 0 as stat2
278 FROM {temp_enroled}
9c1600f1 279 GROUP BY courseid, roleid";
61460dd6 280
aa600c47 281 if (!stats_run_query($sql)) {
61460dd6 282 $failed = true;
283 break;
284 }
9c1600f1 285 stats_progress('3');
61460dd6 286
4614b029 287 // Set stat2 to the number distinct users with role assignments in the course that were active
61460dd6 288 // using table alias in UPDATE does not work in pg < 8.2
0934f5c3 289 $sql = "UPDATE {temp_stats_daily}
9c1600f1
TB
290 SET stat2 = (
291
aa600c47
TB
292 SELECT COUNT(DISTINCT userid)
293 FROM {temp_enroled} te
294 WHERE roleid = {temp_stats_daily}.roleid
295 AND courseid = {temp_stats_daily}.courseid
9c1600f1
TB
296 AND EXISTS (
297
298 SELECT 'x'
299 FROM {temp_log1} l
0934f5c3 300 WHERE l.course = {temp_stats_daily}.courseid
aa600c47 301 AND l.userid = te.userid
9c1600f1 302 )
aa600c47
TB
303 )
304 WHERE {temp_stats_daily}.stattype = 'enrolments'
305 AND {temp_stats_daily}.timeend = $nextmidnight
306 AND {temp_stats_daily}.courseid IN (
9c1600f1 307
aa600c47 308 SELECT DISTINCT course FROM {temp_log2})";
9c1600f1 309
4614b029 310 if ($logspresent && !stats_run_query($sql, array('courselevel'=>CONTEXT_COURSE))) {
61460dd6 311 $failed = true;
312 break;
313 }
9c1600f1 314 stats_progress('4');
61460dd6 315
9c1600f1 316 // Now get course total enrolments (roleid==0) - except frontpage
0934f5c3 317 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 318
aa600c47 319 SELECT 'enrolments', $nextmidnight AS timeend, te.courseid AS courseid, 0 AS roleid,
9c1600f1 320 COUNT(DISTINCT userid) AS stat1, 0 AS stat2
aa600c47 321 FROM {temp_enroled} te
9c1600f1 322 GROUP BY courseid
98c00d75 323 HAVING COUNT(DISTINCT userid) > 0";
61460dd6 324
9c1600f1 325 if ($logspresent && !stats_run_query($sql)) {
61460dd6 326 $failed = true;
327 break;
328 }
9c1600f1 329 stats_progress('5');
61460dd6 330
4614b029 331 // Set stat 2 to the number of enrolled users who were active in the course
0934f5c3 332 $sql = "UPDATE {temp_stats_daily}
9c1600f1
TB
333 SET stat2 = (
334
aa600c47
TB
335 SELECT COUNT(DISTINCT te.userid)
336 FROM {temp_enroled} te
337 WHERE te.courseid = {temp_stats_daily}.courseid
338 AND EXISTS (
9c1600f1 339
aa600c47
TB
340 SELECT 'x'
341 FROM {temp_log1} l
342 WHERE l.course = {temp_stats_daily}.courseid
343 AND l.userid = te.userid
344 )
345 )
9c1600f1 346
0934f5c3
TB
347 WHERE {temp_stats_daily}.stattype = 'enrolments'
348 AND {temp_stats_daily}.timeend = $nextmidnight
349 AND {temp_stats_daily}.roleid = 0
350 AND {temp_stats_daily}.courseid IN (
9c1600f1 351
aa600c47
TB
352 SELECT l.course
353 FROM {temp_log2} l
354 WHERE l.course <> ".SITEID.")";
9c1600f1
TB
355
356 if ($logspresent && !stats_run_query($sql, array())) {
61460dd6 357 $failed = true;
358 break;
359 }
9c1600f1 360 stats_progress('6');
61460dd6 361
9c1600f1 362 // Frontpage(==site) enrolments total
0934f5c3 363 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 364
9c1600f1
TB
365 SELECT 'enrolments', $nextmidnight, ".SITEID.", 0, $totalactiveusers AS stat1,
366 $dailyactiveusers AS stat2" .
98c00d75 367 $DB->sql_null_from_clause();
61460dd6 368
9c1600f1 369 if ($logspresent && !stats_run_query($sql)) {
61460dd6 370 $failed = true;
371 break;
372 }
cc67c6db
RS
373 // The steps up until this point, all add to {temp_stats_daily} and don't use new tables.
374 // There is no point updating statistics as they won't be used until the DELETE below.
375 $DB->update_temp_table_stats();
376
9c1600f1 377 stats_progress('7');
61460dd6 378
9c1600f1 379 // Default frontpage role enrolments are all site users (not deleted)
61460dd6 380 if ($defaultfproleid) {
381 // first remove default frontpage role counts if created by previous query
382 $sql = "DELETE
0934f5c3 383 FROM {temp_stats_daily}
9c1600f1
TB
384 WHERE stattype = 'enrolments'
385 AND courseid = ".SITEID."
386 AND roleid = $defaultfproleid
387 AND timeend = $nextmidnight";
0934f5c3 388
9c1600f1 389 if ($logspresent && !stats_run_query($sql)) {
61460dd6 390 $failed = true;
391 break;
f3221af9 392 }
9c1600f1 393 stats_progress('8');
61460dd6 394
0934f5c3 395 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 396
4614b029 397 SELECT 'enrolments', $nextmidnight, ".SITEID.", $defaultfproleid,
9c1600f1 398 $totalactiveusers AS stat1, $dailyactiveusers AS stat2" .
0e35ba6f 399 $DB->sql_null_from_clause();
61460dd6 400
9c1600f1 401 if ($logspresent && !stats_run_query($sql)) {
61460dd6 402 $failed = true;
403 break;
f3221af9 404 }
9c1600f1 405 stats_progress('9');
61460dd6 406
407 } else {
9c1600f1
TB
408 stats_progress('x');
409 stats_progress('x');
f3221af9 410 }
f3221af9 411
61460dd6 412
4614b029 413 /// individual user stats (including not-logged-in) in each course, this is slow - reuse this data if possible
cf717dc2
PS
414 list($viewactionssql, $params1) = $DB->get_in_or_equal($viewactions, SQL_PARAMS_NAMED, 'view');
415 list($postactionssql, $params2) = $DB->get_in_or_equal($postactions, SQL_PARAMS_NAMED, 'post');
0934f5c3 416 $sql = "INSERT INTO {temp_stats_user_daily} (stattype, timeend, courseid, userid, statsreads, statswrites)
61460dd6 417
9c1600f1
TB
418 SELECT 'activity' AS stattype, $nextmidnight AS timeend, course AS courseid, userid,
419 SUM(CASE WHEN action $viewactionssql THEN 1 ELSE 0 END) AS statsreads,
420 SUM(CASE WHEN action $postactionssql THEN 1 ELSE 0 END) AS statswrites
421 FROM {temp_log1} l
68203f45 422 GROUP BY userid, course";
9c1600f1
TB
423
424 if ($logspresent && !stats_run_query($sql, array_merge($params1, $params2))) {
61460dd6 425 $failed = true;
f3221af9 426 break;
427 }
9c1600f1 428 stats_progress('10');
61460dd6 429
430
4614b029 431 /// How many view/post actions in each course total
0934f5c3 432 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 433
434 SELECT 'activity' AS stattype, $nextmidnight AS timeend, c.id AS courseid, 0,
9c1600f1
TB
435 SUM(CASE WHEN l.action $viewactionssql THEN 1 ELSE 0 END) AS stat1,
436 SUM(CASE WHEN l.action $postactionssql THEN 1 ELSE 0 END) AS stat2
437 FROM {course} c, {temp_log1} l
438 WHERE l.course = c.id
68203f45 439 GROUP BY c.id";
9c1600f1
TB
440
441 if ($logspresent && !stats_run_query($sql, array_merge($params1, $params2))) {
61460dd6 442 $failed = true;
443 break;
444 }
9c1600f1 445 stats_progress('11');
f3221af9 446
f3221af9 447
4614b029 448 /// how many view actions for each course+role - excluding guests and frontpage
f3221af9 449
0934f5c3 450 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
f3221af9 451
9c1600f1 452 SELECT 'activity', $nextmidnight AS timeend, courseid, roleid, SUM(statsreads), SUM(statswrites)
98c00d75 453 FROM (
9c1600f1
TB
454
455 SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
0934f5c3 456 FROM {temp_stats_user_daily} sud, (
9c1600f1 457
aa600c47
TB
458 SELECT DISTINCT te.userid, te.roleid, te.courseid
459 FROM {temp_enroled} te
460 WHERE te.roleid <> $guestrole
461 AND te.userid <> $guest
462 ) pl
463
9c1600f1
TB
464 WHERE sud.userid = pl.userid
465 AND sud.courseid = pl.courseid
0934f5c3
TB
466 AND sud.timeend = $nextmidnight
467 AND sud.stattype='activity'
98c00d75 468 ) inline_view
9c1600f1 469
68203f45 470 GROUP BY courseid, roleid
98c00d75 471 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
f3221af9 472
9c1600f1 473 if ($logspresent && !stats_run_query($sql, array('courselevel'=>CONTEXT_COURSE))) {
61460dd6 474 $failed = true;
475 break;
476 }
9c1600f1 477 stats_progress('12');
61460dd6 478
4614b029
TB
479 /// how many view actions from guests only in each course - excluding frontpage
480 /// normal users may enter course with temporary guest access too
61460dd6 481
0934f5c3 482 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 483
9c1600f1
TB
484 SELECT 'activity', $nextmidnight AS timeend, courseid, $guestrole AS roleid,
485 SUM(statsreads), SUM(statswrites)
98c00d75 486 FROM (
9c1600f1
TB
487
488 SELECT sud.courseid, sud.statsreads, sud.statswrites
0934f5c3 489 FROM {temp_stats_user_daily} sud
9c1600f1
TB
490 WHERE sud.timeend = $nextmidnight
491 AND sud.courseid <> ".SITEID."
492 AND sud.stattype='activity'
493 AND (sud.userid = $guest OR sud.userid NOT IN (
494
aa600c47
TB
495 SELECT userid
496 FROM {temp_enroled} te
497 WHERE te.courseid = sud.courseid
9c1600f1 498 ))
98c00d75 499 ) inline_view
9c1600f1 500
68203f45 501 GROUP BY courseid
98c00d75 502 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
61460dd6 503
9c1600f1 504 if ($logspresent && !stats_run_query($sql, array())) {
61460dd6 505 $failed = true;
506 break;
507 }
9c1600f1 508 stats_progress('13');
61460dd6 509
510
4614b029 511 /// How many view actions for each role on frontpage - excluding guests, not-logged-in and default frontpage role
0934f5c3 512 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 513
9c1600f1
TB
514 SELECT 'activity', $nextmidnight AS timeend, courseid, roleid,
515 SUM(statsreads), SUM(statswrites)
98c00d75 516 FROM (
9c1600f1 517 SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
0934f5c3 518 FROM {temp_stats_user_daily} sud, (
9c1600f1
TB
519
520 SELECT DISTINCT ra.userid, ra.roleid, c.instanceid AS courseid
521 FROM {role_assignments} ra
522 JOIN {context} c ON c.id = ra.contextid
523 WHERE ra.contextid = :fpcontext
524 AND ra.roleid <> $defaultfproleid
525 AND ra.roleid <> $guestrole
526 AND ra.userid <> $guest
527 ) pl
528 WHERE sud.userid = pl.userid
529 AND sud.courseid = pl.courseid
530 AND sud.timeend = $nextmidnight
531 AND sud.stattype='activity'
98c00d75 532 ) inline_view
9c1600f1 533
68203f45 534 GROUP BY courseid, roleid
98c00d75 535 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
61460dd6 536
9c1600f1 537 if ($logspresent && !stats_run_query($sql, array('fpcontext'=>$fpcontext->id))) {
61460dd6 538 $failed = true;
539 break;
540 }
9c1600f1 541 stats_progress('14');
61460dd6 542
543
4614b029 544 // How many view actions for default frontpage role on frontpage only
0934f5c3 545 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 546
9c1600f1
TB
547 SELECT 'activity', timeend, courseid, $defaultfproleid AS roleid,
548 SUM(statsreads), SUM(statswrites)
98c00d75 549 FROM (
9c1600f1 550 SELECT sud.timeend AS timeend, sud.courseid, sud.statsreads, sud.statswrites
0934f5c3
TB
551 FROM {temp_stats_user_daily} sud
552 WHERE sud.timeend = :nextm
553 AND sud.courseid = :siteid
9c1600f1
TB
554 AND sud.stattype='activity'
555 AND sud.userid <> $guest
556 AND sud.userid <> 0
557 AND sud.userid NOT IN (
558
559 SELECT ra.userid
560 FROM {role_assignments} ra
561 WHERE ra.roleid <> $guestrole
562 AND ra.roleid <> $defaultfproleid
563 AND ra.contextid = :fpcontext)
98c00d75 564 ) inline_view
9c1600f1 565
68203f45 566 GROUP BY timeend, courseid
98c00d75 567 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
61460dd6 568
9c1600f1 569 if ($logspresent && !stats_run_query($sql, array('fpcontext'=>$fpcontext->id, 'siteid'=>SITEID, 'nextm'=>$nextmidnight))) {
61460dd6 570 $failed = true;
571 break;
572 }
cc67c6db 573 $DB->update_temp_table_stats();
9c1600f1 574 stats_progress('15');
61460dd6 575
4614b029 576 // How many view actions for guests or not-logged-in on frontpage
0934f5c3 577 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 578
68203f45
TB
579 SELECT stattype, timeend, courseid, $guestrole AS roleid,
580 SUM(statsreads) AS stat1, SUM(statswrites) AS stat2
98c00d75 581 FROM (
68203f45
TB
582 SELECT sud.stattype, sud.timeend, sud.courseid,
583 sud.statsreads, sud.statswrites
0934f5c3 584 FROM {temp_stats_user_daily} sud
9c1600f1
TB
585 WHERE (sud.userid = $guest OR sud.userid = 0)
586 AND sud.timeend = $nextmidnight
587 AND sud.courseid = ".SITEID."
588 AND sud.stattype='activity'
68203f45 589 ) inline_view
6eff995f 590 GROUP BY stattype, timeend, courseid
68203f45 591 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
61460dd6 592
9c1600f1 593 if ($logspresent && !stats_run_query($sql)) {
61460dd6 594 $failed = true;
595 break;
596 }
9c1600f1
TB
597 stats_progress('16');
598
599 stats_temp_table_clean();
600
601 stats_progress('out');
f3221af9 602
61460dd6 603 // remember processed days
604 set_config('statslastdaily', $nextmidnight);
9c1600f1
TB
605 $elapsed = time()-$daystart;
606 mtrace(" finished until $nextmidnight: ".userdate($nextmidnight)." (in $elapsed s)");
607 $total += $elapsed;
61460dd6 608
609 $timestart = $nextmidnight;
610 $nextmidnight = stats_get_next_day_start($nextmidnight);
f3221af9 611 }
f3221af9 612
9c1600f1
TB
613 stats_temp_table_drop();
614
61460dd6 615 set_cron_lock('statsrunning', null);
616
617 if ($failed) {
618 $days--;
4614b029 619 mtrace("...error occurred, completed $days days of statistics in {$total} s.");
61460dd6 620 return false;
621
9c1600f1 622 } else if ($timeout) {
4614b029 623 mtrace("...stopping early, reached maximum number of $maxdays days ({$total} s) - will continue next time.");
9c1600f1
TB
624 return false;
625
61460dd6 626 } else {
9c1600f1 627 mtrace("...completed $days days of statistics in {$total} s.");
61460dd6 628 return true;
f3221af9 629 }
61460dd6 630}
631
632
633/**
634 * Execute weekly statistics gathering
635 * @return boolean success
636 */
637function stats_cron_weekly() {
d23a798a 638 global $CFG, $DB;
6bb1f0bf 639 require_once($CFG->libdir.'/adminlib.php');
f3221af9 640
61460dd6 641 $now = time();
f3221af9 642
61460dd6 643 // read last execution date from db
644 if (!$timestart = get_config(NULL, 'statslastweekly')) {
645 $timestart = stats_get_base_daily(stats_get_start_from('weekly'));
646 set_config('statslastweekly', $timestart);
647 }
f3221af9 648
61460dd6 649 $nextstartweek = stats_get_next_week_start($timestart);
650
651 // are there any weeks that need to be processed?
652 if ($now < $nextstartweek) {
653 return true; // everything ok and up-to-date
f3221af9 654 }
655
61460dd6 656 $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
f3221af9 657
61460dd6 658 if (!set_cron_lock('statsrunning', $now + $timeout)) {
659 return false;
f3221af9 660 }
f3221af9 661
61460dd6 662 // fisrt delete entries that should not be there yet
d23a798a 663 $DB->delete_records_select('stats_weekly', "timeend > $timestart");
664 $DB->delete_records_select('stats_user_weekly', "timeend > $timestart");
61460dd6 665
666 mtrace("Running weekly statistics gathering, starting at $timestart:");
658b9372 667 cron_trace_time_and_memory();
61460dd6 668
669 $weeks = 0;
670 while ($now > $nextstartweek) {
3ef7279f 671 core_php_time_limit::raise($timeout - 200);
f3221af9 672 $weeks++;
673
61460dd6 674 if ($weeks > 1) {
675 // move the lock
676 set_cron_lock('statsrunning', time() + $timeout, true);
f3221af9 677 }
61460dd6 678
61460dd6 679 $stattimesql = "timeend > $timestart AND timeend <= $nextstartweek";
680
9c1600f1
TB
681 $weekstart = time();
682 stats_progress('init');
683
61460dd6 684 /// process login info first
d23a798a 685 $sql = "INSERT INTO {stats_user_weekly} (stattype, timeend, courseid, userid, statsreads)
61460dd6 686
e6f0b5f1 687 SELECT 'logins', timeend, courseid, userid, SUM(statsreads)
98c00d75 688 FROM (
041a1de0 689 SELECT $nextstartweek AS timeend, courseid, userid, statsreads
e6f0b5f1
690 FROM {stats_user_daily} sd
691 WHERE stattype = 'logins' AND $stattimesql
98c00d75 692 ) inline_view
693 GROUP BY timeend, courseid, userid
e6f0b5f1 694 HAVING SUM(statsreads) > 0";
61460dd6 695
d23a798a 696 $DB->execute($sql);
61460dd6 697
9c1600f1
TB
698 stats_progress('1');
699
d23a798a 700 $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 701
702 SELECT 'logins' AS stattype, $nextstartweek AS timeend, ".SITEID." as courseid, 0,
703 COALESCE((SELECT SUM(statsreads)
d23a798a 704 FROM {stats_user_weekly} s1
61460dd6 705 WHERE s1.stattype = 'logins' AND timeend = $nextstartweek), 0) AS nstat1,
cdcd7283 706 (SELECT COUNT('x')
d23a798a 707 FROM {stats_user_weekly} s2
98c00d75 708 WHERE s2.stattype = 'logins' AND timeend = $nextstartweek) AS nstat2" .
709 $DB->sql_null_from_clause();
61460dd6 710
d23a798a 711 $DB->execute($sql);
61460dd6 712
9c1600f1 713 stats_progress('2');
61460dd6 714
715 /// now enrolments averages
d23a798a 716 $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 717
dfc05efe 718 SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . "
98c00d75 719 FROM (
720 SELECT $nextstartweek AS ntimeend, courseid, roleid, stat1, stat2
721 FROM {stats_daily} sd
722 WHERE stattype = 'enrolments' AND $stattimesql
723 ) inline_view
724 GROUP BY ntimeend, courseid, roleid";
61460dd6 725
d23a798a 726 $DB->execute($sql);
61460dd6 727
9c1600f1 728 stats_progress('3');
61460dd6 729
730 /// activity read/write averages
d23a798a 731 $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 732
98c00d75 733 SELECT 'activity', ntimeend, courseid, roleid, SUM(stat1), SUM(stat2)
734 FROM (
735 SELECT $nextstartweek AS ntimeend, courseid, roleid, stat1, stat2
736 FROM {stats_daily}
737 WHERE stattype = 'activity' AND $stattimesql
738 ) inline_view
739 GROUP BY ntimeend, courseid, roleid";
61460dd6 740
d23a798a 741 $DB->execute($sql);
61460dd6 742
9c1600f1 743 stats_progress('4');
61460dd6 744
745 /// user read/write averages
d23a798a 746 $sql = "INSERT INTO {stats_user_weekly} (stattype, timeend, courseid, userid, statsreads, statswrites)
61460dd6 747
98c00d75 748 SELECT 'activity', ntimeend, courseid, userid, SUM(statsreads), SUM(statswrites)
749 FROM (
750 SELECT $nextstartweek AS ntimeend, courseid, userid, statsreads, statswrites
751 FROM {stats_user_daily}
752 WHERE stattype = 'activity' AND $stattimesql
753 ) inline_view
754 GROUP BY ntimeend, courseid, userid";
61460dd6 755
d23a798a 756 $DB->execute($sql);
61460dd6 757
9c1600f1
TB
758 stats_progress('5');
759
61460dd6 760 set_config('statslastweekly', $nextstartweek);
9c1600f1 761 $elapsed = time()-$weekstart;
4614b029 762 mtrace(" finished until $nextstartweek: ".userdate($nextstartweek) ." (in $elapsed s)");
61460dd6 763
764 $timestart = $nextstartweek;
765 $nextstartweek = stats_get_next_week_start($nextstartweek);
f3221af9 766 }
61460dd6 767
768 set_cron_lock('statsrunning', null);
769 mtrace("...completed $weeks weeks of statistics.");
770 return true;
f3221af9 771}
f3221af9 772
61460dd6 773/**
774 * Execute monthly statistics gathering
775 * @return boolean success
776 */
777function stats_cron_monthly() {
d23a798a 778 global $CFG, $DB;
6bb1f0bf 779 require_once($CFG->libdir.'/adminlib.php');
f3221af9 780
61460dd6 781 $now = time();
782
783 // read last execution date from db
784 if (!$timestart = get_config(NULL, 'statslastmonthly')) {
785 $timestart = stats_get_base_monthly(stats_get_start_from('monthly'));
786 set_config('statslastmonthly', $timestart);
f3221af9 787 }
788
61460dd6 789 $nextstartmonth = stats_get_next_month_start($timestart);
790
791 // are there any months that need to be processed?
792 if ($now < $nextstartmonth) {
793 return true; // everything ok and up-to-date
f3221af9 794 }
61460dd6 795
796 $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
797
798 if (!set_cron_lock('statsrunning', $now + $timeout)) {
799 return false;
f3221af9 800 }
f3221af9 801
61460dd6 802 // fisr delete entries that should not be there yet
d23a798a 803 $DB->delete_records_select('stats_monthly', "timeend > $timestart");
804 $DB->delete_records_select('stats_user_monthly', "timeend > $timestart");
f3221af9 805
61460dd6 806 $startmonth = stats_get_base_monthly($now);
807
808
809 mtrace("Running monthly statistics gathering, starting at $timestart:");
658b9372 810 cron_trace_time_and_memory();
61460dd6 811
812 $months = 0;
813 while ($now > $nextstartmonth) {
3ef7279f 814 core_php_time_limit::raise($timeout - 200);
61460dd6 815 $months++;
816
817 if ($months > 1) {
818 // move the lock
819 set_cron_lock('statsrunning', time() + $timeout, true);
f3221af9 820 }
61460dd6 821
61460dd6 822 $stattimesql = "timeend > $timestart AND timeend <= $nextstartmonth";
823
9c1600f1
TB
824 $monthstart = time();
825 stats_progress('init');
826
61460dd6 827 /// process login info first
d23a798a 828 $sql = "INSERT INTO {stats_user_monthly} (stattype, timeend, courseid, userid, statsreads)
61460dd6 829
e6f0b5f1 830 SELECT 'logins', timeend, courseid, userid, SUM(statsreads)
98c00d75 831 FROM (
041a1de0 832 SELECT $nextstartmonth AS timeend, courseid, userid, statsreads
e6f0b5f1
833 FROM {stats_user_daily} sd
834 WHERE stattype = 'logins' AND $stattimesql
98c00d75 835 ) inline_view
e6f0b5f1
836 GROUP BY timeend, courseid, userid
837 HAVING SUM(statsreads) > 0";
61460dd6 838
d23a798a 839 $DB->execute($sql);
61460dd6 840
9c1600f1
TB
841 stats_progress('1');
842
d23a798a 843 $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 844
845 SELECT 'logins' AS stattype, $nextstartmonth AS timeend, ".SITEID." as courseid, 0,
846 COALESCE((SELECT SUM(statsreads)
d23a798a 847 FROM {stats_user_monthly} s1
61460dd6 848 WHERE s1.stattype = 'logins' AND timeend = $nextstartmonth), 0) AS nstat1,
cdcd7283 849 (SELECT COUNT('x')
d23a798a 850 FROM {stats_user_monthly} s2
98c00d75 851 WHERE s2.stattype = 'logins' AND timeend = $nextstartmonth) AS nstat2" .
852 $DB->sql_null_from_clause();
61460dd6 853
d23a798a 854 $DB->execute($sql);
61460dd6 855
9c1600f1 856 stats_progress('2');
61460dd6 857
858 /// now enrolments averages
d23a798a 859 $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 860
dfc05efe 861 SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . "
98c00d75 862 FROM (
863 SELECT $nextstartmonth AS ntimeend, courseid, roleid, stat1, stat2
864 FROM {stats_daily} sd
865 WHERE stattype = 'enrolments' AND $stattimesql
866 ) inline_view
867 GROUP BY ntimeend, courseid, roleid";
61460dd6 868
d23a798a 869 $DB->execute($sql);
61460dd6 870
9c1600f1 871 stats_progress('3');
61460dd6 872
873 /// activity read/write averages
d23a798a 874 $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
61460dd6 875
98c00d75 876 SELECT 'activity', ntimeend, courseid, roleid, SUM(stat1), SUM(stat2)
877 FROM (
878 SELECT $nextstartmonth AS ntimeend, courseid, roleid, stat1, stat2
879 FROM {stats_daily}
880 WHERE stattype = 'activity' AND $stattimesql
881 ) inline_view
882 GROUP BY ntimeend, courseid, roleid";
61460dd6 883
d23a798a 884 $DB->execute($sql);
61460dd6 885
9c1600f1 886 stats_progress('4');
61460dd6 887
888 /// user read/write averages
d23a798a 889 $sql = "INSERT INTO {stats_user_monthly} (stattype, timeend, courseid, userid, statsreads, statswrites)
61460dd6 890
98c00d75 891 SELECT 'activity', ntimeend, courseid, userid, SUM(statsreads), SUM(statswrites)
892 FROM (
893 SELECT $nextstartmonth AS ntimeend, courseid, userid, statsreads, statswrites
894 FROM {stats_user_daily}
895 WHERE stattype = 'activity' AND $stattimesql
896 ) inline_view
897 GROUP BY ntimeend, courseid, userid";
61460dd6 898
d23a798a 899 $DB->execute($sql);
61460dd6 900
9c1600f1
TB
901 stats_progress('5');
902
61460dd6 903 set_config('statslastmonthly', $nextstartmonth);
9c1600f1
TB
904 $elapsed = time() - $monthstart;
905 mtrace(" finished until $nextstartmonth: ".userdate($nextstartmonth) ." (in $elapsed s)");
61460dd6 906
907 $timestart = $nextstartmonth;
908 $nextstartmonth = stats_get_next_month_start($nextstartmonth);
f3221af9 909 }
f3221af9 910
61460dd6 911 set_cron_lock('statsrunning', null);
912 mtrace("...completed $months months of statistics.");
913 return true;
914}
915
61460dd6 916/**
917 * Return starting date of stats processing
918 * @param string $str name of table - daily, weekly or monthly
919 * @return int timestamp
920 */
f3221af9 921function stats_get_start_from($str) {
d23a798a 922 global $CFG, $DB;
f3221af9 923
61460dd6 924 // are there any data in stats table? Should not be...
5b903967 925 if ($timeend = $DB->get_field_sql('SELECT MAX(timeend) FROM {stats_'.$str.'}')) {
f3221af9 926 return $timeend;
927 }
f3221af9 928 // decide what to do based on our config setting (either all or none or a timestamp)
f3221af9 929 switch ($CFG->statsfirstrun) {
61460dd6 930 case 'all':
92927fba
931 $manager = get_log_manager();
932 $stores = $manager->get_readers();
933 $firstlog = false;
934 foreach ($stores as $store) {
59aebbed 935 if ($store instanceof \core\log\sql_internal_table_reader) {
92927fba
936 $logtable = $store->get_internal_log_table_name();
937 if (!$logtable) {
938 continue;
939 }
940 $first = $DB->get_field_sql("SELECT MIN(timecreated) FROM {{$logtable}}");
941 if ($first and (!$firstlog or $firstlog > $first)) {
942 $firstlog = $first;
943 }
944 }
945 }
946
947 $first = $DB->get_field_sql('SELECT MIN(time) FROM {log}');
948 if ($first and (!$firstlog or $firstlog > $first)) {
949 $firstlog = $first;
950 }
951
952 if ($firstlog) {
61460dd6 953 return $firstlog;
954 }
92927fba 955
f3221af9 956 default:
957 if (is_numeric($CFG->statsfirstrun)) {
61460dd6 958 return time() - $CFG->statsfirstrun;
f3221af9 959 }
61460dd6 960 // not a number? use next instead
961 case 'none':
962 return strtotime('-3 day', time());
f3221af9 963 }
964}
965
61460dd6 966/**
967 * Start of day
968 * @param int $time timestamp
d6e7a63d 969 * @return int start of day
61460dd6 970 */
f3221af9 971function stats_get_base_daily($time=0) {
972 if (empty($time)) {
973 $time = time();
974 }
d6e7a63d
PS
975
976 core_date::set_default_server_timezone();
977 $time = strtotime(date('d-M-Y', $time));
978
979 return $time;
f3221af9 980}
981
61460dd6 982/**
983 * Start of week
984 * @param int $time timestamp
d6e7a63d 985 * @return int start of week
61460dd6 986 */
f3221af9 987function stats_get_base_weekly($time=0) {
61460dd6 988 global $CFG;
f3221af9 989
61460dd6 990 $time = stats_get_base_daily($time);
991 $startday = $CFG->calendar_startwday;
d6e7a63d
PS
992
993 core_date::set_default_server_timezone();
994 $thisday = date('w', $time);
995
61460dd6 996 if ($thisday > $startday) {
997 $time = $time - (($thisday - $startday) * 60*60*24);
998 } else if ($thisday < $startday) {
999 $time = $time - ((7 + $thisday - $startday) * 60*60*24);
1000 }
1001 return $time;
f3221af9 1002}
1003
61460dd6 1004/**
1005 * Start of month
1006 * @param int $time timestamp
d6e7a63d 1007 * @return int start of month
61460dd6 1008 */
f3221af9 1009function stats_get_base_monthly($time=0) {
1010 if (empty($time)) {
1011 $time = time();
1012 }
61460dd6 1013
d6e7a63d
PS
1014 core_date::set_default_server_timezone();
1015 $return = strtotime(date('1-M-Y', $time));
1016
1017 return $return;
f3221af9 1018}
1019
61460dd6 1020/**
1021 * Start of next day
1022 * @param int $time timestamp
1023 * @return start of next day
1024 */
1025function stats_get_next_day_start($time) {
1026 $next = stats_get_base_daily($time);
3cdc35b4
AG
1027 $nextdate = new DateTime();
1028 $nextdate->setTimestamp($next);
1029 $nextdate->add(new DateInterval('P1D'));
1030 return $nextdate->getTimestamp();
4d8e4c38 1031}
1032
61460dd6 1033/**
1034 * Start of next week
1035 * @param int $time timestamp
1036 * @return start of next week
1037 */
1038function stats_get_next_week_start($time) {
1039 $next = stats_get_base_weekly($time);
3cdc35b4
AG
1040 $nextdate = new DateTime();
1041 $nextdate->setTimestamp($next);
1042 $nextdate->add(new DateInterval('P1W'));
1043 return $nextdate->getTimestamp();
4d8e4c38 1044}
1045
61460dd6 1046/**
1047 * Start of next month
1048 * @param int $time timestamp
1049 * @return start of next month
1050 */
1051function stats_get_next_month_start($time) {
1052 $next = stats_get_base_monthly($time);
3cdc35b4
AG
1053 $nextdate = new DateTime();
1054 $nextdate->setTimestamp($next);
1055 $nextdate->add(new DateInterval('P1M'));
1056 return $nextdate->getTimestamp();
f3221af9 1057}
1058
61460dd6 1059/**
1060 * Remove old stats data
1061 */
f3221af9 1062function stats_clean_old() {
d23a798a 1063 global $DB;
61460dd6 1064 mtrace("Running stats cleanup tasks...");
658b9372 1065 cron_trace_time_and_memory();
61460dd6 1066 $deletebefore = stats_get_base_monthly();
1067
1068 // delete dailies older than 3 months (to be safe)
1069 $deletebefore = strtotime('-3 months', $deletebefore);
d23a798a 1070 $DB->delete_records_select('stats_daily', "timeend < $deletebefore");
1071 $DB->delete_records_select('stats_user_daily', "timeend < $deletebefore");
61460dd6 1072
1073 // delete weeklies older than 9 months (to be safe)
1074 $deletebefore = strtotime('-6 months', $deletebefore);
d23a798a 1075 $DB->delete_records_select('stats_weekly', "timeend < $deletebefore");
1076 $DB->delete_records_select('stats_user_weekly', "timeend < $deletebefore");
f3221af9 1077
1078 // don't delete monthlies
61460dd6 1079
1080 mtrace("...stats cleanup finished");
f3221af9 1081}
1082
0f259f63 1083function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) {
29f83769 1084 global $CFG, $DB;
61460dd6 1085
365a5941 1086 $param = new stdClass();
f87cab2a 1087 $param->params = array();
61460dd6 1088
f3221af9 1089 if ($time < 10) { // dailies
1090 // number of days to go back = 7* time
f3221af9 1091 $param->table = 'daily';
1092 $param->timeafter = strtotime("-".($time*7)." days",stats_get_base_daily());
1093 } elseif ($time < 20) { // weeklies
1094 // number of weeks to go back = time - 10 * 4 (weeks) + base week
f3221af9 1095 $param->table = 'weekly';
8b658d8a 1096 $param->timeafter = strtotime("-".(($time - 10)*4)." weeks",stats_get_base_weekly());
f3221af9 1097 } else { // monthlies.
1098 // number of months to go back = time - 20 * months + base month
f3221af9 1099 $param->table = 'monthly';
1100 $param->timeafter = strtotime("-".($time - 20)." months",stats_get_base_monthly());
1101 }
1102
3a317277 1103 $param->extras = '';
1104
f3221af9 1105 switch ($report) {
96edb010 1106 // ******************** STATS_MODE_GENERAL ******************** //
1107 case STATS_REPORT_LOGINS:
0dd5bf42 1108 $param->fields = 'timeend,sum(stat1) as line1,sum(stat2) as line2';
1109 $param->fieldscomplete = true;
0f259f63 1110 $param->stattype = 'logins';
f3221af9 1111 $param->line1 = get_string('statslogins');
1112 $param->line2 = get_string('statsuniquelogins');
0dd5bf42 1113 if ($courseid == SITEID) {
1114 $param->extras = 'GROUP BY timeend';
1115 }
f3221af9 1116 break;
96edb010 1117
1118 case STATS_REPORT_READS:
29f83769 1119 $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat1 as line1';
0f259f63 1120 $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
0dd5bf42 1121 $param->aggregategroupby = 'roleid';
0f259f63 1122 $param->stattype = 'activity';
1123 $param->crosstab = true;
1124 $param->extras = 'GROUP BY timeend,roleid,stat1';
0dd5bf42 1125 if ($courseid == SITEID) {
29f83769 1126 $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1) as line1';
0dd5bf42 1127 $param->extras = 'GROUP BY timeend,roleid';
1128 }
f3221af9 1129 break;
96edb010 1130
61460dd6 1131 case STATS_REPORT_WRITES:
29f83769 1132 $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat2 as line1';
0f259f63 1133 $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
0dd5bf42 1134 $param->aggregategroupby = 'roleid';
0f259f63 1135 $param->stattype = 'activity';
1136 $param->crosstab = true;
1137 $param->extras = 'GROUP BY timeend,roleid,stat2';
0dd5bf42 1138 if ($courseid == SITEID) {
29f83769 1139 $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat2) as line1';
0dd5bf42 1140 $param->extras = 'GROUP BY timeend,roleid';
1141 }
f3221af9 1142 break;
96edb010 1143
1144 case STATS_REPORT_ACTIVITY:
29f83769 1145 $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1+stat2) as line1';
0f259f63 1146 $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
0dd5bf42 1147 $param->aggregategroupby = 'roleid';
0f259f63 1148 $param->stattype = 'activity';
1149 $param->crosstab = true;
1150 $param->extras = 'GROUP BY timeend,roleid';
0dd5bf42 1151 if ($courseid == SITEID) {
1152 $param->extras = 'GROUP BY timeend,roleid';
1153 }
f3221af9 1154 break;
96edb010 1155
0f259f63 1156 case STATS_REPORT_ACTIVITYBYROLE;
1157 $param->fields = 'stat1 AS line1, stat2 AS line2';
1158 $param->stattype = 'activity';
4bd112e4
LB
1159 $role = $DB->get_record('role', array('id' => $roleid));
1160 $rolename = role_get_name($role, context_course::instance($courseid));
0f259f63 1161 $param->line1 = $rolename . get_string('statsreads');
1162 $param->line2 = $rolename . get_string('statswrites');
96edb010 1163 if ($courseid == SITEID) {
1164 $param->extras = 'GROUP BY timeend';
1165 }
f3221af9 1166 break;
96edb010 1167
1168 // ******************** STATS_MODE_DETAILED ******************** //
f3221af9 1169 case STATS_REPORT_USER_ACTIVITY:
addcde11 1170 $param->fields = 'statsreads as line1, statswrites as line2';
f3221af9 1171 $param->line1 = get_string('statsuserreads');
1172 $param->line2 = get_string('statsuserwrites');
1173 $param->stattype = 'activity';
1174 break;
96edb010 1175
f3221af9 1176 case STATS_REPORT_USER_ALLACTIVITY:
addcde11 1177 $param->fields = 'statsreads+statswrites as line1';
f3221af9 1178 $param->line1 = get_string('statsuseractivity');
1179 $param->stattype = 'activity';
1180 break;
96edb010 1181
f3221af9 1182 case STATS_REPORT_USER_LOGINS:
addcde11 1183 $param->fields = 'statsreads as line1';
f3221af9 1184 $param->line1 = get_string('statsuserlogins');
1185 $param->stattype = 'logins';
1186 break;
96edb010 1187
f3221af9 1188 case STATS_REPORT_USER_VIEW:
addcde11 1189 $param->fields = 'statsreads as line1, statswrites as line2, statsreads+statswrites as line3';
f3221af9 1190 $param->line1 = get_string('statsuserreads');
1191 $param->line2 = get_string('statsuserwrites');
1192 $param->line3 = get_string('statsuseractivity');
1193 $param->stattype = 'activity';
1194 break;
96edb010 1195
1196 // ******************** STATS_MODE_RANKED ******************** //
61460dd6 1197 case STATS_REPORT_ACTIVE_COURSES:
96edb010 1198 $param->fields = 'sum(stat1+stat2) AS line1';
1199 $param->stattype = 'activity';
3eb9babc 1200 $param->orderby = 'line1 DESC';
f64828f2 1201 $param->line1 = get_string('useractivity');
3eb9babc 1202 $param->graphline = 'line1';
1203 break;
96edb010 1204
3eb9babc 1205 case STATS_REPORT_ACTIVE_COURSES_WEIGHTED:
96edb010 1206 $threshold = 0;
9afaefaa 1207 if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
96edb010 1208 $threshold = $CFG->statsuserthreshold;
9afaefaa 1209 }
96edb010 1210 $param->fields = '';
1211 $param->sql = 'SELECT activity.courseid, activity.all_activity AS line1, enrolments.highest_enrolments AS line2,
61460dd6 1212 activity.all_activity / enrolments.highest_enrolments as line3
96edb010 1213 FROM (
b555127f 1214 SELECT courseid, sum(stat1+stat2) AS all_activity
29f83769 1215 FROM {stats_'.$param->table.'}
a919ae89 1216 WHERE stattype=\'activity\' AND timeend >= '.(int)$param->timeafter.' AND roleid = 0 GROUP BY courseid
96edb010 1217 ) activity
61460dd6 1218 INNER JOIN
96edb010 1219 (
117bd748 1220 SELECT courseid, max(stat1) AS highest_enrolments
29f83769 1221 FROM {stats_'.$param->table.'}
a919ae89 1222 WHERE stattype=\'enrolments\' AND timeend >= '.(int)$param->timeafter.' AND stat1 > '.(int)$threshold.'
61460dd6 1223 GROUP BY courseid
96edb010 1224 ) enrolments
1225 ON (activity.courseid = enrolments.courseid)
96edb010 1226 ORDER BY line3 DESC';
f64828f2 1227 $param->line1 = get_string('useractivity');
3eb9babc 1228 $param->line2 = get_string('users');
1229 $param->line3 = get_string('activityweighted');
1230 $param->graphline = 'line3';
1231 break;
96edb010 1232
3eb9babc 1233 case STATS_REPORT_PARTICIPATORY_COURSES:
96edb010 1234 $threshold = 0;
7b89e189 1235 if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
96edb010 1236 $threshold = $CFG->statsuserthreshold;
7b89e189 1237 }
96edb010 1238 $param->fields = '';
dfc05efe 1239 $param->sql = 'SELECT courseid, ' . $DB->sql_ceil('avg(all_enrolments)') . ' as line1, ' .
1240 $DB->sql_ceil('avg(active_enrolments)') . ' as line2, avg(proportion_active) AS line3
96edb010 1241 FROM (
61460dd6 1242 SELECT courseid, timeend, stat2 as active_enrolments,
29f83769 1243 stat1 as all_enrolments, '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' AS proportion_active
1244 FROM {stats_'.$param->table.'}
a919ae89 1245 WHERE stattype=\'enrolments\' AND roleid = 0 AND stat1 > '.(int)$threshold.'
61460dd6 1246 ) aq
a919ae89 1247 WHERE timeend >= '.(int)$param->timeafter.'
96edb010 1248 GROUP BY courseid
1249 ORDER BY line3 DESC';
1250
3eb9babc 1251 $param->line1 = get_string('users');
1252 $param->line2 = get_string('activeusers');
1253 $param->line3 = get_string('participationratio');
1254 $param->graphline = 'line3';
1255 break;
96edb010 1256
3eb9babc 1257 case STATS_REPORT_PARTICIPATORY_COURSES_RW:
96edb010 1258 $param->fields = '';
1259 $param->sql = 'SELECT courseid, sum(views) AS line1, sum(posts) AS line2,
1260 avg(proportion_active) AS line3
1261 FROM (
61460dd6 1262 SELECT courseid, timeend, stat1 as views, stat2 AS posts,
29f83769 1263 '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' as proportion_active
1264 FROM {stats_'.$param->table.'}
61460dd6 1265 WHERE stattype=\'activity\' AND roleid = 0 AND stat1 > 0
1266 ) aq
a919ae89 1267 WHERE timeend >= '.(int)$param->timeafter.'
96edb010 1268 GROUP BY courseid
1269 ORDER BY line3 DESC';
3eb9babc 1270 $param->line1 = get_string('views');
1271 $param->line2 = get_string('posts');
1272 $param->line3 = get_string('participationratio');
1273 $param->graphline = 'line3';
1274 break;
f3221af9 1275 }
96edb010 1276
0dd5bf42 1277 /*
3eb9babc 1278 if ($courseid == SITEID && $mode != STATS_MODE_RANKED) { // just aggregate all courses.
0dd5bf42 1279 $param->fields = preg_replace('/(?:sum)([a-zA-Z0-9+_]*)\W+as\W+([a-zA-Z0-9_]*)/i','sum($1) as $2',$param->fields);
1280 $param->extras = ' GROUP BY timeend'.((!empty($param->aggregategroupby)) ? ','.$param->aggregategroupby : '');
3a317277 1281 }
0dd5bf42 1282 */
1283 //TODO must add the SITEID reports to the rest of the reports.
6c45af4f 1284 return $param;
61460dd6 1285}
f3221af9 1286
1287function stats_get_view_actions() {
1288 return array('view','view all','history');
1289}
1290
1291function stats_get_post_actions() {
1292 return array('add','delete','edit','add mod','delete mod','edit section'.'enrol','loginas','new','unenrol','update','update mod');
1293}
1294
61460dd6 1295function stats_get_action_names($str) {
d23a798a 1296 global $CFG, $DB;
61460dd6 1297
d23a798a 1298 $mods = $DB->get_records('modules');
f3221af9 1299 $function = 'stats_get_'.$str.'_actions';
1300 $actions = $function();
1301 foreach ($mods as $mod) {
bd903ae9 1302 $file = $CFG->dirroot.'/mod/'.$mod->name.'/lib.php';
1303 if (!is_readable($file)) {
1304 continue;
1305 }
1306 require_once($file);
f3221af9 1307 $function = $mod->name.'_get_'.$str.'_actions';
1308 if (function_exists($function)) {
553baa8f
DC
1309 $mod_actions = $function();
1310 if (is_array($mod_actions)) {
1311 $actions = array_merge($actions, $mod_actions);
1312 }
f3221af9 1313 }
1314 }
3eb9babc 1315
61460dd6 1316 // The array_values() forces a stack-like array
1317 // so we can later loop over safely...
1318 $actions = array_values(array_unique($actions));
1319 $c = count($actions);
1320 for ($n=0;$n<$c;$n++) {
a919ae89 1321 $actions[$n] = $actions[$n];
f3221af9 1322 }
61460dd6 1323 return $actions;
f3221af9 1324}
1325
1326function stats_get_time_options($now,$lastweekend,$lastmonthend,$earliestday,$earliestweek,$earliestmonth) {
1327
1328 $now = stats_get_base_daily(time());
1329 // it's really important that it's TIMEEND in the table. ie, tuesday 00:00:00 is monday night.
1330 // so we need to take a day off here (essentially add a day to $now
1331 $now += 60*60*24;
1332
3f73372f 1333 $timeoptions = array();
1334
f3221af9 1335 if ($now - (60*60*24*7) >= $earliestday) {
1336 $timeoptions[STATS_TIME_LASTWEEK] = get_string('numweeks','moodle',1);
1337 }
1338 if ($now - (60*60*24*14) >= $earliestday) {
1339 $timeoptions[STATS_TIME_LAST2WEEKS] = get_string('numweeks','moodle',2);
1340 }
1341 if ($now - (60*60*24*21) >= $earliestday) {
61460dd6 1342 $timeoptions[STATS_TIME_LAST3WEEKS] = get_string('numweeks','moodle',3);
f3221af9 1343 }
1344 if ($now - (60*60*24*28) >= $earliestday) {
1345 $timeoptions[STATS_TIME_LAST4WEEKS] = get_string('numweeks','moodle',4);// show dailies up to (including) here.
1346 }
1347 if ($lastweekend - (60*60*24*56) >= $earliestweek) {
1348 $timeoptions[STATS_TIME_LAST2MONTHS] = get_string('nummonths','moodle',2);
1349 }
1350 if ($lastweekend - (60*60*24*84) >= $earliestweek) {
1351 $timeoptions[STATS_TIME_LAST3MONTHS] = get_string('nummonths','moodle',3);
1352 }
1353 if ($lastweekend - (60*60*24*112) >= $earliestweek) {
1354 $timeoptions[STATS_TIME_LAST4MONTHS] = get_string('nummonths','moodle',4);
1355 }
1356 if ($lastweekend - (60*60*24*140) >= $earliestweek) {
1357 $timeoptions[STATS_TIME_LAST5MONTHS] = get_string('nummonths','moodle',5);
1358 }
1359 if ($lastweekend - (60*60*24*168) >= $earliestweek) {
1360 $timeoptions[STATS_TIME_LAST6MONTHS] = get_string('nummonths','moodle',6); // show weeklies up to (including) here
1361 }
1362 if (strtotime('-7 months',$lastmonthend) >= $earliestmonth) {
1363 $timeoptions[STATS_TIME_LAST7MONTHS] = get_string('nummonths','moodle',7);
1364 }
1365 if (strtotime('-8 months',$lastmonthend) >= $earliestmonth) {
1366 $timeoptions[STATS_TIME_LAST8MONTHS] = get_string('nummonths','moodle',8);
1367 }
1368 if (strtotime('-9 months',$lastmonthend) >= $earliestmonth) {
1369 $timeoptions[STATS_TIME_LAST9MONTHS] = get_string('nummonths','moodle',9);
1370 }
1371 if (strtotime('-10 months',$lastmonthend) >= $earliestmonth) {
1372 $timeoptions[STATS_TIME_LAST10MONTHS] = get_string('nummonths','moodle',10);
1373 }
1374 if (strtotime('-11 months',$lastmonthend) >= $earliestmonth) {
1375 $timeoptions[STATS_TIME_LAST11MONTHS] = get_string('nummonths','moodle',11);
1376 }
1377 if (strtotime('-1 year',$lastmonthend) >= $earliestmonth) {
1378 $timeoptions[STATS_TIME_LASTYEAR] = get_string('lastyear');
1379 }
1380
17fdbcac 1381 $years = (int)date('y', $now) - (int)date('y', $earliestmonth);
1382 if ($years > 1) {
1383 for($i = 2; $i <= $years; $i++) {
1384 $timeoptions[$i*12+20] = get_string('numyears', 'moodle', $i);
1385 }
1386 }
1387
f3221af9 1388 return $timeoptions;
1389}
1390
1391function stats_get_report_options($courseid,$mode) {
d23a798a 1392 global $CFG, $DB;
61460dd6 1393
f3221af9 1394 $reportoptions = array();
1395
1396 switch ($mode) {
1397 case STATS_MODE_GENERAL:
1398 $reportoptions[STATS_REPORT_ACTIVITY] = get_string('statsreport'.STATS_REPORT_ACTIVITY);
6a7fb9f7 1399 if ($courseid != SITEID && $context = context_course::instance($courseid)) {
9b01f685 1400 $sql = 'SELECT r.id, r.name FROM {role} r JOIN {stats_daily} s ON s.roleid = r.id WHERE s.courseid = :courseid GROUP BY r.id, r.name';
f9c2702e 1401 if ($roles = $DB->get_records_sql($sql, array('courseid' => $courseid))) {
0f259f63 1402 foreach ($roles as $role) {
9955d65e
LB
1403 $reportoptions[STATS_REPORT_ACTIVITYBYROLE.$role->id] = get_string('statsreport'.STATS_REPORT_ACTIVITYBYROLE).
1404 ' ' . format_string($role->name, true, ['context' => $context]);
0f259f63 1405 }
1406 }
1407 }
f3221af9 1408 $reportoptions[STATS_REPORT_READS] = get_string('statsreport'.STATS_REPORT_READS);
1409 $reportoptions[STATS_REPORT_WRITES] = get_string('statsreport'.STATS_REPORT_WRITES);
1410 if ($courseid == SITEID) {
1411 $reportoptions[STATS_REPORT_LOGINS] = get_string('statsreport'.STATS_REPORT_LOGINS);
1412 }
61460dd6 1413
f3221af9 1414 break;
1415 case STATS_MODE_DETAILED:
1416 $reportoptions[STATS_REPORT_USER_ACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ACTIVITY);
1417 $reportoptions[STATS_REPORT_USER_ALLACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ALLACTIVITY);
6a7fb9f7 1418 if (has_capability('report/stats:view', context_system::instance())) {
f3221af9 1419 $site = get_site();
1420 $reportoptions[STATS_REPORT_USER_LOGINS] = get_string('statsreport'.STATS_REPORT_USER_LOGINS);
1421 }
3eb9babc 1422 break;
1423 case STATS_MODE_RANKED:
6a7fb9f7 1424 if (has_capability('report/stats:view', context_system::instance())) {
3eb9babc 1425 $reportoptions[STATS_REPORT_ACTIVE_COURSES] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES);
1426 $reportoptions[STATS_REPORT_ACTIVE_COURSES_WEIGHTED] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES_WEIGHTED);
1427 $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES);
1428 $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES_RW] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES_RW);
1429 }
aa600c47 1430 break;
f3221af9 1431 }
61460dd6 1432
f3221af9 1433 return $reportoptions;
1434}
1435
2e0bb2af
FM
1436/**
1437 * Fix missing entries in the statistics.
1438 *
1439 * This creates a dummy stat when nothing happened during a day/week/month.
1440 *
1441 * @param array $stats array of statistics.
1442 * @param int $timeafter unused.
1443 * @param string $timestr type of statistics to generate (dayly, weekly, monthly).
1444 * @param boolean $line2
1445 * @param boolean $line3
1446 * @return array of fixed statistics.
1447 */
f3221af9 1448function stats_fix_zeros($stats,$timeafter,$timestr,$line2=true,$line3=false) {
1449
3a317277 1450 if (empty($stats)) {
1451 return;
1452 }
1453
f3221af9 1454 $timestr = str_replace('user_','',$timestr); // just in case.
d94fc7ec 1455
2e0bb2af
FM
1456 // Gets the current user base time.
1457 $fun = 'stats_get_base_'.$timestr;
8b658d8a 1458 $now = $fun();
f3221af9 1459
2e0bb2af 1460 // Extract the ending time of the statistics.
0f259f63 1461 $actualtimes = array();
bed828de 1462 $actualtimeshour = null;
2e0bb2af
FM
1463 foreach ($stats as $statid => $s) {
1464 // Normalise the month date to the 1st if for any reason it's set to later. But we ignore
bed828de
FM
1465 // anything above or equal to 29 because sometimes we get the end of the month. Also, we will
1466 // set the hours of the result to all of them, that way we prevent DST differences.
1467 if ($timestr == 'monthly') {
1468 $day = date('d', $s->timeend);
1469 if (date('d', $s->timeend) > 1 && date('d', $s->timeend) < 29) {
1470 $day = 1;
1471 }
1472 if (is_null($actualtimeshour)) {
1473 $actualtimeshour = date('H', $s->timeend);
1474 }
1475 $s->timeend = mktime($actualtimeshour, 0, 0, date('m', $s->timeend), $day, date('Y', $s->timeend));
2e0bb2af 1476 }
61460dd6 1477 $stats[$statid] = $s;
0f259f63 1478 $actualtimes[] = $s->timeend;
1479 }
11887b6d 1480
2e0bb2af
FM
1481 $actualtimesvalues = array_values($actualtimes);
1482 $timeafter = array_pop($actualtimesvalues);
3eb9babc 1483
2e0bb2af
FM
1484 // Generate a base timestamp for each possible month/week/day.
1485 $times = array();
f3221af9 1486 while ($timeafter < $now) {
1487 $times[] = $timeafter;
1488 if ($timestr == 'daily') {
61460dd6 1489 $timeafter = stats_get_next_day_start($timeafter);
f3221af9 1490 } else if ($timestr == 'weekly') {
61460dd6 1491 $timeafter = stats_get_next_week_start($timeafter);
f3221af9 1492 } else if ($timestr == 'monthly') {
2e0bb2af
FM
1493 // We can't just simply +1 month because the 31st Jan + 1 month = 2nd of March.
1494 $year = date('Y', $timeafter);
1495 $month = date('m', $timeafter);
1496 $day = date('d', $timeafter);
1497 $dayofnextmonth = $day;
1498 if ($day >= 29) {
1499 $daysinmonth = date('n', mktime(0, 0, 0, $month+1, 1, $year));
1500 if ($day > $daysinmonth) {
1501 $dayofnextmonth = $daysinmonth;
1502 }
1503 }
bed828de 1504 $timeafter = mktime($actualtimeshour, 0, 0, $month+1, $dayofnextmonth, $year);
f3221af9 1505 } else {
2e0bb2af
FM
1506 // This will put us in a never ending loop.
1507 return $stats;
f3221af9 1508 }
1509 }
1510
2e0bb2af 1511 // Add the base timestamp to the statistics if not present.
0f259f63 1512 foreach ($times as $count => $time) {
1513 if (!in_array($time,$actualtimes) && $count != count($times) -1) {
f3221af9 1514 $newobj = new StdClass;
1515 $newobj->timeend = $time;
1516 $newobj->id = 0;
11887b6d 1517 $newobj->roleid = 0;
f3221af9 1518 $newobj->line1 = 0;
1519 if (!empty($line2)) {
1520 $newobj->line2 = 0;
1521 }
1522 if (!empty($line3)) {
1523 $newobj->line3 = 0;
1524 }
11887b6d 1525 $newobj->zerofixed = true;
0f259f63 1526 $stats[] = $newobj;
f3221af9 1527 }
1528 }
61460dd6 1529
0f259f63 1530 usort($stats,"stats_compare_times");
f3221af9 1531 return $stats;
f3221af9 1532}
1533
0f259f63 1534// helper function to sort arrays by $obj->timeend
1535function stats_compare_times($a,$b) {
1536 if ($a->timeend == $b->timeend) {
1537 return 0;
1538 }
1539 return ($a->timeend > $b->timeend) ? -1 : 1;
1540}
1541
140c139e 1542function stats_check_uptodate($courseid=0) {
d23a798a 1543 global $CFG, $DB;
140c139e 1544
1545 if (empty($courseid)) {
450a0a7d 1546 $courseid = SITEID;
140c139e 1547 }
1548
1549 $latestday = stats_get_start_from('daily');
1550
4900433c 1551 if ((time() - 60*60*24*2) < $latestday) { // we're ok
13d32e22 1552 return NULL;
140c139e 1553 }
1554
365a5941 1555 $a = new stdClass();
d23a798a 1556 $a->daysdone = $DB->get_field_sql("SELECT COUNT(DISTINCT(timeend)) FROM {stats_daily}");
140c139e 1557
1558 // how many days between the last day and now?
1559 $a->dayspending = ceil((stats_get_base_daily() - $latestday)/(60*60*24));
1560
1561 if ($a->dayspending == 0 && $a->daysdone != 0) {
13d32e22 1562 return NULL; // we've only just started...
140c139e 1563 }
13d32e22 1564
1565 //return error as string
1566 return get_string('statscatchupmode','error',$a);
140c139e 1567}
9c1600f1
TB
1568
1569/**
1570 * Create temporary tables to speed up log generation
1571 */
1572function stats_temp_table_create() {
1573 global $CFG, $DB;
1574
1575 $dbman = $DB->get_manager(); // We are going to use database_manager services
1576
1577 stats_temp_table_drop();
1578
283e448a
RS
1579 $tables = array();
1580
1581 /// Define tables user to be created
1582 $table = new xmldb_table('temp_stats_daily');
d745d2d4 1583 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
2412371e
RS
1584 $table->add_field('courseid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1585 $table->add_field('timeend', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1586 $table->add_field('roleid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
283e448a 1587 $table->add_field('stattype', XMLDB_TYPE_CHAR, 20, null, XMLDB_NOTNULL, null, 'activity');
2412371e
RS
1588 $table->add_field('stat1', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1589 $table->add_field('stat2', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
283e448a 1590 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
d745d2d4
EL
1591 $table->add_index('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid'));
1592 $table->add_index('timeend', XMLDB_INDEX_NOTUNIQUE, array('timeend'));
1593 $table->add_index('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid'));
283e448a
RS
1594 $tables['temp_stats_daily'] = $table;
1595
1596 $table = new xmldb_table('temp_stats_user_daily');
d745d2d4 1597 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
283e448a
RS
1598 $table->add_field('courseid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1599 $table->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1600 $table->add_field('roleid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1601 $table->add_field('timeend', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1602 $table->add_field('statsreads', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1603 $table->add_field('statswrites', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
2412371e 1604 $table->add_field('stattype', XMLDB_TYPE_CHAR, 30, null, XMLDB_NOTNULL, null, null);
283e448a 1605 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
d745d2d4
EL
1606 $table->add_index('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid'));
1607 $table->add_index('userid', XMLDB_INDEX_NOTUNIQUE, array('userid'));
1608 $table->add_index('timeend', XMLDB_INDEX_NOTUNIQUE, array('timeend'));
1609 $table->add_index('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid'));
283e448a
RS
1610 $tables['temp_stats_user_daily'] = $table;
1611
1612 $table = new xmldb_table('temp_enroled');
d745d2d4 1613 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
283e448a
RS
1614 $table->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1615 $table->add_field('courseid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
d745d2d4 1616 $table->add_field('roleid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null);
283e448a 1617 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
d745d2d4
EL
1618 $table->add_index('userid', XMLDB_INDEX_NOTUNIQUE, array('userid'));
1619 $table->add_index('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid'));
1620 $table->add_index('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid'));
283e448a
RS
1621 $tables['temp_enroled'] = $table;
1622
1623
1624 $table = new xmldb_table('temp_log1');
d745d2d4 1625 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
283e448a 1626 $table->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
2412371e 1627 $table->add_field('course', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
283e448a
RS
1628 $table->add_field('action', XMLDB_TYPE_CHAR, 40, null, XMLDB_NOTNULL, null, null);
1629 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
d745d2d4
EL
1630 $table->add_index('action', XMLDB_INDEX_NOTUNIQUE, array('action'));
1631 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
1632 $table->add_index('user', XMLDB_INDEX_NOTUNIQUE, array('userid'));
1633 $table->add_index('usercourseaction', XMLDB_INDEX_NOTUNIQUE, array('userid','course','action'));
283e448a
RS
1634 $tables['temp_log1'] = $table;
1635
1636 /// temp_log2 is exactly the same as temp_log1.
1637 $tables['temp_log2'] = clone $tables['temp_log1'];
1638 $tables['temp_log2']->setName('temp_log2');
4614b029
TB
1639
1640 try {
1641
1642 foreach ($tables as $table) {
1643 $dbman->create_temp_table($table);
1644 }
0934f5c3 1645
9c1600f1 1646 } catch (Exception $e) {
4614b029 1647 mtrace('Temporary table creation failed: '. $e->getMessage());
9c1600f1
TB
1648 return false;
1649 }
1650
1651 return true;
1652}
1653
1654/**
1655 * Deletes summary logs table for stats calculation
1656 */
1657function stats_temp_table_drop() {
1658 global $DB;
1659
1660 $dbman = $DB->get_manager();
1661
aa600c47 1662 $tables = array('temp_log1', 'temp_log2', 'temp_stats_daily', 'temp_stats_user_daily', 'temp_enroled');
9c1600f1
TB
1663
1664 foreach ($tables as $name) {
1665
1666 if ($dbman->table_exists($name)) {
1667 $table = new xmldb_table($name);
1668
1669 try {
4614b029 1670 $dbman->drop_table($table);
9c1600f1
TB
1671 } catch (Exception $e) {
1672 mtrace("Error occured while dropping temporary tables!");
1673 }
1674 }
1675 }
1676}
1677
1678/**
1679 * Fills the temporary stats tables with new data
1680 *
aa600c47
TB
1681 * This function is meant to be called once at the start of stats generation
1682 *
92927fba
1683 * @param int timestart timestamp of the start time of logs view
1684 * @param int timeend timestamp of the end time of logs view
1685 * @return bool success (true) or failure(false)
aa600c47
TB
1686 */
1687function stats_temp_table_setup() {
1688 global $DB;
1689
1690 $sql = "INSERT INTO {temp_enroled} (userid, courseid, roleid)
1691
1692 SELECT ue.userid, e.courseid, ra.roleid
1693 FROM {role_assignments} ra
1694 JOIN {context} c ON (c.id = ra.contextid AND c.contextlevel = :courselevel)
1695 JOIN {enrol} e ON e.courseid = c.instanceid
1696 JOIN {user_enrolments} ue ON (ue.enrolid = e.id AND ue.userid = ra.userid)";
1697
1698 return stats_run_query($sql, array('courselevel' => CONTEXT_COURSE));
1699}
1700
1701/**
1702 * Fills the temporary stats tables with new data
1703 *
1704 * This function is meant to be called to get a new day of data
1705 *
92927fba
1706 * @param int timestamp of the start time of logs view
1707 * @param int timestamp of the end time of logs view
1708 * @return bool success (true) or failure(false)
9c1600f1
TB
1709 */
1710function stats_temp_table_fill($timestart, $timeend) {
1711 global $DB;
1712
92927fba
1713 // First decide from where we want the data.
1714
8dcb7ae6
PS
1715 $params = array('timestart' => $timestart,
1716 'timeend' => $timeend,
1717 'participating' => \core\event\base::LEVEL_PARTICIPATING,
1718 'teaching' => \core\event\base::LEVEL_TEACHING,
1719 'loginevent1' => '\core\event\user_loggedin',
1720 'loginevent2' => '\core\event\user_loggedin',
1721 );
92927fba
1722
1723 $filled = false;
1724 $manager = get_log_manager();
1725 $stores = $manager->get_readers();
1726 foreach ($stores as $store) {
59aebbed 1727 if ($store instanceof \core\log\sql_internal_table_reader) {
92927fba
1728 $logtable = $store->get_internal_log_table_name();
1729 if (!$logtable) {
1730 continue;
1731 }
9c1600f1 1732
92927fba
1733 $sql = "SELECT COUNT('x')
1734 FROM {{$logtable}}
1735 WHERE timecreated >= :timestart AND timecreated < :timeend";
9c1600f1 1736
92927fba
1737 if (!$DB->get_field_sql($sql, $params)) {
1738 continue;
1739 }
1740
1741 // Let's fake the old records using new log data.
8dcb7ae6
PS
1742 // We want only data relevant to educational process
1743 // done by real users.
92927fba
1744
1745 $sql = "INSERT INTO {temp_log1} (userid, course, action)
1746
1747 SELECT userid,
1748 CASE
1749 WHEN courseid IS NULL THEN ".SITEID."
1750 WHEN courseid = 0 THEN ".SITEID."
1751 ELSE courseid
1752 END,
1753 CASE
8dcb7ae6 1754 WHEN eventname = :loginevent1 THEN 'login'
92927fba
1755 WHEN crud = 'r' THEN 'view'
1756 ELSE 'update'
1757 END
1758 FROM {{$logtable}}
8dcb7ae6
PS
1759 WHERE timecreated >= :timestart AND timecreated < :timeend
1760 AND (origin = 'web' OR origin = 'ws')
1761 AND (edulevel = :participating OR edulevel = :teaching OR eventname = :loginevent2)";
92927fba
1762
1763 $DB->execute($sql, $params);
1764 $filled = true;
1765 }
1766 }
1767
1768 if (!$filled) {
1769 // Fallback to legacy data.
1770 $sql = "INSERT INTO {temp_log1} (userid, course, action)
1771
1772 SELECT userid, course, action
1773 FROM {log}
1774 WHERE time >= :timestart AND time < :timeend";
1775
1776 $DB->execute($sql, $params);
1777 }
9c1600f1
TB
1778
1779 $sql = 'INSERT INTO {temp_log2} (userid, course, action)
1780
1781 SELECT userid, course, action FROM {temp_log1}';
1782
1783 $DB->execute($sql);
1784
cc67c6db
RS
1785 // We have just loaded all the temp tables, collect statistics for that.
1786 $DB->update_temp_table_stats();
1787
9c1600f1
TB
1788 return true;
1789}
1790
1791
1792/**
1793 * Deletes summary logs table for stats calculation
1794 *
92927fba 1795 * @return bool success (true) or failure(false)
9c1600f1
TB
1796 */
1797function stats_temp_table_clean() {
1798 global $DB;
1799
0934f5c3
TB
1800 $sql = array();
1801
1802 $sql['up1'] = 'INSERT INTO {stats_daily} (courseid, roleid, stattype, timeend, stat1, stat2)
1803
1804 SELECT courseid, roleid, stattype, timeend, stat1, stat2 FROM {temp_stats_daily}';
1805
1806 $sql['up2'] = 'INSERT INTO {stats_user_daily}
1807 (courseid, userid, roleid, timeend, statsreads, statswrites, stattype)
1808
1809 SELECT courseid, userid, roleid, timeend, statsreads, statswrites, stattype
1810 FROM {temp_stats_user_daily}';
1811
1812 foreach ($sql as $id => $query) {
aa600c47 1813 if (! stats_run_query($query)) {
0934f5c3
TB
1814 mtrace("Error during table cleanup!");
1815 return false;
1816 }
1817 }
1818
1819 $tables = array('temp_log1', 'temp_log2', 'temp_stats_daily', 'temp_stats_user_daily');
9c1600f1
TB
1820
1821 foreach ($tables as $name) {
0934f5c3 1822 $DB->delete_records($name);
9c1600f1
TB
1823 }
1824
1825 return true;
1826}