# requests, specimens & lab-tests for Salford & Wigan
use Getopt::Std;
use Modern::Perl;
use Data::Printer;
use lib '/home/raj/perl-lib';
use Local::DB;
use Local::WriteExcel; # now supports multiple worksheets
getopts('q'); # output sql query
our($opt_q);
# switch on sql output to console:
$ENV{SQL_TRACE} = $opt_q;
my $dbix = Local::DB->dbix({ dbname => 'hilis4' });
my $sql = join '', <DATA>; # p $sql;
my %orgs = (
Salford => 'RRF__', # organisation_code like ?
WWL => 'RM3__',
);
my @dates = ();
push @dates, [ $_, 2017 ] for 4 .. 12;
push @dates, [ $_, 2018 ] for 1 .. 3; # p @dates;
my %months; @months{1..12} =
qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
my @headers = qw(lab_num specimen lab_test test_type);
for my $location (keys %orgs) {
my $filename = $location . '.xlsx';
my $org_code = $orgs{$location};
my $xl = Local::WriteExcel->new( filename => $filename );
for (@dates) {
my ($month, $year) = @$_; # [ 4, 2017 ], [ 5, 2017 ], etc
my $worksheet_name = join ' ', $months{$month}, $year; # p $worksheet_name;
$xl->worksheet_name($worksheet_name);
$xl->write_bold_row(\@headers);
my @data = $dbix->query($sql, $org_code, $month, $year)->arrays;
$xl->write_row($_) for @data;
}
$xl->save();
}
__DATA__
select concat_ws('/', r.request_number, r.year - 2000) as lab_num,
group_concat(s.sample_code order by s.sample_code) as specimen,
concat(lt.field_label, ' [', ls.section_name, ']') as 'lab test',
lt.test_type
from requests r
join patient_case pc on r.patient_case_id = pc.id
join referral_sources rs1 on pc.referral_source_id = rs1.id
join request_specimen rs2 on rs2.request_id = r.id
join specimens s on rs2.specimen_id = s.id
join request_lab_test_status ts on ts.request_id = r.id
join lab_tests lt on ts.lab_test_id = lt.id
join lab_sections ls on lt.lab_section_id = ls.id
where rs1.organisation_code like ?
and month(r.created_at) = ?
and r.year = ?
group by r.id, lt.id
order by r.request_number
