Use Cron to send custom SQL report results as Excel attachment via email

Use Cron to send custom SQL report results as Excel attachment via email

by Tim TO. -
Number of replies: 12

Hello there,

I've started on a local plugin to build with, knowing it's better than hacking the core code for upgrades down the road, but wanted to know if anyone has input to share on the best way to do the following:

  1. Automate an email via CRON to send custom SQL results (would love to do it via the "Configurable reports block plugin" in the admin, but can't seem to see it and don't want to update the module's code so our changes won't be overridden in the future upgrades.
  2. Send the email to a recipient (to be managed as an editable field in the admin) with the SQL results as an Excel attachment, to be sent daily and only include the results from the day before.


Anyone have ideas on the best way to implement this? I'll continue to build, but thought I'd reach out to the community to see if there's a better solution.


Possibly just setting up a moodle/cron.php file in the main directory and calling it via a cronjob at midnight every night.

Cheers!

Average of ratings: -
In reply to Tim TO.

Re: Use Cron to send custom SQL report results as Excel attachment via email

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Have you seen this plugin: https://moodle.org/plugins/view/report_customsql

Average of ratings: -
In reply to Tim Hunt

Re: Use Cron to send custom SQL report results as Excel attachment via email

by Tim TO. -
Hi Tim,

Thanks for the note - I’ve checked this out. Is there the ability to choose to only include yesterday’s results (as a filter) and an editable address to email the automated cron sql results file (as an attachment)?

It seems some custom dev will be needed regardless with this plugin too, correct? (Currently using the custom_reports plugin.)

Cheers!
Average of ratings: -
In reply to Tim TO.

Re: Use Cron to send custom SQL report results as Excel attachment via email

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Sending only yesterday's data is just a matter of writing the right SQL query.

Average of ratings: -
In reply to Tim Hunt

Re: Use Cron to send custom SQL report results as Excel attachment via email

by Tim TO. -
Hi Tim,

I’ve got this working when running remotely, but when running within a plugin, the timestamps don’t seem to be working. Do you see any changes needed in this query?


Thanks for your assistance.
Average of ratings: -
In reply to Tim TO.

Re: Use Cron to send custom SQL report results as Excel attachment via email

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Note that, when you set up the query in the report, there are two tokens %%STARTTIME%% %%ENDTIME%% that give you the start and end time of the period the report should be run for.

I don't know exactly why the query is failing. There are some issues like the all Moodle columns have lower-case names, but you have referred to st.VALUE.

I suggest you temporarily set Debugging to DEVELOPER level, then you should get a fill error message, if there is one.

Average of ratings: -
In reply to Tim Hunt

Re: Use Cron to send custom SQL report results as Excel attachment via email

by Tim TO. -
Hi Tim, no errors with debugging turned on. I’m curious - how would you write a query to get all of yesterday’s SCORM completions? It’s when I’m trying to narrow the results within the specific date (separate file, being called by the cron every night) when I try and use those two lines. When using the query within the admin interface, adding the two lines you suggested works, but what about when trying to call on cron as a separate file?

Cheers for your notes.
Average of ratings: -
In reply to Tim Hunt

Re: Use Cron to send custom SQL report results as Excel attachment via email

by Tim TO. -
Hey Tim,


Do you think you could come up with the correct/appropriate way to use your plugin and the SQL code that should get all of "yesterday's SCORM activity completions" that will work with your module? Wasn't sure how you'd state the to/from dates dynamically on a scheduled report...

Thanks!

Average of ratings: -
In reply to Tim TO.

Re: Use Cron to send custom SQL report results as Excel attachment via email

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I think you need to set the report to be run daily at midnight.

Then you need a query like this:

SELECT displayname, to_timestamp(lastrun), lastexecutiontime
FROM prefix_report_customsql_queries
WHERE runable != 'manual'
AND lastrun > %%STARTTIME%% AND lastrun < %%ENDTIME%% 
ORDER BY lastrun DESC

(I don't know the SCORM database structure. This example query shows all the customsql queries that were run by the schedule in the last day.)

Average of ratings: -
In reply to Tim Hunt

Re: Use Cron to send custom SQL report results as Excel attachment via email

by Tim TO. -
Hi Tim -

Good thanks for your reply and effort here. I'll try and work with that example and may post when I get it working. The field 'timemodified' use saved as a BIGINT (10) field under the  scorm_track table, and was having issues getting that time format to compare to a query that uses only yesterday's date, EST for example.

When running through the plugin, shall anything be selected specifically? I'm assuming no dates need picked so it won't always grab static dates, is that true?

Thanks again!

Average of ratings: -
In reply to Tim Hunt

Re: Use Cron to send custom SQL report results as Excel attachment via email

by Tim TO. -

Hi Tim,

As you check out this SQL, can you help determine why I'm receiving the following error?


Here's the SQL: http://pastie.org/private/hht0may38bxfqqirxmlokq

Here's the error I'm receiving when trying to save in the admin view: http://pastie.org/private/rt3qa04inwavwoppsecp3a


Would love your assistance on this asap! Cheers for your contributions to the community thus far.

Average of ratings: -
In reply to Tim TO.

Re: Use Cron to send custom SQL report results as Excel attachment via email

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

You have WHERE twice. Second one should be AND.

Average of ratings: -
In reply to Tim Hunt

Re: Use Cron to send custom SQL report results as Excel attachment via email

by Tim TO. -
Ah, sorry - that must have gotten in there just while I was trying different things.

Can you get this to work locally for you - changing out the name of the first field to another in the same table and then changing the course name to match what you have locally (though course ID would probably be better to use).

Thanks for the second pair of eyes. Really hoping to wrap this up asap and would love to use the plugin.

Other questions;
1. In order to send to a second address, can we just separate with commas? Or do I need to adjust the php code in the module (moving it into the local directory and making changes to it which I might need to do anyway for other functionality we need)?

2. Although I'm running the main Moodle Cron every 10 minutes, it doesn't seem to be triggering the email. I'll play with this more but the clock on the server is far off. Maybe it's best to find a way to set the local time stamp in your module and make that an option for the end-user?

3. Maybe you can take a screenshot or screencast or just note all the details needed in order to set this up after install to get it working at a basic level, with examples and screenshots of a basic setup and then one with two email recipients and a way to test the email sending from within the module.

4. What if the user wanted to input their own time for when the cron gets triggered? Does it need to match a time interval (such as every 10 minutes, not 11) so that it gets triggered by the main cron running Moodle (have that running every 10 minutes). (...All this would be helpful to add to the documentation on the wiki id imagine).

5. Is there a way to update the email content, subject line, etc. from within the admin? We're also going to need to send an email even if there are no results but with different verbiage. So more than likely we'll use the great work you've done (and can still maybe help out with) and move it to the local directory to make (and track) all our changes.

We really dig the work you've done - thanks for contributing it to the community.
Average of ratings: -