# prototype script for calculating turnaround times for specific lab-test:

BEGIN {
    use FindBin qw($Bin); # warn $Bin;

    use lib (
        '/home/raj/perl-lib',
        "$Bin/../lib",
    );
}

use Data::Printer alias => 'p';
use Local::DB;

my $lab_section = 'FISH';
my $trial_name  = 'NCRI CLL FLAIR';
my $panel_name  = 'cll_trial';
my $test_name   = 'p53';

$Local::QueryLogger::NO_QUERY_LOGS = 1; # don't need queries in logs dir

my $dbix = Local::DB->dbix({ dbname => 'hilis4' });

my $result = do {
    my $sql = _get_query();
    $dbix->query($sql, $trial_name, $panel_name, $lab_section);
};

while ( my $data = $result->hash ) {
    my $manual_request_date = '';
    my $auto_request_date   = '';
    # use manual request date if exists, or auto-request date, or date of screening:
    my $date = $manual_request_date || $auto_request_date || $data->{screened};
}

sub _get_query {
    return q!
        SELECT r.id, rh.time as 'screened',
        FROM requests r
            JOIN ( request_trial rt JOIN clinical_trials ct on rt.trial_id = ct.id )
                on rt.request_id = r.id
            JOIN ( request_lab_test_status rlts
                JOIN ( lab_tests lt JOIN lab_sections ls on lt.lab_section_id = ls.id )
					 		on rlts.lab_test_id = lt.id ) on rlts.request_id = r.id
            JOIN lab_test_status_options so on rlts.status_option_id = so.id
            JOIN request_history rh on ( rh.request_id = r.id AND rh.action = 'screened' )
        WHERE ct.trial_name = ?
            and lt.test_name = ?
            and ls.section_name = ?
            and so.description = 'complete'!;
}

__DATA__
SELECT request_id, `action`, `time` as 'datetime'
FROM request_lab_test_history
WHERE `action` RLIKE '^(auto-)?requested' and request_id in (??)
ORDER BY `time` /* so any repeat tests in chronological order */