Comments

• 

  Tim Hunt

  Fri, 20 Feb 2015, 9:59 PM

  You need to do something like
  
  SELECT c.fullname, traineruser.lastname AS trainer_name, manageruser.lastname AS manager_name
  
  FROM prefix_course AS c
  JOIN prefix_context AS ctx ON c.id = ctx.instanceid
  JOIN prefix_role_assignments AS trainerra ON trainerra.contextid = ctx.id
  JOIN prefix_user AS traineruser ON traineruser.id = trainerra.userid
  JOIN prefix_role_assignments AS managerra ON managerra.contextid = ctx.id
  JOIN prefix_user AS manageruser ON manageruser.id = managerra.userid
  
  WHERE trainerra.roleid = 15
  AND managerra.roleid = 17
  AND ctx.contextlevel = 50
  AND u.deleted=0
  
• 

  Barbara S.

  Fri, 27 Feb 2015, 8:33 PM

  Many, many thanks Tim!
  I am able now to retrieve the data required.
  
• 

  Dirk Meyer

  Sun, 1 Mar 2015, 12:03 AM

  Good day,
  
  I am having trouble with this on a 2.7 in that the following query only returns a few courses from a variety of categories. The same query works well for me on a 2.6. I noticed that in the 2.7 I have an extra box at the bottom of the edit query screen labelled 'Select category for this report' I am trying to generate a report of most active courses across all categories. Thank you for your time.
  
  SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename
  FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id
  GROUP BY courseId
  ORDER BY hits DESC
• 

  Blair F.

  Wed, 6 May 2015, 1:07 AM

  I LOVE this plugin and use it often. I often use named placeholders so that values can be input when the report is run, but I'm wondering if anyone knows of a way of doing what would basically amount to the following:
  
  1) entering multiple values in the named placeholder (for example, not just course id=1234, but course id=1234,2345,3456
  2) using a wildcard (for example, not just a specific course id, but ALL courses -- yet still giving the user the option of entering a specific course)
  
  If anyone has any ideas or solutions, I'd really appreciate it! It would save me a lot of time!
• 

  Tim Hunt

  Wed, 6 May 2015, 1:16 AM

  If you know exactly how many placeholders you want, you can do
  
  WHERE courseid IN (:id1, :id2, :id3)
  
  Or, you could do
  
  WHERE course.shortname LIKE :pattern
  
  and pattern would need to be somthing like %maths%
  
  Anything more than that would require a fair amount of development.
• 

  Benjamin Lesjak

  Wed, 6 May 2015, 6:06 PM

  Hi.
  
  We tried to install this plugin to our Moodle version 2.56. We installed the newest version 2.7 for Moodle 2.4+ (2014061800) but we ended with en error.
  
  Capability "report/customsql:view" was not found! This has to be fixed in code.
  line 389 of /lib/accesslib.php: call to debagging()
  line 1258 of /lib/adminlib.php: call to has_capability()
  line 3408 of /lib/navigationlib.php: call to admin_externalpage->check_access()
  ...
  
  Did anyone experienced this error? Thanks,
  
  Ben
• 

  Tim Hunt

  Wed, 6 May 2015, 6:08 PM

  After adding the plugin, did you got to Site administration -> Notifications to complete the install?
• 

  Bruce Nicholls

  Fri, 15 May 2015, 10:22 AM

  Hi Tim, I'm not to good on the SQL stuff, and I need to run a simple report to extract info on single users, for course/subject completion, dates and grades.
  Can you advise the best code for this please?
  Thanks
  Bruce
• 

  Mykl Belfatto

  Fri, 12 June 2015, 1:19 AM

  Hi Tim, How's it going? I trying to run this query ti get the outstanding activities for each student. it runs in phpmyadmin, but it won't run in Moodle. In Moodle, the first query (nested select) runs fine, it's when I add the NOT IN select that causes the problem. Any idea? Or another way to do this? Cheers, Mykl
  
  
  SELECT Company, StudentID, Student, Course, GROUP_CONCAT(Item), SortOrder
  FROM (
  
  SELECT Institution AS 'Company', u.id AS StudentID, CONCAT(u.lastname, ', ', u.firstname) AS 'Student', c.fullname AS Course, gi.itemname AS 'Item', gi.sortorder AS SortOrder
  FROM {user} AS u
  JOIN {groups_members} AS gm ON gm.userid = u.id
  JOIN {groups} AS g ON g.id = gm.groupid
  JOIN {course} AS c ON c.id = g.courseid
  JOIN {grade_items} AS gi ON gi.courseid = g.courseid
  
  WHERE u.Institution != '' AND NOT u.deleted
  AND itemmodule = 'assign' AND CONCAT(u.id, ', ', gi.id) NOT IN (
  SELECT CONCAT(u.id, ', ', gg.itemid) 'Item'
  FROM {user} AS u
  JOIN {grade_grades} AS gg ON gg.userid = u.id )
  
  ORDER BY CONCAT(Company, Student, Course), SortOrder ) AS tmp
  
  GROUP BY CONCAT(Company, Student, Course)
  
• 

  Tim Hunt

  Fri, 12 June 2015, 1:23 AM

  Try turning on https://docs.moodle.org/29/en/Debugging (set it to DEVELOPER level temporarily) then try again. You should get a useful error message.
  
  AS tmp in the ORDER BY looks wrong.
• 

  Mykl Belfatto

  Fri, 12 June 2015, 8:37 AM

  I didn't know the debug would work The AS tmp is not part of the ORDER BY, it's the naming for the nested SELECT
• 

  Mykl Belfatto

  Fri, 12 June 2015, 11:53 AM

  The error I get is Error when executing the query: Error reading from database Incorrect key file for table '/var/mysqltmp/#sql_1f3_0.MYI'; try to repair it...I think I'm blowing the tmp space I think I'll need to increase the size.
• 

  Mykl Belfatto

  Fri, 12 June 2015, 12:05 PM

  I got it!!! It's not perfect (yet), but this gives the outstanding activities (not graded) for each student.
  
  SELECT Company, StudentID, Student, Course, GROUP_CONCAT(Item)
  FROM (
  SELECT Institution AS 'Company', u.id AS StudentID, CONCAT(u.lastname, ', ', u.firstname) AS 'Student', c.fullname AS Course, gi.itemname AS 'Item', gg.itemid AS ItemsCompleted
  FROM prefix_user AS u
  JOIN prefix_groups_members AS gm ON gm.userid = u.id
  JOIN prefix_groups AS g ON g.id = gm.groupid
  JOIN prefix_course AS c ON c.id = g.courseid
  JOIN prefix_grade_items AS gi ON gi.courseid = g.courseid
  LEFT JOIN prefix_grade_grades As gg ON gi.id = gg.itemid
  WHERE u.institution != '' AND NOT u.deleted AND gi.itemmodule = 'assign'
  AND gg.itemid IS NULL
  
  ORDER BY CONCAT(Company, Student, Course), gi.sortorder) AS tmp
  GROUP BY CONCAT(Company, Student, Course)
• 

  Daemon Hunt

  Fri, 19 June 2015, 6:10 AM

  I am panicking. My Moodle for 2015 has been using the new standard log store with legacy logging turned off, and I have no idea how to tweak this SQL code to make it work!
  
  SELECT COUNT(l.id) hits, concat('',c.fullname,'') 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
  
  ,CASE
  WHEN c.fullname LIKE '%-13%' THEN '2013'
  WHEN c.fullname LIKE '%-14%' THEN '2014'
  WHEN c.fullname LIKE '%-15%' THEN '2015'
  END AS YEAR
  
  ,(SELECT COUNT(*) FROM prefix_course_modules cm WHERE cm.course = l.course) Modules
  
  ,(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 Students
  
  FROM prefix_log l
  INNER JOIN prefix_course c ON l.course = c.id
  GROUP BY c.id
  HAVING Modules > 2
  ORDER BY YEAR DESC, hits DESC
• 

  Craig Simmons

  Fri, 19 June 2015, 6:54 PM

  This is a great plugin. Thanks.
  
  However, I am having problems entering a date as parameter.
  
  This works:
  
  SELECT
  subject, firstname, lastname, email, FROM_UNIXTIME(created)
  FROM
  {forum_posts}
  INNER JOIN
  {user} ON {forum_posts}.userid = {user}.id
  WHERE
  parent > 0
  AND FROM_UNIXTIME(created) BETWEEN '2015/04/27' AND '2015/05/03'
  
  Whereas this doesn't (although it correctly prompts for start and finish dates):
  
  SELECT
  subject, firstname, lastname, email, FROM_UNIXTIME(created)
  FROM
  {forum_posts}
  INNER JOIN
  {user} ON {forum_posts}.userid = {user}.id
  WHERE
  parent > 0
  AND FROM_UNIXTIME(created) BETWEEN :start_date AND :end_date
  
  Any thoughts why?
  
  Thank you for your time.
  
  Craig