From 03843223700d79cedbfa14c882c43fbbf7889bbd Mon Sep 17 00:00:00 2001
From: mark burdett <mfburdett@gmail.com>
Date: Thu, 14 Mar 2019 16:42:46 -0700
Subject: [PATCH] Use TempTable methods.

---
 CRM/Report/Form.php                         | 25 ++++++++++++++++-----
 CRM/Report/Form/Contribute/Repeat.php       | 14 ++++--------
 tests/phpunit/api/v3/ReportTemplateTest.php |  2 +-
 3 files changed, 24 insertions(+), 17 deletions(-)

diff --git a/CRM/Report/Form.php b/CRM/Report/Form.php
index 900a35d493..06c3df5caf 100644
--- a/CRM/Report/Form.php
+++ b/CRM/Report/Form.php
@@ -1146,18 +1146,31 @@ class CRM_Report_Form extends CRM_Core_Form {
    * This function creates a table AND adds the details to the developer tab & $this->>temporary tables.
    *
    * @param string $identifier
-   * @param $sql
+   *   This is the key that will be used for the table in the temporaryTables property.
+   * @param string $sql
+   *   Sql select statement or column description (the latter requires the columns flag)
+   * @param bool $isColumns
+   *   Is the sql describing columns to create (rather than using a select query).
+   * @param bool $isMemory
+   *   Create a memory table rather than a normal INNODB table.
    *
    * @return string
    */
-  public function createTemporaryTable($identifier, $sql) {
-    $tempTable = CRM_Utils_SQL_TempTable::build()->setUtf8(TRUE)->createWithQuery($sql);
+  public function createTemporaryTable($identifier, $sql, $isColumns = FALSE, $isMemory = FALSE) {
+    $tempTable = CRM_Utils_SQL_TempTable::build()->setUtf8();
+    if ($isMemory) {
+      $tempTable->setMemory();
+    }
+    if ($isColumns) {
+      $tempTable->createWithColumns($sql);
+    }
+    else {
+      $tempTable->createWithQuery($sql);
+    }
     $name = $tempTable->getName();
     // Developers may force tables to be durable to assist in debugging so lets check.
     $isNotTrueTemporary = $tempTable->isDurable();
-    // The TempTable build routine adds the next line - we output it to help developers see what has happened.
-    $sql = 'CREATE ' . ($isNotTrueTemporary ? '' : 'TEMPORARY ') . "TABLE $name DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci " . $sql;
-    $this->addToDeveloperTab($sql);
+    $this->addToDeveloperTab($tempTable->getCreateSql());
     $this->temporaryTables[$identifier] = ['temporary' => !$isNotTrueTemporary, 'name' => $name];
     return $name;
   }
diff --git a/CRM/Report/Form/Contribute/Repeat.php b/CRM/Report/Form/Contribute/Repeat.php
index e9f0d41821..142c7eb403 100644
--- a/CRM/Report/Form/Contribute/Repeat.php
+++ b/CRM/Report/Form/Contribute/Repeat.php
@@ -1030,31 +1030,25 @@ SELECT {$subSelect2} contribution2.{$this->contributionJoinTableColumn},
 {$from}
 {$subWhere}
 GROUP BY contribution2.{$this->contributionJoinTableColumn}, currency";
-    $this->tempTableRepeat1 = 'civicrm_temp_civireport_repeat1' . uniqid();
-    $sql = "
-CREATE TEMPORARY TABLE $this->tempTableRepeat1 (
+    $this->tempTableRepeat1 = $this->createTemporaryTable('tempTableRepeat1', "
 {$create}
 {$this->contributionJoinTableColumn} int unsigned,
 total_amount_sum decimal(20,2),
 total_amount_count int
-) ENGINE=HEAP {$this->_databaseAttributes}";
-    $this->executeReportQuery($sql);
+", TRUE, TRUE);
     $this->executeReportQuery("INSERT INTO $this->tempTableRepeat1 {$subContributionQuery1}");
 
     $this->executeReportQuery("
       ALTER TABLE $this->tempTableRepeat1 ADD INDEX ({$this->contributionJoinTableColumn})
     ");
 
-    $this->tempTableRepeat2 = 'civicrm_temp_civireport_repeat2' . uniqid();
-    $sql = "
-CREATE TEMPORARY TABLE  $this->tempTableRepeat2 (
+    $this->tempTableRepeat2 = $this->createTemporaryTable('tempTableRepeat2', "
 {$create}
 {$this->contributionJoinTableColumn} int unsigned,
 total_amount_sum decimal(20,2),
 total_amount_count int,
 currency varchar(3)
-) ENGINE=HEAP {$this->_databaseAttributes}";
-    $this->executeReportQuery($sql);
+", TRUE, TRUE);
     $sql = "INSERT INTO $this->tempTableRepeat2 {$subContributionQuery2}";
     $this->executeReportQuery($sql);
 
diff --git a/tests/phpunit/api/v3/ReportTemplateTest.php b/tests/phpunit/api/v3/ReportTemplateTest.php
index 2f02b1409a..d328da7b12 100644
--- a/tests/phpunit/api/v3/ReportTemplateTest.php
+++ b/tests/phpunit/api/v3/ReportTemplateTest.php
@@ -473,7 +473,7 @@ class api_v3_ReportTemplateTest extends CiviUnitTestCase {
 
     $this->assertEquals(2, $rows['count'], "Report failed - the sql used to generate the results was " . print_r($rows['metadata']['sql'], TRUE));
 
-    $expected = preg_replace('/\s+/', ' ', 'DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
+    $expected = preg_replace('/\s+/', ' ', 'DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
       SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as cid  FROM civicrm_contact contact_civireport    INNER JOIN civicrm_contribution contribution_civireport USE index (received_date) ON contribution_civireport.contact_id = contact_civireport.id
          AND contribution_civireport.is_test = 0
          AND contribution_civireport.receive_date BETWEEN \'20140701000000\' AND \'20150630235959\'
-- 
2.25.1