PHP CRM_Contribute_BAO_Contribution::computeStats方法代码示例

 /**
  * 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(',&nbsp;', $summary['cancel']['amount']);
//.........这里部分代码省略.........