/** * 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) {