#!/usr/bin/env perl
=begin -------------------------------------------------------------------------
transfers xls file of requests for any auth'd within past 24hrs to
ftp.tdlpathology.com, using Net::SFTP::Foreign
sets uclh database manually, no need for command-line arg
=cut ---------------------------------------------------------------------------
use Getopt::Std;
getopts('d:tq'); # days, testing, query output
our($opt_d,$opt_t,$opt_q); # warn $opt_t; exit;
use strict;
use warnings;
use feature 'say';
my $JUST_TESTING = $opt_t || 0; # dumps xls file in /tmp only
#==============================================================================
my $duration = $opt_d || 1; # days; for DATE(time_col) >= ( now minus 2 days )
#==============================================================================
use lib '/home/raj/perl5/lib/perl5';
use IO::All;
use Try::Tiny;
use Text::CSV;
use File::Temp;
use Data::Printer;
use Net::SFTP::Foreign;
use SQL::Abstract::More;
use FindBin qw($Bin); # warn $Bin;
use lib $Bin . '/../../../lib';
use LIMS::Local::ScriptHelpers;
use Spreadsheet::WriteExcel::Simple;
# get tools from LIMS::Local::ScriptHelpers:
my $tools = LIMS::Local::ScriptHelpers->new();
$tools->test_only($JUST_TESTING);
my $today = $tools->time_now;
my $cfg_file = "$Bin/../lib/settings.cfg";
my $settings = Config::Auto::parse($cfg_file); # p $settings;
my $cfg = $settings->{uclh_tdl_server}; # p $cfg;
#-------------------------------------------------------------------------------
my $ref_date = $today->clone->subtract( days => $duration ); # warn $ref_date;
my $sftphost = $cfg->{remote_address} or die 'remote_address cfg value missing';
my $username = $cfg->{username} or die 'username cfg value missing';
my $password = $cfg->{password} or die 'password cfg value missing';
my $remote_path = $cfg->{remote_path} or die 'remote_path cfg value missing';
my $remote_file = 'hsl_hmds_data.xls';
#-------------------------------------------------------------------------------
my $config = $tools->config();
my $dbix = $tools->dbix();
$dbix->lc_columns = 0; # preserve MixedCase
$dbix->dbh->do('USE uclh');
my ($sql, @bind) = _get_query(); # p $sql; p @bind;
my $query = $dbix->query( $sql, @bind );
my @cols = $query->columns; # p @cols;
my @data = $query->arrays; # p @data;
exit unless @data;
# open my $fh, '>:encoding(utf8)', my \$data or die "IO: $!";
my $fh = File::Temp->new(); # create new fh to tmpfile in /tmp dir
my $xl = Spreadsheet::WriteExcel::Simple->new;
my $local_file = $fh->filename;
$local_file .= '.xls' if $JUST_TESTING; # warn $local_file; # won't auto-delete
$xl->write_bold_row(\@cols);
$xl->write_row($_) for @data;
$xl->save($local_file);
if ($JUST_TESTING) {
say 'data saved to ' . $local_file;
exit;
}
{ # sftp file:
my %args = ( user => $username, password => $password );
my $sftp = Net::SFTP::Foreign->new($sftphost, %args);
$sftp->die_on_error('unable to establish SFTP connection');
# need to manually set remote path:
$sftp->setcwd($remote_path) or
msg_and_exit('unable to set cwd: ' . $sftp->error);
# need to set copy_perm false or get error "put failed: Couldn't setstat
# remote file: SSH_FILEXFER_ATTR_PERMISSIONS":
$sftp->put($local_file, $remote_file, copy_perm => 0) or
msg_and_exit('put failed: ' . $sftp->error);
# probably caught any error aleady, but just in case:
if ( my $err = $sftp->error ) {
msg_and_exit($err);
}
else {
my $script = $tools->script_filename;
my $date = $ref_date->dmy; # for cron log
say "$script reports UCLH data for $date successfully transferred";
}
}
sub msg_and_exit {
my $msg = shift;
my $script = $tools->script_filename;
say "$script - $msg"; # dump to logs
$tools->mail_admin({ script => $script, msg => $msg });
exit;
}
sub _get_query {
my @cols = (
'pc.unit_number|PatientNumber',
q{CONCAT_WS(' ', p.first_name, p.last_name)|PatientName},
'p.gender|Sex',
'p.dob|DoB',
q{DATE_FORMAT(r.created_at,'%Y') - DATE_FORMAT(p.dob,'%Y')
- (DATE_FORMAT(r.created_at,'00-%m-%d')
< DATE_FORMAT(p.dob,'00-%m-%d')) AS Age},
q{MAX(CASE WHEN ao.option_name = 'private'
THEN 'Private' ELSE 'NHS' END) AS Category},
'p.nhs_number|NHSNo',
'pn.detail|PatientNotes',
'NULL|RefOrdNo', # referred from another location
'NULL|ReferringLabNo', # referred from another location
'NULL|ReferringClinician', # referred from another location
'rs.organisation_code|SourceCode',
'rs.display_name|SourceName',
'NULL|SourceClass',
'ref.national_code|ClinicianCode',
'ref.name|ClinicianName',
'DATE(rsd.specimen_date)|SampleDate',
'TIME(rsd.specimen_date)|SampleTime',
# assuming received date/time same as registration date/time
q{MAX(CASE WHEN rh.action = 'registered'
THEN DATE(rh.time) END) AS ReceivedDate},
q{MAX(CASE WHEN rh.action = 'registered'
THEN TIME(rh.time) END) AS ReceivedTime},
q{MAX(CASE WHEN rh.action = 'registered'
THEN DATE(rh.time) END) AS BookedDate},
q{MAX(CASE WHEN rh.action = 'registered'
THEN TIME(rh.time) END) AS BookedTime},
q{MAX(CASE WHEN rh.action = 'registered'
THEN CONCAT_WS(' ', u.first_name, u.last_name) END) AS BookedUser},
q{MAX(CASE WHEN ao.option_name = 'urgent'
THEN 'yes' ELSE 'no' END) AS Urgent},
's.sample_code|SampleType',
's.description|SampleDesc',
q{CONCAT('U', r.request_number, '/', r.year - 2000)|LabNo},
q{IF(lt.test_type = 'panel', lt.field_label, NULL) AS TLC},
q{IF(lt.test_type = 'test', lt.field_label, NULL) AS TFC},
'NULL|Result', # no results available
'NULL|ResultDate', # no results available
'NULL|ResultTime', # no results available
q{MAX(CASE WHEN rh.action = 'authorised'
THEN DATE(rh.time) END) AS AuthDate},
q{MAX(CASE WHEN rh.action = 'authorised'
THEN TIME(rh.time) END) AS AuthTime},
'NULL|Units',
'NULL|RangeLo',
'NULL|RangeHi',
);
my @rels = ( 'requests|r' ,
q{r.patient_case_id=pc.id} => 'patient_case|pc' ,
q{pc.patient_id=p.id} => 'patients|p' ,
q{pc.referral_source_id=rs.id} => 'referral_sources|rs' ,
q{rsv.request_id=r.id} => 'request_status_view|rsv' ,
q{r.referrer_department_id=rd.id} => 'referrer_department|rd' ,
q{rd.referrer_id=ref.id} => 'referrers|ref' ,
q{rsd.request_id=r.id} => 'request_specimen_detail|rsd' ,
q{rsp.request_id=r.id} => 'request_specimen|rsp' ,
q{rsp.specimen_id=s.id} => 'specimens|s' ,
q{rh.request_id=r.id} => 'request_history|rh' ,
q{rh.user_id=u.id} => 'users|u' ,
# left joins:
q{=>ts.request_id=r.id} => 'request_lab_test_status|ts' ,
q{=>ts.lab_test_id=lt.id} => 'lab_tests|lt' ,
q{=>ro.request_id=r.id} => 'request_option|ro' ,
q{=>ro.option_id=ao.id} => 'additional_options|ao' ,
q{=>pn.patient_id=p.id} => 'patient_notes|pn' ,
);
my %where = (
'DATE(rsv.time)' => $ref_date->ymd,
'rsv.action' => 'authorised',
);
my @args = (
-columns => \@cols,
-from => [ -join => @rels ],
-where => \%where,
-group_by => ['lt.id','r.id'],
-order_by => 'r.id',
); # p @args;
my ($sql, @bind) = SQL::Abstract::More->new->select(@args); # p $sql; p \@bind;
$dbix->dump_query($sql, @bind) if $opt_q; # exit;
return ($sql, @bind);
}
