#!/usr/bin/env perl
#===============================================================================
# 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 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 $tools = LIMS::Local::ScriptHelpers->new();
$tools->test_only($JUST_TESTING);
my $duration = $opt_d || 7; # week
my $ref_date = DateTime->now->subtract( days => $duration )->ymd();
#my @recipients = ('raj.secure', 'sharon.barrans@nhs.net');
my @recipients = ('sharon.barrans@nhs.net');
# set SQLLibrary to use __DATA__
my $sql_lib = new SQL::Library { lib => [<DATA>] };
# initialise
my $query = LIMS::Local::QueryMailer->new;
# drop/create/insert into tmp. used by both select queries
my $sql = $sql_lib->retr('Temp');
$query->reset_tmp_table($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_dev.xlsx");
$query->mail_results(
recipients => \@recipients,
subject => "DLBCL data this week"
);
# debugging
$query->save_file('/tmp') if $JUST_TESTING;
__DATA__
[Temp]
insert into tmp(id)
select distinct(r.id)
from request_diagnosis_history rdh
join diagnoses d on rdh.diagnosis_id = d.id
join requests r on rdh.request_id = r.id
join patient_case pc on r.patient_case_id = pc.id
where d.name = 'High grade B-cell lymphoma, further tests pending';
[Patients with 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 tmp t on t.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 d.name <> 'DLBCL or Burkitt lymphoma - further tests pending'
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 tmp t on t.id = pc.patient_id
join tmp t on t.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%' )
#or d.name = 'High grade B-cell lymphoma, further tests pending')
and r.created_at >= ?
order by pc.patient_id, r.id;
