#!/usr/bin/perl
package TextSearch_base;

##########################################################################
##### Author :	Shuai Weng
##### Date   :  June 2001
##### Description : This class contains all necessary methods for 
#####               searching information from following tables/columns:
#####
#####               locus.locus_name  (gene names)
#####               feature.feature_name (gene names)
#####               alias.alias_name
#####
#####               gene_product.gene_product
#####
#####               go.go_term
#####               go_synonym.go_synonym
#####
#####               colleague.last_name
#####
#####               author.author_name?
#####
#####               paragraph.paragraph_text
#####               
#####               abstract.abstract               
#####               
#####               feature.brief_id
#####               locus.description
#####
#####
#####  Usage: 
#####  use TextSearch;
#####
#####  To instantiate a new TextSearch object, you may use one of 
#####  following syntaxes:
#####  our $tsObj = TextSearch->new(dbh=>$dbh,
#####	  	                    query=>$query);
#####  our $tsObj = TextSearch->new(database=>$database,
##### 		                    query=>$query);
#####
#####  passing $dbh OR $database
#####    
#####
#####  our $geneNmArrayRef = $tsObj->geneNmArrayRef; 
#####                        # gene/feature/alias
#####  our $geneProductArrayRef = $tsObj->geneProductArrayRef;
#####  our $goTermArrayRef = $tsObj->goTermArrayRef;
#####  our $colleagueArrayRef = $tsObj->colleagueArrayRef;    
#####  our $authorArrayRef = $tsObj->authorArrayRef;
#####  our $phraseArrayRef = $tsObj->phraseArrayRef;
#####  our $abstractArrayRef = $tsObj->abstractArrayRef;
#####  our $descriptionArrayRef = $tsObj->descriptionArrayRef;
#####
#####  our $phenotypeArrayRef = $tsObj->phenotypeArrayRef;
#####  
#####
#####  
#######################################################################
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/Objects";
use ConfigPathdictyBase;

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

our $dbh;

#######################################################################
sub new {      ############ constructor ###############################
#######################################################################
       
    my ($type, %args) = @_;

    my $self = {};
    bless $self, $type;

    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->{'_query'} = $args{'query'};
    $self->{'_type'} = $args{'type'};

    $self->_processQuery;

    return $self;
}

########################################################################
sub _processQuery {
########################################################################
    my ($self) = @_;
    $self->{'_query'} =~ s/\*/\%/g;
    $self->{'_query'} =~ tr/a-z/A-Z/;

    $self->{'_oriquery'} = $self->{'_query'};

    if ($self->{'_type'}) {
	$self->{'_query'} =~ s/[,;:\t\f\n\r]/ /g;
	$self->{'_query'} =~ s/ +/ /g;
    }

    my @geneBasedQuery;

    if (!$self->{'_type'} || $self->{'_query'} !~ / /) { 
	push(@geneBasedQuery, $self->{'_oriquery'});
    }
    elsif ($self->{'_type'} =~ /^Any word/i) {
	$self->{'_query'} =~ s/[,;:\t\f\n\r]/ /g;
	$self->{'_query'} =~ s/ +/ /g;
	
	@geneBasedQuery = split(/ /, $self->{'_query'});
    }
    elsif ($self->{'_type'} =~ /Boolean/i && 
	   $self->{'_query'} !~ / and /i) {

	$self->{'_query'} =~ s/[ \t\f\n\r]/ /g;
	$self->{'_query'} =~ s/[\(\)]/ /g;
	$self->{'_query'} =~ s/ +/ /g;

	my @word = split(/ /, $self->{'_query'});

	foreach my $word (@word) {

	    if ($word =~ /^or$/i) { next; }

	    push(@geneBasedQuery, $word);
	    
	}	

    }

    my @char = (' ', '(', '>', '[');

 
    $self->{'_geneBasedQueryRef'} = \@geneBasedQuery;

    if (!$self->{'_type'} || 
	$self->{'_type'} =~ /^EXACT phrase/i ||
	$self->{'_query'} !~ / /) { 

        ### for quick and text searches

	if ($self->{'_query'} !~ /\%/) {
	    $self->{'_query'} .= "%";
	}
       
	my @whereValues = $self->{'_query'};
	$self->{'_whereClause'} = "upper(SUBSTITUTE_TEXT) like ?";
	
	if ($self->{'_query'} !~ /^\%/) {
	    
	    foreach my $char (@char) {
		push(@whereValues, "%".$char.$self->{'_query'});
		$self->{'_whereClause'} .= " OR upper(SUBSTITUTE_TEXT) like ?";
	    }
	    
	}
	$self->{'_whereValuesRef'} = \@whereValues;   
	$self->{'_whereClause'} = "(".$self->{'_whereClause'}.")";

	return;

    }

    ### for advanced keyword search which supports 
    ### boolean operators
    if ($self->{'_type'} =~ /^(All|Any) word/i) {

	$self->{'_query'} =~ s/[,;:\t\f\n\r]/ /g;
	$self->{'_query'} =~ s/ +/ /g;
	
	my @word = split(/ /, $self->{'_query'});

	my $andOr;
	if ($self->{'_type'} =~ /^Any word/i) { 
	    $andOr = "OR";
	}
	else {
	    $andOr = "AND";
	}
	    
	my @whereValues;
	    
	foreach my $word (@word) {

	    if ($self->{'_whereClause'}) { 
		$self->{'_whereClause'} .= " $andOr ";
	    }

	    if ($word !~ /\%/) { $word .= "%"; }
	    

	    my $whereClause = "upper(SUBSTITUTE_TEXT) like ?";

	    push(@whereValues, $word);
 
	    if ($word =~ /^\%/) { next; }
		
	    foreach my $char (@char) {
		    
		my $thisWord = "%${char}${word}";
		    
		$whereClause .= " OR upper(SUBSTITUTE_TEXT) like ?";
		
		push(@whereValues, $thisWord);

	    }    

	    $self->{'_whereClause'} .= "($whereClause)";
	    
	}
	$self->{'_whereClause'} = "(".$self->{'_whereClause'}.")";
	$self->{'_whereValuesRef'} = \@whereValues;    
       
	return;

    }

    if ($self->{'_type'} =~ /Boolean/i) {

	$self->{'_query'} =~ s/[ \t\f\n\r]/ /g;
	$self->{'_query'} =~ s/([\(\)])/ $1 /g;
	$self->{'_query'} =~ s/ +/ /g;

	my @word = split(/ /, $self->{'_query'});

	my @whereValues; 

	my $searchStr;
	my $operator;
	foreach my $word (@word) {

	    if (!$word || $word =~ /^ +$/) { next; }
 
	    if ($word =~ /^(\(|\)|AND|OR)$/i) {

		$self->{'_whereClause'} .= $operator;

		if ($searchStr) {

		    if ($searchStr !~ /\%/) { $searchStr .= "%"; }
		   			
		    my $whereClause = "upper(SUBSTITUTE_TEXT) like ?";

		    push(@whereValues, $searchStr);
 
		    if ($searchStr =~ /^\%/) { next; }
	
		    foreach my $char (@char) {
		    
			my $thisSearchStr = "%${char}${searchStr}";
		    
			$whereClause .= " OR upper(SUBSTITUTE_TEXT) like ?";
		
			push(@whereValues, $thisSearchStr);
		    }    

		    $self->{'_whereClause'} .= "($whereClause)";

		    undef $searchStr; 
		}
		$operator = " $word ";

	    }
	    else {
		
		if ($searchStr) { $searchStr .= " "; }

		$searchStr .= $word;

	    }
	}
	
	if ($searchStr) {
	
	    if ($searchStr !~ /\%/) { $searchStr .= "%"; }

	    my $whereClause = "upper(SUBSTITUTE_TEXT) like ?";

	    push(@whereValues, $searchStr);
 
	    if ($searchStr !~ /^\%/) { 
		
		foreach my $char (@char) {
		    
		    my $thisSearchStr = "%${char}${searchStr}";
		   
		    $whereClause .= " OR upper(SUBSTITUTE_TEXT) like ?";
		
		    push(@whereValues, $thisSearchStr);
		    
		}    
		$self->{'_whereClause'} .= $operator."($whereClause)";
		
	    }
	}
	elsif ($operator){
	
	    $self->{'_whereClause'} .= $operator;
 
	}
	    
	$self->{'_whereValuesRef'} = \@whereValues;    
	$self->{'_whereClause'} = "(".$self->{'_whereClause'}.")";

#####
	# print $self->{'_whereClause'}, "<p>";

#####
	

    }

}

########################################################################
sub colleagueArrayRef {
########################################################################
    my ($self) = @_;
    my $sth = $dbh->prepare("
        SELECT colleague_no, last_name, first_name, suffix
        FROM   CGM_DDB.colleague
        WHERE  upper(last_name) like ?
        ORDER BY upper(last_name), upper(first_name)
    ");
    $sth->execute($self->{'_query'});
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

########################################################################
sub authorArrayRef {
########################################################################
    my ($self) = @_;
    my $sth = $dbh->prepare("
        SELECT author_no, author_name
        FROM   CGM_DDB.author
        WHERE  upper(author_name) like ?
        ORDER BY upper(author_name)
    ");
    $sth->execute($self->{'_query'});
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

########################################################################
sub geneNmArrayRef {
########################################################################
    my ($self) = @_;
    #### match locus_name

    my @array;

    foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	my $sth = $dbh->prepare("
            SELECT unique F.feature_name, L.locus_name 
            FROM   CGM_DDB.locus L, CGM_DDB.feature F
            WHERE  F.locus_no(+) = L.locus_no
            AND    upper(L.locus_name) like ?  
          UNION
            SELECT unique F.feature_name, L.locus_name 
            FROM   CGM_DDB.feature F, CGM_DDB.locus L
            WHERE  F.locus_no = L.locus_no(+) 
            AND    upper(F.feature_name) like ?
            AND    (upper(L.locus_name) not like ? or
                   L.locus_name is null)
          UNION
            SELECT unique F.feature_name, L.locus_name
            FROM   CGM_DDB.alias A, CGM_DDB.feature F, CGM_DDB.locus L, 
                   CGM_DDB.locus_alias AL
            WHERE  AL.alias_no = A.alias_no
            AND    AL.locus_no = L.locus_no
            AND    L.locus_no = F.locus_no(+)
            AND    upper(A.alias_name) like ?
            AND    upper(L.locus_name) not like ?
            AND    (upper(F.feature_name) not like ? or
                   F.feature_name is null)
          UNION
            SELECT unique F.feature_name, L.locus_name
	    FROM   CGM_DDB.feature F, CGM_DDB.locus L, CGM_DDB.alias A
            WHERE  A.feature_no = F.feature_no
            AND    F.locus_no = L.locus_no(+)
            AND    upper(A.alias_name) like ?
            AND    upper(F.feature_name) not like ?
            AND    (upper(L.locus_name) not like ? or
                   L.locus_name is null)
       ");
       $sth->execute($query, $query, $query,
		     $query, $query, $query,
		     $query, $query, $query);
	my $thisArrayRef = $sth->fetchall_arrayref();
	$sth->finish;
	push(@array, @$thisArrayRef);
    }
    return \@array;

} 

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

    my @array;

    foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	#### match locus_name
	my $sth = $dbh->prepare("
            SELECT unique F.feature_name, L.locus_name 
            FROM   CGM_DDB.locus L, CGM_DDB.feature F
            WHERE  F.locus_no = L.locus_no
            AND    upper(L.locus_name) like ?  
            AND    F.feature_no in 
                   (select feature_no from CGM_DDB.protein_info) 
          UNION
            SELECT unique F.feature_name, L.locus_name 
            FROM   CGM_DDB.feature F, CGM_DDB.locus L
            WHERE  F.locus_no = L.locus_no(+)
            AND    upper(F.feature_name) like ?
            AND    (upper(L.locus_name) not like ? or
                   L.locus_name is null)
            AND    F.feature_no in
                   (select feature_no from CGM_DDB.protein_info)
          UNION
            SELECT unique F.feature_name, L.locus_name
            FROM   CGM_DDB.alias A, CGM_DDB.feature F, CGM_DDB.locus L, 
                   CGM_DDB.locus_alias AL
            WHERE  AL.alias_no = A.alias_no
            AND    AL.locus_no = L.locus_no
            AND    L.locus_no = F.locus_no(+)
            AND    upper(A.alias_name) like ?
            AND    upper(L.locus_name) not like ?
            AND    upper(F.feature_name) not like ?
            AND    F.feature_no in
                   (select feature_no from CGM_DDB.protein_info)
          UNION
            SELECT unique F.feature_name, L.locus_name
	    FROM   CGM_DDB.feature F, CGM_DDB.locus L, CGM_DDB.alias A
            WHERE  A.feature_no = F.feature_no
            AND    F.locus_no = L.locus_no(+)
            AND    upper(A.alias_name) like ?
            AND    upper(F.feature_name) not like ?
            AND    (upper(L.locus_name) not like ? or
                   L.locus_name is null)
            AND    F.feature_no in
                   (select feature_no from CGM_DDB.protein_info)
            ORDER BY upper(F.feature_name)
        ");
	$sth->execute($query, $query, $query,
		     $query, $query, $query,
		     $query, $query, $query);
	my $thisArrayRef = $sth->fetchall_arrayref();
	$sth->finish;
	push(@array, @$thisArrayRef);
    }
    return \@array;

}

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

    my @array;

    foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	#### match locus_name
	my $sth = $dbh->prepare("
            SELECT unique F.feature_name, L.locus_name 
            FROM   CGM_DDB.locus L, CGM_DDB.feature F
            WHERE  F.locus_no = L.locus_no
            AND    upper(L.locus_name) like ?  
            AND    F.feature_no in 
                   (select feature_no from CGM_DDB.display_seq) 
          UNION
            SELECT unique F.feature_name, L.locus_name 
            FROM   CGM_DDB.feature F, CGM_DDB.locus L
            WHERE  F.locus_no = L.locus_no(+)
            AND    upper(F.feature_name) like ?
            AND    (upper(L.locus_name) not like ? or
                   L.locus_name is null)
            AND    F.feature_no in
                   (select feature_no from CGM_DDB.display_seq)
          UNION
            SELECT unique F.feature_name, L.locus_name
            FROM   CGM_DDB.alias A, CGM_DDB.feature F, CGM_DDB.locus L, 
                   CGM_DDB.locus_alias AL
            WHERE  AL.alias_no = A.alias_no
            AND    AL.locus_no = L.locus_no
            AND    L.locus_no = F.locus_no(+)
            AND    upper(A.alias_name) like ?
            AND    upper(L.locus_name) not like ?
            AND    upper(F.feature_name) not like ?
            AND    F.feature_no in
                   (select feature_no from CGM_DDB.display_seq)
          UNION
            SELECT unique F.feature_name, L.locus_name
	    FROM   CGM_DDB.feature F, CGM_DDB.locus L, CGM_DDB.alias A
            WHERE  A.feature_no = F.feature_no
            AND    F.locus_no = L.locus_no(+)
            AND    upper(A.alias_name) like ?
            AND    upper(F.feature_name) not like ?
            AND    (upper(L.locus_name) not like ? or
                   L.locus_name is null)
            AND    F.feature_no in
                   (select feature_no from CGM_DDB.display_seq)
            ORDER BY upper(F.feature_name)
       ");
	$sth->execute($query, $query, $query,
		     $query, $query, $query,
		     $query, $query, $query);
	my $thisArrayRef = $sth->fetchall_arrayref();
	$sth->finish;
	push(@array, @$thisArrayRef);
    }
    return @array;
    
}

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

    my @array;

    foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	my $sth = $dbh->prepare("
           SELECT unique dictyBaseid, tab_name, primary_key 
           FROM   CGM_DDB.dictyBaseid
           WHERE  dictyBaseid like ?  
        ");
        $sth->execute($query);
	my $thisArrayRef = $sth->fetchall_arrayref();
	$sth->finish;
	push(@array, @$thisArrayRef);
    }
    return \@array;

}

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

    my @array;

    foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	my $sth = $dbh->prepare("
           SELECT external_id, tab_name, primary_key
           FROM   CGM_DDB.external_id
           WHERE  external_id like ?
           AND    source = 'EC'
        ");
	$sth->execute($query);
	my $thisArrayRef = $sth->fetchall_arrayref();
	$sth->finish;
	push(@array, @$thisArrayRef);

    }
    return \@array;
    
}

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

    my $whereClause = $self->{'_whereClause'};

    $whereClause =~ s/SUBSTITUTE_TEXT/gene_product/g;
    
    my $sth = $dbh->prepare("
            SELECT gene_product_no, gene_product
            FROM   CGM_DDB.gene_product
            WHERE  $whereClause
            ORDER BY upper(gene_product)
    ");

#    print "SELECT gene_product_no, gene_product<br>";
#    print "FROM   CGM_DDB.gene_product<br>";
#    print "WHERE  $whereClause<br>";
#    print "ORDER BY upper(gene_product)<br>";
#    print "@{$self->{'_whereValuesRef'}}<p>";

    $sth->execute(@{$self->{'_whereValuesRef'}});
    
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

########################################################################
sub goTermArrayRef {
########################################################################
    my ($self) = @_;
    
    my $whereClause1 = $self->{'_whereClause'};
    my $whereClause2 = $self->{'_whereClause'};

    $whereClause1 =~ s/SUBSTITUTE_TEXT/go_term/g;
    $whereClause2 =~ s/SUBSTITUTE_TEXT/GS\.go_synonym/g;

    my $sth = $dbh->prepare("
            SELECT goid, go_term as term, 
                   go_aspect as aspect
            FROM   CGM_DDB.go
            WHERE  $whereClause1
          UNION  
            SELECT G.goid, GS.go_synonym as term, 
                   G.go_aspect as aspect
            FROM   CGM_DDB.go G, CGM_DDB.go_synonym GS, CGM_DDB.go_gosyn GGS
            WHERE  $whereClause2 
            AND    GGS.go_synonym_no = GS.go_synonym_no
            AND    G.goid = GGS.goid  
            ORDER BY  term, aspect
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}},
		  @{$self->{'_whereValuesRef'}});
    
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

########################################################################
sub phraseArrayRef {
########################################################################
    my ($self) = @_;
    
    my $whereClause = $self->{'_whereClause'};
	
    $whereClause =~ s/SUBSTITUTE_TEXT/P\.phrase_text/g;

    my $sth = $dbh->prepare("
            SELECT unique P.phrase_text, L.locus_name
            FROM   CGM_DDB.phrase P, CGM_DDB.locus L
            WHERE  $whereClause
            AND    P.paragraph_no = L.paragraph_no
            ORDER BY P.phrase_text, L.locus_name
    ");
	
    $sth->execute(@{$self->{'_whereValuesRef'}});
    
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

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

    my $whereClause = $self->{'_whereClause'};

    $whereClause =~ s/SUBSTITUTE_TEXT/external_id_name/g;

    my $sth = $dbh->prepare("
            SELECT primary_key, external_id_name
            FROM   CGM_DDB.external_id
            WHERE  source = 'MetaCyc'
            AND    $whereClause
            ORDER BY upper(external_id_name)
    ");

    $sth->execute(@{$self->{'_whereValuesRef'}});
    
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}


########################################################################
sub abstractArrayRef {
########################################################################
    my ($self) = @_;
   
    my $whereClause = $self->{'_whereClause'};
	
    $whereClause =~ s/SUBSTITUTE_TEXT/abstract/g;
	
    my $sth = $dbh->prepare("
            SELECT reference_no, abstract
            FROM   CGM_DDB.abstract
            WHERE  $whereClause
    ");
	
    $sth->execute(@{$self->{'_whereValuesRef'}});
    
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;

}

########################################################################
sub descriptionArrayRef {
########################################################################
    my ($self) = @_;
   
    my $whereClause1 = $self->{'_whereClause'};
    my $whereClause2 = $self->{'_whereClause'};
	
    $whereClause1 =~ s/SUBSTITUTE_TEXT/description/g;
    $whereClause2 =~ s/SUBSTITUTE_TEXT/brief_id/g;

    my $sth = $dbh->prepare("
            SELECT description, locus_name
            FROM   CGM_DDB.locus
            WHERE  $whereClause1
              UNION  
            SELECT brief_id, feature_name 
            FROM   CGM_DDB.feature
            WHERE  $whereClause2
            ORDER BY 1
    ");
	
    $sth->execute(@{$self->{'_whereValuesRef'}},
		  @{$self->{'_whereValuesRef'}});
    
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;

}

########################################################################
sub phenotypeArrayRef {
########################################################################
    my ($self) = @_;
  
    my $whereClause = $self->{'_whereClause'};
	
    $whereClause =~ s/SUBSTITUTE_TEXT/phenotype/g;

    my $sth = $dbh->prepare("
            SELECT phenotype
            FROM   CGM_DDB.phenotype
            WHERE  $whereClause
            ORDER BY phenotype
    ");
    
    $sth->execute(@{$self->{'_whereValuesRef'}});
    
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

########################################################################
sub noteArrayRef {
########################################################################
    my ($self) = @_;
    
    my $whereClause = $self->{'_whereClause'};

    $whereClause =~ s/SUBSTITUTE_TEXT/CN\.note/g;

    my $sth = $dbh->prepare("
            SELECT CN.note, L.locus_name
            FROM   CGM_DDB.curator_note CN, CGM_DDB.locus_cn LCN, 
                   CGM_DDB.locus L
            WHERE  $whereClause
            AND    CN.is_public = 'Y'
            AND    CN.curator_note_no = LCN.curator_note_no
            AND    LCN.locus_no = L.locus_no
          UNION  
            SELECT CN.note, F.feature_name
            FROM   CGM_DDB.curator_note CN, CGM_DDB.feat_cn FCN, 
                   CGM_DDB.feature F
            WHERE  $whereClause
            AND    CN.is_public = 'Y'
            AND    CN.curator_note_no = FCN.curator_note_no
            AND    FCN.feature_no = F.feature_no
            ORDER BY 1, 2
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}},
		  @{$self->{'_whereValuesRef'}});
    
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;

}

########################################################################
sub citationArrayRef {
########################################################################
    my ($self) = @_;
  
    my $whereClause = $self->{'_whereClause'};
    $whereClause =~ s/SUBSTITUTE_TEXT/citation/g;

    my $sth = $dbh->prepare("
            SELECT reference_no, citation
            FROM   CGM_DDB.reference
            WHERE  $whereClause
            ORDER BY upper(citation)
    ");
    
    $sth->execute(@{$self->{'_whereValuesRef'}});

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

}


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

    my $whereClause = $self->{'_whereClause'};

    $whereClause =~ s/SUBSTITUTE_TEXT/remark/g;

    my $sth = $dbh->prepare("
            SELECT colleague_no, remark
            FROM   CGM_DDB.colleague_remark
            WHERE  $whereClause
            AND    remark_type = 'Research Interest'
            ORDER BY remark
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}});
   
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
    
}

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

    my $whereClause = $self->{'_whereClause'};

    $whereClause =~ s/SUBSTITUTE_TEXT/keyword/g;

    my $sth = $dbh->prepare("
            SELECT keyword_no, keyword
            FROM   CGM_DDB.keyword
            WHERE  $whereClause
            ORDER BY keyword
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}});
   
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
    
}

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

    my $whereClause = $self->{'_whereClause'};

    $whereClause =~ s/SUBSTITUTE_TEXT/keyword/g;

    my $sth = $dbh->prepare("
            SELECT keyword_no, keyword
            FROM   CGM_DDB.keyword
            WHERE  $whereClause
            AND    source = 'Curator-defined'
            ORDER BY keyword
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}});
   
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
    
}

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

    my $whereClause1 = $self->{'_whereClause'};
    my $whereClause2 = $whereClause1;
    my $whereClause3 = $whereClause1;
    my $whereClause4 = $whereClause1;

    $whereClause1 =~ s/SUBSTITUTE_TEXT/city/g;
    $whereClause2 =~ s/SUBSTITUTE_TEXT/state/g;
    $whereClause3 =~ s/SUBSTITUTE_TEXT/country/g;
    $whereClause4 =~ s/SUBSTITUTE_TEXT/institution/g;

    my $sth = $dbh->prepare("
            SELECT colleague_no, last_name, first_name, suffix, city, 
                   state, country, institution 
            FROM   CGM_DDB.colleague
            WHERE  $whereClause1
            OR     $whereClause2
            OR     $whereClause3
            OR     $whereClause4
            ORDER BY last_name, first_name
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}},
		  @{$self->{'_whereValuesRef'}},
		  @{$self->{'_whereValuesRef'}},
		  @{$self->{'_whereValuesRef'}});
   
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
    
}

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

    my @array;

    foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	#### match locus_name
	my $sth = $dbh->prepare("
           SELECT L.locus_name, C.colleague_no, C.last_name, 
                  C.first_name, C.suffix 
           FROM   CGM_DDB.locus L, CGM_DDB.coll_locus CL, CGM_DDB.colleague C 
           WHERE  upper(L.locus_name) like ?
           AND    L.locus_no = CL.locus_no
           AND    CL.colleague_no = C.colleague_no
        ");
        $sth->execute($query);
	
	my $thisArrayRef = $sth->fetchall_arrayref();
	
	$sth->finish;

	push(@array, @$thisArrayRef);

    }

    return \@array;

}


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

    my $configPath = ConfigPathdictyBase->new;

    my $datafile = $configPath->dataDir."sacch3d/emotif/emotif.data";

    open(IN, "$datafile") || 
	die "Can't open '$datafile' for reading:$!\n";

    my %features4emotif;
    my %urlDesc4emotif;

    while(<IN>) {
	
	my ($orf, $orflen, $emotif, $pvalue, $beg, $end, $url, $desc) 
	    = split(/\t/);

	foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	    $query =~ s/\%/\.\*/g;
	    
	    if ($emotif =~ /$query/i || $desc =~ /$query/i) {

		if (!$urlDesc4emotif{$emotif}) {

		    $urlDesc4emotif{$emotif} = $url."\t".$desc;

		}
		if ($features4emotif{$emotif}) {

		    $features4emotif{$emotif} .= ":";

		}
		$features4emotif{$emotif} .= $orf;

		last;

	    }

        }
       
    }
    close(IN);

    my @array;

    foreach my $emotif (sort (keys %urlDesc4emotif)) {

	my ($url, $desc) = split(/\t/, $urlDesc4emotif{$emotif});

	my @row = ($emotif, $url, $desc, $features4emotif{$emotif});

	push (@array, \@row);
	
    }

    return \@array;

}

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

    my $whereClause1 = $self->{'_whereClause'};
    my $whereClause2 = $self->{'_whereClause'};

    $whereClause1 =~ s/SUBSTITUTE_TEXT/sequence_name/g;
    $whereClause2 =~ s/SUBSTITUTE_TEXT/note/g;
    
    my $sth = $dbh->prepare("
            SELECT pdb_sequence_no, sequence_name, organism, note
            FROM   CGM_DDB.pdb_sequence
            WHERE  (($whereClause1) or ($whereClause2))
            AND    source = 'PDB'
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}}, 
		  @{$self->{'_whereValuesRef'}});
   
    my $arrayRef = $sth->fetchall_arrayref();

    $sth->finish;

    return $arrayRef;

    
}


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

    my $collNum;

    foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	my $sth = $dbh->prepare("
            SELECT count(colleague_no)
            FROM   CGM_DDB.colleague
            WHERE  upper(last_name) like ?
        ");
 
	$sth->execute($query);
	
	my $count = $sth->fetchrow;

	$collNum += $count;

	$sth->finish;

	if ($count) { next; }


	if ($query =~ /\%/) { next; }

	$sth = $dbh->prepare("
            SELECT count(colleague_no)
            FROM   CGM_DDB.colleague
            WHERE  upper(last_name) like ?
        "); 

	$query .= "%";

	$sth->execute($query);

	$count = $sth->fetchrow;

	$collNum += $count;

	$sth->finish;

    }
    return $collNum;

}

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

    my $authorNum;
    
    foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	my $sth = $dbh->prepare("
            SELECT count(author_no)
            FROM   CGM_DDB.author
            WHERE  upper(author_name) like ?
        ");

	if ($query !~ /\%/) { $query .= '%'; }

	$sth->execute($query);
    
	my $count = $sth->fetchrow;

	$authorNum += $count;

	$sth->finish;

    }
    return $authorNum;

}

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

    my $geneNum = 0;

    foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	#### match locus_name
	my $sth = $dbh->prepare("
           SELECT count(unique L.locus_name) 
           FROM   CGM_DDB.locus L, CGM_DDB.feature F
           WHERE  F.locus_no(+) = L.locus_no
           AND    upper(L.locus_name) like ?  
        ");
        $sth->execute($query);
	my $count = $sth->fetchrow;
	$geneNum += $count;
	$sth->finish;

	#### match feature_name
	$sth = $dbh->prepare("
           SELECT count(unique F.feature_name) 
           FROM   CGM_DDB.feature F, CGM_DDB.locus L
           WHERE  upper(F.feature_name) like ?
           AND    F.locus_no = L.locus_no(+) 
           AND    (upper(L.locus_name) not like ? or
                  F.locus_no is null)
        ");
        $sth->execute($query, $query);
	$count = $sth->fetchrow;
	$geneNum += $count;
	$sth->finish;
    
        #### match locus alias
        $sth = $dbh->prepare("
           SELECT count(unique L.locus_name)
           FROM   CGM_DDB.alias A, CGM_DDB.feature F, CGM_DDB.locus L, 
                  CGM_DDB.locus_alias AL
           WHERE  AL.alias_no = A.alias_no
           AND    AL.locus_no = L.locus_no
           AND    L.locus_no = F.locus_no(+)
           AND    upper(A.alias_name) like ?
           AND    upper(L.locus_name) not like ?
           AND    (upper(F.feature_name) not like ? or
                  F.feature_name is null)
        ");
	$sth->execute($query, $query, $query);
	$count = $sth->fetchrow;
	$geneNum += $count;
	$sth->finish;

	#### match feature alias
	$sth = $dbh->prepare("
	   SELECT count(unique F.feature_name)
	   FROM   CGM_DDB.feature F, CGM_DDB.locus L, CGM_DDB.alias A
           WHERE  A.feature_no = F.feature_no
           AND    F.locus_no = L.locus_no(+)
           AND    upper(A.alias_name) like ?
           AND    upper(F.feature_name) not like ?
           AND    (upper(L.locus_name) not like ? or
                  L.locus_name is null)  
        ");
	$sth->execute($query, $query, $query);
	$count = $sth->fetchrow;
	$geneNum += $count;
	$sth->finish;
    }

    return $geneNum;

}

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

    my $proteinNum;

    foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	#### match locus_name
	my $sth = $dbh->prepare("
           SELECT count(unique L.locus_name) 
           FROM   CGM_DDB.locus L, CGM_DDB.feature F
           WHERE  upper(L.locus_name) like ?
           AND    L.locus_no = F.locus_no
           AND    F.feature_no in 
                  (select feature_no from CGM_DDB.protein_info) 
        ");
	$sth->execute($query);
	my $count = $sth->fetchrow;
	$proteinNum += $count;
	$sth->finish;
    
        #### match feature_name
	$sth = $dbh->prepare("
           SELECT count(unique F.feature_name) 
           FROM   CGM_DDB.feature F, CGM_DDB.locus L
           WHERE  upper(F.feature_name) like ?
           AND    F.locus_no = L.locus_no(+) 
           AND    (upper(L.locus_name) not like ? or
                  F.locus_no is null)
           AND    F.feature_no in 
                  (select feature_no from CGM_DDB.protein_info) 
        ");
        $sth->execute($query, $query);
	$count = $sth->fetchrow;
	$proteinNum += $count;
	$sth->finish;
    
        #### match locus alias
	$sth = $dbh->prepare("
            SELECT count(unique L.locus_name)
            FROM   CGM_DDB.alias A, CGM_DDB.feature F, CGM_DDB.locus L, 
                   CGM_DDB.locus_alias AL
            WHERE  upper(A.alias_name) like ?
            AND    A.alias_no = AL.alias_no
            AND    AL.locus_no = L.locus_no
            AND    upper(L.locus_name) not like ?
            AND    L.locus_no = F.locus_no
            AND    upper(F.feature_name) not like ?
            AND    F.feature_no in 
                   (select feature_no from CGM_DDB.protein_info) 
        ");
	$sth->execute($query, $query, $query);
	$count = $sth->fetchrow;
	$proteinNum += $count;
	$sth->finish;
	
	#### match feature alias
	$sth = $dbh->prepare("
	    SELECT count(unique F.feature_name)
	    FROM   CGM_DDB.feature F, CGM_DDB.locus L, CGM_DDB.alias A
            WHERE  upper(A.alias_name) like ?
            AND    A.feature_no = F.feature_no
            AND    upper(F.feature_name) not like ?
            AND    F.locus_no = L.locus_no(+)
            AND    (upper(L.locus_name) not like ? or
                   L.locus_name is null)  
            AND    F.feature_no in
                   (select feature_no from CGM_DDB.protein_info) 
        ");
	$sth->execute($query, $query, $query);

	$count = $sth->fetchrow;
	$proteinNum += $count;
	$sth->finish;
    }
    return $proteinNum;

}


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

    my $geneNum = 0;

    foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	#### match locus_name
	my $sth = $dbh->prepare("
           SELECT count(unique locus_name) 
           FROM   CGM_DDB.locus 
           WHERE  upper(locus_name) like ?
           AND    locus_no in (select locus_no from CGM_DDB.coll_locus)
        ");
        $sth->execute($query);
	my $count = $sth->fetchrow;
	$geneNum += $count;
	$sth->finish;

    }

    return $geneNum;

}

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

    my $dictyBaseidNum;

    foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	my $sth = $dbh->prepare("
           SELECT count(unique dictyBaseid) 
           FROM   CGM_DDB.dictyBaseid
           WHERE  dictyBaseid like ?  
        ");
	$sth->execute($query);
	my $count = $sth->fetchrow;
	$dictyBaseidNum += $count; 
	$sth->finish;

    }

    return $dictyBaseidNum;

}

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

    foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	my $sth = $dbh->prepare("
           SELECT count(unique external_id) 
           FROM   CGM_DDB.external_id
           WHERE  external_id like ?
           AND    source = 'EC'
        ");

	$sth->execute($query);

	my $count = $sth->fetchrow;

	$ecNum += $count;

	$sth->finish;

    }

    return $ecNum;
    
}

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

    my $seqNum = 0;

    foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	#### match locus_name
	my $sth = $dbh->prepare("
           SELECT count(unique L.locus_name) 
           FROM   CGM_DDB.locus L, CGM_DDB.feature F
           WHERE  upper(L.locus_name) like ?
           AND    L.locus_no = F.locus_no
           AND    F.feature_no in 
                  (select feature_no from CGM_DDB.display_seq) 
        ");
	$sth->execute($query);
	my $count = $sth->fetchrow;
	$seqNum += $count;
	$sth->finish;
    
        #### match feature_name
	$sth = $dbh->prepare("
           SELECT count(unique F.feature_name) 
           FROM   CGM_DDB.feature F, CGM_DDB.locus L
           WHERE  upper(F.feature_name) like ?
           AND    F.locus_no = L.locus_no(+) 
           AND    (upper(L.locus_name) not like ? or
                  F.locus_no is null)
           AND    F.feature_no in 
                  (select feature_no from CGM_DDB.display_seq) 
        ");
	$sth->execute($query, $query);
	$count = $sth->fetchrow;
	$seqNum += $count;
	$sth->finish;
    
        #### match locus alias
	$sth = $dbh->prepare("
           SELECT count(unique L.locus_name)
           FROM   CGM_DDB.alias A, CGM_DDB.feature F, CGM_DDB.locus L, 
                  CGM_DDB.locus_alias AL
           WHERE  upper(A.alias_name) like ?
           AND    A.alias_no = AL.alias_no
           AND    AL.locus_no = L.locus_no
           AND    upper(L.locus_name) not like ?
           AND    L.locus_no = F.locus_no
           AND    upper(F.feature_name) not like ?
           AND    F.feature_no in 
                  (select feature_no from CGM_DDB.display_seq) 
        ");
	$sth->execute($query, $query, $query);
	$count = $sth->fetchrow;
	$seqNum += $count;
	$sth->finish;

	#### match feature alias
	$sth = $dbh->prepare("
	   SELECT count(unique F.feature_name)
	   FROM   CGM_DDB.feature F, CGM_DDB.locus L, CGM_DDB.alias A
           WHERE  upper(A.alias_name) like ?
           AND    A.feature_no = F.feature_no
           AND    upper(F.feature_name) not like ?
           AND    F.locus_no = L.locus_no(+)
           AND    (upper(L.locus_name) not like ? or
                  L.locus_name is null)  
           AND    F.feature_no in
                  (select feature_no from CGM_DDB.display_seq) 
        ");
	$sth->execute($query, $query, $query);
        $count = $sth->fetchrow;
	$seqNum += $count;
	$sth->finish;
    }
    return $seqNum;

}

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

    my $cloneNum;

    foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	my $sth = $dbh->prepare("
           SELECT count(unique clone_no) 
           FROM   CGM_DDB.clone
           WHERE  atcc_name like ?
           OR     washu_name like ? 
        ");

	$sth->execute($query, $query);
	my $count = $sth->fetchrow;
	$cloneNum += $count;
	$sth->finish;

    }
    return $cloneNum;

}

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

    my $arrayRef = $self->geneNmArrayRef;

    my $seqNum;

    foreach my $rowRef (@$arrayRef) {
	
	my ($featNm, $locusNm) = @$rowRef;
	
	if (!$featNm) { next; }

	my $sth = $dbh->prepare("
           SELECT count(E.external_id) 
           FROM   CGM_DDB.external_id E, CGM_DDB.ei_tu U, CGM_DDB.template_url T
           WHERE  E.tab_name = 'FEATURE'
           AND    E.source in ('Entrez GI', 'GenBank DNA Version', 
                               'InterPro', 'MIPS', 'PDB', 'PIR', 
                               'RefSeq Version', 'SwissProt', 'YPD')
           AND    E.primary_key in
                  (select feature_no
                   from   CGM_DDB.feature
                   where  feature_name = ?
                  )
           AND   E.external_id_no = U.external_id_no 
           AND   U.template_url_no = T.template_url_no 
        ");

	$sth->execute($featNm);
	
	my $count = $sth->fetchrow();
	$seqNum += $count;
	$sth->finish;
    }
    
    $seqNum += $self->systematicSeqNum;

    return $seqNum;

}

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

    my $configPath = ConfigPathdictyBase->new;

    my $datafile = $configPath->dataDir."sacch3d/emotif/emotif.data";

    open(IN, "$datafile") || 
	die "Can't open '$datafile' for reading:$!\n";

    my $count;

    my %foundMotif;

    while(<IN>) {
	
	my ($orf, $orflen, $emotif, $pvalue, $beg, $end, $url, $desc) 
	    = split(/\t/);

	my $found;

	foreach my $query (@{$self->{'_geneBasedQueryRef'}}) {

	    $query =~ s/\%/\.\*/g;
	    
	    if ($emotif =~ /$query/i || $desc =~ /$query/i) {
		
		if (!$foundMotif{$emotif})  {

		    $found++;

		    $foundMotif{$emotif}++;
		}
		last;

	    }

        }
	if ($found) { $count++; }

    }
    close(IN);

    return $count;

}

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

    my $whereClause1 = $self->{'_whereClause'};
    my $whereClause2 = $self->{'_whereClause'};

    $whereClause1 =~ s/SUBSTITUTE_TEXT/sequence_name/g;
    $whereClause2 =~ s/SUBSTITUTE_TEXT/note/g;

    my $sth = $dbh->prepare("
            SELECT count(note)
            FROM   CGM_DDB.pdb_sequence
            WHERE  ($whereClause1 or $whereClause2)
            AND    source = 'PDB'
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}},
		  @{$self->{'_whereValuesRef'}});
   
    my $pdbNum = $sth->fetchrow;
    $sth->finish;
    return $pdbNum;
    
}

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

    my %colleagueCount;

    #### match associated gene names
    
    my $whereClause = $self->{'_whereClause'};

    $whereClause =~ s/SUBSTITUTE_TEXT/L.locus_name/g;

    my $sth = $dbh->prepare("
           SELECT CL.colleague_no  
           FROM   CGM_DDB.coll_locus CL, CGM_DDB.locus L 
           WHERE  $whereClause
           AND    L.locus_no = CL.locus_no
    ");
    $sth->execute(@{$self->{'_whereValuesRef'}});
   
    while (my ($collNo) = $sth->fetchrow()) {
	$colleagueCount{$collNo}++;
    }
    $sth->finish;

    #### match colleague research interests

    my $whereClause = $self->{'_whereClause'};

    $whereClause =~ s/SUBSTITUTE_TEXT/remark/g;
    
    my $sth = $dbh->prepare("
            SELECT colleague_no
            FROM   CGM_DDB.colleague_remark
            WHERE  $whereClause
            AND    remark_type = 'Research Interest'
    ");
    $sth->execute(@{$self->{'_whereValuesRef'}});
   
    while (my ($collNo) = $sth->fetchrow()) {
	$colleagueCount{$collNo}++;
    }
    $sth->finish;

    #### match colleague keywords
    
    my $whereClause = $self->{'_whereClause'};

    $whereClause =~ s/SUBSTITUTE_TEXT/K.keyword/g;

    my $sth = $dbh->prepare("
            SELECT CK.colleague_no
            FROM   CGM_DDB.keyword K, CGM_DDB.coll_keyword CK
            WHERE  $whereClause
            AND    K.keyword_no = CK.keyword_no
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}});
   
    while (my ($collNo) = $sth->fetchrow()) {
	$colleagueCount{$collNo}++;
    }
    $sth->finish;

    my @colleagueNo = keys(%colleagueCount);

    return \@colleagueNo;
    
}

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

    my $whereClause = $self->{'_whereClause'};

    $whereClause =~ s/SUBSTITUTE_TEXT/remark/g;

    my $sth = $dbh->prepare("
            SELECT count(unique remark)
            FROM   CGM_DDB.colleague_remark
            WHERE  $whereClause
            AND    remark_type = 'Research Interest'
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}});
   
    my $interestNum = $sth->fetchrow;
    $sth->finish;
    return $interestNum;
    
}

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

    my $whereClause = $self->{'_whereClause'};

    $whereClause =~ s/SUBSTITUTE_TEXT/keyword/g;

    my $sth = $dbh->prepare("
            SELECT count(unique keyword)
            FROM   CGM_DDB.keyword
            WHERE  $whereClause
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}});
   
    my $keywordNum = $sth->fetchrow;
    $sth->finish;
    return $keywordNum;
    
}

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

    my $whereClause = $self->{'_whereClause'};

    $whereClause =~ s/SUBSTITUTE_TEXT/keyword/g;

    my $sth = $dbh->prepare("
            SELECT count(unique keyword)
            FROM   CGM_DDB.keyword
            WHERE  $whereClause
            AND    source = 'Curator-defined'
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}});
   
    my $topicNum = $sth->fetchrow;
    $sth->finish;
    return $topicNum;
    
}

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

    my $whereClause1 = $self->{'_whereClause'};
    my $whereClause2 = $whereClause1;
    my $whereClause3 = $whereClause1;
    my $whereClause4 = $whereClause1;
    
    $whereClause1 =~ s/SUBSTITUTE_TEXT/city/g;
    $whereClause2 =~ s/SUBSTITUTE_TEXT/state/g;
    $whereClause3 =~ s/SUBSTITUTE_TEXT/country/g;
    $whereClause4 =~ s/SUBSTITUTE_TEXT/institution/g;

    my $sth = $dbh->prepare("
            SELECT count(unique colleague_no)
            FROM   CGM_DDB.colleague
            WHERE  $whereClause1
            OR     $whereClause2
            OR     $whereClause3
            OR     $whereClause4
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}},
		  @{$self->{'_whereValuesRef'}},
		  @{$self->{'_whereValuesRef'}},
		  @{$self->{'_whereValuesRef'}});
   
    my $locationNum = $sth->fetchrow;
    $sth->finish;
    return $locationNum;
    
}

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

    my $whereClause = $self->{'_whereClause'};

    $whereClause =~ s/SUBSTITUTE_TEXT/gene_product/g;

    my $sth = $dbh->prepare("
            SELECT count(gene_product_no)
            FROM   CGM_DDB.gene_product
            WHERE  $whereClause
    ");
       
    $sth->execute(@{$self->{'_whereValuesRef'}});
   
    my $gpNum = $sth->fetchrow;
    $sth->finish;
    return $gpNum;

}

########################################################################
sub goTermNum {
########################################################################
    my ($self) = @_;
    
    my $whereClause = $self->{'_whereClause'};
	
    $whereClause =~ s/SUBSTITUTE_TEXT/go_term/g;

    my $sth = $dbh->prepare("
            SELECT count(goid)
            FROM   CGM_DDB.go
            WHERE  $whereClause
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}});
    
    my $goTermNum = $sth->fetchrow;

    $sth->finish;

    my $whereClause = $self->{'_whereClause'};
    $whereClause =~ s/SUBSTITUTE_TEXT/GS\.go_synonym/g;
	
    my $sth = $dbh->prepare("
            SELECT count(GGS.goid)
            FROM   CGM_DDB.go_gosyn GGS, CGM_DDB.go_synonym GS
            WHERE  $whereClause
            AND    GGS.go_synonym_no = GS.go_synonym_no
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}});
    
    $goTermNum += $sth->fetchrow;
    return $goTermNum;

}

########################################################################
sub phraseParagraphNum {
########################################################################
    my ($self) = @_;
    
    my $whereClause = $self->{'_whereClause'};
	
    $whereClause =~ s/SUBSTITUTE_TEXT/phrase_text/g;

    my $sth = $dbh->prepare("
            SELECT count(unique phrase_text), count(unique paragraph_no)
            FROM   CGM_DDB.phrase
            WHERE  $whereClause
            AND    paragraph_no in
                   (select paragraph_no from CGM_DDB.locus)
    ");

    $sth->execute(@{$self->{'_whereValuesRef'}});
    
    my ($phraseNum, $paragraphNum) = $sth->fetchrow;    
    $sth->finish;
    return ($phraseNum, $paragraphNum);

}

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

    my $whereClause = $self->{'_whereClause'};

    $whereClause =~ s/SUBSTITUTE_TEXT/external_id_name/g;

    my $sth = $dbh->prepare("
            SELECT count(unique external_id_name)
            FROM   CGM_DDB.external_id
            WHERE  source = 'MetaCyc'
            AND    $whereClause
    ");
       
    $sth->execute(@{$self->{'_whereValuesRef'}});
   
    my $pathwayNum = $sth->fetchrow;
    $sth->finish;
    return $pathwayNum;

}

########################################################################
sub abstractNum {
########################################################################
    my ($self) = @_;
    
    my $whereClause = $self->{'_whereClause'};
    $whereClause =~ s/SUBSTITUTE_TEXT/abstract/g;

    my $sth = $dbh->prepare("
            SELECT count(reference_no)
            FROM   CGM_DDB.abstract
            WHERE  $whereClause
    ");
	
    $sth->execute(@{$self->{'_whereValuesRef'}});
    
    my $abstractNum = $sth->fetchrow;
    $sth->finish;
    return $abstractNum;

}

########################################################################
sub descriptionNum {
########################################################################
    my ($self) = @_;
    ##### the description in locus table 
    ##### and the brief_id in feature table
    
    my $whereClause1 = $self->{'_whereClause'};
    my $whereClause2 = $self->{'_whereClause'};
	
    $whereClause1 =~ s/SUBSTITUTE_TEXT/description/g;
    $whereClause2 =~ s/SUBSTITUTE_TEXT/brief_id/g;

    my $sth = $dbh->prepare("
            SELECT unique description
            FROM   CGM_DDB.locus
            WHERE  $whereClause1
          UNION
            SELECT unique brief_id
            FROM   CGM_DDB.feature
            WHERE  $whereClause2
    ");

    $sth->execute(@{$self->{'_whereValuesRef'}},
		  @{$self->{'_whereValuesRef'}});
    
    
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    my $descNum = @$arrayRef;
    return $descNum;

}

########################################################################
sub phenotypeNum {
########################################################################
    my ($self) = @_;
    
    my $whereClause = $self->{'_whereClause'};
    $whereClause =~ s/SUBSTITUTE_TEXT/phenotype/g;
	
    my $sth = $dbh->prepare("
            SELECT count(unique phenotype)
            FROM   CGM_DDB.phenotype
            WHERE  $whereClause
    ");
	
    $sth->execute(@{$self->{'_whereValuesRef'}});
    
    my $phenotypeNum = $sth->fetchrow;

    $sth->finish;

    return $phenotypeNum;

}

########################################################################
sub noteNum {
########################################################################
    my ($self) = @_;
    
    my $whereClause = $self->{'_whereClause'};
    $whereClause =~ s/SUBSTITUTE_TEXT/note/g;

    my $sth = $dbh->prepare("
            SELECT count(curator_note_no)
            FROM   CGM_DDB.curator_note
            WHERE  $whereClause
            AND    is_public = 'Y'
            AND    curator_note_no in
                   (select curator_note_no from CGM_DDB.locus_cn
                    union
                    select curator_note_no from CGM_DDB.feat_cn)
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}});
    
    my $refNum = $sth->fetchrow;
    $sth->finish;
    return $refNum;

}

########################################################################
sub citationNum {
########################################################################
    my ($self) = @_;
    
    my $whereClause = $self->{'_whereClause'};
    $whereClause =~ s/SUBSTITUTE_TEXT/citation/g;

    my $sth = $dbh->prepare("
            SELECT count(reference_no)
            FROM   CGM_DDB.reference
            WHERE  $whereClause
    ");
        
    $sth->execute(@{$self->{'_whereValuesRef'}});
    
    my $refNum = $sth->fetchrow;
    $sth->finish;
    return $refNum;

}


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