#!/usr/bin/env perl

=begin -------------------------------------------------------------------------
details of CLL ARCTIC & ADMIRE trials
=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 = ('lisa.worrilow'); # no patient data sent
my $duration   = $opt_d || 7; # 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 { join ' :: ', @$_ } @data; # warn Dumper \@rows;

    my %mail = (
        config  => $config,
        subject => 'HMDS ARCTIC/ADMIRE sample alert',
        message => join "\n", @rows,
    ); # p $mail{message};

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

__DATA__
select concat(r.request_number, '/', r.year - 2000),
    ct.trial_name, date(r.created_at)
from requests r
    join ( request_trial rt join clinical_trials ct on rt.trial_id = ct.id )
    	on rt.request_id = r.id
where ct.trial_name in ('NCRI CLL ADMIRE', 'NCRI CLL ARCTIC')
	and date(r.created_at) >= date_sub(curdate(), interval ? day)
