#
# Author: Paul Mooney
#
# This is were shared subroutines for Chimp related programs should be put.
# `request_dna_plates` donated most of this code.
#
# $Id: Chimp_Utils.pm,v 1.1 2000/12/18 17:18:39 pjm Exp $
#

package Chimp_Utils;

use strict;
use WrapDBI;

sub new {
    my ($type, $db_user) = @_;

    my $self = {
        sql_cache => {},
        db_user   => $db_user,
    };

    bless( $self, $type );

    return $self;
}

sub prepare_sql {
    my ($self) = @_;

    my $dbh = $self->connect_to_oracle(); # set up $dbh

    my $sql_cache = $self->{sql_cache};

    $sql_cache->{teams_for_person} =
        $dbh->prepare(q[SELECT p.FORENAME, p.SURNAME, tpr.TEAMNAME, tpr.ID_ROLE
                        from   PERSON p, TEAM_PERSON_ROLE tpr
                        where  p.EMAIL      = ?
                        and    p.ISCURRENT  = 1
                        and    p.ID_PERSON  = tpr.ID_PERSON
                        and    tpr.ROLETYPE = 'Finishing' ]);


    $sql_cache->{get_plates_for_proj} =
        $dbh->prepare(q[select /*+ INDEX(bbm)  */ unique bb.NAME,
                               cp.CLONENAME,
                               ps.STATUS, l.ID_LIGATION, dps.ID_DNAPLATE,
                               dp.team
                        from   CLONE_PROJECT cp, PROJECT_STATUS ps,
                               LIGATION l, SUBCLONE sc,
                               BB_MAP bbm, BECKMAN_BOX bb,
                               DNAPLATE_MAP dpm, dna_plate dp,
                               DNAPLATE_STATUS dps
                        where  cp.PROJECTNAME    = ?
                        and    cp.PROJECTNAME    = ps.PROJECTNAME
                        and    ps.ISCURRENT      = 1
                        and    cp.CLONENAME      = l.CLONENAME
                        and    l.ID_LIGATION     = sc.ID_LIGATION
                        and    sc.ID_SUBCLONE    = bbm.ID_SUBCLONE
                        and    bbm.ID_BECKMANBOX = bb.ID_BECKMANBOX
                        and    bbm.ID_MAP        = 1
                        and    dpm.ID_BBMAP      = bbm.ID_BBMAP
                        and    dpm.ID_DNAPLATE   = dps.ID_DNAPLATE
                        AND    dpm.id_dnaplate   = dp.id_dnaplate
                        and    dps.ISCURRENT     = 1
                        and    dps.STATUS        = 1
                        order by dps.ID_DNAPLATE]);

    $sql_cache->{clones_for_proj} =
        $dbh->prepare(q[SELECT clonename
                        FROM   clone_project
                        WHERE  projectname = ? ]);

    $sql_cache->{already_requested} =
        $dbh->prepare(qq[select tpr.TEAMNAME, p.FORENAME, p.SURNAME
                         from   DP_FINREQUEST dpfr,
                                TEAM_PERSON_ROLE tpr, PERSON p
                         where  dpfr.PROJECTNAME  = ?
                         and    dpfr.REQUESTED_BY = tpr.ID_ROLE
                         and    tpr.ID_PERSON     = p.ID_PERSON ]);

    $sql_cache->{insert_request} =
        $dbh->prepare(q[insert into DP_FINREQUEST
                        values (?, ?, SYSDATE, 0) ]);
}

sub commit {
    my ($self) = @_;

    if( $self->{dbh} ){
        $self->{dbh}->commit();
    }
    else {
        die "No database handle so can not commit() !!!";
    }
}

#
# Get db handle, store it in $self.
#
sub connect_to_oracle {
    my ($self) = @_;

    my $dbh = $self->{dbh};
    
    unless( $dbh ) {
        $dbh = WrapDBI->connect($self->{db_user}, {RaiseError => 1});
    }

    if( !defined $dbh ) {
        $self->{dbh} = undef;
        die "Could not connect to database";
    }

    $dbh->{AutoCommit} = 0;
    $self->{dbh}       = $dbh;

    return $dbh;
}

sub get_dbh {
    my ($self) = @_;

    return $self->{dbh};
}

#
# Pass in an email address and it returns a 2D array listing the full name and
# the teams the user is registered as a finisher
#
sub get_teams_for_person {
    my ($self, $user) = @_;
    my $sql_cache     = $self->{sql_cache};

    $sql_cache->{teams_for_person}->execute($user);
    my $ref = $sql_cache->{teams_for_person}->fetchall_arrayref();
    $sql_cache->{teams_for_person}->finish();

    return $ref;
}

sub get_clones_for_project {
    my ($self, $project_name) = @_;
    my $sql_cache             = $self->{sql_cache};
    
    ${sql_cache}->{clones_for_proj}->execute($project_name);
    my $clones_ref = $sql_cache->{clones_for_proj}->fetchall_arrayref();
    $sql_cache->{clones_for_proj}->finish();

    return $clones_ref;
}

#
# returns a 2D array ref that holds the name of each DNA plate listed in Oracle
# that has passed.
#
sub get_plates_for_project {
    my ($self, $project_name) = @_;
    my $sql_cache        = $self->{sql_cache};

    $sql_cache->{get_plates_for_proj}->execute($project_name);

    my $arrayOfArrays = $sql_cache->{get_plates_for_proj}->fetchall_arrayref();

    $sql_cache->{get_plates_for_proj}->finish();

    return $arrayOfArrays
}

 
#
# Will return team, forename and surname of person who has lareayd requested
# this project, ELSE it adds the request.
#
sub add_request {
    my ($self, $project_name, $team_person_role) = @_;

    unless( $project_name && $team_person_role ){
        die "project name and/or team_person_role not supplied to ".
            "add_request()";
    }

    my @row = $self->who_requested($project_name);

    if( @row ){
        return @row;
    };

    $self->{sql_cache}->{insert_request}->execute($project_name,
                                                  $team_person_role);

    return undef;
}

#
# For a given project, returns the team number, forename and surname of the
# person who has already requested this project.
#
sub who_requested {
    my ($self, $project) = @_;

    my $sql_cache = $self->{sql_cache};

    $sql_cache->{already_requested}->execute($project);
    my @row = $sql_cache->{already_requested}->fetchrow_array();
    $sql_cache->{already_requested}->finish();

    return @row;
}

################
# END OF MODULE
################
1;


#
# $Log: Chimp_Utils.pm,v $
# Revision 1.1  2000/12/18  17:18:39  pjm
# Initial revision
#
#
