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