lib/statslib.php

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