package LIMS::Local::QueryMailer 0.01;
use HMDS::Perl;
use Carp;
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();
}
sub run {
my ( $self, %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
};
return;
}
sub save_file {
my $self = shift;
my $dir = shift || q{};
if ( $dir =~ m{ [^/]\z }xms ) { # add a slash if path doesnt end with one
$dir .= q{/};
}
open my $fh, '>:', $dir . $self->excel->{filename};
binmode $fh;
print {$fh} $self->excel->{workbook}
or croak q{couldn't write to file}
. $self->excel->{workbook}
. ":$OS_ERROR\n";
close $fh;
return;
}
sub make_excel {
my $self = shift;
my $filename = shift || 'output.xlsx'; # result index
croak 'no results to write' if !scalar @{ $self->results };
$self->excel->{filename} = $filename;
## no critic 'InputOutput::RequireBriefOpen'
open my $fh, '>:', \$self->excel->{workbook};
binmode $fh;
my $workbook = Excel::Writer::XLSX->new($fh);
my $bold = $workbook->add_format( bold => 1 );
# count rows to test if spreadsheet is empty
$self->excel->{_rows_in_output} = 0;
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 croak 'Failed to close Excel document';
close $fh;
return;
}
sub mail_results {
my ( $self, %params ) = @_;
my $recipients = $params{recipients};
my %mail = (
config => $tools->config,
subject => $params{subject},
filename => $self->excel->{filename},
attachment => $self->excel->{workbook},
);
if ( !$self->excel->{_rows_in_output} ) {
$mail{message} = 'Query produced no results during this period';
}
$tools->send_mail( \%mail, $recipients ) or croak;
return;
}
__PACKAGE__->meta->make_immutable;
# reduce the boilerplate for these crons
sub import {
my $target = caller;
LIMS::Local::ScriptHelpers->import::into($target);
LIMS::Local::QueryLibrary->import::into($target);
# SQL::Library->import::into($target);
lib->import::into($target);
return;
}
1;
__END__
=pod
=encoding UTF-8
=for stopwords QueryMailer
=head1 NAME
LIMS::Local::QueryMailer - Handles mailing Excel files of DB results to recipients
=head1 VERSION
Version 0.01
=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.
=over 1
=item 1) run a query
=item 2) make an Excel file
=item 3) email it to a user
=back
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 file system instead of emailing it.
=begin comment
=head1 Attributes
=head2 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
=head2 results
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,
}
=head2 excel
HashRef
{
filename => 'excel document filename' ,
workbook => 'binary data' ,
_rows_in_output => 'used to check there is some data from query'
}
=head2 dbix
DBIx::Simple object
=end comment
=head1 SUBROUTINES/METHODS
=head2 new(testing=0|1)
Test mode can be set in the constructor
=head2 run($sql, \@bind, $sheet_name)
Run query and push it onto results stack
=head2 save_file($dir)
Saves an Excel file with the name given to C<make_excel()>
=head2 make_excel($filename)
Generates an Excel document in memory for later sending or saving.
Stored in string $self->excel->{workbook}
=head2 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
=head1 DIAGNOSTICS
=over 4
=item save_file()
croaks "couldn't write to file" if it cant write to file
autodies on open and close if there is a filesystem issue
=item make_excel()
croaks "no results to write" if no queries have been run yet
autodies if it can't open string type FH to write
croaks "Failed to close Excel document" if Excel::Writer::XLSX wont close
=item mail_results()
croaks if LIMS::Local::ScriptHelpers->send_mail fails
=back
=head1 CONFIGURATION AND ENVIRONMENT
Uses HILIS configuration
=head1 DEPENDENCIES
=over
=item Moose
=item Excel::Writer::XLSX
=item use Const::Fast;
=item LIMS::Local::ScriptHelpers
=item LIMS::Local::QueryLibrary
=item HMDS
=back
=head1 BUGS AND LIMITATIONS
No known bugs. Please report to C<garry.quested@nhs.net>
=head1 AUTHOR
Garry Quested <garry.quested@nhs.net>
=head1 SEE ALSO
perl(1).
=head1 LICENSE AND COPYRIGHT
Copyright (C) 2018, HMDS. All Rights Reserved.
This script is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.
