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

=pod

=for stopwords sharon RfC

=head1 NAME

C<RfC370L-follicular-sharon.pl> - Email Follicular Lymphoma queries to Sharon

=head1 USAGE

C<script/crons/weekly/RfC370L-follicular-sharon.pl -d 14 -t>

=head1 DESCRIPTION

Run follicular lymphoma query 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

# this could all go in crontab to avoid hard coding user directories
# PERL5LIB='/home/raj/perl5/lib:/home/raj/apps/HILIS4/lib'
use lib '/home/raj/perl5/lib/perl5';
use FindBin qw($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 );

# execute both queries using date set to 7 days ago unless set on command line
my $sql = $sql_lib->retr('FL');
$query->run( sql => $sql, bind => [$ref_date], sheet_name => 'FL' );

# process results and send email
$query->make_excel("fl_from_$ref_date.xlsx");
$query->mail_results(
    recipients => \@recipients,
    subject    => 'FL or DLBCL data this week'
);

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

__DATA__
[FL]
select #r.id, #p.dob,
    concat('H',r.request_number,'/',r.year-2000) as LabNo, #p.nhs_number,
    timestampdiff(YEAR, p.dob,curdate()) as Age,
    d.name,
    group_concat(if(jlt.lab_section_id =7,concat(jlt.test_name,':',jrltr.result, ' '),'') separator '') as 'FISH tests',
    rr.tests as 'Immunohistochemistry Tests'
from requests r
    join patient_case pc on pc.id = r.patient_case_id
    join patients p on pc.patient_id = p.id
    left join request_lab_test_results jrltr  on jrltr.request_id = r.id
    left join lab_tests jlt on jlt.id = jrltr.lab_test_id
    join request_report_detail rrd on r.id = rrd.request_id
    join diagnoses d on rrd.diagnosis_id = d.id
    join (
    # get all r.ids that have had all the correct tests/results
    select r.id, group_concat(concat(lt.test_name,':',rltr.result) order by lt.test_name) as tests from requests r
        join request_lab_test_results rltr on rltr.request_id = r.id
        join lab_tests lt on lt.id = rltr.lab_test_id
    where
         lt.test_name in ('bcl6', 'cd10', 'irf4')
        and lt.lab_section_id = 2 # section 2 immunohistochemistry
        and rltr.result = '+'
    group by r.id
    having
        tests = 'bcl6:+,cd10:+,irf4:+'
    ) rr on rr.id = r.id
where
    (      d.name like 'Diffuse large B-cell lymphoma%'
    or     d.name like '%DLBCL%'
    or     d.name like '%follicular%lymphoma%'
    )
    and r.created_at >= ?
group by r.id
order by r.id

[DEBUG]
select * from requests r where r.id < 100 or r.created_at = ?