#######################################################################
# 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: gull_graphics_sql.pm,v 1.13 2000/06/13 15:01:37 dn1 Exp $
# $Log: gull_graphics_sql.pm,v $
# Revision 1.13  2000/06/13  15:01:37  dn1
# *** empty log message ***
#
# Revision 1.12  1999/08/17  15:15:35  dn1
# *** empty log message ***
#
# Revision 1.11  1999/05/13 12:29:24  dn1
# *** empty log message ***
#
# Revision 1.10  1999/03/22 18:22:22  dn1
# *** empty log message ***
#

# DN1
########################################
# SQL calls needed for gull_graphics.pm
########################################
package parent_contig;

sub calc_length {
    my ($pkg, $dbh, $contig) = @_;
    my $statement = "
select leftpos 
from contig 
where on_contig = \'$contig\'
order by leftpos asc
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $from = $sth->fetchrow();
    $sth->finish;
    $statement = "
select rightpos 
from contig 
where on_contig = \'$contig\'
order by rightpos desc
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $to = $sth->fetchrow();
    $sth->finish;
#    warn "$contig -> from $from to $to \n";
    my @array = ($from, $to);
    my $return = \@array;
    return ($return);
}

sub count_contigs {
    my ($pkg, $dbh, $contig) = @_;
    my $statement = "
select count(contigname)
from contig
where on_contig = \'$contig\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $count = $sth->fetchrow();
    return $count;
}

sub get_contigs {
    my ($pkg, $dbh, $contig, $interface, $number) = @_;
    my $counter = 1;
    my $statement = "
select contig.contigname, contig.leftpos, contig.rightpos
from contig
where contig.on_contig = \'$contig\'
order by contig.leftpos asc
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($clonename, $leftpos, $rightpos, $status, $remark);
    my @contigs;
    while ( ($contigname, $leftpos, $rightpos) = $sth->fetchrow() ){

	my %clone_info;
	$clone_info{clonename} = $contigname;
	$clone_info{leftpos} = $leftpos;
	$clone_info{rightpos} = $rightpos;
	push @contigs, \%clone_info;
	my $old_width = $interface->{base}->Width;
	my $new_width = int($counter/$number*$old_width);
	$interface->{bar}->configure(-width => $new_width );
	$counter++;
	$interface->{mainWindow}->update;
	last if ($interface->{cancel_test} == 1);

    }
    $sth->finish;
    my $return = \@contigs;
    return ($return);

}
####################################################
####################################################



package contig;

sub get_type {
    my ($pkg, $dbh, $contig) = @_;
    ###################
    # Is it a parent ?
    ###################
    my $statement = "
select unique on_contig
from contig
where on_contig = \'$contig\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($parent) = $sth->fetchrow();
    $sth->finish;
    my $return;
    if ($parent ne ''){
	$return = 'Parent';
	return ($return);
    }
    
    ############################
    # Is it a custom made map ?
    ############################
    $statement = "
select manual_map
from contig
where contigname = \'$contig\'
";
    $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $man = $sth->fetchrow();
    $sth->finish;
    if ($man == 0){
	$return = 'Clone';
    } else {
	$return = 'Custom';
    }
    return ($return);
}


sub calc_length {
    my ($pkg, $dbh, $contig) = @_;
    my $statement = "
select extentfrom, extentto
from contig
where contigname = \'$contig\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($from, $to) = $sth->fetchrow();
    $sth->finish;
#    warn "from -> $from \n to -> $to \n";
    my @array = ($from, $to);
    my $return = \@array;
    return ($return);
}

sub on_contig {
    my ($pkg, $dbh, $contig) = @_;
    my $statement = "
select ON_CONTIG
from contig
where contigname = \'$contig\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $on_contig = $sth->fetchrow();
    $sth->finish;
    if ($contig eq ''){
	$contig = 'N/A';
    }
    my $return = $on_contig;
    return ($return);
}

sub on_positions {
    my ($pkg, $dbh, $contig) = @_;
    my $statement = "
select LEFTPOS, RIGHTPOS
from contig
where contigname = \'$contig\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($left_pos, $right_pos) = $sth->fetchrow();
    $sth->finish;
    if ($left_pos eq ''){
	$left_pos = 'N/A';
    }
    if ($right_pos eq ''){
	$right_pos = 'N/A';
    }

    return ($left_pos, $right_pos);
}

sub count_clones {
    my ($pkg, $dbh, $contig) = @_;
    my $statement = "
select count(clone_contig.clonename)
from clone_contig, clone_status
where clone_contig.clonename = clone_status.clonename 
and clone_contig.clonename = clone_status.clonename
and clone_contig.contigname = \'$contig\'
and clone_status.iscurrent = 1
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my $count = $sth->fetchrow();
    return $count;
}


#sub get_clones {
#    
#
#    warn "getting the clones now !!!\n";
#
#    my ($pkg, $dbh, $contig, $interface, $number) = @_;
#    my $counter = 1;
#    my $statement = "
#select clone_contig.clonename, clone_contig.leftpos, clone_contig.rightpos, clonestatusdict.description, clone.remark
#from clone_contig, clone_status, clonestatusdict, clone
#where clone_contig.clonename = clone_status.clonename 
#and clone_status.status = clonestatusdict.id_dict
#and clone_contig.clonename = clone.clonename
#and clone_contig.contigname = \'$contig\'
#and clone_status.iscurrent = 1
#order by leftpos asc
#";
#    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
#    $sth->execute || die "ERROR:$DBI::errstr";
#    my ($clonename, $leftpos, $rightpos, $status, $remark);
#    my @clones;
#    while ( ($clonename, $leftpos, $rightpos, $status, $remark) = $sth->fetchrow() ){
#
#	my $s = "select CFG_VALUE from program_config where PROGRAM = 'gull' and CFG_SET = 1 and CFG_PARAM = \'$status\'";
#
#	my $sth2 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
#	$sth2->execute || die "ERROR:$DBI::errstr";
#	my ($colour) = $sth2->fetchrow();
#	$sth2->finish();
#
#
#	##################################################################
#	# We need some project data ... This only works if there is a 1:1
#	# relationship between clone and project
#	##################################################################
#
#	my $sx = "
#select projectstatusdict.description, project.projectname, project_status.remark, project.remark
#from projectstatusdict, project, project_status, clone_project 
#where clone_project.projectname = project.projectname 
#and project.projectname = project_status.projectname
#and project_status.status = projectstatusdict.id_dict 
#and project_status.iscurrent = 1
#and clone_project.clonename = \'$clonename\'
#";
#
#	my $sthx = $dbh->prepare($sx) || die "ERROR:$DBI::errstr";
#	$sthx->execute || die "ERROR:$DBI::errstr";
#
#	########################################################
#	# This only takes the first one from the db... could
#	# possibly be multiples 1 clone -> many projects
#	########################################################
#	my $project_counter = 0;
#
#	my ($a, $b, $c, $d);
#	my ($project_status, $p_name, $p_s_remark, $p_remark, $finisher);
#	while ( ($a, $b, $c, $d) = $sthx->fetchrow()) {
#
#####################################################################################
#	    #########################################################
#	    # We can also collect the current finisher at this point
#	    #########################################################
#	    $finisher_s = "
#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 = \'$b\'
#order by project_role.assigned_from desc
#";
#	    my $sth = $dbh->prepare($finisher_s) || die "ERROR:$DBI::errstr";
#	    $sth->execute || die "ERROR:$DBI::errstr";
#	    my $check = "no hits";
#	    $finisher = $sth->fetchrow();
#
#
#	    $sth->finish();
#####################################################################################
#
#	    $project_status = $a;
#	    $p_name = $b;
#	    $p_s_remark = $c;
#	    $p_remark = $d;
#	    # don't forget $finisher
#
#	    $project_counter++;
#	}
#	$sthx->finish();
#
#	my $project_colour;
#	if ( $project_counter > 1) {
#	    $p_name = "Multiple projects exist";
#	    $p_remark = '';
#	    $project_status = '';
#	    $p_s_remark = '';
#	    $finisher = '';
#	}
#   
#	if ($project_counter == 0){
#	    $p_name = "No projects exist";
#	    $p_remark = '';
#	    $project_status = '';
#	    $p_s_remark = '';
#	    $finisher = '';
#	}
#
#	if ($project_status eq ''){
#	    $project_status = 'Cannot determine';
#	    $project_colour = 'black';
#	} else {
#	    my $s4 = "select CFG_VALUE from program_config where PROGRAM = 'gull' and CFG_SET = 2 and CFG_PARAM = \'$project_status\'";
#	    my $sth4 = $dbh->prepare($s4) || die "ERROR:$DBI::errstr";
#	    $sth4->execute || die "ERROR:$DBI::errstr";
#	    ($project_colour) = $sth4->fetchrow();
#	    $sth4->finish();
#	}
#
#	my %clone_info;
#	$clone_info{clonename} = $clonename;
#	$clone_info{leftpos} = $leftpos;
#	$clone_info{rightpos} = $rightpos;
#	$clone_info{clone_overlaps} = "overlaps info ?";
#	$clone_info{status_now} = $status;
#	$clone_info{project_name} = $p_name;
#	$clone_info{project_remark} = $p_remark;
#	$clone_info{project_status} = $project_status;
#	$clone_info{project_status_remark} = $p_s_remark;
#	#####################################################
#	$clone_info{project_finisher} = $finisher;
#	#####################################################
#	$clone_info{project_status_colour} = $project_colour;
#	
#	$clone_info{status_colour} = $colour;
#	$clone_info{remark} = $remark;
#	push @clones, \%clone_info;
#	my $old_width = $interface->{base}->Width;
#	my $new_width = int($counter/$number*$old_width);
#	$interface->{bar}->configure(-width => $new_width );
#	$counter++;
#	$interface->{mainWindow}->update;
#
#	#warn "$interface->{cancel_test} \n";
#	last if ($interface->{cancel_test} == 1);
#    }
#    $sth->finish;
#    my $return = \@clones;
#    return ($return);
#}

##################################################################################################
####################################################################################################################################################################################################


sub NEWget_clones {

    my ($pkg, $dbh, $contig, $interface, $number) = @_;
    my $counter = 1;
    
    my $statement = "
select clone_contig.clonename, clone_contig.leftpos, clone_contig.rightpos, clonestatusdict.description, clone.remark, program_config.cfg_value
from clone_contig, clone_status, clonestatusdict, clone, program_config
where clone_contig.clonename = clone_status.clonename 
and clone_status.status = clonestatusdict.id_dict
and clone_contig.clonename = clone.clonename
and clonestatusdict.description = program_config.cfg_param
and program_config.cfg_set = 1
and clone_contig.contigname = ?
and clone_status.iscurrent = 1
order by leftpos asc
";

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->bind_param(1, $contig);
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($clonename, $leftpos, $rightpos, $status, $remark, $clone_cfg);

    ############
    my @clones;
    ############

    my @clone_contig_array;
    while ( ($clonename, $leftpos, $rightpos, $status, $remark, $clone_cfg) = $sth->fetchrow() ){
	my %clonehash;
	$clonehash{clonename} = $clonename;
	$clonehash{leftpos} = $leftpos;
	$clonehash{rightpos} = $rightpos;
	$clonehash{status} = $status;
	$clonehash{remark} = $remark;
	$clonehash{cfg} = $clone_cfg;
	push @clone_contig_array, \%clonehash;
    }
    $sth->finish;

    # Part 1 DONE
    my $old_width = $interface->{base}->Width;
    my $new_width = int($counter/$number*$old_width);
    $interface->{bar}->configure(-width => $new_width );
    $counter++;
    $interface->{mainWindow}->update;
    return if ($interface->{cancel_test} == 1);


	###################################################################
	# We need some project data ... This only works if there is a 1:1
	# relationship between clone and project
	##################################################################

    my $sx = "
select projectstatusdict.description, project.projectname, project_status.remark, project.remark, program_config.cfg_value
from projectstatusdict, project, project_status, clone_project, program_config
where clone_project.projectname = project.projectname 
and project.projectname = project_status.projectname
and project_status.status = projectstatusdict.id_dict
and projectstatusdict.description = program_config.cfg_param
and program_config.cfg_set = 2
and project_status.iscurrent = 1
and clone_project.clonename = ?
";

# ? = clonename

    my $sthx = $dbh->prepare($sx) || die "ERROR:$DBI::errstr";
    my ($a, $pdescription, $pprojectname, $pstatus_remark, $premark, $pcfg);
    foreach my $n (sort @clone_contig_array){

	#################################################################################################
	#warn "CLONE $n->{clonename} $n->{leftpos} $n->{rightpos} $n->{status} $n->{remark} $n->{cfg}\n";
	#################################################################################################

	$a = $n->{clonename};
	$sthx->bind_param(1, $a);
	$sthx->execute || die "ERROR:$DBI::errstr";
	my $project_counter = 0;
	while ( ($pdescription, $pprojectname, $pstatus_remark, $premark, $pcfg) = $sthx->fetchrow){
	    $project_counter++;
	    $n->{projectname} = $pprojectname;
	    $n->{pdescription} = $pdescription;
	    $n->{pstatus_remark} = $pstatus_remark;
	    $n->{premark} = $premark;
	    $n->{pcfg} = $pcfg;
	}

	$n->{project_counter} = $project_counter;
	
	if ( $n->{project_counter} > 1) {
	    $n->{projectname} = "Multiple projects exist";
	    $n->{pdescription} = '';
	    $n->{pstatus_remark} = '';
	    $n->{premark} = '';
	    $n->{pcfg} = '';
	    $n->{finisher} = '';
	}

	if ($n->{project_counter} == 0){
	    $n->{projectname} = "No projects exist";
	    $n->{pdescription} = '';
	    $n->{pstatus_remark} = '';
	    $n->{premark} = '';
	    $n->{pcfg} = '';
	    $n->{finisher} = '';
	}
	
	if ($n->{pdescription} eq ''){
	    $n->{pdescription} = 'Cannot determine';
	    $n->{pcfg} = 'black';
	}
    }
    $sthx->finish;

    # Part 2 DONE
    my $old_width = $interface->{base}->Width;
    my $new_width = int($counter/$number*$old_width);
    $interface->{bar}->configure(-width => $new_width );
    $counter++;
    $interface->{mainWindow}->update;
    return if ($interface->{cancel_test} == 1);

    ##########################################################
    # Collect the current finisher at this point
    #########################################################
    my $finisher_s = "
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 = ?
order by project_role.assigned_from desc
";
    my $sth = $dbh->prepare($finisher_s) || die "ERROR:$DBI::errstr";
    foreach my $n (sort @clone_contig_array){
	if ( ($n->{projectname} ne 'Multiple projects exist') && ($n->{projectname} ne 'No projects exist') ){
	    $sth->bind_param(1, $n->{projectname});
	    $sth->execute || die "ERROR:$DBI::errstr";
	    my $finisher = $sth->fetchrow();
	    $n->{finisher} = $finisher;
	}
    }
    $sth->finish;

    # Part 3 DONE
    my $old_width = $interface->{base}->Width;
    my $new_width = int($counter/$number*$old_width);
    $interface->{bar}->configure(-width => $new_width );
    $counter++;
    $interface->{mainWindow}->update;
    return if ($interface->{cancel_test} == 1);

    foreach my $n (sort @clone_contig_array){
	
#	warn "INFO: CLONE $n->{clonename} $n->{leftpos} $n->{rightpos} $n->{status} $n->{remark} $n->{cfg}\n\tPROJECT $n->{projectname} $n->{pdescription} $n->{pstatus} $n->{pstatus_remark} $n->{premark} $n->{pcfg} $n->{project_counter} $n->{finisher}\n";
	my %clone_info;
	$clone_info{clonename} = $n->{clonename};
	$clone_info{leftpos} = $n->{leftpos};
	$clone_info{rightpos} = $n->{rightpos};
	$clone_info{clone_overlaps} = "overlaps info ?";
	$clone_info{status_now} = $n->{status};
	$clone_info{project_name} = $n->{projectname};
	$clone_info{project_remark} = $n->{premark};
	$clone_info{project_status} = $n->{pdescription};
	$clone_info{project_status_remark} = $n->{pstatus_remark};
	$clone_info{project_finisher} = $n->{finisher};
	$clone_info{project_status_colour} = $n->{pcfg};	
	$clone_info{status_colour} = $n->{cfg};
	$clone_info{remark} = $n->{remark};
	push @clones, \%clone_info;



###################################################
#	last if ($interface->{cancel_test} == 1);
###################################################

    }

    # Part 4 DONE
    my $old_width = $interface->{base}->Width;
    my $new_width = int($counter/$number*$old_width);
    $interface->{bar}->configure(-width => $new_width );
    $counter++;
    $interface->{mainWindow}->update;
    return if ($interface->{cancel_test} == 1);

    $sth->finish;
    my $return = \@clones;
    return ($return);
}

################################
######### End of new subroutine
################################














package marker;

sub get_markers {
    my ($pkg, $dbh, $clone) = @_;
#    warn "THIS CLONE -> $clone \n";
#    my $statement = "
#select clone_marker.markeralias, marker_alias.markername
#from clone_marker, marker_alias
#where clone_marker.markeralias = marker_alias.markeralias
#and clone_marker.clonename = \'$clone\'
#";

    my $statement = "
select clone_marker.markeralias
from clone_marker
where clone_marker.clonename = \'$clone\'
";

#    warn $statement;

    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($markername, $markeralias);
    my %hash;
    my $number = 0;
    while ( ($markeralias) = $sth->fetchrow() ){
	my %hash2;
	$number++;
	my $statement2 = "
	select marker_alias.markername
from marker_alias
where marker_alias.markeralias = \'$markeralias\'
";
#	warn $statement2;
	
	my $sth2 = $dbh->prepare($statement2) || die "ERROR:$DBI::errstr";
	$sth2->execute || die "ERROR:$DBI::errstr";
	$markername = $sth2->fetchrow();

	if ($markername eq ''){ $markername = 'None'; }

#	warn "@@@@@@@@@@@@@@@@@@@@@@@@@ \n";
#	warn "markername $markername markeralias $markeralias \n";
	$hash2{markeralias} = $markeralias;
	$hash2{markername} = $markername;
	$hash{$number} = \%hash2;
    }
    $sth->finish;
    my $return = \%hash;
    return ($return);
}

package overlap;

sub get_overlaps {
    my ($pkg, $dbh, $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_KB, $VARIATION_CHECK, $REMARK);
    my %hash;
    my $number = 0;
    while ( ($CLONENAME, $OVERLAPS_CLONE, $CONFIRMATION, $SIZE_KB, $VARIATION_CHECK, $REMARK) = $sth->fetchrow() ){

	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 = $$VARIATION_CHECK;
	    }

	my %hash2;
	$number++;
	$hash2{CLONENAME} = $CLONENAME;
	$hash2{OVERLAPS_CLONE} = $OVERLAPS_CLONE;
	$hash2{CONFIRMATION} = $CONFIRMATION;
	$hash2{SIZE_KB} = $SIZE_KB;
	$hash2{VARIATION_CHECK} = $VARIATION_CHECK;
	$hash2{OLR} = $REMARK;
	$hash{$number} = \%hash2;
    }
    $sth->finish;
    my $return = \%hash;
    return ($return);
}

package project;

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

    # we need projectname team and status

    my $statement = "
select clone_project.projectname 
from clone_project
where clone_project.clonename = \'$clone\'
";
    my $sth = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
    $sth->execute || die "ERROR:$DBI::errstr";
    my ($PROJECTNAME);

    my %hash;
    my $number = 0;

    while ( ($PROJECTNAME) = $sth->fetchrow() ){
	my %hash2;
	$number++;
#	my $statement2 = "
#select project.projectname, projectstatusdict.description
#from project, project_status, projectstatusdict
#where project.projectname =  project_status.projectname
#and project_status.status = projectstatusdict.id_dict
#and project.projectname = \'$PROJECTNAME\'
#and project_status.iscurrent = 1
#";

	my $statement2 = "
select project.projectname, project.remark, projectstatusdict.description, project_status.remark
from project, project_status, projectstatusdict
where project.projectname =  project_status.projectname
and project_status.status = projectstatusdict.id_dict
and project.projectname = \'$PROJECTNAME\'
and project_status.iscurrent = 1
";

	my $sth2 = $dbh->prepare($statement2) || die "ERROR:$DBI::errstr";
	$sth2->execute || die "ERROR:$DBI::errstr";
	my ($PROJECTNAME, $REMARK, $DESC, $STATUS_REMARK);
	while ( ($PROJECTNAME, $REMARK, $DESC, $STATUS_REMARK) = $sth2->fetchrow() ){
	    
	    # Need to get the current team_owner too 
	    my $s_p_o = "
select teamname 
from project_owner
where projectname = \'$PROJECTNAME\' 
order by OWNED_FROM desc
";
	    my $sth_p_o = $dbh->prepare($s_p_o) || die "ERROR:$DBI::errstr";
	    $sth_p_o->execute || die "ERROR:$DBI::errstr";
	    my $p_o;
	    ($p_o) = $sth_p_o->fetchrow();
	    $sth_p_o->finish();

	    # Need to get the current finisher too
	    my $finisher_s = "
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
";
	    my $sthf = $dbh->prepare($finisher_s) || die "ERROR:$DBI::errstr";
	    $sthf->execute || die "ERROR:$DBI::errstr";
	    my $finisher = $sthf->fetchrow();
	    $sthf->finish();

 	    $hash2{PROJECTNAME} = $PROJECTNAME;
	    $hash2{PROJECT_REMARK} = $REMARK;
	    $hash2{STATUS} = $DESC;
	    $hash2{STATUS_REMARK} = $STATUS_REMARK;
	    $hash2{TEAM_OWNER} = $p_o;
	    $hash2{FINISHER} = $finisher;

	    $sth_p_o->finish();
	    $hash{$number} = \%hash2;
	}
	$sth2->finish();
    }
    $sth->finish();

    my $return = \%hash;
    return ($return);
}




#package overlap;
#
#sub lock_entry {
#    my ($pkg, $hit, $dbh) = @_;
#    
#    #my $capture = Data::Dumper::Dumper($hit);
#    #warn $capture;
#
#    my $lock_fail = 0;
#
#    eval {
#	my $statement = "
#select clone_overlaps.CLONENAME, clone_overlaps.OVERLAPS_CLONE, clone_overlaps.OVERLAPS_CLONE, clone_overlaps.CONFIRMATION, clone_overlaps.SIZE_KB, clone_overlaps.VARIATION_CHECK
#from clone_overlaps
#where clone_overlaps.CLONENAME = \'$hit->{CLONENAME}\' 
#and clone_overlaps.OVERLAPS_CLONE = \'$hit->{OVERLAPS_CLONE}\'
#for update nowait
#";
#	warn "$statement\n";
#	my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
#	$sth1->execute || die "ERROR:$DBI::errstr";
#	$sth1->finish;
#    };    
#
#    #####################
#    # Could we do it >?<
#    #####################
#    if ($@ =~ /ORA-00054: resource busy and acquire with NOWAIT specified/){
#	#############################
#	# No :-( It's already locked
#	#############################
#	$lock_fail++;
#    }
#    return($lock_fail);
#}
#
#sub change_entry {
#    my ($pkg, $hit, $dbh) = @_;
#
#    my @problems = ();
#
#    # Need to convert the values for CONFIRMATION => 'Not checked' VARIATION_CHECK => 'No variation found',
#    # ...into something the db likes
#    my ($v, $c);
#    if ($hit->{CONFIRMATION} eq 'Not checked'){
#	$c = 'NULL';
#    } else {
#	$c = convert->confirmation2id_dict($dbh, $hit->{CONFIRMATION});
#    }
#
#    if ($hit->{VARIATION_CHECK} eq 'Not checked'){
#	$v = 'NULL';
#    } else {
#	$v = convert->variation_check2id_dict($dbh, $hit->{VARIATION_CHECK});
#    }    
#
#    my $test = ref($c);
#    my $test2 = ref($v);
#
#    if ( ref($c) eq 'convert'){
#	# $c is a ref -> need to deref
#	$c = $$c;
#    }
#
#    if ( ref($v) eq 'convert'){
#	# $v is a ref -> need to deref
#	$v = $$v;
#    }
#
#    warn "$hit->{CONFIRMATION} ----- $c ----- $test !!! \n";
#    warn "$hit->{VARIATION_CHECK} ----- $v ----- $test2 !!!\n";
#
#
#    eval {
#	my $statement = "update clone_overlaps set clone_overlaps.CONFIRMATION = $c, clone_overlaps.VARIATION_CHECK = $v, clone_overlaps.SIZE_BP = \'$hit->{SIZE_KB}\' where clone_overlaps.CLONENAME = \'$hit->{CLONENAME}\' and clone_overlaps.OVERLAPS_CLONE = \'$hit->{OVERLAPS_CLONE}\'";

#	warn "%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% \n";
#	warn "$statement \n";
#	warn "%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% \n";
#
#	my $sth1 = $dbh->prepare($statement) || die "ERROR:$DBI::errstr";
#	$sth1->execute || die "ERROR:$DBI::errstr";
#	$sth1->finish();
#    };
#
#    if ($@ ne ''){
#	push @problems, $@;
#    } else {
#	push @problems, 'End';
#    }
#
#    return (@problems);
#
#}


1;
