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