ViewVC Help
View File | Revision Log | Show Annotations | Root Listing
root/msatfinder/msatviewer.cgi
Revision: 1.1.1.1 (vendor branch)
Committed: Mon Mar 7 15:34:46 2005 UTC (11 years, 1 month ago) by knirirr
Branch: MAIN
CVS Tags: HEAD, HEAD
Changes since 1.1: +0 -0 lines
Log Message:
First import

Line File contents
1 #!/usr/bin/perl
2
3 # Summarize files created with MsatMiner
4 # print contents of any SQL query
5 # into nice HTML table format where each
6 # column can be sorted
7
8 # Cared for by Dawn Field (dfield@ceh.ac.uk)
9
10 use strict;
11 use warnings;
12 use DBI;
13 use CGI;
14 use CGI::Carp qw(fatalsToBrowser set_message);
15 use Config::Simple;
16 use Cwd;
17 use File::Basename;
18 use Mail::Send;
19 use Msatminer;
20
21
22 # get data needed to check for config file
23 my $cwd = getcwd;
24 my $install = $ENV{MSATMINER_HOME};
25
26 ####################################
27 # import settings from config file #
28 ####################################
29 my $config = &Msatminer::getconfig($install,$cwd);
30
31 ### set some variables ###
32 my $title = $config->{'VIEWER.title'};
33 my $stylesheet = $config->{'VIEWER.stylesheet'};
34 my $filepath = $config->{'COMMON.url'};
35 my $baseURL = $config->{'VIEWER.baseURL'};
36 my $dbname = $config->{'VIEWER.dbname'};
37 my $dbtype = $config->{'VIEWER.dbtype'};
38 my $dbusername = $config->{'VIEWER.sdbusername'};
39 my $dbpassword = $config->{'VIEWER.sdbpassword'};
40 my $mailto = $config->{'VIEWER.mailto'};
41 my $msatview = $config->{'VIEWER.msatview'};
42 my $repeat_dir = $config->{'COMMON.repeat_dir'};
43 $repeat_dir =~ s/\/$//;
44 my $repeats = lc $repeat_dir;
45 my $annodir = $config->{'COMMON.anno_dir'};
46 $annodir =~ s/\/$//;
47 # a handy warning message
48 set_message("Please mail the error shown above to <a href=\"mailto:$mailto\" subject=\"I'm really sorry to bother you, but it looks like your CGI is broken.\">the site administrator</a>. Thanks.");
49
50 # source the file containing the sql queries
51 my $actionfile;
52 if (-R "$cwd/actions.txt")
53 {
54 $actionfile = "$cwd/actions.txt";
55 }
56 elsif (-R "$install/etc/actions.txt")
57 {
58 $actionfile = "$install/etc/actions.txt";
59 }
60 my %catseen=();
61 my @categories=();
62 open (ACT, "<$actionfile") or die "Can't open $actionfile: $!";
63 my @sqllines = <ACT>;
64 foreach (@sqllines)
65 {
66 next if ($_ =~ /^#/);
67 my $cat = [split(/\|/, $_)]->[0];
68 unless (defined $catseen{$cat})
69 {
70 push (@categories, $cat);
71 }
72 $catseen{$cat} = 1;
73 }
74 close ACT;
75
76
77 # Instantiate CGI, and print header
78 my $cgi = new CGI();
79
80 my $sql = "";
81
82 my $debug = "0";
83 my $bgcolor = "gray";
84 my ($top, $bottom) = "";
85
86 # check what the action button is
87 my $action = $cgi->param('action');
88
89 # make a connection
90 my $dbh;
91 if ($dbtype eq "mysql")
92 {
93 $dbh = DBI->connect("dbi:mysql:$dbname",
94 "$dbusername",
95 "$dbpassword") or die "Can't connect: $!";
96 }
97 elsif ($dbtype eq "Pg")
98 {
99 $dbh = DBI->connect("dbi:Pg:host=localhost dbname=$dbname", $dbusername, $dbpassword, {RaiseError=>1, AutoCommit=>1, Taint=>1}) or die "Can't connect: $!";
100 }
101 else
102 {
103 die "Can't connect - no db type specified: $!";
104 }
105
106 # download file from last query
107 if (defined $action and $action =~ /download/)
108 {
109 # run the query again
110 my $sql = $cgi->param('sqlstring');
111 my $sort = $cgi->param('sortstring');
112 if ($sort) {$sort = " order by $sort";}
113 $sql .= $sort;
114
115 my $sth = $dbh->prepare($sql);
116 $sth->execute();
117
118 # set appropriate newline characters
119 my ($newline,$sep);
120 for ($cgi->user_agent())
121 {
122 /windows/i and do { $newline = "\cM\cJ"; last; };
123 /mac/i and do { $newline = "\cM"; last; };
124 $newline = "\cJ";
125 }
126
127 # print headers to force download
128 print $cgi->header(-TYPE => 'text/plain',
129 -EXPIRES => 'now',
130 -ATTACHMENT => 'mgmd_download.csv');
131
132 # print query to temp file
133 foreach (@{$sth->{NAME}}) { print "$_\cI"; }
134 print "$newline";
135 while (my @row = $sth->fetchrow_array())
136 {
137 foreach (@row) { print "$_\cI"; }
138 print "$newline";
139 }
140 exit 0;
141 }
142
143 print $cgi->header("text/html");
144
145 print $cgi->start_html(-title=>"$title",
146 -style=>{-src=>"$filepath/$stylesheet"}
147 );
148
149 # headers!
150 &msat_header();
151
152 print <<MENU;
153 <P>
154
155 <table width="100%" border="0" class="analysisfilelinks" bgcolor="beige">
156 <tr>
157 <td>
158 MENU
159
160 my %title=();
161 my %command=();
162 my %category=();
163 my @actions=();
164
165 foreach my $category (@categories)
166 {
167 print "$category:\n ";
168 foreach my $line (@sqllines)
169 {
170 my ($cat,$act,$title,$com) = split(/\|/, $line);
171 $title{$act} = $title;
172 $command{$act} = $com;
173 if ($category eq $cat)
174 {
175 print "<a href=\"msatviewer.cgi?action=$act\">$title</a>&nbsp;&nbsp;";
176 }
177 }
178 print "<br>\n";
179 }
180
181 print <<MENU;
182 </tr>
183 </td>
184 </table>
185 <p>
186 These data were generated using <a href="http://www.genomics.ceh.ac.uk/msatminer/">msatfinder</a>. For more details of the column headings used, click <a href="http://www.genomics.ceh.ac.uk/msatminer/key.html">here.</a>
187 <p>
188 <font size="0">Contact:</font> <a href="mailto:$mailto">$mailto</a>
189
190 <hr>
191 MENU
192
193
194 #########################################
195 # START FORM - get custom SQL statement #
196 #########################################
197 # write a form to get a user input
198 print $cgi->startform();
199
200 print "Enter a custom SQL statement: ";
201 print $cgi->textfield(-name => 'sql',
202 -size => 100,
203 -maxlength => 500
204 );
205
206 # print a 'submit' button at end of form: script self-calls
207 print $cgi->submit( 'action', 'submit' );
208
209 # print a 'clear' button at end of form: script self-calls
210 print $cgi->defaults('Clear');
211
212 # print an e-mail button to send query to the administrator
213 print $cgi->submit( 'action', 'email' );
214
215 print $cgi->endform();
216
217 print "<font size=\"-2\">Clicking &ldquo;email&rdquo; will both run your query and send the SQL string to the site administrator, who will consider it for inclusion as a set option.<br><br>";
218
219
220 ############
221 # END FORM #
222 ############
223
224 if (defined $action)
225 {
226 if ($action eq '')
227 {
228 &menu;
229 }
230 elsif ($action eq 'submit')
231 {
232 $sql = $cgi->param('sql');
233
234 }
235 elsif ($action eq 'email')
236 {
237 $sql = $cgi->param('sql');
238 my $ip = $cgi->remote_addr();
239 my $body = <<EOF;
240 Sender: $ip
241 Query: $sql
242 EOF
243 my $msg = new Mail::Send;
244 $msg->to("$mailto");
245 $msg->subject('suggested SQL database query');
246 my $fh = $msg->open;
247 print $fh "$body";
248 $fh->close;
249 }
250 elsif ($action eq "X")
251 {
252 $sql = "";
253 print "<b>UNDER CONSTRUCTION: Sorry, not implemented yet.</b><P>";
254
255 }
256 elsif ($action eq "filenames") {&filenames();}
257 elsif ($action eq "showfiles") {&showfiles();}
258 elsif ($action eq "matrix") {&matrix();}
259 elsif ($action eq "annotation") {&annotation();}
260 elsif ($action eq "related") {&related();}
261 else
262 {
263 $sql = $command{$action};
264 }
265 }
266
267 # run the sort, if defined above
268 # append details for sorting if selected
269 my $sort = $cgi->param('sort');
270 if ($sort)
271 {
272 $sort = " order by $sort";
273 my $oldsql = $sql;
274 $sql .= $sort;
275 }
276
277
278 # take one last look at final sql statement
279 if ($debug) {print "<hr><P>SQL Statement used: $sql<hr><P>";}
280
281 # only execute if a query if called, otherwise, just show
282 # text
283 if ($sql)
284 {
285 my $tbl_str = &make_table_from_query($dbh,$sql);
286 print $tbl_str;
287 }
288
289
290 # Print end html, disconnect from the database, and exit
291 print $cgi->end_html;
292 $dbh->disconnect;
293 exit;
294
295
296
297 # ==================Functions====================== #
298
299 sub make_table_from_query
300 {
301 my ($dbh, $query) = @_;
302 my $sth = $dbh->prepare($query);
303 unless ($sth->execute())
304 {
305 print <<POX;
306 Sorry, it's not possible to perform your query: $query.<br> <font size="+1"><a href=\"msatviewer.cgi?action=$action&sort=$_\">BACK</a>.<br></font>
307 If you require these data to be sorted, we recommend downloading as a tab-delimited text file, importing into a spreadsheet application and performing your sort there. Apologies for the inconvenience.<br>
308 POX
309 exit 0;
310 }
311
312 # use column names for cells in the header row
313 my @rows = ();
314 my @colheaders = ("row_number", @{$sth->{NAME}});
315 push (@rows, $cgi->Tr ({-class=>"analysisfilelinks", -bgcolor=>"beige"}, $cgi->th ([ map { "<a href=\"msatviewer.cgi?action=$action&sort=$_\"><nobr>$_</nobr></a>"} @colheaders ])));
316
317 # fetch each data row
318 my $rowcounter = 0;
319 while (my $row_ref = $sth->fetchrow_arrayref())
320 {
321 $rowcounter++;
322
323 # encode cell values, avoid warnings for undefined values
324 # place
325 my @val = map
326 {
327 # use this to escape HTML in fields
328 # defined($_) && $_ !~ /^s*$/ ? $cgi->escapeHTML ($_) : "&nbsp;"
329
330 defined($_) && $_ !~ /^s*$/ ? ($_) : "&nbsp;"
331
332 } @{$row_ref};
333
334 my $row_str;
335 my $i = "";
336 for ($i = 0; $i < @val; $i++)
337 {
338
339 # add no break
340 $val[$i] = "<nobr>$val[$i]</nobr>";
341
342 # right-justify numeric columns
343 if ($sth->{mysql_is_num}->[$i])
344 {
345 $row_str .= $cgi->td ({align => "right", -class=>"analysisfilelinks"}, ($val[$i]));
346 }
347 else
348 {
349 $row_str .= $cgi->td({-class=>"analysisfilelinks"}, $val[$i]);
350 }
351 }
352 # modify cell formats here
353 $bgcolor = ($bgcolor eq "silver" ? "gray" : "silver");
354 $row_str = "<td>$rowcounter</td>" . $row_str;
355 push (@rows, $cgi->Tr ({ - bgcolor=> $bgcolor}, $row_str));
356
357
358 }
359 if ($sql)
360 {
361 print "Number of rows returned: $rowcounter\n<br>";
362
363 # add the "download" button. Print raw data to screen if clicked.
364 print $cgi->startform(-method=>"GET");
365 print "Click here to download the data in tab delimited format: ";
366 print $cgi->hidden(-name=>"sqlstring",-value=>"$sql",-override=>1);
367 print $cgi->hidden(-name=>"sortstring",-value=>"$sort",-override=>1);
368 print $cgi->submit(-name=>"action",-value=>"download",-override=>1);
369 print $cgi->endform();
370 print "<br>";
371 print "<b><font size=\"-1\" color=\"black\">[Click on any column header to sort all values]<br></b>";
372 }
373
374 # print the actual table
375 return ($cgi->table ({-border => "1", -width => "100%"}, @rows));
376
377 }
378
379 sub menu
380 {
381 &table_top();
382
383 print <<HEADER;
384 <p><p><p><p>
385 <font size="+3" color="green" face="Arial, Helvetica, sans-serif">
386 <b><center>Views of the Data</center></b></font>
387 HEADER
388
389 print <<TABLE;
390 <table width="450" border="2" class="analysisfilelinks" cellspacing="2" cellpadding="2" align="center" class="analysislinks">
391 <tr>
392 <td>View custom views of the database</td>
393 <td>
394 <div align="center">See Links Above</div>
395 </td>
396 </tr>
397 <tr>
398 <td>View summaries of the genomes searched (see &quot;index.html&quot; for
399 overall repeats found&quot;)</td>
400 <td>
401 <div align="center"><a href="msatviewer.cgi?action=repeats">Summary</a></div>
402 </td>
403 </tr>
404 <tr>
405 <td>View all repeats found as fasta files</td>
406 <td>
407 <div align="center"><a href="msatviewer.cgi?action=fasta">Fasta</a></div>
408 </td>
409 </tr>
410 <tr>
411 <td>View primers for each repeats selected with eprimer3</td>
412 <td>
413 <div align="center"><a href="msatviewer.cgi?action=primers">Primers</a></div>
414 </td>
415 </tr>
416 <tr>
417 <td>View repeats from all genomes as files ready for loading into Artemis</td>
418 <td>
419 <div align="center"><a href="msatviewer.cgi?action=artemis">Artemis</a></div>
420 </td>
421 </tr>
422 </table>
423 TABLE
424
425 &table_bottom();
426
427 }
428
429 sub annotation
430 {
431 &table_top();
432 if (-d "$msatview$annodir")
433 {
434 opendir ANNODIR, "$msatview$annodir" or warn "Can't read $annodir: $!";
435 my @dirs = readdir ANNODIR;
436 closedir ANNODIR;
437 unless (@dirs > 3)
438 {
439 print "<b>No annotation files are available.</b>";
440 return;
441 }
442 foreach my $subdir (@dirs)
443 {
444 next if ($subdir =~ /^\./);
445 if ($subdir =~ /^annotated/)
446 {
447 print "<a href=\"$filepath$annodir/$subdir\">$subdir</a><br>";
448 }
449 else
450 {
451 opendir SUBDIR, "$msatview$annodir/$subdir" or warn "Can't read $subdir: $!";
452 my @sfiles = readdir SUBDIR;
453 closedir SUBDIR;
454 foreach my $sfile (@sfiles)
455 {
456 next if ($sfile =~ /^\./);
457 print "<a href=\"$filepath$annodir/$subdir/$sfile\">$subdir: $sfile</a><br>";
458 }
459 }
460 }
461 }
462 else
463 {
464 print "<b>No annotation files are available.</b>";
465 }
466 &table_bottom();
467 }
468
469 #######################################
470 # print out contents of various files #
471 #######################################
472 sub filenames
473 {
474 &table_top();
475 my $filetype = $cgi->param('filetype');
476 if ($filetype eq $repeats) { $filetype = "repeatfiles"; }
477 my $sql = "select filename from $filetype";
478 my $sth = $dbh->prepare($sql);
479 $sth->execute();
480 while (my $row = $sth->fetchrow())
481 {
482 print "<a href=\"${baseURL}msatviewer.cgi?action=showfiles&filetype=$filetype&filename=$row\">$row</a><br>";
483 }
484 &table_bottom();
485 }
486
487 sub showfiles
488 {
489 &table_top();
490 my $filename = $cgi->param('filename');
491 my $filetype = $cgi->param('filetype');
492 if ($filetype eq $repeats) { $filetype = "repeatfiles"; }
493 my $sql = "select data from $filetype where filename = " . $dbh->quote($filename);
494 my $sth = $dbh->prepare($sql);
495 $sth->execute();
496 print "<pre>\n";
497 while (my $row = $sth->fetchrow())
498 {
499 print "$row";
500 }
501 print "</pre>\n";
502 &table_bottom();
503 }
504
505 sub related
506 {
507 &table_top();
508 my $related;
509 if (-R "$cwd/related_links.txt")
510 {
511 $related = "$cwd/related_links.txt";
512 }
513 elsif (-R "$install/etc/related_links.txt")
514 {
515 $related = "$install/etcinstall/etc/related_links.txt";
516 }
517 open (IN, "$related") or die "can't open file related_links.txt";
518 print "<P>Related Genomic Links<P>";
519 while (my $line=<IN>)
520 {
521 my ($name, $URL, $desc) = split (/\t/, $line);
522 print "<a href=\"$URL\">$name</a>: $desc<br>";
523 }
524 &table_bottom;
525 }
526
527
528 sub matrix
529 {
530 &table_top();
531 print "<a href=\"$filepath/repeat_matrix.html\">Msataligner output</a><br>";
532 &table_bottom;
533 }
534
535 sub table_top
536 {
537
538 print <<TEXT;
539 <table width="100%" border="0" class="analysisfilelinks" bgcolor="beige">
540 <tr>
541 <td>
542 TEXT
543
544 }
545
546 sub table_bottom
547 {
548
549 print <<TEXT;
550 </td>
551 </tr>
552 </table>
553 TEXT
554
555 }
556
557 sub msat_header {
558
559 print <<EOF;
560
561 <font size="+7" color="#000099" face="Arial, Helvetica, sans-serif">
562 <b>$title</b></font>
563 <hr>
564
565 <a href="${baseURL}msatviewer.cgi">
566 HOME</a> |
567
568 <font class="analysisfilelinks">Msatfinder:</font>
569
570 <a href="${baseURL}msatviewer.cgi?action=filenames&filetype=fasta">
571 Fasta Files</a>,
572
573 <a href="${baseURL}msatviewer.cgi?action=filenames&filetype=artemis">
574 Artemis Files</a>,
575
576 <a href="${baseURL}msatviewer.cgi?action=filenames&filetype=primers">
577 Primer Files</a>,
578
579 <a href="${baseURL}msatviewer.cgi?action=filenames&filetype=repeatfiles">
580 Summary</a>,
581
582 <a href="${baseURL}msatviewer.cgi?action=filenames&filetype=mine">
583 MINE</a> |
584
585 <a href="${baseURL}msatviewer.cgi?action=matrix">
586 MsatAligner Output</a> |
587
588 <a href="${baseURL}msatviewer.cgi?action=annotation">
589 MsatAnnotator Output</a> |
590
591 <a href="${baseURL}msatviewer.cgi?action=related">
592 related links</a> |
593
594 <a href="http://www.genomics.ceh.ac.uk/msatminer/">MsatMiner Software</a>
595
596 EOF
597 }