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