#!/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.                      #
#######################################################################
# $Id: estfin_hack.pl,v 1.16 2006/06/06 12:52:47 kva Exp $

BEGIN {
#    warn "Using local version of estfin_hack.pl ...\n";
};

use strict;
use sql_engine;
require 'badgerGetOpt.pl';

##THINGS DONE AND PROCESS OF EXECUTION:-
##submit a loadingplate ID
##update the category of the plate to EST plate id_dict 5 or VIRAL plate id_dict 15
##create the link between loadingsample.id_dnaplatemap and dnaplate_map.id_dnaplatemap for
##the EST/VIRAL plate.

my $prog = $ENV{'SCRIPT_NAME'};
my $lp_id;
my $db;
my $debug;

#Command-line short cuts
my $opts = {
    "-lp" => {
        "type" => "text",
        "var"  => \$lp_id,
        "help" => "Loadingplate ID",
    },

    "-database" => {
        "type" => "text",
        "var"  => \$db,
        "help" => "database user",
    },

    "-debug" => {
        "type" => "boolean",
        "var"  => \$debug,
        "help" => "debugging tags",
    }
};
&badgerGetOpt($prog,'',$opts) && die "Quit\n";

unless ($db){
    ##Database connection
#    $db = 'tcas_finch'; ##for testing
    $db = 'finch'; ##for live use
}

print "Database connection is to user $db.\n" if $debug==1;

my $STATEMENTS;
eval {
    $STATEMENTS = statements->new("$db");
};
if ($@){
    warn "$@\nConnection problem !\n";
    die;
}

#sql_engine library files
#$STATEMENTS->default_sql_lib_path('./'); ##for testing
$STATEMENTS->default_sql_lib_path('/usr/local/badger/bin/'); ##for live use
$STATEMENTS->load_sql_lib(["estfin.sql_lib::all"]);
my $dbh = $STATEMENTS->{DBH};

unless ($lp_id){
    print "No loadingplate ID was detected.\n";
    exit;
}

print "LOADINGPLATE ID DEFINED AS - $lp_id\n" if $debug==1;

##check that the lp's are valid in db
my @not_exist;
my @lp_list = split(/\,/,$lp_id);
my $count = 0;
foreach (@lp_list){
    $count++;
    $_=~ s/\s+//g;
    print "NUMBER $count - $_\n" if $debug==1;
    my $get_loadingplate = $STATEMENTS->get("estfin.sql_lib::CHECK_LP_EXISTS",[$_]);
    my $lp_exist;
    for my $j (0 .. $#{$get_loadingplate}){
        $lp_exist = $get_loadingplate->[$j]->[0];
    }
    push @not_exist,$_ unless $lp_exist;
}

if (scalar(@not_exist)>0){
    print "Loadingplate ID @not_exist do not exist in the database\n";
    print "Either omit or rectify and try again.\n";
    die;
}


foreach my $loadingplate (@lp_list){
    print "\n###############################\n";
    print "Loadingplate ID is $loadingplate\n";

    #check the repositorytype
    my $TYPE = $STATEMENTS->get("estfin.sql_lib::CHECK_TYPE",[$loadingplate]);
    print ">>TYPE IS $TYPE->[0]->[0]<<\n" if $debug==1;

    if (scalar(@$TYPE)>1) {
        print "Loadingplate $loadingplate has more than 1 type ($TYPE->[0]->[0],$TYPE->[1]->[0]).\n";
        print "Please remove from the list and rerun the script!!\n";
        next;
    }

    #SEQCATEGORY ASSIGNMENT DEPENDENT ON TYPE
    my $hashtypes = {
        '8' => '5', 
        '17'=> '15'
        }; 
    my %TYPE_HASH = %$hashtypes;

    if (($TYPE->[0]->[0] == 8)||($TYPE->[0]->[0] == 17)){
        #Check the loadingplate category.
        my $get_loadingcat = $STATEMENTS->get("estfin.sql_lib::CHECK_LPCAT",[$loadingplate]);
        my $category;
        for my $j (0 .. $#{$get_loadingcat}){
            $category = $get_loadingcat->[$j]->[0];
        }
        
        print ">>CATEGORY IS $category<<\n" if $debug==1;
        
        my $count1=0;    
        if ($category eq '4'){
            
            eval {
                
                ##Obtain the correct seqcategory for the repository type
                ##and update the current loadingplate entry.
                my $seqcategory_type = $TYPE_HASH{$TYPE->[0]->[0]};
                print ">>SEQCATEGORY IS $seqcategory_type<<\n" if $debug==1;

                my $update_loadingplate = $STATEMENTS->do("estfin.sql_lib::UPDATE_LP_TO_EST",[$seqcategory_type,$loadingplate]);
                
                my $rec = $STATEMENTS->get("estfin.sql_lib::GET_LP_LIST",[$loadingplate]);
                my $count = @$rec;
                print "Number of samples:- $count\n";
                if ($count ne '96'){
                    print "96 entries were not detected, please contact bug!!\n";
                    die;
                }
                
                foreach my $sample  (@$rec) {
                    my $PLTE1 = sprintf("%s%s%02d",
                                        $sample->[0], $sample->[1], $sample->[2]);
                    my $PLTE2 = sprintf("%s%s%d",
                                        $sample->[0], $sample->[1], $sample->[2]);
                    my $LSP_ID = $sample->[3];
                    my $FB_PROJECT = $sample->[4];
                    print ">>$PLTE1 <=> $PLTE2 => $LSP_ID => $FB_PROJECT<<\n" if $debug==1;
                    
                    my $rec2 = $STATEMENTS->get("estfin.sql_lib::GET_DPLATEMAP_INFO",[$PLTE1,$PLTE2,$FB_PROJECT]);
                    
                    my $dp_rec;
                    for my $j (0 .. $#{$rec2}){
                        $dp_rec = $rec2->[$j]->[0];
                    }
                    
                    unless ($dp_rec && @$rec2) {
                        $rec2 = $STATEMENTS->get("estfin.sql_lib::GET_DPLATEMAP_INFO_BB", [$sample->[0], $sample->[1], $sample->[2], $FB_PROJECT]);
                        
                        
                        for my $j (0 .. $#{$rec2}){
                            $dp_rec = $rec2->[$j]->[0];
                        }
                    }
                    
                    
                    print ">>CHECK IF THE DNAPLATE INFO IS DEFINED $dp_rec<<\n" if $debug==1;
                    
                    
                    unless ($dp_rec && @$rec2) {
                        warn "No info for @$sample\n";
                    }
                    
##
##Put in place where plates have samples that do not
##satisfy the first sql statement.
##
##NOTE:-
##The problem was due to the fact that the query which inserts the
##dnaplate_map information checks that the project the sample belongs to is
##the same as the project related to the finish_batch.
##
##I have not added this into the general script as yet as the project
##addition was put in place to stop the roslin problem, and if anything
##should bypass this then we could get into even more problems.
                    
                    
#                my $total = @$rec2;
#                if ($total == 0){
#                    my $rec3 = $STATEMENTS->get("estfin.sql_lib::GET_DPLATEMAP_INFO2",[$PLTE1,$PLTE2]);
#                    for my $k (0 .. $#{$rec3}){
#                        my $DPMP_ID= $rec3->[$k]->[0];
#                        print "Using 2nd statement = $DPMP_ID\n";
#                        unless (! defined $DPMP_ID){
#			    $count1++;
#			    print ">>COUNT $count1 DNAPLATEMAP $DPMP_ID<<\n" if $debug==1;
#			    my $update_loadingsample = $STATEMENTS->do("estfin.sql_lib::UPDATE_LOADINGSAMPLE",[$DPMP_ID,$LSP_ID]);  
#			}  
#                    }
#                }
                    
                    for my $i (0 .. $#{$rec2}){
                        my $DPMP_ID = $rec2->[$i]->[0];
                        unless (! defined $DPMP_ID){
                            $count1++;
                            print ">>COUNT $count1 DNAPLATEMAP $DPMP_ID<<\n" if $debug==1;
                            my $update_loadingsample = $STATEMENTS->do("estfin.sql_lib::UPDATE_LOADINGSAMPLE",[$DPMP_ID,$LSP_ID]);
                        }
                    }
                }
                
                if ($count1 != $count){
                    warn "Number of samples updated $count1 is not equal to number of sample to update $count\n";
                    warn "Updated entries for plate $loadingplate have been rolled back in the database, contact bug!!\n";
                    exit;
                }
            };
            if ($@){
                warn "Error detected :- $@\n";
                $STATEMENTS->{DBH}->rollback;
                die;
            } else {
                $STATEMENTS->{DBH}->commit;
                #$STATEMENTS->{DBH}->rollback;
                print "The information has been commited to the database\n";
                print "No problems detected.\n";
                exit;
            }
            
        } else {
            
            print "Not a finishing plate it is a category $category plate.\n";
            exit;
        }
    }
    
} 

END {
    
#    print STDOUT "\n\n\n-----------------------------\n";
#    print STDOUT "END BLOCK; rolling back !\n";
    $STATEMENTS->{DBH}->rollback;
    $STATEMENTS->end;
    
};


#select a.LOADSAMP,dpm.id_dnaplatemap
#select a.PLTE, a.PLTE2, sm.samplename, sm.id_sourceplatemap, dpm.id_dnaplatemap, a.LOADSAMP
#from sourceplate_map sm, bb_map bbm, dnaplate_map dpm, (select platename||rowletter||decode(length(colnum),1,0||colnum,colnum) PLTE, platename||rowletter||colnum PLTE2, ls.id_loadingsample LOADSAMP
#from finish_request fr, request_map rm, loadingsample ls
#where fr.id_frequest = rm.id_frequest
#and rm.id_loadingsample = ls.id_loadingsample
#and ls.id_loadingplate = 626135
#order by ls.id_loadingplate)a
#where (a.PLTE = sm.samplename OR a.PLTE2 = sm.samplename)
#and sm.id_sourceplatemap in (select sourceplate_map.id_sourceplatemap 
#from sourceplate_map 
#where sourceplate_map.samplename = sm.samplename
#and rownum <2)
#and sm.id_sourceplatemap = bbm.id_sourceplatemap
#and bbm.id_bbmap = dpm.id_bbmap
#order by a.LOADSAMP;
#

#select * from loadingplate where id_loadingplate = 626135;
#select * from loadingsample where id_loadingplate = 626135 order by id_map
#sm.samplename = 'Ch910e22'
#select a.PLTE, a.PLTE2, sm.samplename, sm.id_sourceplatemap, dpm.id_dnaplatemap, a.LOADSAMP
#
##test loadingplate = 626135
#
#SELECT * FROM SOURCEPLATE_MAP WHERE SAMPLENAME = 'TGas029h09';
#
#SELECT MAX(ID_SOURCEPLATEMAP) FROM SOURCEPLATE_MAP;
#
#MAX(ID_SOURCEPLATEMAP)
#----------------------
#               2549744
#
#20000 - 20002 NEW SOURCEPLATE INFORMATION FOR TGas029.
#2700000 - 2700002 NEW SOURCEPLATE_MAP INFO FOR TGas029.
#
#insert into subclone values (92737700,39676);
#insert into bb_map values (86162600,92737700,883805,273,2700000);
#insert into dnaplate_map values (91207800,86162600,1187175,273);



__END__

=head1 NAME

 estfin_hack.pl

=head1 SYNOPSIS

 estfin_hack.pl [args]

=head1 DESCRIPTION

=head1 AUTHOR

 Karen Ambrose

=head1 HISTORY

 # $Log: estfin_hack.pl,v $
% Revision 1.16  2006/06/06  12:52:47  kva
% *** empty log message ***
%
% Revision 1.15  2006/06/06  12:48:03  kva
% *** empty log message ***
%
% Revision 1.14  2006/05/31  15:40:33  kva
% Additional functionality to handle the Viral seqcategory.
%
% Revision 1.13  2004/07/13  16:19:45  kva
% *** empty log message ***
%
% Revision 1.12  2004/07/13  15:39:43  kva
% Added extra checking.
%
% Revision 1.11  2004/05/17  11:08:33  rmd
% Now tries to look for finishing reads in beckman_box, bb_map if they don't
% exist in sourceplate_map.  This allows samples to be submitted for
% sourceplate_map entries that don't have a row, column address at the end of
% the name.
%
% Revision 1.10  2004/02/27  12:04:59  kva
% *** empty log message ***
%
% Revision 1.9  2004/02/27  11:58:21  kva
% *** empty log message ***
%
% Revision 1.8  2004/01/15  14:01:27  kva
% *** empty log message ***
%
% Revision 1.7  2004/01/09  15:40:12  kva
% Use of the projectname has been added in for further clarification.
%
% Revision 1.6  2003/11/20  15:04:08  kva
% Added the extra count of the samples to be updated with the dnaplate_map entry, along with the check to ensure that the number of samples and the number of samples updated are the same.
%
% Revision 1.5  2003/09/30  14:07:33  kva
% Seperated the get_dnaplate call into 2 for quicker and easier execution.
%
% Revision 1.4  2003/09/25  16:00:28  kva
% *** empty log message ***
%
% Revision 1.3  2003/09/25  15:42:04  kva
% Added the extra function to send the database user as an attribute so that the script can be run from another script under it's db user or independantly.
%
% Revision 1.2  2003/09/18  10:12:14  kva
% Added debug tags and additional entity checking.
%
% Revision 1.1  2003/09/18  08:36:40  kva
% Initial revision
%
% Revision 1.1  2003/09/18  08:31:50  kva
% Initial revision
%

=cut
