/**
* @param string $queryText
* @param array $tables a list of places to query. Keys may be:
* - sql: an array of SQL queries to execute
* - final: an array of SQL queries to execute at the end
* - *: All other keys are treated as table names
* @return array keys: match-descriptor
* - count: int
* - files: NULL | array
*/
function runQueries($queryText, &$tables, $entityIDTableName, $limit)
{
$sql = "TRUNCATE {$entityIDTableName}";
CRM_Core_DAO::executeQuery($sql);
$files = NULL;
foreach ($tables as $tableName => $tableValues) {
if ($tableName == 'final') {
continue;
} else {
if ($tableName == 'sql') {
foreach ($tableValues as $sqlStatement) {
$sql = "\nREPLACE INTO {$entityIDTableName} ( entity_id )\n{$sqlStatement}\n{$this->toLimit($limit)}\n";
CRM_Core_DAO::executeQuery($sql);
}
} else {
if ($tableName == 'file') {
$searcher = CRM_Core_BAO_File::getSearchService();
if (!($searcher && CRM_Core_Permission::check('access uploaded files'))) {
continue;
}
$query = $tableValues + array('text' => CRM_Utils_QueryFormatter::singleton()->format($queryText, CRM_Utils_QueryFormatter::LANG_SOLR));
list($intLimit, $intOffset) = $this->parseLimitOffset($limit);
$files = $searcher->search($query, $intLimit, $intOffset);
$matches = array();
foreach ($files as $file) {
$matches[] = array('entity_id' => $file['xparent_id']);
}
if ($matches) {
$insertSql = CRM_Utils_SQL_Insert::into($entityIDTableName)->usingReplace()->rows($matches)->toSQL();
CRM_Core_DAO::executeQuery($insertSql);
}
} else {
$fullTextFields = array();
// array (string $sqlColumnName)
$clauses = array();
// array (string $sqlExpression)
foreach ($tableValues['fields'] as $fieldName => $fieldType) {
if ($fieldType == 'Int') {
if (is_numeric($queryText)) {
$clauses[] = "{$fieldName} = {$queryText}";
}
} else {
$fullTextFields[] = $fieldName;
}
}
if (!empty($fullTextFields)) {
$clauses[] = $this->matchText($tableName, $fullTextFields, $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 = "\nREPLACE INTO {$entityIDTableName} ( entity_id )\nSELECT {$tableValues['id']}\nFROM {$tableName}\nWHERE ( {$whereClause} )\nAND {$tableValues['id']} IS NOT NULL\nGROUP BY {$tableValues['id']}\n{$this->toLimit($limit)}\n";
CRM_Core_DAO::executeQuery($sql);
}
}
}
}
if (isset($tables['final'])) {
foreach ($tables['final'] as $sqlStatement) {
CRM_Core_DAO::executeQuery($sqlStatement);
}
}
return array('count' => CRM_Core_DAO::singleValueQuery("SELECT count(*) FROM {$entityIDTableName}"), 'files' => $files);
}
/**
* @param CRM_Core_Form $form
*/
public function buildForm(&$form)
{
$config = CRM_Core_Config::singleton();
$form->applyFilter('__ALL__', 'trim');
$form->add('text', 'text', ts('Find'), TRUE);
// also add a select box to allow the search to be constrained
$tables = array('' => ts('All tables'));
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);
$form->assign('csID', $form->get('csid'));
// also add the limit constant
$form->assign('limit', self::LIMIT);
// set form defaults
if (!empty($form->_formValues)) {
$defaults = array();
if (isset($form->_formValues['text'])) {
$defaults['text'] = $form->_formValues['text'];
}
if (isset($form->_formValues['table'])) {
$defaults['table'] = $form->_formValues['table'];
$form->assign('table', $form->_formValues['table']);
}
$form->setDefaults($defaults);
}
/**
* You can define a custom title for the search form
*/
$this->setTitle(ts('Full-text Search'));
$searchService = CRM_Core_BAO_File::getSearchService();
$form->assign('allowFileSearch', !empty($searchService) && CRM_Core_Permission::check('access uploaded files'));
}
