#!/usr/bin/perl
package Colleague;

#####################################################################
# Author : Shuai Weng
# Date   : July 2001
# 
# See documentation for the usage details. 
#    
# http:///usr/local/dicty/www_dictybase/db/lib/html/dictyBase/programmer/Colleague.html
#
#####################################################################

use strict;
use DBI;
use Carp;
use vars qw (@ISA %allowedConstructors);
use dictyBase_Table;
@ISA = qw (dictyBase_Table); # base class

# Class Globals

# put column names in the hash below, that are able to uniquely
# specify a row in the table

%allowedConstructors = (colleague_no=>undef);

#######################################################################
sub interest {
#######################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT remark
        FROM   $schema.colleague_remark
        WHERE  colleague_no = ?
        AND    remark_type = 'Research Interest'
    ");
    $sth->execute($self->colleague_no);
    my $interest = $sth->fetchrow;
    $sth->finish;
    return $interest;
}

#######################################################################
sub comment {
#######################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT remark
        FROM   $schema.colleague_remark
        WHERE  colleague_no = ?
        AND    remark_type = 'Announcement'
    ");
    $sth->execute($self->colleague_no);
    my $comment = $sth->fetchrow;
    $sth->finish;
    return $comment;
}

#######################################################################
sub associateRef {
#######################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT C.colleague_no, C.last_name, C.first_name, C.suffix
        FROM   $schema.colleague C, $schema.associate A
        WHERE  A.colleague_no = ?
        AND    A.associate_no = C.colleague_no
        ORDER BY C.last_name, C.first_name
    ");
    $sth->execute($self->colleague_no);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

#######################################################################
sub associate4piRef {
#######################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT C.colleague_no, C.last_name, C.first_name, C.suffix
        FROM   $schema.colleague C, $schema.pi P
        WHERE  P.pi_no = ?
        AND    P.colleague_no = C.colleague_no
        AND    C.last_name not like '~%'
        ORDER BY C.last_name, C.first_name
    ");
    $sth->execute($self->colleague_no);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}
#######################################################################
sub piRef {
#######################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT C.colleague_no, C.last_name, C.first_name, C.suffix
        FROM   $schema.colleague C, $schema.pi P
        WHERE  P.pi_no = C.colleague_no
        AND    P.colleague_no = ?
        AND    C.last_name not like '~%'
        ORDER BY C.last_name, C.first_name
    ");
    $sth->execute($self->colleague_no);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}
#######################################################################
sub isPIWithoutAssocs {
#######################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT C.colleague_no, C.last_name, C.first_name, C.suffix
        FROM   $schema.colleague C, $schema.pi P
        WHERE  P.pi_no = C.colleague_no
        AND    P.colleague_no = ? 
        AND    C.last_name like '~Associate'
        ORDER BY C.last_name, C.first_name
    ");
    $sth->execute($self->colleague_no);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;

    if (defined $arrayRef) {
	return 1;
    }
    else {
	return 0;
    }
}

#######################################################################
sub urlRef {
#######################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT U.url, U.www_name, U.url_type
        FROM   $schema.url U, $schema.coll_url CU
        WHERE  CU.colleague_no = ?
        AND    CU.url_no = U.url_no
    ");
    $sth->execute($self->colleague_no);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

#######################################################################
sub emailRef {
#######################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT E.email
        FROM   $schema.email E, $schema.coll_email CE
        WHERE  CE.colleague_no = ?
        AND    CE.email_no = E.email_no
    ");
    $sth->execute($self->colleague_no);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

#######################################################################
sub phoneRef {
#######################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT P.phone_num, P.phone_type, P.phone_location
        FROM   $schema.phone P, $schema.coll_phone CP
        WHERE  CP.colleague_no = ?
        AND    CP.phone_no = P.phone_no
    ");
    $sth->execute($self->colleague_no);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

#######################################################################
sub noteRef {
#######################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT CN.note, CN.is_public
        FROM   $schema.curator_note CN, $schema.coll_cn CCN
        WHERE  CCN.colleague_no = ?
        AND    CCN.curator_note_no = CN.curator_note_no
    ");
    $sth->execute($self->colleague_no);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

#######################################################################
sub locusRef {
#######################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT L.locus_no, L.locus_name
        FROM   $schema.locus L, $schema.coll_locus CL
        WHERE  CL.colleague_no = ?
        AND    CL.locus_no = L.locus_no
    ");
    $sth->execute($self->colleague_no);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

######################################################################
sub keywordRef  {
######################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT K.keyword
        FROM   $schema.keyword K, $schema.coll_keyword CK
        WHERE  K.keyword_no = CK.keyword_no
        AND    CK.colleague_no = ?
        AND    NOT K.source = 'Curator-defined'
    ");
    $sth->execute($self->colleague_no);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

######################################################################
sub researchTopicsRef  {
######################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT K.keyword
        FROM   $schema.keyword K, $schema.coll_keyword CK
        WHERE  K.keyword_no = CK.keyword_no
        AND    CK.colleague_no = ?
        AND    K.source = 'Curator-defined'
        ORDER BY K.keyword
    ");
    $sth->execute($self->colleague_no);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef ;
}

####################################################################
sub GetStateAbbrevArrayRef {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed to the 'GetStateAbbrevArrayRef' method.", ;
    my $schema = $self->schema;
    my $sth = $dbh->prepare("
       SELECT abbrev, country
       FROM   $schema.state
       ORDER BY  1
    ");
    $sth->execute;
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef ;
}

####################################################################
sub GetColleagueNoBYnameAndEmail {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed into GetColleagueNoBYnameAndEmail method.";
    my $schema = $self->schema;
    my $sth = $dbh->prepare( "
        SELECT C.colleague_no
        FROM   $schema.colleague C, $schema.coll_email CE, 
               $schema.email E 
	WHERE  upper(C.last_name) = ?
        AND    upper(C.first_name) = ?
        AND    CE.colleague_no = C.colleague_no
        AND    CE.email_no = E.email_no
        AND    upper(E.email) = ?
    ");
    $sth->execute(uc($args{'last_name'}), uc($args{'first_name'}), 
		  uc($args{'email'}));
    my $collNo = $sth->fetchrow;
    $sth->finish;
    return $collNo;
}


####################################################################
sub GetColleagueNoBYlname {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed into GetColleagueNoBYlname method.";
    my $schema = $self->schema;
    my $sth = $dbh->prepare( "
        SELECT colleague_no
        FROM   $schema.colleague 
        WHERE  upper(last_name) like ?
        AND    last_name not like '%~'
    ");
    $sth->execute(uc($args{'last_name'}));
    my @colleagueNo;
    while(my ($collNo) = $sth->fetchrow()) {
	push(@colleagueNo, $collNo);
    }
    $sth->finish;
    return @colleagueNo;
}

####################################################################
sub GetColleagueNoBYlocusNo {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed into GetColleagueNoBYlocusNo method.";
    my $schema = $self->schema;
    my $sth = $dbh->prepare( "
        SELECT colleague_no
        FROM   $schema.coll_locus 
        WHERE  locus_no = ?
    ");
    $sth->execute($args{'locus_no'});
    my @colleagueNo;
    while(my ($collNo) = $sth->fetchrow()) {
	push(@colleagueNo, $collNo);
    }
    $sth->finish;
    return @colleagueNo;
}

####################################################################
sub GetColleagueNoBYaceNm {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed into GetColleagueNoBYaceNm method.";
    my $schema = $self->schema;
    my $sth = $dbh->prepare( "
        SELECT primary_key
        FROM   $schema.acelink
        WHERE  tab_name = 'COLLEAGUE' 
        AND    ace_object = ?
    ");
    $sth->execute($args{'aceNm'});
    my $collNo = $sth->fetchrow;
    $sth->finish;
    return $collNo;
}

####################################################################
sub GetColleagueNoBYfullname {
####################################################################
    my ($self, %args) = @_;
    my ($lname, $fname) = split(/, /, $args{'fullname'});
    my @colleagueNo = $self->GetColleagueNoBYname(dbh=>$args{'dbh'},
						  last_name=>$lname,
						  first_name=>$fname);
    return @colleagueNo;
}


####################################################################
sub GetColleagueNoBYname {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed into GetColleagueNoBYname method.";
    my $schema = $self->schema;
    my $sth;
    if (!$args{'suffix'}) {
	$sth = $dbh->prepare( "
           SELECT colleague_no
           FROM   $schema.colleague
	   WHERE  upper(last_name) = ?
           AND    upper(first_name) = ?
        ");
	$sth->execute(uc($args{'last_name'}), 
		      uc($args{'first_name'}));
    }
    else {
	$sth = $dbh->prepare( "
           SELECT colleague_no
           FROM   $schema.colleague
	   WHERE  upper(last_name) = ?
           AND    upper(first_name) = ?
           AND    suffix = ?
        ");
	$sth->execute(uc($args{'last_name'}), 
		      uc($args{'first_name'}),
		      $args{'suffix'});
    }
    my @colleagueNo;
    while(my ($collNo) = $sth->fetchrow()) {
	push(@colleagueNo, $collNo);
    }
    $sth->finish;
    return @colleagueNo;
}

####################################################################
sub GetColleagueInfoArrayRefBYname {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed into GetColleagueInfoArrayRefBYlname method.";
    my $schema = $self->schema;
    my $sth = $dbh->prepare("
       SELECT colleague_no, first_name, last_name, institution
       FROM   $schema.colleague
       WHERE  upper(last_name) = ?
    ");
    $sth->execute(uc($args{'last_name'}));
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

####################################################################
sub DESTROY{
####################################################################
# nothing needs to be done 

}


####################################################################
1; #################################################################
####################################################################

=pod

=head1 Name

Colleague.pm    

=head1 Description

This perl object (Colleague.pm) acts as container for all
colleague related information associated with a colleague_no
in oracle database.  Once an object has been instantiated, several methods
are available to retrieve the attributes of the object. 

=head1 Instantiating a New Colleague Object

To instantiate a new colleague object, you may use following syntax:

my $collObject = Colleague->new(dbh=>$dbh,
			        colleague_no=>$colleagueNo);

=head1 Accessor Methods


All accessor methods take the form of : 



my $column_value = $collObject->column_name, eg: 


my $lname = $collObject->last_name; 


etc. You can use an accessor for any valid column in the Colleague table. 

See valid columns in colleague table:

http:///usr/local/dicty/www_dictybase/db/lib/cgi-bin/dictyBase/tableSpecifications?table=COLLEAGUE


Following methods are provided to access the colleague related info in other tables.

=head2 interest

Usage :

my $interest = $collObject->interest;

This accessor returns reserach_interest for a given colleague.

=head2 comment

Usage :

my $comment = $collObject->comment;

This accessor returns announcement for a given colleague.

=head2 associateRef

Usage :

my $associateRef = $collObject->associateRef;

foreach my $rowRef (@$associateRef) {


    my($colleagueNo, $lname, $fname, $suffix) = @$rowRef;

    #####
   
}

This accessor returns info for all associates/collaborators.


=head2 associate4piRef

Usage :

my $associate4piRef = $collObject->associate4piRef;

foreach my $rowRef (@$associate4piRef) {


    my($colleagueNo, $lname, $fname, $suffix) = @$rowRef;

    #####
   
}

This accessor returns info for all lab members.  It will not return the lab member whose last name is '~Associate'.


=head2 piRef

Usage :

my $piRef = $collObject->piRef;

foreach my $rowRef (@$piRef) {


    my($colleagueNo, $lname, $fname, $suffix) = @$rowRef;

    #####
   
}

This accessor returns info for all PIs.  It will not return the PI with the
last name of '~Associate'.

=head2 isPIWithoutAssocs

Usage : 
$collObject->isPIWithoutAssocs

Returns true if this is a PI that does not have any associates, else returns false.



=head2 urlRef;

my $urlRef = $collObject->urlRef;

foreach my $rowRef (@$urlRef) {


    my ($url, $wwwNm) = @$rowRef;

    #####
   
}

This accessor returns info for all urls

=head2 emailRef;

my $emailRef = $collObject->emailRef;

foreach my $rowRef (@$emailRef) {

    my ($email) = @$rowRef;

    #####
   
}

This accessor returns info for all emails

=head2 phoneRef;

my $phoneRef = $collObject->phoneRef;

foreach my $rowRef (@$phoneRef) {

    my ($phoneNum, $phoneType, $phoneLoc) = @$rowRef;

    #####
   
}

This accessor returns info for all phones.

=head2 noteRef;

my $noteRef = $collObject->noteRef;

foreach my $rowRef (@$noteRef) {

    my ($note, $isPublic) = @$rowRef;

    #####
   
}

This accessor returns info for all curator notes.

=head2 locusRef;

my $locusRef = $collObject->locusRef;

foreach my $rowRef (@$locusRef) {

    my ($locusNo, $locus) = @$rowRef;

    #####
   
}

This accessor returns info for all associated loci.

=head2 keywordRef

my $keywordRef = $collObject->keywordRef;

foreach my $rowRef (@$keywordRef) {

    my ($keyword) = @$rowRef;

    #####
   
}

This accessor returns keyword info for a given colleague.


=head2 GetStateAbbrevArrayRef


Usage:


my $arrayRef = Colleague->GetStateAbbrevArrayRef(dbh=>$dbh);


foreach my $rowRef (@$arrayRef) {

    my ($abbrev, $country) = @$rowRef;

    ####

}


This method returns an arrayref for USA states/Canada Provices and their abbreviations 


=head2 GetColleagueNoBYnameAndEmail


my $collNo = Colleague->GetColleagueNoBYnameAndEmail(dbh=>$dbh,
						       last_name=>$lname,
						       first_name=>$fname,
						       email=>$email);


This methods returns colleague_no for the specified clooeague last_name, first_name and email address.


=head2 GetColleagueNoBYlname


my @collNo = Colleague->GetColleagueNoBYlname(dbh=>$dbh,
					      last_name=>$lname);


This method returns an array for the colleague_nos whose last_names match
$lname.


=head2 GetColleagueNoBYlocusNo


my @collNo = Colleague->GetColleagueNoBYlocusNo(dbh=>$dbh,
						locus_no=>$locusNo);


This method returns an array for the colleague_no associated with a specified locus_no.


=head2 GetColleagueNoBYaceNm


my $collNo = Colleague->GetColleagueNoBYaceNm(dbh=>$dbh,
					      aceNm=>$aceNm);


This method returns colleague_no for a specified ace colleague object name.


=head2 GetColleagueNoBYfullname


my @collNo = Colleague->GetColleagueNoBYfullname(dbh=>$dbh,
						 fullname=>$fullname);


This method returns an array for the colleague_no whose full name match $fullname (last_name, first_name).


=head2 GetColleagueNoBYname


my @collNo = Colleague->GetColleagueNoBYname(dbh=>$dbh,
					     last_name=>$lname,
					     first_name=>$fname,
					     suffix=>$suffix);


The $suffix is optional.


This method returns an array for the colleague_no whose name match $lname, $fname and $suffix.


=head2 GetColleagueInfoArrayRefBYname 


my $arrayRef = Colleague->GetColleagueInfoArrayRefBYname(dbh=>$dbh,
							 last_name=>$lname);


foreach my $rowRef (@$arrayRef) {


    my ($collNo, $fname, $lname, $institution) = @$rowRef;


    ######


}


This method returns info for the colleagues whose last_names match $lname. 


=head1 getRow method

Usage:

my $row = $collObject->getRow;

This method returns a tab-delimited row from colleague table.


=head1 Insert, update and delete Methods

You can also use 'Insert' class method and 'update' and 'delete' 
instance methods for inserting new row into database, updating and 
deleting info for a specified row. 

See dictyBase_Table documentation for usage details :

Update : http:///usr/local/dicty/www_dictybase/db/lib/staff/dictyBase/programmer/dictyBase_Table.html#Update_Method

Insert : http:///usr/local/dicty/www_dictybase/db/lib/staff/dictyBase/programmer/dictyBase_Table.html#Insert_Method

Delete : http:///usr/local/dicty/www_dictybase/db/lib/staff/dictyBase/programmer/dictyBase_Table.html#Delete_Method


=head1 Author

Shuai Weng

shuai@genome.stanford.edu

=cut