From d1641c514cc7490923db5111f24c7f98da6fa91e Mon Sep 17 00:00:00 2001 From: jitendrapurohit Date: Tue, 17 May 2016 11:17:42 +0530 Subject: [PATCH] CRM-18439 - Report Fixes to include Full Group by clause --- CRM/Report/Form.php | 1 + CRM/Report/Form/Activity.php | 4 ++- CRM/Report/Form/ActivitySummary.php | 10 ++++-- CRM/Report/Form/Case/Demographics.php | 5 ++- CRM/Report/Form/Case/Detail.php | 2 ++ CRM/Report/Form/Case/Summary.php | 1 + CRM/Report/Form/Case/TimeSpent.php | 17 ++++++---- CRM/Report/Form/Contact/CurrentEmployer.php | 9 +++++- CRM/Report/Form/Contact/Detail.php | 9 ++---- CRM/Report/Form/Contact/Relationship.php | 9 ++++-- CRM/Report/Form/Contribute/Bookkeeping.php | 32 +++++++++++++------ CRM/Report/Form/Contribute/Detail.php | 6 +++- CRM/Report/Form/Contribute/History.php | 10 ++++-- .../Form/Contribute/HouseholdSummary.php | 8 +++++ CRM/Report/Form/Contribute/Lybunt.php | 1 + .../Form/Contribute/OrganizationSummary.php | 10 +++++- CRM/Report/Form/Contribute/PCP.php | 3 +- CRM/Report/Form/Contribute/Recur.php | 3 +- CRM/Report/Form/Contribute/RecurSummary.php | 8 +++-- CRM/Report/Form/Contribute/Repeat.php | 4 +-- CRM/Report/Form/Contribute/SoftCredit.php | 4 ++- CRM/Report/Form/Contribute/Summary.php | 14 +++++--- CRM/Report/Form/Contribute/Sybunt.php | 8 +++-- CRM/Report/Form/Contribute/TopDonor.php | 2 ++ CRM/Report/Form/Event/Income.php | 22 +++++++------ CRM/Report/Form/Event/IncomeCountSummary.php | 4 ++- .../Form/Event/ParticipantListCount.php | 2 ++ CRM/Report/Form/Event/ParticipantListing.php | 2 ++ CRM/Report/Form/Event/Summary.php | 5 ++- CRM/Report/Form/Grant/Statistics.php | 6 ++-- CRM/Report/Form/Mailing/Bounce.php | 7 ++-- CRM/Report/Form/Mailing/Clicks.php | 8 +++-- CRM/Report/Form/Mailing/Opened.php | 7 ++-- CRM/Report/Form/Mailing/Summary.php | 4 ++- CRM/Report/Form/Member/ContributionDetail.php | 8 ++++- CRM/Report/Form/Member/Detail.php | 1 + CRM/Report/Form/Member/Summary.php | 7 ++-- CRM/Report/Form/Pledge/Detail.php | 11 ++++--- CRM/Report/Form/Pledge/Pbnp.php | 12 ++++--- CRM/Report/Form/Pledge/Summary.php | 9 ++++-- 40 files changed, 209 insertions(+), 86 deletions(-) diff --git a/CRM/Report/Form.php b/CRM/Report/Form.php index 0331ddc3ff..c0c2209537 100644 --- a/CRM/Report/Form.php +++ b/CRM/Report/Form.php @@ -2663,6 +2663,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND if (!empty($groupBys)) { $this->_groupBy = "GROUP BY " . implode(', ', $groupBys); + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBys); } } diff --git a/CRM/Report/Form/Activity.php b/CRM/Report/Form/Activity.php index 24814b2354..9a4f337262 100644 --- a/CRM/Report/Form/Activity.php +++ b/CRM/Report/Form/Activity.php @@ -647,6 +647,7 @@ class CRM_Report_Form_Activity extends CRM_Report_Form { */ public function groupBy() { $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_activity']}.id"; + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_activity']}.id"); } /** @@ -831,9 +832,10 @@ GROUP BY civicrm_activity_id $having {$this->_orderBy}"; } } $this->limit(); + $groupByFromSelect = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, 'civicrm_activity_id'); $sql = "{$this->_select} FROM civireport_activity_temp_target tar -GROUP BY civicrm_activity_id {$this->_having} {$this->_orderBy} {$this->_limit}"; +GROUP BY civicrm_activity_id {$groupByFromSelect} {$this->_having} {$this->_orderBy} {$this->_limit}"; $this->buildRows($sql, $rows); // format result set. diff --git a/CRM/Report/Form/ActivitySummary.php b/CRM/Report/Form/ActivitySummary.php index b25165548d..94633467d5 100644 --- a/CRM/Report/Form/ActivitySummary.php +++ b/CRM/Report/Form/ActivitySummary.php @@ -275,6 +275,7 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { } } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -400,7 +401,7 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { } } - public function groupBy() { + public function groupBy($includeSelectCol = TRUE) { $this->_groupBy = array(); if (!empty($this->_params['group_bys']) && is_array($this->_params['group_bys'])) { @@ -431,12 +432,16 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { } } } - + $groupBy = $this->_groupBy; $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy); } else { + $groupBy = "{$this->_aliases['civicrm_activity']}.id"; $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_activity']}.id "; } + if ($includeSelectCol) { + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); + } } /** @@ -537,6 +542,7 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { // now build the query for duration sum $this->from(TRUE); $this->where(TRUE); + $this->groupBy(FALSE); // build the query to calulate duration sum $sql = "SELECT SUM(activity_civireport.duration) as civicrm_activity_duration_total {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}"; diff --git a/CRM/Report/Form/Case/Demographics.php b/CRM/Report/Form/Case/Demographics.php index 177d29faf7..33d81057e6 100644 --- a/CRM/Report/Form/Case/Demographics.php +++ b/CRM/Report/Form/Case/Demographics.php @@ -251,6 +251,7 @@ where (cg.extends='Contact' OR cg.extends='Individual' OR cg.extends_entity_colu } } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -357,7 +358,9 @@ where (cg.extends='Contact' OR cg.extends='Individual' OR cg.extends_entity_colu } public function groupBy() { - $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_case']}.id"; + $groupBy = array("{$this->_aliases['civicrm_contact']}.id", "{$this->_aliases['civicrm_case']}.id"); + $this->_groupBy = "GROUP BY " . implode(', ', $groupBy); + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } public function orderBy() { diff --git a/CRM/Report/Form/Case/Detail.php b/CRM/Report/Form/Case/Detail.php index c3982af1ea..0a5715d53c 100644 --- a/CRM/Report/Form/Case/Detail.php +++ b/CRM/Report/Form/Case/Detail.php @@ -336,6 +336,7 @@ class CRM_Report_Form_Case_Detail extends CRM_Report_Form { } } } + $this->_selectClauses = $select; $this->_select = 'SELECT ' . implode(', ', $select) . ' '; } @@ -452,6 +453,7 @@ class CRM_Report_Form_Case_Detail extends CRM_Report_Form { public function groupBy() { $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_case']}.id"; + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_case']}.id"); } /** diff --git a/CRM/Report/Form/Case/Summary.php b/CRM/Report/Form/Case/Summary.php index a5b625463b..ea39d69d36 100644 --- a/CRM/Report/Form/Case/Summary.php +++ b/CRM/Report/Form/Case/Summary.php @@ -215,6 +215,7 @@ class CRM_Report_Form_Case_Summary extends CRM_Report_Form { } } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } diff --git a/CRM/Report/Form/Case/TimeSpent.php b/CRM/Report/Form/Case/TimeSpent.php index f09b557401..865e3b34d5 100644 --- a/CRM/Report/Form/Case/TimeSpent.php +++ b/CRM/Report/Form/Case/TimeSpent.php @@ -208,6 +208,7 @@ class CRM_Report_Form_Case_TimeSpent extends CRM_Report_Form { } } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -284,12 +285,16 @@ class CRM_Report_Form_Case_TimeSpent extends CRM_Report_Form { public function groupBy() { $this->_groupBy = ''; if ($this->has_grouping) { - $this->_groupBy = " -GROUP BY {$this->_aliases['civicrm_contact']}.id, -"; - $this->_groupBy .= ($this->has_activity_type) ? "{$this->_aliases['civicrm_activity']}.activity_type_id, " : ""; - $this->_groupBy .= "civicrm_activity_activity_date_time -"; + $groupBy = array( + "{$this->_aliases['civicrm_contact']}.id", + "civicrm_activity_activity_date_time" + ); + if ($this->has_activity_type) { + $groupBy[] = "{$this->_aliases['civicrm_activity']}.activity_type_id"; + } + + $this->_groupBy = "GROUP BY " . implode(', ', $groupBy); + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } } diff --git a/CRM/Report/Form/Contact/CurrentEmployer.php b/CRM/Report/Form/Contact/CurrentEmployer.php index fc85be7a6a..674958af02 100644 --- a/CRM/Report/Form/Contact/CurrentEmployer.php +++ b/CRM/Report/Form/Contact/CurrentEmployer.php @@ -246,6 +246,7 @@ class CRM_Report_Form_Contact_CurrentEmployer extends CRM_Report_Form { } } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -321,7 +322,13 @@ FROM civicrm_contact {$this->_aliases['civicrm_contact']} } public function groupBy() { - $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_employer']}.id,{$this->_aliases['civicrm_contact']}.id"; + $groupBy = array( + "{$this->_aliases['civicrm_employer']}.id", + "{$this->_aliases['civicrm_contact']}.id" + ); + + $this->_groupBy = "GROUP BY " . implode(', ', $groupBy); + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } public function orderBy() { diff --git a/CRM/Report/Form/Contact/Detail.php b/CRM/Report/Form/Contact/Detail.php index d8703f8d25..f738138a08 100644 --- a/CRM/Report/Form/Contact/Detail.php +++ b/CRM/Report/Form/Contact/Detail.php @@ -614,8 +614,6 @@ class CRM_Report_Form_Contact_Detail extends CRM_Report_Form { if ($this->_aclWhere) { $this->_where .= " AND {$this->_aclWhere} "; } - - $this->_where .= " GROUP BY {$this->_aliases['civicrm_contact']}.id "; } /** @@ -631,7 +629,7 @@ class CRM_Report_Form_Contact_Detail extends CRM_Report_Form { ) { $sql = "{$this->_selectComponent[$val]} {$this->_formComponent[$val]} WHERE {$this->_aliases['civicrm_contact']}.id IN ( $selectedContacts ) - GROUP BY {$this->_aliases['civicrm_contact']}.id,{$val}.id "; + "; $dao = CRM_Core_DAO::executeQuery($sql); while ($dao->fetch()) { @@ -667,7 +665,7 @@ class CRM_Report_Form_Contact_Detail extends CRM_Report_Form { {$this->_aliases['civicrm_relationship']}.is_active = 1 AND contact_a.is_deleted = 0 AND {$this->_aliases['civicrm_contact']}.is_deleted = 0 - GROUP BY {$this->_aliases['civicrm_relationship']}.id"; + "; $dao = CRM_Core_DAO::executeQuery($sql); while ($dao->fetch()) { @@ -724,9 +722,6 @@ class CRM_Report_Form_Contact_Detail extends CRM_Report_Form { civicrm_option_group.name = 'activity_type' AND {$this->_aliases['civicrm_activity']}.is_test = 0 AND ($componentClause) - - GROUP BY {$this->_aliases['civicrm_activity']}.id - ORDER BY {$this->_aliases['civicrm_activity']}.activity_date_time desc "; $dao = CRM_Core_DAO::executeQuery($sql); diff --git a/CRM/Report/Form/Contact/Relationship.php b/CRM/Report/Form/Contact/Relationship.php index 36e3d06d99..56073269b0 100644 --- a/CRM/Report/Form/Contact/Relationship.php +++ b/CRM/Report/Form/Contact/Relationship.php @@ -321,6 +321,7 @@ class CRM_Report_Form_Contact_Relationship extends CRM_Report_Form { } } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -559,12 +560,14 @@ class CRM_Report_Form_Contact_Relationship extends CRM_Report_Form { } if (!empty($groupBy)) { - $this->_groupBy = " GROUP BY " . implode(', ', $groupBy) . - " , {$this->_aliases['civicrm_relationship']}.id "; + $groupBy[] = "{$this->_aliases['civicrm_relationship']}.id"; } else { - $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_relationship']}.id "; + $groupBy = array("{$this->_aliases['civicrm_relationship']}.id"); } + + $this->_groupBy = " GROUP BY " . implode(', ', $groupBy); + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } public function orderBy() { diff --git a/CRM/Report/Form/Contribute/Bookkeeping.php b/CRM/Report/Form/Contribute/Bookkeeping.php index f71433d689..fc350b2a6f 100644 --- a/CRM/Report/Form/Contribute/Bookkeeping.php +++ b/CRM/Report/Form/Contribute/Bookkeeping.php @@ -397,6 +397,7 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form { } } } + $this->_selectClauses = $select; $this->_select = 'SELECT ' . implode(', ', $select) . ' '; } @@ -432,7 +433,7 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form { ON fitem.entity_id = {$this->_aliases['civicrm_line_item']}.id AND fitem.entity_table = 'civicrm_line_item' "; if ($this->isTableSelected('civicrm_batch')) { $this->_from .= "LEFT JOIN civicrm_entity_batch ent_batch - ON {$this->_aliases['civicrm_financial_trxn']}.id = ent_batch.entity_id AND ent_batch.entity_table = 'civicrm_financial_trxn' + ON {$this->_aliases['civicrm_financial_trxn']}.id = ent_batch.entity_id AND ent_batch.entity_table = 'civicrm_financial_trxn' LEFT JOIN civicrm_batch batch ON ent_batch.batch_id = batch.id"; } @@ -512,7 +513,13 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form { } public function groupBy() { - $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_entity_financial_trxn']}.id, {$this->_aliases['civicrm_line_item']}.id "; + $groupFromSelect = ''; + $groupBy = array( + "{$this->_aliases['civicrm_entity_financial_trxn']}.id", + "{$this->_aliases['civicrm_line_item']}.id" + ); + $groupFromSelect = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); + $this->_groupBy = " GROUP BY " . implode(', ', $groupBy) . " {$groupFromSelect}"; } /** @@ -523,13 +530,20 @@ 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'); - $select = "SELECT {$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 -"; + + $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", + ); + $select = "SELECT " . implode(', ', $this->_selectClauses); + + $this->groupBy(); $tempQuery = "CREATE TEMPORARY TABLE {$tempTableName} CHARACTER SET utf8 COLLATE utf8_unicode_ci AS {$select} {$this->_from} {$this->_where} {$this->_groupBy} "; diff --git a/CRM/Report/Form/Contribute/Detail.php b/CRM/Report/Form/Contribute/Detail.php index 1a7c9ac065..b94a8a142f 100644 --- a/CRM/Report/Form/Contribute/Detail.php +++ b/CRM/Report/Form/Contribute/Detail.php @@ -357,6 +357,7 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { //total_amount was affected by sum as it is considered as one of the stat field //so it is been replaced with correct alias, CRM-13833 $this->_select = str_replace("sum({$this->_aliases['civicrm_contribution']}.total_amount)", "{$this->_aliases['civicrm_contribution']}.total_amount", $this->_select); + $this->_selectClauses = str_replace("sum({$this->_aliases['civicrm_contribution']}.total_amount)", "{$this->_aliases['civicrm_contribution']}.total_amount", $this->_selectClauses); } public function orderBy() { @@ -456,7 +457,9 @@ class CRM_Report_Form_Contribute_Detail extends CRM_Report_Form { } public function groupBy() { - $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_contribution']}.id "; + $groupBy = array("{$this->_aliases['civicrm_contact']}.id", "{$this->_aliases['civicrm_contribution']}.id"); + $this->_groupBy = " GROUP BY " . implode(', ', $groupBy); + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } /** @@ -591,6 +594,7 @@ GROUP BY {$this->_aliases['civicrm_contribution']}.currency"; $this->customDataFrom(); $select = str_ireplace('contribution_civireport.total_amount', 'contribution_soft_civireport.amount', $this->_select); + $this->_groupBy = str_ireplace('contribution_civireport.total_amount', 'contribution_soft_civireport.amount', $this->_groupBy); $select = str_ireplace("'Contribution' as", "'Soft Credit' as", $select); // we inner join with temp1 to restrict soft contributions to those in temp1 table $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy}"; diff --git a/CRM/Report/Form/Contribute/History.php b/CRM/Report/Form/Contribute/History.php index 8c3fe86bf7..6ae62a5c36 100644 --- a/CRM/Report/Form/Contribute/History.php +++ b/CRM/Report/Form/Contribute/History.php @@ -366,6 +366,7 @@ class CRM_Report_Form_Contribute_History extends CRM_Report_Form { } } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -479,7 +480,12 @@ class CRM_Report_Form_Contribute_History extends CRM_Report_Form { } public function groupBy() { - $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contribution']}.contact_id, YEAR({$this->_aliases['civicrm_contribution']}.receive_date)"; + $groupBy = array( + "{$this->_aliases['civicrm_contribution']}.contact_id", + "YEAR({$this->_aliases['civicrm_contribution']}.receive_date)", + ); + $this->_groupBy = "GROUP BY " . implode(', ', $groupBy); + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } /** @@ -750,7 +756,7 @@ class CRM_Report_Form_Contribute_History extends CRM_Report_Form { } $sqlRelationship = "SELECT {$this->_aliases['civicrm_relationship']}.relationship_type_id as relationship_type_id, {$this->_aliases['civicrm_relationship']}.contact_id_a as contact_id_a, {$this->_aliases['civicrm_relationship']}.contact_id_b as contact_id_b {$addRelSelect} FROM civicrm_contact {$relContactAlias} {$this->_relationshipFrom} WHERE {$relContactAlias}.id IN (" . implode(',', $contactIds) . - ") AND {$this->_aliases['civicrm_relationship']}.is_active = 1 {$this->_relationshipWhere} GROUP BY {$this->_aliases['civicrm_relationship']}.contact_id_a, {$this->_aliases['civicrm_relationship']}.contact_id_b"; + ") AND {$this->_aliases['civicrm_relationship']}.is_active = 1 {$this->_relationshipWhere} GROUP BY {$this->_aliases['civicrm_relationship']}.contact_id_a, {$this->_aliases['civicrm_relationship']}.contact_id_b, {$this->_aliases['civicrm_relationship']}.relationship_type_id"; $relationshipTypes = CRM_Core_PseudoConstant::relationshipType(); $dao = CRM_Core_DAO::executeQuery($sqlRelationship); diff --git a/CRM/Report/Form/Contribute/HouseholdSummary.php b/CRM/Report/Form/Contribute/HouseholdSummary.php index dc6954062a..d72b7b929f 100644 --- a/CRM/Report/Form/Contribute/HouseholdSummary.php +++ b/CRM/Report/Form/Contribute/HouseholdSummary.php @@ -238,6 +238,7 @@ class CRM_Report_Form_Contribute_HouseholdSummary extends CRM_Report_Form { } } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -318,7 +319,14 @@ class CRM_Report_Form_Contribute_HouseholdSummary extends CRM_Report_Form { } public function groupBy() { + $groupBy = array( + "{$this->_aliases['civicrm_relationship']}.$this->householdContact", + "{$this->_aliases['civicrm_relationship']}.$this->otherContact", + "{$this->_aliases['civicrm_contribution']}.id", + "{$this->_aliases['civicrm_relationship']}.relationship_type_id", + ); $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_relationship']}.$this->householdContact, {$this->_aliases['civicrm_relationship']}.$this->otherContact , {$this->_aliases['civicrm_contribution']}.id, {$this->_aliases['civicrm_relationship']}.relationship_type_id "; + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } public function orderBy() { diff --git a/CRM/Report/Form/Contribute/Lybunt.php b/CRM/Report/Form/Contribute/Lybunt.php index c12deb627c..da628bd166 100644 --- a/CRM/Report/Form/Contribute/Lybunt.php +++ b/CRM/Report/Form/Contribute/Lybunt.php @@ -550,6 +550,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->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_contribution']}.contact_id"); $this->assign('chartSupported', TRUE); } diff --git a/CRM/Report/Form/Contribute/OrganizationSummary.php b/CRM/Report/Form/Contribute/OrganizationSummary.php index cc8b2cc9c0..82120133eb 100644 --- a/CRM/Report/Form/Contribute/OrganizationSummary.php +++ b/CRM/Report/Form/Contribute/OrganizationSummary.php @@ -247,6 +247,7 @@ class CRM_Report_Form_Contribute_OrganizationSummary extends CRM_Report_Form { } } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -328,7 +329,14 @@ class CRM_Report_Form_Contribute_OrganizationSummary extends CRM_Report_Form { } public function groupBy() { - $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_relationship']}.$this->orgContact, {$this->_aliases['civicrm_relationship']}.$this->otherContact , {$this->_aliases['civicrm_contribution']}.id, {$this->_aliases['civicrm_relationship']}.relationship_type_id "; + $groupBy = array( + "{$this->_aliases['civicrm_relationship']}.$this->orgContact", + "{$this->_aliases['civicrm_relationship']}.$this->otherContact", + "{$this->_aliases['civicrm_contribution']}.id", + "{$this->_aliases['civicrm_relationship']}.relationship_type_id", + ); + $this->_groupBy = " GROUP BY " . implode(', ', $groupBy); + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } public function orderBy() { diff --git a/CRM/Report/Form/Contribute/PCP.php b/CRM/Report/Form/Contribute/PCP.php index 5ef011714d..39fe3b4d1a 100644 --- a/CRM/Report/Form/Contribute/PCP.php +++ b/CRM/Report/Form/Contribute/PCP.php @@ -193,7 +193,8 @@ LEFT JOIN civicrm_contribution_page {$this->_aliases['civicrm_contribution_page' } public function groupBy() { - $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_pcp']}.id"; + $groupFromSelect = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_pcp']}.id"); + $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_pcp']}.id {$groupFromSelect}"; } public function orderBy() { diff --git a/CRM/Report/Form/Contribute/Recur.php b/CRM/Report/Form/Contribute/Recur.php index 7675191d99..2db3acfe84 100644 --- a/CRM/Report/Form/Contribute/Recur.php +++ b/CRM/Report/Form/Contribute/Recur.php @@ -253,7 +253,8 @@ class CRM_Report_Form_Contribute_Recur extends CRM_Report_Form { } public function groupBy() { - $this->_groupBy = "GROUP BY " . $this->_aliases['civicrm_contribution_recur'] . ".id"; + $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contribution_recur']}.id"; + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_contribution_recur']}.id"); } public function where() { diff --git a/CRM/Report/Form/Contribute/RecurSummary.php b/CRM/Report/Form/Contribute/RecurSummary.php index c0353e24f5..9b41a4816a 100644 --- a/CRM/Report/Form/Contribute/RecurSummary.php +++ b/CRM/Report/Form/Contribute/RecurSummary.php @@ -193,6 +193,7 @@ class CRM_Report_Form_Contribute_RecurSummary extends CRM_Report_Form { } } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -224,6 +225,7 @@ class CRM_Report_Form_Contribute_RecurSummary extends CRM_Report_Form { public function groupBy() { $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contribution_recur']}.payment_instrument_id"; + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_contribution_recur']}.payment_instrument_id"); } /** @@ -301,9 +303,9 @@ class CRM_Report_Form_Contribute_RecurSummary extends CRM_Report_Form { $lineTotal = 0; $amountSql = " - SELECT SUM(cc.total_amount) as amount FROM `civicrm_contribution` cc - INNER JOIN civicrm_contribution_recur cr ON (cr.id = cc.contribution_recur_id AND cr.payment_instrument_id = {$paymentInstrumentId}) - WHERE cc.contribution_status_id = 1 AND cc.is_test = 0 AND "; + SELECT SUM(cc.total_amount) as amount FROM `civicrm_contribution` cc + INNER JOIN civicrm_contribution_recur cr ON (cr.id = cc.contribution_recur_id AND cr.payment_instrument_id = {$paymentInstrumentId}) + WHERE cc.contribution_status_id = 1 AND cc.is_test = 0 AND "; $amountSql .= str_replace("start_date", "cc.`receive_date`", $startedDateSql); $amountDao = CRM_Core_DAO::executeQuery($amountSql); $amountDao->fetch(); diff --git a/CRM/Report/Form/Contribute/Repeat.php b/CRM/Report/Form/Contribute/Repeat.php index 152775c1a4..ae608b46bb 100644 --- a/CRM/Report/Form/Contribute/Repeat.php +++ b/CRM/Report/Form/Contribute/Repeat.php @@ -320,7 +320,7 @@ class CRM_Report_Form_Contribute_Repeat extends CRM_Report_Form { } } } - + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -1014,7 +1014,7 @@ SELECT {$subSelect2} contribution2.{$this->contributionJoinTableColumn}, currency {$from} {$subWhere} -GROUP BY contribution2.{$this->contributionJoinTableColumn}"; +GROUP BY contribution2.{$this->contributionJoinTableColumn}, currency"; $this->tempTableRepeat1 = 'civicrm_temp_civireport_repeat1' . uniqid(); $sql = " CREATE TEMPORARY TABLE $this->tempTableRepeat1 ( diff --git a/CRM/Report/Form/Contribute/SoftCredit.php b/CRM/Report/Form/Contribute/SoftCredit.php index c9058223e7..a174818b50 100644 --- a/CRM/Report/Form/Contribute/SoftCredit.php +++ b/CRM/Report/Form/Contribute/SoftCredit.php @@ -380,6 +380,7 @@ class CRM_Report_Form_Contribute_SoftCredit extends CRM_Report_Form { } } } + $this->selectClause = $select; $this->_select = 'SELECT ' . implode(', ', $select) . ' '; } @@ -458,8 +459,9 @@ class CRM_Report_Form_Contribute_SoftCredit extends CRM_Report_Form { public function groupBy() { $this->_rollup = 'WITH ROLLUP'; + $groupFromSelect = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($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}"; +GROUP BY {$this->_aliases['civicrm_contribution_soft']}.contact_id, constituentname.id {$groupFromSelect} {$this->_rollup}"; } public function where() { diff --git a/CRM/Report/Form/Contribute/Summary.php b/CRM/Report/Form/Contribute/Summary.php index 5c2f76cd18..883a1a7feb 100644 --- a/CRM/Report/Form/Contribute/Summary.php +++ b/CRM/Report/Form/Contribute/Summary.php @@ -399,6 +399,7 @@ class CRM_Report_Form_Contribute_Summary extends CRM_Report_Form { } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -536,12 +537,15 @@ class CRM_Report_Form_Contribute_Summary extends CRM_Report_Form { ) { $this->_rollup = " WITH ROLLUP"; } - $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy) . - " {$this->_rollup} "; + $groupBy = $this->_groupBy; + $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy); } else { + $groupBy = "{$this->_aliases['civicrm_contact']}.id"; $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id"; } + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); + $this->_groupBy .= $this->_rollup; } /** @@ -610,8 +614,10 @@ ROUND(AVG({$this->_aliases['civicrm_contribution_soft']}.amount), 2) as civicrm_ $groupBy = "\n{$group}, {$this->_aliases['civicrm_contribution']}.total_amount"; $orderBy = "\nORDER BY civicrm_contribution_total_amount_count DESC"; - $modeSQL = "SELECT civicrm_contribution_total_amount_count, amount, currency - FROM (SELECT {$this->_aliases['civicrm_contribution']}.total_amount as amount, + $modeSQL = "SELECT MAX(civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count, + SUBSTRING_INDEX(GROUP_CONCAT(amount ORDER BY mode.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount, + currency + FROM (SELECT {$this->_aliases['civicrm_contribution']}.total_amount as amount, {$contriQuery} {$groupBy} {$orderBy}) as mode GROUP BY currency"; $mode = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL); diff --git a/CRM/Report/Form/Contribute/Sybunt.php b/CRM/Report/Form/Contribute/Sybunt.php index 3d6655df15..e8538005b0 100644 --- a/CRM/Report/Form/Contribute/Sybunt.php +++ b/CRM/Report/Form/Contribute/Sybunt.php @@ -311,6 +311,7 @@ class CRM_Report_Form_Contribute_Sybunt extends CRM_Report_Form { } } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -395,9 +396,10 @@ class CRM_Report_Form_Contribute_Sybunt extends CRM_Report_Form { public function groupBy() { $this->assign('chartSupported', TRUE); - $this->_groupBy = "Group BY {$this->_aliases['civicrm_contribution']}.contact_id, " . - self::fiscalYearOffset($this->_aliases['civicrm_contribution'] . - '.receive_date') . " " . " " . $this->_rollup; + $fiscalYearOffset = self::fiscalYearOffset("{$this->_aliases['civicrm_contribution']}.receive_date"); + $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contribution']}.contact_id, {$fiscalYearOffset}"; + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, array("{$this->_aliases['civicrm_contribution']}.contact_id", $fiscalYearOffset)); + $this->_groupBy .= " {$this->_rollup}"; } /** diff --git a/CRM/Report/Form/Contribute/TopDonor.php b/CRM/Report/Form/Contribute/TopDonor.php index 1ffe5d6115..48dc9b3e03 100644 --- a/CRM/Report/Form/Contribute/TopDonor.php +++ b/CRM/Report/Form/Contribute/TopDonor.php @@ -275,6 +275,7 @@ class CRM_Report_Form_Contribute_TopDonor extends CRM_Report_Form { } } } + $this->_selectClauses = $select; $this->_select = " SELECT * FROM ( SELECT " . implode(', ', $select) . " "; } @@ -375,6 +376,7 @@ class CRM_Report_Form_Contribute_TopDonor extends CRM_Report_Form { public function groupBy() { $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_contribution']}.currency"; + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, array("{$this->_aliases['civicrm_contact']}.id", "{$this->_aliases['civicrm_contribution']}.currency")); } public function postProcess() { diff --git a/CRM/Report/Form/Event/Income.php b/CRM/Report/Form/Event/Income.php index d5570f3321..25288427f3 100644 --- a/CRM/Report/Form/Event/Income.php +++ b/CRM/Report/Form/Event/Income.php @@ -99,15 +99,18 @@ class CRM_Report_Form_Event_Income extends CRM_Report_Form_Event { $activeParticipantStatus = implode(',', $activeParticipantStatusIDArray); $activeparticipnatStutusLabel = implode(', ', $activeParticipantStatusLabelArray); $activeParticipantClause = " AND civicrm_participant.status_id IN ( $activeParticipantStatus ) "; + $select = array( + "civicrm_event.id as event_id", + "civicrm_event.title as event_title", + "civicrm_event.max_participants as max_participants", + "civicrm_event.start_date as start_date", + "civicrm_event.end_date as end_date", + "civicrm_option_value.label as event_type", + "civicrm_participant.fee_currency as currency", + ); $sql = " - SELECT civicrm_event.id as event_id, - civicrm_event.title as event_title, - civicrm_event.max_participants as max_participants, - civicrm_event.start_date as start_date, - civicrm_event.end_date as end_date, - civicrm_option_value.label as event_type, - civicrm_participant.fee_currency as currency, + SELECT " . implode(', ', $select) . ", SUM(civicrm_participant.fee_amount) as total, COUNT(civicrm_participant.id) as participant @@ -120,8 +123,7 @@ class CRM_Report_Form_Event_Income extends CRM_Report_Form_Event { WHERE civicrm_event.id IN( {$eventID}) - GROUP BY civicrm_event.id - "; + GROUP BY " . CRM_Contact_BAO_Query::getGroupByFromSelectColumns($select); $eventDAO = CRM_Core_DAO::executeQuery($sql); $currency = array(); @@ -166,7 +168,7 @@ class CRM_Report_Form_Event_Income extends CRM_Report_Form_Event { WHERE civicrm_participant.event_id IN ( {$eventID}) AND civicrm_participant.is_test = 0 {$activeParticipantClause} - GROUP BY civicrm_participant.role_id, civicrm_participant.event_id + GROUP BY civicrm_participant.role_id, civicrm_participant.event_id, civicrm_participant.fee_currency "; $roleDAO = CRM_Core_DAO::executeQuery($role); diff --git a/CRM/Report/Form/Event/IncomeCountSummary.php b/CRM/Report/Form/Event/IncomeCountSummary.php index 8bfd9151ef..7f160db484 100644 --- a/CRM/Report/Form/Event/IncomeCountSummary.php +++ b/CRM/Report/Form/Event/IncomeCountSummary.php @@ -205,6 +205,7 @@ class CRM_Report_Form_Event_IncomeCountSummary extends CRM_Report_Form_Event { } } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select); } @@ -304,7 +305,8 @@ class CRM_Report_Form_Event_IncomeCountSummary extends CRM_Report_Form_Event { public function groupBy() { $this->assign('chartSupported', TRUE); $this->_rollup = " WITH ROLLUP"; - $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_event']}.id {$this->_rollup}"; + $groupFromSelect = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_event']}.id"); + $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_event']}.id {$groupFromSelect} {$this->_rollup}"; } public function postProcess() { diff --git a/CRM/Report/Form/Event/ParticipantListCount.php b/CRM/Report/Form/Event/ParticipantListCount.php index 390e50a66c..e6adfd7769 100644 --- a/CRM/Report/Form/Event/ParticipantListCount.php +++ b/CRM/Report/Form/Event/ParticipantListCount.php @@ -425,6 +425,7 @@ class CRM_Report_Form_Event_ParticipantListCount extends CRM_Report_Form_Event { $this->_columnHeaders["blank_{$i}"]['title'] = "_ _ _ _"; } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -475,6 +476,7 @@ class CRM_Report_Form_Event_ParticipantListCount extends CRM_Report_Form_Event { parent::groupBy(); if (empty($this->_groupBy)) { $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_participant']}.id"; + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_participant']}.id"); } } diff --git a/CRM/Report/Form/Event/ParticipantListing.php b/CRM/Report/Form/Event/ParticipantListing.php index 3497a4a84d..976d4240a3 100644 --- a/CRM/Report/Form/Event/ParticipantListing.php +++ b/CRM/Report/Form/Event/ParticipantListing.php @@ -500,6 +500,7 @@ ORDER BY cv.label } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -629,6 +630,7 @@ ORDER BY cv.label public function groupBy() { $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_participant']}.id"; + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_participant']}.id"); } public function postProcess() { diff --git a/CRM/Report/Form/Event/Summary.php b/CRM/Report/Form/Event/Summary.php index 291194df89..3e74490040 100644 --- a/CRM/Report/Form/Event/Summary.php +++ b/CRM/Report/Form/Event/Summary.php @@ -128,6 +128,7 @@ class CRM_Report_Form_Event_Summary extends CRM_Report_Form_Event { } } + $this->_selectClauses = $select; $this->_select = 'SELECT ' . implode(', ', $select); } @@ -180,6 +181,7 @@ class CRM_Report_Form_Event_Summary extends CRM_Report_Form_Event { public function groupBy() { $this->assign('chartSupported', TRUE); $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_event']}.id"; + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_event']}.id"); } /** @@ -204,7 +206,8 @@ class CRM_Report_Form_Event_Summary extends CRM_Report_Form_Event { $this->_participantWhere GROUP BY civicrm_participant.event_id, - civicrm_participant.status_id"; + civicrm_participant.status_id, + civicrm_participant.fee_currency"; $info = CRM_Core_DAO::executeQuery($sql); $participant_data = $participant_info = $currency = array(); diff --git a/CRM/Report/Form/Grant/Statistics.php b/CRM/Report/Form/Grant/Statistics.php index 811ee3b113..b609a33496 100644 --- a/CRM/Report/Form/Grant/Statistics.php +++ b/CRM/Report/Form/Grant/Statistics.php @@ -234,6 +234,7 @@ class CRM_Report_Form_Grant_Statistics extends CRM_Report_Form { } } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -318,14 +319,15 @@ WHERE {$this->_aliases['civicrm_grant']}.amount_total IS NOT NULL if (array_key_exists('fields', $table)) { foreach ($table['fields'] as $fieldName => $field) { if (!empty($this->_params['fields'][$fieldName])) { - $this->_groupBy[] = $field['dbAlias']; + $groupBy[] = $field['dbAlias']; } } } } } if (!empty($this->_groupBy)) { - $this->_groupBy = " GROUP BY " . implode(', ', $this->_groupBy); + $this->_groupBy = " GROUP BY " . implode(', ', $groupBy); + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } } diff --git a/CRM/Report/Form/Mailing/Bounce.php b/CRM/Report/Form/Mailing/Bounce.php index 82e1b0a6ac..6aa4b8dd55 100644 --- a/CRM/Report/Form/Mailing/Bounce.php +++ b/CRM/Report/Form/Mailing/Bounce.php @@ -268,6 +268,7 @@ class CRM_Report_Form_Mailing_Bounce extends CRM_Report_Form { $this->_columnHeaders["civicrm_mailing_bounce_count"]['title'] = ts('Bounce Count'); } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -358,11 +359,13 @@ class CRM_Report_Form_Mailing_Bounce extends CRM_Report_Form { public function groupBy() { if (!empty($this->_params['charts'])) { - $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_mailing']}.id"; + $groupBy = "{$this->_aliases['civicrm_mailing']}.id"; } else { - $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_mailing_event_bounce']}.id"; + $groupBy = "{$this->_aliases['civicrm_mailing_event_bounce']}.id"; } + $this->_groupBy = " GROUP BY {$groupBy}"; + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } public function postProcess() { diff --git a/CRM/Report/Form/Mailing/Clicks.php b/CRM/Report/Form/Mailing/Clicks.php index 3fd5e8e0e2..316a3588b9 100644 --- a/CRM/Report/Form/Mailing/Clicks.php +++ b/CRM/Report/Form/Mailing/Clicks.php @@ -208,6 +208,7 @@ class CRM_Report_Form_Mailing_Clicks extends CRM_Report_Form { $this->_columnHeaders["civicrm_mailing_click_count"]['title'] = ts('Click Count'); } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -256,14 +257,15 @@ class CRM_Report_Form_Mailing_Clicks extends CRM_Report_Form { } public function groupBy() { - $this->_groupBy = ''; if (!empty($this->_params['charts'])) { - $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_mailing']}.id"; + $groupBy = "{$this->_aliases['civicrm_mailing']}.id"; } else { - $this->_groupBy = " GROUP BY civicrm_mailing_event_trackable_url_open.id"; + $groupBy = "civicrm_mailing_event_trackable_url_open.id"; } + $this->_groupBy = " GROUP BY {$groupBy}"; + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } public function postProcess() { diff --git a/CRM/Report/Form/Mailing/Opened.php b/CRM/Report/Form/Mailing/Opened.php index 2e70292ee0..7f04ddeaaa 100644 --- a/CRM/Report/Form/Mailing/Opened.php +++ b/CRM/Report/Form/Mailing/Opened.php @@ -188,6 +188,7 @@ class CRM_Report_Form_Mailing_Opened extends CRM_Report_Form { $this->_columnHeaders["civicrm_mailing_opened_count"]['title'] = ts('Opened Count'); } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -236,11 +237,13 @@ class CRM_Report_Form_Mailing_Opened extends CRM_Report_Form { public function groupBy() { if (!empty($this->_params['charts'])) { - $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_mailing']}.id"; + $groupBy = "{$this->_aliases['civicrm_mailing']}.id"; } else { - $this->_groupBy = " GROUP BY civicrm_mailing_event_queue.email_id"; + $groupBy = "civicrm_mailing_event_queue.email_id"; } + $this->_groupBy = " GROUP BY {$groupBy}"; + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } public function postProcess() { diff --git a/CRM/Report/Form/Mailing/Summary.php b/CRM/Report/Form/Mailing/Summary.php index 2706d4721d..85f9101093 100644 --- a/CRM/Report/Form/Mailing/Summary.php +++ b/CRM/Report/Form/Mailing/Summary.php @@ -362,6 +362,7 @@ class CRM_Report_Form_Mailing_Summary extends CRM_Report_Form { } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; //print_r($this->_select); } @@ -391,7 +392,7 @@ class CRM_Report_Form_Mailing_Summary extends CRM_Report_Form { if ($this->isTableSelected('civicrm_mailing_group')) { $this->_from .= " LEFT JOIN civicrm_mailing_group {$this->_aliases['civicrm_mailing_group']} - ON {$this->_aliases['civicrm_mailing_group']}.mailing_id = {$this->_aliases['civicrm_mailing']}.id"; + ON {$this->_aliases['civicrm_mailing_group']}.mailing_id = {$this->_aliases['civicrm_mailing']}.id"; } if ($this->campaignEnabled) { $this->_from .= " @@ -459,6 +460,7 @@ class CRM_Report_Form_Mailing_Summary extends CRM_Report_Form { public function groupBy() { $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_mailing']}.id"; + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_mailing']}.id"); } public function orderBy() { diff --git a/CRM/Report/Form/Member/ContributionDetail.php b/CRM/Report/Form/Member/ContributionDetail.php index 5ecf3266ba..85f39a1ff5 100644 --- a/CRM/Report/Form/Member/ContributionDetail.php +++ b/CRM/Report/Form/Member/ContributionDetail.php @@ -447,6 +447,7 @@ class CRM_Report_Form_Member_ContributionDetail extends CRM_Report_Form { } } + $this->_selectClauses = $select; $this->_select = 'SELECT ' . implode(', ', $select) . ' '; } @@ -589,7 +590,12 @@ class CRM_Report_Form_Member_ContributionDetail extends CRM_Report_Form { } public function groupBy() { - $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_contact']}.id, {$this->_aliases['civicrm_contribution']}.id "; + $groupBy = array( + "{$this->_aliases['civicrm_contact']}.id", + "{$this->_aliases['civicrm_contribution']}.id", + ); + $this->_groupBy = " GROUP BY " . implode(', ', $groupBy); + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } public function orderBy() { diff --git a/CRM/Report/Form/Member/Detail.php b/CRM/Report/Form/Member/Detail.php index cd6ed27ad8..fde2da1993 100644 --- a/CRM/Report/Form/Member/Detail.php +++ b/CRM/Report/Form/Member/Detail.php @@ -288,6 +288,7 @@ class CRM_Report_Form_Member_Detail extends CRM_Report_Form { } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } diff --git a/CRM/Report/Form/Member/Summary.php b/CRM/Report/Form/Member/Summary.php index d5400f5e56..de2cba8947 100644 --- a/CRM/Report/Form/Member/Summary.php +++ b/CRM/Report/Form/Member/Summary.php @@ -323,6 +323,7 @@ class CRM_Report_Form_Member_Summary extends CRM_Report_Form { unset($select['joinDate']); unset($this->_columnHeaders["civicrm_membership_member_join_date"]); } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -380,11 +381,13 @@ class CRM_Report_Form_Member_Summary extends CRM_Report_Form { } $this->_rollup = ' WITH ROLLUP'; - $this->_groupBy = 'GROUP BY ' . implode(', ', $this->_groupBy) . + $groupFromSelect = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $this->_groupBy); + $this->_groupBy = 'GROUP BY ' . implode(', ', $this->_groupBy) . $groupFromSelect . " {$this->_rollup} "; } else { - $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_membership']}.join_date"; + $groupFromSelect = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, "{$this->_aliases['civicrm_membership']}.join_date"); + $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_membership']}.join_date {$groupFromSelect}"; } } diff --git a/CRM/Report/Form/Pledge/Detail.php b/CRM/Report/Form/Pledge/Detail.php index 3417da4f6d..a318571583 100644 --- a/CRM/Report/Form/Pledge/Detail.php +++ b/CRM/Report/Form/Pledge/Detail.php @@ -252,7 +252,9 @@ class CRM_Report_Form_Pledge_Detail extends CRM_Report_Form { public function groupBy() { parent::groupBy(); if (empty($this->_groupBy) && $this->_totalPaid) { - $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_pledge']}.id, {$this->_aliases['civicrm_pledge']}.currency"; + $groupBy = array("{$this->_aliases['civicrm_pledge']}.id", "{$this->_aliases['civicrm_pledge']}.currency"); + $this->_groupBy = " GROUP BY " . implode(', ', $groupBy); + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } } @@ -467,11 +469,10 @@ class CRM_Report_Form_Pledge_Detail extends CRM_Report_Form { if (!empty($display)) { $statusId = array_keys(CRM_Core_PseudoConstant::accountOptionValues("contribution_status", NULL, " AND v.name IN ('Pending', 'Overdue')")); $statusId = implode(',', $statusId); + $select = "payment.pledge_id, payment.scheduled_amount, pledge.contact_id"; $sqlPayment = " SELECT min(payment.scheduled_date) as scheduled_date, - payment.pledge_id, - payment.scheduled_amount, - pledge.contact_id + {$select} FROM civicrm_pledge_payment payment LEFT JOIN civicrm_pledge pledge @@ -479,7 +480,7 @@ class CRM_Report_Form_Pledge_Detail extends CRM_Report_Form { WHERE payment.status_id IN ({$statusId}) - GROUP BY payment.pledge_id"; + GROUP BY {$select}"; $daoPayment = CRM_Core_DAO::executeQuery($sqlPayment); diff --git a/CRM/Report/Form/Pledge/Pbnp.php b/CRM/Report/Form/Pledge/Pbnp.php index e90e03b00d..43c81746f9 100644 --- a/CRM/Report/Form/Pledge/Pbnp.php +++ b/CRM/Report/Form/Pledge/Pbnp.php @@ -248,6 +248,7 @@ class CRM_Report_Form_Pledge_Pbnp extends CRM_Report_Form { } } } + $this->_selectClauses = $select; $this->_select = "SELECT " . implode(', ', $select) . " "; } @@ -301,10 +302,13 @@ class CRM_Report_Form_Pledge_Pbnp extends CRM_Report_Form { } public function groupBy() { - $this->_groupBy = " - GROUP BY {$this->_aliases['civicrm_pledge']}.contact_id, - {$this->_aliases['civicrm_pledge']}.id, - {$this->_aliases['civicrm_pledge']}.currency"; + $groupBy = array( + "{$this->_aliases['civicrm_pledge']}.contact_id", + "{$this->_aliases['civicrm_pledge']}.id", + "{$this->_aliases['civicrm_pledge']}.currency", + ); + $this->_groupBy = "GROUP BY " . implode(', ', $groupBy); + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); } public function orderBy() { diff --git a/CRM/Report/Form/Pledge/Summary.php b/CRM/Report/Form/Pledge/Summary.php index 04f10bbae7..2b002ff0b1 100644 --- a/CRM/Report/Form/Pledge/Summary.php +++ b/CRM/Report/Form/Pledge/Summary.php @@ -273,12 +273,15 @@ class CRM_Report_Form_Pledge_Summary extends CRM_Report_Form { ) { $this->_rollup = " WITH ROLLUP"; } - $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy) . - " {$this->_rollup} "; + $groupBy = $this->_groupBy; + $this->_groupBy = "GROUP BY " . implode(', ', $this->_groupBy); } else { - $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contact']}.id"; + $groupBy = "{$this->_aliases['civicrm_contact']}.id"; + $this->_groupBy = "GROUP BY {$groupBy}"; } + $this->_groupBy .= CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy); + $this->_groupBy .= " {$this->_rollup}"; } /** -- 2.25.1