Search moodle.org's
Developer Documentation

  • Bug fixes for general core bugs in 3.11.x will end 9 May 2022 (12 months).
  • Bug fixes for security issues in 3.11.x will end 14 November 2022 (18 months).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.
  • /lib/ -> statslib.php (source)

    Differences Between: [Versions 35 and 311]

       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      $tables['temp_enroled'] = $table;
    1636  
    1637  
    1638      $table = new xmldb_table('temp_log1');
    1639      $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
    1640      $table->add_field('userid', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
    1641      $table->add_field('course', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL, null, '0');
    1642      $table->add_field('action', XMLDB_TYPE_CHAR, 40, null, XMLDB_NOTNULL, null, null);
    1643      $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
    1644      $table->add_index('action', XMLDB_INDEX_NOTUNIQUE, array('action'));
    1645      $table->add_index('course', XMLDB_INDEX_NOTUNIQUE, array('course'));
    1646      $table->add_index('user', XMLDB_INDEX_NOTUNIQUE, array('userid'));
    1647      $table->add_index('usercourseaction', XMLDB_INDEX_NOTUNIQUE, array('userid','course','action'));
    1648      $tables['temp_log1'] = $table;
    1649  
    1650      /// temp_log2 is exactly the same as temp_log1.
    1651      $tables['temp_log2'] = clone $tables['temp_log1'];
    1652      $tables['temp_log2']->setName('temp_log2');
    1653  
    1654      try {
    1655  
    1656          foreach ($tables as $table) {
    1657              $dbman->create_temp_table($table);
    1658          }
    1659  
    1660      } catch (Exception $e) {
    1661          mtrace('Temporary table creation failed: '. $e->getMessage());
    1662          return false;
    1663      }
    1664  
    1665      return true;
    1666  }
    1667  
    1668  /**
    1669   * Deletes summary logs table for stats calculation
    1670   */
    1671  function stats_temp_table_drop() {
    1672      global $DB;
    1673  
    1674      $dbman = $DB->get_manager();
    1675  
    1676      $tables = array('temp_log1', 'temp_log2', 'temp_stats_daily', 'temp_stats_user_daily', 'temp_enroled');
    1677  
    1678      foreach ($tables as $name) {
    1679  
    1680          if ($dbman->table_exists($name)) {
    1681              $table = new xmldb_table($name);
    1682  
    1683              try {
    1684                  $dbman->drop_table($table);
    1685              } catch (Exception $e) {
    1686                  mtrace("Error occured while dropping temporary tables!");
    1687              }
    1688          }
    1689      }
    1690  }
    1691  
    1692  /**
    1693   * Fills the temporary stats tables with new data
    1694   *
    1695   * This function is meant to be called once at the start of stats generation
    1696   *
    1697   * @param int timestart timestamp of the start time of logs view
    1698   * @param int timeend timestamp of the end time of logs view
    1699   * @return bool success (true) or failure(false)
    1700   */
    1701  function stats_temp_table_setup() {
    1702      global $DB;
    1703  
    1704      $sql = "INSERT INTO {temp_enroled} (userid, courseid, roleid)
    1705  
    1706                 SELECT ue.userid, e.courseid, ra.roleid
    1707                  FROM {role_assignments} ra
    1708                  JOIN {context} c ON (c.id = ra.contextid AND c.contextlevel = :courselevel)
    1709                  JOIN {enrol} e ON e.courseid = c.instanceid
    1710                  JOIN {user_enrolments} ue ON (ue.enrolid = e.id AND ue.userid = ra.userid)";
    1711  
    1712      return stats_run_query($sql, array('courselevel' => CONTEXT_COURSE));
    1713  }
    1714  
    1715  /**
    1716   * Fills the temporary stats tables with new data
    1717   *
    1718   * This function is meant to be called to get a new day of data
    1719   *
    1720   * @param int timestamp of the start time of logs view
    1721   * @param int timestamp of the end time of logs view
    1722   * @return bool success (true) or failure(false)
    1723   */
    1724  function stats_temp_table_fill($timestart, $timeend) {
    1725      global $DB;
    1726  
    1727      // First decide from where we want the data.
    1728  
    1729      $params = array('timestart' => $timestart,
    1730                      'timeend' => $timeend,
    1731                      'participating' => \core\event\base::LEVEL_PARTICIPATING,
    1732                      'teaching' => \core\event\base::LEVEL_TEACHING,
    1733                      'loginevent1' => '\core\event\user_loggedin',
    1734                      'loginevent2' => '\core\event\user_loggedin',
    1735      );
    1736  
    1737      $filled = false;
    1738      $manager = get_log_manager();
    1739      $stores = $manager->get_readers();
    1740      foreach ($stores as $store) {
    1741          if ($store instanceof \core\log\sql_internal_table_reader) {
    1742              $logtable = $store->get_internal_log_table_name();
    1743              if (!$logtable) {
    1744                  continue;
    1745              }
    1746  
    1747              $sql = "SELECT COUNT('x')
    1748                        FROM {{$logtable}}
    1749                       WHERE timecreated >= :timestart AND timecreated < :timeend";
    1750  
    1751              if (!$DB->get_field_sql($sql, $params)) {
    1752                  continue;
    1753              }
    1754  
    1755              // Let's fake the old records using new log data.
    1756              // We want only data relevant to educational process
    1757              // done by real users.
    1758  
    1759              $sql = "INSERT INTO {temp_log1} (userid, course, action)
    1760  
    1761              SELECT userid,
    1762                     CASE
    1763                        WHEN courseid IS NULL THEN ".SITEID."
    1764                        WHEN courseid = 0 THEN ".SITEID."
    1765                        ELSE courseid
    1766                     END,
    1767                     CASE
    1768                         WHEN eventname = :loginevent1 THEN 'login'
    1769                         WHEN crud = 'r' THEN 'view'
    1770                         ELSE 'update'
    1771                     END
    1772                FROM {{$logtable}}
    1773               WHERE timecreated >= :timestart AND timecreated < :timeend
    1774                     AND (origin = 'web' OR origin = 'ws')
    1775                     AND (edulevel = :participating OR edulevel = :teaching OR eventname = :loginevent2)";
    1776  
    1777              $DB->execute($sql, $params);
    1778              $filled = true;
    1779          }
    1780      }
    1781  
    1782      if (!$filled) {
    1783          // Fallback to legacy data.
    1784          $sql = "INSERT INTO {temp_log1} (userid, course, action)
    1785  
    1786              SELECT userid, course, action
    1787                FROM {log}
    1788               WHERE time >= :timestart AND time < :timeend";
    1789  
    1790          $DB->execute($sql, $params);
    1791      }
    1792  
    1793      $sql = 'INSERT INTO {temp_log2} (userid, course, action)
    1794  
    1795              SELECT userid, course, action FROM {temp_log1}';
    1796  
    1797      $DB->execute($sql);
    1798  
    1799      // We have just loaded all the temp tables, collect statistics for that.
    1800      $DB->update_temp_table_stats();
    1801  
    1802      return true;
    1803  }
    1804  
    1805  
    1806  /**
    1807   * Deletes summary logs table for stats calculation
    1808   *
    1809   * @return bool success (true) or failure(false)
    1810   */
    1811  function stats_temp_table_clean() {
    1812      global $DB;
    1813  
    1814      $sql = array();
    1815  
    1816      $sql['up1'] = 'INSERT INTO {stats_daily} (courseid, roleid, stattype, timeend, stat1, stat2)
    1817  
    1818                     SELECT courseid, roleid, stattype, timeend, stat1, stat2 FROM {temp_stats_daily}';
    1819  
    1820      $sql['up2'] = 'INSERT INTO {stats_user_daily}
    1821                                 (courseid, userid, roleid, timeend, statsreads, statswrites, stattype)
    1822  
    1823                     SELECT courseid, userid, roleid, timeend, statsreads, statswrites, stattype
    1824                       FROM {temp_stats_user_daily}';
    1825  
    1826      foreach ($sql as $id => $query) {
    1827          if (! stats_run_query($query)) {
    1828              mtrace("Error during table cleanup!");
    1829              return false;
    1830          }
    1831      }
    1832  
    1833      $tables = array('temp_log1', 'temp_log2', 'temp_stats_daily', 'temp_stats_user_daily');
    1834  
    1835      foreach ($tables as $name) {
    1836          $DB->delete_records($name);
    1837      }
    1838  
    1839      return true;
    1840  }