/**
* 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;
}
}
}
/**
* 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();
}