Hi,
does anyone know how to create sql reports which show a mentor only information about their assigned users?
It's really important. Thanks for any advice.
What information do you want to include in the report? "information about their assigned users" is very vague.
Basically, I want them to see which contents of a course each student has completed. Similar to the standard Activity completion report, but only with a percentage.
Our code looks like this:
SELECT firstname AS 'Vorname', lastname AS "Nachname", Email, institution AS 'Firma', Course, Progress,
access AS "Last Access"
FROM (SELECT
u.firstname AS firstname,
u.lastname AS lastname,
u.email AS Email,
u.institution AS Institution,
r.shortname AS Role,
c.fullname AS Course,
IFNULL((SELECT DATE_FORMAT(FROM_UNIXTIME(la.timeaccess), '%d.%m.%Y')
FROM prefix_user_lastaccess AS la
WHERE la.userid = u.id
AND la.courseid = c.id), 'Never') AS access,
IFNULL((SELECT COUNT(cm.instance)
FROM prefix_course_modules AS cm
JOIN prefix_course_sections AS cs ON cm.section = cs.id
WHERE cm.course = c.id
AND cm.completion != 0
AND cm.module != 12 /* Textfeld ignorieren */
AND cs.sequence != 'NULL'
AND cs.section >= 1), '0') AS ActivitiesAssigned,
IFNULL((SELECT COUNT(cmc.id)
FROM prefix_course_modules_completion AS cmc
JOIN prefix_course_modules AS cm ON cm.id = cmc.coursemoduleid
JOIN prefix_course_sections AS cs ON cm.section = cs.id
WHERE u.id = cmc.userid
AND c.id = cm.course
AND cm.module != 12 /* Textfeld ignorieren */
AND cmc.completionstate >= 1
AND cs.sequence != 'NULL'
AND cs.section >= 1), '0') AS ActivitiesCompleted ,
(SELECT IF
( ActivitiesAssigned != '0', (SELECT CONCAT(IFNULL(ROUND(( ActivitiesCompleted ) / ( ActivitiesAssigned ) * 100,0), '0'),'%')), 'n/a')
) AS Progress
FROM (prefix_course AS c
JOIN prefix_enrol AS en ON en.courseid = c.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_role_assignments AS ra ON ra.userid = ue.userid
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_role AS r ON r.id = ra.roleid)
WHERE u.suspended = 0
AND (ue.timeend = 0 OR from_unixtime(ue.timeend) >= NOW())
AND c.id = %%COURSEID%%
GROUP BY u.id
ORDER BY u.lastname, u.firstname, c.fullname) AS q
I just need to add something, so that a mentor who sees this report, does only see his assigned users and not all enrolled users of the course.
Our code looks like this:
SELECT firstname AS 'Vorname', lastname AS "Nachname", Email, institution AS 'Firma', Course, Progress,
access AS "Last Access"
FROM (SELECT
u.firstname AS firstname,
u.lastname AS lastname,
u.email AS Email,
u.institution AS Institution,
r.shortname AS Role,
c.fullname AS Course,
IFNULL((SELECT DATE_FORMAT(FROM_UNIXTIME(la.timeaccess), '%d.%m.%Y')
FROM prefix_user_lastaccess AS la
WHERE la.userid = u.id
AND la.courseid = c.id), 'Never') AS access,
IFNULL((SELECT COUNT(cm.instance)
FROM prefix_course_modules AS cm
JOIN prefix_course_sections AS cs ON cm.section = cs.id
WHERE cm.course = c.id
AND cm.completion != 0
AND cm.module != 12 /* Textfeld ignorieren */
AND cs.sequence != 'NULL'
AND cs.section >= 1), '0') AS ActivitiesAssigned,
IFNULL((SELECT COUNT(cmc.id)
FROM prefix_course_modules_completion AS cmc
JOIN prefix_course_modules AS cm ON cm.id = cmc.coursemoduleid
JOIN prefix_course_sections AS cs ON cm.section = cs.id
WHERE u.id = cmc.userid
AND c.id = cm.course
AND cm.module != 12 /* Textfeld ignorieren */
AND cmc.completionstate >= 1
AND cs.sequence != 'NULL'
AND cs.section >= 1), '0') AS ActivitiesCompleted ,
(SELECT IF
( ActivitiesAssigned != '0', (SELECT CONCAT(IFNULL(ROUND(( ActivitiesCompleted ) / ( ActivitiesAssigned ) * 100,0), '0'),'%')), 'n/a')
) AS Progress
FROM (prefix_course AS c
JOIN prefix_enrol AS en ON en.courseid = c.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_role_assignments AS ra ON ra.userid = ue.userid
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_role AS r ON r.id = ra.roleid)
WHERE u.suspended = 0
AND (ue.timeend = 0 OR from_unixtime(ue.timeend) >= NOW())
AND c.id = %%COURSEID%%
GROUP BY u.id
ORDER BY u.lastname, u.firstname, c.fullname) AS q
I just need to add something, so that a mentor who sees this report, does only see his assigned users and not all enrolled users of the course.
Below is the filter I use in the where clause of a mentor report to only show the appropriate mentee user data. Please share if this is helpful. Thanks!
-Ben
--- use after the where clause ---
and u.id in
(SELECT c.instanceid
FROM prefix_context c
join prefix_role_assignments ra on c.id = ra.contextid
WHERE instanceid = u.id AND contextlevel = 30
and ra.userid = '%%USERID%%')
-Ben
--- use after the where clause ---
and u.id in
(SELECT c.instanceid
FROM prefix_context c
join prefix_role_assignments ra on c.id = ra.contextid
WHERE instanceid = u.id AND contextlevel = 30
and ra.userid = '%%USERID%%')
That's exactly what I was looking for! Thank you 