Moodle: exporter les notes via sql

Voici les requêtes intéressantes à réaliser via phpmyadmin pour obtenir les notes des étudiants au travers des différents cours où ils sont inscrits:

SELECT
c.id as Course_Id,
c.shortname AS shortname,
c.fullname AS fullname,
u.id AS User_Id,
u.firstname AS firstname, 
u.lastname AS lastname, 
u.email AS email, 
COALESCE(ROUND(gg.finalgrade,2),0) as finalgrade
FROM mdl_user u
INNER JOIN mdl_role_assignments ra ON ra.userid = u.id
INNER JOIN mdl_context ct ON ct.id = ra.contextid
INNER JOIN mdl_course c ON c.id = ct.instanceid
INNER JOIN mdl_role r ON r.id = ra.roleid
LEFT JOIN
(
SELECT
u.id AS userid,c.id as courseid,
g.finalgrade AS finalgrade
FROM mdl_user u
JOIN mdl_grade_grades g ON g.userid = u.id
JOIN mdl_grade_items gi ON g.itemid =  gi.id
JOIN mdl_course c ON c.id = gi.courseid where gi.itemtype = 'course'
) gg ON gg.userid = u.id and gg.courseid = c.id

Source

:

Melvyn Gomez: https://moodle.org/mod/forum/discuss.php?d=326345

Categories

Une autre, requête, qui malheureusement renvoient erreurs. Elle inclue les catégories ce qui pourra donner des pistes aux explorateurs.

select mc.shortname as course_Id,mu.username, 
case when ABS(ROUND(mgg.finalgrade, 0) - mgg.finalgrade) <= 0.0000005 then cast(mgg.finalgrade as text) else 'Pending...' end as final_grade,
mgi.itemname
from mdl_grade_grades mgg
join mdl_user mu on mgg.userid=mu.id
join mdl_user_enrolments mue on mue.userid=mu.id
join mdl_grade_items mgi on mgg.itemid=mgi.id
join mdl_course mc on mc.id=mgi.courseid
join mdl_enrol me on mue.enrolid=me.id and me.courseid=mc.id
join mdl_role_assignments mra on mu.id=mra.userid
join mdl_role mr on mra.roleid=mr.id
where mc.category = '1' and -- mdl_course_categories.id = '1'; id=1 => name='Spring 2012' as an example 
mr.shortname='student' and -- only get people enrolled with a 'student' role in the course
mgi.itemname ilike '%Final%Grade%'; --anything with 'final' and 'grade' in it regardless of case and surrounding text

Source

Mike Buchanon: https://gist.github.com/mikebuchanon/2589794

Moodle dans powerBI