Hi all,
I am trying to make a brief user report that our teachers can run from the front page instead of having to go into each individual course.
It returns the user, courses their enrolled in, activities completed, enrol date and completion status and a few other things.
I have got it going somewhat but am struggling with 2 particular things.
1. I only want it to return results where both the teacher and the student are in the same group, at the moment it is returning results regardless, this is an important part as we have multiple schools on the one site. I tried on line (*) thanks to some other help but no good....
2. If the role of the teacher is set at the system level the it doesn't return anything, it will only return if the role has been set within the course itself manually.
If any one out there that is more knowledgeable than me can take a look I'd greatly appreciate it, I am really trying but I feel like when I look at it now its just a big blur, need some fresh insight.
Cheers
Rivv
----------------------------------------
SELECT ra.id,
DATE_FORMAT(FROM_UNIXTIME(ra.timemodified),'%d-%m-%Y')as Enrolment_Date,
c.fullname,
IFNULL(cmc.completed, 0) as 'Activities Completed',
u.firstname,
u.lastname,
DATE_FORMAT(FROM_UNIXTIME(cc.timecompleted),'%d-%m-%Y') AS complete_date,
concat('<a target="_new" href="%%WWWROOT%%/user/profile.php? id=',ra.userid,'">',u.firstname ,' ',u.lastname,'</a>') AS Username
FROM mdl_role_assignments AS ra
JOIN mdl_user u ON ra.userid = u.id
JOIN mdl_context AS ctx ON ctx.id = ra.contextid
JOIN mdl_course c ON c.id = ctx.instanceid
JOIN mdl_enrol e ON e.courseid = c.id
JOIN mdl_user_enrolments ue ON ue.enrolid = e.id AND ue.userid = u.id
******** JOIN (SELECT gms.userid FROM mdl_groups_members gms JOIN mdl_groups_members AS gmu ON gms.groupid = gmu.groupid AND gmu.userid = '41' WHERE gms.userid = '3') sg ON ra.userid = sg.userid
LEFT JOIN mdl_course_completions cc ON cc.course = c.id AND cc.userid = u.id
LEFT JOIN mdl_grade_items gi ON gi.courseid = c.id AND gi.itemtype = 'course'
LEFT JOIN mdl_grade_grades g ON g.itemid = gi.id AND g.userid =u.id
LEFT JOIN (SELECT cmc.userid, cm.course, COUNT(cmc.id) as completed FROM mdl_course_modules_completion cmc, mdl_course_modules cm WHERE cm.visible = 1 AND cmc.coursemoduleid = cm.id AND cmc.completionstate IN ('1','2') GROUP BY cm.course, cmc.userid) cmc ON cmc.course = c.id AND cmc.userid = u.id
WHERE u.id > 0 AND (
c.id IN(SELECT distinct(ctx.instanceid) as id FROM mdl_role_assignments ra
JOIN mdl_context ctx ON ra.contextid = ctx.id
WHERE ra.userid = '41' AND ctx.contextlevel = 50 AND ra.roleid IN ('1','9','2','3','4','10') )
OR
c.id IN(SELECT distinct(c.id) as id FROM mdl_role_assignments ra
JOIN mdl_context ctx ON ra.contextid = ctx.id
JOIN mdl_course c ON c.category = ctx.instanceid
WHERE ra.userid = '41' AND ctx.contextlevel = 40 AND ra.roleid IN ('1','9','2','3','4','10') )
) AND u.deleted = 0 AND u.suspended = 0 AND u.username <> 'guest' AND c.visible = 1 AND ue.status = 0 AND e.status = 0 AND ra.roleid = '5' HAVING u.firstname LIKE 'student' ESCAPE '\\'