# refactor cron jobs which all do the same thing
# 1) run a query
# 2) make an Excel file
# 3) email it to a user
package LIMS::Local::QueryMailer;
use Modern::Perl;
use autodie;
use DateTime;
use Import::Into;
use lib '/home/raj/perl5/lib/perl5';
use FindBin qw($Bin); # warn $Bin;
use lib $Bin . '/../../../lib';
use Excel::Writer::XLSX;
use LIMS::Local::ScriptHelpers;
use LIMS::Local::QueryLibrary;
# get tools from LIMS::Local::ScriptHelpers:
my $tools = LIMS::Local::ScriptHelpers->new();
use Moose;
use namespace::autoclean;
has testing => ( is => 'ro', isa => 'Int', default => 0 ); # flag JUST_TESTING
has results => ( # arrayref of hashes {sql=>'', bind => [] , db_output => DBIx::Simple::Result, sheet_name => ''}
is => 'ro',
isa => 'ArrayRef',
default => sub { [] }
);
has excel => ( # hashref {filename => 'excel document filename' , workbook => 'binary data' , _rows_in_output => 'used to check there is some data from query'}
is => 'ro',
isa => 'HashRef',
default => sub { {} }
);
has dbix =>
( is => 'ro', isa => 'DBIx::Simple', lazy => 1, builder => '_build_dbix' );
# connect to appropriate database - depends on testing/production
sub _build_dbix {
my $self = shift;
$tools->test_only( $self->testing );
return $tools->get_dbix();
}
# run query and push it onto results stack
sub run {
my $self = shift;
my %params = @_;
my $sql = $params{sql};
my $bind = $params{bind};
my $sheet_name = $params{sheet_name};
my $result = $self->dbix->query( $params{sql}, @{ $params{bind} } );
push @{ $self->results },
{
sql => $sql,
bind => $bind,
db_output => $result,
sheet_name => $sheet_name
};
}
# drop/create tmp incase it already exists
sub reset_tmp_table {
my $self = shift;
my $sql = shift;
$self->dbix->query('drop table if exists tmp');
$self->dbix->query('create table tmp (id int)');
$self->dbix->query($sql) if $sql;
}
# test method to output Excel file directly
sub save_file {
my $self = shift;
my $dir = shift || '';
$dir .= '/' if $dir =~ m{[^/]$}; # add a slash if path doesnt end with one
open my $fh, '>', $dir . $self->excel->{filename};
binmode($fh);
print $fh $self->excel->{workbook};
close $fh;
}
# build excel file from results and store in string $self->excel->{workbook}
sub make_excel {
my $self = shift;
my $filename = shift || 'output.xlsx'; # result index
die "no results to write" unless scalar @{ $self->results };
$self->excel->{filename} = $filename;
open my $fh, '>', \$self->excel->{workbook};
my $workbook = Excel::Writer::XLSX->new($fh);
my $bold = $workbook->add_format( bold => 1 );
$self->excel->{_rows_in_output} = 0; # count rows so we can test if spreadsheet is empty
foreach my $i ( @{ $self->results } ) {
my @column_titles = $i->{db_output}->columns; # p \@column_titles;
my $worksheet = $workbook->add_worksheet( $i->{sheet_name} );
$worksheet->write_row( 0, 0, \@column_titles );
my $y = 1;
foreach my $row ( $i->{db_output}->arrays ) {
$self->excel->{_rows_in_output}++;
$worksheet->write_row( $y++, 0, $row );
}
}
$workbook->close() or die "Failed to close Excel document";
}
# mail excel to recipients if not in testing mode
sub mail_results {
my $self = shift;
my %params = @_;
my $recipients = $params{recipients};
my %mail = (
config => $tools->config,
subject => $params{subject},
filename => $self->excel->{filename},
attachment => $self->excel->{workbook},
);
$mail{message} = "Query produced no results during this period" unless $self->excel->{_rows_in_output};
$tools->send_mail( \%mail, $recipients ) or die;
}
__PACKAGE__->meta->make_immutable;
# reduce the boilerplate for these crons
sub import {
my $target = caller;
Modern::Perl->import::into($target);
LIMS::Local::ScriptHelpers->import::into($target);
SQL::Library->import::into($target);
lib->import::into($target);
DateTime->import::into($target);
}
1;
__END__
=pod
=encoding UTF-8
=head1 NAME
LIMS::Local::QueryMailer - Handles mailing excel files of DB results to recipients
=head1 VERSION
version 1.0
=head1 SYNOPSIS
my $query = LIMS::Local::QueryMailer->new(testing => $JUST_TESTING);
$query->run( sql => $sql, bind => [$ref_date], sheet_name => $name );
$query->make_excel("dlbcl_from_$ref_date.xlsx");
$query->mail_results(
recipients => \@recipients,
subject => 'Patients with xyz this week'
);
=head1 DESCRIPTION
QueryMailer is designed primarily for cron jobs that query HILIS and email results in excel format.
QueryMailer can handle multiple queries. It makes a new worksheet per query.
When a QueryMailer object is initialised with a true value for its B<testing> attribute, it will not email the recipient. It does have a save_file method to write the excel file to your filesystem instead of emailing it.
=head1 Attributes
=over 4
=item has testing
flag testing mode. JUST_TESTING is usually set using Getopt:std -t option on the command line
This flag is passed on to LIMS::Local::ScriptHelpers to restrict emails and logging
=item has results
isa => 'ArrayRef[HashRef]'
results holds the query details and DBIx::Simple::Result returned by a query made by calling run(). It also stores the worksheet name for the results
{
$sql,
\@bind,
db_output => DBIx::Simple::Result,
$sheet_name,
}
=item has excel
HashRef
{
filename => 'excel document filename' ,
workbook => 'binary data' ,
_rows_in_output => 'used to check there is some data from query'
}
=item has dbix
DBIx::Simple object
=back
=head1 Methods
=over 2
=item * new(testing=0|1)
=item * run($sql, \@bind, $sheet_name)
=item * make_excel($filename)
Generates an Excel document in memory for later sending or saving.
Dies if no queries have been run yet
=item * mail_results(\@recipients, $subject)
Mails all recipients
If Excel file is empty, it informs recipient in the body of the email
Only mails raj if in testing mode
=item * save_file($dir)
Saves an Excel file with the name given to C<make_excel()>
=back
