Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

  • Bug fixes for general core bugs in 4.1.x will end 13 November 2023 (12 months).
  • Bug fixes for security issues in 4.1.x will end 10 November 2025 (36 months).
  • PHP version: minimum PHP 7.4.0 Note: minimum PHP version has increased since Moodle 4.0. PHP 8.0.x is supported too.
/lib/ -> statslib.php (source)

Differences Between: [Versions 310 and 401] [Versions 39 and 401] [Versions 401 and 402] [Versions 401 and 403]

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