/**
* This will add a groupid to each of the submissions coming out of the moduleunion. This means getting
* all the group memberships for this course and choosing the maximum groupid. We can't count more than
* once when students are in two groups, so we need to do it like this. It's a greatest-n-per-group
* problem, solved with the classic left-join approach.
*
* @param block_ajax_marking_query $query
* @return void
*/
protected function alter_query(block_ajax_marking_query $query)
{
list($maxquery, $maxparams) = block_ajax_marking_group_max_subquery();
list($memberquery, $memberparams) = block_ajax_marking_group_members_subquery();
list($visibilitysubquery, $visibilityparams) = block_ajax_marking_group_visibility_subquery();
// We need to join to groups members to see if there are any memberships at all (in which case
// we use the highest visible id if there is one), or 0 if there are no memberships at all.
$table = array('join' => 'LEFT JOIN', 'table' => $memberquery, 'on' => 'membergroupquery.userid = moduleunion.userid
AND membergroupquery.coursemoduleid = moduleunion.coursemoduleid', 'alias' => 'membergroupquery', 'subquery' => true);
$query->add_from($table);
$query->add_params($memberparams);
// To make sure it's the highest visible one, we use this subquery as a greatest-n-per-group thing.
$table = array('join' => 'LEFT JOIN', 'table' => $maxquery, 'on' => 'membergroupquery.userid = maxgroupquery.userid
AND membergroupquery.coursemoduleid = maxgroupquery.coursemoduleid
AND maxgroupquery.groupid > membergroupquery.groupid', 'alias' => 'maxgroupquery', 'subquery' => true);
$query->add_from($table);
$query->add_params($maxparams);
// We join only if the group id is larger, then specify that it must be null. This means that
// the membergroupquery group id will be the largest available.
$query->add_where(array('type' => 'AND', 'condition' => 'maxgroupquery.groupid IS NULL'));
// Make sure it's not hidden. We want to know if there are people with no group, compared to a group that
// is hidden, so the aim is to get a null group id if there are no memberships by left joining, then
// hide that null row if the settings for group id 0 say so.
$table = array('join' => 'LEFT JOIN', 'table' => $visibilitysubquery, 'on' => '(membervisibilityquery.groupid = membergroupquery.groupid OR
(membervisibilityquery.groupid = 0 AND membergroupquery.groupid IS NULL))
AND membervisibilityquery.coursemoduleid = membergroupquery.coursemoduleid', 'alias' => 'membervisibilityquery', 'subquery' => true);
$query->add_from($table);
$query->add_params($visibilityparams);
$query->add_where(array('type' => 'AND', 'condition' => 'membervisibilityquery.coursemoduleid IS NULL'));
}
/**
* Returns an SQL snippet that will tell us whether a student is directly enrolled in this
* course
*
* @param block_ajax_marking_query $query
* @param array $filters So we can filter by cohortid if we need to
* @return array The join and where strings, with params. (Where starts with 'AND)
*/
private static function apply_sql_enrolled_students(block_ajax_marking_query $query, array $filters)
{
global $DB, $CFG, $USER;
$nextnodefilter = block_ajax_marking_get_nextnodefilter_from_params($filters);
// Hide users added by plugins which are now disabled.
if (isset($filters['cohortid']) || $nextnodefilter == 'cohortid') {
// We need to specify only people enrolled via a cohort.
$enabledsql = " = 'cohort'";
} else {
if ($CFG->enrol_plugins_enabled) {
// Returns list of english names of enrolment plugins.
$plugins = explode(',', $CFG->enrol_plugins_enabled);
list($enabledsql, $params) = $DB->get_in_or_equal($plugins, SQL_PARAMS_NAMED, 'enrol001');
$query->add_params($params);
} else {
// No enabled enrolment plugins.
$enabledsql = ' = :sqlenrollednever';
$query->add_param('sqlenrollednever', -1);
}
}
$sql = <<<SQL
SELECT NULL
FROM {enrol} enrol
INNER JOIN {user_enrolments} user_enrolments
ON user_enrolments.enrolid = enrol.id
WHERE enrol.enrol {$enabledsql}
AND enrol.courseid = moduleunion.course
AND user_enrolments.userid != :enrolcurrentuser
AND user_enrolments.userid = moduleunion.userid
SQL;
$query->add_where(array('type' => 'AND', 'condition' => "EXISTS ({$sql})"));
$query->add_param('enrolcurrentuser', $USER->id, false);
}