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