/**
* Get field search SQL request (used by class CMS_object_search)
*
* @param integer $fieldID : this field id in object (aka $this->_field->getID())
* @param mixed $value : the value to search
* @param string $operator : additionnal search operator
* @param string $where : where clauses to add to SQL
* @param boolean $public : values are public or edited ? (default is edited)
* @return string : the SQL request
* @access public
*/
function getFieldSearchSQL($fieldID, $value, $operator, $where, $public = false)
{
$supportedOperator = array('<', '<=', '>', '>=');
if ($operator && !in_array($operator, $supportedOperator)) {
$this->_raiseError(get_class($this) . " : getFieldSearchSQL : unkown search operator : " . $operator . ", use default search instead");
$operator = false;
}
if (!$operator) {
return parent::getFieldSearchSQL($fieldID, $value, $operator, $where, $public);
}
$statusSuffix = $public ? "_public" : "_edited";
$sql = "\n\t\t\tselect\n\t\t\t\tdistinct objectID\n\t\t\tfrom\n\t\t\t\tmod_subobject_integer" . $statusSuffix . "\n\t\t\twhere\n\t\t\t\tobjectFieldID = '" . SensitiveIO::sanitizeSQLString($fieldID) . "'\n\t\t\t\tand value " . $operator . " '" . SensitiveIO::sanitizeSQLString($value) . "'\n\t\t\t\t{$where}";
return $sql;
}
/**
* Get field search SQL request (used by class CMS_object_search)
*
* @param integer $fieldID : this field id in object (aka $this->_field->getID())
* @param mixed $value : the value to search
* @param string $operator : additionnal search operator
* @param string $where : where clauses to add to SQL
* @param boolean $public : values are public or edited ? (default is edited)
* @return string : the SQL request
* @access public
*/
function getFieldSearchSQL($fieldID, $value, $operator, $where, $public = false)
{
$supportedOperator = array('like', '!=', '=', 'any', 'all', 'phrase', 'beginswith');
$supportedOperatorForArray = array('in', 'not in', 'any', 'all');
// No operator : use default search
if (!$operator) {
return parent::getFieldSearchSQL($fieldID, $value, $operator, $where, $public);
}
// Check supported operators
if ($operator && !in_array($operator, array_merge($supportedOperator, $supportedOperatorForArray))) {
$this->raiseError("Unknown search operator : " . $operator . ", use default search instead");
$operator = false;
}
// Check operators for array value
if (is_array($value) && $operator && !in_array($operator, $supportedOperatorForArray)) {
$this->raiseError("Can't use this operator : " . $operator . " with an array value, return empty sql");
return '';
}
$statusSuffix = $public ? "_public" : "_edited";
$cleanedWords = array();
if (is_array($value)) {
if ($operator == 'any' || $operator == 'all') {
// in this case, we do a specific cleanup
foreach ($value as $i => $val) {
$cleanedWords[] = str_replace(array('%', '_'), array('\\%', '\\_'), $val);
}
} else {
foreach ($value as $i => $val) {
$value[$i] = "'" . SensitiveIO::sanitizeSQLString($val) . "'";
}
$value = '(' . implode(',', $value) . ')';
}
} elseif (strtolower($value) == 'null') {
$value = "''";
} else {
if ($operator == 'any' || $operator == 'all') {
$words = array();
$words = array_map("trim", array_unique(explode(" ", $value)));
foreach ($words as $aWord) {
if ($aWord && $aWord != '' && io::strlen($aWord) >= 3) {
$aWord = str_replace(array('%', '_'), array('\\%', '\\_'), $aWord);
$cleanedWords[] = $aWord;
}
}
} elseif ($operator != 'phrase' && $operator != 'beginswith') {
// we keep this for backward compatibility, where the user can specify his search with % at the beginning / end
$value = "'" . SensitiveIO::sanitizeSQLString($value) . "'";
}
}
$whereClause = '';
switch ($operator) {
case 'any':
$whereClause .= '(';
//then add keywords
$count = '0';
foreach ($cleanedWords as $aWord) {
$whereClause .= $count ? ' or ' : '';
$count++;
$whereClause .= "value like '%" . $aWord . "%'";
if (htmlentities($aWord) != $aWord) {
$whereClause .= " or value like '%" . htmlentities($aWord) . "%'";
}
}
$whereClause .= ')';
break;
case 'all':
$whereClause .= '(';
//then add keywords
$count = '0';
foreach ($cleanedWords as $aWord) {
$whereClause .= $count ? ' and ' : '';
$count++;
if (htmlentities($aWord) != $aWord) {
$whereClause .= "(value like '%" . $aWord . "%' or value like '%" . htmlentities($aWord) . "%')";
} else {
$whereClause .= "value like '%" . $aWord . "%'";
}
}
$whereClause .= ')';
break;
case 'phrase':
$value = str_replace(array('%', '_'), array('\\%', '\\_'), trim($value));
if (htmlentities($value) != $value) {
$whereClause .= "(value like '%" . $value . "%' or value like '%" . htmlentities($value) . "%')";
} else {
$whereClause .= "value like '%" . $value . "%'";
}
break;
case 'beginswith':
$value = str_replace(array('%', '_'), array('\\%', '\\_'), trim($value));
if (htmlentities($value) != $value) {
$whereClause .= "(value like '" . $value . "%' or value like '" . htmlentities($value) . "%')";
} else {
$whereClause .= "value like '" . $value . "%'";
}
break;
default:
$whereClause .= " value " . $operator . " " . $value;
break;
}
$sql = "\n\t\t\tselect\n\t\t\t\tdistinct objectID\n\t\t\tfrom\n\t\t\t\tmod_subobject_text" . $statusSuffix . "\n\t\t\twhere\n\t\t\t\tobjectFieldID = '" . SensitiveIO::sanitizeSQLString($fieldID) . "'\n\t\t\t\tand " . $whereClause . "\n\t\t\t\t{$where}";
return $sql;
}
/**
* Get field search SQL request (used by class CMS_object_search)
*
* @param integer $fieldID : this field id in object (aka $this->_field->getID())
* @param mixed $value : the value to search
* @param string $operator : additionnal search operator
* @param string $where : where clauses to add to SQL
* @param boolean $public : values are public or edited ? (default is edited)
* @return string : the SQL request
* @access public
*/
function getFieldSearchSQL($fieldID, $value, $operator, $where, $public = false)
{
$supportedOperator = array('>=', '<=', '>', '<', '>= or null', '<= or null', '> or null', '< or null', '>= and not null', '<= and not null', '> and not null', '< and not null', 'beginswith');
if ($operator && !in_array($operator, $supportedOperator)) {
$this->raiseError("Unknown search operator : " . $operator . ", use default search instead");
$operator = false;
}
if (!$operator) {
return parent::getFieldSearchSQL($fieldID, $value, $operator, $where, $public);
}
// canBeNull
$operators = explode('or', $operator);
$operator = trim($operators[0]);
$canBeNull = isset($operators[1]) ? ' or value is NULL' : '';
// cantBeNull
$operators = explode('and', $operator);
$operator = trim($operators[0]);
$cantBeNull = isset($operators[1]) ? ' and value is not NULL and value != \'0000-00-00\' and value != \'0000-00-00 00:00:00\'' : '';
$statusSuffix = $public ? "_public" : "_edited";
$whereClause = '';
if ($operator == 'beginswith') {
global $cms_language;
$dateFormat = $cms_language->getDateFormat();
$dateFormatSql = str_replace(array('D', 'M', 'n', 'jS', 'd', 'j', 'u', 'H', 'h', 'g', 'i', 'z', 'G', 'g', 'F', 'm', 'A', 's', 's', 'W', 'l', 'w', 'Y', 'y'), array('%a', '%b', '%c', '%D', '%d', '%e', '%f', '%H', '%h', '%I', '%i', '%j', '%k', '%l', '%M', '%m', '%p', '%S', '%s', '%u', '%W', '%w', '%Y', '%y'), $dateFormat);
$whereClause = "(DATE_FORMAT(value,'" . $dateFormatSql . "') like '" . SensitiveIO::sanitizeSQLString($value) . "%')";
} else {
$whereClause = "(value " . $operator . " '" . SensitiveIO::sanitizeSQLString($value) . "'" . $canBeNull . $cantBeNull . ")";
}
$sql = "\n\t\t\tselect\n\t\t\t\tdistinct objectID\n\t\t\tfrom\n\t\t\t\tmod_subobject_date" . $statusSuffix . "\n\t\t\twhere\n\t\t\t\tobjectFieldID = '" . SensitiveIO::sanitizeSQLString($fieldID) . "'\n\t\t\t\tand " . $whereClause . "\n\t\t\t\t{$where}";
return $sql;
}
