statslib.php

<?php

// This file is part of Moodle - http://moodle.org/
//
// Moodle is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// Moodle is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.

/**
 * @package    core
 * @subpackage stats
 * @copyright  1999 onwards Martin Dougiamas  {@link http://moodle.com}
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 */

defined('MOODLE_INTERNAL') || die();

/** THESE CONSTANTS ARE USED FOR THE REPORTING PAGE. */

define('STATS_REPORT_LOGINS',1); // double impose logins and unique 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, selected 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

// Output string when nodebug is on
define('STATS_PLACEHOLDER_OUTPUT', '.');

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

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

    $elapsed = round(microtime(true) - $start);
    $start   = microtime(true);

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

/**
 * Execute individual daily statistics queries
 *
 * @param string $sql The query to run
 * @return boolean success
 */
function stats_run_query($sql, $parameters) {
    global $DB;

    try {
        $DB->execute($sql, $parameters);
    } catch (dml_exception $e) {

       if (debugging('', DEBUG_ALL)) {
           mtrace($e->getMessage()); 
       }
       return false; 
    } 
    return true;
}

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

    $now = time();

    $fpcontext = context_course::instance(SITEID, MUST_EXIST);

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

    // calculate scheduled time
    $scheduledtime = stats_get_base_daily() + $CFG->statsruntimestarthour*60*60 + $CFG->statsruntimestartminute*60;

    // Note: This will work fine for sites running cron each 4 hours or less (hopefully, 99.99% of sites). MDL-16709
    // check to make sure we're due to run, at least 20 hours after last run
    if (isset($CFG->statslastexecution) && ((time() - 20*60*60) < $CFG->statslastexecution)) {
        mtrace("...preventing stats to run, last execution was less than 20 hours ago.");
        return false;
    // also check that we are a max of 4 hours after scheduled time, stats won't run after that
    } else if (time() > $scheduledtime + 4*60*60) {
        mtrace("...preventing stats to run, more than 4 hours since scheduled time.");
        return false;
    } else {
        set_config('statslastexecution', time()); /// Grab this execution as last one
    }

    $nextmidnight = stats_get_next_day_start($timestart);

    // are there any days that need to be processed?
    if ($now < $nextmidnight) {
        return true; // everything ok and up-to-date
    }


    $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;

    if (!set_cron_lock('statsrunning', $now + $timeout)) {
        return false;
    }

    // first delete entries that should not be there yet
    $DB->delete_records_select('stats_daily',      "timeend > $timestart");
    $DB->delete_records_select('stats_user_daily', "timeend > $timestart");

    // Read in a few things we'll use later
    $viewactions = stats_get_action_names('view');
    $postactions = stats_get_action_names('post');

    $guest           = (int)$CFG->siteguest;
    $guestrole       = (int)$CFG->guestroleid;
    $defaultfproleid = (int)$CFG->defaultfrontpageroleid;

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

    $days  = 0;
    $total = 0;
    $failed  = false; // failed stats flag
    $timeout = false;

    if (!stats_temp_table_create()) {
        $days = 1;
        $failed = true;
    }
    mtrace("Temporary tables created");

    $totalactiveusers = $DB->count_records('user', array('deleted' => '0'));

    while (!$failed && ($now > $nextmidnight)) {
        if ($days >= $maxdays) {
            $timeout = true;
            break;
        }

        $days++;
        @set_time_limit($timeout - 200);

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

        $daystart = time();

        stats_progress('init');

        if (!stats_temp_table_fill($timestart, $nextmidnight)) {
            $failed = true;
            break;
        }

        stats_progress('in');

        // Find out if any logs available for this day
        $sql = "SELECT 'x' FROM {temp_log1} l";
        $logspresent = $DB->get_records_sql($sql, null, 0, 1);

        // Calculate the number of active users today
        $sql = 'SELECT COUNT(DISTINCT u.id)
                  FROM {user} u
                  JOIN {temp_log1} l ON l.userid = u.id
                 WHERE u.deleted = 0';
        $dailyactiveusers = $DB->count_records_sql($sql);

        // Process login info first
        // Note: PostgreSQL doesn't like aliases in HAVING clauses
        $sql = "INSERT INTO {temp_stats_user_daily}
                            (stattype, timeend, courseid, userid, statsreads)

                SELECT 'logins', $nextmidnight AS timeend, ".SITEID." AS courseid,
                        userid, COUNT(id) AS statsreads
                  FROM {temp_log1} l
                 WHERE action = 'login'
              GROUP BY timeend, courseid, userid
                HAVING COUNT(id) > 0";

        if ($logspresent && !stats_run_query($sql)) {
            $failed = true;
            break;
        }

        stats_progress('1');

        $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'logins' AS stattype, $nextmidnight AS timeend, ".SITEID." AS courseid, 0,
                       COALESCE(SUM(statsreads), 0) as stat1, COUNT('x') as stat2
                  FROM {temp_stats_user_daily}
                 WHERE stattype = 'logins' AND timeend = $nextmidnight";

        if ($logspresent && !stats_run_query($sql)) {
            $failed = true;
            break;
        }
        stats_progress('2');


        // Enrolments and active enrolled users
        //
        // Unfortunately, we do not know how many users were registered
        // at given times in history :-(
        // - stat1: enrolled users
        // - stat2: enrolled users active in this period
        // - SITEID is special case here, because it's all about default enrolment
        //   in that case, we'll count non-deleted users.
        //

        $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'enrolments' AS stattype, $nextmidnight AS timeend, e.courseid, ra.roleid,
                        COUNT(DISTINCT ue.userid) AS stat1, 0 AS stat2
                  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)
              GROUP BY courseid, roleid";

        if (!stats_run_query($sql, array('courselevel'=>CONTEXT_COURSE))) {
            $failed = true;
            break;
        }
        stats_progress('3');

        // using table alias in UPDATE does not work in pg < 8.2
        $sql = "UPDATE {temp_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 = {temp_stats_daily}.roleid
                       AND e.courseid = {temp_stats_daily}.courseid
                       AND EXISTS (

                        SELECT 'x'
                          FROM {temp_log1} l
                         WHERE l.course = {temp_stats_daily}.courseid
                           AND l.userid = ra.userid
                                  )
                                )

                 WHERE {stats_daily}.stattype = 'enrolments'
                   AND {stats_daily}.timeend = $nextmidnight
                   AND {stats_daily}.courseid IN (

                      SELECT DISTINCT course FROM {temp_log2}
                                                 )";

        if (!stats_run_query($sql, array('courselevel'=>CONTEXT_COURSE))) {
            $failed = true;
            break;
        }
        stats_progress('4');

        // Now get course total enrolments (roleid==0) - except frontpage
        $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'enrolments', $nextmidnight AS timeend, e.courseid AS courseid, 0 AS roleid,
                       COUNT(DISTINCT userid) AS stat1, 0 AS stat2
                  FROM {enrol} e
                  JOIN {user_enrolments} ue ON ue.enrolid = e.id
              GROUP BY courseid
                HAVING COUNT(DISTINCT userid) > 0";

        if ($logspresent && !stats_run_query($sql)) {
            $failed = true;
            break;
        }
        stats_progress('5');

        $sql = "UPDATE {temp_stats_daily}
                   SET stat2 = (

                   SELECT COUNT(DISTINCT ue.userid)
                     FROM {enrol} e
                     JOIN {user_enrolments} ue ON ue.enrolid = e.id
                    WHERE e.courseid = {temp_stats_daily}.courseid
                      AND EXISTS (

                       SELECT 'x'
                         FROM {temp_log1} l
                        WHERE l.course = {temp_stats_daily}.courseid
                          AND l.userid = ue.userid
                                 )
                                )

                 WHERE {temp_stats_daily}.stattype = 'enrolments'
                   AND {temp_stats_daily}.timeend = $nextmidnight
                   AND {temp_stats_daily}.roleid = 0
                   AND {temp_stats_daily}.courseid IN (

                   SELECT l.course
                     FROM {temp_log2} l
                    WHERE l.course <> ".SITEID.")";

        if ($logspresent && !stats_run_query($sql, array())) {
            $failed = true;
            break;
        }
        stats_progress('6');

        // Frontpage(==site) enrolments total
        $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'enrolments', $nextmidnight, ".SITEID.", 0, $totalactiveusers AS stat1,
                       $dailyactiveusers AS stat2" .
                $DB->sql_null_from_clause();

        if ($logspresent && !stats_run_query($sql)) {
            $failed = true;
            break;
        }
        stats_progress('7');

        // Default frontpage role enrolments are all site users (not deleted)
        if ($defaultfproleid) {
            // first remove default frontpage role counts if created by previous query
            $sql = "DELETE
                      FROM {temp_stats_daily}
                     WHERE stattype = 'enrolments'
                       AND courseid = ".SITEID."
                       AND roleid = $defaultfproleid
                       AND timeend = $nextmidnight";

            if ($logspresent && !stats_run_query($sql)) {
                $failed = true;
                break;
            }
            stats_progress('8');

            $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)

                    SELECT 'enrolments', $nextmidnight, ".SITEID.", $defaultfproleid, 
                           $totalactiveusers AS stat1, $dailyactiveusers AS stat2" .
                    $DB->sql_null_from_clause();;

            if ($logspresent && !stats_run_query($sql)) {
                $failed = true;
                break;
            }
            stats_progress('9');

        } else {
            stats_progress('x');
            stats_progress('x');
        }


    /// individual user stats (including not-logged-in) in each course, this is slow - reuse this data if possible
        list($viewactionssql, $params1) = $DB->get_in_or_equal($viewactions, SQL_PARAMS_NAMED, 'view');
        list($postactionssql, $params2) = $DB->get_in_or_equal($postactions, SQL_PARAMS_NAMED, 'post');
        $sql = "INSERT INTO {temp_stats_user_daily} (stattype, timeend, courseid, userid, statsreads, statswrites)

                SELECT 'activity' AS stattype, $nextmidnight AS timeend, course AS courseid, userid,
                       SUM(CASE WHEN action $viewactionssql THEN 1 ELSE 0 END) AS statsreads,
                       SUM(CASE WHEN action $postactionssql THEN 1 ELSE 0 END) AS statswrites
                  FROM {temp_log1} l
                 WHERE !(course = 0 AND userid = 0)
              GROUP BY userid, courseid";

        if ($logspresent && !stats_run_query($sql, array_merge($params1, $params2))) {
            $failed = true;
            break;
        }
        stats_progress('10');


    /// how many view/post actions in each course total
        $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'activity' AS stattype, $nextmidnight AS timeend, c.id AS courseid, 0,
                       SUM(CASE WHEN l.action $viewactionssql THEN 1 ELSE 0 END) AS stat1,
                       SUM(CASE WHEN l.action $postactionssql THEN 1 ELSE 0 END) AS stat2
                  FROM {course} c, {temp_log1} l
                 WHERE l.course = c.id
              GROUP BY courseid";

        if ($logspresent && !stats_run_query($sql, array_merge($params1, $params2))) {
            $failed = true;
            break;
        }
        stats_progress('11');


    /// how many view actions for each course+role - excluding guests and frontpage

        $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'activity', $nextmidnight AS timeend, courseid, roleid, SUM(statsreads), SUM(statswrites)
                  FROM (

                    SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
                      FROM {temp_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 && !stats_run_query($sql, array('courselevel'=>CONTEXT_COURSE))) {
            $failed = true;
            break;
        }
        stats_progress('12');

    /// how many view actions from guests only in each course - excluding frontpage
    /// normal users may enter course with temporary guest access too

        $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'activity', $nextmidnight AS timeend, courseid, $guestrole AS roleid,
                       SUM(statsreads), SUM(statswrites)
                  FROM (

                    SELECT sud.courseid, sud.statsreads, sud.statswrites
                      FROM {temp_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, roleid
                HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";

        if ($logspresent && !stats_run_query($sql, array())) {
            $failed = true;
            break;
        }
        stats_progress('13');


    /// how many view actions for each role on frontpage - excluding guests, not-logged-in and default frontpage role
        $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'activity', $nextmidnight AS timeend, courseid, roleid,
                       SUM(statsreads), SUM(statswrites)
                  FROM (
                    SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
                      FROM {temp_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 && !stats_run_query($sql, array('fpcontext'=>$fpcontext->id))) {
            $failed = true;
            break;
        }
        stats_progress('14');


    /// how many view actions for default frontpage role on frontpage only
        $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'activity', timeend, courseid, $defaultfproleid AS roleid,
                       SUM(statsreads), SUM(statswrites)
                  FROM (
                    SELECT sud.timeend AS timeend, sud.courseid, sud.statsreads, sud.statswrites
                      FROM {temp_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, roleid
                HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";

        if ($logspresent && !stats_run_query($sql, array('fpcontext'=>$fpcontext->id, 'siteid'=>SITEID, 'nextm'=>$nextmidnight))) {
            $failed = true;
            break;
        }
        stats_progress('15');

    /// how many view actions for guests or not-logged-in on frontpage
        $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)

                SELECT 'activity', $nextmidnight AS timeend, ".SITEID." AS courseid, $guestrole AS roleid,
                       SUM(statsreads), SUM(statswrites)
                  FROM (

                    SELECT sud.statsreads, sud.statswrites
                      FROM {temp_stats_user_daily} sud
                     WHERE (sud.userid = $guest OR sud.userid = 0)
                       AND sud.timeend = $nextmidnight
                       AND sud.courseid = ".SITEID."
                       AND sud.stattype='activity'
                        ) inline_view

              GROUP BY timeend, courseid, roleid
                HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";

        if ($logspresent && !stats_run_query($sql)) {
            $failed = true;
            break;
        }
        stats_progress('16');

        stats_temp_table_clean();

        stats_progress('out');

        // remember processed days
        set_config('statslastdaily', $nextmidnight);
        $elapsed = time()-$daystart;
        mtrace("  finished until $nextmidnight: ".userdate($nextmidnight)." (in $elapsed s)");
        $total += $elapsed;

        $timestart    = $nextmidnight;
        $nextmidnight = stats_get_next_day_start($nextmidnight);
    }

    stats_temp_table_drop();

    set_cron_lock('statsrunning', null);

    if ($failed) {
        $days--;
        mtrace("...error occurred, completed $days days of statistics.");
        return false;

    } else if ($timeout) {
        mtrace("...stopping early, reached maximum number of $maxdays days - will continue next time.");
        return false;

    } else {
        mtrace("...completed $days days of statistics in {$total} s.");
        return true;
    }
}


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

    $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
    $DB->delete_records_select('stats_weekly',      "timeend > $timestart");
    $DB->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";

        $weekstart = time();
        stats_progress('init');

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

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

        $DB->execute($sql);

        stats_progress('1');

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

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

        $DB->execute($sql);

        stats_progress('2');

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

                SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . "
                  FROM (
                           SELECT $nextstartweek AS ntimeend, courseid, roleid, stat1, stat2
                             FROM {stats_daily} sd
                            WHERE stattype = 'enrolments' AND $stattimesql
                       ) inline_view
              GROUP BY ntimeend, courseid, roleid";

        $DB->execute($sql);

        stats_progress('3');

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

                SELECT 'activity', ntimeend, courseid, roleid, SUM(stat1), SUM(stat2)
                  FROM (
                           SELECT $nextstartweek AS ntimeend, courseid, roleid, stat1, stat2
                             FROM {stats_daily}
                            WHERE stattype = 'activity' AND $stattimesql
                       ) inline_view
              GROUP BY ntimeend, courseid, roleid";

        $DB->execute($sql);

        stats_progress('4');

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

                SELECT 'activity', ntimeend, courseid, userid, SUM(statsreads), SUM(statswrites)
                  FROM (
                           SELECT $nextstartweek AS ntimeend, courseid, userid, statsreads, statswrites
                             FROM {stats_user_daily}
                            WHERE stattype = 'activity' AND $stattimesql
                       ) inline_view
              GROUP BY ntimeend, courseid, userid";

        $DB->execute($sql);

        stats_progress('5');

        set_config('statslastweekly', $nextstartweek);
        $elapsed = time()-$weekstart;
        mtrace(" finished until $nextstartweek: ".userdate($nextstartweek) ." ( in $elapsed s)");

        $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, $DB;

    $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
    $DB->delete_records_select('stats_monthly', "timeend > $timestart");
    $DB->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";

        $monthstart = time();
        stats_progress('init');

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

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

        $DB->execute($sql);

        stats_progress('1');

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

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

        $DB->execute($sql);

        stats_progress('2');

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

                SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . "
                  FROM (
                           SELECT $nextstartmonth AS ntimeend, courseid, roleid, stat1, stat2
                             FROM {stats_daily} sd
                            WHERE stattype = 'enrolments' AND $stattimesql
                       ) inline_view
              GROUP BY ntimeend, courseid, roleid";

        $DB->execute($sql);

        stats_progress('3');

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

                SELECT 'activity', ntimeend, courseid, roleid, SUM(stat1), SUM(stat2)
                  FROM (
                           SELECT $nextstartmonth AS ntimeend, courseid, roleid, stat1, stat2
                             FROM {stats_daily}
                            WHERE stattype = 'activity' AND $stattimesql
                       ) inline_view
              GROUP BY ntimeend, courseid, roleid";

        $DB->execute($sql);

        stats_progress('4');

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

                SELECT 'activity', ntimeend, courseid, userid, SUM(statsreads), SUM(statswrites)
                  FROM (
                           SELECT $nextstartmonth AS ntimeend, courseid, userid, statsreads, statswrites
                             FROM {stats_user_daily}
                            WHERE stattype = 'activity' AND $stattimesql
                       ) inline_view
              GROUP BY ntimeend, courseid, userid";

        $DB->execute($sql);

        stats_progress('5');

        set_config('statslastmonthly', $nextstartmonth);
        $elapsed = time() - $monthstart;
        mtrace(" finished until $nextstartmonth: ".userdate($nextstartmonth) ." (in $elapsed s)");

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

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

/**
 * 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, $DB;

    // are there any data in stats table? Should not be...
    if ($timeend = $DB->get_field_sql('SELECT MAX(timeend) FROM {stats_'.$str.'}')) {
        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 = $DB->get_field_sql('SELECT MIN(time) FROM {log}')) {
                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() {
    global $DB;
    mtrace("Running stats cleanup tasks...");
    $deletebefore =  stats_get_base_monthly();

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

    // delete weeklies older than 9  months (to be safe)
    $deletebefore = strtotime('-6 months', $deletebefore);
    $DB->delete_records_select('stats_weekly',      "timeend < $deletebefore");
    $DB->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 stdClass();
    $param->params = array();

    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 = '';

    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 = $DB->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 = $DB->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 = $DB->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 = $DB->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 = $DB->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 = $DB->get_field('role','name', array('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, sum(stat1+stat2) AS all_activity
                              FROM {stats_'.$param->table.'}
                             WHERE stattype=\'activity\' AND timeend >= '.(int)$param->timeafter.' AND roleid = 0 GROUP BY courseid
                       ) activity
                       INNER JOIN
                            (
                            SELECT courseid, max(stat1) AS highest_enrolments
                              FROM {stats_'.$param->table.'}
                             WHERE stattype=\'enrolments\' AND timeend >= '.(int)$param->timeafter.' AND stat1 > '.(int)$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, ' . $DB->sql_ceil('avg(all_enrolments)') . ' as line1, ' .
                         $DB->sql_ceil('avg(active_enrolments)') . ' as line2, avg(proportion_active) AS line3
                       FROM (
                           SELECT courseid, timeend, stat2 as active_enrolments,
                                  stat1 as all_enrolments, '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' AS proportion_active
                             FROM {stats_'.$param->table.'}
                            WHERE stattype=\'enrolments\' AND roleid = 0 AND stat1 > '.(int)$threshold.'
                       ) aq
                       WHERE timeend >= '.(int)$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,
                                  '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' as proportion_active
                             FROM {stats_'.$param->table.'}
                            WHERE stattype=\'activity\' AND roleid = 0 AND stat1 > 0
                       ) aq
                       WHERE timeend >= '.(int)$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, $DB;

    $mods = $DB->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)) {
            $mod_actions = $function();
            if (is_array($mod_actions)) {
                $actions = array_merge($actions, $mod_actions);
            }
        }
    }

    // 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];
    }
    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');
    }

    $years = (int)date('y', $now) - (int)date('y', $earliestmonth);
    if ($years > 1) {
        for($i = 2; $i <= $years; $i++) {
            $timeoptions[$i*12+20] = get_string('numyears', 'moodle', $i);
        }
    }

    return $timeoptions;
}

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

    $reportoptions = array();

    switch ($mode) {
    case STATS_MODE_GENERAL:
        $reportoptions[STATS_REPORT_ACTIVITY] = get_string('statsreport'.STATS_REPORT_ACTIVITY);
        if ($courseid != SITEID && $context = context_course::instance($courseid)) {
            $sql = 'SELECT r.id, r.name FROM {role} r JOIN {stats_daily} s ON s.roleid = r.id WHERE s.courseid = :courseid GROUP BY r.id, r.name';
            if ($roles = $DB->get_records_sql($sql, array('courseid' => $courseid))) {
                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('report/stats:view', context_system::instance())) {
            $site = get_site();
            $reportoptions[STATS_REPORT_USER_LOGINS] = get_string('statsreport'.STATS_REPORT_USER_LOGINS);
        }
        break;
    case STATS_MODE_RANKED:
        if (has_capability('report/stats:view', context_system::instance())) {
            $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, $DB;

    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 stdClass();
    $a->daysdone = $DB->get_field_sql("SELECT COUNT(DISTINCT(timeend)) FROM {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);
}

/**
 * Create temporary tables to speed up log generation
 */
function stats_temp_table_create() {
    global $CFG, $DB;

    $dbman = $DB->get_manager(); // We are going to use database_manager services

    stats_temp_table_drop();

    $log = new xmldb_table('temp_log1');

    $log->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null);
    $log->add_field('userid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null);
    $log->add_field('course', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, null);
    $log->add_field('action', XMLDB_TYPE_CHAR, '40', null, XMLDB_NOTNULL, null, null);

    $log->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
    $log->add_index('temp_tl_course_ix', XMLDB_INDEX_NOTUNIQUE, array('course'));
    $log->add_index('temp_tl_act_ix', XMLDB_INDEX_NOTUNIQUE, array('action'));
    $log->add_index('temp_tl_user_ix', XMLDB_INDEX_NOTUNIQUE, array('userid'));
    $log->add_index('temp_tl_usecouact_ix', XMLDB_INDEX_NOTUNIQUE, array('userid','course','action'));

    $user = new xmldb_table('temp_stats_daily');
    $user->add_field('courseid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, 0);
    $user->add_field('roleid',   XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, 0);
    $user->add_field('stattype', XMLDB_TYPE_CHAR,    '20', null,           XMLDB_NOTNULL, null, 'activity');
    $user->add_field('timeend',  XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, 0);
    $user->add_field('stat1',    XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, 0);
    $user->add_field('stat2',    XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, 0);
 
    $user->add_index('temp_tsd_courseid_ix', XMLDB_INDEX_NOTUNIQUE, array('courseid'));
    $user->add_index('temp_tsd_roleid_ix',   XMLDB_INDEX_NOTUNIQUE, array('roleid'));
    $user->add_index('temp_tsd_statype_ix',  XMLDB_INDEX_NOTUNIQUE, array('stattype'));
    $user->add_index('temp_tsd_timeend_ix',  XMLDB_INDEX_NOTUNIQUE, array('timeend'));

    $daily = new xmldb_table('temp_stats_user_daily');
    $daily->add_field('courseid',    XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, 0);
    $daily->add_field('userid',      XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, 0);
    $daily->add_field('roleid',      XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, 0);
    $daily->add_field('timeend',     XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, 0);
    $daily->add_field('statsreads',  XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, 0);
    $daily->add_field('statswrites', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, null, 0);
    $daily->add_field('stattype',    XMLDB_TYPE_CHAR,    '30', null,           XMLDB_NOTNULL, null, null);

    $daily->add_index('temp_tsud_courseid_ix', XMLDB_INDEX_NOTUNIQUE, array('courseid'));
    $daily->add_index('temp_tsud_userid_ix',   XMLDB_INDEX_NOTUNIQUE, array('userid'));
    $daily->add_index('temp_tsud_roleid_ix',   XMLDB_INDEX_NOTUNIQUE, array('roleid'));
    $daily->add_index('temp_tsud_stattype_ix', XMLDB_INDEX_NOTUNIQUE, array('stattype'));
    $daily->add_index('temp_tsud_timeend_ix',  XMLDB_INDEX_NOTUNIQUE, array('timeend'));

    try {
        $dbman->create_temp_table($log);

        $log->name = 'temp_log2';

        $dbman->create_temp_table($log);

        $dbman->create_temp_table($user);

        $dbman->create_temp_table($daily);

    } catch (Exception $e) {
        mtrace("Temporary table creation failed!");
        return false;
    }

    return true;
}

/**
 * Deletes summary logs table for stats calculation
 */
function stats_temp_table_drop() {
    global $DB;

    $dbman = $DB->get_manager();

    $tables = array('temp_log1', 'temp_log2', 'temp_stats_daily', 'temp_stats_user_daily');

    foreach ($tables as $name) {

        if ($dbman->table_exists($name)) {
            $table = new xmldb_table($name);

            try {
                $dbman->drop_temp_table($table);
            } catch (Exception $e) {
                mtrace("Error occured while dropping temporary tables!");
            }
        }
    }
}

/**
 * Fills the temporary stats tables with new data
 *
 * @param timestart timestamp of the start time of logs view
 * @param timeend timestamp of the end time of logs view
 * @returns boolen success (true) or failure(false)
 */
function stats_temp_table_fill($timestart, $timeend) {
    global $DB;

    $sql = 'INSERT INTO {temp_log1} (userid, course, action)

            SELECT userid, course, action FROM {log} l
             WHERE l.time >= ? AND l.time < ?';

    $DB->execute($sql, array($timestart, $timeend));

    $sql = 'INSERT INTO {temp_log2} (userid, course, action)

            SELECT userid, course, action FROM {temp_log1}';

    $DB->execute($sql);

    return true;
}


/**
 * Deletes summary logs table for stats calculation
 *
 * @returns boolen success (true) or failure(false)
 */
function stats_temp_table_clean() {
    global $DB;

    $sql = array();

    $sql['up1'] = 'INSERT INTO {stats_daily} (courseid, roleid, stattype, timeend, stat1, stat2)

                   SELECT courseid, roleid, stattype, timeend, stat1, stat2 FROM {temp_stats_daily}';

    $sql['up2'] = 'INSERT INTO {stats_user_daily}
                               (courseid, userid, roleid, timeend, statsreads, statswrites, stattype)

                   SELECT courseid, userid, roleid, timeend, statsreads, statswrites, stattype
                     FROM {temp_stats_user_daily}';

    foreach ($sql as $id => $query) {
        try {
            $DB->execute($query);
        } catch (Exception $e) {
            mtrace("Error during table cleanup!");
            return false;
        }
    }

    $tables = array('temp_log1', 'temp_log2', 'temp_stats_daily', 'temp_stats_user_daily');

    foreach ($tables as $name) {
        $DB->delete_records($name);
    }

    return true;
}