Assignments requiring grading report

Assignments requiring grading report

by Paul Vincent -
Number of replies: 3

Hi, I've been trying to get the following report: https://docs.moodle.org/310/en/ad-hoc_contributed_reports#All_Ungraded_Assignments_w.2F_Link to work on our 3.8 Moodle, but althoug no errors are produced, no results are output via the report. I'm assuming it's the query in the top-half of the sql script that's most recent, as that in the bottom half (I'm assuming this is the deprecated Assignment2 version) produces errors. 

SELECT 
u.firstname AS "First",
u.lastname AS "Last",
c.fullname AS "Course",
a.name AS "Assignment",
 
concat('<a target="_new" href="%%WWWROOT%%/mod/assign/view.php?id=', 
cm.id, 
'&rownum=0&action=grader&userid=', 
u.id,
'">Grade</a>') 
AS "Assignment link"
 
FROM prefix_assign_submission sub
JOIN prefix_assign a ON a.id = sub.assignment
JOIN prefix_user u ON u.id = sub.userid
JOIN prefix_course c ON c.id = a.course AND c.id = %%COURSEID%%
JOIN prefix_course_modules cm ON c.id = cm.course 
JOIN prefix_context cxt ON c.id=cxt.instanceid AND cxt.contextlevel=50
JOIN prefix_role_assignments ra ON cxt.id = ra.contextid AND ra.roleid=5 AND ra.userid=u.id
 
WHERE cm.instance = a.id AND cm.module = 22 AND sub.status='submitted'
 
ORDER BY c.fullname, a.name, u.lastname

Can anyone point me towards why this might not be working correctly? Other reports seem to work ok...

Many thanks
Paul

                        

    
    
    
                            
    
    
                                

                                        
Average of ratings:  - 

                                        
                                

    
                            
                    

                

            

        

    
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

                                        In reply to Paul Vincent
                                        
Re: Assignments requiring grading report

                                        

                                            by Paul Vincent - 
                        

                                

                
                                        

                                        

                            

                
                            

                
                                

                                    

                                        
Sorry, I should have tried a little harder myself. The issue is the cm.module=22 - the Assignment cm seems to vary between installations, but removing this condition seems to fix things, and have removed the  :

JOIN prefix_course c ON c.id = a.course AND c.id = %%COURSEID%%

line to make the report work site-wide, which is what we're after.
However, I'm trying to now display only the ungraded items for a teacher, where they're in the same course group as the student, but can't work out where to start on this. We use separate groups for each of our courses as we can have a dozen or more teachers per course, each with their own students.  Can anyone give any pointers for this?

Many thanks,
Paul


                                    

                
                
                
                                        
                
                
                                            

                                                    
Average of ratings:  - 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

                                        In reply to Paul Vincent
                                        
Re: Assignments requiring grading report

                                        

                                            by Miguel González Laredo - 
                        

                                

                
                                        

                                                        Picture of Plugin developers
                                        

                            

                
                            

                
                                

                                    

                                        
Hi Paul. I have dedicated several minutes for help you. I hope this query can address you on the rigth way:


SELECT DISTINCT

g.name AS "Group",

u.firstname AS "First",

u.lastname AS "Last",

c.fullname AS "Course",

a.name AS "Assignment",


concat('Grade') 

AS "Assignment link"


FROM prefix_assign_submission sub

JOIN prefix_assign a ON a.id = sub.assignment

JOIN prefix_user u ON u.id = sub.userid

JOIN prefix_course c ON c.id = a.course -- AND c.id = %%COURSEID%%

JOIN prefix_groups g ON g.courseid=c.id 

JOIN prefix_groups_members gm ON u.id=gm.userid and gm.groupid=g.id

JOIN prefix_course_modules cm ON c.id = cm.course 

JOIN prefix_modules m ON m.id=cm.module -- and m.name="assignment"

JOIN prefix_context cxt ON c.id=cxt.instanceid AND cxt.contextlevel=50

JOIN prefix_role_assignments ra ON cxt.id = ra.contextid AND ra.roleid=5

JOIN prefix_grade_items gi ON gi.courseid=c.id 

JOIN prefix_grade_grades gg ON gg.itemid=gi.id and gg.finalgrade IS NOT NULL

AND ra.userid=u.id

WHERE cm.instance = a.id /*AND cm.module = 22*/ AND sub.status='submitted'

ORDER BY c.fullname, a.name, u.lastname

                                    

                
                
                
                                        
                
                
                                            

                                                    
Average of ratings: Useful (1) 

                                                    
                                            

                
                                        
                                

                            

                        

                    

                
        

                

                    

                        
                
                        

                            

                                        

                                                
                                        

                                

                                        In reply to Miguel González Laredo
                                        
Re: Assignments requiring grading report

                                        

                                            by Paul Vincent - 
                        

                                

                
                                        

                                        

                            

                
                            

                
                                

                                    

                                        
Sorry for my delayed reply - this is great Miguel - many thanks for taking the time to help with this!👍

                                    

                
                
                
                                        
                
                
                                            

                                                    
Average of ratings:  -