#!/usr/bin/perl
use strict;
use warnings;

use DBI;
use Data::Dumper;
use SQL::Abstract;

my %where = ();

sub process(){

    my $processed = $_[0];
    
    return sub {
    
        if ( @_ ){
            
            my ($colname,$colval) = @_;
            $colval =~ s/^&//; # strip leading & - this only occurs in
+ the 'additional field'
            print "Colname: $colname\n";
            print "Colvalue: $colval\n";
            
            unless($colval =~ m/[&|*]/){
                print "normal value: $colval \n";
                $where{$colname}=$colval;
            }
                    
            # detect ors
            if ( index($colval,'|') >= 0 ){
                print "$colval contains pipe character for ORs\n";
                #$where{'-or'}{ {$colname=$colval}
            }

            # detect wildcards
            if ( index($colval,'*') >= 0 ){
                
                # convert * Wildcard to Oracle wildcard
                $colval =~ s/\*/%/g;
                $where{$colname}{'-like'} = $colval;                
            }
            
            # detect date range
            if( index($colval, ':') > 0 ){
            
                print "found date: key: $colname - value: $colval\n";
                my ( $from, $to ) = split(':', $colval);
                print "from: $from\n";
                print "to: $to\n";
                $where{$colname}{'-between'} = [$from,$to];
            
            }
            
        }
    
    }
    
}

my $cnt=0;
my $sql = SQL::Abstract->new;
my $table_name = "TABNAME";
my $facility = "FAC1";
my $col_type = "BEG";
my $col_stype = "FOO|BAR";
my $rec_number = "BAZ/*";
my $additional = "&Key1=VAL1&Key2=VAL2&DATE1=20111111:20111112";

process->("col_type",$col_type) if defined($col_type);
process->("col_stype",$col_stype) if defined($col_stype);
process->("rec_number",$rec_number) if defined($rec_number);

# process additional:
$additional =~ s/^&//; 

if ( index($additional,'&') >= 0 ){
    print "$additional contains & character - so it's the additional f
+ield with multiple key value pairs\n";
    
    my @additional = split('&',$additional);
    foreach my $addval (@additional){
        print "additional has pair: $addval\n";
        
        my @addpair = split('=',$addval);
        print "$addpair[0] | $addpair[1]\n";
        process->($addpair[0], $addpair[1]);
    }
    
}else{
    print "Single value in additional:\n";
    my @addpair = split('=',$additional);
    print "$addpair[0] | $addpair[1]\n";
    process->($addpair[0], $addpair[1]);        
}

my($stmt, @bind) = $sql->select($table_name, 'count(rowid)', \%where);
print Dumper $stmt;
print "\n";
print Dumper @bind;
print "\n";
[download]

Colname: col_type
Colvalue: BEG
normal value: BEG
Colname: col_stype
Colvalue: FOO|BAR
FOO|BAR contains pipe character for ORs
Colname: rec_number
Colvalue: BAZ/*
Key1=VAL1&Key2=VAL2&DATE1=20111111:20111112 contains & character - so 
+it's the additional field with multiple key value pairs
additional has pair: Key1=VAL1
Key1 | VAL1
Colname: Key1
Colvalue: VAL1
normal value: VAL1
additional has pair: Key2=VAL2
Key2 | VAL2
Colname: Key2
Colvalue: VAL2
normal value: VAL2
additional has pair: DATE1=20111111:20111112
DATE1 | 20111111:20111112
Colname: DATE1
Colvalue: 20111111:20111112
normal value: 20111111:20111112
found date: key: DATE1 - value: 20111111:20111112
from: 20111111
to: 20111112
Can't use string ("20111111:20111112") as a HASH ref while "strict ref
+s" in use at ./short.pl line 51.
[download]