/**
* Build & execute the query and return results array
*
* @return array
* @throws \API_Exception
* @throws \CRM_Core_Exception
* @throws \Exception
*/
public function run()
{
$this->buildSelectFields();
$this->buildWhereClause();
if (in_array('count_rows', $this->select)) {
$this->query->select("count(*) as c");
} else {
foreach ($this->selectFields as $column => $alias) {
$this->query->select("{$column} as `{$alias}`");
}
// Order by
$this->buildOrderBy();
}
// Limit
if (!empty($this->limit) || !empty($this->offset)) {
$this->query->limit($this->limit, $this->offset);
}
$result_entities = array();
$result_dao = \CRM_Core_DAO::executeQuery($this->query->toSQL());
while ($result_dao->fetch()) {
if (in_array('count_rows', $this->select)) {
$result_dao->free();
return (int) $result_dao->c;
}
$result_entities[$result_dao->id] = array();
foreach ($this->selectFields as $column => $alias) {
$returnName = $alias;
$alias = str_replace('.', '_', $alias);
if (property_exists($result_dao, $alias) && $result_dao->{$alias} != NULL) {
$result_entities[$result_dao->id][$returnName] = $result_dao->{$alias};
}
// Backward compatibility on fields names.
if ($this->isFillUniqueFields && !empty($this->apiFieldSpec[$alias]['uniqueName'])) {
$result_entities[$result_dao->id][$this->apiFieldSpec[$alias]['uniqueName']] = $result_dao->{$alias};
}
foreach ($this->apiFieldSpec as $returnName => $spec) {
if (empty($result_entities[$result_dao->id][$returnName]) && !empty($result_entities[$result_dao->id][$spec['name']])) {
$result_entities[$result_dao->id][$returnName] = $result_entities[$result_dao->id][$spec['name']];
}
}
}
}
$result_dao->free();
return $result_entities;
}
/**
* Build & execute the query and return results array
*
* @return array
* @throws \API_Exception
* @throws \CRM_Core_Exception
* @throws \Exception
*/
public function run()
{
// $select_fields maps column names to the field names of the result values.
$select_fields = $custom_fields = array();
// populate $select_fields
$return_all_fields = empty($this->options['return']) || !is_array($this->options['return']);
$return = $return_all_fields ? array_fill_keys($this->entityFieldNames, 1) : $this->options['return'];
// core return fields
foreach ($return as $field_name => $include) {
if ($include) {
$field = $this->getField($field_name);
if ($field && in_array($field['name'], $this->entityFieldNames)) {
// 'a.' is an alias for the entity table.
$select_fields["a.{$field['name']}"] = $field['name'];
} elseif ($include && strpos($field_name, '.')) {
$fkField = $this->addFkField($field_name);
if ($fkField) {
$select_fields[implode('.', $fkField)] = $field_name;
}
}
}
}
// Do custom fields IF the params contain the word "custom" or we are returning *
if ($return_all_fields || strpos(json_encode($this->params), 'custom')) {
$custom_fields = _civicrm_api3_custom_fields_for_entity($this->entity);
foreach ($custom_fields as $cf_id => $custom_field) {
$field_name = "custom_{$cf_id}";
if ($return_all_fields || !empty($this->options['return'][$field_name]) || !empty($this->options['return']['custom'])) {
list($table_name, $column_name) = $this->addCustomField($custom_field);
if ($custom_field["data_type"] != "ContactReference") {
// 'ordinary' custom field. We will select the value as custom_XX.
$select_fields["{$table_name}.{$column_name}"] = $field_name;
} else {
// contact reference custom field. The ID will be stored in custom_XX_id.
// custom_XX will contain the sort name of the contact.
$this->query->join("c_{$cf_id}", "LEFT JOIN civicrm_contact c_{$cf_id} ON c_{$cf_id}.id = `{$table_name}`.`{$column_name}`");
$select_fields["{$table_name}.{$column_name}"] = $field_name . "_id";
// We will call the contact table for the join c_XX.
$select_fields["c_{$cf_id}.sort_name"] = $field_name;
}
}
}
}
// Always select the ID.
$select_fields["a.id"] = "id";
// populate where_clauses
foreach ($this->params as $key => $value) {
$table_name = NULL;
$column_name = NULL;
if (substr($key, 0, 7) == 'filter.') {
// Legacy support for old filter syntax per the test contract.
// (Convert the style to the later one & then deal with them).
$filterArray = explode('.', $key);
$value = array($filterArray[1] => $value);
$key = 'filters';
}
// Legacy support for 'filter's construct.
if ($key == 'filters') {
foreach ($value as $filterKey => $filterValue) {
if (substr($filterKey, -4, 4) == 'high') {
$key = substr($filterKey, 0, -5);
$value = array('<=' => $filterValue);
}
if (substr($filterKey, -3, 3) == 'low') {
$key = substr($filterKey, 0, -4);
$value = array('>=' => $filterValue);
}
if ($filterKey == 'is_current' || $filterKey == 'isCurrent') {
// Is current is almost worth creating as a 'sql filter' in the DAO function since several entities have the
// concept.
$todayStart = date('Ymd000000', strtotime('now'));
$todayEnd = date('Ymd235959', strtotime('now'));
$this->query->where(array("(a.start_date <= '{$todayStart}' OR a.start_date IS NULL) AND (a.end_date >= '{$todayEnd}' OR\n a.end_date IS NULL)\n AND a.is_active = 1\n "));
}
}
}
// Ignore the "options" param if it is referring to api options and not a field in this entity
if ($key === 'options' && is_array($value) && !in_array(\CRM_Utils_Array::first(array_keys($value)), \CRM_Core_DAO::acceptedSQLOperators())) {
continue;
}
$field = $this->getField($key);
if ($field) {
$key = $field['name'];
}
if (in_array($key, $this->entityFieldNames)) {
$table_name = 'a';
$column_name = $key;
} elseif (($cf_id = \CRM_Core_BAO_CustomField::getKeyID($key)) != FALSE) {
list($table_name, $column_name) = $this->addCustomField($custom_fields[$cf_id]);
} elseif (strpos($key, '.')) {
$fkInfo = $this->addFkField($key);
if ($fkInfo) {
list($table_name, $column_name) = $fkInfo;
$this->validateNestedInput($key, $value);
}
}
// I don't know why I had to specifically exclude 0 as a key - wouldn't the others have caught it?
// We normally silently ignore null values passed in - if people want IS_NULL they can use acceptedSqlOperator syntax.
if (!$table_name || empty($key) || is_null($value)) {
// No valid filter field. This might be a chained call or something.
// Just ignore this for the $where_clause.
continue;
}
if (!is_array($value)) {
$this->query->where(array("`{$table_name}`.`{$column_name}` = @value"), array("@value" => $value));
} else {
// We expect only one element in the array, of the form
// "operator" => "rhs".
$operator = \CRM_Utils_Array::first(array_keys($value));
if (!in_array($operator, \CRM_Core_DAO::acceptedSQLOperators())) {
$this->query->where(array("{$table_name}.{$column_name} = @value"), array("@value" => $value));
} else {
$this->query->where(\CRM_Core_DAO::createSQLFilter("{$table_name}.{$column_name}", $value));
}
}
}
if (!$this->options['is_count']) {
foreach ($select_fields as $column => $alias) {
$this->query->select("{$column} as `{$alias}`");
}
} else {
$this->query->select("count(*) as c");
}
// order by
if (!empty($this->options['sort'])) {
$sort_fields = array();
foreach (explode(',', $this->options['sort']) as $sort_option) {
$words = preg_split("/[\\s]+/", $sort_option);
if (count($words) > 0 && in_array($words[0], array_values($select_fields))) {
$tmp = $words[0];
if (!empty($words[1]) && strtoupper($words[1]) == 'DESC') {
$tmp .= " DESC";
}
$sort_fields[] = $tmp;
}
}
if (count($sort_fields) > 0) {
$this->query->orderBy(implode(",", $sort_fields));
}
}
// limit
if (!empty($this->options['limit']) || !empty($this->options['offset'])) {
$this->query->limit($this->options['limit'], $this->options['offset']);
}
// ACLs
$this->query->where($this->getAclClause('a'));
$this->bao->free();
$result_entities = array();
$result_dao = \CRM_Core_DAO::executeQuery($this->query->toSQL());
while ($result_dao->fetch()) {
if ($this->options['is_count']) {
$result_dao->free();
return (int) $result_dao->c;
}
$result_entities[$result_dao->id] = array();
foreach ($select_fields as $column => $alias) {
$returnName = $alias;
$alias = str_replace('.', '_', $alias);
if (property_exists($result_dao, $alias) && $result_dao->{$alias} != NULL) {
$result_entities[$result_dao->id][$returnName] = $result_dao->{$alias};
}
// Backward compatibility on fields names.
if ($this->isFillUniqueFields && !empty($this->apiFieldSpec[$alias]['uniqueName'])) {
$result_entities[$result_dao->id][$this->apiFieldSpec[$alias]['uniqueName']] = $result_dao->{$alias};
}
foreach ($this->apiFieldSpec as $returnName => $spec) {
if (empty($result_entities[$result_dao->id][$returnName]) && !empty($result_entities[$result_dao->id][$spec['name']])) {
$result_entities[$result_dao->id][$returnName] = $result_entities[$result_dao->id][$spec['name']];
}
}
}
}
$result_dao->free();
return $result_entities;
}
