#!/usr/bin/perl -w
# Script: 	xtab.pl
# Author: 	Giuseppe Maxia, May 2001
# purpose:	creating a simple cross tabulation with one level of rows and
#		columns, and grand total
#		
# requires:	Perl DBI (tested on 1.14), DBD:mysql
#		MySQL 3.23 (tested on 3.23.30 to 3.23.36)
#		
# input:	a configuration file (from STDIN) containing pre-defined key=value pairs.
#		(See %params below for description)
#		row_where, col_where, col_order, row_order can have blank values, if not
#		necessary, but can't be omitted
#		
# output:	to STDOUT, a MySQL script, ready to run.
#	
# usage:	xtab.pl < input_file [ | mysql -t [mysql parameters] ]
#
# Warning:	Very minimum error checking. Written for the purpose of
#		demonstrating a server-side cross-tabulation algorithm.
#		In real applications, you should enforce strict parameters checking
#		for database applicability.

use DBI;
use strict;

# unlike the wizard's script in the article, where %params was created blindly,
# here we have a minimum of error checking, to insure that at least the
# fields we are reading are correctly named.
# A check for empty values is performed. It doesn't guarantee that the values entered will
# make a correct SQL statement. Error checking on this respect is performed by MySQL


my %params = (
	title       => "", # title of the xtab (will be shown on STDERR) (*can be empty*)
	database    => "", # database containing the tables to query
	row_name    => "", # field used to name the rows
	row_alias   => "", # alias for the above field
	col_name    => "", # field containing the unique values for columns
	col_alias   => "", # alias for the above
	col_value   => "", # 1 for counting, a numeric field for summing
	col_from    => "", # FROM clause for the column values
	col_where   => "", # WHERE clause for column values (*can be empty*)
	col_order   => "", # ORDER BY clause for column values (*can be empty*)
	row_from    => "", # FROM clause for rows (final query)
	row_where   => "", # WHERE clause for rows (final query) (*can be empty*)
	row_order   => "", # ORDER BY clause for rows (final query) (*can be empty*)
	row_group   => "", # GROUP BY clause for rows (final query)
);

my $keywords = "^" . join ("|", keys %params)  ; 

while (<>) { 					# gets the configuration from the input pipe
	next if /^#/; 				# skips comments (rows starting with '#'
	my ($name,$value) = split '=',$_;	# separate the identifier from the value
	$name =~ s/\s//g;  			# remove spaces from the identifier
	$name =~ m/$keywords/ 			# accepts only existing keys
		or die "unrecognized keyword <<$name>>\n"; 
	chomp $value; 				# remove trailing newlines

	# accepts empty values only for title and WHERE or ORDER BY clauses 
	$value =~ m/\S/ or (!($value =~ m/\S/) and m/title|order|where/) 
		or die "no valid input given. Missing $name\n";
	$params{$name} =  $value ;
}

print STDERR " $params{title} \n";

my $dbh = DBI->connect("DBI:mysql:$params{database};"  # database where we perform the xtab
	."host=localhost;" # replace with host name (or IP) according to your needs
	."mysql_read_default_file=$ENV{HOME}/.my.cnf") # username and password from ~/.my.cnf file 
	or die "can't connect $! \n";

my $row_statement = "";
# ------------- get the columns
my $sth = $dbh->prepare("SELECT DISTINCT $params{col_name}, $params{col_alias} "
		. $params{col_from} . " " . $params{col_where} . $params{col_order});
$sth->execute();
# compose the columns part of the SQL statement
while (my ($colname, $colalias) = $sth->fetchrow_array()) {
	$row_statement .=  ", SUM(IF($params{col_name} = \"${colname}\","
	. " $params{col_value}, 0)) AS `${colalias}` \n"
}
$sth->finish();
$dbh->disconnect();

# ------------ add total column and row details
$row_statement .=  ", " . (($params{col_value} eq "1")? "COUNT(*)" : "SUM($params{col_value})") . " AS total \n" 
	       . $params{row_from} 
	       . $params{row_where} ;

print "use $params{database};\n";	       
$row_statement =~ s/\n\n/\n/g; # remove double EOL (from empty WHERE and ORDER BY clauses)
print   "SELECT $params{row_name} AS $params{row_alias} \n",
	"$row_statement\n$params{row_group} \n $params{row_order} ;\n"; # the cross table

print "SELECT 'TOTAL'\n$row_statement;\n"; # the total line