Ad-hoc database queries
Maintained by
Tim Hunt, Mahmoud Kassaei, Hieu Vu Van
This report plugin allows Administrators to set up arbitrary database queries
to act as ad-hoc reports. Reports can be of two types, either run on demand,
or scheduled to run automatically. Other users with the right capability can
go in and see a list of queries that they have access to. Results can be viewed
on-screen or downloaded as CSV.
Comments
Comments are no longer open for new posts. Existing comments remain available to read.
If you have a test server, try running the current code with Moodle 3.1. It might acutally work.
Will this officially support Moodle 3.1?
I tested the changes in commit 5d35b97. I did not experience any error or notice messages on my pages. I created, edited, and deleted a query via the "Ad-hoc database queries" report page, and did not experience any problems.
Thanks for finding the event problem. I just pushed what I think is the right fix to and eventfix branch in git: https://github.com/moodleou/moodle-report_customsql/tree/eventfix. However, I don't have time to test this. Can you test?
In each of the 3 event classes that this plugin has, the get_url() function wants to use $this->context. However, sometimes that context is null, and I get notice messages like the following on my page.
Notice: Trying to get property of non-object in C:\moodle\report\customsql\classes\event\query_edited.php on line 51
The fix is to use $this->get_context(), which will create a context if it does not exist yet.
I have discovered that when report_customsql_generate_csv() determines that a value should be formatted as a date string, it calls userdate(), providing it with a format string of '%F %T'. With that format string, the return is always a empty string.
In contrast, if userdate() is called with no format string, then the return is a valid string such as “Tuesday, 8 March 2016, 1:17 PM”. Therefore, the format string of ‘%F %T’ is suspect.
Perhaps a better alternative would be to use one of the format strings in “moodle\lang\[language code]\langconfig.php” ?
Thanks for the quick response.
The values for course_completions.timecompleted in my query result are indeed non-NULL integers. The WHERE clause I use in my query ensures that. I can execute the same query directly on the database (removing curly braces from and adding the 'mdl_' prefix back to the table names, of course), and I get integer values for that column.
I do have the debug setting set to DEVELOPER. When I run the query from Moodle, I do not observe any error output on the page.
Looks like the report_customsql_get_element_type() function is the starting point for my investigation. I will see where it leads.
Also, turn on Debugging to verify there is no error message.
Ad-hoc database queries plugin version: 3.0
I used this plugin to create the following query, which lists courses that are completed.
select u.username, c.shortname, rec.timecompleted, rec.timecompleted as cdate
from {course_completions} rec
inner join {user} u
on rec.userid = u.id
inner join {course} c
on rec.course = c.id
where rec.timecompleted is not NULL
A comment in the query editing page says:
| If a column name in the results ends with the characters date, and the column contains integer values,
| then they will be treated as Unix time-stamps, and automatically converted to human-readable dates.
The timecompleted field of the course_completions table in the database definitely contains integer values. And in my query, I tell it to have a column in the result whose name ends with 'date'.
However, when I run the query, my 'cdate' column is not getting human-readable date strings. I just get blanks.
Do you have any suggestions on what the problem is?
Thank you.