#!/usr/bin/perl
package DBsearch_base;

#######################################################################
##### Author :	Shuai Weng
##### Date   :  August 2000
##### Description : This package contains all necessary methods for dictyBase
#####               curators to search gene_product, phenotype, go_term
#####               in oracle database.
#####               This package also contains methods for displaying 
#####               the loci for the specified gene_product or displaying
#####               loci and features for the specified phenotype or 
#####               go_term.
#####  
#######################################################################
use strict;
use DBI;
use CGI qw/:all/;
use CGI::Carp qw(fatalsToBrowser);
use lib "/usr/local/dicty/www_dictybase/db/lib/common";
use Login qw (ConnectToDatabase);

use lib "/usr/local/dicty/www_dictybase/db/lib/dictyBase";
use dictyBaseCentralMod qw (:formatPage);

use lib "/usr/local/dicty/www_dictybase/db/lib/dictyBase/Objects";
use ConfigURLdictyBase;

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

our $dbh;
our $query;
our $dblink; 
our $dbname;
our $searchUrl;
our $descText;
our $title;

our $configUrl = ConfigURLdictyBase->new;

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

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

      	$self->{'_database'} = $args{'database'};
	$self->{'_help'}     = defined($args{'help'}) ? 
	                       $args{'help'} : "";
	$self->{'_queryStr'} 
	        = defined($args{'queryStr'}) ? $args{'queryStr'} : "";
	$self->{'_type'}     = defined($args{'type'}) ? $args{'type'} : "";
	$dbh = &ConnectToDatabase($self->database);
    	return $self;
}

sub help { $_[0]->{_help} }
sub database { $_[0]->{_database} }


######################################################################
sub DESTROY {   ############ destructor ##############################
######################################################################
    	if (defined $dbh) {
		$dbh->disconnect;
    	}
}

######################################################################
sub start{
######################################################################

    	my ($self) = @_;
	if ($self->database eq "dictyBase") {
	    $dblink = "dictyBase";
	    $dbname = "dictyBase";
	}
	else {
	    $dblink = "dictyBaseDEV";
	    $dbname = "SDEV";
	}
	if ($self->{'_type'} =~ /phenotype/i) {
	    $descText = "Phenotype";
	}
	elsif ($self->{'_type'} =~ /go_term/i) {
	    $descText = "Gene Ontology Term";
	}
	else {
	    $descText = "Gene_Product";
	}
	$title = "$dbname $descText Search";
        $searchUrl = $configUrl->dictyBaseCGIRoot."$dblink/curation/dbSearch";
	$query = new CGI;
	$self->{'_queryStr'} =~ s/^ +//;
	$self->{'_queryStr'} =~ s/ +$//;
	$self->{'_queryStr'} =~ s/[\n\t\r\f]//g;
	$self->{'_queryStr'} =~ s/\*/\%/g;
	if ($query->param('id')) {
	    $self->displayLocus;
	}
	elsif ($self->{'_queryStr'} && !$query->param('fill')) {
	    $self->displayInfo;
	}
        else {
	    $self->printEntryForm;
        }
}

#######################################################################
sub printEntryForm {
#######################################################################
        my ($self) = shift;
	my $defaultType = "$descText";
	$title = "$dbname Gene Product or Phenotype Search";
	&printStartPage($self->database, $title, $self->help);
	my %typeLabels;
	my @typeValues;
	if ($self->{'_type'} =~ /phenotype/i) {
	    %typeLabels = ('Phenotype'=>'Phenotype',
			   'Gene_Product'=>'Gene_Product');
	    @typeValues = qw/Phenotype Gene_Product/;
	}
	else {
	    %typeLabels = ('Gene_Product'=>'Gene_Product',
			  'Phenotype'=>'Phenotype');
	    @typeValues = qw/Gene_Product Phenotype/;
	}
	my $queryStr = $self->{'_queryStr'};
        ##########################
	print 
	startform({-method=>'POST',
		   -action=>$searchUrl}),
	b("This form is for dictyBase curators to search gene_products or phenotypes in oracle database.").p,
        b(font({-size=>"+1"}, 'Enter proposed ')),
        popup_menu(-name=>'type',
                   -"values"=>\@typeValues,
                   -labels=>\%typeLabels,
		   -default=>$defaultType
                  ), b(": "),      
        textfield(-name=>'queryStr',
		  -value=>"$queryStr",
		  -size=>30),
        p, " ",  
        submit('Submit','Submit'), " ",
	reset,
	endform;
	&printEndPage;
}

######################################################################
sub displayInfo {
######################################################################
        my ($self) = shift;
	&printStartPage($self->database, $title, $self->help);
	my $queryStr = $self->{'_queryStr'};
	my $searchText = "\U$queryStr";
	$searchText =~ s/\'/\'\'/g;
	my $type = $self->{'_type'};
	$type =~ s/ /_/g;
	my $sth;
	my $desc = "\L$descText";
	if ($type =~ /phenotype/i) {
	    $sth = $dbh->prepare("
                 SELECT phenotype, phenotype_no
                 FROM   CGM_DDB.phenotype
                 WHERE  upper(phenotype) like '%$searchText%'
                 ORDER BY phenotype
            "); 
        }
	else {
	    $sth = $dbh->prepare("
                SELECT gene_product, gene_product_no
                FROM   CGM_DDB.gene_product
                WHERE  upper(gene_product) like '%$searchText%'
                ORDER BY gene_product
            ");
	}
	my $found1 = 0;
	if ($sth->execute) {
	    while(my ($text, $id) = $sth->fetchrow()) {
		$found1++;
		if ($found1 == 1) {
		    print "Results of searching ${desc}s for <font color=red>\%${queryStr}\%</font>:<p>";
		    print "<b>$desc (${desc}_no)</b><br>";
		    print "---------------------------<br>";

		}
		$text =~ s/$queryStr/<font color=red>$queryStr<\/font>/ig;
		print "<b>$text (<a href=\"$searchUrl?id=$id&type=$type\" target=\"infowin\" onClick=\"open_win()\">$id</a>)</b><br>";
	    }
	}
	if ($found1 == 0) {
	    print "No match found for <font color=red>\%${queryStr}\%</font>.<br>";
	}
	print "<p>";
    
	if ($self->{'_type'} =~ /phenotype/i) {
	    $sth = $dbh->prepare("
                 SELECT phenotype, phenotype_no
                 FROM   CGM_DDB.phenotype
                 WHERE  soundex(phenotype) = soundex('$searchText')
                 ORDER BY phenotype
            ");
	}
	else {
	    $sth = $dbh->prepare("
                 SELECT gene_product, gene_product_no
                 FROM   CGM_DDB.gene_product
                 WHERE  soundex(gene_product) = soundex('$searchText')
                 ORDER BY gene_product
            ");
	}
	my $found2 = 0;
	if ($sth->execute) {
	    while(my($text, $id) = $sth->fetchrow()) {
		if ($text =~ /$queryStr/i) {
		    next;
		}
		$found2++;
		if ($found2 == 1) {
		    print "<hr>'Fuzzy' Search Results by using where clause <font color=red>soundex($desc) = soundex($queryStr)</font>:<p>";  
		    print "<b>$desc (${desc}_no)</b><br>";
		    print "----------------------------<br>";
		}
		print "<b>$text (<a href=\"$searchUrl?id=$id&type=$type\" target=\"infowin\" onClick=\"open_win()\">$id</a>)</b><br>";
	    }
	}
	if ($found2 == 0) {
	    print "<hr>No extra match found for 'fuzzy' search by using where clause <font color=red>soundex($desc) = soundex($queryStr)</font>.<p>";
	}

	if (!$found1 && !$found2) {
	    print "<hr>Please check your query string, make necessary change and try again.<p>";
	}

	$dbh->disconnect;
	############################ 
	&printEndPage;
}

###########################################################################
sub displayLocus {
###########################################################################
    my ($self) = shift;
	
    my $title = "$descText for Loci";
    if ($self->{'_type'} =~ /phenotype/i || 
	$self->{'_type'} =~ /go_term/i) {
	$title = "$title and Features";
    }
    &printStartPage($self->database, $title, $self->help);
    
    my $id = $query->param('id');
    my $sth;
    if ($self->{'_type'} =~ /phenotype/i) {
	$sth = $dbh->prepare("
             SELECT unique locus_name, phenotype
             FROM   CGM_DDB.phenotype P, CGM_DDB.locus L, CGM_DDB.locus_pheno LP
             WHERE  P.phenotype_no = ?
             AND    P.phenotype_no = LP.phenotype_no
             AND    LP.locus_no = L.locus_no
        ");
	my $found;
	if ($sth->execute($id)) {
	    while(my($locus, $phenotype) = $sth->fetchrow()) {
		$found++;
		if ($found == 1) {
		    print "<b><font color=red>$phenotype</font>:<p>";
		}
		print a({-href=>$configUrl->dictyBaseCGIRoot."gene_page.pl?gene_name=$locus",
			 -target=>'info'}, $locus);
	    }
	}
	######################
	$sth = $dbh->prepare("
             SELECT unique feature_name, phenotype
             FROM   CGM_DDB.phenotype P, CGM_DDB.feature F, CGM_DDB.feat_pheno FP
             WHERE  P.phenotype_no = ?
             AND    P.phenotype_no = FP.phenotype_no
             AND    FP.feature_no = F.feature_no
        ");
	if ($sth->execute($id)) {
	    while(my($feature, $phenotype) = $sth->fetchrow()) {
		$found++;
		print a({-href=>$configUrl->dictyBaseCGIRoot."gene_page.pl?gene_name=$feature",
			 -target=>'info'}, $feature);
	    }
	}
	if ($found) {
	    print "</b><p>";
	}
    }
    elsif ($self->{'_type'} =~ /go_term/i) {
	$sth = $dbh->prepare("
             SELECT unique locus_name, go_term
             FROM   CGM_DDB.go G, CGM_DDB.locus L, CGM_DDB.go_locus_goev GLG
             WHERE  G.goid = ?
             AND    G.goid = GLG.goid
             AND    GLG.locus_no = L.locus_no
        ");
	my $found;
	if ($sth->execute($id)) {
	    while(my($locus, $goTerm) = $sth->fetchrow()) {
		$found++;
		if ($found == 1) {
		    print "<b><font color=red>$goTerm</font>:<p>";
		}
		print a({-href=>$configUrl->dictyBaseCGIRoot."gene_page.pl?gene_name=$locus",
			 -target=>'info'}, $locus);
	      
	    }
	}
	######################
	$sth = $dbh->prepare("
             SELECT unique feature_name, go_term
             FROM   CGM_DDB.go G, CGM_DDB.feature F, CGM_DDB.go_feat_goev GFG
             WHERE  G.goid = ?
             AND    G.goid = GFG.goid
             AND    GFG.feature_no = F.feature_no
        ");
	if ($sth->execute($id)) {
	    while(my($feature, $goTerm) = $sth->fetchrow()) {
		$found++;
		print a({-href=>$configUrl->dictyBaseCGIRoot."gene_page.pl?gene_name=$feature",
			 -target=>'info'}, $feature);
	    }
	}
	if ($found) {
	    print "</b><p>";
	}
    }
    else {
	$sth = $dbh->prepare("
             SELECT gene_product, locus_name
             FROM   CGM_DDB.gene_product GP, CGM_DDB.locus L, CGM_DDB.locus_gp LGP
             WHERE  GP.gene_product_no = ?
             AND    GP.gene_product_no = LGP.gene_product_no
             AND    LGP.locus_no = L.locus_no
        ");
	my $locusNum;
	if ($sth->execute($id)) {
	    while(my($gp, $locus) = $sth->fetchrow()) {
		$locusNum++;
		if ($locusNum == 1) {
		    print "<b><font color=red>$gp</font>:<p>";
		}
		print a({-href=>$configUrl->dictyBaseCGIRoot."gene_page.pl?gene_name=$locus",
			 -target=>'info'}, $locus);
	    }
	    if ($locusNum) {
		print "</b><p>";
	    }
	}
    }
    print hr;
    print "<div align=center>\n";
    print "<form>\n";
    print "<input type=button value=\"    Close    \" onClick=\"self.close()\">\n";
    print "</form>";

#    &printEndPage;
}


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