From 4f5de90392bf6ae173822605664d2ccdf86236dc Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Fri, 30 May 2014 01:07:52 -0700 Subject: [PATCH] CRM-14765 - FullText - Split monolithic search class into 8 smaller classes with more explicit contracts This will make it easier to understand what's going on when we add a new partial-query that is not backed by SQL. In fact, it should make it easier to understand... period... --- CRM/Contact/Form/Search/Custom/FullText.php | 608 +----------------- .../Custom/FullText/AbstractPartialQuery.php | 215 +++++++ .../Form/Search/Custom/FullText/Activity.php | 131 ++++ .../Form/Search/Custom/FullText/Case.php | 114 ++++ .../Form/Search/Custom/FullText/Contact.php | 133 ++++ .../Search/Custom/FullText/Contribution.php | 119 ++++ .../Search/Custom/FullText/Membership.php | 101 +++ .../Search/Custom/FullText/Participant.php | 112 ++++ 8 files changed, 956 insertions(+), 577 deletions(-) create mode 100644 CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php create mode 100644 CRM/Contact/Form/Search/Custom/FullText/Activity.php create mode 100644 CRM/Contact/Form/Search/Custom/FullText/Case.php create mode 100644 CRM/Contact/Form/Search/Custom/FullText/Contact.php create mode 100644 CRM/Contact/Form/Search/Custom/FullText/Contribution.php create mode 100644 CRM/Contact/Form/Search/Custom/FullText/Membership.php create mode 100644 CRM/Contact/Form/Search/Custom/FullText/Participant.php diff --git a/CRM/Contact/Form/Search/Custom/FullText.php b/CRM/Contact/Form/Search/Custom/FullText.php index c9709ea30b..4631aa7c85 100644 --- a/CRM/Contact/Form/Search/Custom/FullText.php +++ b/CRM/Contact/Form/Search/Custom/FullText.php @@ -36,6 +36,11 @@ class CRM_Contact_Form_Search_Custom_FullText implements CRM_Contact_Form_Search const LIMIT = 10; + /** + * @var array CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery + */ + protected $_partialQueries = NULL; + protected $_formValues; protected $_columns; @@ -65,6 +70,15 @@ class CRM_Contact_Form_Search_Custom_FullText implements CRM_Contact_Form_Search * @param $formValues */ function __construct(&$formValues) { + $this->_partialQueries = array( + new CRM_Contact_Form_Search_Custom_FullText_Contact(), + new CRM_Contact_Form_Search_Custom_FullText_Activity(), + new CRM_Contact_Form_Search_Custom_FullText_Case(), + new CRM_Contact_Form_Search_Custom_FullText_Contribution(), + new CRM_Contact_Form_Search_Custom_FullText_Participant(), + new CRM_Contact_Form_Search_Custom_FullText_Membership(), + ); + $formValues['table'] = $this->getFieldValue($formValues, 'table', 'String'); $this->_table = $formValues['table']; @@ -108,31 +122,6 @@ class CRM_Contact_Form_Search_Custom_FullText implements CRM_Contact_Form_Search return $value; } - /** - * Format text to include wild card characters at beginning and end - * - * @param string $text - * @return string - */ - public function toSqlWildCard($text) { - if ($text) { - $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower'; - $text = $strtolower(CRM_Core_DAO::escapeString($text)); - if (strpos($text, '%') === FALSE) { - $text = "'%{$text}%'"; - return $text; - } - else { - $text = "'{$text}'"; - return $text; - } - } - else { - $text = "'%'"; - return $text; - } - } - function __destruct() { } @@ -226,50 +215,13 @@ CREATE TEMPORARY TABLE {$this->_entityIDTableName} ( } function fillTable() { - $config = CRM_Core_Config::singleton(); - - if ((!$this->_table || $this->_table == 'Contact')) { - $this->_foundRows['Contact'] = $this->fillContactIDs($this->_text, $this->_entityIDTableName, $this->_limitClause); - $this->moveContactIDs($this->_entityIDTableName, $this->_tableName, $this->_limitDetailClause); - } - - if ((!$this->_table || $this->_table == 'Activity') && - CRM_Core_Permission::check('view all activities') - ) { - $this->_foundRows['Activity'] = $this->fillActivityIDs($this->_text, $this->_entityIDTableName, $this->_limitClause); - $this->moveActivityIDs($this->_entityIDTableName, $this->_tableName, $this->_limitDetailClause); - } - - if ((!$this->_table || $this->_table == 'Case') && - in_array('CiviCase', $config->enableComponents) - ) { - $this->_foundRows['Case'] = $this->fillCaseIDs($this->_text, $this->_entityIDTableName, $this->_limitClause); - $this->moveCaseIDs($this->_entityIDTableName, $this->_tableName, $this->_limitDetailClause); - } - - if ((!$this->_table || $this->_table == 'Contribution') && - in_array('CiviContribute', $config->enableComponents) && - CRM_Core_Permission::check('access CiviContribute') - ) { - $this->_foundRows['Contribution'] = $this->fillContributionIDs($this->_text, $this->_entityIDTableName, $this->_limitClause); - $this->moveContributionIDs($this->_entityIDTableName, $this->_tableName, $this->_limitDetailClause); - } - - if ((!$this->_table || $this->_table == 'Participant') && - (in_array('CiviEvent', $config->enableComponents) && - CRM_Core_Permission::check('view event participants') - ) - ) { - $this->_foundRows['Participant'] = $this->fillParticipantIDs($this->_text, $this->_entityIDTableName, $this->_limitClause); - $this->moveParticipantIDs($this->_entityIDTableName, $this->_tableName, $this->_limitDetailClause); - } - - if ((!$this->_table || $this->_table == 'Membership') && - in_array('CiviMember', $config->enableComponents) && - CRM_Core_Permission::check('access CiviMember') - ) { - $this->_foundRows['Membership'] = $this->fillMembershipIDs($this->_text, $this->_entityIDTableName, $this->_limitClause); - $this->moveMembershipIDs($this->_entityIDTableName, $this->_tableName, $this->_limitDetailClause); + foreach ($this->_partialQueries as $partialQuery) { + /** @var $partialQuery CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery */ + if (!$this->_table || $this->_table == $partialQuery->getName()) { + if ($partialQuery->isActive()) { + $this->_foundRows[$partialQuery->getName()] = $partialQuery->fillTempTable($this->_text, $this->_entityIDTableName, $this->_tableName, $this->_limitClause, $this->_limitDetailClause); + } + } } $this->filterACLContacts(); @@ -321,488 +273,6 @@ WHERE t.table_name = 'Activity' AND CRM_Core_DAO::executeQuery($sql, $params); } - /** - * @param $tables - * @param $extends - */ - function fillCustomInfo(&$tables, $extends) { - $sql = " -SELECT cg.table_name, cf.column_name -FROM civicrm_custom_group cg -INNER JOIN civicrm_custom_field cf ON cf.custom_group_id = cg.id -WHERE cg.extends IN $extends -AND cg.is_active = 1 -AND cf.is_active = 1 -AND cf.is_searchable = 1 -AND cf.html_type IN ( 'Text', 'TextArea', 'RichTextEditor' ) -"; - - $dao = CRM_Core_DAO::executeQuery($sql); - while ($dao->fetch()) { - if (!array_key_exists($dao->table_name, $tables)) { - $tables[$dao->table_name] = array( - 'id' => 'entity_id', - 'fields' => array(), - ); - } - $tables[$dao->table_name]['fields'][$dao->column_name] = NULL; - } - } - - /** - * @param string $queryText - * @param array $tables - * @return int the total number of matches - */ - function runQueries($queryText, &$tables, $entityIDTableName, $limit) { - $sql = "TRUNCATE {$entityIDTableName}"; - CRM_Core_DAO::executeQuery($sql); - - foreach ($tables as $tableName => $tableValues) { - if ($tableName == 'final') { - continue; - } - else { - if ($tableName == 'sql') { - foreach ($tableValues as $sqlStatement) { - $sql = " -REPLACE INTO {$entityIDTableName} ( entity_id ) -$sqlStatement -{$limit} -"; - CRM_Core_DAO::executeQuery($sql); - } - } - else { - $clauses = array(); - - foreach ($tableValues['fields'] as $fieldName => $fieldType) { - if ($fieldType == 'Int') { - if (is_numeric($queryText)) { - $clauses[] = "$fieldName = {$queryText}"; - } - } - else { - $clauses[] = "$fieldName LIKE {$this->toSqlWildCard($queryText)}"; - } - } - - if (empty($clauses)) { - continue; - } - - $whereClause = implode(' OR ', $clauses); - - //resolve conflict between entity tables. - if ($tableName == 'civicrm_note' && - $entityTable = CRM_Utils_Array::value('entity_table', $tableValues) - ) { - $whereClause .= " AND entity_table = '{$entityTable}'"; - } - - $sql = " -REPLACE INTO {$entityIDTableName} ( entity_id ) -SELECT {$tableValues['id']} -FROM $tableName -WHERE ( $whereClause ) -AND {$tableValues['id']} IS NOT NULL -GROUP BY {$tableValues['id']} -{$limit} -"; - CRM_Core_DAO::executeQuery($sql); - } - } - } - - if (isset($tables['final'])) { - foreach ($tables['final'] as $sqlStatement) { - CRM_Core_DAO::executeQuery($sqlStatement); - } - } - - $rowCount = "SELECT count(*) FROM {$entityIDTableName}"; - return CRM_Core_DAO::singleValueQuery($rowCount); - } - - /** - * @param string $queryText - * @return int the total number of matches - */ - function fillContactIDs($queryText, $entityIDTableName, $limit) { - $contactSQL = array(); - $contactSQL[] = " -SELECT et.entity_id -FROM civicrm_entity_tag et -INNER JOIN civicrm_tag t ON et.tag_id = t.id -WHERE et.entity_table = 'civicrm_contact' -AND et.tag_id = t.id -AND t.name LIKE {$this->toSqlWildCard($queryText)} -GROUP BY et.entity_id -"; - - // lets delete all the deceased contacts from the entityID box - // this allows us to keep numbers in sync - // when we have acl contacts, the situation gets even more murky - $final = array(); - $final[] = "DELETE FROM {$this->_entityIDTableName} WHERE entity_id IN (SELECT id FROM civicrm_contact WHERE is_deleted = 1)"; - - $tables = array( - 'civicrm_contact' => array( - 'id' => 'id', - 'fields' => array( - 'sort_name' => NULL, - 'nick_name' => NULL, - 'display_name' => NULL, - ), - ), - 'civicrm_address' => array( - 'id' => 'contact_id', - 'fields' => array( - 'street_address' => NULL, - 'city' => NULL, - 'postal_code' => NULL, - ), - ), - 'civicrm_email' => array( - 'id' => 'contact_id', - 'fields' => array('email' => NULL), - ), - 'civicrm_phone' => array( - 'id' => 'contact_id', - 'fields' => array('phone' => NULL), - ), - 'civicrm_note' => array( - 'id' => 'entity_id', - 'entity_table' => 'civicrm_contact', - 'fields' => array( - 'subject' => NULL, - 'note' => NULL, - ), - ), - 'sql' => $contactSQL, - 'final' => $final, - ); - - // get the custom data info - $this->fillCustomInfo($tables, - "( 'Contact', 'Individual', 'Organization', 'Household' )" - ); - - return $this->runQueries($queryText, $tables, $entityIDTableName, $limit); - } - - public function moveContactIDs($fromTable, $toTable, $limit) { - $sql = " -INSERT INTO {$toTable} -( id, contact_id, sort_name, display_name, table_name ) -SELECT c.id, ct.entity_id, c.sort_name, c.display_name, 'Contact' - FROM {$fromTable} ct -INNER JOIN civicrm_contact c ON ct.entity_id = c.id -{$limit} -"; - CRM_Core_DAO::executeQuery($sql); - } - - /** - * @param string $queryText - * @return int the total number of matches - */ - function fillActivityIDs($queryText, $entityIDTableName, $limit) { - $contactSQL = array(); - - $contactSQL[] = " -SELECT distinct ca.id -FROM civicrm_activity ca -INNER JOIN civicrm_activity_contact cat ON cat.activity_id = ca.id -INNER JOIN civicrm_contact c ON cat.contact_id = c.id -LEFT JOIN civicrm_email e ON cat.contact_id = e.contact_id -LEFT JOIN civicrm_option_group og ON og.name = 'activity_type' -LEFT JOIN civicrm_option_value ov ON ( ov.option_group_id = og.id ) -WHERE ( (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR c.display_name LIKE {$this->toSqlWildCard($queryText)}) OR - ( e.email LIKE {$this->toSqlWildCard($queryText)} AND - ca.activity_type_id = ov.value AND - ov.name IN ('Inbound Email', 'Email') ) ) -AND (ca.is_deleted = 0 OR ca.is_deleted IS NULL) -AND (c.is_deleted = 0 OR c.is_deleted IS NULL) -"; - - $contactSQL[] = " -SELECT et.entity_id -FROM civicrm_entity_tag et -INNER JOIN civicrm_tag t ON et.tag_id = t.id -INNER JOIN civicrm_activity ca ON et.entity_id = ca.id -WHERE et.entity_table = 'civicrm_activity' -AND et.tag_id = t.id -AND t.name LIKE {$this->toSqlWildCard($queryText)} -AND (ca.is_deleted = 0 OR ca.is_deleted IS NULL) -GROUP BY et.entity_id -"; - - $contactSQL[] = " -SELECT distinct ca.id -FROM civicrm_activity ca -WHERE (ca.subject LIKE {$this->toSqlWildCard($queryText)} OR ca.details LIKE {$this->toSqlWildCard($queryText)}) -AND (ca.is_deleted = 0 OR ca.is_deleted IS NULL) -"; - - $final = array(); - - $tables = array( - 'civicrm_activity' => array('fields' => array()), - 'sql' => $contactSQL, - 'final' => $final, - ); - - $this->fillCustomInfo($tables, "( 'Activity' )"); - return $this->runQueries($queryText, $tables, $entityIDTableName, $limit); - } - - public function moveActivityIDs($fromTable, $toTable, $limit) { - $sql = " -INSERT INTO {$toTable} -( table_name, activity_id, subject, details, contact_id, sort_name, record_type, - activity_type_id, case_id, client_id ) -SELECT 'Activity', ca.id, substr(ca.subject, 1, 50), substr(ca.details, 1, 250), - c1.id, c1.sort_name, cac.record_type_id, - ca.activity_type_id, - cca.case_id, - ccc.contact_id as client_id -FROM {$fromTable} eid -INNER JOIN civicrm_activity ca ON ca.id = eid.entity_id -INNER JOIN civicrm_activity_contact cac ON cac.activity_id = ca.id -INNER JOIN civicrm_contact c1 ON cac.contact_id = c1.id -LEFT JOIN civicrm_case_activity cca ON cca.activity_id = ca.id -LEFT JOIN civicrm_case_contact ccc ON ccc.case_id = cca.case_id -WHERE (ca.is_deleted = 0 OR ca.is_deleted IS NULL) -GROUP BY ca.id -{$limit} -"; - CRM_Core_DAO::executeQuery($sql); - } - - /** - * @param string $queryText - * @return int the total number of matches - */ - function fillCaseIDs($queryText, $entityIDTableName, $limit) { - $contactSQL = array(); - - $contactSQL[] = " -SELECT distinct cc.id -FROM civicrm_case cc -LEFT JOIN civicrm_case_contact ccc ON cc.id = ccc.case_id -LEFT JOIN civicrm_contact c ON ccc.contact_id = c.id -WHERE (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR c.display_name LIKE {$this->toSqlWildCard($queryText)}) - AND (cc.is_deleted = 0 OR cc.is_deleted IS NULL) -"; - - if (is_numeric($queryText)) { - $contactSQL[] = " -SELECT distinct cc.id -FROM civicrm_case cc -LEFT JOIN civicrm_case_contact ccc ON cc.id = ccc.case_id -LEFT JOIN civicrm_contact c ON ccc.contact_id = c.id -WHERE cc.id = {$queryText} - AND (cc.is_deleted = 0 OR cc.is_deleted IS NULL) -"; - } - - $contactSQL[] = " -SELECT et.entity_id -FROM civicrm_entity_tag et -INNER JOIN civicrm_tag t ON et.tag_id = t.id -WHERE et.entity_table = 'civicrm_case' -AND et.tag_id = t.id -AND t.name LIKE {$this->toSqlWildCard($queryText)} -GROUP BY et.entity_id -"; - - $tables = array( - 'civicrm_case' => array('fields' => array()), - 'sql' => $contactSQL, - ); - - return $this->runQueries($queryText, $tables, $entityIDTableName, $limit); - } - - public function moveCaseIDs($fromTable, $toTable, $limit) { - $sql = " -INSERT INTO {$toTable} -( table_name, contact_id, sort_name, case_id, case_start_date, case_end_date, case_is_deleted ) -SELECT 'Case', c.id, c.sort_name, cc.id, DATE(cc.start_date), DATE(cc.end_date), cc.is_deleted -FROM {$fromTable} ct -INNER JOIN civicrm_case cc ON cc.id = ct.entity_id -LEFT JOIN civicrm_case_contact ccc ON cc.id = ccc.case_id -LEFT JOIN civicrm_contact c ON ccc.contact_id = c.id -{$limit} -"; - CRM_Core_DAO::executeQuery($sql); - } - - /** - * get contribution ids in entity tables. - * - * @param string $queryText - * @return int the total number of matches - */ - function fillContributionIDs($queryText, $entityIDTableName, $limit) { - $contactSQL = array(); - $contactSQL[] = " -SELECT distinct cc.id -FROM civicrm_contribution cc -INNER JOIN civicrm_contact c ON cc.contact_id = c.id -WHERE (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR - c.display_name LIKE {$this->toSqlWildCard($queryText)}) -"; - $tables = array( - 'civicrm_contribution' => array( - 'id' => 'id', - 'fields' => array( - 'source' => NULL, - 'amount_level' => NULL, - 'trxn_Id' => NULL, - 'invoice_id' => NULL, - 'check_number' => (is_numeric($queryText)) ? 'Int' : NULL, - 'total_amount' => (is_numeric($queryText)) ? 'Int' : NULL, - ), - ), - 'sql' => $contactSQL, - 'civicrm_note' => array( - 'id' => 'entity_id', - 'entity_table' => 'civicrm_contribution', - 'fields' => array( - 'subject' => NULL, - 'note' => NULL, - ), - ), - ); - - // get the custom data info - $this->fillCustomInfo($tables, "( 'Contribution' )"); - return $this->runQueries($queryText, $tables, $entityIDTableName, $limit); - } - - public function moveContributionIDs($fromTable, $toTable, $limit) { - $sql = " -INSERT INTO {$toTable} -( table_name, contact_id, sort_name, contribution_id, financial_type, contribution_page, contribution_receive_date, - contribution_total_amount, contribution_trxn_Id, contribution_source, contribution_status, contribution_check_number ) - SELECT 'Contribution', c.id, c.sort_name, cc.id, cct.name, ccp.title, cc.receive_date, - cc.total_amount, cc.trxn_id, cc.source, contribution_status.label, cc.check_number - FROM {$fromTable} ct -INNER JOIN civicrm_contribution cc ON cc.id = ct.entity_id -LEFT JOIN civicrm_contact c ON cc.contact_id = c.id -LEFT JOIN civicrm_financial_type cct ON cct.id = cc.financial_type_id -LEFT JOIN civicrm_contribution_page ccp ON ccp.id = cc.contribution_page_id -LEFT JOIN civicrm_option_group option_group_contributionStatus ON option_group_contributionStatus.name = 'contribution_status' -LEFT JOIN civicrm_option_value contribution_status ON -( contribution_status.option_group_id = option_group_contributionStatus.id AND contribution_status.value = cc.contribution_status_id ) -{$limit} -"; - CRM_Core_DAO::executeQuery($sql); - } - - /** - * get participant ids in entity tables. - * - * @param string $queryText - * @return int the total number of matches - */ - function fillParticipantIDs($queryText, $entityIDTableName, $limit) { - $contactSQL = array(); - $contactSQL[] = " -SELECT distinct cp.id -FROM civicrm_participant cp -INNER JOIN civicrm_contact c ON cp.contact_id = c.id -WHERE (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR c.display_name LIKE {$this->toSqlWildCard($queryText)}) -"; - $tables = array( - 'civicrm_participant' => array( - 'id' => 'id', - 'fields' => array( - 'source' => NULL, - 'fee_level' => NULL, - 'fee_amount' => (is_numeric($queryText)) ? 'Int' : NULL, - ), - ), - 'sql' => $contactSQL, - 'civicrm_note' => array( - 'id' => 'entity_id', - 'entity_table' => 'civicrm_participant', - 'fields' => array( - 'subject' => NULL, - 'note' => NULL, - ), - ), - ); - - // get the custom data info - $this->fillCustomInfo($tables, "( 'Participant' )"); - return $this->runQueries($queryText, $tables, $entityIDTableName, $limit); - } - - public function moveParticipantIDs($fromTable, $toTable, $limit) { - $sql = " -INSERT INTO {$toTable} -( table_name, contact_id, sort_name, participant_id, event_title, participant_fee_level, participant_fee_amount, -participant_register_date, participant_source, participant_status, participant_role ) - SELECT 'Participant', c.id, c.sort_name, cp.id, ce.title, cp.fee_level, cp.fee_amount, cp.register_date, cp.source, - participantStatus.label, cp.role_id - FROM {$fromTable} ct -INNER JOIN civicrm_participant cp ON cp.id = ct.entity_id -LEFT JOIN civicrm_contact c ON cp.contact_id = c.id -LEFT JOIN civicrm_event ce ON ce.id = cp.event_id -LEFT JOIN civicrm_participant_status_type participantStatus ON participantStatus.id = cp.status_id -{$limit} -"; - CRM_Core_DAO::executeQuery($sql); - } - - /** - * get membership ids in entity tables. - * - * @param string $queryText - * @return int the total number of matches - */ - function fillMembershipIDs($queryText, $entityIDTableName, $limit) { - $contactSQL = array(); - $contactSQL[] = " -SELECT distinct cm.id -FROM civicrm_membership cm -INNER JOIN civicrm_contact c ON cm.contact_id = c.id -WHERE (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR c.display_name LIKE {$this->toSqlWildCard($queryText)}) -"; - $tables = array( - 'civicrm_membership' => array( - 'id' => 'id', - 'fields' => array('source' => NULL), - ), - 'sql' => $contactSQL, - ); - - // get the custom data info - $this->fillCustomInfo($tables, "( 'Membership' )"); - return $this->runQueries($queryText, $tables, $entityIDTableName, $limit); - } - - public function moveMembershipIDs($fromTable, $toTable, $limit) { - $sql = " -INSERT INTO {$toTable} -( table_name, contact_id, sort_name, membership_id, membership_type, membership_fee, membership_start_date, -membership_end_date, membership_source, membership_status ) - SELECT 'Membership', c.id, c.sort_name, cm.id, cmt.name, cc.total_amount, cm.start_date, cm.end_date, cm.source, cms.name - FROM {$fromTable} ct -INNER JOIN civicrm_membership cm ON cm.id = ct.entity_id -LEFT JOIN civicrm_contact c ON cm.contact_id = c.id -LEFT JOIN civicrm_membership_type cmt ON cmt.id = cm.membership_type_id -LEFT JOIN civicrm_membership_payment cmp ON cmp.membership_id = cm.id -LEFT JOIN civicrm_contribution cc ON cc.id = cmp.contribution_id -LEFT JOIN civicrm_membership_status cms ON cms.id = cm.status_id -{$limit} -"; - CRM_Core_DAO::executeQuery($sql); - } - /** * @param CRM_Core_Form $form */ @@ -818,23 +288,11 @@ LEFT JOIN civicrm_membership_status cms ON cms.id = cm.status_id // also add a select box to allow the search to be constrained $tables = array('' => ts('All tables')); - if (CRM_Core_Permission::check('view all contacts')) { - $tables['Contact'] = ts('Contacts'); - } - if (CRM_Core_Permission::check('view all activities')) { - $tables['Activity'] = ts('Activities'); - } - if (in_array('CiviCase', $config->enableComponents)) { - $tables['Case'] = ts('Cases'); - } - if (in_array('CiviContribute', $config->enableComponents)) { - $tables['Contribution'] = ts('Contributions'); - } - if (in_array('CiviEvent', $config->enableComponents) && CRM_Core_Permission::check('view event participants')) { - $tables['Participant'] = ts('Participants'); - } - if (in_array('CiviMember', $config->enableComponents)) { - $tables['Membership'] = ts('Memberships'); + foreach ($this->_partialQueries as $partialQuery) { + /** @var $partialQuery CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery */ + if ($partialQuery->isActive()) { + $tables[$partialQuery->getName()] = $partialQuery->getLabel(); + } } $form->add('select', 'table', ts('Tables'), $tables ); @@ -884,14 +342,11 @@ LEFT JOIN civicrm_membership_status cms ON cms.id = cm.status_id function summary() { $this->initialize(); - $summary = array( - 'Contact' => array(), - 'Activity' => array(), - 'Case' => array(), - 'Contribution' => array(), - 'Participant' => array(), - 'Membership' => array(), - ); + $summary = array(); + foreach ($this->_partialQueries as $partialQuery) { + /** @var $partialQuery CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery */ + $summary[$partialQuery->getName()] = array(); + } // now iterate through the table and add entries to the relevant section $sql = "SELECT * FROM {$this->_tableName}"; @@ -1045,4 +500,3 @@ FROM {$this->_tableName} contact_a } } } - diff --git a/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php b/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php new file mode 100644 index 0000000000..2a81bf9e16 --- /dev/null +++ b/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php @@ -0,0 +1,215 @@ +name = $name; + $this->label = $label; + } + + public function getLabel() { + return $this->label; + } + + public function getName() { + return $this->name; + } + + /** + * Execute a query and write out a page worth of matches to $detailTable. + * + * TODO: Consider removing $entityIDTableName from the function-signature. Each implementation could be + * responsible for its own temp tables. + * + * @param string $queryText a string of text to search for + * @param string $entityIDTableName a temporary table into which we can write a list of all matching IDs + * @param string $detailTable a table into which we can write details about a page worth of matches + * @param string $queryLimit overall limit (applied when building $entityIDTableName) + * @param string $detailLimit final limit (applied when building $detailTable) + * @return int number of matches + */ + public abstract function fillTempTable($queryText, $entityIDTableName, $detailTable, $queryLimit, $detailLimit); + + /** + * @return bool + */ + public function isActive() { + return TRUE; + } + + /** + * @param $tables + * @param $extends + */ + function fillCustomInfo(&$tables, $extends) { + $sql = " +SELECT cg.table_name, cf.column_name +FROM civicrm_custom_group cg +INNER JOIN civicrm_custom_field cf ON cf.custom_group_id = cg.id +WHERE cg.extends IN $extends +AND cg.is_active = 1 +AND cf.is_active = 1 +AND cf.is_searchable = 1 +AND cf.html_type IN ( 'Text', 'TextArea', 'RichTextEditor' ) +"; + + $dao = CRM_Core_DAO::executeQuery($sql); + while ($dao->fetch()) { + if (!array_key_exists($dao->table_name, $tables)) { + $tables[$dao->table_name] = array( + 'id' => 'entity_id', + 'fields' => array(), + ); + } + $tables[$dao->table_name]['fields'][$dao->column_name] = NULL; + } + } + + + /** + * @param string $queryText + * @param array $tables + * @return int the total number of matches + */ + function runQueries($queryText, &$tables, $entityIDTableName, $limit) { + $sql = "TRUNCATE {$entityIDTableName}"; + CRM_Core_DAO::executeQuery($sql); + + foreach ($tables as $tableName => $tableValues) { + if ($tableName == 'final') { + continue; + } + else { + if ($tableName == 'sql') { + foreach ($tableValues as $sqlStatement) { + $sql = " +REPLACE INTO {$entityIDTableName} ( entity_id ) +$sqlStatement +{$limit} +"; + CRM_Core_DAO::executeQuery($sql); + } + } + else { + $clauses = array(); + + foreach ($tableValues['fields'] as $fieldName => $fieldType) { + if ($fieldType == 'Int') { + if (is_numeric($queryText)) { + $clauses[] = "$fieldName = {$queryText}"; + } + } + else { + $clauses[] = "$fieldName LIKE {$this->toSqlWildCard($queryText)}"; + } + } + + if (empty($clauses)) { + continue; + } + + $whereClause = implode(' OR ', $clauses); + + //resolve conflict between entity tables. + if ($tableName == 'civicrm_note' && + $entityTable = CRM_Utils_Array::value('entity_table', $tableValues) + ) { + $whereClause .= " AND entity_table = '{$entityTable}'"; + } + + $sql = " +REPLACE INTO {$entityIDTableName} ( entity_id ) +SELECT {$tableValues['id']} +FROM $tableName +WHERE ( $whereClause ) +AND {$tableValues['id']} IS NOT NULL +GROUP BY {$tableValues['id']} +{$limit} +"; + CRM_Core_DAO::executeQuery($sql); + } + } + } + + if (isset($tables['final'])) { + foreach ($tables['final'] as $sqlStatement) { + CRM_Core_DAO::executeQuery($sqlStatement); + } + } + + $rowCount = "SELECT count(*) FROM {$entityIDTableName}"; + return CRM_Core_DAO::singleValueQuery($rowCount); + } + + /** + * Format text to include wild card characters at beginning and end + * + * @param string $text + * @return string + */ + public function toSqlWildCard($text) { + if ($text) { + $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower'; + $text = $strtolower(CRM_Core_DAO::escapeString($text)); + if (strpos($text, '%') === FALSE) { + $text = "'%{$text}%'"; + return $text; + } + else { + $text = "'{$text}'"; + return $text; + } + } + else { + $text = "'%'"; + return $text; + } + } + +} \ No newline at end of file diff --git a/CRM/Contact/Form/Search/Custom/FullText/Activity.php b/CRM/Contact/Form/Search/Custom/FullText/Activity.php new file mode 100644 index 0000000000..86ea35455f --- /dev/null +++ b/CRM/Contact/Form/Search/Custom/FullText/Activity.php @@ -0,0 +1,131 @@ +fillActivityIDs($queryText, $entityIDTableName, $queryLimit); + $this->moveActivityIDs($entityIDTableName, $toTable, $detailLimit); + return $count; + } + + /** + * @param string $queryText + * @return int the total number of matches + */ + function fillActivityIDs($queryText, $entityIDTableName, $limit) { + $contactSQL = array(); + + $contactSQL[] = " +SELECT distinct ca.id +FROM civicrm_activity ca +INNER JOIN civicrm_activity_contact cat ON cat.activity_id = ca.id +INNER JOIN civicrm_contact c ON cat.contact_id = c.id +LEFT JOIN civicrm_email e ON cat.contact_id = e.contact_id +LEFT JOIN civicrm_option_group og ON og.name = 'activity_type' +LEFT JOIN civicrm_option_value ov ON ( ov.option_group_id = og.id ) +WHERE ( (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR c.display_name LIKE {$this->toSqlWildCard($queryText)}) OR + ( e.email LIKE {$this->toSqlWildCard($queryText)} AND + ca.activity_type_id = ov.value AND + ov.name IN ('Inbound Email', 'Email') ) ) +AND (ca.is_deleted = 0 OR ca.is_deleted IS NULL) +AND (c.is_deleted = 0 OR c.is_deleted IS NULL) +"; + + $contactSQL[] = " +SELECT et.entity_id +FROM civicrm_entity_tag et +INNER JOIN civicrm_tag t ON et.tag_id = t.id +INNER JOIN civicrm_activity ca ON et.entity_id = ca.id +WHERE et.entity_table = 'civicrm_activity' +AND et.tag_id = t.id +AND t.name LIKE {$this->toSqlWildCard($queryText)} +AND (ca.is_deleted = 0 OR ca.is_deleted IS NULL) +GROUP BY et.entity_id +"; + + $contactSQL[] = " +SELECT distinct ca.id +FROM civicrm_activity ca +WHERE (ca.subject LIKE {$this->toSqlWildCard($queryText)} OR ca.details LIKE {$this->toSqlWildCard($queryText)}) +AND (ca.is_deleted = 0 OR ca.is_deleted IS NULL) +"; + + $final = array(); + + $tables = array( + 'civicrm_activity' => array('fields' => array()), + 'sql' => $contactSQL, + 'final' => $final, + ); + + $this->fillCustomInfo($tables, "( 'Activity' )"); + return $this->runQueries($queryText, $tables, $entityIDTableName, $limit); + } + + public function moveActivityIDs($fromTable, $toTable, $limit) { + $sql = " +INSERT INTO {$toTable} +( table_name, activity_id, subject, details, contact_id, sort_name, record_type, + activity_type_id, case_id, client_id ) +SELECT 'Activity', ca.id, substr(ca.subject, 1, 50), substr(ca.details, 1, 250), + c1.id, c1.sort_name, cac.record_type_id, + ca.activity_type_id, + cca.case_id, + ccc.contact_id as client_id +FROM {$fromTable} eid +INNER JOIN civicrm_activity ca ON ca.id = eid.entity_id +INNER JOIN civicrm_activity_contact cac ON cac.activity_id = ca.id +INNER JOIN civicrm_contact c1 ON cac.contact_id = c1.id +LEFT JOIN civicrm_case_activity cca ON cca.activity_id = ca.id +LEFT JOIN civicrm_case_contact ccc ON ccc.case_id = cca.case_id +WHERE (ca.is_deleted = 0 OR ca.is_deleted IS NULL) +GROUP BY ca.id +{$limit} +"; + CRM_Core_DAO::executeQuery($sql); + } + +} \ No newline at end of file diff --git a/CRM/Contact/Form/Search/Custom/FullText/Case.php b/CRM/Contact/Form/Search/Custom/FullText/Case.php new file mode 100644 index 0000000000..475593f409 --- /dev/null +++ b/CRM/Contact/Form/Search/Custom/FullText/Case.php @@ -0,0 +1,114 @@ +enableComponents); + } + + /** + * {@inheritdoc} + */ + public function fillTempTable($queryText, $entityIDTableName, $toTable, $queryLimit, $detailLimit) { + $count = $this->fillCaseIDs($queryText, $entityIDTableName, $queryLimit); + $this->moveCaseIDs($entityIDTableName, $toTable, $detailLimit); + return $count; + } + + /** + * @param string $queryText + * @return int the total number of matches + */ + function fillCaseIDs($queryText, $entityIDTableName, $limit) { + $contactSQL = array(); + + $contactSQL[] = " +SELECT distinct cc.id +FROM civicrm_case cc +LEFT JOIN civicrm_case_contact ccc ON cc.id = ccc.case_id +LEFT JOIN civicrm_contact c ON ccc.contact_id = c.id +WHERE (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR c.display_name LIKE {$this->toSqlWildCard($queryText)}) + AND (cc.is_deleted = 0 OR cc.is_deleted IS NULL) +"; + + if (is_numeric($queryText)) { + $contactSQL[] = " +SELECT distinct cc.id +FROM civicrm_case cc +LEFT JOIN civicrm_case_contact ccc ON cc.id = ccc.case_id +LEFT JOIN civicrm_contact c ON ccc.contact_id = c.id +WHERE cc.id = {$queryText} + AND (cc.is_deleted = 0 OR cc.is_deleted IS NULL) +"; + } + + $contactSQL[] = " +SELECT et.entity_id +FROM civicrm_entity_tag et +INNER JOIN civicrm_tag t ON et.tag_id = t.id +WHERE et.entity_table = 'civicrm_case' +AND et.tag_id = t.id +AND t.name LIKE {$this->toSqlWildCard($queryText)} +GROUP BY et.entity_id +"; + + $tables = array( + 'civicrm_case' => array('fields' => array()), + 'sql' => $contactSQL, + ); + + return $this->runQueries($queryText, $tables, $entityIDTableName, $limit); + } + + public function moveCaseIDs($fromTable, $toTable, $limit) { + $sql = " +INSERT INTO {$toTable} +( table_name, contact_id, sort_name, case_id, case_start_date, case_end_date, case_is_deleted ) +SELECT 'Case', c.id, c.sort_name, cc.id, DATE(cc.start_date), DATE(cc.end_date), cc.is_deleted +FROM {$fromTable} ct +INNER JOIN civicrm_case cc ON cc.id = ct.entity_id +LEFT JOIN civicrm_case_contact ccc ON cc.id = ccc.case_id +LEFT JOIN civicrm_contact c ON ccc.contact_id = c.id +{$limit} +"; + CRM_Core_DAO::executeQuery($sql); + } + +} \ No newline at end of file diff --git a/CRM/Contact/Form/Search/Custom/FullText/Contact.php b/CRM/Contact/Form/Search/Custom/FullText/Contact.php new file mode 100644 index 0000000000..176486ba21 --- /dev/null +++ b/CRM/Contact/Form/Search/Custom/FullText/Contact.php @@ -0,0 +1,133 @@ +fillContactIDs($queryText, $entityIDTableName, $queryLimit); + $this->moveContactIDs($entityIDTableName, $toTable, $detailLimit); + return $count; + } + + /** + * @param string $queryText + * @return int the total number of matches + */ + function fillContactIDs($queryText, $entityIDTableName, $limit) { + $contactSQL = array(); + $contactSQL[] = " +SELECT et.entity_id +FROM civicrm_entity_tag et +INNER JOIN civicrm_tag t ON et.tag_id = t.id +WHERE et.entity_table = 'civicrm_contact' +AND et.tag_id = t.id +AND t.name LIKE {$this->toSqlWildCard($queryText)} +GROUP BY et.entity_id +"; + + // lets delete all the deceased contacts from the entityID box + // this allows us to keep numbers in sync + // when we have acl contacts, the situation gets even more murky + $final = array(); + $final[] = "DELETE FROM {$entityIDTableName} WHERE entity_id IN (SELECT id FROM civicrm_contact WHERE is_deleted = 1)"; + + $tables = array( + 'civicrm_contact' => array( + 'id' => 'id', + 'fields' => array( + 'sort_name' => NULL, + 'nick_name' => NULL, + 'display_name' => NULL, + ), + ), + 'civicrm_address' => array( + 'id' => 'contact_id', + 'fields' => array( + 'street_address' => NULL, + 'city' => NULL, + 'postal_code' => NULL, + ), + ), + 'civicrm_email' => array( + 'id' => 'contact_id', + 'fields' => array('email' => NULL), + ), + 'civicrm_phone' => array( + 'id' => 'contact_id', + 'fields' => array('phone' => NULL), + ), + 'civicrm_note' => array( + 'id' => 'entity_id', + 'entity_table' => 'civicrm_contact', + 'fields' => array( + 'subject' => NULL, + 'note' => NULL, + ), + ), + 'sql' => $contactSQL, + 'final' => $final, + ); + + // get the custom data info + $this->fillCustomInfo($tables, + "( 'Contact', 'Individual', 'Organization', 'Household' )" + ); + + return $this->runQueries($queryText, $tables, $entityIDTableName, $limit); + } + + public function moveContactIDs($fromTable, $toTable, $limit) { + $sql = " +INSERT INTO {$toTable} +( id, contact_id, sort_name, display_name, table_name ) +SELECT c.id, ct.entity_id, c.sort_name, c.display_name, 'Contact' + FROM {$fromTable} ct +INNER JOIN civicrm_contact c ON ct.entity_id = c.id +{$limit} +"; + CRM_Core_DAO::executeQuery($sql); + } + +} \ No newline at end of file diff --git a/CRM/Contact/Form/Search/Custom/FullText/Contribution.php b/CRM/Contact/Form/Search/Custom/FullText/Contribution.php new file mode 100644 index 0000000000..7233e0928a --- /dev/null +++ b/CRM/Contact/Form/Search/Custom/FullText/Contribution.php @@ -0,0 +1,119 @@ +enableComponents) && + CRM_Core_Permission::check('access CiviContribute'); + } + + /** + * {@inheritdoc} + */ + public function fillTempTable($queryText, $entityIDTableName, $toTable, $queryLimit, $detailLimit) { + $count = $this->fillContributionIDs($queryText, $entityIDTableName, $queryLimit); + $this->moveContributionIDs($entityIDTableName, $toTable, $detailLimit); + return $count; + } + + /** + * get contribution ids in entity tables. + * + * @param string $queryText + * @return int the total number of matches + */ + function fillContributionIDs($queryText, $entityIDTableName, $limit) { + $contactSQL = array(); + $contactSQL[] = " +SELECT distinct cc.id +FROM civicrm_contribution cc +INNER JOIN civicrm_contact c ON cc.contact_id = c.id +WHERE (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR + c.display_name LIKE {$this->toSqlWildCard($queryText)}) +"; + $tables = array( + 'civicrm_contribution' => array( + 'id' => 'id', + 'fields' => array( + 'source' => NULL, + 'amount_level' => NULL, + 'trxn_Id' => NULL, + 'invoice_id' => NULL, + 'check_number' => (is_numeric($queryText)) ? 'Int' : NULL, + 'total_amount' => (is_numeric($queryText)) ? 'Int' : NULL, + ), + ), + 'sql' => $contactSQL, + 'civicrm_note' => array( + 'id' => 'entity_id', + 'entity_table' => 'civicrm_contribution', + 'fields' => array( + 'subject' => NULL, + 'note' => NULL, + ), + ), + ); + + // get the custom data info + $this->fillCustomInfo($tables, "( 'Contribution' )"); + return $this->runQueries($queryText, $tables, $entityIDTableName, $limit); + } + + public function moveContributionIDs($fromTable, $toTable, $limit) { + $sql = " +INSERT INTO {$toTable} +( table_name, contact_id, sort_name, contribution_id, financial_type, contribution_page, contribution_receive_date, + contribution_total_amount, contribution_trxn_Id, contribution_source, contribution_status, contribution_check_number ) + SELECT 'Contribution', c.id, c.sort_name, cc.id, cct.name, ccp.title, cc.receive_date, + cc.total_amount, cc.trxn_id, cc.source, contribution_status.label, cc.check_number + FROM {$fromTable} ct +INNER JOIN civicrm_contribution cc ON cc.id = ct.entity_id +LEFT JOIN civicrm_contact c ON cc.contact_id = c.id +LEFT JOIN civicrm_financial_type cct ON cct.id = cc.financial_type_id +LEFT JOIN civicrm_contribution_page ccp ON ccp.id = cc.contribution_page_id +LEFT JOIN civicrm_option_group option_group_contributionStatus ON option_group_contributionStatus.name = 'contribution_status' +LEFT JOIN civicrm_option_value contribution_status ON +( contribution_status.option_group_id = option_group_contributionStatus.id AND contribution_status.value = cc.contribution_status_id ) +{$limit} +"; + CRM_Core_DAO::executeQuery($sql); + } + +} \ No newline at end of file diff --git a/CRM/Contact/Form/Search/Custom/FullText/Membership.php b/CRM/Contact/Form/Search/Custom/FullText/Membership.php new file mode 100644 index 0000000000..4d289e39e2 --- /dev/null +++ b/CRM/Contact/Form/Search/Custom/FullText/Membership.php @@ -0,0 +1,101 @@ +enableComponents) && + CRM_Core_Permission::check('access CiviMember'); + } + + /** + * {@inheritdoc} + */ + public function fillTempTable($queryText, $entityIDTableName, $toTable, $queryLimit, $detailLimit) { + $count = $this->fillMembershipIDs($queryText, $entityIDTableName, $queryLimit); + $this->moveMembershipIDs($entityIDTableName, $toTable, $detailLimit); + return $count; + } + + /** + * get membership ids in entity tables. + * + * @param string $queryText + * @return int the total number of matches + */ + function fillMembershipIDs($queryText, $entityIDTableName, $limit) { + $contactSQL = array(); + $contactSQL[] = " +SELECT distinct cm.id +FROM civicrm_membership cm +INNER JOIN civicrm_contact c ON cm.contact_id = c.id +WHERE (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR c.display_name LIKE {$this->toSqlWildCard($queryText)}) +"; + $tables = array( + 'civicrm_membership' => array( + 'id' => 'id', + 'fields' => array('source' => NULL), + ), + 'sql' => $contactSQL, + ); + + // get the custom data info + $this->fillCustomInfo($tables, "( 'Membership' )"); + return $this->runQueries($queryText, $tables, $entityIDTableName, $limit); + } + + public function moveMembershipIDs($fromTable, $toTable, $limit) { + $sql = " +INSERT INTO {$toTable} +( table_name, contact_id, sort_name, membership_id, membership_type, membership_fee, membership_start_date, +membership_end_date, membership_source, membership_status ) + SELECT 'Membership', c.id, c.sort_name, cm.id, cmt.name, cc.total_amount, cm.start_date, cm.end_date, cm.source, cms.name + FROM {$fromTable} ct +INNER JOIN civicrm_membership cm ON cm.id = ct.entity_id +LEFT JOIN civicrm_contact c ON cm.contact_id = c.id +LEFT JOIN civicrm_membership_type cmt ON cmt.id = cm.membership_type_id +LEFT JOIN civicrm_membership_payment cmp ON cmp.membership_id = cm.id +LEFT JOIN civicrm_contribution cc ON cc.id = cmp.contribution_id +LEFT JOIN civicrm_membership_status cms ON cms.id = cm.status_id +{$limit} +"; + CRM_Core_DAO::executeQuery($sql); + } + +} \ No newline at end of file diff --git a/CRM/Contact/Form/Search/Custom/FullText/Participant.php b/CRM/Contact/Form/Search/Custom/FullText/Participant.php new file mode 100644 index 0000000000..2ed343bf62 --- /dev/null +++ b/CRM/Contact/Form/Search/Custom/FullText/Participant.php @@ -0,0 +1,112 @@ +enableComponents) && + CRM_Core_Permission::check('view event participants'); + } + + /** + * {@inheritdoc} + */ + public function fillTempTable($queryText, $entityIDTableName, $toTable, $queryLimit, $detailLimit) { + $count = $this->fillParticipantIDs($queryText, $entityIDTableName, $queryLimit); + $this->moveParticipantIDs($entityIDTableName, $toTable, $detailLimit); + return $count; + } + + /** + * get participant ids in entity tables. + * + * @param string $queryText + * @return int the total number of matches + */ + function fillParticipantIDs($queryText, $entityIDTableName, $limit) { + $contactSQL = array(); + $contactSQL[] = " +SELECT distinct cp.id +FROM civicrm_participant cp +INNER JOIN civicrm_contact c ON cp.contact_id = c.id +WHERE (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR c.display_name LIKE {$this->toSqlWildCard($queryText)}) +"; + $tables = array( + 'civicrm_participant' => array( + 'id' => 'id', + 'fields' => array( + 'source' => NULL, + 'fee_level' => NULL, + 'fee_amount' => (is_numeric($queryText)) ? 'Int' : NULL, + ), + ), + 'sql' => $contactSQL, + 'civicrm_note' => array( + 'id' => 'entity_id', + 'entity_table' => 'civicrm_participant', + 'fields' => array( + 'subject' => NULL, + 'note' => NULL, + ), + ), + ); + + // get the custom data info + $this->fillCustomInfo($tables, "( 'Participant' )"); + return $this->runQueries($queryText, $tables, $entityIDTableName, $limit); + } + + public function moveParticipantIDs($fromTable, $toTable, $limit) { + $sql = " +INSERT INTO {$toTable} +( table_name, contact_id, sort_name, participant_id, event_title, participant_fee_level, participant_fee_amount, +participant_register_date, participant_source, participant_status, participant_role ) + SELECT 'Participant', c.id, c.sort_name, cp.id, ce.title, cp.fee_level, cp.fee_amount, cp.register_date, cp.source, + participantStatus.label, cp.role_id + FROM {$fromTable} ct +INNER JOIN civicrm_participant cp ON cp.id = ct.entity_id +LEFT JOIN civicrm_contact c ON cp.contact_id = c.id +LEFT JOIN civicrm_event ce ON ce.id = cp.event_id +LEFT JOIN civicrm_participant_status_type participantStatus ON participantStatus.id = cp.status_id +{$limit} +"; + CRM_Core_DAO::executeQuery($sql); + } + +} \ No newline at end of file -- 2.25.1