It appears that for stylistic reasons Moodle is lowercasing all the column names returned from the database queries. This seems like standard behavior in the system. Is there any way to make it respect the case that the query is returning in column names/aliases? In this case the client wants some columns capitalized to match their existing spreadsheet structure.
I don't think it is a good idea to try to do this by changing the Moodle DB layer. Instead I would do it just in the report output code.
I have only ever wanted this for cosmetic reasons, and so it has never been worth the bother. However, I just had a thought about how to do this: In the output code, search in the text of the query for something that matches case-instensitively the column name you got back from the database (which we know is all lower-case). Then, for the title, use that bit of the original query (which might have mixed case).
Acutally, that is pretty easy to do. Is anyone able to test this: https://github.com/moodleou/moodle-report_customsql/commit/wip-prettier-names
Wow, thanks for the fast response. I haven't tested your code yet, but I'm looking a little at what in PDO might cause this,
http://php.net/manual/en/pdo.setattribute.php
PDO::CASE_LOWER is likely set on the database driver-- Maybe here?
https://github.com/moodle/moodle/blob/master/lib/dml/pdo_moodle_database.php#L74
Is there an opportunity to set it to PDO::CASE_NATURAL for the query and then back after it has run? That might be a cleaner way to do it. In the patch you proposed I see that you are getting the db handle, I'm wondering if setting the attribute will work after the query has executed but before fetch()... I will test locally and see.
http://php.net/manual/en/pdo.setattribute.php
PDO::CASE_LOWER is likely set on the database driver-- Maybe here?
https://github.com/moodle/moodle/blob/master/lib/dml/pdo_moodle_database.php#L74
Is there an opportunity to set it to PDO::CASE_NATURAL for the query and then back after it has run? That might be a cleaner way to do it. In the patch you proposed I see that you are getting the db handle, I'm wondering if setting the attribute will work after the query has executed but before fetch()... I will test locally and see.
FYI I tested and the setAttribute() must go before the execute() to have an effect in this way. I'll see if the client is willing to test your modification.