package LIMS::Local::QueryMailer 0.01;

use lib '/home/raj/perl5/lib/perl5';
use FindBin qw($Bin);    # warn $Bin;
use lib $Bin . '/../../../lib';
use HMDS::Perl;
use Import::Into;
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};
    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.