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