#!/usr/local/bin/perl
#
#######################################################################
# 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: seqstats2dev.pl,v 1.12 2002/07/31 13:44:21 kva Exp $
#
# Author dn1@sanger.ac.uk
# Additions made by kva@sanger.ac.uk
# -

use strict;
use Data::Dumper;
use SDTable;

use DBI;

#  --------------------------------------------------------
my $db_database = 'cas';  # database name
my $db_user = 'seqstat';         # database username
my $db_pass = 'sstat1';             # database password
$ENV{'ORACLE_HOME'} = '/usr/local/oracle';
# ---------------------------------------------------------

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

my $file = "/nfs/disk54/badger/mg1month_seq_report/data.out";
open (FH, "< $file") || die $!;

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

#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 $counter = 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);

    $counter++;
    #warn "Inserting -> $counter\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(galyep)\n";
print MAIL "Subject: Sequencing Monthly Statistics\n";
print MAIL "\n\nSuccessfully finished populating the table seq_stats in the tracking database from the seqstats2dev.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(galyep)\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;










