PHP CRM_Report_Form::statistics方法代碼示例

 function statistics(&$rows)
 {
     $statistics = parent::statistics($rows);
     if (!$this->_having) {
         $select = "\n            SELECT COUNT({$this->_aliases['civicrm_pledge']}.amount )       as count,\n                   SUM({$this->_aliases['civicrm_pledge']}.amount )         as amount,\n                   ROUND(AVG({$this->_aliases['civicrm_pledge']}.amount), 2) as avg\n            ";
         $sql = "{$select} {$this->_from} {$this->_where}";
         $dao = CRM_Core_DAO::executeQuery($sql);
         if ($dao->fetch()) {
             $statistics['counts']['amount'] = array('value' => $dao->amount, 'title' => 'Total Pledged', 'type' => CRM_Utils_Type::T_MONEY);
             $statistics['counts']['count '] = array('value' => $dao->count, 'title' => 'Total No Pledges');
             $statistics['counts']['avg   '] = array('value' => $dao->avg, 'title' => 'Average', 'type' => CRM_Utils_Type::T_MONEY);
         }
     }
     return $statistics;
 }

 function statistics(&$rows)
 {
     $statistics = parent::statistics($rows);
     $avg = null;
     $select = " SELECT SUM( {$this->_aliases['civicrm_line_item']}.participant_count ) as count,\n\t\t\t\t\t\t\t\t\tSUM( {$this->_aliases['civicrm_line_item']}.line_total )\t as amount\n\t\t\t\t\t\t";
     $sql = "{$select} {$this->_from} {$this->_where}";
     $dao = CRM_Core_DAO::executeQuery($sql);
     if ($dao->fetch()) {
         if ($dao->count && $dao->amount) {
             $avg = $dao->amount / $dao->count;
         }
         $statistics['counts']['count'] = array('value' => $dao->count, 'title' => 'Total Participants', 'type' => CRM_Utils_Type::T_INT);
         $statistics['counts']['amount'] = array('value' => $dao->amount, 'title' => 'Total Income', 'type' => CRM_Utils_Type::T_MONEY);
         $statistics['counts']['avg	  '] = array('value' => $avg, 'title' => 'Average', 'type' => CRM_Utils_Type::T_MONEY);
     }
     return $statistics;
 }

 /**
  * @param $rows
  *
  * @return array
  */
 public function statistics(&$rows)
 {
     $statistics = parent::statistics($rows);
     if (!empty($rows)) {
         $select = "\n                   SELECT\n                        SUM({$this->_aliases['civicrm_contribution']}.total_amount ) as amount ";
         $sql = "{$select} {$this->_from} {$this->_where}";
         $dao = CRM_Core_DAO::executeQuery($sql);
         if ($dao->fetch()) {
             $statistics['counts']['amount'] = array('value' => $dao->amount, 'title' => 'Total LifeTime', 'type' => CRM_Utils_Type::T_MONEY);
         }
     }
     return $statistics;
 }

 function statistics(&$rows)
 {
     return parent::statistics($rows);
 }

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

 function statistics(&$rows)
 {
     $statistics = parent::statistics($rows);
     $select = " SELECT COUNT({$this->_aliases['civicrm_financial_trxn']}.id ) as count,\n                {$this->_aliases['civicrm_contribution']}.currency,\n                SUM(CASE\n                  WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL\n                  THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount\n                  ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount\n                END) as amount\n";
     $sql = "{$select} {$this->_from} {$this->_where}\n            GROUP BY {$this->_aliases['civicrm_contribution']}.currency\n";
     $dao = CRM_Core_DAO::executeQuery($sql);
     while ($dao->fetch()) {
         $amount[] = CRM_Utils_Money::format($dao->amount, $dao->currency);
         $avg[] = CRM_Utils_Money::format(round($dao->amount / $dao->count, 2), $dao->currency);
     }
     $statistics['counts']['amount'] = array('value' => implode(', ', $amount), 'title' => 'Total Amount', 'type' => CRM_Utils_Type::T_STRING);
     $statistics['counts']['avg'] = array('value' => implode(', ', $avg), 'title' => 'Average', 'type' => CRM_Utils_Type::T_STRING);
     return $statistics;
 }

 function statistics(&$rows)
 {
     $statistics = parent::statistics($rows);
     //hack filter display for relationship type
     $type = substr($this->_params['relationship_type_id_value'], -3);
     foreach ($statistics['filters'] as $id => $value) {
         if ($value['title'] == 'Relationship Type') {
             $statistics['filters'][$id]['value'] = 'Is equal to ' . $this->relationTypes[$this->relationshipId . '_' . $type];
         }
     }
     return $statistics;
 }

 /**
  * @param $rows
  *
  * @return array
  */
 public function statistics(&$rows)
 {
     $statistics = parent::statistics($rows);
     $select = "SELECT DISTINCT {$this->_aliases['civicrm_contribution']}.id";
     $sql = "SELECT COUNT(cc.id) as count, SUM(cc.total_amount) as amount, ROUND(AVG(cc.total_amount), 2) as avg, cc.currency as currency\n            FROM civicrm_contribution cc\n            WHERE cc.id IN ({$select} {$this->_from} {$this->_where})\n            GROUP BY cc.currency";
     $dao = CRM_Core_DAO::executeQuery($sql);
     $totalAmount = $average = array();
     while ($dao->fetch()) {
         $totalAmount[] = CRM_Utils_Money::format($dao->amount, $dao->currency) . "(" . $dao->count . ")";
         $average[] = CRM_Utils_Money::format($dao->avg, $dao->currency);
     }
     $statistics['counts']['amount'] = array('title' => ts('Total Amount'), 'value' => implode(',  ', $totalAmount), 'type' => CRM_Utils_Type::T_STRING);
     $statistics['counts']['avg'] = array('title' => ts('Average'), 'value' => implode(',  ', $average), 'type' => CRM_Utils_Type::T_STRING);
     return $statistics;
 }

 function statistics(&$rows)
 {
     $statistics = parent::statistics($rows);
     //replace Advisor id by name
     if (CRM_Utils_Array::value('filters', $statistics)) {
         foreach ($statistics['filters'] as $key => $fields) {
             if (CRM_Utils_Array::value('title', $fields) == 'Advisor') {
                 $statistics['filters'][$key]['value'] = 'Is equal to ' . $this->teacherOptions[$this->_params['id_teacher_value']];
             }
         }
     }
     return $statistics;
 }

 /**
  * @param $rows
  *
  * @return array
  */
 public function statistics(&$rows)
 {
     $statistics = parent::statistics($rows);
     $select = "select COUNT( DISTINCT( {$this->_aliases['civicrm_address']}.country_id))";
     $sql = "{$select} {$this->_from} {$this->_where}";
     $countryCount = CRM_Core_DAO::singleValueQuery($sql);
     $statistics['counts']['case'] = array('title' => ts('Total Number of Cases '), 'value' => isset($statistics['counts']['rowsFound']) ? $statistics['counts']['rowsFound']['value'] : count($rows));
     $statistics['counts']['country'] = array('title' => ts('Total Number of Countries '), 'value' => $countryCount);
     return $statistics;
 }

 /**
  * @param $rows
  *
  * @return array
  */
 public function statistics(&$rows)
 {
     $statistics = parent::statistics($rows);
     $totalType = $totalActivity = $totalDuration = 0;
     $query = "SELECT {$this->_tempTableName}.civicrm_activity_activity_type_id,\n        {$this->_tempTableName}.civicrm_activity_id_count,\n        {$this->_tempDurationSumTableName}.civicrm_activity_duration_total\n    FROM {$this->_tempTableName} INNER JOIN {$this->_tempDurationSumTableName}\n      ON ({$this->_tempTableName}.id = {$this->_tempDurationSumTableName}.id)";
     $actDAO = CRM_Core_DAO::executeQuery($query);
     $activityTypesCount = array();
     while ($actDAO->fetch()) {
         if (!in_array($actDAO->civicrm_activity_activity_type_id, $activityTypesCount)) {
             $activityTypesCount[] = $actDAO->civicrm_activity_activity_type_id;
         }
         $totalActivity += $actDAO->civicrm_activity_id_count;
         $totalDuration += $actDAO->civicrm_activity_duration_total;
     }
     $totalType = count($activityTypesCount);
     $statistics['counts']['type'] = array('title' => ts('Total Types'), 'value' => $totalType);
     $statistics['counts']['activities'] = array('title' => ts('Total Number of Activities'), 'value' => $totalActivity);
     $statistics['counts']['duration'] = array('title' => ts('Total Duration (in Minutes)'), 'value' => $totalDuration);
     return $statistics;
 }

 function statistics(&$rows)
 {
     $statistics = parent::statistics($rows);
     $select = "\n        SELECT COUNT({$this->_aliases['civicrm_contribution']}.total_amount ) as count,\n               IFNULL(SUM({$this->_aliases['civicrm_contribution']}.total_amount ), 0) as amount,\n               IFNULL(ROUND(AVG({$this->_aliases['civicrm_contribution']}.total_amount), 2),0) as avg,\n               COUNT( DISTINCT {$this->_aliases['civicrm_membership']}.id ) as memberCount\n        ";
     $sql = "{$select} {$this->_from} {$this->_where}";
     $dao = CRM_Core_DAO::executeQuery($sql);
     if ($dao->fetch()) {
         $statistics['counts']['amount'] = array('value' => $dao->amount, 'title' => 'Total Amount', 'type' => CRM_Utils_Type::T_MONEY);
         $statistics['counts']['count '] = array('value' => $dao->count, 'title' => 'Total Donations');
         $statistics['counts']['memberCount'] = array('value' => $dao->memberCount, 'title' => 'Total Members');
         $statistics['counts']['avg   '] = array('value' => $dao->avg, 'title' => 'Average', 'type' => CRM_Utils_Type::T_MONEY);
         if (!(int) $statistics['counts']['amount']['value']) {
             //if total amount is zero then hide Chart Options
             $this->assign('chartSupported', false);
         }
     }
     return $statistics;
 }

 function statistics(&$rows)
 {
     $statistics = parent::statistics($rows);
     return $statistics;
 }

 function statistics(&$rows)
 {
     $statistics = parent::statistics($rows);
     $totalAmount = array();
     $count = 0;
     $select = "\n      SELECT \n      SUM( cg.{$this->customFields['time_scheduled_minutes']['column_name']} ) AS scheduled,\n      SUM( cg.{$this->customFields['time_completed_minutes']['column_name']} ) AS completed";
     $sql = "{$select} {$this->_from} {$this->_where}";
     $dao = CRM_Core_DAO::executeQuery($sql);
     while ($dao->fetch()) {
         $scheduled = $dao->scheduled;
         $completed = $dao->completed;
     }
     $statistics['counts']['scheduled'] = array('title' => ts('Total Time Scheduled in Minutes', array('domain' => 'org.civicrm.volunteer')), 'value' => $scheduled, 'type' => CRM_Utils_Type::T_STRING);
     $statistics['counts']['completed'] = array('title' => ts('Total Time Completed in Minutes', array('domain' => 'org.civicrm.volunteer')), 'value' => $completed, 'type' => CRM_Utils_Type::T_STRING);
     return $statistics;
 }

 function statistics(&$rows)
 {
     $statistics = parent::statistics($rows);
     $totalAmount = $average = array();
     $count = 0;
     $select = "\n        SELECT COUNT({$this->_aliases['civicrm_cdntaxreceipts_log']}.receipt_amount ) as count,\n               SUM( {$this->_aliases['civicrm_cdntaxreceipts_log']}.receipt_amount ) as amount,\n               ROUND(AVG({$this->_aliases['civicrm_cdntaxreceipts_log']}.receipt_amount), 2) as avg\n        ";
     $sql = "{$select}\n      FROM cdntaxreceipts_log {$this->_aliases['civicrm_cdntaxreceipts_log']}\n      {$this->_where}";
     $dao = CRM_Core_DAO::executeQuery($sql);
     while ($dao->fetch()) {
         $totalAmount[] = CRM_Utils_Money::format($dao->amount, 'CAD');
         $average[] = CRM_Utils_Money::format($dao->avg, 'CAD');
         $count += $dao->count;
     }
     $statistics['counts']['amount'] = array('title' => ts('Total Amount Issued', array('domain' => 'org.civicrm.cdntaxreceipts')), 'value' => implode(',  ', $totalAmount), 'type' => CRM_Utils_Type::T_STRING);
     $statistics['counts']['count'] = array('title' => ts('Number Issued', array('domain' => 'org.civicrm.cdntaxreceipts')), 'value' => $count);
     $statistics['counts']['avg'] = array('title' => ts('Average Amount Issued', array('domain' => 'org.civicrm.cdntaxreceipts')), 'value' => implode(',  ', $average), 'type' => CRM_Utils_Type::T_STRING);
     return $statistics;
 }