From dc852c7b1193afea23e912e488457019650ee407 Mon Sep 17 00:00:00 2001 From: jitendrapurohit Date: Mon, 6 Jun 2016 11:17:47 +0530 Subject: [PATCH] warning fixes --- CRM/Contact/BAO/Contact/Utils.php | 3 +- CRM/Contact/BAO/Query.php | 59 ++++++++++----------- CRM/Mailing/BAO/Mailing.php | 2 +- CRM/Report/Form.php | 2 +- CRM/Report/Form/Case/TimeSpent.php | 2 +- CRM/Report/Form/Contact/CurrentEmployer.php | 2 +- CRM/Report/Form/Contribute/Bookkeeping.php | 12 ++--- CRM/Report/Form/Contribute/Summary.php | 2 +- 8 files changed, 40 insertions(+), 44 deletions(-) diff --git a/CRM/Contact/BAO/Contact/Utils.php b/CRM/Contact/BAO/Contact/Utils.php index 4a94e0c5da..3339de232a 100644 --- a/CRM/Contact/BAO/Contact/Utils.php +++ b/CRM/Contact/BAO/Contact/Utils.php @@ -777,12 +777,13 @@ INNER JOIN civicrm_contact contact_target ON ( contact_target.id = act.contact_i $fromClause = implode(' ', $from); $selectClause = implode(', ', $select); $whereClause = "{$compTable}.id IN (" . implode(',', $componentIds) . ')'; + $groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($select, array("{$compTable}.id", 'contact.id')); $query = " SELECT contact.id as contactId, $compTable.id as componentId, $selectClause FROM $compTable as $compTable $fromClause WHERE $whereClause -Group By componentId"; + {$groupBy}"; $contact = CRM_Core_DAO::executeQuery($query); while ($contact->fetch()) { diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 4cbf220535..ec4b004e15 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -4523,7 +4523,7 @@ civicrm_relationship.is_permission_a_b = 0 /** * Include Select columns in groupBy clause. * - * @param array $selectArray + * @param array $selectClauses * @param array $groupBy - Columns already included in GROUP By clause. * * @return string @@ -4534,39 +4534,34 @@ civicrm_relationship.is_permission_a_b = 0 $sqlMode = CRM_Core_DAO::singleValueQuery('SELECT @@sql_mode'); //return if ONLY_FULL_GROUP_BY is not enabled. - if (version_compare($mysqlVersion, '5.7', '<') || !empty($sqlMode) && !in_array('ONLY_FULL_GROUP_BY', explode(',', $sqlMode))) { - if (!empty($groupBy)) { - return " GROUP BY " . implode(', ', $groupBy); - } - return ''; - } - - $regexToExclude = '/(ROUND|AVG|COUNT|GROUP_CONCAT|SUM|MAX|MIN)\(/i'; - foreach ($selectClauses as $key => $val) { - $aliasArray = preg_split('/ as /i', $val); - // if more than 1 alias we need to split by ','. - if (count($aliasArray) > 2) { - $aliasArray = preg_split('/,/', $val); - foreach ($aliasArray as $key => $value) { - $alias = current(preg_split('/ as /i', $value)); - if (!in_array($alias, $groupBy) && preg_match($regexToExclude, trim($alias)) !== 1) { - $groupBy[] = $alias; + if (!version_compare($mysqlVersion, '5.7', '<') && !empty($sqlMode) && in_array('ONLY_FULL_GROUP_BY', explode(',', $sqlMode))) { + $regexToExclude = '/(ROUND|AVG|COUNT|GROUP_CONCAT|SUM|MAX|MIN)\(/i'; + foreach ($selectClauses as $key => $val) { + $aliasArray = preg_split('/ as /i', $val); + // if more than 1 alias we need to split by ','. + if (count($aliasArray) > 2) { + $aliasArray = preg_split('/,/', $val); + foreach ($aliasArray as $key => $value) { + $alias = current(preg_split('/ as /i', $value)); + if (!in_array($alias, $groupBy) && preg_match($regexToExclude, trim($alias)) !== 1) { + $groupBy[] = $alias; + } } } - } - else { - list($selectColumn, $alias) = array_pad($aliasArray, 2, NULL); - $dateRegex = '/^(DATE_FORMAT|DATE_ADD|CASE)/i'; - $tableName = current(explode('.', $selectColumn)); - $primaryKey = "{$tableName}.id"; - // exclude columns which are already included in groupBy and aggregate functions from select - // CRM-18439 - Also exclude the columns which are functionally dependent on columns in $groupBy (MySQL 5.7+) - if (!in_array($selectColumn, $groupBy) && !in_array($primaryKey, $groupBy) && preg_match($regexToExclude, trim($selectColumn)) !== 1) { - if (!empty($alias) && preg_match($dateRegex, trim($selectColumn))) { - $groupBy[] = $alias; - } - else { - $groupBy[] = $selectColumn; + else { + list($selectColumn, $alias) = array_pad($aliasArray, 2, NULL); + $dateRegex = '/^(DATE_FORMAT|DATE_ADD|CASE)/i'; + $tableName = current(explode('.', $selectColumn)); + $primaryKey = "{$tableName}.id"; + // exclude columns which are already included in groupBy and aggregate functions from select + // CRM-18439 - Also exclude the columns which are functionally dependent on columns in $groupBy (MySQL 5.7+) + if (!in_array($selectColumn, $groupBy) && !in_array($primaryKey, $groupBy) && preg_match($regexToExclude, trim($selectColumn)) !== 1) { + if (!empty($alias) && preg_match($dateRegex, trim($selectColumn))) { + $groupBy[] = $alias; + } + else { + $groupBy[] = $selectColumn; + } } } } diff --git a/CRM/Mailing/BAO/Mailing.php b/CRM/Mailing/BAO/Mailing.php index bf190fa049..a89bf9d266 100644 --- a/CRM/Mailing/BAO/Mailing.php +++ b/CRM/Mailing/BAO/Mailing.php @@ -2512,7 +2512,7 @@ LEFT JOIN civicrm_mailing_group g ON g.mailing_id = m.id "$mailing.id", "$mailing.name", "$job.status", "$mailing.approval_status_id", "createdContact.sort_name as created_by", "scheduledContact.sort_name as scheduled_by", "$mailing.created_id as created_id", "$mailing.scheduled_id as scheduled_id", "$mailing.is_archived as archived", - "$mailing.created_date as created_date", "campaign_id", "$mailing.sms_provider_id as sms_provider_id" + "$mailing.created_date as created_date", "campaign_id", "$mailing.sms_provider_id as sms_provider_id", ); // we only care about parent jobs, since that holds all the info on diff --git a/CRM/Report/Form.php b/CRM/Report/Form.php index 07a6c87db7..c101e38c67 100644 --- a/CRM/Report/Form.php +++ b/CRM/Report/Form.php @@ -2645,7 +2645,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND /** * append select with ANY_VALUE() keyword. * - * @param array $selectArray + * @param array $selectClauses * @param array $groupBy - Columns already included in GROUP By clause. */ public function appendSelect($selectClauses, $groupBy) { diff --git a/CRM/Report/Form/Case/TimeSpent.php b/CRM/Report/Form/Case/TimeSpent.php index 659ee4a9be..c902705764 100644 --- a/CRM/Report/Form/Case/TimeSpent.php +++ b/CRM/Report/Form/Case/TimeSpent.php @@ -287,7 +287,7 @@ class CRM_Report_Form_Case_TimeSpent extends CRM_Report_Form { if ($this->has_grouping) { $groupBy = array( "{$this->_aliases['civicrm_contact']}.id", - "civicrm_activity_activity_date_time" + "civicrm_activity_activity_date_time", ); if ($this->has_activity_type) { $groupBy[] = "{$this->_aliases['civicrm_activity']}.activity_type_id"; diff --git a/CRM/Report/Form/Contact/CurrentEmployer.php b/CRM/Report/Form/Contact/CurrentEmployer.php index 35cd3362a3..150fe68cf8 100644 --- a/CRM/Report/Form/Contact/CurrentEmployer.php +++ b/CRM/Report/Form/Contact/CurrentEmployer.php @@ -324,7 +324,7 @@ FROM civicrm_contact {$this->_aliases['civicrm_contact']} public function groupBy() { $groupBy = array( "{$this->_aliases['civicrm_employer']}.id", - "{$this->_aliases['civicrm_contact']}.id" + "{$this->_aliases['civicrm_contact']}.id", ); $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); diff --git a/CRM/Report/Form/Contribute/Bookkeeping.php b/CRM/Report/Form/Contribute/Bookkeeping.php index 12e704e9bf..1f818f4f74 100644 --- a/CRM/Report/Form/Contribute/Bookkeeping.php +++ b/CRM/Report/Form/Contribute/Bookkeeping.php @@ -515,7 +515,7 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form { public function groupBy() { $groupBy = array( "{$this->_aliases['civicrm_entity_financial_trxn']}.id", - "{$this->_aliases['civicrm_line_item']}.id" + "{$this->_aliases['civicrm_line_item']}.id", ); $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } @@ -528,16 +528,16 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form { public function statistics(&$rows) { $statistics = parent::statistics($rows); $tempTableName = CRM_Core_DAO::createTempTableName('civicrm_contribution'); + $financialSelect = "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"; $this->_selectClauses = array( "{$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", + $financialSelect, ); $select = "SELECT " . implode(', ', $this->_selectClauses); diff --git a/CRM/Report/Form/Contribute/Summary.php b/CRM/Report/Form/Contribute/Summary.php index e4efe70d84..83564ec987 100644 --- a/CRM/Report/Form/Contribute/Summary.php +++ b/CRM/Report/Form/Contribute/Summary.php @@ -539,7 +539,7 @@ class CRM_Report_Form_Contribute_Summary extends CRM_Report_Form { } $groupBy = array(); foreach ($this->_groupBy as $key => $val) { - if (strpos($val, ';;') !== false) { + if (strpos($val, ';;') !== FALSE) { $groupBy = array_merge($groupBy, explode(';;', $val)); } else { -- 2.25.1