[Summary view] [Print] [Text view]

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