#!/usr/bin/env perl
## no critic 'RequirePodAtEnd'

=head1 NAME

RfC369L-dlbcl-sharon.pl - Email DLBCL queries to Sharon

=head1 USAGE

script/crons/weekly/RfC369L-dlbcl-sharon.pl -d 14 -t

=head1 DESCRIPTION

Run 2 dlbcl queries and email to Sharon as spreadsheet defaults to last 7 days
of results so if it fails one night, use -d from L</OPTIONS>

=head1 OPTIONS

=head2 C<-t>

Flag to put program in testing mode (no emails to end users

=head2 C<-d>

Option to set number of days to report on. Default is 7

=cut

use lib '/home/raj/perl5/lib/perl5';
use FindBin qw($Bin);    # warn $Bin;
use lib $Bin . '/../../../lib';

use HMDS::Perl;
use LIMS::Local::QueryMailer;
const my $DURATION => 7;    # no of days to report on

# get command line options and setup
use Getopt::Std;
getopts( 'd:t', \my %opt );    # week, testing
my $JUST_TESTING = $opt{t} || 0;    # email to ra.jones only

my $duration = $opt{d} || $DURATION;    # week
my $ref_date = DateTime->now->subtract( days => $duration )->ymd();

my @recipients = ( 'raj.secure', 'sharon.barrans.secure' );

# set SQLLibrary to use __DATA__
my $sql_lib = LIMS::Local::QueryLibrary->new( { lib => [<DATA>] } );

# initialise
my $query = LIMS::Local::QueryMailer->new( testing => $JUST_TESTING );

## drop/create/insert into tmp. used by both select queries
#my $sql = $sql_lib->retr('Temp');
#$query->reset_tmp_table($sql);

my $sql;

# execute both queries using date set to 7 days ago unless set on command line
foreach ( 'Patients with DLBCL', 'Patients without DLBCL' ) {
    $sql = $sql_lib->retr($_);
    $query->run( sql => $sql, bind => [$ref_date], sheet_name => $_ );
}

# process results and send email
$query->make_excel("dlbcl_from_$ref_date.xlsx");
$query->mail_results(
    recipients => \@recipients,
    subject    => 'Patients with/without DLBCL data this week'
);

# debugging
if ($JUST_TESTING) {
    $query->save_file('/tmp');
}

__DATA__
[Patients with DLBCL]
select r.request_number, r.year, p.last_name, p.first_name, p.dob, p.nhs_number,
    date(r.created_at) as 'registered', d.name as 'diagnosis', j.stype as 'specimen'
from requests r
    join patient_case pc on r.patient_case_id = pc.id
    join patients p on pc.patient_id = p.id
    join ( request_report_detail rrd join diagnoses d on rrd.diagnosis_id = d.id )
        on rrd.request_id = r.id
    join
    (
        select distinct(r2.id)
        from request_diagnosis_history rdh
            join diagnoses d2 on rdh.diagnosis_id = d2.id
            join requests r2 on rdh.request_id = r2.id
            join patient_case pc on r2.patient_case_id = pc.id
        where d2.name like 'High grade B-cell lymphoma%'
    ) as ftp on ftp.id = r.id
    inner join
    (  # get the specimen type
     #  this makes sure we are only reporting one diagnosis per request even when there are multiple specimens
     select rs.request_id
,  group_concat(st.specimen_type )  as 'stype'
     from request_specimen rs
     inner join specimens s on rs.specimen_id = s.id
     inner join specimen_sample_type sst on sst.specimen_id = s.id
     inner join sample_types st on st.id = sst.sample_type_id
     group by rs.request_id
    ) j  on j.request_id = r.id
where ( d.name like 'DLBCL%' or d.name like 'Diffuse large B-cell lymphoma%' )
    and r.created_at >= ?
order by pc.patient_id, r.id;

[Patients without DLBCL]
select r.created_at, r.request_number, r.year, p.last_name, p.first_name, p.dob, p.nhs_number, d.name as 'diagnosis'
, j.stype as "Specimen Type"
from requests r
    join patient_case pc on r.patient_case_id = pc.id
    join patients p on pc.patient_id = p.id
    join ( request_report_detail rrd join diagnoses d on rrd.diagnosis_id = d.id )
        on rrd.request_id = r.id
    join
    (
        select distinct(r2.id)
        from request_diagnosis_history rdh
            join diagnoses d2 on rdh.diagnosis_id = d2.id
            join requests r2 on rdh.request_id = r2.id
            join patient_case pc on r2.patient_case_id = pc.id
        where d2.name like 'High grade B-cell lymphoma%'
    ) as ftp on ftp.id = r.id
    inner join
    (  # get the specimen type
     #  this makes sure we are only reporting one diagnosis per request even when there are multiple specimens
     select rs.request_id
,  group_concat(st.specimen_type )  as 'stype'
     from request_specimen rs
     inner join specimens s on rs.specimen_id = s.id
     inner join specimen_sample_type sst on sst.specimen_id = s.id
     inner join sample_types st on st.id = sst.sample_type_id
     group by rs.request_id
    ) j  on j.request_id = r.id
where  (d.name not like 'DLBCL%' and d.name not like 'Diffuse large B-cell lymphoma%' )
and r.created_at >= ?
order by pc.patient_id, r.id;