/**
* @inheritDoc
*/
protected function buildWhereClause()
{
foreach ($this->where as $key => $value) {
$table_name = NULL;
$column_name = NULL;
$field = $this->getField($key);
if (in_array($key, $this->entityFieldNames)) {
$table_name = self::MAIN_TABLE_ALIAS;
$column_name = $key;
} elseif (($cf_id = \CRM_Core_BAO_CustomField::getKeyID($key)) != FALSE) {
//list($table_name, $column_name) = $this->addCustomField($this->apiFieldSpec['custom_' . $cf_id], 'INNER');
} elseif (strpos($key, '.')) {
$fkInfo = $this->addFkField($key, 'INNER');
if ($fkInfo) {
list($table_name, $column_name) = $fkInfo;
$this->validateNestedInput($key, $value);
}
}
if (!$table_name || !$column_name || is_null($value)) {
throw new \API_Exception("Invalid field '{$key}' in where clause.");
}
if (!is_array($value)) {
$this->query->where(array("`{$table_name}`.`{$column_name}` = @value"), array("@value" => $value));
} elseif (count($value) !== 1) {
throw new \API_Exception("Invalid value in where clause for field '{$key}'");
} else {
$clause = \CRM_Core_DAO::createSQLFilter("`{$table_name}`.`{$column_name}`", $value);
if ($clause === NULL) {
throw new \API_Exception("Invalid value in where clause for field '{$key}'");
}
$this->query->where($clause);
}
}
}
/**
* Helper function to form the sql for relationship retrieval.
*
* @param int $contactId
* Contact id.
* @param int $status
* (check const at top of file).
* @param int $numRelationship
* No of relationships to display (limit).
* @param int $count
* Get the no of relationships.
* $param int $relationshipId relationship id
* @param int $relationshipId
* @param string $direction
* The direction we are interested in a_b or b_a.
* @param array $params
* Array of extra values including relationship_type_id per api spec.
*
* @return array
* [select, from, where]
*/
public static function makeURLClause($contactId, $status, $numRelationship, $count, $relationshipId, $direction, $params = array())
{
$select = $from = $where = '';
$select = '( ';
if ($count) {
if ($direction == 'a_b') {
$select .= ' SELECT count(DISTINCT civicrm_relationship.id) as cnt1, 0 as cnt2 ';
} else {
$select .= ' SELECT 0 as cnt1, count(DISTINCT civicrm_relationship.id) as cnt2 ';
}
} else {
$select .= ' SELECT civicrm_relationship.id as civicrm_relationship_id,
civicrm_contact.sort_name as sort_name,
civicrm_contact.display_name as display_name,
civicrm_contact.job_title as job_title,
civicrm_contact.employer_id as employer_id,
civicrm_contact.organization_name as organization_name,
civicrm_address.street_address as street_address,
civicrm_address.city as city,
civicrm_address.postal_code as postal_code,
civicrm_state_province.abbreviation as state,
civicrm_country.name as country,
civicrm_email.email as email,
civicrm_contact.contact_type as contact_type,
civicrm_phone.phone as phone,
civicrm_contact.id as civicrm_contact_id,
civicrm_relationship.contact_id_b as contact_id_b,
civicrm_relationship.contact_id_a as contact_id_a,
civicrm_relationship_type.id as civicrm_relationship_type_id,
civicrm_relationship.start_date as start_date,
civicrm_relationship.end_date as end_date,
civicrm_relationship.description as description,
civicrm_relationship.is_active as is_active,
civicrm_relationship.is_permission_a_b as is_permission_a_b,
civicrm_relationship.is_permission_b_a as is_permission_b_a,
civicrm_relationship.case_id as case_id';
if ($direction == 'a_b') {
$select .= ', civicrm_relationship_type.label_a_b as label_a_b,
civicrm_relationship_type.label_b_a as relation ';
} else {
$select .= ', civicrm_relationship_type.label_a_b as label_a_b,
civicrm_relationship_type.label_a_b as relation ';
}
}
$from = "\n FROM civicrm_relationship\nINNER JOIN civicrm_relationship_type ON ( civicrm_relationship.relationship_type_id = civicrm_relationship_type.id )\nINNER JOIN civicrm_contact ";
if ($direction == 'a_b') {
$from .= 'ON ( civicrm_contact.id = civicrm_relationship.contact_id_a ) ';
} else {
$from .= 'ON ( civicrm_contact.id = civicrm_relationship.contact_id_b ) ';
}
$from .= "\nLEFT JOIN civicrm_address ON (civicrm_address.contact_id = civicrm_contact.id AND civicrm_address.is_primary = 1)\nLEFT JOIN civicrm_phone ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary = 1)\nLEFT JOIN civicrm_email ON (civicrm_email.contact_id = civicrm_contact.id AND civicrm_email.is_primary = 1)\nLEFT JOIN civicrm_state_province ON (civicrm_address.state_province_id = civicrm_state_province.id)\nLEFT JOIN civicrm_country ON (civicrm_address.country_id = civicrm_country.id)\n";
$where = 'WHERE ( 1 )';
if ($contactId) {
if ($direction == 'a_b') {
$where .= ' AND civicrm_relationship.contact_id_b = ' . CRM_Utils_Type::escape($contactId, 'Positive');
} else {
$where .= ' AND civicrm_relationship.contact_id_a = ' . CRM_Utils_Type::escape($contactId, 'Positive') . '
AND civicrm_relationship.contact_id_a != civicrm_relationship.contact_id_b ';
}
}
if ($relationshipId) {
$where .= ' AND civicrm_relationship.id = ' . CRM_Utils_Type::escape($relationshipId, 'Positive');
}
$date = date('Y-m-d');
if ($status == self::PAST) {
//this case for showing past relationship
$where .= ' AND civicrm_relationship.is_active = 1 ';
$where .= " AND civicrm_relationship.end_date < '" . $date . "'";
} elseif ($status == self::DISABLED) {
// this case for showing disabled relationship
$where .= ' AND civicrm_relationship.is_active = 0 ';
} elseif ($status == self::CURRENT) {
//this case for showing current relationship
$where .= ' AND civicrm_relationship.is_active = 1 ';
$where .= " AND (civicrm_relationship.end_date >= '" . $date . "' OR civicrm_relationship.end_date IS NULL) ";
} elseif ($status == self::INACTIVE) {
//this case for showing inactive relationships
$where .= " AND (civicrm_relationship.end_date < '" . $date . "'";
$where .= ' OR civicrm_relationship.is_active = 0 )';
}
// CRM-6181
$where .= ' AND civicrm_contact.is_deleted = 0';
if (!empty($params['membership_type_id']) && empty($params['relationship_type_id'])) {
$where .= self::membershipTypeToRelationshipTypes($params, $direction);
}
if (!empty($params['relationship_type_id'])) {
if (is_array($params['relationship_type_id'])) {
$where .= " AND " . CRM_Core_DAO::createSQLFilter('relationship_type_id', $params['relationship_type_id'], 'Integer');
} else {
$where .= ' AND relationship_type_id = ' . CRM_Utils_Type::escape($params['relationship_type_id'], 'Positive');
}
}
if ($direction == 'a_b') {
$where .= ' ) UNION ';
} else {
$where .= ' ) ';
}
return array($select, $from, $where);
}
/**
* Generate where clause for any parameters not already handled.
*
* @param array $values
*
* @throws Exception
*/
public function restWhere(&$values)
{
$name = CRM_Utils_Array::value(0, $values);
$op = CRM_Utils_Array::value(1, $values);
$value = CRM_Utils_Array::value(2, $values);
$grouping = CRM_Utils_Array::value(3, $values);
$wildcard = CRM_Utils_Array::value(4, $values);
if (isset($grouping) && empty($this->_where[$grouping])) {
$this->_where[$grouping] = array();
}
$multipleFields = array('url');
//check if the location type exits for fields
$lType = '';
$locType = explode('-', $name);
if (!in_array($locType[0], $multipleFields)) {
//add phone type if exists
if (isset($locType[2]) && $locType[2]) {
$locType[2] = CRM_Core_DAO::escapeString($locType[2]);
}
}
$field = CRM_Utils_Array::value($name, $this->_fields);
if (!$field) {
$field = CRM_Utils_Array::value($locType[0], $this->_fields);
if (!$field) {
return;
}
}
$setTables = TRUE;
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
$locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
if (substr($name, 0, 14) === 'state_province') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.state_province_id";
} else {
$where = "civicrm_address.state_province_id";
}
$this->_where[$grouping][] = self::buildClause($where, $op, $value);
list($qillop, $qillVal) = self::buildQillForFieldValue('CRM_Core_DAO_Address', "state_province_id", $value, $op);
$this->_qill[$grouping][] = ts("State %1 %2", array(1 => $qillop, 2 => $qillVal));
} elseif (!empty($field['pseudoconstant'])) {
$this->optionValueQuery($name, $op, $value, $grouping, 'CRM_Contact_DAO_Contact', $field, $field['title'], 'String', TRUE);
if ($name == 'gender_id') {
self::$_openedPanes[ts('Demographics')] = TRUE;
}
} elseif (substr($name, 0, 7) === 'country' || substr($name, 0, 6) === 'county') {
$name = substr($name, 0, 7) === 'country' ? "country_id" : "county_id";
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.{$name}";
} else {
$where = "civicrm_address.{$name}";
}
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
if ($lType) {
$field['title'] .= " ({$lType})";
}
list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $name, $value, $op);
$this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['title'], 2 => $qillop, 3 => $qillVal));
} elseif ($name === 'world_region') {
$this->optionValueQuery($name, $op, $value, $grouping, NULL, $field, ts('World Region'), 'Positive', TRUE);
} elseif ($name === 'is_deceased') {
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
self::$_openedPanes[ts('Demographics')] = TRUE;
} elseif ($name === 'created_date' || $name === 'modified_date' || $name === 'deceased_date' || $name === 'birth_date') {
$appendDateTime = TRUE;
if ($name === 'deceased_date' || $name === 'birth_date') {
$appendDateTime = FALSE;
self::$_openedPanes[ts('Demographics')] = TRUE;
}
$this->dateQueryBuilder($values, 'contact_a', $name, $name, $field['title'], $appendDateTime);
} elseif ($name === 'contact_id') {
if (is_int($value)) {
$this->_where[$grouping][] = self::buildClause($field['where'], $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} {$value}";
}
} elseif ($name === 'name') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, "'{$value}'");
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'current_employer') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = self::caseImportant($op) ? "LOWER(contact_a.organization_name)" : "contact_a.organization_name";
$ceWhereClause = self::buildClause($wc, $op, $value);
$ceWhereClause .= " AND contact_a.contact_type = 'Individual'";
$this->_where[$grouping][] = $ceWhereClause;
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'email_greeting') {
$filterCondition = array('greeting_type' => 'email_greeting');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Email Greeting'));
} elseif ($name === 'postal_greeting') {
$filterCondition = array('greeting_type' => 'postal_greeting');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Postal Greeting'));
} elseif ($name === 'addressee') {
$filterCondition = array('greeting_type' => 'addressee');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Addressee'));
} elseif (substr($name, 0, 4) === 'url-') {
$tName = 'civicrm_website';
$this->_whereTables[$tName] = $this->_tables[$tName] = "\nLEFT JOIN civicrm_website ON ( civicrm_website.contact_id = contact_a.id )";
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = 'civicrm_website.url';
$this->_where[$grouping][] = $d = self::buildClause($wc, $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'contact_is_deleted') {
$this->_where[$grouping][] = self::buildClause("contact_a.is_deleted", $op, $value);
list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $name, $value, $op);
$this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['title'], 2 => $qillop, 3 => $qillVal));
} elseif (!empty($field['where'])) {
$type = NULL;
if (!empty($field['type'])) {
$type = CRM_Utils_Type::typeToString($field['type']);
}
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
//get the location name
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$fieldName = "LOWER(`{$tName}`.{$fldName})";
// we set both _tables & whereTables because whereTables doesn't seem to do what the name implies it should
$this->_tables[$tName] = $this->_whereTables[$tName] = 1;
} else {
if ($tableName == 'civicrm_contact') {
$fieldName = "LOWER(contact_a.{$fieldName})";
} else {
if ($op != 'IN' && !is_numeric($value)) {
$fieldName = "LOWER({$field['where']})";
} else {
$fieldName = "{$field['where']}";
}
}
}
list($qillop, $qillVal) = self::buildQillForFieldValue(NULL, $field['title'], $value, $op);
$this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $field['title'], 2 => $qillop, 3 => strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE ? $qillVal : "'{$qillVal}'"));
if (is_array($value)) {
// traditionally an array being passed has been a fatal error. We can take advantage of this to add support
// for api style operators for functions that hit this point without worrying about regression
// (the previous comments indicated the condition for hitting this point were unknown
// per CRM-14743 we are adding modified_date & created_date operator support
$operations = array_keys($value);
foreach ($operations as $operator) {
if (!in_array($operator, CRM_Core_DAO::acceptedSQLOperators())) {
//Via Contact get api value is not in array(operator => array(values)) format ONLY for IN/NOT IN operators
//so this condition will satisfy the search for now
if (strpos($op, 'IN') !== FALSE) {
$value = array($op => $value);
} else {
CRM_Core_Error::fatal(ts("%1 is not a valid operator", array(1 => $operator)));
}
}
}
$this->_where[$grouping][] = CRM_Core_DAO::createSQLFilter($fieldName, $value, $type);
} else {
if (!strpos($op, 'IN')) {
$value = $strtolower($value);
}
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$this->_where[$grouping][] = self::buildClause($fieldName, $op, $value, $type);
}
}
if ($setTables && isset($field['where'])) {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if (isset($tableName)) {
$this->_tables[$tableName] = 1;
$this->_whereTables[$tableName] = 1;
}
}
}
/**
* 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;
}
/**
* @param $values
*
* @throws Exception
*/
function restWhere(&$values)
{
$name = CRM_Utils_Array::value(0, $values);
$op = CRM_Utils_Array::value(1, $values);
$value = CRM_Utils_Array::value(2, $values);
$grouping = CRM_Utils_Array::value(3, $values);
$wildcard = CRM_Utils_Array::value(4, $values);
if (isset($grouping) && empty($this->_where[$grouping])) {
$this->_where[$grouping] = array();
}
$multipleFields = array('url');
//check if the location type exits for fields
$lType = '';
$locType = explode('-', $name);
if (!in_array($locType[0], $multipleFields)) {
//add phone type if exists
if (isset($locType[2]) && $locType[2]) {
$locType[2] = CRM_Core_DAO::escapeString($locType[2]);
}
}
$field = CRM_Utils_Array::value($name, $this->_fields);
if (!$field) {
$field = CRM_Utils_Array::value($locType[0], $this->_fields);
if (!$field) {
return;
}
}
$setTables = TRUE;
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
$locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
if (substr($name, 0, 14) === 'state_province') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.state_province_id";
} else {
$where = "civicrm_address.state_province_id";
}
$states = CRM_Core_PseudoConstant::stateProvince();
if (is_numeric($value)) {
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$value = $states[(int) $value];
} else {
$intVal = CRM_Utils_Array::key($value, $states);
$this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('State') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('State') . " ({$lType}) {$op} '{$value}'";
}
} elseif (!empty($field['pseudoconstant'])) {
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', $field['name']), $field, $field['title'], 'String', TRUE);
if ($name == 'gender_id') {
self::$_openedPanes[ts('Demographics')] = TRUE;
}
} elseif (substr($name, 0, 7) === 'country') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.country_id";
} else {
$where = "civicrm_address.country_id";
}
$countries = CRM_Core_PseudoConstant::country();
if (is_numeric($value)) {
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$value = $countries[(int) $value];
} else {
$intVal = CRM_Utils_Array::key($value, $countries);
$this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('Country') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('Country') . " ({$lType}) {$op} '{$value}'";
}
} elseif (substr($name, 0, 6) === 'county') {
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
$aName = "{$locationType[$locType[1]]}-address";
$where = "`{$aName}`.county_id";
} else {
$where = "civicrm_address.county_id";
}
$counties = CRM_Core_PseudoConstant::county();
if (is_numeric($value)) {
$this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
$value = $counties[(int) $value];
} else {
$intVal = CRM_Utils_Array::key($value, $counties);
$this->_where[$grouping][] = self::buildClause($where, $op, $intVal, 'Positive');
}
if (!$lType) {
$this->_qill[$grouping][] = ts('County') . " {$op} '{$value}'";
} else {
$this->_qill[$grouping][] = ts('County') . " ({$lType}) {$op} '{$value}'";
}
} elseif ($name === 'world_region') {
$field['where'] = 'civicrm_worldregion.id';
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::worldRegion(), $field, ts('World Region'), 'Positive', TRUE);
} elseif ($name === 'birth_date') {
$date = CRM_Utils_Date::processDate($value);
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $date);
if ($date) {
$date = CRM_Utils_Date::customFormat($date);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$date}\"";
} else {
$this->_qill[$grouping][] = "{$field['title']} {$op}";
}
self::$_openedPanes[ts('Demographics')] = TRUE;
} elseif ($name === 'deceased_date') {
$date = CRM_Utils_Date::processDate($value);
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $date);
if ($date) {
$date = CRM_Utils_Date::customFormat($date);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$date}\"";
} else {
$this->_qill[$grouping][] = "{$field['title']} {$op}";
}
self::$_openedPanes[ts('Demographics')] = TRUE;
} elseif ($name === 'is_deceased') {
$this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
self::$_openedPanes[ts('Demographics')] = TRUE;
} elseif ($name === 'contact_id') {
if (is_int($value)) {
$this->_where[$grouping][] = self::buildClause($field['where'], $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} {$value}";
}
} elseif ($name === 'name') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
$this->_where[$grouping][] = self::buildClause($wc, $op, "'{$value}'");
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'current_employer') {
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = self::caseImportant($op) ? "LOWER(contact_a.organization_name)" : "contact_a.organization_name";
$ceWhereClause = self::buildClause($wc, $op, $value);
$ceWhereClause .= " AND contact_a.contact_type = 'Individual'";
$this->_where[$grouping][] = $ceWhereClause;
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'email_greeting') {
$filterCondition = array('greeting_type' => 'email_greeting');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Email Greeting'));
} elseif ($name === 'postal_greeting') {
$filterCondition = array('greeting_type' => 'postal_greeting');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Postal Greeting'));
} elseif ($name === 'addressee') {
$filterCondition = array('greeting_type' => 'addressee');
$this->optionValueQuery($name, $op, $value, $grouping, CRM_Core_PseudoConstant::greeting($filterCondition), $field, ts('Addressee'));
} elseif (substr($name, 0, 4) === 'url-') {
$tName = 'civicrm_website';
$this->_whereTables[$tName] = $this->_tables[$tName] = "\nLEFT JOIN civicrm_website ON ( civicrm_website.contact_id = contact_a.id )";
$value = $strtolower(CRM_Core_DAO::escapeString($value));
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
$wc = 'civicrm_website.url';
$this->_where[$grouping][] = $d = self::buildClause($wc, $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} elseif ($name === 'contact_is_deleted') {
$this->_where[$grouping][] = self::buildClause("contact_a.is_deleted", $op, $value);
$this->_qill[$grouping][] = "{$field['title']} {$op} \"{$value}\"";
} else {
if (is_array($value)) {
// traditionally an array being passed has been a fatal error. We can take advantage of this to add support
// for api style operators for functions that hit this point without worrying about regression
// (the previous comments indicated the condition for hitting this point were unknown
// per CRM-14743 we are adding modified_date & created_date operator support
$operations = array_keys($value);
foreach ($operations as $operator) {
if (!in_array($operator, CRM_Core_DAO::acceptedSQLOperators())) {
// we don't know when this might happen
CRM_Core_Error::fatal();
}
}
$this->_where[$grouping][] = CRM_Core_DAO::createSQLFilter($name, $value, NULL);
//since this is not currently being called by the form layer we can skip worrying about the 'qill' for now
return;
}
if (!empty($field['where'])) {
if ($op != 'IN') {
$value = $strtolower($value);
}
if ($wildcard) {
$value = "%{$value}%";
$op = 'LIKE';
}
if (isset($locType[1]) && is_numeric($locType[1])) {
$setTables = FALSE;
//get the location name
list($tName, $fldName) = self::getLocationTableName($field['where'], $locType);
$where = "`{$tName}`.{$fldName}";
$this->_where[$grouping][] = self::buildClause("LOWER({$where})", $op, $value);
// we set both _tables & whereTables because whereTables doesn't seem to do what the name implies it should
$this->_tables[$tName] = $this->_whereTables[$tName] = 1;
$this->_qill[$grouping][] = "{$field['title']} {$op} '{$value}'";
} else {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if ($tableName == 'civicrm_contact') {
$fieldName = "LOWER(contact_a.{$fieldName})";
} else {
if ($op != 'IN' && !is_numeric($value)) {
$fieldName = "LOWER({$field['where']})";
} else {
$fieldName = "{$field['where']}";
}
}
$type = NULL;
if (!empty($field['type'])) {
$type = CRM_Utils_Type::typeToString($field['type']);
}
$this->_where[$grouping][] = self::buildClause($fieldName, $op, $value, $type);
$this->_qill[$grouping][] = "{$field['title']} {$op} {$value}";
}
}
}
if ($setTables && isset($field['where'])) {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if (isset($tableName)) {
$this->_tables[$tableName] = 1;
$this->_whereTables[$tableName] = 1;
}
}
}
/**
* Get function for query object api.
*
* This is a simple get function, but it should be usable for any kind of
* entity. I created it to work around CRM-16036.
*
* @param string $dao_name
* Name of DAO
* @param array $params
* As passed into api get function.
* @param bool $isFillUniqueFields
* Do we need to ensure unique fields continue to be populated for this api? (backward compatibility).
* @param CRM_Utils_SQL_Select|NULL $sqlFragment
*
* @return array
*/
function _civicrm_api3_get_using_utils_sql($dao_name, $params, $isFillUniqueFields, $sqlFragment)
{
$dao = new $dao_name();
$entity = _civicrm_api_get_entity_name_from_dao($dao);
$custom_fields = _civicrm_api3_custom_fields_for_entity($entity);
$options = _civicrm_api3_get_options_from_params($params);
// Unset $params['options'] if they are api parameters (not options as a fieldname).
if (!empty($params['options']) && is_array($params['options']) && array_intersect(array_keys($params['options']), array_keys($options))) {
unset($params['options']);
}
$entity_field_names = _civicrm_api3_field_names(_civicrm_api3_build_fields_array($dao));
$custom_field_names = array();
$uniqueAliases = array();
$getFieldsResult = civicrm_api3($entity, 'getfields', array('action' => 'get'));
$getFieldsResult = $getFieldsResult['values'];
foreach ($getFieldsResult as $getFieldKey => $getFieldSpec) {
$uniqueAliases[$getFieldKey] = $getFieldSpec['name'];
$uniqueAliases[$getFieldSpec['name']] = $getFieldSpec['name'];
}
// $select_fields maps column names to the field names of the result
// values.
$select_fields = array();
// array with elements array('column', 'operator', 'value');
$where_clauses = array();
// Tables we need to join with to retrieve the custom values.
$custom_value_tables = array();
// ID's of custom fields that refer to a contact.
$contact_reference_field_ids = array();
// populate $select_fields
$return_all_fields = empty($options['return']) || !is_array($options['return']);
$return = $return_all_fields ? array_fill_keys($entity_field_names, 1) : $options['return'];
// default fields
foreach (array_keys($return) as $field_name) {
if (!empty($uniqueAliases[$field_name]) && CRM_Core_BAO_CustomField::getKeyID($field_name) == FALSE) {
// 'a.' is an alias for the entity table.
$select_fields["a.{$uniqueAliases[$field_name]}"] = $uniqueAliases[$field_name];
}
}
// custom fields
foreach ($custom_fields as $cf_id => $custom_field) {
$field_name = "custom_{$cf_id}";
$custom_field_names[] = $field_name;
if ($return_all_fields || !empty($options['return'][$field_name]) || !empty($options['return']['custom'])) {
$table_name = $custom_field["table_name"];
$column_name = $custom_field["column_name"];
// remember that we will need to join the correct table.
if (!in_array($table_name, $custom_value_tables)) {
$custom_value_tables[] = $table_name;
}
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.
$contact_reference_field_ids[] = $cf_id;
$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;
}
}
}
if (!in_array("a.id", $select_fields)) {
// Always select the ID.
$select_fields["a.id"] = "id";
}
// build query
$query = CRM_Utils_SQL_Select::from($dao->tableName() . " a");
// populate $where_clauses
foreach ($params as $key => $value) {
$type = 'String';
$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'));
$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 "));
}
}
}
if (array_key_exists($key, $getFieldsResult)) {
$type = $getFieldsResult[$key]['type'];
$key = $getFieldsResult[$key]['name'];
}
if ($key == _civicrm_api_get_entity_name_from_camel($entity) . '_id') {
// The test contract enforces support of (eg) mailing_group_id if the entity is MailingGroup.
$type = 'int';
$key = 'id';
}
if (in_array($key, $entity_field_names)) {
$table_name = 'a';
$column_name = $key;
} elseif (($cf_id = CRM_Core_BAO_CustomField::getKeyID($key)) != FALSE) {
$table_name = $custom_fields[$cf_id]["table_name"];
$column_name = $custom_fields[$cf_id]["column_name"];
if (!in_array($table_name, $custom_value_tables)) {
$custom_value_tables[] = $table_name;
}
}
// 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)) {
$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())) {
$query->where(array("{$table_name}.{$column_name} = @value"), array("@value" => $value));
} else {
$query->where(CRM_Core_DAO::createSQLFilter("{$table_name}.{$column_name}", $value, $type));
}
}
}
$i = 0;
if (!$options['is_count']) {
foreach ($select_fields as $column => $alias) {
++$i;
$query = $query->select("!column_{$i} as !alias_{$i}", array("!column_{$i}" => $column, "!alias_{$i}" => $alias));
}
} else {
$query->select("count(*) as c");
}
// join with custom value tables
foreach ($custom_value_tables as $table_name) {
++$i;
$query = $query->join("!table_name_{$i}", "LEFT OUTER JOIN !table_name_{$i} ON !table_name_{$i}.entity_id = a.id", array("!table_name_{$i}" => $table_name));
}
// join with contact for contact reference fields
foreach ($contact_reference_field_ids as $field_id) {
++$i;
$query = $query->join("!contact_table_name{$i}", "LEFT OUTER JOIN civicrm_contact !contact_table_name_{$i} ON !contact_table_name_{$i}.id = !values_table_name_{$i}.!column_name_{$i}", array("!contact_table_name_{$i}" => "c_{$field_id}", "!values_table_name_{$i}" => $custom_fields[$field_id]["table_name"], "!column_name_{$i}" => $custom_fields[$field_id]["column_name"]));
}
foreach ($where_clauses as $clause) {
++$i;
if (substr($clause[1], -4) == "NULL") {
$query->where("!columnName_{$i} !nullThing_{$i}", array("!columnName_{$i}" => $clause[0], "!nullThing_{$i}" => $clause[1]));
} else {
$query->where("!columnName_{$i} !operator_{$i} @value_{$i}", array("!columnName_{$i}" => $clause[0], "!operator_{$i}" => $clause[1], "@value_{$i}" => $clause[2]));
}
}
if (!empty($sqlFragment)) {
$query->merge($sqlFragment);
}
// order by
if (!empty($options['sort'])) {
$sort_fields = array();
foreach (explode(',', $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) {
$query->orderBy(implode(",", $sort_fields));
}
}
// limit
if (!empty($options['limit']) || !empty($options['offset'])) {
$query->limit($options['limit'], $options['offset']);
}
$result_entities = array();
$result_dao = CRM_Core_DAO::executeQuery($query->toSQL());
while ($result_dao->fetch()) {
if ($options['is_count']) {
$result_dao->free();
return (int) $result_dao->c;
}
$result_entities[$result_dao->id] = array();
foreach ($select_fields as $column => $alias) {
if (property_exists($result_dao, $alias) && $result_dao->{$alias} != NULL) {
$result_entities[$result_dao->id][$alias] = $result_dao->{$alias};
}
// Backward compatibility on fields names.
if ($isFillUniqueFields && !empty($getFieldsResult['values'][$column]['uniqueName'])) {
$result_entities[$result_dao->id][$getFieldsResult['values'][$column]['uniqueName']] = $result_dao->{$alias};
}
foreach ($getFieldsResult 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;
}
/**
* @inheritDoc
*/
protected function buildWhereClause()
{
foreach ($this->where 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'));
$a = self::MAIN_TABLE_ALIAS;
$this->query->where("({$a}.start_date <= '{$todayStart}' OR {$a}.start_date IS NULL)\n AND ({$a}.end_date >= '{$todayEnd}' OR {$a}.end_date IS NULL)\n AND a.is_active = 1");
}
}
}
// 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 = self::MAIN_TABLE_ALIAS;
$column_name = $key;
} elseif (($cf_id = \CRM_Core_BAO_CustomField::getKeyID($key)) != FALSE) {
list($table_name, $column_name) = $this->addCustomField($this->apiFieldSpec['custom_' . $cf_id], 'INNER');
} elseif (strpos($key, '.')) {
$fkInfo = $this->addFkField($key, 'INNER');
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));
}
}
}
}
