/** * Implement user searching with SQL * * NOTE: user with ID zero should never be returned * * @param string The query string * @param integer How many results to return * @param integer What result to start at (0 == first result) * @param data Filters the users searched by * can contain: * 'group' => integer, // only users in this group * 'owner' => boolean // include the group ownwer (only if group is set) * 'exclude'=> int // excludes a user * @return array A data structure containing results looking like ... * $results = array( * count => integer, // total number of results * limit => integer, // how many results are returned * offset => integer, // starting from which result * data => array( // the result records * array( * id => integer, * username => string, * institution => string, * firstname => string, * lastname => string, * preferredname => string, * email => string, * ), * array( * id => integer, * username => string, * institution => string, * firstname => string, * lastname => string, * preferredname => string, * email => string, * ), * array(...), * ), * ); */ public static function search_user($query_string, $limit, $offset = 0, $data = array()) { safe_require('artefact', 'internal'); $publicfields = array_keys(ArtefactTypeProfile::get_public_fields()); if (empty($publicfields)) { $publicfields = array('preferredname'); } if (is_postgres()) { return self::search_user_pg($query_string, $limit, $offset, $data, $publicfields); } else { if (is_mysql()) { return self::search_user_my($query_string, $limit, $offset, $data, $publicfields); } else { throw new SQLException('search_user() is not implemented for your database engine (' . get_config('dbtype') . ')'); } } }
/** * Given a query string and limits, return an array of matching users using the * search plugin defined in config.php * * @param string The query string * @param integer How many results to return * @param integer What result to start at (0 == first result) * @return array A data structure containing results looking like ... * $results = array( * count => integer, // total number of results * limit => integer, // how many results are returned * offset => integer, // starting from which result * results => array( // the result records * array( * id => integer, * username => string, * institution => string, * firstname => string, * lastname => string, * preferredname => string, * email => string, * ), * array( * id => integer, * username => string, * institution => string, * firstname => string, * lastname => string, * preferredname => string, * email => string, * ), * array(...), * ), * ); */ function search_user($query_string, $limit, $offset = 0, $data = array()) { $plugin = get_config('searchplugin'); safe_require('search', $plugin); safe_require('artefact', 'internal'); $publicfields = array_keys(ArtefactTypeProfile::get_public_fields()); if (empty($publicfields)) { $publicfields = array('preferredname'); } $fieldlist = "('" . join("','", $publicfields) . "')"; $results = call_static_method(generate_class_name('search', $plugin), 'search_user', $query_string, $limit, $offset, $data); if ($results['data']) { $userlist = '(' . join(',', array_map(create_function('$u', 'return (int)$u[\'id\'];'), $results['data'])) . ')'; $public_fields = get_records_sql_array(' SELECT u.id, a.artefacttype, a.title FROM {usr} u LEFT JOIN {artefact} a ON u.id=a.owner AND a.artefacttype IN ' . $fieldlist . ' WHERE u.id IN ' . $userlist . ' ORDER BY u.firstname, u.lastname, u.id, a.artefacttype', array()); $public_fields_byuser = array(); if (!empty($public_fields)) { foreach ($public_fields as $field) { // This will be null if the user does not have a field marked public if ($field->artefacttype !== null) { $public_fields_byuser[$field->id][$field->artefacttype] = $field->title; } } } foreach ($results['data'] as &$result) { $result['name'] = display_name($result); if (isset($public_fields_byuser[$result['id']])) { foreach ($public_fields_byuser[$result['id']] as $field => $value) { $result[$field] = $value; } } if (isset($result['country'])) { $result['country'] = get_string('country.' . $result['country']); } } } return $results; }
/** * Implement user searching with SQL * * NOTE: user with ID zero should never be returned * * @param string The query string * @param integer How many results to return * @param integer What result to start at (0 == first result) * @param data Filters the users searched by * can contain: * 'group' => integer, // only users in this group * 'owner' => boolean // include the group ownwer (only if group is set) * 'exclude'=> int // excludes a user * @return array A data structure containing results looking like ... * $results = array( * count => integer, // total number of results * limit => integer, // how many results are returned * offset => integer, // starting from which result * data => array( // the result records * array( * id => integer, * username => string, * institution => string, * firstname => string, * lastname => string, * preferredname => string, * email => string, * ), * array( * id => integer, * username => string, * institution => string, * firstname => string, * lastname => string, * preferredname => string, * email => string, * ), * array(...), * ), * ); */ public static function search_user($query_string, $limit, $offset = 0, $data = array()) { safe_require('artefact', 'internal'); $publicfields = array_keys(ArtefactTypeProfile::get_public_fields()); if (empty($publicfields)) { $publicfields = array('preferredname'); } $fieldlist = '(' . join(',', array_map('db_quote', $publicfields)) . ')'; $ilike = db_ilike(); $data = self::prepare_search_user_options($data); $sql = ' SELECT COUNT(DISTINCT u.id) FROM {artefact} a INNER JOIN {usr} u ON u.id = a.owner'; if (isset($data['group'])) { $groupadminsql = ''; if (isset($data['includeadmins']) and !$data['includeadmins']) { $groupadminsql = " AND gm.role != 'admin'"; } $groupjoin = ' INNER JOIN {group_member} gm ON (gm.member = u.id AND gm.group = ' . (int) $data['group'] . $groupadminsql . ")\n"; $sql .= $groupjoin; } $sql .= "\n LEFT OUTER JOIN {usr_account_preference} h ON (u.id = h.usr AND h.field = 'hiderealname')"; $querydata = split(' ', preg_replace('/\\s\\s+/', ' ', strtolower(trim($query_string)))); $hidenameallowed = get_config('userscanhiderealnames') ? 'TRUE' : 'FALSE'; $searchusernamesallowed = get_config('searchusernames') ? 'TRUE' : 'FALSE'; $namesql = "(u.preferredname {$ilike} '%' || ? || '%')\n OR ((u.preferredname IS NULL OR u.preferredname = '' OR NOT {$hidenameallowed} OR h.value != '1')\n AND (u.firstname {$ilike} '%' || ? || '%' OR u.lastname {$ilike} '%' || ? || '%'))\n OR ({$searchusernamesallowed} AND u.username {$ilike} '%' || ? || '%')\n OR (a.artefacttype IN {$fieldlist}\n AND ( a.title {$ilike} '%' || ? || '%'))"; $namesql = join(' OR ', array_fill(0, count($querydata), $namesql)); $values = array(); foreach ($querydata as $w) { $values = array_pad($values, count($values) + 5, $w); } $where = ' WHERE u.id <> 0 AND u.active = 1 AND u.deleted = 0 AND ( ' . $namesql . ' ) ' . (isset($data['exclude']) ? 'AND u.id != ' . $data['exclude'] : '') . ' '; $sql .= $where; $count = count_records_sql($sql, $values); $result = array('count' => $count, 'limit' => $limit, 'offset' => $offset, 'data' => false); if ($count < 1) { return $result; } if (is_postgres()) { $sql = ' SELECT DISTINCT ON (' . $data['orderby'] . ') u.id, u.username, u.firstname, u.lastname, u.preferredname, u.email, u.staff FROM {artefact} a INNER JOIN {usr} u ON u.id = a.owner'; } else { if (is_mysql()) { // @todo This is quite possibly not correct. See the postgres // query. It should be DISTINCT ON the fields as specified by the // postgres query. There is a workaround by using SELECT * followed // by GROUP BY 1, 2, 3, but it's not really valid SQL. The other // way is to rewrite the query using a self join on the usr table. $sql = ' SELECT DISTINCT u.id, u.username, u.firstname, u.lastname, u.preferredname, u.email, u.staff FROM {artefact} a INNER JOIN {usr} u ON u.id = a.owner'; } else { throw new SQLException('search_user() not implemented for your database engine (' . get_config('dbtype') . ')'); } } if (isset($data['group'])) { $sql .= $groupjoin; } $sql .= "\n LEFT OUTER JOIN {usr_account_preference} h ON (u.id = h.usr AND h.field = 'hiderealname')"; $sql .= $where . ' ORDER BY ' . $data['orderby']; $result['data'] = get_records_sql_array($sql, $values, $offset, $limit); if ($result['data']) { foreach ($result['data'] as &$item) { $item = (array) $item; } } return $result; }
public static function rebuild_users() { log_debug('Starting rebuild_users()'); self::delete_bytype('user'); $users = get_recordset('usr', 'deleted', '0'); safe_require('artefact', 'internal'); $publicfields = array_keys(ArtefactTypeProfile::get_public_fields()); while ($user = $users->FetchRow()) { if ($user['id'] == 0) { continue; } self::index_user($user); } $users->close(); log_debug('Completed rebuild_users()'); }