--- sql2xls.cgi	2008/11/03 20:32:44	13
+++ sql2xls.cgi	2008/11/03 23:19:40	21
@@ -8,19 +8,19 @@
 
 =head1 USAGE
 
-Each file in current directory which ends in C<< *.sql >> will
+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
+Comments in sql files (lines beginning with C<-->) will be placed
 in first line in bold.
 
 To specify database on which SQL query is executed
-C<< \c database >> syntax is supported.
+C<\c database> syntax is supported.
 
 You can also run script from command line, and it will produce
-C<< sql_reports.xls >> file.
+C<sql_reports.xls> file.
 
 If run within directory, it will use files in it to produce file.
 
@@ -39,9 +39,28 @@
 
 in Apache's virtual host configuration to get nice URLs
 
+To configure default database, user, password and other settings create
+C<config.pl> file in same directory in which C<sql2xls.cgi> is with something
+like this:
+
+  $dsn      = 'DBI:mysql:dbname=';
+  $database = 'database';
+  $user     = 'user';
+  $passwd   = 'password';
+  $path     = 'sql_reports.xls';
+
+  $db_encoding     = 'utf-8';
+  $xls_date_format = 'dd.mm.yyyy';
+
+  $debug = 1;
+
+=head1 SECURITY
+
+There is none. Use apache auth modules if you need it.
+
 =head1 AUTHOR
 
-Dobrica Pavlinusic, dpavlin@rot13.org
+Dobrica Pavlinusic, dpavlin@rot13.org, L<http://svn.rot13.org/index.cgi/SQL2XLS/>
 
 =cut
 
@@ -51,7 +70,6 @@
 use Encode qw/decode/;
 use Data::Dump qw/dump/;
 
-# edit following to set defaults
 our $dsn      = 'DBI:Pg:dbname=';
 our $database = 'template1';
 our $user     = 'dpavlin';
@@ -66,15 +84,21 @@
 my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
 $sql_dir =~ s,/[^/]+$,,;
 
-my $config_path = "$sql_dir/config.pl";
-warn "# using $config_path\n";
-require $config_path if -e $config_path;
+sub require_config {
+	my $config_path = $1 if "$sql_dir/config.pl" =~ m/^(.+)$/; # untaint
+	warn "# using $config_path\n";
+	require $config_path if -e $config_path;
+}
+
+require_config;
 
-my $reports_path = $ENV{PATH_INFO};
+my $reports_path = $ENV{PATH_INFO} || '';
 $reports_path =~ s/\.\.//g; # some protection against path exploits
 $reports_path ||= shift @ARGV; # for CLI invocation
 $sql_dir .= "/$reports_path" if -e "$sql_dir/$reports_path";
 
+require_config;
+
 warn "# reading SQL queries from $sql_dir\n" if $debug;
 
 opendir(DIR, $sql_dir) || die "can't opendir $sql_dir: $!";
@@ -97,13 +121,14 @@
 my $dbh = DBI->connect($dsn . $database,$user,$passwd, { RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;
 
 sub _c {
+	return shift unless $db_encoding;
 	return decode( $db_encoding, shift );
 }
 
 foreach my $sql_file (@sql_files) {
 
 	my $sheet_name = $sql_file;
-	$sheet_name =~ s/\d+_//;
+	$sheet_name =~ s/\d+[_-]//;
 	$sheet_name =~ s/_/ /g;
 	$sheet_name =~ s/\.sql//;
 
@@ -115,7 +140,7 @@
 
 	open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_dir/$sql_file': $!";
 	my $comment = '';
-	my $sql = "";
+	my $full_sql = "";
 	while(<SQL>) {
 		chomp;
 		if (/^\\c\s+(\S+)/) {
@@ -125,14 +150,15 @@
 		} elsif (/^--(.+)/) {
 			$comment.=$1;
 		} else {
-			$sql.= ' ' . $_;
+			$full_sql.= ' ' . $_;
 		}
 	}
 	close(SQL);
 
-	$sql =~ s/\s\s+/ /gs;
+	$full_sql =~ s/\s\s+/ /gs;
+	$full_sql .= ';' unless $full_sql =~ m/;\s*/s;
 
-	print STDERR "sql: $sql\ncomment: $comment\n" if ($debug);
+	print STDERR "sql: $full_sql\ncomment: $comment\n" if ($debug);
 
 	my $row = 0;
 
@@ -149,34 +175,48 @@
 		$row+=2;
 	}
 
-	my $sth = $dbh->prepare($sql);
-	$sth->execute();
-
 	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++;
+	foreach my $sql ( split(/;/, $full_sql ) ) {
 
-	my @types = map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
+		warn "SQL: $sql\n" if $debug;
 
-	while (my @row = $sth->fetchrow_array() ) {
-		for(my $col=0; $col<=$#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 ) );
-			}
+		my $sth = $dbh->prepare($sql);
+		$sth->execute();
+
+		next unless $sth->{NAME}; # $sth->rows doesn't work for insert into with MySQL
+
+		my @types = eval {
+			map { $dbh->type_info($_) ? $dbh->type_info($_)->{TYPE_NAME} : '?' } @{ $sth->{TYPE} };
+		};
+
+		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++) {
+				my $data = $row[$col];
+				next unless defined $data;
+				if ( $types[$col] && $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:\d\d:\d\d)$/$1T$2/;
+					warn "## by type datetime $data\n" if $debug;
+					$worksheet->write_date_time( $row, $col, $data, $date_format );
+				} elsif ( $data =~ s/^(\d\d\d\d-\d\d-\d\d)[\sT](\d\d:\d\d:\d\d)$/$1T$2/ ) {
+					warn "## heuristic date time: $1T$2\n" if $debug;
+					$worksheet->write_date_time( $row, $col, "$1T$2", $date_format );
+				} else {
+					$worksheet->write($row, $col, _c( $data ) );
+				}
+			}
+			$row++;
+		}
+
+		$row++; # separete queries by one row
+	}
 }
 
 $dbh->disconnect;