#!/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