#!/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 = ?
