use v5.34;
package Model::SQL;
use Moo;
use SQL::Abstract::More;
use Data::Printer alias => 'p';
use Types::Standard qw(InstanceOf ArrayRef Str);
has sql_abs => ( is => 'lazy', builder => sub { SQL::Abstract::More->new() } );
# has dbix => ( is => 'ro', isa => InstanceOf['Local::DB'] ); # for get_cols()
has patient_cols => ( is => 'ro', isa => ArrayRef[Str] ); # avoids needing a dbix here
sub user_details {
my ($self, $ref) = @_; # p $ref; # used directly in query 'where' clause:
# $ref = key => userId, where key = nhs_number, email or patient_id
if ( $ref->{patient_id} ) { # patient_id is ambiguous, replace with p.id:
$ref->{'p.id'} = delete $ref->{patient_id};
}
# ensure patient isn't dead (ie disable login by relative):
# TODO - needs 'status' col on pd table, not just request_followup.option_id:
# $ref->{'pd.status'} = { '!=' => 'dead' };
# p $ref;
my @cols = qw(
p.dob
p.nhs_number
pa.email
pa.patient_id
pa.last_login
pa.password
pd.address
pd.post_code
pd.contact_number
ref.name|GP
rs.display_name|practice
); # p \@cols;
# names cols depend on db in use (patient_access_demo or outreach_patient_access):
unshift @cols, ( grep $_ eq 'last_name', @{ $self->patient_cols } )
? qw( p.last_name p.first_name ) : qw(p.initials); # p \@cols;
my @rels = ( 'patient_access|pa' ,
'pa.patient_id=p.id' => 'patients|p' ,
'pd.patient_id=p.id' => 'patient_demographics|pd' ,
'pd.practice_id=rs.id' => 'referral_sources|rs' ,
'pd.gp_id=ref.id' => 'referrers|ref' ,
);
my @params = (
-columns => \@cols,
-from => [ -join => @rels ],
-where => $ref,
# -order_by => $sort_by,
);
return $self->sql_abs->select(@params);
}
sub get_pack_dispatches {
my ($self, $patient_id) = @_; # p $patient_id;
# last 10 requests & pack dispatches, reverse chronological order:
my @cols = (
'r.id',
'r.request_number',
'r.year',
'r.registered',
'IFNULL(d.count, 0)|section_data_count',
'so.description|request_status',
'fo.option',
'fo.label|outcome',
'rpd.pack_due',
'rpd.pack_sent',
'rpd.return_due',
);
my @rels = ( 'requests|r' ,
'r.status_option_id=so.id' => 'status_options|so' ,
'r.patient_case_id=pc.id' => 'patient_case|pc' ,
'pc.patient_id=p.id' => 'patients|p' ,
# left joins:
'=>rpd.request_id=r.id' => 'request_pack_dispatch|rpd' ,
'=>rfu.request_id=r.id' => 'request_followup|rfu' ,
'=>rfu.followup_option_id=fo.id' => 'followup_options|fo' ,
'=>d.request_id=r.id' => 'lab_sections_data|d' ,
);
my %where = ( 'p.id' => $patient_id );
my @params = (
-columns => \@cols,
-from => [ -join => @rels ],
-where => \%where,
-order_by => { -desc => 'r.id' }, # or just '-r.id' will work
-limit => 10,
);
return $self->sql_abs->select(@params);
}
=begin - same queries configured for hilis4 & outreach db's only:
sub user_details {
my ($self, $ref) = @_; # p $ref;
# $ref = key => userId, where key = nhs_number, email or patient_id
if ( $ref->{patient_id} ) { # patient_id is ambiguous, replace with p.id:
$ref->{'p.id'} = delete $ref->{patient_id};
} # p $where;
my @cols = qw(
p.last_name
p.first_name
p.dob
p.nhs_number
pa.email
pa.patient_id
pa.last_login
pa.password
pd.address
pd.post_code
pd.contact_number
ref.name|GP
rs.display_name|practice
); # p \@cols;
my @rels = ( 'patient_access|pa' ,
'pa.patient_id=p.id' => 'hilis4.patients|p' ,
'pd.patient_id=p.id' => 'hilis4.patient_demographics|pd' ,
'pd.practice_id=rs.id' => 'hilis4.referral_sources|rs' ,
'pd.gp_id=ref.id' => 'hilis4.referrers|ref' ,
);
my @params = (
-columns => \@cols,
-from => [ -join => @rels ],
-where => $ref,
# -order_by => $sort_by,
);
return $self->sql_abs->select(@params);
}
sub get_pack_dispatches {
my ($self, $patient_id) = @_; # p $patient_id;
# last 10 requests & pack dispatches, reverse chronological order:
my @cols = (
'r.id',
'r.request_number',
'r.year',
'r.created_at|registered',
'DATE(rsv.time)|reported',
'COUNT(DISTINCT(rt.description))|section_data_count',
'so.description|request_status',
'fo.option',
'fo.label|outcome',
'rpd.pack_due',
'rpd.pack_sent',
'rpd.return_due',
);
my @rels = ( 'hilis4.requests|r' ,
'r.status_option_id=so.id' => 'hilis4.status_options|so' ,
'r.patient_case_id=pc.id' => 'hilis4.patient_case|pc' ,
'pc.patient_id=p.id' => 'hilis4.patients|p' ,
'pd.patient_id=p.id' => 'hilis4.patient_demographics|pd',
'rs.request_id=r.id' => 'hilis4.request_specimen|rs' ,
q!rs.specimen_id=s.id,s.sample_code='CMP'!
=> 'hilis4.specimens|s' ,
# left joins:
'=>rpd.request_id=r.id' => 'request_pack_dispatch|rpd' ,
'=>rfu.request_id=r.id' => 'request_followup|rfu' ,
'=>rfu.followup_option_id=fo.id' => 'followup_options|fo' ,
'=>rr.request_id=r.id' => 'request_results|rr' ,
'=>rr.param_id=lp.id' => 'lab_params|lp' ,
'=>lp.department_id=rt.id' => 'result_types|rt' ,
q!=>rsv.request_id=r.id,rsv.action='reported'!,
=> 'hilis4.request_status_view|rsv' ,
);
my %where = (
'p.id' => $patient_id,
'rt.description' => { -in => ['haematology', 'flow_cytometry'] },
);
my @params = (
-columns => \@cols,
-from => [ -join => @rels ],
-where => \%where,
-group_by => 'r.id',
-order_by => { -desc => 'r.id' }, # or just '-r.id' will work
-limit => 10,
);
return $self->sql_abs->select(@params);
}
=cut
1;
