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

##</code><code>##

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 );

##</code><code>##

$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
}

##</code><code>##

 @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'],
);

##</code><code>##

$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 }
  ]
];

##</code><code>##

# $level =0; $value = 'N';
print '<td rowspan= "',
    shift @{$recs_formats->{$level}->{$value}},
    '">', $value,'</td>';

##</code><code>##

$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 ],
  }
};

##</code><code>##


-- 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 }