if (!array_key_exists($cfID, $this->_cfIDs)) {
$this->_cfIDs[$cfID] = array();
}
+ // Set wildcard value based on "and/or" selection
+ foreach ($this->_params as $key => $param) {
+ if ($param[0] == $value[0] . '_operator') {
+ $value[4] = $param[2] == 'or';
+ break;
+ }
+ }
$this->_cfIDs[$cfID][] = $value;
}
* @param boolean $count
* @param boolean $sortByChar
* @param boolean $groupContacts
+ * @param boolean $onlyDeleted
*
* @return array sql query parts as an array
* @access public
*/
- function query($count = FALSE, $sortByChar = FALSE, $groupContacts = FALSE) {
+ function query($count = FALSE, $sortByChar = FALSE, $groupContacts = FALSE, $onlyDeleted = FALSE) {
+ // build permission clause
+ $this->generatePermissionClause($onlyDeleted, $count);
+
if ($count) {
if (isset($this->_rowCountClause)) {
$select = "SELECT {$this->_rowCountClause}";
$where = "WHERE {$this->_whereClause}";
}
+ if (!empty($this->_permissionWhereClause)) {
+ if (empty($where)) {
+ $where = "WHERE $this->_permissionWhereClause";
+ }
+ else {
+ $where = "$where AND $this->_permissionWhereClause";
+ }
+ }
+
$having = '';
if (!empty($this->_having)) {
foreach ($this->_having as $havingSets) {
}
$result = array($id, 'IN', $values, 0, 0);
}
+ elseif ($id == 'contact_type') {
+ $result = array($id, 'IN', $values, 0, $wildcard);
+ }
else {
$result = array($id, '=', $values, 0, $wildcard);
}
$this->_where[0][] = "contact_a.id IN ({$idList})";
}
else {
- $this->_where[0][] = "contact_a.id {$this->_params[$id][1]} {$this->_params[$id][2]}";
+ $this->_where[0][] = self::buildClause("contact_a.id", "{$this->_params[$id][1]}", "{$this->_params[$id][2]}");
}
}
else {
$name, $op, $value, $grouping,
CRM_Core_PseudoConstant::worldRegion(),
$field,
- ts('World Region')
+ ts('World Region'),
+ 'Positive',
+ TRUE
);
}
elseif ($name === 'birth_date') {
$this->_qill[$grouping][] = "$field[title] $op \"$value\"";
}
else {
- // sometime the value is an array, need to investigate and fix
if (is_array($value)) {
- CRM_Core_Error::fatal();
+ // 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'])) {
$where = "`$tName`.$fldName";
$this->_where[$grouping][] = self::buildClause("LOWER($where)", $op, $value);
- $this->_whereTables[$tName] = $this->_tables[$tName];
+ // 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 {
}
/**
+ * Where tables is sometimes used to create the from clause, but, not reliably, set this AND set tables
+ * It's unclear the intent - there is a 'simpleFrom' clause which takes whereTables into account & a fromClause which doesn't
+ * logic may have eroded
* @return array
*/
function whereTables() {
$from .= " $side JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )";
}
else {
+ //CRM-14263 further handling of address joins further down...
$from .= " $side JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id ) ";
}
continue;
continue;
default:
- $from .= CRM_Core_Component::from($name, $mode, $side);
+ $locationTypeName = '';
+ if (strpos($name, '-address') != 0) {
+ $locationTypeName = 'address';
+ }
+ elseif (strpos($name, '-phone') != 0) {
+ $locationTypeName = 'phone';
+ }
+ elseif (strpos($name, '-email') != 0) {
+ $locationTypeName = 'email';
+ }
+ if($locationTypeName) {
+ //we have a join on an location table - possibly in conjunction with search builder - CRM-14263
+ $parts = explode('-', $name);
+ $locationID = array_search($parts[0], CRM_Core_BAO_Address::buildOptions('location_type_id', 'get', array('name' => $parts[0])));
+ $from .= " $side JOIN civicrm_{$locationTypeName} `{$name}` ON ( contact_a.id = `{$name}`.contact_id ) and `{$name}`.location_type_id = $locationID ";
+ }
+ else {
+ $from .= CRM_Core_Component::from($name, $mode, $side);
+ }
$from .= CRM_Contact_BAO_Query_Hook::singleton()->buildSearchfrom($name, $mode, $side);
continue;
}
}
-
return $from;
}
// fix for CRM-771
if (!empty($clause)) {
+ $quill = $clause;
if ($op == 'IN' || $op == 'NOT IN') {
$this->_where[$grouping][] = "contact_a.contact_type $op (" . implode(',', $clause) . ')';
}
else {
- $quill = $clause;
$type = array_pop($clause);
- $this->_where[$grouping][] = "contact_a.contact_type $op $type";
+ $this->_where[$grouping][] = self::buildClause("contact_a.contact_type", $op, $contactType);
}
$this->_qill[$grouping][] = ts('Contact Type') . ' - ' . implode(' ' . ts('or') . ' ', $quill);
*
* @param bool $skipOrderAndLimit
*
- * @return CRM_Contact_DAO_Contact
+ * @return CRM_Core_DAO
* @access public
*/
function searchQuery(
break;
}
}
- $this->generatePermissionClause($onlyDeleted, $count);
// building the query string
$groupBy = NULL;
}
if (!empty($orderBy)) {
// this is special case while searching for
- // changelog CRM-1718
+ // change log CRM-1718
if (preg_match('/sort_name/i', $orderBy)) {
$orderBy = str_replace('sort_name', 'contact_a.sort_name', $orderBy);
}
// note : this modifies _fromClause and _simpleFromClause
$this->includePseudoFieldsJoin($sort);
- list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts);
-
- if(!empty($this->_permissionWhereClause)){
- if (empty($where)) {
- $where = "WHERE $this->_permissionWhereClause";
- }
- else {
- $where = "$where AND $this->_permissionWhereClause";
- }
- }
+ list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts, $onlyDeleted);
if ($additionalWhereClause) {
$where = $where . ' AND ' . $additionalWhereClause;
if (!$count) {
$this->_useDistinct = TRUE;
}
- $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode);
- $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);
+
+ if (empty($this->_fromClause)) {
+ $this->_fromClause = self::fromClause($this->_tables, NULL, NULL, $this->_primaryLocation, $this->_mode);
+ }
+
+ if (empty($this->_simpleFromClause)) {
+ $this->_simpleFromClause = self::fromClause($this->_whereTables, NULL, NULL, $this->_primaryLocation, $this->_mode);
+ }
}
}
else {
return $clause;
case 'IS EMPTY':
- $clause = " ( $field IS NULL OR $field = '' ) ";
+ $clause = " (NULLIF($field, '') IS NULL) ";
return $clause;
case 'IS NOT EMPTY':
- $clause = " ( $field IS NOT NULL AND $field <> '' ) ";
+ $clause = " (NULLIF($field, '') IS NOT NULL) ";
return $clause;
case 'IN':
}
$value = CRM_Utils_Type::escape($value, $dataType);
- // if we dont have a dataType we should assume
+ // if we don't have a dataType we should assume
if ($dataType == 'String' || $dataType == 'Text') {
$value = "'" . strtolower($value) . "'";
}
$useIDsOnly = FALSE
) {
- if (!empty($selectValues)) {
+ if (!empty($selectValues) && !is_array($value) && !empty($selectValues[$value])) {
$qill = $selectValues[$value];
}
else {
$qill = $selectValues[(int ) $value];
}
elseif ($op == 'IN' || $op == 'NOT IN') {
- $values = self::parseSearchBuilderString($value);
- if (is_array($values)) {
+ if (is_array($value)) {
$intVals = array();
$newValues = array();
- foreach ($values as $v) {
- $intVals[] = (int) $v;
- $newValues[] = $selectValues[(int ) $v];
+ foreach ($value as $k => $v) {
+ $intVals[$k] = (int) $k;
+ $newValues[] = $selectValues[(int) $k];
}
$value = (in_array($name, $pseudoFields)) ? $intVals : $newValues;
if ($tableName == 'civicrm_contact') {
$wc = "contact_a.$fieldName";
}
+ else {
+ $wc = "$tableName.id";
+ }
}
else {
$wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
//preserve id value
$idColumn = "{$key}_id";
$dao->$idColumn = $val;
- $dao->$value['pseudoField'] = $dao->$key = CRM_Core_PseudoConstant::getLabel($baoName, $value['pseudoField'], $val);
+
+ if ($key == 'state_province_name') {
+ $dao->$value['pseudoField'] = $dao->$key = CRM_Core_PseudoConstant::stateProvinceAbbreviation($val);
+ }
+ else {
+ $dao->$value['pseudoField'] = $dao->$key = CRM_Core_PseudoConstant::getLabel($baoName, $value['pseudoField'], $val);
+ }
}
elseif ($value['pseudoField'] == 'state_province_abbreviation') {
$dao->$key = CRM_Core_PseudoConstant::stateProvinceAbbreviation($val);