/**
* Who has this capability in this context?
*
* This can be a very expensive call - use sparingly and keep
* the results if you are going to need them again soon.
*
* Note if $fields is empty this function attempts to get u.*
* which can get rather large - and has a serious perf impact
* on some DBs.
*
* @param context $context
* @param string|array $capability - capability name(s)
* @param string $fields - fields to be pulled. The user table is aliased to 'u'. u.id MUST be included.
* @param string $sort - the sort order. Default is lastaccess time.
* @param mixed $limitfrom - number of records to skip (offset)
* @param mixed $limitnum - number of records to fetch
* @param string|array $groups - single group or array of groups - only return
* users who are in one of these group(s).
* @param string|array $exceptions - list of users to exclude, comma separated or array
* @param bool $doanything_ignored not used any more, admin accounts are never returned
* @param bool $view_ignored - use get_enrolled_sql() instead
* @param bool $useviewallgroups if $groups is set the return users who
* have capability both $capability and moodle/site:accessallgroups
* in this context, as well as users who have $capability and who are
* in $groups.
* @return mixed
*/
function get_users_by_capability(context $context, $capability, $fields = '', $sort = '', $limitfrom = '', $limitnum = '', $groups = '', $exceptions = '', $doanything_ignored = null, $view_ignored = null, $useviewallgroups = false)
{
global $CFG, $DB;
$defaultuserroleid = isset($CFG->defaultuserroleid) ? $CFG->defaultuserroleid : 0;
$defaultfrontpageroleid = isset($CFG->defaultfrontpageroleid) ? $CFG->defaultfrontpageroleid : 0;
$ctxids = trim($context->path, '/');
$ctxids = str_replace('/', ',', $ctxids);
// Context is the frontpage
$iscoursepage = false;
// coursepage other than fp
$isfrontpage = false;
if ($context->contextlevel == CONTEXT_COURSE) {
if ($context->instanceid == SITEID) {
$isfrontpage = true;
} else {
$iscoursepage = true;
}
}
$isfrontpage = $isfrontpage || is_inside_frontpage($context);
$caps = (array) $capability;
// construct list of context paths bottom-->top
list($contextids, $paths) = get_context_info_list($context);
// we need to find out all roles that have these capabilities either in definition or in overrides
$defs = array();
list($incontexts, $params) = $DB->get_in_or_equal($contextids, SQL_PARAMS_NAMED, 'con');
list($incaps, $params2) = $DB->get_in_or_equal($caps, SQL_PARAMS_NAMED, 'cap');
$params = array_merge($params, $params2);
$sql = "SELECT rc.id, rc.roleid, rc.permission, rc.capability, ctx.path\n FROM {role_capabilities} rc\n JOIN {context} ctx on rc.contextid = ctx.id\n WHERE rc.contextid {$incontexts} AND rc.capability {$incaps}";
$rcs = $DB->get_records_sql($sql, $params);
foreach ($rcs as $rc) {
$defs[$rc->capability][$rc->path][$rc->roleid] = $rc->permission;
}
// go through the permissions bottom-->top direction to evaluate the current permission,
// first one wins (prohibit is an exception that always wins)
$access = array();
foreach ($caps as $cap) {
foreach ($paths as $path) {
if (empty($defs[$cap][$path])) {
continue;
}
foreach ($defs[$cap][$path] as $roleid => $perm) {
if ($perm == CAP_PROHIBIT) {
$access[$cap][$roleid] = CAP_PROHIBIT;
continue;
}
if (!isset($access[$cap][$roleid])) {
$access[$cap][$roleid] = (int) $perm;
}
}
}
}
// make lists of roles that are needed and prohibited in this context
$needed = array();
// one of these is enough
$prohibited = array();
// must not have any of these
foreach ($caps as $cap) {
if (empty($access[$cap])) {
continue;
}
foreach ($access[$cap] as $roleid => $perm) {
if ($perm == CAP_PROHIBIT) {
unset($needed[$cap][$roleid]);
$prohibited[$cap][$roleid] = true;
} else {
if ($perm == CAP_ALLOW and empty($prohibited[$cap][$roleid])) {
$needed[$cap][$roleid] = true;
}
}
}
if (empty($needed[$cap]) or !empty($prohibited[$cap][$defaultuserroleid])) {
// easy, nobody has the permission
unset($needed[$cap]);
unset($prohibited[$cap]);
} else {
if ($isfrontpage and !empty($prohibited[$cap][$defaultfrontpageroleid])) {
// everybody is disqualified on the frontapge
unset($needed[$cap]);
unset($prohibited[$cap]);
}
}
if (empty($prohibited[$cap])) {
unset($prohibited[$cap]);
}
}
if (empty($needed)) {
// there can not be anybody if no roles match this request
return array();
}
if (empty($prohibited)) {
// we can compact the needed roles
$n = array();
foreach ($needed as $cap) {
foreach ($cap as $roleid => $unused) {
$n[$roleid] = true;
}
}
$needed = array('any' => $n);
unset($n);
}
/// ***** Set up default fields ******
if (empty($fields)) {
if ($iscoursepage) {
$fields = 'u.*, ul.timeaccess AS lastaccess';
} else {
$fields = 'u.*';
}
} else {
if (debugging('', DEBUG_DEVELOPER) && strpos($fields, 'u.*') === false && strpos($fields, 'u.id') === false) {
debugging('u.id must be included in the list of fields passed to get_users_by_capability().', DEBUG_DEVELOPER);
}
}
/// Set up default sort
if (empty($sort)) {
// default to course lastaccess or just lastaccess
if ($iscoursepage) {
$sort = 'ul.timeaccess';
} else {
$sort = 'u.lastaccess';
}
}
// Prepare query clauses
$wherecond = array();
$params = array();
$joins = array();
// User lastaccess JOIN
if (strpos($sort, 'ul.timeaccess') === false and strpos($fields, 'ul.timeaccess') === false) {
// user_lastaccess is not required MDL-13810
} else {
if ($iscoursepage) {
$joins[] = "LEFT OUTER JOIN {user_lastaccess} ul ON (ul.userid = u.id AND ul.courseid = {$context->instanceid})";
} else {
throw new coding_exception('Invalid sort in get_users_by_capability(), ul.timeaccess allowed only for course contexts.');
}
}
/// We never return deleted users or guest account.
$wherecond[] = "u.deleted = 0 AND u.id <> :guestid";
$params['guestid'] = $CFG->siteguest;
/// Groups
if ($groups) {
$groups = (array) $groups;
list($grouptest, $grpparams) = $DB->get_in_or_equal($groups, SQL_PARAMS_NAMED, 'grp');
$grouptest = "u.id IN (SELECT userid FROM {groups_members} gm WHERE gm.groupid {$grouptest})";
$params = array_merge($params, $grpparams);
if ($useviewallgroups) {
$viewallgroupsusers = get_users_by_capability($context, 'moodle/site:accessallgroups', 'u.id, u.id', '', '', '', '', $exceptions);
if (!empty($viewallgroupsusers)) {
$wherecond[] = "({$grouptest} OR u.id IN (" . implode(',', array_keys($viewallgroupsusers)) . '))';
} else {
$wherecond[] = "({$grouptest})";
}
} else {
$wherecond[] = "({$grouptest})";
}
}
/// User exceptions
if (!empty($exceptions)) {
$exceptions = (array) $exceptions;
list($exsql, $exparams) = $DB->get_in_or_equal($exceptions, SQL_PARAMS_NAMED, 'exc', false);
$params = array_merge($params, $exparams);
$wherecond[] = "u.id {$exsql}";
}
// now add the needed and prohibited roles conditions as joins
if (!empty($needed['any'])) {
// simple case - there are no prohibits involved
if (!empty($needed['any'][$defaultuserroleid]) or $isfrontpage and !empty($needed['any'][$defaultfrontpageroleid])) {
// everybody
} else {
$joins[] = "JOIN (SELECT DISTINCT userid\n FROM {role_assignments}\n WHERE contextid IN ({$ctxids})\n AND roleid IN (" . implode(',', array_keys($needed['any'])) . ")\n ) ra ON ra.userid = u.id";
}
} else {
$unions = array();
$everybody = false;
foreach ($needed as $cap => $unused) {
if (empty($prohibited[$cap])) {
if (!empty($needed[$cap][$defaultuserroleid]) or $isfrontpage and !empty($needed[$cap][$defaultfrontpageroleid])) {
$everybody = true;
break;
} else {
$unions[] = "SELECT userid\n FROM {role_assignments}\n WHERE contextid IN ({$ctxids})\n AND roleid IN (" . implode(',', array_keys($needed[$cap])) . ")";
}
} else {
if (!empty($prohibited[$cap][$defaultuserroleid]) or $isfrontpage and !empty($prohibited[$cap][$defaultfrontpageroleid])) {
// nobody can have this cap because it is prevented in default roles
continue;
} else {
if (!empty($needed[$cap][$defaultuserroleid]) or $isfrontpage and !empty($needed[$cap][$defaultfrontpageroleid])) {
// everybody except the prohibitted - hiding does not matter
$unions[] = "SELECT id AS userid\n FROM {user}\n WHERE id NOT IN (SELECT userid\n FROM {role_assignments}\n WHERE contextid IN ({$ctxids})\n AND roleid IN (" . implode(',', array_keys($prohibited[$cap])) . "))";
} else {
$unions[] = "SELECT userid\n FROM {role_assignments}\n WHERE contextid IN ({$ctxids})\n AND roleid IN (" . implode(',', array_keys($needed[$cap])) . ")\n AND roleid NOT IN (" . implode(',', array_keys($prohibited[$cap])) . ")";
}
}
}
}
if (!$everybody) {
if ($unions) {
$joins[] = "JOIN (SELECT DISTINCT userid FROM ( " . implode(' UNION ', $unions) . " ) us) ra ON ra.userid = u.id";
} else {
// only prohibits found - nobody can be matched
$wherecond[] = "1 = 2";
}
}
}
// Collect WHERE conditions and needed joins
$where = implode(' AND ', $wherecond);
if ($where !== '') {
$where = 'WHERE ' . $where;
}
$joins = implode("\n", $joins);
/// Ok, let's get the users!
$sql = "SELECT {$fields}\n FROM {user} u\n {$joins}\n {$where}\n ORDER BY {$sort}";
return $DB->get_records_sql($sql, $params, $limitfrom, $limitnum);
}
/**
* Gets sql joins for finding users with a capability in the given context
*
* @param context $context
* @param string $capability
* @param string $useridcolumn e.g. u.id
* @return \core\dml\sql_join Contains joins, wheres, params
*/
function get_with_capability_join(context $context, $capability, $useridcolumn)
{
global $DB, $CFG;
// Use unique prefix just in case somebody makes some SQL magic with the result.
static $i = 0;
$i++;
$prefix = 'eu' . $i . '_';
// First find the course context.
$coursecontext = $context->get_course_context();
$isfrontpage = $coursecontext->instanceid == SITEID;
$joins = array();
$wheres = array();
$params = array();
list($contextids, $contextpaths) = get_context_info_list($context);
list($incontexts, $cparams) = $DB->get_in_or_equal($contextids, SQL_PARAMS_NAMED, 'ctx');
$cparams['cap'] = $capability;
$defs = array();
$sql = "SELECT rc.id, rc.roleid, rc.permission, ctx.path\n FROM {role_capabilities} rc\n JOIN {context} ctx on rc.contextid = ctx.id\n WHERE rc.contextid {$incontexts} AND rc.capability = :cap";
$rcs = $DB->get_records_sql($sql, $cparams);
foreach ($rcs as $rc) {
$defs[$rc->path][$rc->roleid] = $rc->permission;
}
$access = array();
if (!empty($defs)) {
foreach ($contextpaths as $path) {
if (empty($defs[$path])) {
continue;
}
foreach ($defs[$path] as $roleid => $perm) {
if ($perm == CAP_PROHIBIT) {
$access[$roleid] = CAP_PROHIBIT;
continue;
}
if (!isset($access[$roleid])) {
$access[$roleid] = (int) $perm;
}
}
}
}
unset($defs);
// Make lists of roles that are needed and prohibited.
$needed = array();
// One of these is enough.
$prohibited = array();
// Must not have any of these.
foreach ($access as $roleid => $perm) {
if ($perm == CAP_PROHIBIT) {
unset($needed[$roleid]);
$prohibited[$roleid] = true;
} else {
if ($perm == CAP_ALLOW and empty($prohibited[$roleid])) {
$needed[$roleid] = true;
}
}
}
$defaultuserroleid = isset($CFG->defaultuserroleid) ? $CFG->defaultuserroleid : 0;
$defaultfrontpageroleid = isset($CFG->defaultfrontpageroleid) ? $CFG->defaultfrontpageroleid : 0;
$nobody = false;
if ($isfrontpage) {
if (!empty($prohibited[$defaultuserroleid]) or !empty($prohibited[$defaultfrontpageroleid])) {
$nobody = true;
} else {
if (!empty($needed[$defaultuserroleid]) or !empty($needed[$defaultfrontpageroleid])) {
// Everybody not having prohibit has the capability.
$needed = array();
} else {
if (empty($needed)) {
$nobody = true;
}
}
}
} else {
if (!empty($prohibited[$defaultuserroleid])) {
$nobody = true;
} else {
if (!empty($needed[$defaultuserroleid])) {
// Everybody not having prohibit has the capability.
$needed = array();
} else {
if (empty($needed)) {
$nobody = true;
}
}
}
}
if ($nobody) {
// Nobody can match so return some SQL that does not return any results.
$wheres[] = "1 = 2";
} else {
if ($needed) {
$ctxids = implode(',', $contextids);
$roleids = implode(',', array_keys($needed));
$joins[] = "JOIN {role_assignments} {$prefix}ra3\n ON ({$prefix}ra3.userid = {$useridcolumn}\n AND {$prefix}ra3.roleid IN ({$roleids})\n AND {$prefix}ra3.contextid IN ({$ctxids}))";
}
if ($prohibited) {
$ctxids = implode(',', $contextids);
$roleids = implode(',', array_keys($prohibited));
$joins[] = "LEFT JOIN {role_assignments} {$prefix}ra4\n ON ({$prefix}ra4.userid = {$useridcolumn}\n AND {$prefix}ra4.roleid IN ({$roleids})\n AND {$prefix}ra4.contextid IN ({$ctxids}))";
$wheres[] = "{$prefix}ra4.id IS NULL";
}
}
$wheres[] = "{$useridcolumn} <> :{$prefix}guestid";
$params["{$prefix}guestid"] = $CFG->siteguest;
$joins = implode("\n", $joins);
$wheres = "(" . implode(" AND ", $wheres) . ")";
return new \core\dml\sql_join($joins, $wheres, $params);
}
