/**
* Where / qill clause for relationship.
*
* @param array $values
*/
public function relationship(&$values)
{
list($name, $op, $value, $grouping, $wildcard) = $values;
if ($this->_relationshipValuesAdded) {
return;
}
// also get values array for relation_target_name
// for relationship search we always do wildcard
$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;
if ($targetName) {
$name = trim($targetName[2]);
if (substr($name, 0, 1) == '"' && substr($name, -1, 1) == '"') {
$name = substr($name, 1, -1);
$name = strtolower(CRM_Core_DAO::escapeString($name));
$nameClause = "= '{$name}'";
} else {
$name = strtolower(CRM_Core_DAO::escapeString($name));
$nameClause = "LIKE '%{$name}%'";
}
}
$rTypeValues = array();
if (!empty($relationType)) {
$rel = explode('_', $relationType[2]);
self::$_relType = $rel[1];
$params = array('id' => $rel[0]);
$rType = CRM_Contact_BAO_RelationshipType::retrieve($params, $rTypeValues);
}
if (!empty($rTypeValues) && $rTypeValues['name_a_b'] == $rTypeValues['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)) {
$where = array();
self::$_relationshipTempTable = $relationshipTempTable = CRM_Core_DAO::createTempTableName('civicrm_rel');
if ($nameClause) {
$where[$grouping][] = " sort_name {$nameClause} ";
}
} else {
$where =& $this->_where;
if ($nameClause) {
$where[$grouping][] = "( contact_b.sort_name {$nameClause} AND contact_b.id != contact_a.id )";
}
}
$allRelationshipType = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, NULL, TRUE);
if ($nameClause || !$targetGroup) {
if (!empty($relationType)) {
$this->_qill[$grouping][] = $allRelationshipType[$relationType[2]] . " {$name}";
} else {
$this->_qill[$grouping][] = $name;
}
}
//check to see if the target contact is in specified group
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'";
$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);
//set the group where clause
if ($ssWhere) {
$groupWhere[] = "( " . $ssWhere . " )";
}
$this->_where[$grouping][] = "( " . implode(" OR ", $groupWhere) . " )";
//Get the names of the target groups for the qill
$groupNames = CRM_Core_PseudoConstant::group();
$qillNames = array();
foreach ($targetGroup[2] as $groupId) {
if (array_key_exists($groupId, $groupNames)) {
$qillNames[] = $groupNames[$groupId];
}
}
if (!empty($relationType)) {
$this->_qill[$grouping][] = $allRelationshipType[$relationType[2]] . " ( " . implode(", ", $qillNames) . " )";
} else {
$this->_qill[$grouping][] = implode(", ", $qillNames);
}
}
// Note we do not currently set mySql to handle timezones, so doing this the old-fashioned way
$today = date('Ymd');
//check for active, inactive and all relation status
if ($relStatus[2] == 0) {
$where[$grouping][] = "(\ncivicrm_relationship.is_active = 1 AND\n( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= {$today} ) AND\n( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= {$today} )\n)";
$this->_qill[$grouping][] = ts('Relationship - Active and Current');
} elseif ($relStatus[2] == 1) {
$where[$grouping][] = "(\ncivicrm_relationship.is_active = 0 OR\ncivicrm_relationship.end_date < {$today} OR\ncivicrm_relationship.start_date > {$today}\n)";
$this->_qill[$grouping][] = ts('Relationship - Inactive or not Current');
}
$onlyDeleted = 0;
if (in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params)) {
$onlyDeleted = 1;
}
$where[$grouping][] = "(contact_b.is_deleted = {$onlyDeleted})";
//check for permissioned, non-permissioned and all permissioned relations
if ($relPermission[2] == 1) {
$where[$grouping][] = "(\ncivicrm_relationship.is_permission_a_b = 1\n)";
$this->_qill[$grouping][] = ts('Relationship - Permissioned');
} elseif ($relPermission[2] == 2) {
//non-allowed permission relationship.
$where[$grouping][] = "(\ncivicrm_relationship.is_permission_a_b = 0\n)";
$this->_qill[$grouping][] = ts('Relationship - Non-permissioned');
}
$this->addRelationshipDateClauses($grouping, $where);
if (!empty($relationType) && !empty($rType) && isset($rType->id)) {
$where[$grouping][] = 'civicrm_relationship.relationship_type_id = ' . $rType->id;
}
$this->_tables['civicrm_relationship'] = $this->_whereTables['civicrm_relationship'] = 1;
$this->_useDistinct = TRUE;
$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])) {
$whereClause = ' WHERE ' . implode(' AND ', $where[$grouping]);
$whereClause = str_replace('contact_b', 'c', $whereClause);
}
$sql = "\n CREATE TEMPORARY TABLE {$relationshipTempTable}\n (SELECT contact_id_b as contact_id, civicrm_relationship.id\n FROM civicrm_relationship\n INNER JOIN civicrm_contact c ON civicrm_relationship.contact_id_a = c.id\n {$whereClause} )\n UNION\n (SELECT contact_id_a as contact_id, civicrm_relationship.id\n FROM civicrm_relationship\n INNER JOIN civicrm_contact c ON civicrm_relationship.contact_id_b = c.id\n {$whereClause} )\n ";
CRM_Core_DAO::executeQuery($sql);
}
}
