Show ghg_details.cgi syntax highlighted
#!/usr/local/bin/perl -w
use strict;
use FindBin;
use DBI;
use CGI;
use CGI::Carp;
use Time::HiRes qw(gettimeofday);
use Queries qw(init_db);
my %times;
#Environment Variables
$ENV{'PATH'} = '/misc/afcs/apps/bin:/usr/local/bin:' . $ENV{'PATH'};
$ENV{'ORACLE_HOME'} = '/usr/local/apps/oracle/product/9.2.0';
$ENV{'ORACLE_PATH'} = "$ENV{'ORACLE_HOME'}/bin";
$ENV{'PATH'} = "$ENV{'ORACLE_PATH'}:" . $ENV{'PATH'};
my $scriptname = $FindBin::Script;
my $starttime = time;
$times{'start'} = gettimeofday;
my $q = new CGI;
my %ref_types =
(
G => 'Gene',
T => 'Translation',
A => 'Accession',
);
my $ref_name = $q->param("ref_name");
(! $ref_name) && ($ref_name = '');
my $ref_type = $q->param("ref_type");
$ref_name =~ s/^\s+//;
$ref_name =~ s/\s+$//;
$ref_name =~ s/\s+/ /g;
my $ghgdb;
print $q->header;
unless (init_db($ghgdb))
{
print "Error: cannot connect to database\n";
print $q->end_html;
exit;
}
#SQL QUERIES===================================================================
$ghgdb->{LongReadLen} = 1024 * 1024;
$ghgdb->{RaiseError} = 1;
#General Gene Information
my $gene_info = $ghgdb->prepare
("
select distinct taxonomy_id, g.gene_id, symbol, name
from gene_synonyms s, genes g
where g.gene_id = ?
and g.gene_id = s.gene_id
");
#Cross-Database Links Ens->EG
my $ensembl_egene = $ghgdb->prepare
("
select egene_id, symbol, name
from ensembl_egene e, genes g
where ensembl_id = ?
and g.gene_id = e.egene_id
");
#Cross-Database Links EG->Ens
my $egene_ensembl = $ghgdb->prepare
("
select ensembl_id, symbol, name
from ensembl_egene e, genes g
where egene_id = ?
and g.gene_id = e.ensembl_id
");
#Etrezgene Accessions
my $eg_accfg = $ghgdb->prepare
("
select distinct accession
from gene_accession where gene_id = ?
order by accession
");
#Ensembl Accessions
my $ens_accfg = $ghgdb->prepare
("
select g.translation, accession
from gene_translation g, trans_accession a
where g.gene_id = ?
and g.translation = a.translation
order by g.translation
");
#Synonyms
my $synfg = $ghgdb->prepare
("
select distinct gene_syn
from gene_synonyms where gene_id = ?
order by lower(gene_syn)
");
#Gene Homology Table
my $genhom = $ghgdb->prepare
("
select gene_id_2, database
from gene_homology
where gene_id_1 = ?
");
#ERROR OUTPUT==================================================================
unless ($ref_name)
{
print $q->start_html('No Reference specified.');
print $q->h1('No Reference specified.');
print '<p>No Reference was specified to display. </p>';
print '<p>Use <a href="ghg_search.cgi">ghg_search.cgi</a> to search. </p>';
print $q->end_html;
exit(0);
}
print $q->start_html("Homology Database Object Details for $ref_name");
print $q->h1("Homology Database Object Details for $ref_name");
unless (exists $ref_types{$ref_type})
{
print '<p>', $ref_type, ' is an invalid reference type. </p>';
print '<p>Use <a href="cgi_search.cgi">cgi_search.cgi</a> to search. </p>';
print $q->end_html;
exit(0);
}
my %gene_details;
my $database;
#GENES=========================================================================
if ($ref_type eq 'G')
{
$database = &checkdb($ref_name);
#General Information
$gene_info->execute($ref_name);
my ($taxid,$geneid,$symbol,$name) = $gene_info->fetchrow_array;
(! $symbol) && ($symbol = '');
(! $name) && ($name = '');
print $q->h3("Gene Details for $ref_name");
print '<p><table align="center" border="1" cellpadding="5">';
print "<caption>General Information</caption>\n";
print '<tr><th>Gene ID</th><th>Taxonomy ID</th><th>Symbol</th>',
'<th>Name</th><th>Database</th></tr>',
"\n";
print '<tr>';
print "<td>$geneid</td>";
print "<td>$taxid</td>";
print '<td>',searchstr($symbol),'</td>';
print '<td>',searchstr($name),'</td>';
print "<td>$database</td>";
print "</tr>\n";
print "</table></p><hr>";
#Cross-Database Links
my %crossdb;
my $findcross;
my $otherdb;
($database eq 'Ensembl') && ($findcross = $ensembl_egene)
&& ($otherdb = 'Entrezgene');
($database eq 'Entrezgene') && ($findcross = $egene_ensembl)
&& ($otherdb = 'Ensembl');
print '<p><table align="center" border="1" cellpadding="5">';
print "<caption>Cross-Database Links</caption>\n";
print '<tr><th>Gene ID</th><th>Symbol</th><th>Name</th>',
'<th>Database</th></tr>', "\n";
$findcross->execute($geneid);
while (my ($geneid,$symbol,$name) = $findcross->fetchrow_array)
{
(! $symbol) && ($symbol = '');
(! $name) && ($name = '');
$crossdb{$geneid}{'symbol'} = $symbol;
$crossdb{$geneid}{'name'} = $name;
}
my $linkcount = 0; #count the links, print msg if zero
foreach my $geneid (sort {
lc $crossdb{$a}{'symbol'} cmp lc $crossdb{$b}{'symbol'}
||
$a cmp $b
} keys %crossdb)
{
$linkcount++;
print '<tr>';
print '<td>', linkname($geneid,'G'), '</td>';
print '<td>', searchstr($crossdb{$geneid}{'symbol'}),'</td>';
print '<td>', searchstr($crossdb{$geneid}{'name'}),'</td>';
print '<td>', checkdb($geneid), '</td>';
print "</tr>\n";
}
if ($linkcount == 0)
{
print '<tr><td align="center" colspan=4>' .
"No corresponding gene found in $otherdb database." .
'</td></tr>',"\n";
}
print "</table></p><hr>\n";
#Protein Accessions
my $accfg;
if ($database eq 'Entrezgene')
{
print '<p><table align="center" border="1" cellpadding="5">';
print "<caption>Protein Accessions</caption>\n";
$accfg = $eg_accfg;
my $accstr = '';
$accfg->execute($geneid);
while (my $acc = $accfg->fetchrow_array)
{
$accstr .= searchstr($acc) . '; ';
}
$accstr =~ s/; $//;
if ($accstr eq ''){$accstr = "No accessions found."}
print "<td>$accstr</td>";
print "</table></p><hr>\n";
}
elsif ($database eq 'Ensembl')
{
my %ens_accs;
$accfg = $ens_accfg;
my $acc_exist = 0; #if no matches, span columns and print message.
print '<p><table align="center" border="1" cellpadding="5">';
print "<caption>Protein Accessions</caption>\n";
print "<tr><th>Translation</th><th>Accession</th>\n";
$accfg->execute($geneid);
while (my ($trans,$acc) = $accfg->fetchrow_array)
{
$ens_accs{$trans} = $acc;
($acc != '') && ($acc_exist = 1);
}
if ($acc_exist == 1)
{
foreach my $trans (sort {
lc $ens_accs{$a}{'acc'} cmp lc $ens_accs{$b}{'acc'}
||
$a cmp $b
} keys %ens_accs)
{
print '<tr>';
print '<td>',$trans,'</td>';
print '<td>',searchstr($ens_accs{$trans}),'</td>';
print "</tr>\n";
}
}
else
{
print '<tr><td align="center" colspan=2>' .
'No accessions found.</td></tr>'
}
print "</table></p><hr>\n";
}
#Gene Synonyms
print '<p><table align="center" border="1" cellpadding="5">';
print "<caption>Synonyms</caption>\n";
my $synstr = '';
$synfg->execute($geneid);
while (my $syn = $synfg->fetchrow_array)
{
{
$synstr .= searchstr($syn) . '; ';
}
}
$synstr =~ s/; $//;
if ($synstr eq '') {$synstr = "No synonyms found."}
print "</tr><td>$synstr</td></tr>\n";
print "</table></p><hr>\n";
#Gene Homology
print '<p><table align="center" border="1" cellpadding="5">';
print "<caption>Gene Homology</caption>\n";
print "<tr><th>Homolog</th><th>Source(s)</th>\n";
#each homolog has an id and a homology source string
my %homologs;
#Homologs from Homology Databases
$genhom->execute($geneid);
while (my ($homolog,$database) = $genhom->fetchrow_array)
{
$homologs{$homolog}{'database'} = $database;
$homologs{$homolog}{'source_str'} = '';
if ($homologs{$homolog}{'database'} eq 'H')
{$homologs{$homolog}{'source_str'} .= 'Homologene<br>';}
elsif ($homologs{$homolog}{'database'} eq 'E')
{$homologs{$homolog}{'source_str'} .= 'Ensembl Homology<br>';}
#Grab cross-database links for each homolog
}
#Homologs from Cross-Database Homology
foreach my $crossdb_id (keys %crossdb)
{
$genhom->execute($crossdb_id);
while (my ($homolog,$database) = $genhom->fetchrow_array)
{
$homologs{$homolog}{'database'} = $database;
$homologs{$homolog}{'source_str'} = '';
if ($homologs{$homolog}{'database'} eq 'H')
{
$homologs{$homolog}{'source_str'} .=
'Homologene via Cross-Database Link<br>';
}
elsif ($homologs{$homolog}{'database'} eq 'E')
{
$homologs{$homolog}{'source_str'} .=
'Ensembl Homology via Cross-Database Link<br>';
}
}
}
foreach my $homolog (sort{
lc $crossdb{$a}{'database'} cmp lc $crossdb{$b}{'database'}
||
$a cmp $b
} keys %homologs)
{
print '<tr>';
print '<td>', linkname($homolog,'G'), '</td>';
print '<td>', $homologs{$homolog}{'source_str'}, '</td>';
print "</tr>\n";
}
print "</table></p><hr>\n";
}
$ghgdb->disconnect;
print $q->end_html;
#Subroutines===================================================================
#Determine the Database that the ID Belongs to
#args: [String] gene or protein ID
#returns: [String] database name
sub checkdb
{
my $db;
if ($_[0] =~ /^ENS.*/)
{$db = 'Ensembl';}
else
{$db = 'Entrezgene';}
return $db;
}
#Create a database link for the ID
#args: [String] gene or protein Id, [String] reference type
#returns: [String] html code for link
sub linkname
{
my $link = "<a href=\"ghg_details.cgi\?ref_name=$_[0]&ref_type=$_[1]\">" .
"$_[0]</a>";
return $link;
}
#Link to the ghg_search.cgi page and search for this string.
#args: [String] string to search
#returns: [String] html code for link
sub searchstr
{
my $link = "<a href=\"ghg_search.cgi\?name_field=$_[0]\">$_[0]</a>";
return $link;
}
See more files for this project here