Hi all, thanks for reading this thread. Not sure if this is possible but here is my question.
I'm trying to generate a custom report. There are some custom user fields in the database but I guess it didn't follow the best practices (not me!). Anyway, data are stored in the mdl_user_info_data table, but I'm having hard time to figure out how to complete my SQL code to include all the custom user field data.
Here is the SQL codes that I have now:
SELECT u.firstname AS 'First name' , u.lastname AS 'Surname', c.fullname AS 'Course', u.id AS 'ID number', u.institution AS 'Institution', u.department AS 'Department', u.email AS 'Email address', da.data AS 'states/tribes',
CASE WHEN gi.itemtype = 'Course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname
END AS 'Item Name', ROUND(gg.finalgrade,2) AS Score,ROUND(gg.rawgrademax,2) AS MAX, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS Percentage,
IF (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Yes' , 'No') AS Pass
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_user_info_data as da ON da.userid = u.id
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
WHERE gi.courseid = c.id AND da.fieldid=6 AND c.id = 90 AND gi.itemname = 'Assessment'
ORDER BY u.id ASC
This is what the custom user profile fields and the table looks like:
So basically, I need to pull out fieldid=6 data and fieldid=9 data, then use the checkbox to map out the demographic result (e.g. if the fieldid=11 is 1, then that user is Asian, if fieldid=14 is 1, then the user is white...etc.).
This is a sample report that I'm trying to generate:
So the "Best describes you" is the fieldid 10~15. In my current query I already covered tribe/state (da.fieldid=6), but I still need to include the races info and fieldid=9 (Hispanic or Latino). Any idea how to complete this query so that it can include those fields? Thanks!
I think I will worry about incorporate the Questionnaire result later...