#!/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;
