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