#!/usr/bin/perl

# emails details of NCG PNH cases screened, every 3 months, to specified recipients

use Getopt::Std;
getopts('qtm:'); # query to stdout, test, months
our($opt_m, $opt_t, $opt_q); # warn $opt_m; exit;

use strict;
use warnings;

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

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

use lib '/home/raj/perl5/lib/perl5';
use Data::Printer;
use SQL::Abstract::More;

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

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

#-------------------------------------------------------------------------------
my $duration = $opt_m || 3; # months

my $start = $tools->date_subtract( months => $duration ); # date 3 months ago
my $end   = $tools->date_subtract( days => 1 ); # yesterday

my $subject = sprintf 'HMDS NCG PNH cases for %s.%s to %s.%s',
	$start->month_abbr, $start->year, $end->month_abbr, $end->year; # warn $subject; exit;
#-------------------------------------------------------------------------------

my $sql_lib = $tools->sql_lib();
my $config  = $tools->config();
my $dbix    = $tools->dbix();
my $sqla    = SQL::Abstract::More->new;

# get SQL statements for query:
my ($sql, @bind) = ncg_pnh_query();

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

my @data;
while ( my @row = $result->list ) { # p @row; next;
    push @data, join ': ', @row;
}

{ # HTS myeloid tests:
    my ($sql, @bind) = ncg_hts_query();
    my $i   = $dbix->query( $sql, @bind )->list || 0;
    push @data, "HTS myeloid: $i";
} # p @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) {
	$mail{message} = join "\n", @data;
}
else {
	$mail{message} = 'No NCG PNH cases for 3 months to ' . $end->dmy;
} # p %mail; exit;

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

sub ncg_pnh_query {
    my @cols = ( 's2.sample_code', 'COUNT(s2.sample_code)' );
    my @rels = (                           'requests|r'                 ,
        'ris.request_id=r.id'           => 'request_initial_screen|ris' ,
        'ris.screen_id=s1.id'           => 'screens|s1'                 ,
        'rs.request_id=r.id'            => 'request_specimen|rs'        ,
        'rs.specimen_id=s2.id'          => 'specimens|s2'               ,
        q{rh.request_id=r.id, rh.action='screened'},
                                        => 'request_history|rh'         ,
    );
    my %where = (
        -or => [
            's1.description' => {  -like => 'NCG PNH %' }, # discontinued 03/2019
            's1.description' => {  -like => 'PNH NCG %' }, # new term
        ],
        's2.sample_code' => { -in => [ qw(PB BMA BMAT) ] },
        'DATE(rh.time)'  => { '>=' => $start->ymd },
    );
    my @args = (
		-columns  => \@cols,
		-from     => [ -join => @rels ],
		-where    => \%where,
        -group_by => 's2.id',
    ); # p @args;
    my ($sql, @bind) = $sqla->select(@args); # p $sql; p \@bind;
        $dbix->dump_query($sql, @bind) if $opt_q; # exit;
    return ($sql, @bind);
}

sub ncg_hts_query {
    my @cols = ( 'COUNT(*)' );
    my @rels = (                           'requests|r'                 ,
        'ris.request_id=r.id'           => 'request_initial_screen|ris' ,
        'ris.screen_id=s1.id'           => 'screens|s1'                 ,
        'ts.request_id=r.id'            => 'request_lab_test_status|ts' ,
        'ts.lab_test_id=lt.id'          => 'lab_tests|lt'               ,
        q{rh.request_id=r.id, rh.action='screened'},
                                        => 'request_history|rh'         ,
    );
    my %where = ( # don't care about sample type - only whether test is done
        -or => [
            's1.description' => {  -like => 'NCG PNH %' }, # discontinued 03/2019
            's1.description' => {  -like => 'PNH NCG %' }, # new term
        ],
        'lt.test_name'  => 'hts_myeloid',
        'DATE(rh.time)' => { '>=' => $start->ymd },
    );
    my @args = (
		-columns => \@cols,
		-from    => [ -join => @rels ],
		-where   => \%where,
    ); # p @args;
    my ($sql, @bind) = SQL::Abstract::More->new->select(@args); # p $sql; p \@bind;
       $dbix->dump_query($sql, @bind) if $opt_q; # exit;
    return ($sql, @bind);
}