|
0
|
1 require 'config.pl';
|
|
|
2
|
|
|
3
|
|
|
4
|
|
|
5 $sth = $dbh->prepare("drop table if exists lib_m_$proj");
|
|
|
6 $sth->execute;
|
|
|
7 $sth = $dbh->prepare("create table lib_m_$proj select distinct library, sum(number) as total from illumina4dec2_$proj group by library");
|
|
|
8 $sth->execute;
|
|
|
9 $sth = $dbh->prepare("update lib_m_$proj set library = left(library,LENGTH(library)-2) where library like '%-L'");
|
|
|
10 $sth->execute;
|
|
|
11 $sth = $dbh->prepare("update lib_m_$proj set library = left(library,LENGTH(library)-2) where library like '%-R'");
|
|
|
12 $sth->execute;
|
|
|
13 $sth = $dbh->prepare("drop table if exists lib_mappable_$proj");
|
|
|
14 $sth->execute;
|
|
|
15 $sth = $dbh->prepare("create table lib_mappable_$proj select library, sum(total) as total from lib_m_$proj group by library");
|
|
|
16 $sth->execute;
|
|
|
17
|
|
|
18 print "completed mapping!!!";
|
|
|
19
|
|
|
20 ###
|
|
|
21 #Generate a report describing the mapping.
|
|
|
22 ###
|
|
|
23
|
|
|
24 open OUT, "> results/project_summary.txt";
|
|
|
25 print OUT "Summary of project $proj\n\nA.Report of project based counts \n";
|
|
|
26
|
|
|
27 $sth = $dbh->prepare("select count(*) from illumina_raw_$proj");
|
|
|
28 $sth->execute;
|
|
|
29
|
|
|
30 while ((@row) = $sth->fetchrow_array) {
|
|
|
31 print OUT $row[0]." Total number of Seqeunces\n";
|
|
|
32 }
|
|
|
33
|
|
|
34 $sth = $dbh->prepare("select count(*) from illumina_decoded_$proj;");
|
|
|
35 $sth->execute;
|
|
|
36
|
|
|
37 while ((@row) = $sth->fetchrow_array) {
|
|
|
38 print OUT $row[0]." Total number of Sequences that map to a barcode\n";
|
|
|
39 }
|
|
|
40
|
|
|
41 $sth = $dbh->prepare("select count(*) from illumina_without_IRDR_$proj");
|
|
|
42 $sth->execute;
|
|
|
43
|
|
|
44 while ((@row) = $sth->fetchrow_array) {
|
|
|
45 print OUT $row[0]." Total number of Sequences with a barcode that also have an IRDR\n";
|
|
|
46 }
|
|
|
47
|
|
|
48 $sth = $dbh->prepare("select count(*) from illumina_without_IRDR_$proj where type = 'idR';");
|
|
|
49 $sth->execute;
|
|
|
50
|
|
|
51 while ((@row) = $sth->fetchrow_array) {
|
|
|
52 print OUT $row[0]." Total number of Ideal Right linker sequences\n";
|
|
|
53 }
|
|
|
54
|
|
|
55 $sth = $dbh->prepare("select count(*) from illumina_without_IRDR_$proj where type = 'idL';");
|
|
|
56 $sth->execute;
|
|
|
57
|
|
|
58 while ((@row) = $sth->fetchrow_array) {
|
|
|
59 print OUT $row[0]." Total number of Ideal left linkersequences\n";
|
|
|
60 }
|
|
|
61
|
|
|
62 $sth = $dbh->prepare("select count(*) from illumina_without_IRDR_$proj where type = 'good';");
|
|
|
63 $sth->execute;
|
|
|
64
|
|
|
65 while ((@row) = $sth->fetchrow_array) {
|
|
|
66 print OUT $row[0]." Total number of good sequences\n";
|
|
|
67 }
|
|
|
68
|
|
|
69 $sth = $dbh->prepare("select count(*) from illumina_without_IRDR_$proj where type = 'bam';");
|
|
|
70 $sth->execute;
|
|
|
71
|
|
|
72 while ((@row) = $sth->fetchrow_array) {
|
|
|
73 print OUT $row[0]." Total number of Bamh1 sequence Artifact (removed)\n";
|
|
|
74 }
|
|
|
75
|
|
|
76 $sth = $dbh->prepare("select count(*) from illumina_without_IRDR_$proj where type = 'noTA';");
|
|
|
77 $sth->execute;
|
|
|
78
|
|
|
79 while ((@row) = $sth->fetchrow_array) {
|
|
|
80 print OUT $row[0]." Total number of sequences removed because of lack of TA sequence)\n";
|
|
|
81 }
|
|
|
82
|
|
|
83 $sth = $dbh->prepare("select count(*) from illumina_without_IRDR_$proj where type = 'min';");
|
|
|
84 $sth->execute;
|
|
|
85
|
|
|
86 while ((@row) = $sth->fetchrow_array) {
|
|
|
87 print OUT $row[0]." Total number of sequences removed because of sequence length < 24)\n";
|
|
|
88 }
|
|
|
89
|
|
|
90 $sth = $dbh->prepare("select count(*) from illumina4dec2_$proj;");
|
|
|
91 $sth->execute;
|
|
|
92
|
|
|
93 while ((@row) = $sth->fetchrow_array) {
|
|
|
94 print OUT $row[0]." Total number of unique seqeunces pryor to mapping\n";
|
|
|
95 }
|
|
|
96
|
|
|
97 $sth = $dbh->prepare("select sum(number) from bowtie_$proj;");
|
|
|
98 $sth->execute;
|
|
|
99 while ((@row) = $sth->fetchrow_array) {
|
|
|
100 print OUT $row[0]." Total SUM of seqeunces that mapped\n";
|
|
|
101 }
|
|
|
102
|
|
|
103 $sth = $dbh->prepare("select count(*) from bowtie_$proj;");
|
|
|
104 $sth->execute;
|
|
|
105
|
|
|
106 while ((@row) = $sth->fetchrow_array) {
|
|
|
107 print OUT $row[0]." Number of unique sequences that mapped\n";
|
|
|
108 }
|
|
|
109
|
|
|
110 $sth = $dbh->prepare("select count(*) from bowtie_lib_$proj;");
|
|
|
111 $sth->execute;
|
|
|
112
|
|
|
113 while ((@row) = $sth->fetchrow_array) {
|
|
|
114 print OUT $row[0]." Number of distinct locations mapped\n";
|
|
|
115 }
|
|
|
116
|
|
|
117 $sth = $dbh->prepare("select count(*) from illumina_hist_$proj;");
|
|
|
118 $sth->execute;
|
|
|
119
|
|
|
120 while ((@row) = $sth->fetchrow_array) {
|
|
|
121 print OUT $row[0]." Number of distinct regions mapped\n";
|
|
|
122 }
|
|
|
123 close OUT;
|
|
|
124
|
|
|
125 #report part B
|
|
|
126 open OUT, ">> results/project_summary.txt";
|
|
|
127 print OUT"\n\nB. Library counts associated with project $proj\n";
|
|
|
128
|
|
|
129 %sequence='';
|
|
|
130 %barcode_count='';
|
|
|
131 %IRDR_count='';
|
|
|
132 %IRDR_good='';
|
|
|
133 %IRDR_unique='';
|
|
|
134 %map_count='';
|
|
|
135 %map_total='';
|
|
|
136 %nr_count='';
|
|
|
137 @library='';
|
|
|
138
|
|
|
139 $sth = $dbh->prepare("select * from barcode_$proj");
|
|
|
140 $sth->execute;
|
|
|
141
|
|
|
142 while ((@row) = $sth->fetchrow_array) {
|
|
|
143 $sequence{$row[1]} = $row[0];
|
|
|
144 push(@library,$row[1]);
|
|
|
145 }
|
|
|
146
|
|
|
147 $sth = $dbh->prepare("select library, count(id) from illumina_decoded_$proj group by library");
|
|
|
148 $sth->execute;
|
|
|
149
|
|
|
150 while ((@row) = $sth->fetchrow_array) {
|
|
|
151 $barcode_count{$row[0]} = $row[1];
|
|
|
152 }
|
|
|
153
|
|
|
154 $sth = $dbh->prepare("select library, count(id) from illumina_without_IRDR_$proj group by library");
|
|
|
155 $sth->execute;
|
|
|
156
|
|
|
157 while ((@row) = $sth->fetchrow_array) {
|
|
|
158 $IRDR_count{$row[0]} = $row[1];
|
|
|
159 }
|
|
|
160
|
|
|
161 $sth = $dbh->prepare("select library, sum(number) from illumina4dec2_$proj group by library;");
|
|
|
162 $sth->execute;
|
|
|
163
|
|
|
164 while ((@row) = $sth->fetchrow_array) {
|
|
|
165 $IRDR_good{$row[0]} = $row[1];
|
|
|
166 }
|
|
|
167
|
|
|
168 $sth = $dbh->prepare("select library, count(number) from illumina4dec2_$proj group by library;");
|
|
|
169 $sth->execute;
|
|
|
170
|
|
|
171 while ((@row) = $sth->fetchrow_array) {
|
|
|
172 $IRDR_unique{$row[0]} = $row[1];
|
|
|
173 }
|
|
|
174
|
|
|
175 $sth = $dbh->prepare("select library, sum(number) from bowtie_$proj group by library");
|
|
|
176 $sth->execute;
|
|
|
177
|
|
|
178 while ((@row) = $sth->fetchrow_array) {
|
|
|
179 $map_count{$row[0]} = $row[1];
|
|
|
180 }
|
|
|
181
|
|
|
182 $sth = $dbh->prepare("select library, count(id) from bowtie_$proj group by library");
|
|
|
183 $sth->execute;
|
|
|
184
|
|
|
185 while ((@row) = $sth->fetchrow_array) {
|
|
|
186 $map_total{$row[0]} = $row[1];
|
|
|
187 }
|
|
|
188
|
|
|
189 $sth = $dbh->prepare("select library, count(chromo) from bowtie_lib_$proj group by library");
|
|
|
190 $sth->execute;
|
|
|
191
|
|
|
192 while ((@row) = $sth->fetchrow_array) {
|
|
|
193 $map_nr{$row[0]} = $row[1];
|
|
|
194 }
|
|
|
195
|
|
|
196 print OUT "Library\tBarcode Sequence\tBarcode\tIRDR\tMappable\tUnique mappable\tTotal map\tUnique map\tNR map";
|
|
|
197 foreach $item (@library) {
|
|
|
198 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";
|
|
|
199 }
|
|
|
200 close OUT;
|
|
|
201
|
|
|
202 open OUT, ">> results/project_summary.txt";
|
|
|
203 print OUT "\n\nC. Regions associated with project $proj\n";
|
|
|
204
|
|
|
205 %mappable='';
|
|
|
206 %name='';
|
|
|
207 %count01='';
|
|
|
208 %count001='';
|
|
|
209 %count0001='';
|
|
|
210 %count0='';
|
|
|
211 @library='';
|
|
|
212
|
|
|
213 $sth = $dbh->prepare("select * from lib_count_$proj");
|
|
|
214 $sth->execute;
|
|
|
215
|
|
|
216 while ((@row) = $sth->fetchrow_array) {
|
|
|
217 $name{$row[0]} = $row[1];
|
|
|
218 push(@library,$row[0]);
|
|
|
219 }
|
|
|
220
|
|
|
221 $sth = $dbh->prepare("select * from lib_mappable_$proj");
|
|
|
222 $sth->execute;
|
|
|
223
|
|
|
224 while ((@row) = $sth->fetchrow_array) {
|
|
|
225 $mappable{$row[0]} = $row[1];
|
|
|
226 }
|
|
|
227
|
|
|
228 $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");
|
|
|
229 $sth->execute;
|
|
|
230
|
|
|
231 while ((@row) = $sth->fetchrow_array) {
|
|
|
232 $count01{$row[0]} = $row[1];
|
|
|
233 }
|
|
|
234
|
|
|
235 $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");
|
|
|
236 $sth->execute;
|
|
|
237
|
|
|
238 while ((@row) = $sth->fetchrow_array) {
|
|
|
239 $count001{$row[0]} = $row[1];
|
|
|
240 }
|
|
|
241
|
|
|
242 $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");
|
|
|
243 $sth->execute;
|
|
|
244
|
|
|
245 while ((@row) = $sth->fetchrow_array) {
|
|
|
246 $count0001{$row[0]} = $row[1];
|
|
|
247 }
|
|
|
248
|
|
|
249 $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");
|
|
|
250 $sth->execute;
|
|
|
251
|
|
|
252 print OUT "Library\tTotal mappable\ttotal map\tLocations >0.01\tLocations >0.001\tLocations >0.0001\t All mapped Locations";
|
|
|
253 while ((@row) = $sth->fetchrow_array) {
|
|
|
254 $count0{$row[0]} = $row[1];
|
|
|
255 }
|
|
|
256
|
|
|
257 foreach $item (@library) {
|
|
|
258 print OUT "$item\t$mappable{$item}\t$name{$item}\t$count01{$item}\t$count001{$item}\t$count0001{$item}\t$count0{$item}\n";
|
|
|
259 }
|
|
|
260 close OUT;
|
|
|
261
|
|
|
262
|
