Global course deployment and auto-enroll using SQL
Overview
For FiLMS Instances with a large number of organizations, the Enrolment Wizard may fail when attempting to enroll users users. This task can be performed by running SQL against the database.
Preparation
ID numbers vary between FiLMS Instances. Collect the following ID numbers for the specific instance you'll be performing this process on:
- course_id
- structure_id
- curriculum_id
Execution
Global
/* Add the course to the org catalog - Includes course in a group's auto-enroll tree */
INSERT IGNORE INTO `organization_course_catalogs`(course_id,organization_id,is_enabled)
SELECT /* !!!!! REPLACE WITH YOUR course_id !!!!! */ 111, id ,1
FROM organizations;
/* Add the structure to the All Users group */
INSERT IGNORE INTO `group_structure_catalogs`(group_id, structure_id,is_required)
SELECT id, /* !!!!! REPLACE WITH YOUR structure_id !!!!! */ 115 ,0
FROM `groups`
WHERE name = 'All Users';
/* Add auto-enrolment - Future users will become enrolled */
INSERT IGNORE INTO `fm_autoenroll_assn`(group_id, curriculum_id)
SELECT id, /* !!!!! REPLACE WITH YOUR curriculum_id !!!!! */ 107
FROM `groups`
WHERE name = 'All Users';
/* Enroll current users */
INSERT IGNORE INTO enrollments (user_id, curriculum_id, enrolled_at, notes)
SELECT id
, /* !!!!! REPLACE WITH YOUR curriculum_id !!!!! */ 107
, DATE_ADD(NOW(), INTERVAL -2 HOUR)
, 'mjohnson: 2015-04-29'
FROM users
WHERE is_active = 1
AND id NOT IN
(
SELECT user_id
FROM enrollments
WHERE curriculum_id = /* !!!!! REPLACE WITH YOUR curriculum_id !!!!! */ 107
);
Group specific
/* Add the course to the org catalog - Includes course in a group's auto-enroll tree */
INSERT IGNORE INTO `organization_course_catalogs`(course_id,organization_id,is_enabled)
SELECT /* ****COURSE ID**** */108, id, 1
FROM organizations ;
/* Add the structure to the group */
INSERT IGNORE INTO `group_structure_catalogs`(group_id, structure_id,is_required)
VALUES(/* ****GROUP ID**** */5, /* ****STRUCTURE ID**** */110,0);
/* Add auto-enrolment - Future users will become enrolled */
INSERT IGNORE INTO `fm_autoenroll_assn`(group_id, curriculum_id)
VALUES(/* ****GROUP ID**** */5, /* ****CURRICULUM ID**** */118);
/* Enroll current users */
INSERT IGNORE INTO enrollments (user_id, curriculum_id, enrolled_at, notes)
SELECT id, /* ****CURRICULUM ID**** */118, DATE_ADD(NOW(), INTERVAL -2 HOUR), 'mjohnson: 2016-02-26'
FROM users
WHERE is_active = 1
AND id NOT IN
(
SELECT user_id
FROM enrollments
WHERE curriculum_id = /* ****CURRICULUM ID**** */118
)
AND id IN
(
SELECT user_id
FROM group_member_assn
WHERE group_id = /* ****GROUP ID**** */5
);