MDL-25053 invalid SQL syntax
[moodle.git] / course / report / participation / index.php
CommitLineData
aa6c1ced 1<?php
bcadf372 2
3 require_once('../../../config.php');
4a8c85b0 4 require_once($CFG->dirroot.'/lib/tablelib.php');
0be6f678 5
bcadf372 6 define('DEFAULT_PAGE_SIZE', 20);
36075e09 7 define('SHOW_ALL_PAGE_SIZE', 5000);
0be6f678 8
1f411afd 9 $id = required_param('id', PARAM_INT); // course id.
4a8c85b0 10 $roleid = optional_param('roleid', 0, PARAM_INT); // which role to show
1f411afd 11 $instanceid = optional_param('instanceid', 0, PARAM_INT); // instance we're looking at.
12 $timefrom = optional_param('timefrom', 0, PARAM_INT); // how far back to look...
13 $action = optional_param('action', '', PARAM_ALPHA);
bcadf372 14 $page = optional_param('page', 0, PARAM_INT); // which page to show
15 $perpage = optional_param('perpage', DEFAULT_PAGE_SIZE, PARAM_INT); // how many per page
aa6c1ced 16
7c4efe3b
SH
17 $url = new moodle_url('/course/report/participation/index.php', array('id'=>$id));
18 if ($roleid !== 0) $url->param('roleid');
19 if ($instanceid !== 0) $url->param('instanceid');
20 if ($timefrom !== 0) $url->param('timefrom');
21 if ($action !== '') $url->param('action');
22 if ($page !== 0) $url->param('page');
23 if ($perpage !== DEFAULT_PAGE_SIZE) $url->param('perpage');
24 $PAGE->set_url($url);
25 $PAGE->set_pagelayout('admin');
bcadf372 26
4a8c85b0 27 if ($action != 'view' and $action != 'post') {
bcadf372 28 $action = ''; // default to all (don't restrict)
29 }
30
29f83769 31 if (!$course = $DB->get_record('course', array('id'=>$id))) {
bcadf372 32 print_error('invalidcourse');
33 }
34
29f83769 35 if ($roleid != 0 and !$role = $DB->get_record('role', array('id'=>$roleid))) {
75673b93 36 print_error('invalidrole');
37 }
38
4a8c85b0 39 require_login($course);
25c38f25 40 $context = get_context_instance(CONTEXT_COURSE, $course->id);
a2e4bf7f 41 require_capability('coursereport/participation:view', $context);
0be6f678 42
43 add_to_log($course->id, "course", "report participation", "report/participation/index.php?id=$course->id", $course->id);
44
bcadf372 45 $strparticipation = get_string('participationreport');
46 $strviews = get_string('views');
47 $strposts = get_string('posts');
48 $strview = get_string('view');
49 $strpost = get_string('post');
50 $strallactions = get_string('allactions');
51 $strreports = get_string('reports');
52
4a8c85b0 53 $actionoptions = array('' => $strallactions,
54 'view' => $strview,
55 'post' => $strpost,);
df2a8db1 56 if (!array_key_exists($action, $actionoptions)) {
4a8c85b0 57 $action = '';
58 }
59
0a122046 60 $PAGE->set_title($course->shortname .': '. $strparticipation);
61 $PAGE->set_heading($course->fullname);
0a122046 62 echo $OUTPUT->header();
bcadf372 63
4a8c85b0 64 $modinfo = get_fast_modinfo($course);
bcadf372 65
29f83769 66 $modules = $DB->get_records_select('modules', "visible = 1 AND name <> 'label'", null, 'name ASC');
bcadf372 67
4a8c85b0 68 $instanceoptions = array();
69 foreach ($modules as $module) {
70 if (empty($modinfo->instances[$module->name])) {
71 continue;
72 }
6770330d 73 $instances = array();
4a8c85b0 74 foreach ($modinfo->instances[$module->name] as $cm) {
6770330d 75 $instances[$cm->id] = format_string($cm->name);
bcadf372 76 }
6770330d 77 $instanceoptions[] = array(get_string('modulenameplural', $module->name)=>$instances);
bcadf372 78 }
79
80 $timeoptions = array();
81 // get minimum log time for this course
29f83769 82 $minlog = $DB->get_field_sql('SELECT min(time) FROM {log} WHERE course = ?', array($course->id));
bcadf372 83
84 $now = usergetmidnight(time());
85
86 // days
87 for ($i = 1; $i < 7; $i++) {
88 if (strtotime('-'.$i.' days',$now) >= $minlog) {
89 $timeoptions[strtotime('-'.$i.' days',$now)] = get_string('numdays','moodle',$i);
90 }
91 }
92 // weeks
93 for ($i = 1; $i < 10; $i++) {
94 if (strtotime('-'.$i.' weeks',$now) >= $minlog) {
95 $timeoptions[strtotime('-'.$i.' weeks',$now)] = get_string('numweeks','moodle',$i);
96 }
97 }
0be6f678 98 // months
bcadf372 99 for ($i = 2; $i < 12; $i++) {
100 if (strtotime('-'.$i.' months',$now) >= $minlog) {
101 $timeoptions[strtotime('-'.$i.' months',$now)] = get_string('nummonths','moodle',$i);
102 }
103 }
104 // try a year
105 if (strtotime('-1 year',$now) >= $minlog) {
106 $timeoptions[strtotime('-1 year',$now)] = get_string('lastyear');
107 }
108
4a8c85b0 109 $roleoptions = array();
4f0c2d00 110 // TODO: we need a new list of roles that are visible here
4a8c85b0 111 if ($roles = get_roles_used_in_context($context)) {
75673b93 112 foreach ($roles as $r) {
4a8c85b0 113 $roleoptions[$r->id] = $r->name;
75673b93 114 }
115 }
5577ceb3 116 $guestrole = get_guest_role();
4a8c85b0 117 if (empty($roleoptions[$guestrole->id])) {
118 $roleoptions[$guestrole->id] = $guestrole->name;
5577ceb3 119 }
0be6f678 120
4a8c85b0 121 $roleoptions = role_fix_names($roleoptions, $context);
0be6f678 122
bcadf372 123 // print first controls.
5577ceb3 124 echo '<form class="participationselectform" action="index.php" method="get"><div>'."\n".
4a8c85b0 125 '<input type="hidden" name="id" value="'.$course->id.'" />'."\n";
126 echo '<label for="menuinstanceid">'.get_string('activitymodule').'</label>'."\n";
6770330d 127 echo html_writer::select($instanceoptions, 'instanceid', $instanceid);
a0648316 128 echo '<label for="menutimefrom">'.get_string('lookback').'</label>'."\n";
d776d59e 129 echo html_writer::select($timeoptions,'timefrom',$timefrom);
a0648316 130 echo '<label for="menuroleid">'.get_string('showonly').'</label>'."\n";
d776d59e 131 echo html_writer::select($roleoptions,'roleid',$roleid,false);
a0648316 132 echo '<label for="menuaction">'.get_string('showactions').'</label>'."\n";
d776d59e 133 echo html_writer::select($actionoptions,'action',$action,false);
5577ceb3 134 echo '<input type="submit" value="'.get_string('go').'" />'."\n</div></form>\n";
bcadf372 135
75673b93 136 $baseurl = $CFG->wwwroot.'/course/report/participation/index.php?id='.$course->id.'&amp;roleid='
4a8c85b0 137 .$roleid.'&amp;instanceid='.$instanceid.'&amp;timefrom='.$timefrom.'&amp;action='.$action.'&amp;perpage='.$perpage;
bcadf372 138
71f15e84 139 if (!empty($instanceid) && !empty($roleid)) {
140 // from here assume we have at least the module we're using.
141 $cm = $modinfo->cms[$instanceid];
142 $modulename = get_string('modulename', $cm->modname);
bcadf372 143
71f15e84 144 include_once($CFG->dirroot.'/mod/'.$cm->modname.'/lib.php');
bcadf372 145
71f15e84 146 $viewfun = $cm->modname.'_get_view_actions';
147 $postfun = $cm->modname.'_get_post_actions';
bcadf372 148
71f15e84 149 if (!function_exists($viewfun) || !function_exists($postfun)) {
29f83769 150 print_error('modulemissingcode', 'error', $baseurl, $cm->modname);
71f15e84 151 }
bcadf372 152
71f15e84 153 $viewnames = $viewfun();
154 $postnames = $postfun();
bcadf372 155
75673b93 156 $table = new flexible_table('course-participation-'.$course->id.'-'.$cm->id.'-'.$roleid);
bcadf372 157 $table->course = $course;
158
4a8c85b0 159 $table->define_columns(array('fullname','count','select'));
bcadf372 160 $table->define_headers(array(get_string('user'),((!empty($action)) ? get_string($action) : get_string('allactions')),get_string('select')));
161 $table->define_baseurl($baseurl);
162
bcadf372 163 $table->set_attribute('cellpadding','5');
a0648316 164 $table->set_attribute('class', 'generaltable generalbox reporttable');
bcadf372 165
166 $table->sortable(true,'lastname','ASC');
4a8c85b0 167 $table->no_sorting('select');
0be6f678 168
bcadf372 169 $table->set_control_variables(array(
170 TABLE_VAR_SORT => 'ssort',
171 TABLE_VAR_HIDE => 'shide',
172 TABLE_VAR_SHOW => 'sshow',
173 TABLE_VAR_IFIRST => 'sifirst',
174 TABLE_VAR_ILAST => 'silast',
175 TABLE_VAR_PAGE => 'spage'
176 ));
177 $table->setup();
0be6f678 178
bcadf372 179 switch ($action) {
180 case 'view':
29f83769 181 $actions = $viewnames;
bcadf372 182 break;
183 case 'post':
29f83769 184 $actions = $postnames;
bcadf372 185 break;
186 default:
187 // some modules have stuff we want to hide, ie mail blocked etc so do actually need to limit here.
29f83769 188 $actions = array_merge($viewnames, $postnames);
bcadf372 189 }
0be6f678 190
29f83769 191 list($actionsql, $params) = $DB->get_in_or_equal($actions, SQL_PARAMS_NAMED, 'action0');
7d8e60f5 192 $actionsql = "action $actionsql";
29f83769 193
4a8c85b0 194 $relatedcontexts = get_related_contexts_string($context);
195
7d8e60f5
SH
196 $sql = "SELECT ra.userid, u.firstname, u.lastname, u.idnumber, l.actioncount AS count
197 FROM (SELECT * FROM {role_assignments} WHERE contextid $relatedcontexts AND roleid = :roleid ) ra
198 JOIN {user} u ON u.id = ra.userid
199 LEFT JOIN (
ad26248c 200 SELECT userid, COUNT(action) AS actioncount FROM {log} WHERE cmid = :instanceid AND time > :timefrom AND $actionsql GROUP BY userid
7d8e60f5 201 ) l ON (l.userid = ra.userid)";
29f83769 202 $params['roleid'] = $roleid;
203 $params['instanceid'] = $instanceid;
204 $params['timefrom'] = $timefrom;
0be6f678 205
0f21a964
PS
206 list($twhere, $tparams) = $table->get_sql_where();
207 if ($twhere) {
7d8e60f5 208 $sql .= ' WHERE '.$twhere; //initial bar
0f21a964 209 $params = array_merge($params, $tparams);
bcadf372 210 }
211
bcadf372 212 if ($table->get_sql_sort()) {
213 $sql .= ' ORDER BY '.$table->get_sql_sort();
214 }
215
4a8c85b0 216 $countsql = "SELECT COUNT(DISTINCT(ra.userid))
5183f0cc 217 FROM {role_assignments} ra
0a94af29 218 WHERE ra.contextid $relatedcontexts AND ra.roleid = :roleid";
0be6f678 219
29f83769 220 $totalcount = $DB->count_records_sql($countsql, $params);
bcadf372 221
0f21a964
PS
222 if ($twhere) {
223 $matchcount = $DB->count_records_sql($countsql.' AND '.$twhere, $params);
bcadf372 224 } else {
225 $matchcount = $totalcount;
226 }
0be6f678 227
a0648316 228 echo '<div id="participationreport">' . "\n";
229 echo '<p class="modulename">'.$modulename . ' ' . $strviews.': '.implode(', ',$viewnames).'<br />'."\n"
230 . $modulename . ' ' . $strposts.': '.implode(', ',$postnames).'</p>'."\n";
0be6f678 231
bcadf372 232 $table->initialbars($totalcount > $perpage);
233 $table->pagesize($perpage, $matchcount);
234
29f83769 235 if (!$users = $DB->get_records_sql($sql, $params, $table->get_page_start(), $table->get_page_size())) {
bcadf372 236 $users = array(); // tablelib will handle saying 'Nothing to display' for us.
237 }
238
239 $data = array();
0be6f678 240
bcadf372 241 $a->count = $totalcount;
75673b93 242 $a->items = $role->name;
0be6f678 243
bcadf372 244 if ($matchcount != $totalcount) {
4a8c85b0 245 $a->count = $matchcount.'/'.$a->count;
bcadf372 246 }
0be6f678 247
a0648316 248 echo '<h2>'.get_string('counteditems', '', $a).'</h2>'."\n";
bcadf372 249
ecbe80cf 250 echo '<form action="'.$CFG->wwwroot.'/user/action_redir.php" method="post" id="studentsform">'."\n";
a0648316 251 echo '<div>'."\n";
252 echo '<input type="hidden" name="id" value="'.$id.'" />'."\n";
11e7b506 253 echo '<input type="hidden" name="returnto" value="'. s($FULLME) .'" />'."\n";
d4a1fcaf 254 echo '<input type="hidden" name="sesskey" value="'.sesskey().'" />'."\n";
bcadf372 255
256 foreach ($users as $u) {
a0648316 257 $data = array('<a href="'.$CFG->wwwroot.'/user/view.php?id='.$u->userid.'&amp;course='.$course->id.'">'.fullname($u,true).'</a>'."\n",
bcadf372 258 ((!empty($u->count)) ? get_string('yes').' ('.$u->count.') ' : get_string('no')),
ecbe80cf 259 '<input type="checkbox" class="usercheckbox" name="user'.$u->userid.'" value="'.$u->count.'" />'."\n",
bcadf372 260 );
261 $table->add_data($data);
262 }
263
264 $table->print_html();
265
36075e09 266 if ($perpage == SHOW_ALL_PAGE_SIZE) {
a0648316 267 echo '<div id="showall"><a href="'.$baseurl.'&amp;perpage='.DEFAULT_PAGE_SIZE.'">'.get_string('showperpage', '', DEFAULT_PAGE_SIZE).'</a></div>'."\n";
bcadf372 268 }
269 else if ($matchcount > 0 && $perpage < $matchcount) {
a0648316 270 echo '<div id="showall"><a href="'.$baseurl.'&amp;perpage='.SHOW_ALL_PAGE_SIZE.'">'.get_string('showall', '', $matchcount).'</a></div>'."\n";
bcadf372 271 }
0be6f678 272
ecbe80cf
PS
273 echo '<div class="selectbuttons">';
274 echo '<input type="button" id="checkall" value="'.get_string('selectall').'" /> '."\n";
275 echo '<input type="button" id="checknone" value="'.get_string('deselectall').'" /> '."\n";
bcadf372 276 if ($perpage >= $matchcount) {
ecbe80cf 277 echo '<input type="button" id="checknos" value="'.get_string('selectnos').'" />'."\n";
bcadf372 278 }
ecbe80cf
PS
279 echo '</div>';
280 echo '<div>';
62357718 281 echo '<label for="formaction">'.get_string('withselectedusers').'</label>';
bcadf372 282 $displaylist['messageselect.php'] = get_string('messageselectadd');
ecbe80cf 283 echo html_writer::select($displaylist, 'formaction', '', array(''=>'choosedots'), array('id'=>'formactionselect'));
62357718 284 echo $OUTPUT->help_icon('withselectedusers');
a0648316 285 echo '<input type="submit" value="' . get_string('ok') . '" />'."\n";
ecbe80cf 286 echo '</div>';
a0648316 287 echo '</div>'."\n";
288 echo '</form>'."\n";
289 echo '</div>'."\n";
290
ecbe80cf 291 $PAGE->requires->js_init_call('M.coursereport_participation.init');
bcadf372 292 }
0be6f678 293
d60c1124 294 echo $OUTPUT->footer();
bcadf372 295
aa6c1ced 296