#!/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: process_insert.pl,v 1.3 2006/06/16 16:12:20 kva Exp $

###################################################
# Script copies process data from a 
# specified top level process id.
# Produces an output file:- output_pi.txt
# detailing the information to be inserted into 
# the live database.
#
# Author: kva
###################################################

## NOTE REM TO ADD THE MISSING ARRAYPATTERNDICT, ARRAY_PATTERN DATA & PROCESS_ARRAY_PATTERN.
## RUN QUERY TO CHECK FOR PROCESSES ATTACHED TO ARRAYPATTERNDICT ENTRIES NOT CURRENTLY
## PRESENT IN THE GTR.ARRAYPATTERNDICT/ARRAY_PATTERN IF NOT NOTIFY & STOP SCRIPT SO THAT
## THE TABLE ON CAS CAN BE UPDATED AND THE SNAPSHOT REFRESHED BEFORE RUNNING THE SCRIPT 
## AGAIN TO INSERT THE PROCESS INFORMATION.

BEGIN {
#    warn "Using test version of process_insert script.....\n";
#    $ENV{WRAPDBI_TEST_CONFIG} = $ENV{'HOME'}.'/badger-bin/finch/test_dblogs/DBlogs.tgtr';
#    $ENV{WRAPDBI_TEST_CONFIG} = $ENV{'HOME'}.'/DBlogs.tgtr';
    $ENV{DEBUG_WRAPDBI} = 1;
};

use strict;
use WrapDBI;
use Term::ReadKey;
require 'badgerGetOpt.pl';
my $prog = 'process_insert.pl';

my($processid);
my $opts = {   
    "-pid" => {
        "type" => "number",
        "var" => \$processid,
        "help" => "Process ID.",
    }
};

&badgerGetOpt($prog,'',$opts) && die "Quit\n";

if (!$processid) {
    print "Process ID needs to be provided to run this program\n";
    die;
}

##file name for output.
my $filename = $processid."-output_pi.txt";

# ADD IN THE SECTION WHICH ASKS WETHER YOU WANT TO PROCEED AND INSERT THE PROCESS DATA FOUND        
ReadMode('cbreak');
warn "\nThis is the process_insert script for GTR, it can run in select mode or update mode.\n";
warn "\nDo you want to run the Update Statements in this sesssion, y|n?\n";
my $char = ReadKey(0);
if ($char eq 'n'){ 
    warn "Only select statements will be run and the output captured in $filename\n";
} else {
    warn "Select & Update statements will be run and the output captured in $filename\n";
}
ReadMode('normal');    


##open the filehandle
open(STDOUT, "> $filename");

my $db = 'gt_process';

my $dbh = WrapDBI->connect("$db", {RaiseError => 1});    
$dbh->{autocommit} = 0;
print STDOUT "process insert script started...\n";

eval {

    my ($sth,$sth1,$sth2,$sth3,$sth4,$sth5,$sth5_5,$sth6,$sth7,$sth8);

my $stat = "SELECT LEVEL, ID_PROCESS, CODE, UNDER_PROCESS
FROM PROCESS\@TGTR2.WORLD
CONNECT BY PRIOR ID_PROCESS = UNDER_PROCESS
START WITH ID_PROCESS = ?
ORDER BY ID_PROCESS, UNDER_PROCESS";

my @process_ids;

$sth = $dbh->prepare($stat);
$sth->execute($processid);
my $count=0;
my($level,$process,$code,$uprocess);
while(($level,$process,$code,$uprocess)=$sth->fetchrow()){
    $count++;
    if (!$uprocess){
        $uprocess = 'T/L';
    }
    print "ENTRY $count --> LEVEL $level: PROCESS $process: CODE $code: UNDER_PROCESS $uprocess\n";
    push @process_ids,$process;
}

my $c = @process_ids;
print "$c\n";


##Check wether the processes rely on array pattern entries in tgtr2 that are not present in gtr.
##Update the array_pattern and arraypatterdict from TCAS->CAS and then upgrade the snapshot from
##CAS->GTR. Also get the list of missing array patterns.
    my $no_dependency;
    my $smt = "select count(*) from process_array_pattern\@tgtr2 where from_process_io in (select id_process_concrete_io from process_concrete_io\@tgtr2 where id_process  = ?) and using_array_pattern not in (select id_dict from arraypatterndict) and using_array_pattern not in (select id_dict from arraypatterndict)";
    my $sth_chk=$dbh->prepare($smt);

    my $smt_pattern = "select USING_ARRAY_PATTERN from process_array_pattern\@tgtr2 where from_process_io in (select id_process_concrete_io from process_concrete_io\@tgtr2 where id_process  = ?) and using_array_pattern not in (select id_dict from arraypatterndict) and using_array_pattern not in (select id_dict from arraypatterndict)";
    my $sth_pattern_chk=$dbh->prepare($smt_pattern);
    my %UNIQUE;
    my @u_list;

    foreach my $pid (sort @process_ids){
        $sth_chk->execute($pid);
        $sth_pattern_chk->execute($pid);
        $no_dependency+=$sth_chk->fetchrow();
        my $pattern=$sth_pattern_chk->fetchrow();
        if ($pattern){
            unless ($UNIQUE{$pattern}){
                $UNIQUE{$pattern}=1;
                push(@u_list,$pattern);
            }
        }
    }
    $sth_chk->finish();
    
    if ($no_dependency > 0){
        print "Number of dependencies are $no_dependency, the snapshot will need to be refreshed.\n";
        print "The array patterns missing from CAS/GTR are @u_list.\n";
        exit;
    } else {
        print "No dependencies detected.\n";
    }
    

    foreach my $pid (sort @process_ids){
        print ">>$pid<<\n";
        
## SELECT PROCESS_TYPE_DICT
        my $smt1 = "SELECT * 
FROM PROCESS_TYPE_DICT\@TGTR2.WORLD 
WHERE ID_PROCESS_TYPE IN (SELECT ID_PROCESS_TYPE 
FROM PROCESS\@TGTR2.WORLD 
WHERE ID_PROCESS = ?) AND ID_PROCESS_TYPE NOT IN (SELECT ID_PROCESS_TYPE FROM PROCESS_TYPE_DICT)";
        
        my $smt1_ref = $dbh->selectall_arrayref($smt1,{},$pid) || die "SM1 ERROR:$DBI::errstr";
        foreach my $ref (@{$smt1_ref}){
            print "smt1 @$ref\n";
        }
        
## SELECT PROCESS
        my $smt2 = "SELECT * FROM PROCESS\@TGTR2.WORLD WHERE ID_PROCESS = ? AND ID_PROCESS NOT IN (SELECT ID_PROCESS FROM PROCESS)";
        
        my $smt2_ref = $dbh->selectall_arrayref($smt2,{},$pid) || die "SM2 ERROR:$DBI::errstr";
        foreach my $ref (@{$smt2_ref}){
            print "smt2 @$ref\n";
        }
        
## SELECT PATTERN_DICT
        my $smt3 = "SELECT *
FROM PATTERN_DICT\@TGTR2.WORLD
WHERE ID_PATTERN IN (SELECT ID_PATTERN 
FROM CONCRETE_IO\@TGTR2.WORLD 
WHERE ID_CONCRETE_IO IN (SELECT ID_CONCRETE_IO 
FROM PROCESS_CONCRETE_IO\@TGTR2.WORLD
WHERE ID_PROCESS = ?)) AND ID_PATTERN NOT IN (SELECT ID_PATTERN FROM PATTERN_DICT)";
        
        my $smt3_ref = $dbh->selectall_arrayref($smt3,{},$pid) || die "SM3 ERROR:$DBI::errstr";
        foreach my $ref (@{$smt3_ref}){
            print "smt3 @$ref\n";
        }
        
## SELECT CONCRETE_ENTITY
        my $smt4 = "SELECT * 
FROM CONCRETE_ENTITY\@TGTR2.WORLD
WHERE ID_CONCRETE_ENTITY IN (SELECT ID_CONCRETE_ENTITY
FROM CONCRETE_IO\@TGTR2.WORLD
WHERE ID_CONCRETE_IO IN (SELECT DISTINCT ID_CONCRETE_IO
FROM PROCESS_CONCRETE_IO\@TGTR2.WORLD
WHERE ID_PROCESS = ?)) AND ID_CONCRETE_ENTITY NOT IN (SELECT ID_CONCRETE_ENTITY FROM CONCRETE_ENTITY)";
        
        my $smt4_ref = $dbh->selectall_arrayref($smt4,{},$pid) || die "SM4 ERROR:$DBI::errstr";
        foreach my $ref (@{$smt4_ref}){
            print "smt4 @$ref\n";
        }
        
## SELECT CONCRETE_IO
        my $smt5 = "SELECT *
FROM CONCRETE_IO\@TGTR2.WORLD 
WHERE ID_CONCRETE_IO IN (SELECT DISTINCT ID_CONCRETE_IO 
FROM PROCESS_CONCRETE_IO\@TGTR2.WORLD
WHERE ID_PROCESS = ?) AND ID_CONCRETE_IO NOT IN (SELECT ID_CONCRETE_IO FROM CONCRETE_IO)";
        
        my $smt5_ref = $dbh->selectall_arrayref($smt5,{},$pid) || die "SM5 ERROR:$DBI::errstr";
        foreach my $ref (@{$smt5_ref}){
            print "smt5 @$ref\n";
        }
        
## SELECT PROCESS_CONCRETE_IO
        my $smt5_5 = "SELECT *
FROM PROCESS_CONCRETE_IO\@TGTR2.WORLD
WHERE ID_PROCESS = ? 
AND ID_PROCESS_CONCRETE_IO NOT IN (SELECT ID_PROCESS_CONCRETE_IO FROM PROCESS_CONCRETE_IO)";
        
        my $smt5_5_ref = $dbh->selectall_arrayref($smt5_5,{},$pid) || die "SM5 ERROR:$DBI::errstr";
        foreach my $ref (@{$smt5_5_ref}){
            print "smt5_5 @$ref\n";
        }
        
## SELECT CONTENT_CARDINALITY
        my $smt6 = "SELECT * 
FROM CONTENT_CARDINALITY\@TGTR2.WORLD
WHERE ID_CONCRETE_IO IN (SELECT DISTINCT ID_CONCRETE_IO
FROM PROCESS_CONCRETE_IO\@TGTR2.WORLD
WHERE ID_PROCESS = ?) AND (ID_CONCRETE_IO,ID_ENTITY_CONTENTS_TYPE) NOT IN (SELECT ID_CONCRETE_IO,ID_ENTITY_CONTENTS_TYPE FROM CONTENT_CARDINALITY)";
        
        my $smt6_ref = $dbh->selectall_arrayref($smt6,{},$pid) || die "SM6 ERROR:$DBI::errstr";
        foreach my $ref (@{$smt6_ref}){
            print "smt6 @$ref\n";
        }
        
## SELECT INFO_ATTRIBUTE_CARDINALITY
        my $smt7 = "SELECT *
FROM INFO_ATTRIBUTE_CARDINALITY\@TGTR2.WORLD
WHERE ID_CONCRETE_IO IN (SELECT DISTINCT ID_CONCRETE_IO
FROM PROCESS_CONCRETE_IO\@TGTR2.WORLD
WHERE ID_PROCESS = ?) AND (ID_CONCRETE_IO,ID_ENTITY_CONTENTS_TYPE,ID_CONTENT_TYPE,ID_REASON,ID_ATTRIBUTE_TYPE) NOT IN (SELECT ID_CONCRETE_IO,ID_ENTITY_CONTENTS_TYPE,ID_CONTENT_TYPE,ID_REASON,ID_ATTRIBUTE_TYPE
FROM INFO_ATTRIBUTE_CARDINALITY)";
        
        my $smt7_ref = $dbh->selectall_arrayref($smt7,{},$pid) || die "SM7 ERROR:$DBI::errstr";
        foreach my $ref (@{$smt7_ref}){
            print "smt7 @$ref\n" if $ref;
        }
        

## SELECT PROCESS_ARRAY_PATTERN
        my $smt8 = "SELECT *
FROM PROCESS_ARRAY_PATTERN\@TGTR2.WORLD
WHERE FROM_PROCESS_IO IN (SELECT ID_PROCESS_CONCRETE_IO
FROM PROCESS_CONCRETE_IO\@TGTR2.WORLD
WHERE ID_PROCESS = ?) AND (FROM_PROCESS_IO,TO_PROCESS_IO,USING_ARRAY_PATTERN) NOT IN (SELECT FROM_PROCESS_IO,TO_PROCESS_IO,USING_ARRAY_PATTERN FROM PROCESS_ARRAY_PATTERN)";
        
        my $smt8_ref = $dbh->selectall_arrayref($smt8,{},$pid) || die "SM8 ERROR:$DBI::errstr";
        foreach my $ref (@{$smt8_ref}){
            print "smt8 @$ref\n" if $ref;
        }
        

        if ($char eq 'y'){

            warn "RUNNING THE INSERT STATEMENTS FOR $pid.....................\n";


########################################################
## INSERT PROCESS_TYPE_DICT
#my $ins_smt1 = "INSERT INTO PROCESS_TYPE_DICT (SELECT * 
#FROM PROCESS_TYPE_DICT\@TGTR2.WORLD 
#WHERE ID_PROCESS_TYPE IN (SELECT ID_PROCESS_TYPE 
#FROM PROCESS\@TGTR2.WORLD 
#WHERE ID_PROCESS = ?) AND ID_PROCESS_TYPE NOT IN (SELECT ID_PROCESS_TYPE FROM PROCESS_TYPE_DICT))";
#
#$sth1=$dbh->prepare($ins_smt1);
#$sth1->execute($pid);
#print "ins_smt1 inserted\n";
#
### INSERT PROCESS
#my $ins_smt2 = "INSERT INTO PROCESS (SELECT * FROM PROCESS\@TGTR2.WORLD WHERE ID_PROCESS = ? AND ID_PROCESS NOT IN (SELECT ID_PROCESS FROM PROCESS))";
#
#$sth2=$dbh->prepare($ins_smt2);
#$sth2->execute($pid);
#print "ins_smt2 inserted\n";
#
#
### INSERT PATTERN_DICT
#my $ins_smt3 = "INSERT INTO PATTERN_DICT (ID_PATTERN, PATTERN_MATCH) (SELECT ID_PATTERN, PATTERN_MATCH 
#FROM PATTERN_DICT\@TGTR2.WORLD
#WHERE ID_PATTERN IN (SELECT ID_PATTERN 
#FROM CONCRETE_IO\@TGTR2.WORLD 
#WHERE ID_CONCRETE_IO IN (SELECT ID_CONCRETE_IO 
#FROM PROCESS_CONCRETE_IO\@TGTR2.WORLD
#WHERE ID_PROCESS = ?)) AND ID_PATTERN NOT IN (SELECT ID_PATTERN FROM PATTERN_DICT))";
#
#$sth3=$dbh->prepare($ins_smt3);
#$sth3->execute($pid);
#print "ins_smt3 inserted\n";
#
#
### INSERT CONCRETE_ENTITY
#my $ins_smt4 = "INSERT INTO CONCRETE_ENTITY (SELECT * 
#FROM CONCRETE_ENTITY\@TGTR2.WORLD
#WHERE ID_CONCRETE_ENTITY IN (SELECT ID_CONCRETE_ENTITY
#FROM CONCRETE_IO\@TGTR2.WORLD
#WHERE ID_CONCRETE_IO IN (SELECT DISTINCT ID_CONCRETE_IO
#FROM PROCESS_CONCRETE_IO\@TGTR2.WORLD
#WHERE ID_PROCESS = ?)) AND ID_CONCRETE_ENTITY NOT IN (SELECT ID_CONCRETE_ENTITY FROM CONCRETE_ENTITY))";
# 
#$sth4=$dbh->prepare($ins_smt4);
#$sth4->execute($pid);
#print "ins_smt4 inserted\n";
#
#
### INSERT CONCRETE_IO
#my $ins_smt5 = "INSERT INTO CONCRETE_IO (SELECT *
#FROM CONCRETE_IO\@TGTR2.WORLD 
#WHERE ID_CONCRETE_IO IN (SELECT DISTINCT ID_CONCRETE_IO 
#FROM PROCESS_CONCRETE_IO\@TGTR2.WORLD
#WHERE ID_PROCESS = ?) AND ID_CONCRETE_IO NOT IN (SELECT ID_CONCRETE_IO FROM CONCRETE_IO))";
#
#$sth5=$dbh->prepare($ins_smt5);
#$sth5->execute($pid);
#print "ins_smt5 inserted\n";
#
### INSERT PROCESS_CONCRETE_IO
#my $ins_smt5_5 = "INSERT INTO PROCESS_CONCRETE_IO (SELECT *
#FROM PROCESS_CONCRETE_IO\@TGTR2.WORLD
#WHERE ID_PROCESS = ?
#AND ID_PROCESS_CONCRETE_IO NOT IN (SELECT ID_PROCESS_CONCRETE_IO FROM PROCESS_CONCRETE_IO))";
#
#$sth5_5=$dbh->prepare($ins_smt5_5);
#$sth5_5->execute($pid);
#print "ins_smt5_5 inserted\n";
#
### INSERT CONTENT_CARDINALITY
#my $ins_smt6 = "INSERT INTO CONTENT_CARDINALITY (SELECT * 
#FROM CONTENT_CARDINALITY\@TGTR2.WORLD
#WHERE ID_CONCRETE_IO IN (SELECT DISTINCT ID_CONCRETE_IO
#FROM PROCESS_CONCRETE_IO\@TGTR2.WORLD
#WHERE ID_PROCESS = ?) AND (ID_CONCRETE_IO,ID_ENTITY_CONTENTS_TYPE) NOT IN (SELECT ID_CONCRETE_IO,ID_ENTITY_CONTENTS_TYPE FROM CONTENT_CARDINALITY))";
#
#$sth6=$dbh->prepare($ins_smt6);
#$sth6->execute($pid);
#print "ins_smt6 inserted\n";
#
#
### INSERT INFO_ATTRIBUTE_CARDINALITY
#my $ins_smt7 = "INSERT INTO INFO_ATTRIBUTE_CARDINALITY (SELECT *
#FROM INFO_ATTRIBUTE_CARDINALITY\@TGTR2.WORLD
#WHERE ID_CONCRETE_IO IN (SELECT DISTINCT ID_CONCRETE_IO
#FROM PROCESS_CONCRETE_IO\@TGTR2.WORLD
#WHERE ID_PROCESS = ?) AND (ID_CONCRETE_IO,ID_ENTITY_CONTENTS_TYPE,ID_CONTENT_TYPE,ID_REASON,ID_ATTRIBUTE_TYPE) NOT IN (SELECT ID_CONCRETE_IO,ID_ENTITY_CONTENTS_TYPE,ID_CONTENT_TYPE,ID_REASON,ID_ATTRIBUTE_TYPE FROM INFO_ATTRIBUTE_CARDINALITY))";
#
#$sth7=$dbh->prepare($ins_smt7);
#$sth7->execute($pid);
#print "ins_smt7 inserted\n";
#
#
### INSERT PROCESS_ARRAY_PATTERN
#        my $ins_smt8 = "INSERT INTO PROCESS_ARRAY_PATTERN (SELECT *
#FROM PROCESS_ARRAY_PATTERN\@TGTR.WORLD
#WHERE FROM_PROCESS_IO IN (SELECT ID_PROCESS_CONCRETE_IO
#FROM PROCESS_CONCRETE_IO\@TGTR.WORLD
#WHERE ID_PROCESS = ?))";
#        
#$sth8=$dbh->prepare($ins_smt8);
#$sth8->execute($pid);
#print "ins_smt8 inserted\n";
#
#    }
#    }
#
#$sth->finish();
#$sth1->finish();
#$sth2->finish();
#$sth3->finish();
#$sth4->finish();
#$sth5->finish();
#$sth5_5->finish();
#$sth6->finish();
#$sth7->finish();
#$sth8->finish();
 
}##only required when the INSERT BLOCK is hashed out.  
}##only required when the INSERT BLOCK is hashed out.

};

if ($@){
    print "Error raised : $@\n";
    print "Data will be rolled back.\n";
    $dbh->rollback();
} else {
    print "No errors found entries inserted\n";
    $dbh->rollback();
    #$dbh->commit();
}

close(STDOUT);


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


__END__

=head1 NAME

 process_insert.pl

=head1 SYNOPSIS

 process_insert.pl [args]

=head1 DESCRIPTION

=head1 AUTHOR

 Karen Ambrose

=head1 HISTORY

 # $Log: process_insert.pl,v $
% Revision 1.3  2006/06/16  16:12:20  kva
% Added an interactive session within the script to run in select or update mode.
%
% Revision 1.2  2005/02/04  15:23:32  kva
% Added comments to the script to document function.
%
% Revision 1.1  2005/02/04  15:11:09  kva
% Initial revision
%

=cut
