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;