X-Git-Url: https://vcs.fsf.org/?a=blobdiff_plain;f=CRM%2FContact%2FBAO%2FQuery.php;h=381ced224b37361b308970c25e201623f65f9cb4;hb=21f4b6087e0bf1da354eee40927fc4a9a7564aa0;hp=ba339290b30379ad7624804344e6a0f8dc091f65;hpb=4041c9b6822697707d1a559cbcc7a01ee2f0d275;p=civicrm-core.git diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index ba339290b3..381ced224b 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -338,6 +338,12 @@ class CRM_Contact_BAO_Query { */ public static $_openedPanes = array(); + /** + * For search builder - which custom fields are location-dependent + * @var array + */ + public $_locationSpecificCustomFields = array(); + /** * The tables which have a dependency on location and/or address * @@ -635,6 +641,12 @@ class CRM_Contact_BAO_Query { } } + if (in_array($name, array('prefix_id', 'suffix_id', 'gender_id'))) { + if (CRM_Utils_Array::value($field['pseudoconstant']['optionGroupName'], $this->_returnProperties)) { + $makeException = TRUE; + } + } + $cfID = CRM_Core_BAO_CustomField::getKeyID($name); if ( CRM_Utils_Array::value($name, $this->_paramLookup) || @@ -709,16 +721,20 @@ class CRM_Contact_BAO_Query { // also get the id of the tableName $tName = substr($tableName, 8); if (in_array($tName, array('country', 'state_province', 'county'))) { - $pf = ($tName == 'state_province') ? 'state_province_name' : $name; - $this->_pseudoConstantsSelect[$pf] = - array('pseudoField' => "{$tName}_id", 'idCol' => "{$tName}_id", 'bao' => 'CRM_Core_BAO_Address', - 'table' => "civicrm_{$tName}", 'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id "); - if ($tName == 'state_province') { + $this->_pseudoConstantsSelect['state_province_name'] = + array('pseudoField' => "{$tName}", 'idCol' => "{$tName}_id", 'bao' => 'CRM_Core_BAO_Address', + 'table' => "civicrm_{$tName}", 'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id "); + $this->_pseudoConstantsSelect[$tName] = array('pseudoField' => 'state_province_abbreviation', 'idCol' => "{$tName}_id", 'table' => "civicrm_{$tName}", 'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id "); } + else { + $this->_pseudoConstantsSelect[$name] = + array('pseudoField' => "{$tName}_id", 'idCol' => "{$tName}_id", 'bao' => 'CRM_Core_BAO_Address', + 'table' => "civicrm_{$tName}", 'join' => " LEFT JOIN civicrm_{$tName} ON civicrm_address.{$tName}_id = civicrm_{$tName}.id "); + } $this->_select["{$tName}_id"] = "civicrm_address.{$tName}_id as {$tName}_id"; $this->_element["{$tName}_id"] = 1; @@ -759,13 +775,13 @@ class CRM_Contact_BAO_Query { $this->_select[$name] = "contact_a.{$fieldName} as `$name`"; } } - elseif (in_array($tName, array('state_province', 'country', 'county'))) { - $this->_pseudoConstantsSelect[$pf]['select'] = "{$field['where']} as `$name`"; - $this->_pseudoConstantsSelect[$pf]['element'] = $name; - if ($tName == 'state_province') { - $this->_pseudoConstantsSelect[$tName]['select'] = "{$field['where']} as `$name`"; - $this->_pseudoConstantsSelect[$tName]['element'] = $name; - } + elseif (in_array($tName, array('country', 'county'))) { + $this->_pseudoConstantsSelect[$name]['select'] = "{$field['where']} as `$name`"; + $this->_pseudoConstantsSelect[$name]['element'] = $name; + } + elseif ($tName == 'state_province') { + $this->_pseudoConstantsSelect[$tName]['select'] = "{$field['where']} as `$name`"; + $this->_pseudoConstantsSelect[$tName]['element'] = $name; } else { $this->_select[$name] = "{$field['where']} as `$name`"; @@ -864,7 +880,7 @@ class CRM_Contact_BAO_Query { CRM_Contact_BAO_Query_Hook::singleton()->alterSearchQuery($this, 'select'); if (!empty($this->_cfIDs)) { - $this->_customQuery = new CRM_Core_BAO_CustomQuery($this->_cfIDs, TRUE); + $this->_customQuery = new CRM_Core_BAO_CustomQuery($this->_cfIDs, TRUE, $this->_locationSpecificCustomFields); $this->_customQuery->query(); $this->_select = array_merge($this->_select, $this->_customQuery->_select); $this->_element = array_merge($this->_element, $this->_customQuery->_element); @@ -1174,10 +1190,10 @@ class CRM_Contact_BAO_Query { } if (!empty($addressCustomFieldIds)) { - $cfIDs = $addressCustomFieldIds; - $customQuery = new CRM_Core_BAO_CustomQuery($cfIDs); + $customQuery = new CRM_Core_BAO_CustomQuery($addressCustomFieldIds); foreach ($addressCustomFieldIds as $cfID => $locTypeName) { foreach ($locTypeName as $name => $dnc) { + $this->_locationSpecificCustomFields[$cfID] = array($name, array_search($name, $locationTypes)); $fieldName = "$name-custom_{$cfID}"; $tName = "$name-address-custom-{$cfID}"; $aName = "`$name-address-custom-{$cfID}`"; @@ -1228,12 +1244,16 @@ class CRM_Contact_BAO_Query { * @param boolean $count * @param boolean $sortByChar * @param boolean $groupContacts + * @param boolean $onlyDeleted * * @return the sql string for that query (this will most likely * change soon) * @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->_distinctComponentClause)) { // we add distinct to get the right count for components @@ -1308,6 +1328,15 @@ class CRM_Contact_BAO_Query { $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) { @@ -1619,6 +1648,7 @@ class CRM_Contact_BAO_Query { case 'activity_subject': case 'test_activities': case 'activity_type_id': + case 'activity_type': case 'activity_survey_id': case 'activity_tags': case 'activity_taglist': @@ -1932,7 +1962,9 @@ class CRM_Contact_BAO_Query { $name, $op, $value, $grouping, CRM_Core_PseudoConstant::worldRegion(), $field, - ts('World Region') + ts('World Region'), + 'Positive', + TRUE ); } elseif ($name === 'birth_date') { @@ -2438,8 +2470,8 @@ class CRM_Contact_BAO_Query { continue; case 'civicrm_log': - $from .= " $side JOIN civicrm_log ON (civicrm_log.entity_id = contact_a.id AND civicrm_log.entity_table = 'civicrm_contact')"; - $from .= " $side JOIN civicrm_contact contact_b_log ON (civicrm_log.modified_id = contact_b_log.id)"; + $from .= " INNER JOIN civicrm_log ON (civicrm_log.entity_id = contact_a.id AND civicrm_log.entity_table = 'civicrm_contact')"; + $from .= " INNER JOIN civicrm_contact contact_b_log ON (civicrm_log.modified_id = contact_b_log.id)"; continue; case 'civicrm_tag': @@ -3647,7 +3679,7 @@ WHERE id IN ( $groupIDs ) $name = $targetName[4] ? "%$name%" : $name; $this->_where[$grouping][] = "contact_b_log.sort_name LIKE '%$name%'"; $this->_tables['civicrm_log'] = $this->_whereTables['civicrm_log'] = 1; - $this->_qill[$grouping][] = ts('Changed by') . ": $name"; + $this->_qill[$grouping][] = ts('Modified by') . ": $name"; } function modifiedDates($values) { @@ -3655,18 +3687,20 @@ WHERE id IN ( $groupIDs ) // CRM-11281, default to added date if not set $fieldTitle = ts('Added Date'); - + $fieldName = 'created_date'; foreach (array_keys($this->_params) as $id) { if ($this->_params[$id][0] == 'log_date') { if ($this->_params[$id][2] == 2) { $fieldTitle = ts('Modified Date'); + $fieldName = 'modified_date'; } } } - $this->dateQueryBuilder($values, - 'civicrm_log', 'log_date', 'modified_date', $fieldTitle - ); + + $this->dateQueryBuilder($values, 'contact_a', 'log_date', $fieldName, $fieldTitle); + + self::$_openedPanes[ts('Change Log')] = TRUE; } function demographics(&$values) { @@ -3745,7 +3779,10 @@ WHERE id IN ( $groupIDs ) list($name, $op, $value, $grouping, $wildcard) = $values; $pref = array(); - if (!is_array($value)) { + 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) { @@ -3772,10 +3809,16 @@ WHERE id IN ( $groupIDs ) $commPref = CRM_Core_PseudoConstant::get('CRM_Contact_DAO_Contact', 'preferred_communication_method'); $sqlValue = array(); + $showValue = array(); $sql = "contact_a.preferred_communication_method"; - foreach ($pref as $val) { - $sqlValue[] = "( $sql like '%" . CRM_Core_DAO::VALUE_SEPARATOR . $val . CRM_Core_DAO::VALUE_SEPARATOR . "%' ) "; - $showValue[] = $commPref[$val]; + if (in_array($op, array('IS NULL', 'IS NOT NULL', 'IS EMPTY', 'IS NOT EMPTY'))) { + $sqlValue[] = "{$sql} {$op}"; + } + 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); @@ -4237,7 +4280,6 @@ civicrm_relationship.is_permission_a_b = 0 break; } } - $this->generatePermissionClause($onlyDeleted, $count); // building the query string $groupBy = NULL; @@ -4336,16 +4378,7 @@ civicrm_relationship.is_permission_a_b = 0 // 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; @@ -4372,7 +4405,6 @@ civicrm_relationship.is_permission_a_b = 0 if ($returnQuery) { return $query; } - if ($count) { return CRM_Core_DAO::singleValueQuery($query); } @@ -4400,7 +4432,8 @@ civicrm_relationship.is_permission_a_b = 0 */ function getCachedContacts($cacheKey, $offset, $rowCount, $includeContactIds) { $this->_includeContactIds = $includeContactIds; - list($select, $from, $where) = $this->query(); + $onlyDeleted = in_array(array('deleted_contacts', '=', '1', '0', '0'), $this->_params); + list($select, $from, $where) = $this->query(FALSE, FALSE, FALSE, $onlyDeleted); $from = " FROM civicrm_prevnext_cache pnc INNER JOIN civicrm_contact contact_a ON contact_a.id = pnc.entity_id1 AND pnc.cacheKey = '$cacheKey' " . substr($from, 31); $order = " ORDER BY pnc.id"; $groupBy = " GROUP BY contact_a.id"; @@ -4465,29 +4498,36 @@ civicrm_relationship.is_permission_a_b = 0 } function &summaryContribution($context = NULL) { - list($select, $from, $where, $having) = $this->query(TRUE); + list($innerselect, $from, $where, $having) = $this->query(TRUE); // hack $select $select = " -SELECT COUNT( civicrm_contribution.total_amount ) as total_count, - SUM( civicrm_contribution.total_amount ) as total_amount, - AVG( civicrm_contribution.total_amount ) as total_avg, - civicrm_contribution.currency as currency"; - - // make sure contribution is completed - CRM-4989 - $where .= " AND civicrm_contribution.contribution_status_id = 1 "; +SELECT COUNT( conts.total_amount ) as total_count, + SUM( conts.total_amount ) as total_amount, + AVG( conts.total_amount ) as total_avg, + conts.currency as currency"; + if($this->_permissionWhereClause) { + $where .= " AND " . $this->_permissionWhereClause; + } if ($context == 'search') { $where .= " AND contact_a.is_deleted = 0 "; } + // make sure contribution is completed - CRM-4989 + $completedWhere = $where . " AND civicrm_contribution.contribution_status_id = 1 "; + + $summary = array(); $summary['total'] = array(); $summary['total']['count'] = $summary['total']['amount'] = $summary['total']['avg'] = "n/a"; - $query = "$select $from $where GROUP BY currency"; - $params = array(); + $query = "$select FROM ( + SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $completedWhere + GROUP BY civicrm_contribution.id + ) as conts + GROUP BY currency"; - $dao = CRM_Core_DAO::executeQuery($query, $params); + $dao = CRM_Core_DAO::executeQuery($query); $summary['total']['count'] = 0; $summary['total']['amount'] = $summary['total']['avg'] = array(); @@ -4505,19 +4545,37 @@ SELECT COUNT( civicrm_contribution.total_amount ) as total_count, } // hack $select + //@todo - this could be one query using the IF in mysql - eg + // SELECT sum(total_completed), sum(count_completed), sum(count_cancelled), sum(total_cancelled) FROM ( + // SELECT civicrm_contribution.total_amount, civicrm_contribution.currency , + // IF(civicrm_contribution.contribution_status_id = 1, 1, 0 ) as count_completed, + // IF(civicrm_contribution.contribution_status_id = 1, total_amount, 0 ) as total_completed, + // IF(civicrm_contribution.cancel_date IS NOT NULL = 1, 1, 0 ) as count_cancelled, + // IF(civicrm_contribution.cancel_date IS NOT NULL = 1, total_amount, 0 ) as total_cancelled + // FROM civicrm_contact contact_a + // LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id + // WHERE ( ... where clause.... + // AND (civicrm_contribution.cancel_date IS NOT NULL OR civicrm_contribution.contribution_status_id = 1) + // ) as conts + $select = " -SELECT COUNT( civicrm_contribution.total_amount ) as cancel_count, - SUM( civicrm_contribution.total_amount ) as cancel_amount, - AVG( civicrm_contribution.total_amount ) as cancel_avg, - civicrm_contribution.currency as currency"; +SELECT COUNT( conts.total_amount ) as cancel_count, + SUM( conts.total_amount ) as cancel_amount, + AVG( conts.total_amount ) as cancel_avg, + conts.currency as currency"; $where .= " AND civicrm_contribution.cancel_date IS NOT NULL "; if ($context == 'search') { $where .= " AND contact_a.is_deleted = 0 "; } - $query = "$select $from $where GROUP BY currency"; - $dao = CRM_Core_DAO::executeQuery($query, $params); + $query = "$select FROM ( + SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $where + GROUP BY civicrm_contribution.id + ) as conts + GROUP BY currency"; + + $dao = CRM_Core_DAO::executeQuery($query); if ($dao->N <= 1) { if ($dao->fetch()) { @@ -4736,16 +4794,6 @@ SELECT COUNT( civicrm_contribution.total_amount ) as cancel_count, $this->_tables[$tableName] = $this->_whereTables[$tableName] = 1; $this->_qill[$grouping][] = "$fieldTitle - $phrase \"$format\""; } - - if ( - $tableName == 'civicrm_log' && - $fieldTitle == ts('Added Date') - ) { - //CRM-6903 --hack to check modified date of first record. - //as added date means first modified date of object. - $addedDateQuery = 'select id from civicrm_log group by entity_id order by id'; - $this->_where[$grouping][] = "civicrm_log.id IN ( {$addedDateQuery} )"; - } } function numberRangeBuilder(&$values, @@ -5079,6 +5127,9 @@ AND displayRelType.is_active = 1 if ($tableName == 'civicrm_contact') { $wc = "contact_a.$fieldName"; } + else { + $wc = "$tableName.id"; + } } else { $wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}"; @@ -5167,7 +5218,7 @@ AND displayRelType.is_active = 1 //preserve id value $idColumn = "{$key}_id"; $dao->$idColumn = $val; - $dao->$key = CRM_Core_PseudoConstant::getLabel($baoName, $value['pseudoField'], $val); + $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);