From 979f1561aabfa440cd61cca58727dc3ce0fbb478 Mon Sep 17 00:00:00 2001 From: monishdeb Date: Mon, 22 Jun 2015 22:33:44 +0530 Subject: [PATCH] CRM-16716 fix - Recurring Contributions report improvements https://issues.civicrm.org/jira/browse/CRM-16716 --- CRM/Report/Form/Contribute/Recur.php | 58 +++++++++++++++++++++------- 1 file changed, 43 insertions(+), 15 deletions(-) diff --git a/CRM/Report/Form/Contribute/Recur.php b/CRM/Report/Form/Contribute/Recur.php index c2278f0d97..1b78473dba 100644 --- a/CRM/Report/Form/Contribute/Recur.php +++ b/CRM/Report/Form/Contribute/Recur.php @@ -120,6 +120,12 @@ class CRM_Report_Form_Contribute_Recur extends CRM_Report_Form { 'title' => ts('Frequency unit'), 'default' => TRUE, ), + 'installment_amount' => array( + 'title' => ts('Installment Amount'), + 'required' => TRUE, + 'default' => TRUE, + 'dbAlias' => 'ROUND(contribution_recur_civireport.amount/contribution_recur_civireport.installments, 2)' + ), 'installments' => array( 'title' => ts('Installments'), 'default' => TRUE, @@ -184,6 +190,12 @@ class CRM_Report_Form_Contribute_Recur extends CRM_Report_Form { 'title' => ts('Installments'), 'type' => CRM_Utils_Type::T_INT, ), + 'installment_amount' => array( + 'title' => ts('Installment Amount'), + 'type' => CRM_Utils_Type::T_MONEY, + 'dbAlias' => 'civicrm_contribution_recur_installment_amount', + 'having' => TRUE, + ), 'start_date' => array( 'title' => ts('Start Date'), 'operatorType' => CRM_Report_Form::OP_DATE, @@ -199,6 +211,11 @@ class CRM_Report_Form_Contribute_Recur extends CRM_Report_Form { 'operatorType' => CRM_Report_Form::OP_DATE, 'type' => CRM_Utils_Type::T_TIME, ), + 'contribution_processed_date' => array( + 'title' => ts('Last Contribution Processed'), + 'operatorType' => CRM_Report_Form::OP_DATE, + 'type' => CRM_Utils_Type::T_TIME, + ), 'calculated_end_date' => array( 'title' => ts('Calculated end date (either end date or date all installments will be made)'), 'description' => "does this work?", @@ -209,6 +226,7 @@ class CRM_Report_Form_Contribute_Recur extends CRM_Report_Form { ), ); $this->_currencyColumn = 'civicrm_contribution_recur_currency'; + $this->_groupFilter = TRUE; parent::__construct(); } @@ -255,6 +273,7 @@ class CRM_Report_Form_Contribute_Recur extends CRM_Report_Form { // with the interval unit (e.g. DATE_ADD(start_date, INTERVAL 12 * 1 MONTH) $date_suffixes = array('relative', 'from', 'to'); while (list(, $suffix) = each($date_suffixes)) { + $isBreak = FALSE; // Check to see if the user wants to search by calculated date. if (!empty($this->_params['calculated_end_date_' . $suffix])) { // The calculated date field is in use - spring into action @@ -278,32 +297,40 @@ class CRM_Report_Form_Contribute_Recur extends CRM_Report_Form { // date, etc) as a field name - so we have to build a complex // OR statement instead. - $installment_clause = '(' . + $this->_where .= 'AND (' . $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) month)", $relative, $from, $to, $start_date_type, NULL, NULL); - $installment_clause .= " AND $frequency_unit_db_alias = 'month' ) OR \n"; + $this->_where .= " AND $frequency_unit_db_alias = 'month' ) OR \n"; - $installment_clause .= '(' . + $this->_where .= '(' . $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias,1) day)", $relative, $from, $to, $start_date_type, NULL, NULL); - $installment_clause .= " AND $frequency_unit_db_alias = 'day' ) OR \n"; + $this->_where .= " AND $frequency_unit_db_alias = 'day' ) OR \n"; - $installment_clause .= '(' . + $this->_where .= '(' . $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) week)", $relative, $from, $to, $start_date_type, NULL, NULL); - $installment_clause .= " AND $frequency_unit_db_alias = 'week' ) OR \n"; + $this->_where .=" AND $frequency_unit_db_alias = 'week' ) OR \n"; - $installment_clause .= '(' . + $this->_where .= '(' . $this->dateClause("DATE_ADD($start_date_db_alias, INTERVAL $installments_db_alias * COALESCE($frequency_interval_db_alias, 1) year)", $relative, $from, $to, $start_date_type, NULL, NULL); - $installment_clause .= " AND $frequency_unit_db_alias = 'year' )\n"; - - $this->_where .= " AND "; - $this->_where .= "("; - $this->_where .= "($end_date_db_alias IS NOT NULL AND $end_date_clause)\n"; - $this->_where .= " OR \n"; - $this->_where .= "($installments_db_alias IS NOT NULL AND ($installment_clause))\n"; - $this->_where .= ')'; + $this->_where .= " AND $frequency_unit_db_alias = 'year' ) + AND (($end_date_db_alias IS NOT NULL AND $end_date_clause) + OR ($installments_db_alias IS NOT NULL)) +"; + $isBreak = TRUE; + } + if (!empty($this->_params['contribution_processed_date_' . $suffix])) { + $relative = CRM_Utils_Array::value("contribution_processed_date_relative", $this->_params); + $from = CRM_Utils_Array::value("contribution_processed_date_from", $this->_params); + $to = CRM_Utils_Array::value("contribution_processed_date_to", $this->_params); + $this->_where .= " AND contribution_civireport.id IN (SELECT MAX(cc.id) FROM civicrm_contribution cc WHERE cc.contribution_status_id = 1 AND (" . + $this->dateClause("DATE(cc.receive_date)", + $relative, $from, $to, CRM_Utils_Type::T_DATE, NULL, NULL) . " ) GROUP BY cc.contribution_recur_id)"; + $isBreak = TRUE; + } + if (!empty($isBreak)) { break; } } @@ -342,6 +369,7 @@ class CRM_Report_Form_Contribute_Recur extends CRM_Report_Form { if ($value = CRM_Utils_Array::value('civicrm_contribution_recur_amount', $row)) { $rows[$rowNum]['civicrm_contribution_recur_amount'] = CRM_Utils_Money::format($rows[$rowNum]['civicrm_contribution_recur_amount'], $rows[$rowNum]['civicrm_contribution_recur_currency']); + $rows[$rowNum]['civicrm_contribution_recur_installment_amount'] = CRM_Utils_Money::format($rows[$rowNum]['civicrm_contribution_recur_installment_amount'], $rows[$rowNum]['civicrm_contribution_recur_currency']); } } -- 2.25.1