# 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