We enrol all our students on courses using a CSV file created from our Student Management system. The problem currently with this is that it creates a file that has 129000 rows (as there are several courses where all staff and students are enrolled ) which the bulk of the data has been already applied to moodle so its just making our cron run for 10 mins. To cut down the file size I have changed the sql query to join with the moodle tables to check the student exists in moodle and that they are not already on the course, the moodle bit of the query to identify students on a course looks like this for course with idnumber 18
FROM moodle_db.dbo.mdl_course AS course
JOIN moodle_db.dbo.mdl_enrol AS en ON en.courseid = course.id
JOIN moodle_db.dbo.mdl_user_enrolments AS ue ON ue.enrolid = en.id
JOIN moodle_db.dbo.mdl_user AS user2 ON ue.userid = user2.id
and rtrim(CAST(SD.refno AS nvarchar(6))) in (select username from moodle_db.dbo.mdl_user)
The problem is that the cron issues error ' Unknown user idnumber or deleted user in field 3' for students that have been unenrolled which from the crons point of view is valid (i.e don't enrol if they have been deleted).
However the query above does not include students that have been unenrolled, I can't find in the moodle tables where this is kept? I know that the cron is finding this info and if you re-enrol the user it asks if you want to restore their grades.
Any ideas on how to amend the query to pick up unenrolled/deleted users from a course?