/**
*
* @param mixed $value
* @param String $operator
* @param WebserviceField $field
*/
private function getConditionValue($value, $operator, $field)
{
$operator = strtolower($operator);
$db = PearDatabase::getInstance();
$noncommaSeparatedFieldTypes = array('currency', 'percentage', 'double', 'integer', 'number');
if (in_array($field->getFieldDataType(), $noncommaSeparatedFieldTypes)) {
if (is_array($value)) {
$valueArray = $value;
} else {
$valueArray = array($value);
}
// if ($field->getFieldDataType() == 'multipicklist' && in_array($operator, array('e', 'n'))) {
// $valueArray = getCombinations($valueArray);
// foreach ($valueArray as $key => $value) {
// $valueArray[$key] = ltrim($value, ' |##| ');
// }
// }
} elseif (is_string($value)) {
$valueArray = explode(',', $value);
} elseif (is_array($value)) {
$valueArray = $value;
} else {
$valueArray = array($value);
}
$sql = array();
if ($operator == 'exists') {
global $current_user, $adb;
$mid = getTabModuleName($field->getTabId());
$qg = new QueryGenerator($mid, $current_user);
$qg->addCondition($field->getFieldName(), $value, 'e');
$sql[] = 'SELECT EXISTS(SELECT 1 ' . $qg->getFromClause() . $qg->getWhereClause() . ')';
return $sql;
}
if ($operator == 'i' or $operator == 'in' or $operator == 'ni' or $operator == 'nin') {
$vals = array_map(array($db, 'quote'), $valueArray);
$sql[] = (($operator == 'ni' or $operator == 'nin') ? ' NOT ' : '') . 'IN (' . implode(',', $vals) . ')';
return $sql;
}
if ($operator == 'between' || $operator == 'bw' || $operator == 'notequal') {
if ($field->getFieldName() == 'birthday') {
$valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]);
$valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]);
$sql[] = "BETWEEN DATE_FORMAT(" . $db->quote($valueArray[0]) . ", '%m%d') AND " . "DATE_FORMAT(" . $db->quote($valueArray[1]) . ", '%m%d')";
} else {
if ($this->isDateType($field->getFieldDataType())) {
$valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]);
$valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]);
}
$sql[] = "BETWEEN " . $db->quote($valueArray[0]) . " AND " . $db->quote($valueArray[1]);
}
return $sql;
}
$yes = strtolower(getTranslatedString('yes'));
$no = strtolower(getTranslatedString('no'));
foreach ($valueArray as $value) {
if (!$this->isStringType($field->getFieldDataType())) {
$value = trim($value);
}
if ($operator == 'empty' || $operator == 'y') {
$sql[] = sprintf("IS NULL OR %s = ''", $this->getSQLColumn($field->getFieldName()));
continue;
}
if ($operator == 'ny') {
$sql[] = sprintf("IS NOT NULL AND %s != ''", $this->getSQLColumn($field->getFieldName()));
continue;
}
if (strtolower(trim($value)) == 'null' || trim($value) == '' && !$this->isStringType($field->getFieldDataType()) && ($operator == 'e' || $operator == 'n')) {
if ($operator == 'e') {
$sql[] = "IS NULL";
continue;
}
$sql[] = "IS NOT NULL";
continue;
} elseif ($field->getFieldDataType() == 'boolean') {
$value = strtolower($value);
if ($value == 'yes' or $value == $yes) {
$value = 1;
} elseif ($value == 'no' or $value == $no) {
$value = 0;
}
} elseif ($this->isDateType($field->getFieldDataType())) {
$value = getValidDBInsertDateTimeValue($value);
if (empty($value)) {
$sql[] = 'IS NULL or ' . $field->getTableName() . '.' . $field->getFieldName() . " = ''";
return $sql;
}
} elseif ($field->getFieldDataType() == 'picklist' || $field->getFieldDataType() == 'multipicklist') {
if (!isValueInPicklist($value, $field->getFieldName())) {
$value = getTranslationKeyFromTranslatedValue($this->module, $value);
}
} else {
if ($field->getFieldDataType() === 'currency') {
$uiType = $field->getUIType();
if ($uiType == 72) {
$value = CurrencyField::convertToDBFormat($value, null, true);
} elseif ($uiType == 71) {
$value = CurrencyField::convertToDBFormat($value, $this->user);
}
}
}
if ($field->getFieldName() == 'birthday' && !$this->isRelativeSearchOperators($operator)) {
$value = "DATE_FORMAT(" . $db->quote($value) . ", '%m%d')";
} else {
$value = $db->sql_escape_string($value);
}
if (trim($value) == '' && ($operator == 's' || $operator == 'ew' || $operator == 'c') && ($this->isStringType($field->getFieldDataType()) || $field->getFieldDataType() == 'picklist' || $field->getFieldDataType() == 'multipicklist')) {
$sql[] = "LIKE ''";
continue;
}
if (trim($value) == '' && $operator == 'k' && $this->isStringType($field->getFieldDataType())) {
$sql[] = "NOT LIKE ''";
continue;
}
switch ($operator) {
case 'e':
$sqlOperator = "=";
break;
case 'n':
$sqlOperator = "<>";
break;
case 's':
$sqlOperator = "LIKE";
$value = "{$value}%";
break;
case 'ew':
$sqlOperator = "LIKE";
$value = "%{$value}";
break;
case 'c':
$sqlOperator = "LIKE";
$value = "%{$value}%";
break;
case 'k':
$sqlOperator = "NOT LIKE";
$value = "%{$value}%";
break;
case 'l':
$sqlOperator = "<";
break;
case 'g':
$sqlOperator = ">";
break;
case 'm':
$sqlOperator = "<=";
break;
case 'h':
$sqlOperator = ">=";
break;
case 'a':
$sqlOperator = ">";
break;
case 'b':
$sqlOperator = "<";
break;
}
if (!$this->isNumericType($field->getFieldDataType()) && ($field->getFieldName() != 'birthday' || $field->getFieldName() == 'birthday' && $this->isRelativeSearchOperators($operator))) {
$value = "'{$value}'";
}
if ($this->isNumericType($field->getFieldDataType()) && empty($value)) {
$value = '0';
}
$sql[] = "{$sqlOperator} {$value}";
}
return $sql;
}
