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