From a7d034b35d9646817676100447c545cbc52a8e26 Mon Sep 17 00:00:00 2001 From: eileen Date: Mon, 7 Dec 2015 22:45:07 +1300 Subject: [PATCH] CRM-17837 / CRM-17680 fix core Lybunt to sort by the sort field (and add last year field to the list) Bug: T119911 Change-Id: I0dd1ce2375b9aeb66368fc6f2ced650dde039b64 --- 1 | 17 +++++ CRM/Report/Form/Contribute/Lybunt.php | 103 +++++++++++++++++++++++--- 2 files changed, 108 insertions(+), 12 deletions(-) create mode 100644 1 diff --git a/1 b/1 new file mode 100644 index 0000000000..b7339c3146 --- /dev/null +++ b/1 @@ -0,0 +1,17 @@ +Merge pull request #7630 from julialongtin/master + +Work around infinite memory consumption bug. CRM-17853 + +# Please enter the commit message for your changes. Lines starting +# with '#' will be ignored, and an empty message aborts the commit. +# +# Author: colemanw +# Date: Thu Jan 21 21:21:39 2016 -0500 +# +# rebase in progress; onto 474d975 +# You are currently rebasing branch 'lybunt' on '474d975'. +# +# Changes to be committed: +# modified: CRM/Core/Error.php +# modified: CRM/Report/Form/Contribute/Lybunt.php +# diff --git a/CRM/Report/Form/Contribute/Lybunt.php b/CRM/Report/Form/Contribute/Lybunt.php index ecd99c0f12..ad0946f4e2 100644 --- a/CRM/Report/Form/Contribute/Lybunt.php +++ b/CRM/Report/Form/Contribute/Lybunt.php @@ -110,7 +110,7 @@ class CRM_Report_Form_Contribute_Lybunt extends CRM_Report_Form { 'order_bys' => array( 'sort_name' => array( 'title' => ts('Last Name, First Name'), - 'default' => '1', + 'default' => '0', 'default_weight' => '0', 'default_order' => 'ASC', ), @@ -228,6 +228,15 @@ class CRM_Report_Form_Contribute_Lybunt extends CRM_Report_Form { 'default' => array('1'), ), ), + 'order_bys' => array( + 'total_amount' => array( + 'title' => ts('Total amount last year (affects available columns)'), + 'description' => ts('When ordering by this life time amount cannot be calculated'), + 'default' => '0', + 'default_weight' => '0', + 'default_order' => 'DESC', + ), + ), ), ); @@ -375,10 +384,42 @@ class CRM_Report_Form_Contribute_Lybunt extends CRM_Report_Form { } } + /** + * Generate where clause for last calendar year or fiscal year. + * + * @todo must be possible to re-use relative dates stuff. + * + * @param string $fieldName + * + * @return null|string + */ + public function whereClauseLastYear($fieldName) { + $current_year = $this->_params['yid_value']; + $previous_year = $current_year - 1; + if (CRM_Utils_Array::value('yid_op', $this->_params) == 'calendar') { + $firstDateOfYear = "{$previous_year}-01-01"; + $lastDateOfYear = "{$previous_year}-12-31 23:11:59"; + } + else { + $fiscalYear = CRM_Core_Config::singleton()->fiscalYearStart; + $firstDateOfYear = "{$previous_year}-{$fiscalYear['M']}-{$fiscalYear['d']}"; + $lastDateOfYear = date('Ymdhis', strtotime(date($current_year . '-m-d'), '- 1 second')); + } + return "$fieldName BETWEEN '{$firstDateOfYear}' AND '{$lastDateOfYear}'"; + } + + public function groupBy() { - $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contribution']}.contact_id, " . - self::fiscalYearOffset($this->_aliases['civicrm_contribution'] . - '.receive_date') . " " . $this->_rollup; + $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contribution']}.contact_id "; + + if (!$this->isOrderByLastYearTotal()) { + // If we are ordering by last year total we can't also get the lifetime total + // in the same query without significant re-work so we may as well drop the + // expensive clause that supports it. + $this->_groupBy .= ', ' . self::fiscalYearOffset($this->_aliases['civicrm_contribution'] . + '.receive_date'); + } + $this->assign('chartSupported', TRUE); } @@ -413,12 +454,8 @@ class CRM_Report_Form_Contribute_Lybunt extends CRM_Report_Form { // get ready with post process params $this->beginPostProcess(); - // get the acl clauses built before we assemble the query - $this->buildACLClause($this->_aliases['civicrm_contact']); - $this->select(); - $this->from(); - $this->where(); - $this->groupBy(); + $this->buildQuery(); + $this->resetFormSql(); $this->getPermissionedFTQuery($this); @@ -437,8 +474,23 @@ class CRM_Report_Form_Contribute_Lybunt extends CRM_Report_Form { } if (!empty($this->_contactIds) || !empty($this->_params['charts'])) { - $sql = "{$this->_select} {$this->_from} WHERE {$this->_aliases['civicrm_contact']}.id IN (" . implode(',', $this->_contactIds) . ") - AND {$this->_aliases['civicrm_contribution']}.is_test = 0 {$this->_statusClause} {$this->_groupBy} "; + $this->_where = "WHERE {$this->_aliases['civicrm_contact']}.id IN (" . implode(',', $this->_contactIds) . ") + AND {$this->_aliases['civicrm_contribution']}.is_test = 0 {$this->_statusClause}"; + + if ($this->isOrderByLastYearTotal()) { + $this->_rollup = ''; + $this->_where .= " AND " . $this->whereClauseLastYear('receive_date'); + unset($this->_columnHeaders['civicrm_life_time_total']); + } + + $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_rollup}"; + + if (!empty($this->_orderByArray)) { + $this->_orderBy = str_replace('contact_civireport.', 'civicrm_contact_', "ORDER BY " . implode(', ', $this->_orderByArray)); + $this->_orderBy = str_replace('contribution_civireport.', 'civicrm_contribution_', $this->_orderBy); + $sql = "SELECT * FROM ( $sql ) as inner_query {$this->_orderBy}"; + } + $this->addToDeveloperTab($sql); $dao = CRM_Core_DAO::executeQuery($sql); $current_year = $this->_params['yid_value']; @@ -478,6 +530,33 @@ class CRM_Report_Form_Contribute_Lybunt extends CRM_Report_Form { $this->endPostProcess($rows); } + /** + * Reset the form sql to prevent misleading developer tab info. + */ + function resetFormSql() { + $this->sql = ''; + } + + /** + * Are we ordering by the latest year total. + * + * If we are we need to drop the rollup to do the ordering. + * + * Without bigger changes we can't get the lifetime total and order by + * the latest year total in the same query. + * + * @return bool + */ + public function isOrderByLastYearTotal() { + $this->storeOrderByArray(); + foreach ($this->_orderByArray as $orderBy) { + if (stristr($orderBy, 'contribution_civireport.total_amount')) { + return TRUE; + } + } + return FALSE; + } + /** * @param $rows */ -- 2.25.1