[Koha] How to receive reports to email

[Katrin Fischer]

Hi,

I think it might not be well known, but Koha has already a command line
script that you can run as a cronjob to email you the results of any report:

http://git.koha-community.org/gitweb/?p=koha.git;a=blob;f=misc/cronjobs/runreport.pl

Hope this helps!

Katrin


On 14.02.19 18:33, Paul Hoffman wrote:
> On Thu, Feb 14, 2019 at 12:30:59PM +0530, SATISH wrote:
>> I need to keep print copy of daily transactions (cumulative) report for
>> check-out & check-in transactions that is from library
>> opening hours to closing hours every day. Currently I am running sql for
>> date range for check-in & check-out.
>>
>> [...]
>>
>> But, I am looking for using same sql report automatically through an email
>> on every day basis.
> Here's one way to do it.  Start with a file that contains the SQL for
> the report, preceded by a suitable e-mail header:
>
> --------------------- /path/to/your/reports/foobar ---------------------
> From: some.email.address at example.com
> To: some.other.email.address at example.com, etc.etc.etc at example.com
> Subject: The report you asked for
> Content-Type: text/plain
>
> SELECT ...
> FROM   ...
> WHERE  ...
> ------------------------------------------------------------------------
>
> Then write a Perl script that runs the report and sends the results in
> tab-delimited format:
>
> ----------------------- /path/to/run-reports.pl ------------------------
> #!/usr/bin/perl
>
> use strict;
> use warnings;
>
> use C4::Context;
>
> my $dbh = connect_to_database();
>
> # Expand file globs
> if (@ARGV == 1 && $ARGV[0] =~ /[*]/) {
>      @ARGV = glob(@ARGV);
> }
> die "No reports to run" if !@ARGV;
>
> # Run report(s)
> foreach my $file (@ARGV) {
>      open STDIN, '<', $file
>          or die "Can't open $file: $!";
>      my $header = read_header();
>      my $sql    = read_sql();
>      my $sender = find_sender($header);
>      my $sth    = prepare_sql($sql);
>      my $fh     = start_sendmail($sender);
>      print $fh $header;
>      print $fh columns_header($sth);
>      while (my @row = $sth->fetchrow_array) {
>          print $fh tab_delimited(@row);
>      }
>      close $fh or die "Close sendmail handle: $!";
>      close STDIN;
> }
>
> # Functions
>
> sub connect_to_database {
>      my $dbh = C4::Context->dbh;
>      $dbh->{RaiseError} = 1;
>      return $dbh;
> }
>
> sub find_sendmail {
>      foreach (qw(/sbin/sendmail /usr/sbin/sendmail /bin/sendmail /usr/bin/sendmail)) {
>          return $_ if -x $_;
>      }
>      die "Can't find sendmail";
> }
>
> sub read_header {
>      my $header = '';
>      while (<>) {
>          $header .= $_;
>          last if /^\r?$/;
>      }
>      return $header;
> }
>
> sub find_sender {
>      my ($header) = @_;
>      foreach (split /\n/, $header) {
>          return email($1) if /^From:\s+(.+)/;
>      }
>      die "No sender found in e-mail header";
> }
>
> sub email {
>      local $_ = shift;
>      return $1 if /^([^@\s]+\@[^@\s]+)\b/;
>      return $1 if /^.+ <([^<>\s]+)>/;
>      die "Can't find sender e-mail address";
> }
>
> sub read_sql {
>      # Read the SQL
>      local $/;
>      my $sql = <>;
>      return $sql;
> }
>
> sub prepare_sql {
>      my $sql = shift;
>      my $sth = $dbh->prepare($sql);
>      $sth->execute(@_);
>      return $sth;
> }
>
> sub start_sendmail {
>      my ($sender) = @_;
>      my $sendmail = find_sendmail();
>      open my $fh, '|-', $sendmail, qw(-oi -oem -t -f), $sender
>          or die "Can't exec $sendmail: $!";
>      return $fh;
> }
>
> sub columns_header {
>      my ($sth) = @_;
>      return join("\t", @{ $sth->{'NAME'} }) . "\n";
> }
>
> sub tab_delimited {
>      return join("\t", map { defined $_ ? $_ : '' } @_) . "\n";
> }
> ------------------------------------------------------------------------
>
> Then use koha-shell to run them:
>
> [as root]
> # koha-shell YOURINSTANCE -c '/path/to/run-reports.pl /path/to/your/reports/*'
>
> I'll leave the rest to you -- cron, etc.
>
> Paul.
>