From 1ebef7494d6dac4275893abaf2fc854e01d7cca6 Mon Sep 17 00:00:00 2001 From: Deepak Srivastava Date: Wed, 5 Jun 2013 18:28:30 +0530 Subject: [PATCH] CRM-12490, towards issues raised by dgg ---------------------------------------- * CRM-12490: Add Soft Credit data to the Contribution Detail Report http://issues.civicrm.org/jira/browse/CRM-12490 --- CRM/Report/Form/Contribute/Detail.php | 69 +++++++++++++++------------ 1 file changed, 38 insertions(+), 31 deletions(-) diff --git a/CRM/Report/Form/Contribute/Detail.php b/CRM/Report/Form/Contribute/Detail.php index 6550a5b7be..bf94aca5ce 100644 --- a/CRM/Report/Form/Contribute/Detail.php +++ b/CRM/Report/Form/Contribute/Detail.php @@ -189,7 +189,7 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { ), 'contribution_status_id' => array('title' => ts('Contribution Status'), ), - 'source' => array('title' => ts('Source'), + 'source' => array('title' => ts('Source'), ), 'payment_instrument_id' => array('title' => ts('Payment Type'), ), @@ -213,6 +213,17 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { ), 'filters' => array( + 'contribution_or_soft' => + array('title' => ts('Contribution OR Soft Credit?'), + 'clause' => "(1)", + 'operatorType' => CRM_Report_Form::OP_SELECT, + 'type' => CRM_Utils_Type::T_STRING, + 'options' => array( + 'both' => ts('Both'), + 'contributions_only' => ts('Contributions Only'), + 'soft_credits_only' => ts('Soft Credits Only'), + ), + ), 'receive_date' => array('operatorType' => CRM_Report_Form::OP_DATE), 'currency' => @@ -348,18 +359,6 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { } $this->_currencyColumn = 'civicrm_contribution_currency'; - - $this->_options = array( - 'include_set' => - array('title' => ts('Include'), - 'type' => 'select', - 'options' => array( - 'contributions_only' => ts('Contributions Only'), - 'soft_credits_only' => ts('Soft Credits Only'), - 'both' => ts('Both'), - ), - ), - ); parent::__construct(); } @@ -441,7 +440,7 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom} INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND {$this->_aliases['civicrm_contribution']}.is_test = 0"; - if (CRM_Utils_Array::value('include_set', $this->_params) == 'soft_credits_only') { + if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) == 'soft_credits_only') { $this->_from .= " INNER JOIN civicrm_contribution_soft contribution_soft_civireport ON contribution_soft_civireport.contribution_id = {$this->_aliases['civicrm_contribution']}.id"; @@ -553,7 +552,7 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { ); // Stats for soft credits - if (CRM_Utils_Array::value('include_set', $this->_params) != 'contributions_only') { + if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) != 'contributions_only') { $totalAmount = $average = array(); $count = 0; $select = " @@ -612,7 +611,7 @@ GROUP BY {$this->_aliases['civicrm_contribution']}.currency"; $sql = "{$select} {$this->_from} {$this->_groupBy}"; $tempQuery = 'CREATE TEMPORARY TABLE civireport_contribution_detail_temp2 AS ' . $sql; CRM_Core_DAO::executeQuery($tempQuery); - if (CRM_Utils_Array::value('include_set', $this->_params) == 'soft_credits_only') { + if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) == 'soft_credits_only') { // revise pager : prev, next based on soft-credits only $this->setPager(); } @@ -622,29 +621,36 @@ GROUP BY {$this->_aliases['civicrm_contribution']}.currency"; $this->from(); // simple reset of ->_from // 3. Decide where to populate temp3 table from - if (CRM_Utils_Array::value('include_set', $this->_params) == 'contributions_only') { - $tempQuery = "(SELECT * FROM civireport_contribution_detail_temp1 WHERE civicrm_contribution_contribution_id=%1)"; - } else if (CRM_Utils_Array::value('include_set', $this->_params) == 'soft_credits_only') { - $tempQuery = "(SELECT * FROM civireport_contribution_detail_temp2 WHERE civicrm_contribution_contribution_id=%1)"; + if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) == 'contributions_only') { + $tempQuery = "(SELECT * FROM civireport_contribution_detail_temp1)"; + } else if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) == 'soft_credits_only') { + $tempQuery = "(SELECT * FROM civireport_contribution_detail_temp2)"; } else { $tempQuery = " -(SELECT * FROM civireport_contribution_detail_temp1 WHERE civicrm_contribution_contribution_id=%1) +(SELECT * FROM civireport_contribution_detail_temp1) UNION ALL -(SELECT * FROM civireport_contribution_detail_temp2 WHERE civicrm_contribution_contribution_id=%1)"; +(SELECT * FROM civireport_contribution_detail_temp2)"; } // 4. build temp table 3 - $query = "select distinct civicrm_contribution_contribution_id FROM civireport_contribution_detail_temp1"; - $dao = CRM_Core_DAO::executeQuery($query); - while ($dao->fetch()) { - $temp3 = $temp3 ? 'INSERT INTO civireport_contribution_detail_temp3' : 'CREATE TEMPORARY TABLE civireport_contribution_detail_temp3 AS'; - $sql = "{$temp3} {$tempQuery}"; - CRM_Core_DAO::executeQuery($sql, array(1 => array($dao->civicrm_contribution_contribution_id, 'Integer'))); + $sql = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3 AS {$tempQuery}"; + CRM_Core_DAO::executeQuery($sql); + + // 5. Re-construct order-by to make sense for final query on temp3 table + $orderBy = ''; + if (!empty($this->_orderByArray)) { + $aliases = array_flip($this->_aliases); + $orderClause = array(); + foreach ($this->_orderByArray as $clause) { + list($alias, $rest) = explode('.', $clause); + $orderClause[] = $aliases[$alias] . "_" . $rest; + } + $orderBy = (!empty($orderClause)) ? "ORDER BY " . implode(', ', $orderClause) : ''; } - // 5. show result set from temp table 3 + // 6. show result set from temp table 3 $rows = array(); - $sql = "SELECT * FROM civireport_contribution_detail_temp3"; + $sql = "SELECT * FROM civireport_contribution_detail_temp3 {$orderBy}"; $this->buildRows($sql, $rows); // format result set. @@ -784,9 +790,10 @@ FROM civireport_contribution_detail_temp2 WHERE civicrm_contribution_contribution_id={$row['civicrm_contribution_contribution_id']}"; $dao = CRM_Core_DAO::executeQuery($query); $string = ''; + $separator = ($this->_outputMode !== 'csv') ? "
" : ' '; while ($dao->fetch()) { $url = CRM_Utils_System::url("civicrm/contact/view", 'reset=1&cid=' . $dao->civicrm_contact_id); - $string = $string . "\n{$dao->civicrm_contact_sort_name} " . + $string = $string . ($string ? $separator : '') . "{$dao->civicrm_contact_sort_name} " . CRM_Utils_Money::format($dao->civicrm_contribution_total_amount_sum, $dao->civicrm_contribution_currency); } $rows[$rowNum]['civicrm_contribution_soft_credits'] = $string; -- 2.25.1