Comments

• 

  Tim Hunt

  Wed, 7 July 2021, 6:59 PM

  New version pushed (with 3.11 compatibility and more.)
• 

  Phil Everist

  Wed, 14 July 2021, 12:43 PM

  Moodle 3.10.4 - PHP v7.2.26 - MSSQL v11.00.7493
  Hi - my Moodle system runs on a windows platform with MSSQL. Date fields are displaying as text fields when downloaded as Excel after an ad-hoc report runs and I am trying to work out how to ensure date fields are recognised by excel as dates so my end users can easily filter by year and month without having to first convert the text field to a date (many users are not excel savvy).
  
  I am sure this was working earlier in the year and the only thing I can think of that has changed was a Windows Server 2008 to Windows Server 2019 upgrade.
  
  The SQl I am using to output a date (course completion date in this case) that works fine when displayed as a HTML table is:
  CONVERT(VARCHAR(10), DATEADD(second, prefix_course_completions.timecompleted-DATEDIFF(second,GETDATE(),GETUTCDATE()), CONVERT(DATETIME, '1970-01-01', 103)), 103) AS Completed
  
  My SQL is very basic -would a different way of converting/formatting the date in the SQL produce a field excel displays as a date when exported or is this an issues with the download process?
  
  Also - is it possible to do any of the following:
  - limit or paginate displayed rows while allowing download of all rows (some of our reports can be large)
  - use the same parameter field in multiple places in the where clause (e.g. where fielda = :parameter1 or fieldb= :parameter1) - I get an error: Incorrect number of query parameters. Expected 3, got 2.
  - or the above with LIKE and the % in the SQL instead of the end user entering it in the parameter field?
  
  Thank you
  Phil
• 

  Csaba Gloner

  Mon, 26 July 2021, 10:57 PM

  Hi,
  the week start for the scheduled mail sent on the 1st day of the week seems to be set to Sunday.
  I can see
  define('REPORT_CUSTOMSQL_START_OF_WEEK', 6); // Saturday, so I guess Saturday is day 0 and Sunday is day one?
  So if I want to change this to Monday, I have to set it to 7?
  Thanks
  Csaba
• 

  Tim Hunt

  Tue, 27 July 2021, 3:46 AM

  @Phil,
  
  1. you should not need to do weird things to achieve date-formatting. Read the on-screen text on the form where you edit the query. That explains how to get Moodle to format the date values.
  
  2. Pagination - this actually puts more load on the server, which is why we prefer to generate thw whole report in one go and then scroll (or filter in excel).
  
  3. Ability to use the same parameter field many times would be lovely, but this is a limitation of Moodle DB query library, which we use. It is almost impossible to get around. There are sometimes tricks you can do in the SQL to get around it. E.g. where :parameter1 IN (fielda, fieldb) - but check that does not prevent the query being executed efficiently.
  
  4. You can do this using string concatenation. Not sure about the MSSQL syntax, but in Postgres it would be fielda LIKE :parameter1 || '%'
• 

  Tim Hunt

  Tue, 27 July 2021, 3:48 AM

  @Csaba in recent versions of this plugin, you don't need to set this in PHP code. It is controlled by an admin setting under Admin -> Plugins -> Reports -> Ad-hoc DB Queries.
• 

  Maemoon Naseer

  Fri, 24 Sept 2021, 4:47 PM

  H I added the plugin and want to run the custom report (Assignments (and Quizzes) waiting to be graded). I tried to add the code mention below. But unable to run the report and save. Can anyone guide me how to do that and run this report.
  
  SELECT 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
  
  ,concat('מטלות') AS Assignments
  
  ,concat('בחנים') AS 'Quizzes'
  
  ,(SELECT COUNT(*)
  FROM prefix_course_modules cm
  JOIN prefix_modules as m ON m.id = cm.module
  WHERE m.name LIKE 'quiz' AND cm.course = c.id
  GROUP BY cm.course
  ) AS 'nQuizzes'
  
  ,(SELECT COUNT(*)
  FROM prefix_quiz_attempts AS qa
  JOIN prefix_quiz AS q ON q.id = qa.quiz
  WHERE q.course = c.id
  AND qa.timefinish = 0
  GROUP BY q.course) AS 'unFinished Quiz attempts'
  
  ,(SELECT COUNT(*)
  FROM prefix_quiz_attempts AS qa
  JOIN prefix_quiz AS q ON q.id = qa.quiz
  WHERE q.course = c.id
  AND qa.timefinish > 0
  GROUP BY q.course) AS 'finished quiz attempts'
  
  ,(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 nStudents
  
  
  ,(
  SELECT count(a.id)
  FROM prefix_assignment AS a
  JOIN prefix_course_modules AS cm ON a.course = cm.course
  WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
  ) nAssignments
  
  ,(
  SELECT count(*)
  FROM prefix_assignment AS a
  WHERE a.course = c.id AND FROM_UNIXTIME(a.timedue) > NOW()
  GROUP BY a.course
  ) 'Open
  Assignments'
  
  , CONCAT(ROUND( (100 / iAssignments ) * iOpenAssignments ) ,'%') 'unFinished
  Assignments
  (percent)'
  
  ,(
  SELECT count(asb.id)
  FROM prefix_assignment_submissions AS asb
  JOIN prefix_assignment AS a ON a.id = asb.assignment
  JOIN prefix_course_modules AS cm ON a.course = cm.course
  WHERE asb.grade < 0 AND cm.instance = a.id AND cm.module = 1 AND a.course = c.id
  ) 'unChecked
  Submissions'
  
  ,(
  SELECT count(asb.id)
  FROM prefix_assignment_submissions AS asb
  JOIN prefix_assignment AS a ON a.id = asb.assignment
  JOIN prefix_course_modules AS cm ON a.course = cm.course
  WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
  ) 'Submitted
  Assignments'
  
  FROM prefix_course AS c
  LEFT JOIN (
  SELECT course, count(*) AS iAssignments
  FROM prefix_assignment AS a
  GROUP BY a.course
  ) AS tblAssignmentsCount ON tblAssignmentsCount.course = c.id
  
  LEFT JOIN (
  SELECT course, count(*) AS iOpenAssignments
  FROM prefix_assignment AS a
  WHERE FROM_UNIXTIME(a.timedue) > NOW()
  GROUP BY a.course
  ) AS tblOpenAssignmentsCount ON tblOpenAssignmentsCount.course = c.id
  
  WHERE 1=1
  #AND c.fullname LIKE '%תשעג%'
  %%FILTER_YEARS:c.fullname%%
  ## You can enable the SEMESTER filter as well,
  ## by uncommenting the following line:
  ## %%FILTER_SEMESTERS:c.fullname%%
  ORDER BY 'Open
  Assignments' DESC
  
  Thanks
• 

  Daniel Spadacini

  Fri, 19 Nov 2021, 2:06 AM

  Question. In a "select count union" query, which is based on a value that can be entered via form/input, is it possible to use the same value recorded in the form/input on all the selections?
  I have tried declaring variables or setting a value associated with the input (e.g. :variable), but the code is not accepted.
  Is there any solution?
• 

  Tim Hunt

  Fri, 19 Nov 2021, 6:45 PM

  At the moment, there is not a good solution to this. Each placeholder being used exactly once is a limitation of the Moodle database system, which we just use as-is.
  
  Sometimes there are tricks you can do. For examle with a single query that joins serveral tables, you can include something like
  
  JOIN (SELECT :my_value AS my_value) input ON 1 = 1
  
  in the JOINs, then you can refer to input.my_value everywhere. I'm not sure if this trick can be adapted to work with UNIONs.
  
  I suppose that, at some point, we could consider adding a work-around to our plugin. Perhpas, if you have parameters called :my_value_copy1, :my_value_copy2, they could be automatically set to the same value as :my_value. Yes, that is a nice idea. we should do that. I created https://github.com/moodleou/moodle-report_customsql/issues/104.
• 

  Mattia Donato

  Mon, 24 Jan 2022, 6:00 PM

  Hi, Tim, we have a problem.
  If I want my query to give me a report with more than 8.000 rows retuned, the query will not work. In the page where I'd see the results nothing happens and I can see just the description of the query.
  We have set in the plugin's settings, the maximum allowed limit rows returned as 50.000.
  Can you help us?
  The query has always worked without any problem since last week.
  Thanks
  
• 

  Tim Hunt

  Mon, 24 Jan 2022, 7:23 PM

  If you are seeing a blank page, then that means an error is happening. Turn on Debugging (https://docs.moodle.org/311/en/Debugging) so you can get the error message.
• 

  Motasem Al Haj Ali

  Mon, 21 Feb 2022, 7:04 PM

  Hi all,
  
  Tim and others who working on this plugin, thanks a lot, it is really helpful and powerful and we really appreciate your work.
  
  I am not sure if this is the right place to request improvement, but I think it would be great if we can add count() function to the calculation tab.
  
  Kind regards
  Motasem
• 

  Tim Hunt

  Thu, 24 Feb 2022, 1:43 AM

  I think you are posting under the wrong plugin. This report does not have that feature. Are you thinking of block_configurablereports?
• 

  Motasem Al Haj Ali

  Thu, 24 Feb 2022, 3:31 AM

  Thanks Tim (-:, you are right.
• 

  Lucia Skudlik

  Fri, 25 Feb 2022, 9:14 PM

  Hi, is there a way to make a parameter optional? I currently let the user filter by country but when they dont enter sth in the input it gives back no data, instead it should give back the list of all users, independetly from the country. I use the parameter like this WHERE :country = u.country
  
• 

  Tim Hunt

  Fri, 25 Feb 2022, 10:26 PM

  The simplest work-around for now is to make two queries, one with the parameter and one without.
  
  in the description for each query, you can link to the other one.