MDL-24081 completion Fixing sql injections and use of sql_ilike()
[moodle.git] / course / report / progress / index.php
CommitLineData
4e781c7b 1<?php
4e781c7b 2require_once('../../../config.php');
4e1132a8 3require_once($CFG->libdir . '/completionlib.php');
4e781c7b 4
24a3b341 5define('COMPLETION_REPORT_PAGE', 25);
35da0e87 6
4e781c7b 7// Get course
dfab77a2 8$id = required_param('course',PARAM_INT);
9$course=$DB->get_record('course',array('id'=>$id));
4e781c7b 10if(!$course) {
63391854 11 print_error('invalidcourseid');
4e781c7b 12}
13
14// Sort (default lastname, optionally firstname)
24a3b341
AB
15$sort = optional_param('sort','',PARAM_ALPHA);
16$firstnamesort = $sort == 'firstname';
4e781c7b 17
18// CSV format
24a3b341
AB
19$format = optional_param('format','',PARAM_ALPHA);
20$excel = $format == 'excelcsv';
21$csv = $format == 'csv' || $excel;
4e781c7b 22
24a3b341
AB
23// Paging
24$start = optional_param('start', 0, PARAM_INT);
25$sifirst = optional_param('sifirst', 'all', PARAM_ALPHA);
26$silast = optional_param('silast', 'all', PARAM_ALPHA);
27$start = optional_param('start',0,PARAM_INT);
35da0e87 28
f9498855 29// Whether to show idnumber
63391854 30// TODO: This should really not be using a config option 'intended' for
a5a63515 31// gradebook, but that option is also used in quiz reports as well. There ought
32// to be a generic option somewhere.
24a3b341 33$idnumbers = $CFG->grade_report_showuseridnumber;
f9498855 34
4e781c7b 35function csv_quote($value) {
b3aa7ae8 36 global $excel;
37 if($excel) {
38 $tl=textlib_get_instance();
39 return $tl->convert('"'.str_replace('"',"'",$value).'"','UTF-8','UTF-16LE');
40 } else {
41 return '"'.str_replace('"',"'",$value).'"';
42 }
4e781c7b 43}
44
a6855934 45$url = new moodle_url('/course/report/progress/index.php', array('course'=>$id));
dfab77a2 46if ($sort !== '') {
47 $url->param('sort', $sort);
48}
49if ($format !== '') {
50 $url->param('format', $format);
51}
7c4efe3b 52if ($start !== 0) {
dfab77a2 53 $url->param('start', $start);
54}
55$PAGE->set_url($url);
7c4efe3b 56$PAGE->set_pagelayout('admin');
dfab77a2 57
a2e4bf7f 58require_login($course);
4e781c7b 59
60// Check basic permission
61$context=get_context_instance(CONTEXT_COURSE,$course->id);
0d130857 62require_capability('coursereport/progress:view',$context);
4e781c7b 63
64// Get group mode
65$group=groups_get_course_group($course,true); // Supposed to verify group
66if($group===0 && $course->groupmode==SEPARATEGROUPS) {
67 require_capability('moodle/site:accessallgroups',$context);
68}
69
70// Get data on activities and progress of all users, and give error if we've
71// nothing to display (no users or no activities)
72$reportsurl=$CFG->wwwroot.'/course/report.php?id='.$course->id;
73$completion=new completion_info($course);
74$activities=$completion->get_activities();
75if(count($activities)==0) {
76 print_error('err_noactivities','completion',$reportsurl);
77}
4e781c7b 78
24a3b341
AB
79// Generate where clause
80$where = array();
5642a8e5 81$where_params = array();
24a3b341
AB
82
83if ($sifirst !== 'all') {
5642a8e5
AB
84 $where[] = $DB->sql_like('u.firstname', ':sifirst', false);
85 $where_params['sifirst'] = $sifirst.'%';
24a3b341
AB
86}
87
88if ($silast !== 'all') {
5642a8e5
AB
89 $where[] = $DB->sql_like('u.lastname', ':silast', false);
90 $where_params['silast'] = $silast.'%';
24a3b341
AB
91}
92
93// Get user match count
5642a8e5 94$total = $completion->get_num_tracked_users(implode(' AND ', $where), $where_params, $group);
24a3b341
AB
95
96// Total user count
5642a8e5 97$grandtotal = $completion->get_num_tracked_users('', array(), $group);
24a3b341
AB
98
99// If no users in this course what-so-ever
100if (!$grandtotal) {
101 print_box_start('errorbox errorboxcontent boxaligncenter boxwidthnormal');
102 print '<p class="nousers">'.get_string('err_nousers','completion').'</p>';
103 print '<p><a href="'.$CFG->wwwroot.'/course/report.php?id='.$course->id.'">'.get_string('continue').'</a></p>';
104 print_box_end();
105 print_footer($course);
106 exit;
107}
108
109// Get user data
110$progress = array();
111
112if ($total) {
113 $progress = $completion->get_progress_all(
114 implode(' AND ', $where),
5642a8e5 115 $where_params,
24a3b341
AB
116 $group,
117 $firstnamesort ? 'u.firstname ASC' : 'u.lastname ASC',
118 $csv ? 0 : COMPLETION_REPORT_PAGE,
119 $csv ? 0 : $start
120 );
121}
b3aa7ae8 122
4e781c7b 123if($csv) {
4e781c7b 124 header('Content-Disposition: attachment; filename=progress.'.
125 preg_replace('/[^a-z0-9-]/','_',strtolower($course->shortname)).'.csv');
b3aa7ae8 126 // Unicode byte-order mark for Excel
127 if($excel) {
128 header('Content-Type: text/csv; charset=UTF-16LE');
129 print chr(0xFF).chr(0xFE);
130 $sep="\t".chr(0);
63391854 131 $line="\n".chr(0);
b3aa7ae8 132 } else {
133 header('Content-Type: text/csv; charset=UTF-8');
134 $sep=",";
135 $line="\n";
136 }
4e781c7b 137} else {
138 // Use SVG to draw sideways text if supported
0eab1dc0 139 $svgcleverness = can_use_rotated_text();
4e781c7b 140
141 // Navigation and header
142 $strreports = get_string("reports");
143 $strcompletion = get_string('completionreport','completion');
0a122046 144
145 $PAGE->set_title($strcompletion);
146 $PAGE->set_heading($course->fullname);
0a122046 147 echo $OUTPUT->header();
148
4e781c7b 149 if($svgcleverness) {
f44b10ed 150 $PAGE->requires->yui2_lib('event');
9dec75db 151 $PAGE->requires->js('/course/report/progress/textrotate.js');
4e781c7b 152 }
153
154 // Handle groups (if enabled)
155 groups_print_course_menu($course,$CFG->wwwroot.'/course/report/progress/?course='.$course->id);
156}
157
24a3b341
AB
158// Build link for paging
159$link = $CFG->wwwroot.'/course/report/progress/?course='.$course->id;
160if (strlen($sort)) {
161 $link .= '&amp;sort='.$sort;
162}
163$link .= '&amp;start=';
164
165// Build the the page by Initial bar
166$initials = array('first', 'last');
167$alphabet = explode(',', get_string('alphabet', 'langconfig'));
168
169$pagingbar = '';
170foreach ($initials as $initial) {
171 $var = 'si'.$initial;
172
173 $pagingbar .= ' <div class="initialbar '.$initial.'initial">';
174 $pagingbar .= get_string($initial.'name').':&nbsp;';
35da0e87 175
24a3b341
AB
176 if ($$var == 'all') {
177 $pagingbar .= '<strong>'.get_string('all').'</strong> ';
178 }
179 else {
180 $pagingbar .= '<a href="'.$link.'">'.get_string('all').'</a> ';
181 }
182
183 foreach ($alphabet as $letter) {
184 if ($$var === $letter) {
185 $pagingbar .= '<strong>'.$letter.'</strong> ';
186 }
187 else {
188 $pagingbar .= '<a href="'.$link.'&amp;'.$var.'='.$letter.'">'.$letter.'</a> ';
35da0e87 189 }
35da0e87 190 }
191
24a3b341
AB
192 $pagingbar .= '</div>';
193}
194
195// Do we need a paging bar?
196if($total > COMPLETION_REPORT_PAGE) {
35da0e87 197
24a3b341
AB
198 // Paging bar
199 $pagingbar .= '<div class="paging">';
200 $pagingbar .= get_string('page').': ';
201
202 // Display previous link
203 if ($start > 0) {
204 $pstart = max($start - COMPLETION_REPORT_PAGE, 0);
205 $pagingbar .= '(<a class="previous" href="'.$link.$pstart.'">'.get_string('previous').'</a>)&nbsp;';
35da0e87 206 }
207
24a3b341
AB
208 // Create page links
209 $curstart = 0;
210 $curpage = 0;
211 while ($curstart < $total) {
212 $curpage++;
213
214 if ($curstart == $start) {
215 $pagingbar .= '&nbsp;'.$curpage.'&nbsp;';
216 }
217 else {
218 $pagingbar .= '&nbsp;<a href="'.$link.$curstart.'">'.$curpage.'</a>&nbsp;';
219 }
220
221 $curstart += COMPLETION_REPORT_PAGE;
222 }
223
224 // Display next link
225 $nstart = $start + COMPLETION_REPORT_PAGE;
226 if ($nstart < $total) {
227 $pagingbar .= '&nbsp;(<a class="next" href="'.$link.$nstart.'">'.get_string('next').'</a>)';
228 }
229
230 $pagingbar .= '</div>';
35da0e87 231}
232
4e781c7b 233// Okay, let's draw the table of progress info,
234
63391854 235// Start of table
4e781c7b 236if(!$csv) {
237 print '<br class="clearer"/>'; // ugh
24a3b341
AB
238
239 print $pagingbar;
240
241 if (!$total) {
242 print_heading(get_string('nothingtodisplay'));
243 print_footer($course);
4e781c7b 244 exit;
245 }
24a3b341 246
4e781c7b 247 print '<table id="completion-progress" class="generaltable flexible boxaligncenter" style="text-align:left"><tr style="vertical-align:top">';
248
249 // User heading / sort option
250 print '<th scope="col" class="completion-sortchoice">';
251 if($firstnamesort) {
63391854 252 print
4e781c7b 253 get_string('firstname').' / <a href="./?course='.$course->id.'">'.
254 get_string('lastname').'</a>';
255 } else {
c215d9d4 256 print '<a href="./?course='.$course->id.'&amp;sort=firstname">'.
4e781c7b 257 get_string('firstname').'</a> / '.
258 get_string('lastname');
259 }
260 print '</th>';
63391854 261
f9498855 262 if($idnumbers) {
63391854 263 print '<th>'.get_string('idnumber').'</th>';
f9498855 264 }
63391854 265
f9498855 266} else {
267 if($idnumbers) {
268 print $sep;
269 }
4e781c7b 270}
271
272// Activities
273foreach($activities as $activity) {
274 $activity->datepassed = $activity->completionexpected && $activity->completionexpected <= time();
275 $activity->datepassedclass=$activity->datepassed ? 'completion-expired' : '';
276
277 if($activity->completionexpected) {
278 $datetext=userdate($activity->completionexpected,get_string('strftimedate','langconfig'));
279 } else {
280 $datetext='';
281 }
63391854 282
82fd829d 283 // Some names (labels) come URL-encoded and can be very long, so shorten them
9a9012dc 284 $activity->name = shorten_text($activity->name);
4e781c7b 285
286 if($csv) {
fe94025d 287 print $sep.csv_quote(strip_tags($activity->name)).$sep.csv_quote($datetext);
4e781c7b 288 } else {
289 print '<th scope="col" class="'.$activity->datepassedclass.'">'.
290 '<a href="'.$CFG->wwwroot.'/mod/'.$activity->modname.
291 '/view.php?id='.$activity->id.'">'.
f8e9c93a 292 '<img src="'.$OUTPUT->pix_url('icon', $activity->modname).'" alt="'.
4e781c7b 293 get_string('modulename',$activity->modname).'" /> <span class="completion-activityname">'.
294 format_string($activity->name).'</span></a>';
295 if($activity->completionexpected) {
296 print '<div class="completion-expected"><span>'.$datetext.'</span></div>';
297 }
298 print '</th>';
299 }
300}
301
302if($csv) {
b3aa7ae8 303 print $line;
4e781c7b 304} else {
305 print '</tr>';
306}
307
308// Row for each user
24a3b341 309foreach($progress as $user) {
4e781c7b 310 // User name
311 if($csv) {
312 print csv_quote(fullname($user));
f9498855 313 if($idnumbers) {
314 print $sep.csv_quote($user->idnumber);
315 }
4e781c7b 316 } else {
317 print '<tr><th scope="row"><a href="'.$CFG->wwwroot.'/user/view.php?id='.
318 $user->id.'&amp;course='.$course->id.'">'.fullname($user).'</a></th>';
f9498855 319 if($idnumbers) {
320 print '<td>'.htmlspecialchars($user->idnumber).'</td>';
321 }
4e781c7b 322 }
323
324 // Progress for each activity
325 foreach($activities as $activity) {
326
327 // Get progress information and state
328 if(array_key_exists($activity->id,$user->progress)) {
35da0e87 329 $thisprogress=$user->progress[$activity->id];
330 $state=$thisprogress->completionstate;
331 $date=userdate($thisprogress->timemodified);
4e781c7b 332 } else {
333 $state=COMPLETION_INCOMPLETE;
334 $date='';
335 }
336
337 // Work out how it corresponds to an icon
4e781c7b 338 switch($state) {
dbb8902e 339 case COMPLETION_INCOMPLETE : $completiontype='n'; break;
340 case COMPLETION_COMPLETE : $completiontype='y'; break;
341 case COMPLETION_COMPLETE_PASS : $completiontype='pass'; break;
342 case COMPLETION_COMPLETE_FAIL : $completiontype='fail'; break;
63391854 343 }
4e781c7b 344
dbb8902e 345 $completionicon='completion-'.
346 ($activity->completion==COMPLETION_TRACKING_AUTOMATIC ? 'auto' : 'manual').
347 '-'.$completiontype;
63391854 348
dbb8902e 349 $describe=get_string('completion-alt-auto-'.$completiontype,'completion');
4e781c7b 350 $a=new StdClass;
351 $a->state=$describe;
352 $a->date=$date;
353 $a->user=fullname($user);
d352141e 354 $a->activity=strip_tags($activity->name);
4e781c7b 355 $fulldescribe=get_string('progress-title','completion',$a);
356
357 if($csv) {
b3aa7ae8 358 print $sep.csv_quote($describe).$sep.csv_quote($date);
4e781c7b 359 } else {
360 print '<td class="completion-progresscell '.$activity->datepassedclass.'">'.
b5d0cafc 361 '<img src="'.$OUTPUT->pix_url('i/'.$completionicon).
666e8458 362 '" alt="'.$describe.'" title="'.$fulldescribe.'" /></td>';
4e781c7b 363 }
364 }
365
366 if($csv) {
b3aa7ae8 367 print $line;
4e781c7b 368 } else {
369 print '</tr>';
370 }
371}
372
373if($csv) {
374 exit;
375}
376print '</table>';
35da0e87 377print $pagingbar;
4e781c7b 378
379print '<ul class="progress-actions"><li><a href="index.php?course='.$course->id.
b3aa7ae8 380 '&amp;format=csv">'.get_string('csvdownload','completion').'</a></li>
381 <li><a href="index.php?course='.$course->id.'&amp;format=excelcsv">'.
382 get_string('excelcsvdownload','completion').'</a></li></ul>';
4e781c7b 383
d60c1124 384echo $OUTPUT->footer();
aa6c1ced 385