/** * Set statistics. * * @param array $rows * * @return array */ public function statistics(&$rows) { $statistics = parent::statistics($rows); $softCredit = CRM_Utils_Array::value('soft_amount', $this->_params['fields']); $onlySoftCredit = $softCredit && !CRM_Utils_Array::value('total_amount', $this->_params['fields']); $group = "\nGROUP BY {$this->_aliases['civicrm_contribution']}.currency"; $this->from('contribution'); $this->customDataFrom(); $contriQuery = "\nCOUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_count,\nSUM({$this->_aliases['civicrm_contribution']}.total_amount ) as civicrm_contribution_total_amount_sum,\nROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2) as civicrm_contribution_total_amount_avg,\n{$this->_aliases['civicrm_contribution']}.currency as currency\n{$this->_from} {$this->_where}"; if ($softCredit) { $this->from(); $select = "\nCOUNT({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_count,\nSUM({$this->_aliases['civicrm_contribution_soft']}.amount ) as civicrm_contribution_soft_soft_amount_sum,\nROUND(AVG({$this->_aliases['civicrm_contribution_soft']}.amount), 2) as civicrm_contribution_soft_soft_amount_avg"; $contriQuery = "{$select}, {$contriQuery}"; $softSQL = "SELECT {$select}, {$this->_aliases['civicrm_contribution']}.currency as currency\n {$this->_from} {$this->_where} {$group} {$this->_having}"; } $contriSQL = "SELECT {$contriQuery} {$group} {$this->_having}"; $contriDAO = CRM_Core_DAO::executeQuery($contriSQL); $totalAmount = $average = $mode = $median = $softTotalAmount = $softAverage = array(); $count = $softCount = 0; while ($contriDAO->fetch()) { $totalAmount[] = CRM_Utils_Money::format($contriDAO->civicrm_contribution_total_amount_sum, $contriDAO->currency) . " (" . $contriDAO->civicrm_contribution_total_amount_count . ")"; $average[] = CRM_Utils_Money::format($contriDAO->civicrm_contribution_total_amount_avg, $contriDAO->currency); $count += $contriDAO->civicrm_contribution_total_amount_count; } $groupBy = "\n{$group}, {$this->_aliases['civicrm_contribution']}.total_amount"; $orderBy = "\nORDER BY civicrm_contribution_total_amount_count DESC"; $modeSQL = "SELECT MAX(civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count,\n SUBSTRING_INDEX(GROUP_CONCAT(amount ORDER BY mode.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount,\n currency\n FROM (SELECT {$this->_aliases['civicrm_contribution']}.total_amount as amount,\n {$contriQuery} {$groupBy} {$orderBy}) as mode GROUP BY currency"; $mode = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL); $medianSQL = "{$this->_from} {$this->_where}"; $median = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, $this->_aliases['civicrm_contribution']); if ($softCredit) { $softDAO = CRM_Core_DAO::executeQuery($softSQL); while ($softDAO->fetch()) { $softTotalAmount[] = CRM_Utils_Money::format($softDAO->civicrm_contribution_soft_soft_amount_sum, $softDAO->currency) . " (" . $softDAO->civicrm_contribution_soft_soft_amount_count . ")"; $softAverage[] = CRM_Utils_Money::format($softDAO->civicrm_contribution_soft_soft_amount_avg, $softDAO->currency); $softCount += $softDAO->civicrm_contribution_soft_soft_amount_count; } } if (!$onlySoftCredit) { $statistics['counts']['amount'] = array('title' => ts('Total Amount'), 'value' => implode(', ', $totalAmount), 'type' => CRM_Utils_Type::T_STRING); $statistics['counts']['count'] = array('title' => ts('Total Contributions'), 'value' => $count); $statistics['counts']['avg'] = array('title' => ts('Average'), 'value' => implode(', ', $average), 'type' => CRM_Utils_Type::T_STRING); $statistics['counts']['mode'] = array('title' => ts('Mode'), 'value' => implode(', ', $mode), 'type' => CRM_Utils_Type::T_STRING); $statistics['counts']['median'] = array('title' => ts('Median'), 'value' => implode(', ', $median), 'type' => CRM_Utils_Type::T_STRING); } if ($softCredit) { $statistics['counts']['soft_amount'] = array('title' => ts('Total Soft Credit Amount'), 'value' => implode(', ', $softTotalAmount), 'type' => CRM_Utils_Type::T_STRING); $statistics['counts']['soft_count'] = array('title' => ts('Total Soft Credits'), 'value' => $softCount); $statistics['counts']['soft_avg'] = array('title' => ts('Average Soft Credit'), 'value' => implode(', ', $softAverage), 'type' => CRM_Utils_Type::T_STRING); } return $statistics; }
/** * @param null $context * * @return array */ public function &summaryContribution($context = NULL) { list($innerselect, $from, $where, $having) = $this->query(TRUE); // hack $select $select = "\nSELECT COUNT( conts.total_amount ) as total_count,\n SUM( conts.total_amount ) as total_amount,\n AVG( conts.total_amount ) as total_avg,\n conts.total_amount as amount,\n 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"; $innerQuery = "SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count,\n civicrm_contribution.currency {$from} {$completedWhere}"; $query = "{$select} FROM (\n {$innerQuery} GROUP BY civicrm_contribution.id\n ) as conts\n 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}, conts.civicrm_contribution_total_amount_count as civicrm_contribution_total_amount_count FROM ({$innerQuery}\n {$groupBy} {$orderBy}) as conts\n GROUP BY currency"; $summary['total']['mode'] = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL); $medianSQL = "{$from} {$completedWhere}"; $summary['total']['median'] = CRM_Contribute_BAO_Contribution::computeStats('median', $medianSQL, 'civicrm_contribution'); $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 = "{$completedWhere} AND civicrm_contribution_soft.id IS NOT NULL"; $query = "\n {$select} FROM (\n SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency {$from} {$softCreditWhere}\n GROUP BY civicrm_contribution_soft.id\n ) as conts\n 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 = "\nSELECT COUNT( conts.total_amount ) as cancel_count,\n SUM( conts.total_amount ) as cancel_amount,\n AVG( conts.total_amount ) as cancel_avg,\n 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 (\n SELECT civicrm_contribution.total_amount, civicrm_contribution.currency {$from} {$where}\n GROUP BY civicrm_contribution.id\n ) as conts\n 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'] = $dao->cancel_amount; $summary['cancel']['avg'] = $dao->cancel_avg; } } 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']); } return $summary; }