[Koha] How to receive reports to email

[Paul Hoffman]

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.

-- 
Paul Hoffman <paul at flo.org>
Software Manager
Fenway Library Organization
550 Huntington Ave.
Boston, MA 02115
617-989-5032