54f001d17945b7aa57e45bc6459083ce2a2cbde8
[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 if (!$row['deleted']) {
685 $out .= '<div class="btn-group" role="group">';
686 $url = BackendUtility::getModuleUrl('record_edit', [
687 'edit' => [
688 $table => [
689 $row['uid'] => 'edit'
690 ]
691 ],
692 'returnUrl' => GeneralUtility::getIndpEnv('REQUEST_URI')
693 . GeneralUtility::implodeArrayForUrl('SET', (array)GeneralUtility::_POST('SET'))
694 ]);
695 $out .= '<a class="btn btn-default" href="' . htmlspecialchars($url) . '">'
696 . $this->iconFactory->getIcon('actions-open', Icon::SIZE_SMALL)->render() . '</a>';
697 $out .= '</div><div class="btn-group" role="group">';
698 $out .= '<a class="btn btn-default" href="#" onClick="top.launchView(\'' . $table . '\',' . $row['uid']
699 . ');return false;">' . $this->iconFactory->getIcon('actions-document-info', Icon::SIZE_SMALL)->render()
700 . '</a>';
701 $out .= '</div>';
702 } else {
703 $out .= '<div class="btn-group" role="group">';
704 $out .= '<a class="btn btn-default" href="' . htmlspecialchars(BackendUtility::getModuleUrl('tce_db', [
705 'cmd' => [
706 $table => [
707 $row['uid'] => [
708 'undelete' => 1
709 ]
710 ]
711 ],
712 'redirect' => GeneralUtility::linkThisScript()
713 ])) . '" title="' . htmlspecialchars($this->languageService->getLL('undelete_only')) . '">';
714 $out .= $this->iconFactory->getIcon('actions-edit-restore', Icon::SIZE_SMALL)->render() . '</a>';
715 $formEngineParameters = [
716 'edit' => [
717 $table => [
718 $row['uid'] => 'edit'
719 ]
720 ],
721 'returnUrl' => GeneralUtility::linkThisScript()
722 ];
723 $redirectUrl = BackendUtility::getModuleUrl('record_edit', $formEngineParameters);
724 $out .= '<a class="btn btn-default" href="' . htmlspecialchars(BackendUtility::getModuleUrl('tce_db', [
725 'cmd' => [
726 $table => [
727 $row['uid'] => [
728 'undelete' => 1
729 ]
730 ]
731 ],
732 'redirect' => $redirectUrl
733 ])) . '" title="' . htmlspecialchars($this->languageService->getLL('undelete_and_edit')) . '">';
734 $out .= $this->iconFactory->getIcon('actions-edit-restore-edit', Icon::SIZE_SMALL)->render() . '</a>';
735 $out .= '</div>';
736 }
737 $_params = [$table => $row];
738 if (is_array($this->hookArray['additionalButtons'])) {
739 foreach ($this->hookArray['additionalButtons'] as $_funcRef) {
740 $out .= GeneralUtility::callUserFunction($_funcRef, $_params, $this);
741 }
742 }
743 $out .= '</td></tr>';
744 return $out;
745 }
746
747 /**
748 * Get processed value extra
749 *
750 * @param string $table
751 * @param string $fieldName
752 * @param string $fieldValue
753 * @param array $conf Not used
754 * @param string $splitString
755 * @return string
756 */
757 public function getProcessedValueExtra($table, $fieldName, $fieldValue, $conf, $splitString)
758 {
759 $out = '';
760 $fields = [];
761 // Analysing the fields in the table.
762 if (is_array($GLOBALS['TCA'][$table])) {
763 $fC = $GLOBALS['TCA'][$table]['columns'][$fieldName];
764 $fields = $fC['config'];
765 $fields['exclude'] = $fC['exclude'];
766 if (is_array($fC) && $fC['label']) {
767 $fields['label'] = preg_replace('/:$/', '', trim($this->languageService->sL($fC['label'])));
768 switch ($fields['type']) {
769 case 'input':
770 if (preg_match('/int|year/i', $fields['eval'])) {
771 $fields['type'] = 'number';
772 } elseif (preg_match('/time/i', $fields['eval'])) {
773 $fields['type'] = 'time';
774 } elseif (preg_match('/date/i', $fields['eval'])) {
775 $fields['type'] = 'date';
776 } else {
777 $fields['type'] = 'text';
778 }
779 break;
780 case 'check':
781 if (!$fields['items']) {
782 $fields['type'] = 'boolean';
783 } else {
784 $fields['type'] = 'binary';
785 }
786 break;
787 case 'radio':
788 $fields['type'] = 'multiple';
789 break;
790 case 'select':
791 $fields['type'] = 'multiple';
792 if ($fields['foreign_table']) {
793 $fields['type'] = 'relation';
794 }
795 if ($fields['special']) {
796 $fields['type'] = 'text';
797 }
798 break;
799 case 'group':
800 $fields['type'] = 'files';
801 if ($fields['internal_type'] === 'db') {
802 $fields['type'] = 'relation';
803 }
804 break;
805 case 'user':
806 case 'flex':
807 case 'passthrough':
808 case 'none':
809 case 'text':
810 default:
811 $fields['type'] = 'text';
812 }
813 } else {
814 $fields['label'] = '[FIELD: ' . $fieldName . ']';
815 switch ($fieldName) {
816 case 'pid':
817 $fields['type'] = 'relation';
818 $fields['allowed'] = 'pages';
819 break;
820 case 'cruser_id':
821 $fields['type'] = 'relation';
822 $fields['allowed'] = 'be_users';
823 break;
824 case 'tstamp':
825 case 'crdate':
826 $fields['type'] = 'time';
827 break;
828 default:
829 $fields['type'] = 'number';
830 }
831 }
832 }
833 switch ($fields['type']) {
834 case 'date':
835 if ($fieldValue != -1) {
836 $out = strftime('%d-%m-%Y', $fieldValue);
837 }
838 break;
839 case 'time':
840 if ($fieldValue != -1) {
841 if ($splitString === '<br />') {
842 $out = strftime('%H:%M' . $splitString . '%d-%m-%Y', $fieldValue);
843 } else {
844 $out = strftime('%H:%M %d-%m-%Y', $fieldValue);
845 }
846 }
847 break;
848 case 'multiple':
849 case 'binary':
850 case 'relation':
851 $out = $this->makeValueList($fieldName, $fieldValue, $fields, $table, $splitString);
852 break;
853 case 'boolean':
854 $out = $fieldValue ? 'True' : 'False';
855 break;
856 case 'files':
857 default:
858 $out = htmlspecialchars($fieldValue);
859 }
860 return $out;
861 }
862
863 /**
864 * Get tree list
865 *
866 * @param int $id
867 * @param int $depth
868 * @param int $begin
869 * @param string $permsClause
870 *
871 * @return string
872 */
873 public function getTreeList($id, $depth, $begin = 0, $permsClause = null)
874 {
875 $depth = (int)$depth;
876 $begin = (int)$begin;
877 $id = (int)$id;
878 if ($id < 0) {
879 $id = abs($id);
880 }
881 if ($begin == 0) {
882 $theList = $id;
883 } else {
884 $theList = '';
885 }
886 if ($id && $depth > 0) {
887 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
888 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
889 $statement = $queryBuilder->select('uid')
890 ->from('pages')
891 ->where(
892 $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($id, \PDO::PARAM_INT)),
893 QueryHelper::stripLogicalOperatorPrefix($permsClause)
894 )
895 ->execute();
896 while ($row = $statement->fetch()) {
897 if ($begin <= 0) {
898 $theList .= ',' . $row['uid'];
899 }
900 if ($depth > 1) {
901 $theList .= $this->getTreeList($row['uid'], $depth - 1, $begin - 1, $permsClause);
902 }
903 }
904 }
905 return $theList;
906 }
907
908 /**
909 * Make value list
910 *
911 * @param string $fieldName
912 * @param string $fieldValue
913 * @param array $conf
914 * @param string $table
915 * @param string $splitString
916 * @return string
917 */
918 public function makeValueList($fieldName, $fieldValue, $conf, $table, $splitString)
919 {
920 $fieldSetup = $conf;
921 $out = '';
922 if ($fieldSetup['type'] === 'files') {
923 $d = dir(PATH_site . $fieldSetup['uploadfolder']);
924 while (false !== ($entry = $d->read())) {
925 if ($entry === '.' || $entry === '..') {
926 continue;
927 }
928 $fileArray[] = $entry;
929 }
930 $d->close();
931 natcasesort($fileArray);
932 foreach ($fileArray as $fileName) {
933 if (GeneralUtility::inList($fieldValue, $fileName) || $fieldValue == $fileName) {
934 if (!$out) {
935 $out = htmlspecialchars($fileName);
936 } else {
937 $out .= $splitString . htmlspecialchars($fileName);
938 }
939 }
940 }
941 }
942 if ($fieldSetup['type'] === 'multiple') {
943 foreach ($fieldSetup['items'] as $key => $val) {
944 if (substr($val[0], 0, 4) === 'LLL:') {
945 $value = $this->languageService->sL($val[0]);
946 } else {
947 $value = $val[0];
948 }
949 if (GeneralUtility::inList($fieldValue, $val[1]) || $fieldValue == $val[1]) {
950 if (!$out) {
951 $out = htmlspecialchars($value);
952 } else {
953 $out .= $splitString . htmlspecialchars($value);
954 }
955 }
956 }
957 }
958 if ($fieldSetup['type'] === 'binary') {
959 foreach ($fieldSetup['items'] as $Key => $val) {
960 if (substr($val[0], 0, 4) === 'LLL:') {
961 $value = $this->languageService->sL($val[0]);
962 } else {
963 $value = $val[0];
964 }
965 if (!$out) {
966 $out = htmlspecialchars($value);
967 } else {
968 $out .= $splitString . htmlspecialchars($value);
969 }
970 }
971 }
972 if ($fieldSetup['type'] === 'relation') {
973 $dontPrefixFirstTable = 0;
974 $useTablePrefix = 0;
975 if ($fieldSetup['items']) {
976 foreach ($fieldSetup['items'] as $key => $val) {
977 if (substr($val[0], 0, 4) === 'LLL:') {
978 $value = $this->languageService->sL($val[0]);
979 } else {
980 $value = $val[0];
981 }
982 if (GeneralUtility::inList($fieldValue, $value) || $fieldValue == $value) {
983 if (!$out) {
984 $out = htmlspecialchars($value);
985 } else {
986 $out .= $splitString . htmlspecialchars($value);
987 }
988 }
989 }
990 }
991 if (stristr($fieldSetup['allowed'], ',')) {
992 $from_table_Arr = explode(',', $fieldSetup['allowed']);
993 $useTablePrefix = 1;
994 if (!$fieldSetup['prepend_tname']) {
995 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table);
996 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
997 $statement = $queryBuilder->select($fieldName)->from($table)->execute();
998 while ($row = $statement->fetch()) {
999 if (stristr($row[$fieldName], ',')) {
1000 $checkContent = explode(',', $row[$fieldName]);
1001 foreach ($checkContent as $singleValue) {
1002 if (!stristr($singleValue, '_')) {
1003 $dontPrefixFirstTable = 1;
1004 }
1005 }
1006 } else {
1007 $singleValue = $row[$fieldName];
1008 if ($singleValue !== '' && !stristr($singleValue, '_')) {
1009 $dontPrefixFirstTable = 1;
1010 }
1011 }
1012 }
1013 }
1014 } else {
1015 $from_table_Arr[0] = $fieldSetup['allowed'];
1016 }
1017 if ($fieldSetup['prepend_tname']) {
1018 $useTablePrefix = 1;
1019 }
1020 if ($fieldSetup['foreign_table']) {
1021 $from_table_Arr[0] = $fieldSetup['foreign_table'];
1022 }
1023 $counter = 0;
1024 $useSelectLabels = 0;
1025 $useAltSelectLabels = 0;
1026 $tablePrefix = '';
1027 $labelFieldSelect = [];
1028 foreach ($from_table_Arr as $from_table) {
1029 if ($useTablePrefix && !$dontPrefixFirstTable && $counter != 1 || $counter == 1) {
1030 $tablePrefix = $from_table . '_';
1031 }
1032 $counter = 1;
1033 if (is_array($GLOBALS['TCA'][$from_table])) {
1034 $labelField = $GLOBALS['TCA'][$from_table]['ctrl']['label'];
1035 $altLabelField = $GLOBALS['TCA'][$from_table]['ctrl']['label_alt'];
1036 if ($GLOBALS['TCA'][$from_table]['columns'][$labelField]['config']['items']) {
1037 $items = $GLOBALS['TCA'][$from_table]['columns'][$labelField]['config']['items'];
1038 foreach ($items as $labelArray) {
1039 if (substr($labelArray[0], 0, 4) === 'LLL:') {
1040 $labelFieldSelect[$labelArray[1]] = $this->languageService->sL($labelArray[0]);
1041 } else {
1042 $labelFieldSelect[$labelArray[1]] = $labelArray[0];
1043 }
1044 }
1045 $useSelectLabels = 1;
1046 }
1047 $altLabelFieldSelect = [];
1048 if ($GLOBALS['TCA'][$from_table]['columns'][$altLabelField]['config']['items']) {
1049 $items = $GLOBALS['TCA'][$from_table]['columns'][$altLabelField]['config']['items'];
1050 foreach ($items as $altLabelArray) {
1051 if (substr($altLabelArray[0], 0, 4) === 'LLL:') {
1052 $altLabelFieldSelect[$altLabelArray[1]] = $this->languageService->sL($altLabelArray[0]);
1053 } else {
1054 $altLabelFieldSelect[$altLabelArray[1]] = $altLabelArray[0];
1055 }
1056 }
1057 $useAltSelectLabels = 1;
1058 }
1059
1060 if (!$this->tableArray[$from_table]) {
1061 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($from_table);
1062 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class));
1063 $selectFields = ['uid', $labelField];
1064 if ($altLabelField) {
1065 $selectFields[] = $altLabelField;
1066 }
1067 $queryBuilder->select(...$selectFields)
1068 ->from($from_table)
1069 ->orderBy('uid');
1070 if (!$this->backendUserAuthentication->isAdmin() && $GLOBALS['TYPO3_CONF_VARS']['BE']['lockBeUserToDBmounts']) {
1071 $webMounts = $this->backendUserAuthentication->returnWebmounts();
1072 $perms_clause = $this->backendUserAuthentication->getPagePermsClause(1);
1073 $webMountPageTree = '';
1074 $webMountPageTreePrefix = '';
1075 foreach ($webMounts as $webMount) {
1076 if ($webMountPageTree) {
1077 $webMountPageTreePrefix = ',';
1078 }
1079 $webMountPageTree .= $webMountPageTreePrefix
1080 . $this->getTreeList($webMount, 999, ($begin = 0), $perms_clause);
1081 }
1082 if ($from_table === 'pages') {
1083 $queryBuilder->where(
1084 QueryHelper::stripLogicalOperatorPrefix($perms_clause),
1085 $queryBuilder->expr()->in(
1086 'uid',
1087 $queryBuilder->createNamedParameter(
1088 GeneralUtility::intExplode(',', $webMountPageTree),
1089 Connection::PARAM_INT_ARRAY
1090 )
1091 )
1092 );
1093 } else {
1094 $queryBuilder->where(
1095 $queryBuilder->expr()->in(
1096 'pid',
1097 $queryBuilder->createNamedParameter(
1098 GeneralUtility::intExplode(',', $webMountPageTree),
1099 Connection::PARAM_INT_ARRAY
1100 )
1101 )
1102 );
1103 }
1104 }
1105 $statement = $queryBuilder->execute();
1106 $this->tableArray[$from_table] = [];
1107 while ($row = $statement->fetch()) {
1108 $this->tableArray[$from_table][] = $row;
1109 }
1110 }
1111
1112 foreach ($this->tableArray[$from_table] as $key => $val) {
1113 $GLOBALS['SOBE']->MOD_SETTINGS['labels_noprefix'] =
1114 $GLOBALS['SOBE']->MOD_SETTINGS['labels_noprefix'] == 1
1115 ? 'on'
1116 : $GLOBALS['SOBE']->MOD_SETTINGS['labels_noprefix'];
1117 $prefixString =
1118 $GLOBALS['SOBE']->MOD_SETTINGS['labels_noprefix'] === 'on'
1119 ? ''
1120 : ' [' . $tablePrefix . $val['uid'] . '] ';
1121 if (GeneralUtility::inList($fieldValue, $tablePrefix . $val['uid'])
1122 || $fieldValue == $tablePrefix . $val['uid']) {
1123 if ($useSelectLabels) {
1124 if (!$out) {
1125 $out = htmlspecialchars($prefixString . $labelFieldSelect[$val[$labelField]]);
1126 } else {
1127 $out .= $splitString . htmlspecialchars(
1128 $prefixString . $labelFieldSelect[$val[$labelField]]
1129 );
1130 }
1131 } elseif ($val[$labelField]) {
1132 if (!$out) {
1133 $out = htmlspecialchars($prefixString . $val[$labelField]);
1134 } else {
1135 $out .= $splitString . htmlspecialchars(
1136 $prefixString . $val[$labelField]
1137 );
1138 }
1139 } elseif ($useAltSelectLabels) {
1140 if (!$out) {
1141 $out = htmlspecialchars($prefixString . $altLabelFieldSelect[$val[$altLabelField]]);
1142 } else {
1143 $out .= $splitString . htmlspecialchars(
1144 $prefixString . $altLabelFieldSelect[$val[$altLabelField]]
1145 );
1146 }
1147 } else {
1148 if (!$out) {
1149 $out = htmlspecialchars($prefixString . $val[$altLabelField]);
1150 } else {
1151 $out .= $splitString . htmlspecialchars(($prefixString . $val[$altLabelField]));
1152 }
1153 }
1154 }
1155 }
1156 }
1157 }
1158 }
1159 return $out;
1160 }
1161
1162 /**
1163 * Render table header
1164 *
1165 * @param array $row Table columns
1166 * @param array $conf Table TCA
1167 * @param string $table Table name
1168 * @return string HTML of table header
1169 */
1170 public function resultRowTitles($row, $conf, $table)
1171 {
1172 $SET = $GLOBALS['SOBE']->MOD_SETTINGS;
1173 $tableHeader = [];
1174 // Start header row
1175 $tableHeader[] = '<thead><tr>';
1176 // Iterate over given columns
1177 foreach ($row as $fieldName => $fieldValue) {
1178 if (GeneralUtility::inList($SET['queryFields'], $fieldName)
1179 || !$SET['queryFields']
1180 && $fieldName !== 'pid'
1181 && $fieldName !== 'deleted'
1182 ) {
1183 if ($GLOBALS['SOBE']->MOD_SETTINGS['search_result_labels']) {
1184 $title = htmlspecialchars($this->languageService->sL($conf['columns'][$fieldName]['label']
1185 ? $conf['columns'][$fieldName]['label']
1186 : $fieldName));
1187 } else {
1188 $title = htmlspecialchars($this->languageService->sL($fieldName));
1189 }
1190 $tableHeader[] = '<th>' . $title . '</th>';
1191 }
1192 }
1193 // Add empty icon column
1194 $tableHeader[] = '<th></th>';
1195 // Close header row
1196 $tableHeader[] = '</tr></thead>';
1197 return implode(LF, $tableHeader);
1198 }
1199
1200 /**
1201 * CSV row titles
1202 *
1203 * @param array $row
1204 * @param array $conf
1205 * @param mixed $table Not used
1206 * @return string
1207 */
1208 public function csvRowTitles($row, $conf, $table)
1209 {
1210 $out = '';
1211 $SET = $GLOBALS['SOBE']->MOD_SETTINGS;
1212 foreach ($row as $fieldName => $fieldValue) {
1213 if (GeneralUtility::inList($SET['queryFields'], $fieldName)
1214 || !$SET['queryFields'] && $fieldName !== 'pid') {
1215 if (!$out) {
1216 if ($GLOBALS['SOBE']->MOD_SETTINGS['search_result_labels']) {
1217 $out = htmlspecialchars($this->languageService->sL($conf['columns'][$fieldName]['label']
1218 ? $conf['columns'][$fieldName]['label']
1219 : $fieldName));
1220 } else {
1221 $out = htmlspecialchars($this->languageService->sL($fieldName));
1222 }
1223 } else {
1224 if ($GLOBALS['SOBE']->MOD_SETTINGS['search_result_labels']) {
1225 $out .= ',' . htmlspecialchars($this->languageService->sL(($conf['columns'][$fieldName]['label']
1226 ? $conf['columns'][$fieldName]['label']
1227 : $fieldName)));
1228 } else {
1229 $out .= ',' . htmlspecialchars($this->languageService->sL($fieldName));
1230 }
1231 }
1232 }
1233 }
1234 return $out;
1235 }
1236
1237 /**
1238 * Sets the current name of the input form.
1239 *
1240 * @param string $formName The name of the form.
1241 */
1242 public function setFormName($formName)
1243 {
1244 $this->formName = trim($formName);
1245 }
1246 }