X-Git-Url: https://vcs.fsf.org/?a=blobdiff_plain;f=CRM%2FContact%2FBAO%2FQuery.php;h=381ced224b37361b308970c25e201623f65f9cb4;hb=21f4b6087e0bf1da354eee40927fc4a9a7564aa0;hp=8a8900491d9339b525ae101af4b1446fb405ce31;hpb=d7f4ff51b9e4fe44f720fa9f4cb321623fd48fe4;p=civicrm-core.git diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 8a8900491d..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 * @@ -634,14 +640,9 @@ class CRM_Contact_BAO_Query { } } } - // Fixme: this stuff does not need to be hard-coded, should be retrieved from schema metadata + if (in_array($name, array('prefix_id', 'suffix_id', 'gender_id'))) { - if ( - // Hack for default search view - !empty($this->_returnProperties[$field['pseudoconstant']['optionGroupName']]) || - // Hack for profile search view - !empty($this->_returnProperties[$name]) - ) { + if (CRM_Utils_Array::value($field['pseudoconstant']['optionGroupName'], $this->_returnProperties)) { $makeException = TRUE; } } @@ -720,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,42 +764,24 @@ class CRM_Contact_BAO_Query { } elseif ($fieldName != 'id') { if ($fieldName == 'prefix_id') { - // Hack - profile views use different field name than normal views! - $this->_pseudoConstantsSelect['prefix_id'] = - $this->_pseudoConstantsSelect['individual_prefix'] = array( - 'pseudoField' => 'prefix_id', - 'idCol' => "prefix_id", - 'bao' => 'CRM_Contact_BAO_Contact' - ); + $this->_pseudoConstantsSelect['individual_prefix'] = array('pseudoField' => 'prefix_id', 'idCol' => "prefix_id", 'bao' => 'CRM_Contact_BAO_Contact'); } if ($fieldName == 'suffix_id') { - // Hack - profile views use different field name than normal views! - $this->_pseudoConstantsSelect['suffix_id'] = - $this->_pseudoConstantsSelect['individual_suffix'] = array( - 'pseudoField' => 'suffix_id', - 'idCol' => "suffix_id", - 'bao' => 'CRM_Contact_BAO_Contact' - ); + $this->_pseudoConstantsSelect['individual_suffix'] = array('pseudoField' => 'suffix_id', 'idCol' => "suffix_id", 'bao' => 'CRM_Contact_BAO_Contact'); } if ($fieldName == 'gender_id') { - // Hack - profile views use different field name than normal views! - $this->_pseudoConstantsSelect['gender_id'] = - $this->_pseudoConstantsSelect['gender'] = array( - 'pseudoField' => 'gender_id', - 'idCol' => "gender_id", - 'bao' => 'CRM_Contact_BAO_Contact' - ); + $this->_pseudoConstantsSelect['gender'] = array('pseudoField' => 'gender_id', 'idCol' => "gender_id", 'bao' => 'CRM_Contact_BAO_Contact'); } $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`"; @@ -893,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); @@ -1203,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}`"; @@ -1257,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 @@ -1337,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) { @@ -1648,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': @@ -1961,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') { @@ -4277,7 +4280,6 @@ civicrm_relationship.is_permission_a_b = 0 break; } } - $this->generatePermissionClause($onlyDeleted, $count); // building the query string $groupBy = NULL; @@ -4376,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; @@ -4412,7 +4405,6 @@ civicrm_relationship.is_permission_a_b = 0 if ($returnQuery) { return $query; } - if ($count) { return CRM_Core_DAO::singleValueQuery($query); } @@ -4440,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"; @@ -4505,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(); @@ -4545,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()) { @@ -5109,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']}"; @@ -5171,7 +5192,6 @@ AND displayRelType.is_active = 1 /** * convert the pseudo constants id's to their names - * FIXME: Get rid of hard-coded references to fields, should be retrievable from schema metadata * * @param reference parameter $dao * @param bool $return @@ -5198,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);