function report_stats_timeoptions($mode)
{
global $CFG, $DB;
if ($mode == STATS_MODE_DETAILED) {
$earliestday = $DB->get_field_sql('SELECT timeend FROM {stats_user_daily} ORDER BY timeend');
$earliestweek = $DB->get_field_sql('SELECT timeend FROM {stats_user_weekly} ORDER BY timeend');
$earliestmonth = $DB->get_field_sql('SELECT timeend FROM {stats_user_monthly} ORDER BY timeend');
} else {
$earliestday = $DB->get_field_sql('SELECT timeend FROM {stats_daily} ORDER BY timeend');
$earliestweek = $DB->get_field_sql('SELECT timeend FROM {stats_weekly} ORDER BY timeend');
$earliestmonth = $DB->get_field_sql('SELECT timeend FROM {stats_monthly} ORDER BY timeend');
}
if (empty($earliestday)) {
$earliestday = time();
}
if (empty($earliestweek)) {
$earliestweek = time();
}
if (empty($earliestmonth)) {
$earliestmonth = time();
}
$now = stats_get_base_daily();
$lastweekend = stats_get_base_weekly();
$lastmonthend = stats_get_base_monthly();
return stats_get_time_options($now, $lastweekend, $lastmonthend, $earliestday, $earliestweek, $earliestmonth);
}
function report_stats_timeoptions($mode)
{
global $CFG;
$tableprefix = $CFG->prefix . 'stats_';
if ($mode == STATS_MODE_DETAILED) {
$tableprefix = $CFG->prefix . 'stats_user_';
}
$earliestday = get_field_sql('SELECT timeend FROM ' . $tableprefix . 'daily ORDER BY timeend');
$earliestweek = get_field_sql('SELECT timeend FROM ' . $tableprefix . 'weekly ORDER BY timeend');
$earliestmonth = get_field_sql('SELECT timeend FROM ' . $tableprefix . 'monthly ORDER BY timeend');
if (empty($earliestday)) {
$earliestday = time();
}
if (empty($earliestweek)) {
$earliestweek = time();
}
if (empty($earliestmonth)) {
$earliestmonth = time();
}
$now = stats_get_base_daily();
$lastweekend = stats_get_base_weekly();
$lastmonthend = stats_get_base_monthly();
return stats_get_time_options($now, $lastweekend, $lastmonthend, $earliestday, $earliestweek, $earliestmonth);
}
notify($statsstatus);
}
$earliestday = get_field_sql('SELECT timeend FROM ' . $CFG->prefix . 'stats_user_daily ORDER BY timeend');
$earliestweek = get_field_sql('SELECT timeend FROM ' . $CFG->prefix . 'stats_user_weekly ORDER BY timeend');
$earliestmonth = get_field_sql('SELECT timeend FROM ' . $CFG->prefix . 'stats_user_monthly ORDER BY timeend');
if (empty($earliestday)) {
$earliestday = time();
}
if (empty($earliestweek)) {
$earliestweek = time();
}
if (empty($earliestmonth)) {
$earliestmonth = time();
}
$now = stats_get_base_daily();
$lastweekend = stats_get_base_weekly();
$lastmonthend = stats_get_base_monthly();
$timeoptions = stats_get_time_options($now, $lastweekend, $lastmonthend, $earliestday, $earliestweek, $earliestmonth);
if (empty($timeoptions)) {
print_error('nostatstodisplay', '', $CFG->wwwroot . '/course/user.php?id=' . $course->id . '&user='******'&mode=outline');
}
// use the earliest.
$time = array_pop(array_keys($timeoptions));
$param = stats_get_parameters($time, STATS_REPORT_USER_VIEW, $course->id, STATS_MODE_DETAILED);
$param->table = 'user_' . $param->table;
$sql = 'SELECT timeend,' . $param->fields . ' FROM ' . $CFG->prefix . 'stats_' . $param->table . ' WHERE ' . ($course->id == SITEID ? '' : ' courseid = ' . $course->id . ' AND ') . ' userid = ' . $user->id . ' AND timeend >= ' . $param->timeafter . $param->extras . ' ORDER BY timeend DESC';
$stats = get_records_sql($sql);
if (empty($stats)) {
print_error('nostatstodisplay', '', $CFG->wwwroot . '/course/user.php?id=' . $course->id . '&user='******'&mode=outline');
}
// MDL-10818, do not display broken graph when user has no permission to view graph
function stats_get_parameters($time, $report, $courseid, $mode, $roleid = 0)
{
global $CFG, $DB;
$param = new stdClass();
$param->params = array();
if ($time < 10) {
// dailies
// number of days to go back = 7* time
$param->table = 'daily';
$param->timeafter = strtotime("-" . $time * 7 . " days", stats_get_base_daily());
} elseif ($time < 20) {
// weeklies
// number of weeks to go back = time - 10 * 4 (weeks) + base week
$param->table = 'weekly';
$param->timeafter = strtotime("-" . ($time - 10) * 4 . " weeks", stats_get_base_weekly());
} else {
// monthlies.
// number of months to go back = time - 20 * months + base month
$param->table = 'monthly';
$param->timeafter = strtotime("-" . ($time - 20) . " months", stats_get_base_monthly());
}
$param->extras = '';
switch ($report) {
// ******************** STATS_MODE_GENERAL ******************** //
case STATS_REPORT_LOGINS:
$param->fields = 'timeend,sum(stat1) as line1,sum(stat2) as line2';
$param->fieldscomplete = true;
$param->stattype = 'logins';
$param->line1 = get_string('statslogins');
$param->line2 = get_string('statsuniquelogins');
if ($courseid == SITEID) {
$param->extras = 'GROUP BY timeend';
}
break;
case STATS_REPORT_READS:
$param->fields = $DB->sql_concat('timeend', 'roleid') . ' AS uniqueid, timeend, roleid, stat1 as line1';
$param->fieldscomplete = true;
// set this to true to avoid anything adding stuff to the list and breaking complex queries.
$param->aggregategroupby = 'roleid';
$param->stattype = 'activity';
$param->crosstab = true;
$param->extras = 'GROUP BY timeend,roleid,stat1';
if ($courseid == SITEID) {
$param->fields = $DB->sql_concat('timeend', 'roleid') . ' AS uniqueid, timeend, roleid, sum(stat1) as line1';
$param->extras = 'GROUP BY timeend,roleid';
}
break;
case STATS_REPORT_WRITES:
$param->fields = $DB->sql_concat('timeend', 'roleid') . ' AS uniqueid, timeend, roleid, stat2 as line1';
$param->fieldscomplete = true;
// set this to true to avoid anything adding stuff to the list and breaking complex queries.
$param->aggregategroupby = 'roleid';
$param->stattype = 'activity';
$param->crosstab = true;
$param->extras = 'GROUP BY timeend,roleid,stat2';
if ($courseid == SITEID) {
$param->fields = $DB->sql_concat('timeend', 'roleid') . ' AS uniqueid, timeend, roleid, sum(stat2) as line1';
$param->extras = 'GROUP BY timeend,roleid';
}
break;
case STATS_REPORT_ACTIVITY:
$param->fields = $DB->sql_concat('timeend', 'roleid') . ' AS uniqueid, timeend, roleid, sum(stat1+stat2) as line1';
$param->fieldscomplete = true;
// set this to true to avoid anything adding stuff to the list and breaking complex queries.
$param->aggregategroupby = 'roleid';
$param->stattype = 'activity';
$param->crosstab = true;
$param->extras = 'GROUP BY timeend,roleid';
if ($courseid == SITEID) {
$param->extras = 'GROUP BY timeend,roleid';
}
break;
case STATS_REPORT_ACTIVITYBYROLE:
$param->fields = 'stat1 AS line1, stat2 AS line2';
$param->stattype = 'activity';
$rolename = $DB->get_field('role', 'name', array('id' => $roleid));
$param->line1 = $rolename . get_string('statsreads');
$param->line2 = $rolename . get_string('statswrites');
if ($courseid == SITEID) {
$param->extras = 'GROUP BY timeend';
}
break;
// ******************** STATS_MODE_DETAILED ******************** //
// ******************** STATS_MODE_DETAILED ******************** //
case STATS_REPORT_USER_ACTIVITY:
$param->fields = 'statsreads as line1, statswrites as line2';
$param->line1 = get_string('statsuserreads');
$param->line2 = get_string('statsuserwrites');
$param->stattype = 'activity';
break;
case STATS_REPORT_USER_ALLACTIVITY:
$param->fields = 'statsreads+statswrites as line1';
$param->line1 = get_string('statsuseractivity');
$param->stattype = 'activity';
break;
case STATS_REPORT_USER_LOGINS:
$param->fields = 'statsreads as line1';
$param->line1 = get_string('statsuserlogins');
$param->stattype = 'logins';
break;
case STATS_REPORT_USER_VIEW:
$param->fields = 'statsreads as line1, statswrites as line2, statsreads+statswrites as line3';
$param->line1 = get_string('statsuserreads');
$param->line2 = get_string('statsuserwrites');
$param->line3 = get_string('statsuseractivity');
$param->stattype = 'activity';
break;
// ******************** STATS_MODE_RANKED ******************** //
// ******************** STATS_MODE_RANKED ******************** //
case STATS_REPORT_ACTIVE_COURSES:
$param->fields = 'sum(stat1+stat2) AS line1';
$param->stattype = 'activity';
$param->orderby = 'line1 DESC';
$param->line1 = get_string('activity');
$param->graphline = 'line1';
break;
case STATS_REPORT_ACTIVE_COURSES_WEIGHTED:
$threshold = 0;
if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
$threshold = $CFG->statsuserthreshold;
}
$param->fields = '';
$param->sql = 'SELECT activity.courseid, activity.all_activity AS line1, enrolments.highest_enrolments AS line2,
activity.all_activity / enrolments.highest_enrolments as line3
FROM (
SELECT courseid, sum(stat1+stat2) AS all_activity
FROM {stats_' . $param->table . '}
WHERE stattype=\'activity\' AND timeend >= ' . (int) $param->timeafter . ' AND roleid = 0 GROUP BY courseid
) activity
INNER JOIN
(
SELECT courseid, max(stat1) AS highest_enrolments
FROM {stats_' . $param->table . '}
WHERE stattype=\'enrolments\' AND timeend >= ' . (int) $param->timeafter . ' AND stat1 > ' . (int) $threshold . '
GROUP BY courseid
) enrolments
ON (activity.courseid = enrolments.courseid)
ORDER BY line3 DESC';
$param->line1 = get_string('activity');
$param->line2 = get_string('users');
$param->line3 = get_string('activityweighted');
$param->graphline = 'line3';
break;
case STATS_REPORT_PARTICIPATORY_COURSES:
$threshold = 0;
if (!empty($CFG->statsuserthreshold) && is_numeric($CFG->statsuserthreshold)) {
$threshold = $CFG->statsuserthreshold;
}
$param->fields = '';
$param->sql = 'SELECT courseid, ' . $DB->sql_ceil('avg(all_enrolments)') . ' as line1, ' . $DB->sql_ceil('avg(active_enrolments)') . ' as line2, avg(proportion_active) AS line3
FROM (
SELECT courseid, timeend, stat2 as active_enrolments,
stat1 as all_enrolments, ' . $DB->sql_cast_char2real('stat2') . '/' . $DB->sql_cast_char2real('stat1') . ' AS proportion_active
FROM {stats_' . $param->table . '}
WHERE stattype=\'enrolments\' AND roleid = 0 AND stat1 > ' . (int) $threshold . '
) aq
WHERE timeend >= ' . (int) $param->timeafter . '
GROUP BY courseid
ORDER BY line3 DESC';
$param->line1 = get_string('users');
$param->line2 = get_string('activeusers');
$param->line3 = get_string('participationratio');
$param->graphline = 'line3';
break;
case STATS_REPORT_PARTICIPATORY_COURSES_RW:
$param->fields = '';
$param->sql = 'SELECT courseid, sum(views) AS line1, sum(posts) AS line2,
avg(proportion_active) AS line3
FROM (
SELECT courseid, timeend, stat1 as views, stat2 AS posts,
' . $DB->sql_cast_char2real('stat2') . '/' . $DB->sql_cast_char2real('stat1') . ' as proportion_active
FROM {stats_' . $param->table . '}
WHERE stattype=\'activity\' AND roleid = 0 AND stat1 > 0
) aq
WHERE timeend >= ' . (int) $param->timeafter . '
GROUP BY courseid
ORDER BY line3 DESC';
$param->line1 = get_string('views');
$param->line2 = get_string('posts');
$param->line3 = get_string('participationratio');
$param->graphline = 'line3';
break;
}
/*
if ($courseid == SITEID && $mode != STATS_MODE_RANKED) { // just aggregate all courses.
$param->fields = preg_replace('/(?:sum)([a-zA-Z0-9+_]*)\W+as\W+([a-zA-Z0-9_]*)/i','sum($1) as $2',$param->fields);
$param->extras = ' GROUP BY timeend'.((!empty($param->aggregategroupby)) ? ','.$param->aggregategroupby : '');
}
*/
//TODO must add the SITEID reports to the rest of the reports.
return $param;
}