General developer forum

Generating course completions from SCORM completions after the fact

 
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: -