#!/usr/bin/perl

=begin -------------------------------------------------------------------------
Generates spreadsheet on Roche Gallium trial cases. Assumptions for cases referred
to Kiel: if diagnosis is, or was, "Gallium trial - referred to Kiel", date of
referral = authorisation date. If diagnosis subsequently updated, then return date
assumed to be date of diagnosis change (taken from request_diagnosis_history table
where entry = "Gallium trial - referred to Kiel")
=cut ---------------------------------------------------------------------------

use strict;
use warnings;

my $JUST_TESTING = 0; # email to ra.jones only

use lib '/home/raj/perl5/lib/perl5';
use FindBin qw($Bin); # warn $Bin;
use lib "$Bin/../../../lib";

use Data::Dumper;
use LIMS::Local::ScriptHelpers;
use Spreadsheet::WriteExcel::Simple;

my $tools = LIMS::Local::ScriptHelpers->new();
$tools->test_only($JUST_TESTING);

################################################################################
my @recipients = qw(hmds.lth@nhs.net); # send to departmental addr for manual entries
# my $duration = 7; # over past number of days - retrieving all data
my $filename = 'gallium_data.xls';
my $subject  = 'GALLIUM trial data';
################################################################################

# xl file headers:
my @cols = <DATA>; chomp(@cols); # warn Dumper @cols;

# my $date_from = $tools->date_subtract(days => $duration); # warn $date_from; exit;

# get tools from LIMS::Local::ScriptHelpers:
my $sql_lib = $tools->sql_lib();
my $config  = $tools->config(); 
my $dbix    = $tools->dbix();

# get cases referred to Kiel:
my $kiel = do {
    my $sql = $sql_lib->retr('gallium_trial_referred_to_kiel');
    $dbix->query($sql)->map;
};
my $requests = do {
    my $sql = $sql_lib->retr('gallium_trial_data_roche'); # warn $sql;
    $dbix->query($sql);
};

my @rows;
while ( my $vars = $requests->hash ) { # warn Dumper $vars;
    # extract block ref + number of slides & blocks from external_ref:
    if ( my $eref = $vars->{external_reference} ) {
        # just capture 1st item of block_ref eg (125-16696) x20uss
        my ($block_ref) = split /\sx(:?\d+)[spu]/, $eref; # warn $block_ref; 

        my ($unstained_slides) = $eref =~ /x(\d+)uss/; # warn $unstained_slides 
        my ($stained_slides)   = $eref =~ /x(\d+)ss/; # warn $stained_slides
        my ($blocks)           = $eref =~ /x(\d+)p/; # warn $blocks;
        
        my $slides = do {
            no warnings 'uninitialized';
            $unstained_slides + $stained_slides;
        }; # warn Dumper [$block_ref, $slides, $blocks];
        
        $vars->{block_id} = LIMS::Local::Utils::trim($block_ref);
        $vars->{slides}   = $slides || 0;
        $vars->{blocks}   = $blocks || 0; 
    }
    
    { # referred to Kiel?
        my $request_id = $vars->{request_id};
        my $diagnosis  = $vars->{diagnosis} || ''; # avoid uninitialized warning
        
        if ( $kiel->{$request_id} || $diagnosis =~ /referred to kiel/i ) {
            $vars->{referred_to_kiel} = $vars->{reported}; # date
        }
        if ( my $return_date = $kiel->{$request_id} ) { # assume diagnosis change date
            $vars->{returned_from_kiel} = $return_date; 
        }
    }
    
    my @data = @{$vars}{@cols}; # warn Dumper $vars;
    push @rows, \@data;
}

exit if ! @rows;
	
{ # construct excel spreadsheet:
    my $xl = Spreadsheet::WriteExcel::Simple->new;
	$xl->write_bold_row(\@cols);
	$xl->write_row($_) for @rows;
    # save to file if just testing:
    $xl->save($filename) if $JUST_TESTING;

    my %mail = (
        attachment  => $xl->data,
        filename    => $filename,
        subject     => $subject,
        config      => $config,
    );
    $tools->send_mail(\%mail, \@recipients);
}

__DATA__
hmds_ref
accession_number
site_id
patient_id
dob
gender
specimen_code
specimen_description
specimen_quality
registered
reported
slides
blocks
block_id
consent
immuno_status
immuno_summary
molecular_result
referred_to_kiel
returned_from_kiel
diagnosis
comment