lib/statslib.php

   1 <?php
   2
   3 // This file is part of Moodle - http://moodle.org/
   4 //
   5 // Moodle is free software: you can redistribute it and/or modify
   6 // it under the terms of the GNU General Public License as published by
   7 // the Free Software Foundation, either version 3 of the License, or
   8 // (at your option) any later version.
   9 //
  10 // Moodle is distributed in the hope that it will be useful,
  11 // but WITHOUT ANY WARRANTY; without even the implied warranty of
  12 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13 // GNU General Public License for more details.
  14 //
  15 // You should have received a copy of the GNU General Public License
  16 // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  17
  18 /**
  19  * @package    core
  20  * @subpackage stats
  21  * @copyright  1999 onwards Martin Dougiamas  {@link http://moodle.com}
  22  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  23  */
  24
  25 defined('MOODLE_INTERNAL') || die();
  26
  27 /** THESE CONSTANTS ARE USED FOR THE REPORTING PAGE. */
  28
  29 define('STATS_REPORT_LOGINS',1); // double impose logins and unique logins on a line graph. site course only.
  30 define('STATS_REPORT_READS',2); // double impose student reads and teacher reads on a line graph.
  31 define('STATS_REPORT_WRITES',3); // double impose student writes and teacher writes on a line graph.
  32 define('STATS_REPORT_ACTIVITY',4); // 2+3 added up, teacher vs student.
  33 define('STATS_REPORT_ACTIVITYBYROLE',5); // all activity, reads vs writes, selected by role.
  34
  35 // user level stats reports.
  36 define('STATS_REPORT_USER_ACTIVITY',7);
  37 define('STATS_REPORT_USER_ALLACTIVITY',8);
  38 define('STATS_REPORT_USER_LOGINS',9);
  39 define('STATS_REPORT_USER_VIEW',10);  // this is the report you see on the user profile.
  40
  41 // admin only ranking stats reports
  42 define('STATS_REPORT_ACTIVE_COURSES',11);
  43 define('STATS_REPORT_ACTIVE_COURSES_WEIGHTED',12);
  44 define('STATS_REPORT_PARTICIPATORY_COURSES',13);
  45 define('STATS_REPORT_PARTICIPATORY_COURSES_RW',14);
  46
  47 // start after 0 = show dailies.
  48 define('STATS_TIME_LASTWEEK',1);
  49 define('STATS_TIME_LAST2WEEKS',2);
  50 define('STATS_TIME_LAST3WEEKS',3);
  51 define('STATS_TIME_LAST4WEEKS',4);
  52
  53 // start after 10 = show weeklies
  54 define('STATS_TIME_LAST2MONTHS',12);
  55
  56 define('STATS_TIME_LAST3MONTHS',13);
  57 define('STATS_TIME_LAST4MONTHS',14);
  58 define('STATS_TIME_LAST5MONTHS',15);
  59 define('STATS_TIME_LAST6MONTHS',16);
  60
  61 // start after 20 = show monthlies
  62 define('STATS_TIME_LAST7MONTHS',27);
  63 define('STATS_TIME_LAST8MONTHS',28);
  64 define('STATS_TIME_LAST9MONTHS',29);
  65 define('STATS_TIME_LAST10MONTHS',30);
  66 define('STATS_TIME_LAST11MONTHS',31);
  67 define('STATS_TIME_LASTYEAR',32);
  68
  69 // different modes for what reports to offer
  70 define('STATS_MODE_GENERAL',1);
  71 define('STATS_MODE_DETAILED',2);
  72 define('STATS_MODE_RANKED',3); // admins only - ranks courses
  73
  74 /**
  75  * Print daily cron progress
  76  * @param string $ident
  77  */
  78 function stats_daily_progress($ident) {
  79     static $start = 0;
  80     static $init  = 0;
  81
  82     if ($ident == 'init') {
  83         $init = $start = time();
  84         return;
  85     }
  86
  87     $elapsed = time() - $start;
  88     $start   = time();
  89
  90     if (debugging('', DEBUG_ALL)) {
  91         mtrace("$ident:$elapsed ", '');
  92     } else {
  93         mtrace('.', '');
  94     }
  95 }
  96
  97 /**
  98  * Execute daily statistics gathering
  99  * @param int $maxdays maximum number of days to be processed
 100  * @return boolean success
 101  */
 102 function stats_cron_daily($maxdays=1) {
 103     global $CFG, $DB;
 104
 105     $now = time();
 106
 107     $fpcontext = get_context_instance(CONTEXT_COURSE, SITEID, MUST_EXIST);
 108
 109     // read last execution date from db
 110     if (!$timestart = get_config(NULL, 'statslastdaily')) {
 111         $timestart = stats_get_base_daily(stats_get_start_from('daily'));
 112         set_config('statslastdaily', $timestart);
 113     }
 114
 115     // calculate scheduled time
 116     $scheduledtime = stats_get_base_daily() + $CFG->statsruntimestarthour*60*60 + $CFG->statsruntimestartminute*60;
 117
 118     // Note: This will work fine for sites running cron each 4 hours or less (hopefully, 99.99% of sites). MDL-16709
 119     // check to make sure we're due to run, at least 20 hours after last run
 120     if (isset($CFG->statslastexecution) and ((time() - 20*60*60) < $CFG->statslastexecution)) {
 121         mtrace("...preventing stats to run, last execution was less than 20 hours ago.");
 122         return false;
 123     // also check that we are a max of 4 hours after scheduled time, stats won't run after that
 124     } else if (time() > $scheduledtime + 4*60*60) {
 125         mtrace("...preventing stats to run, more than 4 hours since scheduled time.");
 126         return false;
 127     } else {
 128         set_config('statslastexecution', time()); /// Grab this execution as last one
 129     }
 130
 131     $nextmidnight = stats_get_next_day_start($timestart);
 132
 133     // are there any days that need to be processed?
 134     if ($now < $nextmidnight) {
 135         return true; // everything ok and up-to-date
 136     }
 137
 138
 139     $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
 140
 141     if (!set_cron_lock('statsrunning', $now + $timeout)) {
 142         return false;
 143     }
 144
 145     // first delete entries that should not be there yet
 146     $DB->delete_records_select('stats_daily',      "timeend > $timestart");
 147     $DB->delete_records_select('stats_user_daily', "timeend > $timestart");
 148
 149     // Read in a few things we'll use later
 150     $viewactions = stats_get_action_names('view');
 151     $postactions = stats_get_action_names('post');
 152
 153     $guest           = (int)$CFG->siteguest;
 154     $guestrole       = (int)$CFG->guestroleid;
 155     $defaultfproleid = (int)$CFG->defaultfrontpageroleid;
 156
 157     mtrace("Running daily statistics gathering, starting at $timestart:");
 158
 159     $days = 0;
 160     $failed = false; // failed stats flag
 161
 162     while ($now > $nextmidnight) {
 163         if ($days >= $maxdays) {
 164             mtrace("...stopping early, reached maximum number of $maxdays days - will continue next time.");
 165             set_cron_lock('statsrunning', null);
 166             return false;
 167         }
 168
 169         $days++;
 170         @set_time_limit($timeout - 200);
 171
 172         if ($days > 1) {
 173             // move the lock
 174             set_cron_lock('statsrunning', time() + $timeout, true);
 175         }
 176
 177         $daystart = time();
 178
 179         $timesql  = "l.time >= $timestart  AND l.time  < $nextmidnight";
 180         $timesql1 = "l1.time >= $timestart AND l1.time < $nextmidnight";
 181         $timesql2 = "l2.time >= $timestart AND l2.time < $nextmidnight";
 182
 183         stats_daily_progress('init');
 184
 185
 186     /// find out if any logs available for this day
 187         $sql = "SELECT 'x'
 188                   FROM {log} l
 189                  WHERE $timesql";
 190         $logspresent = $DB->get_records_sql($sql, null, 0, 1);
 191
 192     /// process login info first
 193         $sql = "INSERT INTO {stats_user_daily} (stattype, timeend, courseid, userid, statsreads)
 194
 195                 SELECT 'logins', timeend, courseid, userid, count(statsreads)
 196                  FROM (
 197                           SELECT $nextmidnight AS timeend, ".SITEID." AS courseid, l.userid, l.id AS statsreads
 198                             FROM {log} l
 199                            WHERE action = 'login' AND $timesql
 200                        ) inline_view
 201               GROUP BY timeend, courseid, userid
 202                 HAVING count(statsreads) > 0";
 203
 204         if ($logspresent and !$DB->execute($sql)) {
 205             $failed = true;
 206             break;
 207         }
 208         stats_daily_progress('1');
 209
 210         $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 211
 212                 SELECT 'logins' AS stattype, $nextmidnight AS timeend, ".SITEID." as courseid, 0,
 213                        COALESCE((SELECT SUM(statsreads)
 214                                        FROM {stats_user_daily} s1
 215                                       WHERE s1.stattype = 'logins' AND timeend = $nextmidnight), 0) AS stat1,
 216                        (SELECT COUNT('x')
 217                           FROM {stats_user_daily} s2
 218                          WHERE s2.stattype = 'logins' AND timeend = $nextmidnight) AS stat2" .
 219                 $DB->sql_null_from_clause();
 220
 221         if ($logspresent and !$DB->execute($sql)) {
 222             $failed = true;
 223             break;
 224         }
 225         stats_daily_progress('2');
 226
 227
 228         // Enrolments and active enrolled users
 229         //
 230         // Unfortunately, we do not know how many users were registered
 231         // at given times in history :-(
 232         // - stat1: enrolled users
 233         // - stat2: enrolled users active in this period
 234         // - SITEID is special case here, because it's all about default enrolment
 235         //   in that case, we'll count non-deleted users.
 236         //
 237
 238         $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 239
 240                 SELECT 'enrolments', timeend, courseid, roleid, COUNT(DISTINCT userid), 0
 241                   FROM (
 242                            SELECT $nextmidnight AS timeend, e.courseid, ra.roleid, ue.userid
 243                              FROM {role_assignments} ra
 244                              JOIN {context} c ON (c.id = ra.contextid AND c.contextlevel = :courselevel)
 245                              JOIN {enrol} e ON e.courseid = c.instanceid
 246                              JOIN {user_enrolments} ue ON (ue.enrolid = e.id AND ue.userid = ra.userid)
 247                         ) inline_view
 248               GROUP BY timeend, courseid, roleid";
 249
 250         if (!$DB->execute($sql, array('courselevel'=>CONTEXT_COURSE))) {
 251             $failed = true;
 252             break;
 253         }
 254         stats_daily_progress('3');
 255
 256         // using table alias in UPDATE does not work in pg < 8.2
 257         $sql = "UPDATE {stats_daily}
 258                    SET stat2 = (SELECT COUNT(DISTINCT ra.userid)
 259                                   FROM {role_assignments} ra
 260                                   JOIN {context} c ON (c.id = ra.contextid AND c.contextlevel = :courselevel)
 261                                   JOIN {enrol} e ON e.courseid = c.instanceid
 262                                   JOIN {user_enrolments} ue ON (ue.enrolid = e.id AND ue.userid = ra.userid)
 263                                   WHERE ra.roleid = {stats_daily}.roleid AND
 264                                        e.courseid = {stats_daily}.courseid AND
 265                                        EXISTS (SELECT 'x'
 266                                                  FROM {log} l
 267                                                 WHERE l.course = {stats_daily}.courseid AND
 268                                                       l.userid = ra.userid AND $timesql))
 269                  WHERE {stats_daily}.stattype = 'enrolments' AND
 270                        {stats_daily}.timeend = $nextmidnight AND
 271                        {stats_daily}.courseid IN
 272                           (SELECT DISTINCT l.course
 273                              FROM {log} l
 274                             WHERE $timesql)";
 275
 276         if (!$DB->execute($sql, array('courselevel'=>CONTEXT_COURSE))) {
 277             $failed = true;
 278             break;
 279         }
 280         stats_daily_progress('4');
 281
 282     /// now get course total enrolments (roleid==0) - except frontpage
 283         $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 284
 285                 SELECT 'enrolments', timeend, id, nroleid, COUNT(DISTINCT userid), 0
 286                   FROM (
 287                            SELECT $nextmidnight AS timeend, e.courseid AS id, 0 AS nroleid, ue.userid
 288                              FROM {enrol} e
 289                              JOIN {user_enrolments} ue ON ue.enrolid = e.id
 290                        ) inline_view
 291               GROUP BY timeend, id, nroleid
 292                 HAVING COUNT(DISTINCT userid) > 0";
 293
 294         if ($logspresent and !$DB->execute($sql)) {
 295             $failed = true;
 296             break;
 297         }
 298         stats_daily_progress('5');
 299
 300         $sql = "UPDATE {stats_daily}
 301                    SET stat2 = (SELECT COUNT(DISTINCT ue.userid)
 302                                   FROM {enrol} e
 303                                   JOIN {user_enrolments} ue ON ue.enrolid = e.id
 304                                  WHERE e.courseid = {stats_daily}.courseid AND
 305                                        EXISTS (SELECT 'x'
 306                                                  FROM {log} l
 307                                                 WHERE l.course = {stats_daily}.courseid AND
 308                                                       l.userid = ue.userid AND $timesql))
 309                  WHERE {stats_daily}.stattype = 'enrolments' AND
 310                        {stats_daily}.timeend = $nextmidnight AND
 311                        {stats_daily}.roleid = 0 AND
 312                        {stats_daily}.courseid IN
 313                           (SELECT l.course
 314                              FROM {log} l
 315                             WHERE $timesql AND l.course <> ".SITEID.")";
 316
 317         if ($logspresent and !$DB->execute($sql, array())) {
 318             $failed = true;
 319             break;
 320         }
 321         stats_daily_progress('6');
 322
 323     /// frontapge(==site) enrolments total
 324         $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 325
 326                 SELECT 'enrolments', $nextmidnight, ".SITEID.", 0,
 327                        (SELECT COUNT('x')
 328                           FROM {user} u
 329                          WHERE u.deleted = 0) AS stat1,
 330                        (SELECT COUNT(DISTINCT u.id)
 331                           FROM {user} u
 332                                JOIN {log} l ON l.userid = u.id
 333                          WHERE u.deleted = 0 AND $timesql) AS stat2" .
 334                 $DB->sql_null_from_clause();
 335
 336         if ($logspresent and !$DB->execute($sql)) {
 337             $failed = true;
 338             break;
 339         }
 340         stats_daily_progress('7');
 341
 342     /// Default frontpage role enrolments are all site users (not deleted)
 343         if ($defaultfproleid) {
 344             // first remove default frontpage role counts if created by previous query
 345             $sql = "DELETE
 346                       FROM {stats_daily}
 347                      WHERE stattype = 'enrolments' AND courseid = ".SITEID." AND
 348                            roleid = $defaultfproleid AND timeend = $nextmidnight";
 349             if ($logspresent and !$DB->execute($sql)) {
 350                 $failed = true;
 351                 break;
 352             }
 353             stats_daily_progress('8');
 354
 355             $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 356
 357                     SELECT 'enrolments', $nextmidnight, ".SITEID.", $defaultfproleid,
 358                            (SELECT COUNT('x')
 359                               FROM {user} u
 360                              WHERE u.deleted = 0) AS stat1,
 361                            (SELECT COUNT(DISTINCT u.id)
 362                               FROM {user} u
 363                                    JOIN {log} l ON l.userid = u.id
 364                              WHERE u.deleted = 0 AND $timesql) AS stat2" .
 365                     $DB->sql_null_from_clause();;
 366
 367             if ($logspresent and !$DB->execute($sql)) {
 368                 $failed = true;
 369                 break;
 370             }
 371             stats_daily_progress('9');
 372
 373         } else {
 374             stats_daily_progress('x');
 375             stats_daily_progress('x');
 376         }
 377
 378
 379
 380     /// individual user stats (including not-logged-in) in each course, this is slow - reuse this data if possible
 381         list($viewactionssql, $params1) = $DB->get_in_or_equal($viewactions, SQL_PARAMS_NAMED, 'view');
 382         list($postactionssql, $params2) = $DB->get_in_or_equal($postactions, SQL_PARAMS_NAMED, 'post');
 383         $sql = "INSERT INTO {stats_user_daily} (stattype, timeend, courseid, userid, statsreads, statswrites)
 384
 385                 SELECT 'activity' AS stattype, $nextmidnight AS timeend, d.courseid, d.userid,
 386                        (SELECT COUNT('x')
 387                           FROM {log} l
 388                          WHERE l.userid = d.userid AND
 389                                l.course = d.courseid AND $timesql AND
 390                                l.action $viewactionssql) AS statsreads,
 391                        (SELECT COUNT('x')
 392                           FROM {log} l
 393                          WHERE l.userid = d.userid AND
 394                                l.course = d.courseid AND $timesql AND
 395                                l.action $postactionssql) AS statswrites
 396                   FROM (SELECT DISTINCT u.id AS userid, l.course AS courseid
 397                           FROM {user} u, {log} l
 398                          WHERE u.id = l.userid AND $timesql
 399                        UNION
 400                         SELECT 0 AS userid, ".SITEID." AS courseid" . $DB->sql_null_from_clause() . ") d";
 401                         // can not use group by here because pg can not handle it :-(
 402
 403         if ($logspresent and !$DB->execute($sql, array_merge($params1, $params2))) {
 404             $failed = true;
 405             break;
 406         }
 407         stats_daily_progress('10');
 408
 409
 410     /// how many view/post actions in each course total
 411         $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 412
 413                 SELECT 'activity' AS stattype, $nextmidnight AS timeend, c.id AS courseid, 0,
 414                        (SELECT COUNT('x')
 415                           FROM {log} l1
 416                          WHERE l1.course = c.id AND l1.action $viewactionssql AND
 417                                $timesql1) AS stat1,
 418                        (SELECT COUNT('x')
 419                           FROM {log} l2
 420                          WHERE l2.course = c.id AND l2.action $postactionssql AND
 421                                $timesql2) AS stat2
 422                   FROM {course} c
 423                  WHERE EXISTS (SELECT 'x'
 424                                  FROM {log} l
 425                                 WHERE l.course = c.id and $timesql)";
 426
 427         if ($logspresent and !$DB->execute($sql, array_merge($params1, $params2))) {
 428             $failed = true;
 429             break;
 430         }
 431         stats_daily_progress('11');
 432
 433
 434     /// how many view actions for each course+role - excluding guests and frontpage
 435
 436         $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 437
 438                 SELECT 'activity', timeend, courseid, roleid, SUM(statsreads), SUM(statswrites)
 439                   FROM (
 440                            SELECT $nextmidnight AS timeend, pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
 441                              FROM {stats_user_daily} sud,
 442                                       (SELECT DISTINCT ra.userid, ra.roleid, e.courseid
 443                                          FROM {role_assignments} ra
 444                                          JOIN {context} c ON (c.id = ra.contextid AND c.contextlevel = :courselevel)
 445                                          JOIN {enrol} e ON e.courseid = c.instanceid
 446                                          JOIN {user_enrolments} ue ON (ue.enrolid = e.id AND ue.userid = ra.userid)
 447                                         WHERE ra.roleid <> $guestrole AND
 448                                               ra.userid <> $guest
 449                                       ) pl
 450                             WHERE sud.userid = pl.userid AND
 451                                   sud.courseid = pl.courseid AND
 452                                   sud.timeend = $nextmidnight AND
 453                                   sud.stattype='activity'
 454                        ) inline_view
 455               GROUP BY timeend, courseid, roleid
 456                 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
 457
 458         if ($logspresent and !$DB->execute($sql, array('courselevel'=>CONTEXT_COURSE))) {
 459             $failed = true;
 460             break;
 461         }
 462         stats_daily_progress('12');
 463
 464     /// how many view actions from guests only in each course - excluding frontpage
 465     /// normal users may enter course with temporary guest access too
 466
 467         $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 468
 469                 SELECT 'activity', timeend, courseid, nroleid, SUM(statsreads), SUM(statswrites)
 470                   FROM (
 471                            SELECT $nextmidnight AS timeend, sud.courseid, $guestrole AS nroleid, sud.statsreads, sud.statswrites
 472                              FROM {stats_user_daily} sud
 473                             WHERE sud.timeend = $nextmidnight AND sud.courseid <> ".SITEID." AND
 474                                   sud.stattype='activity' AND
 475                                   (sud.userid = $guest OR sud.userid
 476                                     NOT IN (SELECT ue.userid
 477                                               FROM {user_enrolments} ue
 478                                               JOIN {enrol} e ON ue.enrolid = e.id
 479                                              WHERE e.courseid = sud.courseid))
 480                        ) inline_view
 481               GROUP BY timeend, courseid, nroleid
 482                 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
 483
 484         if ($logspresent and !$DB->execute($sql, array())) {
 485             $failed = true;
 486             break;
 487         }
 488         stats_daily_progress('13');
 489
 490
 491     /// how many view actions for each role on frontpage - excluding guests, not-logged-in and default frontpage role
 492         $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 493
 494                 SELECT 'activity', timeend, courseid, roleid, SUM(statsreads), SUM(statswrites)
 495                   FROM (
 496                            SELECT $nextmidnight AS timeend, pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
 497                              FROM {stats_user_daily} sud,
 498                                       (SELECT DISTINCT ra.userid, ra.roleid, c.instanceid AS courseid
 499                                          FROM {role_assignments} ra
 500                                          JOIN {context} c ON c.id = ra.contextid
 501                                         WHERE ra.contextid = :fpcontext AND
 502                                               ra.roleid <> $defaultfproleid AND
 503                                               ra.roleid <> $guestrole AND
 504                                               ra.userid <> $guest
 505                                       ) pl
 506                             WHERE sud.userid = pl.userid AND
 507                                   sud.courseid = pl.courseid AND
 508                                   sud.timeend = $nextmidnight AND
 509                                   sud.stattype='activity'
 510                        ) inline_view
 511               GROUP BY timeend, courseid, roleid
 512                 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
 513
 514         if ($logspresent and !$DB->execute($sql, array('fpcontext'=>$fpcontext->id))) {
 515             $failed = true;
 516             break;
 517         }
 518         stats_daily_progress('14');
 519
 520
 521     /// how many view actions for default frontpage role on frontpage only
 522         $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 523
 524                 SELECT 'activity', timeend, courseid, nroleid, SUM(statsreads), SUM(statswrites)
 525                   FROM (
 526                            SELECT sud.timeend AS timeend, sud.courseid, $defaultfproleid AS nroleid, sud.statsreads, sud.statswrites
 527                              FROM {stats_user_daily} sud
 528                             WHERE sud.timeend = :nextm AND sud.courseid = :siteid AND
 529                                   sud.stattype='activity' AND
 530                                   sud.userid <> $guest AND sud.userid <> 0 AND sud.userid
 531                                   NOT IN (SELECT ra.userid
 532                                             FROM {role_assignments} ra
 533                                            WHERE ra.roleid <> $guestrole AND
 534                                                  ra.roleid <> $defaultfproleid AND ra.contextid = :fpcontext)
 535                        ) inline_view
 536               GROUP BY timeend, courseid, nroleid
 537                 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
 538
 539         if ($logspresent and !$DB->execute($sql, array('fpcontext'=>$fpcontext->id, 'siteid'=>SITEID, 'nextm'=>$nextmidnight))) {
 540             $failed = true;
 541             break;
 542         }
 543         stats_daily_progress('15');
 544
 545     /// how many view actions for guests or not-logged-in on frontpage
 546         $sql = "INSERT INTO {stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 547
 548                 SELECT 'activity', timeend, courseid, nroleid, SUM(statsreads), SUM(statswrites)
 549                   FROM (
 550                            SELECT $nextmidnight AS timeend, ".SITEID." AS courseid, $guestrole AS nroleid, pl.statsreads, pl.statswrites
 551                              FROM (
 552                                       SELECT sud.statsreads, sud.statswrites
 553                                         FROM {stats_user_daily} sud
 554                                        WHERE (sud.userid = $guest OR sud.userid = 0) AND
 555                                              sud.timeend = $nextmidnight AND sud.courseid = ".SITEID." AND
 556                                              sud.stattype='activity'
 557                                   ) pl
 558                        ) inline_view
 559               GROUP BY timeend, courseid, nroleid
 560                 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
 561
 562         if ($logspresent and !$DB->execute($sql)) {
 563             $failed = true;
 564             break;
 565         }
 566         stats_daily_progress('16');
 567
 568         // remember processed days
 569         set_config('statslastdaily', $nextmidnight);
 570         mtrace("  finished until $nextmidnight: ".userdate($nextmidnight)." (in ".(time()-$daystart)." s)");
 571
 572         $timestart    = $nextmidnight;
 573         $nextmidnight = stats_get_next_day_start($nextmidnight);
 574     }
 575
 576     set_cron_lock('statsrunning', null);
 577
 578     if ($failed) {
 579         $days--;
 580         mtrace("...error occurred, completed $days days of statistics.");
 581         return false;
 582
 583     } else {
 584         mtrace("...completed $days days of statistics.");
 585         return true;
 586     }
 587 }
 588
 589
 590 /**
 591  * Execute weekly statistics gathering
 592  * @return boolean success
 593  */
 594 function stats_cron_weekly() {
 595     global $CFG, $DB;
 596
 597     $now = time();
 598
 599     // read last execution date from db
 600     if (!$timestart = get_config(NULL, 'statslastweekly')) {
 601         $timestart = stats_get_base_daily(stats_get_start_from('weekly'));
 602         set_config('statslastweekly', $timestart);
 603     }
 604
 605     $nextstartweek = stats_get_next_week_start($timestart);
 606
 607     // are there any weeks that need to be processed?
 608     if ($now < $nextstartweek) {
 609         return true; // everything ok and up-to-date
 610     }
 611
 612     $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
 613
 614     if (!set_cron_lock('statsrunning', $now + $timeout)) {
 615         return false;
 616     }
 617
 618     // fisrt delete entries that should not be there yet
 619     $DB->delete_records_select('stats_weekly',      "timeend > $timestart");
 620     $DB->delete_records_select('stats_user_weekly', "timeend > $timestart");
 621
 622     mtrace("Running weekly statistics gathering, starting at $timestart:");
 623
 624     $weeks = 0;
 625     while ($now > $nextstartweek) {
 626         @set_time_limit($timeout - 200);
 627         $weeks++;
 628
 629         if ($weeks > 1) {
 630             // move the lock
 631             set_cron_lock('statsrunning', time() + $timeout, true);
 632         }
 633
 634         $logtimesql  = "l.time >= $timestart AND l.time < $nextstartweek";
 635         $stattimesql = "timeend > $timestart AND timeend <= $nextstartweek";
 636
 637     /// process login info first
 638         $sql = "INSERT INTO {stats_user_weekly} (stattype, timeend, courseid, userid, statsreads)
 639
 640                 SELECT 'logins', timeend, courseid, userid, COUNT(statsreads)
 641                   FROM (
 642                            SELECT $nextstartweek AS timeend, ".SITEID." as courseid, l.userid, l.id AS statsreads
 643                              FROM {log} l
 644                             WHERE action = 'login' AND $logtimesql
 645                        ) inline_view
 646               GROUP BY timeend, courseid, userid
 647                 HAVING count(statsreads) > 0";
 648
 649         $DB->execute($sql);
 650
 651         $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
 652
 653                 SELECT 'logins' AS stattype, $nextstartweek AS timeend, ".SITEID." as courseid, 0,
 654                        COALESCE((SELECT SUM(statsreads)
 655                                    FROM {stats_user_weekly} s1
 656                                   WHERE s1.stattype = 'logins' AND timeend = $nextstartweek), 0) AS nstat1,
 657                        (SELECT COUNT('x')
 658                           FROM {stats_user_weekly} s2
 659                          WHERE s2.stattype = 'logins' AND timeend = $nextstartweek) AS nstat2" .
 660                 $DB->sql_null_from_clause();
 661
 662         $DB->execute($sql);
 663
 664
 665     /// now enrolments averages
 666         $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
 667
 668                 SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . "
 669                   FROM (
 670                            SELECT $nextstartweek AS ntimeend, courseid, roleid, stat1, stat2
 671                              FROM {stats_daily} sd
 672                             WHERE stattype = 'enrolments' AND $stattimesql
 673                        ) inline_view
 674               GROUP BY ntimeend, courseid, roleid";
 675
 676         $DB->execute($sql);
 677
 678
 679     /// activity read/write averages
 680         $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
 681
 682                 SELECT 'activity', ntimeend, courseid, roleid, SUM(stat1), SUM(stat2)
 683                   FROM (
 684                            SELECT $nextstartweek AS ntimeend, courseid, roleid, stat1, stat2
 685                              FROM {stats_daily}
 686                             WHERE stattype = 'activity' AND $stattimesql
 687                        ) inline_view
 688               GROUP BY ntimeend, courseid, roleid";
 689
 690         $DB->execute($sql);
 691
 692
 693     /// user read/write averages
 694         $sql = "INSERT INTO {stats_user_weekly} (stattype, timeend, courseid, userid, statsreads, statswrites)
 695
 696                 SELECT 'activity', ntimeend, courseid, userid, SUM(statsreads), SUM(statswrites)
 697                   FROM (
 698                            SELECT $nextstartweek AS ntimeend, courseid, userid, statsreads, statswrites
 699                              FROM {stats_user_daily}
 700                             WHERE stattype = 'activity' AND $stattimesql
 701                        ) inline_view
 702               GROUP BY ntimeend, courseid, userid";
 703
 704         $DB->execute($sql);
 705
 706         set_config('statslastweekly', $nextstartweek);
 707         mtrace(" finished until $nextstartweek: ".userdate($nextstartweek));
 708
 709         $timestart     = $nextstartweek;
 710         $nextstartweek = stats_get_next_week_start($nextstartweek);
 711     }
 712
 713     set_cron_lock('statsrunning', null);
 714     mtrace("...completed $weeks weeks of statistics.");
 715     return true;
 716 }
 717
 718 /**
 719  * Execute monthly statistics gathering
 720  * @return boolean success
 721  */
 722 function stats_cron_monthly() {
 723     global $CFG, $DB;
 724
 725     $now = time();
 726
 727     // read last execution date from db
 728     if (!$timestart = get_config(NULL, 'statslastmonthly')) {
 729         $timestart = stats_get_base_monthly(stats_get_start_from('monthly'));
 730         set_config('statslastmonthly', $timestart);
 731     }
 732
 733     $nextstartmonth = stats_get_next_month_start($timestart);
 734
 735     // are there any months that need to be processed?
 736     if ($now < $nextstartmonth) {
 737         return true; // everything ok and up-to-date
 738     }
 739
 740     $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
 741
 742     if (!set_cron_lock('statsrunning', $now + $timeout)) {
 743         return false;
 744     }
 745
 746     // fisr delete entries that should not be there yet
 747     $DB->delete_records_select('stats_monthly', "timeend > $timestart");
 748     $DB->delete_records_select('stats_user_monthly', "timeend > $timestart");
 749
 750     $startmonth = stats_get_base_monthly($now);
 751
 752
 753     mtrace("Running monthly statistics gathering, starting at $timestart:");
 754
 755     $months = 0;
 756     while ($now > $nextstartmonth) {
 757         @set_time_limit($timeout - 200);
 758         $months++;
 759
 760         if ($months > 1) {
 761             // move the lock
 762             set_cron_lock('statsrunning', time() + $timeout, true);
 763         }
 764
 765         $logtimesql  = "l.time >= $timestart AND l.time < $nextstartmonth";
 766         $stattimesql = "timeend > $timestart AND timeend <= $nextstartmonth";
 767
 768     /// process login info first
 769         $sql = "INSERT INTO {stats_user_monthly} (stattype, timeend, courseid, userid, statsreads)
 770
 771                 SELECT 'logins', timeend, courseid, userid, COUNT(statsreads)
 772                   FROM (
 773                            SELECT $nextstartmonth AS timeend, ".SITEID." as courseid, l.userid, l.id AS statsreads
 774                              FROM {log} l
 775                             WHERE action = 'login' AND $logtimesql
 776                        ) inline_view
 777               GROUP BY timeend, courseid, userid";
 778
 779         $DB->execute($sql);
 780
 781         $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
 782
 783                 SELECT 'logins' AS stattype, $nextstartmonth AS timeend, ".SITEID." as courseid, 0,
 784                        COALESCE((SELECT SUM(statsreads)
 785                                    FROM {stats_user_monthly} s1
 786                                   WHERE s1.stattype = 'logins' AND timeend = $nextstartmonth), 0) AS nstat1,
 787                        (SELECT COUNT('x')
 788                           FROM {stats_user_monthly} s2
 789                          WHERE s2.stattype = 'logins' AND timeend = $nextstartmonth) AS nstat2" .
 790                 $DB->sql_null_from_clause();
 791
 792         $DB->execute($sql);
 793
 794
 795     /// now enrolments averages
 796         $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
 797
 798                 SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . "
 799                   FROM (
 800                            SELECT $nextstartmonth AS ntimeend, courseid, roleid, stat1, stat2
 801                              FROM {stats_daily} sd
 802                             WHERE stattype = 'enrolments' AND $stattimesql
 803                        ) inline_view
 804               GROUP BY ntimeend, courseid, roleid";
 805
 806         $DB->execute($sql);
 807
 808
 809     /// activity read/write averages
 810         $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
 811
 812                 SELECT 'activity', ntimeend, courseid, roleid, SUM(stat1), SUM(stat2)
 813                   FROM (
 814                            SELECT $nextstartmonth AS ntimeend, courseid, roleid, stat1, stat2
 815                              FROM {stats_daily}
 816                             WHERE stattype = 'activity' AND $stattimesql
 817                        ) inline_view
 818               GROUP BY ntimeend, courseid, roleid";
 819
 820         $DB->execute($sql);
 821
 822
 823     /// user read/write averages
 824         $sql = "INSERT INTO {stats_user_monthly} (stattype, timeend, courseid, userid, statsreads, statswrites)
 825
 826                 SELECT 'activity', ntimeend, courseid, userid, SUM(statsreads), SUM(statswrites)
 827                   FROM (
 828                            SELECT $nextstartmonth AS ntimeend, courseid, userid, statsreads, statswrites
 829                              FROM {stats_user_daily}
 830                             WHERE stattype = 'activity' AND $stattimesql
 831                        ) inline_view
 832               GROUP BY ntimeend, courseid, userid";
 833
 834         $DB->execute($sql);
 835
 836         set_config('statslastmonthly', $nextstartmonth);
 837         mtrace(" finished until $nextstartmonth: ".userdate($nextstartmonth));
 838
 839         $timestart      = $nextstartmonth;
 840         $nextstartmonth = stats_get_next_month_start($nextstartmonth);
 841     }
 842
 843     set_cron_lock('statsrunning', null);
 844     mtrace("...completed $months months of statistics.");
 845     return true;
 846 }
 847
 848 /**
 849  * Return starting date of stats processing
 850  * @param string $str name of table - daily, weekly or monthly
 851  * @return int timestamp
 852  */
 853 function stats_get_start_from($str) {
 854     global $CFG, $DB;
 855
 856     // are there any data in stats table? Should not be...
 857     if ($timeend = $DB->get_field_sql('SELECT MAX(timeend) FROM {stats_'.$str.'}')) {
 858         return $timeend;
 859     }
 860     // decide what to do based on our config setting (either all or none or a timestamp)
 861     switch ($CFG->statsfirstrun) {
 862         case 'all':
 863             if ($firstlog = $DB->get_field_sql('SELECT MIN(time) FROM {log}')) {
 864                 return $firstlog;
 865             }
 866         default:
 867             if (is_numeric($CFG->statsfirstrun)) {
 868                 return time() - $CFG->statsfirstrun;
 869             }
 870             // not a number? use next instead
 871         case 'none':
 872             return strtotime('-3 day', time());
 873     }
 874 }
 875
 876 /**
 877  * Start of day
 878  * @param int $time timestamp
 879  * @return start of day
 880  */
 881 function stats_get_base_daily($time=0) {
 882     global $CFG;
 883
 884     if (empty($time)) {
 885         $time = time();
 886     }
 887     if ($CFG->timezone == 99) {
 888         $time = strtotime(date('d-M-Y', $time));
 889         return $time;
 890     } else {
 891         $offset = get_timezone_offset($CFG->timezone);
 892         $gtime = $time + $offset;
 893         $gtime = intval($gtime / (60*60*24)) * 60*60*24;
 894         return $gtime - $offset;
 895     }
 896 }
 897
 898 /**
 899  * Start of week
 900  * @param int $time timestamp
 901  * @return start of week
 902  */
 903 function stats_get_base_weekly($time=0) {
 904     global $CFG;
 905
 906     $time = stats_get_base_daily($time);
 907     $startday = $CFG->calendar_startwday;
 908     if ($CFG->timezone == 99) {
 909         $thisday = date('w', $time);
 910     } else {
 911         $offset = get_timezone_offset($CFG->timezone);
 912         $gtime = $time + $offset;
 913         $thisday = gmdate('w', $gtime);
 914     }
 915     if ($thisday > $startday) {
 916         $time = $time - (($thisday - $startday) * 60*60*24);
 917     } else if ($thisday < $startday) {
 918         $time = $time - ((7 + $thisday - $startday) * 60*60*24);
 919     }
 920     return $time;
 921 }
 922
 923 /**
 924  * Start of month
 925  * @param int $time timestamp
 926  * @return start of month
 927  */
 928 function stats_get_base_monthly($time=0) {
 929     global $CFG;
 930
 931     if (empty($time)) {
 932         $time = time();
 933     }
 934     if ($CFG->timezone == 99) {
 935         return strtotime(date('1-M-Y', $time));
 936
 937     } else {
 938         $time = stats_get_base_daily($time);
 939         $offset = get_timezone_offset($CFG->timezone);
 940         $gtime = $time + $offset;
 941         $day = gmdate('d', $gtime);
 942         if ($day == 1) {
 943             return $time;
 944         }
 945         return $gtime - (($day-1) * 60*60*24);
 946     }
 947 }
 948
 949 /**
 950  * Start of next day
 951  * @param int $time timestamp
 952  * @return start of next day
 953  */
 954 function stats_get_next_day_start($time) {
 955     $next = stats_get_base_daily($time);
 956     $next = $next + 60*60*26;
 957     $next = stats_get_base_daily($next);
 958     if ($next <= $time) {
 959         //DST trouble - prevent infinite loops
 960         $next = $next + 60*60*24;
 961     }
 962     return $next;
 963 }
 964
 965 /**
 966  * Start of next week
 967  * @param int $time timestamp
 968  * @return start of next week
 969  */
 970 function stats_get_next_week_start($time) {
 971     $next = stats_get_base_weekly($time);
 972     $next = $next + 60*60*24*9;
 973     $next = stats_get_base_weekly($next);
 974     if ($next <= $time) {
 975         //DST trouble - prevent infinite loops
 976         $next = $next + 60*60*24*7;
 977     }
 978     return $next;
 979 }
 980
 981 /**
 982  * Start of next month
 983  * @param int $time timestamp
 984  * @return start of next month
 985  */
 986 function stats_get_next_month_start($time) {
 987     $next = stats_get_base_monthly($time);
 988     $next = $next + 60*60*24*33;
 989     $next = stats_get_base_monthly($next);
 990     if ($next <= $time) {
 991         //DST trouble - prevent infinite loops
 992         $next = $next + 60*60*24*31;
 993     }
 994     return $next;
 995 }
 996
 997 /**
 998  * Remove old stats data
 999  */
1000 function stats_clean_old() {
1001     global $DB;
1002     mtrace("Running stats cleanup tasks...");
1003     $deletebefore =  stats_get_base_monthly();
1004
1005     // delete dailies older than 3 months (to be safe)
1006     $deletebefore = strtotime('-3 months', $deletebefore);
1007     $DB->delete_records_select('stats_daily',      "timeend < $deletebefore");
1008     $DB->delete_records_select('stats_user_daily', "timeend < $deletebefore");
1009
1010     // delete weeklies older than 9  months (to be safe)
1011     $deletebefore = strtotime('-6 months', $deletebefore);
1012     $DB->delete_records_select('stats_weekly',      "timeend < $deletebefore");
1013     $DB->delete_records_select('stats_user_weekly', "timeend < $deletebefore");
1014
1015     // don't delete monthlies
1016
1017     mtrace("...stats cleanup finished");
1018 }
1019
1020 function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) {
1021     global $CFG, $DB;
1022
1023     $param = new stdClass();
1024     $param->params = array();
1025
1026     if ($time < 10) { // dailies
1027         // number of days to go back = 7* time
1028         $param->table = 'daily';
1029         $param->timeafter = strtotime("-".($time*7)." days",stats_get_base_daily());
1030     } elseif ($time < 20) { // weeklies
1031         // number of weeks to go back = time - 10 * 4 (weeks) + base week
1032         $param->table = 'weekly';
1033         $param->timeafter = strtotime("-".(($time - 10)*4)." weeks",stats_get_base_weekly());
1034     } else { // monthlies.
1035         // number of months to go back = time - 20 * months + base month
1036         $param->table = 'monthly';
1037         $param->timeafter = strtotime("-".($time - 20)." months",stats_get_base_monthly());
1038     }
1039
1040     $param->extras = '';
1041
1042     switch ($report) {
1043     // ******************** STATS_MODE_GENERAL ******************** //
1044     case STATS_REPORT_LOGINS:
1045         $param->fields = 'timeend,sum(stat1) as line1,sum(stat2) as line2';
1046         $param->fieldscomplete = true;
1047         $param->stattype = 'logins';
1048         $param->line1 = get_string('statslogins');
1049         $param->line2 = get_string('statsuniquelogins');
1050         if ($courseid == SITEID) {
1051             $param->extras = 'GROUP BY timeend';
1052         }
1053         break;
1054
1055     case STATS_REPORT_READS:
1056         $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat1 as line1';
1057         $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
1058         $param->aggregategroupby = 'roleid';
1059         $param->stattype = 'activity';
1060         $param->crosstab = true;
1061         $param->extras = 'GROUP BY timeend,roleid,stat1';
1062         if ($courseid == SITEID) {
1063             $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1) as line1';
1064             $param->extras = 'GROUP BY timeend,roleid';
1065         }
1066         break;
1067
1068     case STATS_REPORT_WRITES:
1069         $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat2 as line1';
1070         $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
1071         $param->aggregategroupby = 'roleid';
1072         $param->stattype = 'activity';
1073         $param->crosstab = true;
1074         $param->extras = 'GROUP BY timeend,roleid,stat2';
1075         if ($courseid == SITEID) {
1076             $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat2) as line1';
1077             $param->extras = 'GROUP BY timeend,roleid';
1078         }
1079         break;
1080
1081     case STATS_REPORT_ACTIVITY:
1082         $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1+stat2) as line1';
1083         $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
1084         $param->aggregategroupby = 'roleid';
1085         $param->stattype = 'activity';
1086         $param->crosstab = true;
1087         $param->extras = 'GROUP BY timeend,roleid';
1088         if ($courseid == SITEID) {
1089             $param->extras = 'GROUP BY timeend,roleid';
1090         }
1091         break;
1092
1093     case STATS_REPORT_ACTIVITYBYROLE;
1094         $param->fields = 'stat1 AS line1, stat2 AS line2';
1095         $param->stattype = 'activity';
1096         $rolename = $DB->get_field('role','name', array('id'=>$roleid));
1097         $param->line1 = $rolename . get_string('statsreads');
1098         $param->line2 = $rolename . get_string('statswrites');
1099         if ($courseid == SITEID) {
1100             $param->extras = 'GROUP BY timeend';
1101         }
1102         break;
1103
1104     // ******************** STATS_MODE_DETAILED ******************** //
1105     case STATS_REPORT_USER_ACTIVITY:
1106         $param->fields = 'statsreads as line1, statswrites as line2';
1107         $param->line1 = get_string('statsuserreads');
1108         $param->line2 = get_string('statsuserwrites');
1109         $param->stattype = 'activity';
1110         break;
1111
1112     case STATS_REPORT_USER_ALLACTIVITY:
1113         $param->fields = 'statsreads+statswrites as line1';
1114         $param->line1 = get_string('statsuseractivity');
1115         $param->stattype = 'activity';
1116         break;
1117
1118     case STATS_REPORT_USER_LOGINS:
1119         $param->fields = 'statsreads as line1';
1120         $param->line1 = get_string('statsuserlogins');
1121         $param->stattype = 'logins';
1122         break;
1123
1124     case STATS_REPORT_USER_VIEW:
1125         $param->fields = 'statsreads as line1, statswrites as line2, statsreads+statswrites as line3';
1126         $param->line1 = get_string('statsuserreads');
1127         $param->line2 = get_string('statsuserwrites');
1128         $param->line3 = get_string('statsuseractivity');
1129         $param->stattype = 'activity';
1130         break;
1131
1132     // ******************** STATS_MODE_RANKED ******************** //
1133     case STATS_REPORT_ACTIVE_COURSES:
1134         $param->fields = 'sum(stat1+stat2) AS line1';
1135         $param->stattype = 'activity';
1136         $param->orderby = 'line1 DESC';
1137         $param->line1 = get_string('activity');
1138         $param->graphline = 'line1';
1139         break;
1140
1141     case STATS_REPORT_ACTIVE_COURSES_WEIGHTED:
1142         $threshold = 0;
1143         if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
1144             $threshold = $CFG->statsuserthreshold;
1145         }
1146         $param->fields = '';
1147         $param->sql = 'SELECT activity.courseid, activity.all_activity AS line1, enrolments.highest_enrolments AS line2,
1148                         activity.all_activity / enrolments.highest_enrolments as line3
1149                        FROM (
1150                             SELECT courseid, sum(stat1+stat2) AS all_activity
1151                               FROM {stats_'.$param->table.'}
1152                              WHERE stattype=\'activity\' AND timeend >= '.(int)$param->timeafter.' AND roleid = 0 GROUP BY courseid
1153                        ) activity
1154                        INNER JOIN
1155                             (
1156                             SELECT courseid, max(stat1) AS highest_enrolments
1157                               FROM {stats_'.$param->table.'}
1158                              WHERE stattype=\'enrolments\' AND timeend >= '.(int)$param->timeafter.' AND stat1 > '.(int)$threshold.'
1159                           GROUP BY courseid
1160                       ) enrolments
1161                       ON (activity.courseid = enrolments.courseid)
1162                       ORDER BY line3 DESC';
1163         $param->line1 = get_string('activity');
1164         $param->line2 = get_string('users');
1165         $param->line3 = get_string('activityweighted');
1166         $param->graphline = 'line3';
1167         break;
1168
1169     case STATS_REPORT_PARTICIPATORY_COURSES:
1170         $threshold = 0;
1171         if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
1172             $threshold = $CFG->statsuserthreshold;
1173         }
1174         $param->fields = '';
1175         $param->sql = 'SELECT courseid, ' . $DB->sql_ceil('avg(all_enrolments)') . ' as line1, ' .
1176                          $DB->sql_ceil('avg(active_enrolments)') . ' as line2, avg(proportion_active) AS line3
1177                        FROM (
1178                            SELECT courseid, timeend, stat2 as active_enrolments,
1179                                   stat1 as all_enrolments, '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' AS proportion_active
1180                              FROM {stats_'.$param->table.'}
1181                             WHERE stattype=\'enrolments\' AND roleid = 0 AND stat1 > '.(int)$threshold.'
1182                        ) aq
1183                        WHERE timeend >= '.(int)$param->timeafter.'
1184                        GROUP BY courseid
1185                        ORDER BY line3 DESC';
1186
1187         $param->line1 = get_string('users');
1188         $param->line2 = get_string('activeusers');
1189         $param->line3 = get_string('participationratio');
1190         $param->graphline = 'line3';
1191         break;
1192
1193     case STATS_REPORT_PARTICIPATORY_COURSES_RW:
1194         $param->fields = '';
1195         $param->sql =  'SELECT courseid, sum(views) AS line1, sum(posts) AS line2,
1196                            avg(proportion_active) AS line3
1197                          FROM (
1198                            SELECT courseid, timeend, stat1 as views, stat2 AS posts,
1199                                   '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' as proportion_active
1200                              FROM {stats_'.$param->table.'}
1201                             WHERE stattype=\'activity\' AND roleid = 0 AND stat1 > 0
1202                        ) aq
1203                        WHERE timeend >= '.(int)$param->timeafter.'
1204                        GROUP BY courseid
1205                        ORDER BY line3 DESC';
1206         $param->line1 = get_string('views');
1207         $param->line2 = get_string('posts');
1208         $param->line3 = get_string('participationratio');
1209         $param->graphline = 'line3';
1210         break;
1211     }
1212
1213     /*
1214     if ($courseid == SITEID && $mode != STATS_MODE_RANKED) { // just aggregate all courses.
1215         $param->fields = preg_replace('/(?:sum)([a-zA-Z0-9+_]*)\W+as\W+([a-zA-Z0-9_]*)/i','sum($1) as $2',$param->fields);
1216         $param->extras = ' GROUP BY timeend'.((!empty($param->aggregategroupby)) ? ','.$param->aggregategroupby : '');
1217     }
1218     */
1219     //TODO must add the SITEID reports to the rest of the reports.
1220     return $param;
1221 }
1222
1223 function stats_get_view_actions() {
1224     return array('view','view all','history');
1225 }
1226
1227 function stats_get_post_actions() {
1228     return array('add','delete','edit','add mod','delete mod','edit section'.'enrol','loginas','new','unenrol','update','update mod');
1229 }
1230
1231 function stats_get_action_names($str) {
1232     global $CFG, $DB;
1233
1234     $mods = $DB->get_records('modules');
1235     $function = 'stats_get_'.$str.'_actions';
1236     $actions = $function();
1237     foreach ($mods as $mod) {
1238         $file = $CFG->dirroot.'/mod/'.$mod->name.'/lib.php';
1239         if (!is_readable($file)) {
1240             continue;
1241         }
1242         require_once($file);
1243         $function = $mod->name.'_get_'.$str.'_actions';
1244         if (function_exists($function)) {
1245             $mod_actions = $function();
1246             if (is_array($mod_actions)) {
1247                 $actions = array_merge($actions, $mod_actions);
1248             }
1249         }
1250     }
1251
1252     // The array_values() forces a stack-like array
1253     // so we can later loop over safely...
1254     $actions =  array_values(array_unique($actions));
1255     $c = count($actions);
1256     for ($n=0;$n<$c;$n++) {
1257         $actions[$n] = $actions[$n];
1258     }
1259     return $actions;
1260 }
1261
1262 function stats_get_time_options($now,$lastweekend,$lastmonthend,$earliestday,$earliestweek,$earliestmonth) {
1263
1264     $now = stats_get_base_daily(time());
1265     // it's really important that it's TIMEEND in the table. ie, tuesday 00:00:00 is monday night.
1266     // so we need to take a day off here (essentially add a day to $now
1267     $now += 60*60*24;
1268
1269     $timeoptions = array();
1270
1271     if ($now - (60*60*24*7) >= $earliestday) {
1272         $timeoptions[STATS_TIME_LASTWEEK] = get_string('numweeks','moodle',1);
1273     }
1274     if ($now - (60*60*24*14) >= $earliestday) {
1275         $timeoptions[STATS_TIME_LAST2WEEKS] = get_string('numweeks','moodle',2);
1276     }
1277     if ($now - (60*60*24*21) >= $earliestday) {
1278         $timeoptions[STATS_TIME_LAST3WEEKS] = get_string('numweeks','moodle',3);
1279     }
1280     if ($now - (60*60*24*28) >= $earliestday) {
1281         $timeoptions[STATS_TIME_LAST4WEEKS] = get_string('numweeks','moodle',4);// show dailies up to (including) here.
1282     }
1283     if ($lastweekend - (60*60*24*56) >= $earliestweek) {
1284         $timeoptions[STATS_TIME_LAST2MONTHS] = get_string('nummonths','moodle',2);
1285     }
1286     if ($lastweekend - (60*60*24*84) >= $earliestweek) {
1287         $timeoptions[STATS_TIME_LAST3MONTHS] = get_string('nummonths','moodle',3);
1288     }
1289     if ($lastweekend - (60*60*24*112) >= $earliestweek) {
1290         $timeoptions[STATS_TIME_LAST4MONTHS] = get_string('nummonths','moodle',4);
1291     }
1292     if ($lastweekend - (60*60*24*140) >= $earliestweek) {
1293         $timeoptions[STATS_TIME_LAST5MONTHS] = get_string('nummonths','moodle',5);
1294     }
1295     if ($lastweekend - (60*60*24*168) >= $earliestweek) {
1296         $timeoptions[STATS_TIME_LAST6MONTHS] = get_string('nummonths','moodle',6); // show weeklies up to (including) here
1297     }
1298     if (strtotime('-7 months',$lastmonthend) >= $earliestmonth) {
1299         $timeoptions[STATS_TIME_LAST7MONTHS] = get_string('nummonths','moodle',7);
1300     }
1301     if (strtotime('-8 months',$lastmonthend) >= $earliestmonth) {
1302         $timeoptions[STATS_TIME_LAST8MONTHS] = get_string('nummonths','moodle',8);
1303     }
1304     if (strtotime('-9 months',$lastmonthend) >= $earliestmonth) {
1305         $timeoptions[STATS_TIME_LAST9MONTHS] = get_string('nummonths','moodle',9);
1306     }
1307     if (strtotime('-10 months',$lastmonthend) >= $earliestmonth) {
1308         $timeoptions[STATS_TIME_LAST10MONTHS] = get_string('nummonths','moodle',10);
1309     }
1310     if (strtotime('-11 months',$lastmonthend) >= $earliestmonth) {
1311         $timeoptions[STATS_TIME_LAST11MONTHS] = get_string('nummonths','moodle',11);
1312     }
1313     if (strtotime('-1 year',$lastmonthend) >= $earliestmonth) {
1314         $timeoptions[STATS_TIME_LASTYEAR] = get_string('lastyear');
1315     }
1316
1317     $years = (int)date('y', $now) - (int)date('y', $earliestmonth);
1318     if ($years > 1) {
1319         for($i = 2; $i <= $years; $i++) {
1320             $timeoptions[$i*12+20] = get_string('numyears', 'moodle', $i);
1321         }
1322     }
1323
1324     return $timeoptions;
1325 }
1326
1327 function stats_get_report_options($courseid,$mode) {
1328     global $CFG, $DB;
1329
1330     $reportoptions = array();
1331
1332     switch ($mode) {
1333     case STATS_MODE_GENERAL:
1334         $reportoptions[STATS_REPORT_ACTIVITY] = get_string('statsreport'.STATS_REPORT_ACTIVITY);
1335         if ($courseid != SITEID && $context = get_context_instance(CONTEXT_COURSE, $courseid)) {
1336             $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';
1337             if ($roles = $DB->get_records_sql($sql, array('courseid' => $courseid))) {
1338                 foreach ($roles as $role) {
1339                     $reportoptions[STATS_REPORT_ACTIVITYBYROLE.$role->id] = get_string('statsreport'.STATS_REPORT_ACTIVITYBYROLE). ' '.$role->name;
1340                 }
1341             }
1342         }
1343         $reportoptions[STATS_REPORT_READS] = get_string('statsreport'.STATS_REPORT_READS);
1344         $reportoptions[STATS_REPORT_WRITES] = get_string('statsreport'.STATS_REPORT_WRITES);
1345         if ($courseid == SITEID) {
1346             $reportoptions[STATS_REPORT_LOGINS] = get_string('statsreport'.STATS_REPORT_LOGINS);
1347         }
1348
1349         break;
1350     case STATS_MODE_DETAILED:
1351         $reportoptions[STATS_REPORT_USER_ACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ACTIVITY);
1352         $reportoptions[STATS_REPORT_USER_ALLACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ALLACTIVITY);
1353         if (has_capability('coursereport/stats:view', get_context_instance(CONTEXT_SYSTEM))) {
1354             $site = get_site();
1355             $reportoptions[STATS_REPORT_USER_LOGINS] = get_string('statsreport'.STATS_REPORT_USER_LOGINS);
1356         }
1357         break;
1358     case STATS_MODE_RANKED:
1359         if (has_capability('coursereport/stats:view', get_context_instance(CONTEXT_SYSTEM))) {
1360             $reportoptions[STATS_REPORT_ACTIVE_COURSES] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES);
1361             $reportoptions[STATS_REPORT_ACTIVE_COURSES_WEIGHTED] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES_WEIGHTED);
1362             $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES);
1363             $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES_RW] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES_RW);
1364         }
1365      break;
1366     }
1367
1368     return $reportoptions;
1369 }
1370
1371 function stats_fix_zeros($stats,$timeafter,$timestr,$line2=true,$line3=false) {
1372
1373     if (empty($stats)) {
1374         return;
1375     }
1376
1377     $timestr = str_replace('user_','',$timestr); // just in case.
1378     $fun = 'stats_get_base_'.$timestr;
1379
1380     $now = $fun();
1381
1382     $times = array();
1383     // add something to timeafter since it is our absolute base
1384     $actualtimes = array();
1385     foreach ($stats as $statid=>$s) {
1386         //normalize the times in stats - those might have been created in different timezone, DST etc.
1387         $s->timeend = $fun($s->timeend + 60*60*5);
1388         $stats[$statid] = $s;
1389
1390         $actualtimes[] = $s->timeend;
1391     }
1392
1393     $timeafter = array_pop(array_values($actualtimes));
1394
1395     while ($timeafter < $now) {
1396         $times[] = $timeafter;
1397         if ($timestr == 'daily') {
1398             $timeafter = stats_get_next_day_start($timeafter);
1399         } else if ($timestr == 'weekly') {
1400             $timeafter = stats_get_next_week_start($timeafter);
1401         } else if ($timestr == 'monthly') {
1402             $timeafter = stats_get_next_month_start($timeafter);
1403         } else {
1404             return $stats; // this will put us in a never ending loop.
1405         }
1406     }
1407
1408     foreach ($times as $count => $time) {
1409         if (!in_array($time,$actualtimes) && $count != count($times) -1) {
1410             $newobj = new StdClass;
1411             $newobj->timeend = $time;
1412             $newobj->id = 0;
1413             $newobj->roleid = 0;
1414             $newobj->line1 = 0;
1415             if (!empty($line2)) {
1416                 $newobj->line2 = 0;
1417             }
1418             if (!empty($line3)) {
1419                 $newobj->line3 = 0;
1420             }
1421             $newobj->zerofixed = true;
1422             $stats[] = $newobj;
1423         }
1424     }
1425
1426     usort($stats,"stats_compare_times");
1427     return $stats;
1428
1429 }
1430
1431 // helper function to sort arrays by $obj->timeend
1432 function stats_compare_times($a,$b) {
1433    if ($a->timeend == $b->timeend) {
1434        return 0;
1435    }
1436    return ($a->timeend > $b->timeend) ? -1 : 1;
1437 }
1438
1439 function stats_check_uptodate($courseid=0) {
1440     global $CFG, $DB;
1441
1442     if (empty($courseid)) {
1443         $courseid = SITEID;
1444     }
1445
1446     $latestday = stats_get_start_from('daily');
1447
1448     if ((time() - 60*60*24*2) < $latestday) { // we're ok
1449         return NULL;
1450     }
1451
1452     $a = new stdClass();
1453     $a->daysdone = $DB->get_field_sql("SELECT COUNT(DISTINCT(timeend)) FROM {stats_daily}");
1454
1455     // how many days between the last day and now?
1456     $a->dayspending = ceil((stats_get_base_daily() - $latestday)/(60*60*24));
1457
1458     if ($a->dayspending == 0 && $a->daysdone != 0) {
1459         return NULL; // we've only just started...
1460     }
1461
1462     //return error as string
1463     return get_string('statscatchupmode','error',$a);
1464 }