--- sql2xls.cgi	2008/03/24 15:57:14	2
+++ sql2xls.cgi	2008/11/03 18:44:42	6
@@ -1,21 +1,49 @@
-#!/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
-
+#!/usr/bin/perl
+use warnings;
 use strict;
+
+=head1 NAME
+
+sql2xls.pl - convert sql queries on file system to Excel file
+
+=head1 USAGE
+
+Each file in current directory which ends in C<< *.sql >> will
+be converted to Excel sheet. If you want to have specific order, you can
+prefix filenames with numbers which will be striped when creating sheet
+names.
+
+Comments in sql files (lines beginning with --) will be placed
+in first line in bold.
+
+To specify database on which SQL query is executed
+C<< \c database >> syntax is supported.
+
+You can also run script from command line, and it will produce
+C<< sql_reports.xls >> file.
+
+=head1 AUTHOR
+
+Dobrica Pavlinusic, dpavlin@rot13.org
+
+=cut
+
 use Spreadsheet::WriteExcel;
 use DBI;
 use CGI::Carp qw(fatalsToBrowser);
 use CGI qw(path_translated);
 use Encode qw/decode/;
+use Data::Dump qw/dump/;
+
+# edit following to set defaults
+my $dsn      = 'DBI:Pg:dbname=';
+my $database = 'template1';
+my $user     = 'dpavlin';
+my $passwd   = '';
+my $path     = 'sql_reports.xls';
 
-# Connect to DB
-my $connect = "DBI:Pg:dbname=new";
-my $user = "web";
-my $passwd = "";
+my $db_encoding     = 'iso-8859-2';
+my $xls_date_format = 'dd.mm.yyyy';
 
 my $debug = 1;
 
@@ -27,19 +55,22 @@
 closedir DIR;
 
 my $workbook;
-if ($0 =~ m/\.cgi$/i) {
+if ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ 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");
+	$workbook = Spreadsheet::WriteExcel->new( $path );
+	warn "Creating XLS file $path\n";
 }
 
-my $dbh = DBI->connect($connect,$user,$passwd) || die $DBI::errstr;
+my $date_format = $workbook->add_format(num_format => $xls_date_format);
+
+my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
 
 sub _c {
-	return decode('iso-8859-2', shift);
+	return decode( $db_encoding, shift );
 }
 
 foreach my $sql_file (@sql_files) {
@@ -52,21 +83,27 @@
 	# Add a worksheet
 	my $worksheet = $workbook->addworksheet($sheet_name);
 
-	print STDERR "working on $sql_file...\n" if ($debug);
+	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 (/^--(.+)/) {
+		if (/^\\c\s+(\S+)/) {
+			$dbh->disconnect if $dbh;
+			print STDERR "## connect to $1\n" if $debug;
+			$dbh = DBI->connect($dsn . $1,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
+		} elsif (/^--(.+)/) {
 			$comment.=$1;
 		} else {
-			$sql.=$_;
+			$sql.= ' ' . $_;
 		}
 	}
 	close(SQL);
 
+	$sql =~ s/\s\s+/ /gs;
+
 	print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
 
 	my $row = 0;
@@ -81,8 +118,8 @@
 		$row+=2;
 	}
 
-	my $sth = $dbh->prepare($sql) || die $dbh->errstr();
-	$sth->execute() || die $sth->errstr();
+	my $sth = $dbh->prepare($sql);
+	$sth->execute();
 
 	my $fmt_header = $workbook->addformat();    # Add a format
 	$fmt_header->set_italic();
@@ -92,9 +129,19 @@
 	}
 	$row++;
 
+	my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
+
 	while (my @row = $sth->fetchrow_array() ) {
 		for(my $col=0; $col<=$#row; $col++) {
-			$worksheet->write($row, $col, _c( $row[$col] ) );
+			my $data = $row[$col];
+			if ( $types[$col] =~ m/^date/i ) {
+				$data .= 'T' if $data =~ m/^\d\d\d\d-\d\d-\d\d$/;
+				$data =~ s/^(\d\d\d\d-\d\d-\d\d)\s(\d\d:\S+)$/$1T$2/;
+				warn "## $data\n";
+				$worksheet->write_date_time( $row, $col, $data, $date_format );
+			} else {
+				$worksheet->write($row, $col, _c( $data ) );
+			}
 		}
 		$row++;
 	}
@@ -107,24 +154,3 @@
 
 __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
-