From a2e4e741bf7f4dc95c2286dc4e1b64fe1df5077e Mon Sep 17 00:00:00 2001 From: Seamus Lee Date: Sat, 19 Nov 2016 09:03:14 +1100 Subject: [PATCH] CRM-19509 Apply standard database attributes to reports and standardise using defined variable --- CRM/Report/Form.php | 6 +++--- CRM/Report/Form/Activity.php | 2 +- CRM/Report/Form/Contribute/Bookkeeping.php | 2 +- CRM/Report/Form/Contribute/Detail.php | 6 +++--- CRM/Report/Form/Contribute/Lybunt.php | 2 +- CRM/Report/Form/Contribute/Repeat.php | 4 ++-- CRM/Report/Form/Member/ContributionDetail.php | 2 +- 7 files changed, 12 insertions(+), 12 deletions(-) diff --git a/CRM/Report/Form.php b/CRM/Report/Form.php index de0ad122ad..9d9c150a47 100644 --- a/CRM/Report/Form.php +++ b/CRM/Report/Form.php @@ -3472,7 +3472,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND * This function is called by both the api (tests) and the UI. */ public function buildGroupTempTable() { - if (!empty($this->groupTempTable) || empty ($this->_params['gid_value']) || $this->groupFilterNotOptimised) { + if (!empty($this->groupTempTable) || empty($this->_params['gid_value']) || $this->groupFilterNotOptimised) { return; } $filteredGroups = (array) $this->_params['gid_value']; @@ -3503,7 +3503,7 @@ WHERE cg.extends IN ('" . implode("','", $this->_customGroupExtends) . "') AND $this->groupTempTable = 'civicrm_report_temp_group_' . date('Ymd_') . uniqid(); $this->executeReportQuery(" - CREATE TEMPORARY TABLE $this->groupTempTable + CREATE TEMPORARY TABLE $this->groupTempTable $this->_databaseAttributes $query "); CRM_Core_DAO::executeQuery("ALTER TABLE $this->groupTempTable ADD INDEX i_id(id)"); @@ -4700,7 +4700,7 @@ LEFT JOIN civicrm_contact {$field['alias']} ON {$field['alias']}.id = {$this->_a $query->_where = "WHERE {$query->_aliases['civicrm_contribution']}.id IS NOT NULL "; } CRM_Core_DAO::executeQuery("DROP TEMPORARY TABLE IF EXISTS civicrm_contribution_temp"); - $sql = "CREATE TEMPORARY TABLE civicrm_contribution_temp AS SELECT {$query->_aliases['civicrm_contribution']}.id {$query->_from} + $sql = "CREATE TEMPORARY TABLE civicrm_contribution_temp {$this->_databaseAttributes} AS SELECT {$query->_aliases['civicrm_contribution']}.id {$query->_from} LEFT JOIN civicrm_line_item {$query->_aliases['civicrm_line_item']} ON {$query->_aliases['civicrm_contribution']}.id = {$query->_aliases['civicrm_line_item']}.contribution_id AND {$query->_aliases['civicrm_line_item']}.entity_table = 'civicrm_contribution' diff --git a/CRM/Report/Form/Activity.php b/CRM/Report/Form/Activity.php index f68b36a6c6..dfb72a156d 100644 --- a/CRM/Report/Form/Activity.php +++ b/CRM/Report/Form/Activity.php @@ -791,7 +791,7 @@ GROUP BY civicrm_activity_id $having {$this->_orderBy}"; $this->customDataFrom(); $this->where('target'); $insertCols = implode(',', $this->_selectAliases); - $tempQuery = "CREATE TEMPORARY TABLE civireport_activity_temp_target CHARACTER SET utf8 COLLATE utf8_unicode_ci AS + $tempQuery = "CREATE TEMPORARY TABLE civireport_activity_temp_target" . $this->_databaseAttributes . " AS {$this->_select} {$this->_from} {$this->_where} "; CRM_Core_DAO::executeQuery($tempQuery); diff --git a/CRM/Report/Form/Contribute/Bookkeeping.php b/CRM/Report/Form/Contribute/Bookkeeping.php index 34bdb94f23..230c148efd 100644 --- a/CRM/Report/Form/Contribute/Bookkeeping.php +++ b/CRM/Report/Form/Contribute/Bookkeeping.php @@ -555,7 +555,7 @@ class CRM_Report_Form_Contribute_Bookkeeping extends CRM_Report_Form { $this->groupBy(); - $tempQuery = "CREATE TEMPORARY TABLE {$tempTableName} CHARACTER SET utf8 COLLATE utf8_unicode_ci AS + $tempQuery = "CREATE TEMPORARY TABLE {$tempTableName} {$this->_databaseAttributes} AS {$select} {$this->_from} {$this->_where} {$this->_groupBy} "; CRM_Core_DAO::executeQuery($tempQuery); diff --git a/CRM/Report/Form/Contribute/Detail.php b/CRM/Report/Form/Contribute/Detail.php index 8b8bf7f826..9815cecf8d 100644 --- a/CRM/Report/Form/Contribute/Detail.php +++ b/CRM/Report/Form/Contribute/Detail.php @@ -557,7 +557,7 @@ GROUP BY {$this->_aliases['civicrm_contribution']}.currency"; // 1. use main contribution query to build temp table 1 $sql = $this->buildQuery(); - $tempQuery = 'CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 AS ' . $sql; + $tempQuery = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp1 {$this->_databaseAttributes} AS {$sql}"; CRM_Core_DAO::executeQuery($tempQuery); $this->setPager(); @@ -575,7 +575,7 @@ GROUP BY {$this->_aliases['civicrm_contribution']}.currency"; } // we inner join with temp1 to restrict soft contributions to those in temp1 table $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy}"; - $tempQuery = 'CREATE TEMPORARY TABLE civireport_contribution_detail_temp2 AS ' . $sql; + $tempQuery = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp2 {$this->_databaseAttributes} AS {$sql}"; CRM_Core_DAO::executeQuery($tempQuery); if (CRM_Utils_Array::value('contribution_or_soft_value', $this->_params) == 'soft_credits_only' @@ -613,7 +613,7 @@ UNION ALL } // 4. build temp table 3 - $sql = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3 AS {$tempQuery}"; + $sql = "CREATE TEMPORARY TABLE civireport_contribution_detail_temp3 {$this->_databaseAttributes} AS {$tempQuery}"; CRM_Core_DAO::executeQuery($sql); // 5. Re-construct order-by to make sense for final query on temp3 table diff --git a/CRM/Report/Form/Contribute/Lybunt.php b/CRM/Report/Form/Contribute/Lybunt.php index ce1a06c7c0..324f4e8d7d 100644 --- a/CRM/Report/Form/Contribute/Lybunt.php +++ b/CRM/Report/Form/Contribute/Lybunt.php @@ -556,7 +556,7 @@ class CRM_Report_Form_Contribute_Lybunt extends CRM_Report_Form { $this->contactTempTable = 'civicrm_report_temp_lybunt_c_' . date('Ymd_') . uniqid(); $this->limit(); $getContacts = " - CREATE TEMPORARY TABLE $this->contactTempTable + CREATE TEMPORARY TABLE $this->contactTempTable {$this->_databaseAttributes} SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid {$this->_from} {$this->_where} GROUP BY {$this->_aliases['civicrm_contact']}.id"; $this->executeReportQuery($getContacts); diff --git a/CRM/Report/Form/Contribute/Repeat.php b/CRM/Report/Form/Contribute/Repeat.php index e90ba421a9..0e8960541e 100644 --- a/CRM/Report/Form/Contribute/Repeat.php +++ b/CRM/Report/Form/Contribute/Repeat.php @@ -1032,7 +1032,7 @@ CREATE TEMPORARY TABLE $this->tempTableRepeat1 ( {$this->contributionJoinTableColumn} int unsigned, total_amount_sum int, total_amount_count int -) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci"; +) ENGINE=HEAP {$this->_databaseAttributes}"; CRM_Core_DAO::executeQuery($sql); CRM_Core_DAO::executeQuery("INSERT INTO $this->tempTableRepeat1 {$subContributionQuery1}"); @@ -1048,7 +1048,7 @@ CREATE TEMPORARY TABLE $this->tempTableRepeat2 ( total_amount_sum int, total_amount_count int, currency varchar(3) -) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci"; +) ENGINE=HEAP {$this->_databaseAttributes}"; CRM_Core_DAO::executeQuery($sql); $sql = "INSERT INTO $this->tempTableRepeat2 {$subContributionQuery2}"; CRM_Core_DAO::executeQuery($sql); diff --git a/CRM/Report/Form/Member/ContributionDetail.php b/CRM/Report/Form/Member/ContributionDetail.php index 66d1278a88..51efbd8814 100644 --- a/CRM/Report/Form/Member/ContributionDetail.php +++ b/CRM/Report/Form/Member/ContributionDetail.php @@ -551,7 +551,7 @@ class CRM_Report_Form_Member_ContributionDetail extends CRM_Report_Form { $sql = 'CREATE TEMPORARY TABLE civireport_membership_contribution_detail (contribution_id int, INDEX USING HASH(contribution_id), contact_id int, INDEX USING HASH(contact_id), - membership_id int, INDEX USING HASH(membership_id), payment_id int, INDEX USING HASH(payment_id)) ENGINE=MEMORY'; + membership_id int, INDEX USING HASH(membership_id), payment_id int, INDEX USING HASH(payment_id)) ENGINE=MEMORY' . $this->_databaseAttributes; CRM_Core_DAO::executeQuery($sql); $fillTemp = " -- 2.25.1