*
* @package CRM
* @copyright CiviCRM LLC (c) 2004-2015
- * $Id$
- *
*/
/**
/**
* Function which actually does all the work for the constructor.
- *
- * @return void
*/
public function initialize() {
$this->_select = array();
$this->openedSearchPanes(TRUE);
}
+ /**
+ * Function for same purpose as convertFormValues.
+ *
+ * Like convert form values this function exists to pre-Process parameters from the form.
+ *
+ * It is unclear why they are different functions & likely relates to advances search
+ * versus search builder.
+ *
+ * The direction we are going is having the form convert values to a standardised format &
+ * moving away from wierd & wonderful where clause switches.
+ *
+ * Fix and handle contact deletion nicely.
+ *
+ * this code is primarily for search builder use case where different clauses can specify if they want deleted.
+ *
+ * CRM-11971
+ */
public function buildParamsLookup() {
- // first fix and handle contact deletion nicely
- // this code is primarily for search builder use case
- // where different clauses can specify if they want deleted
- // contacts or not
- // CRM-11971
$trashParamExists = FALSE;
$paramByGroup = array();
foreach ($this->_params as $k => $param) {
}
/**
- * Some composite fields do not appear in the fields array
- * hack to make them part of the query
- *
- * @return void
+ * Some composite fields do not appear in the fields array hack to make them part of the query.
*/
public function addSpecialFields() {
static $special = array('contact_type', 'contact_sub_type', 'sort_name', 'display_name');
* clauses. Note that since the where clause introduces new
* tables, the initial attempt also retrieves all variables used
* in the params list
- *
- * @return void
*/
public function selectClause() {
CRM_Contact_BAO_Query_Hook::singleton()->alterSearchQuery($this, 'select');
if (!empty($this->_cfIDs)) {
+ // @todo This function is the select function but instead of running 'select' it
+ // is running the whole query.
$this->_customQuery = new CRM_Core_BAO_CustomQuery($this->_cfIDs, TRUE, $this->_locationSpecificCustomFields);
$this->_customQuery->query();
$this->_select = array_merge($this->_select, $this->_customQuery->_select);
}
/**
- * If the return Properties are set in a hierarchy, traverse the hierarchy to get
- * the return values
- *
- * @return void
+ * If the return Properties are set in a hierarchy, traverse the hierarchy to get the return values.
*/
public function addHierarchicalElements() {
if (empty($this->_returnProperties['location'])) {
}
/**
- * If the return Properties are set in a hierarchy, traverse the hierarchy to get
- * the return values
- *
- * @return void
+ * If the return Properties are set in a hierarchy, traverse the hierarchy to get the return values.
*/
public function addMultipleElements() {
if (empty($this->_returnProperties['website'])) {
// make sure there is only one element
// this is used when we are running under smog and need to know
// how the contact was added (CRM-1203)
+ $groups = (array) CRM_Utils_Array::value($this->_paramLookup['group'][0][1], $this->_paramLookup['group'][0][2], $this->_paramLookup['group'][0][2]);
if ((count($this->_paramLookup['group']) == 1) &&
- (count($this->_paramLookup['group'][0][2]) == 1)
+ (count($groups) == 1)
) {
- $groups = array_keys($this->_paramLookup['group'][0][2]);
$groupId = $groups[0];
//check if group is saved search
}
/**
+ * Get where values from the parameters.
+ *
* @param string $name
- * @param $grouping
+ * @param mixed $grouping
*
- * @return null
+ * @return mixed
*/
- public function &getWhereValues($name, $grouping) {
+ public function getWhereValues($name, $grouping) {
$result = NULL;
foreach ($this->_params as $values) {
if ($values[0] == $name && $values[3] == $grouping) {
}
/**
- * @param $relative
- * @param $from
- * @param $to
+ * Fix date values.
+ *
+ * @param bool $relative
+ * @param string $from
+ * @param string $to
*/
public static function fixDateValues($relative, &$from, &$to) {
if ($relative) {
}
/**
- * @param $formValues
+ * Convert values from form-appropriate to query-object appropriate.
+ *
+ * The query object is increasingly supporting the sql-filter syntax which is the most flexible syntax.
+ * So, ideally we would convert all fields to look like
+ * array(
+ * 0 => $fieldName
+ * // Set the operator for legacy reasons, but it is ignored
+ * 1 => '='
+ * // array in sql filter syntax
+ * 2 => array('BETWEEN' => array(1,60),
+ * 3 => null
+ * 4 => null
+ * );
+ *
+ * There are some examples of the syntax in
+ * https://github.com/civicrm/civicrm-core/tree/master/api/v3/examples/Relationship
+ *
+ * More notes at CRM_Core_DAO::createSQLFilter
+ *
+ * and a list of supported operators in CRM_Core_DAO
+ *
+ * @param array $formValues
* @param int $wildcard
* @param bool $useEquals
*
* @param string $apiEntity
*
+ * @param array $entityReferenceFields
+ * Field names of any entity reference fields (which will need reformatting to IN syntax).
+ *
* @return array
*/
- public static function convertFormValues(&$formValues, $wildcard = 0, $useEquals = FALSE, $apiEntity = NULL) {
+ public static function convertFormValues(&$formValues, $wildcard = 0, $useEquals = FALSE, $apiEntity = NULL,
+ $entityReferenceFields = array()) {
$params = array();
if (empty($formValues)) {
return $params;
}
foreach ($formValues as $id => $values) {
+ if (self::isAlreadyProcessedForQueryFormat($values)) {
+ $params[] = $values;
+ continue;
+ }
if ($id == 'privacy') {
if (is_array($formValues['privacy'])) {
$op = !empty($formValues['privacy']['do_not_toggle']) ? '=' : '!=';
$params[] = array('on_hold', '=', $formValues['email_on_hold']['on_hold'], 0, 0);
}
}
+ elseif (substr($id, 0, 7) == 'custom_'
+ && (
+ substr($id, -9, 9) == '_relative'
+ || substr($id, -5, 5) == '_from'
+ || substr($id, -3, 3) == '_to'
+ )
+ ) {
+ self::convertCustomDateRelativeFields($formValues, $params, $values, $id);
+ }
elseif (preg_match('/_date_relative$/', $id) ||
$id == 'event_relative' ||
$id == 'case_from_relative' ||
continue;
}
}
+ elseif (in_array($id, $entityReferenceFields) && !empty($values) && is_string($values) && (strpos($values, ',') !=
+ FALSE)) {
+ $params[] = array($id, 'IN', explode(',', $values), 0, 0);
+ }
else {
$values = CRM_Contact_BAO_Query::fixWhereValues($id, $values, $wildcard, $useEquals, $apiEntity);
}
/**
+ * Fix values from query from/to something no-one cared enough to document.
+ *
* @param int $id
- * @param $values
+ * @param array $values
* @param int $wildcard
* @param bool $useEquals
*
*
* @return array|null
*/
- public static function &fixWhereValues($id, &$values, $wildcard = 0, $useEquals = FALSE, $apiEntity = NULL) {
+ public static function fixWhereValues($id, &$values, $wildcard = 0, $useEquals = FALSE, $apiEntity = NULL) {
// skip a few search variables
static $skipWhere = NULL;
static $likeNames = NULL;
elseif (is_string($values) && strpos($values, '%') !== FALSE) {
$result = array($id, 'LIKE', $values, 0, 0);
}
- elseif ($id == 'group') {
- if (is_array($values)) {
- foreach ($values as $groupIds => $val) {
- $matches = array();
- if (preg_match('/-(\d+)$/', $groupIds, $matches)) {
- if (strlen($matches[1]) > 0) {
- $values[$matches[1]] = 1;
- unset($values[$groupIds]);
- }
- }
- }
- }
- else {
- $groupIds = explode(',', $values);
- unset($values);
- foreach ($groupIds as $groupId) {
- $values[$groupId] = 1;
- }
- }
-
- $result = array($id, 'IN', $values, 0, 0);
- }
- elseif ($id == 'contact_tags' || $id == 'tag') {
- if (!is_array($values)) {
- $tagIds = explode(',', $values);
- unset($values);
- foreach ($tagIds as $tagId) {
- $values[$tagId] = 1;
- }
- }
- $result = array($id, 'IN', $values, 0, 0);
- }
elseif ($id == 'contact_type' ||
(!empty($values) && is_array($values) && !in_array(key($values), CRM_Core_DAO::acceptedSQLOperators(), TRUE))
) {
}
/**
- * @param $values
+ * Get the where clause for a single field.
+ *
+ * @param array $values
*/
public function whereClauseSingle(&$values) {
// do not process custom fields or prefixed contact ids or component params
}
/**
- * Given a list of conditions in params generate the required.
- * where clause
+ * Given a list of conditions in params generate the required where clause.
*
* @return string
*/
}
if ($this->_customQuery) {
- // Added following if condition to avoid the wrong value diplay for 'myaccount' / any UF info.
+ // Added following if condition to avoid the wrong value display for 'my account' / any UF info.
// Hope it wont affect the other part of civicrm.. if it does please remove it.
if (!empty($this->_customQuery->_where)) {
$this->_where = CRM_Utils_Array::crmArrayMerge($this->_where, $this->_customQuery->_where);
}
/**
- * @param $values
+ * Generate where clause for any parameters not already handled.
+ *
+ * @param array $values
*
* @throws Exception
*/
$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'";
- }
+ $this->_where[$grouping][] = self::buildClause($where, $op, $value, 'Positive');
+ 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_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', $field['name']),
+ 'CRM_Contact_DAO_Contact',
$field,
$field['title'],
'String',
self::$_openedPanes[ts('Demographics')] = TRUE;
}
}
- elseif (substr($name, 0, 7) === 'country') {
+ 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`.country_id";
+ $where = "`$aName`.$name";
}
else {
- $where = "civicrm_address.country_id";
+ $where = "civicrm_address.$name";
}
- $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');
- }
+ $this->_where[$grouping][] = self::buildClause($where, $op, $value, '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'";
+ 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') {
- $field['where'] = 'civicrm_worldregion.id';
$this->optionValueQuery(
$name, $op, $value, $grouping,
- CRM_Core_PseudoConstant::worldRegion(),
+ NULL,
$field,
ts('World Region'),
'Positive',
}
elseif ($name === 'contact_is_deleted') {
$this->_where[$grouping][] = self::buildClause("contact_a.is_deleted", $op, $value);
- $this->_qill[$grouping][] = "$field[title] $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;
}
list($qillop, $qillVal) = self::buildQillForFieldValue(NULL, $field['title'], $value, $op);
- $this->_qill[$grouping][] = "$field[title] $qillop '$qillVal'";
+ $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
$this->_where[$grouping][] = CRM_Core_DAO::createSQLFilter($fieldName, $value, $type);
}
else {
- if ($op != 'IN') {
+ if (!strpos($op, 'IN')) {
$value = $strtolower($value);
}
if ($wildcard) {
array(
'address_name',
'street_address',
+ 'street_name',
+ 'street_number_suffix',
+ 'street_unit',
'supplemental_address_1',
'supplemental_address_2',
'city',
'postal_code_suffix',
'geo_code_1',
'geo_code_2',
+ 'master_id',
)
)) {
//fix for search by profile with address fields.
$tName = "{$locationType[$locType[1]]}-address";
}
- elseif ($locType[0] == 'on_hold') {
+ elseif (in_array($locType[0],
+ array(
+ 'on_hold',
+ 'signature_html',
+ 'signature_text',
+ 'is_bulkmail',
+ )
+ )) {
$tName = "{$locationType[$locType[1]]}-email";
}
+ elseif ($locType[0] == 'provider_id') {
+ $tName = "{$locationType[$locType[1]]}-im";
+ }
+ elseif ($locType[0] == 'openid') {
+ $tName = "{$locationType[$locType[1]]}-openid";
+ }
else {
$tName = "{$locationType[$locType[1]]}-{$locType[0]}";
}
elseif (strpos($name, '-email') != 0) {
$locationTypeName = 'email';
}
+ elseif (strpos($name, '-im') != 0) {
+ $locationTypeName = 'im';
+ }
+ elseif (strpos($name, '-openid') != 0) {
+ $locationTypeName = 'openid';
+ }
+
if ($locationTypeName) {
//we have a join on an location table - possibly in conjunction with search builder - CRM-14263
$parts = explode('-', $name);
/**
* WHERE / QILL clause for deleted_contacts
*
- * @param $values
- *
- * @return void
+ * @param array $values
*/
public function deletedContacts($values) {
list($_, $_, $value, $grouping, $_) = $values;
* Where / qill clause for contact_type
*
* @param $values
- *
- * @return void
*/
public function contactType(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
* Where / qill clause for contact_sub_type
*
* @param $values
- *
- * @return void
*/
public function contactSubType(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
* Where / qill clause for groups
*
* @param $values
- *
- * @return void
*/
public function group(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
$this->_useDistinct = TRUE;
}
+ if (isset($value)) {
+ $value = CRM_Utils_Array::value($op, $value, $value);
+ }
+
$groupIds = NULL;
$names = array();
$isSmart = FALSE;
$isNotOp = ($op == 'NOT IN' || $op == '!=');
- if ($value) {
- if (strpos($op, 'IN') === FALSE) {
- $value = key($value);
- }
- else {
- $value = array_keys($value);
- }
- }
-
$statii = array();
- $gcsValues = &$this->getWhereValues('group_contact_status', $grouping);
+ $gcsValues = $this->getWhereValues('group_contact_status', $grouping);
if ($gcsValues &&
is_array($gcsValues[2])
) {
$joinClause[] = "{$gcTable}.status IN (" . implode(', ', $statii) . ")";
}
$this->_tables[$gcTable] = $this->_whereTables[$gcTable] = " LEFT JOIN civicrm_group_contact {$gcTable} ON (" . implode(' AND ', $joinClause) . ")";
- $groupClause = "{$gcTable}.group_id $op $groupIds";
if (strpos($op, 'IN') !== FALSE) {
$groupClause = "{$gcTable}.group_id $op ( $groupIds )";
}
+ elseif ($op == '!=') {
+ $groupClause = "{$gcTable}.contact_id NOT IN (SELECT contact_id FROM civicrm_group_contact cgc WHERE cgc.group_id = $groupIds)";
+ }
+ else {
+ $groupClause = "{$gcTable}.group_id $op $groupIds";
+ }
}
if ($ssClause) {
* Where / qill clause for cms users
*
* @param $values
- *
- * @return void
*/
public function ufUser(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
/**
* All tag search specific.
*
- * @param $values
- *
- * @return void
+ * @param array $values
*/
public function tagSearch(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
/**
* Where / qill clause for tag
*
- * @param $values
- *
- * @return void
+ * @param array $values
*/
public function tag(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
- $tagNames = CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', array('onlyActive' => FALSE));
+ list($qillop, $qillVal) = self::buildQillForFieldValue('CRM_Core_DAO_EntityTag', "tag_id", $value, $op, array('onlyActive' => FALSE));
+ // API/Search Builder format array(operator => array(values))
if (is_array($value)) {
+ if (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
+ $op = key($value);
+ $value = $value[$op];
+ }
if (count($value) > 1) {
$this->_useDistinct = TRUE;
}
- foreach ($value as $id => $dontCare) {
- $names[] = CRM_Utils_Array::value($id, $tagNames);
- }
- $names = implode(' ' . ts('or') . ' ', $names);
- $value = implode(',', array_keys($value));
- }
- else {
- $names = CRM_Utils_Array::value($value, $tagNames);
+ $value = implode(',', (array) $value);
}
$useAllTagTypes = $this->getWhereValues('all_tag_types', $grouping);
else {
$this->_where[$grouping][] = "({$etTable}.tag_id $op (" . $value . ") OR {$etCaseTable}.tag_id $op (" . $value . ") OR {$etActTable}.tag_id $op (" . $value . "))";
}
- $this->_qill[$grouping][] = ts('Tag %1 %2', array(1 => $op, 2 => $tagTypesText[2])) . ' ' . $names;
}
else {
$this->_tables[$etTable] = $this->_whereTables[$etTable]
$op = str_replace('EMPTY', 'NULL', $op);
$this->_where[$grouping][] = "{$etTable}.tag_id $op";
}
- else {
- $this->_where[$grouping][] = "{$etTable}.tag_id $op (" . $value . ')';
+ // CRM-16941: for tag tried with != operator we don't show contact who don't have given $value AND also in other tag
+ elseif ($op == '!=') {
+ $this->_where[$grouping][] = "{$etTable}.entity_id NOT IN (SELECT entity_id FROM civicrm_entity_tag cet WHERE cet.entity_table = 'civicrm_contact' AND " . self::buildClause("cet.tag_id", '=', $value, 'Int') . ")";
+ }
+ elseif ($op == '=' || strstr($op, 'IN')) {
+ $op = ($op == '=') ? 'IN' : $op;
+ $this->_where[$grouping][] = "{$etTable}.tag_id $op ( $value )";
}
- $this->_qill[$grouping][] = ts('Tagged %1', array(1 => $op)) . ' ' . $names;
}
-
+ $this->_qill[$grouping][] = ts('Tagged %1 %2', array(1 => $qillop, 2 => $qillVal));
}
/**
* Where/qill clause for notes
*
- * @param $values
- *
- * @return void
+ * @param array $values
*/
public function notes(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
$strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
$n = trim($value);
$value = $strtolower(CRM_Core_DAO::escapeString($n));
- if ($wildcard || $op == 'LIKE') {
+ if ($wildcard) {
if (strpos($value, '%') === FALSE) {
$value = "%$value%";
}
$label = $label ? ts('Note: Body and Subject') : ts('Note: Subject Only');
}
$this->_where[$grouping][] = "( " . implode(' OR ', $clauses) . " )";
- $this->_qill[$grouping][] = $label . " $op - '$n'";
+ list($qillOp, $qillVal) = self::buildQillForFieldValue(NULL, $name, $n, $op);
+ $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $label, 2 => $qillOp, 3 => $qillVal));
}
/**
/**
* Where / qill clause for sort_name
*
- * @param $values
- *
- * @return void
+ * @param array $values
*/
public function sortName(&$values) {
list($fieldName, $op, $value, $grouping, $wildcard) = $values;
list($name, $op, $value, $grouping, $wildcard) = $values;
$name .= '_display';
- $this->_qill[$grouping][] = ts('Greeting %1 %2', array(1 => $op, 2 => $value));
- $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", 'LIKE', "$value", 'String');
+ list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, $name, $value, $op);
+ $this->_qill[$grouping][] = ts('Greeting %1 %2', array(1 => $qillop, 2 => $qillVal));
+ $this->_where[$grouping][] = self::buildClause("contact_a.{$name}", $op, $value, 'String');
}
/**
* Where / qill clause for email
*
- * @param $values
- *
- * @return void
+ * @param array $values
*/
public function email(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
/**
* Where / qill clause for phone number
*
- * @param $values
- *
- * @return void
+ * @param array $values
*/
public function phone_numeric(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
/**
* Where / qill clause for phone type/location
*
- * @param $values
- *
- * @return void
+ * @param array $values
*/
public function phone_option_group($values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
}
/**
- * Where / qill clause for street_address
- *
- * @param $values
+ * Where / qill clause for street_address.
*
- * @return void
+ * @param array $values
*/
public function street_address(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
}
/**
- * Where / qill clause for street_unit
+ * Where / qill clause for street_unit.
*
- * @param $values
- *
- * @return void
+ * @param array $values
*/
public function street_number(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
}
/**
- * Where / qill clause for sorting by character
+ * Where / qill clause for sorting by character.
*
- * @param $values
- *
- * @return void
+ * @param array $values
*/
public function sortByCharacter(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
}
/**
- * Where / qill clause for including contact ids
- *
- * @return void
+ * Where / qill clause for including contact ids.
*/
public function includeContactIDs() {
if (!$this->_includeContactIds || empty($this->_params)) {
}
/**
- * Where / qill clause for postal code
- *
- * @param $values
+ * Where / qill clause for postal code.
*
- * @return void
+ * @param array $values
*/
public function postalCode(&$values) {
// skip if the fields dont have anything to do with postal_code
}
/**
- * Where / qill clause for location type
+ * Where / qill clause for location type.
*
- * @param $values
+ * @param array $values
* @param null $status
*
- * @return void
+ * @return string
*/
public function locationType(&$values, $status = NULL) {
list($name, $op, $value, $grouping, $wildcard) = $values;
$this->_tables['civicrm_address'] = 1;
$this->_whereTables['civicrm_address'] = 1;
- $countries = CRM_Core_PseudoConstant::country();
- if (is_numeric($value)) {
- $countryClause = self::buildClause(
- 'civicrm_address.country_id',
- $op,
- $value,
- 'Positive'
- );
- $countryName = $countries[(int ) $value];
- }
-
- else {
- $intValues = self::parseSearchBuilderString($value);
- if ($intValues && ($op == 'IN' || $op == 'NOT IN')) {
- $countryClause = self::buildClause(
- 'civicrm_address.country_id',
- $op,
- $intValues,
- 'Positive'
- );
- $countryNames = array();
- foreach ($intValues as $v) {
- $countryNames[] = $countries[$v];
- }
- $countryName = implode(',', $countryNames);
- }
- else {
- $countries = CRM_Core_PseudoConstant::country();
- $intVal = CRM_Utils_Array::key($value, $countries);
- $countryClause = self::buildClause(
- 'civicrm_address.country_id',
- $op,
- $intVal,
- 'Integer'
- );
- $countryName = $value;
- }
- }
- $countryQill = ts('Country') . " {$op} '$countryName'";
+ $countryClause = self::buildClause('civicrm_address.country_id', $op, $value, 'Positive');
+ list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(NULL, 'country_id', $value, $op);
+ $countryQill = ts("%1 %2 %3", array(1 => 'Country', 2 => $qillop, 3 => $qillVal));
if (!$fromStateProvince) {
$this->_where[$grouping][] = $countryClause;
}
/**
- * Where / qill clause for county (if present)
+ * Where / qill clause for county (if present).
*
- * @param $values
+ * @param array $values
* @param null $status
*
- * @return void
+ * @return string
*/
public function county(&$values, $status = NULL) {
list($name, $op, $value, $grouping, $wildcard) = $values;
}
/**
- * Where / qill clause for state/province AND country (if present)
+ * Where / qill clause for state/province AND country (if present).
*
- * @param $values
+ * @param array $values
* @param null $status
*
- * @return void
+ * @return string
*/
public function stateProvince(&$values, $status = NULL) {
list($name, $op, $value, $grouping, $wildcard) = $values;
}
/**
- * Where / qill clause for change log
+ * Where / qill clause for change log.
*
- * @param $values
- *
- * @return void
+ * @param array $values
*/
public function changeLog(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
$this->_where[$grouping][] = "contact_a.{$name} $op $value";
}
$field = CRM_Utils_Array::value($name, $this->_fields);
+ $op = CRM_Utils_Array::value($op, CRM_Core_SelectValues::getSearchBuilderOperators(), $op);
$title = $field ? $field['title'] : $name;
$this->_qill[$grouping][] = "$title $op $value";
}
public function preferredCommunication(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
- $pref = array();
- if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
- $value = NULL;
- }
- elseif (!is_array($value)) {
- $v = array();
- $value = trim($value, ' ()');
- if (strpos($value, CRM_Core_DAO::VALUE_SEPARATOR) !== FALSE) {
- $v = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
- }
- else {
- $v = explode(",", $value);
- }
-
- foreach ($v as $item) {
- if ($item) {
- $pref[] = $item;
- }
- }
+ if (!is_array($value)) {
+ $value = str_replace(array('(', ')'), '', explode(",", $value));
}
- else {
- foreach ($value as $key => $checked) {
- if ($checked) {
- $pref[] = $key;
- }
- }
+ elseif (in_array(key($value), CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
+ $op = key($value);
+ $value = $value[$op];
}
+ list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue('CRM_Contact_DAO_Contact', $name, $value, $op);
- $commPref = CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'preferred_communication_method');
-
- $sqlValue = array();
- $showValue = array();
- $sql = "contact_a.preferred_communication_method";
- if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) {
- $sqlValue[] = "{$sql} {$op}";
+ if (self::caseImportant($op)) {
+ $value = implode("[[:cntrl:]]|[[:cntrl:]]", (array) $value);
+ $op = (strstr($op, '!') || strstr($op, 'NOT')) ? 'NOT RLIKE' : 'RLIKE';
+ $value = "[[:cntrl:]]" . $value . "[[:cntrl:]]";
}
- else {
- foreach ($pref as $val) {
- $sqlValue[] = "( $sql like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $val . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) ";
- $showValue[] = $commPref[$val];
- }
- }
- $this->_where[$grouping][] = "( " . implode(' OR ', $sqlValue) . " )";
- $this->_qill[$grouping][] = ts('Preferred Communication Method') . " $op " . implode(' ' . ts('or') . ' ', $showValue);
+
+ $this->_where[$grouping][] = self::buildClause("contact_a.preferred_communication_method", $op, $value);
+ $this->_qill[$grouping][] = ts('Preferred Communication Method %1 %2', array(1 => $qillop, 2 => $qillVal));
}
/**
- * Where / qill clause for relationship
+ * Where / qill clause for relationship.
*
- * @param $values
- *
- * @return void
+ * @param array $values
*/
public function relationship(&$values) {
list($name, $op, $value, $grouping, $wildcard) = $values;
return array($values, $options);
}
+ /**
+ * Get the actual custom field name by stripping off the appended string.
+ *
+ * The string could be _relative, _from, or _to
+ *
+ * @todo use metadata rather than convention to do this.
+ *
+ * @param string $parameterName
+ * The name of the parameter submitted to the form.
+ * e.g
+ * custom_3_relative
+ * custom_3_from
+ *
+ * @return string
+ */
+ public static function getCustomFieldName($parameterName) {
+ if (substr($parameterName, -5, 5) == '_from') {
+ return substr($parameterName, 0, strpos($parameterName, '_from'));
+ }
+ if (substr($parameterName, -9, 9) == '_relative') {
+ return substr($parameterName, 0, strpos($parameterName, '_relative'));
+ }
+ if (substr($parameterName, -3, 3) == '_to') {
+ return substr($parameterName, 0, strpos($parameterName, '_to'));
+ }
+ }
+
+ /**
+ * Convert submitted values for relative custom date fields to query object format.
+ *
+ * The query will support the sqlOperator format so convert to that format.
+ *
+ * @param array $formValues
+ * Submitted values.
+ * @param array $params
+ * Converted parameters for the query object.
+ * @param string $values
+ * Submitted value.
+ * @param string $fieldName
+ * Submitted field name. (Matches form field not DB field.)
+ */
+ protected static function convertCustomDateRelativeFields(&$formValues, &$params, $values, $fieldName) {
+ if (empty($values)) {
+ // e.g we might have relative set & from & to empty. The form flow is a bit funky &
+ // this function gets called again after they fields have been converted which can get ugly.
+ return;
+ }
+ $customFieldName = self::getCustomFieldName($fieldName);
+
+ if (substr($fieldName, -9, 9) == '_relative') {
+ list($from, $to) = CRM_Utils_Date::getFromTo($values, NULL, NULL);
+ }
+ else {
+ if ($fieldName == $customFieldName . '_to' && CRM_Utils_Array::value($customFieldName . '_from', $formValues)) {
+ // Both to & from are set. We only need to acton one, choosing from.
+ return;
+ }
+
+ list($from, $to) = CRM_Utils_Date::getFromTo(
+ NULL,
+ (empty($formValues[$customFieldName . '_from']) ? NULL : $formValues[$customFieldName . '_from']),
+ CRM_Utils_Array::value($customFieldName . '_to', $formValues)
+ );
+ }
+
+ if ($from) {
+ if ($to) {
+ $relativeFunction = array('BETWEEN' => array($from, $to));
+ }
+ else {
+ $relativeFunction = array('>=' => $from);
+ }
+ }
+ else {
+ $relativeFunction = array('<=' => $to);
+ }
+ $params[] = array(
+ $customFieldName,
+ '=',
+ $relativeFunction,
+ 0,
+ 0,
+ );
+ }
+
+ /**
+ * Has this field already been reformatting to Query object syntax.
+ *
+ * The form layer passed formValues to this function in preProcess & postProcess. Reason unknown. This seems
+ * to come with associated double queries & is possibly damaging performance.
+ *
+ * However, here we add a tested function to ensure convertFormValues identifies pre-processed fields & returns
+ * them as they are.
+ *
+ * @param mixed $values
+ * Value in formValues for the field.
+ *
+ * @return bool;
+ */
+ protected static function isAlreadyProcessedForQueryFormat($values) {
+ if (!is_array($values)) {
+ return FALSE;
+ }
+ if (($operator = CRM_Utils_Array::value(1, $values)) == FALSE) {
+ return FALSE;
+ }
+ return in_array($operator, CRM_Core_DAO::acceptedSQLOperators());
+ }
+
/**
* Create and query the db for an contact search.
*
}
/**
- * @param $values
+ * Build query for a date field.
+ *
+ * @param array $values
* @param string $tableName
* @param string $fieldName
* @param string $dbFieldName
- * @param $fieldTitle
+ * @param string $fieldTitle
* @param bool $appendTimeStamp
*/
public function dateQueryBuilder(
}
/**
- * Builds the necessary structures for all fields that are similar to option value lookups.
+ * Builds the necessary structures for all fields that are similar to option value look-ups.
*
* @param string $name
* the name of the field.
* @param string $dataType
* The data type for this element.
* @param bool $useIDsOnly
- *
- * @return void
- * adds the where clause and qill to the query object
*/
public function optionValueQuery(
$name,
$op,
$value,
$grouping,
- $selectValues,
+ $daoName = NULL,
$field,
$label,
$dataType = 'String',
$useIDsOnly = FALSE
) {
- if (!empty($selectValues) && !is_array($value) && !empty($selectValues[$value])) {
- $qill = $selectValues[$value];
- }
- else {
- $qill = $value;
- }
-
$pseudoFields = array(
'email_greeting',
'postal_greeting',
'communication_style_id',
);
- if (is_numeric($value)) {
- $qill = $selectValues[(int ) $value];
- }
- elseif ($op == 'IN' || $op == 'NOT IN') {
- if (is_array($value)) {
- $intVals = array();
- $newValues = array();
- foreach ($value as $k => $v) {
- $intVals[$k] = (int) $k;
- $newValues[] = $selectValues[(int) $k];
- }
-
- $value = (in_array($name, $pseudoFields)) ? $intVals : $newValues;
- $qill = implode(', ', $newValues);
- }
- }
- elseif (!array_key_exists($value, $selectValues)) {
- // its a string, lets get the int value
- $value = array_search($value, $selectValues);
- }
if ($useIDsOnly) {
list($tableName, $fieldName) = explode('.', $field['where'], 2);
if ($tableName == 'civicrm_contact') {
else {
$wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
}
-
if (in_array($name, $pseudoFields)) {
if (!in_array($name, array('gender_id', 'prefix_id', 'suffix_id', 'communication_style_id'))) {
$wc = "contact_a.{$name}_id";
$dataType = 'Positive';
$value = (!$value) ? 0 : $value;
}
+ if ($name == "world_region") {
+ $field['name'] = $name;
+ }
- $this->_qill[$grouping][] = $label . " $op '$qill'";
- $op = (in_array($name, $pseudoFields) && ($op == 'LIKE' || $op == 'RLIKE')) ? '=' : $op;
+ list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue($daoName, $field['name'], $value, $op);
+ $this->_qill[$grouping][] = ts("%1 %2 %3", array(1 => $label, 2 => $qillop, 3 => $qillVal));
$this->_where[$grouping][] = self::buildClause($wc, $op, $value, $dataType);
}
*
* @param CRM_Core_DAO $dao
* @param bool $return
+ * @param bool $usedForAPI
*
* @return array|NULL
*/
- public function convertToPseudoNames(&$dao, $return = FALSE) {
+ public function convertToPseudoNames(&$dao, $return = FALSE, $usedForAPI = FALSE) {
if (empty($this->_pseudoConstantsSelect)) {
return NULL;
}
continue;
}
- if (property_exists($dao, $value['idCol'])) {
+ if (is_object($dao) && property_exists($dao, $value['idCol'])) {
$val = $dao->$value['idCol'];
if (CRM_Utils_System::isNull($val)) {
}
// FIX ME: we should potentially move this to component Query and write a wrapper function that
// handles pseudoconstant fixes for all component
- elseif (in_array($value['pseudoField'], array('participant_status', 'participant_role'))) {
- $pseudoOptions = $viewValues = array();
- $pseudoOptions = CRM_Core_PseudoConstant::get('CRM_Event_DAO_Participant', $value['idCol']);
- foreach (explode(CRM_Core_DAO::VALUE_SEPARATOR, $val) as $k => $v) {
- $viewValues[] = $pseudoOptions[$v];
+ elseif (in_array($value['pseudoField'], array('participant_role_id', 'participant_role'))) {
+ $viewValues = explode(CRM_Core_DAO::VALUE_SEPARATOR, $val);
+
+ if ($value['pseudoField'] == 'participant_role') {
+ $pseudoOptions = CRM_Core_PseudoConstant::get('CRM_Event_DAO_Participant', 'role_id');
+ foreach ($viewValues as $k => $v) {
+ $viewValues[$k] = $pseudoOptions[$v];
+ }
}
- $dao->$key = implode(', ', $viewValues);
+ $dao->$key = ($usedForAPI && count($viewValues) > 1) ? $viewValues : implode(', ', $viewValues);
}
else {
$labels = CRM_Core_OptionGroup::values($value['pseudoField']);
*
* Qill refers to the query detail visible on the UI.
*
- * @param $daoName
- * @param $fieldName
- * @param $fieldValue
- * @param $op
- * @param array $pseduoExtraParam
+ * @param string $daoName
+ * @param string $fieldName
+ * @param mixed $fieldValue
+ * @param string $op
+ * @param array $pseudoExtraParam
+ * @param int $type
+ * Type of the field per CRM_Utils_Type
*
* @return array
*/
- public static function buildQillForFieldValue($daoName = NULL, $fieldName, $fieldValue, $op, $pseduoExtraParam = array()) {
+ public static function buildQillForFieldValue(
+ $daoName,
+ $fieldName,
+ $fieldValue,
+ $op,
+ $pseudoExtraParam = array(),
+ $type = CRM_Utils_Type::T_STRING
+ ) {
$qillOperators = CRM_Core_SelectValues::getSearchBuilderOperators();
+ // if Operator chosen is NULL/EMPTY then
+ if (strpos($op, 'NULL') !== FALSE || strpos($op, 'EMPTY') !== FALSE) {
+ return array(CRM_Utils_Array::value($op, $qillOperators, $op), '');
+ }
+
if ($fieldName == 'activity_type_id') {
- $pseduoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
+ $pseudoOptions = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
+ }
+ elseif ($fieldName == 'country_id') {
+ $pseduoOptions = CRM_Core_PseudoConstant::country();
+ }
+ elseif ($fieldName == 'county_id') {
+ $pseduoOptions = CRM_Core_PseudoConstant::county();
+ }
+ elseif ($fieldName == 'world_region') {
+ $pseduoOptions = CRM_Core_PseudoConstant::worldRegion();
}
elseif ($daoName == 'CRM_Event_DAO_Event' && $fieldName == 'id') {
- $pseduoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE);
+ $pseudoOptions = CRM_Event_BAO_Event::getEvents(0, $fieldValue, TRUE, TRUE, TRUE);
}
elseif ($fieldName == 'contribution_product_id') {
- $pseduoOptions = CRM_Contribute_PseudoConstant::products();
+ $pseudoOptions = CRM_Contribute_PseudoConstant::products();
}
elseif ($daoName == 'CRM_Contact_DAO_Group' && $fieldName == 'id') {
- $pseduoOptions = CRM_Core_PseudoConstant::group();
+ $pseudoOptions = CRM_Core_PseudoConstant::group();
+ }
+ elseif ($fieldName == 'country_id') {
+ $pseudoOptions = CRM_Core_PseudoConstant::country();
}
elseif ($daoName) {
- $pseduoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseduoExtraParam = array());
+ $pseudoOptions = CRM_Core_PseudoConstant::get($daoName, $fieldName, $pseudoExtraParam);
}
//API usually have fieldValue format as array(operator => array(values)),
if (is_array($fieldValue)) {
$qillString = array();
- if (!empty($pseduoOptions)) {
+ if (!empty($pseudoOptions)) {
foreach ((array) $fieldValue as $val) {
- $qillString[] = CRM_Utils_Array::value($val, $pseduoOptions, $val);
+ $qillString[] = CRM_Utils_Array::value($val, $pseudoOptions, $val);
}
$fieldValue = implode(', ', $qillString);
}
else {
- $fieldValue = implode(', ', $fieldValue);
+ if ($type == CRM_Utils_Type::T_DATE) {
+ foreach ($fieldValue as $index => $value) {
+ $fieldValue[$index] = CRM_Utils_Date::customFormat($value);
+ }
+ }
+ $separator = ', ';
+ // @todo - this is a bit specific (one operator).
+ // However it is covered by a unit test so can be altered later with
+ // some confidence.
+ if ($op == 'BETWEEN') {
+ $separator = ' AND ';
+ }
+ $fieldValue = implode($separator, $fieldValue);
}
}
- elseif (!empty($pseduoOptions) && array_key_exists($fieldValue, $pseduoOptions)) {
- $fieldValue = $pseduoOptions[$fieldValue];
+ elseif (!empty($pseudoOptions) && array_key_exists($fieldValue, $pseudoOptions)) {
+ $fieldValue = $pseudoOptions[$fieldValue];
+ }
+ elseif ($type === CRM_Utils_Type::T_DATE) {
+ $fieldValue = CRM_Utils_Date::customFormat($fieldValue);
}
return array(CRM_Utils_Array::value($op, $qillOperators, $op), $fieldValue);
projects / civicrm-core.git / blobdiff