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