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     // return codes - whether to rerun
  51     define('STATS_RUN_COMPLETE',1);
  52     define('STATS_RUN_ABORTED',0);
  53
  54 function stats_cron_daily () {
  55     global $CFG;
  56
  57     if (empty($CFG->enablestats)) {
  58         return STATS_RUN_ABORTED;
  59     }
  60
  61     if (!$timestart = stats_get_start_from('daily')) {
  62         return STATS_RUN_ABORTED;
  63     }
  64
  65
  66     $midnight = stats_getmidnight(time());
  67
  68     // check to make sure we're due to run, at least one day after last run
  69     if (isset($CFG->statslastdaily) and ((time() - 24*60*60) < $CFG->statslastdaily)) {
  70         return STATS_RUN_ABORTED;
  71     }
  72
  73     mtrace("Running daily statistics gathering...");
  74     set_config('statslastdaily',time());
  75
  76     $return = STATS_RUN_COMPLETE; // optimistic
  77
  78     static $daily_modules;
  79
  80     if (empty($daily_modules)) {
  81         $daily_modules = array();
  82         $mods = get_records("modules");
  83         foreach ($mods as $mod) {
  84             require_once($CFG->dirroot.'/mod/'.$mod->name.'/lib.php');
  85             $fname = $mod->name.'_get_daily_stats';
  86             if (function_exists($fname)) {
  87                 $daily_modules[$mod] = $fname;
  88             }
  89         }
  90     }
  91
  92     $nextmidnight = stats_get_next_dayend($timestart);
  93
  94     if (!$courses = get_records('course','','','','id,1')) {
  95         return STATS_RUN_ABORTED;
  96     }
  97
  98     $days = 0;
  99     mtrace("starting at $timestart");
 100     while ($midnight > $nextmidnight && $timestart < $nextmidnight) {
 101
 102         $timesql = " (l.time > $timestart AND l.time < $nextmidnight) ";
 103         begin_sql();
 104         foreach ($courses as $course) {
 105             //do this first.
 106             if ($course->id == SITEID) {
 107                 $stat = new StdClass;
 108                 $stat->courseid = $course->id;
 109                 $stat->timeend = $nextmidnight;
 110                 $stat->roleid = 0; // all users
 111                 $stat->stattype = 'logins';
 112                 $sql = 'SELECT count(l.id) FROM '.$CFG->prefix.'log l WHERE l.action = \'login\' AND '.$timesql;
 113                 $stat->stat1 = count_records_sql($sql);
 114                 $sql = 'SELECT COUNT(DISTINCT(l.userid)) FROM '.$CFG->prefix.'log l WHERE l.action = \'login\' AND '.$timesql;
 115                 $stat->stat2 = count_records_sql($sql);
 116                 insert_record('stats_daily',$stat,false); // don't worry about the return id, we don't need it.
 117
 118                 // and now user logins...
 119                 $sql = 'SELECT l.userid,count(l.id) as count FROM '.$CFG->prefix.'log l WHERE action = \'login\' AND '.$timesql.' GROUP BY userid';
 120
 121                 if ($logins = get_records_sql($sql)) {
 122                     foreach ($logins as $l) {
 123                         $stat->statsreads = $l->count;
 124                         $stat->userid = $l->userid;
 125                         $stat->timeend = $nextmidnight;
 126                         $stat->courseid = SITEID;
 127                         $stat->statswrites = 0;
 128                         $stat->stattype = 'logins';
 129                         $stat->roleid = 0;
 130                         insert_record('stats_user_daily',$stat,false);
 131                     }
 132                 }
 133             }
 134
 135             $context = get_record('context','instanceid',$course->id,'contextlevel',CONTEXT_COURSE);
 136             if (!$roles = get_roles_on_exact_context($context)) {
 137                 // no roles.. nothing to log.
 138                 continue;
 139             }
 140
 141             $primary_roles = sql_primary_role_subselect();  // In dmllib.php
 142             foreach ($roles as $role) {
 143                 // ENROLMENT FIRST....
 144                 // ALL users with this role...
 145                 $stat = new StdClass;
 146                 $stat->courseid = $course->id;
 147                 $stat->roleid = $role->id;
 148                 $stat->timeend = $nextmidnight;
 149                 $stat->stattype = 'enrolments';
 150                 $sql = 'SELECT COUNT(DISTINCT prs.userid) FROM ('.$primary_roles.') prs WHERE prs.primary_roleid='.$role->id.
 151                     ' AND prs.courseid='.$course->id.' AND prs.contextlevel = '.CONTEXT_COURSE;
 152                 $stat->stat1 = count_records_sql($sql);
 153
 154                 $sql = 'SELECT COUNT(DISTINCT prs.userid) FROM ('.$primary_roles.') prs
 155                         INNER JOIN '.$CFG->prefix.'log l ON (prs.userid=l.userid AND l.course=prs.courseid)
 156                         WHERE prs.primary_roleid='.$role->id.' AND prs.courseid='.$course->id.'
 157                         AND prs.contextlevel = '.CONTEXT_COURSE.' AND '.$timesql;
 158
 159                 $stat->stat2 = count_records_sql($sql);
 160                 insert_record('stats_daily',$stat,false); // don't worry about the return id, we don't need it.
 161
 162                 // ACTIVITY
 163
 164                 $stat = new StdClass;
 165                 $stat->courseid = $course->id;
 166                 $stat->roleid = $role->id;
 167                 $stat->timeend = $nextmidnight;
 168                 $stat->stattype = 'activity';
 169
 170                 $sql = 'SELECT COUNT(DISTINCT l.id) FROM ('.$primary_roles.') prs
 171                         INNER JOIN '.$CFG->prefix.'log l ON (prs.userid=l.userid
 172                         AND l.course=prs.courseid) WHERE prs.primary_roleid='.$role->id.'
 173                         AND prs.courseid='.$course->id.' AND prs.contextlevel = '.CONTEXT_COURSE.'
 174                          AND '.$timesql.' '.stats_get_action_sql_in('view');
 175                 $stat->stat1 = count_records_sql($sql);
 176
 177                 $sql = 'SELECT COUNT(DISTINCT l.id) FROM ('.$primary_roles.') prs
 178                         INNER JOIN '.$CFG->prefix.'log l ON (prs.userid=l.userid  AND l.course=prs.courseid)
 179                         WHERE prs.primary_roleid='.$role->id.' AND prs.courseid='.$course->id.'
 180                         AND prs.contextlevel = '.CONTEXT_COURSE.' AND '.$timesql.' '.stats_get_action_sql_in('post');
 181                 $stat->stat2 = count_records_sql($sql);
 182
 183                 insert_record('stats_daily',$stat,false); // don't worry about the return id, we don't need it.
 184             }
 185
 186             $users = stats_get_course_users($course,$timesql);
 187             foreach ($users as $user) {
 188                 stats_do_daily_user_cron($course,$user,$user->primaryrole,$timesql,$nextmidnight,$daily_modules);
 189             }
 190         }
 191         commit_sql();
 192         $timestart = $nextmidnight;
 193         $nextmidnight = stats_get_next_dayend($nextmidnight);
 194         $days++;
 195
 196         if (!stats_check_runtime()) {
 197             mtrace("Stopping early! reached maxruntime");
 198             $return = STATS_RUN_ABORTED;
 199             break;
 200         }
 201     }
 202     mtrace("got up to ".$timestart);
 203     mtrace("Completed $days days");
 204     return $return;
 205
 206 }
 207
 208
 209 function stats_cron_weekly () {
 210
 211     global $CFG;
 212
 213     if (empty($CFG->enablestats)) {
 214         STATS_RUN_ABORTED;
 215     }
 216
 217     if (!$timestart = stats_get_start_from('weekly')) {
 218         return STATS_RUN_ABORTED;
 219     }
 220
 221     // check to make sure we're due to run, at least one week after last run
 222     $sunday = stats_get_base_weekly();
 223
 224     if (isset($CFG->statslastweekly) and ((time() - (7*24*60*60)) <= $CFG->statslastweekly)) {
 225         return STATS_RUN_ABORTED;
 226     }
 227
 228     mtrace("Running weekly statistics gathering...");
 229     set_config('statslastweekly',time());
 230
 231     $return = STATS_RUN_COMPLETE; // optimistic
 232
 233     static $weekly_modules;
 234
 235     if (empty($weekly_modules)) {
 236         $weekly_modules = array();
 237         $mods = get_records("modules");
 238         foreach ($mods as $mod) {
 239             require_once($CFG->dirroot.'/mod/'.$mod->name.'/lib.php');
 240             $fname = $mod->name.'_get_weekly_stats';
 241             if (function_exists($fname)) {
 242                 $weekly_modules[$mod] = $fname;
 243             }
 244         }
 245     }
 246
 247     $nextsunday = stats_get_next_weekend($timestart);
 248
 249     if (!$courses = get_records('course','','','','id,1')) {
 250         return STATS_RUN_ABORTED;
 251     }
 252
 253     $weeks = 0;
 254     mtrace("starting at $timestart");
 255     while ($sunday > $nextsunday && $timestart < $nextsunday) {
 256
 257         $timesql = " (timeend > $timestart AND timeend < $nextsunday) ";
 258         begin_sql();
 259         foreach ($courses as $course) {
 260
 261             // enrolment first
 262             $sql = 'SELECT roleid, ceil(avg(stat1)) AS stat1, ceil(avg(stat2)) AS stat2
 263                     FROM '.$CFG->prefix.'stats_daily
 264                     WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'enrolments\'
 265                     GROUP BY roleid';
 266
 267             if ($rolestats = get_records_sql($sql)) {
 268                 foreach ($rolestats as $stat) {
 269                     $stat->courseid = $course->id;
 270                     $stat->timeend = $nextsunday;
 271                     $stat->stattype = 'enrolments';
 272
 273                     insert_record('stats_weekly',$stat,false); // don't worry about the return id, we don't need it.
 274                 }
 275             }
 276
 277             // activity
 278             $sql = 'SELECT roleid, sum(stat1) AS stat1, sum(stat2) as stat2
 279                     FROM '.$CFG->prefix.'stats_daily
 280                     WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'activity\'
 281                     GROUP BY roleid';
 282
 283             if ($rolestats = get_records_sql($sql)) {
 284                 foreach ($rolestats as $stat) {
 285                     $stat->courseid = $course->id;
 286                     $stat->timeend = $nextsunday;
 287                     $stat->stattype = 'activity';
 288                     unset($stat->id);
 289
 290                     insert_record('stats_weekly',$stat,false); // don't worry about the return id, we don't need it.
 291                 }
 292             }
 293
 294             // logins
 295             if ($course->id == SITEID) {
 296                 $sql = 'SELECT sum(stat1) AS stat1
 297                     FROM '.$CFG->prefix.'stats_daily
 298                     WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'logins\'';
 299
 300                 if ($stat = get_record_sql($sql)) {
 301                     if (empty($stat->stat1)) {
 302                         $stat->stat1 = 0;
 303                     }
 304                     $stat->courseid = $course->id;
 305                     $stat->roleid = 0;
 306                     $stat->timeend = $nextsunday;
 307                     $stat->stattype = 'logins';
 308                     $sql = 'SELECT COUNT(DISTINCT(l.userid)) FROM '.$CFG->prefix.'log l WHERE l.action = \'login\' AND '
 309                         .str_replace('timeend','time',$timesql);
 310                     $stat->stat2 = count_records_sql($sql);
 311
 312                     insert_record('stats_weekly',$stat,false); // don't worry about the return id, we don't need it.
 313                 }
 314             }
 315
 316             $users = stats_get_course_users($course,$timesql);
 317             foreach ($users as $user) {
 318                 stats_do_aggregate_user_cron($course,$user,$user->primaryrole,$timesql,$nextsunday,'weekly',$weekly_modules);
 319             }
 320         }
 321         stats_do_aggregate_user_login_cron($timesql,$nextsunday,'weekly');
 322         commit_sql();
 323         $timestart = $nextsunday;
 324         $nextsunday = stats_get_next_weekend($nextsunday);
 325         $weeks++;
 326
 327         if (!stats_check_runtime()) {
 328             mtrace("Stopping early! reached maxruntime");
 329             $return = STATS_RUN_ABORTED;
 330             break;
 331         }
 332     }
 333     mtrace("got up to ".$timestart);
 334     mtrace("Completed $weeks weeks");
 335     return $return;
 336 }
 337
 338
 339 function stats_cron_monthly () {
 340     global $CFG;
 341
 342     if (empty($CFG->enablestats)) {
 343         return STATS_RUN_ABORTED;
 344     }
 345
 346     if (!$timestart = stats_get_start_from('monthly')) {
 347         return STATS_RUN_ABORTED;
 348     }
 349
 350     // check to make sure we're due to run, at least one month after last run
 351     $monthend = stats_get_base_monthly();
 352
 353     if (isset($CFG->statslastmonthly) and ((time() - (31*24*60*60)) <= $CFG->statslastmonthly)) {
 354         return STATS_RUN_ABORTED;
 355     }
 356
 357     mtrace("Running monthly statistics gathering...");
 358     set_config('statslastmonthly',time());
 359
 360     $return = STATS_RUN_COMPLETE; // optimistic
 361
 362     static $monthly_modules;
 363
 364     if (empty($monthly_modules)) {
 365         $monthly_modules = array();
 366         $mods = get_records("modules");
 367         foreach ($mods as $mod) {
 368             require_once($CFG->dirroot.'/mod/'.$mod->name.'/lib.php');
 369             $fname = $mod->name.'_get_monthly_stats';
 370             if (function_exists($fname)) {
 371                 $monthly_modules[$mod] = $fname;
 372             }
 373         }
 374     }
 375
 376     $nextmonthend = stats_get_next_monthend($timestart);
 377
 378     if (!$courses = get_records('course','','','','id,1')) {
 379         return STATS_RUN_ABORTED;
 380     }
 381
 382     $months = 0;
 383     mtrace("starting from $timestart");
 384     while ($monthend > $nextmonthend && $timestart < $nextmonthend) {
 385
 386         $timesql = " (timeend > $timestart AND timeend < $nextmonthend) ";
 387         begin_sql();
 388         foreach ($courses as $course) {
 389
 390             // enrolment first
 391             $sql = 'SELECT roleid, ceil(avg(stat1)) AS stat1, ceil(avg(stat2)) AS stat2
 392                     FROM '.$CFG->prefix.'stats_daily
 393                     WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'enrolments\'
 394                     GROUP BY roleid';
 395
 396             if ($rolestats = get_records_sql($sql)) {
 397                 foreach ($rolestats as $stat) {
 398                     $stat->courseid = $course->id;
 399                     $stat->timeend = $nextmonthend;
 400                     $stat->stattype = 'enrolments';
 401
 402                     insert_record('stats_monthly',$stat,false); // don't worry about the return id, we don't need it.
 403                 }
 404             }
 405
 406             // activity
 407             $sql = 'SELECT roleid, sum(stat1) AS stat1, sum(stat2) as stat2
 408                     FROM '.$CFG->prefix.'stats_daily
 409                     WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'activity\'
 410                     GROUP BY roleid';
 411
 412             if ($rolestats = get_records_sql($sql)) {
 413                 foreach ($rolestats as $stat) {
 414                     $stat->courseid = $course->id;
 415                     $stat->timeend = $nextmonthend;
 416                     $stat->stattype = 'activity';
 417                     unset($stat->id);
 418
 419                     insert_record('stats_monthly',$stat,false); // don't worry about the return id, we don't need it.
 420                 }
 421             }
 422
 423             // logins
 424             if ($course->id == SITEID) {
 425                 $sql = 'SELECT sum(stat1) AS stat1
 426                     FROM '.$CFG->prefix.'stats_daily
 427                     WHERE courseid = '.$course->id.' AND '.$timesql.' AND stattype = \'logins\'';
 428
 429                 if ($stat = get_record_sql($sql)) {
 430                     if (empty($stat->stat1)) {
 431                         $stat->stat1 = 0;
 432                     }
 433                     $stat->courseid = $course->id;
 434                     $stat->roleid = 0;
 435                     $stat->timeend = $nextmonthend;
 436                     $stat->stattype = 'logins';
 437                     $sql = 'SELECT COUNT(DISTINCT(l.userid)) FROM '.$CFG->prefix.'log l WHERE l.action = \'login\' AND '
 438                         .str_replace('timeend','time',$timesql);
 439                     $stat->stat2 = count_records_sql($sql);
 440
 441                     insert_record('stats_monthly',$stat,false); // don't worry about the return id, we don't need it.
 442                 }
 443             }
 444
 445             $users = stats_get_course_users($course,$timesql);
 446             foreach ($users as $user) {
 447                 stats_do_aggregate_user_cron($course,$user,$user->primaryrole,$timesql,$nextmonthend,'monthly',$monthly_modules);
 448             }
 449
 450         }
 451         stats_do_aggregate_user_login_cron($timesql,$nextmonthend,'monthly');
 452         commit_sql();
 453         $timestart = $nextmonthend;
 454         $nextmonthend = stats_get_next_monthend($timestart);
 455         $months++;
 456         if (!stats_check_runtime()) {
 457             mtrace("Stopping early! reached maxruntime");
 458             break;
 459             $return = STATS_RUN_ABORTED;
 460         }
 461     }
 462     mtrace("got up to $timestart");
 463     mtrace("Completed $months months");
 464     return $return;
 465 }
 466
 467 function stats_get_start_from($str) {
 468     global $CFG;
 469
 470     // if it's not our first run, just return the most recent.
 471     if ($timeend = get_field_sql('SELECT timeend FROM '.$CFG->prefix.'stats_'.$str.' ORDER BY timeend DESC')) {
 472         return $timeend;
 473     }
 474
 475     // decide what to do based on our config setting (either all or none or a timestamp)
 476     $function = 'stats_get_base_'.$str;
 477     switch ($CFG->statsfirstrun) {
 478         case 'all':
 479             return $function(get_field_sql('SELECT time FROM '.$CFG->prefix.'log ORDER BY time'));
 480             break;
 481         case 'none':
 482             return $function(strtotime('-1 day',time()));
 483             break;
 484         default:
 485             if (is_numeric($CFG->statsfirstrun)) {
 486                 return $function(time() - $CFG->statsfirstrun);
 487             }
 488             return false;
 489             break;
 490     }
 491 }
 492
 493 function stats_get_base_daily($time=0) {
 494     if (empty($time)) {
 495         $time = time();
 496     }
 497     return stats_getmidnight($time);
 498 }
 499
 500 function stats_get_base_weekly($time=0) {
 501     if (empty($time)) {
 502         $time = time();
 503     }
 504     // if we're currently a monday, last monday will take us back a week
 505     $str = 'last monday';
 506     if (date('D',$time) == 'Mon')
 507         $str = 'now';
 508
 509     return stats_getmidnight(strtotime($str,$time));
 510 }
 511
 512 function stats_get_base_monthly($time=0) {
 513     if (empty($time)) {
 514         $time = time();
 515     }
 516     return stats_getmidnight(strtotime(date('1-M-Y',$time)));
 517 }
 518
 519 function stats_get_next_monthend($lastmonth) {
 520     return stats_getmidnight(strtotime(date('1-M-Y',$lastmonth).' +1 month'));
 521 }
 522
 523 function stats_get_next_weekend($lastweek) {
 524     return stats_getmidnight(strtotime('+1 week',$lastweek));
 525 }
 526
 527 function stats_get_next_dayend($lastday) {
 528     return stats_getmidnight(strtotime('+1 day',$lastday));
 529 }
 530
 531 function stats_clean_old() {
 532     mtrace("Running stats cleanup tasks... ");
 533     // delete dailies older than 2 months (to be safe)
 534     $deletebefore = stats_get_next_monthend(strtotime('-2 months',time()));
 535     delete_records_select('stats_daily',"timeend < $deletebefore");
 536     delete_records_select('stats_user_daily',"timeend < $deletebefore");
 537
 538     // delete weeklies older than 8 months (to be safe)
 539     $deletebefore = stats_get_next_monthend(strtotime('-8 months',time()));
 540     delete_records_select('stats_weekly',"timeend < $deletebefore");
 541     delete_records_select('stats_user_weekly',"timeend < $deletebefore");
 542
 543     // don't delete monthlies
 544 }
 545
 546 function stats_get_parameters($time,$report,$courseid,$mode,$roleid=0) {
 547     global $CFG,$db;
 548     if ($time < 10) { // dailies
 549         // number of days to go back = 7* time
 550         $param->table = 'daily';
 551         $param->timeafter = strtotime("-".($time*7)." days",stats_get_base_daily());
 552     } elseif ($time < 20) { // weeklies
 553         // number of weeks to go back = time - 10 * 4 (weeks) + base week
 554         $param->table = 'weekly';
 555         $param->timeafter = strtotime("-".(($time - 10)*4)." weeks",stats_get_base_weekly());
 556     } else { // monthlies.
 557         // number of months to go back = time - 20 * months + base month
 558         $param->table = 'monthly';
 559         $param->timeafter = strtotime("-".($time - 20)." months",stats_get_base_monthly());
 560     }
 561
 562     $param->extras = '';
 563
 564     // compatibility - if we're in postgres, cast to real for some reports.
 565     $real = '';
 566     if ($CFG->dbfamily == 'postgres') {
 567         $real = '::real';
 568     }
 569
 570     switch ($report) {
 571     // ******************** STATS_MODE_GENERAL ******************** //
 572     case STATS_REPORT_LOGINS:
 573         $param->fields = 'timeend,sum(stat1) as line1,sum(stat2) as line2';
 574         $param->fieldscomplete = true;
 575         $param->stattype = 'logins';
 576         $param->line1 = get_string('statslogins');
 577         $param->line2 = get_string('statsuniquelogins');
 578         if ($courseid == SITEID) {
 579             $param->extras = 'GROUP BY timeend';
 580         }
 581         break;
 582
 583     case STATS_REPORT_READS:
 584         $param->fields = $db->Concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat1 as line1';
 585         $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
 586         $param->aggregategroupby = 'roleid';
 587         $param->stattype = 'activity';
 588         $param->crosstab = true;
 589         $param->extras = 'GROUP BY timeend,roleid,stat1';
 590         if ($courseid == SITEID) {
 591             $param->fields = $db->Concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1) as line1';
 592             $param->extras = 'GROUP BY timeend,roleid';
 593         }
 594         break;
 595
 596     case STATS_REPORT_WRITES:
 597         $param->fields = $db->Concat('timeend','roleid').' AS uniqueid, timeend, roleid, stat2 as line1';
 598         $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
 599         $param->aggregategroupby = 'roleid';
 600         $param->stattype = 'activity';
 601         $param->crosstab = true;
 602         $param->extras = 'GROUP BY timeend,roleid,stat2';
 603         if ($courseid == SITEID) {
 604             $param->fields = $db->Concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat2) as line1';
 605             $param->extras = 'GROUP BY timeend,roleid';
 606         }
 607         break;
 608
 609     case STATS_REPORT_ACTIVITY:
 610         $param->fields = $db->Concat('timeend','roleid').' AS uniqueid, timeend, roleid, sum(stat1+stat2) as line1';
 611         $param->fieldscomplete = true; // set this to true to avoid anything adding stuff to the list and breaking complex queries.
 612         $param->aggregategroupby = 'roleid';
 613         $param->stattype = 'activity';
 614         $param->crosstab = true;
 615         $param->extras = 'GROUP BY timeend,roleid';
 616         if ($courseid == SITEID) {
 617             $param->extras = 'GROUP BY timeend,roleid';
 618         }
 619         break;
 620
 621     case STATS_REPORT_ACTIVITYBYROLE;
 622         $param->fields = 'stat1 AS line1, stat2 AS line2';
 623         $param->stattype = 'activity';
 624         $rolename = get_field('role','name','id',$roleid);
 625         $param->line1 = $rolename . get_string('statsreads');
 626         $param->line2 = $rolename . get_string('statswrites');
 627         if ($courseid == SITEID) {
 628             $param->extras = 'GROUP BY timeend';
 629         }
 630         break;
 631
 632     // ******************** STATS_MODE_DETAILED ******************** //
 633     case STATS_REPORT_USER_ACTIVITY:
 634         $param->fields = 'statsreads as line1, statswrites as line2';
 635         $param->line1 = get_string('statsuserreads');
 636         $param->line2 = get_string('statsuserwrites');
 637         $param->stattype = 'activity';
 638         break;
 639
 640     case STATS_REPORT_USER_ALLACTIVITY:
 641         $param->fields = 'statsreads+statswrites as line1';
 642         $param->line1 = get_string('statsuseractivity');
 643         $param->stattype = 'activity';
 644         break;
 645
 646     case STATS_REPORT_USER_LOGINS:
 647         $param->fields = 'statsreads as line1';
 648         $param->line1 = get_string('statsuserlogins');
 649         $param->stattype = 'logins';
 650         break;
 651
 652     case STATS_REPORT_USER_VIEW:
 653         $param->fields = 'statsreads as line1, statswrites as line2, statsreads+statswrites as line3';
 654         $param->line1 = get_string('statsuserreads');
 655         $param->line2 = get_string('statsuserwrites');
 656         $param->line3 = get_string('statsuseractivity');
 657         $param->stattype = 'activity';
 658         break;
 659
 660     // ******************** STATS_MODE_RANKED ******************** //
 661     case STATS_REPORT_ACTIVE_COURSES:
 662         $param->fields = 'sum(stat1+stat2) AS line1';
 663         $param->stattype = 'activity';
 664         $param->orderby = 'line1 DESC';
 665         $param->line1 = get_string('activity');
 666         $param->graphline = 'line1';
 667         break;
 668
 669     case STATS_REPORT_ACTIVE_COURSES_WEIGHTED:
 670         $threshold = 0;
 671         if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
 672             $threshold = $CFG->statsuserthreshold;
 673         }
 674         $param->fields = '';
 675         $param->sql = 'SELECT activity.courseid, activity.all_activity AS line1, enrolments.highest_enrolments AS line2,
 676                         activity.all_activity / enrolments.highest_enrolments as line3
 677                        FROM (
 678                             SELECT courseid, sum(stat1+stat2) AS all_activity
 679                             FROM '.$CFG->prefix.'stats_'.$param->table.'
 680                             WHERE stattype=\'activity\' AND timeend >= '.$param->timeafter.'
 681                             GROUP BY courseid
 682                        ) activity
 683                        INNER JOIN
 684                             (
 685                             SELECT courseid, max(stat1) AS highest_enrolments
 686                             FROM '.$CFG->prefix.'stats_'.$param->table.'
 687                             WHERE stattype=\'enrolments\' AND timeend >= '.$param->timeafter.'
 688                             GROUP BY courseid
 689                       ) enrolments
 690                       ON (activity.courseid = enrolments.courseid)
 691                       WhERE enrolments.highest_enrolments > '.$threshold.'
 692                       ORDER BY line3 DESC';
 693         $param->line1 = get_string('activity');
 694         $param->line2 = get_string('users');
 695         $param->line3 = get_string('activityweighted');
 696         $param->graphline = 'line3';
 697         break;
 698
 699     case STATS_REPORT_PARTICIPATORY_COURSES:
 700         $threshold = 0;
 701         if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
 702             $threshold = $CFG->statsuserthreshold;
 703         }
 704         $param->fields = '';
 705         $param->sql = 'SELECT courseid, ceil(avg(all_enrolments)) as line1,
 706                          ceil(avg(active_enrolments)) as line2, avg(proportion_active) AS line3
 707                        FROM (
 708                            SELECT courseid, timeend, sum(stat2) as active_enrolments,
 709                               sum(stat1) as all_enrolments, sum(stat2)'.$real.'/sum(stat1)'.$real.' as proportion_active
 710                            FROM '.$CFG->prefix.'stats_'.$param->table.' WHERE stattype=\'enrolments\'
 711                            GROUP BY courseid, timeend
 712                            HAVING sum(stat1) > '.$threshold.'
 713                        ) aq
 714                        WHERE timeend >= '.$param->timeafter.'
 715                        GROUP BY courseid
 716                        ORDER BY line3 DESC';
 717
 718         $param->line1 = get_string('users');
 719         $param->line2 = get_string('activeusers');
 720         $param->line3 = get_string('participationratio');
 721         $param->graphline = 'line3';
 722         break;
 723
 724     case STATS_REPORT_PARTICIPATORY_COURSES_RW:
 725         $param->fields = '';
 726         $param->sql =  'SELECT courseid, sum(views) AS line1, sum(posts) AS line2,
 727                            avg(proportion_active) AS line3
 728                          FROM (
 729                            SELECT courseid, timeend,sum(stat1) as views, sum(stat2) AS posts,
 730                             sum(stat2)'.$real.'/sum(stat1)'.$real.' as proportion_active
 731                            FROM '.$CFG->prefix.'stats_'.$param->table.' WHERE stattype=\'activity\'
 732                            GROUP BY courseid, timeend
 733                            HAVING sum(stat1) > 0
 734                        ) aq
 735                        WHERE timeend >= '.$param->timeafter.'
 736                        GROUP BY courseid
 737                        ORDER BY line3 DESC';
 738         $param->line1 = get_string('views');
 739         $param->line2 = get_string('posts');
 740         $param->line3 = get_string('participationratio');
 741         $param->graphline = 'line3';
 742         break;
 743     }
 744
 745     /*
 746     if ($courseid == SITEID && $mode != STATS_MODE_RANKED) { // just aggregate all courses.
 747         $param->fields = preg_replace('/(?:sum)([a-zA-Z0-9+_]*)\W+as\W+([a-zA-Z0-9_]*)/i','sum($1) as $2',$param->fields);
 748         $param->extras = ' GROUP BY timeend'.((!empty($param->aggregategroupby)) ? ','.$param->aggregategroupby : '');
 749     }
 750     */
 751     //TODO must add the SITEID reports to the rest of the reports.
 752     return $param;
 753 }
 754
 755 function stats_get_view_actions() {
 756     return array('view','view all','history');
 757 }
 758
 759 function stats_get_post_actions() {
 760     return array('add','delete','edit','add mod','delete mod','edit section'.'enrol','loginas','new','unenrol','update','update mod');
 761 }
 762
 763 function stats_get_action_sql_in($str) {
 764     global $CFG;
 765
 766     $mods = get_records('modules');
 767     $function = 'stats_get_'.$str.'_actions';
 768     $actions = $function();
 769     foreach ($mods as $mod) {
 770         require_once($CFG->dirroot.'/mod/'.$mod->name.'/lib.php');
 771         $function = $mod->name.'_get_'.$str.'_actions';
 772         if (function_exists($function)) {
 773             $actions = array_merge($actions,$function());
 774         }
 775     }
 776     $actions = array_unique($actions);
 777     if (empty($actions)) {
 778         return ' ';
 779     } else if (count($actions) == 1) {
 780         return ' AND l.action = '.array_pop($actions).' ';
 781     } else {
 782         return ' AND l.action IN (\''.implode('\',\'',$actions).'\') ';
 783     }
 784 }
 785
 786
 787 function stats_get_course_users($course,$timesql) {
 788     global $CFG;
 789
 790     $timesql = str_replace('timeend','l.time',$timesql);
 791
 792     $sql = "SELECT userid, primaryrole FROM (
 793                 SELECT active_course_users.userid,
 794                     (SELECT roleid FROM {$CFG->prefix}role_assignments outer_r_a INNER JOIN {$CFG->prefix}role outer_r ON outer_r_a.roleid=outer_r.id
 795                         INNER JOIN {$CFG->prefix}context c ON outer_r_a.contextid = c.id
 796                         WHERE c.instanceid=".$course->id." AND c.contextlevel = ".CONTEXT_COURSE." AND outer_r_a.userid=active_course_users.userid
 797                         AND NOT EXISTS (SELECT 1 FROM {$CFG->prefix}role_assignments inner_r_a
 798                             INNER JOIN {$CFG->prefix}role inner_r ON inner_r_a.roleid = inner_r.id
 799                             WHERE inner_r.sortorder < outer_r.sortorder
 800                             AND inner_r_a.userid = outer_r_a.userid
 801                             AND inner_r_a.contextid = outer_r_a.contextid
 802                         )
 803                     ) AS primaryrole
 804                     FROM (SELECT DISTINCT userid FROM {$CFG->prefix}log l WHERE course=".$course->id." AND ".$timesql." )
 805                     active_course_users
 806                 ) foo WHERE primaryrole IS NOT NULL";
 807     if (!$users = get_records_sql($sql)) {
 808         $users = array();
 809     }
 810
 811     return $users;
 812
 813 }
 814
 815 function stats_do_daily_user_cron($course,$user,$roleid,$timesql,$timeend,$mods) {
 816
 817     global $CFG;
 818
 819     $stat = new StdClass;
 820     $stat->userid   = $user->userid;
 821     $stat->roleid   = $roleid;
 822     $stat->courseid = $course->id;
 823     $stat->stattype = 'activity';
 824     $stat->timeend  = $timeend;
 825
 826     $sql = 'SELECT COUNT(l.id) FROM '.$CFG->prefix.'log l WHERE l.userid = '.$user->userid
 827         .' AND  l.course = '.$course->id
 828         .' AND '.$timesql .' '.stats_get_action_sql_in('view');
 829
 830     $stat->statsreads  = count_records_sql($sql);
 831
 832     $sql = 'SELECT COUNT(l.id) FROM '.$CFG->prefix.'log l WHERE l.userid = '.$user->userid
 833         .' AND l.course = '.$course->id
 834         .' AND '.$timesql.' '.stats_get_action_sql_in('post');
 835
 836     $stat->statswrites = count_records_sql($sql);
 837
 838     insert_record('stats_user_daily',$stat,false);
 839
 840     // now ask the modules if they want anything.
 841     foreach ($mods as $mod => $fname) {
 842         mtrace('  doing daily statistics for '.$mod->name);
 843         $fname($course,$user,$timeend,$roleid);
 844     }
 845 }
 846
 847 function stats_do_aggregate_user_cron($course,$user,$roleid,$timesql,$timeend,$timestr,$mods) {
 848
 849     global $CFG;
 850
 851     $stat = new StdClass;
 852     $stat->userid   = $user->userid;
 853     $stat->roleid   = $roleid;
 854     $stat->courseid = $course->id;
 855     $stat->stattype = 'activity';
 856     $stat->timeend  = $timeend;
 857
 858     $sql = 'SELECT sum(statsreads) as statsreads, sum(statswrites) as statswrites FROM '.$CFG->prefix.'stats_user_daily WHERE courseid = '.$course->id.' AND '.$timesql
 859         ." AND roleid=".$roleid." AND userid = ".$stat->userid." AND stattype='activity'"; // add on roleid in case they have teacher and student records.
 860
 861     $r = get_record_sql($sql);
 862     $stat->statsreads = (empty($r->statsreads)) ? 0 : $r->statsreads;
 863     $stat->statswrites = (empty($r->statswrites)) ? 0 : $r->statswrites;
 864
 865     insert_record('stats_user_'.$timestr,$stat,false);
 866
 867     // now ask the modules if they want anything.
 868     foreach ($mods as $mod => $fname) {
 869         mtrace('  doing '.$timestr.' statistics for '.$mod->name);
 870         $fname($course,$user,$timeend,$roleid);
 871     }
 872 }
 873
 874 function stats_do_aggregate_user_login_cron($timesql,$timeend,$timestr) {
 875     global $CFG;
 876
 877     $sql = 'SELECT userid,roleid,sum(statsreads) as statsreads, sum(statswrites) as writes FROM '.$CFG->prefix.'stats_user_daily WHERE stattype = \'logins\' AND '.$timesql.' GROUP BY userid,roleid';
 878
 879     if ($users = get_records_sql($sql)) {
 880         foreach ($users as $stat) {
 881             $stat->courseid = SITEID;
 882             $stat->timeend = $timeend;
 883             $stat->stattype = 'logins';
 884
 885             insert_record('stats_user_'.$timestr,$stat,false);
 886         }
 887     }
 888 }
 889
 890
 891 function stats_get_time_options($now,$lastweekend,$lastmonthend,$earliestday,$earliestweek,$earliestmonth) {
 892
 893     $now = stats_get_base_daily(time());
 894     // it's really important that it's TIMEEND in the table. ie, tuesday 00:00:00 is monday night.
 895     // so we need to take a day off here (essentially add a day to $now
 896     $now += 60*60*24;
 897
 898     $timeoptions = array();
 899
 900     if ($now - (60*60*24*7) >= $earliestday) {
 901         $timeoptions[STATS_TIME_LASTWEEK] = get_string('numweeks','moodle',1);
 902     }
 903     if ($now - (60*60*24*14) >= $earliestday) {
 904         $timeoptions[STATS_TIME_LAST2WEEKS] = get_string('numweeks','moodle',2);
 905     }
 906     if ($now - (60*60*24*21) >= $earliestday) {
 907         $timeoptions[STATS_TIME_LAST3WEEKS] = get_string('numweeks','moodle',3);
 908     }
 909     if ($now - (60*60*24*28) >= $earliestday) {
 910         $timeoptions[STATS_TIME_LAST4WEEKS] = get_string('numweeks','moodle',4);// show dailies up to (including) here.
 911     }
 912     if ($lastweekend - (60*60*24*56) >= $earliestweek) {
 913         $timeoptions[STATS_TIME_LAST2MONTHS] = get_string('nummonths','moodle',2);
 914     }
 915     if ($lastweekend - (60*60*24*84) >= $earliestweek) {
 916         $timeoptions[STATS_TIME_LAST3MONTHS] = get_string('nummonths','moodle',3);
 917     }
 918     if ($lastweekend - (60*60*24*112) >= $earliestweek) {
 919         $timeoptions[STATS_TIME_LAST4MONTHS] = get_string('nummonths','moodle',4);
 920     }
 921     if ($lastweekend - (60*60*24*140) >= $earliestweek) {
 922         $timeoptions[STATS_TIME_LAST5MONTHS] = get_string('nummonths','moodle',5);
 923     }
 924     if ($lastweekend - (60*60*24*168) >= $earliestweek) {
 925         $timeoptions[STATS_TIME_LAST6MONTHS] = get_string('nummonths','moodle',6); // show weeklies up to (including) here
 926     }
 927     if (strtotime('-7 months',$lastmonthend) >= $earliestmonth) {
 928         $timeoptions[STATS_TIME_LAST7MONTHS] = get_string('nummonths','moodle',7);
 929     }
 930     if (strtotime('-8 months',$lastmonthend) >= $earliestmonth) {
 931         $timeoptions[STATS_TIME_LAST8MONTHS] = get_string('nummonths','moodle',8);
 932     }
 933     if (strtotime('-9 months',$lastmonthend) >= $earliestmonth) {
 934         $timeoptions[STATS_TIME_LAST9MONTHS] = get_string('nummonths','moodle',9);
 935     }
 936     if (strtotime('-10 months',$lastmonthend) >= $earliestmonth) {
 937         $timeoptions[STATS_TIME_LAST10MONTHS] = get_string('nummonths','moodle',10);
 938     }
 939     if (strtotime('-11 months',$lastmonthend) >= $earliestmonth) {
 940         $timeoptions[STATS_TIME_LAST11MONTHS] = get_string('nummonths','moodle',11);
 941     }
 942     if (strtotime('-1 year',$lastmonthend) >= $earliestmonth) {
 943         $timeoptions[STATS_TIME_LASTYEAR] = get_string('lastyear');
 944     }
 945
 946     return $timeoptions;
 947 }
 948
 949 function stats_get_report_options($courseid,$mode) {
 950     global $CFG;
 951
 952     $reportoptions = array();
 953
 954     switch ($mode) {
 955     case STATS_MODE_GENERAL:
 956         $reportoptions[STATS_REPORT_ACTIVITY] = get_string('statsreport'.STATS_REPORT_ACTIVITY);
 957         if ($courseid != SITEID && $context = get_record('context','instanceid',$courseid,'contextlevel',CONTEXT_COURSE)) {
 958             $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;
 959             if ($roles = get_records_sql($sql)) {
 960                 foreach ($roles as $role) {
 961                     $reportoptions[STATS_REPORT_ACTIVITYBYROLE.$role->id] = get_string('statsreport'.STATS_REPORT_ACTIVITYBYROLE). ' '.$role->name;
 962                 }
 963             }
 964         }
 965         $reportoptions[STATS_REPORT_READS] = get_string('statsreport'.STATS_REPORT_READS);
 966         $reportoptions[STATS_REPORT_WRITES] = get_string('statsreport'.STATS_REPORT_WRITES);
 967         if ($courseid == SITEID) {
 968             $reportoptions[STATS_REPORT_LOGINS] = get_string('statsreport'.STATS_REPORT_LOGINS);
 969         }
 970
 971         break;
 972     case STATS_MODE_DETAILED:
 973         $reportoptions[STATS_REPORT_USER_ACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ACTIVITY);
 974         $reportoptions[STATS_REPORT_USER_ALLACTIVITY] = get_string('statsreport'.STATS_REPORT_USER_ALLACTIVITY);
 975         if (has_capability('moodle/site:viewreports', get_context_instance(CONTEXT_SYSTEM, SITEID))) {
 976             $site = get_site();
 977             $reportoptions[STATS_REPORT_USER_LOGINS] = get_string('statsreport'.STATS_REPORT_USER_LOGINS);
 978         }
 979         break;
 980     case STATS_MODE_RANKED:
 981         if (has_capability('moodle/site:viewreports', get_context_instance(CONTEXT_SYSTEM, SITEID))) {
 982             $reportoptions[STATS_REPORT_ACTIVE_COURSES] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES);
 983             $reportoptions[STATS_REPORT_ACTIVE_COURSES_WEIGHTED] = get_string('statsreport'.STATS_REPORT_ACTIVE_COURSES_WEIGHTED);
 984             $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES);
 985             $reportoptions[STATS_REPORT_PARTICIPATORY_COURSES_RW] = get_string('statsreport'.STATS_REPORT_PARTICIPATORY_COURSES_RW);
 986         }
 987      break;
 988     }
 989
 990     return $reportoptions;
 991 }
 992
 993 function stats_fix_zeros($stats,$timeafter,$timestr,$line2=true,$line3=false) {
 994
 995     if (empty($stats)) {
 996         return;
 997     }
 998
 999     $timestr = str_replace('user_','',$timestr); // just in case.
1000     $fun = 'stats_get_base_'.$timestr;
1001
1002     $now = $fun();
1003
1004     $times = array();
1005     // add something to timeafter since it is our absolute base
1006     $actualtimes = array();
1007     foreach ($stats as $s) {
1008         $actualtimes[] = $s->timeend;
1009     }
1010
1011     $timeafter = array_pop(array_values($actualtimes));
1012
1013     while ($timeafter < $now) {
1014         $times[] = $timeafter;
1015         if ($timestr == 'daily') {
1016             $timeafter = stats_get_next_dayend($timeafter);
1017         } else if ($timestr == 'weekly') {
1018             $timeafter = stats_get_next_weekend($timeafter);
1019         } else if ($timestr == 'monthly') {
1020             $timeafter = stats_get_next_monthend($timeafter);
1021         } else {
1022             return $stats; // this will put us in a never ending loop.
1023         }
1024     }
1025
1026     foreach ($times as $count => $time) {
1027         if (!in_array($time,$actualtimes) && $count != count($times) -1) {
1028             $newobj = new StdClass;
1029             $newobj->timeend = $time;
1030             $newobj->id = 0;
1031             $newobj->roleid = 0;
1032             $newobj->line1 = 0;
1033             if (!empty($line2)) {
1034                 $newobj->line2 = 0;
1035             }
1036             if (!empty($line3)) {
1037                 $newobj->line3 = 0;
1038             }
1039             $newobj->zerofixed = true;
1040             $stats[] = $newobj;
1041         }
1042     }
1043
1044     usort($stats,"stats_compare_times");
1045     return $stats;
1046
1047 }
1048
1049 // helper function to sort arrays by $obj->timeend
1050 function stats_compare_times($a,$b) {
1051    if ($a->timeend == $b->timeend) {
1052        return 0;
1053    }
1054    return ($a->timeend > $b->timeend) ? -1 : 1;
1055 }
1056
1057 function stats_check_runtime() {
1058     global $CFG;
1059
1060     if (empty($CFG->statsmaxruntime)) {
1061         return true;
1062     }
1063
1064     if ((time() - $CFG->statsrunning) < $CFG->statsmaxruntime) {
1065         return true;
1066     }
1067
1068     return false; // we've gone over!
1069
1070 }
1071
1072 function stats_check_uptodate($courseid=0) {
1073     global $CFG;
1074
1075     if (empty($courseid)) {
1076         $courseid = SITEID;
1077     }
1078
1079     $latestday = stats_get_start_from('daily');
1080
1081     if ((time() - 60*60*24*2) < $latestday) { // we're ok
1082         return NULL;
1083     }
1084
1085     $a = new object();
1086     $a->daysdone = get_field_sql("SELECT count(distinct(timeend)) from {$CFG->prefix}stats_daily");
1087
1088     // how many days between the last day and now?
1089     $a->dayspending = ceil((stats_get_base_daily() - $latestday)/(60*60*24));
1090
1091     if ($a->dayspending == 0 && $a->daysdone != 0) {
1092         return NULL; // we've only just started...
1093     }
1094
1095     //return error as string
1096     return get_string('statscatchupmode','error',$a);
1097 }
1098
1099
1100 // copied from usergetmidnight, but we ignore dst
1101 function stats_getmidnight($date, $timezone=99) {
1102     $timezone = get_user_timezone_offset($timezone);
1103     $userdate = stats_getdate($date, $timezone);
1104     return make_timestamp($userdate['year'], $userdate['mon'], $userdate['mday'], 0, 0, 0, $timezone,false ); // ignore dst for this.
1105 }
1106
1107 function stats_getdate($time, $timezone=99) {
1108
1109     $timezone = get_user_timezone_offset($timezone);
1110
1111     if (abs($timezone) > 13) {    // Server time
1112         return getdate($time);
1113     }
1114
1115     // There is no gmgetdate so we use gmdate instead
1116     $time += intval((float)$timezone * HOURSECS);
1117     $datestring = strftime('%S_%M_%H_%d_%m_%Y_%w_%j_%A_%B', $time);
1118     list(
1119         $getdate['seconds'],
1120         $getdate['minutes'],
1121         $getdate['hours'],
1122         $getdate['mday'],
1123         $getdate['mon'],
1124         $getdate['year'],
1125         $getdate['wday'],
1126         $getdate['yday'],
1127         $getdate['weekday'],
1128         $getdate['month']
1129     ) = explode('_', $datestring);
1130
1131     return $getdate;
1132 }
1133
1134
1135 function stats_upgrade_for_roles_wrapper() {
1136     global $CFG;
1137     if (!empty($CFG->statsrolesupgraded)) {
1138         return true;
1139     }
1140
1141     $result = begin_sql();
1142
1143     $result = $result && stats_upgrade_user_table_for_roles('daily');
1144     $result = $result && stats_upgrade_user_table_for_roles('weekly');
1145     $result = $result && stats_upgrade_user_table_for_roles('monthly');
1146
1147     $result = $result && stats_upgrade_table_for_roles('daily');
1148     $result = $result && stats_upgrade_table_for_roles('weekly');
1149     $result = $result && stats_upgrade_table_for_roles('monthly');
1150
1151
1152     $result = $result && commit_sql();
1153
1154     if (!empty($result)) {
1155         set_config('statsrolesupgraded',time());
1156     }
1157
1158     return $result;
1159 }
1160
1161 /**
1162  * Upgrades a prefix_stats_user_* table for the new role based permission
1163  * system.
1164  *
1165  * @param string $period  daily, weekly or monthly: the stat period to upgrade
1166  * @return boolean @todo maybe something else (error message) depending on
1167  * how this will be called.
1168  */
1169 function stats_upgrade_user_table_for_roles ($period) {
1170     global $CFG;
1171     static $teacher_role_id, $student_role_id;
1172
1173     if (!in_array($period, array('daily', 'weekly', 'monthly'))) {
1174         error_log('stats upgrade:  invalid period: ' . $period);
1175         return false;
1176     }
1177
1178     if (!$teacher_role_id) {
1179         $role            = get_roles_with_capability('moodle/legacy:editingteacher', CAP_ALLOW);
1180         $role            = array_keys($role);
1181         $teacher_role_id = $role[0];
1182         $role            = get_roles_with_capability('moodle/legacy:student', CAP_ALLOW);
1183         $role            = array_keys($role);
1184         $student_role_id = $role[0];
1185     }
1186
1187     if (empty($teacher_role_id) || empty($student_role_id)) {
1188         error_log("Couldn't find legacy roles for teacher or student");
1189         return false;
1190     }
1191
1192     $status = true;
1193
1194     $status = $status && execute_sql("UPDATE {$CFG->prefix}stats_user_{$period}
1195         SET roleid = $teacher_role_id
1196         WHERE roleid = 1");
1197     $status = $status && execute_sql("UPDATE {$CFG->prefix}stats_user_{$period}
1198         SET roleid = $student_role_id
1199         WHERE roleid = 2");
1200
1201     return $status;
1202 }
1203
1204 /**
1205  * Upgrades a prefix_stats_* table for the new role based permission system.
1206  *
1207  * @param string $period  daily, weekly or monthly: the stat period to upgrade
1208  * @return boolean        @todo depends on how this will be called
1209  */
1210 function stats_upgrade_table_for_roles ($period) {
1211     global $CFG;
1212     static $teacher_role_id, $student_role_id;
1213
1214     if (!in_array($period, array('daily', 'weekly', 'monthly'))) {
1215         return false;
1216     }
1217
1218     if (!$teacher_role_id) {
1219         $role            = get_roles_with_capability('moodle/legacy:teacher', CAP_ALLOW);
1220         $role            = array_keys($role);
1221         $teacher_role_id = $role[0];
1222         $role            = get_roles_with_capability('moodle/legacy:student', CAP_ALLOW);
1223         $role            = array_keys($role);
1224         $student_role_id = $role[0];
1225     }
1226
1227     if (empty($teacher_role_id) || empty($student_role_id)) {
1228         error_log("Couldn't find legacy roles for teacher or student");
1229         return false;
1230     }
1231
1232     execute_sql("CREATE TABLE {$CFG->prefix}stats_{$period}_tmp AS
1233         SELECT * FROM {$CFG->prefix}stats_{$period}");
1234
1235     $table = new XMLDBTable('stats_' . $period);
1236     if (!drop_table($table)) {
1237         return false;
1238     }
1239
1240     // Create a new stats table
1241     // @todo this definition I have made blindly by looking at how definitions are
1242     // made, it needs work to make sure it works properly
1243     require_once("$CFG->libdir/xmldb/classes/XMLDBTable.class.php");
1244
1245     $table = new XMLDBTable('stats_' . $period);
1246     $table->addFieldInfo('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1247         XMLDB_NOTNULL, XMLDB_SEQUENCE, null, null, null);
1248
1249     $table->addFieldInfo('courseid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1250         XMLDB_NOTNULL, null, null, null, null);
1251
1252     $table->addFieldInfo('roleid', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1253         XMLDB_NOTNULL, null, null, null, null);
1254     $table->addFieldInfo('timeend', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1255         XMLDB_NOTNULL, null, null, null, null);
1256     $table->addFieldInfo('stattype', XMLDB_TYPE_CHAR, '20', null, XMLDB_NOTNULL,
1257         null, XMLDB_ENUM, array('enrolments', 'activity', 'logins'), 'activity');
1258     $table->addFieldInfo('stat1', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1259         XMLDB_NOTNULL, null, null, null, null);
1260     $table->addFieldInfo('stat2', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED,
1261         XMLDB_NOTNULL, null, null, null, null);
1262
1263     /// Adding keys to table stats_daily
1264     $table->addKeyInfo('primary', XMLDB_KEY_PRIMARY, array('id'));
1265
1266     /// Adding indexes to table stats_daily
1267     $table->addIndexInfo('courseid', XMLDB_INDEX_NOTUNIQUE, array('courseid'));
1268     $table->addIndexInfo('timeend', XMLDB_INDEX_NOTUNIQUE, array('timeend'));
1269     $table->addIndexInfo('roleid', XMLDB_INDEX_NOTUNIQUE, array('roleid'));
1270
1271     if (!create_table($table)) {
1272         return false;
1273     }
1274
1275     //
1276     // Now insert the data from the temporary table into the new one
1277     //
1278
1279     // Student enrolments
1280     execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
1281        (courseid, roleid, timeend, stattype, stat1, stat2)
1282        SELECT courseid, $student_role_id, timeend, 'enrolments', students, activestudents
1283        FROM {$CFG->prefix}stats_{$period}_tmp");
1284
1285     // Teacher enrolments
1286     execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
1287        (courseid, roleid, timeend, stattype, stat1, stat2)
1288        SELECT courseid, $teacher_role_id, timeend, 'enrolments', teachers, activeteachers
1289        FROM {$CFG->prefix}stats_{$period}_tmp");
1290
1291     // Student activity
1292     execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
1293        (courseid, roleid, timeend, stattype, stat1, stat2)
1294        SELECT courseid, $student_role_id, timeend, 'activity', studentreads, studentwrites
1295        FROM {$CFG->prefix}stats_{$period}_tmp");
1296
1297     // Teacher activity
1298     execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
1299        (courseid, roleid, timeend, stattype, stat1, stat2)
1300        SELECT courseid, $teacher_role_id, timeend, 'activity', teacherreads, teacherwrites
1301        FROM {$CFG->prefix}stats_{$period}_tmp");
1302
1303     // Logins
1304     execute_sql("INSERT INTO {$CFG->prefix}stats_{$period}
1305        (courseid, roleid, timeend, stattype, stat1, stat2)
1306        SELECT courseid, 0, timeend, 'logins', logins, uniquelogins
1307        FROM {$CFG->prefix}stats_{$period}_tmp WHERE courseid = ".SITEID);
1308
1309     // Drop the temporary table
1310     $table = new XMLDBTable('stats_' . $period . '_tmp');
1311     if (!drop_table($table)) {
1312         return false;
1313     }
1314
1315     return true;
1316 }
1317
1318 ?>