Custom SQL reports module
Synopsis : A module which allows admins to enter SQL reports for later use as CSV (or other) exports.
Why ?
I regularly have to write small custom modules which hold a number of custom SQL queries (when the query is more complex than views allows for), the end user then just hits a special url e.g. (/admin/csv-reports/downloads/) and gets a nice friendly CSV file to use as they please. In some cases there are also arguments e.g. /admin/csv-reports/downloads/10 - to show only downloads by user 10.
I think a nice interface to allow creation of these custom reports, inside Drupal would be brilliant, it could also then hook into the permissions system and even allow for a nicer interface for the end user, listing report or allowing help pages e.g. /admin/csv-reports/downloads/ being a page with a text input box for the UID with a description of what its for etc - perhaps even auto complete.
Reports could be a custom db table or even nodes with CCK (which makes permissions simpler)
I'm not sure when i might be able to write this, but wanted the idea out there in case a) its already been done or b) im missing some uber feature it might benefit from. Ideas and feedback very welcome.
:)
I could just say this straight to your face in IRC, but... someone else will write it otherwise I suppose.
I would hazard a guess that the reason such a module (probably) doesn't exist is for one of security?
Drupal Build & Development in Nottingham by Kineta Systems
Its not really much less
Its not really much less secure than the sql being hard wired in code, it will still be going though all the standard checks and balances.
Having PHP input formats is FAR less secure.
Hm
Perhaps you miss the point - couldn't one simple XSS exploit or a permissions mishap result in any user being able to query the DB for the admin password?
Drupal Build & Development in Nottingham by Kineta Systems
Passwords aren't stored are
Passwords aren't stored are they?
I suppose they could always do a 'Bobby Tables'...
I've written a module similar
I've written a module similar to this in house that we use. It isn't as feature rich as you are going for though. One of the things I have found though is that my SQL queries get really nasty whereas I could accomplish something similar just using php.
Secondly, most people are using CCK and Views so there really isn't a major need for a module like this because views is the sql query builder.
Perhaps making having the
Perhaps making having the processing of the saved query as sql or php, would allow for greater flexibility.
I agree with the views comment, but this is explicitly for when views isn't able to create the right results.
I did some in-page SQL and
I did some in-page SQL and CSV generation for a site a while back... it was ugly and they may well need more of this when they do another conference and again find that CiviCRM just doesn't give you much by way of useful data.
A simple way to do this would be with nodes.
Define a node type, add a form element with hook_form for the SQL query.
Then you can add a menu item at /node/NID/csv, give that its own permission for access, and have code that whips up the CSV there.
Add a node link or even a pretty icon to the node display for download for the finishing touch.
I'm doing it
http://drupal.org/project/customreports
Will post here once a release is out.