O comando SQL a seguir gera o seguinte relatório:
- Relação dos alunos que concluíram os cursos no Moodle, tendo como critério, o rastreamento de conclusão do curso configurado nos cursos;
- Informação sobre nota final do curso;
- Informação sobre data de início e término da inscrição, bem como o status de inscrição
SELECT rs.id,u.firstname,u.lastname,u.email,u.city,c.id AS courseid,c.fullname AS course,ct.name AS category,r.name AS rolename,r.shortname AS roleshortname,g.finalgrade,i.grademax,g.rawgrademax,g.timecreated,g.timemodified,en.status AS methodstatus,en.enrol AS methodplugin,ue.status AS enrolstatus,ue.timestart,ue.timeend,p.timecompleted AS coursecompletedtime 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_course_completions p ON (p.course=c.id AND p.userid=rs.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 ) WHERE e.contextlevel=50 AND rs.roleid IN (5) AND (i.itemtype = 'course' OR i.itemtype IS NULL ) AND en.status = 0 AND p.timecompleted > 0 ORDER BY g.timemodified