X-Git-Url: https://vcs.fsf.org/?a=blobdiff_plain;f=CRM%2FContact%2FBAO%2FQuery.php;h=5247434ab824ea3793a0b7e5ac44c2168b814b4d;hb=2027a5085d14bf284bda113e4b340c48ff59a981;hp=04e635399dc60721127b0c8e9403f8cb8cc34c72;hpb=650135feb36206f3e335b52f0aaf0fe85a5bb9bc;p=civicrm-core.git diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 04e635399d..5247434ab8 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -5103,99 +5103,13 @@ civicrm_relationship.start_date > {$today} $this->addModeToStats($summary, $from, $where); $this->addMedianToStats($summary, $where, $from); - $summary['total']['currencyCount'] = count($summary['total']['median']); - - if (!empty($summary['total']['amount'])) { - $summary['total']['amount'] = implode(', ', $summary['total']['amount']); - $summary['total']['avg'] = implode(', ', $summary['total']['avg']); - $summary['total']['mode'] = implode(', ', $summary['total']['mode']); - $summary['total']['median'] = implode(', ', $summary['total']['median']); - } - else { - $summary['total']['amount'] = $summary['total']['avg'] = $summary['total']['median'] = 0; - } - - // soft credit summary if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) { - $softCreditWhere = "$where AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution_soft.id IS NOT NULL"; - $query = " - 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 - 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( 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 "; + $this->addBasicSoftCreditStatsToStats($summary, $where, $from); } - $query = "$select FROM ( - SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $where - GROUP BY civicrm_contribution.id - ) as conts - GROUP BY currency"; + $summary['total']['currencyCount'] = count($summary['total']['median']); - $dao = CRM_Core_DAO::executeQuery($query); - - if ($dao->N <= 1) { - if ($dao->fetch()) { - $summary['cancel']['count'] = $dao->cancel_count; - $summary['cancel']['amount'] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency); - $summary['cancel']['avg'] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency); - } - } - else { - $summary['cancel']['count'] = 0; - $summary['cancel']['amount'] = $summary['cancel']['avg'] = array(); - while ($dao->fetch()) { - $summary['cancel']['count'] += $dao->cancel_count; - $summary['cancel']['amount'][] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency); - $summary['cancel']['avg'][] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency); - } - $summary['cancel']['amount'] = implode(', ', $summary['cancel']['amount']); - $summary['cancel']['avg'] = implode(', ', $summary['cancel']['avg']); - } + $this->addBasicCancelStatsToSummary($summary, $where, $from); return $summary; } @@ -6620,7 +6534,8 @@ AND displayRelType.is_active = 1 * @return array */ protected function addBasicStatsToSummary(&$summary, $where, $from) { - $summary['total']['count'] = $summary['total']['amount'] = $summary['total']['avg'] = "n/a"; + $summary['total']['count'] = 0; + $summary['total']['amount'] = $summary['total']['avg'] = []; $query = " SELECT COUNT( conts.total_amount ) as total_count, @@ -6638,13 +6553,18 @@ AND displayRelType.is_active = 1 $dao = CRM_Core_DAO::executeQuery($query); - $summary['total']['count'] = 0; - $summary['total']['amount'] = $summary['total']['avg'] = []; while ($dao->fetch()) { $summary['total']['count'] += $dao->total_count; $summary['total']['amount'][] = CRM_Utils_Money::format($dao->total_amount, $dao->currency); $summary['total']['avg'][] = CRM_Utils_Money::format($dao->total_avg, $dao->currency); } + if (!empty($summary['total']['amount'])) { + $summary['total']['amount'] = implode(', ', $summary['total']['amount']); + $summary['total']['avg'] = implode(', ', $summary['total']['avg']); + } + else { + $summary['total']['amount'] = $summary['total']['avg'] = $summary['total']['median'] = 0; + } return $summary; } @@ -6679,7 +6599,8 @@ AND displayRelType.is_active = 1 ) as conts GROUP BY currency"; - $summary['total']['mode'] = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL); + $mode = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL); + $summary['total']['mode'] = implode(', ', (array) $mode); } /** @@ -6697,7 +6618,89 @@ AND displayRelType.is_active = 1 */ protected function addMedianToStats(&$summary, $where, $from) { $medianSQL = "{$from} {$where} AND civicrm_contribution.contribution_status_id = 1 "; - $summary['total']['median'] = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, 'civicrm_contribution'); + $median = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, 'civicrm_contribution'); + $summary['total']['median'] = implode(', ', (array) $median); + } + + /** + * Add basic soft credit statistics to summary array. + * + * @param array $summary + * @param string $where + * @param string $from + */ + protected function addBasicSoftCreditStatsToStats(&$summary, $where, $from) { + $query = " + 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 + FROM ( + SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency + $from + $where AND civicrm_contribution.contribution_status_id = 1 AND civicrm_contribution_soft.id IS NOT NULL + 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'] = []; + 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; + } + } + + /** + * Add basic stats about cancelled contributions to the summary. + * + * @param array $summary + * @param string $where + * @param string $from + */ + protected function addBasicCancelStatsToSummary(&$summary, $where, $from) { + $query = " + 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 + FROM ( + SELECT civicrm_contribution.total_amount, civicrm_contribution.currency + $from + $where AND civicrm_contribution.cancel_date IS NOT NULL + GROUP BY civicrm_contribution.id + ) as conts + GROUP BY currency"; + + $dao = CRM_Core_DAO::executeQuery($query); + + if ($dao->N <= 1) { + if ($dao->fetch()) { + $summary['cancel']['count'] = $dao->cancel_count; + $summary['cancel']['amount'] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency); + $summary['cancel']['avg'] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency); + } + } + else { + $summary['cancel']['count'] = 0; + $summary['cancel']['amount'] = $summary['cancel']['avg'] = []; + while ($dao->fetch()) { + $summary['cancel']['count'] += $dao->cancel_count; + $summary['cancel']['amount'][] = CRM_Utils_Money::format($dao->cancel_amount, $dao->currency); + $summary['cancel']['avg'][] = CRM_Utils_Money::format($dao->cancel_avg, $dao->currency); + } + $summary['cancel']['amount'] = implode(', ', $summary['cancel']['amount']); + $summary['cancel']['avg'] = implode(', ', $summary['cancel']['avg']); + } } }