Mercurial > repos > jesse-erdmann > tapdance
diff lib/Report.pl @ 0:1437a2df99c0
Uploaded
author | jesse-erdmann |
---|---|
date | Fri, 09 Dec 2011 11:56:56 -0500 |
parents | |
children |
line wrap: on
line diff
--- /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; + +