/**
* Get a single value from a table row where a particular select clause is true.
*
* @uses $CFG
* @param string $table the table to query.
* @param string $return the field to return the value of.
* @param string $select A fragment of SQL to be used in a where clause in the SQL call.
* @return mixed|false Returns the value return from the SQL statment or false if an error occured.
*/
function get_fieldset_select($table, $return, $select)
{
global $CFG;
if ($select) {
$select = ' WHERE ' . $select;
}
return get_fieldset_sql('SELECT ' . $return . ' FROM ' . $CFG->prefix . $table . $select);
}
/**
* Creates known user filter if present
* @param string $fieldname
* @param boolean $advanced
* @return object filter
*/
function get_field($fieldname, $advanced)
{
global $USER, $CFG, $SITE;
switch ($fieldname) {
case 'username':
return new user_filter_text('username', get_string('username'), $advanced, 'username');
case 'realname':
return new user_filter_text('realname', get_string('fullname'), $advanced, sql_fullname());
case 'lastname':
return new user_filter_text('lastname', get_string('lastname'), $advanced, 'lastname');
case 'firstname':
return new user_filter_text('firstname', get_string('firstname'), $advanced, 'firstname');
case 'email':
return new user_filter_text('email', get_string('email'), $advanced, 'email');
case 'city':
return new user_filter_text('city', get_string('city'), $advanced, 'city');
case 'country':
return new user_filter_select('country', get_string('country'), $advanced, 'country', get_list_of_countries(), $USER->country);
case 'confirmed':
return new user_filter_yesno('confirmed', get_string('confirmed', 'admin'), $advanced, 'confirmed');
case 'profile':
return new user_filter_profilefield('profile', get_string('profile'), $advanced);
case 'courserole':
return new user_filter_courserole('courserole', get_string('courserole', 'filters'), $advanced);
case 'systemrole':
return new user_filter_globalrole('systemrole', get_string('globalrole', 'role'), $advanced);
case 'firstaccess':
return new user_filter_date('firstaccess', get_string('firstaccess', 'filters'), $advanced, 'firstaccess');
case 'lastaccess':
return new user_filter_date('lastaccess', get_string('lastaccess'), $advanced, 'lastaccess');
case 'lastlogin':
return new user_filter_date('lastlogin', get_string('lastlogin'), $advanced, 'lastlogin');
case 'timemodified':
return new user_filter_date('timemodified', get_string('lastmodified'), $advanced, 'timemodified');
case 'auth':
$plugins = get_list_of_plugins('auth');
$choices = array();
foreach ($plugins as $auth) {
$choices[$auth] = auth_get_plugin_title($auth);
}
return new user_filter_simpleselect('auth', get_string('authentication'), $advanced, 'auth', $choices);
case 'mnethostid':
// include all hosts even those deleted or otherwise problematic
if (!($hosts = get_records('mnet_host', '', '', 'id', 'id, wwwroot, name'))) {
$hosts = array();
}
$choices = array();
foreach ($hosts as $host) {
if ($host->id == $CFG->mnet_localhost_id) {
$choices[$host->id] = format_string($SITE->fullname) . ' (' . get_string('local') . ')';
} else {
if (empty($host->wwwroot)) {
// All hosts
continue;
} else {
$choices[$host->id] = $host->name . ' (' . $host->wwwroot . ')';
}
}
}
if ($usedhosts = get_fieldset_sql("SELECT DISTINCT mnethostid FROM {$CFG->prefix}user WHERE deleted=0")) {
foreach ($usedhosts as $hostid) {
if (empty($hosts[$hostid])) {
$choices[$hostid] = 'id: ' . $hostid . ' (' . get_string('error') . ')';
}
}
}
if (count($choices) < 2) {
return null;
// filter not needed
}
return new user_filter_simpleselect('mnethostid', 'mnethostid', $advanced, 'mnethostid', $choices);
default:
return null;
}
}
/**
* Will re-sort a $users results array (from get_users_by_capability(), usually)
* based on a sorting policy. This is to support the odd practice of
* sorting teachers by 'authority', where authority was "lowest id of the role
* assignment".
*
* Will execute 1 database query. Only suitable for small numbers of users, as it
* uses an u.id IN() clause.
*
* Notes about the sorting criteria.
*
* As a default, we cannot rely on role.sortorder because then
* admins/coursecreators will always win. That is why the sane
* rule "is locality matters most", with sortorder as 2nd
* consideration.
*
* If you want role.sortorder, use the 'sortorder' policy, and
* name explicitly what roles you want to cover. It's probably
* a good idea to see what roles have the capabilities you want
* (array_diff() them against roiles that have 'can-do-anything'
* to weed out admin-ish roles. Or fetch a list of roles from
* variables like $CFG->coursemanagers .
*
* @param array users Users' array, keyed on userid
* @param object context
* @param array roles - ids of the roles to include, optional
* @param string policy - defaults to locality, more about
* @return array - sorted copy of the array
*/
function sort_by_roleassignment_authority($users, $context, $roles = array(), $sortpolicy = 'locality')
{
global $CFG;
$userswhere = ' ra.userid IN (' . implode(',', array_keys($users)) . ')';
$contextwhere = ' ra.contextid IN (' . str_replace('/', ',', substr($context->path, 1)) . ')';
if (empty($roles)) {
$roleswhere = '';
} else {
$roleswhere = ' AND ra.roleid IN (' . implode(',', $roles) . ')';
}
$sql = "SELECT ra.userid\n FROM {$CFG->prefix}role_assignments ra\n JOIN {$CFG->prefix}role r\n ON ra.roleid=r.id\n JOIN {$CFG->prefix}context ctx\n ON ra.contextid=ctx.id\n WHERE\n {$userswhere}\n AND {$contextwhere}\n {$roleswhere}\n ";
// Default 'locality' policy -- read PHPDoc notes
// about sort policies...
$orderby = 'ORDER BY
ctx.depth DESC, /* locality wins */
r.sortorder ASC, /* rolesorting 2nd criteria */
ra.id /* role assignment order tie-breaker */';
if ($sortpolicy === 'sortorder') {
$orderby = 'ORDER BY
r.sortorder ASC, /* rolesorting 2nd criteria */
ra.id /* role assignment order tie-breaker */';
}
$sortedids = get_fieldset_sql($sql . $orderby);
$sortedusers = array();
$seen = array();
foreach ($sortedids as $id) {
// Avoid duplicates
if (isset($seen[$id])) {
continue;
}
$seen[$id] = true;
// assign
$sortedusers[$id] = $users[$id];
}
return $sortedusers;
}
function iterate_purge($starttime)
{
global $SESSION, $CFG;
$userid = current($SESSION->purge_progress);
$incourses = implode(',', $SESSION->bulk_courses);
// delete all quiz activity
$quizzessql = "SELECT DISTINCT q.* FROM {$CFG->prefix}quiz q INNER JOIN {$CFG->prefix}quiz_attempts a\n ON a.quiz=q.id AND a.userid={$userid} AND q.course IN ({$incourses})";
if ($quizzes = get_records_sql($quizzessql)) {
foreach ($quizzes as $quiz) {
$attemptssql = "SELECT a.* FROM {$CFG->prefix}quiz_attempts a\n \t\t\t\tWHERE a.quiz={$quiz->id} AND a.userid={$userid}";
$attempts = get_records_sql($attemptssql);
foreach ($attempts as $attempt) {
quiz_delete_attempt($attempt, $quiz);
}
}
}
if (is_timeout_close($starttime)) {
return false;
}
// delete all lesson activity
$lessons = get_fieldset_select('lesson', 'id', "course IN ({$incourses})");
if (!empty($lessons)) {
$lessons = implode(',', $lessons);
/// Clean up the timer table
delete_records_select('lesson_timer', "userid={$userid} AND lessonid IN ({$lessons})");
/// Remove the grades from the grades and high_scores tables
delete_records_select('lesson_grades', "userid={$userid} AND lessonid IN ({$lessons})");
delete_records_select('lesson_high_scores', "userid={$userid} AND lessonid IN ({$lessons})");
/// Remove attempts
delete_records_select('lesson_attempts', "userid={$userid} AND lessonid IN ({$lessons})");
/// Remove seen branches
delete_records_select('lesson_branch', "userid={$userid} AND lessonid IN ({$lessons})");
}
if (is_timeout_close($starttime)) {
return false;
}
// delete all assignment submissions
$assignmentlist = array();
// delete submission files
$assignmentssql = "SELECT DISTINCT a.id, a.course FROM {$CFG->prefix}assignment a INNER JOIN {$CFG->prefix}assignment_submissions s\n ON s.assignment=a.id AND s.userid={$userid} AND a.course IN ({$incourses})";
if ($assignments = get_records_sql($assignmentssql)) {
foreach ($assignments as $assignment) {
fulldelete($CFG->dataroot . '/' . $assignment->course . '/moddata/assignment/' . $assignment->id . '/' . $userid);
$assignmentlist[] = $assignment->id;
}
}
// delete submission records
if (!empty($assignmentlist)) {
$assignmentlist = implode(',', $assignmentlist);
delete_records_select('assignment_submissions', "userid={$userid} AND assignment IN ({$assignmentlist})");
}
if (is_timeout_close($starttime)) {
return false;
}
// finally, delete all grade records to clean up database
$sql = "SELECT g.id \n FROM {$CFG->prefix}grade_grades g INNER JOIN {$CFG->prefix}grade_items i\n ON g.itemid = i.id AND i.courseid IN ({$incourses}) AND g.userid={$userid}";
$grades = get_fieldset_sql($sql);
if (!empty($grades)) {
$grades = implode(',', $grades);
delete_records_select('grade_grades', "id IN ({$grades})");
}
// unenrol selected users from all courses
foreach ($SESSION->bulk_courses as $course) {
$context = get_context_instance(CONTEXT_COURSE, $course);
role_unassign(0, $userid, 0, $context->id);
}
array_shift($SESSION->purge_progress);
if (is_timeout_close($starttime)) {
return false;
}
return true;
}
/**
* Get post tags in a CSV format
*
* @param int $postid
* @return string
* @uses $CFG;
*/
function oublog_get_tags_csv($postid)
{
global $CFG;
$sql = "SELECT t.tag\n FROM {$CFG->prefix}oublog_taginstances ti\n INNER JOIN {$CFG->prefix}oublog_tags t ON ti.tagid = t.id\n WHERE ti.postid = {$postid} ";
if ($tags = get_fieldset_sql($sql)) {
return implode(', ', $tags);
} else {
return '';
}
}
$sqlmax = "SELECT {$count} FROM {$tables} {$where} {$groupselect} {$approveselect}";
// number of all recoirds user may see
/// Work out the paging numbers and counts
$totalcount = count_records_sql($sqlcount);
if (empty($searchselect) && empty($advsearchselect)) {
$maxcount = $totalcount;
} else {
$maxcount = count_records_sql($sqlmax);
}
if ($record) {
// We need to just show one, so where is it in context?
$nowperpage = 1;
$mode = 'single';
$page = 0;
// TODO: Improve this because we are executing $sqlselect twice (here and some lines below)!
if ($allrecordids = get_fieldset_sql($sqlselect)) {
$page = (int) array_search($record->id, $allrecordids);
unset($allrecordids);
}
} else {
if ($mode == 'single') {
// We rely on ambient $page settings
$nowperpage = 1;
} else {
$nowperpage = $perpage;
}
}
/// Get the actual records
if (!($records = get_records_sql($sqlselect, $page * $nowperpage, $nowperpage))) {
// Nothing to show!
if ($record) {
