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