#!/usr/local/bin/perl5.6.1
#
#######################################################################
# 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.                      #
#######################################################################
#
########################################
#                                      #
# Script generates daily gel orders to #
# to be sent to the gel pouring group  #
# 24 hours before they are needed      #
# The script will be run on a cronjob  #
# at 4 am and will send orders to      #
# jellies and myself for the moment    #
#                                      #
# Author : kva                         #
#                                      #
########################################

$|=1;

use strict;
use WrapDBI;
use SDTable;

my $send_to="jellies,kva,usg";
my $error_to='kva,usg';
my($db,$dbh);
my $total_gels;
my $all_gels;
my $table1;
my @all;
my $day;
my @dates;

eval {
    
    $db = 'finch';
#    $db = 'trackingclone1';
    $dbh = WrapDBI->connect("$db", {RaiseError => 1}) || die print "Connection with database failed: $DBI::errstr";
    $dbh->{autocommit} = 0;
    
};

my $error;
if ($@) {
    $error = $@;   
    open(MAIL, "| sendmail $error_to") || die "Cant open pipe to sendmail";
    print MAIL "To: $error_to\n";
    print MAIL "From: galyep\n";
    print MAIL "Subject: Daily gel order from the Web booking form.\n";
    print MAIL "(cronjob on galyep) Error connecting to the database.\n";
    print MAIL "ERROR MESSAGE - $error\n";
    close MAIL;
    die;
    
} else {
    
    open(MAIL, "| sendmail $error_to") || die "Cant open pipe to sendmail";
    print MAIL "To: $error_to\n";
    print MAIL "From: galyep\n";
    print MAIL "Subject: Daily gel order from the Web booking form.\n";
    print MAIL "(cronjob on galyep) Connection to the database was successful\n";
    close MAIL;
    
}


eval {
    
    my $stat_day = "select to_char(sysdate,'day') from dual";
    my $sth_dy = $dbh->prepare($stat_day);
    $sth_dy->execute() || die "ERROR:$DBI::errstr\n";
    $day = $sth_dy->fetchrow();
    
    $day=~ s/\s+//g;
    
    if ($day eq 'friday'){
  
        my $stat_date = "select sysdate+1,sysdate+2,sysdate+3 from dual";
        my $sth_date = $dbh->prepare($stat_date);
        $sth_date->execute() || die "ERROR:$DBI::errstr\n";
        @dates = $sth_date->fetchrow();
        
        my $statement = "select a.teamname, sum(a.gel_requirement)
from fin_schedule a, finschedule_status b
where a.id_finslot = b.id_finslot
and to_date(a.from_date, 'DD-MON-YY') = to_date(?, 'DD-MON-YY')
and b.status = 1
and b.iscurrent = 1
group by a.teamname
order by a.teamname";
        
        
        foreach my $od (@dates) {
            my $t_gels;
            my @r_cs;
            my $table2 = SDTable->new('format' => 'gaudy'); 
            $od=~ s/\s+//g;
            my $gel_order = $dbh->selectall_arrayref($statement,{},$od) || die "ERROR:$DBI::errstr\n"; 
            
            $table2->set('title'=> "Gel orders for $od", 'titles' => ["Team", "Number of Gels"]);
            foreach my $go (@{$gel_order}){
                my @cs=("@{$go}[0]","@{$go}[1]");
                push (@r_cs, \@cs);
                $t_gels+= @{$go}[1];
            }
            
            foreach my $rc (@r_cs){
                $table2->add(@$rc);
            }
            
            push @all, $table2->format();
            push @all,"\n\n";
            
            if ($t_gels == ""){
                push @all,"TOTAL NUMBER OF GELS NEEDED  = 0\n\n";
            } else {
                push @all,"TOTAL NUMBER OF GELS NEEDED  = $t_gels\n\n";
            } 
            
            $all_gels+=$t_gels;
            
        }
        
        push @all,"----------------------------\n\n";
        push @all,"Overall total = $all_gels gel(s)\n\n";
        
    } else {
        
        my $stat_date = "select sysdate+1 from dual";
        my $sth_dte = $dbh->prepare($stat_date);
        $sth_dte->execute() || die "ERROR:$DBI::errstr\n";
        @dates = $sth_dte->fetchrow();
        
        my $statement = "select a.teamname, sum(a.gel_requirement)
from fin_schedule a, finschedule_status b
where a.id_finslot = b.id_finslot
and to_char(a.from_date, 'DD-MON-YY') = to_char(sysdate+1, 'DD-MON-YY')
and b.status = 1
and b.iscurrent = 1
group by a.teamname
order by a.teamname";
        
        my $gel_order = $dbh->selectall_arrayref($statement);
        
        my @r_cs;
        $table1 = SDTable->new('format' => 'gaudy');
        $table1->set('title'=> "Gel orders for @dates", 'titles' => ["Team", "Number of Gels"]);

        foreach my $go (@{$gel_order}) {
	    my $note;
	    if (@{$go}[0] eq '72'){
		$note = "(Baby Gels)";
	    }
	    my @cs=("@{$go}[0]","@{$go}[1] $note");
	    push (@r_cs, \@cs);
	    $total_gels+= @{$go}[1];     
	}
        $all_gels+=$total_gels;
        
        foreach my $rc (@r_cs){
            $table1->add(@$rc);
        }
        
        
    }
    
    
};


if ($@) {

    my $error = $@;
    open(MAIL, "| sendmail $error_to") || die "Cant open pipe to sendmail";
    print MAIL "To: $error_to\n";
    print MAIL "From: galyep\n";
    print MAIL "Subject: Daily gel order from the Web booking form.\n";
    print MAIL "(cronjob on galyep) Errors were detected, the data could not be obtained. Contact kva\n";
    print MAIL "ERROR MESSAGE - $error\n";
    close MAIL;
    die;
    
} else {
    
    my $countall = scalar(@all);
    
    if ($all_gels != '0') {
#Generate the email, which goes 
#to the jellies and myself.
        
        
        if ($countall != '0'){
            
            open(MAIL, "| sendmail $send_to") || die "Cant open pipe to sendmail";
            print MAIL "To: $send_to\n";
            print MAIL "From: Cronjob(galyep)\n";
            print MAIL "Subject: Daily gel order from the Web booking form.\n";
            print MAIL "Below are the orders for the next 3 days (sat,sun,mon) on the 377 machines.\n";
            print MAIL "See URL : http://intweb.sanger.ac.uk/cgi-bin/oracle_reports/377booking/book_377.pl for more information. \n\n";
            foreach (@all){
                print MAIL "$_";
            }
            print MAIL "\n";
            print MAIL "\n\nRegards Karen (dn1g)\n\n";
            close MAIL;
            
        } else {
            
            open(MAIL, "| sendmail $send_to") || die "Cant open pipe to sendmail";
            print MAIL "To: $send_to\n";
            print MAIL "From: Cronjob(galyep)\n";
            print MAIL "Subject: Daily gel order from the Web booking form.\n";
            print MAIL "Below are the orders for tomorrows gel runs on the 377 machines.\n";
            print MAIL "See URL : http://intweb.sanger.ac.uk/cgi-bin/oracle_reports/377booking/book_377.pl for more information. \n\n";
            
            if ($day eq 'sunday'){
                print MAIL "If this is the email for mondays orders, Please check the total number of gels required sent on friday against this one to see if the number has increased\n\n";
            }
            
            print MAIL $table1->format();
            print MAIL "\n";
            print MAIL "TOTAL NUMBER OF GELS NEEDED  = $total_gels\n\n";
            print MAIL "\n\nRegards Karen (dn1g)\n\n";
            close MAIL;         
        }

        
    } else {
              
        open(MAIL, "| sendmail $send_to") || die "Cant open pipe to sendmail";
        print MAIL "To: $send_to\n";
        print MAIL "From: Cronjob(galyep)\n";
        print MAIL "Subject: Daily gel order from the Web booking form.\n";
        print MAIL "No Gel orders were found for ".&commify_series(@dates).".\n";
        print MAIL "\n\nRegards Karen (dn1g)\n\n";
        close MAIL;

        
    }
}


sub commify_series {

    my $sepchar = grep(/,/ => @_) ? ";" : ",";
    (@_ == 0) ? ''                      :
        (@_ == 1) ? $_[0]                   :
            (@_ == 2) ? join(" and ", @_)       :
                join("$sepchar ", @_[0 .. ($#_-1)], "and $_[-1]");
    
}


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








