# 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