From edae5c54a5dd729ff7bd0e0f8a1ee807530bf7ba Mon Sep 17 00:00:00 2001 From: Seamus Lee Date: Tue, 12 Nov 2019 07:42:09 +1100 Subject: [PATCH] dev/core#183 Convert Include group / exclude group and include tags / exclude tags custom search to using CRM_Utils_SQL_TempTable interface --- CRM/Contact/Form/Search/Custom/Group.php | 119 +++++++++++++++-------- 1 file changed, 80 insertions(+), 39 deletions(-) diff --git a/CRM/Contact/Form/Search/Custom/Group.php b/CRM/Contact/Form/Search/Custom/Group.php index acaafa02ab..4f528da8cc 100644 --- a/CRM/Contact/Form/Search/Custom/Group.php +++ b/CRM/Contact/Form/Search/Custom/Group.php @@ -34,7 +34,45 @@ class CRM_Contact_Form_Search_Custom_Group extends CRM_Contact_Form_Search_Custo protected $_formValues; - protected $_tableName = NULL; + /** + * @var CRM_Utils_SQL_TemTable Object + */ + protected $_xGTable = NULL; + + /** + * @var CRM_Utils_SQL_TempTable object + */ + protected $_iGTable = NULL; + + /** + * @var string Table Name for xclude Groups + */ + protected $_xGTableName = NULL; + + /** + * @var string Table Name for Inclue Groups + */ + protected $_iGTableName = NULL; + + /** + * @var CRM_Utils_SQL_TemTable Object + */ + protected $_xTTable = NULL; + + /** + * @var CRM_Utils_SQL_TempTable object + */ + protected $_iTTable = NULL; + + /** + * @var string Table Name for xclude Groups + */ + protected $_xTTableName = NULL; + + /** + * @var string Table Name for Inclue Groups + */ + protected $_iTTableName = NULL; protected $_where = ' (1) '; @@ -243,9 +281,11 @@ class CRM_Contact_Form_Search_Custom_Group extends CRM_Contact_Form_Search_Custo $iGroups = $xGroups = $iTags = $xTags = 0; - //define table name - $randomNum = md5(uniqid()); - $this->_tableName = "civicrm_temp_custom_{$randomNum}"; + //define table names + $this->_xGTable = CRM_Utils_SQL_TempTable::build()->setCategory('xggroup'); + $this->_xGTableName = $this->_xGTable->getName(); + $this->_iGTable = CRM_Utils_SQL_TempTable::build()->setCategory('iggroup'); + $this->_iGTableName = $this->_iGTable->getName(); //block for Group search $smartGroup = []; @@ -281,12 +321,11 @@ class CRM_Contact_Form_Search_Custom_Group extends CRM_Contact_Form_Search_Custo $xGroups = 0; } - $sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=InnoDB"; - CRM_Core_DAO::executeQuery($sql); + $this->_xGTable->createWithColumns("contact_id int primary key"); //used only when exclude group is selected if ($xGroups != 0) { - $excludeGroup = "INSERT INTO Xg_{$this->_tableName} ( contact_id ) + $excludeGroup = "INSERT INTO {$this->_xGTableName} ( contact_id ) SELECT DISTINCT civicrm_group_contact.contact_id FROM civicrm_group_contact, civicrm_contact WHERE @@ -311,20 +350,16 @@ SELECT gcc.contact_id FROM civicrm_group_contact_cache gcc WHERE gcc.group_id = {$ssGroup->id} "; - $smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) $smartSql"; + $smartGroupQuery = " INSERT IGNORE INTO {$this->_xGTableName}(contact_id) $smartSql"; CRM_Core_DAO::executeQuery($smartGroupQuery); } } } - $sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT, - contact_id int, - group_names varchar(64)) ENGINE=InnoDB"; - - CRM_Core_DAO::executeQuery($sql); + $this->_iGTable->createWithColumns("id int PRIMARY KEY AUTO_INCREMENT, contact_id int, group_names varchar(64)"); if ($iGroups) { - $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names) + $includeGroup = "INSERT INTO {$this->_iGTableName} (contact_id, group_names) SELECT civicrm_contact.id as contact_id, civicrm_group.title as group_name FROM civicrm_contact INNER JOIN civicrm_group_contact @@ -333,14 +368,14 @@ WHERE gcc.group_id = {$ssGroup->id} ON civicrm_group_contact.group_id = civicrm_group.id"; } else { - $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names) + $includeGroup = "INSERT INTO {$this->_iGTableName} (contact_id, group_names) SELECT civicrm_contact.id as contact_id, '' FROM civicrm_contact"; } //used only when exclude group is selected if ($xGroups != 0) { - $includeGroup .= " LEFT JOIN Xg_{$this->_tableName} - ON civicrm_contact.id = Xg_{$this->_tableName}.contact_id"; + $includeGroup .= " LEFT JOIN {$this->_xGTableName} + ON civicrm_contact.id = {$this->_xGTableName}.contact_id"; } if ($iGroups) { @@ -354,7 +389,7 @@ WHERE gcc.group_id = {$ssGroup->id} //used only when exclude group is selected if ($xGroups != 0) { - $includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null"; + $includeGroup .= " AND {$this->_xGTableName}.contact_id IS null"; } CRM_Core_DAO::executeQuery($includeGroup); @@ -378,18 +413,18 @@ WHERE gcc.group_id = {$ssGroup->id} //used only when exclude group is selected if ($xGroups != 0) { - $smartSql .= " AND gcc.contact_id NOT IN (SELECT contact_id FROM Xg_{$this->_tableName})"; + $smartSql .= " AND gcc.contact_id NOT IN (SELECT contact_id FROM {$this->_xGTableName})"; } - $smartGroupQuery = " INSERT IGNORE INTO Ig_{$this->_tableName}(contact_id) + $smartGroupQuery = " INSERT IGNORE INTO {$this->_iGTableName}(contact_id) $smartSql"; CRM_Core_DAO::executeQuery($smartGroupQuery); - $insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName} + $insertGroupNameQuery = "UPDATE IGNORE {$this->_iGTableName} SET group_names = (SELECT title FROM civicrm_group WHERE civicrm_group.id = $values) - WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL - AND Ig_{$this->_tableName}.group_names IS NULL"; + WHERE {$this->_iGTableName}.contact_id IS NOT NULL + AND {$this->_iGTableName}.group_names IS NULL"; CRM_Core_DAO::executeQuery($insertGroupNameQuery); } } @@ -421,12 +456,16 @@ WHERE gcc.group_id = {$ssGroup->id} $xTags = 0; } - $sql = "CREATE TEMPORARY TABLE Xt_{$this->_tableName} ( contact_id int primary key) ENGINE=InnoDB"; - CRM_Core_DAO::executeQuery($sql); + $this->_xTTable = CRM_Utils_SQL_TempTable::build()->setCategory('xtgroup'); + $this->_xTTableName = $this->_xTTable->getName(); + $this->_iTTable = CRM_Utils_SQL_TempTable::build()->setCategory('itgroup'); + $this->_iTTableName = $this->_iTTable->getName(); + + $this->_xTTable->createWithColumns("contact_id int primary key"); //used only when exclude tag is selected if ($xTags != 0) { - $excludeTag = "INSERT INTO Xt_{$this->_tableName} ( contact_id ) + $excludeTag = "INSERT INTO {$this->_xTTableName} ( contact_id ) SELECT DISTINCT civicrm_entity_tag.entity_id FROM civicrm_entity_tag, civicrm_contact WHERE @@ -437,14 +476,10 @@ WHERE gcc.group_id = {$ssGroup->id} CRM_Core_DAO::executeQuery($excludeTag); } - $sql = "CREATE TEMPORARY TABLE It_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT, - contact_id int, - tag_names varchar(64)) ENGINE=InnoDB"; - - CRM_Core_DAO::executeQuery($sql); + $this->_iTTable->createWithColumns("id int PRIMARY KEY AUTO_INCREMENT, contact_id int, tag_names varchar(64)"); if ($iTags) { - $includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names) + $includeTag = "INSERT INTO {$this->_iTTableName} (contact_id, tag_names) SELECT civicrm_contact.id as contact_id, civicrm_tag.name as tag_name FROM civicrm_contact INNER JOIN civicrm_entity_tag @@ -454,15 +489,15 @@ WHERE gcc.group_id = {$ssGroup->id} ON civicrm_entity_tag.tag_id = civicrm_tag.id"; } else { - $includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names) + $includeTag = "INSERT INTO {$this->_iTTableName} (contact_id, tag_names) SELECT civicrm_contact.id as contact_id, '' FROM civicrm_contact"; } //used only when exclude tag is selected if ($xTags != 0) { - $includeTag .= " LEFT JOIN Xt_{$this->_tableName} - ON civicrm_contact.id = Xt_{$this->_tableName}.contact_id"; + $includeTag .= " LEFT JOIN {$this->_xTTableName} + ON civicrm_contact.id = {$this->_xTTableName}.contact_id"; } if ($iTags) { $includeTag .= " WHERE civicrm_entity_tag.tag_id IN($iTags)"; @@ -473,7 +508,7 @@ WHERE gcc.group_id = {$ssGroup->id} //used only when exclude tag is selected if ($xTags != 0) { - $includeTag .= " AND Xt_{$this->_tableName}.contact_id IS null"; + $includeTag .= " AND {$this->_xTTableName}.contact_id IS null"; } CRM_Core_DAO::executeQuery($includeTag); @@ -505,15 +540,21 @@ WHERE gcc.group_id = {$ssGroup->id} * Set from statement depending on array sel */ $whereitems = []; + $tableNames = [ + 'Ig' => $this->_iGTableName, + 'Xg' => $this->_xGTableName, + 'It' => $this->_iTTableName, + 'Xt' => $this->_xTTableName, + ]; foreach (['Ig', 'It'] as $inc) { if ($this->_andOr == 1) { if ($$inc) { - $from .= " INNER JOIN {$inc}_{$this->_tableName} temptable$inc ON (contact_a.id = temptable$inc.contact_id)"; + $from .= " INNER JOIN {$tableNames[$inc]} temptable$inc ON (contact_a.id = temptable$inc.contact_id)"; } } else { if ($$inc) { - $from .= " LEFT JOIN {$inc}_{$this->_tableName} temptable$inc ON (contact_a.id = temptable$inc.contact_id)"; + $from .= " LEFT JOIN {$tableNames[$inc]} temptable$inc ON (contact_a.id = temptable$inc.contact_id)"; } } if ($$inc) { @@ -523,7 +564,7 @@ WHERE gcc.group_id = {$ssGroup->id} $this->_where = $whereitems ? "(" . implode(' OR ', $whereitems) . ')' : '(1)'; foreach (['Xg', 'Xt'] as $exc) { if ($$exc) { - $from .= " LEFT JOIN {$exc}_{$this->_tableName} temptable$exc ON (contact_a.id = temptable$exc.contact_id)"; + $from .= " LEFT JOIN {$tableNames[$exc]} temptable$exc ON (contact_a.id = temptable$exc.contact_id)"; $this->_where .= " AND temptable$exc.contact_id IS NULL"; } } -- 2.25.1