#!/usr/bin/env perl

use strict;
use warnings;

# 0600 on Mon, Thurs, Sat

my $JUST_TESTING = 0; # dumps xl file only

# recipients ===================================================================
my @recipients = qw(
	rf.cytogenetics
	e.nacheva
);
# ==============================================================================

BEGIN {
	use lib (
		'/home/raj/perl5/lib/perl5', # *must* come before FindBin($Bin)
		'/home/raj/perl-lib',
	);
 	use FindBin qw($Bin); # warn $Bin;
 	use lib $Bin . '/../../../lib';
}

use Spreadsheet::WriteExcel::Simple;
use LIMS::Local::ScriptHelpers;
use Data::Printer alias=>'p';
use Local::DB;
use IO::All;

my $tools  = LIMS::Local::ScriptHelpers->new();
my $config = $tools->config();
my $today  = $tools->time_now;
$tools->test_only($JUST_TESTING);

#-------------------------------------------------------------------------------
my $filename = sprintf 'outstanding_rfh_%s.xls', $today->ymd('_'); # warn $filename;
my $subject  = sprintf 'Outstanding RFH requests [%s]', $today->dmy; # warn $subject; exit;
#-------------------------------------------------------------------------------

my $dbix = Local::DB->dbix({ dbname => 'uclh' });
$Local::QueryLogger::NO_QUERY_LOGS = 1; # don't need queries in logs dir

my $sql = _get_query();
my $data = $dbix->query($sql)->arrays; # p $data;

my $xl = Spreadsheet::WriteExcel::Simple->new;

# xl file headers:
my @headers = ( 'Request Number', 'BORIS ID', 'Initials', 'Date of Birth',
	'Date Created', 'Specimens', 'Tests', 'Section', 'Status',
);
$xl->write_bold_row(\@headers);
$xl->write_row($_) for @$data;

if ($JUST_TESTING) {
    $xl->save($filename);  exit;
}

my %mail = (
	config  => $config,
	subject => $subject,
	filename   => $filename,
	attachment => $xl->data,
); # p %mail; p @recipients;
$tools->send_mail(\%mail, \@recipients);

sub _get_query {
  return q!
	SELECT
		CONCAT('U',r.request_number,'/',RIGHT(r.year,2)) as 'Request Number',
		(SELECT lsfid.foreign_id
			FROM request_lab_section_foreign_id lsfid
			WHERE ts.request_id=lsfid.request_id AND lsfid.lab_section_id=4) AS 'BORIS ID',
		UPPER(CONCAT(LEFT(p.first_name, 1),LEFT(p.last_name, 1))) as 'Initials',
		DATE_FORMAT(p.dob,'%d-%m-%Y') as 'Date of Birth',
		DATE_FORMAT(r.created_at,'%d-%m-%Y') as 'Date Created',
		GROUP_CONCAT(spec.sample_code SEPARATOR ", ") AS 'Specimens',
		tests.field_label as 'Tests',
		labs.section_name as 'Section',
		ltso.description as 'Status'
	FROM
		request_lab_test_status ts
		JOIN lab_test_status_options ltso on (ts.status_option_id = ltso.id)
		JOIN lab_tests tests on (ts.lab_test_id = tests.id)
		JOIN lab_sections labs on (labs.id = tests.lab_section_id)
		JOIN requests r on (r.id = ts.request_id)
		LEFT JOIN request_specimen rs on (rs.request_id = r.id)
		JOIN specimens spec on (spec.id = rs.specimen_id)
		JOIN patient_case pc on (r.patient_case_id = pc.id)
		JOIN patients p on (pc.patient_id = p.id)
		JOIN referral_sources refs on (pc.referral_source_id = refs.id)
		JOIN status_options so on (r.status_option_id = so.id)
	WHERE
		ltso.description LIKE '%RFH%'
		AND (
			labs.section_name = 'CG/FISH' OR
			labs.section_name = 'Gene Variant Screening'
		)
	GROUP BY ts.id
	ORDER BY r.year, r.request_number, labs.section_name!
}