/**
* 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;
}
}
}
/**
* 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 = get_context_instance(CONTEXT_COURSE, 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) and ((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;
$failed = false; // failed stats flag
while ($now > $nextmidnight) {
if ($days >= $maxdays) {
mtrace("...stopping early, reached maximum number of $maxdays days - will continue next time.");
set_cron_lock('statsrunning', null);
return false;
}
$days++;
@set_time_limit($timeout - 200);
if ($days > 1) {
// move the lock
set_cron_lock('statsrunning', time() + $timeout, true);
}
$daystart = time();
$timesql = "l.time >= $timestart AND l.time < $nextmidnight";
$timesql1 = "l1.time >= $timestart AND l1.time < $nextmidnight";
$timesql2 = "l2.time >= $timestart AND l2.time < $nextmidnight";
stats_daily_progress('init');
/// find out if any logs available for this day
$sql = "SELECT 'x'
FROM {log} l
WHERE $timesql";
$logspresent = $DB->get_records_sql($sql, null, 0, 1);
/// process login info first
$sql = "INSERT INTO {stats_user_daily} (stattype, timeend, courseid, userid, statsreads)
SELECT 'logins', timeend, courseid, userid, count(statsreads)
FROM (
SELECT $nextmidnight AS timeend, ".SITEID." AS courseid, l.userid, l.id AS statsreads
FROM {log} l
WHERE action = 'login' AND $timesql
) inline_view
GROUP BY timeend, courseid, userid
HAVING count(statsreads) > 0";
if ($logspresent and !$DB->execute($sql)) {
$failed = true;
break;
}
stats_daily_progress('1');
$sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
SELECT 'logins' AS stattype, $nextmidnight AS timeend, ".SITEID." as courseid, 0,
COALESCE((SELECT SUM(statsreads)
FROM {stats_user_daily} s1
WHERE s1.stattype = 'logins' AND timeend = $nextmidnight), 0) AS stat1,
(SELECT COUNT('x')
FROM {stats_user_daily} s2
WHERE s2.stattype = 'logins' AND timeend = $nextmidnight) AS stat2" .
$DB->sql_null_from_clause();
if ($logspresent and !$DB->execute($sql)) {
$failed = true;
break;
}
stats_daily_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 {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
SELECT 'enrolments', timeend, courseid, roleid, COUNT(DISTINCT userid), 0
FROM (
SELECT $nextmidnight AS timeend, e.courseid, ra.roleid, ue.userid
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)
) inline_view
GROUP BY timeend, courseid, roleid";
if (!$DB->execute($sql, array('courselevel'=>CONTEXT_COURSE))) {
$failed = true;
break;
}
stats_daily_progress('3');
// using table alias in UPDATE does not work in pg < 8.2
$sql = "UPDATE {stats_daily}
SET stat2 = (SELECT COUNT(DISTINCT ra.userid)
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)
WHERE ra.roleid = {stats_daily}.roleid AND
e.courseid = {stats_daily}.courseid AND
EXISTS (SELECT 'x'
FROM {log} l
WHERE l.course = {stats_daily}.courseid AND
l.userid = ra.userid AND $timesql))
WHERE {stats_daily}.stattype = 'enrolments' AND
{stats_daily}.timeend = $nextmidnight AND
{stats_daily}.courseid IN
(SELECT DISTINCT l.course
FROM {log} l
WHERE $timesql)";
if (!$DB->execute($sql, array('courselevel'=>CONTEXT_COURSE))) {
$failed = true;
break;
}
stats_daily_progress('4');
/// now get course total enrolments (roleid==0) - except frontpage
$sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
SELECT 'enrolments', timeend, id, nroleid, COUNT(DISTINCT userid), 0
FROM (
SELECT $nextmidnight AS timeend, e.courseid AS id, 0 AS nroleid, ue.userid
FROM {enrol} e
JOIN {user_enrolments} ue ON ue.enrolid = e.id
) inline_view
GROUP BY timeend, id, nroleid
HAVING COUNT(DISTINCT userid) > 0";
if ($logspresent and !$DB->execute($sql)) {
$failed = true;
break;
}
stats_daily_progress('5');
$sql = "UPDATE {stats_daily}
SET stat2 = (SELECT COUNT(DISTINCT ue.userid)
FROM {enrol} e
JOIN {user_enrolments} ue ON ue.enrolid = e.id
WHERE e.courseid = {stats_daily}.courseid AND
EXISTS (SELECT 'x'
FROM {log} l
WHERE l.course = {stats_daily}.courseid AND
l.userid = ue.userid AND $timesql))
WHERE {stats_daily}.stattype = 'enrolments' AND
{stats_daily}.timeend = $nextmidnight AND
{stats_daily}.roleid = 0 AND
{stats_daily}.courseid IN
(SELECT l.course
FROM {log} l
WHERE $timesql AND l.course <> ".SITEID.")";
if ($logspresent and !$DB->execute($sql, array())) {
$failed = true;
break;
}
stats_daily_progress('6');
/// frontapge(==site) enrolments total
$sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
SELECT 'enrolments', $nextmidnight, ".SITEID.", 0,
(SELECT COUNT('x')
FROM {user} u
WHERE u.deleted = 0) AS stat1,
(SELECT COUNT(DISTINCT u.id)
FROM {user} u
JOIN {log} l ON l.userid = u.id
WHERE u.deleted = 0 AND $timesql) AS stat2" .
$DB->sql_null_from_clause();
if ($logspresent and !$DB->execute($sql)) {
$failed = true;
break;
}
stats_daily_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 {stats_daily}
WHERE stattype = 'enrolments' AND courseid = ".SITEID." AND
roleid = $defaultfproleid AND timeend = $nextmidnight";
if ($logspresent and !$DB->execute($sql)) {
$failed = true;
break;
}
stats_daily_progress('8');
$sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
SELECT 'enrolments', $nextmidnight, ".SITEID.", $defaultfproleid,
(SELECT COUNT('x')
FROM {user} u
WHERE u.deleted = 0) AS stat1,
(SELECT COUNT(DISTINCT u.id)
FROM {user} u
JOIN {log} l ON l.userid = u.id
WHERE u.deleted = 0 AND $timesql) AS stat2" .
$DB->sql_null_from_clause();;
if ($logspresent and !$DB->execute($sql)) {
$failed = true;
break;
}
stats_daily_progress('9');
} else {
stats_daily_progress('x');
stats_daily_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 {stats_user_daily} (stattype, timeend, courseid, userid, statsreads, statswrites)
SELECT 'activity' AS stattype, $nextmidnight AS timeend, d.courseid, d.userid,
(SELECT COUNT('x')
FROM {log} l
WHERE l.userid = d.userid AND
l.course = d.courseid AND $timesql AND
l.action $viewactionssql) AS statsreads,
(SELECT COUNT('x')
FROM {log} l
WHERE l.userid = d.userid AND
l.course = d.courseid AND $timesql AND
l.action $postactionssql) AS statswrites
FROM (SELECT DISTINCT u.id AS userid, l.course AS courseid
FROM {user} u, {log} l
WHERE u.id = l.userid AND $timesql
UNION
SELECT 0 AS userid, ".SITEID." AS courseid" . $DB->sql_null_from_clause() . ") d";
// can not use group by here because pg can not handle it :-(
if ($logspresent and !$DB->execute($sql, array_merge($params1, $params2))) {
$failed = true;
break;
}
stats_daily_progress('10');
/// how many view/post actions in each course total
$sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
SELECT 'activity' AS stattype, $nextmidnight AS timeend, c.id AS courseid, 0,
(SELECT COUNT('x')
FROM {log} l1
WHERE l1.course = c.id AND l1.action $viewactionssql AND
$timesql1) AS stat1,
(SELECT COUNT('x')
FROM {log} l2
WHERE l2.course = c.id AND l2.action $postactionssql AND
$timesql2) AS stat2
FROM {course} c
WHERE EXISTS (SELECT 'x'
FROM {log} l
WHERE l.course = c.id and $timesql)";
if ($logspresent and !$DB->execute($sql, array_merge($params1, $params2))) {
$failed = true;
break;
}
stats_daily_progress('11');
/// how many view actions for each course+role - excluding guests and frontpage
$sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
SELECT 'activity', timeend, courseid, roleid, SUM(statsreads), SUM(statswrites)
FROM (
SELECT $nextmidnight AS timeend, pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
FROM {stats_user_daily} sud,
(SELECT DISTINCT ra.userid, ra.roleid, e.courseid
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)
WHERE 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 timeend, courseid, roleid
HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
if ($logspresent and !$DB->execute($sql, array('courselevel'=>CONTEXT_COURSE))) {
$failed = true;
break;
}
stats_daily_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 {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
SELECT 'activity', timeend, courseid, nroleid, SUM(statsreads), SUM(statswrites)
FROM (
SELECT $nextmidnight AS timeend, sud.courseid, $guestrole AS nroleid, sud.statsreads, sud.statswrites
FROM {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 ue.userid
FROM {user_enrolments} ue
JOIN {enrol} e ON ue.enrolid = e.id
WHERE e.courseid = sud.courseid))
) inline_view
GROUP BY timeend, courseid, nroleid
HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
if ($logspresent and !$DB->execute($sql, array())) {
$failed = true;
break;
}
stats_daily_progress('13');
/// how many view actions for each role on frontpage - excluding guests, not-logged-in and default frontpage role
$sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
SELECT 'activity', timeend, courseid, roleid, SUM(statsreads), SUM(statswrites)
FROM (
SELECT $nextmidnight AS timeend, pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
FROM {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 timeend, courseid, roleid
HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
if ($logspresent and !$DB->execute($sql, array('fpcontext'=>$fpcontext->id))) {
$failed = true;
break;
}
stats_daily_progress('14');
/// how many view actions for default frontpage role on frontpage only
$sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
SELECT 'activity', timeend, courseid, nroleid, SUM(statsreads), SUM(statswrites)
FROM (
SELECT sud.timeend AS timeend, sud.courseid, $defaultfproleid AS nroleid, sud.statsreads, sud.statswrites
FROM {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, nroleid
HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
if ($logspresent and !$DB->execute($sql, array('fpcontext'=>$fpcontext->id, 'siteid'=>SITEID, 'nextm'=>$nextmidnight))) {
$failed = true;
break;
}
stats_daily_progress('15');
/// how many view actions for guests or not-logged-in on frontpage
$sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
SELECT 'activity', timeend, courseid, nroleid, SUM(statsreads), SUM(statswrites)
FROM (
SELECT $nextmidnight AS timeend, ".SITEID." AS courseid, $guestrole AS nroleid, pl.statsreads, pl.statswrites
FROM (
SELECT sud.statsreads, sud.statswrites
FROM {stats_user_daily} sud
WHERE (sud.userid = $guest OR sud.userid = 0) AND
sud.timeend = $nextmidnight AND sud.courseid = ".SITEID." AND
sud.stattype='activity'
) pl
) inline_view
GROUP BY timeend, courseid, nroleid
HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
if ($logspresent and !$DB->execute($sql)) {
$failed = true;
break;
}
stats_daily_progress('16');
// remember processed days
set_config('statslastdaily', $nextmidnight);
mtrace(" finished until $nextmidnight: ".userdate($nextmidnight)." (in ".(time()-$daystart)." s)");
$timestart = $nextmidnight;
$nextmidnight = stats_get_next_day_start($nextmidnight);
}
set_cron_lock('statsrunning', null);
if ($failed) {
$days--;
mtrace("...error occurred, completed $days days of statistics.");
return false;
} else {
mtrace("...completed $days days of statistics.");
return true;
}
}
/**
* Test the function that gets the action names.
*
* Note: The function results depend on installed modules. The hard coded lists are the
* defaults for a new Moodle 2.3 install.
*/
public function test_statslib_get_action_names()
{
$basepostactions = array(0 => 'add', 1 => 'delete', 2 => 'edit', 3 => 'add mod', 4 => 'delete mod', 5 => 'edit sectionenrol', 6 => 'loginas', 7 => 'new', 8 => 'unenrol', 9 => 'update', 10 => 'update mod', 11 => 'upload', 12 => 'submit', 13 => 'submit for grading', 14 => 'talk', 15 => 'choose', 16 => 'choose again', 17 => 'record delete', 18 => 'add discussion', 19 => 'add post', 20 => 'delete discussion', 21 => 'delete post', 22 => 'move discussion', 23 => 'prune post', 24 => 'update post', 25 => 'add category', 26 => 'add entry', 27 => 'approve entry', 28 => 'delete category', 29 => 'delete entry', 30 => 'edit category', 31 => 'update entry', 32 => 'end', 33 => 'start', 34 => 'attempt', 35 => 'close attempt', 36 => 'preview', 37 => 'editquestions', 38 => 'delete attempt', 39 => 'manualgrade');
$baseviewactions = array(0 => 'view', 1 => 'view all', 2 => 'history', 3 => 'view submission', 4 => 'view feedback', 5 => 'print', 6 => 'report', 7 => 'view discussion', 8 => 'search', 9 => 'forum', 10 => 'forums', 11 => 'subscribers', 12 => 'view forum', 13 => 'view entry', 14 => 'review', 15 => 'pre-view', 16 => 'download', 17 => 'view form', 18 => 'view graph', 19 => 'view report');
$postactions = stats_get_action_names('post');
foreach ($basepostactions as $action) {
$this->assertContains($action, $postactions);
}
$viewactions = stats_get_action_names('view');
foreach ($baseviewactions as $action) {
$this->assertContains($action, $viewactions);
}
}
/**
* 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;
$now = time();
// 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;
}
// fisrt delete entries that should not be there yet
delete_records_select('stats_daily', "timeend > {$timestart}");
delete_records_select('stats_user_daily', "timeend > {$timestart}");
// Read in a few things we'll use later
$viewactions = implode(',', stats_get_action_names('view'));
$postactions = implode(',', stats_get_action_names('post'));
$guest = get_guest();
$guestrole = get_guest_role();
list($enroljoin, $enrolwhere) = stats_get_enrolled_sql($CFG->statscatdepth, true);
list($enroljoin_na, $enrolwhere_na) = stats_get_enrolled_sql($CFG->statscatdepth, false);
list($fpjoin, $fpwhere) = stats_get_enrolled_sql(0, true);
mtrace("Running daily statistics gathering, starting at {$timestart}:");
$days = 0;
$failed = false;
// failed stats flag
while ($now > $nextmidnight) {
if ($days >= $maxdays) {
mtrace("...stopping early, reached maximum number of {$maxdays} days - will continue next time.");
set_cron_lock('statsrunning', null);
return false;
}
$days++;
@set_time_limit($timeout - 200);
if ($days > 1) {
// move the lock
set_cron_lock('statsrunning', time() + $timeout, true);
}
$daystart = time();
$timesql = "l.time >= {$timestart} AND l.time < {$nextmidnight}";
$timesql1 = "l1.time >= {$timestart} AND l1.time < {$nextmidnight}";
$timesql2 = "l2.time >= {$timestart} AND l2.time < {$nextmidnight}";
stats_daily_progress('init');
/// find out if any logs available for this day
$sql = "SELECT 'x'\n FROM {$CFG->prefix}log l\n WHERE {$timesql}";
$logspresent = get_records_sql($sql, 0, 1);
/// process login info first
$sql = "INSERT INTO {$CFG->prefix}stats_user_daily (stattype, timeend, courseid, userid, statsreads)\n\n SELECT 'logins', timeend, courseid, userid, count(statsreads)\n FROM (\n SELECT {$nextmidnight} AS timeend, " . SITEID . " AS courseid, l.userid, l.id AS statsreads\n FROM {$CFG->prefix}log l\n WHERE action = 'login' AND {$timesql}\n ) inline_view\n GROUP BY timeend, courseid, userid\n HAVING count(statsreads) > 0";
if ($logspresent and !execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_progress('1');
$sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2)\n\n SELECT 'logins' AS stattype, {$nextmidnight} AS timeend, " . SITEID . " as courseid, 0,\n COALESCE((SELECT SUM(statsreads)\n FROM {$CFG->prefix}stats_user_daily s1\n WHERE s1.stattype = 'logins' AND timeend = {$nextmidnight}), 0) AS stat1,\n (SELECT COUNT('x')\n FROM {$CFG->prefix}stats_user_daily s2\n WHERE s2.stattype = 'logins' AND timeend = {$nextmidnight}) AS stat2" . sql_null_from_clause();
if ($logspresent and !execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_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
// - enrolment is defined now as having course:view capability in
// course context or above, we look 3 cats upwards only and ignore prevent
// and prohibit caps to simplify it
// - SITEID is specialcased here, because it's all about default enrolment
// in that case, we'll count non-deleted users.
//
$sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2)\n\n SELECT 'enrolments', timeend, courseid, roleid, COUNT(DISTINCT userid), 0\n FROM (\n SELECT {$nextmidnight} AS timeend, pl.courseid, pl.roleid, pl.userid\n FROM (\n SELECT DISTINCT ra.roleid, ra.userid, c.id as courseid\n FROM {$CFG->prefix}role_assignments ra {$enroljoin_na}\n WHERE {$enrolwhere_na}\n ) pl\n ) inline_view\n GROUP BY timeend, courseid, roleid";
if (!execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_progress('3');
// using table alias in UPDATE does not work in pg < 8.2
$sql = "UPDATE {$CFG->prefix}stats_daily\n SET stat2 = (SELECT COUNT(DISTINCT ra.userid)\n FROM {$CFG->prefix}role_assignments ra {$enroljoin_na}\n WHERE ra.roleid = {$CFG->prefix}stats_daily.roleid AND\n c.id = {$CFG->prefix}stats_daily.courseid AND\n {$enrolwhere_na} AND\n EXISTS (SELECT 'x'\n FROM {$CFG->prefix}log l\n WHERE l.course = {$CFG->prefix}stats_daily.courseid AND\n l.userid = ra.userid AND {$timesql}))\n WHERE {$CFG->prefix}stats_daily.stattype = 'enrolments' AND\n {$CFG->prefix}stats_daily.timeend = {$nextmidnight} AND\n {$CFG->prefix}stats_daily.courseid IN\n (SELECT DISTINCT l.course\n FROM {$CFG->prefix}log l\n WHERE {$timesql})";
if ($logspresent and !execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_progress('4');
/// now get course total enrolments (roleid==0) - except frontpage
$sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2)\n\n SELECT 'enrolments', timeend, id, nroleid, COUNT(DISTINCT userid), 0\n FROM (\n SELECT {$nextmidnight} AS timeend, c.id, 0 AS nroleid, ra.userid\n FROM {$CFG->prefix}role_assignments ra {$enroljoin_na}\n WHERE c.id <> " . SITEID . " AND {$enrolwhere_na}\n ) inline_view\n GROUP BY timeend, id, nroleid\n HAVING COUNT(DISTINCT userid) > 0";
if ($logspresent and !execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_progress('5');
$sql = "UPDATE {$CFG->prefix}stats_daily\n SET stat2 = (SELECT COUNT(DISTINCT ra.userid)\n FROM {$CFG->prefix}role_assignments ra {$enroljoin_na}\n WHERE c.id = {$CFG->prefix}stats_daily.courseid AND\n {$enrolwhere_na} AND\n EXISTS (SELECT 'x'\n FROM {$CFG->prefix}log l\n WHERE l.course = {$CFG->prefix}stats_daily.courseid AND\n l.userid = ra.userid AND {$timesql}))\n WHERE {$CFG->prefix}stats_daily.stattype = 'enrolments' AND\n {$CFG->prefix}stats_daily.timeend = {$nextmidnight} AND\n {$CFG->prefix}stats_daily.roleid = 0 AND\n {$CFG->prefix}stats_daily.courseid IN\n (SELECT l.course\n FROM {$CFG->prefix}log l\n WHERE {$timesql} AND l.course <> " . SITEID . ")";
if ($logspresent and !execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_progress('6');
/// frontapge(==site) enrolments total
$sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2)\n\n SELECT 'enrolments', {$nextmidnight}, " . SITEID . ", 0,\n (SELECT COUNT('x')\n FROM {$CFG->prefix}user u\n WHERE u.deleted = 0) AS stat1,\n (SELECT COUNT(DISTINCT u.id)\n FROM {$CFG->prefix}user u\n JOIN {$CFG->prefix}log l ON l.userid = u.id\n WHERE u.deleted = 0 AND {$timesql}) AS stat2" . sql_null_from_clause();
if ($logspresent and !execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_progress('7');
if (empty($CFG->defaultfrontpageroleid)) {
// 1.9 only, so far
$defaultfproleid = 0;
} else {
$defaultfproleid = $CFG->defaultfrontpageroleid;
}
/// 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 {$CFG->prefix}stats_daily\n WHERE stattype = 'enrolments' AND courseid = " . SITEID . " AND\n roleid = {$defaultfproleid} AND timeend = {$nextmidnight}";
if ($logspresent and !execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_progress('8');
$sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2)\n\n SELECT 'enrolments', {$nextmidnight}, " . SITEID . ", {$defaultfproleid},\n (SELECT COUNT('x')\n FROM {$CFG->prefix}user u\n WHERE u.deleted = 0) AS stat1,\n (SELECT COUNT(DISTINCT u.id)\n FROM {$CFG->prefix}user u\n JOIN {$CFG->prefix}log l ON l.userid = u.id\n WHERE u.deleted = 0 AND {$timesql}) AS stat2" . sql_null_from_clause();
if ($logspresent and !execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_progress('9');
} else {
stats_daily_progress('x');
stats_daily_progress('x');
}
/// individual user stats (including not-logged-in) in each course, this is slow - reuse this data if possible
$sql = "INSERT INTO {$CFG->prefix}stats_user_daily (stattype, timeend, courseid, userid, statsreads, statswrites)\n\n SELECT 'activity' AS stattype, {$nextmidnight} AS timeend, d.courseid, d.userid,\n (SELECT COUNT('x')\n FROM {$CFG->prefix}log l\n WHERE l.userid = d.userid AND\n l.course = d.courseid AND {$timesql} AND\n l.action IN ({$viewactions})) AS statsreads,\n (SELECT COUNT('x')\n FROM {$CFG->prefix}log l\n WHERE l.userid = d.userid AND\n l.course = d.courseid AND {$timesql} AND\n l.action IN ({$postactions})) AS statswrites\n FROM (SELECT DISTINCT u.id AS userid, l.course AS courseid\n FROM {$CFG->prefix}user u, {$CFG->prefix}log l\n WHERE u.id = l.userid AND {$timesql}\n UNION\n SELECT 0 AS userid, " . SITEID . " AS courseid" . sql_null_from_clause() . ") d";
// can not use group by here because pg can not handle it :-(
if ($logspresent and !execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_progress('10');
/// how many view/post actions in each course total
$sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2)\n\n SELECT 'activity' AS stattype, {$nextmidnight} AS timeend, c.id AS courseid, 0,\n (SELECT COUNT('x')\n FROM {$CFG->prefix}log l1\n WHERE l1.course = c.id AND l1.action IN ({$viewactions}) AND\n {$timesql1}) AS stat1,\n (SELECT COUNT('x')\n FROM {$CFG->prefix}log l2\n WHERE l2.course = c.id AND l2.action IN ({$postactions}) AND\n {$timesql2}) AS stat2\n FROM {$CFG->prefix}course c\n WHERE EXISTS (SELECT 'x'\n FROM {$CFG->prefix}log l\n WHERE l.course = c.id and {$timesql})";
if ($logspresent and !execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_progress('11');
/// how many view actions for each course+role - excluding guests and frontpage
$sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2)\n\n SELECT 'activity', timeend, courseid, roleid, SUM(statsreads), SUM(statswrites)\n FROM (\n SELECT {$nextmidnight} AS timeend, pl.courseid, pl.roleid, sud.statsreads, sud.statswrites\n FROM {$CFG->prefix}stats_user_daily sud,\n (SELECT DISTINCT ra.userid, ra.roleid, c.id AS courseid\n FROM {$CFG->prefix}role_assignments ra {$enroljoin}\n WHERE c.id <> " . SITEID . " AND\n ra.roleid <> {$guestrole->id} AND\n ra.userid <> {$guest->id} AND\n {$enrolwhere}\n ) pl\n WHERE sud.userid = pl.userid AND\n sud.courseid = pl.courseid AND\n sud.timeend = {$nextmidnight} AND\n sud.stattype='activity'\n ) inline_view\n GROUP BY timeend, courseid, roleid\n HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
if ($logspresent and !execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_progress('12');
/// how many view actions from guests only in each course - excluding frontpage
/// (guest is anybody with guest role or no role with course:view in course - this may not work properly if category limit too low)
/// normal users may enter course with temporary guest acces too
$sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2)\n\n SELECT 'activity', timeend, courseid, nroleid, SUM(statsreads), SUM(statswrites)\n FROM (\n SELECT {$nextmidnight} AS timeend, sud.courseid, {$guestrole->id} AS nroleid, sud.statsreads, sud.statswrites\n FROM {$CFG->prefix}stats_user_daily sud\n WHERE sud.timeend = {$nextmidnight} AND sud.courseid <> " . SITEID . " AND\n sud.stattype='activity' AND\n (sud.userid = {$guest->id} OR sud.userid\n NOT IN (SELECT ra.userid\n FROM {$CFG->prefix}role_assignments ra {$enroljoin}\n WHERE c.id <> " . SITEID . " AND ra.roleid <> {$guestrole->id} AND\n {$enrolwhere}))\n ) inline_view\n GROUP BY timeend, courseid, nroleid\n HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
if ($logspresent and !execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_progress('13');
/// how many view actions for each role on frontpage - excluding guests, not-logged-in and default frontpage role
$sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2)\n\n SELECT 'activity', timeend, courseid, roleid, SUM(statsreads), SUM(statswrites)\n FROM (\n SELECT {$nextmidnight} AS timeend, pl.courseid, pl.roleid, sud.statsreads, sud.statswrites\n FROM {$CFG->prefix}stats_user_daily sud,\n (SELECT DISTINCT ra.userid, ra.roleid, c.id AS courseid\n FROM {$CFG->prefix}role_assignments ra {$enroljoin}\n WHERE c.id = " . SITEID . " AND\n ra.roleid <> {$defaultfproleid} AND\n ra.roleid <> {$guestrole->id} AND\n ra.userid <> {$guest->id} AND\n {$enrolwhere}\n ) pl\n WHERE sud.userid = pl.userid AND\n sud.courseid = pl.courseid AND\n sud.timeend = {$nextmidnight} AND\n sud.stattype='activity'\n ) inline_view\n GROUP BY timeend, courseid, roleid\n HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
if ($logspresent and !execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_progress('14');
/// how many view actions for default frontpage role on frontpage only
$sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2)\n\n SELECT 'activity', timeend, courseid, nroleid, SUM(statsreads), SUM(statswrites)\n FROM (\n SELECT {$nextmidnight} AS timeend, sud.courseid, {$defaultfproleid} AS nroleid, sud.statsreads, sud.statswrites\n FROM {$CFG->prefix}stats_user_daily sud\n WHERE sud.timeend = {$nextmidnight} AND sud.courseid = " . SITEID . " AND\n sud.stattype='activity' AND\n sud.userid <> {$guest->id} AND sud.userid <> 0 AND sud.userid\n NOT IN (SELECT ra.userid\n FROM {$CFG->prefix}role_assignments ra {$fpjoin}\n WHERE c.id = " . SITEID . " AND ra.roleid <> {$guestrole->id} AND\n ra.roleid <> {$defaultfproleid} AND {$fpwhere})\n ) inline_view\n GROUP BY timeend, courseid, nroleid\n HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
if ($logspresent and !execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_progress('15');
/// how many view actions for guests or not-logged-in on frontpage
$sql = "INSERT INTO {$CFG->prefix}stats_daily (stattype, timeend, courseid, roleid, stat1, stat2)\n\n SELECT 'activity', timeend, courseid, nroleid, SUM(statsreads), SUM(statswrites)\n FROM (\n SELECT {$nextmidnight} AS timeend, " . SITEID . " AS courseid, {$guestrole->id} AS nroleid, pl.statsreads, pl.statswrites\n FROM (\n SELECT sud.statsreads, sud.statswrites\n FROM {$CFG->prefix}stats_user_daily sud\n WHERE (sud.userid = {$guest->id} OR sud.userid = 0) AND\n sud.timeend = {$nextmidnight} AND sud.courseid = " . SITEID . " AND\n sud.stattype='activity'\n ) pl\n ) inline_view\n GROUP BY timeend, courseid, nroleid\n HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
if ($logspresent and !execute_sql($sql, false)) {
$failed = true;
break;
}
stats_daily_progress('16');
// remember processed days
set_config('statslastdaily', $nextmidnight);
mtrace(" finished until {$nextmidnight}: " . userdate($nextmidnight) . " (in " . (time() - $daystart) . " s)");
$timestart = $nextmidnight;
$nextmidnight = stats_get_next_day_start($nextmidnight);
}
set_cron_lock('statsrunning', null);
if ($failed) {
$days--;
mtrace("...error occured, completed {$days} days of statistics.");
return false;
} else {
mtrace("...completed {$days} days of statistics.");
return true;
}
}
