From 36d2f4d5a2e30a0d58baee089318807e2defe65a Mon Sep 17 00:00:00 2001 From: jitendrapurohit Date: Mon, 8 Aug 2016 15:51:24 +0530 Subject: [PATCH] CRM-19184 - Activities target contact list does not truncate --- CRM/Activity/BAO/Activity.php | 12 +++---- CRM/Contact/BAO/Query.php | 35 +++++++++++++++++++ CRM/Report/Form.php | 31 +--------------- CRM/Report/Form/Activity.php | 2 +- CRM/Report/Form/Contribute/Detail.php | 2 +- CRM/Report/Form/Contribute/Lybunt.php | 2 +- CRM/Report/Form/Contribute/SoftCredit.php | 2 +- CRM/Report/Form/Contribute/Summary.php | 2 +- CRM/Report/Form/Contribute/Sybunt.php | 2 +- CRM/Report/Form/Event/IncomeCountSummary.php | 2 +- CRM/Report/Form/Member/Summary.php | 2 +- CRM/Report/Form/Pledge/Summary.php | 2 +- .../phpunit/CRM/Activity/BAO/ActivityTest.php | 26 ++++++++++++++ 13 files changed, 76 insertions(+), 46 deletions(-) diff --git a/CRM/Activity/BAO/Activity.php b/CRM/Activity/BAO/Activity.php index 3be24e0457..54a522b1ee 100644 --- a/CRM/Activity/BAO/Activity.php +++ b/CRM/Activity/BAO/Activity.php @@ -789,22 +789,20 @@ WHERE ac.record_type_id != %1 $params = array(1 => array($targetID, 'Integer')); CRM_Core_DAO::executeQuery($query, $params); + $activityFields = array("ac.activity_id", "ac.contact_id", "ac.record_type_id", "c.sort_name", "c.is_deleted"); + $select = CRM_Contact_BAO_Query::appendAnyValueToSelect($activityFields, "ac.activity_id"); + // for each activity insert one target contact // if we load all target contacts the performance will suffer a lot for mass-activities. $query = " INSERT INTO {$activityContactTempTable} ( activity_id, contact_id, record_type_id, contact_name, is_deleted, counter ) -SELECT ac.activity_id, - ac.contact_id, - ac.record_type_id, - c.sort_name, - c.is_deleted, - count(ac.contact_id) +{$select}, count(ac.contact_id) FROM {$activityTempTable} INNER JOIN civicrm_activity a ON ( a.id = {$activityTempTable}.activity_id ) INNER JOIN civicrm_activity_contact ac ON ( ac.activity_id = {$activityTempTable}.activity_id ) INNER JOIN civicrm_contact c ON c.id = ac.contact_id WHERE ac.record_type_id = %1 -GROUP BY ac.activity_id, ac.contact_id +GROUP BY ac.activity_id "; CRM_Core_DAO::executeQuery($query, $params); diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 1467ae6eac..eeb4281951 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -4522,6 +4522,41 @@ civicrm_relationship.is_permission_a_b = 0 } } + /** + * For some special cases, grouping by subset of select fields becomes mandatory. + * Hence, full_group_by mode is handled by appending any_value + * keyword to select fields not present in groupBy + * + * @param array $selectClauses + * @param array $groupBy - Columns already included in GROUP By clause. + * + * @return string + */ + public static function appendAnyValueToSelect($selectClauses, $groupBy) { + $mysqlVersion = CRM_Core_DAO::singleValueQuery('SELECT VERSION()'); + $sqlMode = explode(',', CRM_Core_DAO::singleValueQuery('SELECT @@sql_mode')); + + // Disable only_full_group_by mode for lower sql versions. + if (version_compare($mysqlVersion, '5.7', '<') || (!empty($sqlMode) && !in_array('ONLY_FULL_GROUP_BY', $sqlMode))) { + $key = array_search('ONLY_FULL_GROUP_BY', $sqlMode); + unset($sqlMode[$key]); + CRM_Core_DAO::executeQuery("SET SESSION sql_mode = '" . implode(',', $sqlMode) . "'"); + } + else { + $groupBy = array_map('trim', (array) $groupBy); + $aggregateFunctions = '/(ROUND|AVG|COUNT|GROUP_CONCAT|SUM|MAX|MIN)\(/i'; + foreach ($selectClauses as $key => &$val) { + list($selectColumn, $alias) = array_pad(preg_split('/ as /i', $val), 2, NULL); + // append ANY_VALUE() keyword + if (!in_array($selectColumn, $groupBy) && preg_match($aggregateFunctions, trim($selectColumn)) !== 1) { + $val = str_replace($selectColumn, "ANY_VALUE({$selectColumn})", $val); + } + } + } + + return "SELECT " . implode(', ', $selectClauses) . " "; + } + /** * Include Select columns in groupBy clause. * diff --git a/CRM/Report/Form.php b/CRM/Report/Form.php index 04be6ad607..a4c5898262 100644 --- a/CRM/Report/Form.php +++ b/CRM/Report/Form.php @@ -2644,35 +2644,6 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND return $sql; } - /** - * append select with ANY_VALUE() keyword. - * - * @param array $selectClauses - * @param array $groupBy - Columns already included in GROUP By clause. - */ - public function appendSelect($selectClauses, $groupBy) { - $mysqlVersion = CRM_Core_DAO::singleValueQuery('SELECT VERSION()'); - $sqlMode = explode(',', CRM_Core_DAO::singleValueQuery('SELECT @@sql_mode')); - - // Disable only_full_group_by mode for lower sql versions. - if (version_compare($mysqlVersion, '5.7', '<') || (!empty($sqlMode) && !in_array('ONLY_FULL_GROUP_BY', $sqlMode))) { - $key = array_search('ONLY_FULL_GROUP_BY', $sqlMode); - unset($sqlMode[$key]); - CRM_Core_DAO::executeQuery("SET SESSION sql_mode = '" . implode(',', $sqlMode) . "'"); - return; - } - $groupBy = array_map('trim', (array) $groupBy); - $aggregateFunctions = '/(ROUND|AVG|COUNT|GROUP_CONCAT|SUM|MAX|MIN)\(/i'; - foreach ($selectClauses as $key => &$val) { - list($selectColumn, $alias) = array_pad(preg_split('/ as /i', $val), 2, NULL); - // append ANY_VALUE() keyword - if (!in_array($selectColumn, $groupBy) && preg_match($aggregateFunctions, trim($selectColumn)) !== 1) { - $val = str_replace($selectColumn, "ANY_VALUE({$selectColumn})", $val); - } - } - $this->_select = "SELECT " . implode(', ', $selectClauses) . " "; - } - /** * Build group by clause. */ @@ -2846,7 +2817,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND $ifnulls[] = "ifnull($alias, '') as $alias"; } $this->_select = "SELECT " . implode(", ", $ifnulls); - $this->appendSelect($ifnulls, $sectionAliases); + $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($ifnulls, $sectionAliases); // Group (un-limited) report by all aliases and get counts. This might // be done more efficiently when the contents of $sql are known, ie. by diff --git a/CRM/Report/Form/Activity.php b/CRM/Report/Form/Activity.php index a78eda2db2..7b06de4f37 100644 --- a/CRM/Report/Form/Activity.php +++ b/CRM/Report/Form/Activity.php @@ -1035,7 +1035,7 @@ FROM civireport_activity_temp_target tar $ifnulls[] = "ifnull($alias, '') as $alias"; } $this->_select = "SELECT " . implode(", ", $ifnulls); - $this->appendSelect($ifnulls, $sectionAliases); + $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($ifnulls, $sectionAliases); $query = $this->_select . ", count(DISTINCT civicrm_activity_id) as ct from civireport_activity_temp_target group by " . diff --git a/CRM/Report/Form/Contribute/Detail.php b/CRM/Report/Form/Contribute/Detail.php index 7ebd1cdcb1..d3c984841c 100644 --- a/CRM/Report/Form/Contribute/Detail.php +++ b/CRM/Report/Form/Contribute/Detail.php @@ -886,7 +886,7 @@ WHERE civicrm_contribution_contribution_id={$row['civicrm_contribution_contribu $ifnulls[] = "ifnull($alias, '') as $alias"; } $this->_select = "SELECT " . implode(", ", $ifnulls); - $this->appendSelect($ifnulls, $sectionAliases); + $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($ifnulls, $sectionAliases); /* Group (un-limited) report by all aliases and get counts. This might * be done more efficiently when the contents of $sql are known, ie. by diff --git a/CRM/Report/Form/Contribute/Lybunt.php b/CRM/Report/Form/Contribute/Lybunt.php index c17bc0b816..f599dea959 100644 --- a/CRM/Report/Form/Contribute/Lybunt.php +++ b/CRM/Report/Form/Contribute/Lybunt.php @@ -552,7 +552,7 @@ class CRM_Report_Form_Contribute_Lybunt extends CRM_Report_Form { public function groupBy() { $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contribution']}.contact_id "; - $this->appendSelect($this->_selectClauses, "{$this->_aliases['civicrm_contribution']}.contact_id"); + $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, "{$this->_aliases['civicrm_contribution']}.contact_id"); $this->assign('chartSupported', TRUE); } diff --git a/CRM/Report/Form/Contribute/SoftCredit.php b/CRM/Report/Form/Contribute/SoftCredit.php index 70b99eb313..52c6165ed6 100644 --- a/CRM/Report/Form/Contribute/SoftCredit.php +++ b/CRM/Report/Form/Contribute/SoftCredit.php @@ -459,7 +459,7 @@ class CRM_Report_Form_Contribute_SoftCredit extends CRM_Report_Form { public function groupBy() { $this->_rollup = 'WITH ROLLUP'; - $this->appendSelect($this->selectClause, array("{$this->_aliases['civicrm_contribution_soft']}.contact_id", "constituentname.id")); + $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->selectClause, array("{$this->_aliases['civicrm_contribution_soft']}.contact_id", "constituentname.id")); $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_contribution_soft']}.contact_id, constituentname.id {$this->_rollup}"; } diff --git a/CRM/Report/Form/Contribute/Summary.php b/CRM/Report/Form/Contribute/Summary.php index ee09282b3d..aaf7064805 100644 --- a/CRM/Report/Form/Contribute/Summary.php +++ b/CRM/Report/Form/Contribute/Summary.php @@ -559,7 +559,7 @@ class CRM_Report_Form_Contribute_Summary extends CRM_Report_Form { } $this->_groupBy .= $this->_rollup; // append select with ANY_VALUE() keyword - $this->appendSelect($this->_selectClauses, $groupBy); + $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, $groupBy); } /** diff --git a/CRM/Report/Form/Contribute/Sybunt.php b/CRM/Report/Form/Contribute/Sybunt.php index fae15cf5e6..a38f5116ff 100644 --- a/CRM/Report/Form/Contribute/Sybunt.php +++ b/CRM/Report/Form/Contribute/Sybunt.php @@ -398,7 +398,7 @@ class CRM_Report_Form_Contribute_Sybunt extends CRM_Report_Form { $this->assign('chartSupported', TRUE); $fiscalYearOffset = self::fiscalYearOffset("{$this->_aliases['civicrm_contribution']}.receive_date"); $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contribution']}.contact_id, {$fiscalYearOffset}"; - $this->appendSelect($this->_selectClauses, array("{$this->_aliases['civicrm_contribution']}.contact_id", $fiscalYearOffset)); + $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, array("{$this->_aliases['civicrm_contribution']}.contact_id", $fiscalYearOffset)); $this->_groupBy .= " {$this->_rollup}"; } diff --git a/CRM/Report/Form/Event/IncomeCountSummary.php b/CRM/Report/Form/Event/IncomeCountSummary.php index f502c2d0a4..fedf49585f 100644 --- a/CRM/Report/Form/Event/IncomeCountSummary.php +++ b/CRM/Report/Form/Event/IncomeCountSummary.php @@ -305,7 +305,7 @@ class CRM_Report_Form_Event_IncomeCountSummary extends CRM_Report_Form_Event { public function groupBy() { $this->assign('chartSupported', TRUE); $this->_rollup = " WITH ROLLUP"; - $this->appendSelect($this->_selectClauses, "{$this->_aliases['civicrm_event']}.id"); + $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, "{$this->_aliases['civicrm_event']}.id"); $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_event']}.id {$this->_rollup}"; } diff --git a/CRM/Report/Form/Member/Summary.php b/CRM/Report/Form/Member/Summary.php index f7d2f77890..8bbba645b1 100644 --- a/CRM/Report/Form/Member/Summary.php +++ b/CRM/Report/Form/Member/Summary.php @@ -382,7 +382,7 @@ class CRM_Report_Form_Member_Summary extends CRM_Report_Form { } $this->_rollup = ' WITH ROLLUP'; - $this->appendSelect($this->_selectClauses, array_filter($this->_groupBy)); + $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, array_filter($this->_groupBy)); $this->_groupBy = 'GROUP BY ' . implode(', ', array_filter($this->_groupBy)) . " {$this->_rollup} "; } diff --git a/CRM/Report/Form/Pledge/Summary.php b/CRM/Report/Form/Pledge/Summary.php index a5b4d022d9..6fa2d37d64 100644 --- a/CRM/Report/Form/Pledge/Summary.php +++ b/CRM/Report/Form/Pledge/Summary.php @@ -280,7 +280,7 @@ class CRM_Report_Form_Pledge_Summary extends CRM_Report_Form { $groupBy = "{$this->_aliases['civicrm_contact']}.id"; $this->_groupBy = "GROUP BY {$groupBy}"; } - $this->appendSelect($this->_selectClauses, $groupBy); + $this->_select = CRM_Contact_BAO_Query::appendAnyValueToSelect($this->_selectClauses, $groupBy); $this->_groupBy .= " {$this->_rollup}"; } diff --git a/tests/phpunit/CRM/Activity/BAO/ActivityTest.php b/tests/phpunit/CRM/Activity/BAO/ActivityTest.php index bc7e1776fa..09abc8b66b 100644 --- a/tests/phpunit/CRM/Activity/BAO/ActivityTest.php +++ b/tests/phpunit/CRM/Activity/BAO/ActivityTest.php @@ -499,6 +499,32 @@ class CRM_Activity_BAO_ActivityTest extends CiviUnitTestCase { } } + /** + * Test target contact count. + */ + public function testTargetCountforContactSummary() { + $targetCount = 5; + $contactId = $this->individualCreate(); + for ($i = 0; $i < $targetCount; $i++) { + $targetContactIDs[] = $this->individualCreate(array(), $i); + } + // create activities with 5 target contacts + $activityParams = array( + 'source_contact_id' => $contactId, + 'target_contact_id' => $targetContactIDs, + ); + $this->activityCreate($activityParams); + + $params = array( + 'contact_id' => $contactId, + 'context' => 'activity', + ); + $activities = CRM_Activity_BAO_Activity::getActivities($params); + + //verify target count + $this->assertEquals($targetCount, $activities[1]['target_contact_counter']); + } + /** * Test getActivities BAO method. */ -- 2.25.1