Thanks,
Zach
Student Name
Course
Activity
Grade
and Date
I should be able to modify something I already have. I have one urgent problem to fix and then I can get to this. It will be two or three days. This should be easy.
How do you want the student name formatted? Do you want username in there too? If I just use lastname, firstname there could be duplicate names for multiple unique users so not sure if that's all that useful. You should be able to modify my SQL to suit your needs so it shouldn't be a big deal.
--
Andy
SELECT mdl_course.idnumber AS course_id,
mdl_user.firstname,
mdl_user.lastname,
mdl_user.username,
mdl_grade_items.itemname AS gradebookitem,
mdl_grade_grades.rawgrade AS raw_grade,
mdl_grade_grades.finalgrade AS final_grade,
FROM_UNIXTIME(mdl_grade_grades.timecreated) AS date_created,
FROM_UNIXTIME(mdl_grade_grades.timemodified) AS date_modified,
SUBSTRING(mdl_grade_grades.feedback, 1, 15)
FROM mdl_grade_grades
JOIN mdl_user ON mdl_grade_grades.userid = mdl_user.id
JOIN mdl_grade_items ON mdl_grade_grades.itemid = mdl_grade_items.id
JOIN mdl_course ON mdl_grade_items.courseid = mdl_course.id
ORDER BY mdl_course.idnumber, mdl_user.username, mdl_grade_items.itemname, mdl_grade_grades.timemodified;
This will return more information that some of you asked for. It's never a good idea to identify your students by firstname, lastname only becasue it is common for a large user base to have two people with the same first/last name.
You may notice the SUBSTRING clause on the feedback column. I only included the first 15 characters of the feedback just so I could tell how many instructors were leaving feedback. You can remove the SUBSTRING clause to get all the feedback or just remove the entire line if you don't want any feedback in the dump file.
This query may pound your database depending on how busy your server is. I only run this in the middle of the night. If you do not know SQL and you want me to customize this for YOU please let me know. It only takes a minute or two.
One thing I forgot to mention is... this gradebook dump is not in the same format as a gradebook "export" and you cannot import my gradebook dump back into a course. However, I do have some scripts that will reformat this SQL gradebook dump into an XML file that can be imported back into moodle on a course by course basis.
Additionally, I don't need the feedback option but others might so if I can easily delete that part, I'm cool
Many thanks Andy!
-Zach
studentid,courseid,coursetotalgrade
I've trolled through some of the SQL bits in the function get_avghtml function in moodle/grade/report/grader/lib.php but am not familiar with the code or schema to make much sense of what I need to do.
Any Moodle/SQL wizards want to take a stab - I'm happy with just a SQL snippet
-Preston
It is defined in mdl_grade_items with itemtype field set to 'course'
So...
SELECT mdl_course.id AS courseid, mdl_user.id AS studentid, mdl_grade_grades.finalgrade AS coursetotalgrade
FROM mdl_grade_grades
JOIN mdl_user ON mdl_grade_grades.userid = mdl_user.id
JOIN mdl_grade_items ON mdl_grade_grades.itemid = mdl_grade_items.id
JOIN mdl_course ON mdl_grade_items.courseid = mdl_course.id
WHERE mdl_grade_items.itemtype = 'course'
ORDER BY mdl_course.idnumber, mdl_user.username, mdl_grade_items.itemname, mdl_grade_grades.timemodified
should do it
Barry,
i tried and it works fine.
i have a small addition to the request. i wish to have dates also. But what i find when i upgraded was that the dates under grades is stored as the date i upgraded. i find that correct dates are stored in lesson and assignment table. How do i join these tables also and show the correct date? better still, is it possible to get dates from lesson and assignment table and replace the dates in grade table?
Biju
SELECT DISTINCT mdl_course.shortname AS course,
mdl_user.firstname, mdl_user.lastname,
mdl_user.username, mdl_grade_grades.`userid` AS userid,
mdl_grade_items.`itemname` AS itemname,
mdl_grade_grades.`finalgrade` AS finalgrade, FROM_UNIXTIME(mdl_assignment_submissions.`timecreated`) AS timecreated, FROM_UNIXTIME(mdl_assignment_submissions.`timemodified`) AS timemodified
FROM mdl_assignment_submissions
JOIN mdl_assignment
ON mdl_assignment_submissions.`assignment` = mdl_assignment.`id`
JOIN mdl_grade_items
ON mdl_assignment.`id` = mdl_grade_items.`iteminstance`
JOIN mdl_grade_grades
ON mdl_grade_items.`id` = mdl_grade_grades.`itemid`
JOIN mdl_course
ON mdl_grade_items.courseid = mdl_course.id
JOIN mdl_user
ON mdl_grade_grades.userid = mdl_user.id
WHERE mdl_grade_items.`itemmodule` = 'assignment'
ORDER BY mdl_grade_items.`courseid` , mdl_grade_grades.`userid` , mdl_grade_items.`itemname`
Which dates in the lesson tables do you want? (Which field from which table?). You could use the same pattern as above (but the table & field names are different).
You could use an update query to update your dates in the grade table
Barry,
Thanks. this works perfectly for assignment to get dates. the issue with the lesson module is this:
- i have courses with many lessons. as i stated in my previous post, while upgrading, mdl_grade_grades stored date of completion of courses as the date of upgrading and i wish to update this date from lesson module also
- in mdl_lesson_grades, the lesson(s)ID and grades for each lesson + date completed is stored
- i wish to capture the latest date under a group of lessons for a course and update that date in the mdl_grade-grades against the course
- the data relating to the lessons and the course it belongs to is under mdl_lesson
Could you please help me with this update? i am also not very comfortable with the update syntax.
Biju
SELECT DISTINCT mdl_course.shortname AS course,
mdl_user.firstname, mdl_user.lastname,
mdl_user.username, mdl_grade_grades.`userid` AS userid,
mdl_grade_items.`itemname` AS itemname,
mdl_grade_grades.`finalgrade` AS finalgrade, FROM_UNIXTIME(mdl_lesson_grades.`completed`) AS completed
FROM mdl_lesson_grades
JOIN mdl_lesson
ON mdl_lesson_grades.`lessonid` = mdl_lesson.`id`
JOIN mdl_grade_items
ON mdl_lesson.`id` = mdl_grade_items.`iteminstance`
JOIN mdl_grade_grades
ON mdl_grade_items.`id` = mdl_grade_grades.`itemid`
JOIN mdl_course
ON mdl_grade_items.courseid = mdl_course.id
JOIN mdl_user
ON mdl_grade_grades.userid = mdl_user.id
WHERE mdl_grade_items.`itemmodule` = 'lesson'
ORDER BY mdl_grade_items.`courseid` , mdl_grade_grades.`userid` , mdl_grade_items.`itemname`
Where in Moodle to you view the timecreated / timemodified fields from the grade_grades table? (I don't use it so just wondering if there is a page that actually uses / displays that data)
Barry,
i think there is a loop or something since the query went on and on. But you have given me enough ideas for me to work this through.
Thanks for all the help
Biju
Barry,
is it possible to display date of completion also in the overview report?
Biju
The overview report only shows course totals and courses don't have 'date of completion' at the moment.
If you mean date of completion in the user report then if it's possible I think you'd have to add some PHP which checks what acitivity type each grade item is and then pulls the dates from the corresponding table for each record separately. This would be very resource intensive. If it's possible to pull this info in one SQL query I'd like to know as well (don't think so), maybe the gradebook structure will cater for this in 2.0.
It should be fairly easy to build a separate report for each activity type with dates etc.
Cheers,
Barry
Barry,
thanks for the quick reply, as usual. i think i will wait till 2.0 and then decide.
Biju
I have a few tweaks but I can handle those (mainly getting idnumber instead of id).
Thanks again - saved me several hours of digging in unfamiliar territory.
-Preston
I think this may be the SQL query that I am looking for but I am having some trouble getting it to work. I am taking my first stab at my first Moodle query in a hopeless attempt to create a site wide grades report. I am attempting to use this query using the Configurable Reports block but I am getting the error "No Explicit Prefix." I know nothing about SQL so I am not sure where to begin with this. Any tips or suggestions are greatly appreciated.
Query I am using in Moodle 1.9.9:
SELECT mdl_course.id AS courseid, mdl_user.id AS studentid, mdl_grade_grades.finalgrade AS coursetotalgrade
FROM mdl_grade_grades
JOIN mdl_user ON mdl_grade_grades.userid = mdl_user.id
JOIN mdl_grade_items ON mdl_grade_grades.itemid = mdl_grade_items.id
JOIN mdl_course ON mdl_grade_items.courseid = mdl_course.id
WHERE mdl_grade_items.itemtype = 'course'
ORDER BY mdl_course.idnumber, mdl_user.username, mdl_grade_items.itemname, mdl_grade_grades.timemodified
Hi Joe, The error "No Explicit Prefix" can be easily corrected. Within your SQL, replace each occurance of "mdl_" with "prefix_".
Why is this required? The moodle configurable reports uses moodles internal prefix mapping rather than the full install path. This is to that the reports are exportable and importable into different moodle installations (which may have different prefix's) and just work.
Happy moodling
Jason
Hello,
I have Custom SQL below for Moodle 1.9.x and my DB is MySQL. I got "Query failed" when I clicked save changes. What is wrong in the MySQL syntax below?
SELECT
u.firstname AS "First",
u.lastname AS "Last",
c.fullname AS "Course",
a.name AS "Assignment",
''<a href="https://moodle.domain.com/mod/assignment/submissions.php'' + char(63) +
+ ''id='' + cast(cm.id AS varchar) + ''&userid='' + cast(u.id AS varchar)
+ ''&mode=single&filter=0&offset=2">'' + a.name + ''</a>''
AS "Assignmentlink"
FROM prefix_assignment_submissions AS asb
JOIN prefix_assignment AS a ON a.id = asb.assignment
JOIN prefix_user AS u ON u.id = asb.userid
JOIN prefix_course AS c ON c.id = a.course
JOIN prefix_course_modules AS cm ON c.id = cm.course
WHERE asb.grade < 0 AND cm.instance = a.id AND cm.module = 1
ORDER BY c.fullname, a.name, u.lastname
I got this from http://docs.moodle.org/23/en/ad-hoc_contributed_reports#All_Ungraded_Assignments_w.2F_Link
Hope anyone could help on this.
Thanks.
James
Hi James,
You should stick to one post: http://moodle.org/mod/forum/discuss.php?d=193585#p905178
--- Reference
http://docs.moodle.org/23/en/Moodle.org_forums_Code_of_Conduct#Post_your_question_in_one_forum_only
Anyway, I got it working already. Please check my duplicate post (sorry admin) - http://moodle.org/mod/forum/discuss.php?d=193585#p905506
Can you please show show scripts and information on how to convert this mysql output into xml as you have described?
Cheers