#!/usr/bin/perl
package sdevTextSearch;

##########################################################################
##### 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:
#####  my $tsObj = TextSearch->new(dbh=>$dbh,
#####	  	                    query=>$query);
#####  my $tsObj = TextSearch->new(database=>$database,
##### 		                    query=>$query);
#####
#####  passing $dbh OR $database
#####    
#####
#####  my $geneNmArrayRef = $tsObj->geneNmArrayRef; 
#####                        # gene/feature/alias
#####  my $geneProductArrayRef = $tsObj->geneProductArrayRef;
#####  my $goTermArrayRef = $tsObj->goTermArrayRef;
#####  my $colleagueArrayRef = $tsObj->colleagueArrayRef;    
#####  my $authorArrayRef = $tsObj->authorArrayRef;
#####  my $phraseArrayRef = $tsObj->phraseArrayRef;
#####  my $abstractArrayRef = $tsObj->abstractArrayRef;
#####  my $descriptionArrayRef = $tsObj->descriptionArrayRef;
#####
#####  my $phenotypeArrayRef = $tsObj->phenotypeArrayRef;
#####  
#####
#####  
#######################################################################
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->{'_query'} = $args{'query'}; 
    $self->_processQuery;

    return $self;
}

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

    $self->{'_oriquery'} = $self->{'_query'};
    $self->{'_oriquery'} =~ tr/a-z/A-Z/;

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

########################################################################
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 $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)
        ORDER BY upper(F.feature_name)
    ");
    $sth->execute($self->{'_oriquery'}, 
		  $self->{'_oriquery'}, $self->{'_oriquery'},
		  $self->{'_oriquery'}, $self->{'_oriquery'},
		  $self->{'_oriquery'},
		  $self->{'_oriquery'}, $self->{'_oriquery'},
		  $self->{'_oriquery'}
		  );
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

########################################################################
sub geneProductArrayRef {
########################################################################
    my ($self) = @_;
    my $sth;
    if ($self->{'_whereClause'}) {
	my $whereClause = $self->{'_whereClause'};
	$whereClause =~ s/SUBSTITUTE_TEXT/gene_product/g;
	$sth = $dbh->prepare("
            SELECT gene_product_no, gene_product
            FROM   CGM_DDB.gene_product
            WHERE  upper(gene_product) like ?
            $whereClause
            ORDER BY upper(gene_product)
        ");
        $sth->execute($self->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT gene_product_no, gene_product
            FROM   CGM_DDB.gene_product
            WHERE  upper(gene_product) like ?
            ORDER BY upper(gene_product)
        ");
        $sth->execute($self->{'_query'});
    }
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

########################################################################
sub goTermArrayRef {
########################################################################
    my ($self) = @_;
    my $sth;
    if ($self->{'_whereClause'}) {
	my $whereClause1 = $self->{'_whereClause'};
	my $whereClause2 = $self->{'_whereClause'};
	$whereClause1 =~ s/SUBSTITUTE_TEXT/go_term/g;
	$whereClause2 =~ s/SUBSTITUTE_TEXT/GS\.go_synonym/g;
	$sth = $dbh->prepare("
            SELECT goid, go_term as term, 
                   go_aspect as aspect
            FROM   CGM_DDB.go
            WHERE  upper(go_term) like ?
            $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  (   upper(GS.go_synonym) like ?
                       $whereClause2 )
            AND    GGS.go_synonym_no = GS.go_synonym_no
            AND    G.goid = GGS.goid  
            ORDER BY  term, aspect
        ");
        $sth->execute($self->{'_query'}, @{$self->{'_whereValuesRef'}},
		      $self->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT goid, go_term as term, go_aspect as aspect
            FROM   CGM_DDB.go
            WHERE  upper(go_term) like ?
          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  upper(GS.go_synonym) like ?
            AND    GGS.go_synonym_no = GS.go_synonym_no
            AND    G.goid = GGS.goid  
            ORDER BY  term, aspect
        ");
        $sth->execute($self->{'_query'}, $self->{'_query'});
    }
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

########################################################################
sub phraseArrayRef {
########################################################################
    my ($self) = @_;
    my $sth;
    if ($self->{'_whereClause'}) {
	my $whereClause = $self->{'_whereClause'};
	$whereClause =~ s/SUBSTITUTE_TEXT/P\.phrase_text/g;
	$sth = $dbh->prepare("
            SELECT unique P.phrase_text, L.locus_name
            FROM   CGM_DDB.phrase P, CGM_DDB.locus L
            WHERE  (upper(P.phrase_text) like ?
                    $whereClause)
            AND    P.paragraph_no = L.paragraph_no
            ORDER BY P.phrase_text, L.locus_name
        ");
	$sth->execute($self->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT unique P.phrase_text, L.locus_name
            FROM   CGM_DDB.phrase P, CGM_DDB.locus L
            WHERE  upper(P.phrase_text) like ?
            AND    P.paragraph_no = L.paragraph_no
            ORDER BY P.phrase_text, L.locus_name
        ");
	$sth->execute($self->{'_query'});
    }
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

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

}

########################################################################
sub descriptionArrayRef {
########################################################################
    my ($self) = @_;
    my $sth;
    if ($self->{'_whereClause'}) {
	my $whereClause1 = $self->{'_whereClause'};
	my $whereClause2 = $self->{'_whereClause'};
	$whereClause1 =~ s/SUBSTITUTE_TEXT/description/g;
	$whereClause2 =~ s/SUBSTITUTE_TEXT/brief_id/g;
	$sth = $dbh->prepare("
            SELECT description, locus_name
            FROM   CGM_DDB.locus
            WHERE  upper(description) like ?
            $whereClause1
          UNION  
            SELECT brief_id, feature_name
            FROM   CGM_DDB.feature
            WHERE  upper(brief_id) like ?
            $whereClause2
          ORDER BY 1, 2
        ");
	$sth->execute($self->{'_query'},  @{$self->{'_whereValuesRef'}},
		      $self->{'_query'},  @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT description, locus_name
            FROM   CGM_DDB.locus
            WHERE  upper(description) like ?
          UNION
            SELECT brief_id, feature_name
            FROM   CGM_DDB.feature
            WHERE  upper(brief_id) like ?
          ORDER BY 1, 2
        ");
	$sth->execute($self->{'_query'}, $self->{'_query'});
    }
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;

}

########################################################################
sub phenotypeArrayRef {
########################################################################
    my ($self) = @_;
    my $sth;
    if ($self->{'_whereClause'}) {
	my $whereClause1 = $self->{'_whereClause'};
	my $whereClause2 = $self->{'_whereClause'};
	$whereClause1 =~ s/SUBSTITUTE_TEXT/S\.sacchdb_phenotype/g;
	$whereClause2 =~ s/SUBSTITUTE_TEXT/phenotype/g;

	$sth = $dbh->prepare("
            SELECT unique S.sacchdb_phenotype, L.locus_name
            FROM   CGM_DDB.sacchdb_phenotype S, CGM_DDB.locus L
            WHERE  ( upper(S.sacchdb_phenotype) like ?
                     $whereClause1)
            AND    L.locus_no = S.locus_no
          UNION
            SELECT unique S.sacchdb_phenotype, F.feature_name
            FROM   CGM_DDB.sacchdb_phenotype S, CGM_DDB.feature F
            WHERE  ( upper(S.sacchdb_phenotype) like ?
                     $whereClause1)
            AND    F.feature_no = S.feature_no
          UNION
            SELECT phenotype, 'YYYYY'
            FROM   CGM_DDB.phenotype
            WHERE  upper(phenotype) like ?
                   $whereClause2
            ORDER BY 1, 2
        ");
	$sth->execute($self->{'_query'}, @{$self->{'_whereValuesRef'}},
		      $self->{'_query'}, @{$self->{'_whereValuesRef'}},
		      $self->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT unique S.sacchdb_phenotype, L.locus_name
            FROM   CGM_DDB.sacchdb_phenotype S, CGM_DDB.locus L
            WHERE  upper(S.sacchdb_phenotype) like ?
            AND    L.locus_no = S.locus_no
          UNION
            SELECT unique S.sacchdb_phenotype, F.feature_name
            FROM   CGM_DDB.sacchdb_phenotype S, CGM_DDB.feature F
            WHERE  upper(S.sacchdb_phenotype) like ?
            AND    F.feature_no = S.feature_no
          UNION
            SELECT phenotype, 'YYYYY'
            FROM   CGM_DDB.phenotype
            WHERE  upper(phenotype) like ?
            ORDER BY 1, 2
        ");
	$sth->execute($self->{'_query'}, $self->{'_query'}, 
		      $self->{'_query'});
    }
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

########################################################################
sub noteArrayRef {
########################################################################
    my ($self) = @_;
    my $sth;
    if ($self->{'_whereClause'}) {
	my $whereClause = $self->{'_whereClause'};
	$whereClause =~ s/SUBSTITUTE_TEXT/CN\.note/g;
	$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  (upper(CN.note) like ?
                    $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  (upper(CN.note) like ?
                    $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->{'_query'}, @{$self->{'_whereValuesRef'}},
		      $self->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$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  upper(CN.note) like ?
            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  upper(CN.note) like ?
            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->{'_query'}, $self->{'_query'});
    }
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

########################################################################
sub citationArrayRef {
########################################################################
    my ($self) = @_;
    my $sth;
    if ($self->{'_whereClause'}) {
	my $whereClause = $self->{'_whereClause'};
	$whereClause =~ s/SUBSTITUTE_TEXT/citation/g;
	$sth = $dbh->prepare("
            SELECT reference_no, citation
            FROM   CGM_DDB.reference
            WHERE  upper(citation) like ?
            $whereClause
            ORDER BY upper(citation)
        ");
        $sth->execute($self->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT reference_no, citation
            FROM   CGM_DDB.reference
            WHERE  upper(citation) like ?
            ORDER BY upper(citation)
        ");
        $sth->execute($self->{'_query'});
    }
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    return $arrayRef;
}

########################################################################
########################################################################
########################################################################
sub colleagueNum {
########################################################################
    my ($self) = @_;
    my $sth = $dbh->prepare("
        SELECT count(colleague_no)
        FROM   CGM_DDB.colleague
        WHERE  upper(last_name) like ?
    "); 
    $sth->execute($self->{'_oriquery'});
    my $collNum = $sth->fetchrow;
    $sth->finish;
    if ($collNum) { return $collNum; }

    if ($self->{'_oriquery'} =~ /\%/) { return ; }

    $sth = $dbh->prepare("
            SELECT count(colleague_no)
            FROM   CGM_DDB.colleague
            WHERE  upper(last_name) like ?
    "); 
    $sth->execute($self->{'_query'});
    $collNum = $sth->fetchrow;
    $sth->finish;
    return $collNum;
}

########################################################################
sub authorNum {
########################################################################
    my ($self) = @_;
    my $sth = $dbh->prepare("
        SELECT count(author_no)
        FROM   CGM_DDB.author
        WHERE  upper(author_name) like ?
    ");
    $sth->execute($self->{'_query'});
    my $authorNum = $sth->fetchrow;
    $sth->finish;
    return $authorNum;
}

########################################################################
sub geneNum {
########################################################################
    my ($self) = @_;
    #### 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($self->{'_oriquery'});
    my $geneNum = $sth->fetchrow;
    $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($self->{'_oriquery'}, $self->{'_oriquery'});
    my $geneNum2 = $sth->fetchrow;
    $geneNum += $geneNum2;
    $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($self->{'_oriquery'}, $self->{'_oriquery'},
		  $self->{'_oriquery'});
    $geneNum2 = $sth->fetchrow;
    $geneNum += $geneNum2;
    $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($self->{'_oriquery'}, $self->{'_oriquery'},
		  $self->{'_oriquery'});
    my $geneNum2 = $sth->fetchrow;
    $geneNum += $geneNum2;
    $sth->finish;
    return $geneNum;
}

########################################################################
sub geneProductNum {
########################################################################
    my ($self) = @_;
    my $sth;
    if ($self->{'_whereClause'}) {
	my $whereClause = $self->{'_whereClause'};
	$whereClause =~ s/SUBSTITUTE_TEXT/gene_product/g;
	$sth = $dbh->prepare("
            SELECT count(gene_product_no)
            FROM   CGM_DDB.gene_product
            WHERE  upper(gene_product) like ?
            $whereClause
        ");
        $sth->execute($self->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT count(gene_product_no)
            FROM   CGM_DDB.gene_product
            WHERE  upper(gene_product) like ?
        ");
        $sth->execute($self->{'_query'});
    }
    my $gpNum = $sth->fetchrow;
    $sth->finish;
    return $gpNum;
}

########################################################################
sub goTermNum {
########################################################################
    my ($self) = @_;
    my $sth;
    if ($self->{'_whereClause'}) {
	my $whereClause = $self->{'_whereClause'};
	$whereClause =~ s/SUBSTITUTE_TEXT/go_term/g;
	$sth = $dbh->prepare("
            SELECT count(goid)
            FROM   CGM_DDB.go
            WHERE  upper(go_term) like ?
            $whereClause
        ");
        $sth->execute($self->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT count(goid)
            FROM   CGM_DDB.go
            WHERE  upper(go_term) like ?
        ");
        $sth->execute($self->{'_query'});
    }
    my $goTermNum = $sth->fetchrow;
    $sth->finish;
    if ($self->{'_whereClause'}) {
	my $whereClause = $self->{'_whereClause'};
	$whereClause =~ s/SUBSTITUTE_TEXT/GS\.go_synonym/g;
	$sth = $dbh->prepare("
            SELECT count(GGS.goid)
            FROM   CGM_DDB.go_gosyn GGS, CGM_DDB.go_synonym GS
            WHERE  (   upper(GS.go_synonym) like ?
                       $whereClause)
            AND    GGS.go_synonym_no = GS.go_synonym_no
        ");
        $sth->execute($self->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT count(GGS.goid)
            FROM   CGM_DDB.go_gosyn GGS, CGM_DDB.go_synonym GS
            WHERE  upper(GS.go_synonym) like ?
            AND    GGS.go_synonym_no = GS.go_synonym_no
        ");
        $sth->execute($self->{'_query'});
    }
    $goTermNum += $sth->fetchrow;
    return $goTermNum;
}

########################################################################
sub phraseParagraphNum {
########################################################################
    my ($self) = @_;
    my $sth;
    if ($self->{'_whereClause'}) {
	my $whereClause = $self->{'_whereClause'};
	$whereClause =~ s/SUBSTITUTE_TEXT/phrase_text/g;
	$sth = $dbh->prepare("
            SELECT count(unique phrase_text), count(unique paragraph_no)
            FROM   CGM_DDB.phrase
            WHERE  (upper(phrase_text) like ?
                    $whereClause)
            AND    paragraph_no in
                   (select paragraph_no from CGM_DDB.locus)
        ");
	$sth->execute($self->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT count(unique phrase_text), count(unique paragraph_no)
            FROM   CGM_DDB.phrase
            WHERE  upper(phrase_text) like ?
            AND    paragraph_no in
                   (select paragraph_no from CGM_DDB.locus)
        ");
	$sth->execute($self->{'_query'});
    }
    my ($phraseNum, $paragraphNum) = $sth->fetchrow;    
    $sth->finish;
    return ($phraseNum, $paragraphNum);
}

########################################################################
sub abstractNum {
########################################################################
    my ($self) = @_;
    my $sth;
    if ($self->{'_whereClause'}) {
	my $whereClause = $self->{'_whereClause'};
	$whereClause =~ s/SUBSTITUTE_TEXT/abstract/g;
	$sth = $dbh->prepare("
            SELECT count(reference_no)
            FROM   CGM_DDB.abstract
            WHERE  upper(abstract) like ?
            $whereClause
        ");
	$sth->execute($self->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT count(reference_no)
            FROM   CGM_DDB.abstract
            WHERE  upper(abstract) like ?
        ");
	$sth->execute($self->{'_query'});
    }
    my $abstractNum = $sth->fetchrow;
    $sth->finish;
    return $abstractNum;
}

########################################################################
sub descriptionNum {
########################################################################
    my ($self) = @_;
    ##### since the description in locus table 
    ##### and the brief_id in feature table
    my $sth;
    if ($self->{'_whereClause'}) {
	my $whereClause1 = $self->{'_whereClause'};
	my $whereClause2 = $self->{'_whereClause'};
	$whereClause1 =~ s/SUBSTITUTE_TEXT/description/g;
	$whereClause2 =~ s/SUBSTITUTE_TEXT/brief_id/g;
	$sth = $dbh->prepare("
            SELECT unique description
            FROM   CGM_DDB.locus
            WHERE  upper(description) like ?
            $whereClause1
          UNION
            SELECT unique brief_id
            FROM   CGM_DDB.feature
            WHERE  upper(brief_id) like ?
            $whereClause2
        ");
	$sth->execute($self->{'_query'}, @{$self->{'_whereValuesRef'}},
		      $self->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT unique description
            FROM   CGM_DDB.locus
            WHERE  upper(description) like ?
           UNION
            SELECT unique brief_id
            FROM   CGM_DDB.feature
            WHERE  upper(brief_id) like ?
        ");
	$sth->execute($self->{'_query'}, $self->{'_query'});
    }
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    my $descNum = @$arrayRef;
    return $descNum;

}

########################################################################
sub phenotypeNum {
########################################################################
    my ($self) = @_;
    my $sth;
    if ($self->{'_whereClause'}) {
	my $whereClause = $self->{'_whereClause'};
	$whereClause =~ s/SUBSTITUTE_TEXT/sacchdb_phenotype/g;
	$sth = $dbh->prepare("
            SELECT count(unique sacchdb_phenotype)
            FROM   CGM_DDB.sacchdb_phenotype
            WHERE  upper(sacchdb_phenotype) like ?
            $whereClause
        ");
	$sth->execute($self->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT count(unique sacchdb_phenotype)
            FROM   CGM_DDB.sacchdb_phenotype
            WHERE  upper(sacchdb_phenotype) like ?
        ");
	$sth->execute($self->{'_query'});
    }
    my $phenotypeNum = $sth->fetchrow;
    $sth->finish;

    if ($self->{'_whereClause'}) {
	my $whereClause = $self->{'_whereClause'};
	$whereClause =~ s/SUBSTITUTE_TEXT/phenotype/g;
	$sth = $dbh->prepare("
            SELECT count(unique phenotype)
            FROM   CGM_DDB.phenotype
            WHERE  upper(phenotype) like ?
            $whereClause
        ");
	$sth->execute($self->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT count(unique phenotype)
            FROM   CGM_DDB.phenotype
            WHERE  upper(phenotype) like ?
        ");
	$sth->execute($self->{'_query'});
    }
    $phenotypeNum += $sth->fetchrow;
    $sth->finish;
    return $phenotypeNum;
}

########################################################################
sub noteNum {
########################################################################
    my ($self) = @_;
    my $sth;
    if ($self->{'_whereClause'}) {
	my $whereClause = $self->{'_whereClause'};
	$whereClause =~ s/SUBSTITUTE_TEXT/note/g;
	$sth = $dbh->prepare("
            SELECT count(curator_note_no)
            FROM   CGM_DDB.curator_note
            WHERE  (upper(note) like ?
                    $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->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT count(curator_note_no)
            FROM   CGM_DDB.curator_note
            WHERE  upper(note) like ?
            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->{'_query'});
    }
    my $refNum = $sth->fetchrow;
    $sth->finish;
    return $refNum;
}

########################################################################
sub citationNum {
########################################################################
    my ($self) = @_;
    my $sth;
    if ($self->{'_whereClause'}) {
	my $whereClause = $self->{'_whereClause'};
	$whereClause =~ s/SUBSTITUTE_TEXT/citation/g;
	$sth = $dbh->prepare("
            SELECT count(reference_no)
            FROM   CGM_DDB.reference
            WHERE  upper(citation) like ?
            $whereClause
        ");
        $sth->execute($self->{'_query'}, @{$self->{'_whereValuesRef'}});
    }
    else {
	$sth = $dbh->prepare("
            SELECT count(reference_no)
            FROM   CGM_DDB.reference
            WHERE  upper(citation) like ?
        ");
        $sth->execute($self->{'_query'});
    }
    my $refNum = $sth->fetchrow;
    $sth->finish;
    return $refNum;
}


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