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     // 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             $mod_actions = $function();
1274             if (is_array($mod_actions)) {
1275                 $actions = array_merge($actions, $mod_actions);
1276             }
1277         }
1278     }
1279
1280     // The array_values() forces a stack-like array
1281     // so we can later loop over safely...
1282     $actions =  array_values(array_unique($actions));
1283     $c = count($actions);
1284     for ($n=0;$n<$c;$n++) {
1285         $actions[$n] = "'" . $actions[$n] . "'"; // quote them for SQL
1286     }
1287     return $actions;
1288 }
1289
1290 function stats_get_time_options($now,$lastweekend,$lastmonthend,$earliestday,$earliestweek,$earliestmonth) {
1291
1292     $now = stats_get_base_daily(time());
1293     // it's really important that it's TIMEEND in the table. ie, tuesday 00:00:00 is monday night.
1294     // so we need to take a day off here (essentially add a day to $now
1295     $now += 60*60*24;
1296
1297     $timeoptions = array();
1298
1299     if ($now - (60*60*24*7) >= $earliestday) {
1300         $timeoptions[STATS_TIME_LASTWEEK] = get_string('numweeks','moodle',1);
1301     }
1302     if ($now - (60*60*24*14) >= $earliestday) {
1303         $timeoptions[STATS_TIME_LAST2WEEKS] = get_string('numweeks','moodle',2);
1304     }
1305     if ($now - (60*60*24*21) >= $earliestday) {
1306         $timeoptions[STATS_TIME_LAST3WEEKS] = get_string('numweeks','moodle',3);
1307     }
1308     if ($now - (60*60*24*28) >= $earliestday) {
1309         $timeoptions[STATS_TIME_LAST4WEEKS] = get_string('numweeks','moodle',4);// show dailies up to (including) here.
1310     }
1311     if ($lastweekend - (60*60*24*56) >= $earliestweek) {
1312         $timeoptions[STATS_TIME_LAST2MONTHS] = get_string('nummonths','moodle',2);
1313     }
1314     if ($lastweekend - (60*60*24*84) >= $earliestweek) {
1315         $timeoptions[STATS_TIME_LAST3MONTHS] = get_string('nummonths','moodle',3);
1316     }
1317     if ($lastweekend - (60*60*24*112) >= $earliestweek) {
1318         $timeoptions[STATS_TIME_LAST4MONTHS] = get_string('nummonths','moodle',4);
1319     }
1320     if ($lastweekend - (60*60*24*140) >= $earliestweek) {
1321         $timeoptions[STATS_TIME_LAST5MONTHS] = get_string('nummonths','moodle',5);
1322     }
1323     if ($lastweekend - (60*60*24*168) >= $earliestweek) {
1324         $timeoptions[STATS_TIME_LAST6MONTHS] = get_string('nummonths','moodle',6); // show weeklies up to (including) here
1325     }
1326     if (strtotime('-7 months',$lastmonthend) >= $earliestmonth) {
1327         $timeoptions[STATS_TIME_LAST7MONTHS] = get_string('nummonths','moodle',7);
1328     }
1329     if (strtotime('-8 months',$lastmonthend) >= $earliestmonth) {
1330         $timeoptions[STATS_TIME_LAST8MONTHS] = get_string('nummonths','moodle',8);
1331     }
1332     if (strtotime('-9 months',$lastmonthend) >= $earliestmonth) {
1333         $timeoptions[STATS_TIME_LAST9MONTHS] = get_string('nummonths','moodle',9);
1334     }
1335     if (strtotime('-10 months',$lastmonthend) >= $earliestmonth) {
1336         $timeoptions[STATS_TIME_LAST10MONTHS] = get_string('nummonths','moodle',10);
1337     }
1338     if (strtotime('-11 months',$lastmonthend) >= $earliestmonth) {
1339         $timeoptions[STATS_TIME_LAST11MONTHS] = get_string('nummonths','moodle',11);
1340     }
1341     if (strtotime('-1 year',$lastmonthend) >= $earliestmonth) {
1342         $timeoptions[STATS_TIME_LASTYEAR] = get_string('lastyear');
1343     }
1344
1345     $years = (int)date('y', $now) - (int)date('y', $earliestmonth);
1346     if ($years > 1) {
1347         for($i = 2; $i <= $years; $i++) {
1348             $timeoptions[$i*12+20] = get_string('numyears', 'moodle', $i);
1349         }
1350     }
1351
1352     return $timeoptions;
1353 }
1354
1355 function stats_get_report_options($courseid,$mode) {
1356     global $CFG;
1357
1358     $reportoptions = array();
1359
1360     switch ($mode) {
1361     case STATS_MODE_GENERAL:
1362         $reportoptions[STATS_REPORT_ACTIVITY] = get_string('statsreport'.STATS_REPORT_ACTIVITY);
1363         if ($courseid != SITEID && $context = get_context_instance(CONTEXT_COURSE, $courseid)) {
1364             $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;
1365             if ($roles = get_records_sql($sql)) {
1366                 foreach ($roles as $role) {
1367                     $reportoptions[STATS_REPORT_ACTIVITYBYROLE.$role->id] = get_string('statsreport'.STATS_REPORT_ACTIVITYBYROLE). ' '.$role->name;
1368                 }
1369             }
1370         }
1371         $reportoptions[STATS_REPORT_READS] = get_string('statsreport'.STATS_REPORT_READS);
1372         $reportoptions[STATS_REPORT_WRITES] = get_string('statsreport'.STATS_REPORT_WRITES);
1373         if ($courseid == SITEID) {
1374             $reportoptions[STATS_REPORT_LOGINS] = get_string('statsreport'.STATS_REPORT_LOGINS);
1375         }
1376
1377         break;
1378     case STATS_MODE_DETAILED:
1379         $reportoptions[STATS_REPORT_USER_ACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ACTIVITY);
1380         $reportoptions[STATS_REPORT_USER_ALLACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ALLACTIVITY);
1381         if (has_capability('coursereport/stats:view', get_context_instance(CONTEXT_SYSTEM))) {
1382             $site = get_site();
1383             $reportoptions[STATS_REPORT_USER_LOGINS] = get_string('statsreport'.STATS_REPORT_USER_LOGINS);
1384         }
1385         break;
1386     case STATS_MODE_RANKED:
1387         if (has_capability('coursereport/stats:view', get_context_instance(CONTEXT_SYSTEM))) {
1388             $reportoptions[STATS_REPORT_ACTIVE_COURSES] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES);
1389             $reportoptions[STATS_REPORT_ACTIVE_COURSES_WEIGHTED] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES_WEIGHTED);
1390             $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES);
1391             $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES_RW] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES_RW);
1392         }
1393      break;
1394     }
1395
1396     return $reportoptions;
1397 }
1398
1399 function stats_fix_zeros($stats,$timeafter,$timestr,$line2=true,$line3=false) {
1400
1401     if (empty($stats)) {
1402         return;
1403     }
1404
1405     $timestr = str_replace('user_','',$timestr); // just in case.
1406     $fun = 'stats_get_base_'.$timestr;
1407
1408     $now = $fun();
1409
1410     $times = array();
1411     // add something to timeafter since it is our absolute base
1412     $actualtimes = array();
1413     foreach ($stats as $statid=>$s) {
1414         //normalize the times in stats - those might have been created in different timezone, DST etc.
1415         $s->timeend = $fun($s->timeend + 60*60*5);
1416         $stats[$statid] = $s;
1417
1418         $actualtimes[] = $s->timeend;
1419     }
1420
1421     $timeafter = array_pop(array_values($actualtimes));
1422
1423     while ($timeafter < $now) {
1424         $times[] = $timeafter;
1425         if ($timestr == 'daily') {
1426             $timeafter = stats_get_next_day_start($timeafter);
1427         } else if ($timestr == 'weekly') {
1428             $timeafter = stats_get_next_week_start($timeafter);
1429         } else if ($timestr == 'monthly') {
1430             $timeafter = stats_get_next_month_start($timeafter);
1431         } else {
1432             return $stats; // this will put us in a never ending loop.
1433         }
1434     }
1435
1436     foreach ($times as $count => $time) {
1437         if (!in_array($time,$actualtimes) && $count != count($times) -1) {
1438             $newobj = new StdClass;
1439             $newobj->timeend = $time;
1440             $newobj->id = 0;
1441             $newobj->roleid = 0;
1442             $newobj->line1 = 0;
1443             if (!empty($line2)) {
1444                 $newobj->line2 = 0;
1445             }
1446             if (!empty($line3)) {
1447                 $newobj->line3 = 0;
1448             }
1449             $newobj->zerofixed = true;
1450             $stats[] = $newobj;
1451         }
1452     }
1453
1454     usort($stats,"stats_compare_times");
1455     return $stats;
1456
1457 }
1458
1459 // helper function to sort arrays by $obj->timeend
1460 function stats_compare_times($a,$b) {
1461    if ($a->timeend == $b->timeend) {
1462        return 0;
1463    }
1464    return ($a->timeend > $b->timeend) ? -1 : 1;
1465 }
1466
1467 function stats_check_uptodate($courseid=0) {
1468     global $CFG;
1469
1470     if (empty($courseid)) {
1471         $courseid = SITEID;
1472     }
1473
1474     $latestday = stats_get_start_from('daily');
1475
1476     if ((time() - 60*60*24*2) < $latestday) { // we're ok
1477         return NULL;
1478     }
1479
1480     $a = new object();
1481     $a->daysdone = get_field_sql("SELECT count(distinct(timeend)) from {$CFG->prefix}stats_daily");
1482
1483     // how many days between the last day and now?
1484     $a->dayspending = ceil((stats_get_base_daily() - $latestday)/(60*60*24));
1485
1486     if ($a->dayspending == 0 && $a->daysdone != 0) {
1487         return NULL; // we've only just started...
1488     }
1489
1490     //return error as string
1491     return get_string('statscatchupmode','error',$a);
1492 }
1493
1494 /**
1495  * Calculate missing course totals in stats
1496  */
1497 function stats_upgrade_totals() {
1498     global $CFG;
1499
1500     if (empty($CFG->statsrolesupgraded)) {
1501         // stats not yet upgraded to cope with roles...
1502         return;
1503     }
1504
1505     $types = array('daily', 'weekly', 'monthly');
1506
1507     $now = time();
1508     $y30 = 60*60*24*365*30;              // 30 years ago :-O
1509     $y20 = 60*60*24*365*20;              // 20 years ago :-O
1510     $limit = $now - $y20;
1511
1512     foreach ($types as $i => $type) {
1513         $type2 = $types[($i+1) % count($types)];
1514
1515         // delete previous incomplete data
1516         $sql = "DELETE FROM {$CFG->prefix}stats_$type2
1517                       WHERE timeend < $limit";
1518         execute_sql($sql);
1519
1520         // clear the totals if already exist
1521         $sql = "DELETE FROM {$CFG->prefix}stats_$type
1522                       WHERE (stattype = 'enrolments' OR stattype = 'activity') AND
1523                             roleid = 0";
1524         execute_sql($sql);
1525
1526         $sql = "INSERT INTO {$CFG->prefix}stats_$type2 (stattype, timeend, courseid, roleid, stat1, stat2)
1527
1528                 SELECT stattype, (timeend - $y30), courseid, 0, SUM(stat1), SUM(stat2)
1529                   FROM {$CFG->prefix}stats_$type
1530                  WHERE (stattype = 'enrolments' OR stattype = 'activity') AND
1531                        roleid <> 0
1532               GROUP BY stattype, timeend, courseid";
1533         execute_sql($sql);
1534
1535         $sql = "INSERT INTO {$CFG->prefix}stats_$type (stattype, timeend, courseid, roleid, stat1, stat2)
1536
1537                 SELECT stattype, (timeend + $y30), courseid, roleid, stat1, stat2
1538                   FROM {$CFG->prefix}stats_$type2
1539                  WHERE (stattype = 'enrolments' OR stattype = 'activity') AND
1540                        roleid = 0 AND timeend < $y20";
1541         execute_sql($sql);
1542
1543         $sql = "DELETE FROM {$CFG->prefix}stats_$type2
1544                       WHERE timeend < $limit";
1545         execute_sql($sql);
1546     }
1547 }
1548
1549
1550 function stats_upgrade_for_roles_wrapper() {
1551     global $CFG;
1552     if (!empty($CFG->statsrolesupgraded)) {
1553         return true;
1554     }
1555
1556     $result = begin_sql();
1557
1558     $result = $result && stats_upgrade_user_table_for_roles('daily');
1559     $result = $result && stats_upgrade_user_table_for_roles('weekly');
1560     $result = $result && stats_upgrade_user_table_for_roles('monthly');
1561
1562     $result = $result && stats_upgrade_table_for_roles('daily');
1563     $result = $result && stats_upgrade_table_for_roles('weekly');
1564     $result = $result && stats_upgrade_table_for_roles('monthly');
1565
1566
1567     $result = $result && commit_sql();
1568
1569     if (!empty($result)) {
1570         set_config('statsrolesupgraded',time());
1571     }
1572
1573     // finally upgade totals, no big deal if it fails
1574     stats_upgrade_totals();
1575
1576     return $result;
1577 }
1578
1579 /**
1580  * Upgrades a prefix_stats_user_* table for the new role based permission
1581  * system.
1582  *
1583  * @param string $period  daily, weekly or monthly: the stat period to upgrade
1584  * @return boolean @todo maybe something else (error message) depending on
1585  * how this will be called.
1586  */
1587 function stats_upgrade_user_table_for_roles($period) {
1588     global $CFG;
1589     static $teacher_role_id, $student_role_id;
1590
1591     if (!in_array($period, array('daily', 'weekly', 'monthly'))) {
1592         error_log('stats upgrade:  invalid period: ' . $period);
1593         return false;
1594     }
1595
1596     if (!$teacher_role_id) {
1597         $role            = get_roles_with_capability('moodle/legacy:editingteacher', CAP_ALLOW);
1598         $role            = array_keys($role);
1599         $teacher_role_id = $role[0];
1600         $role            = get_roles_with_capability('moodle/legacy:student', CAP_ALLOW);
1601         $role            = array_keys($role);
1602         $student_role_id = $role[0];
1603     }
1604
1605     if (empty($teacher_role_id) || empty($student_role_id)) {
1606         error_log("Couldn't find legacy roles for teacher or student");
1607         return false;
1608     }
1609
1610     $status = true;
1611
1612     $status = $status && execute_sql("UPDATE {$CFG->prefix}stats_user_{$period}
1613         SET roleid = $teacher_role_id
1614         WHERE roleid = 1");
1615     $status = $status && execute_sql("UPDATE {$CFG->prefix}stats_user_{$period}
1616         SET roleid = $student_role_id
1617         WHERE roleid = 2");
1618
1619     return $status;
1620 }
1621
1622 /**
1623  * Upgrades a prefix_stats_* table for the new role based permission system.
1624  *
1625  * @param string $period  daily, weekly or monthly: the stat period to upgrade
1626  * @return boolean        @todo depends on how this will be called
1627  */
1628 function stats_upgrade_table_for_roles ($period) {
1629     global $CFG;
1630     static $teacher_role_id, $student_role_id;
1631
1632     if (!in_array($period, array('daily', 'weekly', 'monthly'))) {
1633         return false;
1634     }
1635
1636     if (!$teacher_role_id) {
1637         $role            = get_roles_with_capability('moodle/legacy:editingteacher', CAP_ALLOW);
1638         $role            = array_keys($role);
1639         $teacher_role_id = $role[0];
1640         $role            = get_roles_with_capability('moodle/legacy:student', CAP_ALLOW);
1641         $role            = array_keys($role);
1642         $student_role_id = $role[0];
1643     }
1644
1645     if (empty($teacher_role_id) || empty($student_role_id)) {
1646         error_log("Couldn't find legacy roles for teacher or student");
1647         return false;
1648     }
1649
1650     execute_sql("CREATE TABLE {$CFG->prefix}stats_{$period}_tmp AS
1651         SELECT * FROM {$CFG->prefix}stats_{$period}");
1652
1653     $table = new XMLDBTable('stats_' . $period);
1654     if (!drop_table($table)) {
1655         return false;
1656     }
1657
1658     // Create a new stats table
1659     // @todo this definition I have made blindly by looking at how definitions are
1660     // made, it needs work to make sure it works properly
1661     require_once("$CFG->libdir/xmldb/classes/XMLDBTable.class.php");
1662
1663     $table = new XMLDBTable('stats_' . $period);
1664     $table->addFieldInfo('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1665         XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1666
1667     $table->addFieldInfo('courseid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1668         XMLDB_NOTNULL, null, null, null, null);
1669
1670     $table->addFieldInfo('roleid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1671         XMLDB_NOTNULL, null, null, null, null);
1672     $table->addFieldInfo('timeend', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1673         XMLDB_NOTNULL, null, null, null, null);
1674     $table->addFieldInfo('stattype', XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL,
1675         null, XMLDB_ENUM, array('enrolments', 'activity', 'logins'), 'activity');
1676     $table->addFieldInfo('stat1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1677         XMLDB_NOTNULL, null, null, null, null);
1678     $table->addFieldInfo('stat2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1679         XMLDB_NOTNULL, null, null, null, null);
1680
1681     /// Adding keys to table stats_daily
1682     $table->addKeyInfo('primary', XMLDB_KEY_PRIMARY, array('id'));
1683
1684     /// Adding indexes to table stats_daily
1685     $table->addIndexInfo('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid'));
1686     $table->addIndexInfo('timeend', XMLDB_INDEX_NOTUNIQUE, array('timeend'));
1687     $table->addIndexInfo('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid'));
1688
1689     if (!create_table($table)) {
1690         return false;
1691     }
1692
1693     //
1694     // Now insert the data from the temporary table into the new one
1695     //
1696
1697     // Student enrolments
1698     execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
1699        (courseid, roleid, timeend, stattype, stat1, stat2)
1700        SELECT courseid, $student_role_id, timeend, 'enrolments', students, activestudents
1701        FROM {$CFG->prefix}stats_{$period}_tmp");
1702
1703     // Teacher enrolments
1704     execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
1705        (courseid, roleid, timeend, stattype, stat1, stat2)
1706        SELECT courseid, $teacher_role_id, timeend, 'enrolments', teachers, activeteachers
1707        FROM {$CFG->prefix}stats_{$period}_tmp");
1708
1709     // Student activity
1710     execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
1711        (courseid, roleid, timeend, stattype, stat1, stat2)
1712        SELECT courseid, $student_role_id, timeend, 'activity', studentreads, studentwrites
1713        FROM {$CFG->prefix}stats_{$period}_tmp");
1714
1715     // Teacher activity
1716     execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
1717        (courseid, roleid, timeend, stattype, stat1, stat2)
1718        SELECT courseid, $teacher_role_id, timeend, 'activity', teacherreads, teacherwrites
1719        FROM {$CFG->prefix}stats_{$period}_tmp");
1720
1721     // Logins
1722     execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
1723        (courseid, roleid, timeend, stattype, stat1, stat2)
1724        SELECT courseid, 0, timeend, 'logins', logins, uniquelogins
1725        FROM {$CFG->prefix}stats_{$period}_tmp WHERE courseid = ".SITEID);
1726
1727     // Drop the temporary table
1728     $table = new XMLDBTable('stats_' . $period . '_tmp');
1729     if (!drop_table($table)) {
1730         return false;
1731     }
1732
1733     return true;
1734 }
1735
1736 ?>