// check for both id and contact_id
if ($this->_params[$id][0] == 'id' || $this->_params[$id][0] == 'contact_id') {
$this->_where[0][] = self::buildClause("contact_a.id", $this->_params[$id][1], $this->_params[$id][2]);
+ $field = CRM_Utils_Array::value('id', $this->_fields);
+ list($qillop, $qillVal) = CRM_Contact_BAO_Query::buildQillForFieldValue(
+ 'CRM_Contact_BAO_Contact',
+ "contact_a.id",
+ $this->_params[$id][2],
+ $this->_params[$id][1]
+ );
+ $this->_qill[0][] = ts("%1 %2 %3", [
+ 1 => $field['title'],
+ 2 => $qillop,
+ 3 => $qillVal
+ ]);
}
else {
$this->whereClauseSingle($this->_params[$id], $apiEntity);
*
* @return array
*/
- public function &summaryContribution($context = NULL) {
+ public function summaryContribution($context = NULL) {
list($innerselect, $from, $where, $having) = $this->query(TRUE);
-
- // hack $select
- $select = "
-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;
}
$where .= " AND contact_a.is_deleted = 0 ";
}
- $query = $this->appendFinancialTypeWhereAndFromToQueryStrings($where, $from);
-
- // 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";
- $innerQuery = "SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
- civicrm_contribution.currency $from $completedWhere";
- $query = "$select FROM (
- $innerQuery GROUP BY civicrm_contribution.id
- ) as conts
- GROUP BY currency";
-
- $dao = CRM_Core_DAO::executeQuery($query);
-
- $summary['total']['count'] = 0;
- $summary['total']['amount'] = $summary['total']['avg'] = array();
- 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);
- }
-
- $orderBy = 'ORDER BY civicrm_contribution_total_amount_count DESC';
- $groupBy = 'GROUP BY currency, civicrm_contribution.total_amount';
- $modeSQL = "$select, SUBSTRING_INDEX(GROUP_CONCAT(conts.total_amount
- ORDER BY conts.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount,
- MAX(conts.civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count
- FROM ($innerQuery
- $groupBy $orderBy) as conts
- GROUP BY currency";
-
- $summary['total']['mode'] = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL);
+ $this->appendFinancialTypeWhereAndFromToQueryStrings($where, $from);
- $medianSQL = "{$from} {$completedWhere}";
- $summary['total']['median'] = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, 'civicrm_contribution');
- $summary['total']['currencyCount'] = count($summary['total']['median']);
+ $summary = ['total' => []];
+ $this->addBasicStatsToSummary($summary, $where, $from);
- 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 = "{$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( 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";
-
- $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;
}
return $select;
}
+ /**
+ * Add basic statistics to the summary.
+ *
+ * @param array $summary
+ * @param string $where
+ * @param string $from
+ *
+ * @return array
+ */
+ protected function addBasicStatsToSummary(&$summary, $where, $from) {
+ $summary['total']['count'] = 0;
+ $summary['total']['amount'] = $summary['total']['avg'] = [];
+
+ $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.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,
+ civicrm_contribution.currency
+ $from
+ $where AND civicrm_contribution.contribution_status_id = 1
+ GROUP BY civicrm_contribution.id
+ ) as conts
+ GROUP BY currency";
+
+ $dao = CRM_Core_DAO::executeQuery($query);
+
+ 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'] = 0;
+ }
+ return $summary;
+ }
+
+ /**
+ * 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']);
+ }
+ }
+
}
projects / civicrm-core.git / blobdiff