#!@@_perl_root_@@/bin/perl.exe 
package TextSearch;

##########################################################
#                                                        #
# dictyBase Extension of TextSearch                      #
#                                                        #
##########################################################

use TextSearch_base;
use Cwd;

use lib "/usr/local/dicty/www_dictybase/cgi-bin/lib";

BEGIN { %TextSearch:: = %TextSearch_base:: }


#
# INNER JOIN TO NOT_DELETED FEATURE TABLE TO EXLCUDE
# GENE PRODUCTS ASSOCIATED WITH LOCI WHICH ARE IMPLICITLY DELETED
# (ONLY ASSOCIATED WITH DELETED FEATURES)
#
#
########################################################################
sub geneProductArrayRef {
########################################################################
    my ($self) = @_;

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

    $whereClause =~ s/SUBSTITUTE_TEXT/GP\.GENE_PRODUCT/g;
    

#    my $sth = $dbh->prepare("
#            SELECT DISTINCT GP.GENE_PRODUCT_NO, GP.GENE_PRODUCT
#              FROM CGM_DDB.GENE_PRODUCT GP
#        INNER JOIN LOCUS_GP LG
#                ON GP.GENE_PRODUCT_NO = LG.GENE_PRODUCT_NO
#        INNER JOIN $ENV{'CHADO_USER'}.V_GENE_FEATURES G
#                ON G.FEATURE_ID = LG.LOCUS_NO
#             WHERE $whereClause
#          ORDER BY UPPER(GP.GENE_PRODUCT)
#    ");

#
# DONT KNOW WHY THIS QUERY IS SO MUCH FASTER BUT IT IS!
#
    my $sth = $dbh->prepare("
             SELECT GP.GENE_PRODUCT_NO, GP.GENE_PRODUCT
               FROM CGM_DDB.GENE_PRODUCT GP
              WHERE $whereClause
                AND EXISTS ( 
                      SELECT 'A'
                        FROM CGM_DDB.LOCUS_GP LGP
                  INNER JOIN $ENV{'CHADO_USER'}.V_GENE_FEATURES G
                          ON LGP.LOCUS_NO = G.FEATURE_ID
                       WHERE LGP.GENE_PRODUCT_NO = GP.GENE_PRODUCT_NO
                    )
            ORDER BY UPPER(GP.GENE_PRODUCT)
    ");

    $sth->execute(@{$self->{'_whereValuesRef'}});

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

#
# INNER JOIN TO NOT_DELETED FEATURE TABLE TO EXLCUDE
# GENE PRODUCTS ASSOCIATED WITH LOCI WHICH ARE IMPLICITLY DELETED
# (ONLY ASSOCIATED WITH DELETED FEATURES)
#
########################################################################
sub geneProductNum {
########################################################################
    my ($self) = @_;

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

    $whereClause =~ s/SUBSTITUTE_TEXT/GP\.GENE_PRODUCT/g;

#    my $sth = $dbh->prepare("
#           SELECT COUNT(DISTINCT GP.GENE_PRODUCT_NO)
#              FROM CGM_DDB.GENE_PRODUCT GP
#        INNER JOIN LOCUS_GP LG
#                ON GP.GENE_PRODUCT_NO = LG.GENE_PRODUCT_NO
#        INNER JOIN $ENV{'CHADO_USER'}.V_GENE_FEATURES G
#                ON G.FEATURE_ID = LG.LOCUS_NO
#             WHERE $whereClause
#          ORDER BY UPPER(GP.GENE_PRODUCT)
#    ");


#
# DONT KNOW WHY THIS QUERY IS SO MUCH FASTER BUT IT IS!
#
    my $sth = $dbh->prepare("
             SELECT COUNT(GENE_PRODUCT_NO) 
               FROM CGM_DDB.GENE_PRODUCT GP
              WHERE $whereClause
                AND EXISTS ( 
                      SELECT 'A'
                        FROM CGM_DDB.LOCUS_GP LGP
                  INNER JOIN $ENV{'CHADO_USER'}.V_GENE_FEATURES G
                          ON LGP.LOCUS_NO = G.FEATURE_ID
                       WHERE LGP.GENE_PRODUCT_NO = GP.GENE_PRODUCT_NO
                    )
   ");

    $sth->execute(@{$self->{'_whereValuesRef'}});

    my $gpNum = $sth->fetchrow;
    $sth->finish;
    return $gpNum;

}
#
#
#  TOOK OUT FEATURE NAME SEARCH, WE DON'T REALLY USE FEATURE NAMES
#  (MAINLY TOOK OUT FOR PERFORMANCE - THIS PART OF QUERY TOOK THE LONGEST)
#
#  this section of the code should match what is going on in
#  LocusPage::wildSearch
#
#
########################################################################
sub geneNum {
########################################################################
    my ($self) = @_;

  #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  #
  #     Match locus name or alias name
  #
  #     Inner join to V_PRIMARY_FEATURE because results table
  #     displays feature information (coordinates, etc)
  #
  #++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    my $sth = $dbh->prepare("
	       SELECT COUNT(DISTINCT(FEATURE_ID))
		   FROM (
                    SELECT G.FEATURE_ID
                      FROM $ENV{'CHADO_USER'}.V_GENE_FEATURES G
                INNER JOIN CGM_DDB.LOCUS_ALIAS LA
                        ON LA.LOCUS_NO = G.FEATURE_ID
                     INNER JOIN CGM_DDB.ALIAS A
                        ON A.ALIAS_NO = LA.ALIAS_NO
                     WHERE UPPER(ALIAS_NAME) LIKE UPPER(?)
	            UNION ALL
		          SELECT G2.FEATURE_ID
		            FROM $ENV{'CHADO_USER'}.V_GENE_FEATURES G2
	                WHERE LOWER(G2.NAME) LIKE LOWER(?)
              )
    ");
    $sth->execute($self->{'_oriquery'}, $self->{'_oriquery'});
    my $geneNum = $sth->fetchrow;
    $sth->finish;

    return $geneNum;
}
########################################################################
1;
########################################################################