#!/usr/bin/env perl
#===============================================================================
# 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 8' the
# next day to capture the results since last run
#===============================================================================
use FindBin qw($Bin); # warn $Bin;
use lib $Bin . '/../../../lib';
use LIMS::Local::QueryMailer;
# get command line options and setup
use Getopt::Std;
getopts('d:t'); # week, testing
our ( $opt_d, $opt_t ); # warn $opt_t; exit;
my $JUST_TESTING = $opt_t || 0; # email to ra.jones only
my $duration = $opt_d || 7; # week
my $ref_date = DateTime->now->subtract( days => $duration )->ymd();
my @recipients = ('raj.secure', 'sharon.barrans@nhs.net');
# set SQLLibrary to use __DATA__
my $sql_lib = new LIMS::Local::QueryLibrary { 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
$query->save_file('/tmp') if $JUST_TESTING;
__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 = ?
