cgi-bin/content/unigene_builds.pl

   1 #!/usr/bin/perl -w
   2 use strict;
   3 use CXGN::Page;
   4 use CXGN::DB::Connection;
   5
   6 our %builds;
   7 our $page = CXGN::Page->new( "SGN Unigene Build Summary", "Koni");
   8
   9 my $dbh = CXGN::DB::Connection->new();
  10 my $buildq = $dbh->prepare("SELECT unigene_build_id, groups.comment, build_date from unigene_build LEFT JOIN groups ON (groups.group_id=unigene_build.organism_group_id) where status=?");
  11 my $clusterq = $dbh->prepare("SELECT COUNT(DISTINCT cluster_no) from unigene where unigene_build_id=?");
  12 my $contigq = $dbh->prepare("SELECT COUNT(unigene_id) from unigene where nr_members>1 and unigene_build_id=?");
  13 my $singletq = $dbh->prepare("SELECT COUNT(unigene_id) from unigene where nr_members=1 and unigene_build_id=?");
  14 my $source_dataq = $dbh->prepare("SELECT COUNT(group_id) from unigene_build LEFT JOIN group_linkage ON (unigene_build.source_data_group_id=group_linkage.group_id) where unigene_build_id=?");
  15 $buildq->execute("C");
  16 while(my ($build_id, $build_name, $build_date) = $buildq->fetchrow_array())
  17 {
  18
  19     $builds{$build_name}->{build_date} = $build_date;
  20
  21     $clusterq->execute($build_id);
  22     ($builds{$build_name}->{clusters}) = $clusterq->fetchrow_array();
  23     $contigq->execute($build_id);
  24     ($builds{$build_name}->{contigs}) = $contigq->fetchrow_array();
  25     $singletq->execute($build_id);
  26     ($builds{$build_name}->{singlets}) = $singletq->fetchrow_array();
  27     $source_dataq->execute($build_id);
  28     ($builds{$build_name}->{nr_inputs}) = $source_dataq->fetchrow_array();
  29
  30     $builds{$build_name}->{unigenes} = $builds{$build_name}->{singlets} + $builds{$build_name}->{contigs};
  31
  32 }
  33 $buildq->finish();
  34 $clusterq->finish();
  35 $contigq->finish();
  36 $singletq->finish();
  37 $source_dataq->finish();
  38
  39 $page->header();
  40
  41 print <<EOF;
  42
  43 <h3>Unigene Build Statistics</h3>
  44 Summary of all current unigene builds available on SGN. Unigene builds are updated periodically as new data becomes available or new advances are made in assembly technology or strategy. For information on each build series, click the links below.
  45
  46 <font color="gray">Note: Content describing each build series is under development as each build series is updated with our latest assembly strategy. Links will be added as each build is updated.</font>
  47
  48 <table summary="" cellspacing="0" cellpadding="0" border="0" align="center" width="60%">
  49
  50 EOF
  51
  52 foreach my $build_name ( sort { $builds{$b}->{build_date} cmp $builds{$a}->{build_date} } keys %builds ) {
  53
  54   print <<EOF;
  55   <tr><td colspan="2"><b>$build_name</b></td><td>Date: $builds{$build_name}->{build_date}</td></tr>
  56   <tr><td colspan="3">$builds{$build_name}->{nr_inputs} ESTs assembled into $builds{$build_name}->{unigenes} unigenes</td></tr>
  57   <tr><td>Clusters: $builds{$build_name}->{clusters}</td>
  58       <td>Contigs: $builds{$build_name}->{contigs}</td>
  59       <td>Singlets: $builds{$build_name}->{singlets}</td></tr>
  60   <tr><td colspan="3"><br /></td></tr>
  61
  62 EOF
  63 }
  64
  65 print "</table><br /><br />";
  66
  67 $page->footer();