/** * Return array of users whose progress is tracked in this course. * * Optionally supply a search's where clause, group id, sorting, paging. * * @param string $where Where clause sql, referring to 'u.' fields (optional) * @param array $whereparams Where clause params (optional) * @param int $groupid Group ID to restrict to (optional) * @param string $sort Order by clause (optional) * @param int $limitfrom Result start (optional) * @param int $limitnum Result max size (optional) * @param context $extracontext If set, includes extra user information fields * as appropriate to display for current user in this context * @return array Array of user objects with standard user fields */ public function get_tracked_users($where = '', $whereparams = array(), $groupid = 0, $sort = '', $limitfrom = '', $limitnum = '', context $extracontext = null) { global $DB; list($enrolledsql, $params) = get_enrolled_sql(context_course::instance($this->course->id), 'moodle/course:isincompletionreports', $groupid, true); $sql = 'SELECT u.id, u.firstname, u.lastname, u.idnumber'; if ($extracontext) { $sql .= get_extra_user_fields_sql($extracontext, 'u', '', array('idnumber')); } $sql .= ' FROM (' . $enrolledsql . ') eu JOIN {user} u ON u.id = eu.id'; if ($where) { $sql .= " AND {$where}"; $params = array_merge($params, $whereparams); } if ($sort) { $sql .= " ORDER BY {$sort}"; } return $DB->get_records_sql($sql, $params, $limitfrom, $limitnum); }
/** * Return filtered (if provided) list of users in site, except guest and deleted users. * * @param string $sort An SQL field to sort by * @param string $dir The sort direction ASC|DESC * @param int $page The page or records to return * @param int $recordsperpage The number of records to return per page * @param string $search A simple string to search for * @param string $firstinitial Users whose first name starts with $firstinitial * @param string $lastinitial Users whose last name starts with $lastinitial * @param string $extraselect An additional SQL select statement to append to the query * @param array $extraparams Additional parameters to use for the above $extraselect * @param stdClass $extracontext If specified, will include user 'extra fields' * as appropriate for current user and given context * @return array Array of {@link $USER} records */ function get_users_listing($sort = 'lastaccess', $dir = 'ASC', $page = 0, $recordsperpage = 0, $search = '', $firstinitial = '', $lastinitial = '', $extraselect = '', array $extraparams = null, $extracontext = null) { global $DB, $CFG; $fullname = $DB->sql_fullname(); $select = "deleted <> 1 AND id <> :guestid"; $params = array('guestid' => $CFG->siteguest); if (!empty($search)) { $search = trim($search); $select .= " AND (" . $DB->sql_like($fullname, ':search1', false, false) . " OR " . $DB->sql_like('email', ':search2', false, false) . " OR username = :search3)"; $params['search1'] = "%{$search}%"; $params['search2'] = "%{$search}%"; $params['search3'] = "{$search}"; } if ($firstinitial) { $select .= " AND " . $DB->sql_like('firstname', ':fni', false, false); $params['fni'] = "{$firstinitial}%"; } if ($lastinitial) { $select .= " AND " . $DB->sql_like('lastname', ':lni', false, false); $params['lni'] = "{$lastinitial}%"; } if ($extraselect) { $select .= " AND {$extraselect}"; $params = $params + (array) $extraparams; } if ($sort) { $sort = " ORDER BY {$sort} {$dir}"; } // If a context is specified, get extra user fields that the current user // is supposed to see. $extrafields = ''; if ($extracontext) { $extrafields = get_extra_user_fields_sql($extracontext, '', '', array('id', 'username', 'email', 'firstname', 'lastname', 'city', 'country', 'lastaccess', 'confirmed', 'mnethostid')); } // warning: will return UNCONFIRMED USERS return $DB->get_records_sql("SELECT id, username, email, firstname, lastname, city, country,\n lastaccess, confirmed, mnethostid, suspended {$extrafields}\n FROM {user}\n WHERE {$select}\n {$sort}", $params, $page, $recordsperpage); }
/** * @method get_users_listing * @todo to get user list of school based on condition * @param string $sort fieldname * @param string $dir specify the order to sort * @param int $page page number * @param int $recordsperpage records perpage * @param string $extraselect extra condition to select user * @param array $extraparams * @return array of objects , list of users */ function get_users_listing($sort = 'lastaccess', $dir = 'ASC', $page = 0, $recordsperpage = 0, $extraselect = '', array $extraparams = null, $extracontext = null) { global $DB, $CFG; $extraselect; $select = "deleted <> 1 AND id <> :guestid"; //$select = "deleted=0"; $params = array('guestid' => $CFG->siteguest); if ($extraselect) { $select .= " AND $extraselect"; $params = $params + (array) $extraparams; } // If a context is specified, get extra user fields that the current user // is supposed to see. $extrafields = ''; if ($extracontext) { $extrafields = get_extra_user_fields_sql($extracontext, '', '', array('id', 'username', 'email', 'firstname', 'lastname', 'city', 'country', 'lastaccess', 'confirmed', 'mnethostid')); } /* * ###Bugreport#183-Filters * @author Naveen Kumar<*****@*****.**> * (Resolved) Added $select parameters for conditions */ // warning: will return UNCONFIRMED USERS // print_object($params); $users = $DB->get_records_sql("SELECT * FROM {user} WHERE $select", $params); $hierarchy = new hierarchy(); $schoollist = $hierarchy->get_assignedschools(); $schoollist = $hierarchy->get_school_parent($schoollist, $selected = array(), $inctop = false, $all = false); if (is_siteadmin()) { $schoollist = $hierarchy->get_school_items(); } $schoolidin = implode(',', array_keys($schoollist)); if ($users && $schoollist) { $useridin = implode(',', array_keys($users)); return $DB->get_records_sql("select user.* from (SELECT u.id, u.username, u.email, u.firstname, u.lastname, u.city, u.country, u.lastaccess, u.confirmed, u.mnethostid, u.suspended FROM {user} u JOIN {local_users} lu ON lu.userid = u.id JOIN {local_school_permissions} sp ON sp.userid = u.id WHERE u.id in ($useridin) AND sp.schoolid IN ($schoolidin) UNION SELECT u.id, u.username, u.email, u.firstname, u.lastname, u.city, u.country, u.lastaccess, u.confirmed, u.mnethostid, u.suspended FROM {user} u JOIN {local_users} lu ON lu.userid = u.id JOIN {local_userdata} ud ON ud.userid = u.id WHERE u.id in ($useridin) AND ud.schoolid IN ($schoolidin)) user GROUP BY user.id ORDER BY user.$sort $dir LIMIT $page, $recordsperpage "); } }
TABLE_VAR_SHOW => 'sshow', TABLE_VAR_IFIRST => 'sifirst', TABLE_VAR_ILAST => 'silast', TABLE_VAR_PAGE => 'spage' )); $table->setup(); // we are looking for all users with this role assigned in this context or higher $contextlist = get_related_contexts_string($context); list($esql, $params) = get_enrolled_sql($context, NULL, $currentgroup, true); $joins = array("FROM {user} u"); $wheres = array(); $extrasql = get_extra_user_fields_sql($context, 'u', '', array( 'id', 'username', 'firstname', 'lastname', 'email', 'city', 'country', 'picture', 'lang', 'timezone', 'maildisplay', 'imagealt', 'lastaccess')); if ($isfrontpage) { $select = "SELECT u.id, u.username, u.firstname, u.lastname, u.email, u.city, u.country, u.picture, u.lang, u.timezone, u.maildisplay, u.imagealt, u.lastaccess$extrasql"; $joins[] = "JOIN ($esql) e ON e.id = u.id"; // everybody on the frontpage usually if ($accesssince) { $wheres[] = get_user_lastaccess_sql($accesssince); } } else { $select = "SELECT u.id, u.username, u.firstname, u.lastname, u.email, u.city, u.country, u.picture,
/** * displays the full report * @param stdClass $scorm full SCORM object * @param stdClass $cm - full course_module object * @param stdClass $course - full course object * @param string $download - type of download being requested */ function display($scorm, $cm, $course, $download) { global $CFG, $DB, $OUTPUT, $PAGE; $contextmodule = get_context_instance(CONTEXT_MODULE, $cm->id); $action = optional_param('action', '', PARAM_ALPHA); $attemptids = optional_param_array('attemptid', array(), PARAM_RAW); $attemptsmode = optional_param('attemptsmode', SCORM_REPORT_ATTEMPTS_ALL_STUDENTS, PARAM_INT); $PAGE->set_url(new moodle_url($PAGE->url, array('attemptsmode' => $attemptsmode))); if ($action == 'delete' && has_capability('mod/scorm:deleteresponses', $contextmodule) && confirm_sesskey()) { if (scorm_delete_responses($attemptids, $scorm)) { //delete responses. add_to_log($course->id, 'scorm', 'delete attempts', 'report.php?id=' . $cm->id, implode(",", $attemptids), $cm->id); echo $OUTPUT->notification(get_string('scormresponsedeleted', 'scorm'), 'notifysuccess'); } } // find out current groups mode $currentgroup = groups_get_activity_group($cm, true); // detailed report $mform = new mod_scorm_report_interactions_settings($PAGE->url, compact('currentgroup')); if ($fromform = $mform->get_data()) { $pagesize = $fromform->pagesize; $includeqtext = $fromform->qtext; $includeresp = $fromform->resp; $includeright = $fromform->right; set_user_preference('scorm_report_pagesize', $pagesize); set_user_preference('scorm_report_interactions_qtext', $includeqtext); set_user_preference('scorm_report_interactions_resp', $includeresp); set_user_preference('scorm_report_interactions_right', $includeright); } else { $pagesize = get_user_preferences('scorm_report_pagesize', 0); $includeqtext = get_user_preferences('scorm_report_interactions_qtext', 0); $includeresp = get_user_preferences('scorm_report_interactions_resp', 1); $includeright = get_user_preferences('scorm_report_interactions_right', 0); } if ($pagesize < 1) { $pagesize = SCORM_REPORT_DEFAULT_PAGE_SIZE; } // select group menu $displayoptions = array(); $displayoptions['attemptsmode'] = $attemptsmode; $displayoptions['qtext'] = $includeqtext; $displayoptions['resp'] = $includeresp; $displayoptions['right'] = $includeright; $mform->set_data($displayoptions + array('pagesize' => $pagesize)); if ($groupmode = groups_get_activity_groupmode($cm)) { // Groups are being used if (!$download) { groups_print_activity_menu($cm, new moodle_url($PAGE->url, $displayoptions)); } } $formattextoptions = array('context' => get_context_instance(CONTEXT_COURSE, $course->id)); // We only want to show the checkbox to delete attempts // if the user has permissions and if the report mode is showing attempts. $candelete = has_capability('mod/scorm:deleteresponses', $contextmodule) && ($attemptsmode != SCORM_REPORT_ATTEMPTS_STUDENTS_WITH_NO); // select the students $nostudents = false; if (empty($currentgroup)) { // all users who can attempt scoes if (!$students = get_users_by_capability($contextmodule, 'mod/scorm:savetrack', '', '', '', '', '', '', false)) { echo $OUTPUT->notification(get_string('nostudentsyet')); $nostudents = true; $allowedlist = ''; } else { $allowedlist = array_keys($students); } } else { // all users who can attempt scoes and who are in the currently selected group if (!$groupstudents = get_users_by_capability($contextmodule, 'mod/scorm:savetrack', '', '', '', '', $currentgroup, '', false)) { echo $OUTPUT->notification(get_string('nostudentsingroup')); $nostudents = true; $groupstudents = array(); } $allowedlist = array_keys($groupstudents); } if ( !$nostudents ) { // Now check if asked download of data $coursecontext = context_course::instance($course->id); if ($download) { $filename = clean_filename("$course->shortname ".format_string($scorm->name, true,$formattextoptions)); } // Define table columns $columns = array(); $headers = array(); if (!$download && $candelete) { $columns[] = 'checkbox'; $headers[] = null; } if (!$download && $CFG->grade_report_showuserimage) { $columns[] = 'picture'; $headers[] = ''; } $columns[] = 'fullname'; $headers[] = get_string('name'); $extrafields = get_extra_user_fields($coursecontext); foreach ($extrafields as $field) { $columns[] = $field; $headers[] = get_user_field_name($field); } $columns[] = 'attempt'; $headers[] = get_string('attempt', 'scorm'); $columns[] = 'start'; $headers[] = get_string('started', 'scorm'); $columns[] = 'finish'; $headers[] = get_string('last', 'scorm'); $columns[] = 'score'; $headers[] = get_string('score', 'scorm'); $scoes = $DB->get_records('scorm_scoes', array("scorm"=>$scorm->id), 'id'); foreach ($scoes as $sco) { if ($sco->launch != '') { $columns[] = 'scograde'.$sco->id; $headers[] = format_string($sco->title,'',$formattextoptions); } } $params = array(); list($usql, $params) = $DB->get_in_or_equal($allowedlist, SQL_PARAMS_NAMED); // Construct the SQL $select = 'SELECT DISTINCT '.$DB->sql_concat('u.id', '\'#\'', 'COALESCE(st.attempt, 0)').' AS uniqueid, '; $select .= 'st.scormid AS scormid, st.attempt AS attempt, ' . 'u.id AS userid, u.idnumber, u.firstname, u.lastname, u.picture, u.imagealt, u.email'. get_extra_user_fields_sql($coursecontext, 'u', '', array('idnumber')) . ' '; // This part is the same for all cases - join users and scorm_scoes_track tables $from = 'FROM {user} u '; $from .= 'LEFT JOIN {scorm_scoes_track} st ON st.userid = u.id AND st.scormid = '.$scorm->id; switch ($attemptsmode) { case SCORM_REPORT_ATTEMPTS_STUDENTS_WITH: // Show only students with attempts $where = ' WHERE u.id ' .$usql. ' AND st.userid IS NOT NULL'; break; case SCORM_REPORT_ATTEMPTS_STUDENTS_WITH_NO: // Show only students without attempts $where = ' WHERE u.id ' .$usql. ' AND st.userid IS NULL'; break; case SCORM_REPORT_ATTEMPTS_ALL_STUDENTS: // Show all students with or without attempts $where = ' WHERE u.id ' .$usql. ' AND (st.userid IS NOT NULL OR st.userid IS NULL)'; break; } $countsql = 'SELECT COUNT(DISTINCT('.$DB->sql_concat('u.id', '\'#\'', 'COALESCE(st.attempt, 0)').')) AS nbresults, '; $countsql .= 'COUNT(DISTINCT('.$DB->sql_concat('u.id', '\'#\'', 'st.attempt').')) AS nbattempts, '; $countsql .= 'COUNT(DISTINCT(u.id)) AS nbusers '; $countsql .= $from.$where; $attempts = $DB->get_records_sql($select.$from.$where, $params); $questioncount = get_scorm_question_count($scorm->id); for($id = 0; $id < $questioncount; $id++) { if ($displayoptions['qtext']) { $columns[] = 'question' . $id; $headers[] = get_string('questionx', 'scormreport_interactions', $id); } if ($displayoptions['resp']) { $columns[] = 'response' . $id; $headers[] = get_string('responsex', 'scormreport_interactions', $id); } if ($displayoptions['right']) { $columns[] = 'right' . $id; $headers[] = get_string('rightanswerx', 'scormreport_interactions', $id); } } if (!$download) { $table = new flexible_table('mod-scorm-report'); $table->define_columns($columns); $table->define_headers($headers); $table->define_baseurl($PAGE->url); $table->sortable(true); $table->collapsible(true); // This is done to prevent redundant data, when a user has multiple attempts $table->column_suppress('picture'); $table->column_suppress('fullname'); foreach ($extrafields as $field) { $table->column_suppress($field); } $table->no_sorting('start'); $table->no_sorting('finish'); $table->no_sorting('score'); foreach ($scoes as $sco) { if ($sco->launch != '') { $table->no_sorting('scograde'.$sco->id); } } $table->column_class('picture', 'picture'); $table->column_class('fullname', 'bold'); $table->column_class('score', 'bold'); $table->set_attribute('cellspacing', '0'); $table->set_attribute('id', 'attempts'); $table->set_attribute('class', 'generaltable generalbox'); // Start working -- this is necessary as soon as the niceties are over $table->setup(); } else if ($download == 'ODS') { require_once("$CFG->libdir/odslib.class.php"); $filename .= ".ods"; // Creating a workbook $workbook = new MoodleODSWorkbook("-"); // Sending HTTP headers $workbook->send($filename); // Creating the first worksheet $sheettitle = get_string('report', 'scorm'); $myxls =& $workbook->add_worksheet($sheettitle); // format types $format =& $workbook->add_format(); $format->set_bold(0); $formatbc =& $workbook->add_format(); $formatbc->set_bold(1); $formatbc->set_align('center'); $formatb =& $workbook->add_format(); $formatb->set_bold(1); $formaty =& $workbook->add_format(); $formaty->set_bg_color('yellow'); $formatc =& $workbook->add_format(); $formatc->set_align('center'); $formatr =& $workbook->add_format(); $formatr->set_bold(1); $formatr->set_color('red'); $formatr->set_align('center'); $formatg =& $workbook->add_format(); $formatg->set_bold(1); $formatg->set_color('green'); $formatg->set_align('center'); // Here starts workshhet headers $colnum = 0; foreach ($headers as $item) { $myxls->write(0, $colnum, $item, $formatbc); $colnum++; } $rownum = 1; } else if ($download =='Excel') { require_once("$CFG->libdir/excellib.class.php"); $filename .= ".xls"; // Creating a workbook $workbook = new MoodleExcelWorkbook("-"); // Sending HTTP headers $workbook->send($filename); // Creating the first worksheet $sheettitle = get_string('report', 'scorm'); $myxls =& $workbook->add_worksheet($sheettitle); // format types $format =& $workbook->add_format(); $format->set_bold(0); $formatbc =& $workbook->add_format(); $formatbc->set_bold(1); $formatbc->set_align('center'); $formatb =& $workbook->add_format(); $formatb->set_bold(1); $formaty =& $workbook->add_format(); $formaty->set_bg_color('yellow'); $formatc =& $workbook->add_format(); $formatc->set_align('center'); $formatr =& $workbook->add_format(); $formatr->set_bold(1); $formatr->set_color('red'); $formatr->set_align('center'); $formatg =& $workbook->add_format(); $formatg->set_bold(1); $formatg->set_color('green'); $formatg->set_align('center'); $colnum = 0; foreach ($headers as $item) { $myxls->write(0, $colnum, $item, $formatbc); $colnum++; } $rownum = 1; } else if ($download == 'CSV') { $filename .= ".txt"; header("Content-Type: application/download\n"); header("Content-Disposition: attachment; filename=\"$filename\""); header("Expires: 0"); header("Cache-Control: must-revalidate,post-check=0,pre-check=0"); header("Pragma: public"); echo implode("\t", $headers)." \n"; } if (!$download) { $sort = $table->get_sql_sort(); } else { $sort = ''; } // Fix some wired sorting if (empty($sort)) { $sort = ' ORDER BY uniqueid'; } else { $sort = ' ORDER BY '.$sort; } if (!$download) { // Add extra limits due to initials bar list($twhere, $tparams) = $table->get_sql_where(); if ($twhere) { $where .= ' AND '.$twhere; //initial bar $params = array_merge($params, $tparams); } if (!empty($countsql)) { $count = $DB->get_record_sql($countsql,$params); $totalinitials = $count->nbresults; if ($twhere) { $countsql .= ' AND '.$twhere; } $count = $DB->get_record_sql($countsql, $params); $total = $count->nbresults; } $table->pagesize($pagesize, $total); echo '<div class="quizattemptcounts">'; if ( $count->nbresults == $count->nbattempts ) { echo get_string('reportcountattempts', 'scorm', $count); } else if ( $count->nbattempts>0 ) { echo get_string('reportcountallattempts', 'scorm', $count); } else { echo $count->nbusers.' '.get_string('users'); } echo '</div>'; } // Fetch the attempts if (!$download) { $attempts = $DB->get_records_sql($select.$from.$where.$sort, $params, $table->get_page_start(), $table->get_page_size()); echo '<div id="scormtablecontainer">'; if ($candelete) { // Start form $strreallydel = addslashes_js(get_string('deleteattemptcheck', 'scorm')); echo '<form id="attemptsform" method="post" action="' . $PAGE->url->out(false) . '" onsubmit="return confirm(\''.$strreallydel.'\');">'; echo '<input type="hidden" name="action" value="delete"/>'; echo '<input type="hidden" name="sesskey" value="'.sesskey().'" />'; echo '<div style="display: none;">'; echo html_writer::input_hidden_params($PAGE->url); echo '</div>'; echo '<div>'; } $table->initialbars($totalinitials>20); // Build table rows } else { $attempts = $DB->get_records_sql($select.$from.$where.$sort, $params); } if ($attempts) { foreach ($attempts as $scouser) { $row = array(); if (!empty($scouser->attempt)) { $timetracks = scorm_get_sco_runtime($scorm->id, false, $scouser->userid, $scouser->attempt); } else { $timetracks = ''; } if (in_array('checkbox', $columns)) { if ($candelete && !empty($timetracks->start)) { $row[] = '<input type="checkbox" name="attemptid[]" value="'. $scouser->userid . ':' . $scouser->attempt . '" />'; } else if ($candelete) { $row[] = ''; } } if (in_array('picture', $columns)) { $user = (object)array( 'id'=>$scouser->userid, 'picture'=>$scouser->picture, 'imagealt'=>$scouser->imagealt, 'email'=>$scouser->email, 'firstname'=>$scouser->firstname, 'lastname'=>$scouser->lastname); $row[] = $OUTPUT->user_picture($user, array('courseid'=>$course->id)); } if (!$download) { $row[] = '<a href="'.$CFG->wwwroot.'/user/view.php?id='.$scouser->userid.'&course='.$course->id.'">'.fullname($scouser).'</a>'; } else { $row[] = fullname($scouser); } foreach ($extrafields as $field) { $row[] = s($scouser->{$field}); } if (empty($timetracks->start)) { $row[] = '-'; $row[] = '-'; $row[] = '-'; $row[] = '-'; } else { if (!$download) { $row[] = '<a href="userreport.php?a='.$scorm->id.'&user='******'&attempt='.$scouser->attempt.'">'.$scouser->attempt.'</a>'; } else { $row[] = $scouser->attempt; } if ($download =='ODS' || $download =='Excel' ) { $row[] = userdate($timetracks->start, get_string("strftimedatetime", "langconfig")); } else { $row[] = userdate($timetracks->start); } if ($download =='ODS' || $download =='Excel' ) { $row[] = userdate($timetracks->finish, get_string('strftimedatetime', 'langconfig')); } else { $row[] = userdate($timetracks->finish); } $row[] = scorm_grade_user_attempt($scorm, $scouser->userid, $scouser->attempt); } // print out all scores of attempt foreach ($scoes as $sco) { if ($sco->launch != '') { if ($trackdata = scorm_get_tracks($sco->id, $scouser->userid, $scouser->attempt)) { if ($trackdata->status == '') { $trackdata->status = 'notattempted'; } $strstatus = get_string($trackdata->status, 'scorm'); // if raw score exists, print it if ($trackdata->score_raw != '') { $score = $trackdata->score_raw; // add max score if it exists if ($scorm->version == 'SCORM_1.3') { $maxkey = 'cmi.score.max'; } else { $maxkey = 'cmi.core.score.max'; } if (isset($trackdata->$maxkey)) { $score .= '/'.$trackdata->$maxkey; } // else print out status } else { $score = $strstatus; } if (!$download) { $row[] = '<img src="'.$OUTPUT->pix_url($trackdata->status, 'scorm').'" alt="'.$strstatus.'" title="'.$strstatus.'" /><br/> <a href="userreport.php?b='.$sco->id.'&user='******'&attempt='.$scouser->attempt. '" title="'.get_string('details', 'scorm').'">'.$score.'</a>'; } else { $row[] = $score; } // interaction data $i=0; $element='cmi.interactions_'.$i.'.id'; while(isset($trackdata->$element)) { if ($displayoptions['qtext']) { $element='cmi.interactions_'.$i.'.id'; if (isset($trackdata->$element)) { $row[] = s($trackdata->$element); } else { $row[] = ' '; } } if ($displayoptions['resp']) { $element='cmi.interactions_'.$i.'.student_response'; if (isset($trackdata->$element)) { $row[] = s($trackdata->$element); } else { $row[] = ' '; } } if ($displayoptions['right']) { $j=0; $element = 'cmi.interactions_'.$i.'.correct_responses_'.$j.'.pattern'; $rightans = ''; if (isset($trackdata->$element)) { while(isset($trackdata->$element)) { if($j>0) { $rightans .= ','; } $rightans .= s($trackdata->$element); $j++; $element = 'cmi.interactions_'.$i.'.correct_responses_'.$j.'.pattern'; } $row[] = $rightans; } else { $row[] = ' '; } } $i++; $element = 'cmi.interactions_'.$i.'.id'; } //---end of interaction data*/ } else { // if we don't have track data, we haven't attempted yet $strstatus = get_string('notattempted', 'scorm'); if (!$download) { $row[] = '<img src="'.$OUTPUT->pix_url('notattempted', 'scorm').'" alt="'.$strstatus.'" title="'.$strstatus.'" /><br/>'.$strstatus; } else { $row[] = $strstatus; } } } } if (!$download) { $table->add_data($row); } else if ($download == 'Excel' or $download == 'ODS') { $colnum = 0; foreach ($row as $item) { $myxls->write($rownum, $colnum, $item, $format); $colnum++; } $rownum++; } else if ($download == 'CSV') { $text = implode("\t", $row); echo $text." \n"; } } if (!$download) { $table->finish_output(); if ($candelete) { echo '<table id="commands">'; echo '<tr><td>'; echo '<a href="javascript:select_all_in(\'DIV\', null, \'scormtablecontainer\');">'. get_string('selectall', 'scorm').'</a> / '; echo '<a href="javascript:deselect_all_in(\'DIV\', null, \'scormtablecontainer\');">'. get_string('selectnone', 'scorm').'</a> '; echo ' '; echo '<input type="submit" value="'.get_string('deleteselected', 'quiz_overview').'"/>'; echo '</td></tr></table>'; // Close form echo '</div>'; echo '</form>'; } echo '</div>'; if (!empty($attempts)) { echo '<table class="boxaligncenter"><tr>'; echo '<td>'; echo $OUTPUT->single_button(new moodle_url($PAGE->url, array('download'=>'ODS') + $displayoptions), get_string('downloadods')); echo "</td>\n"; echo '<td>'; echo $OUTPUT->single_button(new moodle_url($PAGE->url, array('download'=>'Excel') + $displayoptions), get_string('downloadexcel')); echo "</td>\n"; echo '<td>'; echo $OUTPUT->single_button(new moodle_url($PAGE->url, array('download'=>'CSV') + $displayoptions), get_string('downloadtext')); echo "</td>\n"; echo "<td>"; echo "</td>\n"; echo '</tr></table>'; } } } else { if ($candelete && !$download) { echo '</div>'; echo '</form>'; $table->finish_output(); } echo '</div>'; } // Show preferences form irrespective of attempts are there to report or not if (!$download) { $mform->set_data(compact('detailedrep', 'pagesize', 'attemptsmode')); $mform->display(); } if ($download == 'Excel' or $download == 'ODS') { $workbook->close(); exit; } else if ($download == 'CSV') { exit; } } else { echo $OUTPUT->notification(get_string('noactivity', 'scorm')); } }// function ends
TABLE_VAR_SORT => 'ssort', TABLE_VAR_HIDE => 'shide', TABLE_VAR_SHOW => 'sshow', TABLE_VAR_IFIRST => 'sifirst', TABLE_VAR_ILAST => 'silast', TABLE_VAR_PAGE => 'spage' )); $table->setup(); list($esql, $params) = get_enrolled_sql($context, null, $currentgroup, true); $joins = array("FROM {user} u"); $wheres = array(); $mainuserfields = user_picture::fields('u', array('username', 'email', 'city', 'country', 'lang', 'timezone', 'maildisplay')); $alreadyretrievedfields = explode(',', $mainuserfields); $extrasql = get_extra_user_fields_sql($context, 'u', '', $alreadyretrievedfields); if ($isfrontpage) { $select = "SELECT $mainuserfields, u.lastaccess$extrasql"; $joins[] = "JOIN ($esql) e ON e.id = u.id"; // Everybody on the frontpage usually. if ($accesssince) { $wheres[] = get_user_lastaccess_sql($accesssince); } } else { $select = "SELECT $mainuserfields, COALESCE(ul.timeaccess, 0) AS lastaccess$extrasql"; $joins[] = "JOIN ($esql) e ON e.id = u.id"; // Course enrolled users only. $joins[] = "LEFT JOIN {user_lastaccess} ul ON (ul.userid = u.id AND ul.courseid = :courseid)"; // Not everybody accessed course yet. $params['courseid'] = $course->id; if ($accesssince) { $wheres[] = get_course_lastaccess_sql($accesssince);
/** * displays the full report * @param \stdClass $scorm full SCORM object * @param \stdClass $cm - full course_module object * @param \stdClass $course - full course object * @param string $download - type of download being requested */ public function display($scorm, $cm, $course, $download) { global $CFG, $DB, $OUTPUT, $PAGE; $contextmodule = \context_module::instance($cm->id); $action = optional_param('action', '', PARAM_ALPHA); $attemptids = optional_param_array('attemptid', array(), PARAM_RAW); $attemptsmode = optional_param('attemptsmode', SCORM_REPORT_ATTEMPTS_ALL_STUDENTS, PARAM_INT); $PAGE->set_url(new \moodle_url($PAGE->url, array('attemptsmode' => $attemptsmode))); if ($action == 'delete' && has_capability('mod/scorm:deleteresponses', $contextmodule) && confirm_sesskey()) { if (scorm_delete_responses($attemptids, $scorm)) { // Delete responses. echo $OUTPUT->notification(get_string('scormresponsedeleted', 'scorm'), 'notifysuccess'); } } // Find out current groups mode. $currentgroup = groups_get_activity_group($cm, true); // Detailed report. $mform = new \mod_scorm_report_objectives_settings($PAGE->url, compact('currentgroup')); if ($fromform = $mform->get_data()) { $pagesize = $fromform->pagesize; $showobjectivescore = $fromform->objectivescore; set_user_preference('scorm_report_pagesize', $pagesize); set_user_preference('scorm_report_objectives_score', $showobjectivescore); } else { $pagesize = get_user_preferences('scorm_report_pagesize', 0); $showobjectivescore = get_user_preferences('scorm_report_objectives_score', 0); } if ($pagesize < 1) { $pagesize = SCORM_REPORT_DEFAULT_PAGE_SIZE; } // Select group menu. $displayoptions = array(); $displayoptions['attemptsmode'] = $attemptsmode; $displayoptions['objectivescore'] = $showobjectivescore; $mform->set_data($displayoptions + array('pagesize' => $pagesize)); if ($groupmode = groups_get_activity_groupmode($cm)) { // Groups are being used. if (!$download) { groups_print_activity_menu($cm, new \moodle_url($PAGE->url, $displayoptions)); } } $formattextoptions = array('context' => \context_course::instance($course->id)); // We only want to show the checkbox to delete attempts // if the user has permissions and if the report mode is showing attempts. $candelete = has_capability('mod/scorm:deleteresponses', $contextmodule) && $attemptsmode != SCORM_REPORT_ATTEMPTS_STUDENTS_WITH_NO; // Select the students. $nostudents = false; if (empty($currentgroup)) { // All users who can attempt scoes. if (!($students = get_users_by_capability($contextmodule, 'mod/scorm:savetrack', 'u.id', '', '', '', '', '', false))) { echo $OUTPUT->notification(get_string('nostudentsyet')); $nostudents = true; $allowedlist = ''; } else { $allowedlist = array_keys($students); } unset($students); } else { // All users who can attempt scoes and who are in the currently selected group. $groupstudents = get_users_by_capability($contextmodule, 'mod/scorm:savetrack', 'u.id', '', '', '', $currentgroup, '', false); if (!$groupstudents) { echo $OUTPUT->notification(get_string('nostudentsingroup')); $nostudents = true; $groupstudents = array(); } $allowedlist = array_keys($groupstudents); unset($groupstudents); } if (!$nostudents) { // Now check if asked download of data. $coursecontext = \context_course::instance($course->id); if ($download) { $filename = clean_filename("{$course->shortname} " . format_string($scorm->name, true, $formattextoptions)); } // Define table columns. $columns = array(); $headers = array(); if (!$download && $candelete) { $columns[] = 'checkbox'; $headers[] = null; } if (!$download && $CFG->grade_report_showuserimage) { $columns[] = 'picture'; $headers[] = ''; } $columns[] = 'fullname'; $headers[] = get_string('name'); $extrafields = get_extra_user_fields($coursecontext); foreach ($extrafields as $field) { $columns[] = $field; $headers[] = get_user_field_name($field); } $columns[] = 'attempt'; $headers[] = get_string('attempt', 'scorm'); $columns[] = 'start'; $headers[] = get_string('started', 'scorm'); $columns[] = 'finish'; $headers[] = get_string('last', 'scorm'); $columns[] = 'score'; $headers[] = get_string('score', 'scorm'); $scoes = $DB->get_records('scorm_scoes', array("scorm" => $scorm->id), 'sortorder, id'); foreach ($scoes as $sco) { if ($sco->launch != '') { $columns[] = 'scograde' . $sco->id; $headers[] = format_string($sco->title, '', $formattextoptions); } } $params = array(); list($usql, $params) = $DB->get_in_or_equal($allowedlist, SQL_PARAMS_NAMED); // Construct the SQL. $select = 'SELECT DISTINCT ' . $DB->sql_concat('u.id', '\'#\'', 'COALESCE(st.attempt, 0)') . ' AS uniqueid, '; $select .= 'st.scormid AS scormid, st.attempt AS attempt, ' . \user_picture::fields('u', array('idnumber'), 'userid') . get_extra_user_fields_sql($coursecontext, 'u', '', array('email', 'idnumber')) . ' '; // This part is the same for all cases - join users and scorm_scoes_track tables. $from = 'FROM {user} u '; $from .= 'LEFT JOIN {scorm_scoes_track} st ON st.userid = u.id AND st.scormid = ' . $scorm->id; switch ($attemptsmode) { case SCORM_REPORT_ATTEMPTS_STUDENTS_WITH: // Show only students with attempts. $where = ' WHERE u.id ' . $usql . ' AND st.userid IS NOT NULL'; break; case SCORM_REPORT_ATTEMPTS_STUDENTS_WITH_NO: // Show only students without attempts. $where = ' WHERE u.id ' . $usql . ' AND st.userid IS NULL'; break; case SCORM_REPORT_ATTEMPTS_ALL_STUDENTS: // Show all students with or without attempts. $where = ' WHERE u.id ' . $usql . ' AND (st.userid IS NOT NULL OR st.userid IS NULL)'; break; } $countsql = 'SELECT COUNT(DISTINCT(' . $DB->sql_concat('u.id', '\'#\'', 'COALESCE(st.attempt, 0)') . ')) AS nbresults, '; $countsql .= 'COUNT(DISTINCT(' . $DB->sql_concat('u.id', '\'#\'', 'st.attempt') . ')) AS nbattempts, '; $countsql .= 'COUNT(DISTINCT(u.id)) AS nbusers '; $countsql .= $from . $where; $nbmaincolumns = count($columns); // Get number of main columns used. $objectives = get_scorm_objectives($scorm->id); $nosort = array(); foreach ($objectives as $scoid => $sco) { foreach ($sco as $id => $objectivename) { $colid = $scoid . 'objectivestatus' . $id; $columns[] = $colid; $nosort[] = $colid; if (!$displayoptions['objectivescore']) { // Display the objective name only. $headers[] = $objectivename; } else { // Display the objective status header with a "status" suffix to avoid confusion. $headers[] = $objectivename . ' ' . get_string('status', 'scormreport_objectives'); // Now print objective score headers. $colid = $scoid . 'objectivescore' . $id; $columns[] = $colid; $nosort[] = $colid; $headers[] = $objectivename . ' ' . get_string('score', 'scormreport_objectives'); } } } $emptycell = ''; // Used when an empty cell is being printed - in html we add a space. if (!$download) { $emptycell = ' '; $table = new \flexible_table('mod-scorm-report'); $table->define_columns($columns); $table->define_headers($headers); $table->define_baseurl($PAGE->url); $table->sortable(true); $table->collapsible(true); // This is done to prevent redundant data, when a user has multiple attempts. $table->column_suppress('picture'); $table->column_suppress('fullname'); foreach ($extrafields as $field) { $table->column_suppress($field); } foreach ($nosort as $field) { $table->no_sorting($field); } $table->no_sorting('start'); $table->no_sorting('finish'); $table->no_sorting('score'); $table->no_sorting('checkbox'); $table->no_sorting('picture'); foreach ($scoes as $sco) { if ($sco->launch != '') { $table->no_sorting('scograde' . $sco->id); } } $table->column_class('picture', 'picture'); $table->column_class('fullname', 'bold'); $table->column_class('score', 'bold'); $table->set_attribute('cellspacing', '0'); $table->set_attribute('id', 'attempts'); $table->set_attribute('class', 'generaltable generalbox'); // Start working -- this is necessary as soon as the niceties are over. $table->setup(); } else { if ($download == 'ODS') { require_once "{$CFG->libdir}/odslib.class.php"; $filename .= ".ods"; // Creating a workbook. $workbook = new \MoodleODSWorkbook("-"); // Sending HTTP headers. $workbook->send($filename); // Creating the first worksheet. $sheettitle = get_string('report', 'scorm'); $myxls = $workbook->add_worksheet($sheettitle); // Format types. $format = $workbook->add_format(); $format->set_bold(0); $formatbc = $workbook->add_format(); $formatbc->set_bold(1); $formatbc->set_align('center'); $formatb = $workbook->add_format(); $formatb->set_bold(1); $formaty = $workbook->add_format(); $formaty->set_bg_color('yellow'); $formatc = $workbook->add_format(); $formatc->set_align('center'); $formatr = $workbook->add_format(); $formatr->set_bold(1); $formatr->set_color('red'); $formatr->set_align('center'); $formatg = $workbook->add_format(); $formatg->set_bold(1); $formatg->set_color('green'); $formatg->set_align('center'); // Here starts workshhet headers. $colnum = 0; foreach ($headers as $item) { $myxls->write(0, $colnum, $item, $formatbc); $colnum++; } $rownum = 1; } else { if ($download == 'Excel') { require_once "{$CFG->libdir}/excellib.class.php"; $filename .= ".xls"; // Creating a workbook. $workbook = new \MoodleExcelWorkbook("-"); // Sending HTTP headers. $workbook->send($filename); // Creating the first worksheet. $sheettitle = get_string('report', 'scorm'); $myxls = $workbook->add_worksheet($sheettitle); // Format types. $format = $workbook->add_format(); $format->set_bold(0); $formatbc = $workbook->add_format(); $formatbc->set_bold(1); $formatbc->set_align('center'); $formatb = $workbook->add_format(); $formatb->set_bold(1); $formaty = $workbook->add_format(); $formaty->set_bg_color('yellow'); $formatc = $workbook->add_format(); $formatc->set_align('center'); $formatr = $workbook->add_format(); $formatr->set_bold(1); $formatr->set_color('red'); $formatr->set_align('center'); $formatg = $workbook->add_format(); $formatg->set_bold(1); $formatg->set_color('green'); $formatg->set_align('center'); $colnum = 0; foreach ($headers as $item) { $myxls->write(0, $colnum, $item, $formatbc); $colnum++; } $rownum = 1; } else { if ($download == 'CSV') { $csvexport = new \csv_export_writer("tab"); $csvexport->set_filename($filename, ".txt"); $csvexport->add_data($headers); } } } } if (!$download) { $sort = $table->get_sql_sort(); } else { $sort = ''; } // Fix some wired sorting. if (empty($sort)) { $sort = ' ORDER BY uniqueid'; } else { $sort = ' ORDER BY ' . $sort; } if (!$download) { // Add extra limits due to initials bar. list($twhere, $tparams) = $table->get_sql_where(); if ($twhere) { $where .= ' AND ' . $twhere; // Initial bar. $params = array_merge($params, $tparams); } if (!empty($countsql)) { $count = $DB->get_record_sql($countsql, $params); $totalinitials = $count->nbresults; if ($twhere) { $countsql .= ' AND ' . $twhere; } $count = $DB->get_record_sql($countsql, $params); $total = $count->nbresults; } $table->pagesize($pagesize, $total); echo \html_writer::start_div('scormattemptcounts'); if ($count->nbresults == $count->nbattempts) { echo get_string('reportcountattempts', 'scorm', $count); } else { if ($count->nbattempts > 0) { echo get_string('reportcountallattempts', 'scorm', $count); } else { echo $count->nbusers . ' ' . get_string('users'); } } echo \html_writer::end_div(); } // Fetch the attempts. if (!$download) { $attempts = $DB->get_records_sql($select . $from . $where . $sort, $params, $table->get_page_start(), $table->get_page_size()); echo \html_writer::start_div('', array('id' => 'scormtablecontainer')); if ($candelete) { // Start form. $strreallydel = addslashes_js(get_string('deleteattemptcheck', 'scorm')); echo \html_writer::start_tag('form', array('id' => 'attemptsform', 'method' => 'post', 'action' => $PAGE->url->out(false), 'onsubmit' => 'return confirm("' . $strreallydel . '");')); echo \html_writer::empty_tag('input', array('type' => 'hidden', 'name' => 'action', 'value' => 'delete')); echo \html_writer::empty_tag('input', array('type' => 'hidden', 'name' => 'sesskey', 'value' => sesskey())); echo \html_writer::start_div('', array('style' => 'display: none;')); echo \html_writer::input_hidden_params($PAGE->url); echo \html_writer::end_div(); echo \html_writer::start_div(); } $table->initialbars($totalinitials > 20); // Build table rows. } else { $attempts = $DB->get_records_sql($select . $from . $where . $sort, $params); } if ($attempts) { foreach ($attempts as $scouser) { $row = array(); if (!empty($scouser->attempt)) { $timetracks = scorm_get_sco_runtime($scorm->id, false, $scouser->userid, $scouser->attempt); } else { $timetracks = ''; } if (in_array('checkbox', $columns)) { if ($candelete && !empty($timetracks->start)) { $row[] = \html_writer::checkbox('attemptid[]', $scouser->userid . ':' . $scouser->attempt, false); } else { if ($candelete) { $row[] = ''; } } } if (in_array('picture', $columns)) { $user = new \stdClass(); $additionalfields = explode(',', \user_picture::fields()); $user = username_load_fields_from_object($user, $scouser, null, $additionalfields); $user->id = $scouser->userid; $row[] = $OUTPUT->user_picture($user, array('courseid' => $course->id)); } if (!$download) { $url = new \moodle_url('/user/view.php', array('id' => $scouser->userid, 'course' => $course->id)); $row[] = \html_writer::link($url, fullname($scouser)); } else { $row[] = fullname($scouser); } foreach ($extrafields as $field) { $row[] = s($scouser->{$field}); } if (empty($timetracks->start)) { $row[] = '-'; $row[] = '-'; $row[] = '-'; $row[] = '-'; } else { if (!$download) { $url = new \moodle_url('/mod/scorm/report/userreport.php', array('id' => $cm->id, 'user' => $scouser->userid, 'attempt' => $scouser->attempt)); $row[] = \html_writer::link($url, $scouser->attempt); } else { $row[] = $scouser->attempt; } if ($download == 'ODS' || $download == 'Excel') { $row[] = userdate($timetracks->start, get_string("strftimedatetime", "langconfig")); } else { $row[] = userdate($timetracks->start); } if ($download == 'ODS' || $download == 'Excel') { $row[] = userdate($timetracks->finish, get_string('strftimedatetime', 'langconfig')); } else { $row[] = userdate($timetracks->finish); } $row[] = scorm_grade_user_attempt($scorm, $scouser->userid, $scouser->attempt); } // Print out all scores of attempt. foreach ($scoes as $sco) { if ($sco->launch != '') { if ($trackdata = scorm_get_tracks($sco->id, $scouser->userid, $scouser->attempt)) { if ($trackdata->status == '') { $trackdata->status = 'notattempted'; } $strstatus = get_string($trackdata->status, 'scorm'); if ($trackdata->score_raw != '') { // If raw score exists, print it. $score = $trackdata->score_raw; // Add max score if it exists. if (isset($trackdata->score_max)) { $score .= '/' . $trackdata->score_max; } } else { // ...else print out status. $score = $strstatus; } if (!$download) { $url = new \moodle_url('/mod/scorm/report/userreporttracks.php', array('id' => $cm->id, 'scoid' => $sco->id, 'user' => $scouser->userid, 'attempt' => $scouser->attempt)); $row[] = \html_writer::img($OUTPUT->pix_url($trackdata->status, 'scorm'), $strstatus, array('title' => $strstatus)) . \html_writer::empty_tag('br') . \html_writer::link($url, $score, array('title' => get_string('details', 'scorm'))); } else { $row[] = $score; } // Iterate over tracks and match objective id against values. $scorm2004 = false; if (scorm_version_check($scorm->version, SCORM_13)) { $scorm2004 = true; $objectiveprefix = "cmi.objectives."; } else { $objectiveprefix = "cmi.objectives_"; } $keywords = array(".id", $objectiveprefix); $objectivestatus = array(); $objectivescore = array(); foreach ($trackdata as $name => $value) { if (strpos($name, $objectiveprefix) === 0 && strrpos($name, '.id') !== false) { $num = trim(str_ireplace($keywords, '', $name)); if (is_numeric($num)) { if ($scorm2004) { $element = $objectiveprefix . $num . '.completion_status'; } else { $element = $objectiveprefix . $num . '.status'; } if (isset($trackdata->{$element})) { $objectivestatus[$value] = $trackdata->{$element}; } else { $objectivestatus[$value] = ''; } if ($displayoptions['objectivescore']) { $element = $objectiveprefix . $num . '.score.raw'; if (isset($trackdata->{$element})) { $objectivescore[$value] = $trackdata->{$element}; } else { $objectivescore[$value] = ''; } } } } } // Interaction data. if (!empty($objectives[$trackdata->scoid])) { foreach ($objectives[$trackdata->scoid] as $name) { if (isset($objectivestatus[$name])) { $row[] = s($objectivestatus[$name]); } else { $row[] = $emptycell; } if ($displayoptions['objectivescore']) { if (isset($objectivescore[$name])) { $row[] = s($objectivescore[$name]); } else { $row[] = $emptycell; } } } } // End of interaction data. } else { // If we don't have track data, we haven't attempted yet. $strstatus = get_string('notattempted', 'scorm'); if (!$download) { $row[] = \html_writer::img($OUTPUT->pix_url('notattempted', 'scorm'), $strstatus, array('title' => $strstatus)) . \html_writer::empty_tag('br') . $strstatus; } else { $row[] = $strstatus; } // Complete the empty cells. for ($i = 0; $i < count($columns) - $nbmaincolumns; $i++) { $row[] = $emptycell; } } } } if (!$download) { $table->add_data($row); } else { if ($download == 'Excel' or $download == 'ODS') { $colnum = 0; foreach ($row as $item) { $myxls->write($rownum, $colnum, $item, $format); $colnum++; } $rownum++; } else { if ($download == 'CSV') { $csvexport->add_data($row); } } } } if (!$download) { $table->finish_output(); if ($candelete) { echo \html_writer::start_tag('table', array('id' => 'commands')); echo \html_writer::start_tag('tr') . \html_writer::start_tag('td'); echo \html_writer::link('javascript:select_all_in(\'DIV\', null, \'scormtablecontainer\');', get_string('selectall', 'scorm')) . ' / '; echo \html_writer::link('javascript:deselect_all_in(\'DIV\', null, \'scormtablecontainer\');', get_string('selectnone', 'scorm')); echo ' '; echo \html_writer::empty_tag('input', array('type' => 'submit', 'value' => get_string('deleteselected', 'scorm'), 'class' => 'btn btn-secondary')); echo \html_writer::end_tag('td') . \html_writer::end_tag('tr') . \html_writer::end_tag('table'); // Close form. echo \html_writer::end_tag('div'); echo \html_writer::end_tag('form'); } echo \html_writer::end_div(); if (!empty($attempts)) { echo \html_writer::start_tag('table', array('class' => 'boxaligncenter')) . \html_writer::start_tag('tr'); echo \html_writer::start_tag('td'); echo $OUTPUT->single_button(new \moodle_url($PAGE->url, array('download' => 'ODS') + $displayoptions), get_string('downloadods'), 'post', ['class' => 'm-t-1']); echo \html_writer::end_tag('td'); echo \html_writer::start_tag('td'); echo $OUTPUT->single_button(new \moodle_url($PAGE->url, array('download' => 'Excel') + $displayoptions), get_string('downloadexcel'), 'post', ['class' => 'm-t-1']); echo \html_writer::end_tag('td'); echo \html_writer::start_tag('td'); echo $OUTPUT->single_button(new \moodle_url($PAGE->url, array('download' => 'CSV') + $displayoptions), get_string('downloadtext'), 'post', ['class' => 'm-t-1']); echo \html_writer::end_tag('td'); echo \html_writer::start_tag('td'); echo \html_writer::end_tag('td'); echo \html_writer::end_tag('tr') . \html_writer::end_tag('table'); } } } else { if ($candelete && !$download) { echo \html_writer::end_div(); echo \html_writer::end_tag('form'); $table->finish_output(); } echo \html_writer::end_div(); } // Show preferences form irrespective of attempts are there to report or not. if (!$download) { $mform->set_data(compact('detailedrep', 'pagesize', 'attemptsmode')); $mform->display(); } if ($download == 'Excel' or $download == 'ODS') { $workbook->close(); exit; } else { if ($download == 'CSV') { $csvexport->download_file(); exit; } } } else { echo $OUTPUT->notification(get_string('noactivity', 'scorm')); } }
/** * pulls out the userids of the users to be display, and sorts them */ public function load_users() { global $CFG, $DB; //limit to users with a gradeable role list($gradebookrolessql, $gradebookrolesparams) = $DB->get_in_or_equal(explode(',', $this->gradebookroles), SQL_PARAMS_NAMED, 'grbr0'); //limit to users with an active enrollment list($enrolledsql, $enrolledparams) = get_enrolled_sql($this->context); //fields we need from the user table $userfields = user_picture::fields('u'); $userfields .= get_extra_user_fields_sql($this->context); $sortjoin = $sort = $params = null; //if the user has clicked one of the sort asc/desc arrows if (is_numeric($this->sortitemid)) { $params = array_merge(array('gitemid' => $this->sortitemid), $gradebookrolesparams, $this->groupwheresql_params, $enrolledparams); $sortjoin = "LEFT JOIN {grade_grades} g ON g.userid = u.id AND g.itemid = {$this->sortitemid}"; $sort = "g.finalgrade {$this->sortorder}"; } else { $sortjoin = ''; switch ($this->sortitemid) { case 'lastname': $sort = "u.lastname {$this->sortorder}, u.firstname {$this->sortorder}"; break; case 'firstname': $sort = "u.firstname {$this->sortorder}, u.lastname {$this->sortorder}"; break; case 'idnumber': default: $sort = "u.idnumber {$this->sortorder}"; break; } $params = array_merge($gradebookrolesparams, $this->groupwheresql_params, $enrolledparams); } $sql = "SELECT {$userfields}\n FROM {user} u\n JOIN ({$enrolledsql}) je ON je.id = u.id\n {$this->groupsql}\n {$sortjoin}\n JOIN (\n SELECT DISTINCT ra.userid\n FROM {role_assignments} ra\n WHERE ra.roleid IN ({$this->gradebookroles})\n AND ra.contextid " . get_related_contexts_string($this->context) . "\n ) rainner ON rainner.userid = u.id\n AND u.deleted = 0\n {$this->groupwheresql}\n ORDER BY {$sort}"; $this->users = $DB->get_records_sql($sql, $params, $this->get_pref('studentsperpage') * $this->page, $this->get_pref('studentsperpage')); if (empty($this->users)) { $this->userselect = ''; $this->users = array(); $this->userselect_params = array(); } else { list($usql, $uparams) = $DB->get_in_or_equal(array_keys($this->users), SQL_PARAMS_NAMED, 'usid0'); $this->userselect = "AND g.userid {$usql}"; $this->userselect_params = $uparams; //add a flag to each user indicating whether their enrolment is active $sql = "SELECT ue.userid\n FROM {user_enrolments} ue\n JOIN {enrol} e ON e.id = ue.enrolid\n WHERE ue.userid {$usql}\n AND ue.status = :uestatus\n AND e.status = :estatus\n AND e.courseid = :courseid\n GROUP BY ue.userid"; $coursecontext = get_course_context($this->context); $params = array_merge($uparams, array('estatus' => ENROL_INSTANCE_ENABLED, 'uestatus' => ENROL_USER_ACTIVE, 'courseid' => $coursecontext->instanceid)); $useractiveenrolments = $DB->get_records_sql($sql, $params); foreach ($this->users as $user) { $this->users[$user->id]->suspendedenrolment = !array_key_exists($user->id, $useractiveenrolments); } } return $this->users; }
public function test_get_extra_user_fields_sql() { global $CFG, $USER, $DB; $olduser = $USER; $USER = $DB->get_record('user', array('id'=>2)); //admin $oldshowuseridentity = $CFG->showuseridentity; $context = context_system::instance(); // No fields $CFG->showuseridentity = ''; $this->assertEquals('', get_extra_user_fields_sql($context)); // One field $CFG->showuseridentity = 'frog'; $this->assertEquals(', frog', get_extra_user_fields_sql($context)); // Two fields with table prefix $CFG->showuseridentity = 'frog,zombie'; $this->assertEquals(', u1.frog, u1.zombie', get_extra_user_fields_sql($context, 'u1')); // Two fields with field prefix $CFG->showuseridentity = 'frog,zombie'; $this->assertEquals(', frog AS u_frog, zombie AS u_zombie', get_extra_user_fields_sql($context, '', 'u_')); // One field excluded $CFG->showuseridentity = 'frog'; $this->assertEquals('', get_extra_user_fields_sql($context, '', '', array('frog'))); // Two fields, one excluded, table+field prefix $CFG->showuseridentity = 'frog,zombie'; $this->assertEquals(', u1.zombie AS u_zombie', get_extra_user_fields_sql($context, 'u1', 'u_', array('frog'))); // As long as this test passes, the value will be set back. This is only // in-memory anyhow $CFG->showuseridentity = $oldshowuseridentity; $USER = $olduser; }
/** * Return array of users whose progress is tracked in this course * * Optionally supply a search's where caluse, group id, sorting, paging * * @param string $where Where clause sql (optional) * @param array $where_params Where clause params (optional) * @param integer $groupid Group ID to restrict to (optional) * @param string $sort Order by clause (optional) * @param integer $limitfrom Result start (optional) * @param integer $limitnum Result max size (optional) * @param context $extracontext If set, includes extra user information fields * as appropriate to display for current user in this context * @return array */ public function get_tracked_users($where = '', $where_params = array(), $groupid = 0, $sort = '', $limitfrom = '', $limitnum = '', context $extracontext = null) { global $DB; $tracked = $this->generate_tracked_user_sql($groupid); $params = $tracked->data; $sql = "\n SELECT\n u.id,\n u.firstname,\n u.lastname,\n u.idnumber\n "; if ($extracontext) { $sql .= get_extra_user_fields_sql($extracontext, 'u', '', array('idnumber')); } $sql .= $tracked->sql; if ($where) { $sql .= " AND {$where}"; $params = array_merge($params, $where_params); } if ($sort) { $sql .= " ORDER BY {$sort}"; } $users = $DB->get_records_sql($sql, $params, $limitfrom, $limitnum); return $users ? $users : array(); // In case it returns false }
/** * get information about particular users with their registrations/queues * * @param int $groupingid optional get only this grouping * @param int $groupid optional get only this group * @param int|array $userids optional get only this user(s) * @param stdClass[] $orderby array how data should be sorted (column as key and ASC/DESC as value) * @return stdClass[] array of objects records from DB with all necessary data */ public function get_user_data($groupingid = 0, $groupid = 0, $userids = 0, $orderby = array()) { global $DB, $PAGE, $OUTPUT; // After which table-fields can we sort? $sortable = array('firstname', 'lastname', 'idnumber', 'email'); $return = new stdClass(); // Indexed by agrpid! $agrps = $this->get_active_groups(false, false, 0, $groupid, $groupingid, false); $agrpids = array_keys($agrps); if (!empty($agrpids)) { list($agrpsql, $agrpparams) = $DB->get_in_or_equal($agrpids); } else { $agrpsql = ''; $agrpparams = array(); echo $OUTPUT->box($OUTPUT->notification(get_string('no_groups_to_display', 'grouptool'), 'notifyproblem'), 'generalbox centered'); } if (!empty($userids)) { if (!is_array($userids)) { $userids = array($userids); } list($usersql, $userparams) = $DB->get_in_or_equal($userids); } else { $usersql = ' LIKE *'; $userparams = array(); } $extrauserfields = get_extra_user_fields_sql($this->context, 'u'); $mainuserfields = user_picture::fields('u', array('idnumber', 'email')); $orderbystring = ""; if (!empty($orderby)) { foreach ($orderby as $field => $direction) { if (in_array($field, $sortable)) { if ($orderbystring != "") { $orderbystring .= ", "; } else { $orderbystring .= " ORDER BY"; } $orderbystring .= " " . $field . " " . (!empty($direction) && $direction == 'ASC' ? 'ASC' : 'DESC'); } else { unset($orderby[$field]); } } } $sql = "SELECT {$mainuserfields} {$extrauserfields} " . "FROM {user} u " . "WHERE u.id " . $usersql . $orderbystring; $params = array_merge($userparams); $data = $DB->get_records_sql($sql, $params); // Add reg and queue data... if (!empty($agrpsql)) { foreach ($data as $idx => &$cur) { $sql = "SELECT agrps.id\n FROM {grouptool_registered} regs\n LEFT JOIN {grouptool_agrps} agrps ON regs.agrpid = agrps.id\n LEFT JOIN {groups} grps ON agrps.groupid = grps.id\n WHERE regs.modified_by >= 0\n AND regs.userid = ?\n AND regs.agrpid " . $agrpsql; $params = array_merge(array($cur->id), $agrpparams); $cur->regs = $DB->get_fieldset_sql($sql, $params); $sql = "SELECT agrps.id\n FROM {grouptool_queued} queued\n LEFT JOIN {grouptool_agrps} agrps ON queued.agrpid = agrps.id\n LEFT JOIN {groups} grps ON agrps.groupid = grps.id\n WHERE queued.userid = ?\n AND queued.agrpid " . $agrpsql; $params = array_merge(array($cur->id), $agrpparams); $cur->queued = $DB->get_fieldset_sql($sql, $params); } } return $data; }
/** * Contruct all the parts of the main database query. * @param object $quiz the quiz settings. * @param string $qmsubselect SQL fragment from {@link quiz_report_qm_filter_select()}. * @param bool $qmfilter whether to show all, or only the final grade attempt. * @param int $attemptsmode which attempts to show. * One of the QUIZ_REPORT_ATTEMPTS_... constants. * @param array $reportstudents list if userids of users to include in the report. * @return array with 4 elements ($fields, $from, $where, $params) that can be used to * build the actual database query. */ protected function base_sql($quiz, $qmsubselect, $qmfilter, $attemptsmode, $reportstudents) { global $DB; $fields = $DB->sql_concat('u.id', "'#'", 'COALESCE(quiza.attempt, 0)') . ' AS uniqueid,'; if ($qmsubselect) { $fields .= "\n(CASE WHEN $qmsubselect THEN 1 ELSE 0 END) AS gradedattempt,"; } $extrafields = get_extra_user_fields_sql($this->context, 'u', '', array('id', 'idnumber', 'firstname', 'lastname', 'picture', 'imagealt', 'institution', 'department', 'email')); $fields .= ' quiza.uniqueid AS usageid, quiza.id AS attempt, u.id AS userid, u.idnumber, u.firstname, u.lastname, u.picture, u.imagealt, u.institution, u.department, u.email' . $extrafields . ', quiza.sumgrades, quiza.timefinish, quiza.timestart, CASE WHEN quiza.timefinish = 0 THEN null WHEN quiza.timefinish > quiza.timestart THEN quiza.timefinish - quiza.timestart ELSE 0 END AS duration'; // To explain that last bit, in MySQL, qa.timestart and qa.timefinish // are unsigned. Since MySQL 5.5.5, when they introduced strict mode, // subtracting a larger unsigned int from a smaller one gave an error. // Therefore, we avoid doing that. timefinish can be non-zero and less // than timestart when you have two load-balanced servers with very // badly synchronised clocks, and a student does a really quick attempt.'; // This part is the same for all cases - join users and quiz_attempts tables $from = "\n{user} u"; $from .= "\nLEFT JOIN {quiz_attempts} quiza ON quiza.userid = u.id AND quiza.quiz = :quizid"; $params = array('quizid' => $quiz->id); if ($qmsubselect && $qmfilter) { $from .= " AND $qmsubselect"; } switch ($attemptsmode) { case QUIZ_REPORT_ATTEMPTS_ALL: // Show all attempts, including students who are no longer in the course $where = 'quiza.id IS NOT NULL AND quiza.preview = 0'; break; case QUIZ_REPORT_ATTEMPTS_STUDENTS_WITH: // Show only students with attempts list($usql, $uparams) = $DB->get_in_or_equal( $reportstudents, SQL_PARAMS_NAMED, 'u'); $params += $uparams; $where = "u.id $usql AND quiza.preview = 0 AND quiza.id IS NOT NULL"; break; case QUIZ_REPORT_ATTEMPTS_STUDENTS_WITH_NO: // Show only students without attempts list($usql, $uparams) = $DB->get_in_or_equal( $reportstudents, SQL_PARAMS_NAMED, 'u'); $params += $uparams; $where = "u.id $usql AND quiza.id IS NULL"; break; case QUIZ_REPORT_ATTEMPTS_ALL_STUDENTS: // Show all students with or without attempts list($usql, $uparams) = $DB->get_in_or_equal( $reportstudents, SQL_PARAMS_NAMED, 'u'); $params += $uparams; $where = "u.id $usql AND (quiza.preview = 0 OR quiza.preview IS NULL)"; break; } return array($fields, $from, $where, $params); }
function local_mail_getsqlrecipients($courseid, $search, $groupid, $roleid, $recipients = false) { global $CFG, $USER, $DB; $context = context_course::instance($courseid); $mailsamerole = has_capability('local/mail:mailsamerole', $context); list($esql, $params) = get_enrolled_sql($context, null, $groupid, true); $joins = array("FROM {user} u"); $wheres = array(); $mainuserfields = user_picture::fields('u', array('username', 'email', 'city', 'country', 'lang', 'timezone', 'maildisplay')); $extrasql = get_extra_user_fields_sql($context, 'u', '', array( 'id', 'firstname', 'lastname')); $select = "SELECT $mainuserfields$extrasql"; $joins[] = "JOIN ($esql) e ON e.id = u.id"; // Performance hacks - we preload user contexts together with accounts. $ccselect = ', ' . context_helper::get_preload_record_columns_sql('ctx'); $ccjoin = "LEFT JOIN {context} ctx ON (ctx.instanceid = u.id AND ctx.contextlevel = :contextlevel)"; $params['contextlevel'] = CONTEXT_USER; $select .= $ccselect; $joins[] = $ccjoin; if (!$mailsamerole) { $userroleids = local_mail_get_user_roleids($USER->id, $context); list($relctxsql, $reldctxparams) = $DB->get_in_or_equal($context->get_parent_context_ids(true), SQL_PARAMS_NAMED, 'relctx'); list($samerolesql, $sameroleparams) = $DB->get_in_or_equal($userroleids, SQL_PARAMS_NAMED, 'samerole' , false); $wheres[] = "u.id IN (SELECT userid FROM {role_assignments} WHERE roleid $samerolesql AND contextid $relctxsql)"; $params = array_merge($params, array('roleid' => $roleid), $sameroleparams, $reldctxparams); } if ($roleid) { // We want to query both the current context and parent contexts. list($relatedctxsql, $relatedctxparams) = $DB->get_in_or_equal($context->get_parent_context_ids(true), SQL_PARAMS_NAMED, 'relatedctx'); $wheres[] = "u.id IN (SELECT userid FROM {role_assignments} WHERE roleid = :roleid AND contextid $relatedctxsql)"; $params = array_merge($params, array('roleid' => $roleid), $relatedctxparams); } $from = implode("\n", $joins); if (!empty($search)) { $fullname = $DB->sql_fullname('u.firstname', 'u.lastname'); $wheres[] = "(". $DB->sql_like($fullname, ':search1', false, false) .") "; $params['search1'] = "%$search%"; } $from = implode("\n", $joins); $wheres[] = 'u.id <> :guestid AND u.deleted = 0 AND u.confirmed = 1 AND u.id <> :userid'; $params['userid'] = $USER->id; $params['guestid'] = $CFG->siteguest; if ($recipients) { $wheres[] = 'u.id IN ('.preg_replace('/^,|,$/', '', $recipients).')'; } $where = "WHERE " . implode(" AND ", $wheres); $sort = 'ORDER BY u.lastname ASC, u.firstname ASC'; return array($select, $from, $where, $sort, $params); }
function get_users_listing_subadmin($sort = 'lastaccess', $dir = 'ASC', $page = 0, $recordsperpage = 0, $search = '', $firstinitial = '', $lastinitial = '', $extraselect = '', array $extraparams = null, $extracontext = null, $join = '') { global $DB; $fullname = $DB->sql_fullname(); $select = "u.deleted <> 1"; $params = array(); if (!empty($search)) { $search = trim($search); $select .= " AND (" . $DB->sql_like($fullname, ':search1', false, false) . " OR " . $DB->sql_like('u.email', ':search2', false, false) . " OR u.username = :search3)"; $params['search1'] = "%{$search}%"; $params['search2'] = "%{$search}%"; $params['search3'] = "{$search}"; } if ($firstinitial) { $select .= " AND " . $DB->sql_like('u.firstname', ':fni', false, false); $params['fni'] = "{$firstinitial}%"; } if ($lastinitial) { $select .= " AND " . $DB->sql_like('u.lastname', ':lni', false, false); $params['lni'] = "{$lastinitial}%"; } if ($extraselect) { $select .= " AND {$extraselect}"; $params = $params + (array) $extraparams; } if ($sort) { $sort = " ORDER BY {$sort} {$dir}"; } // If a context is specified, get extra user fields that the current user // is supposed to see. $extrafields = ''; if ($extracontext) { $extrafields = get_extra_user_fields_sql($extracontext, '', '', array('id', 'username', 'email', 'firstname', 'lastname', 'city', 'country', 'lastaccess', 'confirmed', 'mnethostid')); } // warning: will return UNCONFIRMED USERS return $DB->get_records_sql("SELECT u.*\n FROM {user} u\n\t\t\t\t\t\t\t\t {$join}\n WHERE {$select}\n {$sort}", $params, $page, $recordsperpage); }
public function test_get_extra_user_fields_sql() { global $CFG, $USER, $DB; $this->resetAfterTest(); $this->setAdminUser(); $context = context_system::instance(); // No fields. $CFG->showuseridentity = ''; $this->assertSame('', get_extra_user_fields_sql($context)); // One field. $CFG->showuseridentity = 'frog'; $this->assertSame(', frog', get_extra_user_fields_sql($context)); // Two fields with table prefix. $CFG->showuseridentity = 'frog,zombie'; $this->assertSame(', u1.frog, u1.zombie', get_extra_user_fields_sql($context, 'u1')); // Two fields with field prefix. $CFG->showuseridentity = 'frog,zombie'; $this->assertSame(', frog AS u_frog, zombie AS u_zombie', get_extra_user_fields_sql($context, '', 'u_')); // One field excluded. $CFG->showuseridentity = 'frog'; $this->assertSame('', get_extra_user_fields_sql($context, '', '', array('frog'))); // Two fields, one excluded, table+field prefix. $CFG->showuseridentity = 'frog,zombie'; $this->assertEquals(', u1.zombie AS u_zombie', get_extra_user_fields_sql($context, 'u1', 'u_', array('frog'))); }
/** * Contruct all the parts of the main database query. * @param array $reportstudents list if userids of users to include in the report. * @return array with 4 elements ($fields, $from, $where, $params) that can be used to * build the actual database query. */ public function base_sql($reportstudents) { global $DB; $fields = $DB->sql_concat('u.id', "'#'", 'COALESCE(quiza.attempt, 0)') . ' AS uniqueid,'; if ($this->qmsubselect) { $fields .= "\n(CASE WHEN $this->qmsubselect THEN 1 ELSE 0 END) AS gradedattempt,"; } $extrafields = get_extra_user_fields_sql($this->context, 'u', '', array('id', 'idnumber', 'firstname', 'lastname', 'picture', 'imagealt', 'institution', 'department', 'email')); $fields .= ' quiza.uniqueid AS usageid, quiza.id AS attempt, u.id AS userid, u.idnumber, u.firstname, u.lastname, u.picture, u.imagealt, u.institution, u.department, u.email' . $extrafields . ', quiza.state, quiza.sumgrades, quiza.timefinish, quiza.timestart, CASE WHEN quiza.timefinish = 0 THEN null WHEN quiza.timefinish > quiza.timestart THEN quiza.timefinish - quiza.timestart ELSE 0 END AS duration'; // To explain that last bit, timefinish can be non-zero and less // than timestart when you have two load-balanced servers with very // badly synchronised clocks, and a student does a really quick attempt. // This part is the same for all cases. Join the users and quiz_attempts tables. $from = "\n{user} u"; $from .= "\nLEFT JOIN {quiz_attempts} quiza ON quiza.userid = u.id AND quiza.quiz = :quizid"; $params = array('quizid' => $this->quiz->id); if ($this->qmsubselect && $this->options->onlygraded) { $from .= " AND $this->qmsubselect"; } switch ($this->options->attempts) { case quiz_attempts_report::ALL_WITH: // Show all attempts, including students who are no longer in the course. $where = 'quiza.id IS NOT NULL AND quiza.preview = 0'; break; case quiz_attempts_report::ENROLLED_WITH: // Show only students with attempts. list($usql, $uparams) = $DB->get_in_or_equal( $reportstudents, SQL_PARAMS_NAMED, 'u'); $params += $uparams; $where = "u.id $usql AND quiza.preview = 0 AND quiza.id IS NOT NULL"; break; case quiz_attempts_report::ENROLLED_WITHOUT: // Show only students without attempts. list($usql, $uparams) = $DB->get_in_or_equal( $reportstudents, SQL_PARAMS_NAMED, 'u'); $params += $uparams; $where = "u.id $usql AND quiza.id IS NULL"; break; case quiz_attempts_report::ENROLLED_ALL: // Show all students with or without attempts. list($usql, $uparams) = $DB->get_in_or_equal( $reportstudents, SQL_PARAMS_NAMED, 'u'); $params += $uparams; $where = "u.id $usql AND (quiza.preview = 0 OR quiza.preview IS NULL)"; break; } if ($this->options->states) { list($statesql, $stateparams) = $DB->get_in_or_equal($this->options->states, SQL_PARAMS_NAMED, 'state'); $params += $stateparams; $where .= " AND (quiza.state $statesql OR quiza.state IS NULL)"; } return array($fields, $from, $where, $params); }
} $table->no_sorting('roles'); $table->no_sorting('groups'); $table->no_sorting('groupings'); $table->no_sorting('select'); $table->set_attribute('cellspacing', '0'); $table->set_attribute('id', 'participants'); $table->set_attribute('class', 'generaltable generalbox'); $table->set_control_variables(array(TABLE_VAR_SORT => 'ssort', TABLE_VAR_HIDE => 'shide', TABLE_VAR_SHOW => 'sshow', TABLE_VAR_IFIRST => 'sifirst', TABLE_VAR_ILAST => 'silast', TABLE_VAR_PAGE => 'spage')); $table->setup(); list($esql, $params) = get_enrolled_sql($context, null, $currentgroup, true); $joins = array("FROM {user} u"); $wheres = array(); $userfields = array('username', 'email', 'city', 'country', 'lang', 'timezone', 'maildisplay'); $mainuserfields = user_picture::fields('u', $userfields); $extrasql = get_extra_user_fields_sql($context, 'u', '', $userfields); if ($isfrontpage) { $select = "SELECT {$mainuserfields}, u.lastaccess{$extrasql}"; $joins[] = "JOIN ({$esql}) e ON e.id = u.id"; // Everybody on the frontpage usually. if ($accesssince) { $wheres[] = get_user_lastaccess_sql($accesssince); } } else { $select = "SELECT {$mainuserfields}, COALESCE(ul.timeaccess, 0) AS lastaccess{$extrasql}"; $joins[] = "JOIN ({$esql}) e ON e.id = u.id"; // Course enrolled users only. $joins[] = "LEFT JOIN {user_lastaccess} ul ON (ul.userid = u.id AND ul.courseid = :courseid)"; // Not everybody accessed course yet. $params['courseid'] = $course->id; if ($accesssince) {
/** * @method get_users_listing * @todo to get user list of costcenter based on condition * @param string $sort fieldname * @param string $dir specify the order to sort * @param int $page page number * @param int $recordsperpage records perpage * @param string $extraselect extra condition to select user * @param array $extraparams * @return array of objects , list of users */ function get_users_listing($sort = 'lastaccess', $dir = 'ASC', $page = 0, $recordsperpage = 0, $extraselect = '', array $extraparams = null, $extracontext = null) { global $DB, $CFG,$USER; $extraselect; $select = "u.deleted <> 1 AND u.id <> :guestid"; //$select = "deleted=0"; $params = array('guestid' => $CFG->siteguest); if ($extraselect) { $select .= " AND $extraselect"; $params = $params + (array) $extraparams; } // If a context is specified, get extra user fields that the current user // is supposed to see. $extrafields = ''; if ($extracontext) { $extrafields = get_extra_user_fields_sql($extracontext, '', '', array('id', 'username', 'email', 'firstname', 'lastname', 'city', 'country', 'lastaccess', 'confirmed', 'mnethostid')); } /* * ###Bugreport#183-Filters * @author Naveen Kumar<*****@*****.**> * (Resolved) Added $select parameters for conditions */ // warning: will return UNCONFIRMED USERS // print_object($params); if (!is_siteadmin()) { //$costcenterid = $DB->get_field('local_costcenter_permissions','costcenterid',array('userid'=>$USER->id)); /* code for training manager capable of handling one or more departments - code added by sreenivas*/ $cost_list = $DB->get_fieldset_sql('select costcenterid from {local_userdata} where userid='.$USER->id.''); /*code added by sreenivas*/ $dsin = implode(',',$cost_list); /*code added by sreenivas*/ //if(!empty($users_list)) $join = " RIGHT JOIN {local_userdata} as ud ON u.id=ud.userid AND costcenterid in ($dsin)"; /*code modified by sreenivas*/ }else{ $join =" RIGHT JOIN {local_userdata} as ud ON u.id=ud.userid"; } return $DB->get_records_sql("SELECT u.* FROM {user} as u $join WHERE $select GROUP BY id ORDER BY $sort $dir LIMIT $page, $recordsperpage", $params); //$userids = $DB->get_fieldset_sql("SELECT id // FROM {user} // WHERE $select", $params); // //if ($userids && $costcenterlist) { // $useridin = implode(',', array_keys($userids)); // return $DB->get_records_sql("select user.* from ( // SELECT u.id, u.username, u.email, u.firstname, u.lastname, u.city, u.country, // u.lastaccess, u.confirmed, u.mnethostid, u.suspended FROM {user} u // JOIN {local_userdata} ud ON ud.userid = u.id // WHERE u.id in ($useridin) AND ud.costcenterid IN ($costcenteridin)) user GROUP BY user.id ORDER BY user.$sort $dir LIMIT $page, $recordsperpage "); //} }