From 8dc82a4afe1ee455b9bd9c05ef3b1de244ce8d9d Mon Sep 17 00:00:00 2001
From: Dobrica Pavlinusic <dpavlin@rot13.org>
Date: Mon, 3 Nov 2008 20:32:44 +0000
Subject: [PATCH] implement sub-reports which are simply directories and SQL
 queries, each generating single XLS file

git-svn-id: svn://svn.rot13.org/SQL2XLS@13 2e857b76-582b-47e5-ad5c-b3ba0f0ee29b
---
 sql2xls.cgi | 28 ++++++++++++++++++++--------
 1 file changed, 20 insertions(+), 8 deletions(-)

diff --git a/sql2xls.cgi b/sql2xls.cgi
index ce80c0c..cd5f2e8 100755
--- a/sql2xls.cgi
+++ b/sql2xls.cgi
@@ -1,4 +1,4 @@
-#!/usr/bin/perl
+#!/usr/bin/perl -T
 use warnings;
 use strict;
 
@@ -22,6 +22,12 @@ C<< \c database >> syntax is supported.
 You can also run script from command line, and it will produce
 C<< sql_reports.xls >> file.
 
+If run within directory, it will use files in it to produce file.
+
+When called as CGI, directory name can be appended to name of script
+to produce report for any sub-directory within directory where
+C<sql2xls.cgi> is installed.
+
 =head1 INSTALLATION
 
 Only required file is this script C<< sql2xls.cgi >>
@@ -42,7 +48,6 @@ Dobrica Pavlinusic, dpavlin@rot13.org
 use Spreadsheet::WriteExcel;
 use DBI;
 use CGI::Carp qw(fatalsToBrowser);
-use CGI qw(path_translated);
 use Encode qw/decode/;
 use Data::Dump qw/dump/;
 
@@ -58,17 +63,24 @@ our $xls_date_format = 'dd.mm.yyyy';
 
 our $debug = 1;
 
-my $sql_dir = path_translated || '.';
+my $sql_dir = $ENV{SCRIPT_FILENAME} || '.';
 $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 $config_path = "$sql_dir/config.pl";
 warn "# using $config_path\n";
 require $config_path if -e $config_path;
 
+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";
+
+warn "# reading SQL queries from $sql_dir\n" if $debug;
+
+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 ($ENV{GATEWAY_INTERFACE} && $ENV{GATEWAY_INTERFACE} =~ m/CGI/i) {
 	# use as cgi script
@@ -101,7 +113,7 @@ foreach my $sql_file (@sql_files) {
 
 	print STDERR "working on $sql_file\n" if ($debug);
 
-	open(SQL,$sql_file) || die "can't open sql file '$sql_file': $!";
+	open(SQL,"$sql_dir/$sql_file") || die "can't open sql file '$sql_dir/$sql_file': $!";
 	my $comment = '';
 	my $sql = "";
 	while(<SQL>) {
-- 
2.20.1