HI, I want to run all ungraded quiz reports in moodle. I am using SQL query to run this report but when I am trying it's not running, As I am running Moodle 3.10.3 and the 10.3.25-MariaDB - MariaDB Server.
The query I am running is as follow can you please guide me if I am running this correct?
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
,concat('<a target="_new" href="%%WWWROOT%%/mod/assignment/index.php?id=',c.id,'">מטלות</a>') AS Assignments
,concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/index.php?id=',c.id,'">בחנים</a>') AS 'Quizzes'
,(SELECT COUNT(*)
FROM prefix_course_modules cm
JOIN prefix_modules as m ON m.id = cm.module
WHERE m.name LIKE 'quiz' AND cm.course = c.id
GROUP BY cm.course
) AS 'nQuizzes'
,(SELECT COUNT(*)
FROM prefix_quiz_attempts AS qa
JOIN prefix_quiz AS q ON q.id = qa.quiz
WHERE q.course = c.id
AND qa.timefinish = 0
GROUP BY q.course) AS 'unFinished Quiz attempts'
,(SELECT COUNT(*)
FROM prefix_quiz_attempts AS qa
JOIN prefix_quiz AS q ON q.id = qa.quiz
WHERE q.course = c.id
AND qa.timefinish > 0
GROUP BY q.course) AS 'finished quiz attempts'
,(SELECT Count( ra.userid ) AS Users
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5
AND ctx.instanceid = c.id
) AS nStudents
,(
SELECT count(a.id)
FROM prefix_assignment AS a
JOIN prefix_course_modules AS cm ON a.course = cm.course
WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
) nAssignments
,(
SELECT count(*)
FROM prefix_assignment AS a
WHERE a.course = c.id AND FROM_UNIXTIME(a.timedue) > NOW()
GROUP BY a.course
) 'Open <br/>Assignments'
, CONCAT(ROUND( (100 / iAssignments ) * iOpenAssignments ) ,'%') 'unFinished <br/>Assignments <br/>(percent)'
,(
SELECT count(asb.id)
FROM prefix_assignment_submissions AS asb
JOIN prefix_assignment AS a ON a.id = asb.assignment
JOIN prefix_course_modules AS cm ON a.course = cm.course
WHERE asb.grade < 0 AND cm.instance = a.id AND cm.module = 1 AND a.course = c.id
) 'unChecked <br/>Submissions'
,(
SELECT count(asb.id)
FROM prefix_assignment_submissions AS asb
JOIN prefix_assignment AS a ON a.id = asb.assignment
JOIN prefix_course_modules AS cm ON a.course = cm.course
WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
) 'Submitted <br/>Assignments'
FROM prefix_course AS c
LEFT JOIN (
SELECT course, count(*) AS iAssignments
FROM prefix_assignment AS a
GROUP BY a.course
) AS tblAssignmentsCount ON tblAssignmentsCount.course = c.id
LEFT JOIN (
SELECT course, count(*) AS iOpenAssignments
FROM prefix_assignment AS a
WHERE FROM_UNIXTIME(a.timedue) > NOW()
GROUP BY a.course
) AS tblOpenAssignmentsCount ON tblOpenAssignmentsCount.course = c.id
WHERE 1=1
#AND c.fullname LIKE '%תשעג%'
%%FILTER_YEARS:c.fullname%%
## You can enable the SEMESTER filter as well,
## by uncommenting the following line:
## %%FILTER_SEMESTERS:c.fullname%%
ORDER BY 'Open <br/>Assignments' DESC