8232a0f640086687c0b250bfe93998d95dbf10df
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Classes / Database / QueryView.php
1 <?php
2 namespace TYPO3\CMS\Core\Database;
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 Doctrine\DBAL\DBALException;
18 use TYPO3\CMS\Backend\Utility\BackendUtility;
19 use TYPO3\CMS\Core\Authentication\BackendUserAuthentication;
20 use TYPO3\CMS\Core\Database\Query\QueryHelper;
21 use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction;
22 use TYPO3\CMS\Core\Imaging\Icon;
23 use TYPO3\CMS\Core\Imaging\IconFactory;
24 use TYPO3\CMS\Core\Localization\LanguageService;
25 use TYPO3\CMS\Core\Messaging\FlashMessage;
26 use TYPO3\CMS\Core\Messaging\FlashMessageRendererResolver;
27 use TYPO3\CMS\Core\Messaging\FlashMessageService;
28 use TYPO3\CMS\Core\Type\Bitmask\Permission;
29 use TYPO3\CMS\Core\Utility\CsvUtility;
30 use TYPO3\CMS\Core\Utility\DebugUtility;
31 use TYPO3\CMS\Core\Utility\ExtensionManagementUtility;
32 use TYPO3\CMS\Core\Utility\GeneralUtility;
33
34 /**
35 * Class used in module tools/dbint (advanced search) and which may hold code specific for that module
36 * However the class has a general principle in it which may be used in the web/export module.
37 */
38 class QueryView
39 {
40 /**
41 * @var string
42 */
43 public $storeList = 'search_query_smallparts,search_result_labels,labels_noprefix,show_deleted,queryConfig,queryTable,queryFields,queryLimit,queryOrder,queryOrderDesc,queryOrder2,queryOrder2Desc,queryGroup,search_query_makeQuery';
44
45 /**
46 * @var string
47 */
48 public $downloadScript = 'index.php';
49
50 /**
51 * @var int
52 */
53 public $formW = 48;
54
55 /**
56 * @var int
57 */
58 public $noDownloadB = 0;
59
60 /**
61 * @var array
62 */
63 public $hookArray = [];
64
65 /**
66 * @var string
67 */
68 protected $formName = '';
69
70 /**
71 * @var \TYPO3\CMS\Core\Imaging\IconFactory
72 */
73 protected $iconFactory;
74
75 /**
76 * @var array
77 */
78 protected $tableArray = [];
79
80 /**
81 * @var LanguageService
82 */
83 protected $languageService;
84
85 /**
86 * @var BackendUserAuthentication
87 */
88 protected $backendUserAuthentication;
89
90 /**
91 * Settings, usually from the controller (previously known from $GLOBALS['SOBE']->MOD_SETTINGS
92 * @var array
93 */
94 protected $settings = [];
95
96 /**
97 * @var array information on the menu of this module
98 */
99 protected $menuItems = [];
100
101 /**
102 * @var string
103 */
104 protected $moduleName;
105
106 /**
107 * @param array $settings previously stored in $GLOBALS['SOBE']->MOD_SETTINGS
108 * @param array $menuItems previously stored in $GLOBALS['SOBE']->MOD_MENU
109 * @param string $moduleName previously stored in $GLOBALS['SOBE']->moduleName
110 */
111 public function __construct(array $settings = null, $menuItems = null, $moduleName = null)
112 {
113 $this->backendUserAuthentication = $GLOBALS['BE_USER'];
114 $this->languageService = $GLOBALS['LANG'];
115 $this->languageService->includeLLFile('EXT:core/Resources/Private/Language/locallang_t3lib_fullsearch.xlf');
116 $this->iconFactory = GeneralUtility::makeInstance(IconFactory::class);
117 $this->settings = $settings ?: $GLOBALS['SOBE']->MOD_SETTINGS;
118 $this->menuItems = $menuItems ?: $GLOBALS['SOBE']->MOD_MENU;
119 $this->moduleName = $moduleName ?: $GLOBALS['SOBE']->moduleName;
120 }
121
122 /**
123 * Get form
124 *
125 * @return string
126 */
127 public function form()
128 {
129 $markup = [];
130 $markup[] = '<div class="form-group">';
131 $markup[] = '<input placeholder="Search Word" class="form-control" type="search" name="SET[sword]" value="'
132 . htmlspecialchars($this->settings['sword']) . '">';
133 $markup[] = '</div>';
134 $markup[] = '<div class="form-group">';
135 $markup[] = '<input class="btn btn-default" type="submit" name="submit" value="Search All Records">';
136 $markup[] = '</div>';
137 return implode(LF, $markup);
138 }
139
140 /**
141 * Make store control
142 *
143 * @return string
144 */
145 public function makeStoreControl()
146 {
147 // Load/Save
148 $storeArray = $this->initStoreArray();
149
150 $opt = [];
151 foreach ($storeArray as $k => $v) {
152 $opt[] = '<option value="' . htmlspecialchars($k) . '">' . htmlspecialchars($v) . '</option>';
153 }
154 // Actions:
155 if (ExtensionManagementUtility::isLoaded('sys_action') && $this->backendUserAuthentication->isAdmin()) {
156 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_action');
157 $queryBuilder->getRestrictions()->removeAll();
158 $statement = $queryBuilder->select('uid', 'title')
159 ->from('sys_action')
160 ->where($queryBuilder->expr()->eq('type', $queryBuilder->createNamedParameter(2, \PDO::PARAM_INT)))
161 ->orderBy('title')
162 ->execute();
163 $opt[] = '<option value="0">__Save to Action:__</option>';
164 while ($row = $statement->fetch()) {
165 $opt[] = '<option value="-' . (int)$row['uid'] . '">' . htmlspecialchars($row['title']
166 . ' [' . (int)$row['uid'] . ']') . '</option>';
167 }
168 }
169 $markup = [];
170 $markup[] = '<div class="load-queries">';
171 $markup[] = ' <div class="form-inline">';
172 $markup[] = ' <div class="form-group">';
173 $markup[] = ' <select class="form-control" name="storeControl[STORE]" onChange="document.forms[0]'
174 . '[\'storeControl[title]\'].value= this.options[this.selectedIndex].value!=0 '
175 . '? this.options[this.selectedIndex].text : \'\';">' . implode(LF, $opt) . '</select>';
176 $markup[] = ' <input class="form-control" name="storeControl[title]" value="" type="text" max="80">';
177 $markup[] = ' <input class="btn btn-default" type="submit" name="storeControl[LOAD]" value="Load">';
178 $markup[] = ' <input class="btn btn-default" type="submit" name="storeControl[SAVE]" value="Save">';
179 $markup[] = ' <input class="btn btn-default" type="submit" name="storeControl[REMOVE]" value="Remove">';
180 $markup[] = ' </div>';
181 $markup[] = ' </div>';
182 $markup[] = '</div>';
183
184 return implode(LF, $markup);
185 }
186
187 /**
188 * Init store array
189 *
190 * @return array
191 */
192 public function initStoreArray()
193 {
194 $storeArray = [
195 '0' => '[New]'
196 ];
197 $savedStoreArray = unserialize($this->settings['storeArray']);
198 if (is_array($savedStoreArray)) {
199 $storeArray = array_merge($storeArray, $savedStoreArray);
200 }
201 return $storeArray;
202 }
203
204 /**
205 * Clean store query configs
206 *
207 * @param array $storeQueryConfigs
208 * @param array $storeArray
209 * @return array
210 */
211 public function cleanStoreQueryConfigs($storeQueryConfigs, $storeArray)
212 {
213 if (is_array($storeQueryConfigs)) {
214 foreach ($storeQueryConfigs as $k => $v) {
215 if (!isset($storeArray[$k])) {
216 unset($storeQueryConfigs[$k]);
217 }
218 }
219 }
220 return $storeQueryConfigs;
221 }
222
223 /**
224 * Add to store query configs
225 *
226 * @param array $storeQueryConfigs
227 * @param int $index
228 * @return array
229 */
230 public function addToStoreQueryConfigs($storeQueryConfigs, $index)
231 {
232 $keyArr = explode(',', $this->storeList);
233 $storeQueryConfigs[$index] = [];
234 foreach ($keyArr as $k) {
235 $storeQueryConfigs[$index][$k] = $this->settings[$k];
236 }
237 return $storeQueryConfigs;
238 }
239
240 /**
241 * Save query in action
242 *
243 * @param int $uid
244 * @return int
245 */
246 public function saveQueryInAction($uid)
247 {
248 if (ExtensionManagementUtility::isLoaded('sys_action')) {
249 $keyArr = explode(',', $this->storeList);
250 $saveArr = [];
251 foreach ($keyArr as $k) {
252 $saveArr[$k] = $this->settings[$k];
253 }
254 // Show query
255 if ($saveArr['queryTable']) {
256 /** @var \TYPO3\CMS\Core\Database\QueryGenerator */
257 $queryGenerator = GeneralUtility::makeInstance(QueryGenerator::class);
258 $queryGenerator->init('queryConfig', $saveArr['queryTable']);
259 $queryGenerator->makeSelectorTable($saveArr);
260 $queryGenerator->enablePrefix = 1;
261 $queryString = $queryGenerator->getQuery($queryGenerator->queryConfig);
262
263 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
264 ->getQueryBuilderForTable($queryGenerator->table);
265 $queryBuilder->getRestrictions()->removeAll()
266 ->add(GeneralUtility::makeInstance(DeletedRestriction::class));
267 $rowCount = $queryBuilder->count('*')
268 ->from($queryGenerator->table)
269 ->where(QueryHelper::stripLogicalOperatorPrefix($queryString))
270 ->execute()->fetchColumn(0);
271
272 $t2DataValue = [
273 'qC' => $saveArr,
274 'qCount' => $rowCount,
275 'qSelect' => $queryGenerator->getSelectQuery($queryString),
276 'qString' => $queryString
277 ];
278 GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('sys_action')
279 ->update(
280 'sys_action',
281 ['t2_data' => serialize($t2DataValue)],
282 ['uid' => (int)$uid],
283 ['t2_data' => Connection::PARAM_LOB]
284 );
285 }
286 return 1;
287 }
288 return null;
289 }
290
291 /**
292 * Load store query configs
293 *
294 * @param array $storeQueryConfigs
295 * @param int $storeIndex
296 * @param array $writeArray
297 * @return array
298 */
299 public function loadStoreQueryConfigs($storeQueryConfigs, $storeIndex, $writeArray)
300 {
301 if ($storeQueryConfigs[$storeIndex]) {
302 $keyArr = explode(',', $this->storeList);
303 foreach ($keyArr as $k) {
304 $writeArray[$k] = $storeQueryConfigs[$storeIndex][$k];
305 }
306 }
307 return $writeArray;
308 }
309
310 /**
311 * Process store control
312 *
313 * @return string
314 */
315 public function procesStoreControl()
316 {
317 $storeArray = $this->initStoreArray();
318 $storeQueryConfigs = unserialize($this->settings['storeQueryConfigs']);
319 $storeControl = GeneralUtility::_GP('storeControl');
320 $storeIndex = (int)$storeControl['STORE'];
321 $saveStoreArray = 0;
322 $writeArray = [];
323 $msg = '';
324 if (is_array($storeControl)) {
325 if ($storeControl['LOAD']) {
326 if ($storeIndex > 0) {
327 $writeArray = $this->loadStoreQueryConfigs($storeQueryConfigs, $storeIndex, $writeArray);
328 $saveStoreArray = 1;
329 $flashMessage = GeneralUtility::makeInstance(
330 FlashMessage::class,
331 sprintf($this->languageService->getLL('query_loaded'), $storeArray[$storeIndex])
332 );
333 } elseif ($storeIndex < 0 && ExtensionManagementUtility::isLoaded('sys_action')) {
334 $actionRecord = BackendUtility::getRecord('sys_action', abs($storeIndex));
335 if (is_array($actionRecord)) {
336 $dA = unserialize($actionRecord['t2_data']);
337 $dbSC = [];
338 if (is_array($dA['qC'])) {
339 $dbSC[0] = $dA['qC'];
340 }
341 $writeArray = $this->loadStoreQueryConfigs($dbSC, '0', $writeArray);
342 $saveStoreArray = 1;
343 $flashMessage = GeneralUtility::makeInstance(
344 FlashMessage::class,
345 sprintf($this->languageService->getLL('query_from_action_loaded'), $actionRecord['title'])
346 );
347 }
348 }
349 } elseif ($storeControl['SAVE']) {
350 if ($storeIndex < 0) {
351 $qOK = $this->saveQueryInAction(abs($storeIndex));
352 if ($qOK) {
353 $flashMessage = GeneralUtility::makeInstance(
354 FlashMessage::class,
355 $this->languageService->getLL('query_saved')
356 );
357 } else {
358 $flashMessage = GeneralUtility::makeInstance(
359 FlashMessage::class,
360 $this->languageService->getLL('query_notsaved'),
361 '',
362 FlashMessage::ERROR
363 );
364 }
365 } else {
366 if (trim($storeControl['title'])) {
367 if ($storeIndex > 0) {
368 $storeArray[$storeIndex] = $storeControl['title'];
369 } else {
370 $storeArray[] = $storeControl['title'];
371 end($storeArray);
372 $storeIndex = key($storeArray);
373 }
374 $storeQueryConfigs = $this->addToStoreQueryConfigs($storeQueryConfigs, $storeIndex);
375 $saveStoreArray = 1;
376 $flashMessage = GeneralUtility::makeInstance(
377 FlashMessage::class,
378 $this->languageService->getLL('query_saved')
379 );
380 }
381 }
382 } elseif ($storeControl['REMOVE']) {
383 if ($storeIndex > 0) {
384 $flashMessage = GeneralUtility::makeInstance(
385 FlashMessage::class,
386 sprintf($this->languageService->getLL('query_removed'), $storeArray[$storeControl['STORE']])
387 );
388 // Removing
389 unset($storeArray[$storeControl['STORE']]);
390 $saveStoreArray = 1;
391 }
392 }
393 if (!empty($flashMessage)) {
394 $msg = GeneralUtility::makeInstance(FlashMessageRendererResolver::class)
395 ->resolve()
396 ->render([$flashMessage]);
397 }
398 }
399 if ($saveStoreArray) {
400 // Making sure, index 0 is not set!
401 unset($storeArray[0]);
402 $writeArray['storeArray'] = serialize($storeArray);
403 $writeArray['storeQueryConfigs'] =
404 serialize($this->cleanStoreQueryConfigs($storeQueryConfigs, $storeArray));
405 $this->settings = BackendUtility::getModuleData(
406 $this->menuItems,
407 $writeArray,
408 $this->moduleName,
409 'ses'
410 );
411 }
412 return $msg;
413 }
414
415 /**
416 * Query marker
417 *
418 * @return string
419 */
420 public function queryMaker()
421 {
422 $output = '';
423 $this->hookArray = $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['t3lib_fullsearch'] ?? [];
424 $msg = $this->procesStoreControl();
425 if (!$this->backendUserAuthentication->userTS['mod.']['dbint.']['disableStoreControl']) {
426 $output .= '<h2>Load/Save Query</h2>';
427 $output .= '<div>' . $this->makeStoreControl() . '</div>';
428 $output .= $msg;
429 }
430 // Query Maker:
431 $queryGenerator = GeneralUtility::makeInstance(QueryGenerator::class);
432 $queryGenerator->init('queryConfig', $this->settings['queryTable']);
433 if ($this->formName) {
434 $queryGenerator->setFormName($this->formName);
435 }
436 $tmpCode = $queryGenerator->makeSelectorTable($this->settings);
437 $output .= '<div id="query"></div><h2>Make query</h2><div>' . $tmpCode . '</div>';
438 $mQ = $this->settings['search_query_makeQuery'];
439 // Make form elements:
440 if ($queryGenerator->table && is_array($GLOBALS['TCA'][$queryGenerator->table])) {
441 if ($mQ) {
442 // Show query
443 $queryGenerator->enablePrefix = 1;
444 $queryString = $queryGenerator->getQuery($queryGenerator->queryConfig);
445 $selectQueryString = $queryGenerator->getSelectQuery($queryString);
446 $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($queryGenerator->table);
447
448 $isConnectionMysql = strpos($connection->getServerVersion(), 'MySQL') === 0;
449 $fullQueryString = '';
450 try {
451 if ($mQ === 'explain' && $isConnectionMysql) {
452 // EXPLAIN is no ANSI SQL, for now this is only executed on mysql
453 // @todo: Move away from getSelectQuery() or model differently
454 $fullQueryString = 'EXPLAIN ' . $selectQueryString;
455 $dataRows = $connection->executeQuery('EXPLAIN ' . $selectQueryString)->fetchAll();
456 } elseif ($mQ === 'count') {
457 $queryBuilder = $connection->createQueryBuilder();
458 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
459 $dataRows = $queryBuilder->count('*')
460 ->from($queryGenerator->table)
461 ->where(QueryHelper::stripLogicalOperatorPrefix($queryString));
462 $fullQueryString = $queryBuilder->getSQL();
463 $dataRows = [$queryBuilder->execute()->fetchColumn(0)];
464 } else {
465 $fullQueryString = $selectQueryString;
466 $dataRows = $connection->executeQuery($selectQueryString)->fetchAll();
467 }
468 if (!$this->backendUserAuthentication->userTS['mod.']['dbint.']['disableShowSQLQuery']) {
469 $output .= '<h2>SQL query</h2><div><pre>' . htmlspecialchars($fullQueryString) . '</pre></div>';
470 }
471 $cPR = $this->getQueryResultCode($mQ, $dataRows, $queryGenerator->table);
472 $output .= '<h2>' . $cPR['header'] . '</h2><div>' . $cPR['content'] . '</div>';
473 } catch (DBALException $e) {
474 if (!$this->backendUserAuthentication->userTS['mod.']['dbint.']['disableShowSQLQuery']) {
475 $output .= '<h2>SQL query</h2><div><pre>' . htmlspecialchars($fullQueryString) . '</pre></div>';
476 }
477 $out = '<p><strong>Error: <span class="text-danger">'
478 . $e->getMessage()
479 . '</span></strong></p>';
480 $output .= '<h2>SQL error</h2><div>' . $out . '</div>';
481 }
482 }
483 }
484 return '<div class="query-builder">' . $output . '</div>';
485 }
486
487 /**
488 * Get query result code
489 *
490 * @param string $type
491 * @param array $dataRows Rows to display
492 * @param string $table
493 * @return array HTML-code for "header" and "content"
494 * @throws \TYPO3\CMS\Core\Exception
495 */
496 public function getQueryResultCode($type, array $dataRows, $table)
497 {
498 $out = '';
499 $cPR = [];
500 switch ($type) {
501 case 'count':
502 $cPR['header'] = 'Count';
503 $cPR['content'] = '<BR><strong>' . (int)$dataRows[0] . '</strong> records selected.';
504 break;
505 case 'all':
506 $rowArr = [];
507 $dataRow = null;
508 foreach ($dataRows as $dataRow) {
509 $rowArr[] = $this->resultRowDisplay($dataRow, $GLOBALS['TCA'][$table], $table);
510 }
511 if (is_array($this->hookArray['beforeResultTable'])) {
512 foreach ($this->hookArray['beforeResultTable'] as $_funcRef) {
513 $out .= GeneralUtility::callUserFunction($_funcRef, $this->settings, $this);
514 }
515 }
516 if (!empty($rowArr)) {
517 $cPR['header'] = 'Result';
518 $out .= '<table class="table table-striped table-hover">'
519 . $this->resultRowTitles($dataRow, $GLOBALS['TCA'][$table], $table) . implode(LF, $rowArr)
520 . '</table>';
521 } else {
522 $this->renderNoResultsFoundMessage();
523 }
524
525 $cPR['content'] = $out;
526 break;
527 case 'csv':
528 $rowArr = [];
529 $first = 1;
530 foreach ($dataRows as $dataRow) {
531 if ($first) {
532 $rowArr[] = $this->csvValues(array_keys($dataRow), ',', '');
533 $first = 0;
534 }
535 $rowArr[] = $this->csvValues($dataRow, ',', '"', $GLOBALS['TCA'][$table], $table);
536 }
537 if (!empty($rowArr)) {
538 $cPR['header'] = 'Result';
539 $out .= '<textarea name="whatever" rows="20" class="text-monospace" style="width:100%">'
540 . htmlspecialchars(implode(LF, $rowArr))
541 . '</textarea>';
542 if (!$this->noDownloadB) {
543 $out .= '<br><input class="btn btn-default" type="submit" name="download_file" '
544 . 'value="Click to download file" onClick="window.location.href=' . htmlspecialchars(GeneralUtility::quoteJSvalue($this->downloadScript))
545 . ';">';
546 }
547 // Downloads file:
548 // @todo: args. routing anyone?
549 if (GeneralUtility::_GP('download_file')) {
550 $filename = 'TYPO3_' . $table . '_export_' . date('dmy-Hi') . '.csv';
551 $mimeType = 'application/octet-stream';
552 header('Content-Type: ' . $mimeType);
553 header('Content-Disposition: attachment; filename=' . $filename);
554 echo implode(CRLF, $rowArr);
555 die;
556 }
557 } else {
558 $this->renderNoResultsFoundMessage();
559 }
560 $cPR['content'] = $out;
561 break;
562 case 'explain':
563 default:
564 foreach ($dataRows as $dataRow) {
565 $out .= '<br />' . DebugUtility::viewArray($dataRow);
566 }
567 $cPR['header'] = 'Explain SQL query';
568 $cPR['content'] = $out;
569 }
570 return $cPR;
571 }
572
573 /**
574 * CSV values
575 *
576 * @param array $row
577 * @param string $delim
578 * @param string $quote
579 * @param array $conf
580 * @param string $table
581 * @return string A single line of CSV
582 */
583 public function csvValues($row, $delim = ',', $quote = '"', $conf = [], $table = '')
584 {
585 $valueArray = $row;
586 if ($this->settings['search_result_labels'] && $table) {
587 foreach ($valueArray as $key => $val) {
588 $valueArray[$key] = $this->getProcessedValueExtra($table, $key, $val, $conf, ';');
589 }
590 }
591 return CsvUtility::csvValues($valueArray, $delim, $quote);
592 }
593
594 /**
595 * Search
596 *
597 * @return string
598 */
599 public function search()
600 {
601 $swords = $this->settings['sword'];
602 $out = '';
603 if ($swords) {
604 foreach ($GLOBALS['TCA'] as $table => $value) {
605 // Get fields list
606 $conf = $GLOBALS['TCA'][$table];
607 // Avoid querying tables with no columns
608 if (empty($conf['columns'])) {
609 continue;
610 }
611 $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($table);
612 $tableColumns = $connection->getSchemaManager()->listTableColumns($table);
613 $fieldsInDatabase = [];
614 foreach ($tableColumns as $column) {
615 $fieldsInDatabase[] = $column->getName();
616 }
617 $fields = array_intersect(array_keys($conf['columns']), $fieldsInDatabase);
618
619 $queryBuilder = $connection->createQueryBuilder();
620 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
621 $queryBuilder->count('*')->from($table);
622 $likes = [];
623 $excapedLikeString = '%' . $queryBuilder->escapeLikeWildcards($swords) . '%';
624 foreach ($fields as $field) {
625 $likes[] = $queryBuilder->expr()->like(
626 $field,
627 $queryBuilder->createNamedParameter($excapedLikeString, \PDO::PARAM_STR)
628 );
629 }
630 $count = $queryBuilder->orWhere(...$likes)->execute()->fetchColumn(0);
631
632 if ($count > 0) {
633 $queryBuilder = $connection->createQueryBuilder();
634 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
635 $queryBuilder->select('uid', $conf['ctrl']['label'])
636 ->from($table)
637 ->setMaxResults(200);
638 $likes = [];
639 foreach ($fields as $field) {
640 $likes[] = $queryBuilder->expr()->like(
641 $field,
642 $queryBuilder->createNamedParameter($excapedLikeString, \PDO::PARAM_STR)
643 );
644 }
645 $statement = $queryBuilder->orWhere(...$likes)->execute();
646 $lastRow = null;
647 $rowArr = [];
648 while ($row = $statement->fetch()) {
649 $rowArr[] = $this->resultRowDisplay($row, $conf, $table);
650 $lastRow = $row;
651 }
652 $markup = [];
653 $markup[] = '<div class="panel panel-default">';
654 $markup[] = ' <div class="panel-heading">';
655 $markup[] = htmlspecialchars($this->languageService->sL($conf['ctrl']['title'])) . ' (' . $count . ')';
656 $markup[] = ' </div>';
657 $markup[] = ' <table class="table table-striped table-hover">';
658 $markup[] = $this->resultRowTitles($lastRow, $conf, $table);
659 $markup[] = implode(LF, $rowArr);
660 $markup[] = ' </table>';
661 $markup[] = '</div>';
662
663 $out .= implode(LF, $markup);
664 }
665 }
666 }
667 return $out;
668 }
669
670 /**
671 * Result row display
672 *
673 * @param array $row
674 * @param array $conf
675 * @param string $table
676 * @return string
677 */
678 public function resultRowDisplay($row, $conf, $table)
679 {
680 $out = '<tr>';
681 foreach ($row as $fieldName => $fieldValue) {
682 if (GeneralUtility::inList($this->settings['queryFields'], $fieldName)
683 || !$this->settings['queryFields']
684 && $fieldName !== 'pid'
685 && $fieldName !== 'deleted'
686 ) {
687 if ($this->settings['search_result_labels']) {
688 $fVnew = $this->getProcessedValueExtra($table, $fieldName, $fieldValue, $conf, '<br />');
689 } else {
690 $fVnew = htmlspecialchars($fieldValue);
691 }
692 $out .= '<td>' . $fVnew . '</td>';
693 }
694 }
695 $out .= '<td>';
696 /** @var \TYPO3\CMS\Backend\Routing\UriBuilder $uriBuilder */
697 $uriBuilder = GeneralUtility::makeInstance(\TYPO3\CMS\Backend\Routing\UriBuilder::class);
698
699 if (!$row['deleted']) {
700 $out .= '<div class="btn-group" role="group">';
701 $url = (string)$uriBuilder->buildUriFromRoute('record_edit', [
702 'edit' => [
703 $table => [
704 $row['uid'] => 'edit'
705 ]
706 ],
707 'returnUrl' => GeneralUtility::getIndpEnv('REQUEST_URI')
708 . GeneralUtility::implodeArrayForUrl('SET', (array)GeneralUtility::_POST('SET'))
709 ]);
710 $out .= '<a class="btn btn-default" href="' . htmlspecialchars($url) . '">'
711 . $this->iconFactory->getIcon('actions-open', Icon::SIZE_SMALL)->render() . '</a>';
712 $out .= '</div><div class="btn-group" role="group">';
713 $out .= '<a class="btn btn-default" href="#" onClick="top.TYPO3.InfoWindow.showItem(\'' . $table . '\',' . $row['uid']
714 . ');return false;">' . $this->iconFactory->getIcon('actions-document-info', Icon::SIZE_SMALL)->render()
715 . '</a>';
716 $out .= '</div>';
717 } else {
718 $out .= '<div class="btn-group" role="group">';
719 $out .= '<a class="btn btn-default" href="' . htmlspecialchars((string)$uriBuilder->buildUriFromRoute('tce_db', [
720 'cmd' => [
721 $table => [
722 $row['uid'] => [
723 'undelete' => 1
724 ]
725 ]
726 ],
727 'redirect' => GeneralUtility::linkThisScript()
728 ])) . '" title="' . htmlspecialchars($this->languageService->getLL('undelete_only')) . '">';
729 $out .= $this->iconFactory->getIcon('actions-edit-restore', Icon::SIZE_SMALL)->render() . '</a>';
730 $formEngineParameters = [
731 'edit' => [
732 $table => [
733 $row['uid'] => 'edit'
734 ]
735 ],
736 'returnUrl' => GeneralUtility::linkThisScript()
737 ];
738 $redirectUrl = (string)$uriBuilder->buildUriFromRoute('record_edit', $formEngineParameters);
739 $out .= '<a class="btn btn-default" href="' . htmlspecialchars((string)$uriBuilder->buildUriFromRoute('tce_db', [
740 'cmd' => [
741 $table => [
742 $row['uid'] => [
743 'undelete' => 1
744 ]
745 ]
746 ],
747 'redirect' => $redirectUrl
748 ])) . '" title="' . htmlspecialchars($this->languageService->getLL('undelete_and_edit')) . '">';
749 $out .= $this->iconFactory->getIcon('actions-edit-restore-edit', Icon::SIZE_SMALL)->render() . '</a>';
750 $out .= '</div>';
751 }
752 $_params = [$table => $row];
753 if (is_array($this->hookArray['additionalButtons'])) {
754 foreach ($this->hookArray['additionalButtons'] as $_funcRef) {
755 $out .= GeneralUtility::callUserFunction($_funcRef, $_params, $this);
756 }
757 }
758 $out .= '</td></tr>';
759 return $out;
760 }
761
762 /**
763 * Get processed value extra
764 *
765 * @param string $table
766 * @param string $fieldName
767 * @param string $fieldValue
768 * @param array $conf Not used
769 * @param string $splitString
770 * @return string
771 */
772 public function getProcessedValueExtra($table, $fieldName, $fieldValue, $conf, $splitString)
773 {
774 $out = '';
775 $fields = [];
776 // Analysing the fields in the table.
777 if (is_array($GLOBALS['TCA'][$table])) {
778 $fC = $GLOBALS['TCA'][$table]['columns'][$fieldName];
779 $fields = $fC['config'];
780 $fields['exclude'] = $fC['exclude'];
781 if (is_array($fC) && $fC['label']) {
782 $fields['label'] = preg_replace('/:$/', '', trim($this->languageService->sL($fC['label'])));
783 switch ($fields['type']) {
784 case 'input':
785 if (preg_match('/int|year/i', $fields['eval'])) {
786 $fields['type'] = 'number';
787 } elseif (preg_match('/time/i', $fields['eval'])) {
788 $fields['type'] = 'time';
789 } elseif (preg_match('/date/i', $fields['eval'])) {
790 $fields['type'] = 'date';
791 } else {
792 $fields['type'] = 'text';
793 }
794 break;
795 case 'check':
796 if (!$fields['items']) {
797 $fields['type'] = 'boolean';
798 } else {
799 $fields['type'] = 'binary';
800 }
801 break;
802 case 'radio':
803 $fields['type'] = 'multiple';
804 break;
805 case 'select':
806 $fields['type'] = 'multiple';
807 if ($fields['foreign_table']) {
808 $fields['type'] = 'relation';
809 }
810 if ($fields['special']) {
811 $fields['type'] = 'text';
812 }
813 break;
814 case 'group':
815 $fields['type'] = 'files';
816 if ($fields['internal_type'] === 'db') {
817 $fields['type'] = 'relation';
818 }
819 break;
820 case 'user':
821 case 'flex':
822 case 'passthrough':
823 case 'none':
824 case 'text':
825 default:
826 $fields['type'] = 'text';
827 }
828 } else {
829 $fields['label'] = '[FIELD: ' . $fieldName . ']';
830 switch ($fieldName) {
831 case 'pid':
832 $fields['type'] = 'relation';
833 $fields['allowed'] = 'pages';
834 break;
835 case 'cruser_id':
836 $fields['type'] = 'relation';
837 $fields['allowed'] = 'be_users';
838 break;
839 case 'tstamp':
840 case 'crdate':
841 $fields['type'] = 'time';
842 break;
843 default:
844 $fields['type'] = 'number';
845 }
846 }
847 }
848 switch ($fields['type']) {
849 case 'date':
850 if ($fieldValue != -1) {
851 $out = strftime('%d-%m-%Y', $fieldValue);
852 }
853 break;
854 case 'time':
855 if ($fieldValue != -1) {
856 if ($splitString === '<br />') {
857 $out = strftime('%H:%M' . $splitString . '%d-%m-%Y', $fieldValue);
858 } else {
859 $out = strftime('%H:%M %d-%m-%Y', $fieldValue);
860 }
861 }
862 break;
863 case 'multiple':
864 case 'binary':
865 case 'relation':
866 $out = $this->makeValueList($fieldName, $fieldValue, $fields, $table, $splitString);
867 break;
868 case 'boolean':
869 $out = $fieldValue ? 'True' : 'False';
870 break;
871 case 'files':
872 default:
873 $out = htmlspecialchars($fieldValue);
874 }
875 return $out;
876 }
877
878 /**
879 * Get tree list
880 *
881 * @param int $id
882 * @param int $depth
883 * @param int $begin
884 * @param string $permsClause
885 *
886 * @return string
887 */
888 public function getTreeList($id, $depth, $begin = 0, $permsClause = null)
889 {
890 $depth = (int)$depth;
891 $begin = (int)$begin;
892 $id = (int)$id;
893 if ($id < 0) {
894 $id = abs($id);
895 }
896 if ($begin == 0) {
897 $theList = $id;
898 } else {
899 $theList = '';
900 }
901 if ($id && $depth > 0) {
902 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
903 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
904 $statement = $queryBuilder->select('uid')
905 ->from('pages')
906 ->where(
907 $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($id, \PDO::PARAM_INT)),
908 QueryHelper::stripLogicalOperatorPrefix($permsClause)
909 )
910 ->execute();
911 while ($row = $statement->fetch()) {
912 if ($begin <= 0) {
913 $theList .= ',' . $row['uid'];
914 }
915 if ($depth > 1) {
916 $theList .= $this->getTreeList($row['uid'], $depth - 1, $begin - 1, $permsClause);
917 }
918 }
919 }
920 return $theList;
921 }
922
923 /**
924 * Make value list
925 *
926 * @param string $fieldName
927 * @param string $fieldValue
928 * @param array $conf
929 * @param string $table
930 * @param string $splitString
931 * @return string
932 */
933 public function makeValueList($fieldName, $fieldValue, $conf, $table, $splitString)
934 {
935 $fieldSetup = $conf;
936 $out = '';
937 if ($fieldSetup['type'] === 'files') {
938 $d = dir(PATH_site . $fieldSetup['uploadfolder']);
939 while (false !== ($entry = $d->read())) {
940 if ($entry === '.' || $entry === '..') {
941 continue;
942 }
943 $fileArray[] = $entry;
944 }
945 $d->close();
946 natcasesort($fileArray);
947 foreach ($fileArray as $fileName) {
948 if (GeneralUtility::inList($fieldValue, $fileName) || $fieldValue == $fileName) {
949 if ($out !== '') {
950 $out .= $splitString;
951 }
952 $out .= htmlspecialchars($fileName);
953 }
954 }
955 }
956 if ($fieldSetup['type'] === 'multiple') {
957 foreach ($fieldSetup['items'] as $key => $val) {
958 if (substr($val[0], 0, 4) === 'LLL:') {
959 $value = $this->languageService->sL($val[0]);
960 } else {
961 $value = $val[0];
962 }
963 if (GeneralUtility::inList($fieldValue, $val[1]) || $fieldValue == $val[1]) {
964 if ($out !== '') {
965 $out .= $splitString;
966 }
967 $out .= htmlspecialchars($value);
968 }
969 }
970 }
971 if ($fieldSetup['type'] === 'binary') {
972 foreach ($fieldSetup['items'] as $Key => $val) {
973 if (substr($val[0], 0, 4) === 'LLL:') {
974 $value = $this->languageService->sL($val[0]);
975 } else {
976 $value = $val[0];
977 }
978 if ($out !== '') {
979 $out .= $splitString;
980 }
981 $out .= htmlspecialchars($value);
982 }
983 }
984 if ($fieldSetup['type'] === 'relation') {
985 $dontPrefixFirstTable = 0;
986 $useTablePrefix = 0;
987 if ($fieldSetup['items']) {
988 foreach ($fieldSetup['items'] as $key => $val) {
989 if (substr($val[0], 0, 4) === 'LLL:') {
990 $value = $this->languageService->sL($val[0]);
991 } else {
992 $value = $val[0];
993 }
994 if (GeneralUtility::inList($fieldValue, $value) || $fieldValue == $value) {
995 if ($out !== '') {
996 $out .= $splitString;
997 }
998 $out .= htmlspecialchars($value);
999 }
1000 }
1001 }
1002 if (stristr($fieldSetup['allowed'], ',')) {
1003 $from_table_Arr = explode(',', $fieldSetup['allowed']);
1004 $useTablePrefix = 1;
1005 if (!$fieldSetup['prepend_tname']) {
1006 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
1007 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
1008 $statement = $queryBuilder->select($fieldName)->from($table)->execute();
1009 while ($row = $statement->fetch()) {
1010 if (stristr($row[$fieldName], ',')) {
1011 $checkContent = explode(',', $row[$fieldName]);
1012 foreach ($checkContent as $singleValue) {
1013 if (!stristr($singleValue, '_')) {
1014 $dontPrefixFirstTable = 1;
1015 }
1016 }
1017 } else {
1018 $singleValue = $row[$fieldName];
1019 if ($singleValue !== '' && !stristr($singleValue, '_')) {
1020 $dontPrefixFirstTable = 1;
1021 }
1022 }
1023 }
1024 }
1025 } else {
1026 $from_table_Arr[0] = $fieldSetup['allowed'];
1027 }
1028 if ($fieldSetup['prepend_tname']) {
1029 $useTablePrefix = 1;
1030 }
1031 if ($fieldSetup['foreign_table']) {
1032 $from_table_Arr[0] = $fieldSetup['foreign_table'];
1033 }
1034 $counter = 0;
1035 $useSelectLabels = 0;
1036 $useAltSelectLabels = 0;
1037 $tablePrefix = '';
1038 $labelFieldSelect = [];
1039 foreach ($from_table_Arr as $from_table) {
1040 if ($useTablePrefix && !$dontPrefixFirstTable && $counter != 1 || $counter == 1) {
1041 $tablePrefix = $from_table . '_';
1042 }
1043 $counter = 1;
1044 if (is_array($GLOBALS['TCA'][$from_table])) {
1045 $labelField = $GLOBALS['TCA'][$from_table]['ctrl']['label'];
1046 $altLabelField = $GLOBALS['TCA'][$from_table]['ctrl']['label_alt'];
1047 if ($GLOBALS['TCA'][$from_table]['columns'][$labelField]['config']['items']) {
1048 $items = $GLOBALS['TCA'][$from_table]['columns'][$labelField]['config']['items'];
1049 foreach ($items as $labelArray) {
1050 if (substr($labelArray[0], 0, 4) === 'LLL:') {
1051 $labelFieldSelect[$labelArray[1]] = $this->languageService->sL($labelArray[0]);
1052 } else {
1053 $labelFieldSelect[$labelArray[1]] = $labelArray[0];
1054 }
1055 }
1056 $useSelectLabels = 1;
1057 }
1058 $altLabelFieldSelect = [];
1059 if ($GLOBALS['TCA'][$from_table]['columns'][$altLabelField]['config']['items']) {
1060 $items = $GLOBALS['TCA'][$from_table]['columns'][$altLabelField]['config']['items'];
1061 foreach ($items as $altLabelArray) {
1062 if (substr($altLabelArray[0], 0, 4) === 'LLL:') {
1063 $altLabelFieldSelect[$altLabelArray[1]] = $this->languageService->sL($altLabelArray[0]);
1064 } else {
1065 $altLabelFieldSelect[$altLabelArray[1]] = $altLabelArray[0];
1066 }
1067 }
1068 $useAltSelectLabels = 1;
1069 }
1070
1071 if (!$this->tableArray[$from_table]) {
1072 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($from_table);
1073 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
1074 $selectFields = ['uid', $labelField];
1075 if ($altLabelField) {
1076 $selectFields[] = $altLabelField;
1077 }
1078 $queryBuilder->select(...$selectFields)
1079 ->from($from_table)
1080 ->orderBy('uid');
1081 if (!$this->backendUserAuthentication->isAdmin() && $GLOBALS['TYPO3_CONF_VARS']['BE']['lockBeUserToDBmounts']) {
1082 $webMounts = $this->backendUserAuthentication->returnWebmounts();
1083 $perms_clause = $this->backendUserAuthentication->getPagePermsClause(Permission::PAGE_SHOW);
1084 $webMountPageTree = '';
1085 $webMountPageTreePrefix = '';
1086 foreach ($webMounts as $webMount) {
1087 if ($webMountPageTree) {
1088 $webMountPageTreePrefix = ',';
1089 }
1090 $webMountPageTree .= $webMountPageTreePrefix
1091 . $this->getTreeList($webMount, 999, $begin = 0, $perms_clause);
1092 }
1093 if ($from_table === 'pages') {
1094 $queryBuilder->where(
1095 QueryHelper::stripLogicalOperatorPrefix($perms_clause),
1096 $queryBuilder->expr()->in(
1097 'uid',
1098 $queryBuilder->createNamedParameter(
1099 GeneralUtility::intExplode(',', $webMountPageTree),
1100 Connection::PARAM_INT_ARRAY
1101 )
1102 )
1103 );
1104 } else {
1105 $queryBuilder->where(
1106 $queryBuilder->expr()->in(
1107 'pid',
1108 $queryBuilder->createNamedParameter(
1109 GeneralUtility::intExplode(',', $webMountPageTree),
1110 Connection::PARAM_INT_ARRAY
1111 )
1112 )
1113 );
1114 }
1115 }
1116 $statement = $queryBuilder->execute();
1117 $this->tableArray[$from_table] = [];
1118 while ($row = $statement->fetch()) {
1119 $this->tableArray[$from_table][] = $row;
1120 }
1121 }
1122
1123 foreach ($this->tableArray[$from_table] as $key => $val) {
1124 $this->settings['labels_noprefix'] =
1125 $this->settings['labels_noprefix'] == 1
1126 ? 'on'
1127 : $this->settings['labels_noprefix'];
1128 $prefixString =
1129 $this->settings['labels_noprefix'] === 'on'
1130 ? ''
1131 : ' [' . $tablePrefix . $val['uid'] . '] ';
1132 if ($out !== '') {
1133 $out .= $splitString;
1134 }
1135 if (GeneralUtility::inList($fieldValue, $tablePrefix . $val['uid'])
1136 || $fieldValue == $tablePrefix . $val['uid']) {
1137 if ($useSelectLabels) {
1138 $out .= htmlspecialchars($prefixString . $labelFieldSelect[$val[$labelField]]);
1139 } elseif ($val[$labelField]) {
1140 $out .= htmlspecialchars($prefixString . $val[$labelField]);
1141 } elseif ($useAltSelectLabels) {
1142 $out .= htmlspecialchars($prefixString . $altLabelFieldSelect[$val[$altLabelField]]);
1143 } else {
1144 $out .= htmlspecialchars($prefixString . $val[$altLabelField]);
1145 }
1146 }
1147 }
1148 }
1149 }
1150 }
1151 return $out;
1152 }
1153
1154 /**
1155 * Render table header
1156 *
1157 * @param array $row Table columns
1158 * @param array $conf Table TCA
1159 * @param string $table Table name
1160 * @return string HTML of table header
1161 */
1162 public function resultRowTitles($row, $conf, $table)
1163 {
1164 $tableHeader = [];
1165 // Start header row
1166 $tableHeader[] = '<thead><tr>';
1167 // Iterate over given columns
1168 foreach ($row as $fieldName => $fieldValue) {
1169 if (GeneralUtility::inList($this->settings['queryFields'], $fieldName)
1170 || !$this->settings['queryFields']
1171 && $fieldName !== 'pid'
1172 && $fieldName !== 'deleted'
1173 ) {
1174 if ($this->settings['search_result_labels']) {
1175 $title = $this->languageService->sL($conf['columns'][$fieldName]['label']
1176 ? $conf['columns'][$fieldName]['label']
1177 : $fieldName);
1178 } else {
1179 $title = $this->languageService->sL($fieldName);
1180 }
1181 $tableHeader[] = '<th>' . htmlspecialchars($title) . '</th>';
1182 }
1183 }
1184 // Add empty icon column
1185 $tableHeader[] = '<th></th>';
1186 // Close header row
1187 $tableHeader[] = '</tr></thead>';
1188 return implode(LF, $tableHeader);
1189 }
1190
1191 /**
1192 * CSV row titles
1193 *
1194 * @param array $row
1195 * @param array $conf
1196 * @param mixed $table Not used
1197 * @return string
1198 */
1199 public function csvRowTitles($row, $conf, $table)
1200 {
1201 $out = '';
1202 foreach ($row as $fieldName => $fieldValue) {
1203 if (GeneralUtility::inList($this->settings['queryFields'], $fieldName)
1204 || !$this->settings['queryFields'] && $fieldName !== 'pid') {
1205 if ($out !== '') {
1206 $out .= ',';
1207 }
1208 if ($this->settings['search_result_labels']) {
1209 $out .= htmlspecialchars(
1210 $this->languageService->sL(
1211 $conf['columns'][$fieldName]['label']
1212 ? $conf['columns'][$fieldName]['label']
1213 : $fieldName
1214 )
1215 );
1216 } else {
1217 $out .= htmlspecialchars($this->languageService->sL($fieldName));
1218 }
1219 }
1220 }
1221 return $out;
1222 }
1223
1224 /**
1225 * Sets the current name of the input form.
1226 *
1227 * @param string $formName The name of the form.
1228 */
1229 public function setFormName($formName)
1230 {
1231 $this->formName = trim($formName);
1232 }
1233
1234 /**
1235 * @throws \InvalidArgumentException
1236 * @throws \TYPO3\CMS\Core\Exception
1237 */
1238 private function renderNoResultsFoundMessage()
1239 {
1240 $flashMessage = GeneralUtility::makeInstance(FlashMessage::class, 'No rows selected!', '', FlashMessage::INFO);
1241 $flashMessageService = GeneralUtility::makeInstance(FlashMessageService::class);
1242 $defaultFlashMessageQueue = $flashMessageService->getMessageQueueByIdentifier();
1243 $defaultFlashMessageQueue->enqueue($flashMessage);
1244 }
1245 }