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

##</code><code>##

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 refs" in use at ./short.pl line 51.