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