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