#!/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.                      #
#######################################################################

use strict;
use WrapDBI;
use Mail::Header;

my $recipients = 'kva';

#cas connection
my $db = 'finch';

##dev connection
##tsnp connection
#my $db2 = 'snptest';

#snp connection
my $db2 = 'ppurch';

my ($dbh,$dbh2);

my $time = 1;

my $SUBJECT = "PURCHASING_SUMMARY INSERT FIN PRIMERS **FIN";

eval {
    
    #cas connection 
    $dbh = WrapDBI->connect("$db", {RaiseError => 1});    
    $dbh->{AutoCommit} = 0;
    
    #snp connection
    $dbh2 = WrapDBI->connect("$db2", {RaiseError => 1});    
    $dbh2->{AutoCommit} = 0;
    
};
if ($@){
    print "Error encountered with database connection : $@\n";
    die;
}

####old sql
##select 'GEN005' VID,a.PN,ps.statusdate ORD_DTE,DECODE(PS.PROGRAM,'REORDER','reorder','standard') TYPE,a.ID_ORD,decode(PS.PROGRAM,'REORDER','0','0.12') BP,'0.00' PP,'0' OP,(decode(PS.PROGRAM,'REORDER','0','0.12')*a.pseq_len) TP,decode(length(ct.COST_TEAM),2,'0'||ct.COST_TEAM,ct.COST_TEAM)||decode(length(ct.COST_PROJECT),1,'000'||ct.COST_PROJECT,2,'00'||ct.COST_PROJECT,3,'0'||ct.COST_PROJECT,ct.COST_PROJECT) CC, a.pseq_len, a.p_count, ps.operator RB,ct.COST_TEAM TM,'cas1' ORD,0 ISC,0 ISR
##from 
##(select e.order_id ID_ORD, p.id_costcode PN, count(e.id_primer) p_count, sum(length(p.primerseq)) pseq_len
##from ext_fin_primer_orders e, primer p
##where e.id_primer = p.id_primer
##and is_transferred = 0
##and to_char(e.order_date,'DD-MON-YY') = to_char(SYSDATE-?,'DD-MON-YY')
##group by e.order_id, p.id_costcode
##order by e.order_id)a, primer_status ps, costcode ct
##where a.ID_ORD = ps.id_primer
##and a.PN = ct.id_costcode
##and ps.status = 1

my $stat1 = "select 'GEN005' VID
, ptpd.PREFIX||pod.ID_PURCHASEORDER PN
, ps.statusdate ORD_DTE
, DECODE(PS.PROGRAM,'REORDER','reorder','standard') TYPE
, a.ID_ORD,decode(PS.PROGRAM,'REORDER','0','0.12') BP
, '0.00' PP
, '0' OP
, (decode(PS.PROGRAM,'REORDER','0','0.12')*a.pseq_len) TP
, decode(length(ct.COST_TEAM),2,'0'||ct.COST_TEAM,ct.COST_TEAM)||decode(length(ct.COST_PROJECT),1,'000'||ct.COST_PROJECT,2,'00'||ct.COST_PROJECT,3,'0'||ct.COST_PROJECT,ct.COST_PROJECT) CC
, a.pseq_len
, a.p_count
, ps.operator RB
, ct.COST_TEAM TM
, 'cas1' ORD
, 0 ISC
, 0 ISR
from 
(select e.order_id ID_ORD
, p.id_costcode PN
, count(e.id_primer) p_count
, sum(length(p.primerseq)) pseq_len
from ext_fin_primer_orders e, primer p
where e.id_primer = p.id_primer
and is_transferred = 0
and to_char(e.order_date,'DD-MON-YY') = to_char(SYSDATE-?,'DD-MON-YY')
group by e.order_id, p.id_costcode
order by e.order_id)a, primer_status ps, costcode ct, purchase_team_prefix_dict ptpd, purchaseorderdict pod
where a.ID_ORD = ps.id_primer
and ct.COST_TEAM = ptpd.team
and to_char(pod.ASSIGN_DATE,'MON-YY') = to_char(ps.statusdate,'MON-YY')
and a.PN = ct.id_costcode
and ps.status = 1";

my $primer_ord_ref = $dbh->selectall_arrayref($stat1,{},$time) || die "Problems extracting primer order data - ERROR:$DBI::errstr\n";


my $total_data = @$primer_ord_ref;
print "total_data - $total_data\n";

my $get_date = "select sysdate-? from dual";
my $sth_get_date = $dbh2->prepare($get_date);
$sth_get_date->execute($time) || die "Problems obtaining date - ERROR:$DBI::errstr\n";
my $date = $sth_get_date->fetchrow();
print "DATE = $date\n";

my $stat2 = "select seq_pusu.nextval from dual";
my $sth2 = $dbh2->prepare($stat2);

my $stat3 = "insert into purchasing_summary (ID_PUSU,VENDORID,PURCHASE_NUMBER,ORDER_DATE,TYPE,ID_ORDER,BASE_PRICE,PLATE_PRICE,OLIGO_PRICE,TOTAL_PRICE,COSTCODE,BASE_QUANTITY,OLIGO_QUANTITY,REQSTBY,TEAM,ORDER_ORIGIN,IS_COLLECTED,IS_RECIEVED) values (?,?,?,to_date(?,'DD-MON-YY'),?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
my $sth3 = $dbh2->prepare($stat3);

#check to ensure that the information hasn't already been
#inserted into the table in tsnp.
my $stat4 = "select id_pusu from purchasing_summary where team = ? and id_order = ?";
my $sth4 = $dbh2->prepare($stat4);

my $stat5 = "update ext_fin_primer_orders set IS_TRANSFERRED = 1 where order_id = ?";
my $sth5 = $dbh->prepare($stat5);

my $count = 0;

my $message;
my $sender = 'badger';

eval {
   
    if ($total_data == 0){
    
        $message = "TOTAL NUMBER OF ROWS OF DATA for $date - $total_data. No data found to insert.\n";
        &send_email($SUBJECT,$message,$recipients,$sender);
        exit;

    } else {
        
        
         foreach my $por (@$primer_ord_ref){
            $count++;
#            print "entry $count >>@$por<<\n";
#            print "team and order id info costcode--> $por->[13],$por->[4],$por->[9]\n";
            $sth4->execute($por->[13],$por->[4]) || die "Problems obtaining count from purchasing_summary - ERROR:$DBI::errstr\n";
            my $id = $sth4->fetchrow();
            if($id){
                $message = "Previous entry $id was found for $date in PURCHASING_SUMMARY, you may need to investigate further!\n";
                &send_email($SUBJECT,$message,$recipients,$sender);
                exit;
            }
            
            $sth2->execute() || die "Problems obtaining pusu sequence - ERROR:$DBI::errstr\n";
            my $pusu_val = $sth2->fetchrow();
#            print "PUSU SEQUENCE VAL = $pusu_val\n";
            $sth3->execute($pusu_val,@$por) || die "Problems inserting data into the purchasing_summary table - ERROR:$DBI::errstr\n";

            $sth5->execute($por->[4]) || die "Problems updating is_transferred for order id $por->[4] - ERROR:$DBI::errstr\n";

        }
        
        
    }
    
};
if ($@){
    $message = "Error inserting data into purchasing_summary for $date - $@\n";
    &send_email($SUBJECT,$message,$recipients,$sender);
    $dbh->rollback();
    $dbh2->rollback();
} else {
    $message = "No errors detected on inserting $total_data rows of data into PURCHASING_SUMMARY for $date.\n";
    &send_email($SUBJECT,$message,$recipients,$sender);
    $dbh->commit();
    $dbh2->commit();
} 

$sth_get_date->finish();
$sth2->finish();
$sth3->finish();
$sth4->finish();
$sth5->finish();


sub send_email {
    
    my ($SUBJECT,$message,$recipients,$sender) = @_;
    
    open(MAIL, "| /usr/sbin/sendmail $recipients") || die "Cant open pipe to sendmail\n";
    print MAIL "To: $recipients\n";
    print MAIL "From: $sender\n";
    print MAIL "Subject: $SUBJECT\n";
    print MAIL "$message\n";
    print MAIL "If attention is needed contact kva or helpdesk.\n";
    close MAIL;
}



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


__END__

=head1 NAME

 insert_tm58primers.pl

=head1 SYNOPSIS

 insert_tm58primers.pl [args]

=head1 DESCRIPTION

=head1 AUTHOR

 Karen Ambrose

=head1 HISTORY

 # $Log: insert_finprimers.pl,v $
% Revision 1.3  2006/10/01  08:40:49  kva
% Altered the purchase order number field.
%
% Revision 1.2  2006/09/27  15:11:23  kva
% Script updates the purchasing_summary table with information relating to the finishing primer orders.
%
% Revision 1.1  2006/09/27  15:10:04  kva
% Initial revision
%
% Revision 1.17  2006/05/25  10:06:05  kva
% *** empty log message ***
%
% Revision 1.16  2005/09/30  15:55:29  kva
% Altered the sql to incorporate the new prefix and use the current purchase order for that month for all orders.
%
% Revision 1.15  2005/09/09  08:49:23  kva
% Altered the tm58 costcode, the format of the type and removed the calulation of the primer order total.
%
% Revision 1.14  2005/06/14  15:16:15  kva
% Altered the db connection to live snp.
%
% Revision 1.13  2005/06/02  07:50:00  kva
% *** empty log message ***
%
% Revision 1.12  2005/05/31  09:13:46  kva
% *** empty log message ***
%
% Revision 1.11  2005/05/31  09:04:28  kva
% Altered the script to use a global variable to set the time frame.
%
% Revision 1.10  2005/02/10  13:56:39  kva
% Altered the base price.
%
% Revision 1.9  2005/01/27  16:36:22  kva
% Updated the login details.
%
% Revision 1.8  2004/07/27  16:15:54  kva
% *** empty log message ***
%
% Revision 1.7  2004/07/26  09:22:12  kva
% Changed the order of the insert information, due to the order list table change in the underlying table.
%
% Revision 1.6  2004/07/22  10:33:50  kva
% Altered the login details.
%
% Revision 1.5  2004/01/19  09:20:42  kva
% Removed seh from the list of recipients for the purchasing primer insert email.
%
% Revision 1.4  2003/12/09  17:27:53  kva
% *** empty log message ***
%
% Revision 1.3  2003/12/09  15:57:25  kva
% *** empty log message ***
%
% Revision 1.2  2003/12/09  15:50:26  kva
% *** empty log message ***
%
% Revision 1.1  2003/12/09  15:49:21  kva
% Initial revision
%

=cut



select 'GEN005' VID,p.id_costcode PN,e.order_date ORD_DTE,DECODE(PS.PROGRAM,'REORDER','reorder','standard') TYPE,e.order_id ID_ORD,decode(PS.PROGRAM,'REORDER','0','0.12') BP,'0.00' PP,'0' OP,'TP' TP,ct.COST_TEAM CC1 , ct.COST_PROJECT CC2, sum(length(p.primerseq)) pseq_len, count(e.id_primer) p_count, ps.operator RB,ct.COST_TEAM TM,'cas1' ORD,0 ISC,0 ISR
from ext_fin_primer_orders@cas1 e, primer@cas1 p, primer_status@cas1 ps, costcode@cas1 ct
where e.id_primer = p.id_primer
and p.id_primer = ps.id_primer
and p.id_costcode = ct.id_costcode
and ps.status = 1
and e.is_transferred = 0
group by 'GEN005',p.id_costcode,e.order_date,PS.PROGRAM,e.order_id,PS.PROGRAM,'0.00','0','TP',ct.COST_TEAM,ct.COST_PROJECT,ps.operator,ct.COST_TEAM,'cas1',0,0
order by e.order_id
