/**
* 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;
}
