#!/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.                      #
#######################################################################
#
########################################
#                                      #
# Script allows the update of old      #
# finslot bookings. The script will    #
# be run at 2am every morning on       #
# galyep and will update the status    #
# of old booking slots from 'Created'  #
# to 'Done'.                           #
#                                      #
# Author : kva                         #
#                                      #
########################################

$|=1;

use strict;
use WrapDBI;

my ($db,$dbh);
my $send_to = 'kva,usg';

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

my $error;
if ($@) {
    $error = $@;
    open(MAIL, "| sendmail $send_to") || die "Cant open pipe to sendmail";
    print MAIL "To: $send_to\n";
    print MAIL "From: galyep\n";
    print MAIL "Subject: Expire old finslots\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 $send_to") || die "Cant open pipe to sendmail";
    print MAIL "To: $send_to\n";
    print MAIL "From: galyep\n";
    print MAIL "Subject: Expire old finslots\n";
    print MAIL "(cronjob on galyep) Connection to the database was successful\n";
    close MAIL;
    
}

#program name
my $prog = "Expire_Finslot";

#get session ID
my $stat_session = "select seq_session.nextval from dual";
my $sth_session = $dbh->prepare($stat_session);
$sth_session->execute || die "ERROR:$DBI::errstr\n";
my $sessionid = $sth_session->fetchrow();
$sth_session->finish();

#get the finslot id's to be updated
my $statement = "select a.id_finslot, b.operator 
from fin_schedule a, finschedule_status b
where a.id_finslot = b.id_finslot
and ((to_date(a.from_date,'DD MON YYYY') < to_date(sysdate,'DD MON YYYY')) AND (to_date(a.to_date,'DD MON YYYY') < to_date(sysdate,'DD MON YYYY')))
and b.status = 1 
and b.iscurrent = 1";

my $id_finslots = $dbh->selectall_arrayref($statement);

my $count = 0;

eval {
    
    foreach my $idf (@{$id_finslots}) {
        $count++;
        #print "ENTRY $count FINSLOT ID - @$idf[0]  USER - @$idf[1]\n";    
        
        my $stat_upd = "update finschedule_status set iscurrent = 0 where id_finslot = ? and status = 1 and iscurrent = 1";
        my $sth_1 = $dbh->prepare($stat_upd);
        $sth_1->execute(@$idf[0]) || die "ERROR:$DBI::errstr\n";    
        $sth_1->finish();
        
        my $stat_ins = "insert into finschedule_status (ID_FINSLOT,STATUS,STATUSDATE,ISCURRENT,SESSIONID,PROGRAM,OPERATOR) values (?,3,sysdate,1,?,?,?)";
        my $sth_2 = $dbh->prepare($stat_ins);
        $sth_2->execute(@$idf[0],$sessionid,$prog,@$idf[1]) || die "ERROR:$DBI::errstr\n";  
        $sth_2->finish();
        
    }    
};

if ($@) {
    my $error = $@;
    open(MAIL, "| sendmail $send_to") || die "Cant open pipe to sendmail";
    print MAIL "To: $send_to\n";
    print MAIL "From: galyep\n";
    print MAIL "Subject: Expire old finslots\n";
    print MAIL "(cronjob on galyep) Errors were detected, the data has been rolled back in the database\n";
    print MAIL "ERROR MESSAGE - $error\n";
    close MAIL;
    
    $dbh->rollback();
    die;
    
} else {
    
    open(MAIL, "| sendmail $send_to") || die "Cant open pipe to sendmail";
    print MAIL "To: $send_to\n";
    print MAIL "From: galyep\n";
    print MAIL "Subject: Expire old finslots\n";
    print MAIL "(cronjob on galyep) No errors were detected, the updates have been entered into the database (if any)\n\n";
    print MAIL "Number of entries updated : $count\n\n";
    foreach my $idf (@{$id_finslots}) {
        print MAIL "ENTRY : FINSLOT ID - @$idf[0]  USER - @$idf[1]\n";
    }
    close MAIL;
    
    $dbh->commit();
    }
    

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