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

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


# DN1
#################################
# General gull library (db info)
#################################
package sessionID;

sub new {
    my ($pkg, $dbh) = @_;
    my $statement_id = "select seq_session.nextval from dual";
    my $sth_id = $dbh->prepare($statement_id) || die "ERROR:$DBI::errstr";
    $sth_id->execute || die "ERROR:$DBI::errstr";
    my $session_id = $sth_id->fetchrow();
    $sth_id->finish;
    my $return = \$session_id;
    bless $return, 'sessionID';
    return ($return);
}

package personID;

sub new {
    my ($pkg, $dbh, $who) = @_;
    my $statement = "select id_person from person where email = '$who' and iscurrent = 1";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $id_person = $sth1->fetchrow();
    $sth1->finish;
    my $return = \$id_person;
    bless $return, 'personID';
    return ($return);
}

package roleID;

sub new {
    my ($pkg, $dbh, $who, $prog) = @_;
    my $statement = "
select team_person_role.id_role 
from person, team_person_role 
where person.id_person = team_person_role.id_person
and person.email = \'$who\'
and team_person_role.roletype = '$prog'
and team_person_role.iscurrent = 1
and person.iscurrent = 1
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $id_role = $sth->fetchrow();
    $sth->finish;
    my $return = \$id_role;
    bless $return, 'roleID';
    return ($return);
}

package teamname;

sub new {
    my ($pkg, $dbh, $id_person) = @_;
    my $statement = "select teamname from team_person where id_person = $$id_person";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth1->execute || die "ERROR:$DBI::errstr";
    my $teamname = $sth1->fetchrow();
    $sth1->finish();
    my $return = \$teamname;
    bless $return, 'teamname';
    return ($return);
}

package sessionID;

sub new {
    my ($pkg, $dbh) = @_;
    my $statement_id = "select seq_session.nextval from dual";
    my $sth_id = $dbh->prepare($statement_id) || die "ERROR:$DBI::errstr";
    $sth_id->execute || die "ERROR:$DBI::errstr";
    my $session_id = $sth_id->fetchrow();
    $sth_id->finish;
    my $return = \$session_id;
    bless $return, 'sessionID';
    return ($return);
}

package convert;

sub email2id_role {
    my ($pkg, $dbh, $email) = @_;
    my $statement = "
select team_person_role.id_role
from team_person_role, person
where person.id_person = team_person_role.id_person
and person.email = \'$email\'
and team_person_role.roletype = 'Finishing'
and team_person_role.iscurrent = 1
";
#mdf
#    my $statement = "
#select team_person_role.id_role
#from team_person_role, person
#where person.id_person = team_person_role.id_person
#and person.email = \'$email\'
#and team_person_role.roletype = 'Finishing'
#";


    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    ###########################################################################
    # This is only gonna get the first finishing entry for the person back >!<
    # ie. Problems if a finisher works for multiple teams
    ###########################################################################
    my $role_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$role_id;
    bless $return, 'convert';
    return ($return);
}

sub email2id_rolepfin {
    my ($pkg, $dbh, $email) = @_;
    my $statement = "
select team_person_role.id_role
from team_person_role, person
where person.id_person = team_person_role.id_person
and person.email = \'$email\'
and team_person_role.roletype = 'Prefinishing'
and team_person_role.iscurrent = 1
";

    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    ###########################################################################
    # This is only gonna get the first finishing entry for the person back >!<
    # ie. Problems if a finisher works for multiple teams
    ###########################################################################
    my $role_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$role_id;
    bless $return, 'convert';
    return ($return);
}

sub email2id_rolecheck {
    my ($pkg, $dbh, $email) = @_;
    my $statement = "
select team_person_role.id_role
from team_person_role, person
where person.id_person = team_person_role.id_person
and person.email = \'$email\'
and team_person_role.roletype = 'Checking'
and team_person_role.iscurrent = 1
";

    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    ###########################################################################
    # This is only gonna get the first finishing entry for the person back >!<
    # ie. Problems if a finisher works for multiple teams
    ###########################################################################
    my $role_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$role_id;
    bless $return, 'convert';
    return ($return);
}


sub confirmation2id_dict {
    my ($pkg, $dbh, $description) = @_;
    my $statement = "
select id_dict
from confirmationdict
where description = \'$description\'
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $confirmation_var_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$confirmation_var_id;
    bless $return, 'convert';
    return ($return);
}

sub id_dict2confirmation {
    my ($pkg, $dbh, $id) = @_;
    my $statement = "
select description
from confirmationdict
where id_dict = \'$id\'
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $confirmation_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$confirmation_id;
    bless $return, 'convert';
    return ($return);
}


sub variation_check2id_dict {
    my ($pkg, $dbh, $description) = @_;
    my $statement = "
select id_dict
from variationdict
where description = \'$description\'
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $variation_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$variation_id;
    bless $return, 'convert';
    return ($return);
}

sub id_dict2variation {
    my ($pkg, $dbh, $id) = @_;
    my $statement = "
select description
from variationdict
where id_dict = \'$id\'
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $variation_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$variation_id;
    bless $return, 'convert';
    return ($return);
}


sub id_dict2chromosome {
    my ($pkg, $dbh, $id_var) = @_;
    my $statement = "
select chromosomedict.chromosome 
from chromosomedict
where chromosomedict.id_dict = \'$id_var\'
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $chromosome_var_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$chromosome_var_id;
    bless $return, 'convert';
    return ($return);
}

sub id_dict2clonetype {
    my ($pkg, $dbh, $id_var) = @_;
    my $statement = "
select clonetypedict.description 
from clonetypedict
where clonetypedict.id_dict = \'$id_var\'
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $clonetype_var_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$clonetype_var_id;
    bless $return, 'convert';
    return ($return);

}

sub chromosome2id_dict {
    my ($pkg, $dbh, $chromosome_var, $species_name_var) = @_;
    my $statement = "
select chromosomedict.id_dict 
from chromosomedict
where chromosomedict.chromosome = \'$chromosome_var\'
and speciesname = \'$species_name_var\'
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $chromosome_var_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$chromosome_var_id;
    bless $return, 'convert';
    return ($return);
}

sub type2id_dict {
    my ($pkg, $dbh, $type_var) = @_;
    my $statement = "
select id_dict 
from repositorytypedict
where description = \'$type_var\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $type_var_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$type_var_id;
    bless $return, 'convert';
    return ($return); 
}

sub teamor2id_dict {
    my ($pkg, $dbh, $teamor_var) = @_;
    my $statement = "
select id_dict 
from projectownerdict
where description = \'$teamor_var\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $teamor_var_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$teamor_var_id;
    bless $return, 'convert';
    return ($return); 
}

sub id_dict2type {
    my ($pkg, $dbh, $type_var) = @_;
    my $statement = "
select description
from repositorytypedict
where id_dict = \'$type_var\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $type_var_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$type_var_id;
    bless $return, 'convert';
    return ($return); 
}

sub robotname2id_dict {
    my ($pkg, $dbh, $robot_var) = @_;
    my $statement = "
select id_robot 
from robot
where robotname = \'$robot_var\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $robot_var_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$robot_var_id;
    bless $return, 'convert';
    return ($return);
}

sub project_status2id_dict {
    my ($pkg, $dbh, $status_var) = @_;
    my $statement = "
select id_dict 
from projectstatusdict
where description = \'$status_var\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $status_var_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$status_var_id;
    bless $return, 'convert';
    return ($return);
}

sub clone_status2id_dict {
    my ($pkg, $dbh, $status_var) = @_;
    my $statement = "
select id_dict 
from clonestatusdict
where description = \'$status_var\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $status_var_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$status_var_id;
    bless $return, 'convert';
    return ($return);
}

sub clone_type2id_dict {
    my ($pkg, $dbh, $type_var) = @_;
    my $statement = "
select id_dict 
from clonetypedict
where description = \'$type_var\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $type_var_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$type_var_id;
    bless $return, 'convert';
    return ($return);
}

sub priority2id_dict {
    my ($pkg, $dbh, $pri_var) = @_;
    my $statement = "
select id_dict 
from cloneprioritydict
where description = \'$pri_var\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $pri_var_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$pri_var_id;
    bless $return, 'convert';
    return ($return);
}

sub seq_reason2id_dict {
    my ($pkg, $dbh, $seqreason_var) = @_;
    my $statement = "
select id_dict
from seqreasondict
where description = \'$seqreason_var\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $seqreason_var_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$seqreason_var_id;
    bless $return, 'convert';
    return ($return);
}

sub clone2contig {
    my ($pkg, $dbh, $clonename) = @_;
    my $statement = "
select contigname
from clone_contig
where clonename = \'$clonename\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";

    my @contigs;
    while ($contigname = $sth->fetchrow()){
	push @contigs, $contigname;
    }
    $sth->finish;
    my $return = \@contigs;
    bless $return, 'convert';
    return ($return);
}


sub org2id_dict {
    my ($pkg, $dbh, $org) = @_;
    my $statement = "
select id_org 
from organisation
where orgtitle = \'$org\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $org_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$org_id;
    bless $return, 'convert';
    return ($return);
}

sub id_dict2org {
    my ($pkg, $dbh, $org) = @_;
    my $statement = "
select orgtitle 
from organisation
where id_org = \'$org\'
";

#    warn "PROBLEM >|< \n $statement \n";

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $org_id = $sth->fetchrow();
    $sth->finish;
    my $return = \$org_id;
    bless $return, 'convert';
    return ($return);
}

package collect;

sub teamleaders {
    my ($pkg, $dbh) = @_;
    my %leaders = ();
    my $query = qq{select a.teamname, b.forename, b.surname from team a, person b where a.teamleader = b.id_person};
    my $queryh1 = $dbh->prepare($query) || die "ERROR:$DBI::errstr";
    $queryh1->execute || die "ERROR:$DBI::errstr";
    my @temp;
    while ( @temp = $queryh1->fetchrow() ) {
	# Team Forename Surname
	$leaders{$temp[0]} = $temp[2] . " " . substr($temp[1],0,1) . ".";
    }
    $queryh1->finish;
    return (\%leaders);
}

sub chromosome_and_species_from_clonename {
    my ($pkg, $dbh, $CLONE) = @_;
    my $statement = "
select CHROMOSOME, SPECIESNAME
from clone
where clonename = \'$CLONE\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($CHROMOSOME, $SPECIESNAME) = $sth->fetchrow();
    $sth->finish();
    my @array;
    push @array, $CHROMOSOME;
    push @array, $SPECIESNAME;
    my $return = \@array; 
    bless ($return), 'collect';
    return ($return);
}


sub clonenames_from_projectname {
    my ($pkg, $dbh, $CLONE_PROJECT, $query_data, $projectname) = @_;
    my $statement = "
select CLONENAME 
from clone_project
where projectname = \'$projectname\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $clonename;
    my $check = "no hits";
    while ( $clonename = $sth->fetchrow() ){
	push @$query_data, "";
	push @$query_data, "Clone name $clonename";
	push @$CLONE_PROJECT, $clonename;
	$check = "hits";
    }
    if ($check eq "no hits"){
	push @$query_data, "";
	push @$query_data, "Clone name None";
    }
    $sth->finish();
    bless ($query_data, $CLONE_PROJECT), 'collect';
    return ($query_data, $CLONE_PROJECT);
}



sub team_from_projectname {
    my ($pkg, $dbh, $PROJECT_OWNER, $query_data, $projectname) = @_;
    my $statement = "
select project_owner.teamname, projectownerdict.description
from project_owner, projectownerdict
where project_owner.OWNER_TYPE = projectownerdict.ID_DICT
and project_owner.projectname = \'$projectname\'
order by project_owner.owned_from desc
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $team;
    my $reason;
    my $check = "no hits";
    while ( ($team, $reason) = $sth->fetchrow() ){
	push @$query_data, "";
	push @$query_data, "Current team $team";
	push @$query_data, "Reason for ownership $reason";
	my %data;
	$data{team} = $team;
	$data{reason} = $reason;
	push @$PROJECT_OWNER, \%data;
	$check = "hits";

	# We only eant the LATEST entry
	last;
    }
    if ($check eq "no hits"){
	push @$query_data, "";
	push @$query_data, "Current team None";
    }
    $sth->finish();
    bless ($query_data, $PROJECT_OWNER), 'collect';
    return ($query_data, $PROJECT_OWNER);
}


sub finisher_from_projectname {
    my ($pkg, $dbh, $PROJECT_ROLE, $query_data, $projectname) = @_;
#    my $statement = "
#select person.email
#from project_role, team_person_role, person
#where person.id_person = team_person_role.id_person
#and project_role.id_role = team_person_role.id_role
#and team_person_role.roletype = 'Finishing'
#and team_person_role.iscurrent = 1
#and project_role.projectname = \'$projectname\'
#order by project_role.assigned_from desc
#";
#mdf - removed requirement for iscurrent - now matched EPS
    my $statement = "
select person.email
from project_role, team_person_role, person
where person.id_person = team_person_role.id_person
and project_role.id_role = team_person_role.id_role
and team_person_role.roletype = 'Finishing'
and project_role.projectname = \'$projectname\'
order by project_role.assigned_from desc
";

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $finisher;
    my $check = "no hits";
#    while ( $finisher = $sth->fetchrow() ){
    $finisher = $sth->fetchrow();
    if ($finisher ne ''){
	push @$query_data, "";
	push @$query_data, "Finisher $finisher";
	push @$PROJECT_ROLE, $finisher;
    } else {
	push @$query_data, "";
	push @$query_data, "Finisher None";
    }
    $sth->finish();
    bless ($query_data, $PROJECT_ROLE), 'collect';
    return ($query_data, $PROJECT_ROLE);
}

sub prefinisher_from_projectname {
    my ($pkg, $dbh, $PROJECT_ROLE2, $query_data, $projectname) = @_;
    my $statement = "
select person.email
from project_role, team_person_role, person
where person.id_person = team_person_role.id_person
and project_role.id_role = team_person_role.id_role
and team_person_role.roletype = 'Prefinishing'
and team_person_role.iscurrent = 1
and project_role.projectname = \'$projectname\'
order by project_role.assigned_from desc
";

#    warn "IFFY !!!! \n $statement \n";

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $pfinisher;
    my $check = "no hits";
#    while ( $pfinisher = $sth->fetchrow() ){
    $pfinisher = $sth->fetchrow();
    if ($pfinisher ne ''){
	push @$query_data, "";
	push @$query_data, "Prefinisher $pfinisher";
	push @$PROJECT_ROLE2, $pfinisher;
    } else {
	push @$query_data, "";
	push @$query_data, "Prefinisher None";
    }
    $sth->finish();
    bless ($query_data, $PROJECT_ROLE2), 'collect';
    return ($query_data, $PROJECT_ROLE2);
}

sub checker_from_projectname {
    my ($pkg, $dbh, $PROJECT_ROLE3, $query_data, $projectname) = @_;
    my $statement = "
select person.email
from project_role, team_person_role, person
where person.id_person = team_person_role.id_person
and project_role.id_role = team_person_role.id_role
and team_person_role.roletype = 'Checking'
and team_person_role.iscurrent = 1
and project_role.projectname = \'$projectname\'
order by project_role.assigned_from desc
";

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $checker;
    my $check = "no hits";
    $checker = $sth->fetchrow();
    if ($checker ne ''){
	push @$query_data, "";
	push @$query_data, "Checker $checker";
	push @$PROJECT_ROLE3, $checker;
    } else {
	push @$query_data, "";
	push @$query_data, "Checker None";
    }
    $sth->finish();
    bless ($query_data, $PROJECT_ROLE3), 'collect';
    return ($query_data, $PROJECT_ROLE3);
}

#####
#####

sub robot_from_projectname {
    my ($pkg, $dbh, $ROBOT_PROJECT, $query_data, $projectname) = @_;
    my $statement = "
select robot.robotname 
from robot, robot_project
where robot_project.id_robot = robot.id_robot
and robot_project.projectname = \'$projectname\'
and robot_project.date_unloaded is NULL
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $robot;
    my $check = "no hits";
    while ( $robot = $sth->fetchrow() ){
	push @$query_data, "";
	push @$query_data, "On robot $robot";
	push @$ROBOT_PROJECT, $robot;
	$check = "hits";
    }
    if ($check eq "no hits"){
	push @$query_data, "";
	push @$query_data, "On robot None";
    }
    $sth->finish();
    bless ($query_data, $ROBOT_PROJECT), 'collect';
    return ($query_data, $ROBOT_PROJECT);
}

sub towards_from_projectname {
my ($pkg, $dbh, $FINISHING_DIRECTION, $query_data, $projectname) = @_;
    my $statement = "
select finish_towards
from finishing_direction
where projectname = \'$projectname\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $towards;
    my $check = "no hits";
    while ( $towards = $sth->fetchrow() ){
	push @$query_data, "";
	push @$query_data, "Finish towards project $towards";
	push @$FINISHING_DIRECTION, $towards;
	$check = "hits";
    }
    if ($check eq "no hits"){
	push @$query_data, "";
	push @$query_data, "Finish towards project None";
    }
    $sth->finish();
    bless ($query_data, $FINISHING_DIRECTION), 'collect';
    return ($query_data, $FINISHING_DIRECTION);

}

sub submission_from_projectname {
    my ($pkg, $dbh, $SUBMISSION, $query_data, $projectname) = @_;
    ################################################################
    # Need to this in 2-rounds; 1 for Finished and 1 for Unfinished
    ################################################################
    my $check = "no hits";
    my ($name, $suffix, $acc, $len, $type);
    my $statement;
    my $sth;
    my %seen;

    #############
    # Finished
    #############
    $statement = "
select finished_submission.accession, finished_submission.suffix, embl_submission.name, embl_submission.length_bp
from finished_submission, embl_submission
where finished_submission.accession = embl_submission.accession
and finished_submission.projectname = \'$projectname\'
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    while ( ($acc, $suffix, $name, $len) = $sth->fetchrow() ){
	push @$query_data, "";
	push @$query_data, "Type Finished";
	push @$query_data, "EMBL name $name";
	push @$query_data, "EMBL accession $acc";
	push @$query_data, "Finished length (bp) $len";
	push @$query_data, "Internal suffix $suffix";

	$seen{$acc}++;

	my %data;
	$data{name} = $name;
	$data{acc} = $acc;
	$data{len} = $len;
	$data{type} = 'Finished';
	$data{suffix} = $suffix;
	push @$SUBMISSION, \%data;
	$check = "hits";
    }

    #############
    # Unfinished
    #############
    $statement = "
select unfinished_submission.accession, embl_submission.name, embl_submission.length_bp
from unfinished_submission, embl_submission
where unfinished_submission.accession = embl_submission.accession
and unfinished_submission.projectname = \'$projectname\'
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    while ( ($acc, $name, $len) = $sth->fetchrow() ){

	# If we have seen this accession before under Finished
	# Don't bother displaying again
	unless ($seen{$acc} > 0){
	    push @$query_data, "";
	    push @$query_data, "Type Unfinished";
	    push @$query_data, "EMBL name $name";
	    push @$query_data, "EMBL accession $acc";
	    push @$query_data, "Finished length (bp) $len";

	    my %data;
	    $data{name} = $name;
	    $data{acc} = $acc;
	    $data{len} = $len;
	    $data{type} = 'Unfinished';
	    $data{suffix} = '';
	    push @$SUBMISSION, \%data;
	    $check = "hits";
	}
    }

    if ($check eq "no hits"){
	push @$query_data, "";
	push @$query_data, "No submission information available";
    }
    $sth->finish();
    bless ($query_data, $SUBMISSION), 'collect';
    return ($query_data, $SUBMISSION);
}
####################
####################

sub summary_from_projectname {
    my ($pkg, $dbh, $SUMMARY, $query_data, $projectname) = @_;
    my $statement = "
select ID_SUMMARY, PROJECTNAME, to_char(project_summary.SUMMARYDATE, 'DD/MM/YYYY HH24-MI-SS'), NUM_CONTIGS, TOTAL_CTG_LEN, NUM_LGE_CONTIGS, LGE_CTG_LEN, NUM_READS, FINISHED_LENGTH_BP, COVERAGE
from project_summary
where projectname = \'$projectname\'
order by SUMMARYDATE
";

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($ID_SUMMARY, $PROJECTNAME, $SUMMARYDATE, $NUM_CONTIGS, $TOTAL_CTG_LEN, $NUM_LGE_CONTIGS, $LGE_CTG_LEN, $NUM_READS, $FINISHED_LENGTH_BP, $COVERAGE);
    my $check = "no hits";
    while ( ($ID_SUMMARY, $PROJECTNAME, $SUMMARYDATE, $NUM_CONTIGS, $TOTAL_CTG_LEN, $NUM_LGE_CONTIGS, $LGE_CTG_LEN, $NUM_READS, $FINISHED_LENGTH_BP, $COVERAGE) = $sth->fetchrow() ){

	push @$query_data, "";
	push @$query_data, "Date                     $SUMMARYDATE";
	push @$query_data, "No. of contigs           $NUM_CONTIGS";
	push @$query_data, "Total contig length (bp) $TOTAL_CTG_LEN";
	push @$query_data, "No. of contigs > 1000bp  $NUM_LGE_CONTIGS";
	push @$query_data, "Large contig total (bp)  $LGE_CTG_LEN";
	push @$query_data, "No. of reads             $NUM_READS";
	push @$query_data, "Coverage                 $COVERAGE";
	push @$query_data, "Finished length (bp)     $FINISHED_LENGTH_BP";

	my %data;

	$data{ID_SUMMARY} = $ID_SUMMARY;
	$data{SUMMARYDATE} = $SUMMARYDATE;
	$data{NUM_CONTIGS} = $NUM_CONTIGS;
	$data{TOTAL_CTG_LEN} = $TOTAL_CTG_LEN;
	$data{NUM_LGE_CONTIGS}  = $NUM_LGE_CONTIGS;
	$data{LGE_CTG_LEN} = $LGE_CTG_LEN;
	$data{NUM_READS} = $NUM_READS;
	$data{COVERAGE} = $COVERAGE;
	$data{FINISHED_LENGTH_BP} =  $FINISHED_LENGTH_BP;

	push @$SUMMARY, \%data;
	$check = "hits";
    }
    if ($check eq "no hits"){
	push @$query_data, "";
	push @$query_data, "No project summary information available";
    }
    $sth->finish();
    bless ($query_data, $SUMMARY), 'collect';
    return ($query_data, $SUMMARY);
}

sub overlaps_from_clonename {
    my ($pkg, $dbh, $OVERLAPS, $query_data, $clone) = @_;
    my $statement = "
select CLONENAME, OVERLAPS_CLONE, CONFIRMATION, SIZE_BP, VARIATION_CHECK, REMARK
from clone_overlaps
where (CLONENAME = \'$clone\') or (OVERLAPS_CLONE = \'$clone\')
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($CLONENAME, $OVERLAPS_CLONE, $CONFIRMATION, $SIZE_BP, $VARIATION_CHECK, $REMARK);
    my $check = "no hits";
    while ( ($CLONENAME, $OVERLAPS_CLONE, $CONFIRMATION, $SIZE_BP, $VARIATION_CHECK, $REMARK) = $sth->fetchrow() ){
	############################
	# Some conversion is needed
	############################
	if ($CONFIRMATION eq ''){
	    $CONFIRMATION = 'Not checked' 
	    } else {
		# convert the id to a description
		$CONFIRMATION = convert->id_dict2confirmation($dbh, $CONFIRMATION);
		$CONFIRMATION = $$CONFIRMATION;
	    }
	
	if ($VARIATION_CHECK eq ''){
	    $VARIATION_CHECK = 'Not checked'
	    } else {
		# convert the id to a description
		$VARIATION_CHECK = convert->id_dict2variation($dbh, $VARIATION_CHECK);
		$VARIATION_CHECK = $$VARIATION_CHECK;
	    }
	push @$query_data, "";
	push @$query_data, "Clonename       $CLONENAME";
	push @$query_data, "Overlaps clone  $OVERLAPS_CLONE";
	push @$query_data, "Confirmation    $CONFIRMATION";
	push @$query_data, "Size (kb)       $SIZE_BP";
	push @$query_data, "Variation check $VARIATION_CHECK";
	push @$query_data, "Variation check $REMARK";

	my %data;

	$data{CLONENAME} = $CLONENAME;
	$data{OVERLAPS_CLONE} = $OVERLAPS_CLONE;
	$data{CONFIRMATION} = $CONFIRMATION;
	$data{SIZE_BP} = $SIZE_BP;
	$data{VARIATION_CHECK} = $VARIATION_CHECK;
	$data{OLR} = $REMARK;

	push @$OVERLAPS, \%data;
	$check = "hits";
    }
    if ($check eq "no hits"){
	push @$query_data, "";
	push @$query_data, "No clone overlap information available";
    }
    $sth->finish();
    bless ($query_data, $OVERLAPS), 'collect';
    return ($query_data, $OVERLAPS);
}

#-----------------------------------------------------------------------------
#-----------------------------------------------------------------------------

sub externalnames_from_clonename {
    my ($pkg, $dbh, $external, $query_data, $r_clonename) = @_;
    my $statement = "
select EXTERNALNAME 
from external_name
where clonename = \'$r_clonename\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $r_externalname;
    my $check = "no hits";
    while ( $r_externalname = $sth->fetchrow() ){
	push @$query_data, "";
	push @$query_data, "External name $r_externalname";
	push @$external, $r_externalname;
	$check = "hits";
    }
    if ($check eq "no hits"){
	push @$query_data, "";
	push @$query_data, "External name None";
    }
    $sth->finish();
    bless ($query_data, $external), 'collect';
    return ($query_data, $external);
}

sub fingerprint_comment_from_clonename {
    my ($pkg, $dbh, $FINGERPRINT, $query_data, $r_clonename) = @_;
    my $statement = "
select priority 
from fp_priority
where clonename = \'$r_clonename\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $fp;
    my $check = "no hits";
    while ( $fp = $sth->fetchrow() ){
	push @$query_data, "";
	push @$query_data, "Fingerprinting comment $fp";
	push @$FINGERPRINT, $fp;
	$check = "hits";
    }
    if ($check eq "no hits"){
	push @$query_data, "";
	push @$query_data, "Fingerprinting comment None";
    }
    $sth->finish();
    bless ($query_data, $FINGERPRINT), 'collect';
    return ($query_data, $FINGERPRINT);

}

sub current_priority_from_clonename {
    my ($pkg, $dbh, $PRIORITY, $query_data, $r_clonename) = @_;
    my $statement = "
select cloneprioritydict.description, clone_priority.remark
from clone_priority, cloneprioritydict
where clone_priority.priority = cloneprioritydict.id_dict
and clonename = \'$r_clonename\'
and is_current = 1
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($p, $r);
    my $check = "no hits";
    while ( ($p, $r)  = $sth->fetchrow() ){
	push @$query_data, "";
	push @$query_data, "Current priority $p";
	push @$query_data, "Priority remark $r";

	my %data;
	$data{priority} = $p;
	$data{remark} = $r;
	push @$PRIORITY, \%data;
	$check = "hits";

    }
    if ($check eq "no hits"){
	push @$query_data, "";
	push @$query_data, "Current priority None";
	push @$query_data, "Priority remark None";
    }
    $sth->finish();
    bless ($query_data, $PRIORITY), 'collect';
    return ($query_data, $PRIORITY);
}

sub projectnames_from_clonename {
    my ($pkg, $dbh, $projectname, $query_data, $r_clonename) = @_;
    my $statement = "
select PROJECTNAME 
from clone_project
where clonename = \'$r_clonename\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $r_projectname;
    my $check = "no hits";
    while ( $r_projectname = $sth->fetchrow() ){
	push @$query_data, "";
	push @$query_data, "Project name $r_projectname";
	push @$projectname, $r_projectname;
	$check = "hits";
    }
    if ($check eq "no hits"){
	push @$query_data, "";
	push @$query_data, "Project name None";
    }
    $sth->finish();
    bless ($query_data, $projectname), 'collect';
    return ($query_data, $projectname);
}


sub colours_for_project_status {
    my ($pkg, $dbh) = @_;
    my $statement = "
select program_config.CFG_VALUE, program_config.CFG_PARAM, projectstatusdict.ASSIGN_ORDER
from program_config, projectstatusdict
where program_config.CFG_PARAM = projectstatusdict.DESCRIPTION
and program_config.CFG_SET = 2
order by projectstatusdict.ASSIGN_ORDER, projectstatusdict.ID_DICT
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($CFG_VALUE, $CFG_PARAM, $ORDER);
    my %hash;
    my $no_assign;
    while ( ($CFG_VALUE, $CFG_PARAM, $ORDER)  = $sth->fetchrow() ){

	if ($ORDER eq ''){
	    $no_assign++;
	    $ORDER = $no_assign;
	} else {
	    $no_assign = $ORDER;
	}

	my %hash2;
	$hash2{$CFG_PARAM} =  $CFG_VALUE;
	$hash{$ORDER} = \%hash2;
    }
    $sth->finish();
    my $return = \%hash;
    bless ($return), 'collect';
    return ($return);
}

sub colours_for_clone_status {
    my ($pkg, $dbh) = @_;
    my $statement = "
select program_config.CFG_VALUE, program_config.CFG_PARAM, clonestatusdict.ASSIGN_ORDER
from program_config, clonestatusdict
where program_config.CFG_PARAM = clonestatusdict.DESCRIPTION
and program_config.CFG_SET = 1
order by clonestatusdict.ASSIGN_ORDER, clonestatusdict.ID_DICT
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($CFG_VALUE, $CFG_PARAM, $ORDER);
    my %hash;
    my $no_assign;
    while ( ($CFG_VALUE, $CFG_PARAM, $ORDER)  = $sth->fetchrow() ){

	
	if ($ORDER eq ''){
	    $no_assign++;
	    $ORDER = $no_assign;
	} else {
	    $no_assign = $ORDER;
	}


	my %hash2;
	$hash2{$CFG_PARAM} =  $CFG_VALUE;
	$hash{$ORDER} = \%hash2;
    }
    $sth->finish();
    my $return = \%hash;
    bless ($return), 'collect';
    return ($return);
}

sub current_status_from_clonename {
    my ($pkg, $dbh, $clone) = @_;
    my $statement = "
select clonestatusdict.description
from clonestatusdict, clone_status
where clone_status.status = clonestatusdict.id_dict
and clone_status.clonename = \'$clone\'
and clone_status.iscurrent = 1
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($DESCRIPTION);
    ($DESCRIPTION)  = $sth->fetchrow();
    $sth->finish();
    my $return = \$DESCRIPTION;
    bless ($return), 'collect';
    return ($return);
}

sub current_status_from_projectname {
    my ($pkg, $dbh, $project) = @_;
    my $statement = "
select projectstatusdict.description, project_status.remark
from projectstatusdict, project_status
where project_status.status = projectstatusdict.id_dict
and project_status.projectname = \'$project\'
and project_status.iscurrent = 1
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($DESCRIPTION, $REMARK);
    ($DESCRIPTION, $REMARK)  = $sth->fetchrow();
    $sth->finish();
    my @array;
    push @array, $DESCRIPTION;
    push @array, $REMARK;
    my $return = \@array;
    bless ($return), 'collect';
    return ($return);
}

package check;

sub project_is_on_hold {
    my ($pkg, $dbh, $list) = @_;
    my @return;
    my %responsible;
    my $statement = "
select a.projectname, a.operator
from project_status a
where
a.projectname = ?
and a.status = 26
and a.iscurrent = 1
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    my ($a);
    foreach my $next (@$list){
	$a = $next;
	$sth1->bind_param(1, $a);
	$sth1->execute || die "ERROR:$DBI::errstr";
	my ($got, $op) = $sth1->fetchrow();
	if ($got){
	    $responsible{$a} = $op;
	} else {
	    push @return, $a;
	}
    }
    $sth1->finish();   
    return (\@return, \%responsible); 
}




sub a_list_of_projectnames {
    my ($pkg, $dbh, $list) = @_;
    my @return;
    my $statement = "
select projectname
from project
where
projectname = ?
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    my ($a);
    foreach my $next (@$list){
	   $a = $next;
	   $sth1->bind_param(1, $a);
	   $sth1->execute || die "ERROR:$DBI::errstr";
	   my $got = $sth1->fetchrow();
	   if ($got){
	       push @return, $a;
	   }
    }
    $sth1->finish();   
    return (\@return);
}



sub a_list_of_clonenames {
    my ($pkg, $dbh, $list) = @_;
    my @return;
    my $statement = "
select clonename
from clone
where
clonename = ?
";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    my ($a);
    foreach my $next (@$list){
	   $a = $next;
	   $sth1->bind_param(1, $a);
	   $sth1->execute || die "ERROR:$DBI::errstr";
	   my $got = $sth1->fetchrow();
	   if ($got){
	       push @return, $a;
	   }
    }
    $sth1->finish();   
    return (\@return);
}



sub what_has_changed_in_embl_submission {
    my ($pkg, $dbh, $acc, $length, $id) = @_;
     my $statement = "
select length_bp, name
from embl_submission
where accession = \'$acc\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($length_check, $name_check) = $sth->fetchrow();
    $sth->finish;

    if ($length eq 'NULL'){ $length = '' };

    #differences ?
    if ($length_check ne $length){
	$length_check = 'changed';
    } 

    if ($name_check ne $id){
	$name_check= 'changed';
    }

    return (\$length_check, \$name_check);

}

sub finished_submission {
    my ($pkg, $dbh, $project, $acc) = @_;
    my $statement = "
select accession  
from finished_submission
where accession = \'$acc\'
and projectname = \'$project\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $check = $sth->fetchrow();
    $sth->finish;
    my $r;
    if ($check eq ''){
	$r = "Not in database";
    } else {
	$r = "In database"
    }
    my $return = \$r;
    bless $return, 'check';
    return ($return);
}

sub unfinished_submission {
    my ($pkg, $dbh, $project) = @_;
    my $statement = "
select accession  
from unfinished_submission
where projectname = \'$project\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $check = $sth->fetchrow();
    $sth->finish;
    my $r;
    if ($check eq ''){
	$r = "Not in database";
    } else {
	$r = "In database"
    }
    my $return = \$r;
    bless $return, 'check';
    return ($return);
}

sub accession {
    my ($pkg, $dbh, $acc) = @_;
    my $statement = "
select accession  
from embl_submission
where accession = \'$acc\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $check = $sth->fetchrow();
    $sth->finish;
    my $r;
    if ($check eq ''){
	$r = "Not in database";
    } else {
	$r = "In database"
    }
    my $return = \$r;
    bless $return, 'check';
    return ($return);
}

sub projectname {
    my ($pkg, $dbh, $projectname) = @_;
  
    my $statement = "
select projectname 
from project
where projectname = \'$projectname\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $project = $sth->fetchrow();
    $sth->finish;
    my $r;
    if ($project eq ''){
	$r = "Not in database";
    } else {
	$r = "In database"
    }
    my $return = \$r;
    bless $return, 'check';
    return ($return);
}


sub project_submission_exists {
    my ($pkg, $dbh, $projectname, $embl_acc, $embl_type) = @_;
  
    my $statement;
    if ($embl_type eq 'Finished'){
	$statement = "
select projectname
from finished_submission
where projectname = \'$projectname\'
and accession = \'$embl_acc\'
";
    } else {
$statement = "
select projectname
from unfinished_submission
where projectname = \'$projectname\'
and accession = \'$embl_acc\'
";
    }

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $project = $sth->fetchrow();
    $sth->finish;
    my $r;
    if ($project eq ''){
	$r = "Not in database";
    } else {
	$r = "In database"
    }
    my $return = \$r;
    bless $return, 'check';
    return ($return);
}



sub speciesname {
    my ($pkg, $dbh, $speciesname) = @_;
    my $statement = "
select speciesname
from
species
where speciesname = \'$speciesname\'
";

#    warn "\n$statement\n";

     my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
     $sth->execute || die "ERROR:$DBI::errstr";
     my $species_return = $sth->fetchrow();
     $sth->finish();
     if ($species_return){
	 return 1;
     } else {
	 return 0;
     }
}



sub clonename {
    my ($pkg, $dbh, $clonename) = @_;
  

    my $statement = "
select clonename 
from clone
where clonename = \'$clonename\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $clone = $sth->fetchrow();
    $sth->finish;
    my $r;
    if ($clone eq ''){
	$r = "Not in database";
    } else {
	$r = "In database"
    }
    my $return = \$r;
    bless $return, 'check';
    return ($return);
}

sub project_on_robot {
    my ($pkg, $dbh, $robot, $project) = @_;

    my $statement = "
select projectname
from robot_project
where id_robot = \'$robot\' 
and projectname = \'$project\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $proj = $sth->fetchrow();
    $sth->finish;
    my $r;
    if ($proj eq ''){
	$r = "Not in database";
    } else {
	$r = "In database"
    }
    my $return = \$r;
    bless $return, 'check';
    return ($return);
}


package owner;

sub get_owner_history {
    my ($pkg, $dbh, $name) = @_;
    my $statement = "
select TEAMNAME, to_char(OWNED_FROM, 'DD/MM/YYYY HH24-MI-SS'), to_char(OWNED_TO, 'DD/MM/YYYY HH24-MI-SS'), DESCRIPTION, decode(ISCURRENT,0,'', 'Yes')
from project_owner, projectownerdict
where project_owner.OWNER_TYPE = projectownerdict.ID_DICT
and projectname = \'$name\'
order by OWNED_FROM desc
";

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($TEAMNAME, $OWNED_FROM, $OWNED_TO, $DESCRIPTION, $ISCURRENT);
    my @owner;

    push @owner, "Team    Owned from            Owned to                For reason    Current";
    push @owner, "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~";

    while ( ($TEAMNAME, $OWNED_FROM, $OWNED_TO, $DESCRIPTION, $ISCURRENT) = $sth->fetchrow() ){
	if ($OWNED_TO eq ''){
	    $OWNED_TO = "-------------------";
	}

	my $d_len = length ($DESCRIPTION);
	# should be 12
	if ($d_len != 12){
	    my $c_len = (12 - $d_len);
	    for ($i =0; $i < $c_len; $i++){
 		$DESCRIPTION = ($DESCRIPTION . " ");
	    }
	}

	
	push @owner, "$TEAMNAME      $OWNED_FROM   $OWNED_TO     $DESCRIPTION   $ISCURRENT";
    }
    $sth->finish;
    my $return = \@owner;
    bless $return, 'owner';
    return ($return);
}







sub get_priority_history {
    my ($pkg, $dbh, $name) = @_;

    my $statement = "
select clone_priority.operator, to_char(clone_priority.ASSIGN_DATE, 'DD/MM/YYYY HH24-MI-SS'), cloneprioritydict.description, clone_priority.REMARK 
from clone_priority, cloneprioritydict
where clone_priority.priority = cloneprioritydict.id_dict
and clone_priority.clonename = \'$name\'
order by clone_priority.ASSIGN_DATE desc
";

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($OP, $DATE, $PRIORITY, $REMARK);
    my @priority;

#    push @owner, "Team         Owned from               For reason";
#    push @owner, "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~";

    while ( ($OP, $DATE, $PRIORITY, $REMARK) = $sth->fetchrow() ){
	push @priority, "$OP   $DATE   $PRIORITY   $REMARK";
    }
    $sth->finish;
    my $return = \@priority;
    bless $return, 'owner';
    return ($return);
}



package status;

sub next_best_clone_status {
    my ($pkg, $name, $dbh) = @_;

    my $check = "
select clonestatusdict.description
from clonestatusdict, clone_status
where clone_status.status = clonestatusdict.id_dict
and clone_status.iscurrent = 0
and clone_status.clonename = \'$name\'
order by clone_status.statusdate desc
";

    my $sth = $dbh->prepare($check) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $desc;
    $desc = $sth->fetchrow();
    $sth->finish();
    my $return = \$desc;
    bless $return, 'status';
    return ($return);
}

sub next_best_project_status {
    my ($pkg, $name, $dbh) = @_;

    my $check = "
select projectstatusdict.description
from projectstatusdict, project_status
where project_status.status = projectstatusdict.id_dict
and project_status.iscurrent = 0
and project_status.projectname = \'$name\'
order by project_status.statusdate desc
";

    my $sth = $dbh->prepare($check) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $desc;
    $desc = $sth->fetchrow();
    $sth->finish();
    my $return = \$desc;
    bless $return, 'status';
    return ($return);
}


sub revert_clone_current {
    my ($pkg, $clonename, $dbh) = @_;
#    warn "($pkg, $dbh, $clonename) \n";

    # check there is more than 1 status before we do anything
    # losing all status entries makes clones 'un-see-able' !
    my $check = "
select count(clonename)
from clone_status
where clonename = \'$clonename\'
";
    my $sth = $dbh->prepare($check) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($num) = $sth->fetchrow(); 

    return if ($num == 1);

    # Get rid of the current_status
    my $statement = "
delete from clone_status
where CLONENAME = \'$clonename\'
and iscurrent = 1
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    $sth->finish();
    # Make the next last one current
    # What is the new last one >?<
    $statement = "
select CLONENAME, STATUS, to_char(clone_status.STATUSDATE, 'DD/MM/YYYY HH24-MI-SS'), SESSIONID, PROGRAM, OPERATOR
from clone_status
where CLONENAME = \'$clonename\'
order by clone_status.STATUSDATE desc
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($CLONENAME, $STATUS, $STATUSDATE, $SESSIONID, $PROGRAM, $OPERATOR);
    ($CLONENAME, $STATUS, $STATUSDATE, $SESSIONID, $PROGRAM, $OPERATOR) = $sth->fetchrow(); 
    $sth->finish();
#    warn "$CLONENAME, $STATUS, $STATUSDATE, $SESSIONID, $PROGRAM, $OPERATOR \n";

    if ($CLONENAME ne ''){
#	$statement = "
#update clone_status
#set iscurrent = 1 
#where CLONENAME = \'$CLONENAME\'
#and STATUS = \'$STATUS\'
#and to_char(clone_status.STATUSDATE, 'DD/MM/YYYY HH24-MI-SS') = \'$STATUSDATE\'
#and SESSIONID = \'$SESSIONID\'
#and PROGRAM = \'$PROGRAM\'
#and OPERATOR = \'$OPERATOR\'
#";

	$statement = "
update clone_status
set iscurrent = 1 
where CLONENAME = \'$CLONENAME\'
and STATUS = \'$STATUS\'
and to_char(clone_status.STATUSDATE, 'DD/MM/YYYY HH24-MI-SS') = \'$STATUSDATE\'
";

#	warn $statement;
	
	$sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	$sth->execute || die "ERROR:$DBI::errstr";
	$sth->finish();
    }
}


sub revert_project_current {
    my ($pkg, $projectname, $dbh) = @_;

    # check there is more than 1 status before we do anything
    # losing all status entries makes clones 'un-see-able' !
    my $check = "
select count(projectname)
from project_status
where projectname = \'$projectname\'
";
    my $sth = $dbh->prepare($check) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($num) = $sth->fetchrow(); 

    return if ($num == 1);

    # Get rid of the current_status
    my $statement = "
delete from project_status
where PROJECTNAME = \'$projectname\'
and iscurrent = 1
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    $sth->finish();
    # Make the next last one current
    # What is the new last one >?<
    $statement = "
select PROJECTNAME, STATUS, to_char(project_status.STATUSDATE, 'DD/MM/YYYY HH24-MI-SS'), SESSIONID, PROGRAM, OPERATOR
from project_status
where PROJECTNAME = \'$projectname\'
order by project_status.STATUSDATE desc
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($NAME, $STATUS, $STATUSDATE, $SESSIONID, $PROGRAM, $OPERATOR);
    ($NAME, $STATUS, $STATUSDATE, $SESSIONID, $PROGRAM, $OPERATOR) = $sth->fetchrow(); 
    $sth->finish();
#    warn "$NAME, $STATUS, $STATUSDATE, $SESSIONID, $PROGRAM, $OPERATOR \n";

    if ($NAME ne ''){
#	$statement = "
#update project_status
#set iscurrent = 1 
#where PROJECTNAME = \'$NAME\'
#and STATUS = \'$STATUS\'
#and to_char(project_status.STATUSDATE, 'DD/MM/YYYY HH24-MI-SS') = \'$STATUSDATE\'
#and SESSIONID = \'$SESSIONID\'
#and PROGRAM = \'$PROGRAM\'
#and OPERATOR = \'$OPERATOR\'
#";

	$statement = "
update project_status
set iscurrent = 1 
where PROJECTNAME = \'$NAME\'
and STATUS = \'$STATUS\'
and to_char(project_status.STATUSDATE, 'DD/MM/YYYY HH24-MI-SS') = \'$STATUSDATE\'
";

#	warn $statement;
	
	$sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
	$sth->execute || die "ERROR:$DBI::errstr";
	$sth->finish();
    }
}

################
# Clone history
################
sub get_history {
    my ($pkg, $dbh, $clonename) = @_;
    my $statement = "
select clonestatusdict.DESCRIPTION, to_char(clone_status.STATUSDATE, 'DD/MM/YYYY HH24-MI-SS'), clone_status.OPERATOR, program_config.CFG_VALUE, clone_status.ISCURRENT
from clone_status, clonestatusdict, program_config
where program_config.CFG_PARAM = clonestatusdict.DESCRIPTION
and clone_status.status = clonestatusdict.id_dict
and program_config.CFG_SET = 1
and program_config.PROGRAM = 'gull'
and clone_status.CLONENAME = \'$clonename\'
order by clone_status.STATUSDATE desc, clonestatusdict.assign_order desc
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($DESCRIPTION, $STATUSDATE, $OPERATOR, $COLOUR, $CURRENT);
    my @status;
    my $count = 1;
    while ( ($DESCRIPTION, $STATUSDATE, $OPERATOR, $COLOUR, $CURRENT) = $sth->fetchrow() ){
	if ($COLOUR eq ''){ $COLOUR = 'black'};
	my %hash;

	$hash{DESCRIPTION} = $DESCRIPTION;
	$hash{STATUSDATE} = $STATUSDATE;
	$hash{OPERATOR} = $OPERATOR;
	$hash{DESCRIPTION} = $DESCRIPTION;
	$hash{COLOUR} = $COLOUR;
	$hash{CURRENT} = $CURRENT;
	$status[$count] = \%hash;
	$count++;
    }
    $sth->finish;
    my $return = \@status;
    bless $return, 'status';
    return ($return);
}

sub get_project_history {
    my ($pkg, $dbh, $name) = @_;
    my $statement = "
select projectstatusdict.DESCRIPTION, to_char(project_status.STATUSDATE, 'DD/MM/YYYY HH24-MI-SS'), project_status.OPERATOR, program_config.CFG_VALUE, project_status.ISCURRENT, project_status.REMARK
from project_status, projectstatusdict, program_config
where program_config.CFG_PARAM = projectstatusdict.DESCRIPTION
and project_status.status = projectstatusdict.id_dict
and program_config.CFG_SET = 2
and program_config.PROGRAM = 'gull'
and project_status.PROJECTNAME = \'$name\'
order by project_status.STATUSDATE desc, projectstatusdict.assign_order desc
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($DESCRIPTION, $STATUSDATE, $OPERATOR, $COLOUR, $CURRENT, $REMARK);
    my @status;
    my $count = 1;
    while ( ($DESCRIPTION, $STATUSDATE, $OPERATOR, $COLOUR, $CURRENT, $REMARK) = $sth->fetchrow() ){
	if ($COLOUR eq ''){ $COLOUR = 'black'};
	my %hash;

	$hash{DESCRIPTION} = $DESCRIPTION;
	$hash{STATUSDATE} = $STATUSDATE;
	$hash{OPERATOR} = $OPERATOR;
	$hash{DESCRIPTION} = $DESCRIPTION;
	$hash{COLOUR} = $COLOUR;
	$hash{CURRENT} = $CURRENT;
	$hash{REMARK} = $REMARK;
	$status[$count] = \%hash;
	$count++;
    }
    $sth->finish;
    my $return = \@status;
    bless $return, 'status';
    return ($return);
}

package embl_submission;

sub insert_new {
    my ($pkg, $dbh, $acc, $name, $len) = @_;
    my $s;
    if ($len eq 'NULL'){
	$s = "insert into embl_submission (ACCESSION, NAME) values(\'$acc\', \'$name\')";
    } else {
	$s = "insert into embl_submission (ACCESSION, NAME, LENGTH_BP) values(\'$acc\', \'$name\', \'$len\')";
    }
    my $sth = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    $sth->finish;
}

sub insert_finished {
    my ($pkg, $dbh, $project, $acc, $suffix) = @_;
    my $s;
    if ($suffix eq ''){
	$s = "
insert into finished_submission
(PROJECTNAME, ACCESSION)
values(\'$project\', \'$acc\')";
} else {
$s = "
insert into finished_submission
(PROJECTNAME, ACCESSION, SUFFIX)
values(\'$project\', \'$acc\', \'$suffix\')";
}
    my $sth = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    $sth->finish;
}

sub insert_unfinished {
    my ($pkg, $dbh, $project, $acc) = @_;
    my $s = "
insert into unfinished_submission
(PROJECTNAME, ACCESSION)
values(\'$project\', \'$acc\')
";
    my $sth = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    $sth->finish;
}

sub change_length {
    my ($pkg, $dbh, $acc, $length) = @_;
    my $s;
    if ($length eq 'NULL'){
	$s = "
update embl_submission
set LENGTH_BP = NULL
where ACCESSION = \'$acc\'
";
    } else {
	$s = "
update embl_submission
set LENGTH_BP = \'$length\'
where ACCESSION = \'$acc\'
";
    }
    my $sth = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    $sth->finish;
}

sub change_name {
    my ($pkg, $dbh, $acc, $id) = @_;
    my $s = "
update embl_submission
set NAME = \'$id\'
where ACCESSION = \'$acc\'
";
    my $sth = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    $sth->finish;
}

###  Was only used by gull_acc.pm, which jgrg now says can be deleted.
#    (his code does still use the underlying tables)
#
#    nb. this package name couldn't be used as "lock->foo()" except under
#    Perl 5.004		-- mca, 2004-04-20
#
# package lock;
# 
# sub unfinished_submission {
#     my ($pkg, $dbh, $project, $acc) = @_;
#     my $s = "
# select accession, projectname
# from unfinished_submission
# where accession = \'$acc\'
# and projectname = \'$project\'
# for update nowait
# ";
#     my $sth = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
#     $sth->execute || die "ERROR:$DBI::errstr";
#     $sth->finish;  
# }
# 
# sub finished_submission {
#     my ($pkg, $dbh, $project, $acc) = @_;
#     my $s = "
# select accession, projectname, suffix
# from finished_submission
# where accession = \'$acc\'
# and projectname = \'$project\'
# for update nowait
# ";
#     my $sth = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
#     $sth->execute || die "ERROR:$DBI::errstr";
#     $sth->finish; 
# 
# }
# 
# sub embl_submission {
#     my ($pkg, $dbh, $acc) = @_;
#     my $s = "
# select ACCESSION, NAME, LENGTH_BP
# from embl_submission
# where accession = \'$acc\'
# for update nowait
# ";
#     my $sth = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
#     $sth->execute || die "ERROR:$DBI::errstr";
#     $sth->finish; 
# 
# }

package library;

sub get_info {
    my ($pkg, $dbh, $name) = @_;
    my @library;
    my $statement = "
select
library.ID_VECTORSITE,
library.INTERNAL_PREFIX,
library.EXTERNAL_PREFIX,
library.DESCRIPTION,
library.SPECIESNAME,
library.CHROMOSOME,
library.VECTORNAME,
library.FIRST_PLATE,
library.LAST_PLATE,
vectortypedict.DESCRIPTION,
vector.VECTORSIZE,
vectorsite.STADENFILE,
vectorsite.FASTAFILE,
vectorsite.FORWARDCLONINGSITE,
vectorsite.REVCLONINGSITE,
vectorsite.FORWARDPRIMERSITE,
vectorsite.REVPRIMERSITE
from library, vectorsite, vector, vectortypedict
where library.ID_VECTORSITE = vectorsite.ID_VECTORSITE
and vectorsite.VECTORNAME = vector.VECTORNAME
and vector.VECTORTYPE = vectortypedict.ID_DICT
and library.LIBRARYNAME = \'$name\'
";

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";

    my ($ID_VECTORSITE, $INTERNAL_PREFIX, $EXTERNAL_PREFIX, $DESCRIPTION, $SPECIESNAME, $CHROMOSOME, $VECTORNAME, $FIRST_PLATE, $LAST_PLATE, $DESCRIPTION2, $VECTORSIZE, $STADENFILE, $FASTAFILE, $FORWARDCLONINGSITE, $REVCLONINGSITE, $FORWARDPRIMERSITE, $REVPRIMERSITE) = $sth->fetchrow();

    push @library, "Vectorsite information.";
    push @library, "~~~~~~~~~~~~~~~~~~~~~~\n";
    push @library, "INTERNAL_PREFIX\n\t\t$INTERNAL_PREFIX\n";
    push @library, "EXTERNAL_PREFIX\n\t\t$EXTERNAL_PREFIX\n";
    push @library, "DESCRIPTION\n\t\t$DESCRIPTION\n";
    push @library, "SPECIESNAME\n\t\t$SPECIESNAME\n";
    push @library, "CHROMOSOME\n\t\t$CHROMOSOME\n";
    push @library, "VECTORNAME\n\t\t$VECTORNAME\n";
    push @library, "FIRST_PLATE\n\t\t$FIRST_PLATE\n";
    push @library, "LAST_PLATE\n\t\t$LAST_PLATE\n";
    push @library, "VECTORTYPE\n\t\t$DESCRIPTION2\n";
    push @library, "VECTORSIZE\n\t\t$VECTORSIZE\n";
    push @library, "STADENFILE\n\t\t$STADENFILE\n";
    push @library, "FASTAFILE\n\t\t$FASTAFILE\n";
    push @library, "FORWARDCLONINGSITE\n\t\t$FORWARDCLONINGSITE\n";
    push @library, "REVCLONINGSITE\n\t\t$REVCLONINGSITE\n";
    push @library, "FORWARDPRIMERSITE\n\t\t$FORWARDPRIMERSITE\n";
    push @library, "REVPRIMERSITE\n\t\t$REVPRIMERSITE\n";


    push @library, "Vectorsite enzyme information.";
    push @library, "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n";
    
    $statement = "
select vectorsite_enzyme.ENZYMENAME, enzyme.CUTSITE, enzyme.ISAVAILABLE, enzyme.METHYLATIONSITE
from vectorsite_enzyme, enzyme
where vectorsite_enzyme.ENZYMENAME = enzyme.ENZYMENAME
and vectorsite_enzyme.ID_VECTORSITE = \'$ID_VECTORSITE\'
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($ENZYMENAME, $CUTSITE, $ISAVAILABLE, $METHYLATIONSITE);
    while (($ENZYMENAME, $CUTSITE, $ISAVAILABLE, $METHYLATIONSITE) = $sth->fetchrow()) {
	push @library, "ENZYMENAME\n\t\t$ENZYMENAME\n";
	push @library, "CUTSITE\n\t\t$CUTSITE\n";
	push @library, "ISAVAILABLE\n\t\t$ISAVAILABLE\n";
	push @library, "METHYLATIONSITE\n\t\t$METHYLATIONSITE\n";
    }
    $sth->finish();
    my $return = \@library;
    return ($return);
}

package make;

sub batch_of_new_clones {
    my ($pkg, $dbh, $list, $cl_speciesname_var, $cl_chromosome_var, $cl_subregion_var, $cl_sourcename_var, $cl_sequencedby_var, $cl_fundedby_var,  $cl_seqreason_var, $cl_libraryname_var, $cl_remark_var, $cl_hsmclone_var, $cl_clonetype_var, $who, $session) = @_;

    #Enter the clones
    my $statement = "
insert into clone(
CLONENAME, 
SPECIESNAME, 
CHROMOSOME, 
SUBREGION, 
SOURCENAME, 
SEQUENCED_BY, 
FUNDED_BY, 
SEQ_REASON, 
LIBRARYNAME, 
REMARK, 
IS_HSM, 
CLONE_TYPE)
values (
?,
\'$cl_speciesname_var\', 
\'$$cl_chromosome_var\',
\'$cl_subregion_var\',
\'$cl_sourcename_var\',
\'$$cl_sequencedby_var\',
\'$$cl_fundedby_var\',
\'$$cl_seqreason_var\',
\'$cl_libraryname_var\',
\'$cl_remark_var\',
\'$cl_hsmclone_var\',
\'$$cl_clonetype_var\'
)
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    my $a;
    foreach my $next (@$list){
	$a = $next;
	$sth1->bind_param(1, $a);
	$sth1->execute || die "ERROR:$DBI::errstr";
    }
    $sth1->finish();   


# Enter a status
    my $statement = "insert into clone_status (CLONENAME, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)values (?, 1, sysdate, 1, $$session, \'Gull_gullAdd-bulk\', \'$who\')";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
        my $a;
    foreach my $next (@$list){
	$a = $next;
	$sth1->bind_param(1, $a);
	$sth1->execute || die "ERROR:$DBI::errstr";
    }
    $sth1->finish();   
}


sub batch_of_new_projects {
    my ($pkg, $dbh, $list, $pr_ftp_var, $pr_remark_var, $pr_type_var, $who, $session) = @_;

    #Enter the clones
    my $statement = "
insert into project(
PROJECTNAME,
ISPRIVATE, 
FTP_DIRECTORY,
REMARK, 
PROJECT_TYPE)
values (
?,
0, 
\'$pr_ftp_var\',
\'$pr_remark_var\',
\'$$pr_type_var\'
)
";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    my $a;
    foreach my $next (@$list){
	$a = $next;
	$sth1->bind_param(1, $a);
	$sth1->execute || die "ERROR:$DBI::errstr";
    }
    $sth1->finish();   


# Enter a status
    my $statement = "insert into project_status (PROJECTNAME, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR)values (?, 1, sysdate, 1, $$session, \'Gull_gullAdd-bulk\', \'$who\')";
    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
        my $a;
    foreach my $next (@$list){
	$a = $next;
	$sth1->bind_param(1, $a);
	$sth1->execute || die "ERROR:$DBI::errstr";
    }
    $sth1->finish();   
}


package take;

sub projects_off_hold {
    my ($pkg, $dbh, $list, $who) = @_;

#    warn "@$list ... \n";

    my %hash;
    ####################################################
    # Foreach of the projects get the rowid of the new
    # current status
    ####################################################
    my $statement = "
select a.rowid
from project_status a
where a.projectname = ?
and a.iscurrent = 0
and a.status != 26
order by a.statusdate desc
";

#    warn "$statement\n";

    my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    my ($a);
    foreach my $next (@$list){
	$a = $next;
	$sth1->bind_param(1, $a);
	$sth1->execute || die "ERROR:$DBI::errstr";
	my $got = $sth1->fetchrow();
	$hash{$a} = $got;
	$sth1->finish();   
    }
    
    ###############################################################
    # Update existing current entries for each project in question
    # to be non-current
    ###############################################################
    my $statement2 = "
update project_status
set iscurrent = 0
where projectname = ?
and iscurrent = 1
";

#    warn "$statement2\n";

    my $sth2 = $dbh->prepare($statement2) || die "ERROR:$DBI::errstr";
    my ($aa);
    foreach my $next (@$list){
	$aa = $next;
	$sth2->bind_param(1, $aa);
	$sth2->execute || die "ERROR:$DBI::errstr";
	$sth2->finish();   
    }

    ######################################
    # ...and make the last status current
    ######################################
    my $statement3 = "
update project_status
set iscurrent = 1
where rowid = ?
";

#    warn "$statement3\n";

    my $sth3 = $dbh->prepare($statement3) || die "ERROR:$DBI::errstr";
    my ($aaa);
    foreach my $next (keys %hash){
	$aaa = $hash{$next};
	$sth3->bind_param(1, $aaa);
	$sth3->execute || die "ERROR:$DBI::errstr";
	$sth3->finish();   
    }

}


1;



