parent::postProcess();
}
+ public function groupBy() {
+ $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_entity_financial_trxn']}.id, {$this->_aliases['civicrm_line_item']}.id ";
+ }
+
/**
* @param $rows
*
*/
public function statistics(&$rows) {
$statistics = parent::statistics($rows);
-
- $select = " SELECT COUNT({$this->_aliases['civicrm_financial_trxn']}.id ) as count,
- {$this->_aliases['civicrm_contribution']}.currency,
- SUM(CASE
- WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL
- THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount
- ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount
- END) as amount
+ $tempTableName = CRM_Core_DAO::createTempTableName('civicrm_contribution');
+ $select = "SELECT {$this->_aliases['civicrm_contribution']}.id, {$this->_aliases['civicrm_entity_financial_trxn']}.id as trxnID, {$this->_aliases['civicrm_contribution']}.currency,
+ CASE
+ WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL
+ THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount
+ ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount
+ END as amount
";
- $sql = "{$select} {$this->_from} {$this->_where}
- GROUP BY {$this->_aliases['civicrm_contribution']}.currency
-";
+ $tempQuery = "CREATE TEMPORARY TABLE {$tempTableName} CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
+ {$select} {$this->_from} {$this->_where} {$this->_groupBy} ";
+ CRM_Core_DAO::executeQuery($tempQuery);
+ $sql = "SELECT COUNT(trxnID) as count, SUM(amount) as amount, currency
+ FROM {$tempTableName}
+ GROUP BY currency";
$dao = CRM_Core_DAO::executeQuery($sql);
$amount = $avg = array();
while ($dao->fetch()) {