Comments

• 

  Mykl Belfatto

  Fri, Jun 12, 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, Jun 19, 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, Jun 19, 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
• 

  Tim Hunt

  Fri, Jun 19, 2015, 8:16 PM

  Try changing FROM_UNIXTIME(created) -> created in the WHERE.
• 

  Kat Bodine

  Thu, Aug 6, 2015, 12:49 AM

  Really liking this tool! We have a hosted site and are using this tool to automate attendance and other reports to help with retention. Currently the email report requires that a user log in to Moodle and have viewing permissions. Suggestion to allow .csv file to be sent directly to ftp site or email address.
• 

  Tim Hunt

  Thu, Aug 6, 2015, 5:00 PM

  A scheduled query can be sent to an email address. Look at the bottom of the form when creating a query.
• 

  Marcus Green

  Mon, Aug 10, 2015, 10:33 PM

  Tim or Mahmoud, it would be useful if you were to update the links to the documents to a later version
  as it is pointing to 2.2 at the moment with lots of references to Moodle 1.9 which makes it look rather
  out of date. If you just take out the version bit (the 22) it should automatically redirect to the latest version
  i.e. https://docs.moodle.org/en/Custom_SQL_queries_report
• 

  Tim Hunt

  Mon, Aug 10, 2015, 10:36 PM

  Thanks Marcus. Link updated.
• 

  Wen Hao Chuang

  Wed, Aug 19, 2015, 3:21 PM

  As Kathleen pointed out, it would be super awesome if we could add direct SFTP (with scheduling) capability to this plugin. Thanks for the great work!
• 

  charles okaformbah

  Wed, Aug 19, 2015, 4:11 PM

  Hi Tim. Need help on schedule, the query works fine but I don't seem to understand why it does not do query scheduled to my email. Don't know if I am doing something wrong or is there a cronjob I need to setup. Here is an imageprint of my setting http://prntscr.com/86cj20
• 

  Tim Hunt

  Wed, Aug 19, 2015, 4:14 PM

  Best to ask for help in the forum https://moodle.org/mod/forum/view.php?id=7979
• 

  Kat Bodine

  Mon, Sep 21, 2015, 9:58 PM

  Actually a csv file cannot be sent to an email address directly. A link to the file can be sent to a Moodle user and indirectly forwarded to that users email address, but the user still needs to log into Moodle to retrieve the csv file or create one from the data included in the email.
  
  We are using this tool to collect daily attendance to be imported to our SIS. Ideally I would like to have the csv file automatically dropped to ftp. The few extra steps we take daily to download the report and place it in a file is mildly annoying. BUT that being said is still 1000 times better than collecting and posting attendance by hand. Not complaining, I am very grateful for this tool. Just a suggestion regarding the ftp drop.
• 

  Tim Hunt

  Mon, Sep 21, 2015, 10:01 PM

  Well, if someone can code that option without making either the user interface, or the back-end code, too much more complicated, then I would certainly accept the change (and it should certainly be possible to do). However, doubt I will have the time or inclination to code this myself.
• 

  Steve Daly

  Wed, Nov 11, 2015, 2:48 PM

  Automating Attendance from all courses
  
  Hi,
  I'm wondering if anyone has written AN SQL query for generating an attendance report for all students for all courses?
  
  Love the power of this!
• 

  tim nixon

  Thu, Nov 19, 2015, 8:44 PM

  I am having a hopefully simple issue. Moodle is installed under centos..
  When I schedule a run of a sql query it never runs. The command crontab -l shows nothing scheduled and I see no cron.php in the report/customsql directory
  
  Is there something I need to do to have a query run at a specific time?
  
  Thanks
  Tim