Hi there
We are using Moodle 3.6.3+ (Build: 20190413). I have a report that needs customising. I have a friendly consultant install a custom certificate with a range of Cert numbers 70,000-80,000. These cert numbers are not being picked up by my completion report, only the moodle cert numbers (eg "hNGsLwVGrn").
Can anyone suggest a solution?
The Report code is:
SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.username as 'MoodleUser',
u.id as 'MoodleID',
c.fullname AS 'Course',
c.id AS 'course ID',
u.idnumber AS 'ID number',
cc.name AS 'Category',
CASE
WHEN gi.itemtype = 'course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname
END AS 'Item Name',
ROUND(gg.finalgrade,2) AS Grade,
DATE_ADD('1970-01-01', INTERVAL gg.timemodified SECOND) AS Time
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
%%FILTER_CATEGORIES:c.category%%
WHERE gi.courseid = c.id and gi.itemname IS NOT NULL and gi.itemname <>'' and ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79
ORDER BY gg.timemodified DESC
Many thanks in advance
Taras