Send reports per mail periodically

[Philluxx]

A functionality to send reports per mail periodically would be nice. For example in the report header section repicient and period can be provided. Reports then can be send via cronjob (symfony command component?).

[jdorn]

The ability to subscribe to reports has definitely been on my todo list for a while. I don't think I would implement this as a report header though.

A fairly simple solution would be adding a setting in config.php like the following:

return array(
  'subscriptions'=>array(
    array(
      'to'=>array('[email protected]'),
      'subject'=>'Monthly Status Report',
      'reports'=>array(
        array(
          'report'=>'path/to/report.sql',
          'macros'=>array(
             'start'=>'-1 month'
          )
        )
      ),
      'frequency'=>"00 00 1 * *"
    )
);

Then, there would be a cron.php script that emails out subscriptions at the right times. To set it up, you would just have to create a crontab on your system and have it run cron.php every so often.

If you define multiple reports, it should combine them all in a single email and attach a CSV file for each one.

[brandom]

I have implemented this using a separate schedule.json config file. I can get it together in a proper pull request.

Sent from my iPhone

On Oct 29, 2013, at 7:08 PM, Jeremy Dorn [email protected] wrote:

The ability to subscribe to reports has definitely been on my todo list for a while. I don't think I would implement this as a report header though.

A fairly simple solution would be adding a setting in config.php like the following:

return array( 'subscriptions'=>array( array( 'to'=>array('[email protected]'), 'subject'=>'Monthly Status Report', 'reports'=>array( array( 'report'=>'path/to/report.sql', 'macros'=>array( 'start'=>'-1 month' ) ) ), 'frequency'=>"00 00 1 * *" ) ); Then, there would be a cron.php script that emails out subscriptions at the right times. To set it up, you would just have to create a crontab on your system and have it run cron.php every so often.

If you define multiple reports, it should combine them all in a single email and attach a CSV file for each one.

— Reply to this email directly or view it on GitHub.

[uded]

I was thinking about the same feature, although I had a bit more complex scenario in mind. @brandom - if you have a pull request you can post it for us to see. Maybe it will be just enough for now ;-)

[blades]

this is something i need for a project i'm working on; i'm planning on having a background cron job to check the stored run time information as the end user (an external client) will want to be able to configure the reports and run times themselves. in other words, i'll likely implement a sort of cron timer style interface with a mechanism for storing the information required to run the reports (e.g. for a daily sales report, i'll need to know that the date range is from midnight yesterday to midnight today, and retrieve that information alongside the run time).

if i can work out how to run a report to generate a type of file (e.g. csv, xls, json etc) automatically via a simple request of some sort (http post, or php call) then it's really just a case of working out how to store and populate the information.

[blades]

and, to be fair, a bit of mucking about had me working that out reasonably quickly: it's possible via http get (and therefore probably post as well as it's using $_REQUEST). could likely do with some documentation around this area, as i've had to hunt through the code to work it out, but it's something along these lines:

/report/json/?report=arrivals/arrivals-parking.sql&macros[daterange][start]=2014-06-01+00:00:00&macros[daterange][end]=2015-06-03+23:59:59

will produce a json report from arrivals/arrivals-parking.sql for the daterange 2014-06-01 to 2015-06-04. i've not done any more testing yet, but presumably the macros array is going to map it directly into the variables in the report without too much trouble.

[brandom]

https://gist.github.com/brandom/ebf2045e026df66acc19

@blades Here is my (hacky) implementation for a reference if it helps. It is in need of a major refactoring but hopefully it is readable enough to give you some ideas. I am using MEAN stack primarily and haven't touched PHP in quite a while but if I remember correctly the main problem with this implementation is that the schedule.json has to be rewritten every time a report is run to update the ranges. There is obviously much better ways to handle this but this was written in haste and quickly forgotten b/c it worked, hah.

I also wrote a wrapper for included Swift dependency (can't remember why) which is not in the gist but used in cron.php

[blades]

swift's used for the email sending element; i can probably refactor that easily enough. i'd probably want to abstract out the email side of things as we've got a back-end mailer that i'd probably want to use so it's all tracked nicely through that, but that's just a case of abstracting that out as no-one else would be remotely interested in that. that definitely gives some great starting points to build on, so thanks!