*/
public $_distinctComponentClause;
+ public $_rowCountClause;
+
/**
* use groupBy component clause for component searches
*
}
foreach ($this->_params as $value) {
- if (!CRM_Utils_Array::value(0, $value)) {
+ if (empty($value[0])) {
continue;
}
$cfID = CRM_Core_BAO_CustomField::getKeyID($value[0]);
function addSpecialFields() {
static $special = array('contact_type', 'contact_sub_type', 'sort_name', 'display_name');
foreach ($special as $name) {
- if (CRM_Utils_Array::value($name, $this->_returnProperties)) {
+ if (!empty($this->_returnProperties[$name])) {
$this->_select[$name] = "contact_a.{$name} as $name";
$this->_element[$name] = 1;
}
}
$cfID = CRM_Core_BAO_CustomField::getKeyID($name);
- if (
- CRM_Utils_Array::value($name, $this->_paramLookup) ||
- CRM_Utils_Array::value($name, $this->_returnProperties) ||
+ if (!empty($this->_paramLookup[$name]) || !empty($this->_returnProperties[$name]) ||
$makeException
) {
if ($cfID) {
// 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;
$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`";
}
}
- if ($cfID &&
- CRM_Utils_Array::value('is_search_range', $field)
- ) {
+ if ($cfID && !empty($field['is_search_range'])) {
// this is a custom field with range search enabled, so we better check for two/from values
- if (CRM_Utils_Array::value($name . '_from', $this->_paramLookup)) {
+ if (!empty($this->_paramLookup[$name . '_from'])) {
if (!array_key_exists($cfID, $this->_cfIDs)) {
$this->_cfIDs[$cfID] = array();
}
}
}
}
- if (CRM_Utils_Array::value($name . '_to', $this->_paramLookup)) {
+ if (!empty($this->_paramLookup[$name . '_to'])) {
if (!array_key_exists($cfID, $this->_cfIDs)) {
$this->_cfIDs[$cfID] = array();
}
* @access public
*/
function addHierarchicalElements() {
- if (!CRM_Utils_Array::value('location', $this->_returnProperties)) {
+ if (empty($this->_returnProperties['location'])) {
return;
}
if (!is_array($this->_returnProperties['location'])) {
}
foreach ($this->_params as $id => $values) {
- if ($values[0] == $nm ||
+ if ((is_array($values) && $values[0] == $nm) ||
(in_array($elementName, array('phone', 'im'))
&& (strpos($values[0], $nm) !== FALSE)
)
$this->_element["{$name}-{$elementFullName}"] = 1;
}
- if (!CRM_Utils_Array::value("`$tName`", $processed)) {
+ if (empty($processed["`$tName`"])) {
$processed["`$tName`"] = 1;
$newName = $tableName . '_' . $index;
switch ($tableName) {
if ($addWhereCount) {
$locClause = array();
foreach ($this->_whereTables as $tableName => $clause) {
- if (CRM_Utils_Array::value($tableName, $locationTypeJoin)) {
+ if (!empty($locationTypeJoin[$tableName])) {
$locClause[] = $locationTypeJoin[$tableName];
}
}
* @access public
*/
function addMultipleElements() {
- if (!CRM_Utils_Array::value('website', $this->_returnProperties)) {
+ if (empty($this->_returnProperties['website'])) {
return;
}
if (!is_array($this->_returnProperties['website'])) {
*/
function query($count = FALSE, $sortByChar = FALSE, $groupContacts = FALSE) {
if ($count) {
- if (isset($this->_distinctComponentClause)) {
+ if (isset($this->_rowCountClause)) {
+ $select = "SELECT {$this->_rowCountClause}";
+ } else if (isset($this->_distinctComponentClause)) {
// we add distinct to get the right count for components
// for the more complex result set, we use GROUP BY the same id
// CRM-9630
$from = $this->_simpleFromClause;
}
else {
- if (CRM_Utils_Array::value('group', $this->_paramLookup)) {
+ if (!empty($this->_paramLookup['group'])) {
// make sure there is only one element
// this is used when we are running under smog and need to know
// how the contact was added (CRM-1203)
foreach ($formValues as $id => $values) {
if ($id == 'privacy') {
if (is_array($formValues['privacy'])) {
- $op = CRM_Utils_Array::value('do_not_toggle', $formValues['privacy']) ? '=' : '!=';
+ $op = !empty($formValues['privacy']['do_not_toggle']) ? '=' : '!=';
foreach ($formValues['privacy'] as $key => $value) {
if ($value) {
$params[] = array($key, $op, $value, 0, 0);
case 'activity_subject':
case 'test_activities':
case 'activity_type_id':
+ case 'activity_type':
case 'activity_survey_id':
case 'activity_tags':
case 'activity_taglist':
$this->includeContactIds();
if (!empty($this->_params)) {
foreach (array_keys($this->_params) as $id) {
- if (!CRM_Utils_Array::value(0, $this->_params[$id])) {
+ if (empty($this->_params[$id][0])) {
continue;
}
// check for both id and contact_id
$grouping = CRM_Utils_Array::value(3, $values);
$wildcard = CRM_Utils_Array::value(4, $values);
- if (isset($grouping) && !CRM_Utils_Array::value($grouping, $this->_where)) {
+ if (isset($grouping) && empty($this->_where[$grouping])) {
$this->_where[$grouping] = array();
}
}
$type = NULL;
- if (CRM_Utils_Array::value('type', $field)) {
+ if (!empty($field['type'])) {
$type = CRM_Utils_Type::typeToString($field['type']);
}
return $from;
}
- if (CRM_Utils_Array::value('civicrm_worldregion', $tables)) {
+ if (!empty($tables['civicrm_worldregion'])) {
$tables = array_merge(array('civicrm_country' => 1), $tables);
}
- if ((CRM_Utils_Array::value('civicrm_state_province', $tables) ||
- CRM_Utils_Array::value('civicrm_country', $tables) ||
+ if ((!empty($tables['civicrm_state_province']) || !empty($tables['civicrm_country']) ||
CRM_Utils_Array::value('civicrm_county', $tables)
- ) &&
- !CRM_Utils_Array::value('civicrm_address', $tables)
- ) {
+ ) && empty($tables['civicrm_address'])) {
$tables = array_merge(array('civicrm_address' => 1),
$tables
);
}
// add group_contact table if group table is present
- if (CRM_Utils_Array::value('civicrm_group', $tables) &&
- !CRM_Utils_Array::value('civicrm_group_contact', $tables)
- ) {
+ if (!empty($tables['civicrm_group']) && empty($tables['civicrm_group_contact'])) {
$tables['civicrm_group_contact'] = " LEFT JOIN civicrm_group_contact ON civicrm_group_contact.contact_id = contact_a.id AND civicrm_group_contact.status = 'Added'";
}
// add group_contact and group table is subscription history is present
- if (CRM_Utils_Array::value('civicrm_subscription_history', $tables)
- && !CRM_Utils_Array::value('civicrm_group', $tables)
- ) {
+ if (!empty($tables['civicrm_subscription_history']) && empty($tables['civicrm_group'])) {
$tables = array_merge(array(
'civicrm_group' => 1,
'civicrm_group_contact' => 1,
continue;
}
- if (CRM_Utils_Array::value($name, $inner)) {
+ if (!empty($inner[$name])) {
$side = 'INNER';
}
- elseif (CRM_Utils_Array::value($name, $right)) {
+ elseif (!empty($right[$name])) {
$side = 'RIGHT';
}
else {
// left join
$groupIDs = array_keys($value);
- if (CRM_Utils_Array::value(0, $groupIDs) &&
+ if (!empty($groupIDs[0]) &&
CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Group',
$groupIDs[0],
'saved_search_id'
*/
function postalCode(&$values) {
// skip if the fields dont have anything to do with postal_code
- if (!CRM_Utils_Array::value('postal_code', $this->_fields)) {
+ if (empty($this->_fields['postal_code'])) {
return;
}
}
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();
$summary['total']['amount'] = $summary['total']['avg'] = 0;
}
+ // soft credit summary
+ if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) {
+ $softCreditWhere = "{$completedWhere} AND civicrm_contribution_soft.id IS NOT NULL";
+ $query = "
+ $select FROM (
+ SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency $from $softCreditWhere
+ GROUP BY civicrm_contribution_soft.id
+ ) as conts
+ GROUP BY currency";
+ $dao = CRM_Core_DAO::executeQuery($query);
+ $summary['soft_credit']['count'] = 0;
+ $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = array();
+ while ($dao->fetch()) {
+ $summary['soft_credit']['count'] += $dao->total_count;
+ $summary['soft_credit']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency);
+ $summary['soft_credit']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency);
+ }
+ if (!empty($summary['soft_credit']['amount'])) {
+ $summary['soft_credit']['amount'] = implode(', ', $summary['soft_credit']['amount']);
+ $summary['soft_credit']['avg'] = implode(', ', $summary['soft_credit']['avg']);
+ }
+ else {
+ $summary['soft_credit']['amount'] = $summary['soft_credit']['avg'] = 0;
+ }
+ }
+
// 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()) {
}
$values = array();
foreach ($this->_pseudoConstantsSelect as $key => $value) {
- if (CRM_Utils_Array::value('sorting', $this->_pseudoConstantsSelect[$key])) {
+ if (!empty($this->_pseudoConstantsSelect[$key]['sorting'])) {
continue;
}
//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);
$present = array();
foreach ($this->_pseudoConstantsSelect as $name => $value) {
- if (CRM_Utils_Array::value('table', $value)) {
+ if (!empty($value['table'])) {
$regex = "/({$value['table']}\.|{$name})/";
if (preg_match($regex, $sort)) {
$this->_elemnt[$value['element']] = 1;