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