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];
}
