Commit | Line | Data |
---|---|---|
4f5de903 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
39de6fd5 | 4 | | CiviCRM version 4.6 | |
4f5de903 TO |
5 | +--------------------------------------------------------------------+ |
6 | | Copyright CiviCRM LLC (c) 2004-2014 | | |
7 | +--------------------------------------------------------------------+ | |
8 | | This file is a part of CiviCRM. | | |
9 | | | | |
10 | | CiviCRM is free software; you can copy, modify, and distribute it | | |
11 | | under the terms of the GNU Affero General Public License | | |
12 | | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. | | |
13 | | | | |
14 | | CiviCRM is distributed in the hope that it will be useful, but | | |
15 | | WITHOUT ANY WARRANTY; without even the implied warranty of | | |
16 | | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. | | |
17 | | See the GNU Affero General Public License for more details. | | |
18 | | | | |
19 | | You should have received a copy of the GNU Affero General Public | | |
20 | | License and the CiviCRM Licensing Exception along | | |
21 | | with this program; if not, contact CiviCRM LLC | | |
22 | | at info[AT]civicrm[DOT]org. If you have questions about the | | |
23 | | GNU Affero General Public License or the licensing of CiviCRM, | | |
24 | | see the CiviCRM license FAQ at http://civicrm.org/licensing | | |
25 | +--------------------------------------------------------------------+ | |
26 | */ | |
27 | ||
28 | /** | |
29 | * | |
30 | * @package CRM | |
31 | * @copyright CiviCRM LLC (c) 2004-2014 | |
32 | * $Id$ | |
33 | * | |
34 | */ | |
35 | abstract class CRM_Contact_Form_Search_Custom_FullText_AbstractPartialQuery { | |
36 | ||
37 | /** | |
38 | * @var string | |
39 | */ | |
40 | protected $name; | |
41 | ||
42 | /** | |
43 | * @var string | |
44 | */ | |
45 | protected $label; | |
46 | ||
47 | /** | |
48 | * @param string $name | |
49 | * @param string $label | |
50 | */ | |
51 | public function __construct($name, $label) { | |
52 | $this->name = $name; | |
53 | $this->label = $label; | |
54 | } | |
55 | ||
56 | public function getLabel() { | |
57 | return $this->label; | |
58 | } | |
59 | ||
60 | public function getName() { | |
61 | return $this->name; | |
62 | } | |
63 | ||
64 | /** | |
65 | * Execute a query and write out a page worth of matches to $detailTable. | |
66 | * | |
67 | * TODO: Consider removing $entityIDTableName from the function-signature. Each implementation could be | |
68 | * responsible for its own temp tables. | |
69 | * | |
7296606d TO |
70 | * TODO: Understand why $queryLimit and $detailLimit are different |
71 | * | |
77c5b619 TO |
72 | * @param string $queryText |
73 | * A string of text to search for. | |
74 | * @param string $entityIDTableName | |
75 | * A temporary table into which we can write a list of all matching IDs. | |
76 | * @param string $detailTable | |
77 | * A table into which we can write details about a page worth of matches. | |
7296606d TO |
78 | * @param array|NULL $queryLimit overall limit (applied when building $entityIDTableName) |
79 | * NULL if no limit; or array(0 => $limit, 1 => $offset) | |
80 | * @param array|NULL $detailLimit final limit (applied when building $detailTable) | |
81 | * NULL if no limit; or array(0 => $limit, 1 => $offset) | |
a6c01b45 CW |
82 | * @return array |
83 | * keys: match-descriptor | |
90873dba | 84 | * - count: int |
4f5de903 TO |
85 | */ |
86 | public abstract function fillTempTable($queryText, $entityIDTableName, $detailTable, $queryLimit, $detailLimit); | |
87 | ||
88 | /** | |
89 | * @return bool | |
90 | */ | |
91 | public function isActive() { | |
92 | return TRUE; | |
93 | } | |
94 | ||
95 | /** | |
96 | * @param $tables | |
97 | * @param $extends | |
98 | */ | |
00be9182 | 99 | public function fillCustomInfo(&$tables, $extends) { |
4f5de903 TO |
100 | $sql = " |
101 | SELECT cg.table_name, cf.column_name | |
102 | FROM civicrm_custom_group cg | |
103 | INNER JOIN civicrm_custom_field cf ON cf.custom_group_id = cg.id | |
104 | WHERE cg.extends IN $extends | |
105 | AND cg.is_active = 1 | |
106 | AND cf.is_active = 1 | |
107 | AND cf.is_searchable = 1 | |
108 | AND cf.html_type IN ( 'Text', 'TextArea', 'RichTextEditor' ) | |
109 | "; | |
110 | ||
111 | $dao = CRM_Core_DAO::executeQuery($sql); | |
112 | while ($dao->fetch()) { | |
113 | if (!array_key_exists($dao->table_name, $tables)) { | |
114 | $tables[$dao->table_name] = array( | |
115 | 'id' => 'entity_id', | |
116 | 'fields' => array(), | |
117 | ); | |
118 | } | |
119 | $tables[$dao->table_name]['fields'][$dao->column_name] = NULL; | |
120 | } | |
121 | } | |
122 | ||
123 | ||
124 | /** | |
125 | * @param string $queryText | |
77c5b619 TO |
126 | * @param array $tables |
127 | * A list of places to query. Keys may be:. | |
90873dba TO |
128 | * - sql: an array of SQL queries to execute |
129 | * - final: an array of SQL queries to execute at the end | |
130 | * - *: All other keys are treated as table names | |
a6c01b45 CW |
131 | * @return array |
132 | * keys: match-descriptor | |
90873dba | 133 | * - count: int |
cac9c01d | 134 | * - files: NULL | array |
4f5de903 | 135 | */ |
00be9182 | 136 | public function runQueries($queryText, &$tables, $entityIDTableName, $limit) { |
4f5de903 TO |
137 | $sql = "TRUNCATE {$entityIDTableName}"; |
138 | CRM_Core_DAO::executeQuery($sql); | |
139 | ||
cac9c01d TO |
140 | $files = NULL; |
141 | ||
4f5de903 TO |
142 | foreach ($tables as $tableName => $tableValues) { |
143 | if ($tableName == 'final') { | |
144 | continue; | |
145 | } | |
146 | else { | |
147 | if ($tableName == 'sql') { | |
148 | foreach ($tableValues as $sqlStatement) { | |
149 | $sql = " | |
150 | REPLACE INTO {$entityIDTableName} ( entity_id ) | |
151 | $sqlStatement | |
7296606d | 152 | {$this->toLimit($limit)} |
4f5de903 TO |
153 | "; |
154 | CRM_Core_DAO::executeQuery($sql); | |
155 | } | |
156 | } | |
4c9b6178 | 157 | elseif ($tableName == 'file') { |
cac9c01d TO |
158 | $searcher = CRM_Core_BAO_File::getSearchService(); |
159 | if (!($searcher && CRM_Core_Permission::check('access uploaded files'))) { | |
160 | continue; | |
161 | } | |
162 | ||
163 | $query = $tableValues + array( | |
ea74069c TO |
164 | 'text' => CRM_Utils_QueryFormatter::singleton() |
165 | ->format($queryText, CRM_Utils_QueryFormatter::LANG_SOLR), | |
cac9c01d TO |
166 | ); |
167 | list($intLimit, $intOffset) = $this->parseLimitOffset($limit); | |
168 | $files = $searcher->search($query, $intLimit, $intOffset); | |
169 | $matches = array(); | |
170 | foreach ($files as $file) { | |
171 | $matches[] = array('entity_id' => $file['xparent_id']); | |
172 | } | |
173 | if ($matches) { | |
174 | $insertSql = CRM_Utils_SQL_Insert::into($entityIDTableName)->usingReplace()->rows($matches)->toSQL(); | |
175 | CRM_Core_DAO::executeQuery($insertSql); | |
176 | } | |
177 | } | |
4f5de903 | 178 | else { |
a04af6db TO |
179 | $fullTextFields = array(); // array (string $sqlColumnName) |
180 | $clauses = array(); // array (string $sqlExpression) | |
4f5de903 TO |
181 | |
182 | foreach ($tableValues['fields'] as $fieldName => $fieldType) { | |
183 | if ($fieldType == 'Int') { | |
184 | if (is_numeric($queryText)) { | |
185 | $clauses[] = "$fieldName = {$queryText}"; | |
186 | } | |
187 | } | |
188 | else { | |
a04af6db | 189 | $fullTextFields[] = $fieldName; |
4f5de903 TO |
190 | } |
191 | } | |
192 | ||
a04af6db TO |
193 | if (!empty($fullTextFields)) { |
194 | $clauses[] = $this->matchText($tableName, $fullTextFields, $queryText); | |
195 | } | |
196 | ||
4f5de903 TO |
197 | if (empty($clauses)) { |
198 | continue; | |
199 | } | |
200 | ||
201 | $whereClause = implode(' OR ', $clauses); | |
202 | ||
203 | //resolve conflict between entity tables. | |
204 | if ($tableName == 'civicrm_note' && | |
205 | $entityTable = CRM_Utils_Array::value('entity_table', $tableValues) | |
206 | ) { | |
207 | $whereClause .= " AND entity_table = '{$entityTable}'"; | |
208 | } | |
209 | ||
210 | $sql = " | |
211 | REPLACE INTO {$entityIDTableName} ( entity_id ) | |
212 | SELECT {$tableValues['id']} | |
213 | FROM $tableName | |
214 | WHERE ( $whereClause ) | |
215 | AND {$tableValues['id']} IS NOT NULL | |
216 | GROUP BY {$tableValues['id']} | |
7296606d | 217 | {$this->toLimit($limit)} |
4f5de903 TO |
218 | "; |
219 | CRM_Core_DAO::executeQuery($sql); | |
220 | } | |
221 | } | |
222 | } | |
223 | ||
224 | if (isset($tables['final'])) { | |
225 | foreach ($tables['final'] as $sqlStatement) { | |
226 | CRM_Core_DAO::executeQuery($sqlStatement); | |
227 | } | |
228 | } | |
229 | ||
90873dba | 230 | return array( |
cac9c01d TO |
231 | 'count' => CRM_Core_DAO::singleValueQuery("SELECT count(*) FROM {$entityIDTableName}"), |
232 | 'files' => $files, | |
90873dba | 233 | ); |
4f5de903 TO |
234 | } |
235 | ||
a04af6db TO |
236 | /** |
237 | * Create a SQL expression for matching against a list of | |
238 | * text columns. | |
239 | * | |
77c5b619 TO |
240 | * @param string $table |
241 | * Eg "civicrm_note" or "civicrm_note mynote". | |
a04af6db TO |
242 | * @param array|string $fullTextFields list of field names |
243 | * @param string $queryText | |
a6c01b45 CW |
244 | * @return string |
245 | * SQL, eg "MATCH (col1) AGAINST (queryText)" or "col1 LIKE '%queryText%'" | |
a04af6db TO |
246 | */ |
247 | public function matchText($table, $fullTextFields, $queryText) { | |
ea74069c TO |
248 | $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower'; |
249 | ||
a04af6db TO |
250 | if (strpos($table, ' ') === FALSE) { |
251 | $tableName = $tableAlias = $table; | |
ea74069c TO |
252 | } |
253 | else { | |
a04af6db TO |
254 | list ($tableName, $tableAlias) = explode(' ', $table); |
255 | } | |
256 | if (is_scalar($fullTextFields)) { | |
257 | $fullTextFields = array($fullTextFields); | |
258 | } | |
259 | ||
260 | $clauses = array(); | |
261 | if (CRM_Core_InnoDBIndexer::singleton()->hasDeclaredIndex($tableName, $fullTextFields)) { | |
ea74069c | 262 | $formattedQuery = CRM_Utils_QueryFormatter::singleton() |
3196b7a5 | 263 | ->format($queryText, CRM_Utils_QueryFormatter::LANG_SQL_FTSBOOL); |
a04af6db TO |
264 | |
265 | $prefixedFieldNames = array(); | |
266 | foreach ($fullTextFields as $fieldName) { | |
267 | $prefixedFieldNames[] = "$tableAlias.$fieldName"; | |
268 | } | |
269 | ||
3196b7a5 | 270 | $clauses[] = sprintf("MATCH (%s) AGAINST ('%s' IN BOOLEAN MODE)", |
a04af6db | 271 | implode(',', $prefixedFieldNames), |
ea74069c | 272 | $strtolower(CRM_Core_DAO::escapeString($formattedQuery)) |
a04af6db TO |
273 | ); |
274 | } | |
275 | else { | |
276 | //CRM_Core_Session::setStatus(ts('Cannot use FTS for %1 (%2)', array( | |
277 | // 1 => $table, | |
278 | // 2 => implode(', ', $fullTextFields), | |
279 | //))); | |
ea74069c TO |
280 | |
281 | $formattedQuery = CRM_Utils_QueryFormatter::singleton() | |
282 | ->format($queryText, CRM_Utils_QueryFormatter::LANG_SQL_LIKE); | |
283 | $escapedText = $strtolower(CRM_Core_DAO::escapeString($formattedQuery)); | |
a04af6db | 284 | foreach ($fullTextFields as $fieldName) { |
ea74069c | 285 | $clauses[] = "$tableAlias.$fieldName LIKE '{$escapedText}'"; |
a04af6db TO |
286 | } |
287 | } | |
288 | return implode(' OR ', $clauses); | |
289 | } | |
290 | ||
cac9c01d TO |
291 | /** |
292 | * For any records in $toTable that originated with this query, | |
293 | * append file information. | |
294 | * | |
295 | * @param string $toTable | |
296 | * @param string $parentIdColumn | |
77c5b619 TO |
297 | * @param array $files |
298 | * See return format of CRM_Core_FileSearchInterface::search. | |
cac9c01d TO |
299 | */ |
300 | public function moveFileIDs($toTable, $parentIdColumn, $files) { | |
301 | if (empty($files)) { | |
302 | return; | |
303 | } | |
304 | ||
305 | $filesIndex = CRM_Utils_Array::index(array('xparent_id', 'file_id'), $files); | |
306 | // ex: $filesIndex[$xparent_id][$file_id] = array(...the file record...); | |
307 | ||
308 | $dao = CRM_Core_DAO::executeQuery(" | |
309 | SELECT distinct {$parentIdColumn} | |
310 | FROM {$toTable} | |
311 | WHERE table_name = %1 | |
312 | ", array( | |
313 | 1 => array($this->getName(), 'String'), | |
314 | )); | |
315 | while ($dao->fetch()) { | |
316 | if (empty($filesIndex[$dao->{$parentIdColumn}])) { | |
317 | continue; | |
318 | } | |
319 | ||
320 | CRM_Core_DAO::executeQuery("UPDATE {$toTable} | |
321 | SET file_ids = %1 | |
322 | WHERE table_name = %2 AND {$parentIdColumn} = %3 | |
323 | ", array( | |
324 | 1 => array(implode(',', array_keys($filesIndex[$dao->{$parentIdColumn}])), 'String'), | |
325 | 2 => array($this->getName(), 'String'), | |
326 | 3 => array($dao->{$parentIdColumn}, 'Int'), | |
327 | )); | |
328 | } | |
329 | } | |
330 | ||
7296606d TO |
331 | /** |
332 | * @param int|array $limit | |
a6c01b45 CW |
333 | * @return string |
334 | * SQL | |
7296606d TO |
335 | * @see CRM_Contact_Form_Search_Custom_FullText::toLimit |
336 | */ | |
337 | public function toLimit($limit) { | |
338 | if (is_array($limit)) { | |
339 | list ($limit, $offset) = $limit; | |
340 | } | |
341 | if (empty($limit)) { | |
342 | return ''; | |
343 | } | |
344 | $result = "LIMIT {$limit}"; | |
345 | if ($offset) { | |
346 | $result .= " OFFSET {$offset}"; | |
347 | } | |
348 | return $result; | |
349 | } | |
350 | ||
cac9c01d TO |
351 | /** |
352 | * @param array|int $limit | |
a6c01b45 CW |
353 | * @return array |
354 | * (0 => $limit, 1 => $offset) | |
cac9c01d TO |
355 | */ |
356 | public function parseLimitOffset($limit) { | |
357 | if (is_scalar($limit)) { | |
358 | $intLimit = $limit; | |
359 | } | |
360 | else { | |
361 | list ($intLimit, $intOffset) = $limit; | |
362 | } | |
363 | if (!$intOffset) { | |
364 | $intOffset = 0; | |
365 | } | |
366 | return array($intLimit, $intOffset); | |
367 | } | |
368 | ||
ef10e0b5 | 369 | } |