• Files
• Commits
• Log
• Graph
• Stats

use Modern::Perl;

# cases screened as suspected aml, cytopenia, suspected mds, etc, or a myeloid
# diagnosis, mails to CC & PE weekly on rollover Thursday/Friday

############ recipients from contacts.lib #######################################
my @recipients = qw( catherine.cargo.secure paul.evans.secure );
################################################################################

use Spreadsheet::WriteExcel::Simple;
use Data::Printer;
use FindBin; # warn $FindBin::Bin;

use lib '/home/raj/apps/HILIS4/lib';
use LIMS::Local::ScriptHelpers;
use LIMS::Local::Utils;

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

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

#-------------------------------------------------------------------------------
my $date = $tools->time_now;
my $date_from = $date->clone->subtract(days => 7)->ymd; # warn $date_from; exit;
my $subject = sprintf 'Myeloid screens & diagnoses for NGS [%s - %s]',
	$date->clone->subtract(days => 7)->dmy,
    $date->clone->subtract(days => 1)->dmy; # warn $subject; exit;
my $filename = 'myeloid_ngs.xls';
#-------------------------------------------------------------------------------

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

# xl file headers:
my @headers = qw( req_number year reg_date last_name first_name dob age screen
    specimen diagnosis );

my @rows;

my $sql = query();
my $result = $dbix->query($sql, $date_from);
while (my $row = $result->array) { # warn Dumper $row; next;
    push @rows, $row;
}

my %mail = (		
	config  => $config,
	subject => $subject,
); 

if (@rows) {
	my $xl = Spreadsheet::WriteExcel::Simple->new;
	$xl->write_bold_row(\@headers);
	$xl->write_row($_) for @rows;
	
	$mail{attachment} = $xl->data;
    $mail{filename}   = $filename;	

    $tools->send_mail(\%mail, \@recipients);
}

sub query {
  return q!
    select
    r.request_number, r.year, date(r.created_at), p.last_name, p.first_name, p.dob, 
            ( date_format(r.created_at,'%Y') - date_format(p.dob,'%Y') ) 
            - ( date_format(r.created_at,'00-%m-%d') < date_format(p.dob,'00-%m-%d') ),
        s.description, GROUP_CONCAT(s2.sample_code), d.name
    from requests r
        join ( patient_case pc join patients p on pc.patient_id = p.id )
            on r.patient_case_id = pc.id
        join ( request_initial_screen ris join screens s on ris.screen_id = s.id )
            on r.id = ris.request_id
        join ( request_specimen rs join specimens s2 on rs.specimen_id = s2.id )
            on rs.request_id = r.id
        left join ( request_report_detail rrd join diagnoses d on rrd.diagnosis_id = d.id )
            on rrd.request_id = r.id
    where date(r.created_at) >= ?
        and ( date_format(r.created_at,'%Y') - date_format(p.dob,'%Y') ) 
            - ( date_format(r.created_at,'00-%m-%d') < date_format(p.dob,'00-%m-%d') ) >= 18
        and ( 
            s.description IN (
                'Suspected AML 60+',
                'Suspected AML under-60',
                'Cytopenia',
                'Suspected MDS',
                'Suspected CMML' 
            ) or ( rrd.status = 'new' and d.name in (
                'Myelodysplastic syndrome, unclassifiable',
                'Refractory anaemia with excess blasts',
                'Refractory anaemia with excess blasts and fibrosis',
                'Refractory anaemia with ring sideroblasts',
                'Refractory cytopenia with multilineage dysplasia',
                'Refractory cytopenia with unilineage dysplasia',
                'Myelodysplastic syndrome (5q-)',
                'AML arising from transformation of MDS',
                'AML arising from transformation of MDS/MPN',
                'AML arising from transformation of MPD',
                'AML inv(16)(p13;q22)',
                'AML NOS',
                'AML t(8;21)(q22;q22)',
                'AML with adverse cellular features',
                'AML with MLL (11q23) rearrangement',
                'AML with NPM mutation as sole abnormality',
                'Chronic myelomonocytic leukaemia',
                'Atypical chronic myeloid leukaemia',
                'Myelodysplastic/myeloproliferative neoplasm unclassified',
                'Refractory anaemia with ring sideroblasts and thrombocytosis'		
            )
          )
        )
    group by r.id
    order by date(r.created_at), r.request_number!;
}