#!/usr/bin/perl

=begin -------------------------------------------------------------------------
emails XL list of outreach requests with non-CMP sample type for previous month
=cut ---------------------------------------------------------------------------

use strict;
use warnings;

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

############ recipients from contacts.lib ######################################
my @recipients = qw(
	lth.commissioning.secure
	andy.rawstron.secure
	surita.dalal.secure
	raj.secure
);
################################################################################

BEGIN {
    use FindBin qw($Bin); # warn $Bin;
    use lib (
        "$Bin/../../../lib",
        '/home/raj/perl5/lib/perl5',
    );
}

use Data::Dumper;
use LIMS::Local::ScriptHelpers;
use Spreadsheet::WriteExcel::Simple;

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

#-------------------------------------------------------------------------------
my $ref_date = $tools->date_subtract( months => 1 );
my $subject  = sprintf 'Outreach patients [%s %s]',
    $ref_date->month_name, $ref_date->year; # warn $subject; exit;
my $filename = sprintf 'outreach_patients_%s_%02d.xls',
    $ref_date->year, $ref_date->month; # warn $filename; exit;

# xl file headers:
my @headers = qw( last_name first_name dob unit_number nhs_number registered
    location sample );
#-------------------------------------------------------------------------------

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

my $query = $sql_lib->retr('outreach_patients');

my $result = $dbix->query($query);    
while (my $row = $result->array) { # warn Dumper $row; next;
    push @rows, $row;
}

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

if (@rows) {
	my $xl = Spreadsheet::WriteExcel::Simple->new;
	$xl->write_bold_row(\@headers);
	$xl->write_row($_) for @rows; # $xl->save($filename); exit;
	
	$mail{attachment} = $xl->data;
    $mail{filename}   = $filename;	
}
else { # unlikely !!
	$mail{message} = q!No Outreach patients registered during this period.!;
}

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