#!/usr/bin/perl -w
use strict;
my @values = (
[ qw( pers dev sales ) ], # dept
[ qw( consultant contractor employee ) ], # category
[ qw( m f ) ] # gender
);
my @condition_names = ('dept', 'category', 'gender');
my $operation = 'SUM';
my $field_to_operate = 'salary';
# permute function written by Randal L. Schwartz,
# aka merlyn
# http://www.perlmonks.org/index.pl?node_id=24270
sub permute {
my $last = pop @_;
unless (@_) {
return map [$_], @$last;
}
return map { my $left = $_; map [@$left, $_], @$last } permute(@_);
}
my @permuted = permute(@values);
#now @permuted = (
# ['pers', 'consultant', 'm'],
# ['pers', 'consultant', 'f'],
# ['pers', 'contractor', 'm'],
# ['pers', 'contractor', 'f'],
# ['pers', 'employee', 'm'],
# ['pers', 'employee', 'f'],
# ['dev', 'consultant', 'm'],
# ['dev', 'consultant', 'f'],
# ['dev', 'contractor', 'm'],
# ['dev', 'contractor', 'f'],
# ['dev', 'employee', 'm'],
# ['dev', 'employee', 'f'],
# ['sales', 'consultant', 'm'],
# ['sales', 'consultant', 'f'],
# ['sales', 'contractor', 'm'],
# ['sales', 'contractor', 'f'],
# ['sales', 'employee', 'm'],
# ['sales', 'employee', 'f'],
#);
my $query = qq{SELECT country, loc\n};
for my $line(@permuted) {
#my @flds = @condition_names;
my $ndx=0;
$query .=
","
. $operation
. "(CASE WHEN "
. join(" AND ", map { $condition_names[$ndx++] . " = '" . $_ . "'" } @$line)
. " THEN "
. $field_to_operate
. " ELSE NULL END ) AS "
. "'"
. join ("#", @$line) . "'\n"
}
$query .= ",$operation($field_to_operate) AS total\n"
. qq{FROM
person
INNER JOIN depts ON (person.dept_id=depts.dept_id)
INNER JOIN categories ON (person.cat_id=categories.cat_id)
INNER JOIN locs on(locs.loc_id=person.loc_id)
INNER JOIN countries ON(locs.country_id=countries.country_id)\n}
. "GROUP BY country, loc\n";
print $query;
####
my $depts = $dbh->selectcol_arrayref(qq{SELECT DISTINCT dept FROM depts})
or die "can't get dept values\n";
my $categories = $dbh->selectcol_arrayref(qq{SELECT DISTINCT category FROM categories})
or die "can't get category values\n";
my $genders = $dbh->selectcol_arrayref(qq{SELECT DISTINCT gender FROM person})
or die "can't get gender values\n";
my @values = ( $depts, $categories, $genders );
##
##
$query = qq{
SELECT country, loc
,SUM(CASE WHEN dept = 'pers' AND category = 'consultant' AND gender = 'm'
THEN salary ELSE NULL END ) AS 'pers#consultant#m'
,SUM(CASE WHEN dept = 'pers' AND category = 'consultant' AND gender = 'f'
THEN salary ELSE NULL END ) AS 'pers#consultant#f'
,SUM(CASE WHEN dept = 'pers' AND category = 'contractor' AND gender = 'm'
THEN salary ELSE NULL END ) AS 'pers#contractor#m'
,SUM(CASE WHEN dept = 'pers' AND category = 'contractor' AND gender = 'f'
THEN salary ELSE NULL END ) AS 'pers#contractor#f'
,SUM(CASE WHEN dept = 'pers' AND category = 'employee' AND gender = 'm'
THEN salary ELSE NULL END ) AS 'pers#employee#m'
,SUM(CASE WHEN dept = 'pers' AND category = 'employee' AND gender = 'f'
THEN salary ELSE NULL END ) AS 'pers#employee#f'
,SUM(CASE WHEN dept = 'dev' AND category = 'consultant' AND gender = 'm'
THEN salary ELSE NULL END ) AS 'dev#consultant#m'
,SUM(CASE WHEN dept = 'dev' AND category = 'consultant' AND gender = 'f'
THEN salary ELSE NULL END ) AS 'dev#consultant#f'
,SUM(CASE WHEN dept = 'dev' AND category = 'contractor' AND gender = 'm'
THEN salary ELSE NULL END ) AS 'dev#contractor#m'
,SUM(CASE WHEN dept = 'dev' AND category = 'contractor' AND gender = 'f'
THEN salary ELSE NULL END ) AS 'dev#contractor#f'
,SUM(CASE WHEN dept = 'dev' AND category = 'employee' AND gender = 'm'
THEN salary ELSE NULL END ) AS 'dev#employee#m'
,SUM(CASE WHEN dept = 'dev' AND category = 'employee' AND gender = 'f'
THEN salary ELSE NULL END ) AS 'dev#employee#f'
,SUM(CASE WHEN dept = 'sales' AND category = 'consultant' AND gender = 'm'
THEN salary ELSE NULL END ) AS 'sales#consultant#m'
,SUM(CASE WHEN dept = 'sales' AND category = 'consultant' AND gender = 'f'
THEN salary ELSE NULL END ) AS 'sales#consultant#f'
,SUM(CASE WHEN dept = 'sales' AND category = 'contractor' AND gender = 'm'
THEN salary ELSE NULL END ) AS 'sales#contractor#m'
,SUM(CASE WHEN dept = 'sales' AND category = 'contractor' AND gender = 'f'
THEN salary ELSE NULL END ) AS 'sales#contractor#f'
,SUM(CASE WHEN dept = 'sales' AND category = 'employee' AND gender = 'm'
THEN salary ELSE NULL END ) AS 'sales#employee#m'
,SUM(CASE WHEN dept = 'sales' AND category = 'employee' AND gender = 'f'
THEN salary ELSE NULL END ) AS 'sales#employee#f'
,SUM(salary) AS total
FROM
person
INNER JOIN depts ON (person.dept_id=depts.dept_id)
INNER JOIN categories ON (person.cat_id=categories.cat_id)
INNER JOIN locs on(locs.loc_id=person.loc_id)
INNER JOIN countries ON(locs.country_id=countries.country_id)
GROUP BY country, loc
}
##
##
@permuted = (
['pers', 'consultant', 'm'],
['pers', 'consultant', 'f'],
['pers', 'consultant'],
['pers', 'contractor', 'm'],
['pers', 'contractor', 'f'],
['pers', 'contractor'],
['pers', 'employee', 'm'],
['pers', 'employee', 'f'],
['pers', 'employee'],
['pers'],
['dev', 'consultant', 'm'],
['dev', 'consultant', 'f'],
['dev', 'consultant'],
['dev', 'contractor', 'm'],
['dev', 'contractor', 'f'],
['dev', 'contractor'],
['dev', 'employee', 'm'],
['dev', 'employee', 'f'],
['dev', 'employee'],
['dev'],
['sales', 'consultant', 'm'],
['sales', 'consultant', 'f'],
['sales', 'consultant'],
['sales', 'contractor', 'm'],
['sales', 'contractor', 'f'],
['sales', 'contractor'],
['sales', 'employee', 'm'],
['sales', 'employee', 'f'],
['sales', 'employee'],
['sales'],
);
##
##
$header_formats = [
[
{ name => 'Area', colspan => 1, rowspan => 3 },
{ name => 'country', colspan => 1, rowspan => 3 },
{ name => 'location', colspan => 1, rowspan => 3 },
{ name => 'pers', colspan => 6, rowspan => 0 },
{ name => 'sales', colspan => 7, rowspan => 0 },
{ name => 'dev', colspan => 5, rowspan => 0 },
{ name => 'total', colspan => 1, rowspan => 3 }
],
[
{ name => 'employee', colspan => 3, rowspan => 0 },
{ name => 'contractor', colspan => 2, rowspan => 0 },
{ name => 'total', colspan => 1, rowspan => 2 },
{ name => 'employee', colspan => 2, rowspan => 0 },
{ name => 'contractor', colspan => 2, rowspan => 0 },
{ name => 'consultant', colspan => 2, rowspan => 0 },
{ name => 'total', colspan => 1, rowspan => 2 },
{ name => 'employee', colspan => 2, rowspan => 0 },
{ name => 'consultant', colspan => 2, rowspan => 0 },
{ name => 'total', colspan => 1, rowspan => 2 }
],
[
{ name => 'f', colspan => 1, rowspan => 1 },
{ name => 'm', colspan => 1, rowspan => 1 },
{ name => 'total', colspan => 1, rowspan => 1 },
{ name => 'm', colspan => 1, rowspan => 1 },
{ name => 'total', colspan => 1, rowspan => 1 },
{ name => 'm', colspan => 1, rowspan => 1 },
{ name => 'total', colspan => 1, rowspan => 1 },
{ name => 'm', colspan => 1, rowspan => 1 },
{ name => 'total', colspan => 1, rowspan => 1 },
{ name => 'f', colspan => 1, rowspan => 1 },
{ name => 'total', colspan => 1, rowspan => 1 },
{ name => 'm', colspan => 1, rowspan => 1 },
{ name => 'total', colspan => 1, rowspan => 1 },
{ name => 'f', colspan => 1, rowspan => 1 },
{ name => 'total', colspan => 1, rowspan => 1 }
]
];
##
##
# $level =0; $value = 'N';
print '', $value,' | ';
##
##
$recs_formats = {
0 => {
'N' => [ 7 ],
'S' => [ 3 ],
'zzzz' => [ 1 ],
},
1 => {
'Germany' => [ 4 ]
'UK' => [ 2 ],
'Italy' => [ 2 ],
'zzzz' => [ 1, 1, 1 ],
},
2 => {
'Berlin' => [ 1 ],
'Bonn' => [ 1 ],
'Munich' => [ 1 ],
'London' => [ 1 ]
'Rome' => [ 1 ],
'zzzz' => [ 1, 1, 1, 1, 1, 1 ],
}
};
##
##
-- xtab { colspan => 22 }
|-- Area { colspan => 1 rowspan => 3 }
|-- country { colspan => 1 rowspan => 3 }
|-- location { colspan => 1 rowspan => 3 }
|-- pers { colspan => 6 }
| |- employee { colspan => 3 }
| | |- f { colspan => 1 rowspan => 1 }
| | |- m { colspan => 1 rowspan => 1 }
| | +- total { colspan => 1 rowspan => 1 }
| |- contractor { colspan => 2 }
| | |- m { colspan => 1 rowspan => 1 }
| | +- total { colspan => 1 rowspan => 1 }
| +- total { colspan => 1 rowspan => 2 }
|-- sales { colspan => 7 }
| | employee { colspan => 2 }
| | |- m { colspan => 1 rowspan => 1 }
| | +- total { colspan => 1 rowspan => 1 }
| |- contractor { colspan => 2 }
| | |- m { colspan => 1 rowspan => 1 }
| | +- total { colspan => 1 rowspan => 1 }
| |- consultant { colspan => 2 }
| | |- f { colspan => 1 rowspan => 1 }
| | +- total { colspan => 1 rowspan => 1 }
| +- total { colspan => 1 rowspan => 2 }
|-- dev { colspan => 5 }
| |- employee { colspan => 2 }
| | |- m { colspan => 1 rowspan => 1 }
| | +- total { colspan => 1 rowspan => 1 }
| |- consultant { colspan => 2 }
| | |- f { colspan => 1 rowspan => 1 }
| | +- total { colspan => 1 rowspan => 1 }
| +- total { colspan => 1 rowspan => 2 }
+-- total { colspan => 1 rowspan => 3 }
-- recs { rowspan => 11 }
|-- N { rowspan => 7 }
| |- Germany { rowspan => 4 }
| | |- Berlin { rowspan => 1 }
| | |- Bonn { rowspan => 1 }
| | |- Munich { rowspan => 1 }
| | +- zzzz { rowspan => 1 }
| |- UK { rowspan => 2 }
| | |- London { rowspan => 1 }
| | +- zzzz { rowspan => 1 }
| +- zzzz { rowspan => 1 }
| +- zzzz { rowspan => 1 }
|-- S { rowspan => 3 }
| |- Italy { rowspan => 2 }
| | |- Rome { rowspan => 1 }
| | +- zzzz { rowspan => 1 }
| +- zzzz { rowspan => 1 }
| +- zzzz { rowspan => 1 }
+-- zzzz { rowspan => 1 }
+- zzzz { rowspan => 1 }
+- zzzz { rowspan => 1 }