statslib.php

<?php

    // THESE CONSTANTS ARE USED FOR THE REPORTING PAGE.

    define('STATS_REPORT_LOGINS',1); // double impose logins and unqiue logins on a line graph. site course only.
    define('STATS_REPORT_READS',2); // double impose student reads and teacher reads on a line graph.
    define('STATS_REPORT_WRITES',3); // double impose student writes and teacher writes on a line graph.
    define('STATS_REPORT_ACTIVITY',4); // 2+3 added up, teacher vs student.
    define('STATS_REPORT_ACTIVITYBYROLE',5); // all activity, reads vs writes, seleted by role.

    // user level stats reports.
    define('STATS_REPORT_USER_ACTIVITY',7);
    define('STATS_REPORT_USER_ALLACTIVITY',8);
    define('STATS_REPORT_USER_LOGINS',9);
    define('STATS_REPORT_USER_VIEW',10);  // this is the report you see on the user profile.

    // admin only ranking stats reports
    define('STATS_REPORT_ACTIVE_COURSES',11);
    define('STATS_REPORT_ACTIVE_COURSES_WEIGHTED',12);
    define('STATS_REPORT_PARTICIPATORY_COURSES',13);
    define('STATS_REPORT_PARTICIPATORY_COURSES_RW',14);

    // start after 0 = show dailies.
    define('STATS_TIME_LASTWEEK',1);
    define('STATS_TIME_LAST2WEEKS',2);
    define('STATS_TIME_LAST3WEEKS',3);
    define('STATS_TIME_LAST4WEEKS',4);

    // start after 10 = show weeklies
    define('STATS_TIME_LAST2MONTHS',12);

    define('STATS_TIME_LAST3MONTHS',13);
    define('STATS_TIME_LAST4MONTHS',14);
    define('STATS_TIME_LAST5MONTHS',15);
    define('STATS_TIME_LAST6MONTHS',16);

    // start after 20 = show monthlies
    define('STATS_TIME_LAST7MONTHS',27);
    define('STATS_TIME_LAST8MONTHS',28);
    define('STATS_TIME_LAST9MONTHS',29);
    define('STATS_TIME_LAST10MONTHS',30);
    define('STATS_TIME_LAST11MONTHS',31);
    define('STATS_TIME_LASTYEAR',32);

    // different modes for what reports to offer
    define('STATS_MODE_GENERAL',1);
    define('STATS_MODE_DETAILED',2);
    define('STATS_MODE_RANKED',3); // admins only - ranks courses

/**
 * Print daily cron progress
 * @param string $ident 
 */
function stats_daily_progress($ident) {
    static $start = 0;
    static $init  = 0;

    if ($ident == 'init') {
        $init = $start = time();
        return;
    }

    $elapsed = time() - $start;
    $start   = time();

    if (debugging('', DEBUG_ALL)) {
        mtrace("$ident:$elapsed ", '');
    } else {
        mtrace('.', '');
    }
}

/**
 * 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'
                  FROM {$CFG->prefix}log l
                 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)

                SELECT 'logins' AS stattype, $nextmidnight AS timeend, ".SITEID." AS courseid,
                       l.userid, count(l.id) AS statsreads
                  FROM {$CFG->prefix}log l
                 WHERE action = 'login' AND $timesql
              GROUP BY stattype, timeend, courseid, userid
                HAVING count(l.id) > 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)

                SELECT 'logins' AS stattype, $nextmidnight AS timeend, ".SITEID." as courseid, 0,
                       COALESCE((SELECT SUM(statsreads)
                                       FROM {$CFG->prefix}stats_user_daily s1
                                      WHERE s1.stattype = 'logins' AND timeend = $nextmidnight), 0) AS stat1,
                       (SELECT COUNT('x')
                          FROM {$CFG->prefix}stats_user_daily s2
                         WHERE s2.stattype = 'logins' AND timeend = $nextmidnight) AS stat2";

        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)

                SELECT 'enrolments' AS stattype, $nextmidnight AS timeend,
                        pl.courseid, pl.roleid, COUNT(DISTINCT pl.userid) AS stat1, 0 AS stat2
                  FROM (SELECT DISTINCT ra.roleid, ra.userid, c.id as courseid
                          FROM {$CFG->prefix}role_assignments ra $enroljoin_na
                         WHERE $enrolwhere_na
                       ) pl
              GROUP BY stattype, timeend, pl.courseid, pl.roleid, stat2";

        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
                   SET stat2 = (SELECT COUNT(DISTINCT ra.userid)
                                  FROM {$CFG->prefix}role_assignments ra $enroljoin_na
                                 WHERE ra.roleid = {$CFG->prefix}stats_daily.roleid AND
                                       c.id = {$CFG->prefix}stats_daily.courseid AND
                                       $enrolwhere_na AND
                                       EXISTS (SELECT 'x'
                                                 FROM {$CFG->prefix}log l
                                                WHERE l.course = {$CFG->prefix}stats_daily.courseid AND
                                                      l.userid = ra.userid AND $timesql))
                 WHERE {$CFG->prefix}stats_daily.stattype = 'enrolments' AND
                       {$CFG->prefix}stats_daily.timeend = $nextmidnight AND
                       {$CFG->prefix}stats_daily.courseid IN
                          (SELECT DISTINCT l.course
                             FROM {$CFG->prefix}log l
                            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)

                SELECT 'enrolments' AS stattype, $nextmidnight AS timeend,
                       c.id, 0 AS nroleid, COUNT(DISTINCT ra.userid) AS stat1, 0 AS stat2
                  FROM {$CFG->prefix}role_assignments ra $enroljoin_na
                 WHERE c.id <> ".SITEID." AND $enrolwhere_na
              GROUP BY stattype, timeend, c.id, nroleid, stat2
                HAVING COUNT(DISTINCT ra.userid) > 0";

        if ($logspresent and !execute_sql($sql, false)) {
            $failed = true;
            break;
        }
        stats_daily_progress('5');

        $sql = "UPDATE {$CFG->prefix}stats_daily
                   SET stat2 = (SELECT COUNT(DISTINCT ra.userid)
                                  FROM {$CFG->prefix}role_assignments ra $enroljoin_na
                                 WHERE c.id = {$CFG->prefix}stats_daily.courseid AND
                                       $enrolwhere_na AND
                                       EXISTS (SELECT 'x'
                                                 FROM {$CFG->prefix}log l
                                                WHERE l.course = {$CFG->prefix}stats_daily.courseid AND
                                                      l.userid = ra.userid AND $timesql))
                 WHERE {$CFG->prefix}stats_daily.stattype = 'enrolments' AND
                       {$CFG->prefix}stats_daily.timeend = $nextmidnight AND
                       {$CFG->prefix}stats_daily.roleid = 0 AND
                       {$CFG->prefix}stats_daily.courseid IN
                          (SELECT l.course
                             FROM {$CFG->prefix}log l
                            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)

                SELECT 'enrolments', $nextmidnight, ".SITEID.", 0,
                       (SELECT COUNT('x')
                          FROM {$CFG->prefix}user u
                         WHERE u.deleted = 0) AS stat1,
                       (SELECT COUNT(DISTINCT u.id)
                          FROM {$CFG->prefix}user u
                               JOIN {$CFG->prefix}log l ON l.userid = u.id
                         WHERE u.deleted = 0 AND $timesql) AS stat2";

        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
                      FROM {$CFG->prefix}stats_daily
                     WHERE stattype = 'enrolments' AND courseid = ".SITEID." AND
                           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)

                    SELECT 'enrolments', $nextmidnight, ".SITEID.", $defaultfproleid,
                           (SELECT COUNT('x')
                              FROM {$CFG->prefix}user u
                             WHERE u.deleted = 0) AS stat1,
                           (SELECT COUNT(DISTINCT u.id)
                              FROM {$CFG->prefix}user u
                                   JOIN {$CFG->prefix}log l ON l.userid = u.id
                             WHERE u.deleted = 0 AND $timesql) AS stat2";

            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)

                SELECT 'activity' AS stattype, $nextmidnight AS timeend, d.courseid, d.userid,
                       (SELECT COUNT('x')
                          FROM {$CFG->prefix}log l
                         WHERE l.userid = d.userid AND
                               l.course = d.courseid AND $timesql AND
                               l.action IN ($viewactions)) AS statsreads,
                       (SELECT COUNT('x')
                          FROM {$CFG->prefix}log l
                         WHERE l.userid = d.userid AND
                               l.course = d.courseid AND $timesql AND
                               l.action IN ($postactions)) AS statswrites
                  FROM (SELECT DISTINCT u.id AS userid, l.course AS courseid
                          FROM {$CFG->prefix}user u, {$CFG->prefix}log l
                         WHERE u.id = l.userid AND $timesql
                       UNION
                        SELECT 0 AS userid, ".SITEID." AS courseid) 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)

                SELECT 'activity' AS stattype, $nextmidnight AS timeend, c.id AS courseid, 0,
                       (SELECT COUNT('x')
                          FROM {$CFG->prefix}log l1
                         WHERE l1.course = c.id AND l1.action IN ($viewactions) AND
                               $timesql1) AS stat1,
                       (SELECT COUNT('x')
                          FROM {$CFG->prefix}log l2
                         WHERE l2.course = c.id AND l2.action IN ($postactions) AND
                               $timesql2) AS stat2
                  FROM {$CFG->prefix}course c
                 WHERE EXISTS (SELECT 'x'
                                 FROM {$CFG->prefix}log l
                                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)

                SELECT 'activity' AS stattype, $nextmidnight AS timeend, pl.courseid, pl.roleid,
                       SUM(sud.statsreads) AS stat1, SUM(sud.statswrites) AS stat2
                  FROM {$CFG->prefix}stats_user_daily sud,
                       (SELECT DISTINCT ra.userid, ra.roleid, c.id AS courseid
                          FROM {$CFG->prefix}role_assignments ra $enroljoin
                         WHERE c.id <> ".SITEID." AND
                               ra.roleid <> $guestrole->id AND ra.userid <> $guest->id AND
                               $enrolwhere
                        ) pl
                 WHERE sud.userid = pl.userid AND sud.courseid = pl.courseid AND
                       sud.timeend = $nextmidnight AND sud.stattype='activity'  
              GROUP BY stattype, timeend, pl.courseid, pl.roleid
                HAVING SUM(sud.statsreads) > 0 OR SUM(sud.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)

                SELECT 'activity' AS stattype, $nextmidnight AS timeend, sud.courseid, $guestrole->id AS nroleid,
                       SUM(sud.statsreads) AS stat1, SUM(sud.statswrites) AS stat2
                  FROM {$CFG->prefix}stats_user_daily sud
                 WHERE sud.timeend = $nextmidnight AND sud.courseid <> ".SITEID." AND
                       sud.stattype='activity' AND
                       (sud.userid = $guest->id OR sud.userid
                         NOT IN (SELECT ra.userid
                                   FROM {$CFG->prefix}role_assignments ra $enroljoin
                                  WHERE c.id <> ".SITEID." AND  ra.roleid <> $guestrole->id AND
                                        $enrolwhere))
              GROUP BY stattype, timeend, sud.courseid, nroleid
                HAVING SUM(sud.statsreads) > 0 OR SUM(sud.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)

                SELECT 'activity' AS stattype, $nextmidnight AS timeend, pl.courseid, pl.roleid,
                       SUM(sud.statsreads) AS stat1, SUM(sud.statswrites) AS stat2
                  FROM {$CFG->prefix}stats_user_daily sud,
                       (SELECT DISTINCT ra.userid, ra.roleid, c.id AS courseid
                          FROM {$CFG->prefix}role_assignments ra $enroljoin
                         WHERE c.id = ".SITEID." AND
                               ra.roleid <> $defaultfproleid AND
                               ra.roleid <> $guestrole->id AND ra.userid <> $guest->id AND
                               $enrolwhere
                        ) pl
                 WHERE sud.userid = pl.userid AND sud.courseid = pl.courseid AND
                       sud.timeend = $nextmidnight AND sud.stattype='activity'  
              GROUP BY stattype, timeend, pl.courseid, pl.roleid
                HAVING SUM(sud.statsreads) > 0 OR SUM(sud.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)

                SELECT 'activity' AS stattype, $nextmidnight AS timeend, sud.courseid, $defaultfproleid AS nroleid,
                       SUM(sud.statsreads) AS stat1, SUM(sud.statswrites) AS stat2
                  FROM {$CFG->prefix}stats_user_daily sud
                 WHERE sud.timeend = $nextmidnight AND sud.courseid = ".SITEID." AND
                       sud.stattype='activity' AND
                       sud.userid <> $guest->id AND sud.userid <> 0 AND sud.userid
                         NOT IN (SELECT ra.userid
                                   FROM {$CFG->prefix}role_assignments ra $fpjoin
                                  WHERE c.id = ".SITEID." AND  ra.roleid <> $guestrole->id AND
                                        ra.roleid <> $defaultfproleid AND $fpwhere)
              GROUP BY stattype, timeend, sud.courseid, nroleid
                HAVING SUM(sud.statsreads) > 0 OR SUM(sud.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)

                SELECT 'activity' AS stattype, $nextmidnight AS timeend, ".SITEID." AS courseid, $guestrole->id AS nroleid,
                       SUM(pl.statsreads) AS stat1, SUM(pl.statswrites) AS stat2
                  FROM (SELECT sud.statsreads, sud.statswrites
                          FROM {$CFG->prefix}stats_user_daily sud
                         WHERE (sud.userid = $guest->id OR sud.userid = 0) AND
                               sud.timeend = $nextmidnight AND sud.courseid = ".SITEID." AND
                               sud.stattype='activity'
                        ) pl
              GROUP BY stattype, timeend, courseid, nroleid
                HAVING SUM(pl.statsreads) > 0 OR SUM(pl.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;
    }
}


/**
 * Execute weekly statistics gathering
 * @return boolean success
 */
function stats_cron_weekly() {
    global $CFG;

    $now = time();

    // read last execution date from db
    if (!$timestart = get_config(NULL, 'statslastweekly')) {
        $timestart = stats_get_base_daily(stats_get_start_from('weekly'));
        set_config('statslastweekly', $timestart);
    }

    $nextstartweek = stats_get_next_week_start($timestart);

    // are there any weeks that need to be processed?
    if ($now < $nextstartweek) {
        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_weekly',      "timeend > $timestart");
    delete_records_select('stats_user_weekly', "timeend > $timestart");

    mtrace("Running weekly statistics gathering, starting at $timestart:");

    $weeks = 0;
    while ($now > $nextstartweek) {
        @set_time_limit($timeout - 200);
        $weeks++;

        if ($weeks > 1) {
            // move the lock
            set_cron_lock('statsrunning', time() + $timeout, true);
        }

        $logtimesql  = "l.time >= $timestart AND l.time < $nextstartweek";
        $stattimesql = "timeend > $timestart AND timeend <= $nextstartweek";

    /// process login info first
        $sql = "INSERT INTO {$CFG->prefix}stats_user_weekly (stattype, timeend, courseid, userid, statsreads)

                SELECT 'logins' AS stattype, $nextstartweek AS timeend, ".SITEID." as courseid,
                       l.userid, count(l.id) AS statsreads
                  FROM {$CFG->prefix}log l
                 WHERE action = 'login' AND $logtimesql
              GROUP BY stattype, timeend, courseid, userid
                HAVING count(l.id) > 0";

        execute_sql($sql, false);

        $sql = "INSERT INTO {$CFG->prefix}stats_weekly (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'logins' AS stattype, $nextstartweek AS timeend, ".SITEID." as courseid, 0,
                       COALESCE((SELECT SUM(statsreads)
                                   FROM {$CFG->prefix}stats_user_weekly s1
                                  WHERE s1.stattype = 'logins' AND timeend = $nextstartweek), 0) AS nstat1,
                       (SELECT COUNT('x')
                          FROM {$CFG->prefix}stats_user_weekly s2
                         WHERE s2.stattype = 'logins' AND timeend = $nextstartweek) AS nstat2";

        execute_sql($sql, false);


    /// now enrolments averages
        $sql = "INSERT INTO {$CFG->prefix}stats_weekly (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'enrolments' AS nstattype, $nextstartweek AS ntimeend, courseid, roleid,
                       CEIL(AVG(stat1)) AS nstat1, CEIL(AVG(stat2)) AS nstat2
                  FROM {$CFG->prefix}stats_daily sd
                 WHERE stattype = 'enrolments' AND $stattimesql
              GROUP BY nstattype, ntimeend, courseid, roleid";

        execute_sql($sql, false);


    /// activity read/write averages
        $sql = "INSERT INTO {$CFG->prefix}stats_weekly (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'activity' AS nstattype, $nextstartweek AS ntimeend, courseid, roleid,
                       SUM(stat1) AS nstat1, SUM(stat2) AS nstat2
                  FROM {$CFG->prefix}stats_daily
                 WHERE stattype = 'activity' AND $stattimesql
              GROUP BY nstattype, ntimeend, courseid, roleid";

        execute_sql($sql, false);


    /// user read/write averages
        $sql = "INSERT INTO {$CFG->prefix}stats_user_weekly (stattype, timeend, courseid, userid, statsreads, statswrites)

                SELECT 'activity' AS nstattype, $nextstartweek AS ntimeend, courseid, userid,
                       SUM(statsreads) AS nstatsreads, SUM(statswrites) AS nstatswrites
                  FROM {$CFG->prefix}stats_user_daily
                 WHERE stattype = 'activity' AND $stattimesql
              GROUP BY nstattype, ntimeend, courseid, userid";

        execute_sql($sql, false);

        set_config('statslastweekly', $nextstartweek);
        mtrace(" finished until $nextstartweek: ".userdate($nextstartweek));

        $timestart     = $nextstartweek;
        $nextstartweek = stats_get_next_week_start($nextstartweek);
    }

    set_cron_lock('statsrunning', null);
    mtrace("...completed $weeks weeks of statistics.");
    return true;
}

/**
 * Execute monthly statistics gathering
 * @return boolean success
 */
function stats_cron_monthly() {
    global $CFG;

    $now = time();

    // read last execution date from db
    if (!$timestart = get_config(NULL, 'statslastmonthly')) {
        $timestart = stats_get_base_monthly(stats_get_start_from('monthly'));
        set_config('statslastmonthly', $timestart);
    }

    $nextstartmonth = stats_get_next_month_start($timestart);

    // are there any months that need to be processed?
    if ($now < $nextstartmonth) {
        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;
    }

    // fisr delete entries that should not be there yet
    delete_records_select('stats_monthly', "timeend > $timestart");
    delete_records_select('stats_user_monthly', "timeend > $timestart");

    $startmonth = stats_get_base_monthly($now);


    mtrace("Running monthly statistics gathering, starting at $timestart:");

    $months = 0;
    while ($now > $nextstartmonth) {
        @set_time_limit($timeout - 200);
        $months++;

        if ($months > 1) {
            // move the lock
            set_cron_lock('statsrunning', time() + $timeout, true);
        }

        $logtimesql  = "l.time >= $timestart AND l.time < $nextstartmonth";
        $stattimesql = "timeend > $timestart AND timeend <= $nextstartmonth";

    /// process login info first
        $sql = "INSERT INTO {$CFG->prefix}stats_user_monthly (stattype, timeend, courseid, userid, statsreads)

                SELECT 'logins' AS stattype, $nextstartmonth AS timeend, ".SITEID." as courseid,
                       l.userid, count(l.id) AS statsreads
                  FROM {$CFG->prefix}log l
                 WHERE action = 'login' AND $logtimesql
              GROUP BY stattype, timeend, courseid, userid";

        execute_sql($sql, false);

        $sql = "INSERT INTO {$CFG->prefix}stats_monthly (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'logins' AS stattype, $nextstartmonth AS timeend, ".SITEID." as courseid, 0,
                       COALESCE((SELECT SUM(statsreads)
                                   FROM {$CFG->prefix}stats_user_monthly s1
                                  WHERE s1.stattype = 'logins' AND timeend = $nextstartmonth), 0) AS nstat1,
                       (SELECT COUNT('x')
                          FROM {$CFG->prefix}stats_user_monthly s2
                         WHERE s2.stattype = 'logins' AND timeend = $nextstartmonth) AS nstat2";

        execute_sql($sql, false);


    /// now enrolments averages
        $sql = "INSERT INTO {$CFG->prefix}stats_monthly (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'enrolments' AS nstattype, $nextstartmonth AS ntimeend, courseid, roleid,
                       CEIL(AVG(stat1)) AS nstat1, CEIL(AVG(stat2)) AS nstat2
                  FROM {$CFG->prefix}stats_daily sd
                 WHERE stattype = 'enrolments' AND $stattimesql
              GROUP BY nstattype, ntimeend, courseid, roleid";

        execute_sql($sql, false);


    /// activity read/write averages
        $sql = "INSERT INTO {$CFG->prefix}stats_monthly (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'activity' AS nstattype, $nextstartmonth AS ntimeend, courseid, roleid,
                       SUM(stat1) AS nstat1, SUM(stat2) AS nstat2
                  FROM {$CFG->prefix}stats_daily
                 WHERE stattype = 'activity' AND $stattimesql
              GROUP BY nstattype, ntimeend, courseid, roleid";

        execute_sql($sql, false);


    /// user read/write averages
        $sql = "INSERT INTO {$CFG->prefix}stats_user_monthly (stattype, timeend, courseid, userid, statsreads, statswrites)

                SELECT 'activity' AS nstattype, $nextstartmonth AS ntimeend, courseid, userid,
                       SUM(statsreads) AS nstatsreads, SUM(statswrites) AS nstatswrites
                  FROM {$CFG->prefix}stats_user_daily
                 WHERE stattype = 'activity' AND $stattimesql
              GROUP BY nstattype, ntimeend, courseid, userid";

        execute_sql($sql, false);

        set_config('statslastmonthly', $nextstartmonth);
        mtrace(" finished until $nextstartmonth: ".userdate($nextstartmonth));

        $timestart      = $nextstartmonth;
        $nextstartmonth = stats_get_next_month_start($nextstartmonth);
    }

    set_cron_lock('statsrunning', null);
    mtrace("...completed $months months of statistics.");
    return true;
}

/**
 * Returns simplified enrolment sql join data
 * @param int $limit number of max parent course categories
 * @param bool $includedoanything include also admins
 * @return array ra join and where string
 */
function stats_get_enrolled_sql($limit, $includedoanything) {
    global $CFG;

    $adm = $includedoanything ? " OR rc.capability = 'moodle/site:doanything'" : "";

    $join = "JOIN {$CFG->prefix}context ctx
                  ON ctx.id = ra.contextid
             CROSS JOIN {$CFG->prefix}course c
             JOIN {$CFG->prefix}role_capabilities rc
                  ON rc.roleid = ra.roleid";
    $where = "((rc.capability = 'moodle/course:view' $adm)
               AND rc.permission = 1 AND rc.contextid = ".SYSCONTEXTID."
               AND (ctx.contextlevel = ".CONTEXT_SYSTEM."
                    OR (c.id = ctx.instanceid AND ctx.contextlevel = ".CONTEXT_COURSE.")";

    for($i=1; $i<=$limit; $i++) {
        if ($i == 1) {
            $join .= " LEFT OUTER JOIN {$CFG->prefix}course_categories cc1
                            ON cc1.id = c.category";
            $where .= " OR (cc1.id = ctx.instanceid AND ctx.contextlevel = ".CONTEXT_COURSECAT.")";
        } else {
            $j = $i-1;
            $join .= " LEFT OUTER JOIN {$CFG->prefix}course_categories cc$i
                            ON cc$i.id = cc$j.parent";
            $where .= " OR (cc$i.id = ctx.instanceid AND ctx.contextlevel = ".CONTEXT_COURSECAT.")";
        }
    }

    $where .= "))";

    return array($join, $where);
}

/**
 * Return starting date of stats processing
 * @param string $str name of table - daily, weekly or monthly
 * @return int timestamp
 */
function stats_get_start_from($str) {
    global $CFG;

    // are there any data in stats table? Should not be...
    if ($timeend = get_field_sql('SELECT timeend FROM '.$CFG->prefix.'stats_'.$str.' ORDER BY timeend DESC')) {
        return $timeend;
    }
    // decide what to do based on our config setting (either all or none or a timestamp)
    switch ($CFG->statsfirstrun) {
        case 'all':
            if ($firstlog = get_field_sql('SELECT time FROM '.$CFG->prefix.'log ORDER BY time ASC')) {
                return $firstlog;
            }
        default:
            if (is_numeric($CFG->statsfirstrun)) {
                return time() - $CFG->statsfirstrun;
            }
            // not a number? use next instead
        case 'none':
            return strtotime('-3 day', time());
    }
}

/**
 * Start of day
 * @param int $time timestamp
 * @return start of day
 */
function stats_get_base_daily($time=0) {
    global $CFG;

    if (empty($time)) {
        $time = time();
    }
    if ($CFG->timezone == 99) {
        $time = strtotime(date('d-M-Y', $time));
        return $time;
    } else {
        $offset = get_timezone_offset($CFG->timezone);
        $gtime = $time + $offset;
        $gtime = intval($gtime / (60*60*24)) * 60*60*24;
        return $gtime - $offset;
    }
}

/**
 * Start of week
 * @param int $time timestamp
 * @return start of week
 */
function stats_get_base_weekly($time=0) {
    global $CFG;

    $time = stats_get_base_daily($time);
    $startday = $CFG->calendar_startwday;
    if ($CFG->timezone == 99) {
        $thisday = date('w', $time);
    } else {
        $offset = get_timezone_offset($CFG->timezone);
        $gtime = $time + $offset;
        $thisday = gmdate('w', $gtime);
    }
    if ($thisday > $startday) {
        $time = $time - (($thisday - $startday) * 60*60*24);
    } else if ($thisday < $startday) {
        $time = $time - ((7 + $thisday - $startday) * 60*60*24);
    }
    return $time;
}

/**
 * Start of month
 * @param int $time timestamp
 * @return start of month
 */
function stats_get_base_monthly($time=0) {
    global $CFG;

    if (empty($time)) {
        $time = time();
    }
    if ($CFG->timezone == 99) {
        return strtotime(date('1-M-Y', $time));

    } else {
        $time = stats_get_base_daily($time);
        $offset = get_timezone_offset($CFG->timezone);
        $gtime = $time + $offset;
        $day = gmdate('d', $gtime);
        if ($day == 1) {
            return $time;
        }
        return $gtime - (($day-1) * 60*60*24);
    }
}

/**
 * Start of next day
 * @param int $time timestamp
 * @return start of next day
 */
function stats_get_next_day_start($time) {
    $next = stats_get_base_daily($time);
    $next = $next + 60*60*26;
    $next = stats_get_base_daily($next);
    if ($next <= $time) {
        //DST trouble - prevent infinite loops
        $next = $next + 60*60*24;
    }
    return $next;
}

/**
 * Start of next week
 * @param int $time timestamp
 * @return start of next week
 */
function stats_get_next_week_start($time) {
    $next = stats_get_base_weekly($time);
    $next = $next + 60*60*24*9;
    $next = stats_get_base_weekly($next);
    if ($next <= $time) {
        //DST trouble - prevent infinite loops
        $next = $next + 60*60*24*7;
    }
    return $next;
}

/**
 * Start of next month
 * @param int $time timestamp
 * @return start of next month
 */
function stats_get_next_month_start($time) {
    $next = stats_get_base_monthly($time);
    $next = $next + 60*60*24*33;
    $next = stats_get_base_monthly($next);
    if ($next <= $time) {
        //DST trouble - prevent infinite loops
        $next = $next + 60*60*24*31;
    }
    return $next;
}

/**
 * Remove old stats data
 */
function stats_clean_old() {
    mtrace("Running stats cleanup tasks...");
    $deletebefore =  stats_get_base_monthly();

    // delete dailies older than 3 months (to be safe)
    $deletebefore = strtotime('-3 months', $deletebefore);
    delete_records_select('stats_daily',      "timeend < $deletebefore");
    delete_records_select('stats_user_daily', "timeend < $deletebefore");

    // delete weeklies older than 9  months (to be safe)
    $deletebefore = strtotime('-6 months', $deletebefore);
    delete_records_select('stats_weekly',      "timeend < $deletebefore");
    delete_records_select('stats_user_weekly', "timeend < $deletebefore");

    // don't delete monthlies

    mtrace("...stats cleanup finished");
}

function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) {
    global $CFG,$db;

    $param = new object();

    if ($time < 10) { // dailies
        // number of days to go back = 7* time
        $param->table = 'daily';
        $param->timeafter = strtotime("-".($time*7)." days",stats_get_base_daily());
    } elseif ($time < 20) { // weeklies
        // number of weeks to go back = time - 10 * 4 (weeks) + base week
        $param->table = 'weekly';
        $param->timeafter = strtotime("-".(($time - 10)*4)." weeks",stats_get_base_weekly());
    } else { // monthlies.
        // number of months to go back = time - 20 * months + base month
        $param->table = 'monthly';
        $param->timeafter = strtotime("-".($time - 20)." months",stats_get_base_monthly());
    }

    $param->extras = '';

    // compatibility - if we're in postgres, cast to real for some reports.
    $real = '';
    if ($CFG->dbfamily == 'postgres') {
        $real = '::real';
    }

    switch ($report) {
    // ******************** STATS_MODE_GENERAL ******************** //
    case STATS_REPORT_LOGINS:
        $param->fields = 'timeend,sum(stat1) as line1,sum(stat2) as line2';
        $param->fieldscomplete = true;
        $param->stattype = 'logins';
        $param->line1 = get_string('statslogins');
        $param->line2 = get_string('statsuniquelogins');
        if ($courseid == SITEID) {
            $param->extras = 'GROUP BY timeend';
        }
        break;

    case STATS_REPORT_READS:
        $param->fields = sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat1 as line1';
        $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
        $param->aggregategroupby = 'roleid';
        $param->stattype = 'activity';
        $param->crosstab = true;
        $param->extras = 'GROUP BY timeend,roleid,stat1';
        if ($courseid == SITEID) {
            $param->fields = sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1) as line1';
            $param->extras = 'GROUP BY timeend,roleid';
        }
        break;

    case STATS_REPORT_WRITES:
        $param->fields = sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat2 as line1';
        $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
        $param->aggregategroupby = 'roleid';
        $param->stattype = 'activity';
        $param->crosstab = true;
        $param->extras = 'GROUP BY timeend,roleid,stat2';
        if ($courseid == SITEID) {
            $param->fields = sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat2) as line1';
            $param->extras = 'GROUP BY timeend,roleid';
        }
        break;

    case STATS_REPORT_ACTIVITY:
        $param->fields = sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1+stat2) as line1';
        $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
        $param->aggregategroupby = 'roleid';
        $param->stattype = 'activity';
        $param->crosstab = true;
        $param->extras = 'GROUP BY timeend,roleid';
        if ($courseid == SITEID) {
            $param->extras = 'GROUP BY timeend,roleid';
        }
        break;

    case STATS_REPORT_ACTIVITYBYROLE;
        $param->fields = 'stat1 AS line1, stat2 AS line2';
        $param->stattype = 'activity';
        $rolename = get_field('role','name','id',$roleid);
        $param->line1 = $rolename . get_string('statsreads');
        $param->line2 = $rolename . get_string('statswrites');
        if ($courseid == SITEID) {
            $param->extras = 'GROUP BY timeend';
        }
        break;

    // ******************** STATS_MODE_DETAILED ******************** //
    case STATS_REPORT_USER_ACTIVITY:
        $param->fields = 'statsreads as line1, statswrites as line2';
        $param->line1 = get_string('statsuserreads');
        $param->line2 = get_string('statsuserwrites');
        $param->stattype = 'activity';
        break;

    case STATS_REPORT_USER_ALLACTIVITY:
        $param->fields = 'statsreads+statswrites as line1';
        $param->line1 = get_string('statsuseractivity');
        $param->stattype = 'activity';
        break;

    case STATS_REPORT_USER_LOGINS:
        $param->fields = 'statsreads as line1';
        $param->line1 = get_string('statsuserlogins');
        $param->stattype = 'logins';
        break;

    case STATS_REPORT_USER_VIEW:
        $param->fields = 'statsreads as line1, statswrites as line2, statsreads+statswrites as line3';
        $param->line1 = get_string('statsuserreads');
        $param->line2 = get_string('statsuserwrites');
        $param->line3 = get_string('statsuseractivity');
        $param->stattype = 'activity';
        break;

    // ******************** STATS_MODE_RANKED ******************** //
    case STATS_REPORT_ACTIVE_COURSES:
        $param->fields = 'sum(stat1+stat2) AS line1';
        $param->stattype = 'activity';
        $param->orderby = 'line1 DESC';
        $param->line1 = get_string('activity');
        $param->graphline = 'line1';
        break;

    case STATS_REPORT_ACTIVE_COURSES_WEIGHTED:
        $threshold = 0;
        if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
            $threshold = $CFG->statsuserthreshold;
        }
        $param->fields = '';
        $param->sql = 'SELECT activity.courseid, activity.all_activity AS line1, enrolments.highest_enrolments AS line2,
                        activity.all_activity / enrolments.highest_enrolments as line3
                       FROM (
                            SELECT courseid, (stat1+stat2) AS all_activity
                              FROM '.$CFG->prefix.'stats_'.$param->table.'
                             WHERE stattype=\'activity\' AND timeend >= '.$param->timeafter.' AND roleid = 0
                       ) activity
                       INNER JOIN
                            (
                            SELECT courseid, max(stat1) AS highest_enrolments 
                              FROM '.$CFG->prefix.'stats_'.$param->table.'
                             WHERE stattype=\'enrolments\' AND timeend >= '.$param->timeafter.' AND stat1 > '.$threshold.' 
                          GROUP BY courseid
                      ) enrolments
                      ON (activity.courseid = enrolments.courseid)
                      ORDER BY line3 DESC';
        $param->line1 = get_string('activity');
        $param->line2 = get_string('users');
        $param->line3 = get_string('activityweighted');
        $param->graphline = 'line3';
        break;

    case STATS_REPORT_PARTICIPATORY_COURSES:
        $threshold = 0;
        if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
            $threshold = $CFG->statsuserthreshold;
        }
        $param->fields = '';
        $param->sql = 'SELECT courseid, ceil(avg(all_enrolments)) as line1,
                         ceil(avg(active_enrolments)) as line2, avg(proportion_active) AS line3
                       FROM (
                           SELECT courseid, timeend, stat2 as active_enrolments,
                                  stat1 as all_enrolments, stat2'.$real.'/stat1'.$real.' as proportion_active
                             FROM '.$CFG->prefix.'stats_'.$param->table.'
                            WHERE stattype=\'enrolments\' AND roleid = 0 AND stat1 > '.$threshold.'
                       ) aq
                       WHERE timeend >= '.$param->timeafter.'
                       GROUP BY courseid
                       ORDER BY line3 DESC';

        $param->line1 = get_string('users');
        $param->line2 = get_string('activeusers');
        $param->line3 = get_string('participationratio');
        $param->graphline = 'line3';
        break;

    case STATS_REPORT_PARTICIPATORY_COURSES_RW:
        $param->fields = '';
        $param->sql =  'SELECT courseid, sum(views) AS line1, sum(posts) AS line2,
                           avg(proportion_active) AS line3
                         FROM (
                           SELECT courseid, timeend, stat1 as views, stat2 AS posts,
                                  stat2'.$real.'/stat1'.$real.' as proportion_active
                             FROM '.$CFG->prefix.'stats_'.$param->table.'
                            WHERE stattype=\'activity\' AND roleid = 0 AND stat1 > 0
                       ) aq
                       WHERE timeend >= '.$param->timeafter.'
                       GROUP BY courseid
                       ORDER BY line3 DESC';
        $param->line1 = get_string('views');
        $param->line2 = get_string('posts');
        $param->line3 = get_string('participationratio');
        $param->graphline = 'line3';
        break;
    }

    /*
    if ($courseid == SITEID && $mode != STATS_MODE_RANKED) { // just aggregate all courses.
        $param->fields = preg_replace('/(?:sum)([a-zA-Z0-9+_]*)\W+as\W+([a-zA-Z0-9_]*)/i','sum($1) as $2',$param->fields);
        $param->extras = ' GROUP BY timeend'.((!empty($param->aggregategroupby)) ? ','.$param->aggregategroupby : '');
    }
    */
    //TODO must add the SITEID reports to the rest of the reports.
    return $param;
}

function stats_get_view_actions() {
    return array('view','view all','history');
}

function stats_get_post_actions() {
    return array('add','delete','edit','add mod','delete mod','edit section'.'enrol','loginas','new','unenrol','update','update mod');
}

function stats_get_action_names($str) {
    global $CFG;

    $mods = get_records('modules');
    $function = 'stats_get_'.$str.'_actions';
    $actions = $function();
    foreach ($mods as $mod) {
        $file = $CFG->dirroot.'/mod/'.$mod->name.'/lib.php';
        if (!is_readable($file)) {
            continue;
        }
        require_once($file);
        $function = $mod->name.'_get_'.$str.'_actions';
        if (function_exists($function)) {
            $actions = array_merge($actions,$function());
        }
    }

    // The array_values() forces a stack-like array
    // so we can later loop over safely...
    $actions =  array_values(array_unique($actions));
    $c = count($actions);
    for ($n=0;$n<$c;$n++) {
        $actions[$n] = "'" . $actions[$n] . "'"; // quote them for SQL
    }
    return $actions;
}

function stats_get_time_options($now,$lastweekend,$lastmonthend,$earliestday,$earliestweek,$earliestmonth) {

    $now = stats_get_base_daily(time());
    // it's really important that it's TIMEEND in the table. ie, tuesday 00:00:00 is monday night.
    // so we need to take a day off here (essentially add a day to $now
    $now += 60*60*24;

    $timeoptions = array();

    if ($now - (60*60*24*7) >= $earliestday) {
        $timeoptions[STATS_TIME_LASTWEEK] = get_string('numweeks','moodle',1);
    }
    if ($now - (60*60*24*14) >= $earliestday) {
        $timeoptions[STATS_TIME_LAST2WEEKS] = get_string('numweeks','moodle',2);
    }
    if ($now - (60*60*24*21) >= $earliestday) {
        $timeoptions[STATS_TIME_LAST3WEEKS] = get_string('numweeks','moodle',3);
    }
    if ($now - (60*60*24*28) >= $earliestday) {
        $timeoptions[STATS_TIME_LAST4WEEKS] = get_string('numweeks','moodle',4);// show dailies up to (including) here.
    }
    if ($lastweekend - (60*60*24*56) >= $earliestweek) {
        $timeoptions[STATS_TIME_LAST2MONTHS] = get_string('nummonths','moodle',2);
    }
    if ($lastweekend - (60*60*24*84) >= $earliestweek) {
        $timeoptions[STATS_TIME_LAST3MONTHS] = get_string('nummonths','moodle',3);
    }
    if ($lastweekend - (60*60*24*112) >= $earliestweek) {
        $timeoptions[STATS_TIME_LAST4MONTHS] = get_string('nummonths','moodle',4);
    }
    if ($lastweekend - (60*60*24*140) >= $earliestweek) {
        $timeoptions[STATS_TIME_LAST5MONTHS] = get_string('nummonths','moodle',5);
    }
    if ($lastweekend - (60*60*24*168) >= $earliestweek) {
        $timeoptions[STATS_TIME_LAST6MONTHS] = get_string('nummonths','moodle',6); // show weeklies up to (including) here
    }
    if (strtotime('-7 months',$lastmonthend) >= $earliestmonth) {
        $timeoptions[STATS_TIME_LAST7MONTHS] = get_string('nummonths','moodle',7);
    }
    if (strtotime('-8 months',$lastmonthend) >= $earliestmonth) {
        $timeoptions[STATS_TIME_LAST8MONTHS] = get_string('nummonths','moodle',8);
    }
    if (strtotime('-9 months',$lastmonthend) >= $earliestmonth) {
        $timeoptions[STATS_TIME_LAST9MONTHS] = get_string('nummonths','moodle',9);
    }
    if (strtotime('-10 months',$lastmonthend) >= $earliestmonth) {
        $timeoptions[STATS_TIME_LAST10MONTHS] = get_string('nummonths','moodle',10);
    }
    if (strtotime('-11 months',$lastmonthend) >= $earliestmonth) {
        $timeoptions[STATS_TIME_LAST11MONTHS] = get_string('nummonths','moodle',11);
    }
    if (strtotime('-1 year',$lastmonthend) >= $earliestmonth) {
        $timeoptions[STATS_TIME_LASTYEAR] = get_string('lastyear');
    }

    return $timeoptions;
}

function stats_get_report_options($courseid,$mode) {
    global $CFG;

    $reportoptions = array();

    switch ($mode) {
    case STATS_MODE_GENERAL:
        $reportoptions[STATS_REPORT_ACTIVITY] = get_string('statsreport'.STATS_REPORT_ACTIVITY);
        if ($courseid != SITEID && $context = get_context_instance(CONTEXT_COURSE, $courseid)) {
            $sql = 'SELECT r.id,r.name FROM '.$CFG->prefix.'role r JOIN '.$CFG->prefix.'stats_daily s ON s.roleid = r.id WHERE s.courseid = '.$courseid;
            if ($roles = get_records_sql($sql)) {
                foreach ($roles as $role) {
                    $reportoptions[STATS_REPORT_ACTIVITYBYROLE.$role->id] = get_string('statsreport'.STATS_REPORT_ACTIVITYBYROLE). ' '.$role->name;
                }
            }
        }
        $reportoptions[STATS_REPORT_READS] = get_string('statsreport'.STATS_REPORT_READS);
        $reportoptions[STATS_REPORT_WRITES] = get_string('statsreport'.STATS_REPORT_WRITES);
        if ($courseid == SITEID) {
            $reportoptions[STATS_REPORT_LOGINS] = get_string('statsreport'.STATS_REPORT_LOGINS);
        }

        break;
    case STATS_MODE_DETAILED:
        $reportoptions[STATS_REPORT_USER_ACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ACTIVITY);
        $reportoptions[STATS_REPORT_USER_ALLACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ALLACTIVITY);
        if (has_capability('moodle/site:viewreports', get_context_instance(CONTEXT_SYSTEM))) {
            $site = get_site();
            $reportoptions[STATS_REPORT_USER_LOGINS] = get_string('statsreport'.STATS_REPORT_USER_LOGINS);
        }
        break;
    case STATS_MODE_RANKED:
        if (has_capability('moodle/site:viewreports', get_context_instance(CONTEXT_SYSTEM))) {
            $reportoptions[STATS_REPORT_ACTIVE_COURSES] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES);
            $reportoptions[STATS_REPORT_ACTIVE_COURSES_WEIGHTED] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES_WEIGHTED);
            $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES);
            $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES_RW] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES_RW);
        }
     break;
    }

    return $reportoptions;
}

function stats_fix_zeros($stats,$timeafter,$timestr,$line2=true,$line3=false) {

    if (empty($stats)) {
        return;
    }

    $timestr = str_replace('user_','',$timestr); // just in case.
    $fun = 'stats_get_base_'.$timestr;

    $now = $fun();

    $times = array();
    // add something to timeafter since it is our absolute base
    $actualtimes = array();
    foreach ($stats as $statid=>$s) {
        //normalize the times in stats - those might have been created in different timezone, DST etc.
        $s->timeend = $fun($s->timeend + 60*60*5);
        $stats[$statid] = $s;

        $actualtimes[] = $s->timeend;
    }

    $timeafter = array_pop(array_values($actualtimes));

    while ($timeafter < $now) {
        $times[] = $timeafter;
        if ($timestr == 'daily') {
            $timeafter = stats_get_next_day_start($timeafter);
        } else if ($timestr == 'weekly') {
            $timeafter = stats_get_next_week_start($timeafter);
        } else if ($timestr == 'monthly') {
            $timeafter = stats_get_next_month_start($timeafter);
        } else {
            return $stats; // this will put us in a never ending loop.
        }
    }

    foreach ($times as $count => $time) {
        if (!in_array($time,$actualtimes) && $count != count($times) -1) {
            $newobj = new StdClass;
            $newobj->timeend = $time;
            $newobj->id = 0;
            $newobj->roleid = 0;
            $newobj->line1 = 0;
            if (!empty($line2)) {
                $newobj->line2 = 0;
            }
            if (!empty($line3)) {
                $newobj->line3 = 0;
            }
            $newobj->zerofixed = true;
            $stats[] = $newobj;
        }
    }

    usort($stats,"stats_compare_times");
    return $stats;

}

// helper function to sort arrays by $obj->timeend
function stats_compare_times($a,$b) {
   if ($a->timeend == $b->timeend) {
       return 0;
   }
   return ($a->timeend > $b->timeend) ? -1 : 1;
}

function stats_check_uptodate($courseid=0) {
    global $CFG;

    if (empty($courseid)) {
        $courseid = SITEID;
    }

    $latestday = stats_get_start_from('daily');

    if ((time() - 60*60*24*2) < $latestday) { // we're ok
        return NULL;
    }

    $a = new object();
    $a->daysdone = get_field_sql("SELECT count(distinct(timeend)) from {$CFG->prefix}stats_daily");

    // how many days between the last day and now?
    $a->dayspending = ceil((stats_get_base_daily() - $latestday)/(60*60*24));

    if ($a->dayspending == 0 && $a->daysdone != 0) {
        return NULL; // we've only just started...
    }

    //return error as string
    return get_string('statscatchupmode','error',$a);
}

/**
 * Calculate missing course totals in stats
 */
function stats_upgrade_totals() {
    global $CFG;

    if (empty($CFG->statsrolesupgraded)) {
        // stats not yet upgraded to cope with roles...
        return;
    }

    $types = array('daily', 'weekly', 'monthly');

    $now = time();
    $y30 = 60*60*24*365*30;              // 30 years ago :-O
    $y20 = 60*60*24*365*20;              // 20 years ago :-O
    $limit = $now - $y20;

    foreach ($types as $i => $type) {
        $type2 = $types[($i+1) % count($types)];

        // delete previous incomplete data
        $sql = "DELETE FROM {$CFG->prefix}stats_$type2
                      WHERE timeend < $limit";
        execute_sql($sql);

        // clear the totals if already exist
        $sql = "DELETE FROM {$CFG->prefix}stats_$type
                      WHERE (stattype = 'enrolments' OR stattype = 'activity') AND
                            roleid = 0";
        execute_sql($sql);

        $sql = "INSERT INTO {$CFG->prefix}stats_$type2 (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT stattype, (timeend - $y30), courseid, 0, SUM(stat1), SUM(stat2)
                  FROM {$CFG->prefix}stats_$type
                 WHERE (stattype = 'enrolments' OR stattype = 'activity') AND
                       roleid <> 0
              GROUP BY stattype, timeend, courseid";
        execute_sql($sql);

        $sql = "INSERT INTO {$CFG->prefix}stats_$type (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT stattype, (timeend + $y30), courseid, roleid, stat1, stat2
                  FROM {$CFG->prefix}stats_$type2
                 WHERE (stattype = 'enrolments' OR stattype = 'activity') AND
                       roleid = 0 AND timeend < $y20";
        execute_sql($sql);

        $sql = "DELETE FROM {$CFG->prefix}stats_$type2
                      WHERE timeend < $limit";
        execute_sql($sql);
    }
}


function stats_upgrade_for_roles_wrapper() {
    global $CFG;
    if (!empty($CFG->statsrolesupgraded)) {
        return true;
    }

    $result = begin_sql();

    $result = $result && stats_upgrade_user_table_for_roles('daily');
    $result = $result && stats_upgrade_user_table_for_roles('weekly');
    $result = $result && stats_upgrade_user_table_for_roles('monthly');

    $result = $result && stats_upgrade_table_for_roles('daily');
    $result = $result && stats_upgrade_table_for_roles('weekly');
    $result = $result && stats_upgrade_table_for_roles('monthly');


    $result = $result && commit_sql();

    if (!empty($result)) {
        set_config('statsrolesupgraded',time());
    }

    // finally upgade totals, no big deal if it fails
    stats_upgrade_totals();

    return $result;
}

/**
 * Upgrades a prefix_stats_user_* table for the new role based permission
 * system.
 *
 * @param string $period  daily, weekly or monthly: the stat period to upgrade
 * @return boolean @todo maybe something else (error message) depending on
 * how this will be called.
 */
function stats_upgrade_user_table_for_roles($period) {
    global $CFG;
    static $teacher_role_id, $student_role_id;

    if (!in_array($period, array('daily', 'weekly', 'monthly'))) {
        error_log('stats upgrade:  invalid period: ' . $period);
        return false;
    }

    if (!$teacher_role_id) {
        $role            = get_roles_with_capability('moodle/legacy:editingteacher', CAP_ALLOW);
        $role            = array_keys($role);
        $teacher_role_id = $role[0];
        $role            = get_roles_with_capability('moodle/legacy:student', CAP_ALLOW);
        $role            = array_keys($role);
        $student_role_id = $role[0];
    }

    if (empty($teacher_role_id) || empty($student_role_id)) {
        error_log("Couldn't find legacy roles for teacher or student");
        return false;
    }

    $status = true;

    $status = $status && execute_sql("UPDATE {$CFG->prefix}stats_user_{$period}
        SET roleid = $teacher_role_id
        WHERE roleid = 1");
    $status = $status && execute_sql("UPDATE {$CFG->prefix}stats_user_{$period}
        SET roleid = $student_role_id
        WHERE roleid = 2");

    return $status;
}

/**
 * Upgrades a prefix_stats_* table for the new role based permission system.
 *
 * @param string $period  daily, weekly or monthly: the stat period to upgrade
 * @return boolean        @todo depends on how this will be called
 */
function stats_upgrade_table_for_roles ($period) {
    global $CFG;
    static $teacher_role_id, $student_role_id;

    if (!in_array($period, array('daily', 'weekly', 'monthly'))) {
        return false;
    }

    if (!$teacher_role_id) {
        $role            = get_roles_with_capability('moodle/legacy:editingteacher', CAP_ALLOW);
        $role            = array_keys($role);
        $teacher_role_id = $role[0];
        $role            = get_roles_with_capability('moodle/legacy:student', CAP_ALLOW);
        $role            = array_keys($role);
        $student_role_id = $role[0];
    }

    if (empty($teacher_role_id) || empty($student_role_id)) {
        error_log("Couldn't find legacy roles for teacher or student");
        return false;
    }

    execute_sql("CREATE TABLE {$CFG->prefix}stats_{$period}_tmp AS
        SELECT * FROM {$CFG->prefix}stats_{$period}");

    $table = new XMLDBTable('stats_' . $period);
    if (!drop_table($table)) {
        return false;
    }

    // Create a new stats table
    // @todo this definition I have made blindly by looking at how definitions are
    // made, it needs work to make sure it works properly
    require_once("$CFG->libdir/xmldb/classes/XMLDBTable.class.php");

    $table = new XMLDBTable('stats_' . $period);
    $table->addFieldInfo('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
        XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);

    $table->addFieldInfo('courseid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
        XMLDB_NOTNULL, null, null, null, null);

    $table->addFieldInfo('roleid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
        XMLDB_NOTNULL, null, null, null, null);
    $table->addFieldInfo('timeend', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
        XMLDB_NOTNULL, null, null, null, null);
    $table->addFieldInfo('stattype', XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL,
        null, XMLDB_ENUM, array('enrolments', 'activity', 'logins'), 'activity');
    $table->addFieldInfo('stat1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
        XMLDB_NOTNULL, null, null, null, null);
    $table->addFieldInfo('stat2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
        XMLDB_NOTNULL, null, null, null, null);

    /// Adding keys to table stats_daily
    $table->addKeyInfo('primary', XMLDB_KEY_PRIMARY, array('id'));

    /// Adding indexes to table stats_daily
    $table->addIndexInfo('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid'));
    $table->addIndexInfo('timeend', XMLDB_INDEX_NOTUNIQUE, array('timeend'));
    $table->addIndexInfo('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid'));

    if (!create_table($table)) {
        return false;
    }

    //
    // Now insert the data from the temporary table into the new one
    //

    // Student enrolments
    execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
       (courseid, roleid, timeend, stattype, stat1, stat2)
       SELECT courseid, $student_role_id, timeend, 'enrolments', students, activestudents
       FROM {$CFG->prefix}stats_{$period}_tmp");

    // Teacher enrolments
    execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
       (courseid, roleid, timeend, stattype, stat1, stat2)
       SELECT courseid, $teacher_role_id, timeend, 'enrolments', teachers, activeteachers
       FROM {$CFG->prefix}stats_{$period}_tmp");

    // Student activity
    execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
       (courseid, roleid, timeend, stattype, stat1, stat2)
       SELECT courseid, $student_role_id, timeend, 'activity', studentreads, studentwrites
       FROM {$CFG->prefix}stats_{$period}_tmp");

    // Teacher activity
    execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
       (courseid, roleid, timeend, stattype, stat1, stat2)
       SELECT courseid, $teacher_role_id, timeend, 'activity', teacherreads, teacherwrites
       FROM {$CFG->prefix}stats_{$period}_tmp");

    // Logins
    execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
       (courseid, roleid, timeend, stattype, stat1, stat2)
       SELECT courseid, 0, timeend, 'logins', logins, uniquelogins
       FROM {$CFG->prefix}stats_{$period}_tmp WHERE courseid = ".SITEID);

    // Drop the temporary table
    $table = new XMLDBTable('stats_' . $period . '_tmp');
    if (!drop_table($table)) {
        return false;
    }

    return true;
}

?>