Example #1
0
/**
 * Execute daily statistics gathering
 * @param int $maxdays maximum number of days to be processed
 * @return boolean success
 */
function stats_cron_daily($maxdays=1) {
    global $CFG, $DB;

    $now = time();

    $fpcontext = 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;
    }
}
Example #2
0
/**
 * Execute daily statistics gathering
 * @param int $maxdays maximum number of days to be processed
 * @return boolean success
 */
function stats_cron_daily($maxdays = 1)
{
    global $CFG;
    $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;
    }
}