Automatically generated installer lang files
[moodle.git] / lib / statslib.php
blobf449f07f5607cfe14aa7c1f24ce75ce3b738d6e5
1 <?php
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.
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/>.
18 /**
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
25 defined('MOODLE_INTERNAL') || die();
27 /** THESE CONSTANTS ARE USED FOR THE REPORTING PAGE. */
29 define('STATS_REPORT_LOGINS',1); // double impose logins and unique logins on a line graph. site course only.
30 define('STATS_REPORT_READS',2); // double impose student reads and teacher reads on a line graph.
31 define('STATS_REPORT_WRITES',3); // double impose student writes and teacher writes on a line graph.
32 define('STATS_REPORT_ACTIVITY',4); // 2+3 added up, teacher vs student.
33 define('STATS_REPORT_ACTIVITYBYROLE',5); // all activity, reads vs writes, selected by role.
35 // user level stats reports.
36 define('STATS_REPORT_USER_ACTIVITY',7);
37 define('STATS_REPORT_USER_ALLACTIVITY',8);
38 define('STATS_REPORT_USER_LOGINS',9);
39 define('STATS_REPORT_USER_VIEW',10); // this is the report you see on the user profile.
41 // admin only ranking stats reports
42 define('STATS_REPORT_ACTIVE_COURSES',11);
43 define('STATS_REPORT_ACTIVE_COURSES_WEIGHTED',12);
44 define('STATS_REPORT_PARTICIPATORY_COURSES',13);
45 define('STATS_REPORT_PARTICIPATORY_COURSES_RW',14);
47 // start after 0 = show dailies.
48 define('STATS_TIME_LASTWEEK',1);
49 define('STATS_TIME_LAST2WEEKS',2);
50 define('STATS_TIME_LAST3WEEKS',3);
51 define('STATS_TIME_LAST4WEEKS',4);
53 // start after 10 = show weeklies
54 define('STATS_TIME_LAST2MONTHS',12);
56 define('STATS_TIME_LAST3MONTHS',13);
57 define('STATS_TIME_LAST4MONTHS',14);
58 define('STATS_TIME_LAST5MONTHS',15);
59 define('STATS_TIME_LAST6MONTHS',16);
61 // start after 20 = show monthlies
62 define('STATS_TIME_LAST7MONTHS',27);
63 define('STATS_TIME_LAST8MONTHS',28);
64 define('STATS_TIME_LAST9MONTHS',29);
65 define('STATS_TIME_LAST10MONTHS',30);
66 define('STATS_TIME_LAST11MONTHS',31);
67 define('STATS_TIME_LASTYEAR',32);
69 // different modes for what reports to offer
70 define('STATS_MODE_GENERAL',1);
71 define('STATS_MODE_DETAILED',2);
72 define('STATS_MODE_RANKED',3); // admins only - ranks courses
74 // Output string when nodebug is on
75 define('STATS_PLACEHOLDER_OUTPUT', '.');
77 /**
78 * Print daily cron progress
79 * @param string $ident
81 function stats_progress($ident) {
82 static $start = 0;
83 static $init = 0;
85 if ($ident == 'init') {
86 $init = $start = microtime(true);
87 return;
90 $elapsed = round(microtime(true) - $start);
91 $start = microtime(true);
93 if (debugging('', DEBUG_ALL)) {
94 mtrace("$ident:$elapsed ", '');
95 } else {
96 mtrace(STATS_PLACEHOLDER_OUTPUT, '');
101 * Execute individual daily statistics queries
103 * @param string $sql The query to run
104 * @return boolean success
106 function stats_run_query($sql, $parameters = array()) {
107 global $DB;
109 try {
110 $DB->execute($sql, $parameters);
111 } catch (dml_exception $e) {
113 if (debugging('', DEBUG_ALL)) {
114 mtrace($e->getMessage());
116 return false;
118 return true;
122 * Execute daily statistics gathering
124 * @param int $maxdays maximum number of days to be processed
125 * @return boolean success
127 function stats_cron_daily($maxdays=1) {
128 global $CFG, $DB;
129 require_once($CFG->libdir.'/adminlib.php');
131 $now = time();
133 $fpcontext = context_course::instance(SITEID, MUST_EXIST);
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);
141 $nextmidnight = stats_get_next_day_start($timestart);
143 // are there any days that need to be processed?
144 if ($now < $nextmidnight) {
145 return true; // everything ok and up-to-date
148 $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
150 if (!set_cron_lock('statsrunning', $now + $timeout)) {
151 return false;
154 // first delete entries that should not be there yet
155 $DB->delete_records_select('stats_daily', "timeend > $timestart");
156 $DB->delete_records_select('stats_user_daily', "timeend > $timestart");
158 // Read in a few things we'll use later
159 $viewactions = stats_get_action_names('view');
160 $postactions = stats_get_action_names('post');
162 $guest = (int)$CFG->siteguest;
163 $guestrole = (int)$CFG->guestroleid;
164 $defaultfproleid = (int)$CFG->defaultfrontpageroleid;
166 mtrace("Running daily statistics gathering, starting at $timestart:");
167 cron_trace_time_and_memory();
169 $days = 0;
170 $total = 0;
171 $failed = false; // failed stats flag
172 $timeout = false;
174 if (!stats_temp_table_create()) {
175 $days = 1;
176 $failed = true;
178 mtrace('Temporary tables created');
180 if(!stats_temp_table_setup()) {
181 $days = 1;
182 $failed = true;
184 mtrace('Enrolments calculated');
186 $totalactiveusers = $DB->count_records('user', array('deleted' => '0'));
188 while (!$failed && ($now > $nextmidnight)) {
189 if ($days >= $maxdays) {
190 $timeout = true;
191 break;
194 $days++;
195 core_php_time_limit::raise($timeout - 200);
197 if ($days > 1) {
198 // move the lock
199 set_cron_lock('statsrunning', time() + $timeout, true);
202 $daystart = time();
204 stats_progress('init');
206 if (!stats_temp_table_fill($timestart, $nextmidnight)) {
207 $failed = true;
208 break;
211 // Find out if any logs available for this day
212 $sql = "SELECT 'x' FROM {temp_log1} l";
213 $logspresent = $DB->get_records_sql($sql, null, 0, 1);
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' => ''));
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);
228 stats_progress('0');
230 // Process login info first
231 // Note: PostgreSQL doesn't like aliases in HAVING clauses
232 $sql = "INSERT INTO {temp_stats_user_daily}
233 (stattype, timeend, courseid, userid, statsreads)
235 SELECT 'logins', $nextmidnight AS timeend, ".SITEID." AS courseid,
236 userid, COUNT(id) AS statsreads
237 FROM {temp_log1} l
238 WHERE action = 'login'
239 GROUP BY userid
240 HAVING COUNT(id) > 0";
242 if ($logspresent && !stats_run_query($sql)) {
243 $failed = true;
244 break;
246 $DB->update_temp_table_stats();
248 stats_progress('1');
250 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
252 SELECT 'logins' AS stattype, $nextmidnight AS timeend, ".SITEID." AS courseid, 0,
253 COALESCE(SUM(statsreads), 0) as stat1, COUNT('x') as stat2
254 FROM {temp_stats_user_daily}
255 WHERE stattype = 'logins' AND timeend = $nextmidnight";
257 if ($logspresent && !stats_run_query($sql)) {
258 $failed = true;
259 break;
261 stats_progress('2');
264 // Enrolments and active enrolled users
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
270 // - SITEID is special case here, because it's all about default enrolment
271 // in that case, we'll count non-deleted users.
274 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
276 SELECT 'enrolments' as stattype, $nextmidnight as timeend, courseid, roleid,
277 COUNT(DISTINCT userid) as stat1, 0 as stat2
278 FROM {temp_enroled}
279 GROUP BY courseid, roleid";
281 if (!stats_run_query($sql)) {
282 $failed = true;
283 break;
285 stats_progress('3');
287 // Set stat2 to the number distinct users with role assignments in the course that were active
288 // using table alias in UPDATE does not work in pg < 8.2
289 $sql = "UPDATE {temp_stats_daily}
290 SET stat2 = (
292 SELECT COUNT(DISTINCT userid)
293 FROM {temp_enroled} te
294 WHERE roleid = {temp_stats_daily}.roleid
295 AND courseid = {temp_stats_daily}.courseid
296 AND EXISTS (
298 SELECT 'x'
299 FROM {temp_log1} l
300 WHERE l.course = {temp_stats_daily}.courseid
301 AND l.userid = te.userid
304 WHERE {temp_stats_daily}.stattype = 'enrolments'
305 AND {temp_stats_daily}.timeend = $nextmidnight
306 AND {temp_stats_daily}.courseid IN (
308 SELECT DISTINCT course FROM {temp_log2})";
310 if ($logspresent && !stats_run_query($sql, array('courselevel'=>CONTEXT_COURSE))) {
311 $failed = true;
312 break;
314 stats_progress('4');
316 // Now get course total enrolments (roleid==0) - except frontpage
317 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
319 SELECT 'enrolments', $nextmidnight AS timeend, te.courseid AS courseid, 0 AS roleid,
320 COUNT(DISTINCT userid) AS stat1, 0 AS stat2
321 FROM {temp_enroled} te
322 GROUP BY courseid
323 HAVING COUNT(DISTINCT userid) > 0";
325 if ($logspresent && !stats_run_query($sql)) {
326 $failed = true;
327 break;
329 stats_progress('5');
331 // Set stat 2 to the number of enrolled users who were active in the course
332 $sql = "UPDATE {temp_stats_daily}
333 SET stat2 = (
335 SELECT COUNT(DISTINCT te.userid)
336 FROM {temp_enroled} te
337 WHERE te.courseid = {temp_stats_daily}.courseid
338 AND EXISTS (
340 SELECT 'x'
341 FROM {temp_log1} l
342 WHERE l.course = {temp_stats_daily}.courseid
343 AND l.userid = te.userid
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 (
352 SELECT l.course
353 FROM {temp_log2} l
354 WHERE l.course <> ".SITEID.")";
356 if ($logspresent && !stats_run_query($sql, array())) {
357 $failed = true;
358 break;
360 stats_progress('6');
362 // Frontpage(==site) enrolments total
363 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
365 SELECT 'enrolments', $nextmidnight, ".SITEID.", 0, $totalactiveusers AS stat1,
366 $dailyactiveusers AS stat2" .
367 $DB->sql_null_from_clause();
369 if ($logspresent && !stats_run_query($sql)) {
370 $failed = true;
371 break;
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();
377 stats_progress('7');
379 // Default frontpage role enrolments are all site users (not deleted)
380 if ($defaultfproleid) {
381 // first remove default frontpage role counts if created by previous query
382 $sql = "DELETE
383 FROM {temp_stats_daily}
384 WHERE stattype = 'enrolments'
385 AND courseid = ".SITEID."
386 AND roleid = $defaultfproleid
387 AND timeend = $nextmidnight";
389 if ($logspresent && !stats_run_query($sql)) {
390 $failed = true;
391 break;
393 stats_progress('8');
395 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
397 SELECT 'enrolments', $nextmidnight, ".SITEID.", $defaultfproleid,
398 $totalactiveusers AS stat1, $dailyactiveusers AS stat2" .
399 $DB->sql_null_from_clause();
401 if ($logspresent && !stats_run_query($sql)) {
402 $failed = true;
403 break;
405 stats_progress('9');
407 } else {
408 stats_progress('x');
409 stats_progress('x');
413 /// individual user stats (including not-logged-in) in each course, this is slow - reuse this data if possible
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');
416 $sql = "INSERT INTO {temp_stats_user_daily} (stattype, timeend, courseid, userid, statsreads, statswrites)
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
422 GROUP BY userid, course";
424 if ($logspresent && !stats_run_query($sql, array_merge($params1, $params2))) {
425 $failed = true;
426 break;
428 stats_progress('10');
431 /// How many view/post actions in each course total
432 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
434 SELECT 'activity' AS stattype, $nextmidnight AS timeend, c.id AS courseid, 0,
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
439 GROUP BY c.id";
441 if ($logspresent && !stats_run_query($sql, array_merge($params1, $params2))) {
442 $failed = true;
443 break;
445 stats_progress('11');
448 /// how many view actions for each course+role - excluding guests and frontpage
450 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
452 SELECT 'activity', $nextmidnight AS timeend, courseid, roleid, SUM(statsreads), SUM(statswrites)
453 FROM (
455 SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
456 FROM {temp_stats_user_daily} sud, (
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
464 WHERE sud.userid = pl.userid
465 AND sud.courseid = pl.courseid
466 AND sud.timeend = $nextmidnight
467 AND sud.stattype='activity'
468 ) inline_view
470 GROUP BY courseid, roleid
471 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
473 if ($logspresent && !stats_run_query($sql, array('courselevel'=>CONTEXT_COURSE))) {
474 $failed = true;
475 break;
477 stats_progress('12');
479 /// how many view actions from guests only in each course - excluding frontpage
480 /// normal users may enter course with temporary guest access too
482 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
484 SELECT 'activity', $nextmidnight AS timeend, courseid, $guestrole AS roleid,
485 SUM(statsreads), SUM(statswrites)
486 FROM (
488 SELECT sud.courseid, sud.statsreads, sud.statswrites
489 FROM {temp_stats_user_daily} sud
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 (
495 SELECT userid
496 FROM {temp_enroled} te
497 WHERE te.courseid = sud.courseid
499 ) inline_view
501 GROUP BY courseid
502 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
504 if ($logspresent && !stats_run_query($sql, array())) {
505 $failed = true;
506 break;
508 stats_progress('13');
511 /// How many view actions for each role on frontpage - excluding guests, not-logged-in and default frontpage role
512 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
514 SELECT 'activity', $nextmidnight AS timeend, courseid, roleid,
515 SUM(statsreads), SUM(statswrites)
516 FROM (
517 SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
518 FROM {temp_stats_user_daily} sud, (
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'
532 ) inline_view
534 GROUP BY courseid, roleid
535 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
537 if ($logspresent && !stats_run_query($sql, array('fpcontext'=>$fpcontext->id))) {
538 $failed = true;
539 break;
541 stats_progress('14');
544 // How many view actions for default frontpage role on frontpage only
545 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
547 SELECT 'activity', timeend, courseid, $defaultfproleid AS roleid,
548 SUM(statsreads), SUM(statswrites)
549 FROM (
550 SELECT sud.timeend AS timeend, sud.courseid, sud.statsreads, sud.statswrites
551 FROM {temp_stats_user_daily} sud
552 WHERE sud.timeend = :nextm
553 AND sud.courseid = :siteid
554 AND sud.stattype='activity'
555 AND sud.userid <> $guest
556 AND sud.userid <> 0
557 AND sud.userid NOT IN (
559 SELECT ra.userid
560 FROM {role_assignments} ra
561 WHERE ra.roleid <> $guestrole
562 AND ra.roleid <> $defaultfproleid
563 AND ra.contextid = :fpcontext)
564 ) inline_view
566 GROUP BY timeend, courseid
567 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
569 if ($logspresent && !stats_run_query($sql, array('fpcontext'=>$fpcontext->id, 'siteid'=>SITEID, 'nextm'=>$nextmidnight))) {
570 $failed = true;
571 break;
573 $DB->update_temp_table_stats();
574 stats_progress('15');
576 // How many view actions for guests or not-logged-in on frontpage
577 $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
579 SELECT stattype, timeend, courseid, $guestrole AS roleid,
580 SUM(statsreads) AS stat1, SUM(statswrites) AS stat2
581 FROM (
582 SELECT sud.stattype, sud.timeend, sud.courseid,
583 sud.statsreads, sud.statswrites
584 FROM {temp_stats_user_daily} sud
585 WHERE (sud.userid = $guest OR sud.userid = 0)
586 AND sud.timeend = $nextmidnight
587 AND sud.courseid = ".SITEID."
588 AND sud.stattype='activity'
589 ) inline_view
590 GROUP BY stattype, timeend, courseid
591 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
593 if ($logspresent && !stats_run_query($sql)) {
594 $failed = true;
595 break;
597 stats_progress('16');
599 stats_temp_table_clean();
601 stats_progress('out');
603 // remember processed days
604 set_config('statslastdaily', $nextmidnight);
605 $elapsed = time()-$daystart;
606 mtrace(" finished until $nextmidnight: ".userdate($nextmidnight)." (in $elapsed s)");
607 $total += $elapsed;
609 $timestart = $nextmidnight;
610 $nextmidnight = stats_get_next_day_start($nextmidnight);
613 stats_temp_table_drop();
615 set_cron_lock('statsrunning', null);
617 if ($failed) {
618 $days--;
619 mtrace("...error occurred, completed $days days of statistics in {$total} s.");
620 return false;
622 } else if ($timeout) {
623 mtrace("...stopping early, reached maximum number of $maxdays days ({$total} s) - will continue next time.");
624 return false;
626 } else {
627 mtrace("...completed $days days of statistics in {$total} s.");
628 return true;
634 * Execute weekly statistics gathering
635 * @return boolean success
637 function stats_cron_weekly() {
638 global $CFG, $DB;
639 require_once($CFG->libdir.'/adminlib.php');
641 $now = time();
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);
649 $nextstartweek = stats_get_next_week_start($timestart);
651 // are there any weeks that need to be processed?
652 if ($now < $nextstartweek) {
653 return true; // everything ok and up-to-date
656 $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
658 if (!set_cron_lock('statsrunning', $now + $timeout)) {
659 return false;
662 // fisrt delete entries that should not be there yet
663 $DB->delete_records_select('stats_weekly', "timeend > $timestart");
664 $DB->delete_records_select('stats_user_weekly', "timeend > $timestart");
666 mtrace("Running weekly statistics gathering, starting at $timestart:");
667 cron_trace_time_and_memory();
669 $weeks = 0;
670 while ($now > $nextstartweek) {
671 core_php_time_limit::raise($timeout - 200);
672 $weeks++;
674 if ($weeks > 1) {
675 // move the lock
676 set_cron_lock('statsrunning', time() + $timeout, true);
679 $stattimesql = "timeend > $timestart AND timeend <= $nextstartweek";
681 $weekstart = time();
682 stats_progress('init');
684 /// process login info first
685 $sql = "INSERT INTO {stats_user_weekly} (stattype, timeend, courseid, userid, statsreads)
687 SELECT 'logins', timeend, courseid, userid, SUM(statsreads)
688 FROM (
689 SELECT $nextstartweek AS timeend, courseid, userid, statsreads
690 FROM {stats_user_daily} sd
691 WHERE stattype = 'logins' AND $stattimesql
692 ) inline_view
693 GROUP BY timeend, courseid, userid
694 HAVING SUM(statsreads) > 0";
696 $DB->execute($sql);
698 stats_progress('1');
700 $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
702 SELECT 'logins' AS stattype, $nextstartweek AS timeend, ".SITEID." as courseid, 0,
703 COALESCE((SELECT SUM(statsreads)
704 FROM {stats_user_weekly} s1
705 WHERE s1.stattype = 'logins' AND timeend = $nextstartweek), 0) AS nstat1,
706 (SELECT COUNT('x')
707 FROM {stats_user_weekly} s2
708 WHERE s2.stattype = 'logins' AND timeend = $nextstartweek) AS nstat2" .
709 $DB->sql_null_from_clause();
711 $DB->execute($sql);
713 stats_progress('2');
715 /// now enrolments averages
716 $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
718 SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . "
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";
726 $DB->execute($sql);
728 stats_progress('3');
730 /// activity read/write averages
731 $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
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";
741 $DB->execute($sql);
743 stats_progress('4');
745 /// user read/write averages
746 $sql = "INSERT INTO {stats_user_weekly} (stattype, timeend, courseid, userid, statsreads, statswrites)
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";
756 $DB->execute($sql);
758 stats_progress('5');
760 set_config('statslastweekly', $nextstartweek);
761 $elapsed = time()-$weekstart;
762 mtrace(" finished until $nextstartweek: ".userdate($nextstartweek) ." (in $elapsed s)");
764 $timestart = $nextstartweek;
765 $nextstartweek = stats_get_next_week_start($nextstartweek);
768 set_cron_lock('statsrunning', null);
769 mtrace("...completed $weeks weeks of statistics.");
770 return true;
774 * Execute monthly statistics gathering
775 * @return boolean success
777 function stats_cron_monthly() {
778 global $CFG, $DB;
779 require_once($CFG->libdir.'/adminlib.php');
781 $now = time();
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);
789 $nextstartmonth = stats_get_next_month_start($timestart);
791 // are there any months that need to be processed?
792 if ($now < $nextstartmonth) {
793 return true; // everything ok and up-to-date
796 $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
798 if (!set_cron_lock('statsrunning', $now + $timeout)) {
799 return false;
802 // fisr delete entries that should not be there yet
803 $DB->delete_records_select('stats_monthly', "timeend > $timestart");
804 $DB->delete_records_select('stats_user_monthly', "timeend > $timestart");
806 $startmonth = stats_get_base_monthly($now);
809 mtrace("Running monthly statistics gathering, starting at $timestart:");
810 cron_trace_time_and_memory();
812 $months = 0;
813 while ($now > $nextstartmonth) {
814 core_php_time_limit::raise($timeout - 200);
815 $months++;
817 if ($months > 1) {
818 // move the lock
819 set_cron_lock('statsrunning', time() + $timeout, true);
822 $stattimesql = "timeend > $timestart AND timeend <= $nextstartmonth";
824 $monthstart = time();
825 stats_progress('init');
827 /// process login info first
828 $sql = "INSERT INTO {stats_user_monthly} (stattype, timeend, courseid, userid, statsreads)
830 SELECT 'logins', timeend, courseid, userid, SUM(statsreads)
831 FROM (
832 SELECT $nextstartmonth AS timeend, courseid, userid, statsreads
833 FROM {stats_user_daily} sd
834 WHERE stattype = 'logins' AND $stattimesql
835 ) inline_view
836 GROUP BY timeend, courseid, userid
837 HAVING SUM(statsreads) > 0";
839 $DB->execute($sql);
841 stats_progress('1');
843 $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
845 SELECT 'logins' AS stattype, $nextstartmonth AS timeend, ".SITEID." as courseid, 0,
846 COALESCE((SELECT SUM(statsreads)
847 FROM {stats_user_monthly} s1
848 WHERE s1.stattype = 'logins' AND timeend = $nextstartmonth), 0) AS nstat1,
849 (SELECT COUNT('x')
850 FROM {stats_user_monthly} s2
851 WHERE s2.stattype = 'logins' AND timeend = $nextstartmonth) AS nstat2" .
852 $DB->sql_null_from_clause();
854 $DB->execute($sql);
856 stats_progress('2');
858 /// now enrolments averages
859 $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
861 SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . "
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";
869 $DB->execute($sql);
871 stats_progress('3');
873 /// activity read/write averages
874 $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
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";
884 $DB->execute($sql);
886 stats_progress('4');
888 /// user read/write averages
889 $sql = "INSERT INTO {stats_user_monthly} (stattype, timeend, courseid, userid, statsreads, statswrites)
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";
899 $DB->execute($sql);
901 stats_progress('5');
903 set_config('statslastmonthly', $nextstartmonth);
904 $elapsed = time() - $monthstart;
905 mtrace(" finished until $nextstartmonth: ".userdate($nextstartmonth) ." (in $elapsed s)");
907 $timestart = $nextstartmonth;
908 $nextstartmonth = stats_get_next_month_start($nextstartmonth);
911 set_cron_lock('statsrunning', null);
912 mtrace("...completed $months months of statistics.");
913 return true;
917 * Return starting date of stats processing
918 * @param string $str name of table - daily, weekly or monthly
919 * @return int timestamp
921 function stats_get_start_from($str) {
922 global $CFG, $DB;
924 // are there any data in stats table? Should not be...
925 if ($timeend = $DB->get_field_sql('SELECT MAX(timeend) FROM {stats_'.$str.'}')) {
926 return $timeend;
928 // decide what to do based on our config setting (either all or none or a timestamp)
929 switch ($CFG->statsfirstrun) {
930 case 'all':
931 $manager = get_log_manager();
932 $stores = $manager->get_readers();
933 $firstlog = false;
934 foreach ($stores as $store) {
935 if ($store instanceof \core\log\sql_internal_table_reader) {
936 $logtable = $store->get_internal_log_table_name();
937 if (!$logtable) {
938 continue;
940 $first = $DB->get_field_sql("SELECT MIN(timecreated) FROM {{$logtable}}");
941 if ($first and (!$firstlog or $firstlog > $first)) {
942 $firstlog = $first;
947 $first = $DB->get_field_sql('SELECT MIN(time) FROM {log}');
948 if ($first and (!$firstlog or $firstlog > $first)) {
949 $firstlog = $first;
952 if ($firstlog) {
953 return $firstlog;
956 default:
957 if (is_numeric($CFG->statsfirstrun)) {
958 return time() - $CFG->statsfirstrun;
960 // not a number? use next instead
961 case 'none':
962 return strtotime('-3 day', time());
967 * Start of day
968 * @param int $time timestamp
969 * @return int start of day
971 function stats_get_base_daily($time=0) {
972 if (empty($time)) {
973 $time = time();
976 core_date::set_default_server_timezone();
977 $time = strtotime(date('d-M-Y', $time));
979 return $time;
983 * Start of week
984 * @param int $time timestamp
985 * @return int start of week
987 function stats_get_base_weekly($time=0) {
988 global $CFG;
990 $datetime = new DateTime();
991 $datetime->setTimestamp(stats_get_base_daily($time));
992 $startday = $CFG->calendar_startwday;
994 core_date::set_default_server_timezone();
995 $thisday = date('w', $time);
997 $days = 0;
999 if ($thisday > $startday) {
1000 $days = $thisday - $startday;
1001 } else if ($thisday < $startday) {
1002 $days = 7 + $thisday - $startday;
1005 $datetime->sub(new DateInterval("P{$days}D"));
1007 return $datetime->getTimestamp();
1011 * Start of month
1012 * @param int $time timestamp
1013 * @return int start of month
1015 function stats_get_base_monthly($time=0) {
1016 if (empty($time)) {
1017 $time = time();
1020 core_date::set_default_server_timezone();
1021 $return = strtotime(date('1-M-Y', $time));
1023 return $return;
1027 * Start of next day
1028 * @param int $time timestamp
1029 * @return start of next day
1031 function stats_get_next_day_start($time) {
1032 $next = stats_get_base_daily($time);
1033 $nextdate = new DateTime();
1034 $nextdate->setTimestamp($next);
1035 $nextdate->add(new DateInterval('P1D'));
1036 return $nextdate->getTimestamp();
1040 * Start of next week
1041 * @param int $time timestamp
1042 * @return start of next week
1044 function stats_get_next_week_start($time) {
1045 $next = stats_get_base_weekly($time);
1046 $nextdate = new DateTime();
1047 $nextdate->setTimestamp($next);
1048 $nextdate->add(new DateInterval('P1W'));
1049 return $nextdate->getTimestamp();
1053 * Start of next month
1054 * @param int $time timestamp
1055 * @return start of next month
1057 function stats_get_next_month_start($time) {
1058 $next = stats_get_base_monthly($time);
1059 $nextdate = new DateTime();
1060 $nextdate->setTimestamp($next);
1061 $nextdate->add(new DateInterval('P1M'));
1062 return $nextdate->getTimestamp();
1066 * Remove old stats data
1068 function stats_clean_old() {
1069 global $DB;
1070 mtrace("Running stats cleanup tasks...");
1071 cron_trace_time_and_memory();
1072 $deletebefore = stats_get_base_monthly();
1074 // delete dailies older than 3 months (to be safe)
1075 $deletebefore = strtotime('-3 months', $deletebefore);
1076 $DB->delete_records_select('stats_daily', "timeend < $deletebefore");
1077 $DB->delete_records_select('stats_user_daily', "timeend < $deletebefore");
1079 // delete weeklies older than 9 months (to be safe)
1080 $deletebefore = strtotime('-6 months', $deletebefore);
1081 $DB->delete_records_select('stats_weekly', "timeend < $deletebefore");
1082 $DB->delete_records_select('stats_user_weekly', "timeend < $deletebefore");
1084 // don't delete monthlies
1086 mtrace("...stats cleanup finished");
1089 function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) {
1090 global $CFG, $DB;
1092 $param = new stdClass();
1093 $param->params = array();
1095 if ($time < 10) { // dailies
1096 // number of days to go back = 7* time
1097 $param->table = 'daily';
1098 $param->timeafter = strtotime("-".($time*7)." days",stats_get_base_daily());
1099 } elseif ($time < 20) { // weeklies
1100 // number of weeks to go back = time - 10 * 4 (weeks) + base week
1101 $param->table = 'weekly';
1102 $param->timeafter = strtotime("-".(($time - 10)*4)." weeks",stats_get_base_weekly());
1103 } else { // monthlies.
1104 // number of months to go back = time - 20 * months + base month
1105 $param->table = 'monthly';
1106 $param->timeafter = strtotime("-".($time - 20)." months",stats_get_base_monthly());
1109 $param->extras = '';
1111 switch ($report) {
1112 // ******************** STATS_MODE_GENERAL ******************** //
1113 case STATS_REPORT_LOGINS:
1114 $param->fields = 'timeend,sum(stat1) as line1,sum(stat2) as line2';
1115 $param->fieldscomplete = true;
1116 $param->stattype = 'logins';
1117 $param->line1 = get_string('statslogins');
1118 $param->line2 = get_string('statsuniquelogins');
1119 if ($courseid == SITEID) {
1120 $param->extras = 'GROUP BY timeend';
1122 break;
1124 case STATS_REPORT_READS:
1125 $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat1 as line1';
1126 $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
1127 $param->aggregategroupby = 'roleid';
1128 $param->stattype = 'activity';
1129 $param->crosstab = true;
1130 $param->extras = 'GROUP BY timeend,roleid,stat1';
1131 if ($courseid == SITEID) {
1132 $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1) as line1';
1133 $param->extras = 'GROUP BY timeend,roleid';
1135 break;
1137 case STATS_REPORT_WRITES:
1138 $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat2 as line1';
1139 $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
1140 $param->aggregategroupby = 'roleid';
1141 $param->stattype = 'activity';
1142 $param->crosstab = true;
1143 $param->extras = 'GROUP BY timeend,roleid,stat2';
1144 if ($courseid == SITEID) {
1145 $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat2) as line1';
1146 $param->extras = 'GROUP BY timeend,roleid';
1148 break;
1150 case STATS_REPORT_ACTIVITY:
1151 $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1+stat2) as line1';
1152 $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
1153 $param->aggregategroupby = 'roleid';
1154 $param->stattype = 'activity';
1155 $param->crosstab = true;
1156 $param->extras = 'GROUP BY timeend,roleid';
1157 if ($courseid == SITEID) {
1158 $param->extras = 'GROUP BY timeend,roleid';
1160 break;
1162 case STATS_REPORT_ACTIVITYBYROLE;
1163 $param->fields = 'stat1 AS line1, stat2 AS line2';
1164 $param->stattype = 'activity';
1165 $rolename = '';
1166 if ($roleid <> 0) {
1167 if ($role = $DB->get_record('role', ['id' => $roleid])) {
1168 $rolename = role_get_name($role, context_course::instance($courseid)) . ' ';
1171 $param->line1 = $rolename . get_string('statsreads');
1172 $param->line2 = $rolename . get_string('statswrites');
1173 if ($courseid == SITEID) {
1174 $param->extras = 'GROUP BY timeend';
1176 break;
1178 // ******************** STATS_MODE_DETAILED ******************** //
1179 case STATS_REPORT_USER_ACTIVITY:
1180 $param->fields = 'statsreads as line1, statswrites as line2';
1181 $param->line1 = get_string('statsuserreads');
1182 $param->line2 = get_string('statsuserwrites');
1183 $param->stattype = 'activity';
1184 break;
1186 case STATS_REPORT_USER_ALLACTIVITY:
1187 $param->fields = 'statsreads+statswrites as line1';
1188 $param->line1 = get_string('statsuseractivity');
1189 $param->stattype = 'activity';
1190 break;
1192 case STATS_REPORT_USER_LOGINS:
1193 $param->fields = 'statsreads as line1';
1194 $param->line1 = get_string('statsuserlogins');
1195 $param->stattype = 'logins';
1196 break;
1198 case STATS_REPORT_USER_VIEW:
1199 $param->fields = 'timeend, SUM(statsreads) AS line1, SUM(statswrites) AS line2, SUM(statsreads+statswrites) AS line3';
1200 $param->fieldscomplete = true;
1201 $param->line1 = get_string('statsuserreads');
1202 $param->line2 = get_string('statsuserwrites');
1203 $param->line3 = get_string('statsuseractivity');
1204 $param->stattype = 'activity';
1205 $param->extras = "GROUP BY timeend";
1206 break;
1208 // ******************** STATS_MODE_RANKED ******************** //
1209 case STATS_REPORT_ACTIVE_COURSES:
1210 $param->fields = 'sum(stat1+stat2) AS line1';
1211 $param->stattype = 'activity';
1212 $param->orderby = 'line1 DESC';
1213 $param->line1 = get_string('useractivity');
1214 $param->graphline = 'line1';
1215 break;
1217 case STATS_REPORT_ACTIVE_COURSES_WEIGHTED:
1218 $threshold = 0;
1219 if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
1220 $threshold = $CFG->statsuserthreshold;
1222 $param->fields = '';
1223 $param->sql = 'SELECT activity.courseid, activity.all_activity AS line1, enrolments.highest_enrolments AS line2,
1224 activity.all_activity / enrolments.highest_enrolments as line3
1225 FROM (
1226 SELECT courseid, sum(stat1+stat2) AS all_activity
1227 FROM {stats_'.$param->table.'}
1228 WHERE stattype=\'activity\' AND timeend >= '.(int)$param->timeafter.' AND roleid = 0 GROUP BY courseid
1229 ) activity
1230 INNER JOIN
1232 SELECT courseid, max(stat1) AS highest_enrolments
1233 FROM {stats_'.$param->table.'}
1234 WHERE stattype=\'enrolments\' AND timeend >= '.(int)$param->timeafter.' AND stat1 > '.(int)$threshold.'
1235 GROUP BY courseid
1236 ) enrolments
1237 ON (activity.courseid = enrolments.courseid)
1238 ORDER BY line3 DESC';
1239 $param->line1 = get_string('useractivity');
1240 $param->line2 = get_string('users');
1241 $param->line3 = get_string('activityweighted');
1242 $param->graphline = 'line3';
1243 break;
1245 case STATS_REPORT_PARTICIPATORY_COURSES:
1246 $threshold = 0;
1247 if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
1248 $threshold = $CFG->statsuserthreshold;
1250 $param->fields = '';
1251 $param->sql = 'SELECT courseid, ' . $DB->sql_ceil('avg(all_enrolments)') . ' as line1, ' .
1252 $DB->sql_ceil('avg(active_enrolments)') . ' as line2, avg(proportion_active) AS line3
1253 FROM (
1254 SELECT courseid, timeend, stat2 as active_enrolments,
1255 stat1 as all_enrolments, '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' AS proportion_active
1256 FROM {stats_'.$param->table.'}
1257 WHERE stattype=\'enrolments\' AND roleid = 0 AND stat1 > '.(int)$threshold.'
1258 ) aq
1259 WHERE timeend >= '.(int)$param->timeafter.'
1260 GROUP BY courseid
1261 ORDER BY line3 DESC';
1263 $param->line1 = get_string('users');
1264 $param->line2 = get_string('activeusers');
1265 $param->line3 = get_string('participationratio');
1266 $param->graphline = 'line3';
1267 break;
1269 case STATS_REPORT_PARTICIPATORY_COURSES_RW:
1270 $param->fields = '';
1271 $param->sql = 'SELECT courseid, sum(views) AS line1, sum(posts) AS line2,
1272 avg(proportion_active) AS line3
1273 FROM (
1274 SELECT courseid, timeend, stat1 as views, stat2 AS posts,
1275 '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' as proportion_active
1276 FROM {stats_'.$param->table.'}
1277 WHERE stattype=\'activity\' AND roleid = 0 AND stat1 > 0
1278 ) aq
1279 WHERE timeend >= '.(int)$param->timeafter.'
1280 GROUP BY courseid
1281 ORDER BY line3 DESC';
1282 $param->line1 = get_string('views');
1283 $param->line2 = get_string('posts');
1284 $param->line3 = get_string('participationratio');
1285 $param->graphline = 'line3';
1286 break;
1290 if ($courseid == SITEID && $mode != STATS_MODE_RANKED) { // just aggregate all courses.
1291 $param->fields = preg_replace('/(?:sum)([a-zA-Z0-9+_]*)\W+as\W+([a-zA-Z0-9_]*)/i','sum($1) as $2',$param->fields);
1292 $param->extras = ' GROUP BY timeend'.((!empty($param->aggregategroupby)) ? ','.$param->aggregategroupby : '');
1295 //TODO must add the SITEID reports to the rest of the reports.
1296 return $param;
1299 function stats_get_view_actions() {
1300 return array('view','view all','history');
1303 function stats_get_post_actions() {
1304 return array('add','delete','edit','add mod','delete mod','edit section'.'enrol','loginas','new','unenrol','update','update mod');
1307 function stats_get_action_names($str) {
1308 global $CFG, $DB;
1310 $mods = $DB->get_records('modules');
1311 $function = 'stats_get_'.$str.'_actions';
1312 $actions = $function();
1313 foreach ($mods as $mod) {
1314 $file = $CFG->dirroot.'/mod/'.$mod->name.'/lib.php';
1315 if (!is_readable($file)) {
1316 continue;
1318 require_once($file);
1319 $function = $mod->name.'_get_'.$str.'_actions';
1320 if (function_exists($function)) {
1321 $mod_actions = $function();
1322 if (is_array($mod_actions)) {
1323 $actions = array_merge($actions, $mod_actions);
1328 // The array_values() forces a stack-like array
1329 // so we can later loop over safely...
1330 $actions = array_values(array_unique($actions));
1331 $c = count($actions);
1332 for ($n=0;$n<$c;$n++) {
1333 $actions[$n] = $actions[$n];
1335 return $actions;
1338 function stats_get_time_options($now,$lastweekend,$lastmonthend,$earliestday,$earliestweek,$earliestmonth) {
1340 $now = stats_get_base_daily(time());
1341 // it's really important that it's TIMEEND in the table. ie, tuesday 00:00:00 is monday night.
1342 // so we need to take a day off here (essentially add a day to $now
1343 $now += 60*60*24;
1345 $timeoptions = array();
1347 if ($now - (60*60*24*7) >= $earliestday) {
1348 $timeoptions[STATS_TIME_LASTWEEK] = get_string('numweeks','moodle',1);
1350 if ($now - (60*60*24*14) >= $earliestday) {
1351 $timeoptions[STATS_TIME_LAST2WEEKS] = get_string('numweeks','moodle',2);
1353 if ($now - (60*60*24*21) >= $earliestday) {
1354 $timeoptions[STATS_TIME_LAST3WEEKS] = get_string('numweeks','moodle',3);
1356 if ($now - (60*60*24*28) >= $earliestday) {
1357 $timeoptions[STATS_TIME_LAST4WEEKS] = get_string('numweeks','moodle',4);// show dailies up to (including) here.
1359 if ($lastweekend - (60*60*24*56) >= $earliestweek) {
1360 $timeoptions[STATS_TIME_LAST2MONTHS] = get_string('nummonths','moodle',2);
1362 if ($lastweekend - (60*60*24*84) >= $earliestweek) {
1363 $timeoptions[STATS_TIME_LAST3MONTHS] = get_string('nummonths','moodle',3);
1365 if ($lastweekend - (60*60*24*112) >= $earliestweek) {
1366 $timeoptions[STATS_TIME_LAST4MONTHS] = get_string('nummonths','moodle',4);
1368 if ($lastweekend - (60*60*24*140) >= $earliestweek) {
1369 $timeoptions[STATS_TIME_LAST5MONTHS] = get_string('nummonths','moodle',5);
1371 if ($lastweekend - (60*60*24*168) >= $earliestweek) {
1372 $timeoptions[STATS_TIME_LAST6MONTHS] = get_string('nummonths','moodle',6); // show weeklies up to (including) here
1374 if (strtotime('-7 months',$lastmonthend) >= $earliestmonth) {
1375 $timeoptions[STATS_TIME_LAST7MONTHS] = get_string('nummonths','moodle',7);
1377 if (strtotime('-8 months',$lastmonthend) >= $earliestmonth) {
1378 $timeoptions[STATS_TIME_LAST8MONTHS] = get_string('nummonths','moodle',8);
1380 if (strtotime('-9 months',$lastmonthend) >= $earliestmonth) {
1381 $timeoptions[STATS_TIME_LAST9MONTHS] = get_string('nummonths','moodle',9);
1383 if (strtotime('-10 months',$lastmonthend) >= $earliestmonth) {
1384 $timeoptions[STATS_TIME_LAST10MONTHS] = get_string('nummonths','moodle',10);
1386 if (strtotime('-11 months',$lastmonthend) >= $earliestmonth) {
1387 $timeoptions[STATS_TIME_LAST11MONTHS] = get_string('nummonths','moodle',11);
1389 if (strtotime('-1 year',$lastmonthend) >= $earliestmonth) {
1390 $timeoptions[STATS_TIME_LASTYEAR] = get_string('lastyear');
1393 $years = (int)date('y', $now) - (int)date('y', $earliestmonth);
1394 if ($years > 1) {
1395 for($i = 2; $i <= $years; $i++) {
1396 $timeoptions[$i*12+20] = get_string('numyears', 'moodle', $i);
1400 return $timeoptions;
1403 function stats_get_report_options($courseid,$mode) {
1404 global $CFG, $DB;
1406 $reportoptions = array();
1408 switch ($mode) {
1409 case STATS_MODE_GENERAL:
1410 $reportoptions[STATS_REPORT_ACTIVITY] = get_string('statsreport'.STATS_REPORT_ACTIVITY);
1411 if ($courseid != SITEID && $context = context_course::instance($courseid)) {
1412 $sql = 'SELECT r.id, r.name, r.shortname FROM {role} r JOIN {stats_daily} s ON s.roleid = r.id
1413 WHERE s.courseid = :courseid GROUP BY r.id, r.name, r.shortname';
1414 if ($roles = $DB->get_records_sql($sql, array('courseid' => $courseid))) {
1415 $roles = array_intersect_key($roles, get_viewable_roles($context));
1416 foreach ($roles as $role) {
1417 $reportoptions[STATS_REPORT_ACTIVITYBYROLE.$role->id] = get_string('statsreport'.STATS_REPORT_ACTIVITYBYROLE).
1418 ' ' . role_get_name($role, $context);
1422 $reportoptions[STATS_REPORT_READS] = get_string('statsreport'.STATS_REPORT_READS);
1423 $reportoptions[STATS_REPORT_WRITES] = get_string('statsreport'.STATS_REPORT_WRITES);
1424 if ($courseid == SITEID) {
1425 $reportoptions[STATS_REPORT_LOGINS] = get_string('statsreport'.STATS_REPORT_LOGINS);
1428 break;
1429 case STATS_MODE_DETAILED:
1430 $reportoptions[STATS_REPORT_USER_ACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ACTIVITY);
1431 $reportoptions[STATS_REPORT_USER_ALLACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ALLACTIVITY);
1432 if (has_capability('report/stats:view', context_system::instance())) {
1433 $site = get_site();
1434 $reportoptions[STATS_REPORT_USER_LOGINS] = get_string('statsreport'.STATS_REPORT_USER_LOGINS);
1436 break;
1437 case STATS_MODE_RANKED:
1438 if (has_capability('report/stats:view', context_system::instance())) {
1439 $reportoptions[STATS_REPORT_ACTIVE_COURSES] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES);
1440 $reportoptions[STATS_REPORT_ACTIVE_COURSES_WEIGHTED] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES_WEIGHTED);
1441 $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES);
1442 $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES_RW] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES_RW);
1444 break;
1447 return $reportoptions;
1451 * Fix missing entries in the statistics.
1453 * This creates a dummy stat when nothing happened during a day/week/month.
1455 * @param array $stats array of statistics.
1456 * @param int $timeafter unused.
1457 * @param string $timestr type of statistics to generate (dayly, weekly, monthly).
1458 * @param boolean $line2
1459 * @param boolean $line3
1460 * @return array of fixed statistics.
1462 function stats_fix_zeros($stats,$timeafter,$timestr,$line2=true,$line3=false) {
1464 if (empty($stats)) {
1465 return;
1468 $timestr = str_replace('user_','',$timestr); // just in case.
1470 // Gets the current user base time.
1471 $fun = 'stats_get_base_'.$timestr;
1472 $now = $fun();
1474 // Extract the ending time of the statistics.
1475 $actualtimes = array();
1476 $actualtimeshour = null;
1477 foreach ($stats as $statid => $s) {
1478 // Normalise the month date to the 1st if for any reason it's set to later. But we ignore
1479 // anything above or equal to 29 because sometimes we get the end of the month. Also, we will
1480 // set the hours of the result to all of them, that way we prevent DST differences.
1481 if ($timestr == 'monthly') {
1482 $day = date('d', $s->timeend);
1483 if (date('d', $s->timeend) > 1 && date('d', $s->timeend) < 29) {
1484 $day = 1;
1486 if (is_null($actualtimeshour)) {
1487 $actualtimeshour = date('H', $s->timeend);
1489 $s->timeend = mktime($actualtimeshour, 0, 0, date('m', $s->timeend), $day, date('Y', $s->timeend));
1491 $stats[$statid] = $s;
1492 $actualtimes[] = $s->timeend;
1495 $actualtimesvalues = array_values($actualtimes);
1496 $timeafter = array_pop($actualtimesvalues);
1498 // Generate a base timestamp for each possible month/week/day.
1499 $times = array();
1500 while ($timeafter < $now) {
1501 $times[] = $timeafter;
1502 if ($timestr == 'daily') {
1503 $timeafter = stats_get_next_day_start($timeafter);
1504 } else if ($timestr == 'weekly') {
1505 $timeafter = stats_get_next_week_start($timeafter);
1506 } else if ($timestr == 'monthly') {
1507 // We can't just simply +1 month because the 31st Jan + 1 month = 2nd of March.
1508 $year = date('Y', $timeafter);
1509 $month = date('m', $timeafter);
1510 $day = date('d', $timeafter);
1511 $dayofnextmonth = $day;
1512 if ($day >= 29) {
1513 $daysinmonth = date('n', mktime(0, 0, 0, $month+1, 1, $year));
1514 if ($day > $daysinmonth) {
1515 $dayofnextmonth = $daysinmonth;
1518 $timeafter = mktime($actualtimeshour, 0, 0, $month+1, $dayofnextmonth, $year);
1519 } else {
1520 // This will put us in a never ending loop.
1521 return $stats;
1525 // Add the base timestamp to the statistics if not present.
1526 foreach ($times as $count => $time) {
1527 if (!in_array($time,$actualtimes) && $count != count($times) -1) {
1528 $newobj = new StdClass;
1529 $newobj->timeend = $time;
1530 $newobj->id = 0;
1531 $newobj->roleid = 0;
1532 $newobj->line1 = 0;
1533 if (!empty($line2)) {
1534 $newobj->line2 = 0;
1536 if (!empty($line3)) {
1537 $newobj->line3 = 0;
1539 $newobj->zerofixed = true;
1540 $stats[] = $newobj;
1544 usort($stats,"stats_compare_times");
1545 return $stats;
1548 // helper function to sort arrays by $obj->timeend
1549 function stats_compare_times($a,$b) {
1550 if ($a->timeend == $b->timeend) {
1551 return 0;
1553 return ($a->timeend > $b->timeend) ? -1 : 1;
1556 function stats_check_uptodate($courseid=0) {
1557 global $CFG, $DB;
1559 if (empty($courseid)) {
1560 $courseid = SITEID;
1563 $latestday = stats_get_start_from('daily');
1565 if ((time() - 60*60*24*2) < $latestday) { // we're ok
1566 return NULL;
1569 $a = new stdClass();
1570 $a->daysdone = $DB->get_field_sql("SELECT COUNT(DISTINCT(timeend)) FROM {stats_daily}");
1572 // how many days between the last day and now?
1573 $a->dayspending = ceil((stats_get_base_daily() - $latestday)/(60*60*24));
1575 if ($a->dayspending == 0 && $a->daysdone != 0) {
1576 return NULL; // we've only just started...
1579 //return error as string
1580 return get_string('statscatchupmode','error',$a);
1584 * Create temporary tables to speed up log generation
1586 function stats_temp_table_create() {
1587 global $CFG, $DB;
1589 $dbman = $DB->get_manager(); // We are going to use database_manager services
1591 stats_temp_table_drop();
1593 $tables = array();
1595 /// Define tables user to be created
1596 $table = new xmldb_table('temp_stats_daily');
1597 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1598 $table->add_field('courseid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1599 $table->add_field('timeend', 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('stattype', XMLDB_TYPE_CHAR, 20, null, XMLDB_NOTNULL, null, 'activity');
1602 $table->add_field('stat1', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1603 $table->add_field('stat2', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1604 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1605 $table->add_index('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid'));
1606 $table->add_index('timeend', XMLDB_INDEX_NOTUNIQUE, array('timeend'));
1607 $table->add_index('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid'));
1608 $tables['temp_stats_daily'] = $table;
1610 $table = new xmldb_table('temp_stats_user_daily');
1611 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1612 $table->add_field('courseid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1613 $table->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1614 $table->add_field('roleid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1615 $table->add_field('timeend', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1616 $table->add_field('statsreads', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1617 $table->add_field('statswrites', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1618 $table->add_field('stattype', XMLDB_TYPE_CHAR, 30, null, XMLDB_NOTNULL, null, null);
1619 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1620 $table->add_index('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid'));
1621 $table->add_index('userid', XMLDB_INDEX_NOTUNIQUE, array('userid'));
1622 $table->add_index('timeend', XMLDB_INDEX_NOTUNIQUE, array('timeend'));
1623 $table->add_index('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid'));
1624 $tables['temp_stats_user_daily'] = $table;
1626 $table = new xmldb_table('temp_enroled');
1627 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1628 $table->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1629 $table->add_field('courseid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1630 $table->add_field('roleid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null);
1631 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1632 $table->add_index('userid', XMLDB_INDEX_NOTUNIQUE, array('userid'));
1633 $table->add_index('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid'));
1634 $table->add_index('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid'));
1635 $tables['temp_enroled'] = $table;
1638 $table = new xmldb_table('temp_log1');
1639 $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
1640 $table->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1641 $table->add_field('course', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
1642 $table->add_field('action', XMLDB_TYPE_CHAR, 40, null, XMLDB_NOTNULL, null, null);
1643 $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
1644 $table->add_index('action', XMLDB_INDEX_NOTUNIQUE, array('action'));
1645 $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
1646 $table->add_index('user', XMLDB_INDEX_NOTUNIQUE, array('userid'));
1647 $table->add_index('usercourseaction', XMLDB_INDEX_NOTUNIQUE, array('userid','course','action'));
1648 $tables['temp_log1'] = $table;
1650 /// temp_log2 is exactly the same as temp_log1.
1651 $tables['temp_log2'] = clone $tables['temp_log1'];
1652 $tables['temp_log2']->setName('temp_log2');
1654 try {
1656 foreach ($tables as $table) {
1657 $dbman->create_temp_table($table);
1660 } catch (Exception $e) {
1661 mtrace('Temporary table creation failed: '. $e->getMessage());
1662 return false;
1665 return true;
1669 * Deletes summary logs table for stats calculation
1671 function stats_temp_table_drop() {
1672 global $DB;
1674 $dbman = $DB->get_manager();
1676 $tables = array('temp_log1', 'temp_log2', 'temp_stats_daily', 'temp_stats_user_daily', 'temp_enroled');
1678 foreach ($tables as $name) {
1680 if ($dbman->table_exists($name)) {
1681 $table = new xmldb_table($name);
1683 try {
1684 $dbman->drop_table($table);
1685 } catch (Exception $e) {
1686 mtrace("Error occured while dropping temporary tables!");
1693 * Fills the temporary stats tables with new data
1695 * This function is meant to be called once at the start of stats generation
1697 * @param int timestart timestamp of the start time of logs view
1698 * @param int timeend timestamp of the end time of logs view
1699 * @return bool success (true) or failure(false)
1701 function stats_temp_table_setup() {
1702 global $DB;
1704 $sql = "INSERT INTO {temp_enroled} (userid, courseid, roleid)
1706 SELECT ue.userid, e.courseid, ra.roleid
1707 FROM {role_assignments} ra
1708 JOIN {context} c ON (c.id = ra.contextid AND c.contextlevel = :courselevel)
1709 JOIN {enrol} e ON e.courseid = c.instanceid
1710 JOIN {user_enrolments} ue ON (ue.enrolid = e.id AND ue.userid = ra.userid)";
1712 return stats_run_query($sql, array('courselevel' => CONTEXT_COURSE));
1716 * Fills the temporary stats tables with new data
1718 * This function is meant to be called to get a new day of data
1720 * @param int timestamp of the start time of logs view
1721 * @param int timestamp of the end time of logs view
1722 * @return bool success (true) or failure(false)
1724 function stats_temp_table_fill($timestart, $timeend) {
1725 global $DB;
1727 // First decide from where we want the data.
1729 $params = array('timestart' => $timestart,
1730 'timeend' => $timeend,
1731 'participating' => \core\event\base::LEVEL_PARTICIPATING,
1732 'teaching' => \core\event\base::LEVEL_TEACHING,
1733 'loginevent1' => '\core\event\user_loggedin',
1734 'loginevent2' => '\core\event\user_loggedin',
1737 $filled = false;
1738 $manager = get_log_manager();
1739 $stores = $manager->get_readers();
1740 foreach ($stores as $store) {
1741 if ($store instanceof \core\log\sql_internal_table_reader) {
1742 $logtable = $store->get_internal_log_table_name();
1743 if (!$logtable) {
1744 continue;
1747 $sql = "SELECT COUNT('x')
1748 FROM {{$logtable}}
1749 WHERE timecreated >= :timestart AND timecreated < :timeend";
1751 if (!$DB->get_field_sql($sql, $params)) {
1752 continue;
1755 // Let's fake the old records using new log data.
1756 // We want only data relevant to educational process
1757 // done by real users.
1759 $sql = "INSERT INTO {temp_log1} (userid, course, action)
1761 SELECT userid,
1762 CASE
1763 WHEN courseid IS NULL THEN ".SITEID."
1764 WHEN courseid = 0 THEN ".SITEID."
1765 ELSE courseid
1766 END,
1767 CASE
1768 WHEN eventname = :loginevent1 THEN 'login'
1769 WHEN crud = 'r' THEN 'view'
1770 ELSE 'update'
1772 FROM {{$logtable}}
1773 WHERE timecreated >= :timestart AND timecreated < :timeend
1774 AND (origin = 'web' OR origin = 'ws')
1775 AND (edulevel = :participating OR edulevel = :teaching OR eventname = :loginevent2)";
1777 $DB->execute($sql, $params);
1778 $filled = true;
1782 if (!$filled) {
1783 // Fallback to legacy data.
1784 $sql = "INSERT INTO {temp_log1} (userid, course, action)
1786 SELECT userid, course, action
1787 FROM {log}
1788 WHERE time >= :timestart AND time < :timeend";
1790 $DB->execute($sql, $params);
1793 $sql = 'INSERT INTO {temp_log2} (userid, course, action)
1795 SELECT userid, course, action FROM {temp_log1}';
1797 $DB->execute($sql);
1799 // We have just loaded all the temp tables, collect statistics for that.
1800 $DB->update_temp_table_stats();
1802 return true;
1807 * Deletes summary logs table for stats calculation
1809 * @return bool success (true) or failure(false)
1811 function stats_temp_table_clean() {
1812 global $DB;
1814 $sql = array();
1816 $sql['up1'] = 'INSERT INTO {stats_daily} (courseid, roleid, stattype, timeend, stat1, stat2)
1818 SELECT courseid, roleid, stattype, timeend, stat1, stat2 FROM {temp_stats_daily}';
1820 $sql['up2'] = 'INSERT INTO {stats_user_daily}
1821 (courseid, userid, roleid, timeend, statsreads, statswrites, stattype)
1823 SELECT courseid, userid, roleid, timeend, statsreads, statswrites, stattype
1824 FROM {temp_stats_user_daily}';
1826 foreach ($sql as $id => $query) {
1827 if (! stats_run_query($query)) {
1828 mtrace("Error during table cleanup!");
1829 return false;
1833 $tables = array('temp_log1', 'temp_log2', 'temp_stats_daily', 'temp_stats_user_daily');
1835 foreach ($tables as $name) {
1836 $DB->delete_records($name);
1839 return true;