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