cgi-bin/search/annotation_search_result.pl

   1 use strict;
   2 use CXGN::Page;
   3 use CXGN::DB::Connection;
   4 use CXGN::Tools::Text qw/to_tsquery_string/;
   5
   6 my $highlight_colour='#0000FF';
   7 my $desc_colour='#EEEEEE';
   8 my $nr_to_show=20;
   9 my $pagination_offset=5;
  10 my $unig_display_nr=10;
  11 my $search_type_print='';
  12 my $unig_link='/search/unigene.pl';
  13 my $show_all_unig_link='/search/all_unig_for_annot.pl';
  14 my $clone_link='/search/est.pl?request_type=8&amp;request_from=1&amp;request_id=';
  15
  16 our $page = CXGN::Page->new( "Annotation Search Results", "Dan, mod by Rob");
  17 my $dbh = CXGN::DB::Connection->new;
  18 my $pg_version = $dbh->dbh_param("pg_server_version");
  19 my $pg_ts_rank = $pg_version >= 80300 ? 'ts_rank' : 'rank';
  20
  21 #get all the relevant info for performing the search
  22 my ($search_type,
  23     $search_text,
  24     $results_page,
  25     $total_matches,
  26     $typed_page)    =   $page->get_encoded_arguments("search_type",
  27                                                      "search_text",
  28                                                      "results_page",
  29                                                      "total_matches",
  30                                                      "typed_page");
  31
  32 my $tsquery_text = to_tsquery_string($search_text);
  33
  34 unless ($search_text) {
  35   null_request();
  36 }
  37
  38 #if no current results page is specified, set it to 0 (first page)
  39 $results_page ||= 0;
  40
  41 #if the user typed in a user-friendly page number, convert to proper page number
  42 if($typed_page eq 'yes'){
  43     $results_page--;
  44 }
  45
  46 my $match_count_q;
  47 #set query type and specify proper result count sql
  48 if ( $search_type eq 'manual_search' ){
  49     $search_type_print = "manual annotation";
  50     $match_count_q = $dbh->prepare_cached(<<EOSQL);
  51 SELECT COUNT(*)
  52 FROM manual_annotations
  53 WHERE annotation_text_fulltext @@ to_tsquery(?)
  54 EOSQL
  55 }
  56 elsif( $search_type eq 'blast_search' ){
  57     $search_type_print = "BLAST based annotation";
  58     $match_count_q = $dbh->prepare_cached(<<EOSQL);
  59 SELECT COUNT(*)
  60 FROM blast_defline
  61 WHERE defline_fulltext @@ to_tsquery(?)
  62 EOSQL
  63 }
  64 else{
  65     null_request();
  66 }
  67
  68
  69 #do this only if we don't know the total number of matches.  This should happen only once, on the first page
  70 unless ($total_matches){
  71     $match_count_q->execute($tsquery_text);
  72     ($total_matches) = $match_count_q->fetchrow_array();
  73 }
  74
  75
  76 my $nr_show_to_print='';
  77
  78 my $start_at=$results_page * $nr_to_show;
  79 my $end_at=$start_at + $nr_to_show;
  80 my $nr_to_get = $end_at-$start_at;
  81
  82 # warn "offsets are ($start_at,$end_at,$nr_to_get,$total_matches)";
  83 # #bound checks
  84 # if(($start_at > $total_matches) or ($start_at < 0)){
  85 #     $start_at = 0;
  86 #     $nr_to_get= -1;
  87 # }
  88 # if($end_at > $total_matches){
  89 #     $end_at = -1;
  90 #     $nr_to_get= -1;
  91 # }
  92 # warn "offsets are ($start_at,$end_at,$nr_to_get,$total_matches)";
  93
  94 #execute the necessary SQL commands to get data
  95 #do only search types requested
  96 my @matches=();
  97 my %unigene_list=();
  98
  99 if ($search_type eq 'manual_search'){
 100
 101 #get the text search matches
 102
 103 # #create temp table to store just the matches for this page
 104 #     $manual_annot_tmp_create_q->execute();
 105 #     $manual_annot_tmp_fill_q->bind_param(3,$start_at,SQL_INTEGER);
 106 #     $manual_annot_tmp_fill_q->bind_param(4,$nr_to_get,SQL_INTEGER);
 107 #     $manual_annot_tmp_fill_q->execute($search_text, $search_text, $nr_to_get,$start_at) or $page->error_page("Couldn't create temp table with search $earch_text and limit $nr_to_get offset $start_at ($DBI::errstr)\n");
 108
 109 # #read the details of the annotation
 110
 111 # #data returned by $manual_annot_matches_q is:
 112 # #tmp.manual_annotations_id,  t.type_description, ma.annotation_text, a.author_name, ma.last_modified, tmp.score
 113
 114 #     $manual_annot_matches_q->execute()
 115 #       or $page->error_page("Couldn't get manual matches ($DBI::errstr)\n");
 116
 117
 118 #  $dbh->trace(2,'/tmp/dbitrace');
 119   my $manual_annot_matches_q = $dbh->prepare_cached(<<EOSQL);
 120 SELECT  ma.manual_annotations_id,
 121         t.type_description,
 122         ma.annotation_text,
 123         a.first_name || ' ' || a.last_name,
 124         ma.last_modified,
 125         $pg_ts_rank(annotation_text_fulltext,to_tsquery(?::text)) as score
 126 FROM manual_annotations as ma
 127 LEFT JOIN sgn_people.sp_person AS a
 128   ON (ma.author_id=a.sp_person_id)
 129 JOIN annotation_target_type AS t
 130   ON (ma.annotation_target_type_id=t.annotation_target_type_id)
 131 WHERE annotation_text_fulltext @@ to_tsquery(?::text)
 132       AND ma.annotation_target_type_id=1
 133 ORDER BY score DESC
 134 LIMIT ? OFFSET ?
 135 EOSQL
 136
 137   $manual_annot_matches_q->execute($tsquery_text,$tsquery_text,$nr_to_get,$start_at);
 138
 139   if ($manual_annot_matches_q->rows == 0) {
 140     no_matches($search_text);
 141   }
 142
 143   while (my ($annot_id, $type_desc, $annot_text, $author_name, $last_updated, $score) = $manual_annot_matches_q->fetchrow_array()) {
 144
 145     my $annot_target_desc = "$type_desc by $author_name on $last_updated";
 146
 147     push @matches, [$annot_id, $annot_target_desc, $score, $annot_text];
 148   }
 149
 150   #get the unigene links
 151
 152   my $manual_clone_unig_link_q = $dbh->prepare_cached(<<EOSQL);
 153 SELECT  c.clone_id,
 154         c.clone_name,
 155         u.unigene_id,
 156         g.comment,
 157         ub.build_nr,
 158         ub.build_date,
 159         ub.unigene_build_id
 160 FROM manual_annotations as ma
 161 JOIN clone as c
 162   ON (ma.annotation_target_id=c.clone_id)
 163 JOIN seqread as s
 164   USING (clone_id)
 165 JOIN est
 166   USING (read_id)
 167 LEFT JOIN unigene_member
 168   USING (est_id)
 169 LEFT JOIN unigene as u
 170   USING (unigene_id)
 171 LEFT JOIN unigene_build as ub
 172   USING (unigene_build_id)
 173 LEFT JOIN groups as g
 174   ON (ub.organism_group_id=g.group_id)
 175 WHERE ma.manual_annotations_id = ?
 176   AND (u.unigene_id IS NULL
 177        OR ub.status='C')
 178 EOSQL
 179
 180   foreach my $annot_id (map {$$_[0]} @matches) {
 181     $manual_clone_unig_link_q->execute($annot_id)
 182       or $page->error_page("Couldn't run manual_clone_unig_link_q ($DBI::errstr)\n");
 183
 184     while (my ($clone_id, $clone_name, $unig_id, $build_desc, $build_nr, $build_date, $unig_build_id) = $manual_clone_unig_link_q->fetchrow_array()) {
 185       my ($unig_desc, $sort_field)=('','');
 186       if ($unig_id) {
 187         $unig_desc = "<tr><td></td><td align=\"left\" nowrap=\"nowrap\"><a href=\"$unig_link?unigene_id=$unig_id\">Unigene $unig_id</a></td>"
 188                      . "<td align=\"left\" nowrap=\"nowrap\">$build_desc build $build_nr from $build_date</td><td></td></tr>";
 189         $sort_field=$unig_build_id;
 190       } else {
 191         $unig_desc="<tr><td></td><td align=\"left\" colspan=\"3\">The annotation is associated with clone <a href=\"$clone_link$clone_id\">$clone_name</a>, which has been censored from the current unigene builds.</td></tr>";
 192         $sort_field=$clone_id;
 193       }
 194
 195
 196       push @{$unigene_list{$annot_id}}, [$unig_desc, $sort_field];
 197
 198     }
 199   }
 200
 201 } elsif ($search_type eq 'blast_search'){
 202
 203     my $blast_matches_q = $dbh->prepare_cached(<<EOSQL);
 204 SELECT  defline_id,
 205         bt.db_name,
 206         defline,
 207         bt.blast_program,
 208         $pg_ts_rank(defline_fulltext,to_tsquery(?)) as score
 209 FROM blast_defline
 210 JOIN blast_targets as bt
 211   USING (blast_target_id)
 212 WHERE defline_fulltext @@ to_tsquery(?)
 213 ORDER BY score DESC
 214 LIMIT ? OFFSET ?
 215 EOSQL
 216
 217     $blast_matches_q->execute($tsquery_text,$tsquery_text,$nr_to_get,$start_at)
 218       or $page->error_page("Couldn't get blast matches ($DBI::errstr)\n");
 219
 220
 221     if ($blast_matches_q->rows == 0){
 222 #clean up the temp table voodoo
 223 #       eval{ $blast_tmp_drop_q->execute() };
 224 #         or $page->error_page("Couldn't drop temp table ($DBI::errstr)\n");
 225         no_matches($search_text);
 226     }
 227
 228     while(my ($defline_id, $blast_target_db, $defline, $blast_program,  $score) = $blast_matches_q->fetchrow_array()) {
 229         my $annot_target_desc = "Unigene <b>$blast_program</b> search against <b>$blast_target_db</b>";
 230         push @matches, [$defline_id, $annot_target_desc, $score, $defline];
 231     }
 232
 233
 234     my $blast_unig_link_q = $dbh->prepare_cached(<<EOSQL);
 235 SELECT  u.unigene_id,
 236         g.comment,
 237         bh.score,
 238         bh.evalue,
 239         bh.identity_percentage,
 240         bh.apply_start,
 241         bh.apply_end
 242 FROM blast_defline as bd
 243 JOIN blast_hits as bh
 244   USING(defline_id)
 245 JOIN blast_annotations as ba
 246   USING(blast_annotation_id)
 247 JOIN unigene as u
 248   ON (ba.apply_id=u.unigene_id)
 249 JOIN unigene_build as ub
 250   USING (unigene_build_id)
 251 JOIN groups as g
 252   ON (ub.organism_group_id=g.group_id)
 253 WHERE bd.defline_id = ?
 254 AND ba.apply_type=15 and ub.status='C'
 255 ORDER BY g.comment, u.unigene_id
 256 EOSQL
 257
 258     foreach my $defline_id (map {$$_[0]} @matches) {
 259       $blast_unig_link_q->execute($defline_id);
 260       while( my ( $unig_id,
 261                   $build_desc,
 262                   $blast_score,
 263                   $evalue,
 264                   $identity_pct,
 265                   $span_start,
 266                   $span_end     )  = $blast_unig_link_q->fetchrow_array()
 267            ) {
 268         my $span_ln=abs($span_end - $span_start);
 269         $identity_pct=sprintf "%7.2f", $identity_pct;
 270         my $unig_desc=<<EOH;
 271 <tr>
 272   <td></td>
 273   <td align="left" style="white-space: nowrap"><a href="$unig_link?unigene_id=$unig_id">Unigene $unig_id</a></td>
 274   <td align="left" style="white-space: nowrap">$build_desc;</td>
 275   <td align="left" style="white-space: nowrap"> matched with $identity_pct% identity over ${span_ln}bp (e-value $evalue)</td>
 276 </tr>
 277 EOH
 278         push @{$unigene_list{$defline_id}}, [$unig_desc, $blast_score];
 279       }
 280     }
 281 }
 282
 283 #set up all the page navigation stuff
 284
 285 my $nr_pages = int (($total_matches - 1)/$nr_to_show) + 1;
 286
 287 my $prev_page=$results_page - 1;
 288 my $next_page=$results_page + 1;
 289 my $pagination_print='';
 290
 291
 292 unless($nr_pages == 1){
 293
 294     my $pagination_start=$results_page-$pagination_offset;
 295     my $pagination_end=$results_page+$pagination_offset;
 296     my ($start_skip, $end_skip) = (1,1);
 297     my $last_page=$nr_pages-1;
 298     my $pagination_range= 2*$pagination_offset +1;
 299
 300     if($pagination_start < 0){
 301         $pagination_start = 0;
 302         $pagination_end = $pagination_start + $pagination_range;
 303         if($pagination_end > $nr_pages){
 304             $pagination_end = $nr_pages;
 305         }
 306         $start_skip=0;
 307     }
 308     if($pagination_end >= $nr_pages){
 309         $pagination_end = $nr_pages;
 310         $pagination_start = $pagination_end - $pagination_range;
 311         if($pagination_start < 0){
 312             $pagination_start = 0;
 313         }
 314         $end_skip=0;
 315     }
 316
 317     $pagination_print.="<tr><td colspan=\"2\" align=\"center\" bgcolor=\"#EEEEEE\">";
 318
 319     if ($prev_page >= 0){
 320         $pagination_print .= "<a href=\"annotation_search_result.pl?search_text=$search_text&amp;request_from=1&amp;search_type=$search_type&amp;results_page=$prev_page\">&lt; Previous</a> |";
 321         $start_skip and $pagination_print .= "| ... |";
 322     }
 323     else{
 324         $pagination_print .= "|";
 325     }
 326
 327     my $pg_nr;
 328     for($pg_nr=$pagination_start; $pg_nr<$pagination_end; $pg_nr++){
 329         my $pg_nr_to_display=$pg_nr+1;
 330         if ($pg_nr == $results_page){
 331             $pagination_print.="| <b>$pg_nr_to_display</b> |";
 332         }
 333         else{
 334             $pagination_print.="| <a href=\"annotation_search_result.pl?search_text=$search_text&amp;request_from=1&amp;search_type=$search_type&amp;results_page=$pg_nr\">$pg_nr_to_display</a> |";
 335         }
 336     }
 337
 338     if ($next_page < $nr_pages){
 339
 340         $end_skip and $pagination_print .= "| ... |";
 341         $pagination_print.= "| <a href=\"annotation_search_result.pl?search_text=$search_text&amp;request_from=1&amp;search_type=$search_type&amp;results_page=$next_page\">Next &gt;</a>";
 342     }
 343     else{
 344         $pagination_print .= "|";
 345     }
 346
 347     $pagination_print .= "</td></tr>";
 348
 349     if($nr_pages > $pagination_range){
 350       $pagination_print .= <<EOH;
 351 <tr><td colspan="2" align="center" bgcolor="#EEEEEE">
 352   <table align="center" cellspacing="0" cellpadding="0" border="0">
 353   <tr><td align="left" style="white-space: nowrap"><a href="annotation_search_result.pl?search_text=$search_text&amp;request_from=1&amp;search_type=$search_type&amp;results_page=0">&lt; First Page</a> || Page [1-$nr_pages]</td>
 354   <td align="center" style="white-space: nowrap">
 355   <form method="get" action="/search/annotation_search_result.pl">
 356   <input type="hidden" name="search_text" value="$search_text" />
 357   <input type="hidden" name="typed_page" value="yes" />
 358   <input type="hidden" name="request_from" value="1" />
 359   <input type="hidden" name="search_type" value="$search_type" />
 360   <input type="text" name="results_page" style="background: #EEEEFF" size="4" />
 361   <input name="get_page" type="submit" value="go" />
 362   </form>
 363   </td><td align="right" style="white-space: nowrap">
 364   &nbsp;||&nbsp;<a href="annotation_search_result.pl?search_text=$search_text&amp;request_from=1&amp;search_type=$search_type&amp;results_page=$last_page">Last Page &gt;</a>
 365   </td></tr>
 366   </table>
 367
 368 </td></tr>
 369 EOH
 370     }
 371 }
 372
 373
 374 #beautify for screen
 375 if ($total_matches <= $nr_to_show){
 376     $nr_show_to_print="Showing <b>all</b> matches";
 377 }
 378 elsif ($end_at < 0){
 379     $nr_show_to_print = "Showing last <b>" . ($total_matches - $start_at) . "</b> matches";
 380 }
 381 else {
 382     $nr_show_to_print= "Showing matches <b>" .($start_at + 1) ."</b> to <b>" . ($end_at) . "</b>";
 383 }
 384
 385
 386 #get the data ready for display
 387
 388 my @results=();
 389
 390 #match data is:
 391 #[match_id, annotated_data_description, text_search_match_score, annotation_text];
 392
 393 foreach my $match (@matches){
 394
 395     my $match_text=$$match[3];
 396     my $score = sprintf "%7.2f", $$match[2];
 397     my $annot_link_id=$$match[0];
 398     my @unigene_info;
 399
 400     if ($unigene_list{$annot_link_id}){
 401         @unigene_info=@{$unigene_list{$annot_link_id}};
 402     }
 403
 404 #strip <br /> tags.
 405 #this is for the current specific version of manual annotation
 406 #it is a kludge, there should be a text only searchable field
 407 # and a separate html enhanced text display field in the db
 408
 409     $match_text =~ s/\<br\>/  /g;
 410
 411 #insert <br /> in front of genbank id tags to break up long deflines
 412     $match_text =~ s/(gi\|)/<br \/>$1/g;
 413
 414 #highlight matches
 415
 416     my @word_parts=split /\W/, $search_text;
 417      foreach (@word_parts){
 418         $_ or next;
 419         $match_text =~ s/($_)/\<span class="hilite"\>$1\<\/span\>/ig;
 420     }
 421     my $web_format="
 422         <tr><td bgcolor=\"$desc_colour\">$$match[1]</td>
 423         <td align=\"right\" bgcolor=\"$desc_colour\">Text Match Relevance: $score</td></tr>
 424         <tr><td colspan=\"2\">$match_text</td></tr>";
 425
 426     if (@unigene_info){
 427         $web_format .= <<EOH;
 428 <tr><td colspan="2" align="left" nowrap="nowrap"><br />Unigenes containing this annotation:</td></tr>
 429 <tr><td colspan="2" align="left" nowrap="nowrap">
 430 <table align="left" cellspacing="2" cellpadding="0" border="0">
 431 EOH
 432
 433
 434 #unigene data is:
 435 #[unigene_line, sorting_value]
 436 # the unigene line has 4 columns
 437
 438         my $display_count=0;
 439         my $nr_unigenes=@unigene_info;
 440         foreach (sort{$$b[1] <=> $$a[1]} @unigene_info){
 441
 442 #show only a set nr of matches, add link to "Show More" if exceeded
 443             if($display_count >= $unig_display_nr){
 444                 $web_format .= <<EOH;
 445                 <tr><td></td><td colspan="3">...</td></tr>
 446                 <tr><td></td><td colspan="3"></td></tr>
 447                 <tr><td></td><td align="left" colspan="3" style="white-space: nowrap">
 448                 Showing only top $unig_display_nr of $nr_unigenes.&nbsp;
 449                 [<a href="$show_all_unig_link?match_id=$annot_link_id&amp;search_type=$search_type" target="All_Unigenes">Show All</a>]
 450                 </td></tr>
 451 EOH
 452                 last;
 453             }
 454
 455             $web_format .= "$$_[0]";
 456             $display_count++;
 457         }
 458         $web_format .="</table></td></tr>";
 459     }
 460     else{
 461         $web_format .= qq|<tr><td colspan="2" align="left" style="white-space: nowrap"><br />No unigenes contain this annotation</td></tr>|
 462         }
 463
 464     $web_format .= qq|<tr><td colspan="2">&nbsp;</td></tr>|;
 465     push @results, $web_format;
 466 }
 467
 468
 469 #start printing the page
 470 $page->header();
 471
 472 print<<EOF
 473     <table align="center" cellspacing="0" cellpadding="2" border="0" width="100%">
 474     <tr><td colspan="2" align="center" bgcolor="#EEEEEE">Your search for <b>$search_text</b> in <b>$search_type_print</b> returned <b>$total_matches</b> results</td></tr>
 475     <tr><td colspan="2" align="center" bgcolor="#EEEEEE">$nr_show_to_print</td></tr>
 476     $pagination_print
 477     <tr><td colspan="2" bgcolor="#FFFFFF"><br /></td></tr>
 478 @results
 479     <tr><td colspan="2" bgcolor="#FFFFFF"><br /></td></tr>
 480     $pagination_print
 481 </table>
 482 EOF
 483 ;
 484
 485 $page->footer();
 486
 487
 488
 489
 490
 491 sub no_matches {
 492   my ($search_text) = @_;
 493
 494   $page->header();
 495
 496   print <<EOF;
 497
 498   <p>Your search for <b>$search_text</b> did not find any relevant matches in our database.<br /><br />
 499 <b>Note:</b> Keywords of less than 4 letters or those that occur in more than half the data are ignored by the search.
 500 <br /><br />
 501 EOF
 502
 503   $page->footer();
 504
 505   exit 0;
 506 }
 507
 508
 509 sub null_request {
 510
 511   $page->header();
 512
 513   print <<EOF;
 514
 515   <p><b>Please enter a search word or phrase and select the annotation type you want to search.</b>
 516
 517 EOF
 518
 519   $page->footer();
 520
 521   exit 0;
 522 }
 523
 524