use strict;
use DBI;

my $dbh; #SET THIS TO A VALID DBI CONNECTION HANDLE.

my @rowheads = qw/fiscal_year sale_in_territory/;
my @colheads = qw/class product_id/;
my @datacols = qw/units_sold/;
my $cubetable = 'transactions';

my @pivots = @rowheads;
push @pivots, @colheads;

my (%values,%names,%ordinals,%filtered,%totals,%columnheaders);

my $sth = $dbh->prepare('select 
'.join(',',@rowheads).','.join(',',@colheads).','.join(',',@datacols).'
from '.$cubetable.' ORDER BY '.join(',',@pivots)) || die
$dbh->errstr;
$sth->execute || die $dbh->errstr;

my @row;
while (@row = $sth->fetchrow_array) {
	#put the data values in a safe place
	my @data;
	for (my $i = 0; $i < @datacols; $i++) {
		my $d = pop @row;
		push @data,$d;
	}
	my $continue = 0;
	foreach (@data) {
		$continue = 1 if defined $_;
	}
	next unless $continue;
	my @key = @{GetKey(@row)};
	my $key = join(' ',@key);
	if (defined $values{$key}) {
		my $count = -1;
		foreach (@{$values{$key}}) {
			$_ = $_ + $data[$count++];
		}
	} else {
		$values{$key} = \@data;	
	}
	#now set up the column header hash
	splice(@key,0,(@rowheads));
	$key = join(' ',@key);
	if (defined $columnheaders{$key}) {
		my $count = -1;
		foreach(@{$columnheaders{$key}}) {
			$_ = $_ + $data[$count++];
		} 
	} else {
		$columnheaders{$key} = \@data;
	}
	
}


#build the column headers in a 2D array


#my $colcount = 1;
#foreach (@colheads) {
#        $colcount = $colcount * (@{$names{$_}});
#}
#$colcount = $colcount * (@datacols);

my $stop;
my @colkey;
my $count = 0;
foreach (@colheads) {
	$colkey[$count] = 0;
	$stop = $stop.$#{$names{$colheads[$count]}}.' ';
	$count++;
}
chop $stop;
my @cols;
my $count = 0;
while ($stop ne join(' ',@colkey)) {
	CalcCol($#colheads);
	my $key = join(' ',@colkey);
#	next if !defined $columnheaders{$key};
	$cols[$count] = $key;
	$count++;
}


#print '<pre>',main::Dumper(@cols),'</pre>';

#display
print '<table border = 1>';
my $datacount;
#print the column headers
my $count = 0;
my @colkeys;
foreach my $h (@colheads) {
	$datacount = 0;
	print '<tr>';
	print '<td colspan="'.(@rowheads-1).'"></td>';
	print "<td>$h</td>";
	foreach my $key (@cols) {
		next if !defined $columnheaders{$key};
		$datacount++;
		my @tmp = split(' ', $key);
		foreach (@datacols) {
			print '<td>'.${$names{$h}}[$tmp[$count]].'</td>';
		}
	}
	print '</tr>';
	$count++;
}

#print the row heads & data col heads
print '<tr>';
print '<td>',join('</td><td>',@rowheads),'</td>';
for (my $i = 0; $i < $datacount;$i++) {
	foreach my $h (@datacols) {
		print "<td>$h</td>";
	}
}

print '</tr>';
#print the row headers and data;
my @rowheader;
my @rowkey;
my $rowcount;
my $count = 0;
my $stop = '';
foreach (@rowheads) {
	$rowkey[$count] = 0;
	$stop = $stop.$#{$names{$rowheads[$count]}}.' ';
	$count++;
	
}
chop $stop;
my $first = 1;
while (join(' ',@rowkey) ne $stop) {
	CalcRow($#rowheads); 
	my $rowOK = 0;
	my $count = 0;
	foreach my $h (@rowheads) {
		$rowheader[$count] = ${$names{$h}}[$rowkey[$count]]; 
		$count++;
	} 
	my $rowstr = '<tr><td>'.join('</td><td>',@rowheader).'</td>';
	foreach my $k (@cols) {
		next if !defined $columnheaders{$k};		
		my $data = $values{join(' ',@rowkey).' '.$k};
		my @data;
		if (defined($data)) {
			@data = @{$data};
			$rowOK = 1;
		} else {
			foreach (@datacols) {
				push @data, undef;
			}
		}
		$rowstr = $rowstr.'<td>'.join('</td><td>',@data).'</td>'; 

	}
	if ($rowOK) {
		print $rowstr,'</tr>';
	}
}

sub CalcRow {
	my $pos = shift;
	if ($pos == 0) {
		$rowkey[0]++ if $rowkey[0] <= $#{$names{$rowheads[0]}};
		return 0;
	} else {
		if ($rowkey[$pos] >= $#{$names{$rowheads[$pos]}}) {
			#PRINT TOTAL LINE HERE
			$rowkey[$pos] = 0;
			CalcRow($pos-1);
		} else {
			$rowkey[$pos]++;
			return 0;
		}
	}
	return 1;
}

sub CalcCol {
	my $pos = shift;
	if ($pos == 0) {
		$colkey[0]++ if $colkey[0] <= $#{$names{$colheads[0]}};
		return 0;
	} else {
		if ($colkey[$pos] >= $#{$names{$colheads[$pos]}}) {
			#PRINT TOTAL LINE HERE
			$colkey[$pos] = 0;
			CalcCol($pos-1);
		} else {
			$colkey[$pos]++;
			return 0;
		}
	}
	return 1;
}



print '</table>';


#print '<p>columnheaders:<br><pre>',main::Dumper(%columnheaders),'</pre><p>';
#print '<p>stop: ',main::Dumper($stop);
#print '<p>stop: ',main::Dumper(join(' ',@rowkey));
#print '<p>NAMES: ',main::Dumper(%names),'<p>';
#print 'VALUES: ',main::Dumper(%values),'<p>';
#print 'ORDINALS: ',main::Dumper(%ordinals),'<p>';



sub GetKey {
	my $count = 0;
	my @key;
	foreach my $val (@_) {

		if (defined $ordinals{$pivots[$count].$val}) {
			push @key, $ordinals{$pivots[$count].$val};
		} else {
			my $o = push @{$names{$pivots[$count]}},$val;
			$o--;
			$ordinals{$pivots[$count].$val} = $o;
			push @key, $o;
		}
		$count++;
	}
	return \@key;
}



1;