public function getWhereSQL($fields)
{
$db = DevblocksPlatform::getDatabaseService();
$where = '';
$db_field_name = $fields[$this->field]->db_table . '.' . $fields[$this->field]->db_column;
// [JAS]: Operators
switch ($this->operator) {
case "eq":
case "=":
$where = sprintf("%s = %s", $db_field_name, self::_escapeSearchParam($this, $fields));
break;
case DevblocksSearchCriteria::OPER_EQ_OR_NULL:
$where = sprintf("(%s = %s OR %s IS NULL)", $db_field_name, self::_escapeSearchParam($this, $fields), $db_field_name);
break;
case "neq":
case "!=":
$where = sprintf("%s != %s", $db_field_name, self::_escapeSearchParam($this, $fields));
break;
case "in":
if (!is_array($this->value)) {
break;
}
$value = !empty($this->value) ? $this->value : array(-1);
$vals = array();
// Escape quotes
foreach ($this->value as $idx => $val) {
$vals[$idx] = addslashes($val);
// [TODO] Test
}
$where = sprintf("%s IN ('%s')", $db_field_name, implode("','", $vals));
break;
case DevblocksSearchCriteria::OPER_NIN:
// 'not in'
if (!is_array($this->value)) {
break;
}
$value = !empty($this->value) ? $this->value : array(-1);
$where = sprintf("%s NOT IN ('%s')", $db_field_name, implode("','", $value));
break;
case DevblocksSearchCriteria::OPER_FULLTEXT:
// 'fulltext'
$search = DevblocksPlatform::getSearchService();
$value = null;
$scope = null;
if (!is_array($this->value)) {
$value = $this->value;
$scope = 'expert';
} else {
$value = $this->value[0];
$scope = $this->value[1];
}
switch ($scope) {
case 'all':
$value = $search->prepareText($value);
$value = '+' . str_replace(' ', ' +', $value);
break;
case 'any':
$value = $search->prepareText($value);
break;
case 'phrase':
$value = '"' . $search->prepareText($value) . '"';
break;
default:
case 'expert':
break;
}
$where = sprintf("MATCH(%s) AGAINST (%s IN BOOLEAN MODE)", $db_field_name, $db->qstr($value));
break;
case DevblocksSearchCriteria::OPER_LIKE:
// 'like'
$where = sprintf("%s LIKE %s", $db_field_name, str_replace('*', '%', self::_escapeSearchParam($this, $fields)));
break;
case DevblocksSearchCriteria::OPER_NOT_LIKE:
// 'not like'
$where = sprintf("%s NOT LIKE %s", $db_field_name, str_replace('*', '%%', self::_escapeSearchParam($this, $fields)));
break;
case DevblocksSearchCriteria::OPER_IS_NULL:
// 'is null'
$where = sprintf("%s IS NULL", $db_field_name);
break;
/*
* [TODO] Someday we may want to call this OPER_DATE_BETWEEN so it doesn't interfere
* with the operator in other uses
*/
/*
* [TODO] Someday we may want to call this OPER_DATE_BETWEEN so it doesn't interfere
* with the operator in other uses
*/
case DevblocksSearchCriteria::OPER_BETWEEN:
// 'between'
if (!is_array($this->value) && 2 != count($this->value)) {
break;
}
$from_date = $this->value[0];
if (!is_numeric($from_date)) {
// Translate periods into dashes on string dates
if (false !== strpos($from_date, '.')) {
$from_date = str_replace(".", "-", $from_date);
}
if (false === ($from_date = strtotime($from_date))) {
$from_date = 0;
}
}
$to_date = $this->value[1];
if (!is_numeric($to_date)) {
// Translate periods into dashes on string dates
if (false !== strpos($to_date, '.')) {
$to_date = str_replace(".", "-", $to_date);
}
if (false === ($to_date = strtotime($to_date))) {
$to_date = strtotime("now");
}
}
if (0 == $from_date) {
$where = sprintf("(%s IS NULL OR %s BETWEEN %s and %s)", $db_field_name, $db_field_name, $from_date, $to_date);
} else {
$where = sprintf("%s BETWEEN %s and %s", $db_field_name, $from_date, $to_date);
}
break;
case DevblocksSearchCriteria::OPER_GT:
case DevblocksSearchCriteria::OPER_GTE:
case DevblocksSearchCriteria::OPER_LT:
case DevblocksSearchCriteria::OPER_LTE:
$where = sprintf("%s %s %s", $db_field_name, $this->operator, self::_escapeSearchParam($this, $fields));
break;
default:
break;
}
return $where;
}
