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