[TASK] Use LOWER instead of LCASE
[Packages/TYPO3.CMS.git] / typo3 / sysext / backend / Classes / Search / LiveSearch / LiveSearch.php
1 <?php
2 namespace TYPO3\CMS\Backend\Search\LiveSearch;
3
4 /**
5 * This file is part of the TYPO3 CMS project.
6 *
7 * It is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU General Public License, either version 2
9 * of the License, or any later version.
10 *
11 * For the full copyright and license information, please read the
12 * LICENSE.txt file that was distributed with this source code.
13 *
14 * The TYPO3 project - inspiring people to share!
15 */
16
17 use TYPO3\CMS\Backend\Utility\BackendUtility;
18 use TYPO3\CMS\Backend\Utility\IconUtility;
19 use TYPO3\CMS\Core\Utility\GeneralUtility;
20 use TYPO3\CMS\Core\Utility\MathUtility;
21
22 /**
23 * Class for handling backend live search.
24 *
25 * @author Michael Klapper <michael.klapper@aoemedia.de>
26 * @author Jeff Segars <jeff@webempoweredchurch.org>
27 */
28 class LiveSearch {
29
30 /**
31 * @var string
32 */
33 const PAGE_JUMP_TABLE = 'pages';
34 /**
35 * @var integer
36 */
37 const RECURSIVE_PAGE_LEVEL = 99;
38 /**
39 * @var integer
40 */
41 const GROUP_TITLE_MAX_LENGTH = 15;
42 /**
43 * @var integer
44 */
45 const RECORD_TITLE_MAX_LENGTH = 28;
46 /**
47 * @var string
48 */
49 private $queryString = '';
50
51 /**
52 * @var integer
53 */
54 private $startCount = 0;
55
56 /**
57 * @var integer
58 */
59 private $limitCount = 5;
60
61 /**
62 * @var string
63 */
64 protected $userPermissions = '';
65
66 /**
67 * @var \TYPO3\CMS\Backend\Search\LiveSearch\QueryParser
68 */
69 protected $queryParser = NULL;
70
71 /**
72 * Initialize access settings
73 */
74 public function __construct() {
75 $this->userPermissions = $GLOBALS['BE_USER']->getPagePermsClause(1);
76 $this->queryParser = GeneralUtility::makeInstance('TYPO3\\CMS\\Backend\\Search\\LiveSearch\\QueryParser');
77 }
78
79 /**
80 * Find records from database based on the given $searchQuery.
81 *
82 * @param string $searchQuery
83 * @return string Edit link to an page record if exists. Otherwise an empty string will returned
84 */
85 public function findPage($searchQuery) {
86 $link = '';
87 $pageId = $this->queryParser->getId($searchQuery);
88 $pageRecord = $this->findPageById($pageId);
89 if (!empty($pageRecord)) {
90 $link = $this->getEditLink(self::PAGE_JUMP_TABLE, $this->findPageById($pageId));
91 }
92 return $link;
93 }
94
95 /**
96 * Find records from database based on the given $searchQuery.
97 *
98 * @param string $searchQuery
99 * @return array Result list of database search.
100 */
101 public function find($searchQuery) {
102 $recordArray = array();
103 $pageList = array();
104 $mounts = $GLOBALS['BE_USER']->returnWebmounts();
105 foreach ($mounts as $pageId) {
106 $pageList[] = $this->getAvailablePageIds($pageId, self::RECURSIVE_PAGE_LEVEL);
107 }
108 $pageIdList = implode(',', array_unique(explode(',', implode(',', $pageList))));
109 unset($pageList);
110 $limit = $this->startCount . ',' . $this->limitCount;
111 if ($this->queryParser->isValidCommand($searchQuery)) {
112 $this->setQueryString($this->queryParser->getSearchQueryValue($searchQuery));
113 $tableName = $this->queryParser->getTableNameFromCommand($searchQuery);
114 if ($tableName) {
115 $recordArray[] = $this->findByTable($tableName, $pageIdList, $limit);
116 }
117 } else {
118 $this->setQueryString($searchQuery);
119 $recordArray = $this->findByGlobalTableList($pageIdList);
120 }
121 return $recordArray;
122 }
123
124 /**
125 * Retrieve the page record from given $id.
126 *
127 * @param integer $id
128 * @return array
129 */
130 protected function findPageById($id) {
131 $pageRecord = array();
132 $row = BackendUtility::getRecord(self::PAGE_JUMP_TABLE, $id);
133 if (is_array($row)) {
134 $pageRecord = $row;
135 }
136 return $pageRecord;
137 }
138
139 /**
140 * Find records from all registered TCA table & column values.
141 *
142 * @param string $pageIdList Comma separated list of page IDs
143 * @return array Records found in the database matching the searchQuery
144 */
145 protected function findByGlobalTableList($pageIdList) {
146 $limit = $this->limitCount;
147 $getRecordArray = array();
148 foreach ($GLOBALS['TCA'] as $tableName => $value) {
149 $recordArray = $this->findByTable($tableName, $pageIdList, '0,' . $limit);
150 $recordCount = count($recordArray);
151 if ($recordCount) {
152 $limit = $limit - $recordCount;
153 $getRecordArray[] = $recordArray;
154 if ($limit <= 0) {
155 break;
156 }
157 }
158 }
159 return $getRecordArray;
160 }
161
162 /**
163 * Find records by given table name.
164 *
165 * @param string $tableName Database table name
166 * @param string $pageIdList Comma separated list of page IDs
167 * @param string $limit MySql Limit notation
168 * @return array Records found in the database matching the searchQuery
169 * @see getRecordArray()
170 * @see makeOrderByTable()
171 * @see makeQuerySearchByTable()
172 * @see extractSearchableFieldsFromTable()
173 */
174 protected function findByTable($tableName, $pageIdList, $limit) {
175 $fieldsToSearchWithin = $this->extractSearchableFieldsFromTable($tableName);
176 $getRecordArray = array();
177 if (count($fieldsToSearchWithin) > 0) {
178 $pageBasedPermission = $tableName == 'pages' && $this->userPermissions ? $this->userPermissions : '1=1 ';
179 $where = 'pid IN (' . $pageIdList . ') AND ' . $pageBasedPermission . $this->makeQuerySearchByTable($tableName, $fieldsToSearchWithin);
180 $getRecordArray = $this->getRecordArray($tableName, $where, $this->makeOrderByTable($tableName), $limit);
181 }
182 return $getRecordArray;
183 }
184
185 /**
186 * Process the Database operation to get the search result.
187 *
188 * @param string $tableName Database table name
189 * @param string $where
190 * @param string $orderBy
191 * @param string $limit MySql Limit notation
192 * @return array
193 * @see \TYPO3\CMS\Backend\Utility\IconUtility::getSpriteIconForRecord()
194 * @see getTitleFromCurrentRow()
195 * @see getEditLink()
196 */
197 protected function getRecordArray($tableName, $where, $orderBy, $limit) {
198 $collect = array();
199 $isFirst = TRUE;
200 $queryParts = array(
201 'SELECT' => '*',
202 'FROM' => $tableName,
203 'WHERE' => $where,
204 'ORDERBY' => $orderBy,
205 'LIMIT' => $limit
206 );
207 $result = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
208 $dbCount = $GLOBALS['TYPO3_DB']->sql_num_rows($result);
209 while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($result)) {
210 $collect[] = array(
211 'id' => $tableName . ':' . $row['uid'],
212 'pageId' => $tableName === 'pages' ? $row['uid'] : $row['pid'],
213 'recordTitle' => $isFirst ? $this->getRecordTitlePrep($this->getTitleOfCurrentRecordType($tableName), self::GROUP_TITLE_MAX_LENGTH) : '',
214 'iconHTML' => IconUtility::getSpriteIconForRecord($tableName, $row, array('title' => 'id=' . $row['uid'] . ', pid=' . $row['pid'])),
215 'title' => $this->getRecordTitlePrep(BackendUtility::getRecordTitle($tableName, $row), self::RECORD_TITLE_MAX_LENGTH),
216 'editLink' => $this->getEditLink($tableName, $row)
217 );
218 $isFirst = FALSE;
219 }
220 $GLOBALS['TYPO3_DB']->sql_free_result($result);
221 return $collect;
222 }
223
224 /**
225 * Build a backend edit link based on given record.
226 *
227 * @param string $tableName Record table name
228 * @param array $row Current record row from database.
229 * @return string Link to open an edit window for record.
230 * @see \TYPO3\CMS\Backend\Utility\BackendUtility::readPageAccess()
231 */
232 protected function getEditLink($tableName, $row) {
233 $pageInfo = BackendUtility::readPageAccess($row['pid'], $this->userPermissions);
234 $calcPerms = $GLOBALS['BE_USER']->calcPerms($pageInfo);
235 $editLink = '';
236 if ($tableName == 'pages') {
237 $localCalcPerms = $GLOBALS['BE_USER']->calcPerms(BackendUtility::getRecord('pages', $row['uid']));
238 $permsEdit = $localCalcPerms & 2;
239 } else {
240 $permsEdit = $calcPerms & 16;
241 }
242 // "Edit" link: ( Only if permissions to edit the page-record of the content of the parent page ($this->id)
243 // @todo Is there an existing function to generate this link?
244 if ($permsEdit) {
245 $returnUrl = BackendUtility::getModuleUrl('web_list', array('id' => $row['pid']));
246 $editLink = 'alt_doc.php?' . '&edit[' . $tableName . '][' . $row['uid'] . ']=edit&returnUrl=' . rawurlencode($returnUrl);
247 }
248 return $editLink;
249 }
250
251 /**
252 * Retrieve the record name
253 *
254 * @param string $tableName Record table name
255 * @return string
256 */
257 protected function getTitleOfCurrentRecordType($tableName) {
258 return $GLOBALS['LANG']->sL($GLOBALS['TCA'][$tableName]['ctrl']['title']);
259 }
260
261 /**
262 * Crops a title string to a limited length and if it really was cropped,
263 * wrap it in a <span title="...">|</span>,
264 * which offers a tooltip with the original title when moving mouse over it.
265 *
266 * @param string $title The title string to be cropped
267 * @param integer $titleLength Crop title after this length - if not set, BE_USER->uc['titleLen'] is used
268 * @return string The processed title string, wrapped in <span title="...">|</span> if cropped
269 */
270 public function getRecordTitlePrep($title, $titleLength = 0) {
271 // If $titleLength is not a valid positive integer, use BE_USER->uc['titleLen']:
272 if (!$titleLength || !MathUtility::canBeInterpretedAsInteger($titleLength) || $titleLength < 0) {
273 $titleLength = $GLOBALS['BE_USER']->uc['titleLen'];
274 }
275 return htmlspecialchars(GeneralUtility::fixed_lgd_cs($title, $titleLength));
276 }
277
278 /**
279 * Build the MySql where clause by table.
280 *
281 * @param string $tableName Record table name
282 * @param array $fieldsToSearchWithin User right based visible fields where we can search within.
283 * @return string
284 */
285 protected function makeQuerySearchByTable($tableName, array $fieldsToSearchWithin) {
286 $queryPart = '';
287 $whereParts = array();
288 // If the search string is a simple integer, assemble an equality comparison
289 if (MathUtility::canBeInterpretedAsInteger($this->queryString)) {
290 foreach ($fieldsToSearchWithin as $fieldName) {
291 if ($fieldName == 'uid' || $fieldName == 'pid' || isset($GLOBALS['TCA'][$tableName]['columns'][$fieldName])) {
292 $fieldConfig = &$GLOBALS['TCA'][$tableName]['columns'][$fieldName]['config'];
293 // Assemble the search condition only if the field is an integer, or is uid or pid
294 if ($fieldName == 'uid' || $fieldName == 'pid' || $fieldConfig['type'] == 'input' && $fieldConfig['eval'] && GeneralUtility::inList($fieldConfig['eval'], 'int')) {
295 $whereParts[] = $fieldName . '=' . $this->queryString;
296 } elseif (
297 $fieldConfig['type'] == 'text' ||
298 $fieldConfig['type'] == 'flex' ||
299 ($fieldConfig['type'] == 'input' && (!$fieldConfig['eval'] ||
300 !preg_match('/date|time|int/', $fieldConfig['eval'])))) {
301 // Otherwise and if the field makes sense to be searched, assemble a like condition
302 $whereParts[] = $fieldName . ' LIKE \'%' . $this->queryString . '%\'';
303 }
304 }
305 }
306 } else {
307 $like = '\'%' . $GLOBALS['TYPO3_DB']->escapeStrForLike($GLOBALS['TYPO3_DB']->quoteStr($this->queryString, $tableName), $tableName) . '%\'';
308 foreach ($fieldsToSearchWithin as $fieldName) {
309 if (isset($GLOBALS['TCA'][$tableName]['columns'][$fieldName])) {
310 $fieldConfig = &$GLOBALS['TCA'][$tableName]['columns'][$fieldName]['config'];
311 // Check whether search should be case-sensitive or not
312 $format = 'LOWER(%s) LIKE LOWER(%s)';
313 if (is_array($fieldConfig['search'])) {
314 if (in_array('case', $fieldConfig['search'])) {
315 $format = '%s LIKE %s';
316 }
317 // Apply additional condition, if any
318 if ($fieldConfig['search']['andWhere']) {
319 $format = '((' . $fieldConfig['search']['andWhere'] . ') AND (' . $format . '))';
320 }
321 }
322 // Assemble the search condition only if the field makes sense to be searched
323 if ($fieldConfig['type'] == 'text' || $fieldConfig['type'] == 'flex' || $fieldConfig['type'] == 'input' && (!$fieldConfig['eval'] || !preg_match('/date|time|int/', $fieldConfig['eval']))) {
324 $whereParts[] = sprintf($format, $fieldName, $like);
325 }
326 }
327 }
328 }
329 // If at least one condition was defined, create the search query
330 if (count($whereParts) > 0) {
331 $queryPart = ' AND (' . implode(' OR ', $whereParts) . ')';
332 // And the relevant conditions for deleted and versioned records
333 $queryPart .= BackendUtility::deleteClause($tableName);
334 $queryPart .= BackendUtility::versioningPlaceholderClause($tableName);
335 } else {
336 $queryPart = ' AND 0 = 1';
337 }
338 return $queryPart;
339 }
340
341 /**
342 * Build the MySql ORDER BY statement.
343 *
344 * @param string $tableName Record table name
345 * @return string
346 */
347 protected function makeOrderByTable($tableName) {
348 $orderBy = '';
349 if (is_array($GLOBALS['TCA'][$tableName]['ctrl']) && array_key_exists('sortby', $GLOBALS['TCA'][$tableName]['ctrl'])) {
350 $sortBy = trim($GLOBALS['TCA'][$tableName]['ctrl']['sortby']);
351 if (!empty($sortBy)) {
352 $orderBy = 'ORDER BY ' . $sortBy;
353 }
354 } else {
355 $orderBy = $GLOBALS['TCA'][$tableName]['ctrl']['default_sortby'];
356 }
357 return $GLOBALS['TYPO3_DB']->stripOrderBy($orderBy);
358 }
359
360 /**
361 * Get all fields from given table where we can search for.
362 *
363 * @param string $tableName Name of the table for which to get the searchable fields
364 * @return array
365 */
366 protected function extractSearchableFieldsFromTable($tableName) {
367 // Get the list of fields to search in from the TCA, if any
368 if (isset($GLOBALS['TCA'][$tableName]['ctrl']['searchFields'])) {
369 $fieldListArray = GeneralUtility::trimExplode(',', $GLOBALS['TCA'][$tableName]['ctrl']['searchFields'], TRUE);
370 } else {
371 $fieldListArray = array();
372 }
373 // Add special fields
374 if ($GLOBALS['BE_USER']->isAdmin()) {
375 $fieldListArray[] = 'uid';
376 $fieldListArray[] = 'pid';
377 }
378 return $fieldListArray;
379 }
380
381 /**
382 * Safely retrieve the queryString.
383 *
384 * @param string $tableName
385 * @return string
386 */
387 public function getQueryString($tableName = '') {
388 return $GLOBALS['TYPO3_DB']->quoteStr($this->queryString, $tableName);
389 }
390
391 /**
392 * Setter for limit value.
393 *
394 * @param integer $limitCount
395 * @return void
396 */
397 public function setLimitCount($limitCount) {
398 $limit = MathUtility::convertToPositiveInteger($limitCount);
399 if ($limit > 0) {
400 $this->limitCount = $limit;
401 }
402 }
403
404 /**
405 * Setter for start count value.
406 *
407 * @param integer $startCount
408 * @return void
409 */
410 public function setStartCount($startCount) {
411 $this->startCount = MathUtility::convertToPositiveInteger($startCount);
412 }
413
414 /**
415 * Setter for the search query string.
416 *
417 * @param string $queryString
418 * @return void
419 * @see \TYPO3\CMS\Core\Utility\GeneralUtility::removeXSS()
420 */
421 public function setQueryString($queryString) {
422 $this->queryString = GeneralUtility::removeXSS($queryString);
423 }
424
425 /**
426 * Creates an instance of \TYPO3\CMS\Backend\Tree\View\PageTreeView which will select a
427 * page tree to $depth and return the object. In that object we will find the ids of the tree.
428 *
429 * @param integer $id Page id.
430 * @param integer $depth Depth to go down.
431 * @return string Comma separated list of uids
432 */
433 protected function getAvailablePageIds($id, $depth) {
434 $idList = '';
435 $tree = GeneralUtility::makeInstance('TYPO3\\CMS\\Backend\\Tree\\View\\PageTreeView');
436 $tree->init('AND ' . $this->userPermissions);
437 $tree->makeHTML = 0;
438 $tree->fieldArray = array('uid', 'php_tree_stop');
439 if ($depth) {
440 $tree->getTree($id, $depth, '');
441 }
442 $tree->ids[] = $id;
443 $idList = implode(',', $tree->ids);
444 return $idList;
445 }
446
447 }