/** * 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); } }
/** * where / qill clause for relationship * * @return void * @access public */ function relationship(&$values) { list($name, $op, $value, $grouping, $wildcard) = $values; // also get values array for relation_target_name // for relatinship search we always do wildcard $targetName = $this->getWhereValues('relation_target_name', $grouping); $relStatus = $this->getWhereValues('relation_status', $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}%'"; } } $rel = explode('_', $value); self::$_relType = $rel[1]; $params = array('id' => $rel[0]); $rTypeValues = array(); $rType = CRM_Contact_BAO_RelationshipType::retrieve($params, $rTypeValues); if (!$rType) { return; } if ($rTypeValues['name_a_b'] == $rTypeValues['name_b_a']) { self::$_relType = 'reciprocal'; } if ($nameClause) { $this->_where[$grouping][] = "( contact_b.sort_name {$nameClause} AND contact_b.id != contact_a.id )"; } $relTypeInd = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, 'Individual'); $relTypeOrg = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, 'Organization'); $relTypeHou = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, 'Household'); $allRelationshipType = array(); $allRelationshipType = array_merge($relTypeInd, $relTypeOrg); $allRelationshipType = array_merge($allRelationshipType, $relTypeHou); if ($nameClause || !$targetGroup) { $this->_qill[$grouping][] = "{$allRelationshipType[$value]} {$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"; $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"); //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]; } } $this->_qill[$grouping][] = "{$allRelationshipType[$value]} ( " . implode(", ", $qillNames) . " )"; } //check for active, inactive and all relation status $today = date('Ymd'); if ($relStatus[2] == 0) { $this->_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'); } elseif ($relStatus[2] == 1) { $this->_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'); } $this->_where[$grouping][] = 'civicrm_relationship.relationship_type_id = ' . $rel[0]; $this->_tables['civicrm_relationship'] = $this->_whereTables['civicrm_relationship'] = 1; $this->_useDistinct = TRUE; }
/** * where / qill clause for relationship * * @return void * @access public */ function relationship(&$values) { list($name, $op, $value, $grouping, $wildcard) = $values; // also get values array for relation_target_name // for relatinship search we always do wildcard $targetName = $this->getWhereValues('relation_target_name', $grouping); $relStatus = $this->getWhereValues('relation_status', $grouping); $nameClause = 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}%'"; } } $rel = explode('_', $value); self::$_relType = $rel[1]; if ($nameClause) { require_once 'CRM/Contact/BAO/RelationshipType.php'; $params = array('id' => $rel[0]); $rTypeValues = array(); require_once "CRM/Contact/BAO/RelationshipType.php"; $rType =& CRM_Contact_BAO_RelationshipType::retrieve($params, $rTypeValues); if (!$rType) { return; } // for relatinship search we always do wildcard if ($rTypeValues['name_a_b'] == $rTypeValues['name_b_a']) { self::$_relType = 'reciprocal'; } $this->_where[$grouping][] = "( contact_b.sort_name {$nameClause} AND contact_b.id != contact_a.id )"; } require_once 'CRM/Contact/BAO/Relationship.php'; $relTypeInd = CRM_Contact_BAO_Relationship::getContactRelationshipType(null, 'null', null, 'Individual'); $relTypeOrg = CRM_Contact_BAO_Relationship::getContactRelationshipType(null, 'null', null, 'Organization'); $relTypeHou = CRM_Contact_BAO_Relationship::getContactRelationshipType(null, 'null', null, 'Household'); $allRelationshipType = array(); $allRelationshipType = array_merge($relTypeInd, $relTypeOrg); $allRelationshipType = array_merge($allRelationshipType, $relTypeHou); $this->_qill[$grouping][] = "{$allRelationshipType[$value]} {$name}"; //check for active, inactive and all relation status $today = date('Ymd'); if ($relStatus[2] == 0) { $this->_where[$grouping][] = "civicrm_relationship.is_active = 1 AND ( civicrm_relationship.end_date is NULL OR civicrm_relationship.end_date >= {$today} )"; $this->_qill[$grouping][] = ts('Relationship - Active'); } else { if ($relStatus[2] == 1) { $this->_where[$grouping][] = "(civicrm_relationship.is_active = 0 OR civicrm_relationship.end_date < {$today})"; $this->_qill[$grouping][] = ts('Relationship - Inactive'); } } $this->_where[$grouping][] = 'civicrm_relationship.relationship_type_id = ' . $rel[0]; $this->_tables['civicrm_relationship'] = $this->_whereTables['civicrm_relationship'] = 1; $this->_useDistinct = true; }