#!/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: insert_tm58primers.pl,v 1.18 2006/09/26 14:07:52 kva Exp $

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


my $recipients = 'kva';

#cas connection
my $db = 'reports';

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

#snp connection
my $db2 = 'ppurch';

my ($dbh,$dbh2);

my $time = 1;

my $SUBJECT = "PURCHASING_SUMMARY INSERT TEAM58";

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;
}


#my $stat1 = "SELECT a.VID, a.PN, a.ORD_DTE, a.TYPE, a.ID_ORD, a.BP, a.PP, a.OP, (a.BP*sum(a.pseq_len))+a.PP TP, a.CC, sum(a.pseq_len) S_LEN, DECODE(a.TYPE,'REORDER',0,count(a.p_count)) C_PRIM, a.RB, a.TM, a.ORD, a.ISC, a.ISR
#FROM (
#SELECT 'GEN005' VID,PP.ID_PURCHASEORDER PN,LPS.STATUSDATE ORD_DTE,DECODE(LPS.PROGRAM,'REORDER','reorder','standard') TYPE,LPS.ID_LOADINGPLATE ID_ORD,decode(LPS.PROGRAM,'REORDER','0','0.12') BP,'6.00' PP,'0' OP,'TP' TP,'0580000' CC,DECODE(LPS.PROGRAM,'REORDER',0,LENGTH(P.PRIMERSEQ)) pseq_len, P.ID_PRIMER p_count,'team58' RB,58 TM,'cas1' ORD,0 ISC,0 ISR
#FROM PRIMER_PLATE PP, LOADINGPLATE LP, LOADINGPLATE_STATUS LPS, PRIMER_MAP PM, PRIMER P
#WHERE PP.ID_PRIMERPLATE = LP.ID_PRIMERPLATE
#AND LP.ID_LOADINGPLATE = LPS.ID_LOADINGPLATE
#AND PP.ID_PRIMERPLATE = PM.ID_PRIMERPLATE
#AND PM.ID_PRIMER = P.ID_PRIMER
#AND LPS.STATUS = 12
#AND to_char(LPS.STATUSDATE,'DD-MON-YY') = to_char(SYSDATE-?,'DD-MON-YY')
#GROUP BY PP.ID_PURCHASEORDER,LPS.STATUSDATE,LPS.PROGRAM,LPS.ID_LOADINGPLATE,P.PRIMERSEQ,P.ID_PRIMER) a
#group by a.VID, a.PN, a.ORD_DTE, a.TYPE, a.ID_ORD, a.BP, a.PP, a.OP, a.CC, a.RB, a.ORD, a.ISC, a.TM, a.ISR";


my $stat1 = "SELECT a.VID, a.PN, a.ORD_DTE, a.TYPE, a.ID_ORD, a.BP, a.PP, a.OP, (a.BP*sum(a.pseq_len))+a.PP TP, a.CC, sum(a.pseq_len) S_LEN, DECODE(a.TYPE,'REORDER',0,count(a.p_count)) C_PRIM, a.RB, a.TM, a.ORD, a.ISC, a.ISR
FROM (
SELECT 'GEN005' VID,PTPD.PREFIX||POD.ID_PURCHASEORDER PN,LPS.STATUSDATE ORD_DTE,DECODE(LPS.PROGRAM,'REORDER','reorder','standard') TYPE,LPS.ID_LOADINGPLATE ID_ORD,decode(LPS.PROGRAM,'REORDER','0','0.12') BP,'6.00' PP,'0' OP,'TP' TP,'0580000' CC,DECODE(LPS.PROGRAM,'REORDER',0,LENGTH(P.PRIMERSEQ)) pseq_len, P.ID_PRIMER p_count,'team58' RB,58 TM,'cas1' ORD,0 ISC,0 ISR
FROM PRIMER_PLATE PP, LOADINGPLATE LP, LOADINGPLATE_STATUS LPS, PRIMER_MAP PM, PRIMER P, PURCHASE_TEAM_PREFIX_DICT PTPD, PURCHASEORDERDICT POD
WHERE PP.ID_PRIMERPLATE = LP.ID_PRIMERPLATE
AND LP.ID_LOADINGPLATE = LPS.ID_LOADINGPLATE
AND PP.ID_PRIMERPLATE = PM.ID_PRIMERPLATE
AND PM.ID_PRIMER = P.ID_PRIMER
AND LPS.STATUS = 12
AND to_char(LPS.STATUSDATE,'DD-MON-YY') = to_char(SYSDATE-?,'DD-MON-YY')
AND to_char(POD.ASSIGN_DATE,'MON-YY') = to_char(SYSDATE,'MON-YY') 
AND POD.ISCURRENT = 1
AND LP.TEAM = PTPD.TEAM
GROUP BY PTPD.PREFIX,POD.ID_PURCHASEORDER,LPS.STATUSDATE,LPS.PROGRAM,LPS.ID_LOADINGPLATE,P.PRIMERSEQ,P.ID_PRIMER) a
group by a.VID, a.PN, a.ORD_DTE, a.TYPE, a.ID_ORD, a.BP, a.PP, a.OP, a.CC, a.RB, a.ORD, a.ISC, a.TM, a.ISR";


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 count(*) from purchasing_summary where team = 58 and to_char(order_date,'DD-MON-YY') = to_char(SYSDATE-?,'DD-MON-YY')";
my $sth4 = $dbh2->prepare($stat4);

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 {

        $sth4->execute($time) || die "Problems obtaining count from purchasing_summary - ERROR:$DBI::errstr\n";
        my $prev_count = $sth4->fetchrow();
        
        if ($prev_count == 0){

            foreach my $por (@$primer_ord_ref){
                $count++;
#                print "entry $count >>@$por<<\n";
                
                $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";
            }
            
        } else {
            $message = "Previous entries were found for $date in PURCHASING_SUMMARY, you may need to investigate further!\n";
            &send_email($SUBJECT,$message,$recipients,$sender);
            exit;
        }
    }
    
};
if ($@){
    $message = "Error inserting data into purchasing_summary for $date - $@\n";
    &send_email($SUBJECT,$message,$recipients,$sender);

    $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);

    $dbh2->commit();
} 

$sth_get_date->finish();
$sth2->finish();
$sth3->finish();
$sth4->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_tm58primers.pl,v $
% Revision 1.18  2006/09/26  14:07:52  kva
% *** empty log message ***
%
% 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


##OLD SQL FOR GATHERING THE RESULTS
#SELECT a.VID, a.PN, a.ORD_DTE, a.TYPE, a.ID_ORD, a.BP, a.PP, (a.BP*sum(a.pseq_len)) OP, (a.BP*sum(a.pseq_len))+a.PP TP, a.CC, sum(a.pseq_len) S_LEN, DECODE(a.TYPE,'REORDER',0,count(a.p_count)) C_PRIM, a.RB, a.TM, a.ORD, a.ISC, a.ISR
#FROM (
#SELECT 'GEN005' VID,PP.ID_PURCHASEORDER PN,LPS.STATUSDATE ORD_DTE,DECODE(LPS.PROGRAM,'REORDER','reorder','standard') TYPE,LPS.ID_LOADINGPLATE ID_ORD,decode(LPS.PROGRAM,'REORDER','0','0.12') BP,'6.00' PP,'OP' OP,'TP' TP,'0580010' CC,DECODE(LPS.PROGRAM,'REORDER',0,LENGTH(P.PRIMERSEQ)) pseq_len, P.ID_PRIMER p_count,'team58' RB,58 TM,'cas1' ORD,0 ISC,0 ISR
#FROM PRIMER_PLATE PP, LOADINGPLATE LP, LOADINGPLATE_STATUS LPS, PRIMER_MAP PM, PRIMER P
#WHERE PP.ID_PRIMERPLATE = LP.ID_PRIMERPLATE
#AND LP.ID_LOADINGPLATE = LPS.ID_LOADINGPLATE
#AND PP.ID_PRIMERPLATE = PM.ID_PRIMERPLATE
#AND PM.ID_PRIMER = P.ID_PRIMER
#AND LPS.STATUS = 12
#AND to_char(LPS.STATUSDATE,'DD-MON-YY') = to_char(SYSDATE-?,'DD-MON-YY')
#GROUP BY PP.ID_PURCHASEORDER,LPS.STATUSDATE,LPS.PROGRAM,LPS.ID_LOADINGPLATE,P.PRIMERSEQ,P.ID_PRIMER) a
#group by a.VID, a.PN, a.ORD_DTE, a.TYPE, a.ID_ORD, a.BP, a.PP, a.CC, a.RB, a.ORD, a.ISC, a.TM, a.ISR
