#!/usr/bin/perl -w

# detail, last update:  6/23/04
# Presents detailed CGI information about soybean features
# by Chet Langin, clangin@siu.edu
# SIU Plant Biotechnology

use strict;
use CGI ':standard';
use DBI;

my $query_string = $ENV{QUERY_STRING};
my @query_array = split /&/, $query_string;
my $name = $query_array[0];
my $id = $query_array[1];          # gives "id"
my $class = $query_array[2];       # gives "Sequence"
my $method = $query_array[3];      # the track
my $source = $query_array[4];      # gives "various"
my $ref = $query_array[5];         # the MLG
my $description = $query_array[6]; # the note
my $start= $query_array[7];
my $end = $query_array[8];
my $segstart = $query_array[9];    # segment start
my $segend = $query_array[10];     # segment end

my $track = $method;

my $mlg = $ref;
substr($mlg, 0, 3) = "";

$description =~ s/%20/ /g;
$description =~ s/%2C/,/g;
$description =~ s/%2F/\//g;
my $note = $description;

my $connected = 1;
my $dbh = DBI->connect("dbi:mysql:soy", "(username)", "(password)")
  or $connected = 0;

my $current_time = localtime;

print header, start_html(-title=>"$name, SIU Bioinformatics", 
                         -author=>'clangin@siu.edu', 
                         -bgcolor=>'#faf0e6');

print 
  a({-href=>'http://bioinformatics.siu.edu'}, '[SIU Bioinformatics Top]'),
  a({-href=>'http://soybeangenome.siu.edu'}, '[SIU Soybean Genome Top]'),
  p,
  table({-border=>'5', 
         -width=>'260',
         -bgcolor=>'#66ffff', 
         -bordercolor=>'lightblue',
         -bordercolordark=>'#ccffff',
         -bordercolorlight=>'$ccffff',
         -cellpadding=>'5'             },
    caption(h3("Soybean Genomic Feature")),
    Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th({-colspan=>'2', -align=>'CENTER'}, h1("$name"))),
    Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th(["Track"]). td(["$track"])),
    Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th(["MLG"]). td(["$mlg"])),
    Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th(["Notes"]). td(["$note"])),
    Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th(["Start"]). td(["$start"])),
    Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th(["End"]). td(["$end"]))
  );

########################################
#############  Contigs  ################
########################################

if($track eq "gContigs"   ||
   $track eq "bContigs"   ||
   $track eq "RevContigs" ||
   $track eq "sContigs"      ) {

  # get the duplicates
  my @table_rows = ();
  my $sth2a = $dbh->prepare("select dup_i, mlg, start, end, rev_start, rev_end, spread_start, spread_end, flip from contig_anchors where ctg = '$name'");
  $sth2a->execute();
  my @row2a = ();
  while(@row2a = $sth2a->fetchrow_array()) {
    push(@table_rows, td(\@row2a));
  } # while
  print p,
    table({-border=>'5', 
           -bgcolor=>'#66ffff', 
           -bordercolor=>'lightblue',
           -bordercolordark=>'#ccffff',
           -bordercolorlight=>'$ccffff',
           -cellpadding=>'5'             },
      caption(h3("Contig Duplicates, if Any")),
      Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th({-align=>'CENTER'}, ['Index', 'MLG', 'Start', 'End', 'Reverse Start', 'Reverse End', 'Spread Start', 'Spread End', 'Flipped'])),
      Tr([@table_rows])
    ); # table

  # get the gamma clones
  @table_rows = ();
  my $sth2b = $dbh->prepare("select distinct contig2clone.clone from contig2clone, clones where contig2clone.ctg = '$name' and contig2clone.clone = clones.clone and clones.type = 'gamma' and contig2clone.valid = 'yes' order by clone");
  $sth2b->execute();
  my @row2b = ();
  while(@row2b = $sth2b->fetchrow_array()) {
    push(@table_rows, td(\@row2b));
  } # while
  print p,
    table({-border=>'5', 
           -width=>'150',
           -bgcolor=>'#66ffff', 
           -bordercolor=>'lightblue',
           -bordercolordark=>'#ccffff',
           -bordercolorlight=>'$ccffff',
           -cellpadding=>'5'             },
      caption(h3("Gamma Clones")),
      Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th({-align=>'CENTER'}, ['Gamma Clone'])),
      Tr([@table_rows])
    ); # table

  # get the end matches info and put it in rows
  @table_rows = ();
  my $sth2z = $dbh->prepare("select end_matches.side1, end_matches.clone1, end_matches.bands1, end_matches.ctg2, end_matches.side2, end_matches.clone2, end_matches.bands2, end_matches.bandsm, end_matches.score, contig_anchors.mlg, contig_anchors.start from end_matches, contig_anchors where ctg1 = '$name' and contig_anchors.ctg = end_matches.ctg2 order by contig_anchors.mlg, contig_anchors.start");
  $sth2z->execute();
  my @row2z = ();
  while(@row2z = $sth2z->fetchrow_array()) {
    my $side1 = $row2z[0];
    my $clone1 = $row2z[1];
    my $bands1 = $row2z[2];
    my $ctg2 = $row2z[3];
    my $side2 = $row2z[4];
    my $clone2 = $row2z[5];
    my $bands2 = $row2z[6];
    my $bandsm = $row2z[7];
    my $score = $row2z[8];
    push(@table_rows, td(\@row2z));
  } # while
  # print the end matches table
  print p,
    table({-border=>'5', 
           -bgcolor=>'#66ffff', 
           -bordercolor=>'lightblue',
           -bordercolordark=>'#ccffff',
           -bordercolorlight=>'$ccffff',
           -cellpadding=>'5'             },
      caption(h3("End Matches")),
      Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th({-align=>'CENTER'}, ['Side', 'Clone', 'Bands', 'Other Ctg', 'Side', 'Clone', 'Bands', 'Matching Bands', 'Score', 'MLG', 'Start'])),
      Tr([@table_rows])
    );

  # get the beta 2 clones
  @table_rows = ();
  my $sth2c = $dbh->prepare("select distinct contig2clone.clone from contig2clone, clones where contig2clone.ctg = '$name' and contig2clone.clone = clones.clone and clones.type = 'beta 2' and contig2clone.valid = 'yes' order by clone");
  $sth2c->execute();
  my @row2c = ();
  while(@row2c = $sth2c->fetchrow_array()) {
    push(@table_rows, td(\@row2c));
  } # while
  print p,
    table({-border=>'5', 
           -width=>'150',
           -bgcolor=>'#66ffff', 
           -bordercolor=>'lightblue',
           -bordercolordark=>'#ccffff',
           -bordercolorlight=>'$ccffff',
           -cellpadding=>'5'             },
      caption(h3("Beta 2 Clones")),
      Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th({-align=>'CENTER'}, ['Beta 2 Clone'])),
      Tr([@table_rows])
    ); # table

} # if


#############################################
##############  Clones  #####################
#############################################

if($track eq "gClones"  ||
   $track eq "aClones"  ||
   $track eq "b1Clones"  ||
   $track eq "b2Clones"  ||
   $track eq "RevClones" ||
   $track eq "gsClones"  ||
   $track eq "b2sClones"   ) {
  my @table_rows = ();

  # show duplications, if any
  my $sth3 = $dbh->prepare("select distinct mlg, start, end from clone_locations where clone = '$name' order by mlg, start");
  $sth3->execute();
  my @row3 = ();
  while(@row3 = $sth3->fetchrow_array()) {
    push(@table_rows, td(\@row3));
  } # while
  print p,
    table({-border=>'5', 
           -width=>'270',
           -bgcolor=>'#66ffff', 
           -bordercolor=>'lightblue',
           -bordercolordark=>'#ccffff',
           -bordercolorlight=>'$ccffff',
           -cellpadding=>'5'             },
      caption(h3("Duplicated Clones, if Any")),
      Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th({-align=>'CENTER'}, ['MLG', 'Start', 'End'])),
      Tr([@table_rows])
    ); # table

  # show loci, if any
  @table_rows = ();
  my $sth3a = $dbh->prepare("Select locus from clone2locus3 where clone = '$name'");
  $sth3a->execute();
  my @row3a = ();
  while(@row3a = $sth3a->fetchrow_array()) {
    push(@table_rows, td(\@row3a));
  } # while
  print p,
    table({-border=>'5', 
           -width=>'110',
           -bgcolor=>'#66ffff', 
           -bordercolor=>'lightblue',
           -bordercolordark=>'#ccffff',
           -bordercolorlight=>'$ccffff',
           -cellpadding=>'5'             },
      caption(h3("Loci, if Any")),
      Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th({-align=>'CENTER'}, ['Locus'])),
      Tr([@table_rows])
    ); # table

  # show related genes
  @table_rows = ();
  my $sth3b = $dbh->prepare("Select related from related where hit = '$name' order by hit");
  $sth3b->execute();
  my @row3b = ();
  while(@row3b = $sth3b->fetchrow_array()) {
    push(@table_rows, td(\@row3b));
  } # while
  print p,
    table({-border=>'5', 
           -width=>'110',
           -bgcolor=>'#66ffff', 
           -bordercolor=>'lightblue',
           -bordercolordark=>'#ccffff',
           -bordercolorlight=>'$ccffff',
           -cellpadding=>'5'             },
      caption(h3("Related Genes")),
      Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th({-align=>'CENTER'}, ['Related Gene'])),
      Tr([@table_rows])
    ); # table

} # if


#############################################
##############  Loci  #######################
#############################################

if($track eq "aLoci" ||
   $track eq "bLoci" ||
   $track eq "gLoci"   ) {

  # get the clones
  my @table_rows = ();
  my $sth4 = $dbh->prepare("select clone from clone2locus3 where locus = '$name'");
  $sth4->execute();
  my @row4 = ();
  while(@row4 = $sth4->fetchrow_array()) {
    push(@table_rows, td(\@row4));
  } # while
  print p,
    table({-border=>'5', 
           -width=>'110',
           -bgcolor=>'#66ffff', 
           -bordercolor=>'lightblue',
           -bordercolordark=>'#ccffff',
           -bordercolorlight=>'$ccffff',
           -cellpadding=>'5'             },
      caption(h3("Clones")),
      Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th({-align=>'CENTER'}, ['Clone'])),
      Tr([@table_rows])
    ); # table

  # get the contigs
  @table_rows = ();
  my $sth4a = $dbh->prepare("select contig2clone.ctg from clone2locus3, contig2clone where locus = '$name' and clone2locus3.clone = contig2clone.clone order by contig2clone.ctg");
  $sth4a->execute();
  my @row4a = ();
  while(@row4a = $sth4a->fetchrow_array()) {
    push(@table_rows, td(\@row4a));
  } # while
  print p,
    table({-border=>'5', 
           -width=>'110',
           -bgcolor=>'#66ffff', 
           -bordercolor=>'lightblue',
           -bordercolordark=>'#ccffff',
           -bordercolorlight=>'$ccffff',
           -cellpadding=>'5'             },
      caption(h3("Contigs")),
      Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th({-align=>'CENTER'}, ['Contigs'])),
      Tr([@table_rows])
    ); # table

  # get the related genes
  @table_rows = ();
  my $sth4b = $dbh->prepare("select related from related where hit = '$name' order by related");
  $sth4b->execute();
  my @row4b = ();
  while(@row4b = $sth4b->fetchrow_array()) {
    push(@table_rows, td(\@row4b));
  } # while
  print p,
    table({-border=>'5', 
           -width=>'110',
           -bgcolor=>'#66ffff', 
           -bordercolor=>'lightblue',
           -bordercolordark=>'#ccffff',
           -bordercolorlight=>'$ccffff',
           -cellpadding=>'5'             },
      caption(h3("Related Genes")),
      Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th({-align=>'CENTER'}, ['Related Gene'])),
      Tr([@table_rows])
    ); # table

} # if


#############################################
##############  Confirmed  ##################
#############################################

if($track eq "Confirmed") {
  # get the info and put it in rows
  my @table_rows = ();
  my $sth1 = $dbh->prepare("select mlg, start, end, type from clone_locations where clone = '$name'");
  $sth1->execute();
  my @row1 = ();
  while(@row1 = $sth1->fetchrow_array()) {
    my $mlg = @row1[0];
    my $start = @row1[1];
    my $end = @row1[2];
    my $type = @row1[3];
    my @temp_row = ();
    push @temp_row, $mlg;
    push @temp_row, $start;
    push @temp_row, $end;
    push @temp_row, $type;
    push(@table_rows, td(\@temp_row));
  } # while

  # print the table
  print p,
    table({-border=>'5', 
           -bgcolor=>'#66ffff', 
           -bordercolor=>'lightblue',
           -bordercolordark=>'#ccffff',
           -bordercolorlight=>'$ccffff',
           -cellpadding=>'5'             },
      caption(h3("Confirmed vs. Other Locations")),
      Tr({-align=>'LEFT', -valign=>'MIDDLE'}, th({-align=>'CENTER'}, ['MLG', 'Start', 'End', 'Type'])),
      Tr({-align=>'LEFT', -valign=>'MIDDLE'}, td({-align=>'LEFT'}, ["$mlg", "$start", "$end", "Confirmed"])),
      Tr([@table_rows])
    );

} # if

if($connected == 1) {
  $dbh->disconnect;
} # if

print p, $current_time;