General developer forum

Generating course completions from SCORM completions after the fact

 
Picture of Olli Savolainen
Generating course completions from SCORM completions after the fact
Core developers

Hi,

We're having an issue where we want to use Course recompletion plugin to remove expired completions so users need to recomplete SCORM courses. (The Moodle courses typically only show the scorm module)

However, admins didn't realize in time they need to turn on course completion to generate course completions too based on the SCORM completions. So no we have thousands of SCORM completions in the database that have expired, and need to be removed. However, as the courses don't have course completions, the plugin doesn't find the completions and cannot delete them (along with the SCORM completions).

So what I want to know is: Is there a way to generate course completions based on completed SCORM courses, after the fact? I spent a couple of days writing SQL to insert data into mdl_course_completions. Specifically I'm taking SCORM completion times in mdl_scorm_scoes_track.timemodified and saving them in the corresponding rows, in column mdl_course_completions.timecompleted.

This does not seem to have the desired effect though. The completions don't show in /report/completion/index.php?course=28 (which curiously seems to look for course_module_completions instead of course_completions). They also don't get deleted by the plugin. 

What gives?

Thanks,
Olli

 
Average of ratings: -
Picture of Olli Savolainen
Re: Generating course completions from SCORM completions after the fact
Core developers
This is the SQL I've been using. Any comments welcome. (I know it's ugly already smile)

UPDATE mdl_course_completions as mcc2
SET timecompleted=subquery.pvm
FROM (SELECT st.id as STID, c.fullname as Course, 
u.firstname as First, u.email as Email, u.lastname as Last,
 sc.name as scormnimi, 
st.timemodified as pvm, 
ss.title as scotitle, 
c.id as cid,
u.id as uid, 
mcc.timecompleted,mcc.userid as mccuserid,mcc.course as mcccourse
FROM mdl_scorm_scoes_track AS st 
JOIN mdl_user AS u ON st.userid=u.id 
JOIN mdl_scorm AS sc ON sc.id=st.scormid 
JOIN mdl_scorm_scoes ss ON ss.id=st.scoid 
JOIN mdl_course AS c ON c.id=sc.course 
LEFT OUTER JOIN mdl_cohort_members AS com ON com.userid=st.userid 
LEFT OUTER JOIN mdl_cohort AS co ON co.id = com.cohortid 
LEFT OUTER JOIN mdl_scorm_scoes_track AS ss2 ON (ss2.element = 'cmi.core.score.raw' AND ss2.userid = st.userid AND ss2.scormid = st.scormid AND ss2.scoid = st.scoid AND ss2.Attempt = st.Attempt) 
LEFT OUTER JOIN mdl_course_completions AS mcc ON mcc.course=c.id AND st.userid=mcc.userid
LEFT OUTER JOIN mdl_scorm_scoes_track AS st2 ON ((st2.element = 'cmi.core.total_time' OR st2.element = 'cmi.total_time') AND st2.userid = st.userid AND st2.scormid = st.scormid AND st2.scoid = st.scoid AND st2.Attempt = st.Attempt) 
WHERE st.element = 'cmi.core.lesson_status'  AND  (st.value = 'completed' OR st.value = 'passed')
AND mcc.timecompleted IS NULL AND mcc.userid IS NOT NULL) as subquery
WHERE mcc2.course=subquery.mcccourse AND mcc2.userid=subquery.mccuserid;

INSERT INTO mdl_course_completions (userid, course, timecompleted)
SELECT 
u.id as uid, 
c.id as cid,
MAX(st.timemodified) as pvm
FROM mdl_scorm_scoes_track AS st 
JOIN mdl_user AS u ON st.userid=u.id 
JOIN mdl_scorm AS sc ON sc.id=st.scormid 
JOIN mdl_scorm_scoes ss ON ss.id=st.scoid 
JOIN mdl_course AS c ON c.id=sc.course 
LEFT OUTER JOIN mdl_cohort_members AS com ON com.userid=st.userid 
LEFT OUTER JOIN mdl_cohort AS co ON co.id = com.cohortid 
LEFT OUTER JOIN mdl_scorm_scoes_track AS ss2 ON (ss2.element = 'cmi.core.score.raw' AND ss2.userid = st.userid AND ss2.scormid = st.scormid AND ss2.scoid = st.scoid AND ss2.Attempt = st.Attempt) 
LEFT OUTER JOIN mdl_course_completions AS mcc ON mcc.course=c.id AND st.userid=mcc.userid
LEFT OUTER JOIN mdl_scorm_scoes_track AS st2 ON ((st2.element = 'cmi.core.total_time' OR st2.element = 'cmi.total_time') AND st2.userid = st.userid AND st2.scormid = st.scormid AND st2.scoid = st.scoid AND st2.Attempt = st.Attempt) 
WHERE st.element = 'cmi.core.lesson_status'  AND   (st.value = 'completed' OR st.value = 'passed')
AND mcc.timecompleted IS NULL AND mcc.userid IS NULL GROUP BY uid, cid ORDER BY uid ASC, cid ASC 
 
Average of ratings: -