public static function admin_search_user($query_string, $constraints, $offset, $limit, $sortfield, $sortdir)
{
// We need to fudge some stuff before sending it on, because get_admin_user_search_results()
// in lib/searchlib.php has some hard-coded special functionality for the internal search plugin
if (is_array($query_string) && count($query_string) > 0) {
$query_string = $query_string[0]['string'];
} else {
$query_string = "";
}
return PluginSearchInternal::admin_search_user($query_string, $constraints, $offset, $limit, $sortfield, $sortdir);
}
public static function group_search_user($group, $queries, $constraints, $offset, $limit, $membershiptype, $order = null)
{
// Only handle OR/AND expressions at the top level. Eventually we may need subexpressions.
$searchsql = '';
$values = array();
if (!empty($queries)) {
$ilike = db_ilike();
$searchsql .= ' AND ( ';
$str = array();
foreach ($queries as $f) {
if (!preg_match('/^[a-zA-Z_0-9"]+$/', $f['field'])) {
continue;
// skip this field as it fails validation
}
$str[] = 'u.' . $f['field'] . PluginSearchInternal::match_expression($f['type'], $f['string'], $values, $ilike);
}
$searchsql .= join(' OR ', $str) . ') ';
}
if ($membershiptype == 'nonmember') {
$select = '
u.id, u.firstname, u.lastname, u.username, u.email, u.profileicon, u.staff';
$from = '
FROM {usr} u
WHERE u.id > 0 AND u.deleted = 0 ' . $searchsql . '
AND NOT u.id IN (SELECT member FROM {group_member} gm WHERE gm.group = ?)';
$values[] = $group;
$orderby = 'u.firstname, u.lastname, u.id';
} else {
if ($membershiptype == 'notinvited') {
$select = '
u.id, u.firstname, u.lastname, u.username, u.email, u.profileicon, u.staff';
$from = '
FROM {usr} u
WHERE u.id > 0 AND u.deleted = 0 ' . $searchsql . '
AND NOT u.id IN (SELECT member FROM {group_member} gm WHERE gm.group = ?)
AND NOT u.id IN (SELECT member FROM {group_member_invite} gmi WHERE gmi.group = ?)';
$values[] = $group;
$values[] = $group;
$orderby = 'u.firstname, u.lastname, u.id';
} else {
if ($membershiptype == 'request') {
$select = '
u.id, u.firstname, u.lastname, u.username, u.email, u.profileicon,
u.staff, ' . db_format_tsfield('gmr.ctime', 'jointime');
$from = '
FROM {usr} u
INNER JOIN {group_member_request} gmr ON (gmr.member = u.id)
WHERE u.id > 0 AND u.deleted = 0 ' . $searchsql . '
AND gmr.group = ?';
$values[] = $group;
$orderby = 'gmr.ctime, u.firstname, u.lastname, u.id';
} else {
if ($membershiptype == 'invite') {
$select = '
u.id, u.firstname, u.lastname, u.username, u.email, u.profileicon,
u.staff, ' . db_format_tsfield('gmi.ctime', 'jointime');
$from = '
FROM {usr} u
INNER JOIN {group_member_invite} gmi ON (gmi.member = u.id)
WHERE u.id > 0 AND u.deleted = 0 ' . $searchsql . '
AND gmi.group = ?';
$values[] = $group;
$orderby = 'gmi.ctime, u.firstname, u.lastname, u.id';
} else {
// All group members
$select = '
u.id, u.firstname, u.lastname, u.username, u.email, u.profileicon,
u.staff, ' . db_format_tsfield('gm.ctime', 'jointime') . ', gm.role';
$from = '
FROM {usr} u
INNER JOIN {group_member} gm ON (gm.member = u.id)
WHERE u.id > 0 AND u.deleted = 0 ' . $searchsql . '
AND gm.group = ?';
$values[] = $group;
$orderby = "gm.role = 'admin' DESC, gm.ctime, u.firstname, u.lastname, u.id";
if ($order == 'latest') {
$orderby = 'gm.ctime DESC, u.firstname, u.lastname, u.id';
}
}
}
}
}
if ($order == 'random') {
$orderby = db_random();
}
$count = get_field_sql('SELECT COUNT(*)' . $from, $values);
if ($count > 0) {
$data = get_records_sql_assoc('
SELECT ' . $select . $from . ' ORDER BY ' . $orderby, $values, $offset, $limit);
if ($data) {
foreach ($data as &$item) {
$item = (array) $item;
}
$data = array_values($data);
}
} else {
$data = array();
}
return array('count' => $count, 'limit' => $limit, 'offset' => $offset, 'data' => $data);
}
/**
* Returns a list of search results for the admin user search interface.
*
* The constraints parameter takes an array of arrays, like so:
* $params = array(
* array(
* 'field' => 'institution'
* 'string' => 'mahara'
* 'type' => 'equals'
* ),
* ...
* )
*
* Each constraint should has these three keys:
* field: Should be a column in the usr table, or the special field "duplicateemails" (which indicates only users with a non-unique email).
* also, for the field "institution", a string value of "mahara" indicates users with no institution
* string: The value to compare the contents of that field against
* type: The operation by which to compare "field" to "string". This can be any of the operations in PluginSearchInternal::match_expression
* (starts, equals, notequals, greaterthan, greaterthanequal, lessthan, lessthanequal, contains, or in)
*
* @param string $query_string The string to search for
* @param array $constraints A list of constraints on the search results (see above for format)
* @param int $offset
* @param int $limit
* @param string $sortfield Which of the output columns to sort by
* @param string $sortdir DESC or ASC
*/
public static function admin_search_user($query_string, $constraints, $offset, $limit, $sortfield, $sortdir)
{
$sort = 'TRUE';
if (preg_match('/^[a-zA-Z_0-9"]+$/', $sortfield)) {
$sort = $sortfield;
if (strtoupper($sortdir) != 'DESC') {
$sort .= ' ASC';
} else {
$sort .= ' DESC';
}
}
$join = '';
$where = 'WHERE u.id <> 0 AND u.deleted = 0';
$values = array();
// Get the correct keyword for case insensitive LIKE
$ilike = db_ilike();
// Generate the part that matches the search term
$querydata = self::split_query_string(strtolower(trim($query_string)));
$matches = array();
foreach (array('firstname', 'lastname', 'preferredname', 'username', 'email') as $f) {
$matches[] = self::match_user_field_expression($f, 'u');
}
$termsql = join(" OR ", $matches);
$values = array();
foreach ($querydata as $term) {
$where .= '
AND (
' . $termsql . '
)';
$values = array_pad($values, count($values) + 5, $term);
}
$firstcols = 'u.id';
if (!empty($constraints)) {
foreach ($constraints as $f) {
if ($f['field'] == 'institution') {
if ($f['string'] == 'mahara') {
$where .= ' AND u.id NOT IN (SELECT usr FROM {usr_institution})';
} else {
$where .= '
AND u.id IN (
SELECT usr FROM {usr_institution} WHERE institution ' . PluginSearchInternal::match_expression($f['type'], $f['string'], $values, $ilike) . '
)';
}
} else {
if ($f['field'] == 'duplicateemail') {
if (!empty($f['string'])) {
$where .= '
AND u.id IN (
SELECT owner
FROM {artefact}
WHERE id IN (' . join(',', array_map('db_quote', $f['string'])) . ')
)';
} else {
// No duplicate email is found, return empty list
$where .= ' AND FALSE';
}
} else {
if ($f['field'] == 'exportqueue') {
$firstcols = 'e.id AS eid,
(SELECT case WHEN e.starttime IS NOT NULL THEN ' . db_format_tsfield('e.starttime', false) . ' ELSE ' . db_format_tsfield('e.ctime', false) . ' END) AS status,
' . $firstcols;
$join .= 'JOIN {export_queue} e ON e.usr = u.id ';
$where .= ' AND u.id' . PluginSearchInternal::match_expression($f['type'], $f['string'], $values, $ilike);
} else {
if ($f['field'] == 'archivesubmissions') {
$firstcols = 'e.id AS eid, a.group,
(SELECT name FROM {group} WHERE id = a.group) AS submittedto,
(SELECT case WHEN a.externalid IS NOT NULL THEN a.externalid ELSE CAST(e.id AS char) END) AS specialid,
e.filetitle, e.filename, e.filepath, ' . db_format_tsfield('e.ctime', 'archivectime') . ', ' . $firstcols;
$join .= 'JOIN {export_archive} e ON e.usr = u.id ';
$join .= 'JOIN {archived_submissions} a ON a.archiveid = e.id ';
$where .= ' AND u.id' . PluginSearchInternal::match_expression($f['type'], $f['string'], $values, $ilike);
} else {
$where .= ' AND u.' . $f['field'] . PluginSearchInternal::match_expression($f['type'], $f['string'], $values, $ilike);
}
}
}
}
}
}
$count = get_field_sql('SELECT COUNT(*) FROM {usr} u ' . $join . $where, $values);
if ($count > 0) {
$data = get_records_sql_assoc('
SELECT ' . $firstcols . ',
u.firstname, u.lastname, u.preferredname, u.username, u.email, u.staff, u.profileicon,
u.lastlogin, u.active, NOT u.suspendedcusr IS NULL as suspended, au.instancename AS authname
FROM {usr} u INNER JOIN {auth_instance} au ON u.authinstance = au.id ' . $join . $where . '
ORDER BY ' . $sort . ', u.id', $values, $offset, $limit);
if ($data) {
$inst = get_records_select_array('usr_institution', 'usr IN (' . join(',', array_keys($data)) . ')', null, '', 'usr,institution');
if ($inst) {
foreach ($inst as $i) {
$data[$i->usr]->institutions[] = $i->institution;
}
}
foreach ($data as &$item) {
$item->username = display_username($item);
$item = (array) $item;
}
$data = array_values($data);
}
} else {
$data = false;
}
return array('count' => $count, 'limit' => $limit, 'offset' => $offset, 'data' => $data);
}
public static function group_search_user($group, $queries, $constraints, $offset, $limit, $membershiptype)
{
$where = 'WHERE gm.group = ?';
$values = array($group);
// Get the correct keyword for case insensitive LIKE
$ilike = db_ilike();
// Only handle OR/AND expressions at the top level. Eventually we may need subexpressions.
if (!empty($queries)) {
$where .= ' AND ( ';
$str = array();
foreach ($queries as $f) {
$str[] = 'u.' . $f['field'] . PluginSearchInternal::match_expression($f['type'], $f['string'], $values, $ilike);
}
$where .= join(' OR ', $str) . ') ';
}
$group_member = 'group_member';
if (!empty($membershiptype) && in_array($membershiptype, array('request', 'invite'))) {
$group_member .= '_' . $membershiptype;
$gm_role = '';
$gm_role_order = '';
} else {
$gm_role = ', gm.role';
$gm_role_order = "gm.role = 'admin' DESC, ";
}
$count = get_field_sql('SELECT COUNT(*) FROM {usr} u INNER JOIN {' . $group_member . '} gm ON (gm.member = u.id) ' . $where, $values);
if ($count > 0) {
$data = get_records_sql_assoc('
SELECT
u.id, u.firstname, u.lastname, u.username, u.email, u.staff, ' . db_format_tsfield('gm.ctime', 'jointime') . $gm_role . '
FROM
{usr} u
INNER JOIN {' . $group_member . '} gm ON (gm.member = u.id) ' . $where . '
ORDER BY ' . $gm_role_order . 'gm.ctime, u.firstname, u.lastname, u.id', $values, $offset, $limit);
if ($data) {
foreach ($data as &$item) {
$item = (array) $item;
}
$data = array_values($data);
}
} else {
$data = array();
}
return array('count' => $count, 'limit' => $limit, 'offset' => $offset, 'data' => $data);
}
