cgi-bin/phenome/ontology_browser.pl

   1 use strict;
   2 use warnings;
   3
   4 use CXGN::Scrap::AjaxPage;
   5 use CXGN::DB::Connection;
   6
   7 my $doc = CXGN::Scrap::AjaxPage->new();
   8 $doc->send_http_header();
   9 my ($term_name, $db_name) = $doc->get_encoded_arguments("term_name", "db_name");
  10
  11 my $dbh = CXGN::DB::Connection->new();
  12
  13 my $synonym_query= $dbh->prepare("SELECT  distinct(cvterm.dbxref_id), cv.name, cvterm.name, dbxref.accession, synonym
  14                                     FROM public.cvterm
  15                                    JOIN public.cv USING (cv_id)
  16                                    LEFT JOIN public.cvtermsynonym USING (cvterm_id)
  17                                    JOIN public.dbxref USING (dbxref_id)
  18                                    JOIN public.db USING (db_id)
  19                                     WHERE cvterm.is_obsolete= 0 AND
  20                                     db.name=? AND
  21                                     cvtermsynonym.synonym ilike '%$term_name%'
  22                                   ");
  23
  24
  25 #
  26 my $ontology_query = $dbh->prepare("SELECT  distinct(cvterm.dbxref_id), cv.name, cvterm.name, dbxref.accession,
  27                                     count(synonym)
  28                                     FROM public.cvterm
  29                                    JOIN public.cv USING (cv_id)
  30                                    LEFT JOIN public.cvtermsynonym USING (cvterm_id)
  31                                    JOIN public.dbxref USING (dbxref_id)
  32                                    JOIN public.db USING (db_id)
  33                                     WHERE cvterm.is_obsolete= 0 AND
  34
  35                                      db.name=? AND
  36                                     (cvterm.name ilike '%$term_name%'
  37                                     OR cvterm.definition ilike '%$term_name%'
  38                                     )
  39                                     GROUP BY cvterm.dbxref_id, cvterm.name, dbxref.accession, cv.name
  40                                     ORDER BY cv.name, cvterm.name
  41                                    ");
  42 $ontology_query->execute($db_name);
  43 my %terms;
  44 my ($dbxref_id, $cv_name,$cvterm_name, $accession, $synonym) = $ontology_query->fetchrow_array();
  45
  46 while($cvterm_name){
  47     $terms{$cv_name}{"$dbxref_id*$cv_name--$db_name:$accession--"}  =  $cvterm_name;
  48     ($dbxref_id, $cv_name,$cvterm_name, $accession, $synonym) = $ontology_query->fetchrow_array();
  49 }
  50
  51 $synonym_query->execute($db_name);
  52 my @synonym_terms;
  53 while (my ($dbxref_id, $cv_name, $cvterm_name, $accession, $synonym) = $synonym_query->fetchrow_array()) {
  54     if ($terms{$cv_name}{"$dbxref_id*$cv_name--$db_name:$accession--"} ) {
  55         $terms{$cv_name}{"$dbxref_id*$cv_name--$db_name:$accession--"} .= " ($synonym)";
  56     }else {
  57         $terms{$cv_name}{"$dbxref_id*$cv_name--$db_name:$accession--"} = $cvterm_name . " ($synonym)";
  58     }
  59 }
  60
  61 #sort the hash of hashes by keys(cv_name)  and then by values (term names)
  62 my $print_string="";
  63
  64 foreach my $cv_name(sort (keys %terms ) ) {
  65     foreach my $key(sort { $terms{$cv_name}{$a} cmp $terms{$cv_name}{$b} } keys %{$terms{$cv_name}} ) {
  66         $print_string .= $key . $terms{$cv_name}{$key};
  67         $print_string .= "|";
  68     }
  69 }
  70 #print STDERR $print_string;
  71 print $print_string;
  72