/**
* 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 ");
//}
}
