#!/usr/bin/perl
package dictyBaseColleague;

##########################################################################
##### Author :        Shuai Weng
##### Date   :  Jan. 2001
##### Rev.   :  May. 2001 (Kane Tse)
##### Description : This package contains all necessary methods for
#####               retrieving colleague related info
#####
#####  Usage:
#####  use Colleague;
#####
#####  To instantiate a new Colleague object, you may use one of
#####  following syntaxes:
#####  my $collObject = Colleague>new(dbh=>$dbh,
#####                                         colleagueNo=>$colleagueNo);
#####  my $collObject = Colleague->new(database=>$database,
#####                                        colleagueNo=>$colleagueNo);
#####
#####     passing $dbh OR $database
#####
#####
#####  my $lname = $collObject->lname;
#####  my $fname = $collObject->fname;
#####  my $address1 = $collObject->address1;
#####  my $address2 = $collObject->address2;
#####  and more ....
#######################################################################
use strict;
use DBI;
use lib "/usr/local/dicty/www_dictybase/db/lib/common";
use Login qw (ConnectToDatabase);

#######################################################################
#################### global variables #################################
#######################################################################

my $dbh;

#######################################################################
sub new {      ############ constructor ###############################
#######################################################################

    my ($self, %args) = @_;

    $self = {};
    bless $self;

    if ($args{'dbh'}) {
        $dbh = $args{'dbh'};
    }
    elsif ($args{'database'}) {
        $self->{'_database'} = $args{'database'};
        $dbh = &ConnectToDatabase($args{'database'});
    }
    else {
        die "No database name or database handle is passed to this object.";
    }
    $self->{'_colleagueNo'} = $args{'colleagueNo'};
    $self->colleagueInfo;

    return $self;
}

#######################################################################
sub colleagueInfo {
#######################################################################
    #KD: modified to retrieve is_subscribed info
    my ($self) = @_;
    my $sth = $dbh->prepare("
        SELECT last_name, first_name, suffix, other_name,
               profession, job_title, institution, address1,
               address2, address3, address4, address5,
               city, state, region, country, postal_code,
               source, date_modified, date_created, created_by,
               is_subscribed
        FROM   CGM_DDB.colleague
        WHERE  colleague_no = ?
    ");
    $sth->execute($self->{'_colleagueNo'});
    my ($last_name, $first_name, $suffix, $other_name, $profession,
        $job_title, $institution, $address1, $address2, $address3,
        $address4, $address5, $city, $state, $region, $country,
        $postal_code, $source, $date_modified, $date_created,
        $created_by,$is_subscribed) = $sth->fetchrow;
    $sth->finish;
    if (!$last_name) { $self->{'_colleagueNo'} = 0; return; }
    $self->{'_lname'} = $last_name;
    $self->{'_fname'} = $first_name;
    $self->{'_suffix'} = $suffix;
    $self->{'_oname'} = $other_name;
    $self->{'_profession'} = $profession;
    $self->{'_jobTitle'} = $job_title;
    $self->{'_institution'} = $institution;
    $self->{'_address1'} = $address1;
    $self->{'_address2'} = $address2;
    $self->{'_address3'} = $address3;
    $self->{'_address4'} = $address4;
    $self->{'_address5'} = $address5;
    $self->{'_city'} = $city;
    $self->{'_state'} = $state;
    $self->{'_region'} = $region;
    $self->{'_country'} = $country;
    $self->{'_postalCode'} = $postal_code;
    $self->{'_source'} = $source;
    $self->{'_dateModified'} = $date_modified;
    $self->{'_dateCreated'} = $date_created;
    $self->{'_createdBy'} = $created_by;
    $self->{'_dictyBasecontact'} = $is_subscribed;
    $self->remarkInfo;
}

#######################################################################
#sub isdictyBasecontact {
#######################################################################
## probably don't need this--can do it in the above sub
## added by KD to get value of new column is_subscribed
 #   my ($self) = @_;
  #  my $schema = $self->schema;
   # my $sth = $self->dbh->prepare("
    #    SELECT is_subscribed
    #    FROM   $schema.colleague
    #    WHERE colleague_no = ?
    #");
    #$sth->execute($self->colleague_no);
    #my $Contact = $sth->fetchrow;
    #$sth->finish;
    #return $Contact;
#}

#######################################################################
sub remarkInfo {
#######################################################################
    my ($self) = @_;
    my $sth = $dbh->prepare("
       SELECT remark, remark_type
       FROM   CGM_DDB.colleague_remark
       WHERE  colleague_no = ?
    ");
    $sth->execute($self->{'_colleagueNo'});
    while(my($remark, $type) = $sth->fetchrow()) {
        if ($type =~ /interest/i) {
            $self->{'_interest'} .= $remark;
        }
        if ($type =~ /announcement/i) {
            $self->{'_comment'} .= $remark;
        }
    }
    $sth->finish();
}

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

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

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

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

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

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

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

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

######################################################################
sub researchTopicsRef  {
######################################################################
    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT K.keyword
        FROM   CGM_DDB.keyword K, CGM_DDB.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->colleagueNo);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish();
    return $arrayRef;
}

#######################################################################
sub isPIWithoutAssocs {
#######################################################################
    # This method is duplicated from
    #   /usr/local/dicty/www_dictybase/db/lib/dictyBase/Objects/Colleague.pm

    my ($self) = @_;
    my $sth = $dbh->prepare("
        SELECT C.colleague_no, C.last_name, C.first_name, C.suffix
        FROM   CGM_DDB.colleague C, CGM_DDB.pi P
        WHERE  P.colleague_no = C.colleague_no
        AND    P.pi_no = ?
        AND    C.last_name like '~Associate'
        ORDER BY C.last_name, C.first_name
    ");
    $sth->execute($self->colleagueNo);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;

    if (defined $arrayRef->[0]) {
        return 1;
    }
    else {
        return 0;
    }
}

######################################################################
sub locusRef {
######################################################################
    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT L.locus_no, L.locus_name
        FROM   CGM_DDB.locus L, CGM_DDB.coll_locus CL
        WHERE  CL.colleague_no = ?
        AND    CL.locus_no = L.locus_no
    ");
    $sth->execute($self->colleagueNo);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

######################################################################
sub database { $_[0]->{'_database'} }
sub colleagueNo {$_[0]->{'_colleagueNo'} }
sub dictyBasecontact { $_[0]->{'_dictyBasecontact'} }
sub lname { $_[0]->{'_lname'} }
sub fname { $_[0]->{'_fname'} }
sub suffix { $_[0]->{'_suffix'} }
sub oname { $_[0]->{'_oname'} }
sub profession { $_[0]->{'_profession'} }
sub jobTitle { $_[0]->{'_jobTitle'} }
sub institution { $_[0]->{'_institution'} }
sub address1 { $_[0]->{'_address1'} }
sub address2 { $_[0]->{'_address2'} }
sub address3 { $_[0]->{'_address3'} }
sub address4 { $_[0]->{'_address4'} }
sub address5 { $_[0]->{'_address5'} }
sub city { $_[0]->{'_city'} }
sub state { $_[0]->{'_state'} }
sub region { $_[0]->{'_region'} }
sub country { $_[0]->{'_country'} }
sub postalCode { $_[0]->{'_postalCode'} }
sub source { $_[0]->{'_source'} }
sub dateModified { $_[0]->{'_dateModified'} }
sub dateCreated { $_[0]->{'_dateCreated'} }
sub createdBy { $_[0]->{'_createdBy'} }
sub interest { $_[0]->{'_interest'} }
sub comment { $_[0]->{'_comment'} }
sub dbh { $dbh }

########################################################################
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 one of following syntaxes:

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

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

You need to pass database_handle/database_name and colleague_no
to the object.

=head1 Accessor Methods

=head2 colleagueNo

Usage :

my $colleagueNo = $collObject->colleagueNo;

This accessor returns colleague_no for an available colleague otherwise
returns a boolean false.

=head2 lname

Usage :

my $lname = $collObject->lname;

This accessor returns last_name for a given colleague.

=head2 fname

Usage :

my $fname = $collObject->fname;

This accessor returns first_name for a given colleague.

=head2 suffix

Usage :

my $suffix= $collObject->suffix;

This accessor returns suffix for a given colleague.

=head2 oname

Usage :

my $oname = $collObject->oname;

This accessor returns other_name for a given colleague.

=head2 profession

Usage :

my $profession = $collObject->profession;

This accessor returns profession for a given colleague.

=head2 jobTitle

Usage :

my $jobTitle = $collObject->jobTitle;

This accessor returns job_title for a given colleague.

=head2 institution

Usage :

my $institution = $collObject->institution;

This accessor returns institution for a given colleague.

=head2 address1

Usage :

my $address1 = $collObject->address1;

This accessor returns address1 for a given colleague.

=head2 address2

Usage :

my $address2 = $collObject->address2;

This accessor returns address2 for a given colleague.

=head2 address3

Usage :

my $address3 = $collObject->address3;

This accessor returns address3 for a given colleague.

=head2 address4

Usage :

my $address4 = $collObject->address4;

This accessor returns address4 for a given colleague.

=head2 address5

Usage :

my $address5 = $collObject->address5;

This accessor returns address5 for a given colleague.

=head2 city

Usage :

my $city = $collObject->city;

This accessor returns city for a given colleague.

=head2 state

Usage :

my $state = $collObject->state;

This accessor returns state for a given colleague.

=head2 region

Usage :

my $region = $collObject->region;

This accessor returns region for a given colleague.

=head2 country

Usage :

my $country = $collObject->country;

This accessor returns country for a given colleague.

=head2 postalCode

Usage :

my $postalCode = $collObject->postalCode;

This accessor returns postalCode for a given colleague.

=head2 source

Usage :

my $source = $collObject->source;

This accessor returns source for a given colleague.

=head2 dateModified

Usage :

my $dateModified = $collObject->dateModified;

This accessor returns dateModified for a given colleague.

=head2 dateCreated

Usage :

my $dateCreated = $collObject->dateCreated;

This accessor returns dateCreated for a given colleague.

=head2 createdBy

Usage :

my $createdBy = $collObject->createdBy;

This accessor returns createdBy for a given colleague.

=head2 remark

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


=head2 piRef

Usage :

my $piRef = $collObject->piRef;

foreach my $rowRef (@$piRef) {


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

    #####

}

This accessor returns info for all PIs.

=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.


=cut