Configurable Reports Block

Installation, Documentation, Tutorials....
See http://docs.moodle.org/en/blocks/configurable_reports/
Also http://moodle.org/mod/data/view.php?d=13&rid=4283

Author: Juan Leyva
<http://twitter.com/jleyvadelgado>

Thanks to:
Sara Arjona for being a co-maintainer
Nadav Kavalerchik for developing amazing new features
Ivan Breziansky for translating the block to slovak language
Iñaki Arenaza for translating the block documentation to spanish
Luis de Vasconcelos for testing the block
Adam Olley and Netspot Moodle Partner for improving some parts of the Moodle2 version

Some functionalities of this plugin uses code from:

Admin Report: Custom SQL queries
http://moodle.org/mod/data/view.php?d=13&rid=2884
By Tim Hunt


READ-ONLY MODE

The "Block report editing" plugin setting (block_edit) defaults to false. Enable it to prevent everyone,
including administrators, from creating, importing, editing, duplicating, deleting or changing report visibility.
Existing reports remain available with their filters, parameters, downloads and report definition exports.
Direct requests to editing and import endpoints are also blocked.

To enforce this in the site configuration, set:
$CFG->forced_plugin_settings['block_configurable_reports']['block_edit'] = true;


SQL REPORT SECURITY

A SQL report can read anything the database user it runs as can read, so treat the "managesqlreports" capability as
database access, and run the reports as a read only database user:

- Fill in all four remote database settings (dbhost, dbname, dbuser, dbpass) in the plugin settings, or force them in
  your site root /config.php with $CFG->forced_plugin_settings['block_configurable_reports'] = [...], with a database
  user that can only SELECT the tables the reports need. It must not read mdl_config, mdl_sessions,
  mdl_external_tokens or the password column of mdl_user, and must not be a superuser.
- When they are all set, SQL reports always run on that connection. The "Run on remote db" option of the report is
  ignored for SQL reports, so a report author cannot opt out of it. If only some of the four settings are filled in,
  SQL reports fail instead of using the main connection. With none of them, SQL reports run on the main connection
  (see the read only transaction below).
- Every query is checked each time it runs, not only when the report is saved, so imported reports are checked too. A
  query must start with SELECT, WITH or VALUES, and a keyword blocklist applies on top. A blocklist alone cannot be
  made safe, hence the database user above. SQL reports are read only: INSERT, INTO and CREATE are refused, and the
  $CFG->block_configurable_reports_enable_sql_execution setting of earlier versions no longer exists. There is no low
  security mode either: the "SQL Security" setting is gone and queries with a semicolon are always refused.
- Without the remote database settings, every query runs in a read only transaction (PostgreSQL and MySQL/MariaDB)
  on the main connection. If that cannot be done, because the database is another one or the code that runs the report
  already has a transaction open (web service calls do), the query is refused instead of running writable. Set up the
  remote database settings for these.
- A database error shows the report author only the start of the message, as the database repeats the values the query
  read in it. The whole message is written to the PHP error log of the server, and never to the page.


VERSIONS HISTORY

4.5.0 (2024051300) for Moodle 4.5
Release date: 06.10.2024
----------------------------------------------------------------------

## What's Changed
* fix webservice calls when missing user by @danielneis in jleyva#250
* Issue with downloading a filtered report by @luukverhoeven in jleyva#248
* Apply format_string on the breadcrumb navigation by @golenkovm in jleyva#181
* csv delimiter settings by @Tsheke in jleyva#214
* Set current courseid when importing a custom SQL report. by @reskit in jleyva#223
* Fixes jleyva#147 by @marcelorhmaia in jleyva#231
* User Profile Fields Language Filter Fix by @sameer-ah in jleyva#189
* Fix 201: Process report names / filenames through Moodle filters by @michael-milette in jleyva#238
* FIX-226: Stop getting string from report_customsql plugin by @michael-milette in jleyva#227
* Set BOM for CSV export closes #198 by @mvaraujo in jleyva#199
* Provide support for SYLK export #200 by @mvaraujo in jleyva#203
* Fix issue #62 Error with apostrophe by @TomoTsuyuki in jleyva#211
* Export multilanguage support by @Tsheke in jleyva#215
* Add embed options by @keevan in jleyva#182
* fix variable redeclaration by @danielneis in jleyva#256

## New Contributors
* @luukverhoeven made their first contribution in jleyva#248
* @golenkovm made their first contribution in jleyva#181
* @Tsheke made their first contribution in jleyva#214
* @reskit made their first contribution in jleyva#223
* @marcelorhmaia made their first contribution in jleyva#231
* @sameer-ah made their first contribution in jleyva#189
* @TomoTsuyuki made their first contribution in jleyva#211
* @keevan made their first contribution in jleyva#182


4.1.0 (2023120600) for Moodle 4.1
Release date: 06.12.2023
----------------------------------------------------------------------
This release focuses on solving issues in Moodle 4.1 and PHP 7.4 and start with small improvements and bug fixes.
- Reformat code
- Add CI testing based on Github Actions created by Catalyst IT
- Add PHP 7.4 support - minimum PHP version is now 7.4
- Add PHP 8.0 support
- Add PHP 8.1 support
- pChart library updated to version 2.4.0
- JS tablesorter library updated to version 2.31.3
- JS CodeMirror library updated to version 5.65.16
- Move repository to https://github.com/Lesterhuis-Training-en-Consultancy/moodle-block_configurablereports
- Original repository (https://github.com/jleyva/moodle-block_configurablereports) can be used for older release or use the old branches within this repository
- removed cr_add_to_log not used anymore
- Important this branch is not backwards compatible with Moodle 3.11 and lower

Thanks to Lesterhuis Training & Consultancy for the contribution / updated by Ldesign Media

- TODOS for the future:
  * Namespaces for classes & autoloading
  * Move all to `classes` to `classes/` directory
  * Clean up code
  * Add tests
  * AJAX_SCRIPT move to webservice
  * unserialize() to json_decode()
  * Readme to Markdown format
  * validation if a class exists and inherits the correct parent class.
  * Changelog to separate file
  * Move cron to a scheduled task
  * phpcpd and phpdocs checks in CI
  * Make CSS strict should only affect this plugin!


3.9.0 (2019122000) for Moodle 3.4, 3.5, 3.6, 3.7, 3.8, 3.9
Release date: Tuesday, 3 November 2020
----------------------------------------------------------------------
- Starting this version for allowing SQL queries performing data insertion/creation the following configuration variable set to 1
  is required in your site root /config.php:
  $CFG->block_configurable_reports_enable_sql_execution = 1;
  Otherwise, previous Custom SQL reports performing data insertion/creation will stop working.
- Added matching colors to pie charts
- Added unmapped palette for general colors to pie charts
- Changed fuserfield to allow for multiple instances of the filter
- Added alphabetical sort to courses filter
- Added webservice to get reports data
- Added new filters for competencies
- Other fixes and improvements:
  * Fix offset error for bar graphs when not using SQL reporting
  * Removed default sorting from DataTables JS UI
  * Fixed issue where colors would mismatch if values weren't present
  * Fixed STARTTIME and ENDTIME variable substitution
  * Avoid text filtering when exporting

Thanks Alex Rowe, David Saylor, Michael Gardener, Muhammad Osama Arshad, Daniel Poggenpohl, Daniel Neis, François Parlant and all the contributors who have sent several fixes and improvements.


3.8.0 (2019122000) for Moodle 3.4, 3.5, 3.6, 3.7, 3.8
Release date: Friday, 20 Dec 2019
----------------------------------------------------------------------
- Order the list of users in users filter by fullname considering language - #130
- Other fixes and improvements:
  * Fix template array error - #132
  * Fix DB mismatch between install.xml and upgrade.php - #123
  * Fix LIMIT error for MSSQL - CONTRIB-7891
  * Remove "_" from the table header titles - #137
  * Review supported Moodle versions, because the core_userlist_provider interface is only supported from 3.4 onwards - #125


Thanks Daniel Neis (danielneis), Mike McDougal (mcdoogs), Tuan Ngoc Nguyen (tuanngocnguyen), Mike Henry (mhenry79mnet), safatshahinsd, kristian-94 and all the contributors who have sent several fixes and improvements.
And special thanks also to Carlos, Moodle HQ and Juan for letting Sara work on this again during the project week.


3.3.0 (2019121900) for Moodle 3.0, 3.1, 3.2, 3.3
Release date: Friday, 20 Dec 2019
----------------------------------------------------------------------
- Order the list of users in users filter by fullname considering language - #130
- Add a new calculation type: percentage
- Allow userfields to be used more than once in the permissions
- Move JS to AMD modules
- Upgrade some 3rd party JS libraries
- Fix import from XML
- Review the year filter to take min and max years from the calendar factory
- Other fixes and improvements:
  * Fix template array error - #132
  * Fix DB mismatch between install.xml and upgrade.php - #123
  * Fix LIMIT error for MSSQL - CONTRIB-7891
  * Remove "_" from the table header titles - #137
  * Review supported Moodle versions, because the core_userlist_provider interface is only supported from 3.4 onwards - #125
  * Fix error when using templates
  * Fix error when using the Start/End filter
  * Fix legacy_polyfill error when running unit tests
  * Display custom title for the block
  * Replace deprecated methods (htmleditor, pix_url and coursecat)
  * Remove CSS files not found error in JS console
  * Show breadcrumbs for users without manage report capability
  * Fix DB query function call for the enrolled students filter
  * Raise the memory limit when exporting
  * Clean some request parameters before using them
  * Fix error with user field search box filter
  * Improve compatibility with PostgreSQL when importing


Thanks Daniel Neis (danielneis), Mike McDougal (mcdoogs), Tuan Ngoc Nguyen (tuanngocnguyen), Mike Henry (mhenry79mnet), safatshahinsd, kristian-94, David (davidpesce), Dimitrii (dmitriim), Alex (agrowe), Donald (emyb2), sopnep15, Danniel (dannielarriola) and all the contributors who have sent several fixes and improvements.
And special thanks also to Carlos, Sander, Moodle HQ and Juan for letting Sara work on this again during the project week.


3.7.0 (2019060300) for Moodle 3.4, 3.5, 3.6, 3.7
Release date: Monday, 3 Jun 2019
----------------------------------------------------------------------
- Add a new calculation type: percentage
- Allow userfields to be used more than once in the permissions
- Other fixes and improvements:
  * Fix error when using templates
  * Fix error when using the Start/End filter
  * Fix legacy_polyfill error when running unit tests
  * Display custom title for the block


Thanks David (davidpesce), Dimitrii (dmitriim) and all the contributors who have sent several fixes and improvements.
And special thanks also to Sander, Moodle HQ and Juan for letting Sara work on this again during the project week.


3.6.0 (2019021500) for Moodle 3.4, 3.5, 3.6
Release date: Friday, 15 Feb 2019
----------------------------------------------------------------------
- Implement Privacy API
- Replace deprecated methods:
  * htmleditor
  * pix_url
  * coursecat
- Move JS to AMD modules
- Upgrade some 3rd party JS libraries
- Fix import from XML
- Review the year filter to take min and max years from the calendar factory
- Other fixes and improvements:
  * Remove CSS files not found error in JS console
  * Show breadcrumbs for users without manage report capability
  * Fix DB query function call for the enrolled students filter
  * Raise the memory limit when exporting
  * Clean some request parameters before using them
  * Fix error with user field search box filter
  * Improve compatibility with PostgreSQL when importing

Thanks Alex (agrowe), Donald (emyb2), sopnep15, Danniel (dannielarriola) and all the contributors who have sent several
fixes and improvements.
And special thanks also to Sander, Moodle HQ and Juan for letting Sara work on this during the project week.



3.1.1 (2016020103) for Moodle 3.0, 3.1
Release date: Monday, 10 Oct 2016
----------------------------------------------------------------------
- Fix a critical bug when adding user action/outline statistics, see https://tracker.moodle.org/browse/CONTRIB-4600

Thanks Marina Glancy for the fix


3.1.0 (2016020102) for Moodle 3.0, 3.1
Release date: Wednesday, 25 May 2016
----------------------------------------------------------------------
- Several bug fixes


3.0.1 (2016020101) for Moodle 3.0
2.3.9 (2011040121) for Moodle 2.2, 2.3, 2.4, 2.5, 2.6, 2.7, 2.8, 2.9
Release date: Wednesday, 17 Feb 2016
----------------------------------------------------------------------
- Fix bug when creating the category list


3.0.0 (2016020100) for Moodle 3.0
Release date: Tuesday, 02 Feb 2016
----------------------------------------------------------------------
- Compatible version with Moodle 3.0


2.3.8 (2011040120) for Moodle 2.2, 2.3, 2.4, 2.5, 2.6, 2.7, 2.8, 2.9
Release date: Tuesday, 02 Feb 2016
----------------------------------------------------------------------
- Release including bug fixes


2.3.7 (2011040119) for Moodle 2.2, 2.3, 2.4, 2.5, 2.6, 2.7, 2.8, 2.9
Release date: Monday, 03 Aug 2015
----------------------------------------------------------------------
- Several bug fixes
- Fixed installation for old moodle versions
- Improved pie chats

Thanks CV&A and Carlos Escobedo for the contribution


2.3.6 (2011040118) for Moodle 2.2, 2.3, 2.4, 2.5, 2.6, 2.7, 2.8, 2.9
Release date: Wednesday, 27 May 2015
----------------------------------------------------------------------
- Minor release fixing a regression

Thanks to Dan Marsden for the fix


2.3.5 (2011040116) for Moodle 2.2, 2.3, 2.4, 2.5, 2.6, 2.7, 2.8, 2.9
Release date: Wednesday, 06 May 2015
----------------------------------------------------------------------
- Added support for Moodle 2.8 and 2.9
- Added support to the new logging system
- Improved the color palette for graphics with lots of elements
- Allow translations of DataTable strings
- Restored the fsearchuserfield plugin
- Minor issues fixes

Thanks albergasset and jpeak5 for their fixes


2.3.4 (2011040114) for Moodle 2.2, 2.3, 2.4, 2.5, 2.6, 2.7
Release date: Thursday, 26 June 2014, 18:16
----------------------------------------------------------------------
- New fullname user field column
- Several bug fixes


2.3.3 (2011040113) for Moodle 2.2, 2.3, 2.4, 2.5, 2.6, 2.7
Release date: Friday, 13 June 2014, 18:16
----------------------------------------------------------------------
- Fixed layout and notice/warnings problems


2.3.2 (2011040110) for Moodle 2.2, 2.3, 2.4, 2.5, 2.6
Release date: Friday, 14 February 2014, 11:14 AM
----------------------------------------------------------------------
- Several bug fixes


2.3.2 (2011040109) for Moodle 2.2, 2.3, 2.4, 2.5, 2.6
Release date: Thursday, 30 January 2014, 10:33 AM
----------------------------------------------------------------------
- Fixed invalid table reference in cron


2.3.2 (2011040108) for Moodle  2.2, 2.3, 2.4, 2.5m 2.6
Release date Thursday, 16 January 2014, 15:25 AM
----------------------------------------------------------------------
- Some bug fixes
- New CSV export


2.3.1 (2011040107) for Moodle 2.0, 2.1, 2.2, 2.3, 2.4, 2.5m 2.6
Release date Monday, 16 December 2013, 10:25 AM
----------------------------------------------------------------------
- Some minor bug fixes
- SQL syntax by default is disabled
- New block instance settings (change name and also show/hide global reports)
- New users cohorts condition


2.3 (2011040106) for Moodle 2.0, 2.1, 2.2, 2.3, 2.4, 2.5m 2.6
Release date Friday, 13 December 2013, 4:35 PM
----------------------------------------------------------------------
- Support for Moodle 2.6
- Multiple bugs fixed
- Global report that can be shared in all courses
- Public reports repository with multiple sample reports available
- Public SQL queries repository
- Reports can run on a different DB that the current (production) DB
- Reports can run on a CRON scheduler
- Several filter plugins added
- Integrated DataTables.js for the report table
- Integrated CodeMirror.js for highlighting SQL query code, while editing.
- New security settings

Thanks to Nadav Kavalerchik for providing most of the new features


2.2 (2011040105) for Moodle 2.0, 2.1, 2.2, 2.3, 2.4, 2.5
Release date Wednesday, 27 February 2013, 9:35 AM
----------------------------------------------------------------------
- Support for Moodle 2.4 and 2.5
- Bugs fixed


2.1 (2011040103) for Moodle 2.0, 2.1, 2.2, 2.3
Release date Friday, 6 July 2012, 1:29 PM
----------------------------------------------------------------------
- Support for Moodle 2.3
- Bugs fixed


2.0.2 (2011040102) for Moodle 2.0, 2.1, 2.2
Release date Monday, 9 January 2012, 11:41 AM
----------------------------------------------------------------------
- Support for Moodle 2.2
- Bugs fixed


2.0.1 (2011040101) for Moodle 2.0, 2.1
Release date Thursday, 13 October 2011, 12:55 AM
----------------------------------------------------------------------


2.0 (2011040100) for Moodle 2.0, 2.1
Release date Thursday, 29 September 2011, 10:47 AM
----------------------------------------------------------------------


1.0 (2010090100) for Moodle 1.9
Release date Thursday, 29 September 2011, 10:38 AM
----------------------------------------------------------------------


SECURITY OF SQL REPORTS

SQL reports run their query on the Moodle database, so whoever writes them can read any data in it, including session
ids, web service tokens and password hashes. Treat the block/configurable_reports:managesqlreports capability as
equivalent to site administration: only grant it to site administrators, and list the users allowed to write SQL
reports in the "SQL report users" (allowedsqlusers) setting. Queries run read only and are checked against a list of
keywords and functions, which is a safeguard and not a sandbox: for real isolation set up the report database
connection in the settings of the plugin, with a database user that can only read what reports need.