Comments

• 

  Chris Hay

  Thu, 31 July 2014, 10:24 PM

  When I set the Teacher capability to view reports to 'Allow' and check any Teacher users' permissions, even though I've selected 'Allow' in Define roles, the user's capabilities don't say 'Allow'. Any idea why? When I log in as a Teacher I am not getting any 'Ad-hoc...' option under Reports under Course Admin either. It looks like it's being set at role level but the user isn't inheriting it ... Any help appreciated.
• 

  Tim Hunt

  Thu, 31 July 2014, 10:56 PM

  Note that typically a teacher is only a teacher within a particular course. These reports are outside courses, at the system-wide level. That is the wrong way round for permission inheritance to work.
  
  You will need to make a new role "View reports" which give that capability, and which you assign at site-level.
  
  (Alternatively, you could call the role something more meaningful like "Teaching staff" in case you need to use it for other purposes in the future.)
• 

  Chris Hay

  Thu, 31 July 2014, 11:55 PM

  Thanks Tim - that's useful information to bear in mind.
• 

  Brian Evans

  Wed, 24 Sept 2014, 3:58 AM

  Tim,
  
  I will add my thanks to everyone else's. This is a fantastic plugin.
  
  I have one of suggestion: create an option to hide the "back to the list of available queries" link for those not allowed to manage/edit queries. Links to individual queries could then be placed in a URL resource, HTML block, custom menu, etc., without exposing the whole list of queries.
  
  Thanks again.
• 

  Tim Hunt

  Wed, 24 Sept 2014, 10:19 PM

  Well, that is just security through obscurity. They could just sit there working through ?id=1, ... ?id=2, ... to access all the reports that they have permission to see.
  
  A better option might be a way to generate a token for a particular query, then if someone went to the URL ...?id=123&token=... then they would be allowed to see just that query.
  
  However, at the moment that is just an idea. I don't have time to implement that.
• 

  Joseph Spilatore

  Thu, 25 Sept 2014, 2:59 AM

  Greetings
  
  Continue to receive Error when attempting to include link in query results. Moodle Version 2.4.5 and lastest version of custom sql. Query tested directly through mysql workbench. Any insight is greatly appreciated
  
  Below is sample.
  
  Query SQLRequired field
  Error when executing the query: ERROR: Incorrect number of query parameters. Expected 1, got 0.
  
  SELECT
  u.id,
  u.firstname,
  concat('',u.firstname,'') AS User
  
  FROM prefix_cohort AS h
  JOIN prefix_cohort_members AS hm ON h.id = hm.cohortid
  JOIN prefix_user AS u ON hm.userid = u.id
  
  
  
  ORDER BY cohortid
• 

  Tim Hunt

  Thu, 25 Sept 2014, 4:13 AM

  The problem is the ?.
  
  ? is a special character in SQL in Moodle, and so you are not allowed it anywhere, even in string literals.
  
  The help text in the latest version makes this clear, and given the work-around. You can sort-of read it here: https://github.com/moodleou/moodle-report_customsql/blob/master/lang/en/report_customsql.php#L114
  
• 

  Joseph Spilatore

  Fri, 26 Sept 2014, 9:07 PM

  has anyone tried to incorporate color coding in custom sql module? I am trying to color code a field in one of my queries. The bold is displaying but not p style color, I am guessing that it may not work because of the CSS
  
  concat('

  Color

  ')
  
  
  Complete query
  
  Select
  c.id As cid,
  Date_Format(From_UnixTime(c.startdate), '%m/%d/%Y') As Start,
  c.shortname As Shortname,
  c.fullname As `Course Name`,
  Count(Distinct u.id) Enrolled,
  c.maxstudents As Max,
  c.maxstudents - Count(Distinct u.id) As Seats_available,
  Format(Count(Distinct u.id) / (c.maxstudents) * 100, 2) As Percentagefull,
  if(Format(Count(Distinct u.id) / (c.maxstudents) * 100, 2) <80 ,'Red', 'Green') Go,
  concat('

  Color

  ')
  
  From
  prefix_role_assignments ra,
  prefix_user u,
  prefix_course c Inner Join
  prefix_course_categories On c.category = prefix_course_categories.id,
  prefix_context cxt
  Where
  ra.userid = u.id And
  ra.contextid = cxt.id And
  cxt.instanceid = c.id And
  c.fullname Not Like '%cancel%' And
  From_UnixTime(c.startdate) >= CurDate() And
  DateDiff(Date_Add(CurDate(), Interval 30 Day), From_UnixTime(c.startdate)) >=
  4
  Group By
  c.id, prefix_course_categories.name
  Having
  Format(Count(Distinct u.id) / (c.maxstudents) * 100, 2) <= 99
  Order By
  c.startdate,
  Percentagefull
  
• 

  Tim Hunt

  Fri, 26 Sept 2014, 9:14 PM

  I think the problem is that you are generating invalid HTML. THe above SQL will output
  

  Color

  
  which of course does not work.
  
  (Note, these comments seem to be unreadable here. The HTML tags were readable in the notification email.)
• 

  Blair F.

  Tue, 30 Sept 2014, 12:08 AM

  I've created a query generates a complete report, runs every day and sends me an email. This weekend, I viewed the report on my mobile device and saw "Previously archived reports" (or something similar) at the bottom of the report. This is what's expected, based on the documentation, which states, "However, if each scheduled run generates a complete report, then at the bottom of a page there will be a list of all the previous runs of the report, so that you can go and see how the report changed over time." The weird thing is, when I viewed the same report this morning, to look back at what happened over the weekend, there's no list of archived reports at the bottom, anymore! I would like to be able to look back at these, but have no idea how to access them now.
  
  Any ideas why this would happen, and how I can see these archived reports? I'm baffled.
  
  Thanks,
  Blair
• 

  Blair F.

  Sat, 8 Nov 2014, 12:58 AM

  This has happened to me again. The report ran on Monday and I had Monday off. So, when I went to look at it on Tuesday, there were no results (because the nature of the report is that it identifies an event in the last 24 hours). So, I look at the report and it's empty. Last night, it ran and when I looked at it, the list of archives was there, so I was again able to access the report from Monday.
  
  I noticed that path to the archived query is in the format /report/customsql/view.php?id=__&timestamp=________.
  
  I could probably create another report to list the archives, but I'm not sure where to look for them. I checked the report_customsql_queries table and cannot find anything.
  
  Any tips?
• 

  Blair F.

  Sat, 8 Nov 2014, 3:56 AM

  I just realized that I could have the results emailed to me, rather than just the link (duh!). At least that way I can access them any day.
• 

  Barbara S.

  Fri, 20 Feb 2015, 3:26 PM

  hi!
  @tim: thanks for that great plugin it helps me a lot in the daily routine.
  
  I am new to SQL and have got a very basic question. Is it possible to retrieve the email of the trainer (Role A) and the email of the manager (role B) for a course in one single record (row) listed behind the course fullname?
  
  What is working is the following:
  SELECT c.id, c.fullname, u.email AS Role_A
  
  FROM prefix_course AS c
  
  JOIN prefix_context AS ctx ON c.id = ctx.instanceid
  
  JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
  JOIN prefix_user AS u ON u.id = ra.userid
  
  WHERE ra.roleid =15
  
  AND ctx.instanceid = c.id
  AND u.deleted=0
  
  I would like to have another column called Role_B which is looking up u.email WHERE ra.roleid =14.
  Is it generally possible to retrieve 'u.email' twice in the same record? And if yes, how? I would appreciate getting a link where this is explained, I could not even find a hint. Thanks a lot!!
• 

  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.