return;
}
- $locationTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
+ $locationTypes = CRM_Core_DAO_Address::buildOptions('location_type_id', 'validate');
$processed = array();
$index = 0;
list($tbName, $fldName) = explode(".", $where);
//get the location name
- $locationType = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
+ $locationType = CRM_Core_DAO_Address::buildOptions('location_type_id', 'validate');
$specialFields = array('email', 'im', 'phone', 'openid', 'phone_ext');
if (in_array($locType[0], $specialFields)) {
//hack to fix / special handing for phone_ext
if ($locationTypeName) {
//we have a join on an location table - possibly in conjunction with search builder - CRM-14263
$parts = explode('-', $name);
- $locationTypes = CRM_Core_BAO_Address::buildOptions('location_type_id', 'get');
+ $locationTypes = CRM_Core_DAO_Address::buildOptions('location_type_id', 'validate');
foreach ($locationTypes as $locationTypeID => $locationType) {
if ($parts[0] == str_replace(' ', '_', $locationType)) {
$locationID = $locationTypeID;
}
/**
- * @param array $groups
- * @param string $tableAlias
- * @param string $joinTable
- * @param string $op
+ * Prime smart group cache for smart groups in the search, and join
+ * civicrm_group_contact_cache table into the query.
+ *
+ * @param array $groups IDs of groups specified in search criteria.
+ * @param string $tableAlias Alias to use for civicrm_group_contact_cache table.
+ * @param string $joinTable Table on which to join civicrm_group_contact_cache
+ * @param string $op SQL comparison operator (NULL, IN, !=, IS NULL, etc.)
+ * @param string $joinColumn Column in $joinTable on which to join civicrm_group_contact_cache.contact_id
*
- * @return null|string
+ * @return string WHERE clause component for smart group criteria.
*/
- public function addGroupContactCache($groups, $tableAlias = NULL, $joinTable = "contact_a", $op) {
+ public function addGroupContactCache($groups, $tableAlias = NULL, $joinTable = "contact_a", $op, $joinColumn = 'id') {
$isNullOp = (strpos($op, 'NULL') !== FALSE);
$groupsIds = $groups;
if (!$isNullOp && !$groups) {
$tableAlias .= ($isNullOp) ? "a`" : implode(',', (array) $groupsIds) . "`";
}
- $this->_tables[$tableAlias] = $this->_whereTables[$tableAlias] = " LEFT JOIN civicrm_group_contact_cache {$tableAlias} ON {$joinTable}.id = {$tableAlias}.contact_id ";
+ $this->_tables[$tableAlias] = $this->_whereTables[$tableAlias] = " LEFT JOIN civicrm_group_contact_cache {$tableAlias} ON {$joinTable}.{$joinColumn} = {$tableAlias}.contact_id ";
return self::buildClause("{$tableAlias}.group_id", $op, $groups, 'Int');
}
$n = trim($value);
if ($n) {
- $value = strtolower($n);
if (strpos($value, '%') === FALSE) {
// only add wild card if not there
$value = "%{$value}%";
}
$op = 'LIKE';
// LOWER roughly translates to 'hurt my database without deriving any benefit' See CRM-19811.
- $this->_where[$grouping][] = self::buildClause('LOWER(civicrm_address.street_address)', $op, $value, 'String');
+ $this->_where[$grouping][] = self::buildClause('civicrm_address.street_address', $op, $value, 'String');
$this->_qill[$grouping][] = ts('Street') . " $op '$n'";
}
else {
$relationType = $this->getWhereValues('relation_type_id', $grouping);
$targetName = $this->getWhereValues('relation_target_name', $grouping);
$relStatus = $this->getWhereValues('relation_status', $grouping);
- $relPermission = $this->getWhereValues('relation_permission', $grouping);
$targetGroup = $this->getWhereValues('relation_target_group', $grouping);
$nameClause = $name = NULL;
$relationType[2] = (array) $relationType[2];
foreach ($relationType[2] as $relType) {
$rel = explode('_', $relType);
- self::$_relType .= $rel[1];
+ self::$_relType = $rel[1];
$params = array('id' => $rel[0]);
$typeValues = array();
$rTypeValue = CRM_Contact_BAO_RelationshipType::retrieve($params, $typeValues);
$rTypeValue = (array) $rTypeValue;
if ($rTypeValue['name_a_b'] == $rTypeValue['name_b_a']) {
// if we don't know which end of the relationship we are dealing with we'll create a temp table
- //@todo unless we are dealing with a target group
self::$_relType = 'reciprocal';
}
}
}
// if we are creating a temp table we build our own where for the relationship table
$relationshipTempTable = NULL;
- if (self::$_relType == 'reciprocal' && empty($targetGroup)) {
+ if (self::$_relType == 'reciprocal') {
$where = array();
self::$_relationshipTempTable = $relationshipTempTable = CRM_Core_DAO::createTempTableName('civicrm_rel');
if ($nameClause) {
$where[$grouping][] = " sort_name $nameClause ";
}
+ $groupJoinTable = "civicrm_relationship";
+ $groupJoinColumn = "contact_id_alt";
}
else {
$where = &$this->_where;
if ($nameClause) {
$where[$grouping][] = "( contact_b.sort_name $nameClause AND contact_b.id != contact_a.id )";
}
+ $groupJoinTable = "contact_b";
+ $groupJoinColumn = "id";
}
$allRelationshipType = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, NULL, TRUE);
if ($nameClause || !$targetGroup) {
if ($targetGroup) {
//add contacts from static groups
$this->_tables['civicrm_relationship_group_contact'] = $this->_whereTables['civicrm_relationship_group_contact']
- = " LEFT JOIN civicrm_group_contact civicrm_relationship_group_contact ON civicrm_relationship_group_contact.contact_id = contact_b.id AND civicrm_relationship_group_contact.status = 'Added'";
+ = " LEFT JOIN civicrm_group_contact civicrm_relationship_group_contact ON civicrm_relationship_group_contact.contact_id = {$groupJoinTable}.{$groupJoinColumn} AND civicrm_relationship_group_contact.status = 'Added'";
$groupWhere[] = "( civicrm_relationship_group_contact.group_id IN (" .
implode(",", $targetGroup[2]) . ") ) ";
//add contacts from saved searches
- $ssWhere = $this->addGroupContactCache($targetGroup[2], "civicrm_relationship_group_contact_cache", "contact_b", $op);
+ $ssWhere = $this->addGroupContactCache($targetGroup[2], "civicrm_relationship_group_contact_cache", $groupJoinTable, $op, $groupJoinColumn);
//set the group where clause
if ($ssWhere) {
if (!empty($relQill)) {
$relQill .= ' OR ';
}
- $relQill .= $allRelationshipType[$rel];
+ $relQill .= CRM_Utils_Array::value($rel, $allRelationshipType);
}
$this->_qill[$grouping][] = 'Relationship Type(s) ' . $relQill . " ( " . implode(", ", $qillNames) . " )";
}
}
$where[$grouping][] = "(contact_b.is_deleted = {$onlyDeleted})";
- //check for permissioned, non-permissioned and all permissioned relations
- if ($relPermission[2] == 1) {
- $where[$grouping][] = "(
-civicrm_relationship.is_permission_a_b = 1
-)";
- $this->_qill[$grouping][] = ts('Relationship - Permissioned');
- }
- elseif ($relPermission[2] == 2) {
- //non-allowed permission relationship.
- $where[$grouping][] = "(
-civicrm_relationship.is_permission_a_b = 0
-)";
- $this->_qill[$grouping][] = ts('Relationship - Non-permissioned');
- }
-
+ $this->addRelationshipPermissionClauses($grouping, $where);
$this->addRelationshipDateClauses($grouping, $where);
$this->addRelationshipActivePeriodClauses($grouping, $where);
if (!empty($relTypes)) {
$this->_relationshipValuesAdded = TRUE;
// it could be a or b, using an OR creates an unindexed join - better to create a temp table &
// join on that,
- // @todo creating a temp table could be expanded to group filter
- // as even creating a temp table of all relationships is much much more efficient than
- // an OR in the join
if ($relationshipTempTable) {
$whereClause = '';
if (!empty($where[$grouping])) {
}
$sql = "
CREATE TEMPORARY TABLE {$relationshipTempTable}
- (SELECT contact_id_b as contact_id, civicrm_relationship.id
+ (SELECT contact_id_b as contact_id, contact_id_a as contact_id_alt, civicrm_relationship.id
FROM civicrm_relationship
INNER JOIN civicrm_contact c ON civicrm_relationship.contact_id_a = c.id
$whereClause )
UNION
- (SELECT contact_id_a as contact_id, civicrm_relationship.id
+ (SELECT contact_id_a as contact_id, contact_id_b as contact_id_alt, civicrm_relationship.id
FROM civicrm_relationship
INNER JOIN civicrm_contact c ON civicrm_relationship.contact_id_b = c.id
$whereClause )
}
}
+ public function addRelationshipPermissionClauses($grouping, &$where) {
+ $relPermission = $this->getWhereValues('relation_permission', $grouping);
+ if ($relPermission) {
+ $where[$grouping][] = "(civicrm_relationship.is_permission_a_b IN (" . implode(",", $relPermission[2]) . "))";
+
+ $allRelationshipPermissions = CRM_Contact_BAO_Relationship::buildOptions('is_permission_a_b');
+ $relQill = '';
+ foreach ($relPermission[2] as $rel) {
+ if (!empty($relQill)) {
+ $relQill .= ' OR ';
+ }
+ $relQill .= ts($allRelationshipPermissions[$rel]);
+ }
+ $this->_qill[$grouping][] = ts('Permissioned Relationships') . ' - ' . $relQill;
+ }
+ }
+
/**
* Add start & end date criteria in
* @param string $grouping
return $clause;
case 'IS EMPTY':
- $clause = " (NULLIF($field, '') IS NULL) ";
+ $clause = ($dataType == 'Date') ? " $field IS NULL " : " (NULLIF($field, '') IS NULL) ";
return $clause;
case 'IS NOT EMPTY':
- $clause = " (NULLIF($field, '') IS NOT NULL) ";
+ $clause = ($dataType == 'Date') ? " $field IS NOT NULL " : " (NULLIF($field, '') IS NOT NULL) ";
return $clause;
case 'IN':
}
default:
- if (empty($dataType)) {
+ if (empty($dataType) || $dataType == 'Date') {
$dataType = 'String';
}
if (is_array($value)) {
* @param $having
*/
public function filterRelatedContacts(&$from, &$where, &$having) {
- static $_rTypeProcessed = NULL;
- static $_rTypeFrom = NULL;
- static $_rTypeWhere = NULL;
-
- if (!$_rTypeProcessed) {
- $_rTypeProcessed = TRUE;
-
+ if (!isset(Civi::$statics[__CLASS__]['related_contacts_filter'])) {
+ Civi::$statics[__CLASS__]['related_contacts_filter'] = array();
+ }
+ $_rTempCache =& Civi::$statics[__CLASS__]['related_contacts_filter'];
+ // since there only can be one instance of this filter in every query
+ // skip if filter has already applied
+ foreach ($_rTempCache as $acache) {
+ foreach ($acache['queries'] as $aqcache) {
+ if (strpos($from, $aqcache['from']) !== FALSE) {
+ $having = NULL;
+ return;
+ }
+ }
+ }
+ $arg_sig = sha1("$from $where $having");
+ if (isset($_rTempCache[$arg_sig])) {
+ $cache = $_rTempCache[$arg_sig];
+ }
+ else {
// create temp table with contact ids
$tableName = CRM_Core_DAO::createTempTableName('civicrm_transform', TRUE);
+
$sql = "CREATE TEMPORARY TABLE $tableName ( contact_id int primary key) ENGINE=HEAP";
CRM_Core_DAO::executeQuery($sql);
";
CRM_Core_DAO::executeQuery($sql);
- $qillMessage = ts('Contacts with a Relationship Type of: ');
+ $cache = array('tableName' => $tableName, 'queries' => array());
+ $_rTempCache[$arg_sig] = $cache;
+ }
+ // upsert the query depending on relationship type
+ if (isset($cache['queries'][$this->_displayRelationshipType])) {
+ $qcache = $cache['queries'][$this->_displayRelationshipType];
+ }
+ else {
+ $tableName = $cache['tableName'];
+ $qcache = array(
+ "from" => "",
+ "where" => "",
+ );
$rTypes = CRM_Core_PseudoConstant::relationshipType();
-
if (is_numeric($this->_displayRelationshipType)) {
$relationshipTypeLabel = $rTypes[$this->_displayRelationshipType]['label_a_b'];
- $_rTypeFrom = "
+ $qcache['from'] = "
INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_a = contact_a.id OR displayRelType.contact_id_b = contact_a.id )
INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_a OR transform_temp.contact_id = displayRelType.contact_id_b )
";
- $_rTypeWhere = "
+ $qcache['where'] = "
WHERE displayRelType.relationship_type_id = {$this->_displayRelationshipType}
AND displayRelType.is_active = 1
";
list($relType, $dirOne, $dirTwo) = explode('_', $this->_displayRelationshipType);
if ($dirOne == 'a') {
$relationshipTypeLabel = $rTypes[$relType]['label_a_b'];
- $_rTypeFrom .= "
+ $qcache['from'] .= "
INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_a = contact_a.id )
INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_b )
";
}
else {
$relationshipTypeLabel = $rTypes[$relType]['label_b_a'];
- $_rTypeFrom .= "
+ $qcache['from'] .= "
INNER JOIN civicrm_relationship displayRelType ON ( displayRelType.contact_id_b = contact_a.id )
INNER JOIN $tableName transform_temp ON ( transform_temp.contact_id = displayRelType.contact_id_a )
";
}
- $_rTypeWhere = "
+ $qcache['where'] = "
WHERE displayRelType.relationship_type_id = $relType
AND displayRelType.is_active = 1
";
}
- $this->_qill[0][] = $qillMessage . "'" . $relationshipTypeLabel . "'";
+ $qcache['relTypeLabel'] = $relationshipTypeLabel;
+ $_rTempCache[$arg_sig]['queries'][$this->_displayRelationshipType] = $qcache;
}
-
- if (!empty($this->_permissionWhereClause)) {
- $_rTypeWhere .= "AND $this->_permissionWhereClause";
+ $qillMessage = ts('Contacts with a Relationship Type of: ');
+ $iqill = $qillMessage . "'" . $qcache['relTypeLabel'] . "'";
+ if (!is_array($this->_qill[0]) || !in_array($iqill, $this->_qill[0])) {
+ $this->_qill[0][] = $iqill;
}
-
- if (strpos($from, $_rTypeFrom) === FALSE) {
+ if (strpos($from, $qcache['from']) === FALSE) {
// lets replace all the INNER JOIN's in the $from so we dont exclude other data
// this happens when we have an event_type in the quert (CRM-7969)
$from = str_replace("INNER JOIN", "LEFT JOIN", $from);
- $from .= $_rTypeFrom;
- $where = $_rTypeWhere;
+ $from .= $qcache['from'];
+ $where = $qcache['where'];
+ if (!empty($this->_permissionWhereClause)) {
+ $where .= "AND $this->_permissionWhereClause";
+ }
}
$having = NULL;
projects / civicrm-core.git / blobdiff