statistics MDL-25822 Fix get_records_sql returning non-unique first column
[moodle.git] / course / report / stats / report.php
CommitLineData
aa6c1ced 1<?php
9cfa80b5 2
3 if (!defined('MOODLE_INTERNAL')) {
4 die('Direct access to this script is forbidden.'); /// It must be included from a Moodle page
5 }
030605e5 6
7 $courses = get_courses('all','c.shortname','c.id,c.shortname,c.fullname');
8 $courseoptions = array();
9
10 foreach ($courses as $c) {
6ac96fcb 11 $context = get_context_instance(CONTEXT_COURSE, $c->id);
12
a2e4bf7f 13 if (has_capability('coursereport/stats:view', $context)) {
030605e5 14 $courseoptions[$c->id] = $c->shortname;
15 }
16 }
ceceb9dd 17
030605e5 18 $reportoptions = stats_get_report_options($course->id, $mode);
19 $timeoptions = report_stats_timeoptions($mode);
0f259f63 20 if (empty($timeoptions)) {
5a2a5331 21 print_error('nostatstodisplay', '', $CFG->wwwroot.'/course/view.php?id='.$course->id);
0f259f63 22 }
23
b4531207 24 $table = new html_table();
61460dd6 25 $table->width = 'auto';
030605e5 26
27 if ($mode == STATS_MODE_DETAILED) {
32da8f41 28 $param = stats_get_parameters($time,null,$course->id,$mode); // we only care about the table and the time string (if we have time)
29
29f83769 30 //TODO: lceanup this ugly mess
ceceb9dd 31 $sql = 'SELECT DISTINCT s.userid, u.firstname, u.lastname, u.idnumber
5183f0cc 32 FROM {stats_user_'.$param->table.'} s
33 JOIN {user} u ON u.id = s.userid
32da8f41 34 WHERE courseid = '.$course->id
35 . ((!empty($param->stattype)) ? ' AND stattype = \''.$param->stattype.'\'' : '')
36 . ((!empty($time)) ? ' AND timeend >= '.$param->timeafter : '')
61460dd6 37 .' ORDER BY u.lastname, u.firstname ASC';
ceceb9dd 38
29f83769 39 if (!$us = $DB->get_records_sql($sql, $param->params)) {
1343697c 40 print_error('nousers');
030605e5 41 }
030605e5 42
32da8f41 43 foreach ($us as $u) {
61460dd6 44 $users[$u->userid] = fullname($u, true);
030605e5 45 }
ceceb9dd 46
030605e5 47 $table->align = array('left','left','left','left','left','left','left','left');
d776d59e
PS
48 $table->data[] = array(get_string('course'),html_writer::select($courseoptions,'course',$course->id,false),
49 get_string('users'),html_writer::select($users,'userid',$userid,false),
50 get_string('statsreporttype'),html_writer::select($reportoptions,'report',($report == 5) ? $report.$roleid : $report,false),
51 get_string('statstimeperiod'),html_writer::select($timeoptions,'time',$time,false),
030605e5 52 '<input type="submit" value="'.get_string('view').'" />') ;
96edb010 53 } else if ($mode == STATS_MODE_RANKED) {
54 $table->align = array('left','left','left','left','left','left');
d776d59e
PS
55 $table->data[] = array(get_string('statsreporttype'),html_writer::select($reportoptions,'report',($report == 5) ? $report.$roleid : $report,false),
56 get_string('statstimeperiod'),html_writer::select($timeoptions,'time',$time,false),
96edb010 57 '<input type="submit" value="'.get_string('view').'" />') ;
58 } else if ($mode == STATS_MODE_GENERAL) {
030605e5 59 $table->align = array('left','left','left','left','left','left','left');
d776d59e
PS
60 $table->data[] = array(get_string('course'),html_writer::select($courseoptions,'course',$course->id,false),
61 get_string('statsreporttype'),html_writer::select($reportoptions,'report',($report == 5) ? $report.$roleid : $report,false),
62 get_string('statstimeperiod'),html_writer::select($timeoptions,'time',$time,false),
030605e5 63 '<input type="submit" value="'.get_string('view').'" />') ;
64 }
65
68febe83 66 echo '<form action="index.php" method="post">'."\n"
61460dd6 67 .'<div>'."\n"
68febe83 68 .'<input type="hidden" name="mode" value="'.$mode.'" />'."\n";
030605e5 69
16be8974 70 echo html_writer::table($table);
68febe83 71
61460dd6 72 echo '</div>';
030605e5 73 echo '</form>';
74
75 if (!empty($report) && !empty($time)) {
76 if ($report == STATS_REPORT_LOGINS && $course->id != SITEID) {
1343697c 77 print_error('reportnotavailable');
030605e5 78 }
61460dd6 79
030605e5 80 $param = stats_get_parameters($time,$report,$course->id,$mode);
0f259f63 81
030605e5 82 if ($mode == STATS_MODE_DETAILED) {
83 $param->table = 'user_'.$param->table;
84 }
61460dd6 85
96edb010 86 if (!empty($param->sql)) {
87 $sql = $param->sql;
88 } else {
29f83769 89 //TODO: lceanup this ugly mess
96edb010 90 $sql = 'SELECT '.((empty($param->fieldscomplete)) ? 'id,roleid,timeend,' : '').$param->fields
80e3b05a 91 .' FROM {stats_'.$param->table.'} WHERE '
96edb010 92 .(($course->id == SITEID) ? '' : ' courseid = '.$course->id.' AND ')
93 .((!empty($userid)) ? ' userid = '.$userid.' AND ' : '')
94 .((!empty($roleid)) ? ' roleid = '.$roleid.' AND ' : '')
95 . ((!empty($param->stattype)) ? ' stattype = \''.$param->stattype.'\' AND ' : '')
96 .' timeend >= '.$param->timeafter
97 .' '.$param->extras
98 .' ORDER BY timeend DESC';
99 }
11887b6d 100
f9c2702e 101 $stats = $DB->get_records_sql($sql);
030605e5 102
103 if (empty($stats)) {
e6db3026 104 echo $OUTPUT->notification(get_string('statsnodata'));
030605e5 105
030605e5 106 } else {
13e21433 107
108 $stats = stats_fix_zeros($stats,$param->timeafter,$param->table,(!empty($param->line2)));
109
7c5286cd 110 echo $OUTPUT->heading(format_string($course->shortname).' - '.get_string('statsreport'.$report)
13e21433 111 .((!empty($user)) ? ' '.get_string('statsreportforuser').' ' .fullname($user,true) : '')
a5d424df 112 .((!empty($roleid)) ? ' '.$DB->get_field('role','name', array('id'=>$roleid)) : ''));
13e21433 113
114
115 if (empty($CFG->gdversion)) {
116 echo "(".get_string("gdneed").")";
030605e5 117 } else {
13e21433 118 if ($mode == STATS_MODE_DETAILED) {
61460dd6 119 echo '<div class="graph"><img src="'.$CFG->wwwroot.'/course/report/stats/graph.php?mode='.$mode.'&amp;course='.$course->id.'&amp;time='.$time.'&amp;report='.$report.'&amp;userid='.$userid.'" alt="'.get_string('statisticsgraph').'" /></div';
13e21433 120 } else {
61460dd6 121 echo '<div class="graph"><img src="'.$CFG->wwwroot.'/course/report/stats/graph.php?mode='.$mode.'&amp;course='.$course->id.'&amp;time='.$time.'&amp;report='.$report.'&amp;roleid='.$roleid.'" alt="'.get_string('statisticsgraph').'" /></div>';
13e21433 122 }
030605e5 123 }
030605e5 124
b4531207 125 $table = new html_table();
13e21433 126 $table->align = array('left','center','center','center');
127 $param->table = str_replace('user_','',$param->table);
61460dd6 128 switch ($param->table) {
129 case 'daily' : $period = get_string('day'); break;
130 case 'weekly' : $period = get_string('week'); break;
131 case 'monthly': $period = get_string('month', 'form'); break;
132 default : $period = '';
133 }
134 $table->head = array(get_string('periodending','moodle',$period));
13e21433 135 if (empty($param->crosstab)) {
136 $table->head[] = $param->line1;
137 if (!empty($param->line2)) {
ceceb9dd 138 $table->head[] = $param->line2;
0f259f63 139 }
afc97d53 140 }
13e21433 141 if (empty($param->crosstab)) {
142 foreach ($stats as $stat) {
143 $a = array(userdate($stat->timeend-(60*60*24),get_string('strftimedate'),$CFG->timezone),$stat->line1);
144 if (isset($stat->line2)) {
145 $a[] = $stat->line2;
11887b6d 146 }
13e21433 147 if (empty($CFG->loglifetime) || ($stat->timeend-(60*60*24)) >= (time()-60*60*24*$CFG->loglifetime)) {
96cdaa40 148 if (has_capability('coursereport/log:view', get_context_instance(CONTEXT_COURSE, $course->id))) {
149 $a[] = '<a href="'.$CFG->wwwroot.'/course/report/log/index.php?id='.
150 $course->id.'&amp;chooselog=1&amp;showusers=1&amp;showcourses=1&amp;user='
151 .$userid.'&amp;date='.usergetmidnight($stat->timeend-(60*60*24)).'">'
152 .get_string('course').' ' .get_string('logs').'</a>&nbsp;';
153 } else {
154 $a[] = '';
155 }
13e21433 156 }
157 $table->data[] = $a;
afc97d53 158 }
13e21433 159 } else {
160 $data = array();
161 $roles = array();
162 $times = array();
163 $missedlines = array();
ceceb9dd 164 $rolenames = get_all_roles();
165 foreach ($rolenames as $r) {
166 $rolenames[$r->id] = $r->name;
167 }
168 $rolenames = role_fix_names($rolenames, get_context_instance(CONTEXT_COURSE, $course->id));
13e21433 169 foreach ($stats as $stat) {
170 if (!empty($stat->zerofixed)) {
171 $missedlines[] = $stat->timeend;
172 }
173 $data[$stat->timeend][$stat->roleid] = $stat->line1;
174 if ($stat->roleid != 0) {
175 if (!array_key_exists($stat->roleid,$roles)) {
ceceb9dd 176 $roles[$stat->roleid] = $rolenames[$stat->roleid];
11887b6d 177 }
61460dd6 178 } else {
179 if (!array_key_exists($stat->roleid,$roles)) {
180 $roles[$stat->roleid] = get_string('all');
181 }
13e21433 182 }
183 if (!array_key_exists($stat->timeend,$times)) {
184 $times[$stat->timeend] = userdate($stat->timeend,get_string('strftimedate'),$CFG->timezone);
11887b6d 185 }
186 }
61460dd6 187
13e21433 188 foreach ($data as $time => $rolesdata) {
189 if (in_array($time,$missedlines)) {
190 $rolesdata = array();
191 foreach ($roles as $roleid => $guff) {
13e21433 192 $rolesdata[$roleid] = 0;
193 }
194 }
bd903ae9 195 else {
196 foreach (array_keys($roles) as $r) {
197 if (!array_key_exists($r, $rolesdata)) {
198 $rolesdata[$r] = 0;
199 }
200 }
201 }
ceceb9dd 202 krsort($rolesdata);
13e21433 203 $row = array_merge(array($times[$time]),$rolesdata);
204 if (empty($CFG->loglifetime) || ($stat->timeend-(60*60*24)) >= (time()-60*60*24*$CFG->loglifetime)) {
96cdaa40 205 if (has_capability('coursereport/log:view', get_context_instance(CONTEXT_COURSE, $course->id))) {
206 $row[] = '<a href="'.$CFG->wwwroot.'/course/report/log/index.php?id='
207 .$course->id.'&amp;chooselog=1&amp;showusers=1&amp;showcourses=1&amp;user='.$userid
208 .'&amp;date='.usergetmidnight($time-(60*60*24)).'">'
209 .get_string('course').' ' .get_string('logs').'</a>&nbsp;';
210 } else {
211 $row[] = '';
212 }
13e21433 213 }
214 $table->data[] = $row;
0f259f63 215 }
ceceb9dd 216 krsort($roles);
13e21433 217 $table->head = array_merge($table->head,$roles);
030605e5 218 }
13e21433 219 $table->head[] = get_string('logs');
220 if (!empty($lastrecord)) {
221 $lastrecord[] = $lastlink;
222 $table->data[] = $lastrecord;
223 }
16be8974 224 echo html_writer::table($table);
030605e5 225 }
ceceb9dd 226 }
030605e5 227
aa6c1ced 228