Global - Quiz Attempts that are in progress - Abandoned Attempts

Global - Quiz Attempts that are in progress - Abandoned Attempts

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

Hello MySQL experts,

The delivered Grade>Results report is perfect to find the abandoned attempts, but I am tasked with examining 200+ courses.

I heavily borrowed for the ad-hoc contributed reports to get to this - but it does not filter as much as I need.

I only want incomplete, quiz attempts, unfortunately I am not skilled enough to tweak this query - What am I missing to exclude the activities that are not quizzes?

SELECT
u.username As 'User',
c.shortname AS 'Course',
m.name AS Activitytype,
CASE
    WHEN m.name = 'quiz'  THEN (SELECT name FROM prefix_quiz WHERE id = cm.instance)
END AS Activityname,
CASE
    WHEN cm.completion = 0 THEN '0 None'
END AS Activtycompletiontype,
CASE
   WHEN cm.instance = 0 THEN 'In Progress'
END AS 'Progress',
DATE_FORMAT(FROM_UNIXTIME(cmc.timemodified), '%Y-%m-%d %H:%i') AS 'When'
FROM prefix_course_modules_completion cmc
JOIN prefix_user u ON cmc.userid = u.id
JOIN prefix_course_modules cm ON cmc.coursemoduleid = cm.id
JOIN prefix_course c ON cm.course = c.id
JOIN prefix_modules m ON cm.module = m.id
WHERE u.id > 2
and (c.visible = 1)
AND u.deleted = 0 AND u.suspended = 0
%%FILTER_USERS:u.username%%
%%FILTER_SEARCHTEXT:m.name:~%%
%%FILTER_STARTTIME:cmc.timemodified:>%% %%FILTER_ENDTIME:cmc.timemodified:<%%
ORDER BY u.username



Average of ratings: -
In reply to Shirley Gregorczyk

Re: Global - Quiz Attempts that are in progress - Abandoned Attempts

by Randy Thornton -
Picture of Documentation writers
Shirley,

This looks like it is trying to take the long way around and not getting there.

There is a table just for quiz attempts with their status, but that in not in the query. A query that starts with something like

SELECT * FROM prefix_quiz_attempts WHERE state = 'inprogress'

or perhaps WHERE state != 'finished'

(since the status options are: finished, overdue, inprogress, abandoned) would be the key part I would think. Then you can easily join the user and quiz tables from the quiz_attempts columns. The timefinish should also be 0 if attempt was not finished so in that case timemodified was the last time they answered a question.
Average of ratings: Useful (2)
In reply to Randy Thornton

Re: Global - Quiz Attempts that are in progress - Abandoned Attempts

by Shirley Gregorczyk -
Picture of Particularly helpful Moodlers
Yes, I was taking the longest path possible and not getting where I want to go.
Thank you so very much, for redirecting me.
Average of ratings: -
In reply to Shirley Gregorczyk

Re: Global - Quiz Attempts that are in progress - Abandoned Attempts

by Randy Thornton -
Picture of Documentation writers
No problem.

Every once in a while, some part of Moodle has really well thought out data structures. Quiz is one of those.

(Assignment is not sad)
Average of ratings: -
In reply to Randy Thornton

Re: Global - Quiz Attempts that are in progress - Abandoned Attempts

by Dave Set -

I have upload a joomla training course for students and I want to evaluate their joomla web assignments via plugin  Is their any plugin to evaluate joomla web content in moodle?

Average of ratings: -
In reply to Randy Thornton

Re: Global - Quiz Attempts that are in progress - Abandoned Attempts

by Shirley Gregorczyk -
Picture of Particularly helpful Moodlers
Dear Experts,
I have been updating and testing my query against my expected results, but I still cannot seem to exclude students who were unenrolled.
The student has moved to a new job assignment and no longer is required to take the course. The enrollment was removed, but somehow the query is still "correctly" reporting that the student has an abandoned attempt. Correct, but not really what I intended. How do I exclude student who are no longer enrolled?

SELECT
CONCAT(u.firstname, ' ', u.lastname) AS Employee,
c.fullname,
state,
timefinish
FROM prefix_quiz_attempts as qa
JOIN prefix_user u ON qa.userid = u.id
JOIN prefix_quiz AS q ON q.id = qa.quiz
INNER JOIN prefix_course c ON q.course = c.id
INNER JOIN prefix_enrol en ON en.courseid = c.id
WHERE state = 'abandoned'
and timefinish = 0
AND u.deleted = 0 AND u.suspended = 0
AND (c.visible =1)
%%FILTER_COURSEENROLLEDSTUDENTS%%
Average of ratings: -
In reply to Shirley Gregorczyk

Re: Global - Quiz Attempts that are in progress - Abandoned Attempts

by Randy Thornton -
Picture of Documentation writers
Shirley,

Probably the easiest way - that is the way of least disruption to the existing query - is to add and AND clause to the WHERE which would be a sub-select statement that limits the results to only students enrolled in the course. You already have the user and course, you just need to check for the enrollment exists. There's a number of examples with code you can borrow over the the contributed page, e.g. https://docs.moodle.org/311/en/ad-hoc_contributed_reports#Students_in_all_courses_of_some_institute
Average of ratings: -