#!/usr/bin/perl
use strict;
# author: Matt Rankin
# date:   16/Aug/2007
# inspirational quote: "LEEEEEROY!!!!!!!" - Leeroy Jenkins

# the data, in all its dataness.
my $sp=['sno','pno','qty'];
my @sp=(['S1','P1','300'],
        ['S1','P2','200'],
        ['S1','P3','400'],
        ['S1','P4','200'],
        ['S1','P5','100'],
        ['S1','P6','100'],
        ['S2','P1','300'],
        ['S2','P2','400'],
        ['S3','P2','200'],
        ['S4','P2','200'],
        ['S4','P4','300'],
        ['S4','P5','400']);
my $select='pno,qty';

# SELECT pno, sum(qty) as sumqty
print("My SQL-O-Matic!\n\n");
print("SELECT pno, sum(qty) as sumqty\nFROM sp\nWHERE sno <= 'S3'\n".
      "GROUP BY pno\nHAVING sumqty > 500\nORDER BY sumqty\n");

my $rows=&select_from($select,\@sp,$sp,'sno <= S3');
my $groups=&group_rows($select,$rows,'qty > 500');

# display the results
print("\nQUERY RESULTS\n\n");
print(sprintf "       %-6s  sum%s\n",split(m/,/,$select));
my $i=0;
# after much trial and error, a self-sorted foreach loop!
foreach my $grp(sort{$b->{qty} <=> $a->{qty}}@$groups){
   print("[".$i++."] ");
   print(sprintf "   %-6s  %d",$grp->{pno},$grp->{qty});
   print("\n");
}
print("\nRows returned: ".scalar(@$groups)."\n");
# -------------------------------- subroutines --------------------------------

sub select_from($$$){
   my @cols=split(m/,/,shift);      # columns to return.
   my $table=shift;                 # ref to 2d array.
   my @fields=@{shift(@_)};         # column names.
   my @cond=split(m/ /,shift);      # 'where' condition.
   my @result;                      # matching rows go here.
   foreach my $row(@$table){
      my %record;
      my $i=0;
      foreach(@$row){
         $record{$fields[$i++]}=$_; # not pretty.
      }
      # we now have a record hash.
      # eval() is such a cheap, dirty trick. now all i need is a 'goto'...
      my $where='$record{'."$cond[0]".'} '.&op($cond[1])." '$cond[2]'";
      push(@result,\%record) if(eval $where);
   }
   # gets rid of unwanted fields in a reasonably generalised manner.
   foreach my $href(@result){
      my $regex=join("|",@cols);
      foreach(@fields){
         delete($href->{$_}) unless(m/$regex/);
      }
   }
   return \@result;  # consists of the desired fields and rows that meet the
                     # 'WHERE' criteria, ready for the next round humiliation
                     # and sadism.
}

sub group_rows($$$){
   my @cols=split(m/,/,shift);
   my $table=shift;
   my @cond=split(m/ /,shift);
   my %groups;
   # lately i've become addicted to drinking brake fluid.
   # it's not a problem though. i can stop any time i want!
   foreach my $row(@$table){
      # $row is a ref to a hash{pno}{qty}
      $groups{$row->{$cols[0]}}+=$row->{$cols[1]}; # i promise i didn't steal
                                                   # this from the internet.
   }
   # click go the shears boys, click click click...
   foreach my $row(keys %groups){
      my $having='$groups{$row} '.$cond[1].' 500';
      delete($groups{$row}) unless(eval $having);
   }
   # put it all back into the same kind of data structure (array of hashes)
   my @output;
   foreach my $k(keys %groups){
      push(@output,{$cols[0]=>$k,$cols[1]=>$groups{$k}});
   }
   return \@output;  # consists of desired fields and rows that meet the
                     # 'HAVING' criteria.
}

# you probably would have let me hardcode the comparison operator, so this is
# probably redundant. god i'm thankful that perl has the 'le' operator...
sub op{
   my $op=shift;
   if($op eq '='){
      return 'eq';
   }elsif($op eq '<'){
      return 'lt';
   }elsif($op eq '>'){
      return 'gt';
   }elsif($op eq '<='){
      return 'le';
   }elsif($op eq '>='){
      return 'ge';
   }
}