-- examen
SELECT z.username as code_tuteur, u.username as code_permanent, gg.userid, gi.courseid, c.shortname as cours, gi.itemname as devoir, gg.rawgrade as note, SUBSTR(gi.idnumber, 3) as note_deposee_serie, ee.serie_examen, from_unixtime(gg.timemodified) as date_note
FROM moodle.mdl_grade_grades gg
JOIN moodle.mdl_grade_items gi ON gg.itemid = gi.id
JOIN moodle.mdl_enrol_etudiant ee ON gg.userid = ee.userid
JOIN moodle.mdl_course c ON gi.courseid = c.id
JOIN moodle.mdl_user u ON gg.userid = u.id
JOIN moodle.mdl_user z ON gg.usermodified = z.id
WHERE gi.idnumber like 'E%' 
AND gi.idnumber is not null
AND gi.itemmodule = 'assign'
AND SUBSTR(gi.idnumber, 3)<>'' 
AND SUBSTR(gi.idnumber, 3) != ee.serie_examen
AND gg.rawgrade is not null
-- AND gg.timemodified > unix_timestamp('2016-01-01')
AND gg.userid = ee.userid
AND gi.courseid = ee.courseid
ORDER BY from_unixtime(gg.timemodified) DESC;

-- devoir
SELECT z.username as code_tuteur, u.username as code_permanent, gg.userid, gi.courseid, c.shortname as cours, gi.itemname as devoir, gg.rawgrade as note, SUBSTR(gi.idnumber, 3) as note_deposee_serie, ee.serie_devoir, from_unixtime(gg.timemodified) as date_note
FROM moodle.mdl_grade_grades gg
JOIN moodle.mdl_grade_items gi ON gg.itemid = gi.id
JOIN moodle.mdl_enrol_etudiant ee ON gg.userid = ee.userid
JOIN moodle.mdl_course c ON gi.courseid = c.id
JOIN moodle.mdl_user u ON gg.userid = u.id
JOIN moodle.mdl_user z ON gg.usermodified = z.id
WHERE gi.idnumber like 'D%' 
AND gi.idnumber is not null
AND gi.itemmodule = 'assign'
AND SUBSTR(gi.idnumber, 3)<>'' 
AND SUBSTR(gi.idnumber, 3) != ee.serie_devoir
AND gg.rawgrade is not null
-- AND gg.timemodified > unix_timestamp('2016-01-01')
AND gg.userid = ee.userid
AND gi.courseid = ee.courseid
ORDER BY from_unixtime(gg.timemodified) DESC;