CODEWITHSUNDEEP
sqlmoodlescorm
Jay Qadan
Jay Qadan

Reputation: 87

Moodle SCORM SQL

What table and field I need to use in SQL to find SCORM activities that reports back score to Moodle and exclude the SCORM objects that are set as non scoring. Basically looking for the SCORM settings field: Require Minimum Score in SQL

The query:

    SELECT
    CONCAT(u.firstname,' ',u.lastname ) AS 'fullname', 
    cc.name AS 'Category',
    c.fullname AS 'Course', 
    gi.itemname AS 'Item Name',
    ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS 'Percentage',
    asgm.status as 'Status',
    p.status AS 'Completed',
    gi.itemmodule as 'Type',
    s.completionscorerequired AS require_minimum_score
    FROM prefix_course AS c
    JOIN prefix_course_categories AS cc ON cc.id = c.category
    JOIN prefix_context AS ctx ON c.id = ctx.instanceid
    JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
    JOIN prefix_user AS u ON u.id = ra.userid
    JOIN prefix_grade_items AS gi ON gi.courseid = c.id
    JOIN prefix_scorm AS s ON c.id = s.course
    LEFT JOIN prefix_grade_grades AS gg ON gi.id = gg.itemid AND gg.userid = u.id
    LEFT JOIN prefix_course_completions AS p ON p.userid = u.id AND p.course = c.id
    LEFT JOIN (
      SELECT asgm.status as status, c.id as cid, u.id as uid,  gi.id as giid
      FROM prefix_course AS c
           JOIN prefix_course_categories AS cc ON cc.id = c.category
           JOIN prefix_context AS ctx ON c.id = ctx.instanceid
           JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
           JOIN prefix_user AS u ON u.id = ra.userid
           JOIN prefix_grade_items AS gi ON gi.courseid = c.id
           JOIN prefix_assign AS asg ON gi.iteminstance = asg.id and asg.course = c.id
           JOIN prefix_assign_submission AS asgm ON asg.id = asgm.assignment and asgm.userid = u.id 
      ) AS asgm ON c.id = asgm.cid and u.id = asgm.uid  and gi.id = asgm.giid

    WHERE (gi.itemmodule = 'quiz' OR gi.itemmodule= 'assign' OR gi.itemmodule= 'scorm') AND c.visible=1

Upvotes: 0

Views: 1583

Answers (1)

Russell England
Russell England

Reputation: 10241

SELECT s.id AS scormid, s.name AS scormname,
       s.completionscorerequired AS require_minimum_score,
       c.id AS courseid, c.fullname AS coursename
FROM mdl_scorm s
JOIN mdl_course c ON c.id = s.course

Upvotes: 1

Related Questions