package Reporter::SQL::Lib;
use SQL::Abstract::More; # extends SQL::Abstract to support joins
use lib '/home/raj/perl-lib';
use Local::QueryLibrary;
use Reporter::Class; # provides Moo, Modern::Perl & Data::Printer::p
has sql_abs => ( is => 'lazy', builder => sub { SQL::Abstract::More->new } );
has sql_lib => ( is => 'lazy', builder => sub { Local::QueryLibrary->new } );
sub request_data {
my ($self, $request_number, $yr) = @_;
my @cols = qw(
r.request_number
r.year
p.last_name
p.first_name
p.nhs_number
p.dob
rs.display_name|location
rr.comment
rr.morphology
d.name|diagnosis
);
# SQL::Abstract::More join:
my @joins = (
# table|alias # FK => PK relationship
qw(
requests|r r.patient_case_id=pc.id
patient_case|pc pc.patient_id=p.id
patients|p pc.referral_source_id=rs.id
referral_sources|rs =>{rr.request_id=r.id}
request_report_view|rr =>{rr.diagnosis_id=d.id}
diagnoses|d
)
);
my ($sql, @bind) = $self->sql_abs->select(
-columns => [ @cols ],
-from => [ -join => @joins ],
-where => {
'r.request_number' => $request_number,
'r.year' => $yr + 2000,
},
); # p $sql;
return ($sql, @bind);
}
1;