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