#!/usr/local/bin/perl5.6.1 -w
#
#######################################################################
# This software has been created by Genome Research Limited (GRL).    # 
# GRL hereby grants permission to use, copy, modify and distribute    # 
# this software and its documentation for non-commercial purposes     # 
# without fee at the user's own risk on the basis set out below.      #
# GRL neither undertakes nor accepts any duty whether contractual or  # 
# otherwise in connection with the software, its use or the use of    # 
# any derivative, and makes no representations or warranties, express #
# or implied, concerning the software, its suitability, fitness for   #
# a particular purpose or non-infringement.                           #
# In no event shall the authors of the software or GRL be responsible # 
# or liable for any loss or damage whatsoever arising in any way      # 
# directly or indirectly out of the use of this software or its       # 
# derivatives, even if advised of the possibility of such damage.     #
# Our software can be freely distributed under the conditions set out # 
# above, and must contain this copyright notice.                      #
#######################################################################
#
#$Id: get_seqdata.pl,v 1.19 2003/04/01 09:03:12 kva Exp $
#
#################################################
# Script that generates a monthly report on the #
# number of plates that have been sequenced for #
# that month. Creates a datafile and then       #
# transfers the data to the SEQ_STATS table on  #
# the production database and generates an      #
# email of stats sent to                        #
# kva,dn1,cn1,seq-leaders,usg. The script is    #
# run as a cronjob on galyep at 1am on the      #
# 1st of each month.                            #
#                                               #
#################################################
#
# Author dn1@sanger.ac.uk
# Additions made by kva@sanger.ac.uk
# --

use strict;
use WrapDBI;
use Data::Dumper;
use SDTable;
use DBI;
use POSIX qw(uname);

my $host = (uname)[1];

my $dbh;
my $db = 'reports';
my @mail;

#my $send_stats_to = 'kva';
my $send_stats_to = 'kva,dn1,usg';

#sendmail when the cronjob is about to start.
open(MAIL, "| sendmail $send_stats_to") || die "Can't open pipe to sendmail";
print MAIL "To: $send_stats_to\n";
print MAIL "From: Cronjob($host)\n";
print MAIL "Subject: Sequencing Monthly Statistics\n";
print MAIL "\n\nSuccessfully started Cron...\n\n";
print MAIL "\n\nThe get_seqdata.pl script is in the process of extracting sequencing data for last month...\n\n";
close MAIL;

# Need somewhere for our data to go to
my $file = "/nfs/disk54/badger/mg1month_seq_report/data.out";
my $file_old = "/nfs/disk54/badger/mg1month_seq_report/data.out_old";

if (-e $file_old){
    unlink ($file_old);
}

if (-e $file){
    system ("mv $file $file_old");
}

open (FH, "> $file") || die $!;

eval {
    $dbh = WrapDBI->connect("$db", {RaiseError => 1});
    $dbh->{autocommit} = 0;
};

if ($@){
    push @mail, "$@";
    
    open(MAIL, "| sendmail $send_stats_to") || die "Cant open pipe to sendmail";
    print MAIL "To: $send_stats_to\n";
    print MAIL "From: Cronjob($host)\n";
    print MAIL "Subject: Problems connecting to Oracle\n";
    print MAIL "\n\nThere may be problems connecting to oracle, the rest of the script won't be run...\n\n";
    foreach (@mail){
	print MAIL "\n$_";
    }
    close MAIL;
    die;
}


# Which loadingplates were made during the month in question ?
#warn "Determining relevant loadingplates...\n";
my $statement = "select distinct lps.id_loadingplate, lp.team
from loadingplate lp, loadingplate_status lps
where
lp.id_loadingplate = lps.id_loadingplate
and lps.status = 1
and to_char(lps.statusdate, 'MM/YYYY') = (select to_char(ADD_MONTHS(SYSDATE,-1),'MM/YYYY') from dual)
and lp.id_loadingplate not in (
	select a.id_loadingplate
	from loadingplate_status a
	where a.iscurrent = 1
	and a.status = 6
)
order by 2, 1
";

my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
$sth1->execute || die "ERROR:$DBI::errstr";
my ($id_loadingplate, $team);
my %TM;
while ( ($id_loadingplate, $team) = $sth1->fetchrow() ){
#    warn "$id_loadingplate, $team\n";
    $TM{$id_loadingplate} = $team;
}
$sth1->finish();

my %LP;
my %DP;
# Which DNA plates do these LP's correspond to ?
#warn "Converting Loadingplates to DNA plates sequenced...\n";

my $dps = "select distinct dpm.id_dnaplate
from loadingsample ls, dnaplate_map dpm
where ls.id_dnaplatemap = dpm.id_dnaplatemap
and ls.id_loadingplate = ?
";
my $sth2 = $dbh->prepare($dps) || die "ERROR:$DBI::errstr";

my $counter = 0;
foreach my $n (sort keys %TM){
    #warn "LP ---> $n\n";
    $sth2->bind_param(1, $n);
    $sth2->execute || die "ERROR:$DBI::errstr";
    my $dp; 
    my @DPS;
    while (my $dp = $sth2->fetchrow){
	$DP{$dp}++;
	#warn "\t$dp\n";
	push @DPS, $dp;
	$counter++;
    }
    $LP{$n} = \@DPS;
}
$sth2->finish();

#warn "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n";
#warn "DP's sequenced = $counter\n";
#warn "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n";

my %clone;
# Clonename ?
#warn "Collecting clonename source of DNA plates...\n";

my $dpback = "select l.clonename
from dnaplate_map dpm, bb_map bbm, subclone s, ligation l
where dpm.id_bbmap = bbm.id_bbmap
and bbm.id_subclone = s.id_subclone
and s.id_ligation = l.id_ligation
and dpm.id_dnaplate = ?
";
my $sth3 = $dbh->prepare($dpback) || die "ERROR:$DBI::errstr";

foreach my $m (sort keys %DP){
    $sth3->bind_param(1, $m);
    $sth3->execute || die "ERROR:$DBI::errstr";
    my ($clonename) = $sth3->fetchrow();
    #warn "DP -> $m   clone -> $clonename\n";
    $clone{$clonename}++;
    $DP{$m} = $clonename;
}
$sth3->finish();


# Find out speciesname and chromosome for each clone
my %species;
#warn "Collecting species/chromosome information...\n";

my $chrom = "select cl.speciesname, NVL(ch.chromosome,'None')
from clone cl, chromosomedict ch
where cl.chromosome = ch.id_dict (+)
and cl.clonename = ?
";
my $sth4 = $dbh->prepare($chrom) || die "ERROR:$DBI::errstr";

foreach my $o (sort keys %clone){
    $sth4->bind_param(1, $o);
    $sth4->execute || die "ERROR:$DBI::errstr";
    my @clone_info;
    my ($species, $chromosome) = $sth4->fetchrow();
#    if ($chromosome eq ''){
#	$chromosome = 'None';
#    }
    $species{$species}++;
    push @clone_info, $species;
    push @clone_info, $chromosome;
    $clone{$o} = \@clone_info;
}

$sth4->finish();

#print STDOUT "----------------------------------------->\n";
#print STDOUT Data::Dumper::Dumper(\%TM);
#print STDOUT "----------------------------------------->\n";
#
#print STDOUT "----------------------------------------->\n";
#print STDOUT Data::Dumper::Dumper(\%LP);
#print STDOUT "----------------------------------------->\n";
#
#print STDOUT "----------------------------------------->\n";
#print STDOUT Data::Dumper::Dumper(\%clone);
#print STDOUT "----------------------------------------->\n";
#
#print STDOUT "----------------------------------------->\n";
#print STDOUT Data::Dumper::Dumper(\%DP);
#print STDOUT "----------------------------------------->\n";
#
#print STDOUT "----------------------------------------->\n";
#print STDOUT Data::Dumper::Dumper(\%species);
#print STDOUT "----------------------------------------->\n";

#warn "Data captured from Oracle... Making datafile...\n";

my $id = 0;
foreach my $look (keys %LP){
    foreach my $look2 ( @{$LP{$look}} ){
	$id++;
	my $loadingplate = $look;
	my $dnaplate = $look2;
	my $team = $TM{$look};
	my $clone = $DP{$dnaplate};
	my $chromosome =  $clone{$clone}->[1];
	my $species =  $clone{$clone}->[0];
	
	###########################################################################
	# We now have all the data we need to make a summary-table to query easily
	###########################################################################
	#warn "id $id loadingplate $loadingplate dnaplate $dnaplate team $team clone $clone chromosome $chromosome species $species \n";
	
	print FH "id $id loadingplate $loadingplate dnaplate $dnaplate team $team clone $clone chromosome $chromosome species $species \n";
    }
}

close FH;

push @mail, "Program completed ok.";


END {
    if ($dbh){
	eval {
	    $dbh->rollback();
	    $dbh->disconnect();
	}
    }
};


#  --------------------------------------------------------
my $db_database = 'cas';  # database name
my $db_user = 'seqstat';         # database username
my $db_pass = 'sstat1';             # database password
# ---------------------------------------------------------

open (FH, "< $file") || die $!;

eval {
    $dbh = DBI->connect($db_database, $db_user, $db_pass, 'Oracle', {AutoCommit =>0}) || die "ERROR:Could not connect to database: $DBI::errstr\n";
};

if ($@){
    push @mail, "$@";
    open(MAIL, "| sendmail $send_stats_to") || die "Cant open pipe to sendmail";
    print MAIL "To: $send_stats_to\n";
    print MAIL "From: Cronjob($host)\n";
    print MAIL "Subject: Problems connecting to Oracle\n";
    print MAIL "\n\nThere may be problems connecting to oracle using $db_user, the rest of the script won't be run...\n\n";
    foreach (@mail){
	print MAIL "\n$_";
    }
    close MAIL;
    die;
}

#warn "Truncating table seqstat.seq_stats...\n";
my $trunc = "truncate table seqstat.seq_stats";
my $stht = $dbh->prepare($trunc) || die "ERROR:$DBI::errstr";
$stht->execute || die "ERROR:$DBI::errstr";
$stht->finish();

my $st = "insert into seqstat.seq_stats (id, id_loadingplate, id_dnaplate, team, clone, chromosome, species) values (?, ?, ?, ?, ?, ?, ?)";
my $sth = $dbh->prepare($st) || die "ERROR:$DBI::errstr";

my $rowcount;
my $count = 0;
while (<FH>) {
    # Get the values
    unless ($_ =~ /^id (\d+) loadingplate (\d+) dnaplate (\d+) team (\d+) clone (\S+) chromosome (\w+) species (\w+.\w+)/ ) {
	$_ =~ /^id (\d+) loadingplate (\d+) dnaplate (\d+) team (\d+) clone (\S+) chromosome (\w+) species (\w+)/;
    }
    #warn "$1 $2 $3 $4 $5 $6 $7\n";
    $sth->bind_param(1, $1);
    $sth->bind_param(2, $2);
    $sth->bind_param(3, $3);
    $sth->bind_param(4, $4);
    $sth->bind_param(5, $5);
    $sth->bind_param(6, $6);
    $sth->bind_param(7, $7);
    
    $count++;
    #warn "Inserting -> $count\n";
    
    $rowcount++;    
    #warn "Rowcount : $rowcount\n";
    
    $sth->execute || die "ERROR:$DBI::errstr";
    
    if ($rowcount=='100'){
        #warn "Commiting 100 reads\n";
        $dbh->commit();
        $rowcount=0;
    }
}

$dbh->commit();
$sth->finish();

close FH;

END {
    if ($dbh){
	eval {
	    $dbh->rollback();
	    $dbh->disconnect();
	}
    }
}


#sendmail when the cronjob is about to finish.
open(MAIL, "| sendmail $send_stats_to") || die "Cant open pipe to sendmail";
print MAIL "To: $send_stats_to\n";
print MAIL "From: Cronjob($host)\n";
print MAIL "Subject: Sequencing Monthly Statistics\n";
print MAIL "\n\nSuccessfully finished populating the table seq_stats in the tracking database from the get_seqdata.pl program...\n\n";
close MAIL;

#sends mail to the sequencing team leaders with the results of the sql report 'sequencing_monthly.sql'.
#my $mail_report_to='kva';
my $mail_report_to='kva,dn1,cn1,seq-leaders,usg';
my @sm_data;

open(FH, "sqlplus -s $db_user/$db_pass '@/usr/local/badger/lib/Reports/Programs/platetrack/sequencing_monthly.sql'|");
while (<FH>){
    push @sm_data, $_;
}

open(MAIL, "| sendmail $mail_report_to") || die "Can't open pipe to sendmail";
print MAIL "To: $mail_report_to\n";
print MAIL "From: Cronjob($host)\n";
print MAIL "Subject: Monthly Sequencing Statistics (for last month)\n";
print MAIL "\nMonthly sequencing statistics.\n";
print MAIL "\n---------------------------------------------------------------------------------------------\n";
print MAIL "\nThe information is broken down by team, then by chromosome for each team and then into 96 well statistics.\n\n";
print MAIL "\nThis is the monthly statistics for the number of plates sequenced by each team, broken down by team then chromosome, where the 384 well plates have been seperated into there appropriate 96 well category. The information provided has been divided into total number of plates overall, then into the main table which displays the number of plates by species, chromosome, and team. The last table collates the number of plates by species.\n\n";
print MAIL "\nThis data and archive data can be seen via the web page located at http://intweb.sanger.ac.uk/cgi-bin/oracle_reports/sequencing_monthly.\n\n";
print MAIL "\n---------------------------------------------------------------------------------------------\n";
foreach (@sm_data){
    print MAIL "$_";
}
close MAIL;


#sendmail when the cronjob is about to finish.
open(MAIL, "| sendmail $send_stats_to") || die "Cant open pipe to sendmail";
print MAIL "To: $send_stats_to\n";
print MAIL "From: Cronjob($host)\n";
print MAIL "Subject: Sequencing Monthly Statistics\n";
print MAIL "\n\nSuccessfully finished Cron running the get_seqdata.pl program...\n\n";
foreach (@mail){
    print MAIL "\n$_";
}
close MAIL;
