#!/usr/bin/perl
package GoToDo;

####################################################################
##### Author :	Shuai Weng
##### Date   :  June 2001
##### Description : This package contains all necessary methods for 
#####               retrieving go to-do info for loci or features.
#####
#####  Usage: 
#####  use GoToDo;
#####
#####  To instantiate a new GoToDo object, you may use one of 
#####  following syntaxes:
#####  my $goToDoObj = GoToDo->new(dbh=>$dbh,
#####	  	                   type=$type);                       
#####  my $goToDoObj = GoToDo->new(database=>$database,
##### 		                   type=>$type); 
#####  
#####  type = [loci|features]
#####     
#####
#####  
#####  
##### 
#####  
#####        
####################################################################
use strict;
use DBI;
use lib "/usr/local/dicty/www_dictybase/db/lib/common";
use Login qw (ConnectToDatabase);
# use lib "/usr/local/dicty/www_dictybase/db/lib/dictyBase";
# use dictyBaseCentralMod qw(:getInfo :logInfo);

####################################################################
#################### 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 {
	print "No database name or database handle is passed to this object.";
	return;
    }
    $self->{'_type'} = $args{'type'};
    if (!$self->{'_type'}) {
	print "No type [loci|features] is passed to this object.";
	return;
    }
    $self->_initInfo;
    return $self;
}

####################################################################
sub _initInfo {
####################################################################
    my ($self) = @_;
    if ($self->type =~ /(loci|locus)/i) {
	$self->{'_giTabNm'} = "CGM_DDB.locus_gene_info";
	$self->{'_NoNm'} = "locus_no";
	$self->{'_Nm'} = "locus_name";
	$self->{'_goTabNm'} = "CGM_DDB.go_locus_goev";
	$self->{'_tabNm'} = "CGM_DDB.locus";
        $self->{'_goTab'} = "CGM_DDB.go";
        $self->{'_goEvTab'} = "CGM_DDB.go_evidence";
   }
    else {
	$self->{'_giTabNm'} = "CGM_DDB.feat_gene_info";
	$self->{'_NoNm'} = "feature_no";
	$self->{'_Nm'} = "feature_name";
	$self->{'_goTabNm'} = "CGM_DDB.go_feat_goev";
	$self->{'_tabNm'} = "CGM_DDB.feature";
        $self->{'_goTab'} = "CGM_DDB.go";
        $self->{'_goEvTab'} = "CGM_DDB.go_evidence";
    }
}

####################################################################
sub refNumArrayRef {
####################################################################
    my ($self, $topic) = @_;
    my $whereClause;
    if ($topic) {
	$topic = "\U$topic";
	$whereClause = "WHERE upper(literature_topic) = '$topic'";
    }
    my $giTabNm = $self->giTabNm;
    my $NoNm = $self->NoNm;
    my $sth = $dbh->prepare("
        SELECT count(unique reference_no), $NoNm 
        FROM   $giTabNm 
        $whereClause 
        GROUP BY $NoNm 
        ORDER BY count(unique reference_no)
    ");
    $sth->execute;
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

####################################################################
sub noGoAnnotationHashRef {
####################################################################
    my ($self, $withGP) = @_;
    my $whereClause;
    if ($withGP && $self->Nm =~ /locus_name/i) {
	$whereClause = "AND locus_no in (select locus_no from CGM_DDB.locus_gp)";
    }
    my $Nm = $self->Nm;
    my $NoNm = $self->NoNm;
    my $tabNm = $self->tabNm;
    my $goTabNm = $self->goTabNm;
    my $sth = $dbh->prepare("
        SELECT $Nm, $NoNm
        FROM   $tabNm
        WHERE  $NoNm not in
          (select $NoNm
           from   $goTabNm)
        $whereClause
    ");
    $sth->execute;
    my %Nm4No;
    while (my ($Nm, $No) = $sth->fetchrow()) {
	$Nm4No{$No} = $Nm;
    }
    $sth->finish;
    return \%Nm4No;
}

#######################################################################
sub annotationInfoArrayRef {
#######################################################################
    my ($self) = @_;
    my $goTabNm = $self->goTabNm;
    my $tabNm = $self->tabNm;
    my $NoNm = $self->NoNm;
    my $Nm = $self->Nm;
    ### example:######################################################
    # SELECT unique GLG.locus_no, L.locus_name, G.go_aspect, G.go_term
    # FROM   CGM_DDB.go_locus_goev GLG, CGM_DDB.locus L, CGM_DDB.go G
    # WHERE  GLG.locus_no = L.locus_no
    # AND    GLG.goid = G.goid
    # ORDER BY L.locus_name
    ##################################################################
    my $sth = $dbh->prepare("
        SELECT unique $goTabNm.$NoNm, $tabNm.$Nm, G.go_aspect, G.go_term
        FROM   $goTabNm, $tabNm, CGM_DDB.go G
        WHERE  $goTabNm.$NoNm = $tabNm.$NoNm
        AND    $goTabNm.goid = G.goid
        ORDER BY $tabNm.$Nm
    ");
    $sth->execute;
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}
#######################################################################
sub functionannotationEvNDInfoArrayRef {
#######################################################################
    my ($self) = @_;
    my $goTabNm = $self->goTabNm;
    my $giTabNm = $self->giTabNm;
    my $tabNm = $self->tabNm;
    my $NoNm = $self->NoNm;
    my $Nm = $self->Nm;
    my $goTab = $self->goTab;    
    my $goEvTab = $self->goEvTab;
    ### example:######################################################
    #SELECT  l.locus_name, l.locus_no
    #from CGM_DDB.locus l, CGM_DDB.locus_gene_info gi, CGM_DDB.go g, 
    #CGM_DDB.go_locus_goev gl, CGM_DDB.go_evidence e
    #where l.locus_no = gi.locus_no
    #and l.locus_no = gl.locus_no
    #and g.goid = gl.goid
    #and gl.go_evidence_no = e.go_evidence_no
    #and gi.literature_topic = 'Function/Process'
    #and g.go_aspect = 'F'
    #and e.evidence_code = 'ND'
    #and gl.date_created < gi.last_curated 
    #ORDER BY l.locus_name
    ##################################################################
    my $sth = $dbh->prepare(" 
        SELECT unique $tabNm.$Nm, $tabNm.$NoNm 
        FROM   $tabNm, $giTabNm, $goTab, $goTabNm, $goEvTab 
        WHERE  $tabNm.$NoNm = $giTabNm.$NoNm 
        AND    $tabNm.$NoNm = $goTabNm.$NoNm 
        AND    $goTab.goid = $goTabNm.goid 
        AND    $goTabNm.go_evidence_no = $goEvTab.go_evidence_no 
        AND    $giTabNm.literature_topic = 'Function/Process' 
        AND    $goTab.go_aspect = 'F'
        AND    $goEvTab.evidence_code = 'ND' 
        AND    $goTabNm.date_created < $giTabNm.last_curated 
        ORDER BY $tabNm.$Nm
    ");
    $sth->execute;
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}
#######################################################################
sub processannotationEvNDInfoArrayRef {
#######################################################################
    my ($self) = @_;
    my $goTabNm = $self->goTabNm;
    my $giTabNm = $self->giTabNm;
    my $tabNm = $self->tabNm;
    my $NoNm = $self->NoNm;
    my $Nm = $self->Nm;
    my $goTab = $self->goTab;    
    my $goEvTab = $self->goEvTab;
    my $sth = $dbh->prepare(" 
        SELECT unique $tabNm.$Nm, $tabNm.$NoNm 
        FROM   $tabNm, $giTabNm, $goTab, $goTabNm, $goEvTab 
        WHERE  $tabNm.$NoNm = $giTabNm.$NoNm 
        AND    $tabNm.$NoNm = $goTabNm.$NoNm 
        AND    $goTab.goid = $goTabNm.goid 
        AND    $goTabNm.go_evidence_no = $goEvTab.go_evidence_no 
        AND    $giTabNm.literature_topic = 'Function/Process' 
        AND    $goTab.go_aspect = 'P'
        AND    $goEvTab.evidence_code = 'ND' 
        AND    $goTabNm.date_created < $giTabNm.last_curated 
        ORDER BY $tabNm.$Nm
    ");
    $sth->execute;
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}
#######################################################################
sub componentannotationEvNDInfoArrayRef {
#######################################################################
    my ($self) = @_;
    my $goTabNm = $self->goTabNm;
    my $giTabNm = $self->giTabNm;
    my $tabNm = $self->tabNm;
    my $NoNm = $self->NoNm;
    my $Nm = $self->Nm;
    my $goTab = $self->goTab;    
    my $goEvTab = $self->goEvTab;
    my $sth = $dbh->prepare(" 
        SELECT unique $tabNm.$Nm, $tabNm.$NoNm 
        FROM   $tabNm, $giTabNm, $goTab, $goTabNm, $goEvTab 
        WHERE  $tabNm.$NoNm = $giTabNm.$NoNm 
        AND    $tabNm.$NoNm = $goTabNm.$NoNm 
        AND    $goTab.goid = $goTabNm.goid 
        AND    $goTabNm.go_evidence_no = $goEvTab.go_evidence_no 
        AND    $giTabNm.literature_topic = 'Cellular Location' 
        AND    $goTab.go_aspect = 'C'
        AND    $goEvTab.evidence_code = 'ND' 
        AND    $goTabNm.date_created < $giTabNm.last_curated 
        ORDER BY $tabNm.$Nm
    ");
    $sth->execute;
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}
#######################################################################
sub Nm4NoHashRef {
#######################################################################
    my ($self) = @_;
    my $Nm = $self->Nm;
    my $NoNm = $self->NoNm;
    my $tabNm = $self->tabNm;
    my $sth = $dbh->prepare("
        SELECT $Nm, $NoNm
        FROM   $tabNm
    ");
    $sth->execute;
    my %Nm4No;
    while(my($Nm, $No) = $sth->fetchrow()) {
	$Nm4No{$No} = $Nm;
    }
    $sth->finish;
    return \%Nm4No;
}

#######################################################################
sub GP4locusHashRef {
#######################################################################
    my ($self) = @_;
    my $sth = $dbh->prepare("
       SELECT L.locus_name, GP.gene_product
       FROM   CGM_DDB.locus L, CGM_DDB.gene_product GP, CGM_DDB.locus_gp LGP
       WHERE  L.locus_no = LGP.locus_no
       AND    LGP.gene_product_no = GP.gene_product_no
    ");
    $sth->execute;
    my %GP4locus;
    while(my($locusNm, $gp) = $sth->fetchrow()) {
	if ($GP4locus{$locusNm}){
	    $GP4locus{$locusNm} .= "; ";
	}
	$GP4locus{$locusNm} .= $gp;
    }
    $sth->finish;
    return \%GP4locus;
}

#######################################################################

sub database { $_[0]->{_database} }
sub type {$_[0]->{_type} }
sub giTabNm { $_[0]->{_giTabNm} }
sub goTabNm { $_[0]->{_goTabNm} }
sub tabNm { $_[0]->{_tabNm} }
sub NoNm { $_[0]->{_NoNm} }
sub Nm { $_[0]->{_Nm} }
sub goTab { $_[0]->{_goTab} }
sub goEvTab { $_[0]->{_goEvTab} }

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