Change log for the Ad-hoc database queries report

This version is compatible with Moodle 4.5+ and 5.0.

Changes in 4.4

• This version is compatible with Moodle 5.0.


• External APIs: Updated to use new base classes, as the minimum supported version is now Moodle 4.4.


• Fixed a bug so that users only see queries they have permission to view.


• Tidied up various parts of the codebase.


• Improved Behat tests and navigation URLs.


• PHPUnit: Removed unnecessary references to deprecated or removed plugins.


• Accessibility: Required form fields now use the default required field message.


• Fixed #114 — when downloading, return an empty CSV file (with headers) when there are no results.


• Fixed test queries for MySQL containing CHR characters.

Changes in 4.3

• The report now shows results for the most recent time the query should have run,
  making it clearer when there are no results.


• Improved the heading for the list of past results.


• Log events: Removed legacy logging methods for compatibility with Moodle 4.2.


• Added [wwwroot] to the email subject line to identify which environment sent the email.


• When emailing single-row queries, the number of rows sent is now limited to 5 to avoid building
  an overly large list over time.


• Fixed a bug to clean the file report name before generating downloads.


• The session is now unlocked, allowing the users to run more than one report at a time (#113).


• Removed underline on mouse hover

Changes in 4.2

• This version works with Moodle 4.0.


• There is now an option to fetch query results via webservice/pluginfile.php, once the permissions are set up right.


• Better navigation when there are a lot of reports. There is now a separate page for each category,
  as well as the main index page.


• When editing queries, the input box for the report name is bigger.


• Actions (e.g. edit) for reports are now shown on the parameters page, and are shown before the list
  of preview report runs, under scheduled reports, which is more convenient.


• Fixed a bug with the display of validation messages on the edit form.


• Added a missing index in the database.

Changes in 4.1

• We now track when each query was created and last modified, along with the user who modified it.
  (With the necessary Privacy API bits.)


• Fix the sort-order of reports on the index page.


• Fix the bug with list of email recipients being saved wrongly.


• Fix a bug when the value in a cell ended in a backslash.


• Minor styling fix.


• Updates to make everything work with Moodle 3.11.

Changes in 4.0

• Fix downloading for Moodle 3.9+.


• When editing queries, the SQL field is now styled more prettily.


• For scheduled reports, the list of users to email is now stored as a list of userids, rather than usernames.
  (Configuration of existing reports is automatically updated on upgrade.)

Changes in 3.9

• Scheduled reports which accumulate one row at a time now display
  with the most recent data at the top.


• When sending emails for scheduled reports, the number of rows in
  the results is now added to the subject line.

Changes in 3.8

• Report results can now be downloaded in any of data format that Moodle supports, not just CSV.


• Admins can control the maximum possible limit for the number of rows a query can return.


• Admins can now set which day is considered the first of the week for weekly reports.
  This defaults to the Moodle setting for this for new installs. For existing installs,
  it stays the same as before (Saturday) but you can change it.

Changes in 3.7

• If a report has query parameters, they can now be set in the URL
  (GET parameters) so you can bookmark a particular variant of a report.


• There is a parameter you can add to the URL of a report, so it is
  displayed with 'embedded' page layout, suitable for showing in an iframe.


• Also, change the page layout used in the normal case to 'report'.


• Query param values that are integers are now sent to the database as
  integers, which can lead to queries being optimised better.


• Fix an issue with showing the results of scheduled reports, when the
  report only produces data sometimes.


• Improve handling of 'pretty' column names.


• Fix compatibility with the messaging API changes in Moodle 3.6.


• Improve message default settings on install.


• Move hard-coded English string 'Run report' to the language file.

Changes in 3.6

• New feature for columns that are links. If the SQL query returns two
  columns name and name_link_url (for any value of name) then
  the that will be shown as a single column of links, where the visible
  link text comes from the name column, and the URL comes from the
  name_link_url column.


• The exact case of the column titles is extracted from the query. So,
  if your query is SELECT 'x' AS My_HTML, previously the table would
  display the column heading as 'my html'. Now it will show 'My HTML'.


• A summary of the number of rows output under the table.


• Moodle 3.3 compatibility re-established thanks to Paul Holden.


• Behat tests fixed for Moodle 3.6.

Changes in 3.5

• Privacy API implementation.


• Fix escaping of values in reports that contain HTML special characters.


• Fix bug where the report name was missing from scheduled task emails.


• Fix some coding style issues.


• Due to privacy API support, this version now only works in Moodle 3.4+
  For older Moodles, you will need to use a previous version of this plugin.

3.4 and before

Changes were not documented here.

Changes in 4.2

• This version works with Moodle 4.0.


• There is now an option to fetch query results via webservice/pluginfile.php, once the permissions are set up right.


• Better navigation when there are a lot of reports. There is now a separate page for each category,
  as well as the main index page.


• When editing queries, the input box for the report name is bigger.


• Actions (e.g. edit) for reports are now shown on the parameters page, and are shown before the list
  of preview report runs, under scheduled reports, which is more convenient.


• Fixed a bug with the display of validation messages on the edit form.


• Added a missing index in the database.

Change log for the Ad-hoc database queries report

Changes in 4.1

• We now track when each query was created and last modified, along with the user who modified it.
  (With the necessary Privacy API bits.)


• Fix the sort-order of reports on the index page.


• Fix the bug with list of email recipients being saved wrongly.


• Fix a bug when the value in a cell ended in a backslash.


• Minor styling fix.


• Updates to make everything work with Moodle 3.11.

Changes in 4.0

• Fix downloading for Moodle 3.9+.


• When editing queries, the SQL field is now styled more prettily.


• For scheduled reports, the list of users to email is now stored as a list of userids, rather than usernames.
  (Configuration of existing reports is automatically updated on upgrade.)

Changes in 3.9

• Scheduled reports which accumulate one row at a time now display
  with the most recent data at the top.


• When sending emails for scheduled reports, the number of rows in
  the results is now added to the subject line.

Changes in 3.8

• Report results can now be downloaded in any of data format that Moodle supports, not just CSV.


• Admins can control the maximum possible limit for the number of rows a query can return.


• Admins can now set which day is considered the first of the week for weekly reports.
  This defaults to the Moodle setting for this for new installs. For existing installs,
  it stays the same as before (Saturday) but you can change it.

Changes in 3.7

• If a report has query parameters, they can now be set in the URL
  (GET parameters) so you can bookmark a particular variant of a report.


• There is a parameter you can add to the URL of a report, so it is
  displayed with 'embedded' page layout, suitable for showing in an iframe.


• Also, change the page layout used in the normal case to 'report'.


• Query param values that are integers are now sent to the database as
  integers, which can lead to queries being optimised better.


• Fix an issue with showing the results of scheduled reports, when the
  report only produces data sometimes.


• Improve handling of 'pretty' column names.


• Fix compatibility with the messaging API changes in Moodle 3.6.


• Improve message default settings on install.


• Move hard-coded English string 'Run report' to the language file.

Changes in 3.6

• New feature for columns that are links. If the SQL query returns two
  columns name and name_link_url (for any value of name) then
  the that will be shown as a single column of links, where the visible
  link text comes from the name column, and the URL comes from the
  name_link_url column.


• The exact case of the column titles is extracted from the query. So,
  if your query is SELECT 'x' AS My_HTML, previously the table would
  display the column heading as 'my html'. Now it will show 'My HTML'.


• A summary of the number of rows output under the table.


• Moodle 3.3 compatibility re-established thanks to Paul Holden.


• Behat tests fixed for Moodle 3.6.

Changes in 3.5

• Privacy API implementation.


• Fix escaping of values in reports that contain HTML special characters.


• Fix bug where the report name was missing from scheduled task emails.


• Fix some coding style issues.


• Due to privacy API support, this version now only works in Moodle 3.4+
  For older Moodles, you will need to use a previous version of this plugin.

New version to be compatible with Moodle 3.5.

• Compatibility with Moodle 3.2 or later (thanks andrewhancox & markjohnson).


• New token %%S%% to easily include ; in the output (thanks lucaboesch).


• Better handling of multilang report names (thanks michael-milette & timhunt).


• Expand/collapse all categories link (thanks michael-milette & timhunt).


• Travis CI integration (thanks timhunt & moodle-plugin-ci).

Ad-hoc database queries

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.

Reports can contain placeholders, in which case, the user running the report is
presented with a form where they can enter the values to substitute for the
placeholders before running the report.

Scheduled reports can also be set to be send out be email whenever they are
generated.

If a column has a name ending in 'date' and contains integer values, then they
will be assumed to be unix timestamps, and formatted as dates. If a query
placeholder has a name ending in 'date', then users will be give a date-time
selector to input the value of that parameter.

You can set a limit on the maximum number of rows returned by a query
(up to the hard limit of 5000).

Reports can be grouped into categories, which helps when you have a lot of them.

See http://docs.moodle.org/25/en/Custom_SQL_queries_report for more information.

Written by Tim Hunt and converted to Moodle 2.0 by Derek Woolhead, both from
The Open University (http://www.open.ac.uk/). There have also been contibutions
but many others, as you can see in the git log.

To install using git, type this command in the root of your Moodle install
git clone git@github.com:moodleou/moodle-report_customsql.git report/customsql

This version of the report is compatible with Moodle 2.4 or later.

Ad-hoc database queries

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.

Reports can contain placeholders, in which case, the user running the report is
presented with a form where they can enter the values to substitute for the
placeholders before running the report.

Scheduled reports can also be set to be send out be email whenever they are
generated.

If a column has a name ending in 'date' and contains integer values, then they
will be assumed to be unix timestamps, and formatted as dates. If a query
placeholder has a name ending in 'date', then users will be give a date-time
selector to input the value of that parameter.

You can set a limit on the maximum number of rows returned by a query
(up to the hard limit of 5000).

Reports can be grouped into categories, which helps when you have a lot of them.

See http://docs.moodle.org/25/en/Custom_SQL_queries_report for more information.

Written by Tim Hunt and converted to Moodle 2.0 by Derek Woolhead, both from
The Open University (http://www.open.ac.uk/). There have also been contibutions
but many others, as you can see in the git log.

To install using git, type this command in the root of your Moodle install
git clone git@github.com:moodleou/moodle-report_customsql.git report/customsql

This version of the report is compatible with Moodle 2.4 or later.

Ad-hoc database queries

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.

Reports can contain placeholders, in which case, the user running the report is
presented with a form where they can enter the values to substitute for the
placeholders before running the report.

Scheduled reports can also be set to be send out be email whenever they are
generated.

If a column has a name ending in 'date' and contains integer values, then they
will be assumed to be unix timestamps, and formatted as dates. If a query
placeholder has a name ending in 'date', then users will be give a date-time
selector to input the value of that parameter.

You can set a limit on the maximum number of rows returned by a query
(up to the hard limit of 5000).

Reports can be grouped into categories, which helps when you have a lot of them.

See http://docs.moodle.org/25/en/Custom_SQL_queries_report for more information.

Written by Tim Hunt and converted to Moodle 2.0 by Derek Woolhead, both from
The Open University (http://www.open.ac.uk/). There have also been contibutions
but many others, as you can see in the git log.

To install using git, type this command in the root of your Moodle install
git clone git@github.com:moodleou/moodle-report_customsql.git report/customsql

This version of the report is compatible with Moodle 2.4 or later.

Ad-hoc database queries

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.

Reports can contain placeholders, in which case, the user running the report is
presented with a form where they can enter the values to substitute for the
placeholders before running the report.

Scheduled reports can also be set to be send out be email whenever they are
generated.

If a column has a name ending in 'date' and contains integer values, then they
will be assumed to be unix timestamps, and formatted as dates. If a query
placeholder has a name ending in 'date', then users will be give a date-time
selector to input the value of that parameter.

You can set a limit on the maximum number of rows returned by a query
(up to the hard limit of 5000).

Reports can be grouped into categories, which helps when you have a lot of them.

See http://docs.moodle.org/25/en/Custom_SQL_queries_report for more information.

Written by Tim Hunt and converted to Moodle 2.0 by Derek Woolhead, both from
The Open University (http://www.open.ac.uk/). There have also been contibutions
but many others, as you can see in the git log.

To install using git, type this command in the root of your Moodle install
git clone git@github.com:moodleou/moodle-report_customsql.git report/customsql

This version of the report is compatible with Moodle 2.4 or later.

Ad-hoc database queries

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.

Reports can contain placeholders, in which case, the user running the report is
presented with a form where they can enter the values to substitute for the
placeholders before running the report.

Scheduled reports can also be set to be send out be email whenever they are
generated.

If a column has a name ending in 'date' and contains integer values, then they
will be assumed to be unix timestamps, and formatted as dates. If a query
placeholder has a name ending in 'date', then users will be give a date-time
selector to input the value of that parameter.

You can set a limit on the maximum number of rows returned by a query
(up to the hard limit of 5000).

Reports can be grouped into categories, which helps when you have a lot of them.

See http://docs.moodle.org/25/en/Custom_SQL_queries_report for more information.

Written by Tim Hunt and converted to Moodle 2.0 by Derek Woolhead, both from
The Open University (http://www.open.ac.uk/). There have also been contibutions
but many others, as you can see in the git log.

To install using git, type this command in the root of your Moodle install
git clone git@github.com:moodleou/moodle-report_customsql.git report/customsql

This version of the report is compatible with Moodle 2.4 or later.

Ad-hoc database queries

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.

Reports can contain placeholders, in which case, the user running the report is
presented with a form where they can enter the values to substitute for the
placeholders before running the report.

Scheduled reports can also be set to be send out be email whenever they are
generated.

If a column has a name ending in 'date' and contains integer values, then they
will be assumed to be unix timestamps, and formatted as dates. If a query
placeholder has a name ending in 'date', then users will be give a date-time
selector to input the value of that parameter.

You can set a limit on the maximum number of rows returned by a query
(up to the hard limit of 5000).

Reports can be grouped into categories, which helps when you have a lot of them.

See http://docs.moodle.org/25/en/Custom_SQL_queries_report for more information.

Written by Tim Hunt and converted to Moodle 2.0 by Derek Woolhead, both from
The Open University (http://www.open.ac.uk/). There have also been contibutions
but many others, as you can see in the git log.

To install using git, type this command in the root of your Moodle install
git clone git@github.com:moodleou/moodle-report_customsql.git report/customsql

This version of the report is compatible with Moodle 2.4 or later.

Ad-hoc database queries

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.

Reports can contain placeholders, in which case, the user running the report is presented with a form where they can enter the values to substitute for the placeholders before running the report.

Scheduled reports can also be set to be send out be email whenever they are generated.

If a column has a name ending in 'date' and contains integer values, then they will be assumed to be unix timestamps, and formatted as dates. If a query placeholder has a name ending in 'date', then users will be give a date-time selector to input the value of that parameter.

You can set a limit on the maximum number of rows returned by a query (up to the hard limit of 5000).

See http://docs.moodle.org/25/en/Custom_SQL_queries_report for more information.

Written by Tim Hunt and converted to Moodle 2.0 by Derek Woolhead, both from The Open University (http://www.open.ac.uk/). There have also been contibutions but many others, as you can see in the git log.

To install using git, type this command in the root of your Moodle install git clone

git@github.com:moodleou/moodle-report_customsql.git report/customsql

This version of the report is compatible with Moodle 2.2 or later. (Tested on Moodle 2.4+.)

Ad-hoc database queries

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.

Reports can contain placeholders, in which case, the user running the report is
presented with a form where they can enter the values to substitute for the
placeholders before running the report.

Scheduled reports can also be set to be send out be email whenever they are
generated.

See http://docs.moodle.org/24/en/Custom_SQL_queries_report for more information.

Written by Tim Hunt and converted to Moodle 2.0 by Derek Woolhead, both from
The Open University (http://www.open.ac.uk/). There have also been contibutions
but many others.

To install using git, type this command in the root of your Moodle install
git clone git://github.com/timhunt/moodle-report_customsql.git report/customsql

This version of the report is compatible with Moodle 2.2 or later. (Tested on
Moodle 2.3+.)

Ad-hoc database queries

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.

Reports can contain placeholders, in which case, the user running the report is
presented with a form where they can enter the values to substitute for the
placeholders before running the report.

See http://docs.moodle.org/22/en/Custom_SQL_queries_report for more information.

Written by Tim Hunt and converted to Moodle 2.0 by Derek Woolhead, both from
The Open University (http://www.open.ac.uk/).

To install using git, type this command in the root of your Moodle install
git clone git://github.com/timhunt/moodle-report_customsql.git report/customsql

This version of the report is compatible with Moodle 2.2 or later.

Ad-hoc database queries

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.

Reports can contain placeholders, in which case, the user running the report is
presented with a form where they can enter the values to substitute for the
placeholders before running the report.

See http://docs.moodle.org/21/en/Custom_SQL_queries_report for more information.

Written by Tim Hunt and converted to Moodle 2.0 by Derek Woolhead, both from
The Open University (http://www.open.ac.uk/).

To install using git, type this command in the root of your Moodle install
git clone -b MOODLE_21_STABLE git://github.com/timhunt/moodle-report_customsql.git admin/report/customsql

This version of the report is compatible with Moodle 2.0.x or 2.1.x.

Ad-hoc database queries

This is a dump of the old MOODLE_19_CODE into git. This branch is no longer
maintained.

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.

See http://docs.moodle.org/19/en/Custom_SQL_queries_report for more information.

Written by Tim Hunt from The Open University (http://www.open.ac.uk/).

To install using git, type this command in the root of your Moodle install
git clone -b MOODLE_19_STABLE git://github.com/timhunt/moodle-report_customsql.git admin/report/customsql

This version of the report is compatible with Moodle 1.9.x.

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.

See http://docs.moodle.org/22/en/Custom_SQL_queries_report for more information.

Written by Tim Hunt and converted to Moodle 2.0 by Derek Woolhead, both from
The Open University (http://www.open.ac.uk/).

To install using git, type this command in the root of your Moodle install
git clone git://github.com/timhunt/moodle-report_customsql.git report/customsql

This version of the report is compatible with Moodle 2.2 or later.