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 // Output string when nodebug is on
  75 define('STATS_PLACEHOLDER_OUTPUT', '.');
  76
  77 /**
  78  * Print daily cron progress
  79  * @param string $ident
  80  */
  81 function stats_progress($ident) {
  82     static $start = 0;
  83     static $init  = 0;
  84
  85     if ($ident == 'init') {
  86         $init = $start = microtime(true);
  87         return;
  88     }
  89
  90     $elapsed = round(microtime(true) - $start);
  91     $start   = microtime(true);
  92
  93     if (debugging('', DEBUG_ALL)) {
  94         mtrace("$ident:$elapsed ", '');
  95     } else {
  96         mtrace(STATS_PLACEHOLDER_OUTPUT, '');
  97     }
  98 }
  99
 100 /**
 101  * Execute individual daily statistics queries
 102  *
 103  * @param string $sql The query to run
 104  * @return boolean success
 105  */
 106 function stats_run_query($sql, $parameters = array()) {
 107     global $DB;
 108
 109     try {
 110         $DB->execute($sql, $parameters);
 111     } catch (dml_exception $e) {
 112
 113        if (debugging('', DEBUG_ALL)) {
 114            mtrace($e->getMessage());
 115        }
 116        return false;
 117     }
 118     return true;
 119 }
 120
 121 /**
 122  * Execute daily statistics gathering
 123  *
 124  * @param int $maxdays maximum number of days to be processed
 125  * @return boolean success
 126  */
 127 function stats_cron_daily($maxdays=1) {
 128     global $CFG, $DB;
 129
 130     $now = time();
 131
 132     $fpcontext = context_course::instance(SITEID, MUST_EXIST);
 133
 134     // read last execution date from db
 135     if (!$timestart = get_config(NULL, 'statslastdaily')) {
 136         $timestart = stats_get_base_daily(stats_get_start_from('daily'));
 137         set_config('statslastdaily', $timestart);
 138     }
 139
 140     // calculate scheduled time
 141     $scheduledtime = stats_get_base_daily() + $CFG->statsruntimestarthour*60*60 + $CFG->statsruntimestartminute*60;
 142
 143     // Note: This will work fine for sites running cron each 4 hours or less (hopefully, 99.99% of sites). MDL-16709
 144     // check to make sure we're due to run, at least 20 hours after last run
 145     if (isset($CFG->statslastexecution) && ((time() - 20*60*60) < $CFG->statslastexecution)) {
 146         mtrace("...preventing stats to run, last execution was less than 20 hours ago.");
 147         return false;
 148     // also check that we are a max of 4 hours after scheduled time, stats won't run after that
 149     } else if (time() > $scheduledtime + 4*60*60) {
 150         mtrace("...preventing stats to run, more than 4 hours since scheduled time.");
 151         return false;
 152     } else {
 153         set_config('statslastexecution', time()); /// Grab this execution as last one
 154     }
 155
 156     $nextmidnight = stats_get_next_day_start($timestart);
 157
 158     // are there any days that need to be processed?
 159     if ($now < $nextmidnight) {
 160         return true; // everything ok and up-to-date
 161     }
 162
 163
 164     $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
 165
 166     if (!set_cron_lock('statsrunning', $now + $timeout)) {
 167         return false;
 168     }
 169
 170     // first delete entries that should not be there yet
 171     $DB->delete_records_select('stats_daily',      "timeend > $timestart");
 172     $DB->delete_records_select('stats_user_daily', "timeend > $timestart");
 173
 174     // Read in a few things we'll use later
 175     $viewactions = stats_get_action_names('view');
 176     $postactions = stats_get_action_names('post');
 177
 178     $guest           = (int)$CFG->siteguest;
 179     $guestrole       = (int)$CFG->guestroleid;
 180     $defaultfproleid = (int)$CFG->defaultfrontpageroleid;
 181
 182     mtrace("Running daily statistics gathering, starting at $timestart:");
 183
 184     $days  = 0;
 185     $total = 0;
 186     $failed  = false; // failed stats flag
 187     $timeout = false;
 188
 189     if (!stats_temp_table_create()) {
 190         $days = 1;
 191         $failed = true;
 192     }
 193     mtrace('Temporary tables created');
 194
 195     if(!stats_temp_table_setup()) {
 196         $days = 1;
 197         $failed = true;
 198     }
 199     mtrace('Enrolments calculated');
 200
 201     $totalactiveusers = $DB->count_records('user', array('deleted' => '0'));
 202
 203     while (!$failed && ($now > $nextmidnight)) {
 204         if ($days >= $maxdays) {
 205             $timeout = true;
 206             break;
 207         }
 208
 209         $days++;
 210         @set_time_limit($timeout - 200);
 211
 212         if ($days > 1) {
 213             // move the lock
 214             set_cron_lock('statsrunning', time() + $timeout, true);
 215         }
 216
 217         $daystart = time();
 218
 219         stats_progress('init');
 220
 221         if (!stats_temp_table_fill($timestart, $nextmidnight)) {
 222             $failed = true;
 223             break;
 224         }
 225
 226         // Find out if any logs available for this day
 227         $sql = "SELECT 'x' FROM {temp_log1} l";
 228         $logspresent = $DB->get_records_sql($sql, null, 0, 1);
 229
 230         if ($logspresent) {
 231             // Insert blank record to force Query 10 to generate additional row when no logs for
 232             // the site with userid 0 exist.  Added for backwards compatibility.
 233             $DB->insert_record('temp_log1', array('userid' => 0, 'course' => SITEID, 'action' => ''));
 234         }
 235
 236         // Calculate the number of active users today
 237         $sql = 'SELECT COUNT(DISTINCT u.id)
 238                   FROM {user} u
 239                   JOIN {temp_log1} l ON l.userid = u.id
 240                  WHERE u.deleted = 0';
 241         $dailyactiveusers = $DB->count_records_sql($sql);
 242
 243         stats_progress('0');
 244
 245         // Process login info first
 246         // Note: PostgreSQL doesn't like aliases in HAVING clauses
 247         $sql = "INSERT INTO {temp_stats_user_daily}
 248                             (stattype, timeend, courseid, userid, statsreads)
 249
 250                 SELECT 'logins', $nextmidnight AS timeend, ".SITEID." AS courseid,
 251                         userid, COUNT(id) AS statsreads
 252                   FROM {temp_log1} l
 253                  WHERE action = 'login'
 254               GROUP BY userid
 255                 HAVING COUNT(id) > 0";
 256
 257         if ($logspresent && !stats_run_query($sql)) {
 258             $failed = true;
 259             break;
 260         }
 261
 262         stats_progress('1');
 263
 264         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 265
 266                 SELECT 'logins' AS stattype, $nextmidnight AS timeend, ".SITEID." AS courseid, 0,
 267                        COALESCE(SUM(statsreads), 0) as stat1, COUNT('x') as stat2
 268                   FROM {temp_stats_user_daily}
 269                  WHERE stattype = 'logins' AND timeend = $nextmidnight";
 270
 271         if ($logspresent && !stats_run_query($sql)) {
 272             $failed = true;
 273             break;
 274         }
 275         stats_progress('2');
 276
 277
 278         // Enrolments and active enrolled users
 279         //
 280         // Unfortunately, we do not know how many users were registered
 281         // at given times in history :-(
 282         // - stat1: enrolled users
 283         // - stat2: enrolled users active in this period
 284         // - SITEID is special case here, because it's all about default enrolment
 285         //   in that case, we'll count non-deleted users.
 286         //
 287
 288         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 289
 290                 SELECT 'enrolments' as stattype, $nextmidnight as timeend, courseid, roleid,
 291                         COUNT(DISTINCT userid) as stat1, 0 as stat2
 292                   FROM {temp_enroled}
 293               GROUP BY courseid, roleid";
 294
 295         if (!stats_run_query($sql)) {
 296             $failed = true;
 297             break;
 298         }
 299         stats_progress('3');
 300
 301         // Set stat2 to the number distinct users with role assignments in the course that were active
 302         // using table alias in UPDATE does not work in pg < 8.2
 303         $sql = "UPDATE {temp_stats_daily}
 304                    SET stat2 = (
 305
 306                     SELECT COUNT(DISTINCT userid)
 307                       FROM {temp_enroled} te
 308                      WHERE roleid = {temp_stats_daily}.roleid
 309                        AND courseid = {temp_stats_daily}.courseid
 310                        AND EXISTS (
 311
 312                         SELECT 'x'
 313                           FROM {temp_log1} l
 314                          WHERE l.course = {temp_stats_daily}.courseid
 315                            AND l.userid = te.userid
 316                                   )
 317                                )
 318                  WHERE {temp_stats_daily}.stattype = 'enrolments'
 319                    AND {temp_stats_daily}.timeend = $nextmidnight
 320                    AND {temp_stats_daily}.courseid IN (
 321
 322                     SELECT DISTINCT course FROM {temp_log2})";
 323
 324         if ($logspresent && !stats_run_query($sql, array('courselevel'=>CONTEXT_COURSE))) {
 325             $failed = true;
 326             break;
 327         }
 328         stats_progress('4');
 329
 330         // Now get course total enrolments (roleid==0) - except frontpage
 331         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 332
 333                 SELECT 'enrolments', $nextmidnight AS timeend, te.courseid AS courseid, 0 AS roleid,
 334                        COUNT(DISTINCT userid) AS stat1, 0 AS stat2
 335                   FROM {temp_enroled} te
 336               GROUP BY courseid
 337                 HAVING COUNT(DISTINCT userid) > 0";
 338
 339         if ($logspresent && !stats_run_query($sql)) {
 340             $failed = true;
 341             break;
 342         }
 343         stats_progress('5');
 344
 345         // Set stat 2 to the number of enrolled users who were active in the course
 346         $sql = "UPDATE {temp_stats_daily}
 347                    SET stat2 = (
 348
 349                     SELECT COUNT(DISTINCT te.userid)
 350                       FROM {temp_enroled} te
 351                      WHERE te.courseid = {temp_stats_daily}.courseid
 352                        AND EXISTS (
 353
 354                         SELECT 'x'
 355                           FROM {temp_log1} l
 356                          WHERE l.course = {temp_stats_daily}.courseid
 357                            AND l.userid = te.userid
 358                                   )
 359                                )
 360
 361                  WHERE {temp_stats_daily}.stattype = 'enrolments'
 362                    AND {temp_stats_daily}.timeend = $nextmidnight
 363                    AND {temp_stats_daily}.roleid = 0
 364                    AND {temp_stats_daily}.courseid IN (
 365
 366                     SELECT l.course
 367                       FROM {temp_log2} l
 368                      WHERE l.course <> ".SITEID.")";
 369
 370         if ($logspresent && !stats_run_query($sql, array())) {
 371             $failed = true;
 372             break;
 373         }
 374         stats_progress('6');
 375
 376         // Frontpage(==site) enrolments total
 377         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 378
 379                 SELECT 'enrolments', $nextmidnight, ".SITEID.", 0, $totalactiveusers AS stat1,
 380                        $dailyactiveusers AS stat2" .
 381                 $DB->sql_null_from_clause();
 382
 383         if ($logspresent && !stats_run_query($sql)) {
 384             $failed = true;
 385             break;
 386         }
 387         stats_progress('7');
 388
 389         // Default frontpage role enrolments are all site users (not deleted)
 390         if ($defaultfproleid) {
 391             // first remove default frontpage role counts if created by previous query
 392             $sql = "DELETE
 393                       FROM {temp_stats_daily}
 394                      WHERE stattype = 'enrolments'
 395                        AND courseid = ".SITEID."
 396                        AND roleid = $defaultfproleid
 397                        AND timeend = $nextmidnight";
 398
 399             if ($logspresent && !stats_run_query($sql)) {
 400                 $failed = true;
 401                 break;
 402             }
 403             stats_progress('8');
 404
 405             $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 406
 407                     SELECT 'enrolments', $nextmidnight, ".SITEID.", $defaultfproleid,
 408                            $totalactiveusers AS stat1, $dailyactiveusers AS stat2" .
 409                     $DB->sql_null_from_clause();;
 410
 411             if ($logspresent && !stats_run_query($sql)) {
 412                 $failed = true;
 413                 break;
 414             }
 415             stats_progress('9');
 416
 417         } else {
 418             stats_progress('x');
 419             stats_progress('x');
 420         }
 421
 422
 423         /// individual user stats (including not-logged-in) in each course, this is slow - reuse this data if possible
 424         list($viewactionssql, $params1) = $DB->get_in_or_equal($viewactions, SQL_PARAMS_NAMED, 'view');
 425         list($postactionssql, $params2) = $DB->get_in_or_equal($postactions, SQL_PARAMS_NAMED, 'post');
 426         $sql = "INSERT INTO {temp_stats_user_daily} (stattype, timeend, courseid, userid, statsreads, statswrites)
 427
 428                 SELECT 'activity' AS stattype, $nextmidnight AS timeend, course AS courseid, userid,
 429                        SUM(CASE WHEN action $viewactionssql THEN 1 ELSE 0 END) AS statsreads,
 430                        SUM(CASE WHEN action $postactionssql THEN 1 ELSE 0 END) AS statswrites
 431                   FROM {temp_log1} l
 432               GROUP BY userid, course";
 433
 434         if ($logspresent && !stats_run_query($sql, array_merge($params1, $params2))) {
 435             $failed = true;
 436             break;
 437         }
 438         stats_progress('10');
 439
 440
 441         /// How many view/post actions in each course total
 442         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 443
 444                 SELECT 'activity' AS stattype, $nextmidnight AS timeend, c.id AS courseid, 0,
 445                        SUM(CASE WHEN l.action $viewactionssql THEN 1 ELSE 0 END) AS stat1,
 446                        SUM(CASE WHEN l.action $postactionssql THEN 1 ELSE 0 END) AS stat2
 447                   FROM {course} c, {temp_log1} l
 448                  WHERE l.course = c.id
 449               GROUP BY c.id";
 450
 451         if ($logspresent && !stats_run_query($sql, array_merge($params1, $params2))) {
 452             $failed = true;
 453             break;
 454         }
 455         stats_progress('11');
 456
 457
 458         /// how many view actions for each course+role - excluding guests and frontpage
 459
 460         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 461
 462                 SELECT 'activity', $nextmidnight AS timeend, courseid, roleid, SUM(statsreads), SUM(statswrites)
 463                   FROM (
 464
 465                     SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
 466                       FROM {temp_stats_user_daily} sud, (
 467
 468                         SELECT DISTINCT te.userid, te.roleid, te.courseid
 469                           FROM {temp_enroled} te
 470                          WHERE te.roleid <> $guestrole
 471                            AND te.userid <> $guest
 472                                                         ) pl
 473
 474                      WHERE sud.userid = pl.userid
 475                        AND sud.courseid = pl.courseid
 476                        AND sud.timeend = $nextmidnight
 477                        AND sud.stattype='activity'
 478                        ) inline_view
 479
 480               GROUP BY courseid, roleid
 481                 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
 482
 483         if ($logspresent && !stats_run_query($sql, array('courselevel'=>CONTEXT_COURSE))) {
 484             $failed = true;
 485             break;
 486         }
 487         stats_progress('12');
 488
 489         /// how many view actions from guests only in each course - excluding frontpage
 490         /// normal users may enter course with temporary guest access too
 491
 492         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 493
 494                 SELECT 'activity', $nextmidnight AS timeend, courseid, $guestrole AS roleid,
 495                        SUM(statsreads), SUM(statswrites)
 496                   FROM (
 497
 498                     SELECT sud.courseid, sud.statsreads, sud.statswrites
 499                       FROM {temp_stats_user_daily} sud
 500                      WHERE sud.timeend = $nextmidnight
 501                        AND sud.courseid <> ".SITEID."
 502                        AND sud.stattype='activity'
 503                        AND (sud.userid = $guest OR sud.userid NOT IN (
 504
 505                         SELECT userid
 506                           FROM {temp_enroled} te
 507                          WHERE te.courseid = sud.courseid
 508                                                                      ))
 509                        ) inline_view
 510
 511               GROUP BY courseid
 512                 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
 513
 514         if ($logspresent && !stats_run_query($sql, array())) {
 515             $failed = true;
 516             break;
 517         }
 518         stats_progress('13');
 519
 520
 521         /// How many view actions for each role on frontpage - excluding guests, not-logged-in and default frontpage role
 522         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 523
 524                 SELECT 'activity', $nextmidnight AS timeend, courseid, roleid,
 525                        SUM(statsreads), SUM(statswrites)
 526                   FROM (
 527                     SELECT pl.courseid, pl.roleid, sud.statsreads, sud.statswrites
 528                       FROM {temp_stats_user_daily} sud, (
 529
 530                         SELECT DISTINCT ra.userid, ra.roleid, c.instanceid AS courseid
 531                           FROM {role_assignments} ra
 532                           JOIN {context} c ON c.id = ra.contextid
 533                          WHERE ra.contextid = :fpcontext
 534                            AND ra.roleid <> $defaultfproleid
 535                            AND ra.roleid <> $guestrole
 536                            AND ra.userid <> $guest
 537                                                    ) pl
 538                      WHERE sud.userid = pl.userid
 539                        AND sud.courseid = pl.courseid
 540                        AND sud.timeend = $nextmidnight
 541                        AND sud.stattype='activity'
 542                        ) inline_view
 543
 544               GROUP BY courseid, roleid
 545                 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
 546
 547         if ($logspresent && !stats_run_query($sql, array('fpcontext'=>$fpcontext->id))) {
 548             $failed = true;
 549             break;
 550         }
 551         stats_progress('14');
 552
 553
 554         // How many view actions for default frontpage role on frontpage only
 555         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 556
 557                 SELECT 'activity', timeend, courseid, $defaultfproleid AS roleid,
 558                        SUM(statsreads), SUM(statswrites)
 559                   FROM (
 560                     SELECT sud.timeend AS timeend, sud.courseid, sud.statsreads, sud.statswrites
 561                       FROM {temp_stats_user_daily} sud
 562                      WHERE sud.timeend = :nextm
 563                        AND sud.courseid = :siteid
 564                        AND sud.stattype='activity'
 565                        AND sud.userid <> $guest
 566                        AND sud.userid <> 0
 567                        AND sud.userid NOT IN (
 568
 569                         SELECT ra.userid
 570                           FROM {role_assignments} ra
 571                          WHERE ra.roleid <> $guestrole
 572                            AND ra.roleid <> $defaultfproleid
 573                            AND ra.contextid = :fpcontext)
 574                        ) inline_view
 575
 576               GROUP BY timeend, courseid
 577                 HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
 578
 579         if ($logspresent && !stats_run_query($sql, array('fpcontext'=>$fpcontext->id, 'siteid'=>SITEID, 'nextm'=>$nextmidnight))) {
 580             $failed = true;
 581             break;
 582         }
 583         stats_progress('15');
 584
 585         // How many view actions for guests or not-logged-in on frontpage
 586         $sql = "INSERT INTO {temp_stats_daily} (stattype, timeend, courseid, roleid, stat1, stat2)
 587
 588                 SELECT stattype, timeend, courseid, $guestrole AS roleid,
 589                        SUM(statsreads) AS stat1, SUM(statswrites) AS stat2
 590                   FROM (
 591                     SELECT sud.stattype, sud.timeend, sud.courseid,
 592                            sud.statsreads, sud.statswrites
 593                       FROM {temp_stats_user_daily} sud
 594                      WHERE (sud.userid = $guest OR sud.userid = 0)
 595                        AND sud.timeend = $nextmidnight
 596                        AND sud.courseid = ".SITEID."
 597                        AND sud.stattype='activity'
 598                        ) inline_view
 599                  GROUP BY stattype, timeend, courseid
 600                  HAVING SUM(statsreads) > 0 OR SUM(statswrites) > 0";
 601
 602         if ($logspresent && !stats_run_query($sql)) {
 603             $failed = true;
 604             break;
 605         }
 606         stats_progress('16');
 607
 608         stats_temp_table_clean();
 609
 610         stats_progress('out');
 611
 612         // remember processed days
 613         set_config('statslastdaily', $nextmidnight);
 614         $elapsed = time()-$daystart;
 615         mtrace("  finished until $nextmidnight: ".userdate($nextmidnight)." (in $elapsed s)");
 616         $total += $elapsed;
 617
 618         $timestart    = $nextmidnight;
 619         $nextmidnight = stats_get_next_day_start($nextmidnight);
 620     }
 621
 622     stats_temp_table_drop();
 623
 624     set_cron_lock('statsrunning', null);
 625
 626     if ($failed) {
 627         $days--;
 628         mtrace("...error occurred, completed $days days of statistics in {$total} s.");
 629         return false;
 630
 631     } else if ($timeout) {
 632         mtrace("...stopping early, reached maximum number of $maxdays days ({$total} s) - will continue next time.");
 633         return false;
 634
 635     } else {
 636         mtrace("...completed $days days of statistics in {$total} s.");
 637         return true;
 638     }
 639 }
 640
 641
 642 /**
 643  * Execute weekly statistics gathering
 644  * @return boolean success
 645  */
 646 function stats_cron_weekly() {
 647     global $CFG, $DB;
 648
 649     $now = time();
 650
 651     // read last execution date from db
 652     if (!$timestart = get_config(NULL, 'statslastweekly')) {
 653         $timestart = stats_get_base_daily(stats_get_start_from('weekly'));
 654         set_config('statslastweekly', $timestart);
 655     }
 656
 657     $nextstartweek = stats_get_next_week_start($timestart);
 658
 659     // are there any weeks that need to be processed?
 660     if ($now < $nextstartweek) {
 661         return true; // everything ok and up-to-date
 662     }
 663
 664     $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
 665
 666     if (!set_cron_lock('statsrunning', $now + $timeout)) {
 667         return false;
 668     }
 669
 670     // fisrt delete entries that should not be there yet
 671     $DB->delete_records_select('stats_weekly',      "timeend > $timestart");
 672     $DB->delete_records_select('stats_user_weekly', "timeend > $timestart");
 673
 674     mtrace("Running weekly statistics gathering, starting at $timestart:");
 675
 676     $weeks = 0;
 677     while ($now > $nextstartweek) {
 678         @set_time_limit($timeout - 200);
 679         $weeks++;
 680
 681         if ($weeks > 1) {
 682             // move the lock
 683             set_cron_lock('statsrunning', time() + $timeout, true);
 684         }
 685
 686         $logtimesql  = "l.time >= $timestart AND l.time < $nextstartweek";
 687         $stattimesql = "timeend > $timestart AND timeend <= $nextstartweek";
 688
 689         $weekstart = time();
 690         stats_progress('init');
 691
 692     /// process login info first
 693         $sql = "INSERT INTO {stats_user_weekly} (stattype, timeend, courseid, userid, statsreads)
 694
 695                 SELECT 'logins', timeend, courseid, userid, COUNT(statsreads)
 696                   FROM (
 697                            SELECT $nextstartweek AS timeend, ".SITEID." as courseid, l.userid, l.id AS statsreads
 698                              FROM {log} l
 699                             WHERE action = 'login' AND $logtimesql
 700                        ) inline_view
 701               GROUP BY timeend, courseid, userid
 702                 HAVING COUNT(statsreads) > 0";
 703
 704         $DB->execute($sql);
 705
 706         stats_progress('1');
 707
 708         $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
 709
 710                 SELECT 'logins' AS stattype, $nextstartweek AS timeend, ".SITEID." as courseid, 0,
 711                        COALESCE((SELECT SUM(statsreads)
 712                                    FROM {stats_user_weekly} s1
 713                                   WHERE s1.stattype = 'logins' AND timeend = $nextstartweek), 0) AS nstat1,
 714                        (SELECT COUNT('x')
 715                           FROM {stats_user_weekly} s2
 716                          WHERE s2.stattype = 'logins' AND timeend = $nextstartweek) AS nstat2" .
 717                 $DB->sql_null_from_clause();
 718
 719         $DB->execute($sql);
 720
 721         stats_progress('2');
 722
 723     /// now enrolments averages
 724         $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
 725
 726                 SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . "
 727                   FROM (
 728                            SELECT $nextstartweek AS ntimeend, courseid, roleid, stat1, stat2
 729                              FROM {stats_daily} sd
 730                             WHERE stattype = 'enrolments' AND $stattimesql
 731                        ) inline_view
 732               GROUP BY ntimeend, courseid, roleid";
 733
 734         $DB->execute($sql);
 735
 736         stats_progress('3');
 737
 738     /// activity read/write averages
 739         $sql = "INSERT INTO {stats_weekly} (stattype, timeend, courseid, roleid, stat1, stat2)
 740
 741                 SELECT 'activity', ntimeend, courseid, roleid, SUM(stat1), SUM(stat2)
 742                   FROM (
 743                            SELECT $nextstartweek AS ntimeend, courseid, roleid, stat1, stat2
 744                              FROM {stats_daily}
 745                             WHERE stattype = 'activity' AND $stattimesql
 746                        ) inline_view
 747               GROUP BY ntimeend, courseid, roleid";
 748
 749         $DB->execute($sql);
 750
 751         stats_progress('4');
 752
 753     /// user read/write averages
 754         $sql = "INSERT INTO {stats_user_weekly} (stattype, timeend, courseid, userid, statsreads, statswrites)
 755
 756                 SELECT 'activity', ntimeend, courseid, userid, SUM(statsreads), SUM(statswrites)
 757                   FROM (
 758                            SELECT $nextstartweek AS ntimeend, courseid, userid, statsreads, statswrites
 759                              FROM {stats_user_daily}
 760                             WHERE stattype = 'activity' AND $stattimesql
 761                        ) inline_view
 762               GROUP BY ntimeend, courseid, userid";
 763
 764         $DB->execute($sql);
 765
 766         stats_progress('5');
 767
 768         set_config('statslastweekly', $nextstartweek);
 769         $elapsed = time()-$weekstart;
 770         mtrace(" finished until $nextstartweek: ".userdate($nextstartweek) ." (in $elapsed s)");
 771
 772         $timestart     = $nextstartweek;
 773         $nextstartweek = stats_get_next_week_start($nextstartweek);
 774     }
 775
 776     set_cron_lock('statsrunning', null);
 777     mtrace("...completed $weeks weeks of statistics.");
 778     return true;
 779 }
 780
 781 /**
 782  * Execute monthly statistics gathering
 783  * @return boolean success
 784  */
 785 function stats_cron_monthly() {
 786     global $CFG, $DB;
 787
 788     $now = time();
 789
 790     // read last execution date from db
 791     if (!$timestart = get_config(NULL, 'statslastmonthly')) {
 792         $timestart = stats_get_base_monthly(stats_get_start_from('monthly'));
 793         set_config('statslastmonthly', $timestart);
 794     }
 795
 796     $nextstartmonth = stats_get_next_month_start($timestart);
 797
 798     // are there any months that need to be processed?
 799     if ($now < $nextstartmonth) {
 800         return true; // everything ok and up-to-date
 801     }
 802
 803     $timeout = empty($CFG->statsmaxruntime) ? 60*60*24 : $CFG->statsmaxruntime;
 804
 805     if (!set_cron_lock('statsrunning', $now + $timeout)) {
 806         return false;
 807     }
 808
 809     // fisr delete entries that should not be there yet
 810     $DB->delete_records_select('stats_monthly', "timeend > $timestart");
 811     $DB->delete_records_select('stats_user_monthly', "timeend > $timestart");
 812
 813     $startmonth = stats_get_base_monthly($now);
 814
 815
 816     mtrace("Running monthly statistics gathering, starting at $timestart:");
 817
 818     $months = 0;
 819     while ($now > $nextstartmonth) {
 820         @set_time_limit($timeout - 200);
 821         $months++;
 822
 823         if ($months > 1) {
 824             // move the lock
 825             set_cron_lock('statsrunning', time() + $timeout, true);
 826         }
 827
 828         $logtimesql  = "l.time >= $timestart AND l.time < $nextstartmonth";
 829         $stattimesql = "timeend > $timestart AND timeend <= $nextstartmonth";
 830
 831         $monthstart = time();
 832         stats_progress('init');
 833
 834     /// process login info first
 835         $sql = "INSERT INTO {stats_user_monthly} (stattype, timeend, courseid, userid, statsreads)
 836
 837                 SELECT 'logins', timeend, courseid, userid, COUNT(statsreads)
 838                   FROM (
 839                            SELECT $nextstartmonth AS timeend, ".SITEID." as courseid, l.userid, l.id AS statsreads
 840                              FROM {log} l
 841                             WHERE action = 'login' AND $logtimesql
 842                        ) inline_view
 843               GROUP BY timeend, courseid, userid";
 844
 845         $DB->execute($sql);
 846
 847         stats_progress('1');
 848
 849         $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
 850
 851                 SELECT 'logins' AS stattype, $nextstartmonth AS timeend, ".SITEID." as courseid, 0,
 852                        COALESCE((SELECT SUM(statsreads)
 853                                    FROM {stats_user_monthly} s1
 854                                   WHERE s1.stattype = 'logins' AND timeend = $nextstartmonth), 0) AS nstat1,
 855                        (SELECT COUNT('x')
 856                           FROM {stats_user_monthly} s2
 857                          WHERE s2.stattype = 'logins' AND timeend = $nextstartmonth) AS nstat2" .
 858                 $DB->sql_null_from_clause();
 859
 860         $DB->execute($sql);
 861
 862         stats_progress('2');
 863
 864     /// now enrolments averages
 865         $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
 866
 867                 SELECT 'enrolments', ntimeend, courseid, roleid, " . $DB->sql_ceil('AVG(stat1)') . ", " . $DB->sql_ceil('AVG(stat2)') . "
 868                   FROM (
 869                            SELECT $nextstartmonth AS ntimeend, courseid, roleid, stat1, stat2
 870                              FROM {stats_daily} sd
 871                             WHERE stattype = 'enrolments' AND $stattimesql
 872                        ) inline_view
 873               GROUP BY ntimeend, courseid, roleid";
 874
 875         $DB->execute($sql);
 876
 877         stats_progress('3');
 878
 879     /// activity read/write averages
 880         $sql = "INSERT INTO {stats_monthly} (stattype, timeend, courseid, roleid, stat1, stat2)
 881
 882                 SELECT 'activity', ntimeend, courseid, roleid, SUM(stat1), SUM(stat2)
 883                   FROM (
 884                            SELECT $nextstartmonth AS ntimeend, courseid, roleid, stat1, stat2
 885                              FROM {stats_daily}
 886                             WHERE stattype = 'activity' AND $stattimesql
 887                        ) inline_view
 888               GROUP BY ntimeend, courseid, roleid";
 889
 890         $DB->execute($sql);
 891
 892         stats_progress('4');
 893
 894     /// user read/write averages
 895         $sql = "INSERT INTO {stats_user_monthly} (stattype, timeend, courseid, userid, statsreads, statswrites)
 896
 897                 SELECT 'activity', ntimeend, courseid, userid, SUM(statsreads), SUM(statswrites)
 898                   FROM (
 899                            SELECT $nextstartmonth AS ntimeend, courseid, userid, statsreads, statswrites
 900                              FROM {stats_user_daily}
 901                             WHERE stattype = 'activity' AND $stattimesql
 902                        ) inline_view
 903               GROUP BY ntimeend, courseid, userid";
 904
 905         $DB->execute($sql);
 906
 907         stats_progress('5');
 908
 909         set_config('statslastmonthly', $nextstartmonth);
 910         $elapsed = time() - $monthstart;
 911         mtrace(" finished until $nextstartmonth: ".userdate($nextstartmonth) ." (in $elapsed s)");
 912
 913         $timestart      = $nextstartmonth;
 914         $nextstartmonth = stats_get_next_month_start($nextstartmonth);
 915     }
 916
 917     set_cron_lock('statsrunning', null);
 918     mtrace("...completed $months months of statistics.");
 919     return true;
 920 }
 921
 922 /**
 923  * Return starting date of stats processing
 924  * @param string $str name of table - daily, weekly or monthly
 925  * @return int timestamp
 926  */
 927 function stats_get_start_from($str) {
 928     global $CFG, $DB;
 929
 930     // are there any data in stats table? Should not be...
 931     if ($timeend = $DB->get_field_sql('SELECT MAX(timeend) FROM {stats_'.$str.'}')) {
 932         return $timeend;
 933     }
 934     // decide what to do based on our config setting (either all or none or a timestamp)
 935     switch ($CFG->statsfirstrun) {
 936         case 'all':
 937             if ($firstlog = $DB->get_field_sql('SELECT MIN(time) FROM {log}')) {
 938                 return $firstlog;
 939             }
 940         default:
 941             if (is_numeric($CFG->statsfirstrun)) {
 942                 return time() - $CFG->statsfirstrun;
 943             }
 944             // not a number? use next instead
 945         case 'none':
 946             return strtotime('-3 day', time());
 947     }
 948 }
 949
 950 /**
 951  * Start of day
 952  * @param int $time timestamp
 953  * @return start of day
 954  */
 955 function stats_get_base_daily($time=0) {
 956     global $CFG;
 957
 958     if (empty($time)) {
 959         $time = time();
 960     }
 961     if ($CFG->timezone == 99) {
 962         $time = strtotime(date('d-M-Y', $time));
 963         return $time;
 964     } else {
 965         $offset = get_timezone_offset($CFG->timezone);
 966         $gtime = $time + $offset;
 967         $gtime = intval($gtime / (60*60*24)) * 60*60*24;
 968         return $gtime - $offset;
 969     }
 970 }
 971
 972 /**
 973  * Start of week
 974  * @param int $time timestamp
 975  * @return start of week
 976  */
 977 function stats_get_base_weekly($time=0) {
 978     global $CFG;
 979
 980     $time = stats_get_base_daily($time);
 981     $startday = $CFG->calendar_startwday;
 982     if ($CFG->timezone == 99) {
 983         $thisday = date('w', $time);
 984     } else {
 985         $offset = get_timezone_offset($CFG->timezone);
 986         $gtime = $time + $offset;
 987         $thisday = gmdate('w', $gtime);
 988     }
 989     if ($thisday > $startday) {
 990         $time = $time - (($thisday - $startday) * 60*60*24);
 991     } else if ($thisday < $startday) {
 992         $time = $time - ((7 + $thisday - $startday) * 60*60*24);
 993     }
 994     return $time;
 995 }
 996
 997 /**
 998  * Start of month
 999  * @param int $time timestamp
1000  * @return start of month
1001  */
1002 function stats_get_base_monthly($time=0) {
1003     global $CFG;
1004
1005     if (empty($time)) {
1006         $time = time();
1007     }
1008     if ($CFG->timezone == 99) {
1009         return strtotime(date('1-M-Y', $time));
1010
1011     } else {
1012         $time = stats_get_base_daily($time);
1013         $offset = get_timezone_offset($CFG->timezone);
1014         $gtime = $time + $offset;
1015         $day = gmdate('d', $gtime);
1016         if ($day == 1) {
1017             return $time;
1018         }
1019         return $gtime - (($day-1) * 60*60*24);
1020     }
1021 }
1022
1023 /**
1024  * Start of next day
1025  * @param int $time timestamp
1026  * @return start of next day
1027  */
1028 function stats_get_next_day_start($time) {
1029     $next = stats_get_base_daily($time);
1030     $next = $next + 60*60*26;
1031     $next = stats_get_base_daily($next);
1032     if ($next <= $time) {
1033         //DST trouble - prevent infinite loops
1034         $next = $next + 60*60*24;
1035     }
1036     return $next;
1037 }
1038
1039 /**
1040  * Start of next week
1041  * @param int $time timestamp
1042  * @return start of next week
1043  */
1044 function stats_get_next_week_start($time) {
1045     $next = stats_get_base_weekly($time);
1046     $next = $next + 60*60*24*9;
1047     $next = stats_get_base_weekly($next);
1048     if ($next <= $time) {
1049         //DST trouble - prevent infinite loops
1050         $next = $next + 60*60*24*7;
1051     }
1052     return $next;
1053 }
1054
1055 /**
1056  * Start of next month
1057  * @param int $time timestamp
1058  * @return start of next month
1059  */
1060 function stats_get_next_month_start($time) {
1061     $next = stats_get_base_monthly($time);
1062     $next = $next + 60*60*24*33;
1063     $next = stats_get_base_monthly($next);
1064     if ($next <= $time) {
1065         //DST trouble - prevent infinite loops
1066         $next = $next + 60*60*24*31;
1067     }
1068     return $next;
1069 }
1070
1071 /**
1072  * Remove old stats data
1073  */
1074 function stats_clean_old() {
1075     global $DB;
1076     mtrace("Running stats cleanup tasks...");
1077     $deletebefore =  stats_get_base_monthly();
1078
1079     // delete dailies older than 3 months (to be safe)
1080     $deletebefore = strtotime('-3 months', $deletebefore);
1081     $DB->delete_records_select('stats_daily',      "timeend < $deletebefore");
1082     $DB->delete_records_select('stats_user_daily', "timeend < $deletebefore");
1083
1084     // delete weeklies older than 9  months (to be safe)
1085     $deletebefore = strtotime('-6 months', $deletebefore);
1086     $DB->delete_records_select('stats_weekly',      "timeend < $deletebefore");
1087     $DB->delete_records_select('stats_user_weekly', "timeend < $deletebefore");
1088
1089     // don't delete monthlies
1090
1091     mtrace("...stats cleanup finished");
1092 }
1093
1094 function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) {
1095     global $CFG, $DB;
1096
1097     $param = new stdClass();
1098     $param->params = array();
1099
1100     if ($time < 10) { // dailies
1101         // number of days to go back = 7* time
1102         $param->table = 'daily';
1103         $param->timeafter = strtotime("-".($time*7)." days",stats_get_base_daily());
1104     } elseif ($time < 20) { // weeklies
1105         // number of weeks to go back = time - 10 * 4 (weeks) + base week
1106         $param->table = 'weekly';
1107         $param->timeafter = strtotime("-".(($time - 10)*4)." weeks",stats_get_base_weekly());
1108     } else { // monthlies.
1109         // number of months to go back = time - 20 * months + base month
1110         $param->table = 'monthly';
1111         $param->timeafter = strtotime("-".($time - 20)." months",stats_get_base_monthly());
1112     }
1113
1114     $param->extras = '';
1115
1116     switch ($report) {
1117     // ******************** STATS_MODE_GENERAL ******************** //
1118     case STATS_REPORT_LOGINS:
1119         $param->fields = 'timeend,sum(stat1) as line1,sum(stat2) as line2';
1120         $param->fieldscomplete = true;
1121         $param->stattype = 'logins';
1122         $param->line1 = get_string('statslogins');
1123         $param->line2 = get_string('statsuniquelogins');
1124         if ($courseid == SITEID) {
1125             $param->extras = 'GROUP BY timeend';
1126         }
1127         break;
1128
1129     case STATS_REPORT_READS:
1130         $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat1 as line1';
1131         $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
1132         $param->aggregategroupby = 'roleid';
1133         $param->stattype = 'activity';
1134         $param->crosstab = true;
1135         $param->extras = 'GROUP BY timeend,roleid,stat1';
1136         if ($courseid == SITEID) {
1137             $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1) as line1';
1138             $param->extras = 'GROUP BY timeend,roleid';
1139         }
1140         break;
1141
1142     case STATS_REPORT_WRITES:
1143         $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat2 as line1';
1144         $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
1145         $param->aggregategroupby = 'roleid';
1146         $param->stattype = 'activity';
1147         $param->crosstab = true;
1148         $param->extras = 'GROUP BY timeend,roleid,stat2';
1149         if ($courseid == SITEID) {
1150             $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat2) as line1';
1151             $param->extras = 'GROUP BY timeend,roleid';
1152         }
1153         break;
1154
1155     case STATS_REPORT_ACTIVITY:
1156         $param->fields = $DB->sql_concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1+stat2) as line1';
1157         $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
1158         $param->aggregategroupby = 'roleid';
1159         $param->stattype = 'activity';
1160         $param->crosstab = true;
1161         $param->extras = 'GROUP BY timeend,roleid';
1162         if ($courseid == SITEID) {
1163             $param->extras = 'GROUP BY timeend,roleid';
1164         }
1165         break;
1166
1167     case STATS_REPORT_ACTIVITYBYROLE;
1168         $param->fields = 'stat1 AS line1, stat2 AS line2';
1169         $param->stattype = 'activity';
1170         $rolename = $DB->get_field('role','name', array('id'=>$roleid));
1171         $param->line1 = $rolename . get_string('statsreads');
1172         $param->line2 = $rolename . get_string('statswrites');
1173         if ($courseid == SITEID) {
1174             $param->extras = 'GROUP BY timeend';
1175         }
1176         break;
1177
1178     // ******************** STATS_MODE_DETAILED ******************** //
1179     case STATS_REPORT_USER_ACTIVITY:
1180         $param->fields = 'statsreads as line1, statswrites as line2';
1181         $param->line1 = get_string('statsuserreads');
1182         $param->line2 = get_string('statsuserwrites');
1183         $param->stattype = 'activity';
1184         break;
1185
1186     case STATS_REPORT_USER_ALLACTIVITY:
1187         $param->fields = 'statsreads+statswrites as line1';
1188         $param->line1 = get_string('statsuseractivity');
1189         $param->stattype = 'activity';
1190         break;
1191
1192     case STATS_REPORT_USER_LOGINS:
1193         $param->fields = 'statsreads as line1';
1194         $param->line1 = get_string('statsuserlogins');
1195         $param->stattype = 'logins';
1196         break;
1197
1198     case STATS_REPORT_USER_VIEW:
1199         $param->fields = 'statsreads as line1, statswrites as line2, statsreads+statswrites as line3';
1200         $param->line1 = get_string('statsuserreads');
1201         $param->line2 = get_string('statsuserwrites');
1202         $param->line3 = get_string('statsuseractivity');
1203         $param->stattype = 'activity';
1204         break;
1205
1206     // ******************** STATS_MODE_RANKED ******************** //
1207     case STATS_REPORT_ACTIVE_COURSES:
1208         $param->fields = 'sum(stat1+stat2) AS line1';
1209         $param->stattype = 'activity';
1210         $param->orderby = 'line1 DESC';
1211         $param->line1 = get_string('activity');
1212         $param->graphline = 'line1';
1213         break;
1214
1215     case STATS_REPORT_ACTIVE_COURSES_WEIGHTED:
1216         $threshold = 0;
1217         if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
1218             $threshold = $CFG->statsuserthreshold;
1219         }
1220         $param->fields = '';
1221         $param->sql = 'SELECT activity.courseid, activity.all_activity AS line1, enrolments.highest_enrolments AS line2,
1222                         activity.all_activity / enrolments.highest_enrolments as line3
1223                        FROM (
1224                             SELECT courseid, sum(stat1+stat2) AS all_activity
1225                               FROM {stats_'.$param->table.'}
1226                              WHERE stattype=\'activity\' AND timeend >= '.(int)$param->timeafter.' AND roleid = 0 GROUP BY courseid
1227                        ) activity
1228                        INNER JOIN
1229                             (
1230                             SELECT courseid, max(stat1) AS highest_enrolments
1231                               FROM {stats_'.$param->table.'}
1232                              WHERE stattype=\'enrolments\' AND timeend >= '.(int)$param->timeafter.' AND stat1 > '.(int)$threshold.'
1233                           GROUP BY courseid
1234                       ) enrolments
1235                       ON (activity.courseid = enrolments.courseid)
1236                       ORDER BY line3 DESC';
1237         $param->line1 = get_string('activity');
1238         $param->line2 = get_string('users');
1239         $param->line3 = get_string('activityweighted');
1240         $param->graphline = 'line3';
1241         break;
1242
1243     case STATS_REPORT_PARTICIPATORY_COURSES:
1244         $threshold = 0;
1245         if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
1246             $threshold = $CFG->statsuserthreshold;
1247         }
1248         $param->fields = '';
1249         $param->sql = 'SELECT courseid, ' . $DB->sql_ceil('avg(all_enrolments)') . ' as line1, ' .
1250                          $DB->sql_ceil('avg(active_enrolments)') . ' as line2, avg(proportion_active) AS line3
1251                        FROM (
1252                            SELECT courseid, timeend, stat2 as active_enrolments,
1253                                   stat1 as all_enrolments, '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' AS proportion_active
1254                              FROM {stats_'.$param->table.'}
1255                             WHERE stattype=\'enrolments\' AND roleid = 0 AND stat1 > '.(int)$threshold.'
1256                        ) aq
1257                        WHERE timeend >= '.(int)$param->timeafter.'
1258                        GROUP BY courseid
1259                        ORDER BY line3 DESC';
1260
1261         $param->line1 = get_string('users');
1262         $param->line2 = get_string('activeusers');
1263         $param->line3 = get_string('participationratio');
1264         $param->graphline = 'line3';
1265         break;
1266
1267     case STATS_REPORT_PARTICIPATORY_COURSES_RW:
1268         $param->fields = '';
1269         $param->sql =  'SELECT courseid, sum(views) AS line1, sum(posts) AS line2,
1270                            avg(proportion_active) AS line3
1271                          FROM (
1272                            SELECT courseid, timeend, stat1 as views, stat2 AS posts,
1273                                   '.$DB->sql_cast_char2real('stat2').'/'.$DB->sql_cast_char2real('stat1').' as proportion_active
1274                              FROM {stats_'.$param->table.'}
1275                             WHERE stattype=\'activity\' AND roleid = 0 AND stat1 > 0
1276                        ) aq
1277                        WHERE timeend >= '.(int)$param->timeafter.'
1278                        GROUP BY courseid
1279                        ORDER BY line3 DESC';
1280         $param->line1 = get_string('views');
1281         $param->line2 = get_string('posts');
1282         $param->line3 = get_string('participationratio');
1283         $param->graphline = 'line3';
1284         break;
1285     }
1286
1287     /*
1288     if ($courseid == SITEID && $mode != STATS_MODE_RANKED) { // just aggregate all courses.
1289         $param->fields = preg_replace('/(?:sum)([a-zA-Z0-9+_]*)\W+as\W+([a-zA-Z0-9_]*)/i','sum($1) as $2',$param->fields);
1290         $param->extras = ' GROUP BY timeend'.((!empty($param->aggregategroupby)) ? ','.$param->aggregategroupby : '');
1291     }
1292     */
1293     //TODO must add the SITEID reports to the rest of the reports.
1294     return $param;
1295 }
1296
1297 function stats_get_view_actions() {
1298     return array('view','view all','history');
1299 }
1300
1301 function stats_get_post_actions() {
1302     return array('add','delete','edit','add mod','delete mod','edit section'.'enrol','loginas','new','unenrol','update','update mod');
1303 }
1304
1305 function stats_get_action_names($str) {
1306     global $CFG, $DB;
1307
1308     $mods = $DB->get_records('modules');
1309     $function = 'stats_get_'.$str.'_actions';
1310     $actions = $function();
1311     foreach ($mods as $mod) {
1312         $file = $CFG->dirroot.'/mod/'.$mod->name.'/lib.php';
1313         if (!is_readable($file)) {
1314             continue;
1315         }
1316         require_once($file);
1317         $function = $mod->name.'_get_'.$str.'_actions';
1318         if (function_exists($function)) {
1319             $mod_actions = $function();
1320             if (is_array($mod_actions)) {
1321                 $actions = array_merge($actions, $mod_actions);
1322             }
1323         }
1324     }
1325
1326     // The array_values() forces a stack-like array
1327     // so we can later loop over safely...
1328     $actions =  array_values(array_unique($actions));
1329     $c = count($actions);
1330     for ($n=0;$n<$c;$n++) {
1331         $actions[$n] = $actions[$n];
1332     }
1333     return $actions;
1334 }
1335
1336 function stats_get_time_options($now,$lastweekend,$lastmonthend,$earliestday,$earliestweek,$earliestmonth) {
1337
1338     $now = stats_get_base_daily(time());
1339     // it's really important that it's TIMEEND in the table. ie, tuesday 00:00:00 is monday night.
1340     // so we need to take a day off here (essentially add a day to $now
1341     $now += 60*60*24;
1342
1343     $timeoptions = array();
1344
1345     if ($now - (60*60*24*7) >= $earliestday) {
1346         $timeoptions[STATS_TIME_LASTWEEK] = get_string('numweeks','moodle',1);
1347     }
1348     if ($now - (60*60*24*14) >= $earliestday) {
1349         $timeoptions[STATS_TIME_LAST2WEEKS] = get_string('numweeks','moodle',2);
1350     }
1351     if ($now - (60*60*24*21) >= $earliestday) {
1352         $timeoptions[STATS_TIME_LAST3WEEKS] = get_string('numweeks','moodle',3);
1353     }
1354     if ($now - (60*60*24*28) >= $earliestday) {
1355         $timeoptions[STATS_TIME_LAST4WEEKS] = get_string('numweeks','moodle',4);// show dailies up to (including) here.
1356     }
1357     if ($lastweekend - (60*60*24*56) >= $earliestweek) {
1358         $timeoptions[STATS_TIME_LAST2MONTHS] = get_string('nummonths','moodle',2);
1359     }
1360     if ($lastweekend - (60*60*24*84) >= $earliestweek) {
1361         $timeoptions[STATS_TIME_LAST3MONTHS] = get_string('nummonths','moodle',3);
1362     }
1363     if ($lastweekend - (60*60*24*112) >= $earliestweek) {
1364         $timeoptions[STATS_TIME_LAST4MONTHS] = get_string('nummonths','moodle',4);
1365     }
1366     if ($lastweekend - (60*60*24*140) >= $earliestweek) {
1367         $timeoptions[STATS_TIME_LAST5MONTHS] = get_string('nummonths','moodle',5);
1368     }
1369     if ($lastweekend - (60*60*24*168) >= $earliestweek) {
1370         $timeoptions[STATS_TIME_LAST6MONTHS] = get_string('nummonths','moodle',6); // show weeklies up to (including) here
1371     }
1372     if (strtotime('-7 months',$lastmonthend) >= $earliestmonth) {
1373         $timeoptions[STATS_TIME_LAST7MONTHS] = get_string('nummonths','moodle',7);
1374     }
1375     if (strtotime('-8 months',$lastmonthend) >= $earliestmonth) {
1376         $timeoptions[STATS_TIME_LAST8MONTHS] = get_string('nummonths','moodle',8);
1377     }
1378     if (strtotime('-9 months',$lastmonthend) >= $earliestmonth) {
1379         $timeoptions[STATS_TIME_LAST9MONTHS] = get_string('nummonths','moodle',9);
1380     }
1381     if (strtotime('-10 months',$lastmonthend) >= $earliestmonth) {
1382         $timeoptions[STATS_TIME_LAST10MONTHS] = get_string('nummonths','moodle',10);
1383     }
1384     if (strtotime('-11 months',$lastmonthend) >= $earliestmonth) {
1385         $timeoptions[STATS_TIME_LAST11MONTHS] = get_string('nummonths','moodle',11);
1386     }
1387     if (strtotime('-1 year',$lastmonthend) >= $earliestmonth) {
1388         $timeoptions[STATS_TIME_LASTYEAR] = get_string('lastyear');
1389     }
1390
1391     $years = (int)date('y', $now) - (int)date('y', $earliestmonth);
1392     if ($years > 1) {
1393         for($i = 2; $i <= $years; $i++) {
1394             $timeoptions[$i*12+20] = get_string('numyears', 'moodle', $i);
1395         }
1396     }
1397
1398     return $timeoptions;
1399 }
1400
1401 function stats_get_report_options($courseid,$mode) {
1402     global $CFG, $DB;
1403
1404     $reportoptions = array();
1405
1406     switch ($mode) {
1407     case STATS_MODE_GENERAL:
1408         $reportoptions[STATS_REPORT_ACTIVITY] = get_string('statsreport'.STATS_REPORT_ACTIVITY);
1409         if ($courseid != SITEID && $context = context_course::instance($courseid)) {
1410             $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';
1411             if ($roles = $DB->get_records_sql($sql, array('courseid' => $courseid))) {
1412                 foreach ($roles as $role) {
1413                     $reportoptions[STATS_REPORT_ACTIVITYBYROLE.$role->id] = get_string('statsreport'.STATS_REPORT_ACTIVITYBYROLE). ' '.$role->name;
1414                 }
1415             }
1416         }
1417         $reportoptions[STATS_REPORT_READS] = get_string('statsreport'.STATS_REPORT_READS);
1418         $reportoptions[STATS_REPORT_WRITES] = get_string('statsreport'.STATS_REPORT_WRITES);
1419         if ($courseid == SITEID) {
1420             $reportoptions[STATS_REPORT_LOGINS] = get_string('statsreport'.STATS_REPORT_LOGINS);
1421         }
1422
1423         break;
1424     case STATS_MODE_DETAILED:
1425         $reportoptions[STATS_REPORT_USER_ACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ACTIVITY);
1426         $reportoptions[STATS_REPORT_USER_ALLACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ALLACTIVITY);
1427         if (has_capability('report/stats:view', context_system::instance())) {
1428             $site = get_site();
1429             $reportoptions[STATS_REPORT_USER_LOGINS] = get_string('statsreport'.STATS_REPORT_USER_LOGINS);
1430         }
1431         break;
1432     case STATS_MODE_RANKED:
1433         if (has_capability('report/stats:view', context_system::instance())) {
1434             $reportoptions[STATS_REPORT_ACTIVE_COURSES] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES);
1435             $reportoptions[STATS_REPORT_ACTIVE_COURSES_WEIGHTED] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES_WEIGHTED);
1436             $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES);
1437             $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES_RW] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES_RW);
1438         }
1439         break;
1440     }
1441
1442     return $reportoptions;
1443 }
1444
1445 function stats_fix_zeros($stats,$timeafter,$timestr,$line2=true,$line3=false) {
1446
1447     if (empty($stats)) {
1448         return;
1449     }
1450
1451     $timestr = str_replace('user_','',$timestr); // just in case.
1452     $fun = 'stats_get_base_'.$timestr;
1453
1454     $now = $fun();
1455
1456     $times = array();
1457     // add something to timeafter since it is our absolute base
1458     $actualtimes = array();
1459     foreach ($stats as $statid=>$s) {
1460         //normalize the times in stats - those might have been created in different timezone, DST etc.
1461         $s->timeend = $fun($s->timeend + 60*60*5);
1462         $stats[$statid] = $s;
1463
1464         $actualtimes[] = $s->timeend;
1465     }
1466
1467     $timeafter = array_pop(array_values($actualtimes));
1468
1469     while ($timeafter < $now) {
1470         $times[] = $timeafter;
1471         if ($timestr == 'daily') {
1472             $timeafter = stats_get_next_day_start($timeafter);
1473         } else if ($timestr == 'weekly') {
1474             $timeafter = stats_get_next_week_start($timeafter);
1475         } else if ($timestr == 'monthly') {
1476             $timeafter = stats_get_next_month_start($timeafter);
1477         } else {
1478             return $stats; // this will put us in a never ending loop.
1479         }
1480     }
1481
1482     foreach ($times as $count => $time) {
1483         if (!in_array($time,$actualtimes) && $count != count($times) -1) {
1484             $newobj = new StdClass;
1485             $newobj->timeend = $time;
1486             $newobj->id = 0;
1487             $newobj->roleid = 0;
1488             $newobj->line1 = 0;
1489             if (!empty($line2)) {
1490                 $newobj->line2 = 0;
1491             }
1492             if (!empty($line3)) {
1493                 $newobj->line3 = 0;
1494             }
1495             $newobj->zerofixed = true;
1496             $stats[] = $newobj;
1497         }
1498     }
1499
1500     usort($stats,"stats_compare_times");
1501     return $stats;
1502
1503 }
1504
1505 // helper function to sort arrays by $obj->timeend
1506 function stats_compare_times($a,$b) {
1507    if ($a->timeend == $b->timeend) {
1508        return 0;
1509    }
1510    return ($a->timeend > $b->timeend) ? -1 : 1;
1511 }
1512
1513 function stats_check_uptodate($courseid=0) {
1514     global $CFG, $DB;
1515
1516     if (empty($courseid)) {
1517         $courseid = SITEID;
1518     }
1519
1520     $latestday = stats_get_start_from('daily');
1521
1522     if ((time() - 60*60*24*2) < $latestday) { // we're ok
1523         return NULL;
1524     }
1525
1526     $a = new stdClass();
1527     $a->daysdone = $DB->get_field_sql("SELECT COUNT(DISTINCT(timeend)) FROM {stats_daily}");
1528
1529     // how many days between the last day and now?
1530     $a->dayspending = ceil((stats_get_base_daily() - $latestday)/(60*60*24));
1531
1532     if ($a->dayspending == 0 && $a->daysdone != 0) {
1533         return NULL; // we've only just started...
1534     }
1535
1536     //return error as string
1537     return get_string('statscatchupmode','error',$a);
1538 }
1539
1540 /**
1541  * Create temporary tables to speed up log generation
1542  */
1543 function stats_temp_table_create() {
1544     global $CFG, $DB;
1545
1546     $dbman = $DB->get_manager(); // We are going to use database_manager services
1547
1548     stats_temp_table_drop();
1549
1550     $xmlfile  = $CFG->dirroot . '/lib/db/install.xml';
1551     $tables   = array();
1552
1553     // Allows for the additional xml files to be used (if necessary)
1554     $files    = array(
1555         $xmlfile  => array(
1556             'stats_daily'           => array('temp_stats_daily'),
1557             'stats_user_daily'      => array('temp_stats_user_daily'),
1558             'temp_enroled_template' => array('temp_enroled'),
1559             'temp_log_template'     => array('temp_log1', 'temp_log2'),
1560         ),
1561     );
1562
1563     foreach ($files as $file => $contents) {
1564
1565         $xmldb_file = new xmldb_file($file);
1566         if (!$xmldb_file->fileExists()) {
1567             throw new ddl_exception('ddlxmlfileerror', null, 'File does not exist');
1568         }
1569         $loaded = $xmldb_file->loadXMLStructure();
1570         if (!$loaded || !$xmldb_file->isLoaded()) {
1571             throw new ddl_exception('ddlxmlfileerror', null, 'not loaded??');
1572         }
1573         $xmldb_structure = $xmldb_file->getStructure();
1574
1575         foreach ($contents as $template => $names) {
1576             $table = $xmldb_structure->getTable($template);
1577
1578             if (is_null($table)) {
1579                 throw new ddl_exception('ddlunknowntable', null, 'The table '. $name .' is not defined in the file '. $xmlfile);
1580             }
1581             $table->setNext(null);
1582             $table->setPrevious(null);
1583
1584             foreach ($names as $name) {
1585                 $named = clone $table;
1586                 $named->setName($name);
1587                 $tables[$name] = $named;
1588             }
1589         }
1590     }
1591
1592     try {
1593
1594         foreach ($tables as $table) {
1595             $dbman->create_temp_table($table);
1596         }
1597
1598     } catch (Exception $e) {
1599         mtrace('Temporary table creation failed: '. $e->getMessage());
1600         return false;
1601     }
1602
1603     return true;
1604 }
1605
1606 /**
1607  * Deletes summary logs table for stats calculation
1608  */
1609 function stats_temp_table_drop() {
1610     global $DB;
1611
1612     $dbman = $DB->get_manager();
1613
1614     $tables = array('temp_log1', 'temp_log2', 'temp_stats_daily', 'temp_stats_user_daily', 'temp_enroled');
1615
1616     foreach ($tables as $name) {
1617
1618         if ($dbman->table_exists($name)) {
1619             $table = new xmldb_table($name);
1620
1621             try {
1622                 $dbman->drop_table($table);
1623             } catch (Exception $e) {
1624                 mtrace("Error occured while dropping temporary tables!");
1625             }
1626         }
1627     }
1628 }
1629
1630 /**
1631  * Fills the temporary stats tables with new data
1632  *
1633  * This function is meant to be called once at the start of stats generation
1634  *
1635  * @param timestart timestamp of the start time of logs view
1636  * @param timeend timestamp of the end time of logs view
1637  * @returns boolen success (true) or failure(false)
1638  */
1639 function stats_temp_table_setup() {
1640     global $DB;
1641
1642     $sql = "INSERT INTO {temp_enroled} (userid, courseid, roleid)
1643
1644                SELECT ue.userid, e.courseid, ra.roleid
1645                 FROM {role_assignments} ra
1646                 JOIN {context} c ON (c.id = ra.contextid AND c.contextlevel = :courselevel)
1647                 JOIN {enrol} e ON e.courseid = c.instanceid
1648                 JOIN {user_enrolments} ue ON (ue.enrolid = e.id AND ue.userid = ra.userid)";
1649
1650     return stats_run_query($sql, array('courselevel' => CONTEXT_COURSE));
1651 }
1652
1653 /**
1654  * Fills the temporary stats tables with new data
1655  *
1656  * This function is meant to be called to get a new day of data
1657  *
1658  * @param timestart timestamp of the start time of logs view
1659  * @param timeend timestamp of the end time of logs view
1660  * @returns boolen success (true) or failure(false)
1661  */
1662 function stats_temp_table_fill($timestart, $timeend) {
1663     global $DB;
1664
1665     $sql = 'INSERT INTO {temp_log1} (userid, course, action)
1666
1667             SELECT userid, course, action FROM {log}
1668              WHERE time >= ? AND time < ?';
1669
1670     $DB->execute($sql, array($timestart, $timeend));
1671
1672     $sql = 'INSERT INTO {temp_log2} (userid, course, action)
1673
1674             SELECT userid, course, action FROM {temp_log1}';
1675
1676     $DB->execute($sql);
1677
1678     return true;
1679 }
1680
1681
1682 /**
1683  * Deletes summary logs table for stats calculation
1684  *
1685  * @returns boolen success (true) or failure(false)
1686  */
1687 function stats_temp_table_clean() {
1688     global $DB;
1689
1690     $sql = array();
1691
1692     $sql['up1'] = 'INSERT INTO {stats_daily} (courseid, roleid, stattype, timeend, stat1, stat2)
1693
1694                    SELECT courseid, roleid, stattype, timeend, stat1, stat2 FROM {temp_stats_daily}';
1695
1696     $sql['up2'] = 'INSERT INTO {stats_user_daily}
1697                                (courseid, userid, roleid, timeend, statsreads, statswrites, stattype)
1698
1699                    SELECT courseid, userid, roleid, timeend, statsreads, statswrites, stattype
1700                      FROM {temp_stats_user_daily}';
1701
1702     foreach ($sql as $id => $query) {
1703         if (! stats_run_query($query)) {
1704             mtrace("Error during table cleanup!");
1705             return false;
1706         }
1707     }
1708
1709     $tables = array('temp_log1', 'temp_log2', 'temp_stats_daily', 'temp_stats_user_daily');
1710
1711     foreach ($tables as $name) {
1712         $DB->delete_records($name);
1713     }
1714
1715     return true;
1716 }