#!/usr/bin/perl
package Go_base;

#####################################################################
# Author : Shuai Weng
# Date   : July 2001
#
# Usage: 
#     
#   use Go;
#
#   ### You may use one of the following syntax 
#   ### to instantiate a new Go object.
#
#   our $goObj = Go->new(dbh=>$dbh,
#                       goid=>$goid);
#       
#
#   ### You can use an accessor for any valid column 
#   ### in the Go table. For example: 
#   our $goTerm = $goObj->go_term;
#   our $goAspect = $goObj->go_aspect;
#
#   See documentation for detail.
#   
#   http:///usr/local/dicty/www_dictybase/db/lib/html/dictyBase/programmer/Go.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 = (goid=>undef);

####################################################################
sub goSynonymArrayRef {
####################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT GS.go_synonym
        FROM   $schema.go_synonym GS, $schema.go_gosyn GGS
        WHERE  GS.go_synonym_no = GGS.go_synonym_no
        AND    GGS.goid = ?
    ");
    $sth->execute($self->goid);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
    
}

####################################################################
sub goFeatGoevArrayRef {
####################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT F.feature_no, F.feature_name, GE.evidence_code
        FROM   $schema.go_feat_goev GFG, $schema.go_evidence GE, 
               $schema.feature F
        WHERE  GFG.goid = ?
        AND    GFG.feature_no = F.feature_no
        AND    GFG.go_evidence_no = GE.go_evidence_no
        ORDER BY upper(F.feature_name)
    ");
    $sth->execute($self->goid);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

####################################################################
sub goLocusGoevArrayRef {
####################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT L.locus_no, L.locus_name, GE.evidence_code
        FROM   $schema.go_locus_goev GLG, $schema.go_evidence GE, 
               $schema.locus L
        WHERE  GLG.goid = ?
        AND    GLG.locus_no = L.locus_no
        AND    GLG.go_evidence_no = GE.go_evidence_no
        ORDER BY upper(L.locus_name)
    ");
    $sth->execute($self->goid);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

########################################################################
sub getLocusArrayRef {
########################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT  unique L.locus_no, L.locus_name
        FROM    $schema.locus L, $schema.go_locus_goev GLG
        WHERE   GLG.goid = ?
        AND     GLG.locus_no = L.locus_no
        ORDER BY upper(L.locus_name)
    ");
    $sth->execute($self->goid);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

########################################################################
sub getFeatureArrayRef {
########################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT  unique F.feature_no, F.feature_name
        FROM    $schema.feature F, $schema.go_feat_goev GFG
        WHERE   GFG.goid = ?
        AND     GFG.feature_no = F.feature_no
        ORDER BY upper(F.feature_name)
    ");
    $sth->execute($self->goid);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

########################################################################
sub getAmiGoLinkUrl {
########################################################################
    my ($self) = @_;
    my $schema = $self->schema;
    my $sth = $self->dbh->prepare("
        SELECT TU.template_url
        FROM   $schema.template_url TU, $schema.external_id EI, 
               $schema.ei_tu EITU 
        WHERE  EI.source = 'GO'
        AND    EI.tab_name = 'GO'
        AND    EI.primary_key = ?
        AND    EI.external_id_no = EITU.external_id_no
        AND    EITU.template_url_no = TU.template_url_no 
        AND    upper(TU.description) like '%AMIGO%'
    ");
    $sth->execute($self->goid);
    my $url = $sth->fetchrow;
    my $goid = $self->goid;
    $url =~ s/_SUBSTITUTE_THIS_/$goid/i;
    $sth->finish;
    return $url;
}

########################################################################
sub GetReferenceInfoNew {
########################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed to GetReferenceInfo method.";
    my $schema = $self->schema;
    my (%goevCode, %goEvidenceCode);
    $args{'goEvidenceCodeRef'} = \%goEvidenceCode;
    $self->_getGoevInfo(%args);
    my $goid = $args{'goid'};
    my $type = uc($args{'type'});
    my $No = $args{'No'};
    my $prikey = $goid."::".$No."::%";
    my $tabNm =  "GO_".$type."_GOEV";
    ### change GO_FEATURE_GOEV to GO_FEAT_GOEV 
    $tabNm =~ s/FEATURE/FEAT/; 
    my $priKeyCol = "GOID::".$type."_NO::GO_EVIDENCE_NO::IS_NOT";
    my $sth = $dbh->prepare("
        SELECT unique R.reference_no, R.citation, RL.reflink_no, 
               RL.primary_key, RL.date_created
        FROM   $schema.reflink RL, $schema.reference R 
        WHERE  RL.primary_key like ?
        AND    RL.tab_name = ?
        AND    RL.primary_key_col = ?
        AND    RL.reference_no = R.reference_no
        ORDER BY R.reference_no
    ");
    $sth->execute($prikey, $tabNm, $priKeyCol);
    my @citationEtc;
    while(my ($refNo, $citation, $reflinkNo, $prikey, $dateCreated) 
	  = $sth->fetchrow()) {
	
	my $externalIdInfoArrayRef 
	    = $self->_getExternalIdInfo($dbh, $reflinkNo);
 
        if ($prikey =~ /^${goid}::${No}::([0-9]+)::([NY])$/) {
	    my $goevNo = $1;
	    my $isNot = $2;
	    
	    if (!@$externalIdInfoArrayRef) {
		my @row = ($refNo, $citation, $goevNo, 
			   $goEvidenceCode{$goevNo},
			   $isNot, $dateCreated);
		push(@citationEtc, \@row);
	    }

	    foreach my $rowRef (@$externalIdInfoArrayRef) {

		my ($externalIdNo, $externaId, $source) = @$rowRef;

		my @row = ($refNo, $citation, $goevNo, 
			   $goEvidenceCode{$goevNo},
			   $isNot, $dateCreated, $externalIdNo, 
			   $externaId, $source);
		push(@citationEtc, \@row);
	    }
	}
    }
    return \@citationEtc;
}

########################################################################
sub _getExternalIdInfo {
########################################################################
    my ($self, $dbh, $reflinkNo) = @_;

    my $schema = $self->schema;

    my $sth = $dbh->prepare("
        SELECT external_id_no, external_id, source
        FROM   $schema.external_id
        WHERE  primary_key = ?
        AND    tab_name = 'REFLINK'
    ");
    $sth->execute($reflinkNo);

    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;

}

########################################################################
sub GetReferenceInfo {
########################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed to GetReferenceInfo method.";
    my $schema = $self->schema;
    my (%goevCode, %goEvidenceCode);
    $args{'goEvidenceCodeRef'} = \%goEvidenceCode;
    $self->_getGoevInfo(%args);
    my $goid = $args{'goid'};
    my $type = uc($args{'type'});
    my $No = $args{'No'};
    my $prikey = $goid."::".$No."::%";
    my $tabNm =  "GO_".$type."_GOEV";
    ### change GO_FEATURE_GOEV to GO_FEAT_GOEV 
    $tabNm =~ s/FEATURE/FEAT/; 
    my $priKeyCol = "GOID::".$type."_NO::GO_EVIDENCE_NO::IS_NOT";
    my $sth = $dbh->prepare("
        SELECT R.reference_no, R.citation, RL.primary_key, RL.date_created
        FROM   $schema.reference R, $schema.reflink RL
        WHERE  RL.primary_key like ?
        AND    tab_name = ?
        AND    primary_key_col = ?
        AND    R.reference_no = RL.reference_no
    ");
    $sth->execute($prikey, $tabNm, $priKeyCol);
    while(my ($refNo, $citation, $prikey, $dateCreated) = $sth->fetchrow()) {
        if ($prikey =~ /^${goid}::${No}::([0-9]+)::([NY])$/) {
	    my $goevNo = $1;
	    my $isNot = $2;
	    $goevCode{"${citation}::${refNo}::${isNot}::${dateCreated}"} 
                       .= "|".$goEvidenceCode{$goevNo};
	}
    }
    $sth->finish;
    my @citationEtc;
    foreach my $key (sort(keys %goevCode)) {
        $goevCode{$key} =~ s/^\|//;
        push(@citationEtc, "${key}::$goevCode{$key}");
    }
    return \@citationEtc;
}

####################################################################
sub GetGoEvidenceCodeArrayRef {
####################################################################
    my ($self, %args) = @_;
    if (!$args{'dbh'}) {
	die "A database handle must be passed to GetGoEvidenceCodeArrayRef method.";
    }
    my $dbh = $args{'dbh'};
    my $schema = $self->schema;
    my $sth = $dbh->prepare("
        SELECT evidence_code
        FROM   $schema.go_evidence
        ORDER  By evidence_code
    "); 
    $sth->execute;
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}


####################################################################
sub GetGoEvidenceInfo {
####################################################################
    my ($self, %args) = @_;
    if (!$args{'dbh'}) {
	die "A database handle must be passed to GetGoEvidenceInfo method.";
    }
    $self->_getGoevInfo(%args);
}

###################################################################
sub GetExternalIdSourceInfo {
###################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || 
	die "A database handle must be passed to GetExternalIdSourceInfo method.";
    my $tabNm = $args{'tab_name'};
    my $prikey = $args{'primary_key'};
    my $schema = $self->schema;
    my $sth = $dbh->prepare("
        SELECT external_id, source
        FROM   $schema.external_id
        WHERE  tab_name = ?
        AND    primary_key = ?
        ORDER BY source
    ");
    $sth->execute($tabNm, $prikey);
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;

}

####################################################################
sub _getGoevInfo {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'};
    my $schema = $self->schema;
    my $goEvidenceCodeRef = $args{'goEvidenceCodeRef'};
    my $goEvidenceNoRef = $args{'goEvidenceNoRef'};
    my $goEvidenceDesc4CodeRef = $args{'goEvidenceDesc4CodeRef'};
    my $goEvidenceDesc4NoRef =  $args{'goEvidenceDesc4NoRef'};
    my $goEvidenceExamples4CodeRef =  $args{'goEvidenceExamples4CodeRef'};
    my $goEvidenceExamples4NoRef =  $args{'goEvidenceExamples4NoRef'};
    my $sth = $dbh->prepare("
        SELECT go_evidence_no, evidence_code, description
        FROM   $schema.go_evidence
    ");
    if ($sth->execute) {
	while(my($goEvNo, $goEvCode, $descExamples) = $sth->fetchrow()) {
	    if ($goEvidenceCodeRef) {
		$$goEvidenceCodeRef{$goEvNo} = $goEvCode;
	    }
	    if ($goEvidenceNoRef) {
		$$goEvidenceNoRef{$goEvCode} = $goEvNo;
	    }
	    if (!$goEvidenceDesc4CodeRef && 
		!$goEvidenceDesc4NoRef &&
		!$goEvidenceExamples4CodeRef &&
		!$goEvidenceExamples4NoRef ) { 
		next; 
	    }
	    my ($desc, $examples) = split(/: /, $descExamples);
	    if ($goEvidenceDesc4CodeRef) {
		$$goEvidenceDesc4CodeRef{$goEvCode} = $desc;
	    }
	    if ($goEvidenceDesc4NoRef) {
		$$goEvidenceDesc4NoRef{$goEvNo} = $desc;
	    }
	    if ($goEvidenceExamples4CodeRef) {
		$$goEvidenceExamples4CodeRef{$goEvCode} = $examples;
	    }
	    if ($goEvidenceExamples4NoRef) {
		$$goEvidenceExamples4NoRef{$goEvNo} = $examples;
	    }
	}
    }
    $sth->finish;
}

####################################################################
sub DeleteAnnotationNew {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed to DeleteAnnotation method.";
    my $schema = $self->schema;
    my $externalIdNo = $args{'external_id_no'};
    my $refNo = $args{'reference_no'};
    my $goid = $args{'goid'};
    my $No = $args{'No'};
    my $eviCodeNo = $args{'evidence_code_no'};
    my $type = uc($args{'type'});
    my $NoNm = $type."_NO";
    my $tabNm = "GO_".$type."_GOEV";
    ### change GO_FEATURE_GOEV to GO_FEAT_GOEV 
    $tabNm =~ s/FEATURE/FEAT/;
    my $priKeyCol = "GOID::".$NoNm."::GO_EVIDENCE_NO::IS_NOT";
    my $priKey = "${goid}::${No}::${eviCodeNo}::%";

    ###### delete the specified external_id entry
    if ($externalIdNo) {
	
	my $sth = $dbh->prepare("
            DELETE from $schema.external_id
            WHERE  external_id_no = ?
        ");

	$sth->execute($externalIdNo);

	$dbh->commit;

    }

    ###### delete the specified reflink entry if there is no more
    ###### external_id entry associated with ...
    my $sth = $dbh->prepare("
            DELETE from $schema.reflink
            WHERE  tab_name = ?
            AND    reference_no = ?
            AND    primary_key_col = ?
            AND    primary_key like ?
            AND    reflink_no not in
                   (select primary_key from $schema.external_id 
                    where  tab_name = 'REFLINK')
    ");
    $sth->execute($tabNm, $refNo, $priKeyCol, $priKey);
    $dbh->commit;
    
    $sth = $dbh->prepare("
            SELECT reference_no
            FROM   $schema.reflink
            WHERE  tab_name = ?
            AND    primary_key_col = ?
            AND    primary_key like ?
    ");
    $sth->execute($tabNm, $priKeyCol, $priKey);
    my $arrayRef = $sth->fetchall_arrayref();
    if (!@$arrayRef) {
	$sth = $dbh->prepare("
            DELETE from $schema.$tabNm
            WHERE  goid = ?
            AND    $NoNm = ?
            AND    go_evidence_no = ?
        ");
	$sth->execute($goid, $No, $eviCodeNo);
	$sth->finish;
	$dbh->commit;
    }	
    else {

	foreach my $rowRef (@$arrayRef) {

	    my ($refNo) = @$rowRef;

	    print "The reference_no = $refNo is still associated with $priKey entry in $tabNm table. The entry for $priKey can not be deleted from $tabNm table.<p>";

        }

    }
}

####################################################################
sub DeleteAnnotation {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || die "A database handle must be passed to DeleteAnnotation method.";
    my $schema = $self->schema;
    my $refNo = $args{'reference_no'};
    my $goid = $args{'goid'};
    my $No = $args{'No'};
    my $type = uc($args{'type'});
    my $NoNm = $type."_NO";
    my $tabNm = "GO_".$type."_GOEV";
    ### change GO_FEATURE_GOEV to GO_FEAT_GOEV 
    $tabNm =~ s/FEATURE/FEAT/;
    my $priKeyCol = "GOID::".$NoNm."::GO_EVIDENCE_NO::IS_NOT";
    my $priKey = "${goid}::${No}::%";
    my $sth = $dbh->prepare("
            DELETE from $schema.reflink
            WHERE  tab_name = ?
            AND    reference_no = ?
            AND    primary_key_col = ?
            AND    primary_key like ?
    ");
    $sth->execute($tabNm, $refNo, $priKeyCol, $priKey);
    $dbh->commit;
    $sth = $dbh->prepare("
            SELECT reference_no
            FROM   $schema.reflink
            WHERE  tab_name = ?
            AND    primary_key_col = ?
            AND    primary_key like ?
    ");
    $sth->execute($tabNm, $priKeyCol, $priKey);
    my $arrayRef = $sth->fetchall_arrayref();
    if (!@$arrayRef) {
	$sth = $dbh->prepare("
                DELETE from $schema.$tabNm
                WHERE  goid = ?
                AND    $NoNm = ?
        ");
	$sth->execute($goid, $No);
	$sth->finish;
    }	
    $dbh->commit;
}

####################################################################
sub GetTotalAnnotatedGeneNum {
####################################################################
    my ($self, %args) = @_;
    my $dbh = $args{'dbh'} || 
	die "A database handle must be passed to GetTotalAnnotatedGeneNum method.";
    my $schema = $self->schema;
    my $sth = $dbh->prepare("
        SELECT count(unique locus_no)
        FROM   $schema.go_locus_goev
       union
        SELECT count(unique feature_no)
        FROM   $schema.go_feat_goev
    ");
    $sth->execute;
    my $count;
    while(my ($num) = $sth->fetchrow()) {
	$count += $num;
    }
    $sth->finish;
    return $count;

}

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

}


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


=pod

=head1 Name

Go.pm

=head1 Description

This perl object (Go.pm) acts as container for go info associated with a goid in oracle database. Once an object has been instantiated, several methods are available to retrieve the attributes of the object. 


=head1 Instantiating a New Go Object

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

my $goObj = Go->new(dbh=>$dbh, $colNm=>$value); 

where $colNm is goid and $dbh is a valid database handle to either dictyBase or SDEV. $value must be a valid value for the column that was provided, otherwise the script will die, with an appropriate error message.
 

Syntax example :

my $goObj = Go->new(dbh=>$dbh, goid=>$goid); 


=head1 Accessor Methods


All accessor methods take the form of : 


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


my $goTerm = $goObj->go_term; 


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


See valid columns in go table:

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

In addition, this object also provides following methods to access 
go related info in others.

=head2 goSynonymArrayRef

Usage :

foreach my $rowRef (@{$goObj->goSynonymArrayRef}) {

    my ($goSynonym) = @$rowRef;

    #####

}

This method returns go_synonym associated with the specified goid.


=head2 goFeatGoevArrayRef

Usage :

foreach my $rowRef (@{$goObj->goFeatGoevArrayRef}) {

    my ($feature_no, $feature_name, $go_evidence_code) = @$rowRef;

    ...

}

This method returns feature and go_evidence_code info for the specified goid.


=head2 goLocusGoevArrayRef

Usage :

foreach my $rowRef (@{$goObj->goLocusGoevArrayRef}) {

    my ($locus_no, $locus_name, $go_evidence_code) = @$rowRef;

    ...

}

This method returns locus and go_evidence_code info for the specified goid.


=head2 getLocusArrayRef

Usage :

foreach my $rowRef (@{$goObj->getLocusArrayRef}) {

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

    ######

}


This method returns all loci associated with the specified goid.


=head2 getFeatureArrayRef

Usage :

foreach my $rowRef (@{$goObj->getFeatureArrayRef}) {

    my ($featNo, $featNm) = $rowRef;

    ######

}


This method return all features associated with the specified goid.


=head2 getAmiGoLinkUrl

Usage :

my $amiGoUrl = $goObj->getAmiGoLinkUrl;

This method returns the AmiGO link url for the given goid.


=head2 GetReferenceInfo

Usage :

my $citationEtcArrayRef = Go->GetReferenceInfo(dbh=>$dbh,
					       goid=>$goid,
					       type=>$type,
					       No=>$No);


$type = [Feature|Locus]

$No = [$feature_no|$locus_no]

foreach my $row (@$citationEtcArrayRef) {

    my ($citation, $refNo, $isNot, $goevCodeList) = split(/::/, $row);

    #### $goevCodeList = goevCodes delimited by '|'
    #### e.g., 'TAS|IPI|IMP'

}


This method returns reference and go_evidence_code info associated with specified goid and feature_no/locus_no. 



=head2 getRow 

Usage:

my $row = $goObj->getRow;

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


=head1 DeleteAnnotation Method

Usage :

eval {

    Go->DeleteAnnotation(dbh=>$dbh,
			 reference_no=>$refNo,
			 goid=>$goid,
			 type=>$type,
			 No=>$No);

};

$type = [Feature|Locus]

$No = [$feature_no|$locus_no]


This method will delete go annotations for the specified goid, reference_no,
and feature_no/locus_no.


=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