*/
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
*
}
}
}
- // 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;
}
}
// 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;
}
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`";
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);
}
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}`";
* @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
$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) {
case 'activity_subject':
case 'test_activities':
case 'activity_type_id':
+ case 'activity_type':
case 'activity_survey_id':
case 'activity_tags':
case 'activity_taglist':
$name, $op, $value, $grouping,
CRM_Core_PseudoConstant::worldRegion(),
$field,
- ts('World Region')
+ ts('World Region'),
+ 'Positive',
+ TRUE
);
}
elseif ($name === 'birth_date') {
break;
}
}
- $this->generatePermissionClause($onlyDeleted, $count);
// building the query string
$groupBy = NULL;
// 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;
if ($returnQuery) {
return $query;
}
-
if ($count) {
return CRM_Core_DAO::singleValueQuery($query);
}
*/
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";
}
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();
}
// 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()) {
if ($tableName == 'civicrm_contact') {
$wc = "contact_a.$fieldName";
}
+ else {
+ $wc = "$tableName.id";
+ }
}
else {
$wc = self::caseImportant($op) ? "LOWER({$field['where']})" : "{$field['where']}";
/**
* 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
//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);
projects / civicrm-core.git / blobdiff