#! /usr/bin/perl -CS

use strict;
use utf8;
use CGI qw(-utf8);
use JSON::XS;
use HTML::Entities;
use DBI;
use POSIX;

my $cgi = CGI->new();


my $id              = $cgi->param('id');
my $lines           = $cgi->param('rows');
my $page            = $cgi->param('page');
my $sidx  			= $cgi->param('sidx') 			// '';
my $sord  			= ($cgi->param('sord') eq 'asc')?'asc':'desc';

my %resp			= ();

s/\D//g foreach($id, $lines, $page);


my $dbh = DBI->connect(
						'DBI:mysql:testbase:localhost', 
						'testuser', 
						'testpassword', 
						{ 
						  RaiseError => 0, 
						  AutoCommit => 1, 
						  mysql_enable_utf8 =>1 
						}) or die "Unable to connect to db\n";
$dbh->do("SET NAMES UTF8");

my %tbl_names = (
  myjq_id				=> 'fc_id',
  myjq_name				=> 'fc_name',
  myjq_cal_num			=> 'fc_cal_per_100g',
  myjq_cal_level		=> 'fc_cal_level',
  myjq_percent			=> 'diet_percent'
);

my $srch_res = get_searchstring( $cgi, \%tbl_names, 'HAVING' );

my $statement = "SELECT SQL_CALC_FOUND_ROWS
						fc_id, 
						fc_name, 
						fc_cal_per_100g, 
						fc_cal_level, 
						ROUND(fc_cal_per_100g*100/2000) AS diet_percent
				  FROM food_cal_table
				  $$srch_res{srch_string}";
				
if ( $sord ne '' and  $sidx ne '' and $tbl_names{$sidx}){
	$statement .= " ORDER BY $tbl_names{$sidx} $sord";	
}
  
$statement .= " LIMIT ?, ?";
				
my $rows = $dbh->selectall_arrayref( $statement, undef, @{$$srch_res{binds}}, ($page-1)*$lines, $lines);

( $resp{records} ) 	= $dbh->selectrow_array("SELECT FOUND_ROWS()");
$resp{total} 		= ceil($resp{records}/$lines);
$resp{page}			= $page;


  my $i=0;
  foreach my $row (@$rows){
	
        $resp{rows}[$i++] = {
            id      => $$row[0],
            cell    => [
				@{$row}[0..4] # slice of array by ref
            ]
        };
  }
  
send_json_data( \%resp );




exit 0;


######################################################
=pod

'eq'		'equal'
'ne'		'not equal'
'lt'		'less'
'le'		'less or equal'
'gt'		'greater'
'ge'		'greater or equal'
'bw'		'begins with'
'bn'		'does not begin with'
'in'		'is in'
'ni'		'is not in'
'ew'		'ends with'
'en'		'does not end with'
'cn'		'contains'
'nc'		'does not contain'

num_cast - to perform this comparison, operand must be casted to number
str_cast - to perform this comparison, operand must be casted to string
=cut

sub _get_filterops{

  my %filterops = (
	'eq' => {
	  str 			=>	sub{ '= ?' },
	  bind			=>	sub{ return shift }
	},
	'ne' => {
	  str			=>	sub{ '<> ?' },
	  bind			=>	sub{ return shift }		
	},
	'lt' => {
	  str			=>	sub{ '< ?' },
	  bind			=>	sub{ return shift },
	  num_cast		=>	0
	},
	'le' => {
	  str			=>	sub{ '<= ?' },
	  bind			=>	sub{ return shift },
	  num_cast		=>	0
	},
	'gt' => {
	  str			=>	sub{ '> ?' },
	  bind			=>	sub{ return shift },
	  num_cast		=>	0
	},
	'ge' => {
	  str			=>	sub{ '>= ?' },
	  bind			=>	sub{ return shift }	,
	  num_cast		=>	0
	},
	
	'bw' => {
	  str			=>	sub{ 'LIKE ?' },
	  bind			=>	sub { return(shift . "%") },
	  str_cast		=>	1,		
	},
	'bn' => {
	  str			=>	sub{ 'NOT LIKE ?' },
	  bind			=>	sub { return(shift . "%") },
	  str_cast		=>	1,
	},
	'in' => {
	  str			=>	sub{ 'IN(' . (  join ',', map{'?'}split(',', shift)) . ')'},
	  bind			=>	sub{ return split(',', shift) }
	},
	'ni' => {
	  str			=>	sub{ 'NOT IN(' . (  join ',', map{'?'}split(',', shift)) . ')' },
	  bind			=>	sub{ return  split(',', shift) }
	}, 
	'ew' => {
	  str			=>	sub{ 'LIKE ?' },
	  bind			=>	sub{ return("%" . shift) },
	  str_cast		=>	1,
	},
	'en' => {
	  str			=>	sub{ 'NOT LIKE ?' },
	  bind			=>	sub{ return("%" . shift) },
	  str_cast		=>	1,
	},
	'cn' => {
	  str 			=>	sub{ 'LIKE ?' },
	  bind			=>	sub{ return("%" . shift . "%") },
	  str_cast		=>	1,
	},
	'nc' => {
	  str 			=>	sub{ 'NOT LIKE ?' },
	  bind			=>	sub{ return("%" . shift . "%") },
	  str_cast		=>	1,
	},
	'nu' => {
	  str 			=>	sub{ 'IS NULL' }
	},
	'nn' => {
	  str 			=>	sub{ 'IS NOT NULL' }
	},
  );
  
  return \%filterops;
}
# translates search request from jqGrid to 
# string of placeholders for mysql and array of corresponding bound values
# function input:
#- cgi object containing jqGrid request
#- hash that links jqGrid and MySQL names
#- type of wrapper that will be used 'AND', 'WHERE', 'HAVING' or empty string
# function output:
# hashref containing string and arrayref of bindvalues

sub get_searchstring{
  my ($cgi, $tbl_names, $str_init) = @_;

  my %tbl_names 	= %$tbl_names;
  my $searchstring 	= '';
  my @binds 		= ();
  
  my %filterops = %{_get_filterops()};
  
  my $searchrequest	= ($cgi->param('_search') eq 'true')?(get_data_from_json($cgi->param('filters'))):undef;
  
  if( $searchrequest ){
	  
	my @searchoptions = ();
	my $groupOp = $$searchrequest{groupOp} eq 'AND'?'AND':'OR';
	
	foreach my $rule (@{$$searchrequest{rules}}){ # processing sequentially all pieces of jqgrid search request
	  next unless $filterops{ $$rule{op} } and $tbl_names{ $$rule{field} }; # if operation is incorrect or column is unknown
      # preparing string of placeholders      
	  push @searchoptions, ( $filterops{ $$rule{op} }{str_cast}? 			# should operand be casted to string?
					"CAST($tbl_names{ $$rule{field} } AS CHAR) ":			
					  $filterops{ $$rule{op} }{num_cast}?					# should operand be casted to number?
						"CAST($tbl_names{ $$rule{field} } AS UNSIGNED)":
						  "$tbl_names{ $$rule{field} } "					# by default operand is  used as is
				) . $filterops{ $$rule{op} }{str}->($$rule{data});			# to the right of the operand we are joining sql condition
				
	  push @binds, $filterops{ $$rule{op} }{bind}->($$rule{data}) if $filterops{ $$rule{op} }{bind}; # corresponding bind value(s)
	}
	
	$searchstring = join(" $groupOp ", @searchoptions); # joining all condidions as it was  selected in JqGrid search dialog
	
	if( $searchstring =~ /\?/ or $searchstring =~ /NULL/){ # if resulting string looks good
	  $searchstring = " $str_init (" . $searchstring . ") "; # adding wrapper
	}
	else{
	  $searchstring = "";
	}
  }
  
  return { srch_string => $searchstring, binds => \@binds};

}

sub send_json_data{	
  # hashref or arrayref
  my $ref = shift;

  my $json_xs = JSON::XS->new();
  $json_xs->ascii(1);

  my $outdata = $json_xs->encode( $ref );
  
  print "Status: 200 OK\n";
  print "Content-Length: " . length($outdata) . ";\n";
  print "Content-type: application/json\n\n";
  
  print $outdata;
  
  exit(0);
}

sub get_data_from_json{
  my $jsontext = shift;
  
  decode_entities($jsontext);
  
  my $json_xs = JSON::XS->new();
  $json_xs->ascii(1);
  
  return $json_xs->decode($jsontext);
}