protected static function buildSingleParam($paramName, $column, $filter) { $sql = ""; $param = ""; $pcolumn = preg_replace('/[^\\da-z]/i', '_', $column); $driver = Setting::get('db.driver'); $column = ActiveRecord::formatSingleCriteria($column, $driver); ## quote field if it is containing illegal char if (!preg_match("/^[a-zA-Z_][a-zA-Z0-9_]*\$/", str_replace(".", "", $column))) { $column = "{$column}"; } switch ($filter['type']) { case "string": if ($filter['value'] != "" || $filter['operator'] == 'Is Empty') { $sCol = DataFilter::toSQLStr("{$column}", $driver); $spCol = DataFilter::toSQLStr(":{$paramName}_{$pcolumn}", $driver); switch ($filter['operator']) { case "Contains": $sql = "{$sCol} LIKE {$spCol}"; $param = "%{$filter['value']}%"; break; case "Does Not Contain": $sql = "{$sCol} NOT LIKE {$spCol}"; $param = "%{$filter['value']}%"; break; case "Is Equal To": $sql = "{$sCol} LIKE {$spCol}"; $param = "{$filter['value']}"; break; case "Starts With": $sql = "{$sCol} LIKE {$spCol}"; $param = "{$filter['value']}%"; break; case "Ends With": $sql = "{$sCol} LIKE {$spCol}"; $param = "%{$filter['value']}"; break; case "Is Any Of": $param_raw = preg_split('/\\s+/', trim($filter['value'])); $param = []; $psql = []; foreach ($param_raw as $k => $p) { $param[":{$paramName}_{$pcolumn}_{$k}"] = "%{$p}%"; $spCol = DataFilter::toSQLStr(":{$paramName}_{$pcolumn}_{$k}", $driver); $psql[] = "{$sCol} LIKE {$spCol}"; } $sql = "(" . implode(" OR ", $psql) . ")"; break; case "Is Not Any Of": $param_raw = preg_split('/\\s+/', trim($filter['value'])); $param = []; $psql = []; foreach ($param_raw as $k => $p) { $param[":{$paramName}_{$pcolumn}_{$k}"] = "%{$p}%"; $spCol = DataFilter::toSQLStr(":{$paramName}_{$pcolumn}_{$k}", $driver); $psql[] = "{$sCol} LIKE {$spCol}"; } $sql = "(" . implode(" AND ", $psql) . ")"; break; case "Is Empty": $sql = "({$column} LIKE '' OR {$column} IS NULL)"; break; } } break; case "number": if ($filter['value'] != "" || $filter['operator'] == 'Is Empty') { switch ($filter['operator']) { case "=": case "<>": case ">": case '>': case '>=': case '<=': case '<': $sql = "{$column} {$filter['operator']} :{$paramName}_{$pcolumn}"; $param = "{$filter['value']}"; break; case "Is Empty": $sql = "({$column} IS NULL)"; break; } } break; case "date": switch ($filter['operator']) { case "Between": case "Weekly": case "Monthly": case "Yearly": if (@$filter['value']['from'] != '' && @$filter['value']['to'] != '') { $a = self::toSQLDateTime(":{$paramName}_{$pcolumn}_from", $driver); $b = self::toSQLDateTime(":{$paramName}_{$pcolumn}_to", $driver); $sql = "({$column} BETWEEN {$a} AND {$b})"; $fromStartHour = date('Y-m-d 23:59:00', strtotime('-1 day', strtotime(@$filter['value']['from']))); $toLastHour = date('Y-m-d 23:59:00', strtotime(@$filter['value']['to'])); $param = [":{$paramName}_{$pcolumn}_from" => $fromStartHour, ":{$paramName}_{$pcolumn}_to" => $toLastHour]; } break; case "Not Between": if (@$filter['value']['from'] != '' && @$filter['value']['to'] != '') { $a = self::toSQLDateTime(":{$paramName}_{$pcolumn}_from", $driver); $b = self::toSQLDateTime(":{$paramName}_{$pcolumn}_to", $driver); $sql = "({$column} NOT BETWEEN {$a} AND {$b})"; $toLastHour = date('Y-m-d 23:59:00', strtotime(@$filter['value']['to'])); $param = [":{$paramName}_{$pcolumn}_from" => @$filter['value']['from'], ":{$paramName}_{$pcolumn}_to" => $toLastHour]; if (@$filter['value']['to'] == '' || @$filter['value']['from'] == '') { $sql = "1 = 1"; } } break; case "More Than": if (@$filter['value']['from'] != '') { $sql = "{$column} > " . self::toSQLDate(":{$paramName}_{$pcolumn}", $driver); $param = @$filter['value']['from']; } break; case "Less Than": if (@$filter['value']['to'] != '') { $sql = "{$column} < " . self::toSQLDate(":{$paramName}_{$pcolumn}", $driver); $param = @$filter['value']['to']; } break; case "Daily": if (@$filter['value'] != '') { if ($driver == "mysql") { $sql = "DATE({$column}) = DATE(:{$paramName}_{$pcolumn})"; } else { if ($driver == "oci") { $sql = "{$column} = TO_DATE('YY-MM-DD',:{$paramName}_{$pcolumn})"; } } $param = @$filter['value']; } break; } break; case "list": if (isset($filter['value']) && $filter['value'] != '') { $sql = "{$column} LIKE :{$paramName}_{$pcolumn}"; $param = @$filter['value']; } break; case "relation": switch ($filter['operator']) { case 'empty': if ($filter['value'] == 'null') { $sql = "{$column} is null"; $param = @$filter['value']; } else { $sql = "{$column} = :{$paramName}_{$pcolumn}"; $param = @$filter['value']; } break; default: if ($filter['value'] != '') { $sql = "{$column} = :{$paramName}_{$pcolumn}"; $param = @$filter['value']; } break; } break; case "check": if ($filter['value'] != '') { if (@$filter['operator'] == 'in') { // USING IN... $param = []; $psql = []; foreach ($filter['value'] as $k => $p) { $param[":{$paramName}_{$pcolumn}_{$k}"] = "{$p}"; $psql[] = ":{$paramName}_{$pcolumn}_{$k}"; } $sql = "{$column} IN (" . implode(", ", $psql) . ")"; } else { // USING LIKE... $param = []; $psql = []; foreach ($filter['value'] as $k => $p) { $param[":{$paramName}_{$pcolumn}_{$k}"] = "%{$p}%"; $psql[] = "{$column} LIKE :{$paramName}_{$pcolumn}_{$k}"; } $sql = "(" . implode(" AND ", $psql) . ")"; } } break; } return ['sql' => $sql, 'param' => $param]; }