PHP CRM_Core_DAO::createSqlFilter方法代码示例

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