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

);