I've got this report "almost there" but can't figure out how to get the last couple steps. Here is the current query:
SELECT
u.lastname,
u.firstname,
u.email,
uid.data as state,
uid2.data as librarytype,
uid3.data as workplace,
c.shortname,
FROM_UNIXTIME(ue.timecreated, '%m/%d/%Y %h:%i:%s %p') as enrollment_date,
COALESCE((
SELECT
FROM_UNIXTIME(la.timeaccess,'%m/%d/%Y %h:%i:%s %p')
FROM
prefix_user_lastaccess as la
WHERE
u.id = la.userid
AND
c.id = la.courseid
), 'Never Accessed') as last_access
FROM
prefix_user_enrolments as ue,
prefix_enrol as e,
prefix_course as c,
prefix_user as u
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_user_info_field AS uif ON uid.fieldid = uif.id
JOIN prefix_user_info_data AS uid2 ON uid2.userid = u.id
JOIN prefix_user_info_field AS uif2 ON uid2.fieldid = uif2.id
JOIN prefix_user_info_data AS uid3 ON uid3.userid = u.id
JOIN prefix_user_info_field AS uif3 ON uid3.fieldid = uif3.id
WHERE
ue.userid = u.id
AND
ue.enrolid = e.id
AND
e.courseid = c.id
and
uif.shortname = 'state'
and
uif2.shortname = 'librarytype'
and
uif3.shortname = 'workplace'
ORDER BY
u.lastname,
u.firstname,
c.startdate DESC,
c.shortname
It could use some clean-up, probably, but it mostly does what it needs to do. Here is a sample of results:
What I would like to add, if possible, is:
Completion Date: I've been able to use p.timecompleted in other reports, but can't figure out how to integrate it properly into this query.
Time Spent in Course: I realize this is difficult to track, but any indicator of how much time (more or less) a user spent in the course would be useful.
Course Status: I don't actually know if Moodle actually even considers this. Our past LMS had "Not Started, In Progress, and Complete" as options. Any options that might indicate these options in some form would be useful.
Any help is appreciated. Thanks in advance!