#/usr/bin/perl


#set the location for custom packages used by the scripts
use lib '/soldb/www/perllib/';
use db_link_coffee;


my $usr='annotator';

my $filein = shift;
my $db='cgn';

my %annotated_clones=();

open FILEIN, $filein;

while(<FILEIN>){
    my ($clone, $evalue, $annot)=split /\t/;
    $clone=~s/cccscccs/cccs/g;
    $clone=~s/cccscccp/cccp/g;
    $annot=~s/\'/\\\'/g;
    $annot=~s/\"/\\\"/g;
    $evalue=~s/\'/\\\'/g;
    $evalue=~s/\"/\\\"/g;
    $evalue=~s/\s//g;
    $evalue and $evalue="Match quality: " . $evalue . "<br>";
    $annotated_clones{$clone}=$evalue.$annot;
}

close FILEIN;



#open db link
unless($dbh = db_link_coffee::connect_db($db, $usr)){
    print "Couldn't connect to database $db as $usr";
    die;
}

my ($stm, $sth, $rv, $rc);

foreach (keys %annotated_clones){

#get the sequence id
    my $seq_id;
    $stm="select local_db_id from other_identifier where external_id='$_'";
    $sth = $dbh->prepare($stm) 
	|| die "Can't prepare statement: $DBI::errstr";
    $rv = $sth->execute
	|| die "Can't execute statement: $DBI::errstr";
    $rc = $sth->bind_columns(\$seq_id);
    unless($sth->fetch){
	print "couldn't find id for $_\n";
	next;
    }

    $stm="insert into annotation (seq_id, notes, author, date) values ('$seq_id', '$annotated_clones{$_}', '2', '2002-06-05')";
    $sth = $dbh->prepare($stm) 
	|| die "Can't prepare statement: $DBI::errstr";
    $rv = $sth->execute
	|| die "Can't execute statement: $DBI::errstr";
}

#close the db link
 db_link_coffee::disconnect_db($dbh);