Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
0.00% |
0 / 3 |
CRAP | |
0.00% |
0 / 67 |
| Filter2where | |
0.00% |
0 / 1 |
|
0.00% |
0 / 3 |
2162.00 | |
0.00% |
0 / 67 |
| filter2where | |
0.00% |
0 / 1 |
272.00 | |
0.00% |
0 / 22 |
|||
| rules | |
0.00% |
0 / 1 |
56.00 | |
0.00% |
0 / 7 |
|||
| rule2sql | |
0.00% |
0 / 1 |
552.00 | |
0.00% |
0 / 38 |
|||
| <?php | |
| namespace ia\JqGrid; | |
| use ia\Util\Str; | |
| /** | |
| * @author Informática Asocaida SA de CV | |
| * @version 1.0.0 | |
| * @copyright 2018 | |
| */ | |
| class Filter2where { | |
| private static $opToStandard = [ | |
| 'eq' => 'eq', | |
| 'ne' => 'ne', | |
| 'lt' => 'lt', | |
| 'le' => 'le', | |
| 'gt' => 'gt', | |
| 'ge' => 'ge', | |
| 'bw' => 'bw', // begins with | |
| 'bn' => 'bn', // not begins with | |
| 'cn' => 'cn', // containds | |
| 'nc' => 'nc', // not contains | |
| 'ew' => 'ew', // ends with | |
| 'en' => 'en', // not ends | |
| 'in' => 'in', // is in set | |
| 'ni' => 'ni', // is not in set | |
| 'nu' => 'nu', // is null | |
| 'nn' => 'nn', // not null | |
| ]; | |
| private static $opOperator = [ | |
| 'eq' => '=', | |
| 'ne' => '<>', | |
| 'lt' => '<', | |
| 'le' => '<=', | |
| 'gt' => '>', | |
| 'ge' => '>=', | |
| 'in' => '=', // fallback por si no mandan array con in clause | |
| 'ni' => '<>', // fallback por si no mandan array con in clause | |
| ]; | |
| public static function filter2where($filters, $groupOp = 'AND') { | |
| if(empty($filters)) { | |
| return ''; | |
| } | |
| $where = ''; | |
| foreach($filters as $key => $f) { | |
| if(empty($f)) | |
| continue; | |
| if($key === 'groupOp') | |
| $groupOp = Str::strim($f); | |
| elseif($key === 'rules') { | |
| $clause = self::rules($f, $groupOp); | |
| if(!empty($clause)) { | |
| $where .= empty($where) ? $clause : " $groupOp $clause"; | |
| } | |
| } elseif($key === 'groups') { | |
| if(array_key_exists('rules', $f)) { | |
| $clause = self::filters2where($f, $groupOp); | |
| if(!empty($clause)) { | |
| $where .= empty($where) ? $clause : " $groupOp $clause"; | |
| } | |
| } else | |
| foreach($f as $g) { | |
| $clause = self::filters2where($g, $groupOp); | |
| if(!empty($clause)) { | |
| $where .= empty($where) ? " $clause" : " $groupOp $clause"; | |
| } | |
| } | |
| } | |
| } | |
| return empty($where) ? '' : '('.$where.')'; | |
| } | |
| private static function rules($rules, $groupOp) { | |
| $where = ''; | |
| if(is_array($rules) && !empty($rules)) | |
| foreach($rules as $r) { | |
| $clause = self::rule2sql($r); | |
| if(!empty($clause)) { | |
| $where .= empty($where) ? ' ' . $clause : ' ' . $groupOp . ' ' . $clause; | |
| } | |
| } | |
| return empty($where) ? '' : '('.$where.')'; | |
| } | |
| public static function rule2sql($r) { | |
| if(!array_key_exists('field',$r) || !array_key_exists('op',$r) || !array_key_exists('Data',$r) ) { | |
| // missing a field, malformed filter | |
| return ''; | |
| } | |
| $fieldSent = Str::strim($r['field']); | |
| if(empty($fieldSent)) { | |
| return ''; | |
| } | |
| $field = Str::fieldit($fieldSent); | |
| $op = strtolower(Str::strim($r['op'])); | |
| if(empty($op)) { | |
| return ''; | |
| } | |
| if(isset(self::$opToStandard[$op])) { | |
| $op = self::$opToStandard[$op]; | |
| } | |
| if(is_array($r['Data']) && !empty($r['Data']) && ($op === 'in' || $op === 'ni' ) ) { | |
| $data = ''; | |
| foreach($r['Data'] as $d) { | |
| $data .= ','.Str::strit($d); | |
| } | |
| if(empty($data)) { | |
| return ''; | |
| } | |
| return $field.($op === 'in' ? ' IN(' : ' NOT IN(').substr($data,1).')'; | |
| } | |
| if(array_key_exists($op,self::$opOperator)) { | |
| return $field . ' ' . self::$opOperator[$op] . Str::strit(Str::strim($r['Data'])); | |
| } | |
| $value = Str::strlike(Str::strim($r['Data'])); | |
| if($op === 'bw') { | |
| return $field.' LIKE '.Str::strit($value.'%'); | |
| } | |
| if($op === 'bn') { | |
| return $field.' NOT LIKE '.Str::strit($value.'%'); | |
| } | |
| if($op === 'cn') { | |
| return $field.' LIKE '.Str::strit('%'.$value.'%'); | |
| } | |
| if($op === 'nc') { | |
| return $field.' NOT LIKE '.Str::strit('%'.$value.'%'); | |
| } | |
| if($op === 'ew') { | |
| return $field.' LIKE '.Str::strit('%'.$value); | |
| } | |
| if($op === 'en') { | |
| return $field.' NOT LIKE '.Str::strit('%'.$value); | |
| } | |
| if($op === 'nu') { | |
| return "$field IS NULL"; | |
| } | |
| if($op === 'nn') { | |
| return "$field IS NOT NULL"; | |
| } | |
| return ''; | |
| } | |
| } |