/**
* Given the field name, operator, value & its data type
* builds the where Clause for the query
* used for handling 'IS NULL'/'IS NOT NULL' operators
*
* @param string $field
* Fieldname.
* @param string $op
* Operator.
* @param string $value
* Value.
* @param string $dataType
* Data type of the field.
*
* @return string
* Where clause for the query.
*/
public static function buildClause($field, $op, $value = NULL, $dataType = NULL)
{
$op = trim($op);
$clause = "{$field} {$op}";
switch ($op) {
case 'IS NULL':
case 'IS NOT NULL':
return $clause;
case 'IS EMPTY':
$clause = " (NULLIF({$field}, '') IS NULL) ";
return $clause;
case 'IS NOT EMPTY':
$clause = " (NULLIF({$field}, '') IS NOT NULL) ";
return $clause;
case 'IN':
case 'NOT IN':
// I feel like this would be escaped properly if passed through $queryString = CRM_Core_DAO::createSqlFilter.
if (!empty($value) && is_array($value) && !array_key_exists($op, $value)) {
$value = array($op => $value);
}
default:
if (empty($dataType)) {
$dataType = 'String';
}
if (is_array($value)) {
//this could have come from the api - as in the restWhere section we potentially use the api operator syntax which is becoming more
// widely used and consistent across the codebase
// adding this here won't accept the search functions which don't submit an array
if (($queryString = CRM_Core_DAO::createSqlFilter($field, $value, $dataType)) != FALSE) {
return $queryString;
}
// This is the here-be-dragons zone. We have no other hopes left for an array so lets assume it 'should' be array('IN' => array(2,5))
// but we got only array(2,5) from the form.
// We could get away with keeping this in 4.6 if we make it such that it throws an enotice in 4.7 so
// people have to de-slopify it.
if (!empty($value[0])) {
$dragonPlace = $iAmAnIntentionalENoticeThatWarnsOfAProblemYouShouldReport;
if (($queryString = CRM_Core_DAO::createSqlFilter($field, array($op => $value), $dataType)) != FALSE) {
return $queryString;
}
} else {
$op = 'IN';
$dragonPlace = $iAmAnIntentionalENoticeThatWarnsOfAProblemYouShouldReportUsingOldFormat;
if (($queryString = CRM_Core_DAO::createSqlFilter($field, array($op => array_keys($value)), $dataType)) != FALSE) {
return $queryString;
}
}
}
$value = CRM_Utils_Type::escape($value, $dataType);
// if we don't have a dataType we should assume
if ($dataType == 'String' || $dataType == 'Text') {
$value = "'" . strtolower($value) . "'";
}
return "{$clause} {$value}";
}
}
/**
* Given the field name, operator, value & its data type
* builds the where Clause for the query
* used for handling 'IS NULL'/'IS NOT NULL' operators
*
* @param string $field
* Fieldname.
* @param string $op
* Operator.
* @param string $value
* Value.
* @param string $dataType
* Data type of the field.
*
* @return string
* Where clause for the query.
*/
public static function buildClause($field, $op, $value = NULL, $dataType = NULL)
{
$op = trim($op);
$clause = "{$field} {$op}";
switch ($op) {
case 'IS NULL':
case 'IS NOT NULL':
return $clause;
case 'IS EMPTY':
$clause = " (NULLIF({$field}, '') IS NULL) ";
return $clause;
case 'IS NOT EMPTY':
$clause = " (NULLIF({$field}, '') IS NOT NULL) ";
return $clause;
case 'IN':
case 'NOT IN':
if (isset($dataType)) {
if (is_array($value)) {
$values = $value;
} else {
$value = CRM_Utils_Type::escape($value, "String");
$values = explode(',', CRM_Utils_Array::value(0, explode(')', CRM_Utils_Array::value(1, explode('(', $value)))));
}
// supporting multiple values in IN clause
$val = array();
foreach ($values as $v) {
$v = trim($v);
$val[] = "'" . CRM_Utils_Type::escape($v, $dataType) . "'";
}
$value = "(" . implode($val, ",") . ")";
}
return "{$clause} {$value}";
default:
if (empty($dataType)) {
$dataType = 'String';
}
if (is_array($value)) {
//this could have come from the api - as in the restWhere section we potentially use the api operator syntax which is becoming more
// widely used and consistent across the codebase
// adding this here won't accept the search functions which don't submit an array
if (($queryString = CRM_Core_DAO::createSqlFilter($field, $value, $dataType)) != FALSE) {
return $queryString;
}
}
$value = CRM_Utils_Type::escape($value, $dataType);
// if we don't have a dataType we should assume
if ($dataType == 'String' || $dataType == 'Text') {
$value = "'" . strtolower($value) . "'";
}
return "{$clause} {$value}";
}
}
/**
* Given the field name, operator, value & its data type
* builds the where Clause for the query
* used for handling 'IS NULL'/'IS NOT NULL' operators
*
* @param string $field
* Fieldname.
* @param string $op
* Operator.
* @param string $value
* Value.
* @param string $dataType
* Data type of the field.
*
* @return string
* Where clause for the query.
*/
public static function buildClause($field, $op, $value = NULL, $dataType = NULL)
{
$op = trim($op);
$clause = "{$field} {$op}";
switch ($op) {
case 'IS NULL':
case 'IS NOT NULL':
return $clause;
case 'IS EMPTY':
$clause = " (NULLIF({$field}, '') IS NULL) ";
return $clause;
case 'IS NOT EMPTY':
$clause = " (NULLIF({$field}, '') IS NOT NULL) ";
return $clause;
default:
if (empty($dataType)) {
$dataType = 'String';
}
if (is_array($value)) {
//this could have come from the api - as in the restWhere section we potentially use the api operator syntax which is becoming more
// widely used and consistent across the codebase
// adding this here won't accept the search functions which don't submit an array
if (($queryString = CRM_Core_DAO::createSqlFilter($field, $value, $dataType)) != FALSE) {
return $queryString;
}
}
$value = CRM_Utils_Type::escape($value, $dataType);
// if we don't have a dataType we should assume
if ($dataType == 'String' || $dataType == 'Text') {
$value = "'" . strtolower($value) . "'";
}
return "{$clause} {$value}";
}
}
/**
* Function transfers the filters being passed into the DAO onto the params object.
*
* @param CRM_Core_DAO $dao
* @param array $params
* @param bool $unique
*
* @throws API_Exception
* @throws Exception
*/
function _civicrm_api3_dao_set_filter(&$dao, $params, $unique = TRUE)
{
$entity = _civicrm_api_get_entity_name_from_dao($dao);
$lowercase_entity = _civicrm_api_get_entity_name_from_camel($entity);
if (!empty($params[$lowercase_entity . "_id"]) && empty($params['id'])) {
//if entity_id is set then treat it as ID (will be overridden by id if set)
$params['id'] = $params[$lowercase_entity . "_id"];
}
$allfields = _civicrm_api3_build_fields_array($dao, $unique);
$fields = array_intersect(array_keys($allfields), array_keys($params));
$options = _civicrm_api3_get_options_from_params($params);
//apply options like sort
_civicrm_api3_apply_options_to_dao($params, $dao, $entity);
//accept filters like filter.activity_date_time_high
// std is now 'filters' => ..
if (strstr(implode(',', array_keys($params)), 'filter')) {
if (isset($params['filters']) && is_array($params['filters'])) {
foreach ($params['filters'] as $paramkey => $paramvalue) {
_civicrm_api3_apply_filters_to_dao($paramkey, $paramvalue, $dao);
}
} else {
foreach ($params as $paramkey => $paramvalue) {
if (strstr($paramkey, 'filter')) {
_civicrm_api3_apply_filters_to_dao(substr($paramkey, 7), $paramvalue, $dao);
}
}
}
}
if (!$fields) {
$fields = array();
}
foreach ($fields as $field) {
if (is_array($params[$field])) {
//get the actual fieldname from db
$fieldName = $allfields[$field]['name'];
$where = CRM_Core_DAO::createSqlFilter($fieldName, $params[$field], 'String');
if (!empty($where)) {
$dao->whereAdd($where);
}
} else {
if ($unique) {
$daoFieldName = $allfields[$field]['name'];
if (empty($daoFieldName)) {
throw new API_Exception("Failed to determine field name for \"{$field}\"");
}
$dao->{$daoFieldName} = $params[$field];
} else {
$dao->{$field} = $params[$field];
}
}
}
if (!empty($options['return']) && is_array($options['return']) && empty($options['is_count'])) {
$dao->selectAdd();
// Ensure 'id' is included.
$options['return']['id'] = TRUE;
$allfields = _civicrm_api3_get_unique_name_array($dao);
$returnMatched = array_intersect(array_keys($options['return']), $allfields);
foreach ($returnMatched as $returnValue) {
$dao->selectAdd($returnValue);
}
// Not already matched on the field names.
$unmatchedFields = array_diff(array_keys($options['return']), $returnMatched);
$returnUniqueMatched = array_intersect($unmatchedFields, array_flip($allfields));
foreach ($returnUniqueMatched as $uniqueVal) {
$dao->selectAdd($allfields[$uniqueVal]);
}
}
$dao->setApiFilter($params);
}
/**
* Function transfers the filters being passed into the DAO onto the params object
*/
function _civicrm_api3_dao_set_filter(&$dao, $params, $unique = TRUE, $entity)
{
$entity = substr($dao->__table, 8);
$allfields = _civicrm_api3_build_fields_array($dao, $unique);
$fields = array_intersect(array_keys($allfields), array_keys($params));
if (isset($params[$entity . "_id"])) {
//if entity_id is set then treat it as ID (will be overridden by id if set)
$dao->id = $params[$entity . "_id"];
}
$options = _civicrm_api3_get_options_from_params($params);
//apply options like sort
_civicrm_api3_apply_options_to_dao($params, $dao, $entity);
//accept filters like filter.activity_date_time_high
// std is now 'filters' => ..
if (strstr(implode(',', array_keys($params)), 'filter')) {
if (isset($params['filters']) && is_array($params['filters'])) {
foreach ($params['filters'] as $paramkey => $paramvalue) {
_civicrm_api3_apply_filters_to_dao($paramkey, $paramvalue, $dao);
}
} else {
foreach ($params as $paramkey => $paramvalue) {
if (strstr($paramkey, 'filter')) {
_civicrm_api3_apply_filters_to_dao(substr($paramkey, 7), $paramvalue, $dao);
}
}
}
}
// http://issues.civicrm.org/jira/browse/CRM-9150 - stick with 'simple' operators for now
// support for other syntaxes is discussed in ticket but being put off for now
$acceptedSQLOperators = array('=', '<=', '>=', '>', '<', 'LIKE', "<>", "!=", "NOT LIKE", 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN');
if (!$fields) {
$fields = array();
}
foreach ($fields as $field) {
if (is_array($params[$field])) {
//get the actual fieldname from db
$fieldName = $allfields[$field]['name'];
$where = CRM_Core_DAO::createSqlFilter($fieldName, $params[$field], 'String');
if (!empty($where)) {
$dao->whereAdd($where);
}
} else {
if ($unique) {
$daoFieldName = $allfields[$field]['name'];
if (empty($daoFieldName)) {
throw new API_Exception("Failed to determine field name for \"{$field}\"");
}
$dao->{$daoFieldName} = $params[$field];
} else {
$dao->{$field} = $params[$field];
}
}
}
if (!empty($options['return']) && is_array($options['return']) && empty($options['is_count'])) {
$dao->selectAdd();
$options['return']['id'] = TRUE;
// ensure 'id' is included
$allfields = _civicrm_api3_get_unique_name_array($dao);
$returnMatched = array_intersect(array_keys($options['return']), $allfields);
foreach ($returnMatched as $returnValue) {
$dao->selectAdd($returnValue);
}
$unmatchedFields = array_diff(array_keys($options['return']), $returnMatched);
$returnUniqueMatched = array_intersect($unmatchedFields, array_flip($allfields));
foreach ($returnUniqueMatched as $uniqueVal) {
$dao->selectAdd($allfields[$uniqueVal]);
}
}
$dao->setApiFilter($params);
}
