#!/usr/local/bin/perl -w
#
# DATABASE routines for the autofinishing cgi pages AND CODE.
#
# Ben Tubby.

use strict;
use WrapDBI;

#-----------------------------------------------------------
sub OracleConnect {
    my ($oracle_user, 
        $dev,
        ) = @_;
    my $dbh = WrapDBI->connect($oracle_user, {RaiseError => 1, AutoCommit=> 0} );
    die "Could not connect to database" if (!defined $dbh);
    return ($dbh);
}
#-----------------------------------------------------------
sub Return_pathogen_from_project{
    my ($dbh, 
        $project,
        ) = @_;
    my $pathogen_name;
    eval{
        ($pathogen_name) = $dbh->selectrow_array(qq[ 
                                                     select distinct c.speciesname
                                                     from clone c, clone_project cp
                                                     where c.clonename = cp.clonename
                                                     and   cp.projectname = ?
                                                     ], {}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return $pathogen_name;
}
#-----------------------------------------------------------
sub Get_ordered_samples_from_project{
    my ($dbh, $project) = @_;
    my ($ref, $sql);
    my @samples;
    eval{
        $ref = $dbh->selectall_arrayref(qq[ 
                                            select fr.PLATENAME, fr.ROWLETTER, fr.COLNUM,  sc.SUFFIX, fr.ITERATION, p.PRIMERNAME
                                            from  finish_batch fb, fin_finservice fs, Autofinishdict afd, frequest_status frs,
                                            finish_request fr,  SEQCHEM_SEQPROTOCOL ssp, SEQCHEMISTRY sc, PRIMER p
                                            where fb.id_team_finserv = fs.id_team_finserv
                                            and   fb.id_team_finserv in (
                                                                         select id_team_finserv
                                                                         from fin_finservice
                                                                         where finmethod in ('Robot', 'Auto')
                                                                         )
                                            and fb.id_auto    != '999999'   
                                            and fb.id_auto     = afd.id_dict
                                            and fb.projectname = ?
                                            AND fr.ID_SCSP     = ssp.ID_SCSP
                                            AND ssp.ID_SEQCHEM = sc.ID_SEQCHEM
                                            AND fr.ID_PRIMER   = p.ID_PRIMER(+)
                                            and fb.id_fbatch   = fr.id_fbatch
                                            and fr.id_frequest = frs.id_frequest
                                            and frs.status    != 6
                                            and frs.iscurrent  = 1
                                            ], {}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    foreach (@{$ref}){
        my ($platename, 
            $platerow, 
            $platecol, 
            $suffix, 
            $iteration, 
            $primername,
            ) = @{$_};
        $iteration     = "" if (!defined $iteration);
        $primername    = "" if (!defined $primername);
        if ($platecol < 10){
            $platecol = "0".$platecol;
        }
        my $real_name = $platename.
                        $platerow.
                        $platecol.
                        $suffix.
                        $iteration.
                        $primername;
        push @samples, $real_name;
    }
    return (\@samples);
}
#-----------------------------------------------------------
sub Get_no_primers_ordered{
    my ($dbh, $project) = @_;
        my $oligo_count;
    eval{
        ($oligo_count) = $dbh->selectrow_array(qq[ 
                                                   select count (distinct fr.id_primer)
                                                   from finish_request fr, finish_batch fb
                                                   where fb.projectname = ?
                                                   and fb.id_fbatch     = fr.id_fbatch
                                                   ], {}, $project);
    };
    warn "ORACLE ERROR: $@\n" if ($@);
    return ($oligo_count);
}
#-----------------------------------------------------------
sub Get_db_project_type{
    my ($dbh, $project) = @_;
    my $type;
    eval{
        ($type) = $dbh->selectrow_array(qq[ 
                                            select rpt.description
                                            from   project p, repositorytypedict rpt
                                            where  p.projectname  = ?
                                            and    p.project_type = rpt.id_dict
                                            ], {}, $project);
    };
    warn "ORACLE ERROR: $@\n" if ($@);
    return ($type);
}
#-----------------------------------------------------------
sub Get_project_contig_info{
    # called from project selection page to show no. contigs
    my ($dbh, $project) = @_;
    my ($date, $contigs, $reads);
    eval{
        ($date, $contigs, $reads) = $dbh->selectrow_array(qq[ 
                                                              select summarydate, num_lge_contigs, NUM_READS
                                                              from   project_summary 
                                                              where  projectname  = ?
                                                              and summarydate = (
                                                                                 select max(summarydate)
                                                                                 from project_summary
                                                                                 where  projectname  = ?
                                                                                 )
                                                              ], {}, $project, $project);
    };
    warn "ORACLE ERROR: $@\n" if ($@);
    return ($date, 
            $contigs, 
            $reads,
            );
}
#-----------------------------------------------------------
sub Get_project_phrap_details{
    # called from project selection page
    my ($dbh, $project) = @_;
    my ($date, $ins_size, $Q20);
    eval{
        ($date, $ins_size, $Q20)= $dbh->selectrow_array(qq[
                                                           select PHRAP_DATE, 
                                                           EST_INS_SIZE_KB, 
                                                           Q20_VAL_1KB 
                                                           from   project_phrap 
                                                           where  PROJECTNAME = ?
                                                           ], {}, $project);
    };
    warn "ORACLE ERROR: $@\n" if ($@);
    return ($date, 
            $ins_size, 
            $Q20,
            );
}
#-----------------------------------------------------------
sub Get_db_autofin_project_info{
    my ($dbh, $selection) = @_;
    my $ref;
    eval{
        if ($selection eq 'all'){
            $ref = $dbh->selectall_arrayref(qq[ 
                                                select afd.camp, fb.id_fbatch, fb.batchdate, fb.projectname, 
                                                fs.finmethod, fs.fsname, fs.team
                                                from finish_batch fb, fin_finservice fs, Autofinishdict afd
                                                where fb.id_team_finserv = fs.id_team_finserv
                                                and fb.id_team_finserv in (
                                                                           select id_team_finserv
                                                                           from fin_finservice
                                                                           where finmethod in ('Robot', 'Auto')
                                                                           )
                                                and fb.id_auto != '999999'   
                                                and fb.id_auto = afd.id_dict
                                                and fb.projectname not in ('PROJECT2', 'proj1')
                                                order by fb.batchdate desc
                                                ], {});
        }elsif ($selection eq 'current'){
            # Get only the cloes with a current 'Selected for pre-fin' project_status 
            $ref = $dbh->selectall_arrayref(qq[
                                               select afd.camp, fb.id_fbatch, fb.batchdate, fb.projectname, 
                                               fs.finmethod, fs.fsname, fs.team
                                               from finish_batch fb, fin_finservice fs, Autofinishdict afd, project_status ps
                                               where fb.id_team_finserv = fs.id_team_finserv
                                               and fb.id_team_finserv in (
                                                                          select id_team_finserv
                                                                          from fin_finservice
                                                                          where finmethod in ('Robot', 'Auto')
                                                                          )
                                               and fb.id_auto != '999999'   
                                               and fb.id_auto = afd.id_dict
                                               and fb.projectname not in ('PROJECT2', 'proj1')
                                               and fb.projectname = ps.projectname
                                               and ps.status = 36
                                               and ps.iscurrent = 1
                                               order by fb.batchdate desc
                                               ], {});
        }
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return ($ref);
}
#-----------------------------------------------------------
sub Get_clones_with_specific_projectstatus{
    my ($dbh, $status) = @_;
    my ($ref, $sql);
    eval{
        $sql = "select ps.projectname, 
                       ps.status, 
                       ps.statusdate
                from   project_status ps,
                       project_finishingproblem pfpr
                where  ps.status         = 36 
                and    ps.iscurrent      = 1
                and    ps.projectname    = pfpr.projectname
                and    pfpr.problem_type = 1
                and    not exists (
                                   select id_finprocess
                                   from   project_finishingprocess
                                   where  projectname = ps.projectname
                                  )";
        ($ref) = $dbh->selectall_arrayref(qq[$sql]);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return ($sql, 
            $ref,
            );
}
#-----------------------------------------------------------
sub WGS_check_pass_or_fail{
    my ($dbh, 
        $projectname, 
        $process_type,
        ) = @_;
    my $result;
    
    eval{
        $result = $dbh->selectrow_array(qq[ 
                                            select id_finprocess
                                            from   project_finishingprocess
                                            where  projectname  = ?
                                            and    process_type = ?
                                            ], {}, $projectname, $process_type);
    };
    warn "ORACLE ERROR: $@\n" if ($@);
    return ($result);
}
#-----------------------------------------------------------
sub Get_available_clones_for_autofin{
    my ($dbh, 
        $status, 
        $species, 
        $mouse_chromosome, 
        $view_full_shotgun_clones,
        ) = @_;
    my ($ref, $sql);
    eval{
        if ($species eq "human"){
            $sql = "select ps.projectname, c.chromosome, ps.statusdate, 
                ps.operator, psd.description, c.SPECIESNAME
                from project_status ps, clone c, projectstatusdict psd,
                clone_priority cp 
                where    ps.status      = $status
                and      ps.iscurrent   = 1
                and      c.clonename    = ps.projectname
                and      c.chromosome   in (1,9,10)
                and      c.clonename    = cp.clonename
                and      cp.priority    in (1,2)
                and      cp.is_current  = 1
                and      ps.status      = psd.id_dict
                and c.speciesname = 'Human'";
        }elsif ($species eq "mouse"){
            my $extra = "";
            if ($view_full_shotgun_clones){
                $extra = "              and lower(ps.remark) = 'full'";  
            }else{
                $extra = "and (lower(ps.remark) != 'full' or ps.remark is NULL)";
            }
            $sql = "select ps.projectname, csd.description, ps.statusdate, 
                           ps.operator, psd.description, c.SPECIESNAME
                    from   project_status ps, clone c, projectstatusdict psd,
                           clone_priority cp,  chromosomedict csd
                    where  ps.status      = 15
                    and    ps.iscurrent   = 1
                    and    csd.description = 'Chromosome $mouse_chromosome'
                    and    c.clonename    = ps.projectname
                    and    c.clonename    = cp.clonename
                    and    c.chromosome   = csd.id_dict
                    and    cp.is_current  = 1
                    and    ps.status      = psd.id_dict
                    and    cp.priority    in (1,2)
                    and    c.speciesname = 'Mouse'
                    $extra";
        }elsif ($species eq "zebrafish"){
            # priority for zebrafish clones removed at request of sm2 23/08/02
            #and    cp.priority    in (0,1,2)
            $sql = "select ps.projectname, csd.description, ps.statusdate, 
                    ps.operator, psd.description, c.SPECIESNAME
                    from   project_status ps, clone c, projectstatusdict psd,
                    clone_priority cp,  chromosomedict csd
                    where  ps.status      = 15
                    and    ps.iscurrent   = 1
                    and    c.clonename    = ps.projectname
                    and    c.clonename    = cp.clonename
                    and    c.chromosome   = csd.id_dict
                    and    cp.is_current  = 1
                    and    ps.status      = psd.id_dict
                    and    c.speciesname  = 'Zebrafish'";
        }elsif ($species eq 'all'){
            $sql = "select ps.projectname, csd.description, ps.statusdate, 
                           ps.operator, psd.description, c.SPECIESNAME
                    from   project_status ps, clone c, projectstatusdict psd,
                           clone_priority cp,  chromosomedict csd
                    where  ps.status      = 15
                    and    ps.iscurrent   = 1
                    and    c.clonename    = ps.projectname
                    and    c.clonename    = cp.clonename
                    and    c.chromosome   = csd.id_dict
                    and    cp.is_current  = 1
                    and    ps.status      = psd.id_dict
                    order by c.SPECIESNAME,  c.chromosome";
        }else{
            print "UN-IMPLEMENTED SPECIES '$species'....";
            return;
        }
        ($ref) = $dbh->selectall_arrayref(qq[$sql]);
    }; 
    die "ORACLE ERROR: $@\n" if ($@);
    return ($sql, 
            $ref,
            );
}
#-----------------------------------------------------------
sub Get_available_to_finishers_pool{
    my ($dbh) = @_;
    # UNUSED!!!!!!!
    my ($ref, $sql);
    eval{
        $sql = "
                select ps.projectname, c.chromosome, ps.statusdate, 
                       ps.operator, psd.description, c.SPECIESNAME, ctd.description
                from   project_status ps, clone c, projectstatusdict psd, clonetypedict ctd
                where  ps.status      = 37
                and    ps.iscurrent   = 1
                and    ps.projectname = c.clonename
                and    ps.status      = psd.id_dict
                and    c.clone_type   = ctd.id_dict
                order  by ps.statusdate";
        ($ref) = $dbh->selectall_arrayref(qq[$sql]);
    }; 
    die "ORACLE ERROR: $@\n" if ($@);
    return ($sql, 
            $ref,
            );
}
#-----------------------------------------------------------
sub Get_chromosome_and_species{
    my ($dbh, $project) = @_;
    # called from finishing_team web selection page
    my ($chr, $species);
    
    # select c.chromosome, c.speciesname
    # from   clone_project cp, clone c
    # where  cp.projectname = ?
    # and    c.clonename = cp.clonename

    eval{
        ($chr, $species) = $dbh->selectrow_array(qq[ 
                                                     select csd.description, c.speciesname
                                                     from   clone_project cp, clone c, chromosomedict csd 
                                                     where  cp.projectname = ?
                                                     and    c.clonename    = cp.clonename
                                                     and    c.chromosome   = csd.id_dict (+)
                                                     ], {}, $project);
    };

    $chr     = '?' unless (defined $chr);
    $species = '?' unless (defined $species);

    warn "ORACLE ERROR: $@\n" if ($@);
    my ($c, $chr_digit) = split " ", $chr;
    return ($chr_digit, 
            $species,
            );
}
#-----------------------------------------------------------
sub get_current_project_status{
    my ($dbh, $projectname) = @_;
    # called from control centre when asking for historical clones... + from finishing teams interface
    my ($statusdate, $status, $operator);
    eval{
        my $sql = "select psd.description, ps.statusdate, ps.operator
                   from  project_status ps, projectstatusdict psd
                   where ps.projectname = '$projectname'
                   and   ps.iscurrent   = 1
                   and   ps.status = psd.id_dict";
        
        ($status, 
         $statusdate, 
         $operator,
         ) = $dbh->fetch_row(qq[$sql]);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return ($status, 
            $statusdate, 
            $operator,
            );
}
#-----------------------------------------------------------
sub get_project_status_info{
    my ($dbh, 
        $projectname, 
        $status,
        ) = @_;
    my ($statusdate, $iscurrent, $operator);
     eval{
        my $sql = "select statusdate, iscurrent, operator
                   from project_status
                   where projectname = '$projectname'
                   and status        = $status
                   order by statusdate";
        
        ($statusdate, 
         $iscurrent, 
         $operator,
         ) = $dbh->fetch_row(qq[$sql]);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return ($statusdate, 
            $iscurrent, 
            $operator,
            );
}
#-----------------------------------------------------------
sub get_current_project_status_info{
    my ($dbh, $projectname) = @_;
    my ($statusdate, $status, $operator);
    eval{
        my $sql = "select ps.statusdate, psd.description, ps.operator
                   from   project_status ps, projectstatusdict psd 
                   where  ps.projectname = '$projectname'
                   and    ps.iscurrent   = 1
                   and    ps.status      = psd.id_dict";
        
        ($statusdate, 
         $status, 
         $operator,
         ) = $dbh->fetch_row(qq[$sql]);    
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return ($statusdate, 
            $status, 
            $operator,
            );
}
#-----------------------------------------------------------
sub get_capminder_run_from_samplename{
    my ($dbh, 
        $samplename,
        ) = @_;
    my $gelfilename;
    eval{
        my $sql = "select a.gelfilename
                   from gelfile a, seqread b
                   where b.name = '$samplename'
                   and b.id_gel = a.id_gel";
        $gelfilename = $dbh->fetch_scalar(qq[$sql]);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return ($gelfilename);
}
#-----------------------------------------------------------
sub get_next_free_primer{
    my ($dbh, $project) = @_;
    my $primer;
    eval{
        $primer = $dbh->fetch_scalar(qq[
                                        select NEXT_VAL
                                        from autoprimername
                                        where projectname = '$project'
                                        ]);
    };  
    die "ORACLE ERROR: $@\n" if ($@);
    return ($primer);
}
#-----------------------------------------------------------
sub delete_from_autoprimername{
    my ($dbh, $project) = @_;
    #bt1 28/03/02
    eval{
        print STDERR "Deleting previous autoprimername entrys for project $project...\n";
        $dbh->do(qq[
                    delete from autoprimername
                    where projectname = ?
                    ],{}, $project);
    };
    if ($@){
        $dbh->rollback;
        warn  "Unable to commit to Oracle!\n$@";
    }else{
        $dbh->commit;
    } 
}
#-----------------------------------------------------------
sub Identify_overlaps{
    my ($dbh, $project) = @_;
    my ($ref1, $ref2);
    my %overlapping_projects;
    eval{
        $ref1 = $dbh->selectall_arrayref(qq[
                                            select  co.OVERLAPS_CLONE, co.remark
                                            from    clone_overlaps co, clone_project cp
                                            where   cp.projectname  = ?
                                            and     cp.projectname  = co.clonename
                                            and     co.CONFIRMATION = 1
                                            ], {}, $project);
        
        $ref2 = $dbh->selectall_arrayref(qq[
                                            select  co.clonename, co.remark
                                            from    clone_overlaps co, clone_project cp
                                            where   cp.projectname =?
                                            and     cp.projectname = co.OVERLAPS_CLONE
                                            and     co.CONFIRMATION = 1
                                            ], {}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    foreach ($ref1, $ref2){
        foreach (@{$_}){
            my ($overlap, $remark) = @{$_};
            #print "\nIdentified $overlap ($remark)";
            $overlapping_projects{$overlap} =  $remark;
        }
    }
    return \%overlapping_projects;
}
#-----------------------------------------------------------
sub Get_EMBL_accession_no{
    my ($dbh, $project) = @_;
    
    my ($accession);
    eval{
        ($accession) = $dbh->selectrow_array(qq[ 
                                                 select ACCESSION 
                                                 from unfinished_submission
                                                 where projectname = ?
                                                 ],{}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return ($accession);
}
#-----------------------------------------------------------
sub Has_clone_been_finished{
    my ($dbh, $project) = @_;
    my ($date, $op, $status);
    eval{
        ($date, 
         $op, 
         $status,
         ) = $dbh->selectrow_array(qq[ 
                                       select ps.STATUSDATE, ps.OPERATOR, psd.description 
                                       from project_status ps, 
                                       projectstatusdict psd
                                       where ps.projectname  = ?
                                       and   psd.description = 'Finished'
                                       and   ps.status       = psd.id_dict
                                       ],{}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return ($date, 
            $op, 
            $status);
}
#-----------------------------------------------------------
sub Update_project_status{
    my ($dbh, 
        $project, 
        $new_status, 
        $user, 
        $program,
        ) = @_;
    $program = "" if (! $program);
    eval{
        $dbh->do(qq[
                    update project_status
                    set    iscurrent      = 0
                    where  iscurrent      = 1
                    and    projectname    = ? 
                    ], {}, $project);    
    
        if (! $user){
            
            $dbh->do(qq[
                        insert into project_status
                        (PROJECTNAME, STATUS, STATUSDATE, ISCURRENT)
                        values (?, ?, sysdate, 1)
                        ], {}, $project, $new_status);    
        }else{
            
            $dbh->do(qq[
                        insert into project_status
                        (PROJECTNAME, STATUS, STATUSDATE, ISCURRENT, OPERATOR, PROGRAM)
                        values (?, ?, sysdate, 1, ?, ?)
                        ], {}, $project, $new_status, $user, $program);    
            
        }
    };
    die "ORACLE ERROR: $@\n" if ($@);
}
#-----------------------------------------------------------
sub Update_project_owner{
    my ($dbh, 
        $project, 
        $new_owner, 
        $projectownerdict_entry,
        ) = @_;
    # Used by finishing team selection web interface bt1
    eval{
        $dbh->do(qq[
                    update project_owner 
                    set    iscurrent   = 0
                    where  iscurrent   = 1
                    and    projectname = ? 
                    ], {}, $project);    
        
        $dbh->do(qq[
                    insert into project_owner
                    (PROJECTNAME, TEAMNAME, OWNED_FROM, OWNER_TYPE, ISCURRENT)
                    values (?, ?, sysdate, ?, 1)
                    ], {}, $project, $new_owner, $projectownerdict_entry);    
        
    };
    die "ORACLE ERROR: $@\n" if ($@);
}
#-----------------------------------------------------------
sub Add_new_prefinishing_problem{
    my ($dbh, 
        $project, 
        $comment,
        ) = @_;
    
    $comment = '' unless (defined $comment);
    eval{
        $dbh->do(qq[
                    insert into project_finishingproblem
                    (       ID_FINPROBLEM,          PROJECTNAME, PROBLEM_TYPE,  REGION_TYPE,  ENTRY_DATE, RESOLVED_DATE, REMARK,    IS_RESOLVED)
                    values (seq_finproblem.nextval, ?,           1,             NULL,         sysdate,    NULL,           '$comment',  0)
                    ], {}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
}
#-----------------------------------------------------------
sub Add_new_prefinishing_problem2{
    # new version to undate new PRIORITY field
    my ($dbh, 
        $project, 
        $comment, 
        $priority,
        ) = @_;
    $comment = '' unless (defined $comment);
    $priority = 0 unless (defined $priority);
    eval{
        $dbh->do(qq[
                    insert into project_finishingproblem
                    (       ID_FINPROBLEM,          PROJECTNAME, PROBLEM_TYPE,  REGION_TYPE,  ENTRY_DATE, RESOLVED_DATE, REMARK,    IS_RESOLVED, PRIORITY)
                    values (seq_finproblem.nextval, ?,           1,             NULL,         sysdate,    NULL,           '$comment',  0,        $priority)
                    ], {}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
}
#-----------------------------------------------------------
sub Update_prefinishing_problem_to_resolved{
    my ($dbh, 
        $project,
        ) = @_;
    # Called from re-assembly cron, to update the finishing problem to resolved.
    my $error = 0;
    eval{
        my $id_finproblem = $dbh->fetch_scalar(qq[
                                                  select id_finproblem
                                                  from   project_finishingproblem
                                                  where  projectname = '$project'
                                                  and is_resolved    = 0
                                                  ]);
        if ($id_finproblem){
            $dbh->do(qq[
                        update project_finishingproblem
                        set is_resolved   = 1,
                        resolved_date     = sysdate
                        where projectname = ?
                        and id_finproblem = ?
                        ],{}, $project, $id_finproblem);
        }else{
            print "ERROR! can't identify id_finproblem for autoprefin 'problem'<br>".
                  "This is probably a pre-finishing problem clone. ok, skipping...";
            $error = 1;
        }
    };
    if (($@) or ($error)){
        $dbh->rollback;
        warn  "Unable to commit to Oracle!\n$@";
    }else{
        $dbh->commit;
        return;
    }
}
#-----------------------------------------------------------
sub Update_infinishing_project_status{
    my ($dbh, 
        $project, 
        $finishingprocess_id_dict,
        ) = @_;
    my $error = 0;
    $error = &Update_project_finishingprocess_iscurrent($dbh, $project, 0);
    eval{
        # Insert new row (new ATTEMPT to solve PROBLEM of 'prefinishing')...
        my $id_finproblem = $dbh->fetch_scalar(qq[
                                                  select id_finproblem
                                                  from   project_finishingproblem
                                                  where  projectname = '$project'
                                                  ]);
        if ($id_finproblem){
            #print "- Inserting new row for '$project' (id_finproblem = >$id_finproblem<) ...<br>";
            $dbh->do(qq[
                        insert into project_finishingprocess
                        (ID_FINPROCESS,                 PROJECTNAME, STATUSDATE, PROCESS_TYPE, ISCURRENT, ID_FINPROBLEM)
                        values (seq_finprocess.nextval, ?,           sysdate,    ?,            1,         ?)
                        ],{}, $project, $finishingprocess_id_dict, $id_finproblem);
        }else{
            print "ERROR! can't identify id_finproblem for $project...";
            $error = 1;
        }
    };
    if (($@) or ($error)){
        $dbh->rollback;
        warn  "Unable to commit to Oracle!\n$@";
    }else{
        $dbh->commit;
        return;
    }
}            
#-----------------------------------------------------------
sub Get_current_prefin_pool{
    my ($dbh, $oldies) = @_;
    my ($ref, $extra);
    if ($oldies){
        $extra = "and    ps.status      = 37";
    }else{
        $extra = "and    ps.status      = 36
                  and    ps.iscurrent   = 1";
    }
    # TO REPORT PRIORITY!!!!
    #select pfpr.priority,
    #                                              ps.projectname,
    #                                              ps.statusdate,
    #                                              pfp.id_finprocess,
    #                                              pfp.statusdate, 
    #                                              fpd.name, 
    #                                              fpd.description,
    #                                              fpd.id_dict,
    #                                              fpd.PREFIN_ORDER
    #                                       from   project_status ps,
    #                                              project_finishingprocess pfp,
    #                                              finishingprocessdict     fpd,
    #                                              project_finishingproblem pfpr
    #                                       where  ps.projectname = pfp.projectname(+)
    #                                       and    pfpr.id_finproblem = pfp.id_finproblem
    #                                       and    pfp.process_type = fpd.id_dict(+)
    #                                       $extra
    #                                       and    ((pfp.iscurrent is NULL) or (pfp.iscurrent = 1))
    #                                       and    ((fpd.description is NULL) or (fpd.description is NOT NULL))
    #                                       order by fpd.PREFIN_ORDER, pfp.statusdate desc

    eval{
        $ref = $dbh->selectall_arrayref(qq[
                                           select ps.projectname,
                                                  ps.statusdate,
                                                  pfp.id_finprocess,
                                                  pfp.statusdate, 
                                                  fpd.name, 
                                                  fpd.description,
                                                  fpd.id_dict,
                                                  fpd.PREFIN_ORDER,
                                                  pfpp.priority
                                           from   project_status ps,
                                                  project_finishingprocess pfp,
                                                  finishingprocessdict     fpd,
                                                  project_finishingproblem pfpp
                                           where  ps.projectname = pfp.projectname(+)
                                           and    pfp.process_type = fpd.id_dict(+)
                                           $extra
                                           and    ((pfp.iscurrent is NULL) or (pfp.iscurrent = 1))
                                           and    ((fpd.description is NULL) or (fpd.description is NOT NULL))
                                           and    pfp.projectname = pfpp.projectname 
                                           order by fpd.PREFIN_ORDER, pfp.statusdate desc
                                           ], {});
        # order by pfp.statusdate desc
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return $ref;
}
#-----------------------------------------------------------
sub Get_projects_with_specific_infinishing_status{
    # as used by holding area webpage
    my ($dbh, 
        $infinishing_status,
        ) = @_;
    my ($ref, $sql);
    eval{
        $sql = "
                select pfp.projectname, pfd.name, pfd.description
                from   project_finishingprocess pfp, finishingprocessdict pfd
                where  pfp.process_type    = $infinishing_status
                and    pfp.iscurrent       = 1
                and    pfp.process_type    = pfd.id_dict
                ";
        ($ref) = $dbh->selectall_arrayref(qq[$sql]);
    };
    
    die "ORACLE ERROR: $@\n" if ($@);
    return ($sql, 
            $ref,
            );
}
#-----------------------------------------------------------
sub Get_available_finishingprocessdict_entries{
    my ($dbh) = @_;
    my $ref;
    eval{
        $ref = $dbh->selectall_arrayref(qq[
                                           select ID_DICT, NAME
                                           from  finishingprocessdict
                                           where is_prefin = 1
                                           order by PREFIN_ORDER
                                           ], {});
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return $ref;
}
#-----------------------------------------------------------
sub Get_infinishing_status_history_for_clone{
    my ($dbh, $project) = @_;
    my $ref;
    # called from project_lookup page
    eval{
        $ref = $dbh->selectall_arrayref(qq[
                                           select pfp.id_finproblem, fpd.name, fpd.description, to_char(pfp.statusdate, 'DD-MON-YYYY HH24:MI:SS'),
                                                  pfp.iscurrent
                                           from   project_finishingprocess pfp,
                                           finishingprocessdict fpd
                                           where  pfp.process_type = fpd.id_dict(+)
                                           and    pfp.projectname  = ?
                                           and    fpd.is_prefin    = 1
                                           order  by pfp.statusdate
                                           ], {}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return $ref;
}
#-----------------------------------------------------------
sub Update_project_finishingprocess_iscurrent{
    my ($dbh, 
        $project, 
        $debug,
        ) = @_;
    my $no_rows;
    eval{
        # Update the last in-finishing iscurrent to 0 (if there is one...)
        my $sql = "
                   update project_finishingprocess
                   set    iscurrent        = 0
                   where  projectname      = '$project'
                   and    id_finprocess    in (
                                              select pfp.id_finprocess
                                              from   project_finishingprocess pfp,
                                              finishingprocessdict     pfd
                                              where  pfp.process_type     = pfd.id_dict
                                              and    pfp.iscurrent        = 1
                                              and    pfd.is_prefin        = 1
                                              and    pfp.projectname      = '$project')";
        print "<hr><pre>$sql</pre><hr>" if ($debug);
        $dbh->do($sql);
    };
    if ($@){
        $dbh->rollback;
        die  "ORACLE ERROR: Unable to commit to Oracle!\n$@";
        return 1;
    }else{
        $dbh->commit;
        return;
    }
}
#-----------------------------------------------------------
sub Get_Autofin_problem_remark{
    my ($dbh, $project) = @_;
    my ($remark, $id_prob);
    eval{
        ($remark, $id_prob) = $dbh->selectrow_array(qq[ 
                                                        select remark, ID_FINPROBLEM
                                                        from  project_finishingproblem
                                                        where projectname    = ?
                                                        ],{}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return ($remark, 
            $id_prob,
            );
}
#-----------------------------------------------------------
sub Get_all_plates{
    my ($dbh, $project) = @_;
    my ($count);
    eval{
        ($count) = $dbh->selectrow_array(qq[ 
                                             select count(distinct bb.name)
                                             from    beckman_box   bb,
                                             bb_map bbm,
                                             subclone s,
                                             ligation l
                                             where l.clonename = ?
                                             and   l.id_ligation = s.id_ligation
                                             and   s.id_subclone = bbm.id_subclone
                                             and bbm.id_beckmanbox = bb.id_beckmanbox
                                             ],{}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return ($count);
}
#-----------------------------------------------------------
sub Get_scanned_plates{
    my ($dbh, $project) = @_;
    my ($count);
    eval{
        ($count) = $dbh->selectrow_array(qq[ 
                                             select count(id_dnaplate) 
                                             from team_dnaplate 
                                             where projectname = ?
                                             ],{}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return ($count);
}
#-----------------------------------------------------------
sub Get_no_reactions_ordered{
    my ($dbh, $project) = @_;
    my ($count);
    eval{
        ($count) = $dbh->selectrow_array(qq[ 
                                             select count(fr.id_frequest)
                                             from   finish_batch fb,
                                             finish_request fr 
                                             where  fb.projectname = ?
                                             and    fb.id_auto is not null
                                             and    fb.id_fbatch  = fr.id_fbatch
                                             ],{}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return ($count);
}
#-----------------------------------------------------------
sub Get_no_reactions_asped{
    my ($dbh, $project) = @_;
    my ($count);
    eval{
        ($count) = $dbh->selectrow_array(qq[ 
                                             select count(distinct sr.name)
                                             from   finish_batch fb, finish_request fr, request_map rm, 
                                                    loadingsample ls, gel_sample gs, gelfile gf, seqread sr
                                             where  fb.projectname = ?
                                             and    fb.id_auto is not null
                                             and    fr.id_fbatch        = fb.id_fbatch
                                             and    fr.id_frequest      = rm.id_frequest
                                             and    rm.id_loadingsample = ls.id_loadingsample
                                             and    ls.id_loadingsample = gs.id_loadingsample
                                             and    gs.id_gel           = gf.id_gel
                                             and    gs.lane             = sr.lane
                                             and    gf.id_gel           = sr.id_gel
                                             and    sr.asp_status is not null
                                             ],{}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return ($count);
}
#-----------------------------------------------------------
sub Reject_project_from_system{
    my ($dbh, $project) = @_;
    # Chuck a project out of the system cause it has problems...
    eval{
        $dbh->do(qq[
                    update project_status
                    set    iscurrent      = 0
                    where  iscurrent      = 1
                    and    projectname    = ?
                    ],{}, $project);
        $dbh->do(qq[
                    insert into project_status
                    (PROJECTNAME, STATUS, STATUSDATE, ISCURRENT, OPERATOR)
                    values (?,   15, sysdate,   1,        'team58' )
                    ],{}, $project);
        $dbh->do(qq[
                    delete from project_finishingprocess
                    where projectname = ?
                    ],{}, $project);
        $dbh->do(qq[
                    delete from project_finishingproblem
                    where projectname = ?
                    ],{}, $project);
    };
    if ($@){
        $dbh->rollback;
        warn  "Unable to commit to Oracle!\n$@";
    }else{
        $dbh->commit;
    } 
    return;
}
#-----------------------------------------------------------
sub Get_prefinishing_problem_remark{
    my ($dbh, 
        $id_finproblem, 
        $project,
        ) = @_;
    # Called from project lookup page
    my ($remark);
    eval{
        ($remark) = $dbh->selectrow_array(qq[ 
                                              select remark
                                              from project_finishingproblem
                                              where id_finproblem = ?
                                              and projectname     = ?
                                              ],{}, $id_finproblem, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return ($remark);
}
#-----------------------------------------------------------
sub Get_prefinishing_problem_remark2{
    my ($dbh, $project) = @_;
    # As above, but without knowing problem_id....
    # called from finishing_selection interface.
    # this works cause EPS will not have been used at this stage of project history...
    #
    my ($remark);
    eval{
        ($remark) = $dbh->selectrow_array(qq[ 
                                              select remark
                                              from project_finishingproblem
                                              where projectname     = ?
                                              ],{}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return ($remark);
}
#-----------------------------------------------------------
sub Get_plate_contents_and_positions{
    my ($dbh, $id_loadingplate) = @_;
    #bt1 23/04/02
    my $ref;
    eval{
        $ref = $dbh->selectall_arrayref(qq[
                                           select rm.maprow, 
                                           rm.mapcol, 
                                           rm.format, 
                                           fr.id_frequest, 
                                           fr.PLATENAME||fr.ROWLETTER||fr.COLNUM||sc.SUFFIX||fr.ITERATION,
                                           p.PRIMERNAME
                                           from   rowcol_map     rm,
                                           request_map    reqm,
                                           loadingsample  ls,
                                           FINISH_REQUEST fr,
                                           PRIMER         p,
                                           SEQCHEM_SEQPROTOCOL ss,
                                           SEQCHEMISTRY sc
                                           where  ls.id_loadingplate  = ?
                                           and    ls.id_loadingsample = reqm.id_loadingsample
                                           and    reqm.id_map         = rm.id_map
                                           AND    reqm.ID_FREQUEST       = fr.ID_FREQUEST
                                           AND    fr.ID_PRIMER           = p.ID_PRIMER(+)
                                           AND    fr.ID_SCSP             = ss.ID_SCSP
                                           AND    ss.ID_SEQCHEM          = sc.ID_SEQCHEM
                                           ], {}, $id_loadingplate);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return $ref;
}
#-----------------------------------------------------------
sub Get_current_mapping_pool{
    my ($dbh) = @_;
    
    my $ref;
    eval{
        $ref = $dbh->selectall_arrayref(qq[
                                           select b.batchdate, 
                                           b.projectname, 
                                           fr.id_frequest, 
                                           fr.PLATENAME||fr.ROWLETTER||fr.COLNUM||h.SUFFIX||fr.ITERATION||p.PRIMERNAME
                                           from   finish_request fr, 
                                           finish_batch b, 
                                           team_person_role d, 
                                           person e, 
                                           seqchem_seqprotocol f, 
                                           seqprotocol g, 
                                           seqchemistry h, 
                                           primer p
                                           where  fr.id_primer = p.id_primer(+)
                                           and    fr.id_fbatch = b.id_fbatch
                                           and    d.id_person = e.id_person
                                           and    d.id_role = b.finisher
                                           and    fr.id_scsp = f.id_scsp
                                           and    f.id_protocol = g.id_protocol
                                           and    f.id_seqchem = h.id_seqchem
                                           and    h.is_finservice = 1
                                           and    fr.id_frequest >= 680523
                                           and    b.id_team_finserv = 43
                                           and    exists (
                                                          select 1
                                                          from frequest_status f
                                                          where f.iscurrent = 1
                                                          and f.status = 1		
                                                          and fr.id_frequest = f.id_frequest)
                                           and 
                                           (fr.id_primer is NULL or exists (
                                                                            select 1
                                                                            from primer_status g
                                                                            where g.iscurrent = 1
                                                                            and g.status = 6
                                                                            and fr.id_primer = g.id_primer))
                                           order by b.projectname, g.name, p.primername, fr.platename, 
                                                    fr.rowletter, fr.colnum, fr.iteration, fr.id_additive
                                           ], {});
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return $ref;
}
#-----------------------------------------------------------
sub Get_loadingplate_details{
    my ($dbh, $project) = @_;
    my $ref;
    eval{
        $ref = $dbh->selectall_arrayref(qq[
                                           select /*+ USE_HASH(lp) */ distinct
                                                 pm.id_primerplate,
                                                 lp.id_loadingplate, lpsd.description, lps.statusdate
                                           from  primer p, primer_map pm,
                                                 lpstatusdict lpsd, loadingplate lp, 
                                                 loadingplate_status lps
                                           where p.id_primer      = pm.id_primer
                                           and   pm.id_primerplate  = lp.id_primerplate
                                           and   lp.id_loadingplate = lps.id_loadingplate 
                                           and   lps.status         = lpsd.id_dict
                                           and   p.projectname      = ?
                                           order by pm.id_primerplate, lps.statusdate
                                           ], {}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return $ref;
}
#-----------------------------------------------------------
sub Available_dna_plates_for_project{
    my ($dbh, $project) = @_;
    # Called from finish_code. to write 'available_plates' file
    my $ref;
    eval{
        $ref = $dbh->selectall_arrayref(qq[
                                           select distinct bb.name 
                                           from   team_dnaplate tdp,
                                           dnaplate_map  dpm,
                                           bb_map        bbm,
                                           beckman_box   bb
                                           where  tdp.projectname   = ?
                                           and    tdp.id_dnaplate   = dpm.id_dnaplate
                                           and    dpm.id_bbmap      = bbm.ID_BBMAP
                                           and    bbm.ID_BECKMANBOX = bb.ID_BECKMANBOX
                                           ],{}, $project);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return $ref;
}
#-----------------------------------------------------------
sub Get_prefinishing_statuses{
    my ($dbh) = @_;

    my $ref;
    eval{
        $ref = $dbh->selectall_arrayref(qq[
                                           select name 
                                           from   finishingprocessdict
                                           where  is_prefin=1
                                           order  by prefin_order
                                       ],{},);
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return $ref;
}                                         
#-----------------------------------------------------------
sub Get_recent_activity{
    my ($dbh, $hours) = @_;
    my $ref;
    eval{
        $ref = $dbh->selectall_arrayref(qq[
                                           select pfp.PROJECTNAME,
                                                  to_char(pfp.statusdate,'DD-MON-YY HH24:MI:SS'),
                                                  pfp.ID_FINPROBLEM,
                                                  pfd.name,
                                                  pfp.iscurrent,
                                                  pfd.description
                                           from   project_finishingprocess pfp,
                                                  finishingprocessdict     pfd
                                           where  statusdate >= sysdate - $hours/24
                                           and    pfp.process_type = pfd.id_dict
                                           order by PFP.STATUSDATE desc
                                           ], {});
    };
    die "ORACLE ERROR: $@\n" if ($@);
    return $ref;
}
#-----------------------------------------------------------

1;
