Re: [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.addr...@example.com
To: some.other.email.addr...@example.com, 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.

_______________________________________________
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha