Segue o comando SQL que extrai a relação de todos os alunos e a nota final em todos curso da Plataforma Moodle. Além disso, extrai informação dos certificados emitidos pelo plugin certificate.
Comando com formatação da data (recomendado para MySQL)
SELECT DISTINCT rs.id,u.firstname,u.lastname,u.username,u.institution,u.department,u.email,u.city,c.id AS courseid,c.fullname AS course,ct.name AS category,r.shortname AS roleshortname,g.finalgrade,from_unixtime(g.timemodified, '%d/%m/%Y %H:%i:%s') AS gradetimeupdate,from_unixtime(ue.timestart, '%d/%m/%Y %H:%i:%s') AS enroltimestart,from_unixtime(ue.timeend, '%d/%m/%Y %H:%i:%s') AS enroltimeend,cf.name AS certificatename,cfi.code AS certificatecode,from_unixtime(cfi.timecreated, '%d/%m/%Y %H:%i:%s') AS datecertificate FROM mdl_role_assignments rs INNER JOIN mdl_role r ON rs.roleid=r.id INNER JOIN mdl_user u ON rs.userid=u.id INNER JOIN mdl_context e ON rs.contextid=e.id INNER JOIN mdl_enrol en ON e.instanceid=en.courseid INNER JOIN mdl_course c ON c.id=en.courseid INNER JOIN mdl_course_categories ct ON ct.id=c.category INNER JOIN mdl_user_enrolments ue ON ( en.id=ue.enrolid AND rs.userid=ue.userid ) LEFT JOIN mdl_grade_items i ON c.id=i.courseid LEFT JOIN mdl_grade_grades g ON (g.itemid=i.id AND rs.userid=g.userid ) LEFT JOIN mdl_certificate cf ON cf.course=c.id LEFT JOIN mdl_certificate_issues cfi ON (cfi.certificateid=cf.id AND cfi.userid=rs.userid) WHERE e.contextlevel=50 AND (i.itemtype = 'course' OR i.itemtype IS NULL ) AND c.visible= 1 AND ue.status = 0 AND en.status = 0 AND u.deleted=0 AND u.confirmed=1
Comando sem função de formatação da data (recomendado para PostgresSql)
SELECT DISTINCT rs.id,u.firstname,u.lastname,u.username,u.institution,u.department,u.email,u.city,c.id AS courseid,c.fullname AS course,ct.name AS category,r.shortname AS roleshortname,g.finalgrade,g.timemodified AS gradetimeupdate,ue.timestart AS enroltimestart, ue.timeend AS enroltimeend,cf.name AS certificatename,cfi.code AS certificatecode,cfi.timecreated AS datecertificate FROM mdl_role_assignments rs INNER JOIN mdl_role r ON rs.roleid=r.id INNER JOIN mdl_user u ON rs.userid=u.id INNER JOIN mdl_context e ON rs.contextid=e.id INNER JOIN mdl_enrol en ON e.instanceid=en.courseid INNER JOIN mdl_course c ON c.id=en.courseid INNER JOIN mdl_course_categories ct ON ct.id=c.category INNER JOIN mdl_user_enrolments ue ON ( en.id=ue.enrolid AND rs.userid=ue.userid ) LEFT JOIN mdl_grade_items i ON c.id=i.courseid LEFT JOIN mdl_grade_grades g ON (g.itemid=i.id AND rs.userid=g.userid ) LEFT JOIN mdl_certificate cf ON cf.course=c.id LEFT JOIN mdl_certificate_issues cfi ON (cfi.certificateid=cf.id AND cfi.userid=rs.userid) WHERE e.contextlevel=50 AND (i.itemtype = 'course' OR i.itemtype IS NULL ) AND c.visible= 1 AND ue.status = 0 AND en.status = 0 AND u.deleted=0 AND u.confirmed=1
Para exibir apenas as lista dos alunos que emitirem o certificado, adicione no final o seguinte comando: AND cfi.timecreated > 0
Esse relatório pressupõe que você usa apenas uma instancia do certificate no curso. Caso houver mais de um, pode ter muita replicação de dados.