/** * 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); }