/**
* Do a test of the user search SQL with database users.
*/
public function test_users_search_sql()
{
global $DB;
$this->resetAfterTest();
// Set up test users.
$user1 = array('username' => 'usernametest1', 'idnumber' => 'idnumbertest1', 'firstname' => 'First Name User Test 1', 'lastname' => 'Last Name User Test 1', 'email' => '*****@*****.**', 'address' => '2 Test Street Perth 6000 WA', 'phone1' => '01010101010', 'phone2' => '02020203', 'icq' => 'testuser1', 'skype' => 'testuser1', 'yahoo' => 'testuser1', 'aim' => 'testuser1', 'msn' => 'testuser1', 'department' => 'Department of user 1', 'institution' => 'Institution of user 1', 'description' => 'This is a description for user 1', 'descriptionformat' => FORMAT_MOODLE, 'city' => 'Perth', 'url' => 'http://moodle.org', 'country' => 'au');
$user1 = self::getDataGenerator()->create_user($user1);
$user2 = array('username' => 'usernametest2', 'idnumber' => 'idnumbertest2', 'firstname' => 'First Name User Test 2', 'lastname' => 'Last Name User Test 2', 'email' => '*****@*****.**', 'address' => '222 Test Street Perth 6000 WA', 'phone1' => '01010101010', 'phone2' => '02020203', 'icq' => 'testuser1', 'skype' => 'testuser1', 'yahoo' => 'testuser1', 'aim' => 'testuser1', 'msn' => 'testuser1', 'department' => 'Department of user 2', 'institution' => 'Institution of user 2', 'description' => 'This is a description for user 2', 'descriptionformat' => FORMAT_MOODLE, 'city' => 'Perth', 'url' => 'http://moodle.org', 'country' => 'au');
$user2 = self::getDataGenerator()->create_user($user2);
// Search by name (anywhere in text).
list($sql, $params) = users_search_sql('User Test 2', '');
$results = $DB->get_records_sql("SELECT id FROM {user} WHERE {$sql} ORDER BY username", $params);
$this->assertFalse(array_key_exists($user1->id, $results));
$this->assertTrue(array_key_exists($user2->id, $results));
// Search by (most of) full name.
list($sql, $params) = users_search_sql('First Name User Test 2 Last Name User', '');
$results = $DB->get_records_sql("SELECT id FROM {user} WHERE {$sql} ORDER BY username", $params);
$this->assertFalse(array_key_exists($user1->id, $results));
$this->assertTrue(array_key_exists($user2->id, $results));
// Search by name (start of text) valid or not.
list($sql, $params) = users_search_sql('User Test 2', '', false);
$results = $DB->get_records_sql("SELECT id FROM {user} WHERE {$sql} ORDER BY username", $params);
$this->assertEquals(0, count($results));
list($sql, $params) = users_search_sql('First Name User Test 2', '', false);
$results = $DB->get_records_sql("SELECT id FROM {user} WHERE {$sql} ORDER BY username", $params);
$this->assertFalse(array_key_exists($user1->id, $results));
$this->assertTrue(array_key_exists($user2->id, $results));
// Search by extra fields included or not (address).
list($sql, $params) = users_search_sql('Test Street', '', true);
$results = $DB->get_records_sql("SELECT id FROM {user} WHERE {$sql} ORDER BY username", $params);
$this->assertCount(0, $results);
list($sql, $params) = users_search_sql('Test Street', '', true, array('address'));
$results = $DB->get_records_sql("SELECT id FROM {user} WHERE {$sql} ORDER BY username", $params);
$this->assertCount(2, $results);
// Exclude user.
list($sql, $params) = users_search_sql('User Test', '', true, array(), array($user1->id));
$results = $DB->get_records_sql("SELECT id FROM {user} WHERE {$sql} ORDER BY username", $params);
$this->assertFalse(array_key_exists($user1->id, $results));
$this->assertTrue(array_key_exists($user2->id, $results));
// Include only user.
list($sql, $params) = users_search_sql('User Test', '', true, array(), array(), array($user1->id));
$results = $DB->get_records_sql("SELECT id FROM {user} WHERE {$sql} ORDER BY username", $params);
$this->assertTrue(array_key_exists($user1->id, $results));
$this->assertFalse(array_key_exists($user2->id, $results));
// Join with another table and use different prefix.
set_user_preference('amphibian', 'frog', $user1);
set_user_preference('amphibian', 'salamander', $user2);
list($sql, $params) = users_search_sql('User Test 1', 'qq');
$results = $DB->get_records_sql("\n SELECT up.id, up.value\n FROM {user} qq\n JOIN {user_preferences} up ON up.userid = qq.id\n WHERE up.name = :prefname\n AND {$sql}", array_merge(array('prefname' => 'amphibian'), $params));
$this->assertEquals(1, count($results));
foreach ($results as $record) {
$this->assertSame('frog', $record->value);
}
}
/**
* Get sql and params to use to get list of users.
*
* @param \context $context Context of the page where the results would be shown.
* @param string $search the text to search for (empty string = find all).
* @param bool $count setting this to true, returns an sql to get count only instead of the complete data records.
*
* @return array sql and params list
*/
protected static function get_users_sql_and_params($context, $search = '', $count = false)
{
// Fields we need from the user table.
$extrafields = get_extra_user_fields($context);
$params = array();
if (!empty($search)) {
list($filtersql, $params) = users_search_sql($search, 'u', true, $extrafields);
$filtersql .= ' AND ';
} else {
$filtersql = '';
}
$ufields = \user_picture::fields('u', $extrafields) . ',u.username';
if ($count) {
$select = "SELECT COUNT(DISTINCT u.id) ";
$orderby = "";
} else {
$select = "SELECT DISTINCT {$ufields} ";
$orderby = " ORDER BY u.lastname ASC, u.firstname ASC";
}
$sql = "{$select}\n FROM {user} u\n JOIN {grade_grades_history} ggh ON u.id = ggh.userid\n JOIN {grade_items} gi ON gi.id = ggh.itemid\n WHERE {$filtersql} gi.courseid = :courseid";
$sql .= $orderby;
$params['courseid'] = $context->instanceid;
return array($sql, $params);
}
/**
* Obtains WHERE clause to filter results by defined search and role filter
* (instance filter is handled separately in JOIN clause, see
* get_instance_sql).
*
* @return array Two-element array with SQL and params for WHERE clause
*/
protected function get_filter_sql()
{
global $DB;
// Search condition.
$extrafields = get_extra_user_fields($this->get_context());
list($sql, $params) = users_search_sql($this->searchfilter, 'u', true, $extrafields);
// Role condition.
if ($this->rolefilter) {
// Get context SQL.
$contextids = $this->context->get_parent_context_ids();
$contextids[] = $this->context->id;
list($contextsql, $contextparams) = $DB->get_in_or_equal($contextids, SQL_PARAMS_NAMED);
$params += $contextparams;
// Role check condition.
$sql .= " AND (SELECT COUNT(1) FROM {role_assignments} ra WHERE ra.userid = u.id " . "AND ra.roleid = :roleid AND ra.contextid {$contextsql}) > 0";
$params['roleid'] = $this->rolefilter;
}
// Group condition.
if ($this->groupfilter) {
$sql .= " AND gm.groupid = :groupid";
$params['groupid'] = $this->groupfilter;
}
// Status condition.
if ($this->statusfilter === ENROL_USER_ACTIVE) {
$sql .= " AND ue.status = :active AND e.status = :enabled AND ue.timestart < :now1\n AND (ue.timeend = 0 OR ue.timeend > :now2)";
$now = round(time(), -2);
// rounding helps caching in DB
$params += array('enabled' => ENROL_INSTANCE_ENABLED, 'active' => ENROL_USER_ACTIVE, 'now1' => $now, 'now2' => $now);
} else {
if ($this->statusfilter === ENROL_USER_SUSPENDED) {
$sql .= " AND (ue.status = :inactive OR e.status = :disabled OR ue.timestart > :now1\n OR (ue.timeend <> 0 AND ue.timeend < :now2))";
$now = round(time(), -2);
// rounding helps caching in DB
$params += array('disabled' => ENROL_INSTANCE_DISABLED, 'inactive' => ENROL_USER_SUSPENDED, 'now1' => $now, 'now2' => $now);
}
}
return array($sql, $params);
}
/**
* @param string $search the text to search for.
* @param string $u the table alias for the user table in the query being
* built. May be ''.
* @return array an array with two elements, a fragment of SQL to go in the
* where clause the query, and an array containing any required parameters.
* this uses ? style placeholders.
*/
protected function search_sql($search, $u)
{
return users_search_sql($search, $u, $this->searchanywhere, $this->extrafields, $this->exclude, $this->validatinguserids);
}
/**
* Obtains WHERE clause to filter results by defined search and role filter
* (instance filter is handled separately in JOIN clause, see
* get_instance_sql).
*
* @return array Two-element array with SQL and params for WHERE clause
*/
protected function get_filter_sql()
{
global $DB;
// Search condition.
$extrafields = get_extra_user_fields($this->get_context());
list($sql, $params) = users_search_sql($this->searchfilter, 'u', true, $extrafields);
// Role condition.
if ($this->rolefilter) {
// Get context SQL.
$contextids = $this->context->get_parent_context_ids();
$contextids[] = $this->context->id;
list($contextsql, $contextparams) = $DB->get_in_or_equal($contextids, SQL_PARAMS_NAMED);
$params += $contextparams;
// Role check condition.
$sql .= " AND (SELECT COUNT(1) FROM {role_assignments} ra WHERE ra.userid = u.id " . "AND ra.roleid = :roleid AND ra.contextid {$contextsql}) > 0";
$params['roleid'] = $this->rolefilter;
}
return array($sql, $params);
}
/**
* Search users.
*
* @param string $query
* @param string $capability
* @param int $limitfrom
* @param int $limitnum
* @return array
*/
public static function search_users($query, $capability = '', $limitfrom = 0, $limitnum = 100)
{
global $DB, $CFG, $PAGE, $USER;
$params = self::validate_parameters(self::search_users_parameters(), array('query' => $query, 'capability' => $capability, 'limitfrom' => $limitfrom, 'limitnum' => $limitnum));
$query = $params['query'];
$cap = $params['capability'];
$limitfrom = $params['limitfrom'];
$limitnum = $params['limitnum'];
$context = context_system::instance();
self::validate_context($context);
$output = $PAGE->get_renderer('tool_lp');
list($filtercapsql, $filtercapparams) = api::filter_users_with_capability_on_user_context_sql($cap, $USER->id, SQL_PARAMS_NAMED);
$extrasearchfields = array();
if (!empty($CFG->showuseridentity) && has_capability('moodle/site:viewuseridentity', $context)) {
$extrasearchfields = explode(',', $CFG->showuseridentity);
}
$fields = \user_picture::fields('u', $extrasearchfields);
list($wheresql, $whereparams) = users_search_sql($query, 'u', true, $extrasearchfields);
list($sortsql, $sortparams) = users_order_by_sql('u', $query, $context);
$countsql = "SELECT COUNT('x') FROM {user} u WHERE {$wheresql} AND u.id {$filtercapsql}";
$countparams = $whereparams + $filtercapparams;
$sql = "SELECT {$fields} FROM {user} u WHERE {$wheresql} AND u.id {$filtercapsql} ORDER BY {$sortsql}";
$params = $whereparams + $filtercapparams + $sortparams;
$count = $DB->count_records_sql($countsql, $countparams);
$result = $DB->get_recordset_sql($sql, $params, $limitfrom, $limitnum);
$users = array();
foreach ($result as $key => $user) {
// Make sure all required fields are set.
foreach (user_summary_exporter::define_properties() as $propertykey => $definition) {
if (empty($user->{$propertykey}) || !in_array($propertykey, $extrasearchfields)) {
if ($propertykey != 'id') {
$user->{$propertykey} = '';
}
}
}
$exporter = new user_summary_exporter($user);
$newuser = $exporter->export($output);
$users[$key] = $newuser;
}
$result->close();
return array('users' => $users, 'count' => $count);
}
