Unable to get course progress percentage | Moodle 3.9

Unable to get course progress percentage | Moodle 3.9

by Gabriel Inbuon -
Number of replies: 3

Hi All,

I try to query course progress % directly from Database but i can't seem to find where it stored. 
Anyone can point me the right direction?

This is what I have so far:

                    SELECT 
                    u.id AS userid, u.idnumber, u.username, u.firstname, u.lastname, u.email, c.fullname AS course,
                    c.id AS course_id,
                    u.institution, cg.name AS category,
                    DATE_FORMAT(FROM_UNIXTIME(u.firstaccess), '%d/%m/%Y %H:%i:%s') AS first_login,
                    (
                        SELECT MAX(qg.grade) FROM mdl_quiz_grades qg
                            LEFT JOIN mdl_quiz  q ON qg.quiz = q.id
                            WHERE q.course = c.id
                      ) AS quiz_progress,
                    FROM_UNIXTIME(ue.timecreated, '%d/%m/%Y') AS enrolled,                    
                    IFNULL((SELECT DATE_FORMAT(MIN(FROM_UNIXTIME(log.time)),'%d/%m/%Y')
                                FROM mdl_log log
                                WHERE log.course=c.id
                                AND log.userid=u.id), NULL
                        ) AS 'first_access',
                    (SELECT IF(ue.status=0, NULL, 'YES')) AS 'withdrawn',
                    IFNULL((SELECT DATE_FORMAT(FROM_UNIXTIME(la.timeaccess), '%d/%m/%Y')
                                    FROM mdl_user_lastaccess la
                                    WHERE la.userid=u.id
                                    AND la.courseid=c.id), NULL
                            ) AS 'last_access',
                    
                    IFNULL((SELECT COUNT(DISTINCT FROM_UNIXTIME(log.time, '%d/%m/%Y'))
                                    FROM mdl_log log
                                    WHERE log.course=c.id
                                    AND log.userid=u.id
                                    AND log.action='view'
                                    AND log.module='course'
                                    GROUP BY u.id),'0'
                            ) AS 'days_accessed',
                    
                    IFNULL((SELECT COUNT(gg.finalgrade) 
                                FROM mdl_grade_grades AS gg 
                                JOIN mdl_grade_items AS gi ON gg.itemid=gi.id
                                WHERE gi.courseid=c.id
                                AND gg.userid=u.id
                                AND gi.itemtype='mod'
                                GROUP BY u.id,c.id),'0'
                            ) AS 'activities_completed',
                    
                    IFNULL((SELECT COUNT(gi.itemname) 
                            FROM mdl_grade_items AS gi 
                            WHERE gi.courseid = c.id
                            AND gi.itemtype='mod'), '0'
                            ) AS 'activities_assigned',
                    
                    (SELECT IF(`activities_assigned`!='0',CONCAT(IFNULL(ROUND(
                        ((SELECT gg.finalgrade/gi.grademax
                            FROM mdl_grade_items AS gi
                            JOIN mdl_grade_grades AS gg ON gg.itemid=gi.id
                            WHERE gi.courseid=c.id
                            AND gg.userid=u.id
                            AND gi.itemtype='course'
                            GROUP BY 'gi.courseid')*100),0),'0'),'%'),'0%')
                    ) AS 'course_progress',
                    
                    IFNULL(
                            ROUND((SELECT (IFNULL((SELECT SUM(gg.finalgrade)
                        FROM mdl_grade_grades AS gg 
                        JOIN mdl_grade_items AS gi ON gi.id=gg.itemid
                        WHERE gg.itemid=gi.id
                        AND gi.courseid=c.id
                        AND gi.itemtype='mod'
                        AND gg.userid=u.id
                        GROUP BY u.id,c.id),0)/(SELECT SUM(gi.grademax)
                        FROM mdl_grade_items AS gi
                        JOIN mdl_grade_grades AS gg ON gi.id=gg.itemid
                        WHERE gg.itemid=gi.id
                        AND gi.courseid=c.id
                        AND gi.itemtype='mod'
                        AND gg.userid=u.id
                        AND gg.finalgrade IS NOT NULL
                        GROUP BY u.id,c.id))*100),0), 0
                        )
                    AS 'quality_of_work',
                    
                    (SELECT IF(`activities_assigned`!='0',IFNULL(ROUND(((SELECT gg.finalgrade/gi.grademax
                    FROM mdl_grade_items AS gi
                    JOIN mdl_grade_grades AS gg ON gg.itemid=gi.id
                    WHERE gi.courseid=c.id
                    AND gg.userid=u.id
                    AND gi.itemtype='course'
                    GROUP BY 'gi.courseid')*100),0),'0'), 0)
                        ) AS 'final_score'

                    FROM mdl_user u
                    JOIN mdl_user_enrolments ue ON ue.userid=u.id
                    JOIN mdl_enrol e ON e.id=ue.enrolid
                    JOIN mdl_course c ON c.id = e.courseid
                    JOIN mdl_context AS ctx ON ctx.instanceid = c.id
                    JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
                    JOIN mdl_role AS r ON r.id = e.roleid
                    JOIN mdl_course_categories cg ON cg.id = c.category
                    
                    WHERE ra.userid=u.id
                    AND ctx.instanceid=c.id
                    AND ra.roleid='5'
                    AND c.visible='1'
                    AND u.institution != ''

                    GROUP BY u.id, c.id
                    ORDER BY u.lastname, u.firstname, c.fullname

Thanks,
Gabriel

Average of ratings: -
In reply to Gabriel Inbuon

Re: Unable to get course progress percentage | Moodle 3.9

by Randy Thornton -
Picture of Documentation writers
Gabriel,

How do you define "progress"? Your query is looking mainly for grades.

However, the course progress report and the progress percentage used in the Course Overview are based on the percentage of the course activities complete, not on grades. You can find the code for this in /completion/classes/progress.php but basically the formula is: (completed activities / total activities with completion ) * 100.

You could of course do the same thing with grades: graded activities / total activities with grades * 100.

But, either way, the database does not store this number anywhere. Reports and other progress indicators calculate it each time, so your code will have to do that as well.
Average of ratings: Useful (1)
In reply to Randy Thornton

Re: Unable to get course progress percentage | Moodle 3.9

by Gabriel Inbuon -
Thanks Randy, that's really helpful but the hardest was to to find which table course activities (user activities) are stored.

I got the course_progress SQL from old moodle post. Anyway, after spending a day looking at the Moodle code and database structure i manage to find it.

I hope it will be helpful for people who are trying to extract Course Progress directly from Database using SQL:

                 SELECT 
                        u.id AS userid, u.idnumber, u.username, u.firstname, u.lastname, u.email, c.fullname AS course,
                        c.id AS course_id,
                        u.institution, cg.name AS category,
                        DATE_FORMAT(FROM_UNIXTIME(u.firstaccess), '%d/%m/%Y %H:%i:%s') AS first_login,
                        IFNULL((
                            SELECT MAX(qg.grade) FROM mdl_quiz_grades qg
                                LEFT JOIN mdl_quiz q ON qg.quiz = q.id
                                WHERE q.course = c.id AND qg.userid = u.id
                        ), 0) AS quiz_progress,
                        FROM_UNIXTIME(ue.timecreated, '%d/%m/%Y') AS enrolled,                    
                        IFNULL((SELECT DATE_FORMAT(MIN(FROM_UNIXTIME(log.time)),'%d/%m/%Y')
                                    FROM mdl_log log
                                    WHERE log.course=c.id
                                    AND log.userid=u.id), NULL
                            ) AS 'first_access',

                        (   
                            SELECT IF(ue.status=0, NULL, 'YES')) AS 'withdrawn',
                            IFNULL((SELECT DATE_FORMAT(FROM_UNIXTIME(la.timeaccess), '%d/%m/%Y')
                                        FROM mdl_user_lastaccess la
                                        WHERE la.userid=u.id
                                        AND la.courseid=c.id), NULL
                        ) AS 'last_access',
                                
                        (
                            SELECT COUNT(*) FROM mdl_course_modules cm
                                WHERE cm.course = c.id
                        ) AS total_modules,
                                
                                (
                                    SELECT COUNT(cmc.id) FROM mdl_course_modules_completion cmc
                                    LEFT JOIN mdl_course_modules cm ON cmc.coursemoduleid = cm.id
                                    WHERE cmc.userid = u.id AND cm.course = c.id
                        ) AS modules_completed,
                        
                        (
                            (100 / (SELECT COUNT(*) FROM mdl_course_modules cm WHERE cm.course = c.id) ) * 
                            (SELECT COUNT(cmc.id) FROM mdl_course_modules_completion cmc
                            LEFT JOIN mdl_course_modules cm ON cmc.coursemoduleid = cm.id
                            WHERE cmc.userid = u.id AND cm.course = c.id)
                        )AS course_progress
                        
                        FROM mdl_user u
                        JOIN mdl_user_enrolments ue ON ue.userid=u.id
                        JOIN mdl_enrol e ON e.id=ue.enrolid
                        JOIN mdl_course c ON c.id = e.courseid
                        JOIN mdl_context AS ctx ON ctx.instanceid = c.id
                        JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
                        JOIN mdl_role AS r ON r.id = e.roleid
                        JOIN mdl_course_categories cg ON cg.id = c.category
                        
                        WHERE ra.userid=u.id
                        AND ctx.instanceid=c.id
                        AND ra.roleid='5'
                        AND c.visible='1'
                        AND u.institution != ''

                        GROUP BY u.id, c.id
                        ORDER BY u.lastname, u.firstname, c.fullname

Average of ratings: -