Your Moodle version

Reports: Ad-hoc database queries

report_customsql
Maintained by Tim at Lone Pine Koala Sanctuary Tim Hunt, at the OU (Perry building) Mahmoud Kassaei
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.
846
1k
25

This report, created by The Open University, lets Administrators set up arbitrary SQL select queries that anyone with the appropriate permissions can then run. Reports can be set to be runnable on-demand, or automatically run weekly or monthly.

The results are displayed as a fairly plain HTML table, and can also be downloaded as CSV.

The idea is that this lets you quicly set up ad-hoc reports, without having to create a whole new admin report plugin.

Screenshots

Screenshot #0

Contributors

Tim at Lone Pine Koala Sanctuary
Tim Hunt (Lead maintainer)
at the OU (Perry building)
Mahmoud Kassaei: Developer
Please login to view contributors details and/or to contact them

Comments RSS

Show comments
  • Picture of Andrew C
    Tue, Mar 1, 2016, 7:11 PM
    Hi Tim, the reports are now running fine and the problem was not connected to the plugin. I think something temporarily happened to the fileserver containing the reports which caused the error. Cheers for your help and for the excellent plugin.
  • Picture of Mike Finch
    Wed, Mar 9, 2016, 8:12 AM
    Moodle version: 3.0.2
    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.
  • Tim at Lone Pine Koala Sanctuary
    Wed, Mar 9, 2016, 3:49 PM
    Check directly in the database. Does this column actually contain integers, or blank (null)?

    Also, turn on Debugging to verify there is no error message.
  • Picture of Mike Finch
    Wed, Mar 9, 2016, 10:41 PM
    Re: Human-readable date strings not displaying

    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.
  • Tim at Lone Pine Koala Sanctuary
    Wed, Mar 9, 2016, 10:51 PM
    Yes. Sorry, I think you will need to debug this one yourself.
  • Picture of Mike Finch
    Thu, Mar 10, 2016, 12:29 AM
    Re: Human-readable date strings not displaying

    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” ?
  • Picture of Mike Finch
    Fri, Mar 11, 2016, 12:17 AM
    Re: Suggested fix for null context

    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.
  • Tim at Lone Pine Koala Sanctuary
    Fri, Mar 11, 2016, 1:04 AM
    Moodle's userdate is a wrapper around strftime, and http://php.net/manual/en/function.strftime.php says both %F and %T are fine. I don't know why it does not work for you. Anyway, that is the date format we want for this report.

    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?
  • Picture of Mike Finch
    Fri, Mar 11, 2016, 3:23 AM
    Re: Suggested fix for null context

    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.
  • Picture of Laura Robinson
    Tue, Mar 29, 2016, 10:46 PM
    I'm running Moodle 3.0.2 and am experiencing issues with the automatic email element of the plugin. I have tested with two different users, but as yet neither user has received a scheduled report email. I can see from the plugin interface that the daily query is running successfully and I can view the output within Moodle, but what's happened to the email is a mystery. Any tips on how to track down where the problem might lie (i.e. within Moodle/the plugin vs my mailserver?)
  • Picture of Brandon Jimenez
    Wed, Jun 15, 2016, 1:42 AM
    quick question that probably has been answered although i couldn't find it on the documentation: is it possible to create a paginated report? let's say i have 1000 records, i don't want to have an immense scroll. Thanks
  • Tim at Lone Pine Koala Sanctuary
    Wed, Jun 15, 2016, 1:43 AM
    Not possible.
  • Picture of Nicolas Dunand
    Thu, Jun 16, 2016, 9:50 PM
    Hi Tim,
    Will this officially support Moodle 3.1?
  • Tim at Lone Pine Koala Sanctuary
    Thu, Jun 16, 2016, 9:52 PM
    In due course, yet. Right now, when I ran the automated tests in 3.1, something failed. I don't remember what, and since then I have not had time to fix it. I don't think it will be anything very serious.

    If you have a test server, try running the current code with Moodle 3.1. It might acutally work.
  • Picture of Charles Fulton
    Tue, Jun 21, 2016, 12:27 AM
    I just ran the automated tests with 3.1.0 and didn't see any behat/phpunit problems. We're doing some manual testing at the CLAMP Hack/Doc now and we'll report back if there are problems.
1 2 3 4 5 6 7 8 9
Please login to post comments