Hi all. I'm using meta course enrolments for many courses and I want to report on these. The very excellent progress report from the ad-hoc contributed reports does a great job of showing progress in all courses by user, but if that user is enrolled in a course via a meta course, they are not included in the report.
I've spent some time playing with the report and trying to work out why it only shows manual enrolments, but I have to admit defeat. I'm just not familiar enough with the Moodle schema to work it out.
I'm hoping someone can look at the query and pinpoint where it excludes meta-course enrolments. If that can be done then hopefully a tiny change to the report will be all that is needed.
Very appreciative of any help on this.
Thanks
Paul
----
SELECT u.firstname AS 'First Name'
,
u.lastname AS 'Last Name'
,
c.fullname AS 'Course'
,
FROM_UNIXTIME(ue.timecreated, '%m/%d/%Y') AS 'Enrolled'
,
IFNULL((SELECT DATE_FORMAT(MIN(FROM_UNIXTIME(log.time)),'%m/%d/%Y')
FROM prefix_log log
WHERE log.course=c.id
AND log.userid=u.id), 'Never') AS 'First Access'
,
(SELECT IF(ue.status=0, ' ', 'Withdrawn')) AS 'Withdrawn'
,
IFNULL((SELECT DATE_FORMAT(FROM_UNIXTIME(la.timeaccess), '%m/%d/%Y')
FROM prefix_user_lastaccess la
WHERE la.userid=u.id
AND la.courseid=c.id),'Never') AS 'Last Access',
/*A count of the number of distinct days a student has entered a course*/
IFNULL((SELECT COUNT(DISTINCT FROM_UNIXTIME(log.time, '%m/%d/%Y'))
FROM prefix_log log
WHERE log.course=c.id
AND log.userid=u.id
AND log.action='view'
AND log.module='course'
GROUP BY u.id
),'0') AS '# Days Accessed',
IFNULL((SELECT COUNT(gg.finalgrade)
FROM prefix_grade_grades AS gg
JOIN prefix_grade_items AS gi ON gg.itemid=gi.id
WHERE gi.courseid=c.id
AND gg.userid=u.id
AND gi.itemtype='mod'
GROUP BY u.id,c.id),'0') AS 'Activities Completed'
,
IFNULL((SELECT COUNT(gi.itemname)
FROM prefix_grade_items AS gi
WHERE gi.courseid = c.id
AND gi.itemtype='mod'), '0') AS 'Activities Assigned'
,
/*If Activities completed = activities assigned, show date of last log entry. Otherwise, show percentage complete. If Activities Assigned = 0, show 'n/a'.--*/
(SELECT IF(`Activities Assigned`!='0', (SELECT IF((`Activities Completed`)=(`Activities Assigned`),
/*--Last log entry--*/
(SELECT CONCAT('100% completed ',FROM_UNIXTIME(MAX(log.time),'%m/%d/%Y'))
FROM prefix_log log
WHERE log.course=c.id
AND log.userid=u.id),
/*--Percent completed--*/
(SELECT CONCAT(IFNULL(ROUND((`Activities Completed`)/(`Activities Assigned`)*100,0), '0'),'% complete')))), 'n/a')) AS '% of Course Completed'
,
IFNULL(CONCAT(ROUND((SELECT (IFNULL((SELECT SUM(gg.finalgrade)
FROM prefix_grade_grades AS gg
JOIN prefix_grade_items AS gi ON gi.id=gg.itemid
WHERE gg.itemid=gi.id
AND gi.courseid=c.id
AND gi.itemtype='mod'
AND gg.userid=u.id
GROUP BY u.id,c.id),0)/(SELECT SUM(gi.grademax)
FROM prefix_grade_items AS gi
JOIN prefix_grade_grades AS gg ON gi.id=gg.itemid
WHERE gg.itemid=gi.id
AND gi.courseid=c.id
AND gi.itemtype='mod'
AND gg.userid=u.id
AND gg.finalgrade IS NOT NULL
GROUP BY u.id,c.id))*100),0),'%'),'n/a')
AS 'Quality of Work to Date',
(SELECT IF(`Activities Assigned`!='0',CONCAT(IFNULL(ROUND(((SELECT gg.finalgrade/gi.grademax
FROM prefix_grade_items AS gi
JOIN prefix_grade_grades AS gg ON gg.itemid=gi.id
WHERE gi.courseid=c.id
AND gg.userid=u.id
AND gi.itemtype='course'
GROUP BY 'gi.courseid')*100),0),'0'),'%'),'n/a')) AS 'Final Score (incl xtra credit)'
FROM prefix_user u
JOIN prefix_user_enrolments ue ON ue.userid=u.id
JOIN prefix_enrol e ON e.id=ue.enrolid
JOIN prefix_course c ON c.id = e.courseid
JOIN prefix_context AS ctx ON ctx.instanceid = c.id
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_role AS r ON r.id = e.roleid
WHERE ra.userid=u.id
AND ctx.instanceid=c.id
/*AND ue.status='0'*/ ### "0" FOR active, "1" FOR suspended. Leave commented OUT TO include BOTH.
AND ra.roleid='5' ### "5" = student
AND c.visible='1' ### "1" FOR course visible, "0" FOR hidden
GROUP BY u.id, c.id
ORDER BY u.lastname, u.firstname, c.fullname