Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 4.0.x will end 8 May 2023 (12 months).
  • Bug fixes for security issues in 4.0.x will end 13 November 2023 (18 months).
  • PHP version: minimum PHP 7.3.0 Note: the minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is also supported.
/lib/ -> statslib.php (source)
<?php

// This file is part of Moodle - http://moodle.org/
//
// Moodle is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// Moodle is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.

/**
 * @package    core
 * @subpackage stats
 * @copyright  1999 onwards Martin Dougiamas  {@link http://moodle.com}
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 */

defined('MOODLE_INTERNAL') || die();

/** THESE CONSTANTS ARE USED FOR THE REPORTING PAGE. */

define('STATS_REPORT_LOGINS',1); // double impose logins and unique logins on a line graph. site course only.
define('STATS_REPORT_READS',2); // double impose student reads and teacher reads on a line graph.
define('STATS_REPORT_WRITES',3); // double impose student writes and teacher writes on a line graph.
define('STATS_REPORT_ACTIVITY',4); // 2+3 added up, teacher vs student.
define('STATS_REPORT_ACTIVITYBYROLE',5); // all activity, reads vs writes, selected by role.

// user level stats reports.
define('STATS_REPORT_USER_ACTIVITY',7);
define('STATS_REPORT_USER_ALLACTIVITY',8);
define('STATS_REPORT_USER_LOGINS',9);
define('STATS_REPORT_USER_VIEW',10);  // this is the report you see on the user profile.

// admin only ranking stats reports
define('STATS_REPORT_ACTIVE_COURSES',11);
define('STATS_REPORT_ACTIVE_COURSES_WEIGHTED',12);
define('STATS_REPORT_PARTICIPATORY_COURSES',13);
define('STATS_REPORT_PARTICIPATORY_COURSES_RW',14);

// start after 0 = show dailies.
define('STATS_TIME_LASTWEEK',1);
define('STATS_TIME_LAST2WEEKS',2);
define('STATS_TIME_LAST3WEEKS',3);
define('STATS_TIME_LAST4WEEKS',4);

// start after 10 = show weeklies
define('STATS_TIME_LAST2MONTHS',12);

define('STATS_TIME_LAST3MONTHS',13);
define('STATS_TIME_LAST4MONTHS',14);
define('STATS_TIME_LAST5MONTHS',15);
define('STATS_TIME_LAST6MONTHS',16);

// start after 20 = show monthlies
define('STATS_TIME_LAST7MONTHS',27);
define('STATS_TIME_LAST8MONTHS',28);
define('STATS_TIME_LAST9MONTHS',29);
define('STATS_TIME_LAST10MONTHS',30);
define('STATS_TIME_LAST11MONTHS',31);
define('STATS_TIME_LASTYEAR',32);

// different modes for what reports to offer
define('STATS_MODE_GENERAL',1);
define('STATS_MODE_DETAILED',2);
define('STATS_MODE_RANKED',3); // admins only - ranks courses

// Output string when nodebug is on
define('STATS_PLACEHOLDER_OUTPUT', '.');

/**
 * Print daily cron progress
 * @param string $ident
 */
function stats_progress($ident) {
    static $start = 0;
    static $init  = 0;

    if ($ident == 'init') {
        $init = $start = microtime(true);
        return;
    }

    $elapsed = round(microtime(true) - $start);
    $start   = microtime(true);

    if (debugging('', DEBUG_ALL)) {
        mtrace("$ident:$elapsed ", '');
    } else {
        mtrace(STATS_PLACEHOLDER_OUTPUT, '');
    }
}

/**
 * Execute individual daily statistics queries
 *
 * @param string $sql The query to run
 * @return boolean success
 */
function stats_run_query($sql, $parameters = array()) {
    global $DB;

    try {
        $DB->execute($sql, $parameters);
    } catch (dml_exception $e) {

       if (debugging('', DEBUG_ALL)) {
           mtrace($e->getMessage());
       }
       return false;
    }
    return true;
}

/**
 * Execute daily statistics gathering
 *
 * @param int $maxdays maximum number of days to be processed
 * @return boolean success
 */
function stats_cron_daily($maxdays=1) {
    global $CFG, $DB;
    require_once($CFG->libdir.'/adminlib.php');

    $now = time();

    $fpcontext = context_course::instance(SITEID, MUST_EXIST);

    // read last execution date from db
    if (!$timestart = get_config(NULL, 'statslastdaily')) {
        $timestart = stats_get_base_daily(stats_get_start_from('daily'));
        set_config('statslastdaily', $timestart);
    }

    $nextmidnight = stats_get_next_day_start($timestart);

    // are there any days that need to be processed?
    if ($now < $nextmidnight) {
        return true; // everything ok and up-to-date
    }

    $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;

    if (!set_cron_lock('statsrunning', $now + $timeout)) {
        return false;
    }

    // first delete entries that should not be there yet
    $DB->delete_records_select('stats_daily',      "timeend > $timestart");
    $DB->delete_records_select('stats_user_daily', "timeend > $timestart");

    // Read in a few things we'll use later
    $viewactions = stats_get_action_names('view');
    $postactions = stats_get_action_names('post');

    $guest           = (int)$CFG->siteguest;
    $guestrole       = (int)$CFG->guestroleid;
    $defaultfproleid = (int)$CFG->defaultfrontpageroleid;

    mtrace("Running daily statistics gathering, starting at $timestart:");
< cron_trace_time_and_memory();
> \core\cron::trace_time_and_memory();
$days = 0; $total = 0; $failed = false; // failed stats flag $timeout = false; if (!stats_temp_table_create()) { $days = 1; $failed = true; } mtrace('Temporary tables created'); if(!stats_temp_table_setup()) { $days = 1; $failed = true; } mtrace('Enrolments calculated'); $totalactiveusers = $DB->count_records('user', array('deleted' => '0')); while (!$failed && ($now > $nextmidnight)) { if ($days >= $maxdays) { $timeout = true; break; } $days++; core_php_time_limit::raise($timeout - 200); if ($days > 1) { // move the lock set_cron_lock('statsrunning', time() + $timeout, true); } $daystart = time(); stats_progress('init'); if (!stats_temp_table_fill($timestart, $nextmidnight)) { $failed = true; break; } // Find out if any logs available for this day $sql = "SELECT 'x' FROM {temp_log1} l"; $logspresent = $DB->get_records_sql($sql, null, 0, 1); if ($logspresent) { // Insert blank record to force Query 10 to generate additional row when no logs for // the site with userid 0 exist. Added for backwards compatibility. $DB->insert_record('temp_log1', array('userid' => 0, 'course' => SITEID, 'action' => '')); } // Calculate the number of active users today $sql = 'SELECT COUNT(DISTINCT u.id) FROM {user} u JOIN {temp_log1} l ON l.userid = u.id WHERE u.deleted = 0'; $dailyactiveusers = $DB->count_records_sql($sql); stats_progress('0'); // Process login info first // Note: PostgreSQL doesn't like aliases in HAVING clauses $sql = "INSERT INTO {temp_stats_user_daily} (stattype, timeend, courseid, userid, statsreads) SELECT 'logins', $nextmidnight AS timeend, ".SITEID." AS courseid, userid, COUNT(id) AS statsreads FROM {temp_log1} l WHERE action = 'login' GROUP BY userid HAVING COUNT(id) > 0"; if ($logspresent && !stats_run_query($sql)) { $failed = true; break; } $DB->update_temp_table_stats(); stats_progress('1'); $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'logins' AS stattype, $nextmidnight AS timeend, ".SITEID." AS courseid, 0, COALESCE(SUM(statsreads), 0) as stat1, COUNT('x') as stat2 FROM {temp_stats_user_daily} WHERE stattype = 'logins' AND timeend = $nextmidnight"; if ($logspresent && !stats_run_query($sql)) { $failed = true; break; } stats_progress('2'); // Enrolments and active enrolled users // // Unfortunately, we do not know how many users were registered // at given times in history :-( // - stat1: enrolled users // - stat2: enrolled users active in this period // - SITEID is special case here, because it's all about default enrolment // in that case, we'll count non-deleted users. // $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'enrolments' as stattype, $nextmidnight as timeend, courseid, roleid, COUNT(DISTINCT userid) as stat1, 0 as stat2 FROM {temp_enroled} GROUP BY courseid, roleid"; if (!stats_run_query($sql)) { $failed = true; break; } stats_progress('3'); // Set stat2 to the number distinct users with role assignments in the course that were active // using table alias in UPDATE does not work in pg < 8.2 $sql = "UPDATE {temp_stats_daily} SET stat2 = ( SELECT COUNT(DISTINCT userid) FROM {temp_enroled} te WHERE roleid = {temp_stats_daily}.roleid AND courseid = {temp_stats_daily}.courseid AND EXISTS ( SELECT 'x' FROM {temp_log1} l WHERE l.course = {temp_stats_daily}.courseid AND l.userid = te.userid ) ) WHERE {temp_stats_daily}.stattype = 'enrolments' AND {temp_stats_daily}.timeend = $nextmidnight AND {temp_stats_daily}.courseid IN ( SELECT DISTINCT course FROM {temp_log2})"; if ($logspresent && !stats_run_query($sql, array('courselevel'=>CONTEXT_COURSE))) { $failed = true; break; } stats_progress('4'); // Now get course total enrolments (roleid==0) - except frontpage $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'enrolments', $nextmidnight AS timeend, te.courseid AS courseid, 0 AS roleid, COUNT(DISTINCT userid) AS stat1, 0 AS stat2 FROM {temp_enroled} te GROUP BY courseid HAVING COUNT(DISTINCT userid) > 0"; if ($logspresent && !stats_run_query($sql)) { $failed = true; break; } stats_progress('5'); // Set stat 2 to the number of enrolled users who were active in the course $sql = "UPDATE {temp_stats_daily} SET stat2 = ( SELECT COUNT(DISTINCT te.userid) FROM {temp_enroled} te WHERE te.courseid = {temp_stats_daily}.courseid AND EXISTS ( SELECT 'x' FROM {temp_log1} l WHERE l.course = {temp_stats_daily}.courseid AND l.userid = te.userid ) ) WHERE {temp_stats_daily}.stattype = 'enrolments' AND {temp_stats_daily}.timeend = $nextmidnight AND {temp_stats_daily}.roleid = 0 AND {temp_stats_daily}.courseid IN ( SELECT l.course FROM {temp_log2} l WHERE l.course <> ".SITEID.")"; if ($logspresent && !stats_run_query($sql, array())) { $failed = true; break; } stats_progress('6'); // Frontpage(==site) enrolments total $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'enrolments', $nextmidnight, ".SITEID.", 0, $totalactiveusers AS stat1, $dailyactiveusers AS stat2" . $DB->sql_null_from_clause(); if ($logspresent && !stats_run_query($sql)) { $failed = true; break; } // The steps up until this point, all add to {temp_stats_daily} and don't use new tables. // There is no point updating statistics as they won't be used until the DELETE below. $DB->update_temp_table_stats(); stats_progress('7'); // Default frontpage role enrolments are all site users (not deleted) if ($defaultfproleid) { // first remove default frontpage role counts if created by previous query $sql = "DELETE FROM {temp_stats_daily} WHERE stattype = 'enrolments' AND courseid = ".SITEID." AND roleid = $defaultfproleid AND timeend = $nextmidnight"; if ($logspresent && !stats_run_query($sql)) { $failed = true; break; } stats_progress('8'); $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'enrolments', $nextmidnight, ".SITEID.", $defaultfproleid, $totalactiveusers AS stat1, $dailyactiveusers AS stat2" . $DB->sql_null_from_clause(); if ($logspresent && !stats_run_query($sql)) { $failed = true; break; } stats_progress('9'); } else { stats_progress('x'); stats_progress('x'); } /// individual user stats (including not-logged-in) in each course, this is slow - reuse this data if possible list($viewactionssql, $params1) = $DB->get_in_or_equal($viewactions, SQL_PARAMS_NAMED, 'view'); list($postactionssql, $params2) = $DB->get_in_or_equal($postactions, SQL_PARAMS_NAMED, 'post'); $sql = "INSERT INTO {temp_stats_user_daily} (stattype, timeend, courseid, userid, statsreads, statswrites) SELECT 'activity' AS stattype, $nextmidnight AS timeend, course AS courseid, userid, SUM(CASE WHEN action $viewactionssql THEN 1 ELSE 0 END) AS statsreads, SUM(CASE WHEN action $postactionssql THEN 1 ELSE 0 END) AS statswrites FROM {temp_log1} l GROUP BY userid, course"; if ($logspresent && !stats_run_query($sql, array_merge($params1, $params2))) { $failed = true; break; } stats_progress('10'); /// How many view/post actions in each course total $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'activity' AS stattype, $nextmidnight AS timeend, c.id AS courseid, 0, SUM(CASE WHEN l.action $viewactionssql THEN 1 ELSE 0 END) AS stat1, SUM(CASE WHEN l.action $postactionssql THEN 1 ELSE 0 END) AS stat2 FROM {course} c, {temp_log1} l WHERE l.course = c.id GROUP BY c.id"; if ($logspresent && !stats_run_query($sql, array_merge($params1, $params2))) { $failed = true; break; } stats_progress('11'); /// how many view actions for each course+role - excluding guests and frontpage $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'activity', $nextmidnight AS timeend, courseid, roleid, SUM(statsreads), SUM(statswrites) FROM ( SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites FROM {temp_stats_user_daily} sud, ( SELECT DISTINCT te.userid, te.roleid, te.courseid FROM {temp_enroled} te WHERE te.roleid <> $guestrole AND te.userid <> $guest ) pl WHERE sud.userid = pl.userid AND sud.courseid = pl.courseid AND sud.timeend = $nextmidnight AND sud.stattype='activity' ) inline_view GROUP BY courseid, roleid HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0"; if ($logspresent && !stats_run_query($sql, array('courselevel'=>CONTEXT_COURSE))) { $failed = true; break; } stats_progress('12'); /// how many view actions from guests only in each course - excluding frontpage /// normal users may enter course with temporary guest access too $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'activity', $nextmidnight AS timeend, courseid, $guestrole AS roleid, SUM(statsreads), SUM(statswrites) FROM ( SELECT sud.courseid, sud.statsreads, sud.statswrites FROM {temp_stats_user_daily} sud WHERE sud.timeend = $nextmidnight AND sud.courseid <> ".SITEID." AND sud.stattype='activity' AND (sud.userid = $guest OR sud.userid NOT IN ( SELECT userid FROM {temp_enroled} te WHERE te.courseid = sud.courseid )) ) inline_view GROUP BY courseid HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0"; if ($logspresent && !stats_run_query($sql, array())) { $failed = true; break; } stats_progress('13'); /// How many view actions for each role on frontpage - excluding guests, not-logged-in and default frontpage role $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'activity', $nextmidnight AS timeend, courseid, roleid, SUM(statsreads), SUM(statswrites) FROM ( SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites FROM {temp_stats_user_daily} sud, ( SELECT DISTINCT ra.userid, ra.roleid, c.instanceid AS courseid FROM {role_assignments} ra JOIN {context} c ON c.id = ra.contextid WHERE ra.contextid = :fpcontext AND ra.roleid <> $defaultfproleid AND ra.roleid <> $guestrole AND ra.userid <> $guest ) pl WHERE sud.userid = pl.userid AND sud.courseid = pl.courseid AND sud.timeend = $nextmidnight AND sud.stattype='activity' ) inline_view GROUP BY courseid, roleid HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0"; if ($logspresent && !stats_run_query($sql, array('fpcontext'=>$fpcontext->id))) { $failed = true; break; } stats_progress('14'); // How many view actions for default frontpage role on frontpage only $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'activity', timeend, courseid, $defaultfproleid AS roleid, SUM(statsreads), SUM(statswrites) FROM ( SELECT sud.timeend AS timeend, sud.courseid, sud.statsreads, sud.statswrites FROM {temp_stats_user_daily} sud WHERE sud.timeend = :nextm AND sud.courseid = :siteid AND sud.stattype='activity' AND sud.userid <> $guest AND sud.userid <> 0 AND sud.userid NOT IN ( SELECT ra.userid FROM {role_assignments} ra WHERE ra.roleid <> $guestrole AND ra.roleid <> $defaultfproleid AND ra.contextid = :fpcontext) ) inline_view GROUP BY timeend, courseid HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0"; if ($logspresent && !stats_run_query($sql, array('fpcontext'=>$fpcontext->id, 'siteid'=>SITEID, 'nextm'=>$nextmidnight))) { $failed = true; break; } $DB->update_temp_table_stats(); stats_progress('15'); // How many view actions for guests or not-logged-in on frontpage $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT stattype, timeend, courseid, $guestrole AS roleid, SUM(statsreads) AS stat1, SUM(statswrites) AS stat2 FROM ( SELECT sud.stattype, sud.timeend, sud.courseid, sud.statsreads, sud.statswrites FROM {temp_stats_user_daily} sud WHERE (sud.userid = $guest OR sud.userid = 0) AND sud.timeend = $nextmidnight AND sud.courseid = ".SITEID." AND sud.stattype='activity' ) inline_view GROUP BY stattype, timeend, courseid HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0"; if ($logspresent && !stats_run_query($sql)) { $failed = true; break; } stats_progress('16'); stats_temp_table_clean(); stats_progress('out'); // remember processed days set_config('statslastdaily', $nextmidnight); $elapsed = time()-$daystart; mtrace(" finished until $nextmidnight: ".userdate($nextmidnight)." (in $elapsed s)"); $total += $elapsed; $timestart = $nextmidnight; $nextmidnight = stats_get_next_day_start($nextmidnight); } stats_temp_table_drop(); set_cron_lock('statsrunning', null); if ($failed) { $days--; mtrace("...error occurred, completed $days days of statistics in {$total} s."); return false; } else if ($timeout) { mtrace("...stopping early, reached maximum number of $maxdays days ({$total} s) - will continue next time."); return false; } else { mtrace("...completed $days days of statistics in {$total} s."); return true; } } /** * Execute weekly statistics gathering * @return boolean success */ function stats_cron_weekly() { global $CFG, $DB; require_once($CFG->libdir.'/adminlib.php'); $now = time(); // read last execution date from db if (!$timestart = get_config(NULL, 'statslastweekly')) { $timestart = stats_get_base_daily(stats_get_start_from('weekly')); set_config('statslastweekly', $timestart); } $nextstartweek = stats_get_next_week_start($timestart); // are there any weeks that need to be processed? if ($now < $nextstartweek) { return true; // everything ok and up-to-date } $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime; if (!set_cron_lock('statsrunning', $now + $timeout)) { return false; } // fisrt delete entries that should not be there yet $DB->delete_records_select('stats_weekly', "timeend > $timestart"); $DB->delete_records_select('stats_user_weekly', "timeend > $timestart"); mtrace("Running weekly statistics gathering, starting at $timestart:");
< cron_trace_time_and_memory();
> \core\cron::trace_time_and_memory();
$weeks = 0; while ($now > $nextstartweek) { core_php_time_limit::raise($timeout - 200); $weeks++; if ($weeks > 1) { // move the lock set_cron_lock('statsrunning', time() + $timeout, true); } $stattimesql = "timeend > $timestart AND timeend <= $nextstartweek"; $weekstart = time(); stats_progress('init'); /// process login info first $sql = "INSERT INTO {stats_user_weekly} (stattype, timeend, courseid, userid, statsreads) SELECT 'logins', timeend, courseid, userid, SUM(statsreads) FROM ( SELECT $nextstartweek AS timeend, courseid, userid, statsreads FROM {stats_user_daily} sd WHERE stattype = 'logins' AND $stattimesql ) inline_view GROUP BY timeend, courseid, userid HAVING SUM(statsreads) > 0"; $DB->execute($sql); stats_progress('1'); $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'logins' AS stattype, $nextstartweek AS timeend, ".SITEID." as courseid, 0, COALESCE((SELECT SUM(statsreads) FROM {stats_user_weekly} s1 WHERE s1.stattype = 'logins' AND timeend = $nextstartweek), 0) AS nstat1, (SELECT COUNT('x') FROM {stats_user_weekly} s2 WHERE s2.stattype = 'logins' AND timeend = $nextstartweek) AS nstat2" . $DB->sql_null_from_clause(); $DB->execute($sql); stats_progress('2'); /// now enrolments averages $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . " FROM ( SELECT $nextstartweek AS ntimeend, courseid, roleid, stat1, stat2 FROM {stats_daily} sd WHERE stattype = 'enrolments' AND $stattimesql ) inline_view GROUP BY ntimeend, courseid, roleid"; $DB->execute($sql); stats_progress('3'); /// activity read/write averages $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'activity', ntimeend, courseid, roleid, SUM(stat1), SUM(stat2) FROM ( SELECT $nextstartweek AS ntimeend, courseid, roleid, stat1, stat2 FROM {stats_daily} WHERE stattype = 'activity' AND $stattimesql ) inline_view GROUP BY ntimeend, courseid, roleid"; $DB->execute($sql); stats_progress('4'); /// user read/write averages $sql = "INSERT INTO {stats_user_weekly} (stattype, timeend, courseid, userid, statsreads, statswrites) SELECT 'activity', ntimeend, courseid, userid, SUM(statsreads), SUM(statswrites) FROM ( SELECT $nextstartweek AS ntimeend, courseid, userid, statsreads, statswrites FROM {stats_user_daily} WHERE stattype = 'activity' AND $stattimesql ) inline_view GROUP BY ntimeend, courseid, userid"; $DB->execute($sql); stats_progress('5'); set_config('statslastweekly', $nextstartweek); $elapsed = time()-$weekstart; mtrace(" finished until $nextstartweek: ".userdate($nextstartweek) ." (in $elapsed s)"); $timestart = $nextstartweek; $nextstartweek = stats_get_next_week_start($nextstartweek); } set_cron_lock('statsrunning', null); mtrace("...completed $weeks weeks of statistics."); return true; } /** * Execute monthly statistics gathering * @return boolean success */ function stats_cron_monthly() { global $CFG, $DB; require_once($CFG->libdir.'/adminlib.php'); $now = time(); // read last execution date from db if (!$timestart = get_config(NULL, 'statslastmonthly')) { $timestart = stats_get_base_monthly(stats_get_start_from('monthly')); set_config('statslastmonthly', $timestart); } $nextstartmonth = stats_get_next_month_start($timestart); // are there any months that need to be processed? if ($now < $nextstartmonth) { return true; // everything ok and up-to-date } $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime; if (!set_cron_lock('statsrunning', $now + $timeout)) { return false; } // fisr delete entries that should not be there yet $DB->delete_records_select('stats_monthly', "timeend > $timestart"); $DB->delete_records_select('stats_user_monthly', "timeend > $timestart"); $startmonth = stats_get_base_monthly($now); mtrace("Running monthly statistics gathering, starting at $timestart:");
< cron_trace_time_and_memory();
> \core\cron::trace_time_and_memory();
$months = 0; while ($now > $nextstartmonth) { core_php_time_limit::raise($timeout - 200); $months++; if ($months > 1) { // move the lock set_cron_lock('statsrunning', time() + $timeout, true); } $stattimesql = "timeend > $timestart AND timeend <= $nextstartmonth"; $monthstart = time(); stats_progress('init'); /// process login info first $sql = "INSERT INTO {stats_user_monthly} (stattype, timeend, courseid, userid, statsreads) SELECT 'logins', timeend, courseid, userid, SUM(statsreads) FROM ( SELECT $nextstartmonth AS timeend, courseid, userid, statsreads FROM {stats_user_daily} sd WHERE stattype = 'logins' AND $stattimesql ) inline_view GROUP BY timeend, courseid, userid HAVING SUM(statsreads) > 0"; $DB->execute($sql); stats_progress('1'); $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'logins' AS stattype, $nextstartmonth AS timeend, ".SITEID." as courseid, 0, COALESCE((SELECT SUM(statsreads) FROM {stats_user_monthly} s1 WHERE s1.stattype = 'logins' AND timeend = $nextstartmonth), 0) AS nstat1, (SELECT COUNT('x') FROM {stats_user_monthly} s2 WHERE s2.stattype = 'logins' AND timeend = $nextstartmonth) AS nstat2" . $DB->sql_null_from_clause(); $DB->execute($sql); stats_progress('2'); /// now enrolments averages $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . " FROM ( SELECT $nextstartmonth AS ntimeend, courseid, roleid, stat1, stat2 FROM {stats_daily} sd WHERE stattype = 'enrolments' AND $stattimesql ) inline_view GROUP BY ntimeend, courseid, roleid"; $DB->execute($sql); stats_progress('3'); /// activity read/write averages $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2) SELECT 'activity', ntimeend, courseid, roleid, SUM(stat1), SUM(stat2) FROM ( SELECT $nextstartmonth AS ntimeend, courseid, roleid, stat1, stat2 FROM {stats_daily} WHERE stattype = 'activity' AND $stattimesql ) inline_view GROUP BY ntimeend, courseid, roleid"; $DB->execute($sql); stats_progress('4'); /// user read/write averages $sql = "INSERT INTO {stats_user_monthly} (stattype, timeend, courseid, userid, statsreads, statswrites) SELECT 'activity', ntimeend, courseid, userid, SUM(statsreads), SUM(statswrites) FROM ( SELECT $nextstartmonth AS ntimeend, courseid, userid, statsreads, statswrites FROM {stats_user_daily} WHERE stattype = 'activity' AND $stattimesql ) inline_view GROUP BY ntimeend, courseid, userid"; $DB->execute($sql); stats_progress('5'); set_config('statslastmonthly', $nextstartmonth); $elapsed = time() - $monthstart; mtrace(" finished until $nextstartmonth: ".userdate($nextstartmonth) ." (in $elapsed s)"); $timestart = $nextstartmonth; $nextstartmonth = stats_get_next_month_start($nextstartmonth); } set_cron_lock('statsrunning', null); mtrace("...completed $months months of statistics."); return true; } /** * Return starting date of stats processing * @param string $str name of table - daily, weekly or monthly * @return int timestamp */ function stats_get_start_from($str) { global $CFG, $DB; // are there any data in stats table? Should not be... if ($timeend = $DB->get_field_sql('SELECT MAX(timeend) FROM {stats_'.$str.'}')) { return $timeend; } // decide what to do based on our config setting (either all or none or a timestamp) switch ($CFG->statsfirstrun) { case 'all': $manager = get_log_manager(); $stores = $manager->get_readers(); $firstlog = false; foreach ($stores as $store) { if ($store instanceof \core\log\sql_internal_table_reader) { $logtable = $store->get_internal_log_table_name(); if (!$logtable) { continue; } $first = $DB->get_field_sql("SELECT MIN(timecreated) FROM {{$logtable}}"); if ($first and (!$firstlog or $firstlog > $first)) { $firstlog = $first; } } } $first = $DB->get_field_sql('SELECT MIN(time) FROM {log}'); if ($first and (!$firstlog or $firstlog > $first)) { $firstlog = $first; } if ($firstlog) { return $firstlog; } default: if (is_numeric($CFG->statsfirstrun)) { return time() - $CFG->statsfirstrun; } // not a number? use next instead case 'none': return strtotime('-3 day', time()); } } /** * Start of day * @param int $time timestamp * @return int start of day */ function stats_get_base_daily($time=0) { if (empty($time)) { $time = time(); } core_date::set_default_server_timezone(); $time = strtotime(date('d-M-Y', $time)); return $time; } /** * Start of week * @param int $time timestamp * @return int start of week */ function stats_get_base_weekly($time=0) { global $CFG; $datetime = new DateTime(); $datetime->setTimestamp(stats_get_base_daily($time)); $startday = $CFG->calendar_startwday; core_date::set_default_server_timezone(); $thisday = date('w', $time); $days = 0; if ($thisday > $startday) { $days = $thisday - $startday; } else if ($thisday < $startday) { $days = 7 + $thisday - $startday; } $datetime->sub(new DateInterval("P{$days}D")); return $datetime->getTimestamp(); } /** * Start of month * @param int $time timestamp * @return int start of month */ function stats_get_base_monthly($time=0) { if (empty($time)) { $time = time(); } core_date::set_default_server_timezone(); $return = strtotime(date('1-M-Y', $time)); return $return; } /** * Start of next day * @param int $time timestamp * @return start of next day */ function stats_get_next_day_start($time) { $next = stats_get_base_daily($time); $nextdate = new DateTime(); $nextdate->setTimestamp($next); $nextdate->add(new DateInterval('P1D')); return $nextdate->getTimestamp(); } /** * Start of next week * @param int $time timestamp * @return start of next week */ function stats_get_next_week_start($time) { $next = stats_get_base_weekly($time); $nextdate = new DateTime(); $nextdate->setTimestamp($next); $nextdate->add(new DateInterval('P1W')); return $nextdate->getTimestamp(); } /** * Start of next month * @param int $time timestamp * @return start of next month */ function stats_get_next_month_start($time) { $next = stats_get_base_monthly($time); $nextdate = new DateTime(); $nextdate->setTimestamp($next); $nextdate->add(new DateInterval('P1M')); return $nextdate->getTimestamp(); } /** * Remove old stats data */ function stats_clean_old() { global $DB; mtrace("Running stats cleanup tasks...");
< cron_trace_time_and_memory();
> \core\cron::trace_time_and_memory();
$deletebefore = stats_get_base_monthly(); // delete dailies older than 3 months (to be safe) $deletebefore = strtotime('-3 months', $deletebefore); $DB->delete_records_select('stats_daily', "timeend < $deletebefore"); $DB->delete_records_select('stats_user_daily', "timeend < $deletebefore"); // delete weeklies older than 9 months (to be safe) $deletebefore = strtotime('-6 months', $deletebefore); $DB->delete_records_select('stats_weekly', "timeend < $deletebefore"); $DB->delete_records_select('stats_user_weekly', "timeend < $deletebefore"); // don't delete monthlies mtrace("...stats cleanup finished"); } function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) { global $CFG, $DB; $param = new stdClass(); $param->params = array(); if ($time < 10) { // dailies // number of days to go back = 7* time $param->table = 'daily'; $param->timeafter = strtotime("-".($time*7)." days",stats_get_base_daily()); } elseif ($time < 20) { // weeklies // number of weeks to go back = time - 10 * 4 (weeks) + base week $param->table = 'weekly'; $param->timeafter = strtotime("-".(($time - 10)*4)." weeks",stats_get_base_weekly()); } else { // monthlies. // number of months to go back = time - 20 * months + base month $param->table = 'monthly'; $param->timeafter = strtotime("-".($time - 20)." months",stats_get_base_monthly()); } $param->extras = ''; switch ($report) { // ******************** STATS_MODE_GENERAL ******************** // case STATS_REPORT_LOGINS: $param->fields = 'timeend,sum(stat1) as line1,sum(stat2) as line2'; $param->fieldscomplete = true; $param->stattype = 'logins'; $param->line1 = get_string('statslogins'); $param->line2 = get_string('statsuniquelogins'); if ($courseid == SITEID) { $param->extras = 'GROUP BY timeend'; } break; case STATS_REPORT_READS: $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat1 as line1'; $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries. $param->aggregategroupby = 'roleid'; $param->stattype = 'activity'; $param->crosstab = true; $param->extras = 'GROUP BY timeend,roleid,stat1'; if ($courseid == SITEID) { $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1) as line1'; $param->extras = 'GROUP BY timeend,roleid'; } break; case STATS_REPORT_WRITES: $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat2 as line1'; $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries. $param->aggregategroupby = 'roleid'; $param->stattype = 'activity'; $param->crosstab = true; $param->extras = 'GROUP BY timeend,roleid,stat2'; if ($courseid == SITEID) { $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat2) as line1'; $param->extras = 'GROUP BY timeend,roleid'; } break; case STATS_REPORT_ACTIVITY: $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1+stat2) as line1'; $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries. $param->aggregategroupby = 'roleid'; $param->stattype = 'activity'; $param->crosstab = true; $param->extras = 'GROUP BY timeend,roleid'; if ($courseid == SITEID) { $param->extras = 'GROUP BY timeend,roleid'; } break; case STATS_REPORT_ACTIVITYBYROLE; $param->fields = 'stat1 AS line1, stat2 AS line2'; $param->stattype = 'activity'; $rolename = ''; if ($roleid <> 0) { if ($role = $DB->get_record('role', ['id' => $roleid])) { $rolename = role_get_name($role, context_course::instance($courseid)) . ' '; } } $param->line1 = $rolename . get_string('statsreads'); $param->line2 = $rolename . get_string('statswrites'); if ($courseid == SITEID) { $param->extras = 'GROUP BY timeend'; } break; // ******************** STATS_MODE_DETAILED ******************** // case STATS_REPORT_USER_ACTIVITY: $param->fields = 'statsreads as line1, statswrites as line2'; $param->line1 = get_string('statsuserreads'); $param->line2 = get_string('statsuserwrites'); $param->stattype = 'activity'; break; case STATS_REPORT_USER_ALLACTIVITY: $param->fields = 'statsreads+statswrites as line1'; $param->line1 = get_string('statsuseractivity'); $param->stattype = 'activity'; break; case STATS_REPORT_USER_LOGINS: $param->fields = 'statsreads as line1'; $param->line1 = get_string('statsuserlogins'); $param->stattype = 'logins'; break; case STATS_REPORT_USER_VIEW: $param->fields = 'timeend, SUM(statsreads) AS line1, SUM(statswrites) AS line2, SUM(statsreads+statswrites) AS line3'; $param->fieldscomplete = true; $param->line1 = get_string('statsuserreads'); $param->line2 = get_string('statsuserwrites'); $param->line3 = get_string('statsuseractivity'); $param->stattype = 'activity'; $param->extras = "GROUP BY timeend"; break; // ******************** STATS_MODE_RANKED ******************** // case STATS_REPORT_ACTIVE_COURSES: $param->fields = 'sum(stat1+stat2) AS line1'; $param->stattype = 'activity'; $param->orderby = 'line1 DESC'; $param->line1 = get_string('useractivity'); $param->graphline = 'line1'; break; case STATS_REPORT_ACTIVE_COURSES_WEIGHTED: $threshold = 0; if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) { $threshold = $CFG->statsuserthreshold; } $param->fields = ''; $param->sql = 'SELECT activity.courseid, activity.all_activity AS line1, enrolments.highest_enrolments AS line2, activity.all_activity / enrolments.highest_enrolments as line3 FROM ( SELECT courseid, sum(stat1+stat2) AS all_activity FROM {stats_'.$param->table.'} WHERE stattype=\'activity\' AND timeend >= '.(int)$param->timeafter.' AND roleid = 0 GROUP BY courseid ) activity INNER JOIN ( SELECT courseid, max(stat1) AS highest_enrolments FROM {stats_'.$param->table.'} WHERE stattype=\'enrolments\' AND timeend >= '.(int)$param->timeafter.' AND stat1 > '.(int)$threshold.' GROUP BY courseid ) enrolments ON (activity.courseid = enrolments.courseid) ORDER BY line3 DESC'; $param->line1 = get_string('useractivity'); $param->line2 = get_string('users'); $param->line3 = get_string('activityweighted'); $param->graphline = 'line3'; break; case STATS_REPORT_PARTICIPATORY_COURSES: $threshold = 0; if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) { $threshold = $CFG->statsuserthreshold; } $param->fields = ''; $param->sql = 'SELECT courseid, ' . $DB->sql_ceil('avg(all_enrolments)') . ' as line1, ' . $DB->sql_ceil('avg(active_enrolments)') . ' as line2, avg(proportion_active) AS line3 FROM ( SELECT courseid, timeend, stat2 as active_enrolments, stat1 as all_enrolments, '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' AS proportion_active FROM {stats_'.$param->table.'} WHERE stattype=\'enrolments\' AND roleid = 0 AND stat1 > '.(int)$threshold.' ) aq WHERE timeend >= '.(int)$param->timeafter.' GROUP BY courseid ORDER BY line3 DESC'; $param->line1 = get_string('users'); $param->line2 = get_string('activeusers'); $param->line3 = get_string('participationratio'); $param->graphline = 'line3'; break; case STATS_REPORT_PARTICIPATORY_COURSES_RW: $param->fields = ''; $param->sql = 'SELECT courseid, sum(views) AS line1, sum(posts) AS line2, avg(proportion_active) AS line3 FROM ( SELECT courseid, timeend, stat1 as views, stat2 AS posts, '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' as proportion_active FROM {stats_'.$param->table.'} WHERE stattype=\'activity\' AND roleid = 0 AND stat1 > 0 ) aq WHERE timeend >= '.(int)$param->timeafter.' GROUP BY courseid ORDER BY line3 DESC'; $param->line1 = get_string('views'); $param->line2 = get_string('posts'); $param->line3 = get_string('participationratio'); $param->graphline = 'line3'; break; } /* if ($courseid == SITEID && $mode != STATS_MODE_RANKED) { // just aggregate all courses. $param->fields = preg_replace('/(?:sum)([a-zA-Z0-9+_]*)\W+as\W+([a-zA-Z0-9_]*)/i','sum($1) as $2',$param->fields); $param->extras = ' GROUP BY timeend'.((!empty($param->aggregategroupby)) ? ','.$param->aggregategroupby : ''); } */ //TODO must add the SITEID reports to the rest of the reports. return $param; } function stats_get_view_actions() { return array('view','view all','history'); } function stats_get_post_actions() { return array('add','delete','edit','add mod','delete mod','edit section'.'enrol','loginas','new','unenrol','update','update mod'); } function stats_get_action_names($str) { global $CFG, $DB; $mods = $DB->get_records('modules'); $function = 'stats_get_'.$str.'_actions'; $actions = $function(); foreach ($mods as $mod) { $file = $CFG->dirroot.'/mod/'.$mod->name.'/lib.php'; if (!is_readable($file)) { continue; } require_once($file); $function = $mod->name.'_get_'.$str.'_actions'; if (function_exists($function)) { $mod_actions = $function(); if (is_array($mod_actions)) { $actions = array_merge($actions, $mod_actions); } } } // The array_values() forces a stack-like array // so we can later loop over safely... $actions = array_values(array_unique($actions)); $c = count($actions); for ($n=0;$n<$c;$n++) { $actions[$n] = $actions[$n]; } return $actions; } function stats_get_time_options($now,$lastweekend,$lastmonthend,$earliestday,$earliestweek,$earliestmonth) { $now = stats_get_base_daily(time()); // it's really important that it's TIMEEND in the table. ie, tuesday 00:00:00 is monday night. // so we need to take a day off here (essentially add a day to $now $now += 60*60*24; $timeoptions = array(); if ($now - (60*60*24*7) >= $earliestday) { $timeoptions[STATS_TIME_LASTWEEK] = get_string('numweeks','moodle',1); } if ($now - (60*60*24*14) >= $earliestday) { $timeoptions[STATS_TIME_LAST2WEEKS] = get_string('numweeks','moodle',2); } if ($now - (60*60*24*21) >= $earliestday) { $timeoptions[STATS_TIME_LAST3WEEKS] = get_string('numweeks','moodle',3); } if ($now - (60*60*24*28) >= $earliestday) { $timeoptions[STATS_TIME_LAST4WEEKS] = get_string('numweeks','moodle',4);// show dailies up to (including) here. } if ($lastweekend - (60*60*24*56) >= $earliestweek) { $timeoptions[STATS_TIME_LAST2MONTHS] = get_string('nummonths','moodle',2); } if ($lastweekend - (60*60*24*84) >= $earliestweek) { $timeoptions[STATS_TIME_LAST3MONTHS] = get_string('nummonths','moodle',3); } if ($lastweekend - (60*60*24*112) >= $earliestweek) { $timeoptions[STATS_TIME_LAST4MONTHS] = get_string('nummonths','moodle',4); } if ($lastweekend - (60*60*24*140) >= $earliestweek) { $timeoptions[STATS_TIME_LAST5MONTHS] = get_string('nummonths','moodle',5); } if ($lastweekend - (60*60*24*168) >= $earliestweek) { $timeoptions[STATS_TIME_LAST6MONTHS] = get_string('nummonths','moodle',6); // show weeklies up to (including) here } if (strtotime('-7 months',$lastmonthend) >= $earliestmonth) { $timeoptions[STATS_TIME_LAST7MONTHS] = get_string('nummonths','moodle',7); } if (strtotime('-8 months',$lastmonthend) >= $earliestmonth) { $timeoptions[STATS_TIME_LAST8MONTHS] = get_string('nummonths','moodle',8); } if (strtotime('-9 months',$lastmonthend) >= $earliestmonth) { $timeoptions[STATS_TIME_LAST9MONTHS] = get_string('nummonths','moodle',9); } if (strtotime('-10 months',$lastmonthend) >= $earliestmonth) { $timeoptions[STATS_TIME_LAST10MONTHS] = get_string('nummonths','moodle',10); } if (strtotime('-11 months',$lastmonthend) >= $earliestmonth) { $timeoptions[STATS_TIME_LAST11MONTHS] = get_string('nummonths','moodle',11); } if (strtotime('-1 year',$lastmonthend) >= $earliestmonth) { $timeoptions[STATS_TIME_LASTYEAR] = get_string('lastyear'); } $years = (int)date('y', $now) - (int)date('y', $earliestmonth); if ($years > 1) { for($i = 2; $i <= $years; $i++) { $timeoptions[$i*12+20] = get_string('numyears', 'moodle', $i); } } return $timeoptions; } function stats_get_report_options($courseid,$mode) { global $CFG, $DB; $reportoptions = array(); switch ($mode) { case STATS_MODE_GENERAL: $reportoptions[STATS_REPORT_ACTIVITY] = get_string('statsreport'.STATS_REPORT_ACTIVITY); if ($courseid != SITEID && $context = context_course::instance($courseid)) { $sql = 'SELECT r.id, r.name, r.shortname FROM {role} r JOIN {stats_daily} s ON s.roleid = r.id WHERE s.courseid = :courseid GROUP BY r.id, r.name, r.shortname'; if ($roles = $DB->get_records_sql($sql, array('courseid' => $courseid))) { $roles = array_intersect_key($roles, get_viewable_roles($context)); foreach ($roles as $role) { $reportoptions[STATS_REPORT_ACTIVITYBYROLE.$role->id] = get_string('statsreport'.STATS_REPORT_ACTIVITYBYROLE). ' ' . role_get_name($role, $context); } } } $reportoptions[STATS_REPORT_READS] = get_string('statsreport'.STATS_REPORT_READS); $reportoptions[STATS_REPORT_WRITES] = get_string('statsreport'.STATS_REPORT_WRITES); if ($courseid == SITEID) { $reportoptions[STATS_REPORT_LOGINS] = get_string('statsreport'.STATS_REPORT_LOGINS); } break; case STATS_MODE_DETAILED: $reportoptions[STATS_REPORT_USER_ACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ACTIVITY); $reportoptions[STATS_REPORT_USER_ALLACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ALLACTIVITY); if (has_capability('report/stats:view', context_system::instance())) { $site = get_site(); $reportoptions[STATS_REPORT_USER_LOGINS] = get_string('statsreport'.STATS_REPORT_USER_LOGINS); } break; case STATS_MODE_RANKED: if (has_capability('report/stats:view', context_system::instance())) { $reportoptions[STATS_REPORT_ACTIVE_COURSES] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES); $reportoptions[STATS_REPORT_ACTIVE_COURSES_WEIGHTED] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES_WEIGHTED); $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES); $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES_RW] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES_RW); } break; } return $reportoptions; } /** * Fix missing entries in the statistics. * * This creates a dummy stat when nothing happened during a day/week/month. * * @param array $stats array of statistics. * @param int $timeafter unused. * @param string $timestr type of statistics to generate (dayly, weekly, monthly). * @param boolean $line2 * @param boolean $line3 * @return array of fixed statistics. */ function stats_fix_zeros($stats,$timeafter,$timestr,$line2=true,$line3=false) { if (empty($stats)) { return; } $timestr = str_replace('user_','',$timestr); // just in case. // Gets the current user base time. $fun = 'stats_get_base_'.$timestr; $now = $fun(); // Extract the ending time of the statistics. $actualtimes = array(); $actualtimeshour = null; foreach ($stats as $statid => $s) { // Normalise the month date to the 1st if for any reason it's set to later. But we ignore // anything above or equal to 29 because sometimes we get the end of the month. Also, we will // set the hours of the result to all of them, that way we prevent DST differences. if ($timestr == 'monthly') { $day = date('d', $s->timeend); if (date('d', $s->timeend) > 1 && date('d', $s->timeend) < 29) { $day = 1; } if (is_null($actualtimeshour)) { $actualtimeshour = date('H', $s->timeend); } $s->timeend = mktime($actualtimeshour, 0, 0, date('m', $s->timeend), $day, date('Y', $s->timeend)); } $stats[$statid] = $s; $actualtimes[] = $s->timeend; } $actualtimesvalues = array_values($actualtimes); $timeafter = array_pop($actualtimesvalues); // Generate a base timestamp for each possible month/week/day. $times = array(); while ($timeafter < $now) { $times[] = $timeafter; if ($timestr == 'daily') { $timeafter = stats_get_next_day_start($timeafter); } else if ($timestr == 'weekly') { $timeafter = stats_get_next_week_start($timeafter); } else if ($timestr == 'monthly') { // We can't just simply +1 month because the 31st Jan + 1 month = 2nd of March. $year = date('Y', $timeafter); $month = date('m', $timeafter); $day = date('d', $timeafter); $dayofnextmonth = $day; if ($day >= 29) { $daysinmonth = date('n', mktime(0, 0, 0, $month+1, 1, $year)); if ($day > $daysinmonth) { $dayofnextmonth = $daysinmonth; } } $timeafter = mktime($actualtimeshour, 0, 0, $month+1, $dayofnextmonth, $year); } else { // This will put us in a never ending loop. return $stats; } } // Add the base timestamp to the statistics if not present. foreach ($times as $count => $time) { if (!in_array($time,$actualtimes) && $count != count($times) -1) { $newobj = new StdClass; $newobj->timeend = $time; $newobj->id = 0; $newobj->roleid = 0; $newobj->line1 = 0; if (!empty($line2)) { $newobj->line2 = 0; } if (!empty($line3)) { $newobj->line3 = 0; } $newobj->zerofixed = true; $stats[] = $newobj; } } usort($stats,"stats_compare_times"); return $stats; } // helper function to sort arrays by $obj->timeend function stats_compare_times($a,$b) { if ($a->timeend == $b->timeend) { return 0; } return ($a->timeend > $b->timeend) ? -1 : 1; } function stats_check_uptodate($courseid=0) { global $CFG, $DB; if (empty($courseid)) { $courseid = SITEID; } $latestday = stats_get_start_from('daily'); if ((time() - 60*60*24*2) < $latestday) { // we're ok return NULL; } $a = new stdClass(); $a->daysdone = $DB->get_field_sql("SELECT COUNT(DISTINCT(timeend)) FROM {stats_daily}"); // how many days between the last day and now? $a->dayspending = ceil((stats_get_base_daily() - $latestday)/(60*60*24)); if ($a->dayspending == 0 && $a->daysdone != 0) { return NULL; // we've only just started... } //return error as string return get_string('statscatchupmode','error',$a); } /** * Create temporary tables to speed up log generation */ function stats_temp_table_create() { global $CFG, $DB; $dbman = $DB->get_manager(); // We are going to use database_manager services stats_temp_table_drop(); $tables = array(); /// Define tables user to be created $table = new xmldb_table('temp_stats_daily'); $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); $table->add_field('courseid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0'); $table->add_field('timeend', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0'); $table->add_field('roleid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0'); $table->add_field('stattype', XMLDB_TYPE_CHAR, 20, null, XMLDB_NOTNULL, null, 'activity'); $table->add_field('stat1', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0'); $table->add_field('stat2', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0'); $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); $table->add_index('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid')); $table->add_index('timeend', XMLDB_INDEX_NOTUNIQUE, array('timeend')); $table->add_index('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid')); $tables['temp_stats_daily'] = $table; $table = new xmldb_table('temp_stats_user_daily'); $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); $table->add_field('courseid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0'); $table->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0'); $table->add_field('roleid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0'); $table->add_field('timeend', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0'); $table->add_field('statsreads', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0'); $table->add_field('statswrites', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0'); $table->add_field('stattype', XMLDB_TYPE_CHAR, 30, null, XMLDB_NOTNULL, null, null); $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); $table->add_index('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid')); $table->add_index('userid', XMLDB_INDEX_NOTUNIQUE, array('userid')); $table->add_index('timeend', XMLDB_INDEX_NOTUNIQUE, array('timeend')); $table->add_index('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid')); $tables['temp_stats_user_daily'] = $table; $table = new xmldb_table('temp_enroled'); $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); $table->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0'); $table->add_field('courseid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0'); $table->add_field('roleid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, null); $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); $table->add_index('userid', XMLDB_INDEX_NOTUNIQUE, array('userid')); $table->add_index('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid')); $table->add_index('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid')); $table->add_index('useridroleidcourseid', XMLDB_INDEX_NOTUNIQUE, array('userid', 'roleid', 'courseid')); $tables['temp_enroled'] = $table; $table = new xmldb_table('temp_log1'); $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); $table->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0'); $table->add_field('course', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0'); $table->add_field('action', XMLDB_TYPE_CHAR, 40, null, XMLDB_NOTNULL, null, null); $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); $table->add_index('action', XMLDB_INDEX_NOTUNIQUE, array('action')); $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course')); $table->add_index('user', XMLDB_INDEX_NOTUNIQUE, array('userid')); $table->add_index('usercourseaction', XMLDB_INDEX_NOTUNIQUE, array('userid','course','action')); $tables['temp_log1'] = $table; /// temp_log2 is exactly the same as temp_log1. $tables['temp_log2'] = clone $tables['temp_log1']; $tables['temp_log2']->setName('temp_log2'); try { foreach ($tables as $table) { $dbman->create_temp_table($table); } } catch (Exception $e) { mtrace('Temporary table creation failed: '. $e->getMessage()); return false; } return true; } /** * Deletes summary logs table for stats calculation */ function stats_temp_table_drop() { global $DB; $dbman = $DB->get_manager(); $tables = array('temp_log1', 'temp_log2', 'temp_stats_daily', 'temp_stats_user_daily', 'temp_enroled'); foreach ($tables as $name) { if ($dbman->table_exists($name)) { $table = new xmldb_table($name); try { $dbman->drop_table($table); } catch (Exception $e) { mtrace("Error occured while dropping temporary tables!"); } } } } /** * Fills the temporary stats tables with new data * * This function is meant to be called once at the start of stats generation * * @param int timestart timestamp of the start time of logs view * @param int timeend timestamp of the end time of logs view * @return bool success (true) or failure(false) */ function stats_temp_table_setup() { global $DB; $sql = "INSERT INTO {temp_enroled} (userid, courseid, roleid) SELECT ue.userid, e.courseid, ra.roleid FROM {role_assignments} ra JOIN {context} c ON (c.id = ra.contextid AND c.contextlevel = :courselevel) JOIN {enrol} e ON e.courseid = c.instanceid JOIN {user_enrolments} ue ON (ue.enrolid = e.id AND ue.userid = ra.userid)"; return stats_run_query($sql, array('courselevel' => CONTEXT_COURSE)); } /** * Fills the temporary stats tables with new data * * This function is meant to be called to get a new day of data * * @param int timestamp of the start time of logs view * @param int timestamp of the end time of logs view * @return bool success (true) or failure(false) */ function stats_temp_table_fill($timestart, $timeend) { global $DB; // First decide from where we want the data. $params = array('timestart' => $timestart, 'timeend' => $timeend, 'participating' => \core\event\base::LEVEL_PARTICIPATING, 'teaching' => \core\event\base::LEVEL_TEACHING, 'loginevent1' => '\core\event\user_loggedin', 'loginevent2' => '\core\event\user_loggedin', ); $filled = false; $manager = get_log_manager(); $stores = $manager->get_readers(); foreach ($stores as $store) { if ($store instanceof \core\log\sql_internal_table_reader) { $logtable = $store->get_internal_log_table_name(); if (!$logtable) { continue; } $sql = "SELECT COUNT('x') FROM {{$logtable}} WHERE timecreated >= :timestart AND timecreated < :timeend"; if (!$DB->get_field_sql($sql, $params)) { continue; } // Let's fake the old records using new log data. // We want only data relevant to educational process // done by real users. $sql = "INSERT INTO {temp_log1} (userid, course, action) SELECT userid, CASE WHEN courseid IS NULL THEN ".SITEID." WHEN courseid = 0 THEN ".SITEID." ELSE courseid END, CASE WHEN eventname = :loginevent1 THEN 'login' WHEN crud = 'r' THEN 'view' ELSE 'update' END FROM {{$logtable}} WHERE timecreated >= :timestart AND timecreated < :timeend AND (origin = 'web' OR origin = 'ws') AND (edulevel = :participating OR edulevel = :teaching OR eventname = :loginevent2)"; $DB->execute($sql, $params); $filled = true; } } if (!$filled) { // Fallback to legacy data. $sql = "INSERT INTO {temp_log1} (userid, course, action) SELECT userid, course, action FROM {log} WHERE time >= :timestart AND time < :timeend"; $DB->execute($sql, $params); } $sql = 'INSERT INTO {temp_log2} (userid, course, action) SELECT userid, course, action FROM {temp_log1}'; $DB->execute($sql); // We have just loaded all the temp tables, collect statistics for that. $DB->update_temp_table_stats(); return true; } /** * Deletes summary logs table for stats calculation * * @return bool success (true) or failure(false) */ function stats_temp_table_clean() { global $DB; $sql = array(); $sql['up1'] = 'INSERT INTO {stats_daily} (courseid, roleid, stattype, timeend, stat1, stat2) SELECT courseid, roleid, stattype, timeend, stat1, stat2 FROM {temp_stats_daily}'; $sql['up2'] = 'INSERT INTO {stats_user_daily} (courseid, userid, roleid, timeend, statsreads, statswrites, stattype) SELECT courseid, userid, roleid, timeend, statsreads, statswrites, stattype FROM {temp_stats_user_daily}'; foreach ($sql as $id => $query) { if (! stats_run_query($query)) { mtrace("Error during table cleanup!"); return false; } } $tables = array('temp_log1', 'temp_log2', 'temp_stats_daily', 'temp_stats_user_daily'); foreach ($tables as $name) { $DB->delete_records($name); } return true; }