Course Recomplete Query-Needs Improvement

Course Recomplete Query-Needs Improvement

by Shirley Gregorczyk -
Number of replies: 11
Picture of Particularly helpful Moodlers

Hello,

I have a simple query to have an understanding of when the Course Recomplete plugin has updated (deleted) a student's current course completion to trigger retraining. The query below works in a limited way, Row 6 returns the values or 0 (zero) or 1 because of the IS Null statement instead of the date I was attempting to retrieve. Any assistance would be appreciated.

Kind Regards.

SELECT
course.fullname course,
CONCAT(user.firstname, ' ', user.lastname) AS 'Name',
FROM_UNIXTIME(completions.timeenrolled, '%m-%d-%Y') AS 'DateEnrolled',
FROM_UNIXTIME(completions.timecompleted, '%m-%d-%Y') AS 'DateCompleted',
FROM_UNIXTIME(completions.timecompleted, '%m-%d-%Y') IS NULL AS 'Notified'
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

AND user.deleted = 0 AND user.suspended = 0

WHERE (course.id = 90)
and (course.visible = 1)

ORDER BY DateCompleted
##%%course.fullname%%
%%FILTER_COURSEENROLLEDSTUDENTS%%

Average of ratings: -
In reply to Shirley Gregorczyk

Re: Course Recomplete Query-Needs Improvement

by Randy Thornton -
Picture of Documentation writers
Shirley,

So what this plugin does is wipe out the timestamp for the original course completion when it is ready to start the process of having the user redo the course? Looks like it.

Anyway, Line 5 and 6 are pulling the same piece of data, which can either be NULL or have some data (a timestamp) but not both. I see a couple of code issues.

- You can't use the IS NULL in that way since it is an operator, you need some sort of conditional logic here to test IF it is null. You can use an IF or a CASE to do that.

- Also, the FROM_UNIXTIME just grabs the timestamp but to do the formatting you need to use (assuming this is MySQL) the DATE_FORMAT function first to wrap it, so DATE_FORMAT( FROM_UNIXTIME(completions.timecompleted, '%m-%d-%Y'))

Personally, I usual combine lines / columns like that together if they might be null (or empty or have white space etc) or may have data. Then I sort my nulls to the front if I need to see those first. Altogether then, something like:

...
CASE
WHEN completions.timecompleted IS NOT NULL THEN DATE_FORMAT( FROM_UNIXTIME(completions.timecompleted, '%m-%d-%Y'))
ELSE '-Notified-'
END AS 'Completion'
...
ORDER BY Completion

Of course, you may want to have this as two columns anyway. In that case, you can use a CASE or IF twice: once to show the date if it is not null and once again to test that it is null and show some status indicator for the notification.

Hope this is helpful (and not full of syntax typos)

Randy
Average of ratings: Useful (1)
In reply to Randy Thornton

Re: Course Recomplete Query-Needs Improvement

by Shirley Gregorczyk -
Picture of Particularly helpful Moodlers
Hello Randy,
Thank you so very much for sharing your knowledge and experience with SQL.

Yes the plugin, wipes out the grade and completion entirely and no graceful way for an administrator to know which/when the students need to recomplete the course. I have been checking the event logs for the courses with the recompletion rule, but I needed to find a better, long-term solution.
Kind Regards,
Shirley
Average of ratings: -
In reply to Shirley Gregorczyk

Re: Course Recomplete Query-Needs Improvement

by Randy Thornton -
Picture of Documentation writers
Ah yes, then the obvious problem is that when the timecompleted field is NULL that can mean the user is working on the course for the first time as well as a subsequent time: this field is always NULL until the user actually completes the course and gets a timestamp.

So you don't actually know what the NULL means: are they are working on the course the first time or are they completed once and this is then a subsequent attempt at the course is about to start off. NULL really means is "not complete on the current attempt (but I can't tell you which attempt that is!)."

Does the plugin itself actually track the user attempts elsewhere? Does it know which attempt at the course this is for the user, second, third, twentieth? Does it save past completion dates somewhere at all before wiping them out? Does it also set to NULL the previous timeenrolled and timestarted when a new attempt is starting up again? (It should, otherwise your progress stats will be way off.)

Or is it removing the database row from the previous attempts altogether and then adding a new row for this particular course and user (since there can only be one row in the table for the same user/course combination.)


Average of ratings: Useful (1)
In reply to Randy Thornton

Re: Course Recomplete Query-Needs Improvement

by Shirley Gregorczyk -
Picture of Particularly helpful Moodlers
These are all very good questions! You understand my query issue completely, there is no way to discern the never completed from the retraining need. Up until recently, the courses with the recompletion condition had a very small training audience, that was easy to find the "reset" in the course events logs. Now, that we turned on the recomplete condition for courses with large enrollments, and students with a wide range of start dates, it is a little challenging without a query.

So far, I have never needed to ventured into the table data. However, the plugin does have the option to "Archive completion data." I always select the option, so the data row should be preserved in the tables.

Additionally, the "attempts" can be viewed on the student's quiz history and individual student quiz attempts page, which verifies the attempt is retrained. Also, since we use learning plans and competencies, we are able to view each time the competency is awarded.

Your last statement, makes me wonder how the "Archive" data is being stored if only one row can be for the same user/course combination?
Average of ratings: -
In reply to Shirley Gregorczyk

Re: Course Recomplete Query-Needs Improvement

by Shirley Gregorczyk -
Picture of Particularly helpful Moodlers
I just reviewed all of the plug-in documentation and various discussion.

The following information is cleared from the course during recompletion:
•All activity grades cleared (and saved to standard grade history tables.)
•All activity completion and course completion flags removed. (with the option to archive this information)

I guess I need to know if the archive option preserve a date/time stamp when the "Course completion flag is removed"?
Average of ratings: -
In reply to Shirley Gregorczyk

Re: Course Recomplete Query-Needs Improvement

by Randy Thornton -
Picture of Documentation writers
Right. Really, for the big picture, it depends on how much you need to save that older completion and other information. Since it is removing a lot of data so the user can retake the course, then as long as you have a copy of that data you need - grades, completion dates, etc. - then you are good. You can extract that in reports. Or maybe you need all the student data via a backup for audit compliance of some sort.

Regarding the report, where you can't be sure if a NULL completion means the user is doing the course for the first time or second/subsequent time. One quick and dirty way to work around this would be to show the dates. You could probably figure out from the dates in the timeenrolled and timestarted columns whether this is a current "re-attempt" or an old one. So, for instance, if you are doing a re-enrollment once a year for certification type re-compliance, it may be clear the user is re-taking it. Try just adding those dates to the columns and see if that is of any real help.

Another avenue to look at is exactly what you say: is the plugin's archive option saving something somewhere that tells you for sure that the user has done the course before? Does it do this each time they redo the course? Does that have date and time stamps?

You can try this experiment in a test course: do the re-enrollment with the archive off once and then again with it on for some test users, and then look in the database to see what it does. The archive data should be somewhere, and it won't be in the course_completion table, but in some table the plugin has for its own (probably/hopefullysmile.
Average of ratings: Useful (1)
In reply to Randy Thornton

Re: Course Recomplete Query-Needs Improvement

by Randy Thornton -
Picture of Documentation writers
Also, you can always find through the log how many times a user actually was enrolled in the same course. So, wherever all the user data may be archived off to (or not), the fact of the enrollment over time is still logged with the role_assign event.
Average of ratings: Useful (1)
In reply to Randy Thornton

Re: Course Recomplete Query-Needs Improvement

by Shirley Gregorczyk -
Picture of Particularly helpful Moodlers
Thank you Randy! You have given great material to work with. Going to spend sometime poking the data with my query.
Thank you for all your support and guidance.
Kind Regards
Average of ratings: -
In reply to Shirley Gregorczyk

Re: Course Recomplete Query-Needs Improvement

by Randy Thornton -
Picture of Documentation writers
Shirley,

Most welcome. It is small recompense for your help with competencies smile Thanks!

Randy
Average of ratings: -
In reply to Randy Thornton

Re: Course Recomplete Query-Needs Improvement

by Donatus Abwao -

Hello Team,


I am a moodle administrator and was looking for a report that will give me who has enrolled students in a course.

The current course design is such that I have more than one Teacher in a course with 800 students, I wanted to know which Teacher has enrolled which student. This way I would display enrollment reports by individual Teacher. 

Thank you and Let me know how I can be able to do this.

Average of ratings: -