--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/lib/Report.pl	Fri Dec 09 11:56:56 2011 -0500
@@ -0,0 +1,262 @@
+require 'config.pl';
+
+
+
+$sth = $dbh->prepare("drop table if exists lib_m_$proj");
+$sth->execute;
+$sth = $dbh->prepare("create table lib_m_$proj select distinct library, sum(number) as total from illumina4dec2_$proj group by library");
+$sth->execute;
+$sth = $dbh->prepare("update lib_m_$proj set library = left(library,LENGTH(library)-2) where library like '%-L'");
+$sth->execute;
+$sth = $dbh->prepare("update lib_m_$proj set library = left(library,LENGTH(library)-2) where library like '%-R'");
+$sth->execute;
+$sth = $dbh->prepare("drop table if exists lib_mappable_$proj");
+$sth->execute;
+$sth = $dbh->prepare("create table lib_mappable_$proj select library, sum(total) as total from lib_m_$proj group by library");
+$sth->execute;
+
+print "completed mapping!!!";
+
+###
+#Generate a report describing the mapping.
+###
+
+open OUT, "> results/project_summary.txt";
+print OUT "Summary of project $proj\n\nA.Report of project based counts \n";
+
+$sth = $dbh->prepare("select count(*) from illumina_raw_$proj");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       print OUT $row[0]."   Total number of Seqeunces\n";
+}
+
+$sth = $dbh->prepare("select count(*) from illumina_decoded_$proj;");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       print OUT $row[0]."   Total number of Sequences that map to a barcode\n";
+}
+
+$sth = $dbh->prepare("select count(*) from illumina_without_IRDR_$proj");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       print OUT $row[0]."   Total number of Sequences with a barcode that also have an IRDR\n";
+}
+
+$sth = $dbh->prepare("select count(*) from illumina_without_IRDR_$proj where type = 'idR';");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       print OUT $row[0]."   Total number of Ideal Right linker sequences\n";
+}
+
+$sth = $dbh->prepare("select count(*) from illumina_without_IRDR_$proj where type = 'idL';");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       print OUT $row[0]."   Total number of Ideal left linkersequences\n";
+}
+
+$sth = $dbh->prepare("select count(*) from illumina_without_IRDR_$proj where type = 'good';");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       print OUT $row[0]."   Total number of good sequences\n";
+}
+
+$sth = $dbh->prepare("select count(*) from illumina_without_IRDR_$proj where type = 'bam';");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       print OUT $row[0]."   Total number of Bamh1 sequence Artifact (removed)\n";
+}
+
+$sth = $dbh->prepare("select count(*) from illumina_without_IRDR_$proj where type = 'noTA';");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       print OUT $row[0]."   Total number of sequences removed because of lack of TA sequence)\n";
+}
+
+$sth = $dbh->prepare("select count(*) from illumina_without_IRDR_$proj where type = 'min';");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       print OUT $row[0]."   Total number of sequences removed because of sequence length < 24)\n";
+}     
+
+$sth = $dbh->prepare("select count(*) from illumina4dec2_$proj;");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+      print OUT $row[0]."   Total number of unique seqeunces pryor to mapping\n";
+}
+
+$sth = $dbh->prepare("select sum(number) from bowtie_$proj;");
+$sth->execute;
+while ((@row) = $sth->fetchrow_array) {
+       print OUT $row[0]."   Total SUM  of seqeunces that mapped\n";
+}
+
+$sth = $dbh->prepare("select count(*) from bowtie_$proj;");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       print OUT $row[0]."   Number of unique sequences that mapped\n";
+}
+
+$sth = $dbh->prepare("select count(*) from bowtie_lib_$proj;");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       print OUT $row[0]."   Number of distinct locations mapped\n";
+}
+
+$sth = $dbh->prepare("select count(*) from illumina_hist_$proj;");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       print OUT $row[0]."   Number of distinct regions mapped\n";
+}
+close OUT;
+
+#report part B
+open OUT, ">> results/project_summary.txt";
+print OUT"\n\nB. Library counts associated with project $proj\n";
+
+%sequence='';
+%barcode_count='';
+%IRDR_count='';
+%IRDR_good='';
+%IRDR_unique='';
+%map_count='';
+%map_total='';
+%nr_count='';
+@library='';
+
+$sth = $dbh->prepare("select * from barcode_$proj");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       $sequence{$row[1]} = $row[0];
+       push(@library,$row[1]);
+}
+
+$sth = $dbh->prepare("select library, count(id) from illumina_decoded_$proj group by library");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       $barcode_count{$row[0]} = $row[1];
+}
+
+$sth = $dbh->prepare("select library, count(id) from illumina_without_IRDR_$proj group by library");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       $IRDR_count{$row[0]} = $row[1];
+}
+
+$sth = $dbh->prepare("select library, sum(number) from illumina4dec2_$proj group by library;");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       $IRDR_good{$row[0]} = $row[1];
+}
+
+$sth = $dbh->prepare("select library, count(number) from illumina4dec2_$proj group by library;");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       $IRDR_unique{$row[0]} = $row[1];
+}
+
+$sth = $dbh->prepare("select library, sum(number) from bowtie_$proj group by library");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       $map_count{$row[0]} = $row[1];
+}
+
+$sth = $dbh->prepare("select library, count(id) from bowtie_$proj group by library");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       $map_total{$row[0]} = $row[1];
+}
+
+$sth = $dbh->prepare("select library, count(chromo) from bowtie_lib_$proj group by library");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       $map_nr{$row[0]} = $row[1];
+}
+
+print OUT "Library\tBarcode Sequence\tBarcode\tIRDR\tMappable\tUnique mappable\tTotal map\tUnique map\tNR map";
+foreach $item (@library) {
+print OUT "$item\t$sequence{$item}\t$barcode_count{$item}\t$IRDR_count{$item}\t$IRDR_good{$item}\t$IRDR_unique{$item}\t$map_count{$item}\t$map_total{$item}\t$map_nr{$item}\n";
+}
+close OUT;
+
+open OUT, ">> results/project_summary.txt";
+print OUT "\n\nC. Regions associated with project $proj\n";
+
+%mappable='';
+%name='';
+%count01='';
+%count001='';
+%count0001='';
+%count0='';
+@library='';
+
+$sth = $dbh->prepare("select * from lib_count_$proj");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       $name{$row[0]} = $row[1];
+       push(@library,$row[0]);
+}
+
+$sth = $dbh->prepare("select * from lib_mappable_$proj");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       $mappable{$row[0]} = $row[1];
+}
+
+$sth = $dbh->prepare("select A.library, count(chromo) from illumina_hist_$proj A, lib_mappable_$proj B where A.library = B.library and count/total > .01  group by A.library order by chromo,start+0");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       $count01{$row[0]} = $row[1];
+}
+
+$sth = $dbh->prepare("select A.library, count(chromo) from illumina_hist_$proj A, lib_mappable_$proj B where A.library = B.library and count/total > .001  group by A.library order by chromo,start+0");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       $count001{$row[0]} = $row[1];
+}
+
+$sth = $dbh->prepare("select A.library, count(chromo) from illumina_hist_$proj A, lib_mappable_$proj B where A.library = B.library and count/total > .0001  group by A.library order by chromo,start+0");
+$sth->execute;
+
+while ((@row) = $sth->fetchrow_array) {
+       $count0001{$row[0]} = $row[1];
+}
+
+$sth = $dbh->prepare("select A.library, count(chromo) from illumina_hist_$proj A, lib_mappable_$proj B where A.library = B.library and count/total > 0  group by A.library order by chromo,start+0");
+$sth->execute;
+
+print OUT "Library\tTotal mappable\ttotal map\tLocations >0.01\tLocations >0.001\tLocations >0.0001\t All mapped Locations";
+while ((@row) = $sth->fetchrow_array) {
+       $count0{$row[0]} = $row[1];
+}
+
+foreach $item (@library) {
+print OUT "$item\t$mappable{$item}\t$name{$item}\t$count01{$item}\t$count001{$item}\t$count0001{$item}\t$count0{$item}\n";
+}
+close OUT;
+
+