#!/usr/bin/env perl

=begin -------------------------------------------------------------------------
mails requests with flow summary rlike B-LPD - non-germinal centre phenotype (CD5-)
=cut ---------------------------------------------------------------------------

use Getopt::Std;
getopts('d:'); # days
our($opt_d); # warn $opt_d; exit;

use strict;
use warnings;

use lib '/home/raj/perl5/lib/perl5';
use Data::Printer;

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

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

################################################################################
my @recipients = ('ruth-de.tute.secure'); # no patient data sent
my $duration   = $opt_d || 1; # days
################################################################################
push @recipients, 'raj' if $JUST_TESTING; # will be sole recipient

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

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

my $sql = join "", <DATA>; # warn $sql; exit;

if ( my @data = $dbix->query($sql, $duration)->arrays ) { # warn Dumper @data;
    my @rows = map {
        $_->[2] =~ s/[\r\n]/ /g; # remove all new-lines from results summary
        join ' :: ', @$_;
    } @data; # warn Dumper \@rows;

    my %mail = (
        config  => $config,
        subject => 'B-LPD - non-GC phenotype sample alert',
        message => join "\n\n", @rows, # new-line + spacer line-break
    ); # p $mail{message};

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

__DATA__
select concat(r.request_number, '/', r.year - 2000), group_concat(s.sample_code),
    rrs.results_summary
from requests r
    join ( request_specimen rs join specimens s on rs.specimen_id = s.id )
        on rs.request_id = r.id
	join ( request_result_summaries rrs
        join lab_sections ls on rrs.lab_section_id = ls.id )
        	on rrs.request_id = r.id
where rrs.results_summary rlike
    'B-LPD - non-germinal centre phenotype [[.left-parenthesis.]]CD5-[[.right-parenthesis.]]'
	and ls.section_name = 'Flow cytometry'
	and date(rrs.time) = date_sub(curdate(), interval ? day)
group by r.id