From 47e886934211c581107274f2d2b0a19e817ab46c Mon Sep 17 00:00:00 2001 From: mark burdett Date: Thu, 14 Mar 2019 16:42:46 -0700 Subject: [PATCH] Use TempTable methods. --- CRM/Contact/Form/Search/Custom/FullText.php | 15 +++++-------- CRM/Report/Form/ActivitySummary.php | 18 +++++---------- CRM/Report/Form/Member/ContributionDetail.php | 22 +++++++++---------- 3 files changed, 22 insertions(+), 33 deletions(-) diff --git a/CRM/Contact/Form/Search/Custom/FullText.php b/CRM/Contact/Form/Search/Custom/FullText.php index 8680dceb00..44c0f399be 100644 --- a/CRM/Contact/Form/Search/Custom/FullText.php +++ b/CRM/Contact/Form/Search/Custom/FullText.php @@ -147,8 +147,8 @@ class CRM_Contact_Form_Search_Custom_FullText extends CRM_Contact_Form_Search_Cu } public function buildTempTable() { - $randomNum = md5(uniqid()); - $this->_tableName = "civicrm_temp_custom_details_{$randomNum}"; + $table = CRM_Utils_SQL_TempTable::build()->setCategory('custom')->setMemory()->setUtf8(); + $this->_tableName = $table->getName(); $this->_tableFields = array( 'id' => 'int unsigned NOT NULL AUTO_INCREMENT', @@ -200,7 +200,6 @@ class CRM_Contact_Form_Search_Custom_FullText extends CRM_Contact_Form_Search_Cu ); $sql = " -CREATE TEMPORARY TABLE {$this->_tableName} ( "; foreach ($this->_tableFields as $name => $desc) { @@ -209,21 +208,19 @@ CREATE TEMPORARY TABLE {$this->_tableName} ( $sql .= " PRIMARY KEY ( id ) -) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci "; - CRM_Core_DAO::executeQuery($sql); + $table->createWithColumns($sql); - $this->_entityIDTableName = "civicrm_temp_custom_entityID_{$randomNum}"; + $entityIdTable = CRM_Utils_SQL_TempTable::build()->setCategory('custom')->setMemory()->setUtf8(); + $this->_entityIDTableName = $entityIdTable->getName(); $sql = " -CREATE TEMPORARY TABLE {$this->_entityIDTableName} ( id int unsigned NOT NULL AUTO_INCREMENT, entity_id int unsigned NOT NULL, UNIQUE INDEX unique_entity_id ( entity_id ), PRIMARY KEY ( id ) -) ENGINE=HEAP DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci "; - CRM_Core_DAO::executeQuery($sql); + $entityIdTable->createWithColumns($sql); if (!empty($this->_formValues['is_unit_test'])) { $this->_tableNameForTest = $this->_tableName; diff --git a/CRM/Report/Form/ActivitySummary.php b/CRM/Report/Form/ActivitySummary.php index af071d83ba..2450a20eb6 100644 --- a/CRM/Report/Form/ActivitySummary.php +++ b/CRM/Report/Form/ActivitySummary.php @@ -532,9 +532,6 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { } CRM_Utils_Hook::alterReportVar('sql', $this, $this); - // store the duration count in temp table - $this->_tempTableName = CRM_Core_DAO::createTempTableName('civicrm_activity'); - // build temporary table column names base on column headers of result $dbColumns = array(); foreach ($this->_columnHeaders as $fieldName => $dontCare) { @@ -542,10 +539,9 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { } // create temp table to store main result - $tempQuery = "CREATE TEMPORARY TABLE {$this->_tempTableName} ( - id int unsigned NOT NULL AUTO_INCREMENT, " . implode(', ', $dbColumns) . ' , PRIMARY KEY (id))' - . $this->_databaseAttributes; - CRM_Core_DAO::executeQuery($tempQuery); + $this->_tempTableName = $this->createTemporaryTable('tempTable', " + id int unsigned NOT NULL AUTO_INCREMENT, " . implode(', ', $dbColumns) . ' , PRIMARY KEY (id)', + TRUE); // build main report query $sql = "{$this->_select} {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}"; @@ -565,11 +561,9 @@ class CRM_Report_Form_ActivitySummary extends CRM_Report_Form { $sql = "SELECT SUM(activity_civireport.duration) as civicrm_activity_duration_total {$this->_from} {$this->_where} {$this->_groupBy} {$this->_having} {$this->_orderBy} {$this->_limit}"; // create temp table to store duration - $this->_tempDurationSumTableName = CRM_Core_DAO::createTempTableName('civicrm_activity'); - $tempQuery = "CREATE TEMPORARY TABLE {$this->_tempDurationSumTableName} ( - id int unsigned NOT NULL AUTO_INCREMENT, civicrm_activity_duration_total VARCHAR(128), PRIMARY KEY (id))" - . $this->_databaseAttributes; - CRM_Core_DAO::executeQuery($tempQuery); + $this->_tempDurationSumTableName = $this->createTemporaryTable('tempDurationSumTable', " + id int unsigned NOT NULL AUTO_INCREMENT, civicrm_activity_duration_total VARCHAR(128), PRIMARY KEY (id)", + TRUE); // store the result in temporary table $insertQuery = "INSERT INTO {$this->_tempDurationSumTableName} (civicrm_activity_duration_total) diff --git a/CRM/Report/Form/Member/ContributionDetail.php b/CRM/Report/Form/Member/ContributionDetail.php index 519c23ef5a..47ab00637f 100644 --- a/CRM/Report/Form/Member/ContributionDetail.php +++ b/CRM/Report/Form/Member/ContributionDetail.php @@ -53,6 +53,8 @@ class CRM_Report_Form_Member_ContributionDetail extends CRM_Report_Form { */ protected $groupFilterNotOptimised = TRUE; + protected $tableName; + /** * Class constructor. */ @@ -441,13 +443,13 @@ class CRM_Report_Form_Member_ContributionDetail extends CRM_Report_Form { public function from() { $this->_from = " - FROM civireport_membership_contribution_detail + FROM {$this->tableName} INNER JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} - ON (civireport_membership_contribution_detail.contribution_id = {$this->_aliases['civicrm_contribution']}.id) + ON ({$this->tableName}.contribution_id = {$this->_aliases['civicrm_contribution']}.id) LEFT JOIN civicrm_membership {$this->_aliases['civicrm_membership']} - ON (civireport_membership_contribution_detail.membership_id = {$this->_aliases['civicrm_membership']}.id) + ON ({$this->tableName}.membership_id = {$this->_aliases['civicrm_membership']}.id) INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} - ON (civireport_membership_contribution_detail.contact_id = {$this->_aliases['civicrm_contact']}.id) + ON ({$this->tableName}.contact_id = {$this->_aliases['civicrm_contact']}.id) LEFT JOIN civicrm_membership_status {$this->_aliases['civicrm_membership_status']} ON {$this->_aliases['civicrm_membership_status']}.id = {$this->_aliases['civicrm_membership']}.status_id @@ -505,16 +507,12 @@ class CRM_Report_Form_Member_ContributionDetail extends CRM_Report_Form { */ public function tempTable($applyLimit = TRUE) { // create temp table with contact ids,contribtuion id,membership id - $dropTempTable = 'DROP TEMPORARY TABLE IF EXISTS civireport_membership_contribution_detail'; - CRM_Core_DAO::executeQuery($dropTempTable); - - $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' . $this->_databaseAttributes; - CRM_Core_DAO::executeQuery($sql); + $this->tableName = $this->createTemporaryTable('table', ' + 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)', TRUE, TRUE); $fillTemp = " - INSERT INTO civireport_membership_contribution_detail (contribution_id, contact_id, membership_id) + INSERT INTO {$this->tableName} (contribution_id, contact_id, membership_id) SELECT contribution.id, {$this->_aliases['civicrm_contact']}.id, m.id FROM civicrm_contribution contribution INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']} -- 2.25.1