Hi,
I'm trying to set up a report, using the configurable reports block, that gives me:
-All new instances of where a student has commenced a moodle course (in this case, attempted or completed at least one assessment), since a given date.
We need to be able to update the status of a student's enrolment once they have started studying a moodle course in our student management system. We can enrol students using this system (it has some integration), but we need to then update the status from 'Enrolled' to 'Commenced' once they actually start studying the course.
I've tried doing this using the 'timestarted' column of the '_course_completions' table, but this doesn't seem to be consistently set. There are many instances where the 'timestarted' value is 0 despite the student having started the course, so this isn't providing the results that I need.
I was thinking it could be done by checking if at least one assessment in a course has been submitted/has a grade associated with it, but I just don't know SQL well enough to do that at the moment, so was hoping someone here could help. We also use both assignments and quizzes in most of our courses, so it can't rely on checking the table(s) of one or the other of those.
Any help would be greatly appreciated, let me know if more information would be required as well.
Thanks,
Andrew
-I've added the SQL that I'm currently using that isn't working for us (timestarted in many instances is set at 0, so they just don't get included in this report). I'm not sure if it'll help, but it's here anyways.
SELECT
user.firstname AS 'Firstname',
user.lastname AS 'Lastname',
course.fullname AS 'Course',
FROM_UNIXTIME(completions.timestarted, '%Y-%m-%d %h:%i') AS 'TimeStarted',
user.id AS 'MoodleID',
FROM_UNIXTIME(completions.timeenrolled, '%Y-%m-%d') AS 'TimeEnrolled',
FROM_UNIXTIME(completions.timecompleted, '%Y-%m-%d') AS 'TimeCompleted'
FROM prefix_course_completions AS completions
JOIN prefix_course AS course ON completions.course = course.id
JOIN prefix_user AS user ON completions.userid = user.id
WHERE completions.timestarted > UNIX_TIMESTAMP('2017-11-30')
AND user.deleted = 0 AND user.suspended = 0
ORDER BY completions.timestarted DESC