cgi-bin/phenome/evidence_browser.pl

   1 use strict;
   2 use warnings;
   3
   4 use CXGN::Scrap::AjaxPage;
   5
   6 my $doc = CXGN::Scrap::AjaxPage->new();
   7 $doc->send_http_header();
   8
   9 my ($type, $evidence_code_id, $locus_id, $individual_id) = $doc->get_encoded_arguments("type", "evidence_code_id", "locus_id", "individual_id");
  10
  11 my $dbh = CXGN::DB::Connection->new();
  12
  13 if ($type eq 'relationship') {
  14
  15     my $relationship_query = $dbh->prepare("SELECT distinct(cvterm.dbxref_id), cvterm.name
  16                                        FROM public.cvterm
  17                                        JOIN public.cv USING (cv_id)
  18                                       JOIN public.cvterm_relationship ON (cvterm.cvterm_id= cvterm_relationship.subject_id)
  19                                        WHERE cv.name ='relationship' AND
  20                                        cvterm.is_obsolete = 0
  21                                        ORDER BY cvterm.name;
  22                                       ");
  23     $relationship_query->execute();
  24
  25     my ($dbxref_id, $cvterm_name) = $relationship_query->fetchrow_array();
  26     my $available_relationships;
  27
  28     while($cvterm_name){
  29         $available_relationships .= "$dbxref_id*$cvterm_name|";
  30         ($dbxref_id, $cvterm_name) = $relationship_query->fetchrow_array();
  31     }
  32
  33
  34     print "$available_relationships";
  35
  36
  37
  38 }elsif ($type eq 'evidence_code') {
  39
  40  my $evidence_code_query = $dbh->prepare("SELECT distinct(cvterm.dbxref_id), cvterm.name
  41                                        FROM public.cvterm_relationship
  42
  43                                       JOIN public.cvterm ON (cvterm.cvterm_id= cvterm_relationship.subject_id)
  44                                        WHERE
  45                                        object_id= (select cvterm_id from cvterm where name = 'evidence_code') AND
  46                                        cvterm.is_obsolete = 0
  47                                        ORDER BY cvterm.name;
  48                                       ");
  49     $evidence_code_query->execute();
  50
  51     my ($dbxref_id, $cvterm_name) = $evidence_code_query->fetchrow_array();
  52     my $available_evidence_codes;
  53
  54     while($cvterm_name){
  55         $available_evidence_codes.= "$dbxref_id*$cvterm_name|";
  56         ($dbxref_id, $cvterm_name) = $evidence_code_query->fetchrow_array();
  57     }
  58
  59
  60     print "$available_evidence_codes";
  61
  62
  63 }elsif ($type eq 'evidence_description') {
  64
  65  my $evidence_description_query = $dbh->prepare("SELECT dbxref_id, cvterm.name FROM cvterm
  66                                                 JOIN cvterm_relationship ON cvterm_id=subject_id
  67                                                 WHERE object_id= (select cvterm_id FROM public.cvterm WHERE dbxref_id= ?)
  68                                                 AND cvterm.is_obsolete = 0"
  69                                                 );
  70     $evidence_description_query->execute($evidence_code_id);
  71
  72     my ($dbxref_id, $cvterm_name) = $evidence_description_query->fetchrow_array();
  73     my $available_evidence_descriptions;
  74
  75     while($cvterm_name){
  76         $available_evidence_descriptions.= "$dbxref_id*$cvterm_name|";
  77         ($dbxref_id, $cvterm_name) = $evidence_description_query->fetchrow_array();
  78     }
  79
  80
  81     print "$available_evidence_descriptions";
  82
  83 }elsif ($type eq 'evidence_with') {
  84     my $evidence_with_query;
  85     if ($locus_id) {
  86         $evidence_with_query = $dbh->prepare("SELECT dbxref.dbxref_id, accession,name, description
  87                                           FROM public.dbxref
  88                                           JOIN feature USING (dbxref_id)
  89                                           JOIN phenome.locus_dbxref USING (dbxref_id)
  90                                           WHERE locus_id= ?
  91                                           AND phenome.locus_dbxref.obsolete = 'f'"
  92                                              );
  93         $evidence_with_query->execute($locus_id);
  94     }elsif ($individual_id) {
  95         $evidence_with_query = $dbh->prepare("SELECT dbxref.dbxref_id, accession,name, description
  96                                           FROM public.dbxref
  97                                           JOIN feature USING (dbxref_id)
  98                                           JOIN phenome.individual_dbxref USING (dbxref_id)
  99                                           WHERE individual_id= ?
 100                                           AND phenome.individual_dbxref.obsolete = 'f'"
 101                                              );
 102         $evidence_with_query->execute($individual_id);
 103     }
 104     my ($dbxref_id, $accession, $name, $description) = $evidence_with_query->fetchrow_array();
 105     my $available_evidence_with;
 106
 107     while($accession){
 108         $available_evidence_with.= "$dbxref_id*$name: $description|";
 109         ($dbxref_id, $accession, $name, $description) = $evidence_with_query->fetchrow_array();
 110     }
 111
 112
 113     print "$available_evidence_with";
 114
 115 }elsif ($type eq 'reference') {
 116
 117     my $reference_query;
 118     if ($locus_id) {
 119         $reference_query= $dbh->prepare("SELECT dbxref.dbxref_id, accession,title
 120                                           FROM public.dbxref
 121                                           JOIN public.pub_dbxref USING (dbxref_id)
 122                                           JOIN public.pub USING (pub_id)
 123                                           JOIN phenome.locus_dbxref USING (dbxref_id)
 124                                           WHERE locus_id= ?
 125                                           AND phenome.locus_dbxref.obsolete = 'f'"
 126                                         );
 127         $reference_query->execute($locus_id);
 128     }elsif ($individual_id) {
 129         $reference_query= $dbh->prepare("SELECT dbxref.dbxref_id, accession,title
 130                                           FROM public.dbxref
 131                                           JOIN public.pub_dbxref USING (dbxref_id)
 132                                           JOIN public.pub USING (pub_id)
 133                                           JOIN phenome.individual_dbxref USING (dbxref_id)
 134                                           WHERE individual_id= ?
 135                                           AND phenome.individual_dbxref.obsolete = 'f'"
 136                                         );
 137         $reference_query->execute($individual_id);
 138     }
 139     my ($dbxref_id, $accession, $title) = $reference_query->fetchrow_array();
 140     my $available_reference;
 141
 142     while($accession){
 143         $available_reference.= "$dbxref_id*$accession: $title|";
 144         ($dbxref_id, $accession, $title) = $reference_query->fetchrow_array();
 145     }
 146
 147
 148     print "$available_reference";
 149
 150 }