#!/usr/bin/perl

=begin -------------------------------------------------------------------------
emails XL list of all trial cases (except HTC & Gallium) 
=cut ---------------------------------------------------------------------------

use strict;
use warnings;

my $JUST_TESTING = 0; # email to ra.jones only

############ usernames from users table #######################################
my @recipients = qw( blythe raj bagguley ); # rawstron hillmen
################################################################################

use lib '/home/raj/perl5/lib/perl5';
use Data::Dumper;
use Sort::Naturally;
use Spreadsheet::WriteExcel::Simple;

use FindBin qw($Bin); # warn $Bin;
use lib $Bin . '/../../../lib';
use LIMS::Local::ScriptHelpers;

my $cfg_file = "$Bin/../lib/settings.cfg"; 
my $settings = Config::Auto::parse($cfg_file); # warn Dumper $settings;

# get tools from LIMS::Local::ScriptHelpers:
my $tools = LIMS::Local::ScriptHelpers->new();
$tools->test_only($JUST_TESTING);

#-------------------------------------------------------------------------------
my $month = $tools->date_subtract( months => 1 ); # date 1 month ago

my $subject = sprintf 'Trial cases for %s %s', $month->month_abbr, $month->year;
# warn $subject; exit;
my $filename = 'trial_cases.xls';

# xl file headers:
my @headers = qw( request_number year last_name first_name dob location
    trial_name presentation registered specimen );
#-------------------------------------------------------------------------------

my $sql_lib  = $tools->sql_lib();
my $config   = $tools->config(); 
my $dbix     = $tools->dbix();

# get SQL statements for query:
my $sql = $sql_lib->retr('trial_cases');

my $result = $dbix->query( $sql );

my @data;
while ( my @row = $result->list ) { # warn Dumper \@row; next;
    push @data, \@row;
} # warn Dumper \@data; exit;

# flag to Local::Mail::_verify_service_status() that msg safe to send:
$config->{_safe_message} = 1 if ! $config->{is_in_production_mode};

my %mail = (		
	config  => $config,
	subject => $subject,
); 

if (@data) {
	my $xl = Spreadsheet::WriteExcel::Simple->new;
	$xl->write_bold_row(\@headers);
	$xl->write_row($_) for @data; # $xl->save("trial_cases.xls"); exit;
	
	$mail{attachment} = $xl->data;
    $mail{filename}   = $filename;	
}
else {
	$mail{message} = sprintf 'No trial cases for %s %s',
        $month->month_abbr, $month->year;
} 

$tools->send_mail(\%mail, \@recipients);