package query_parser;

use Text::Wrap qw($columns &wrap);
use db_link;

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

sub get_sequence_count {
  my ($result) = @_;
  my $sequence_list, @list, $size;

  $sequence_list = $result->{sequences};
  @list = @$sequence_list;
  $size = @list;

  return $size;
}


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

sub print_result {

  my ($header, $result) = @_;
  my $fasta_sequence, $source_id, $master, $line, $cornell_stock_id, $gi_number, $estmarkerid, $library, $organism, $tissue;
  my $count = 0;

  $sequence_list = $result->{sequences};
  $master = $result->{master};

  print "<P><BR><H3 ALIGN=LEFT>$header</H3>";
  print "<TABLE BORDER=0 CELLSPACING=1 CELLPADDING=2>";
 
  foreach $sequence (@$sequence_list) {
    $fasta_sequence = $sequence->{fasta_sequence};
    $fasta_sequence = join '', split(/\s/, $fasta_sequence);
    $source_id = $sequence->{source_id};
    $count++;
    print "<TR ALIGN=LEFT><TD COLSPAN=2 ALIGN=LEFT><TABLE BORDER=0><TR><TH VALIGN=TOP ALIGN=LEFT>
<FORM ACTION=\"/cgi-bin/SGN/blast/blast_search.pl\" METHOD =\"POST\" NAME=\"BlastIt\">
<INPUT TYPE=hidden NAME=\"seq\" VALUE=\">$header\n$fasta_sequence\">
<INPUT TYPE=\"SUBMIT\" NAME=\"SUBMIT\" VALUE=\"Blast this sequence\">
</FORM></TH>";
    print "<TH><FORM ACTION=\"/cgi-bin/SGN/translation/seq_translation.pl\" METHOD =\"POST\" NAME=\"Translate\">
<INPUT TYPE=hidden NAME=\"seq\" VALUE=\"$source_id#$fasta_sequence\">
<INPUT TYPE=\"SUBMIT\" NAME=\"SUBMIT\" VALUE=\"Translate this sequence\">
</FORM>
</TH></TR></TABLE></TD></TR>";
    print "<TR ALIGN=LEFT><TH VALIGN=TOP ALIGN=LEFT>Fasta Sequence [$source_id]</TH><TD><CODE>";
 
    $columns = 50;
    foreach $line (split(/\n/,wrap('', '', $fasta_sequence))) {
      print "$line<BR>";
    }
    print "</CODE></TD></TR>";
  }
  if ($count == 0) {
    print "<TR><TH VALIGN=TOP ALIGN=LEFT>Fasta Sequence</TH><TD>No Sequence Information Found</TD></TR>";
  }

  if ($count >= 2) {
    print "<TR><TH VALIGN=TOP ALIGN=LEFT>Fasta Notes:</TH><TD><B>'TH' suffix of TIGR id indicates 5' sequence run and 'TV' suffix of TIGR id indicates 3' sequence run</B></TD></TR>";
  }
  

  if ($master eq 'master not found') {
    print "<TR><TH ALIGN=LEFT VALIGN=TOP>Master Information</TH><TD>No Master Information Found (Cornell Stock ID, GI#...etc)</TD></TR>";
  }
  else {
    $source_id = $result->{source_id};
    $cornell_stock_id = $result->{master}->{cornell_stock_id};
    $gi_number = $result->{master}->{gi_number};
    $estmarkerid = $result->{master}->{est_marker_id};
    $library = $result->{master}->{library_name};
 
    if ($estmarkerid eq '') { $estmarkerid = 'No EST Marker Information Found'; }
 
      print "<TR>
           <TD VALIGN=TOP ALIGN=LEFT>TIGR# <I>(or)</I> Other Sequence ID</TD>
           <TD><A HREF=\"tomato_est_search_result.pl?esttigr=$source_id\">$source_id</A></TD>
         </TR>
         <TR>
           <TD VALIGN=TOP ALIGN=LEFT>Translated cornell stock#</TD>
           <TD><A HREF=\"tomato_est_search_result.pl?translatedcornellstock=$cornell_stock_id\">$cornell_stock_id</A></TD>
         </TR>
           <TD VALIGN=TOP ALIGN=LEFT>GI#(NCBI)</TD>
           <TD><A HREF=\"http://www.ncbi.nlm.nih.gov:80/entrez/query.fcgi?cmd=Retrieve&db=Nucleotide&dopt=GenBank&list_uids=$gi_number\">$gi_number</A></TD>
         </TR>
         <TR>
           <TD VALIGN=TOP ALIGN=LEFT>EST Marker ID: </TD>
           <TD>$estmarkerid</TD>
         </TR>
         <TR>
	<TD VALIGN=TOP ALIGN=LEFT>cDNA library source: </TD>
	<TD><A HREF=\"\/about/libraries.html\">$library</A></TD>
	</TR>";
  }
  if ($result->{library} eq 'library not found') {
    print "<TR><TD ALIGN=LEFT VALIGN=TOP>Library Information</TH><TD>No Library Information Found (Organism, Tissue)</TD></TR>";
  }
  else {
    $organism = $result->{library}->{organism};
    $tissue = $result->{library}->{tissue};
    print "<TR>
         <TD VALIGN=TOP ALIGN=LEFT>Organism:</TD>
         <TD> $organism</TD>
       </TR>
       <TR>
         <TD VALIGN=TOP ALIGN=LEFT>Tissue: </TD>
         <TD>$tissue</TD>
       </TR>
      <TR>";
  }
  print "</TABLE><BR>";

#new information added 7/24/01
##############################

  my $esttigr=$source_id;
#try to open the database link
  my $dbh = db_link::connect_db('tomato');

#look for contig membership
  my $stm = "select o.organism_name, o.organism_id, c.build, c.cluster, c.contig from organisms as o, new_contigs as c, new_contig_components as cp, sequences as s where s.source_id='$esttigr' and cp.seq_id=s.seq_id and c.contig_id=cp.contig_id and c.organism=o.organism_id";
  my $sth = $dbh->prepare($stm) 
      || die "Can't prepare statement: $DBI::errstr";
  my $rv = $sth->execute
      || die "Can't execute statement: $DBI::errstr";
  my ($org_name, $org_id, $build, $cluster, $contig); 
  my $rc = $sth->bind_columns(\$org_name, \$org_id, \$build, \$cluster, \$contig);
  my %build_hash=();
  while ($sth->fetch) {
      $build_hash{$build}=[$org_name, $org_id, $build, $cluster, $contig];
  }
  
  my $latest_build=0;
  my $key;
  foreach $key (keys %build_hash){
      $latest_build < $key
	  and $latest_build=$key;
  }
  
  print "<BR><P><B>Contig Membership:</B><BR><BR>\n";
  if ($latest_build){
      print "Member of contig <A HREF=\"/cgi-bin/SGN/consensus/consensus_search_result.pl?highlight=$esttigr&ORGANISM=${$build_hash{$latest_build}}[1]-${$build_hash{$latest_build}}[2]&Cluster_No=${$build_hash{$latest_build}}[3]&Contig_No=${$build_hash{$latest_build}}[4]\">${$build_hash{$latest_build}}[0] ${$build_hash{$latest_build}}[2].${$build_hash{$latest_build}}[3].${$build_hash{$latest_build}}[4]</A><BR> \n";
  }
  else {
    print "Not incorporated into any consensus sequence.<BR>\n";
}
  
  
  
#look to see if it is a COS marker, pull cos info if it is
  $stm = "select c.cos_id, c.at_match, c.bac_id, c.at_position, c.best_gb_prot_hit, c.gbprot_evalue, c.gbprot_identities, c.mips_cat, c.description, c.comment from cos_marker_new as c, sequences as s where s.source_id='$esttigr' and s.seq_id=c.seq_id";
  $sth = $dbh->prepare($stm) 
      || die "Can't prepare statement: $DBI::errstr";
  $rv = $sth->execute
      || die "Can't execute statement: $DBI::errstr";
  my ($cos_id, $at_match, $bac_id, $at_pos, $best_gb_prot, $hit_eval, $ident, $mips_cat, $descr, $comment);
  $rc = $sth->bind_columns(\$cos_id, \$at_match, \$bac_id, \$at_pos, \$best_gb_prot, \$hit_eval, \$ident, \$mips_cat, \$descr, \$comment);
  while ($sth->fetch){
      
#format mips category links 'cause TIGR's site is brain-dead and trips over missing zeroes
      $mips_cat=~s/^([0-9]\.)/0$1/;
      $mips_cat=~s/\.([0-9])\./\.0$1\./g;
      
      print "<BR><P><B>COS marker information:</B><BR><BR>\n<TABLE BORDER=0>\n";
      $cos_id
	  and print "<TR><TD ALIGN=LEFT>COS id</TD><TD ALIGN=LEFT>&nbsp &nbsp $cos_id</TD></TR>";
      $at_match
	and print "<TR><TD ALIGN=LEFT>Arabidopsis best BAC match</TD><TD ALIGN=LEFT>&nbsp &nbsp <A HREF=\"http://www.ncbi.nlm.nih.gov:80/entrez/query.fcgi?cmd=Retrieve&db=Nucleotide&dopt=GenBank&list_uids=$bac_id\">$at_match</A></TD></TR>";
      $at_pos
	and print "<TR><TD ALIGN=LEFT>Arabidopsis position</TD><TD ALIGN=LEFT>&nbsp &nbsp $at_pos</TD></TR>";
      $best_gb_prot
	  and print "<TR><TD ALIGN=LEFT>Best genbank protein hit</TD><TD ALIGN=LEFT>&nbsp &nbsp $best_gb_prot</TD></TR>";
      $hit_eval
	  and print "<TR><TD ALIGN=LEFT>Protein hit e-value</TD><TD ALIGN=LEFT>&nbsp &nbsp $hit_eval</TD></TR>";
      $ident
	and print "<TR><TD ALIGN=LEFT>Identities</TD><TD ALIGN=LEFT>&nbsp &nbsp $ident</TD></TR>";
      $mips_cat
	  and print "<TR><TD ALIGN=LEFT><A HREF=\"http://www.tigr.org/tigr-scripts/edb2_scripts/euk_role_table.dbi?info=ath1__access_access\">MIPS role category</A></TD><TD ALIGN=LEFT>&nbsp &nbsp <A HREF=\"http://www.tigr.org/tigr-scripts/edb2_scripts/role_gene_list.dbi?role_class=$mips_cat&user=access&password=access&db=ath1\">$mips_cat</A></TD></TR>";
      $descr
	  and print "<TR VALIGN=TOP><TD ALIGN=LEFT>Putative function</TD><TD ALIGN=LEFT>&nbsp &nbsp $descr</TD></TR>";
      $comment
	  and print "<TR VALIGN=TOP><TD ALIGN=LEFT>Comment</TD><TD ALIGN=LEFT>&nbsp &nbsp $comment</TD></TR>";
      print "</TABLE>";
  }
  print "\n<BR><HR WIDTH=100%>\n";
 db_link::disconnect_db($dbh);
  
}


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

sub get_tigr_info {
  my ($tigr, $db) = @_;

  my @sequence_list = ( ), $sequence_list_size = 0, $sequence, $toReturn = ( ), $library;

  # get sequence information;

  @sequence_list = &get_sequence_info($tigr, 'source_id', $db);
  $sequence_list_size = @sequence_list;
  $toReturn->{sequences} = \@sequence_list;
  $toReturn->{source_id} = $tigr;
  
  if ($sequence_list_size > 0) {
    $sequence = @sequence_list[0];

    # get master information
    if (($toReturn->{master} =&get_master_info($sequence->{seq_id}, 'seq_id', $db)) eq 'master not found') {
      $library = get_library_from_translation($sequence->{source_id}, $db);
      if ($library eq 'library_not found') {
        $toReturn->{library} = 'library_not_found';
      }
      else {
        $toReturn->{library} = &get_library_info($library, $db);
      }
    }
    else {
      $toReturn->{library} = &get_library_info($toReturn->{master}->{library_name}, $db);
    }
  }
  else {
    $toReturn->{master} = 'master not found';
    $toReturn->{library} = 'library not found';
  }

  return $toReturn;
}


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

sub get_info_by_master {
  my ($input, $column, $db) = @_;
  my @sequence_list = ( ), $sequence_list_size = 0;

  # get the master information
  if (($toReturn->{master} =&get_master_info($input, $column, $db)) eq 'master not found') {
    $toReturn->{sequence} = \@sequence_list;
    $toReturn->{library} = 'library not found';
  }
  else {
    $toReturn->{library} = &get_library_info($toReturn->{master}->{library_name}, $db);

    @translation_list = get_translation($toReturn->{master}->{cornell_stock_id}, $db);
    
    foreach $translation (@translation_list) {  
      @to_add = &get_sequence_info($translation, 'source_id', $db);
      push @sequence_list, @to_add;
    }
  
    $sequence_list_size = @sequence_list;

    $toReturn->{sequences} = \@sequence_list;

    # there must be a sequence in the sequence list because there is a master entry...otherwise !@#!@##@$
    if ($sequence_list_size > 0) {
      $toReturn->{source_id} = @sequence_list[0]->{source_id};
    }
   else {
      $toReturn->{source_id} = '';    
    }
  }
  return $toReturn;
}


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

sub get_library_from_translation {
  my ($tigr_id, $dbh) = @_;
my $stm = "SELECT library from translation where tigr_id = '$tigr_id'";
my $sth = $dbh->prepare($stm) 
	|| die "Can't prepare statement: $DBI::errstr";
my $rv = $sth->execute
	|| die "Can't execute statement: $DBI::errstr";
my $rc = $sth->bind_columns(\$library);
while ($sth->fetch) {
    return $library;
  }
  return 'library_not_found';
}


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

sub get_translation {
  my ($cornell_stock_id, $dbh) = @_;
  my $tigr_id, @toReturn = ( );
my $stm = "SELECT t.tigr_id from translation as t, sequences as s where t.tigr_id=s.source_id and t.cornell_stock_id = '$cornell_stock_id' ";
my $sth = $dbh->prepare($stm) 
	|| die "Can't prepare statement: $DBI::errstr";
my $rv = $sth->execute
	|| die "Can't execute statement: $DBI::errstr";
my $rc = $sth->bind_columns(\$tigr_id);
while ($sth->fetch) {
    push @toReturn, $tigr_id;
  }
  return @toReturn;
}


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

sub get_library_info {
  my ($library_name, $dbh) = @_;
  my $organism, $tissue, $toReturn; 
  my $stm = "SELECT organism, tissue from libraries where library= '$library_name'";
my $sth = $dbh->prepare($stm) 
	|| die "Can't prepare statement: $DBI::errstr";
my $rv = $sth->execute
	|| die "Can't execute statement: $DBI::errstr";
my $rc = $sth->bind_columns(\$organism, \$tissue);
while ($sth->fetch) {

    $toReturn->{organism} = $organism;
    $toReturn->{tissue} = $tissue;

    return $toReturn;
  }
  return 'library not found';
}


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

sub get_sequence_info {
  my ($input, $column, $dbh) = @_;
  my @toReturn = ( ), $seq_id, $source_id, $fasta_sequence, $to_insert, $where; 

  if ($column eq 'seq_id') {
    $where = "seq_id = $input";
  }
  elsif ($column eq 'source_id') {
    $where = "source_id = '$input'";
  }
  else {
    return -1;
  }
  my $stm = "SELECT seq_id, source_id, fasta_sequence from sequences where $where";
  my $sth = $dbh->prepare($stm) 
	|| die "Can't prepare statement: $DBI::errstr";
  my $rv = $sth->execute
	|| die "Can't execute statement: $DBI::errstr";
  my $rc = $sth->bind_columns(\$seq_id, \$source_id, \$fasta_sequence);

   while ($sth->fetch) {
     $to_insert = ( );

    $to_insert->{seq_id} = $seq_id;
    $to_insert->{fasta_sequence} = $fasta_sequence;
    $to_insert->{source_id} = $source_id;

    push @toReturn, $to_insert;
};

  return @toReturn;
}


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

sub get_master_info {
  my ($input, $column, $dbh) = @_;
  my $library_name, $cornell_stock_id, $gi_number, $est_marker_id, $seq_id, $where;

  if ($column eq 'seq_id') {
    $where = "seq_id = $input";
  }
  elsif ($column eq 'ncbi') {
    $where = "gi_number = '$input'";
  }
  elsif ($column eq 'cornell_stock_id') {
    $where = "cornell_stock_id = '$input'";
  }
  elsif ($column eq 'est_marker_id') {
    $where = "est_marker_id = '$input'";
  }
  else {
    return 'master not found';
  }

my $stm = "SELECT seq_id, library_name, cornell_stock_id, gi_number, est_marker_id from master where $where";
my $sth = $dbh->prepare($stm) 
	|| die "Can't prepare statement: $DBI::errstr";
my $rv = $sth->execute
	|| die "Can't execute statement: $DBI::errstr";

  
my $rc = $sth->bind_columns(\$seq_id, \$library_name, \$cornell_stock_id, \$gi_number, \$est_marker_id);
while ($sth->fetch) {
    $master->{seq_id} = $seq_id;
    $master->{library_name} = $library_name;
    $master->{cornell_stock_id} = $cornell_stock_id;
    $master->{gi_number} = $gi_number;
    $master->{est_marker_id} = $est_marker_id;

    return $master;
  }

}

return 1;