Re: [Koha] How to receive reports to email

14 Feb 2019

      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/runr...

Hope this helps!

Katrin

On 14.02.19 18:33, Paul Hoffman 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
On Thu, Feb 14, 2019 at 12:30:59PM +0530, SATISH wrote:
the report, preceded by a suitable e-mail header:
--------------------- /path/to/your/reports/foobar ---------------------
From: some.email.address@example.com
To: some.other.email.address@example.com, etc.etc.etc@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.

Re: [Koha] How to receive reports to email

Katrin Fischer