Enrollment

Here's the official story.

Users take up one or more specific roles in a 'context', e.g. they are student (role) in a course (context). Contexts are divided into levels:

From lib/accesslib.php:

define(‘CONTEXT_SYSTEM’, 10);
define(‘CONTEXT_PERSONAL’, 20);
define(‘CONTEXT_USER’, 30);
define(‘CONTEXT_COURSECAT’, 40);
define(‘CONTEXT_COURSE’, 50);
define(‘CONTEXT_GROUP’, 60);
define(‘CONTEXT_MODULE’, 70);
define(‘CONTEXT_BLOCK’, 80);

This means that, for instance, contextlevel 50 is about courses.

There are five important tables involved in enrollment. Here's the schema for the five of them:

(Schema created with http://ondras.zarovi.cz/sql/demo/)

If you are assigning a user as a student to a course, you have to make a new role_assignments record. To do that, you need look up:

• role_assignments.roleid: retrieve by role.shortname ('student')
• role_assignments.contextid: retrieve by context.contextlevel (always 50 for a course, see above) and instanceid (course.id)
• role_assignments.userid: user.id of the, uh, user.

Please note that there's an additional column in role_assignments called modifierid, destined for the user id of the user who added the new user (usually a course administrator or an admin user).

In addition, for each user you have to create a record in user_enrolments. The user_enrolments.enrolid is a foreign key pointing to enrol.id. The column user_enrolments.status normally has one of two values: 0 means active enrolment; 1 means suspended - anything else means inactive. The enrol.enrol column stores the enrolment plugin name (usually 'manual').

WARNING: as far as I can tell, Moodle 2.0 and 2.1 don't use enrol.roleid in any meaningful way. For instance, if you enrol a user as a teacher, the roleid will still point to a student for that course. In other words: user_enrolment.enrolid points to an enrol record which points to a student role, even though the user is actually a teacher in that course.

The column enrol.roleid is probably only used to store the default roleid for a given enrollment method.

A user can have an active or inactive enrollment status in a course. This enrollment status is in no way linked to the role(s) a user may have in a course.

In other words, you can be a student who is enrolled 'manually', and also through a cohort sync enrollment. If the cohort enrollment is deleted, you'll still have the student role in that course.

Here's how to retrieve all active users (i.e. they haven't completed the course yet), by a specific (i.e. single) capability:

$active_users = $DB->get_records_sql("
    SELECT DISTINCT(u.id), u.firstname, u.lastname, u.email,  con.instanceid as course_id
    FROM mdl_context AS con, mdl_user AS u, mdl_role_assignments AS ra, mdl_course AS c, mdl_role_capabilities AS rc
    WHERE con.contextlevel = 50 
    AND ra.userid = u.id 
    AND ra.contextid = con.id
    AND NOT EXISTS (SELECT * FROM mdl_course_completions AS cc WHERE cc.userid = ra.userid AND cc.course = con.instanceid AND cc.timecompleted > 0)
    AND EXISTS (SELECT * FROM mdl_enrol AS e, mdl_user_enrolments AS ue WHERE e.id = ue.enrolid AND e.courseid = con.instanceid AND ue.userid = ra.userid AND ue.status = 0)
    AND NOT u.deleted = 1
    AND c.id = con.instanceid
    AND c.id = :course_id
    AND rc.permission = 1
    AND ra.roleid = rc.roleid
    AND rc.capability LIKE :capability_receivereminders
    AND u.id NOT IN ({$CFG->siteadmins})",
    array(
        'course_id' => $course_id,
        'capability_receivereminders' => 'local/remindermailer:receivereminders'
    )
);

By the way, you'd expect you'd have to add in this clause:

AND rc.contextid = con.id

But for some unknown reason, Moodle sets the role_capabilities.contextid to 1 in almost all cases (except where permission != 1). I assume that a permission can be withdrawn in a more specialized local context.

Conversely, to get all users who are inactive, change the EXISTS clause on the user enrollment status to NOT EXISTS:

AND NOT EXISTS (SELECT * FROM mdl_enrol AS e, mdl_user_enrolments AS ue WHERE e.id = ue.enrolid AND e.courseid = con.instanceid AND ue.userid = ra.userid AND ue.status = 0)

If you enroll a user through cohort sync, please keep in mind that unenrolling that user will result in the removal of all grades, user/course specific settings, group membership, etc..

The grades WILL be stored if you add in another type of enrollment. For instance, if the user is enrolled through a cohort sync and through a manual enrollment, then the user data will be stored if you remove the user from the cohort.

The grades will still be invisible if the alternative (i.e. non-cohort) enrollment is set to 'Suspended' (inactive) status.