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