#######################################################################
# 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: project_query.pm,v 1.52 2007/02/08 15:30:17 mdf Exp $
# $Log: project_query.pm,v $
# Revision 1.52  2007/02/08  15:30:17  mdf
# added new rule for assembly start tag
#
# Revision 1.51  2006/02/09  14:54:22  mdf
# *** empty log message ***
#
# Revision 1.50  2006/02/09  14:49:10  mdf
# tried to fix the problem of people taking projects out of autofinishing
#
# Revision 1.49  2006/01/05  11:27:09  mdf
# attempted to fix the problem of roletypes from non current teams
#
# Revision 1.48  2002/07/07  15:52:27  dn1
# *** empty log message ***
#
# Revision 1.47  2002/07/07  15:24:47  dn1
# *** empty log message ***
#
# Revision 1.46  2001/03/26  09:19:36  dn1
# *** empty log message ***
#
# Revision 1.45  2000/06/05  10:30:35  dn1
# *** empty log message ***
#
# Revision 1.44  2000/03/20  19:00:57  dn1
# *** empty log message ***
#
# Revision 1.43  2000/03/13  11:41:11  dn1
# *** empty log message ***
#
# Revision 1.42  2000/03/02  10:53:27  dn1
# *** empty log message ***
#
# Revision 1.41  1999/11/22  11:57:40  dn1
# *** empty log message ***
#
# Revision 1.40  1999/11/22 10:41:09  dn1
# *** empty log message ***
#
# Revision 1.39  1999/09/07 16:39:09  dn1
# *** empty log message ***
#
# Revision 1.38  1999/09/07 14:55:02  dn1
# *** empty log message ***
#
# Revision 1.37  1999/08/17 15:24:29  dn1
# *** empty log message ***
#
# Revision 1.36  1999/05/14 12:57:59  dn1
# *** empty log message ***
#
# Revision 1.35  1999/05/11 15:03:17  dn1
# *** empty log message ***
#
# Revision 1.34  1999/03/22 18:19:34  dn1
# *** empty log message ***
#

# DN1
################################################################################
#
################################################################################


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


package project_query;

sub make_with {

    my ($pkg, $counter, $project_name_var, $ftp_var, $remark_var, $type_var_id,  $status_var_id, $s_remark_var, $length_var, $clone_var, $fin_var_id, $pfin_var_id, $check_var_id, $towards_var, $robot_var_id, $teamo_var, $teamor_var_id) = @_;

    my $query;

    ################
    # Handle select
    ################

    $query = "select unique (project.projectname), (project.ftp_directory), (project.remark), (project.project_type), (projectstatusdict.description), (project_status.remark), (project.internal_length_bp) ";
    

    ################
    # Handle from
    ################

    $query = "$query" . " from project, project_status, projectstatusdict ";
    

    if ($clone_var ne ''){
	$query = "$query" . " , clone_project ";
    }
    if ( ($teamo_var ne '') || ($teamor_var_id) ){
	$query = "$query" . ", project_owner ";
    }
    if ( ($fin_var_id ne '') || ($pfin_var_id ne '') || ($check_var_id ne '') ){
	$query = "$query" . ", project_role ";
    }
    if ($robot_var_id ne ''){
	$query = "$query" . ", robot_project ";
    }
    if ($towards_var ne ''){
	$query = "$query" . ", finishing_direction ";
    }

    ###############
    # Handle where
    ###############
    # We should always join project and project_status
    $query = "$query" . " where project.projectname = project_status.projectname and project_status.status = projectstatusdict.id_dict and  ";

    # don't other forget joins >!< 
    if ($clone_var ne ''){
	$query = "$query" . " project.projectname  = clone_project.projectname and";
    }
    if ( ($teamo_var ne '') || ($teamor_var_id) ){
	$query = "$query" . " project.projectname = project_owner.projectname and";
    }

    if ( ($fin_var_id ne '') || ($pfin_var_id ne '') || ($check_var_id ne '') ){
	$query = "$query" . " project.projectname = project_role.projectname and";
    }

    if ($robot_var_id ne ''){
	$query = "$query" . " project.projectname = robot_project.projectname and";
    }
    if ($towards_var ne ''){
	$query = "$query" . " project.projectname = finishing_direction.projectname and";
    }

#    # rest of conditions
#    if ($project_name_var ne ''){
#	$query = "$query" . " project.projectname = \'$project_name_var\'";
#	$counter--;
#	unless ($counter == 0){
#	    $query = "$query" . " and ";
#	}
#    }

    if ($project_name_var ne ''){
	if ($project_name_var =~ /\*/){
	    $project_name_var =~ s/\*/%/g;
	    $query = "$query" . " project.projectname like \'$project_name_var\'";
	} else {
	    $query = "$query" . " project.projectname = \'$project_name_var\'";
	}
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }
    
#    if ($unfin_var ne ''){
#	$query = "$query" . " project.unfin_embl_acc = \'$unfin_var\'";
#	$counter--;
#	unless ($counter == 0){
#	    $query = "$query" . " and ";
#	}
#    }
#
#    if ($unfin2_var ne ''){
#	$query = "$query" . " project.unfin_embl_name = \'$unfin2_var\'";
#	$counter--;
#	unless ($counter == 0){
#	    $query = "$query" . " and ";
#	}
#    }
    
    if ($ftp_var ne ''){
	$query = "$query" . " project.ftp_directory = \'$ftp_var\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }


    if ($remark_var ne ''){
	$query = "$query" . " project.remark = \'$remark_var\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

    if ($type_var_id ne ''){
	$query = "$query" . " project.project_type = \'$$type_var_id\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

    if ($length_var ne ''){
	$query = "$query" . " project.internal_length_bp = \'$length_var\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

    
    if ($status_var_id ne ''){
	$query = "$query" . " project_status.status = \'$$status_var_id\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

    
    if ($s_remark_var ne ''){
	$query = "$query" . " project_status.remark = \'$s_remark_var\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

    
    if ($clone_var ne ''){
	$query = "$query" . " clone_project.clonename = \'$clone_var\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }
    
    if ($towards_var ne ''){
	$query = "$query" . " finishing_direction.finish_towards = \'$towards_var\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

    if ($robot_var_id ne ''){
	$query = "$query" . " robot_project.id_robot = \'$$robot_var_id\' and robot_project.date_unloaded is NULL ";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

#    if ($suba_var ne ''){
#	$query = "$query" . " submission.embl_name = \'$suba_var\'";
#	$counter--;
#	unless ($counter == 0){
#	    $query = "$query" . " and ";
#	}
#    }

#    if ($subb_var ne ''){
#	$query = "$query" . " submission.primary_embl_acc = \'$subb_var\'";
#	$counter--;
#	unless ($counter == 0){
#	    $query = "$query" . " and ";
#	}
#    }

#    if ($subc_var ne ''){
#	$query = "$query" . " submission.finished_length = \'$subc_var\'";
#	$counter--;
#	unless ($counter == 0){
#	    $query = "$query" . " and ";
#	}
#    }

    ###########################################################
    # Some new code to handle team/ownership-reason correctly
    ###########################################################
    my $q1 = '';
    if ( ($teamo_var ne '') || ($teamor_var_id ne '')){
	$q1 = $q1 . " project_owner.projectname in ( ";
    }

    my $q1_team;
    if ($teamo_var ne ''){
	$q1_team = " select p.projectname from project_owner p where (p.projectname, p.owned_from) in (select a.projectname, max(a.owned_from) from project_owner a group by a.projectname) and p.teamname = \'$teamo_var\' ";
    } else {
	$q1_team = " intersect select p.projectname from project_owner p where (p.projectname, p.owned_from) in (select a.projectname, max(a.owned_from) from project_owner a group by a.projectname) and p.teamname like \'%\'";
    }

    my $q1_int = " intersect ";
    
    my $q1_reason;
    if ($teamor_var_id ne ''){
	$q1_reason = " select p.projectname from project_owner p where (p.projectname, p.owned_from) in (select a.projectname, max(a.owned_from) from project_owner a group by a.projectname) and p.owner_type = \'$$teamor_var_id\' ";
    } else {
	$q1_reason = " intersect select p.projectname from project_owner p where (p.projectname, p.owned_from) in (select a.projectname, max(a.owned_from) from project_owner a group by a.projectname) and p.owner_type like \'%\' ";
    }

    if ( ($teamo_var ne '') && ($teamor_var_id ne '')){
	$counter--;
	$counter--;
	$q1 = "$q1" . "$q1_team" . "$q1_int" . "$q1_reason";
#	unless ($counter == 0){
#	    $query = "$query" . " and ";
#	}
    }

    if ( ($teamo_var ne '') && ($teamor_var_id eq '')){
	$counter--;
	$q1 = "$q1" . "$q1_team" . "$q1_reason";
#	unless ($counter == 0){
#	    $query = "$query" . " and ";
#	}
    }

    if ( ($teamo_var eq '') && ($teamor_var_id ne '')){
	$counter--;
	$q1 = "$q1" . "$q1_reason" . "$q1_team";
#	unless ($counter == 0){
#	    $query = "$query" . " and ";
#	}
    }

    $q1 = "$q1" . " ) "; 

    
    if ( ($teamo_var ne '') || ($teamor_var_id ne '')){
	$query = "$query" . "$q1";
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }


#    ###########################################################
#    # Some new code to handle finishers/prefinishers correctly
#    ###########################################################
#    my $q2 = '';
#    if ( ($fin_var_id ne '') || ($pfin_var_id ne '')){
#	$q2 = $q2 . " project_role.projectname in ( ";
#
#    }
#
#    my $q2_fin = " select pr.projectname from project_role pr where (projectname,assigned_from) in (select projectname, max(assigned_from) from project_role a, team_person_role b where a.id_role = b.id_role and b.roletype = 'Finishing' group by projectname) and id_role = \'$$fin_var_id\'  ";
#
#    my $q2_int = " intersect ";
#
#    my $q2_pfin = " select pr.projectname from project_role pr where (projectname,assigned_from) in (select projectname, max(assigned_from) from project_role a, team_person_role b where a.id_role = b.id_role and b.roletype = 'Prefinishing' group by projectname) and id_role = \'$$pfin_var_id\'";
#
#    if ( ($fin_var_id ne '') && ($pfin_var_id ne '')){
#	$counter--;
#	$counter--;
#	$q2 = "$q2" . "$q2_fin" . "$q2_int" . "$q2_pfin";
#	unless ($counter == 0){
#	    $query = "$query" . " and ";
#	}
#    }
#
#    if ( ($fin_var_id ne '') && ($pfin_var_id eq '')){
#	$counter--;
#	$q2 = "$q2" . "$q2_fin";
#	unless ($counter == 0){
#	    $query = "$query" . " and ";
#	}
#    }

#    if ( ($fin_var_id eq '') && ($pfin_var_id ne '')){
#	$counter--;
#	$q2 = "$q2" . "$q2_pfin";
#	unless ($counter == 0){
#	    $query = "$query" . " and ";
#	}
#    }
#
#    $q2 = "$q2" . " ) "; 
#    
#    if ( ($fin_var_id ne '') || ($pfin_var_id ne '')){
#	$query = "$query" . "$q2";
#    }
#
#    $query = "$query" . " and project_status.iscurrent = 1 ";
#    
#    #warn "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n";
#    #warn "$query \n";
#    #warn "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n";
#
#    my $return = \$query;
#    bless $return, 'clone_query';
#    return ($return);
#}

###############
# insert here
###############

    #-----> here

    ###########################################################
    # Some new code to handle finishers/prefinishers correctly
    ###########################################################
    my $q2 = '';

#    if ( ($fin_var_id ne '') || ($pfin_var_id ne '')){
    if ( ($fin_var_id ne '') || ($pfin_var_id ne '') || ($check_var_id ne '') ){
	$q2 = $q2 . " project_role.projectname in ( ";
    }

    my $q2_fin = " select pr.projectname from project_role pr where (projectname,assigned_from) in (select projectname, max(assigned_from) from project_role a, team_person_role b where a.id_role = b.id_role and b.roletype = 'Finishing' group by projectname) and id_role = \'$$fin_var_id\'  ";

    my $q2_int = " intersect ";

    my $q2_pfin = " select pr.projectname from project_role pr where (projectname,assigned_from) in (select projectname, max(assigned_from) from project_role a, team_person_role b where a.id_role = b.id_role and b.roletype = 'Prefinishing' group by projectname) and id_role = \'$$pfin_var_id\'";

    my $q2_check = " select pr.projectname from project_role pr where (projectname,assigned_from) in (select projectname, max(assigned_from) from project_role a, team_person_role b where a.id_role = b.id_role and b.roletype = 'Checking' group by projectname) and id_role = \'$$check_var_id\'";

    if ( ($fin_var_id ne '') && ($pfin_var_id ne '')){
	$counter--;
	$counter--;
	$q2 = "$q2" . "$q2_fin" . "$q2_int" . "$q2_pfin";
    }

    ##########
    if ( ($fin_var_id ne '') && ($check_var_id ne '')){
	$counter--;
	$counter--;
	$q2 = "$q2" . "$q2_fin" . "$q2_int" . "$q2_check";
    }
    if ( ($pfin_var_id ne '') && ($check_var_id ne '')){
	$counter--;
	$counter--;
	$q2 = "$q2" . "$q2_pfin" . "$q2_int" . "$q2_check";
    }
    ##########

    if ( ($fin_var_id ne '') && ($pfin_var_id eq '') && ($check_var_id eq '') ){
	$counter--;
	$q2 = "$q2" . "$q2_fin";
    }

    if ( ($fin_var_id eq '') && ($pfin_var_id ne '') && ($check_var_id eq '') ){
	$counter--;
	$q2 = "$q2" . "$q2_pfin";
    }

    if ( ($fin_var_id eq '') && ($pfin_var_id eq '') && ($check_var_id ne '') ){
	$counter--;
	$q2 = "$q2" . "$q2_check";
    }

    $q2 = "$q2" . " ) "; 
    

    if ( ($fin_var_id ne '') || ($pfin_var_id ne '') || ($check_var_id ne '') ){
#	warn "\n\n$q2\n\n";
	$query = "$query" . "$q2";
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

    #########################################################
    $query = "$query" . " and project_status.iscurrent = 1 ";



#    warn "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n";
#    warn "MAKE WITH\n";
#    warn "$query \n";
#    warn "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n";


    my $return = \$query;
    bless $return, 'clone_query';
    return ($return);
}
#-----> here

sub count_with {

#    warn "Using this version of project_query.../n";

    my ($pkg, $counter, $project_name_var, $ftp_var, $remark_var, $type_var_id,  $status_var_id, $s_remark_var, $length_var, $clone_var, $fin_var_id, $pfin_var_id, $check_var_id, $towards_var, $robot_var_id, $teamo_var, $teamor_var_id) = @_;

    my $query;

    ################
    # Handle select
    ################
    $query = "select count( unique(project.projectname) )";
    
    ################
    # Handle from
    ################
    $query = "$query" . " from project, project_status ";
    if ($clone_var ne ''){
	$query = "$query" . " , clone_project ";
	}
    if ( ($teamo_var ne '') || ($teamor_var_id ne '') ){
	$query = "$query" . ", project_owner ";
    }
    
    if ( ($fin_var_id ne '') || ($pfin_var_id ne '') || ($check_var_id ne '') ){
	$query = "$query" . ", project_role ";
    }

    if ($robot_var_id ne ''){
	$query = "$query" . ", robot_project ";
    }
    if ($towards_var ne ''){
	$query = "$query" . ", finishing_direction ";
    }

    ###############
    # Handle where
    ###############
    # We should always join project and project_status
    $query = "$query" . " where project.projectname = project_status.projectname and ";

    # don't other forget joins >!< 
    if ($clone_var ne ''){
	$query = "$query" . " project.projectname  = clone_project.projectname and";
    }
    if ( ($teamo_var ne '') || ($teamor_var_id) ){
	$query = "$query" . " project.projectname = project_owner.projectname and";
    }

    if ( ($fin_var_id ne '') || ($pfin_var_id ne '') || ($check_var_id ne '') ) {
	$query = "$query" . " project.projectname = project_role.projectname and";
    }
    if ($robot_var_id ne ''){
	$query = "$query" . " project.projectname = robot_project.projectname and";
    }
    if ($towards_var ne ''){
	$query = "$query" . " project.projectname = finishing_direction.projectname and";
    }

    # rest of condtions
#    if ($project_name_var ne ''){
#	$query = "$query" . " project.projectname = \'$project_name_var\'";
#	$counter--;
#	unless ($counter == 0){
#	    $query = "$query" . " and ";
#	}
#    }

    if ($project_name_var ne ''){
	if ($project_name_var =~ /\*/){
	    $project_name_var =~ s/\*/%/g;
	    $query = "$query" . " project.projectname like \'$project_name_var\'";
	} else {
	    $query = "$query" . " project.projectname = \'$project_name_var\'";
	}
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }


    if ($ftp_var ne ''){
	$query = "$query" . " project.ftp_directory = \'$ftp_var\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }


    if ($remark_var ne ''){
	$query = "$query" . " project.remark = \'$remark_var\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

    if ($type_var_id ne ''){
	$query = "$query" . " project.project_type = \'$$type_var_id\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }


    if ($length_var ne ''){
	$query = "$query" . " project.internal_length_bp = \'$length_var\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

    
    if ($status_var_id ne ''){
	$query = "$query" . " project_status.status = \'$$status_var_id\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

    
    if ($s_remark_var ne ''){
	$query = "$query" . " project_status.remark = \'$s_remark_var\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

    
    if ($clone_var ne ''){
	$query = "$query" . " clone_project.clonename = \'$clone_var\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }
 
    if ($towards_var ne ''){
	$query = "$query" . " finishing_direction.finish_towards = \'$towards_var\'";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

    if ($robot_var_id ne ''){
	$query = "$query" . " robot_project.id_robot = \'$$robot_var_id\' and robot_project.date_unloaded is NULL ";
	$counter--;
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

    ###########################################################
    # Some new code to handle team/ownership-reason correctly
    ###########################################################
    my $q1 = '';
    if ( ($teamo_var ne '') || ($teamor_var_id ne '')){
	$q1 = $q1 . " project_owner.projectname in ( ";
    }

    my $q1_team;
    if ($teamo_var ne ''){
	$q1_team = " select p.projectname from project_owner p where (p.projectname, p.owned_from) in (select a.projectname, max(a.owned_from) from project_owner a group by a.projectname) and p.teamname = \'$teamo_var\' ";
    } else {
	$q1_team = " intersect select p.projectname from project_owner p where (p.projectname, p.owned_from) in (select a.projectname, max(a.owned_from) from project_owner a group by a.projectname) and p.teamname like \'%\'";
    }

    my $q1_int = " intersect ";
    
    my $q1_reason;
    if ($teamor_var_id ne ''){
	$q1_reason = " select p.projectname from project_owner p where (p.projectname, p.owned_from) in (select a.projectname, max(a.owned_from) from project_owner a group by a.projectname) and p.owner_type = \'$$teamor_var_id\' ";
    } else {
	$q1_reason = " intersect select p.projectname from project_owner p where (p.projectname, p.owned_from) in (select a.projectname, max(a.owned_from) from project_owner a group by a.projectname) and p.owner_type like \'%\' ";
    }

    if ( ($teamo_var ne '') && ($teamor_var_id ne '')){
	$counter--;
	$counter--;
	$q1 = "$q1" . "$q1_team" . "$q1_int" . "$q1_reason";
    }

    if ( ($teamo_var ne '') && ($teamor_var_id eq '')){
	$counter--;
	$q1 = "$q1" . "$q1_team" . "$q1_reason";
    }

    if ( ($teamo_var eq '') && ($teamor_var_id ne '')){
	$counter--;
	$q1 = "$q1" . "$q1_reason" . "$q1_team";
    }

    $q1 = "$q1" . " ) "; 

    
    if ( ($teamo_var ne '') || ($teamor_var_id ne '')){
	$query = "$query" . "$q1";
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

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

    #-----> here

    ###########################################################
    # Some new code to handle finishers/prefinishers correctly
    ###########################################################
    my $q2 = '';

#    if ( ($fin_var_id ne '') || ($pfin_var_id ne '')){
    if ( ($fin_var_id ne '') || ($pfin_var_id ne '') || ($check_var_id ne '') ){
	$q2 = $q2 . " project_role.projectname in ( ";
    }

    my $q2_fin = " select pr.projectname from project_role pr where (projectname,assigned_from) in (select projectname, max(assigned_from) from project_role a, team_person_role b where a.id_role = b.id_role and b.roletype = 'Finishing' group by projectname) and id_role = \'$$fin_var_id\'  ";

    my $q2_int = " intersect ";

    my $q2_pfin = " select pr.projectname from project_role pr where (projectname,assigned_from) in (select projectname, max(assigned_from) from project_role a, team_person_role b where a.id_role = b.id_role and b.roletype = 'Prefinishing' group by projectname) and id_role = \'$$pfin_var_id\'";

    my $q2_check = " select pr.projectname from project_role pr where (projectname,assigned_from) in (select projectname, max(assigned_from) from project_role a, team_person_role b where a.id_role = b.id_role and b.roletype = 'Checking' group by projectname) and id_role = \'$$check_var_id\'";

    if ( ($fin_var_id ne '') && ($pfin_var_id ne '')){
	$counter--;
	$counter--;
	$q2 = "$q2" . "$q2_fin" . "$q2_int" . "$q2_pfin";
    }

    ##########
    if ( ($fin_var_id ne '') && ($check_var_id ne '')){
	$counter--;
	$counter--;
	$q2 = "$q2" . "$q2_fin" . "$q2_int" . "$q2_check";
    }
    if ( ($pfin_var_id ne '') && ($check_var_id ne '')){
	$counter--;
	$counter--;
	$q2 = "$q2" . "$q2_pfin" . "$q2_int" . "$q2_check";
    }
    ##########

    if ( ($fin_var_id ne '') && ($pfin_var_id eq '') && ($check_var_id eq '') ){
	$counter--;
	$q2 = "$q2" . "$q2_fin";
    }

    if ( ($fin_var_id eq '') && ($pfin_var_id ne '') && ($check_var_id eq '') ){
	$counter--;
	$q2 = "$q2" . "$q2_pfin";
    }

    if ( ($fin_var_id eq '') && ($pfin_var_id eq '') && ($check_var_id ne '') ){
	$counter--;
	$q2 = "$q2" . "$q2_check";
    }

    $q2 = "$q2" . " ) "; 
    

    if ( ($fin_var_id ne '') || ($pfin_var_id ne '') || ($check_var_id ne '') ){
#	warn "\n\n$q2\n\n";
	$query = "$query" . "$q2";
	unless ($counter == 0){
	    $query = "$query" . " and ";
	}
    }

    #########################################################
    $query = "$query" . " and project_status.iscurrent = 1 ";



#    warn "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n";
#    warn "COUNT WITH\n";
#    warn "$query \n";
#    warn "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@\n";

    my $return = \$query;
    bless $return, 'clone_query';
    return ($return);
}
#-----> here

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

package datastructure;

sub new {

    #warn "making a datasstructure ... done !\n";

    my ($pkg, $projectname, $ftp_directory, $remark, $project_type, $description, $status_remark, $length, $CLONE_PROJECT, $PROJECT_OWNER, $PROJECT_ROLE, $PROJECT_ROLE2, $PROJECT_ROLE3, $ROBOT_PROJECT, $FINISHING_DIRECTION, $SUBMISSION, $SUMMARY) =@_;

    my %project_hash = (
			"Projectname" => $projectname,
			"Ftp_directory" => $ftp_directory,
			"Remark" => $remark,
			"Project_type" => $project_type,
			"Status" => $description,
			"Status_remark" => $status_remark,
			"Length" => $length
			);

    my $project_hash = \%project_hash;
    my %hash = (
		"Project_info" => $project_hash,
		"CLONE_PROJECT_info" => $CLONE_PROJECT,
		"PROJECT_OWNER_info" => $PROJECT_OWNER,
		"PROJECT_ROLE_info" => $PROJECT_ROLE,
		"PROJECT_ROLE_info2" => $PROJECT_ROLE2,
		"PROJECT_ROLE_info3" => $PROJECT_ROLE3,
		"ROBOT_PROJECT_info" => $ROBOT_PROJECT,
		"FINISHING_DIRECTION_info" => $FINISHING_DIRECTION, 
		"SUBMISSION_info" => $SUBMISSION,
		"SUMMARY_info" => $SUMMARY
		);

    my $return = \%hash;    
    bless $return, $pkg;
    return ($return);

}


sub lock_data {
    my ($hit, $dbh) = @_;
    my $lock_fail = 0;

    eval {
	my $statement = "
select PROJECTNAME, ISPRIVATE, FTP_DIRECTORY, REMARK, ID_ONLINE, PROJECT_TYPE, INTERNAL_LENGTH_BP
from project
where projectname = \'$hit->{Project_info}->{Projectname}\'
for update nowait
";

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

    if ($@ =~ /ORA-00054: resource busy and acquire with NOWAIT specified/){

	$lock_fail++;

    } else {

	my ($CLONE_counter, $PROJECT_OWNER_counter, $PROJECT_ROLE_counter, $ROBOT_PROJECT_counter, $FINISHING_DIRECTION_counter, $SUBMISSION_counter);
	$CLONE_counter = @{$hit->{CLONE_PROJECT_info}};
	$PROJECT_ROLE_counter= @{$hit->{PROJECT_ROLE_info}};
	$ROBOT_PROJECT_counter = @{$hit->{ROBOT_PROJECT_info}};
	$FINISHING_DIRECTION_counter = @{$hit->{FINISHING_DIRECTION_info}};
	$SUBMISSION_counter = @{$hit->{SUBMISSION_info}};
	$SUMMARY_counter = @{$hit->{SUMMARY_info}};

	my $next;
	### CLONE_PROJECT ###
	foreach $next(1 .. ($CLONE_counter-1)){
	    eval {
		$statement = "
select clone_project.clonename, clone_project.projectname 
from clone_project
where clone_project.clonename = \'$hit->{CLONE_PROJECT_info}->[$next]\'
and clone_project.projectname = \'$hit->{Project_info}->{Projectname}\'
for update nowait
";

		#warn "$statement\n";

		$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++;
	    } else {

	    }
	}

	### PROJECT_OWNER ###

	# convert reason into its id
	my $teamor_var_id = convert->teamor2id_dict($dbh, $hit->{PROJECT_OWNER_info}->[1]->{reason});

	eval {
	    $statement = "
select PROJECTNAME,  TEAMNAME, OWNED_FROM, OWNER_TYPE
from project_owner 
where PROJECTNAME = \'$hit->{Project_info}->{Projectname}\'
and TEAMNAME = \'$hit->{PROJECT_OWNER_info}->[1]->{team}\'
and OWNER_TYPE = \'$$teamor_var_id\'
for update nowait
";

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

	if ($@ =~ /ORA-00054: resource busy and acquire with NOWAIT specified/){
	    $lock_fail++;
	} else {

	}



	### PROJECT_ROLE ###
	eval {

	    $statement = "
select project_role.PROJECTNAME, project_role.ID_ROLE, project_role.ASSIGNED_FROM
from project_role
where project_role.PROJECTNAME = \'$hit->{Project_info}->{Projectname}\'
and project_role.ID_ROLE in (
	select team_person_role.id_role 
	from team_person_role, person
	where person.id_person = team_person_role.id_person
	and person.email = \'$hit->{PROJECT_ROLE_info}->[1]\'
	and team_person_role.roletype = 'Finishing'
        and team_person_role.iscurrent = 1)
for update nowait
";

	    $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++;
	} else {

	}

	### PROJECT_ROLE2 ###
	eval {

	    $statement = "
select project_role.PROJECTNAME, project_role.ID_ROLE, project_role.ASSIGNED_FROM
from project_role
where project_role.PROJECTNAME = \'$hit->{Project_info}->{Projectname}\'
and project_role.ID_ROLE in (
	select team_person_role.id_role 
	from team_person_role, person
	where person.id_person = team_person_role.id_person
	and person.email = \'$hit->{PROJECT_ROLE_info2}->[1]\'
	and team_person_role.roletype = 'Prefinishing'
        and team_person_role.iscurrent = 1)
for update nowait
";

 	    $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++;
	} else {

	}

# ---> here
	### PROJECT_ROLE3 ###
	eval {

	    $statement = "
select project_role.PROJECTNAME, project_role.ID_ROLE, project_role.ASSIGNED_FROM
from project_role
where project_role.PROJECTNAME = \'$hit->{Project_info}->{Projectname}\'
and project_role.ID_ROLE in (
	select team_person_role.id_role 
	from team_person_role, person
	where person.id_person = team_person_role.id_person
	and person.email = \'$hit->{PROJECT_ROLE_info3}->[1]\'
	and team_person_role.roletype = 'Checking'
        and team_person_role.iscurrent = 1)
for update nowait
";

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

	if ($@ =~ /ORA-00054: resource busy and acquire with NOWAIT specified/){
	    $lock_fail++;
	} else {

	}
# ---> here



	### ROBOT_PROJECT ###
	foreach $next(1 .. ($ROBOT_PROJECT_counter-1)){
	    eval {

$statement = "
select robot_project.PROJECTNAME, robot_project.ID_ROBOT, robot_project.DATE_LOADED, robot_project.DATE_UNLOADED
from robot_project
where robot_project.id_robot in (
	select id_robot
	from robot
	where robot.ROBOTNAME = \'$hit->{ROBOT_PROJECT_info}->[$next]\')
and robot_project.PROJECTNAME = \'$hit->{Project_info}->{Projectname}\'
for update nowait
";

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

	    if ($@ =~ /ORA-00054: resource busy and acquire with NOWAIT specified/){
		# No :-( It's already locked
		$lock_fail++;
	    } else {

	    }
	}

	### FINISHING_DIRECTION ###
	foreach $next(1 .. ($FINISHING_DIRECTION_counter-1)){
	    eval {
		$statement = "
select PROJECTNAME, FINISH_TOWARDS
from finishing_direction
where FINISH_TOWARDS = \'$hit->{FINISHING_DIRECTION_info}->[$next]\'
and PROJECTNAME = \'$hit->{Project_info}->{Projectname}\'
for update nowait
";

		$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++;
	    } else {

	    }
	}

	### SUBMISSION ###
	foreach $next(1 .. ($SUBMISSION_counter-1)){

	    eval {
		if ($hit->{SUBMISSION_info}->[$next]->{type} eq 'Finished'){
		    $statement = "
select finished_submission.projectname, finished_submission.accession, embl_submission.accession, embl_submission.name, embl_submission.length_bp
from finished_submission, embl_submission
where finished_submission.accession = embl_submission.accession
and embl_submission.accession = \'$hit->{SUBMISSION_info}->[$next]->{acc}\'
and finished_submission.accession = \'$hit->{SUBMISSION_info}->[$next]->{acc}\'
for update nowait
";
		} else {
		 $statement = "
select unfinished_submission.projectname, unfinished_submission.accession, embl_submission.accession, embl_submission.name, embl_submission.length_bp
from unfinished_submission, embl_submission
where unfinished_submission.accession = embl_submission.accession
and embl_submission.accession = \'$hit->{SUBMISSION_info}->[$next]->{acc}\'
and unfinished_submission.accession = \'$hit->{SUBMISSION_info}->[$next]->{acc}\'
for update nowait
";   
		}

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

	    if ($@ =~ /ORA-00054: resource busy and acquire with NOWAIT specified/){
		# No :-( It's already locked
		$lock_fail++;
	    } else {

	    }
	}

	### SUMMARY ###
foreach $next(1 .. ($SUMMARY_counter-1)){
	    eval {
		$statement = "
select ID_SUMMARY, PROJECTNAME, SUMMARYDATE, NUM_CONTIGS, TOTAL_CTG_LEN, NUM_LGE_CONTIGS, LGE_CTG_LEN, NUM_READS, FINISHED_LENGTH_BP, COVERAGE, FINISHED_LENGTH_BP
from project_summary
where PROJECTNAME = \'$hit->{Project_info}->{Projectname}\'
and to_char(project_summary.SUMMARYDATE, 'DD/MM/YYYY HH24-MI-SS') = \'$hit->{SUMMARY_info}->[$next]->{SUMMARYDATE}\'
and NUM_CONTIGS = \'$hit->{SUMMARY_info}->[$next]->{NUM_CONTIGS}\'
and TOTAL_CTG_LEN = \'$hit->{SUMMARY_info}->[$next]->{TOTAL_CTG_LEN}\'
and NUM_LGE_CONTIGS = \'$hit->{SUMMARY_info}->[$next]->{NUM_LGE_CONTIGS}\'
and LGE_CTG_LEN = \'$hit->{SUMMARY_info}->[$next]->{LGE_CTG_LEN}\'
and NUM_READS = \'$hit->{SUMMARY_info}->[$next]->{NUM_READS}\'
and FINISHED_LENGTH_BP = \'$hit->{SUMMARY_info}->[$next]->{FINISHED_LENGTH_BP}\'
and COVERAGE = \'$hit->{SUMMARY_info}->[$next]->{COVERAGE}\'
for update nowait
";

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


	    if ($@ =~ /ORA-00054: resource busy and acquire with NOWAIT specified/){
		# No :-( It's already locked
		$lock_fail++;
	    } else {

	    }
	}

}

    #######################################################################
    # We also need to lock the recorded status info for this project
    #######################################################################
    eval {
	my $statement = "
select PROJECTNAME, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR, REMARK
from project_status
where PROJECTNAME = \'$hit->{Project_info}->{Projectname}\'
for update nowait
";

	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++;
    
    }

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




    ##########################################################
    # ... so if $lock_fail is 1 the lock has failed somewhere
    ##########################################################
    return($lock_fail);
}

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


sub delete_sub {
    my ($hit, $dbh, $sub_current_hit) = @_;

#    warn "Current sub name -> $sub_current_hit \n";

#        # 1 - Name_a    
#        # 2 - Name_b
##--->   # 3 - Name_c  <---- We need to remove this one from the datastruct and shuffle all following entries up >!<
#        # 4 - Name_d
#        # 5 - Name_e
#    
    my $capture = Data::Dumper::Dumper( $hit );
#    warn "THIS DATA ; Inside delete_sub >!< \n";
#    warn  "---------------------------------------------------------------- \n";
#    warn "$capture";

    # We can use $sub_current_hit to cut the array in half :-)
    my @scrag_ends = splice @{$hit->{SUBMISSION_info}}, ($sub_current_hit);
    # Now take the first entry off @scrag_ends (the one we don't want any more)
    my $not_wanted = shift @scrag_ends;    
    # ...and re-make the original array
    push @{$hit->{SUBMISSION_info}}, @scrag_ends;
}


sub delete_sum {
    my ($hit, $dbh, $sum_current_hit) = @_;
    my @scrag_ends = splice @{$hit->{SUMMARY_info}}, ($sum_current_hit);
    my $not_wanted = shift @scrag_ends;    
    push @{$hit->{SUMMARY_info}}, @scrag_ends;
}


sub delete_towards {
    my ($hit, $dbh, $towards_current_hit) = @_;
    # We can use $sub_current_hit to cut the array in half :-)
    my @scrag_ends = splice @{$hit->{FINISHING_DIRECTION_info}}, ($towards_current_hit);
    # Now take the first entry off @scrag_ends (the one we don't want any more)
    my $not_wanted = shift @scrag_ends;    
    # ...and re-make the original array
    push @{$hit->{FINISHING_DIRECTION_info}}, @scrag_ends;
}

sub delete_robot {
    my ($hit, $dbh, $robot_current_hit) = @_;
    # We can use $sub_current_hit to cut the array in half :-)
    my @scrag_ends = splice @{$hit->{ROBOT_PROJECT_info}}, ($robot_current_hit);
    # Now take the first entry off @scrag_ends (the one we don't want any more)
    my $not_wanted = shift @scrag_ends;    
    # ...and re-make the original array
    push @{$hit->{ROBOT_PROJECT_info}}, @scrag_ends;
}

sub delete_cp {
    my ($hit, $dbh, $cp_current_hit) = @_;
    # We can use $sub_current_hit to cut the array in half :-)
    my @scrag_ends = splice @{$hit->{CLONE_PROJECT_info}}, ($cp_current_hit);
    # Now take the first entry off @scrag_ends (the one we don't want any more)
    my $not_wanted = shift @scrag_ends;    
    # ...and re-make the original array
    push @{$hit->{CLONE_PROJECT_info}}, @scrag_ends;
}

sub add_clone {
    my ($hit, $dbh, $clone_current_hit, $value) = @_;
    $hit->{CLONE_PROJECT_info}->[$clone_current_hit] = $value;
}

sub add_towards {
    my ($hit, $dbh, $towards_current_hit, $value) = @_;
    $hit->{FINISHING_DIRECTION_info}->[$towards_current_hit] = $value;
}

sub add_robot {
    my ($hit, $dbh, $robot_current_hit, $value) = @_;
    $hit->{ROBOT_PROJECT_info}->[$robot_current_hit] = $value;
}

sub add_sub {
    my ($hit, $dbh, $sub_current_hit, $a, $b, $c, $d) = @_;

#    $hit->{SUBMISSION_info}->[$sub_current_hit]->{name} = $a;
#    $hit->{SUBMISSION_info}->[$sub_current_hit]->{acc} = $b;
#    $hit->{SUBMISSION_info}->[$sub_current_hit]->{len} = $c;

    $hit->{SUBMISSION_info}->[$sub_current_hit]->{type} = $a;
    $hit->{SUBMISSION_info}->[$sub_current_hit]->{acc} = $b;
    $hit->{SUBMISSION_info}->[$sub_current_hit]->{len} = $c;
    $hit->{SUBMISSION_info}->[$sub_current_hit]->{name} = $d;

}

sub add_sum {

    my ($hit, $dbh, $sum_current_hit, $SUMMARYDATE_var, $NUM_CONTIGS_var, $TOTAL_CTG_LEN_var, $NUM_LGE_CONTIGS_var, $LGE_CTG_LEN_var, $NUM_READS_var, $COVERAGE_var) = @_;

    #############################################################
    #$hit->{SUMMARY_info}->[$sum_current_hit]->{ID_SUMMARY} = $;
    #############################################################

    $hit->{SUMMARY_info}->[$sum_current_hit]->{SUMMARYDATE} = $SUMMARYDATE_var;
    $hit->{SUMMARY_info}->[$sum_current_hit]->{NUM_CONTIGS} = $NUM_CONTIGS_var;
    $hit->{SUMMARY_info}->[$sum_current_hit]->{TOTAL_CTG_LEN} = $TOTAL_CTG_LEN_var;
    $hit->{SUMMARY_info}->[$sum_current_hit]->{NUM_LGE_CONTIGS} = $NUM_LGE_CONTIGS_var;
    $hit->{SUMMARY_info}->[$sum_current_hit]->{LGE_CTG_LEN} = $LGE_CTG_LEN_var;
    $hit->{SUMMARY_info}->[$sum_current_hit]->{NUM_READS} = $NUM_READS_var;
    $hit->{SUMMARY_info}->[$sum_current_hit]->{COVERAGE} = $COVERAGE_var;

}

sub change_checker_assigned {
    my ($hit, $dbh, $value) = @_;
    $hit->{PROJECT_ROLE_info3}->[1] = $value;
}

sub change_finisher_assigned {
    my ($hit, $dbh, $value) = @_;
    $hit->{PROJECT_ROLE_info}->[1] = $value;
}

sub change_prefinisher_assigned {
    my ($hit, $dbh, $value) = @_;
    $hit->{PROJECT_ROLE_info2}->[1] = $value;
}


sub change_team_assigned {
    my ($hit, $dbh, $team, $reason) = @_;
    $hit->{PROJECT_OWNER_info}->[1]->{team} = $team;
    $hit->{PROJECT_OWNER_info}->[1]->{reason} = $reason;
    my $capture = Data::Dumper::Dumper($hit);
#    warn  "---------------------------------%%%%%%%------------------------------- \n";
#    warn "$capture";
}

sub save_changes {

    my ($hit, $dbh, $old_hit, $sessionID, $prog, $who) = @_;
    my @problems = ();

    ########################
    # (-) Handle project info
    ########################
    # Some conversion is required of the entries first -> Project type ->Current status

    ######################
    # status -> id_status
    ######################
    my ($status_id_dict) = convert->project_status2id_dict($dbh, $hit->{Project_info}->{Status});
    #####################
    # project_type -> id
    #####################
    my ($type_id_dict) = convert->type2id_dict($dbh, $hit->{Project_info}->{Project_type});
    #######################################
    # A simple update of the project table
    #######################################

    eval {
	my $statement = "
update project
set
PROJECTNAME = \'$hit->{Project_info}->{Projectname}\', 
FTP_DIRECTORY = \'$hit->{Project_info}->{Ftp_directory}\', 
REMARK = \'$hit->{Project_info}->{Remark}\', 
PROJECT_TYPE = \'$$type_id_dict\'
where PROJECTNAME = \'$hit->{Project_info}->{Projectname}\'
";

#	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, $statement;
	push @problems, $@;
    }

    ###############################
    # (-) Handle any status change
    ###############################
    # Do we actually have a status-change >?<
    my $old_status = $old_hit->{Project_info}->{Status};
    my $new_status = $hit->{Project_info}->{Status};
    my $old_status_remark = $old_hit->{Project_info}->{Status_remark};
    my $new_status_remark = $hit->{Project_info}->{Status_remark};

#    warn "old_status -> $old_status \n";
#    warn "new_status -> $new_status \n";
    
#    warn "old_status_remark -> $old_status_remark \n";
#    warn "new_status_remark -> $new_status_reamrk \n";


    # If we have a new status... we need to have a completely new entry
    # yarg

    if ($new_status ne $old_status){

	my $res = 0;
	if ( ($new_status eq 'Finished') || ($new_status eq 'Shotgun complete') || ($new_status eq 'Half shotgun complete') || ($new_status eq 'Submitted to EMBL') ){
	    my $cs = "
select count (project_status.projectname)
from project_status, projectstatusdict
where project_status.status = projectstatusdict.id_dict
and projectstatusdict.description = \'$new_status\'
and project_status.projectname = \'$hit->{Project_info}->{Projectname}\'
";

	    $sth1 = $dbh->prepare($cs) || die "ERROR:$DBI::errstr";
	    $sth1->execute || die "ERROR:$DBI::errstr";
	    ($res) =  $sth1->fetchrow();
	    $sth1->finish();
	    unless ($res == 0){
		push @problems, "Project $hit->{Project_info}->{Projectname} has already had assigned a status of $new_status !\n";
	    }
	}


	############################################################################
	# New rule; Some project status entries should not be updatable via gullAdd
	############################################################################
	if ($new_status eq 'Selected for prefinishing' || $new_status eq 'Prefinishing complete' || $new_status eq 'Submitted for QC checking'|| $new_status eq 'Selected for auto-prefinishing'|| $new_status eq 'Auto-prefinishing complete'){
	    push @problems, "You cannot assign this status manually !\n";
	}


#
#	 ####################################################################################################################
#	 # New rule; Cannot set assigned to finisher unless autofin complete.
#	 ####################################################################################################################
	 my $OK = 'yes';
	 if ($new_status eq 'Assigned to finisher'|| $new_status eq 'Assigned to prefinisher'){
	    print STDERR "Running new check\n\n";
	     my $autofin = "
select projectname 
from project_status, projectstatusdict
where project_status.status = projectstatusdict.id_dict
and project_status.projectname = \'$hit->{Project_info}->{Projectname}\'
and projectstatusdict.description = 'Selected for auto-prefinishing'
";

	     my $sth1 = $dbh->prepare($autofin) || die "ERROR:$DBI::errstr";
	     $sth1->execute || die "ERROR:$DBI::errstr";
	     ($OK) =  $sth1->fetchrow();
	     $sth1->finish();
	     if ($OK eq $new_status){
	     print STDERR "$OK\n$new_status\n";
		 push @problems, "Cannot manually update project, please select it from the the auto-prefininshing pages!\n";
	     }
	 }
#
##### Disabled by dn1 for S.beck
#
#	 ####################################################################################################################
#	 # New rule; you can only set a status of 'Shotgun complete' if there is already a status of 'Half shotgun complete'.
#	 ####################################################################################################################
	 my $OK = 'yes';
#	 if ($new_status eq 'Shotgun complete'){
#	     my $half_2_comp = "
#select projectname 
#from project_status, projectstatusdict
#where project_status.status = projectstatusdict.id_dict
#and project_status.projectname = \'$hit->{Project_info}->{Projectname}\'
#and projectstatusdict.description = 'Half shotgun complete'
#";
#
#	     my $sth1 = $dbh->prepare($half_2_comp) || die "ERROR:$DBI::errstr";
#	     $sth1->execute || die "ERROR:$DBI::errstr";
#	     ($OK) =  $sth1->fetchrow();
#	     $sth1->finish();
#	     if ($OK eq ''){
#		 push @problems, "Project $hit->{Project_info}->{Projectname} has not yet had a Half shotgun complete status assigned !\n";
#	     }
#	 }
#


#	 ####################################################################################################################
#	 # New rule; you can only set a status of 'Shotgun complete' if there is already a status of 'Assembly Start'.
#	 ####################################################################################################################
	 my $OK = 'yes';
	 if ($new_status eq 'Shotgun complete'){
	     my $ass_start = "
select projectname 
from project_status, projectstatusdict
where project_status.status = projectstatusdict.id_dict
and project_status.projectname = \'$hit->{Project_info}->{Projectname}\'
and projectstatusdict.description = 'Assembly start'
";

	     my $sth1 = $dbh->prepare($ass_start) || die "ERROR:$DBI::errstr";
	     $sth1->execute || die "ERROR:$DBI::errstr";
	     ($OK) =  $sth1->fetchrow();
	     $sth1->finish();
	     if ($OK eq ''){
		 push @problems, "Project $hit->{Project_info}->{Projectname} has not yet had an Assembly Start status assigned !\n";
	     }
	 }





	my $can_state = "
select projectname, projectstatusdict.description
from project_status, projectstatusdict
where project_status.status = projectstatusdict.id_dict
and project_status.projectname = \'$hit->{Project_info}->{Projectname}\'
and 
(projectstatusdict.description = 'Cancelled' or projectstatusdict.description = 'On hold')
and project_status.iscurrent      = 1
";
	my $sth1 = $dbh->prepare($can_state) || die "ERROR:$DBI::errstr";
	$sth1->execute || die "ERROR:$DBI::errstr";
	my ($can_res, $res_desc) =  $sth1->fetchrow();
	$sth1->finish();
	if ($can_res ne ''){
	    push @problems, "Project $hit->{Project_info}->{Projectname} is $res_desc !\n";
	}

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

	if ( ($res == 0) && ($OK ne '') &&($can_res eq '') ){
	    eval {
		$s = "update project_status set ISCURRENT = 0 where projectname = \'$hit->{Project_info}->{Projectname}\' and ISCURRENT = 1";
		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();
	    };
	    
	    if ($@ ne ''){
		push @problems, $s;
		push @problems, $@;
	    }

	    ###########################
	    # ... and make a new entry 
	    ###########################
	    eval {
		$s = "insert into project_status(PROJECTNAME, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR, REMARK) values (\'$hit->{Project_info}->{Projectname}\', \'$$status_id_dict\', sysdate, 1, \'$$sessionID\', \'$prog\', \'$who\', \'$hit->{Project_info}->{Status_remark}\')";
		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();
	    };
	
	    if ($@ ne ''){
		push @problems, $s;
		push @problems, $@;
	    }
	
	} ################## <------------ #end# if new status ne old
    }
    

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



    # If we have just a new remark ... we should just change the remark
    if ( ($new_status eq $old_status) && ($new_status_remark ne $old_status_remark) ){
    
	eval {
	    $s = "
update project_status
set remark = \'$hit->{Project_info}->{Status_remark}\'
where projectname = \'$hit->{Project_info}->{Projectname}\'
and ISCURRENT = 1
";
	    
#	    warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#	    warn "$s\n";
#	    warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";

	    $sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
	    $sth1->execute || die "ERROR:$DBI::errstr";
	    $sth1->finish();
	};

	if ($@ ne ''){
	    push @problems, $s;
	    push @problems, $@;
	}

    } ################## <------------ !

    push @problems, "End";
    return (@problems);
}





sub save_CLONE_PROJECT_changes {

    my ($hit, $dbh, $old_hit, $sessionID, $prog, $who) = @_;
    my @problems = ();
    #############################################
    # (-) Handle multiples of clone name info
    #############################################

    ######################
    # Grab the first list
    ######################
    my @old_clone_names = @{$old_hit->{CLONE_PROJECT_info}};
    #######################
    # Grab the second list
    #######################
    my @new_clone_names = @{$hit->{CLONE_PROJECT_info}};

#    warn "old -> @old_clone_names \n";
#    warn "new -> @new_clone_names \n";

    ########################################################################
    # Go though the first list and see what is missing from the second list
    # -> DELETES
    ########################################################################
    my $next;
    my $next2;
    foreach $next (@old_clone_names){
	my $seen = 0;
	foreach $next2 (@new_clone_names){
#	    warn "Now comparing $next against $next2 \n";
	    if ($next2 eq $next){
		$seen = 1;
	    }
	}
	if ($seen == 0){

	    #############################################
	    # Delete the clone_project entry in question
	    #############################################
	    eval {
		$s = "
delete 
from clone_project 
where CLONENAME = \'$next\' and PROJECTNAME = \'$hit->{Project_info}->{Projectname}\'
";

#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#		warn "$s\n";
#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";

		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();    
	    };

	    if ($@ ne ''){
		push @problems, $s;
		push @problems, $@;
	    }
	}
    }

    #########################################################################
    # Go through the second list and see what is missing from the first list
    # -> INSERTS
    #########################################################################
    my @to_add;
    foreach $next2 (@new_clone_names){
	my $seen = 0;
	foreach $next (@old_clone_names){
#	    warn "Now comparing $next2 against $next \n";
	    if ($next2 eq $next){
		$seen = 1;
#		warn "HIT \n";
	    }
	}
	if ($seen == 0){
#	    warn "adding $next2 \n";
	    push @to_add, $next2;
	}
    }

#    warn "to_add -> @to_add \n";
    foreach $next (@to_add){

	##############################################
	# Check this clonename exists in the db first
	##############################################
	my $check = check->clonename($dbh, $next);

	if ($$check eq 'In database'){
	    ##########################################
	    # Insert the new clonename into the db
	    ##########################################
	    eval {
		$s = "
insert into clone_project
(projectname, clonename)
values (\'$hit->{Project_info}->{Projectname}\', \'$next\')
";
#	    warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#	    warn "$s\n";
#	    warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";

		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();
	    };
	    if ($@ ne ''){
                push @problems, $@;
		push @problems, $@;
	    }    
	} else {
	    push @problems, "Clone $next does not exist in the database";
	}
    }
    
    push @problems, "End";
    return (@problems);
}




sub save_ROBOT_PROJECT_changes {
    my ($hit, $dbh, $old_hit, $sessionID, $prog, $who) = @_;
    my @problems = ();
    #############################################
    # (-) Handle multiples of robot info
    #############################################

    ######################
    # Grab the first list
    ######################
    my @old_robot_names = @{$old_hit->{ROBOT_PROJECT_info}};
    #######################
    # Grab the second list
    #######################
    my @new_robot_names = @{$hit->{ROBOT_PROJECT_info}};

#    warn "old -> @old_robot_names \n";
#    warn "new -> @new_robot_names \n";

    ########################################################################
    # Go though the first list and see what is missing from the second list
    # -> DELETES
    ########################################################################
    my $next;
    my $next2;
    foreach $next (@old_robot_names){
	my $seen = 0;
	foreach $next2 (@new_robot_names){
#	    warn "Now comparing $next against $next2 \n";
	    if ($next2 eq $next){
		$seen = 1;
	    }
	}
	if ($seen == 0){

	    ###################################
	    # Convert the robotname into an id
	    ###################################
	    my $robot_id = convert->robotname2id_dict($dbh, $next);

	    ###########################################################
	    # Update the robot_project entry in question ie. unload it
	    ###########################################################
	    eval {
		$s = "update robot_project set date_unloaded = sysdate where ID_ROBOT = \'$$robot_id\' and PROJECTNAME = \'$hit->{Project_info}->{Projectname}\'";

#	    warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#	    warn "$s\n";
#	    warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";

		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();    
	    };

	    if ($@ ne ''){
		push @problems, $s;
		push @problems, $@;
	    }
	}
    }

    #########################################################################
    # Go through the second list and see what is missing from the first list
    # -> INSERTS
    #########################################################################
    my @to_add;
    foreach $next2 (@new_robot_names){
	my $seen = 0;
	foreach $next (@old_robot_names){
#	    warn "Now comparing $next2 against $next \n";
	    if ($next2 eq $next){
		$seen = 1;
#		warn "HIT \n";
	    }
	}
	if ($seen == 0){
#	    warn "adding $next2 \n";
	    push @to_add, $next2;
	}
    }

#    warn "to_add -> @to_add \n";
    foreach $next (@to_add){

	# We need a check to make sure that we do not already have an entry
	# for this project on this robot. May have been loaded/unloaded already

	# convert name2id
	my $robot_id = convert->robotname2id_dict($dbh, $next);

	# We need a check to make sure that we do not already have an entry
	# for this project on this robot. May have been loaded/unloaded already
	my $robot_check = check->project_on_robot($dbh, $$robot_id, $hit->{Project_info}->{Projectname});

	if ($$robot_check eq 'Not in database'){
	    eval {
		$s = "insert into robot_project (projectname, id_robot, date_loaded) values (\'$hit->{Project_info}->{Projectname}\', \'$$robot_id\', sysdate)";

#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#		warn "$s\n";
#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";

		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();
	    };
	    if ($@ ne ''){
		push @problems, $s;
		push @problems, $@;
	    }    
	} else {
	    push @problems, "There is already a database entry for project $hit->{Project_info}->{Projectname} on robot $next !";
	}
    }

    push @problems, "End";
    return (@problems);
}

sub save_FINISHING_DIRECTION_changes {
    my ($hit, $dbh, $old_hit, $sessionID, $prog, $who) = @_;
    my @problems = ();
    ##############################################
    # (-) Handle multiples of finish towards info
    ##############################################

    ######################
    # Grab the first list
    ######################
    my @old_towards_names = @{$old_hit->{FINISHING_DIRECTION_info}};
    #######################
    # Grab the second list
    #######################
    my @new_towards_names = @{$hit->{FINISHING_DIRECTION_info}};

#    warn "old -> @old_towards_names \n";
#    warn "new -> @new_towards_names \n";

    ########################################################################
    # Go though the first list and see what is missing from the second list
    # -> DELETES
    ########################################################################
    my $next;
    my $next2;
    foreach $next (@old_towards_names){
	my $seen = 0;
	foreach $next2 (@new_towards_names){
#	    warn "Now comparing $next against $next2 \n";
	    if ($next2 eq $next){
		$seen = 1;
	    }
	}
	if ($seen == 0){

	    #############################################
	    # Delete the clone_project entry in question
	    #############################################
	    eval {
		$s = "
delete 
from finishing_direction 
where finish_towards = \'$next\' and PROJECTNAME = \'$hit->{Project_info}->{Projectname}\'
";

#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#		warn "$s\n";
#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";

		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();    
	    };

	    if ($@ ne ''){
		push @problems, $s;
		push @problems, $@;
	    }
	}
    }

    #########################################################################
    # Go through the second list and see what is missing from the first list
    # -> INSERTS
    #########################################################################
    my @to_add;
    foreach $next2 (@new_towards_names){
	my $seen = 0;
	foreach $next (@old_towards_names){
#	    warn "Now comparing $next2 against $next \n";
	    if ($next2 eq $next){
		$seen = 1;
#		warn "HIT \n";
	    }
	}
	if ($seen == 0){
#	    warn "adding $next2 \n";
	    push @to_add, $next2;
	}
    }

#    warn "to_add -> @to_add \n";

    foreach $next (@to_add){

	# Need to check that finish_towards has a valid projectname as an entry first
	my $project_check = check->projectname($dbh, $next);

	if ($$project_check eq 'In database'){
	    eval {
		$s = "insert into finishing_direction (projectname, finish_towards) values (\'$hit->{Project_info}->{Projectname}\', \'$next\')";
		
#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#		warn "$s\n";
#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";

		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();

	    };
	    if ($@ ne ''){
		push @problems, $@;
		push @problems, $@;
	    }
	} else {
	    push @problems, "There is no project database entry for $next !";
	}

	}
    
    push @problems, "End";
    return (@problems);

}

sub save_SUMMARY_changes {
    my ($hit, $dbh, $old_hit, $sessionID, $prog, $who) = @_;
    my @problems = ();

#    my $capture = Data::Dumper::Dumper($hit);
#    warn  "---------------------------------------------------------------- \n";
#    warn "$capture";
#
#    warn "@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ \n";
#
#    $capture = Data::Dumper::Dumper($old_hit);
#    warn  "---------------------------------------------------------------- \n";
#    warn "$capture";

    #############################################
    # (-) Handle multiples of summary info
    #############################################

    ######################
    # Grab the first list
    ######################
    my @old_summary = @{$old_hit->{SUMMARY_info}};

    #######################
    # Grab the second list
    #######################
    my @new_summary = @{$hit->{SUMMARY_info}};

#    warn "old -> @old_summary \n";
#    warn "new -> @new_summary \n";

    ########################################################################
    # Go though the first list and see what is missing from the second list
    # -> DELETES
    ########################################################################
    my $next;
    my $next2;
    foreach $next (@old_summary){

#	warn $next->{ID_SUMMARY};
#	warn $next->{SUMMARYDATE};
#	warn $next->{NUM_CONTIGS};
#	warn $next->{TOTAL_CTG_LEN};
#	warn $next->{NUM_LGE_CONTIGS};
#	warn $next->{LGE_CTG_LEN};
#	warn $next->{NUM_READS};
#	warn $next->{COVERAGE};
#	warn $next->{FINISHED_LENGTH_BP};

	my $seen = 0;
	foreach $next2 (@new_summary){
#	    warn "Now comparing $next against $next2 \n";
	    if ( ($next->{ID_SUMMARY} eq  $next2->{ID_SUMMARY}) && ($next->{SUMMARYDATE} eq  $next2->{SUMMARYDATE}) && ($next->{NUM_CONTIGS} eq  $next2->{NUM_CONTIGS}) && ($next->{TOTAL_CTG_LEN} eq  $next2->{TOTAL_CTG_LEN}) && ($next->{NUM_LGE_CONTIGS} eq  $next2->{NUM_LGE_CONTIGS}) && ($next->{LGE_CTG_LEN} eq  $next2->{LGE_CTG_LEN}) && ($next->{NUM_READS} eq  $next2->{NUM_READS}) && ($next->{COVERAGE} eq  $next2->{COVERAGE}) && ($next->{FINISHED_LENGTH_BP} eq  $next2->{FINISHED_LENGTH_BP}) ){

		$seen = 1;
	    }
	}

	if ($seen == 0){
	    eval {

		$s = "
delete from project_summary 
where ID_SUMMARY =  \'$next->{ID_SUMMARY}\' 
";

#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#		warn "$s\n";
#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";

		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();    
	    };

	    if ($@ ne ''){
		push @problems, $s;
		push @problems, $@;
	    }

	}
    }

    #########################################################################
    # Go through the second list and see what is missing from the first list
    # -> INSERTS
    #########################################################################
    my @to_add;
    foreach $next2 (@new_summary){
	my $seen = 0;
	foreach $next (@old_summary){
#	    warn "Now comparing $next2 against $next \n";
	    if ( ($next2->{ID_SUMMARY} eq  $next->{ID_SUMMARY}) && ($next2->{SUMMARYDATE} eq  $next->{SUMMARYDATE}) && ($next2->{NUM_CONTIGS} eq  $next->{NUM_CONTIGS}) && ($next2->{TOTAL_CTG_LEN} eq  $next->{TOTAL_CTG_LEN}) && ($next2->{NUM_LGE_CONTIGS} eq  $next->{NUM_LGE_CONTIGS}) && ($next2->{LGE_CTG_LEN} eq  $next->{LGE_CTG_LEN}) && ($next2->{NUM_READS} eq  $next->{NUM_READS}) && ($next2->{COVERAGE} eq  $next->{COVERAGE}) && ($next2->{FINISHED_LENGTH_BP} eq  $next->{FINISHED_LENGTH_BP}) ){

		$seen = 1;
#		warn "HIT \n";
	    }
	}


	if ($seen == 0){
#	    warn "adding $next2 \n";
	    push @to_add, $next2;
	}
    }

#    warn "to_add -> @to_add \n";
    foreach $next (@to_add){


	eval {

	    ############################################################
	    # Grab a new value for id_summary from sequence seq_summary
	    ############################################################
	    my $seq_sum = "select seq_summary.nextval from dual";
	    my $ss = $dbh->prepare($seq_sum) || die "ERROR:$DBI::errstr";
	    $ss->execute || die "ERROR:$DBI::errstr";
	    my ($new_ID_SUMMARY) = $ss->fetchrow();
	    $ss->finish();
	    $next->{ID_SUMMARY} = $new_ID_SUMMARY;
#	    warn " @@@@@@@@@@@@@@@@@@@@@@ $new_ID_SUMMARY \n";
#	    warn " @@@@@@@@@@@@@@@@@@@@@@ $hit->{Project_info}->{Projectname} \n";


	    $s = "insert into project_summary (ID_SUMMARY, PROJECTNAME, SUMMARYDATE, NUM_CONTIGS, TOTAL_CTG_LEN, NUM_LGE_CONTIGS, LGE_CTG_LEN, NUM_READS, COVERAGE, FINISHED_LENGTH_BP) values (\'$new_ID_SUMMARY\', \'$hit->{Project_info}->{Projectname}\', sysdate, \'$next->{NUM_CONTIGS}\', \'$next->{TOTAL_CTG_LEN}\', \'$next->{NUM_LGE_CONTIGS}\', \'$next->{LGE_CTG_LEN}\', \'$next->{NUM_READS}\', \'$next->{COVERAGE}\', \'$next->{FINISHED_LENGTH_BP}\')";

#	    warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#	    warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#	    warn "$s\n";
#	    warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#	    warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";

	    $sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
	    $sth1->execute || die "ERROR:$DBI::errstr";
	    $sth1->finish();
	    
	    #########################################################
	    # Grab the new date value for this project summary entry
	    #########################################################
	    my $sum_date = "select to_char(project_summary.SUMMARYDATE, 'DD/MM/YYYY HH24-MI-SS') from project_summary where ID_SUMMARY = \'$new_ID_SUMMARY\'";
	    my $ssd = $dbh->prepare($sum_date) || die "ERROR:$DBI::errstr";
	    $ssd->execute || die "ERROR:$DBI::errstr";
	    $next->{SUMMARYDATE} = $ssd->fetchrow();
	    $ssd->finish();

	};
	if ($@ ne ''){
	    push @problems, $s;
	    push @problems, $@;
	}

    }
    push @problems, "End";
    return (@problems);

}

sub save_SUBMISSION_changes {
    my ($hit, $dbh, $old_hit, $sessionID, $prog, $who) = @_;
    my @problems = ();
    my @old_submission = @{$old_hit->{SUBMISSION_info}};
    my @new_submission = @{$hit->{SUBMISSION_info}};

    ########################################################################
    # Go though the first list and see what is missing from the second list
    # -> DELETES
    ########################################################################
    my $next;
    my $next2;
    foreach $next (@old_submission){

	my $seen = 0;
	foreach $next2 (@new_submission){
	    if ( ($next->{name} eq  $next2->{name}) && ($next->{len} eq  $next2->{len}) && ($next->{acc} eq  $next2->{acc}) && ($next->{type} eq  $next2->{type}) ){
		$seen = 1;
	    }
	}

	if ($seen == 0){
	    eval {
#		$s = "delete from project_submission where embl_name = \'$next->{name}\' and projectname = \'$hit->{Project_info}->{Projectname}\'
#";
		#new# #new# #new# #new# #new# #new# #new# #new# #new# #new# 
		if ($next->{type} eq 'Finished'){
		    $s = "delete from finished_submission where projectname = \'$hit->{Project_info}->{Projectname}\' and accession = \'$next->{acc}\'";
		} else {
		    $s = "delete from unfinished_submission where projectname = \'$hit->{Project_info}->{Projectname}\' and accession = \'$next->{acc}\'"; 
		}
		#new# #new# #new# #new# #new# #new# #new# #new# #new# #new# 

#		warn "!!!!!!!!!!!!!!!!!!!!!!!!!! \n $s \n";

		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();    
	    };

	    if ($@ ne ''){
		push @problems, $s;
		push @problems, $@;
	    }

	    eval {

#		$s = "delete from submission where embl_name = \'$next->{name}\' and primary_embl_acc = \'$next->{acc}\' and finished_length = \'$next->{len}\'";

                #new# #new# #new# #new# #new# #new# #new# #new# #new# #new# 
		if ($next->{len} ne ''){
		    $s = "delete from embl_submission where accession = \'$next->{acc}\' and name = \'$next->{name}\' and length_bp = \'$next->{len}\'";
		} else {
		    $s = "delete from embl_submission where accession = \'$next->{acc}\' and name = \'$next->{name}\' and length_bp is NULL"
		}
		#new# #new# #new# #new# #new# #new# #new# #new# #new# #new# 

#		warn "!!!!!!!!!!!!!!!!!!!!!!!!!! \n $s \n";

		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();    
	    };

	    if ($@ ne ''){
		push @problems, $s;
		push @problems, $@;
	    }
	}
    }

    #########################################################################
    # Go through the second list and see what is missing from the first list
    # -> INSERTS
    #########################################################################
    my @to_add;
    foreach $next2 (@new_submission){
	my $seen = 0;
	foreach $next (@old_submission){
	    if ( ($next2->{name} eq  $next->{name}) && ($next2->{len} eq  $next->{len}) && ($next2->{acc} eq  $next->{acc}) && ($next2->{type} eq  $next->{type}) ){
		$seen = 1;
	    }
	}


	if ($seen == 0){
	    push @to_add, $next2;
	}
    }

    foreach $next (@to_add){

	eval {
#	    $s = "insert into submission (embl_name, primary_embl_acc, finished_length) values (\'$next->{name}\', \'$next->{acc}\', \'$next->{len}\')";

	    #new# #new# #new# #new# #new# #new# #new# #new# #new# #new#
	    $s = "insert into embl_submission (name, accession, length_bp) values (\'$next->{name}\', \'$next->{acc}\', \'$next->{len}\')";
	    #new# #new# #new# #new# #new# #new# #new# #new# #new# #new#
#	    warn "!!!!!!!!!!!!!!!!!!!!!!!!!! \n $s \n";

	    $sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
	    $sth1->execute || die "ERROR:$DBI::errstr";
	    $sth1->finish();
	};
	if ($@ ne ''){
	    push @problems, $s;
	    push @problems, $@;
	}  

	##############################################################################
	# we will need to make a project_submission entry unless there already is one
	##############################################################################
	my $project_submission_check = check->project_submission_exists($dbh, $hit->{Project_info}->{Projectname}, $next->{acc}, $next->{type});
	
#	warn $$project_submission_check;

	if ($$project_submission_check eq 'Not in database'){
	    # If there isnt a bridge-entry -> MAKE ONE !
	    eval {
#		$s = "insert into project_submission values(\'$hit->{Project_info}->{Projectname}\', \'$next->{name}\')";
                #new# #new# #new# #new# #new# #new# #new# #new# #new# #new#
		if ($next->{type} eq 'Finished'){
		    $s = "insert into finished_submission (projectname, accession) values(\'$hit->{Project_info}->{Projectname}\', \'$next->{acc}\')";
		} else {
		    $s = "insert into unfinished_submission (projectname, accession) values(\'$hit->{Project_info}->{Projectname}\', \'$next->{acc}\')";
		}
		#new# #new# #new# #new# #new# #new# #new# #new# #new# #new#
#		warn "!!!!!!!!!!!!!!!!!!!!!!!!!! \n $s \n";


		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();
	    };
	    if ($@ ne ''){
                push @problems, $s;
		push @problems, $@;
	    }    
	}
    }
    push @problems, "End";
    return (@problems);
}


sub save_PROJECT_OWNER_changes {

    my ($hit, $dbh, $old_hit, $sessionID, $prog, $who) = @_;
    my @problems = ();

    ######################
    # Grab the first list
    ######################
    my $old_team = $old_hit->{PROJECT_OWNER_info}->[1]->{team};
    my $old_reason = $old_hit->{PROJECT_OWNER_info}->[1]->{reason};
    #######################
    # Grab the second list
    #######################
    my $new_team = $hit->{PROJECT_OWNER_info}->[1]->{team};
    my $new_reason = $hit->{PROJECT_OWNER_info}->[1]->{reason};

    if ( ($old_team ne $new_team) || ($old_reason ne $new_reason) ) {

	my $new_reason_id = convert->teamor2id_dict($dbh, $new_reason);
	

	# Is there a previous entry we have to change ?
	my $s = "
select projectname
from project_owner
where projectname = \'$hit->{Project_info}->{Projectname}\'
and iscurrent = 1
";
	
	$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
	$sth1->execute || die "ERROR:$DBI::errstr";
	my $owner_check = $sth1->fetchrow();
	$sth1->finish();

	if ($owner_check){
	    eval {
		$s = "
update project_owner
set iscurrent = 0, owned_to = sysdate
where projectname = \'$hit->{Project_info}->{Projectname}\'
and iscurrent = 1
";
	
	    $sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
	    $sth1->execute || die "ERROR:$DBI::errstr";
	    $sth1->finish();
	    };

	    if ($@ ne ''){
		push @problems, $s;
		push @problems, $@;
	    }

	}
	my $new_reason_id = convert->teamor2id_dict($dbh, $new_reason);
	eval {
	    $s = "
insert into project_owner 
(projectname, teamname, owned_from, owner_type, iscurrent)
values (\'$hit->{Project_info}->{Projectname}\', \'$new_team\', sysdate, \'$$new_reason_id\', 1)
";


		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();    
	    };

	    if ($@ ne ''){
		push @problems, $s;
		push @problems, $@;
	    }
    }
    
    push @problems, "End";
    return (@problems);
}

sub save_PROJECT_ROLE_changes {
    my ($hit, $dbh, $old_hit, $sessionID, $prog, $who) = @_;
    my @problems = ();

    ######################
    # Grab the first list
    ######################
    my $old_fin = $old_hit->{PROJECT_ROLE_info}->[1];
    #######################
    # Grab the second list
    #######################
    my $new_fin = $hit->{PROJECT_ROLE_info}->[1];

#    warn "old -> $old_fin \n";
#    warn "new -> $new_fin \n";

    if ($old_fin ne $new_fin){

	########################################
	# convert $new_fin into an id (id_role)
	########################################
	
	my ($email,$team) = split (/ - /,$new_fin);
	print STDERR "email =$email.\nteam =$team.\n";
	#my $fin_id = convert->email2id_role($dbh, $new_fin);

    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.teamname = \'$team\'
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 $fin_id = $sth->fetchrow();
    $sth->finish;

print STDERR "email $email:team $team:role $fin_id\n";

	eval {
		$s = "
insert into project_role 
(projectname, id_role, ASSIGNED_FROM)
values (\'$hit->{Project_info}->{Projectname}\', \'$fin_id\', sysdate)
";

#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#		warn "$s\n";
#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";

		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();    
	    };

	    if ($@ ne ''){
		push @problems, $s;
		push @problems, $@;
	    }
    }
    
    push @problems, "End";
    return (@problems);
}

#####
#####

sub save_PROJECT_ROLE2_changes {
    my ($hit, $dbh, $old_hit, $sessionID, $prog, $who) = @_;
    my @problems = ();

    ######################
    # Grab the first list
    ######################
    my $old_pfin = $old_hit->{PROJECT_ROLE_info2}->[1];
    #######################
    # Grab the second list
    #######################
    my $new_pfin = $hit->{PROJECT_ROLE_info2}->[1];

    if ($old_pfin ne $new_pfin){

	########################################
	# convert $new_fin into an id (id_role)
	########################################
	my $pfin_id = convert->email2id_rolepfin($dbh, $new_pfin);
	my $pfin = $$pfin_id;

	eval {
		$s = "
insert into project_role 
(projectname, id_role, ASSIGNED_FROM)
values (\'$hit->{Project_info}->{Projectname}\', \'$pfin\', sysdate)
";

#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#		warn "$s\n";
#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";

		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();    
	    };

	    if ($@ ne ''){
		push @problems, $s;
		push @problems, $@;
	    }
    }
    
    push @problems, "End";
    return (@problems);
}

sub save_PROJECT_ROLE3_changes {
#    warn "Inside here @@@@@@@@@@@@@@@@@@@ \n";
    my ($hit, $dbh, $old_hit, $sessionID, $prog, $who) = @_;
    my @problems = ();


    #got here !
    #my $capture = Data::Dumper::Dumper($hit);
    #warn "THIS DATA ; $capture\n";


    ######################
    # Grab the first list
    ######################
    my $old_check = $old_hit->{PROJECT_ROLE_info3}->[1];
    #######################
    # Grab the second list
    #######################
    my $new_check = $hit->{PROJECT_ROLE_info3}->[1];

    if ($old_check ne $new_check){

	########################################
	# convert $new_fin into an id (id_role)
	########################################
	my $check_id = convert->email2id_rolecheck($dbh, $new_check);
#	warn "-----------> $$fin_id \n";
	my $check = $$check_id;
	

	eval {
		$s = "
insert into project_role 
(projectname, id_role, ASSIGNED_FROM)
values (\'$hit->{Project_info}->{Projectname}\', \'$check\', sysdate)
";

#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";
#		warn "$s\n";
#		warn "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n";

		$sth1 = $dbh->prepare($s) || die "ERROR:$DBI::errstr";
		$sth1->execute || die "ERROR:$DBI::errstr";
		$sth1->finish();    
	    };

	    if ($@ ne ''){
		push @problems, $s;
		push @problems, $@;
	    }
    }
    
    push @problems, "End";
    return (@problems);
}


#####
#####

package project_history;

sub lock_data {
    my ($self, $hit, $dbh) = @_;
    my $lock_fail = 0;
    eval {

	my $statement = "
select PROJECTNAME, STATUS, STATUSDATE, ISCURRENT, SESSIONID, PROGRAM, OPERATOR, REMARK
from project_status
where PROJECTNAME = \'$hit\'
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);
}

1;







##



