#!/usr/bin/env perl

# ------------------------------------------------------------------------------
# emails genomics data report
# ------------------------------------------------------------------------------

BEGIN {	$ENV{CENTRE} = 'genomics' } # no need to set in cron

use Getopt::Std;
getopts('t'); # testing
our($opt_t); # warn $opt_t; exit;

#===============================================================================
my $JUST_TESTING = $opt_t || 0; # skips email, saves xls file to disk
my @contacts = ( 'james.mcleod', 'richard.holmes', 'sarah.fitzgerald', 'raj' );
#===============================================================================

use lib (
    '/home/raj/perl5/lib/perl5',
    '/home/raj/apps/HILIS4/lib',
);

use Spreadsheet::WriteExcel::Simple;
use LIMS::Local::ScriptHelpers;
use Data::Printer;
use Modern::Perl;
use FindBin; # warn $FindBin::Bin;

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

my $config = $tools->config();
my $dbix   = $tools->dbix();

my $filename = 'genomics_data.xls';
my $subject  = sprintf 'Genomics data [%s]', $tools->time_now->dmy; # p $subject;

my $xl = Spreadsheet::WriteExcel::Simple->new;

# xl file headers:
chomp( my @headers = <DATA> ); # p \@headers; exit;
$xl->write_bold_row(\@headers);

my @data = do{
	my $sql = $tools->sql_lib->retr('genomics_data'); # p $sql; exit;
	$dbix->query($sql)->arrays;
};
# substitute 'arm' at array position 6 for 'participant type' - too complex for query:
$_->[6] = participant_type($_) for @data;

$xl->write_row($_) for @data;

if ($JUST_TESTING) {
    $xl->save($FindBin::Bin . '/genomics_data.xls');
}
else {
    my %mail = (
        attachment => $xl->data,
        filename   => $filename,
        config     => $config,
        subject    => $subject,
    );
    $tools->send_mail(\%mail, \@contacts);
}

sub participant_type { # returns Proband/Relative for RD with a family ID, or UNKNOWN without
    my $data = shift;

    my ($unit_number, $arm, $family_id) = @{$data}[3,6,7];
    # cannot determine participant_type if unscreened:
    return undef unless $arm;

	my $participant_type = ( lc $arm eq 'rare disease' )
        ? $family_id
            ? ( $unit_number == $family_id )
                ? 'Proband'  # unit number = family ID
                : 'Relative' # unit number != family ID
            : 'UNKNOWN'      # RD with no family ID
        : 'N/A';             # not Rare Disease arm
	return $participant_type;
}

__DATA__
Username
Source
Lab Number
Participant ID
Presentation
Registration Date
Participant Type
Family ID
Approach Date
Follow up Date
First Outcome
Second Outcome
Total Samples in Group
Consent Date
Blood Sample Taken
PaXgene RNA
EDTA #1 QC Type
EDTA #1 QC DateTime
Blood DNA QC Summary
Blood DNA Qubit
Blood DNA Lab Number
Clinic Sample Type
Tumour Sample Taken
Reason Sample Not Sent
Reason Sample Withdrawn
Tumour Type
Tumour Size
Tumour DNA Lab Number
Tumour DNA QC Test Type
Tumour DNA  QC DateTime
Tumour DNA Qubit
Tumour DNA QC Summary
Tumour DNA Qubit DateTime
Saliva Sample Taken
Bone Marrow Sample Taken
Reasons BM Sample Not Sent
BM Sample ID
BM DNA Lab Number
BM DNA Qubit
BM DNA EDTA #1 QC DateTime
BM DNA QC Summary
Saliva DNA Lab Number
Saliva DNA QC Summary
Saliva DNA Qubit
Saliva DNA Oragene #1 QC DateTime
DNA Sent to CCP
DNA Arrived CCP
DNA Dispatched DateTime
DNA Consignment Number
OMICS Sent to CCP
OMICS Arrived CCP
OMICS Dispatched DateTime
OMICS Consignment Number
Consent Withdrawal Date
Sent from Histopath
Received by Genetics
Notes
Patient deceased
Blood sending lab
Blood sending lab date
Blood receiving lab
Blood receiving lab date
Tumour sending lab
Tumour sending lab date
Tumour receiving lab
Tumour receiving lab date