begin work on web reports which return SQL queries

as XLS table (readable in Gnumeric also :-)

git-svn-id: svn://svn.rot13.org/SQL2XLS@1 2e857b76-582b-47e5-ad5c-b3ba0f0ee29b

diff --git a/sql2xls.cgi b/sql2xls.cgi
new file mode 100755 (executable)
index 0000000..1627e09
--- /dev/null
+++ b/sql2xls.cgi
@@ -0,0 +1,125 @@
+#!/usr/bin/perl -w
+#
+# convert sql queries on file system to excel file (one query per
+# sheet)
+#
+# Dobrica Pavlinsic <dpavlin@rot13.org>, 2002-04-10
+
+use strict;
+use Spreadsheet::WriteExcel;
+use DBI;
+use CGI::Carp qw(fatalsToBrowser);
+use CGI qw(path_translated);
+
+my $debug = 1;
+
+my $sql_dir = path_translated || '.';
+$sql_dir =~ s,/[^/]+$,,;
+
+opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
+my @sql_files = sort grep { /\.sql$/i && -f "$sql_dir/$_" } readdir(DIR);
+closedir DIR;
+
+my $workbook;
+if ($0 =~ m/\.cgi$/i) {
+       # use as cgi script
+       print "Content-type: application/vnd.ms-excel\n\n";
+       $workbook = Spreadsheet::WriteExcel->new("-");
+} else {
+       # Create a new Excel workbook
+       $workbook = Spreadsheet::WriteExcel->new("sql_result.xls");
+}
+
+# Connect to DB
+my $connect = "DBI:Pg:dbname=new";
+my $user = "web";
+my $passwd = "";
+
+my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr;
+
+foreach my $sql_file (@sql_files) {
+
+       my $sheet_name = $sql_file;
+       $sheet_name =~ s/\d+_//;
+       $sheet_name =~ s/_/ /g;
+       $sheet_name =~ s/\.sql//;
+
+       # Add a worksheet
+       my $worksheet = $workbook->addworksheet($sheet_name);
+
+       print STDERR "working on $sql_file...\n" if ($debug);
+
+       open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
+       my $comment;
+       my $sql = "";
+       while(<SQL>) {
+               chomp;
+               if (/^--(.+)/) {
+                       $comment.=$1;
+               } else {
+                       $sql.=$_;
+               }
+       }
+       close(SQL);
+
+       print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
+
+       my $row = 0;
+
+       if ($comment) {
+
+               #  Add and define a format
+               my $fmt_comment = $workbook->addformat();    # Add a format
+               $fmt_comment->set_bold();
+
+               $worksheet->write($row, 0, $comment, $fmt_comment);
+               $row+=2;
+       }
+
+       my $sth = $dbh->prepare($sql) || die $dbh->errstr();
+       $sth->execute() || die $sth->errstr();
+
+       my $fmt_header = $workbook->addformat();    # Add a format
+       $fmt_header->set_italic();
+
+       for(my $col=0; $col<=$#{ $sth->{NAME} }; $col++) {
+               $worksheet->write($row, $col, ${ $sth->{NAME} }[$col], $fmt_header);
+       }
+       $row++;
+
+       while (my @row = $sth->fetchrow_array() ) {
+               for(my $col=0; $col<=$#row; $col++) {
+                       $worksheet->write($row, $col, $row[$col] );
+               }
+               $row++;
+       }
+
+}
+
+$dbh->disconnect;
+
+1;
+
+__END__
+
+=head1 NAME
+
+sql2xls.pl - convert sql queries on file system to excel file
+
+=head1 USAGE
+
+Edit top of script and edit @sql_files array which describes
+files which are going to be loaded and executed producing
+Excel sheets (one sheet per file)
+
+Comments in sql files (lines beginning with --) will be placed
+in outout sheet on top in bold.
+
+Sheet will have name same as sql file.
+
+Run script and examine b<sql_result.xls> file.
+
+=head1 AUTHOR
+
+Dobrica Pavlinusic, dpavlin@rot13.org
+