示例#1
0
/**
 * 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;
    $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);
    }
    // calculate scheduled time
    $scheduledtime = stats_get_base_daily() + $CFG->statsruntimestarthour * 60 * 60 + $CFG->statsruntimestartminute * 60;
    // Note: This will work fine for sites running cron each 4 hours or less (hopefully, 99.99% of sites). MDL-16709
    // check to make sure we're due to run, at least 20 hours after last run
    if (isset($CFG->statslastexecution) && time() - 20 * 60 * 60 < $CFG->statslastexecution) {
        mtrace("...preventing stats to run, last execution was less than 20 hours ago.");
        return false;
        // also check that we are a max of 4 hours after scheduled time, stats won't run after that
    } else {
        if (time() > $scheduledtime + 4 * 60 * 60) {
            mtrace("...preventing stats to run, more than 4 hours since scheduled time.");
            return false;
        } else {
            set_config('statslastexecution', time());
            /// Grab this execution as last one
        }
    }
    $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}:");
    $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++;
        @set_time_limit($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}\n                            (stattype, timeend, courseid, userid, statsreads)\n\n                SELECT 'logins', {$nextmidnight} AS timeend, " . SITEID . " AS courseid,\n                        userid, COUNT(id) AS statsreads\n                  FROM {temp_log1} l\n                 WHERE action = 'login'\n              GROUP BY userid\n                HAVING COUNT(id) > 0";
        if ($logspresent && !stats_run_query($sql)) {
            $failed = true;
            break;
        }
        stats_progress('1');
        $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)\n\n                SELECT 'logins' AS stattype, {$nextmidnight} AS timeend, " . SITEID . " AS courseid, 0,\n                       COALESCE(SUM(statsreads), 0) as stat1, COUNT('x') as stat2\n                  FROM {temp_stats_user_daily}\n                 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)\n\n                SELECT 'enrolments' as stattype, {$nextmidnight} as timeend, courseid, roleid,\n                        COUNT(DISTINCT userid) as stat1, 0 as stat2\n                  FROM {temp_enroled}\n              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}\n                   SET stat2 = (\n\n                    SELECT COUNT(DISTINCT userid)\n                      FROM {temp_enroled} te\n                     WHERE roleid = {temp_stats_daily}.roleid\n                       AND courseid = {temp_stats_daily}.courseid\n                       AND EXISTS (\n\n                        SELECT 'x'\n                          FROM {temp_log1} l\n                         WHERE l.course = {temp_stats_daily}.courseid\n                           AND l.userid = te.userid\n                                  )\n                               )\n                 WHERE {temp_stats_daily}.stattype = 'enrolments'\n                   AND {temp_stats_daily}.timeend = {$nextmidnight}\n                   AND {temp_stats_daily}.courseid IN (\n\n                    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)\n\n                SELECT 'enrolments', {$nextmidnight} AS timeend, te.courseid AS courseid, 0 AS roleid,\n                       COUNT(DISTINCT userid) AS stat1, 0 AS stat2\n                  FROM {temp_enroled} te\n              GROUP BY courseid\n                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}\n                   SET stat2 = (\n\n                    SELECT COUNT(DISTINCT te.userid)\n                      FROM {temp_enroled} te\n                     WHERE te.courseid = {temp_stats_daily}.courseid\n                       AND EXISTS (\n\n                        SELECT 'x'\n                          FROM {temp_log1} l\n                         WHERE l.course = {temp_stats_daily}.courseid\n                           AND l.userid = te.userid\n                                  )\n                               )\n\n                 WHERE {temp_stats_daily}.stattype = 'enrolments'\n                   AND {temp_stats_daily}.timeend = {$nextmidnight}\n                   AND {temp_stats_daily}.roleid = 0\n                   AND {temp_stats_daily}.courseid IN (\n\n                    SELECT l.course\n                      FROM {temp_log2} l\n                     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)\n\n                SELECT 'enrolments', {$nextmidnight}, " . SITEID . ", 0, {$totalactiveusers} AS stat1,\n                       {$dailyactiveusers} AS stat2" . $DB->sql_null_from_clause();
        if ($logspresent && !stats_run_query($sql)) {
            $failed = true;
            break;
        }
        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\n                      FROM {temp_stats_daily}\n                     WHERE stattype = 'enrolments'\n                       AND courseid = " . SITEID . "\n                       AND roleid = {$defaultfproleid}\n                       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)\n\n                    SELECT 'enrolments', {$nextmidnight}, " . SITEID . ", {$defaultfproleid},\n                           {$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)\n\n                SELECT 'activity' AS stattype, {$nextmidnight} AS timeend, course AS courseid, userid,\n                       SUM(CASE WHEN action {$viewactionssql} THEN 1 ELSE 0 END) AS statsreads,\n                       SUM(CASE WHEN action {$postactionssql} THEN 1 ELSE 0 END) AS statswrites\n                  FROM {temp_log1} l\n              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)\n\n                SELECT 'activity' AS stattype, {$nextmidnight} AS timeend, c.id AS courseid, 0,\n                       SUM(CASE WHEN l.action {$viewactionssql} THEN 1 ELSE 0 END) AS stat1,\n                       SUM(CASE WHEN l.action {$postactionssql} THEN 1 ELSE 0 END) AS stat2\n                  FROM {course} c, {temp_log1} l\n                 WHERE l.course = c.id\n              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)\n\n                SELECT 'activity', {$nextmidnight} AS timeend, courseid, roleid, SUM(statsreads), SUM(statswrites)\n                  FROM (\n\n                    SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites\n                      FROM {temp_stats_user_daily} sud, (\n\n                        SELECT DISTINCT te.userid, te.roleid, te.courseid\n                          FROM {temp_enroled} te\n                         WHERE te.roleid <> {$guestrole}\n                           AND te.userid <> {$guest}\n                                                        ) pl\n\n                     WHERE sud.userid = pl.userid\n                       AND sud.courseid = pl.courseid\n                       AND sud.timeend = {$nextmidnight}\n                       AND sud.stattype='activity'\n                       ) inline_view\n\n              GROUP BY courseid, roleid\n                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)\n\n                SELECT 'activity', {$nextmidnight} AS timeend, courseid, {$guestrole} AS roleid,\n                       SUM(statsreads), SUM(statswrites)\n                  FROM (\n\n                    SELECT sud.courseid, sud.statsreads, sud.statswrites\n                      FROM {temp_stats_user_daily} sud\n                     WHERE sud.timeend = {$nextmidnight}\n                       AND sud.courseid <> " . SITEID . "\n                       AND sud.stattype='activity'\n                       AND (sud.userid = {$guest} OR sud.userid NOT IN (\n\n                        SELECT userid\n                          FROM {temp_enroled} te\n                         WHERE te.courseid = sud.courseid\n                                                                     ))\n                       ) inline_view\n\n              GROUP BY courseid\n                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)\n\n                SELECT 'activity', {$nextmidnight} AS timeend, courseid, roleid,\n                       SUM(statsreads), SUM(statswrites)\n                  FROM (\n                    SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites\n                      FROM {temp_stats_user_daily} sud, (\n\n                        SELECT DISTINCT ra.userid, ra.roleid, c.instanceid AS courseid\n                          FROM {role_assignments} ra\n                          JOIN {context} c ON c.id = ra.contextid\n                         WHERE ra.contextid = :fpcontext\n                           AND ra.roleid <> {$defaultfproleid}\n                           AND ra.roleid <> {$guestrole}\n                           AND ra.userid <> {$guest}\n                                                   ) pl\n                     WHERE sud.userid = pl.userid\n                       AND sud.courseid = pl.courseid\n                       AND sud.timeend = {$nextmidnight}\n                       AND sud.stattype='activity'\n                       ) inline_view\n\n              GROUP BY courseid, roleid\n                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)\n\n                SELECT 'activity', timeend, courseid, {$defaultfproleid} AS roleid,\n                       SUM(statsreads), SUM(statswrites)\n                  FROM (\n                    SELECT sud.timeend AS timeend, sud.courseid, sud.statsreads, sud.statswrites\n                      FROM {temp_stats_user_daily} sud\n                     WHERE sud.timeend = :nextm\n                       AND sud.courseid = :siteid\n                       AND sud.stattype='activity'\n                       AND sud.userid <> {$guest}\n                       AND sud.userid <> 0\n                       AND sud.userid NOT IN (\n\n                        SELECT ra.userid\n                          FROM {role_assignments} ra\n                         WHERE ra.roleid <> {$guestrole}\n                           AND ra.roleid <> {$defaultfproleid}\n                           AND ra.contextid = :fpcontext)\n                       ) inline_view\n\n              GROUP BY timeend, courseid\n                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;
        }
        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)\n\n                SELECT stattype, timeend, courseid, {$guestrole} AS roleid,\n                       SUM(statsreads) AS stat1, SUM(statswrites) AS stat2\n                  FROM (\n                    SELECT sud.stattype, sud.timeend, sud.courseid,\n                           sud.statsreads, sud.statswrites\n                      FROM {temp_stats_user_daily} sud\n                     WHERE (sud.userid = {$guest} OR sud.userid = 0)\n                       AND sud.timeend = {$nextmidnight}\n                       AND sud.courseid = " . SITEID . "\n                       AND sud.stattype='activity'\n                       ) inline_view\n                 GROUP BY stattype, timeend, courseid\n                 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;
        }
    }
}
示例#2
0
 /**
  * Test the function that clean out the temporary tables.
  *
  * @depends test_statslib_temp_table_create_and_drop
  */
 public function test_statslib_temp_table_clean()
 {
     global $DB;
     $rows = array('temp_log1' => array('id' => 1, 'course' => 1), 'temp_log2' => array('id' => 1, 'course' => 1), 'temp_stats_daily' => array('id' => 1, 'courseid' => 1), 'temp_stats_user_daily' => array('id' => 1, 'courseid' => 1));
     stats_temp_table_create();
     foreach ($rows as $table => $row) {
         $DB->insert_record_raw($table, $row);
         $this->assertEquals(1, $DB->count_records($table));
     }
     stats_temp_table_clean();
     foreach ($rows as $table => $row) {
         $this->assertEquals(0, $DB->count_records($table));
     }
     $this->assertEquals(1, $DB->count_records('stats_daily'));
     $this->assertEquals(1, $DB->count_records('stats_user_daily'));
     stats_temp_table_drop();
 }