1<?php 2 3/* 4 * This file is part of the TYPO3 CMS project. 5 * 6 * It is free software; you can redistribute it and/or modify it under 7 * the terms of the GNU General Public License, either version 2 8 * of the License, or any later version. 9 * 10 * For the full copyright and license information, please read the 11 * LICENSE.txt file that was distributed with this source code. 12 * 13 * The TYPO3 project - inspiring people to share! 14 */ 15 16namespace TYPO3\CMS\Lowlevel\Database; 17 18use Doctrine\DBAL\Exception as DBALException; 19use TYPO3\CMS\Backend\Routing\UriBuilder; 20use TYPO3\CMS\Backend\Utility\BackendUtility; 21use TYPO3\CMS\Core\Authentication\BackendUserAuthentication; 22use TYPO3\CMS\Core\Database\Connection; 23use TYPO3\CMS\Core\Database\ConnectionPool; 24use TYPO3\CMS\Core\Database\Query\QueryHelper; 25use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction; 26use TYPO3\CMS\Core\Imaging\Icon; 27use TYPO3\CMS\Core\Imaging\IconFactory; 28use TYPO3\CMS\Core\Localization\LanguageService; 29use TYPO3\CMS\Core\Messaging\FlashMessage; 30use TYPO3\CMS\Core\Messaging\FlashMessageRendererResolver; 31use TYPO3\CMS\Core\Messaging\FlashMessageService; 32use TYPO3\CMS\Core\Type\Bitmask\Permission; 33use TYPO3\CMS\Core\Utility\CsvUtility; 34use TYPO3\CMS\Core\Utility\DebugUtility; 35use TYPO3\CMS\Core\Utility\ExtensionManagementUtility; 36use TYPO3\CMS\Core\Utility\GeneralUtility; 37use TYPO3\CMS\Core\Utility\HttpUtility; 38use TYPO3\CMS\Core\Utility\MathUtility; 39use TYPO3\CMS\Core\Utility\StringUtility; 40 41/** 42 * Class used in module tools/dbint (advanced search) and which may hold code specific for that module 43 * 44 * @internal This class is a specific implementation for the lowlevel extension and is not part of the TYPO3's Core API. 45 */ 46class QueryGenerator 47{ 48 /** 49 * @var string 50 */ 51 protected $storeList = 'search_query_smallparts,search_result_labels,labels_noprefix,show_deleted,queryConfig,queryTable,queryFields,queryLimit,queryOrder,queryOrderDesc,queryOrder2,queryOrder2Desc,queryGroup,search_query_makeQuery'; 52 53 /** 54 * @var int 55 */ 56 protected $noDownloadB = 0; 57 58 /** 59 * @var array 60 */ 61 protected $hookArray = []; 62 63 /** 64 * @var string 65 */ 66 protected $formName = ''; 67 68 /** 69 * @var IconFactory 70 */ 71 protected $iconFactory; 72 73 /** 74 * @var array 75 */ 76 protected $tableArray = []; 77 78 /** 79 * @var array Settings, usually from the controller 80 */ 81 protected $settings = []; 82 83 /** 84 * @var array information on the menu of this module 85 */ 86 protected $menuItems = []; 87 88 /** 89 * @var string 90 */ 91 protected $moduleName; 92 93 /** 94 * @var array 95 */ 96 protected $lang = [ 97 'OR' => 'or', 98 'AND' => 'and', 99 'comparison' => [ 100 // Type = text offset = 0 101 '0_' => 'contains', 102 '1_' => 'does not contain', 103 '2_' => 'starts with', 104 '3_' => 'does not start with', 105 '4_' => 'ends with', 106 '5_' => 'does not end with', 107 '6_' => 'equals', 108 '7_' => 'does not equal', 109 // Type = number , offset = 32 110 '32_' => 'equals', 111 '33_' => 'does not equal', 112 '34_' => 'is greater than', 113 '35_' => 'is less than', 114 '36_' => 'is between', 115 '37_' => 'is not between', 116 '38_' => 'is in list', 117 '39_' => 'is not in list', 118 '40_' => 'binary AND equals', 119 '41_' => 'binary AND does not equal', 120 '42_' => 'binary OR equals', 121 '43_' => 'binary OR does not equal', 122 // Type = multiple, relation, offset = 64 123 '64_' => 'equals', 124 '65_' => 'does not equal', 125 '66_' => 'contains', 126 '67_' => 'does not contain', 127 '68_' => 'is in list', 128 '69_' => 'is not in list', 129 '70_' => 'binary AND equals', 130 '71_' => 'binary AND does not equal', 131 '72_' => 'binary OR equals', 132 '73_' => 'binary OR does not equal', 133 // Type = date,time offset = 96 134 '96_' => 'equals', 135 '97_' => 'does not equal', 136 '98_' => 'is greater than', 137 '99_' => 'is less than', 138 '100_' => 'is between', 139 '101_' => 'is not between', 140 '102_' => 'binary AND equals', 141 '103_' => 'binary AND does not equal', 142 '104_' => 'binary OR equals', 143 '105_' => 'binary OR does not equal', 144 // Type = boolean, offset = 128 145 '128_' => 'is True', 146 '129_' => 'is False', 147 // Type = binary , offset = 160 148 '160_' => 'equals', 149 '161_' => 'does not equal', 150 '162_' => 'contains', 151 '163_' => 'does not contain', 152 ], 153 ]; 154 155 /** 156 * @var array 157 */ 158 protected $compSQL = [ 159 // Type = text offset = 0 160 '0' => '#FIELD# LIKE \'%#VALUE#%\'', 161 '1' => '#FIELD# NOT LIKE \'%#VALUE#%\'', 162 '2' => '#FIELD# LIKE \'#VALUE#%\'', 163 '3' => '#FIELD# NOT LIKE \'#VALUE#%\'', 164 '4' => '#FIELD# LIKE \'%#VALUE#\'', 165 '5' => '#FIELD# NOT LIKE \'%#VALUE#\'', 166 '6' => '#FIELD# = \'#VALUE#\'', 167 '7' => '#FIELD# != \'#VALUE#\'', 168 // Type = number, offset = 32 169 '32' => '#FIELD# = \'#VALUE#\'', 170 '33' => '#FIELD# != \'#VALUE#\'', 171 '34' => '#FIELD# > #VALUE#', 172 '35' => '#FIELD# < #VALUE#', 173 '36' => '#FIELD# >= #VALUE# AND #FIELD# <= #VALUE1#', 174 '37' => 'NOT (#FIELD# >= #VALUE# AND #FIELD# <= #VALUE1#)', 175 '38' => '#FIELD# IN (#VALUE#)', 176 '39' => '#FIELD# NOT IN (#VALUE#)', 177 '40' => '(#FIELD# & #VALUE#)=#VALUE#', 178 '41' => '(#FIELD# & #VALUE#)!=#VALUE#', 179 '42' => '(#FIELD# | #VALUE#)=#VALUE#', 180 '43' => '(#FIELD# | #VALUE#)!=#VALUE#', 181 // Type = multiple, relation, offset = 64 182 '64' => '#FIELD# = \'#VALUE#\'', 183 '65' => '#FIELD# != \'#VALUE#\'', 184 '66' => '#FIELD# LIKE \'%#VALUE#%\' AND #FIELD# LIKE \'%#VALUE1#%\'', 185 '67' => '(#FIELD# NOT LIKE \'%#VALUE#%\' OR #FIELD# NOT LIKE \'%#VALUE1#%\')', 186 '68' => '#FIELD# IN (#VALUE#)', 187 '69' => '#FIELD# NOT IN (#VALUE#)', 188 '70' => '(#FIELD# & #VALUE#)=#VALUE#', 189 '71' => '(#FIELD# & #VALUE#)!=#VALUE#', 190 '72' => '(#FIELD# | #VALUE#)=#VALUE#', 191 '73' => '(#FIELD# | #VALUE#)!=#VALUE#', 192 // Type = date, offset = 32 193 '96' => '#FIELD# = \'#VALUE#\'', 194 '97' => '#FIELD# != \'#VALUE#\'', 195 '98' => '#FIELD# > #VALUE#', 196 '99' => '#FIELD# < #VALUE#', 197 '100' => '#FIELD# >= #VALUE# AND #FIELD# <= #VALUE1#', 198 '101' => 'NOT (#FIELD# >= #VALUE# AND #FIELD# <= #VALUE1#)', 199 '102' => '(#FIELD# & #VALUE#)=#VALUE#', 200 '103' => '(#FIELD# & #VALUE#)!=#VALUE#', 201 '104' => '(#FIELD# | #VALUE#)=#VALUE#', 202 '105' => '(#FIELD# | #VALUE#)!=#VALUE#', 203 // Type = boolean, offset = 128 204 '128' => '#FIELD# = \'1\'', 205 '129' => '#FIELD# != \'1\'', 206 // Type = binary = 160 207 '160' => '#FIELD# = \'#VALUE#\'', 208 '161' => '#FIELD# != \'#VALUE#\'', 209 '162' => '(#FIELD# & #VALUE#)=#VALUE#', 210 '163' => '(#FIELD# & #VALUE#)=0', 211 ]; 212 213 /** 214 * @var array 215 */ 216 protected $comp_offsets = [ 217 'text' => 0, 218 'number' => 1, 219 'multiple' => 2, 220 'relation' => 2, 221 'date' => 3, 222 'time' => 3, 223 'boolean' => 4, 224 'binary' => 5, 225 ]; 226 227 /** 228 * Form data name prefix 229 * 230 * @var string 231 */ 232 protected $name; 233 234 /** 235 * Table for the query 236 * 237 * @var string 238 */ 239 protected $table; 240 241 /** 242 * Field list 243 * 244 * @var string 245 */ 246 protected $fieldList; 247 248 /** 249 * Array of the fields possible 250 * 251 * @var array 252 */ 253 protected $fields = []; 254 255 /** 256 * @var array 257 */ 258 protected $extFieldLists = []; 259 260 /** 261 * The query config 262 * 263 * @var array 264 */ 265 protected $queryConfig = []; 266 267 /** 268 * @var bool 269 */ 270 protected $enablePrefix = false; 271 272 /** 273 * @var bool 274 */ 275 protected $enableQueryParts = false; 276 277 /** 278 * @var string 279 */ 280 protected $fieldName; 281 282 /** 283 * If the current user is an admin and $GLOBALS['TYPO3_CONF_VARS']['BE']['debug'] 284 * is set to true, the names of fields and tables are displayed. 285 * 286 * @var bool 287 */ 288 protected $showFieldAndTableNames = false; 289 290 public function __construct(array $settings, array $menuItems, string $moduleName) 291 { 292 $this->getLanguageService()->includeLLFile('EXT:core/Resources/Private/Language/locallang_t3lib_fullsearch.xlf'); 293 $this->iconFactory = GeneralUtility::makeInstance(IconFactory::class); 294 $this->settings = $settings; 295 $this->menuItems = $menuItems; 296 $this->moduleName = $moduleName; 297 $this->showFieldAndTableNames = ($GLOBALS['TYPO3_CONF_VARS']['BE']['debug'] ?? false) 298 && $this->getBackendUserAuthentication()->isAdmin(); 299 } 300 301 /** 302 * Get form 303 * 304 * @return string 305 */ 306 public function form() 307 { 308 $markup = []; 309 $markup[] = '<div class="form-group">'; 310 $markup[] = '<input placeholder="Search Word" class="form-control" type="search" name="SET[sword]" value="' 311 . htmlspecialchars($this->settings['sword'] ?? '') . '">'; 312 $markup[] = '</div>'; 313 $markup[] = '<div class="form-group">'; 314 $markup[] = '<input class="btn btn-default" type="submit" name="submit" value="Search All Records">'; 315 $markup[] = '</div>'; 316 return implode(LF, $markup); 317 } 318 319 /** 320 * Query marker 321 * 322 * @return string 323 */ 324 public function queryMaker() 325 { 326 $output = ''; 327 $this->hookArray = $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['t3lib_fullsearch'] ?? []; 328 $msg = $this->procesStoreControl(); 329 $userTsConfig = $this->getBackendUserAuthentication()->getTSConfig(); 330 if (!($userTsConfig['mod.']['dbint.']['disableStoreControl'] ?? false)) { 331 $output .= '<h2>Load/Save Query</h2>'; 332 $output .= '<div>' . $this->makeStoreControl() . '</div>'; 333 $output .= $msg; 334 } 335 // Query Maker: 336 $this->init('queryConfig', $this->settings['queryTable'] ?? '', '', $this->settings); 337 if ($this->formName) { 338 $this->setFormName($this->formName); 339 } 340 $tmpCode = $this->makeSelectorTable($this->settings); 341 $output .= '<div id="query"></div><h2>Make query</h2><div>' . $tmpCode . '</div>'; 342 $mQ = $this->settings['search_query_makeQuery']; 343 // Make form elements: 344 if ($this->table && is_array($GLOBALS['TCA'][$this->table])) { 345 if ($mQ) { 346 // Show query 347 $this->enablePrefix = true; 348 $queryString = $this->getQuery($this->queryConfig); 349 $selectQueryString = $this->getSelectQuery($queryString); 350 $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->table); 351 352 $isConnectionMysql = strpos($connection->getServerVersion(), 'MySQL') === 0; 353 $fullQueryString = ''; 354 try { 355 if ($mQ === 'explain' && $isConnectionMysql) { 356 // EXPLAIN is no ANSI SQL, for now this is only executed on mysql 357 // @todo: Move away from getSelectQuery() or model differently 358 $fullQueryString = 'EXPLAIN ' . $selectQueryString; 359 $dataRows = $connection->executeQuery('EXPLAIN ' . $selectQueryString)->fetchAllAssociative(); 360 } elseif ($mQ === 'count') { 361 $queryBuilder = $connection->createQueryBuilder(); 362 $queryBuilder->getRestrictions()->removeAll(); 363 if (empty($this->settings['show_deleted'])) { 364 $queryBuilder->getRestrictions()->add(GeneralUtility::makeInstance(DeletedRestriction::class)); 365 } 366 $queryBuilder->count('*') 367 ->from($this->table) 368 ->where(QueryHelper::stripLogicalOperatorPrefix($queryString)); 369 $fullQueryString = $queryBuilder->getSQL(); 370 $dataRows = [$queryBuilder->executeQuery()->fetchOne()]; 371 } else { 372 $fullQueryString = $selectQueryString; 373 $dataRows = $connection->executeQuery($selectQueryString)->fetchAllAssociative(); 374 } 375 if (!($userTsConfig['mod.']['dbint.']['disableShowSQLQuery'] ?? false)) { 376 $output .= '<h2>SQL query</h2><div><code>' . htmlspecialchars($fullQueryString) . '</code></div>'; 377 } 378 $cPR = $this->getQueryResultCode($mQ, $dataRows, $this->table); 379 $output .= '<h2>' . ($cPR['header'] ?? '') . '</h2><div>' . $cPR['content'] . '</div>'; 380 } catch (DBALException $e) { 381 if (!($userTsConfig['mod.']['dbint.']['disableShowSQLQuery'] ?? false)) { 382 $output .= '<h2>SQL query</h2><div><code>' . htmlspecialchars($fullQueryString) . '</code></div>'; 383 } 384 $out = '<p><strong>Error: <span class="text-danger">' 385 . htmlspecialchars($e->getMessage()) 386 . '</span></strong></p>'; 387 $output .= '<h2>SQL error</h2><div>' . $out . '</div>'; 388 } 389 } 390 } 391 return '<div class="database-query-builder">' . $output . '</div>'; 392 } 393 394 /** 395 * Search 396 * 397 * @return string 398 */ 399 public function search() 400 { 401 $swords = $this->settings['sword'] ?? ''; 402 $out = ''; 403 if ($swords) { 404 foreach ($GLOBALS['TCA'] as $table => $value) { 405 // Get fields list 406 $conf = $GLOBALS['TCA'][$table]; 407 // Avoid querying tables with no columns 408 if (empty($conf['columns'])) { 409 continue; 410 } 411 $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($table); 412 $tableColumns = $connection->createSchemaManager()->listTableColumns($table); 413 $fieldsInDatabase = []; 414 foreach ($tableColumns as $column) { 415 $fieldsInDatabase[] = $column->getName(); 416 } 417 $fields = array_intersect(array_keys($conf['columns']), $fieldsInDatabase); 418 419 $queryBuilder = $connection->createQueryBuilder(); 420 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class)); 421 $queryBuilder->count('*')->from($table); 422 $likes = []; 423 $escapedLikeString = '%' . $queryBuilder->escapeLikeWildcards($swords) . '%'; 424 foreach ($fields as $field) { 425 $likes[] = $queryBuilder->expr()->like( 426 $field, 427 $queryBuilder->createNamedParameter($escapedLikeString, \PDO::PARAM_STR) 428 ); 429 } 430 $count = $queryBuilder->orWhere(...$likes)->executeQuery()->fetchOne(); 431 432 if ($count > 0) { 433 $queryBuilder = $connection->createQueryBuilder(); 434 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class)); 435 $queryBuilder->select('uid', $conf['ctrl']['label']) 436 ->from($table) 437 ->setMaxResults(200); 438 $likes = []; 439 foreach ($fields as $field) { 440 $likes[] = $queryBuilder->expr()->like( 441 $field, 442 $queryBuilder->createNamedParameter($escapedLikeString, \PDO::PARAM_STR) 443 ); 444 } 445 $statement = $queryBuilder->orWhere(...$likes)->executeQuery(); 446 $lastRow = null; 447 $rowArr = []; 448 while ($row = $statement->fetchAssociative()) { 449 $rowArr[] = $this->resultRowDisplay($row, $conf, $table); 450 $lastRow = $row; 451 } 452 $markup = []; 453 $markup[] = '<div class="panel panel-default">'; 454 $markup[] = ' <div class="panel-heading">'; 455 $markup[] = htmlspecialchars($this->getLanguageService()->sL($conf['ctrl']['title'])) . ' (' . $count . ')'; 456 $markup[] = ' </div>'; 457 $markup[] = ' <table class="table table-striped table-hover">'; 458 $markup[] = $this->resultRowTitles((array)$lastRow, $conf); 459 $markup[] = implode(LF, $rowArr); 460 $markup[] = ' </table>'; 461 $markup[] = '</div>'; 462 463 $out .= implode(LF, $markup); 464 } 465 } 466 } 467 return $out; 468 } 469 470 /** 471 * Sets the current name of the input form. 472 * 473 * @param string $formName The name of the form. 474 */ 475 public function setFormName($formName) 476 { 477 $this->formName = trim($formName); 478 } 479 480 /** 481 * Make store control 482 * 483 * @return string 484 */ 485 protected function makeStoreControl() 486 { 487 // Load/Save 488 $storeArray = $this->initStoreArray(); 489 490 $opt = []; 491 foreach ($storeArray as $k => $v) { 492 $opt[] = '<option value="' . htmlspecialchars($k) . '">' . htmlspecialchars($v) . '</option>'; 493 } 494 // Actions: 495 if (ExtensionManagementUtility::isLoaded('sys_action') && $this->getBackendUserAuthentication()->isAdmin()) { 496 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_action'); 497 $queryBuilder->getRestrictions()->removeAll(); 498 $statement = $queryBuilder->select('uid', 'title') 499 ->from('sys_action') 500 ->where($queryBuilder->expr()->eq('type', $queryBuilder->createNamedParameter(2, \PDO::PARAM_INT))) 501 ->orderBy('title') 502 ->executeQuery(); 503 $opt[] = '<option value="0">__Save to Action:__</option>'; 504 while ($row = $statement->fetchAssociative()) { 505 $opt[] = '<option value="-' . (int)$row['uid'] . '">' . htmlspecialchars($row['title'] 506 . ' [' . (int)$row['uid'] . ']') . '</option>'; 507 } 508 } 509 $markup = []; 510 $markup[] = '<div class="load-queries">'; 511 $markup[] = ' <div class="row row-cols-auto">'; 512 $markup[] = ' <div class="col">'; 513 $markup[] = ' <select class="form-select" name="storeControl[STORE]" data-assign-store-control-title>' . implode(LF, $opt) . '</select>'; 514 $markup[] = ' </div>'; 515 $markup[] = ' <div class="col">'; 516 $markup[] = ' <input class="form-control" name="storeControl[title]" value="" type="text" max="80">'; 517 $markup[] = ' </div>'; 518 $markup[] = ' <div class="col">'; 519 $markup[] = ' <input class="btn btn-default" type="submit" name="storeControl[LOAD]" value="Load">'; 520 $markup[] = ' </div>'; 521 $markup[] = ' <div class="col">'; 522 $markup[] = ' <input class="btn btn-default" type="submit" name="storeControl[SAVE]" value="Save">'; 523 $markup[] = ' </div>'; 524 $markup[] = ' <div class="col">'; 525 $markup[] = ' <input class="btn btn-default" type="submit" name="storeControl[REMOVE]" value="Remove">'; 526 $markup[] = ' </div>'; 527 $markup[] = ' </div>'; 528 $markup[] = '</div>'; 529 530 return implode(LF, $markup); 531 } 532 533 /** 534 * Init store array 535 * 536 * @return array 537 */ 538 protected function initStoreArray() 539 { 540 $storeArray = [ 541 '0' => '[New]', 542 ]; 543 $savedStoreArray = unserialize($this->settings['storeArray'] ?? '', ['allowed_classes' => false]); 544 if (is_array($savedStoreArray)) { 545 $storeArray = array_merge($storeArray, $savedStoreArray); 546 } 547 return $storeArray; 548 } 549 550 /** 551 * Clean store query configs 552 * 553 * @param array $storeQueryConfigs 554 * @param array $storeArray 555 * @return array 556 */ 557 protected function cleanStoreQueryConfigs($storeQueryConfigs, $storeArray) 558 { 559 if (is_array($storeQueryConfigs)) { 560 foreach ($storeQueryConfigs as $k => $v) { 561 if (!isset($storeArray[$k])) { 562 unset($storeQueryConfigs[$k]); 563 } 564 } 565 } 566 return $storeQueryConfigs; 567 } 568 569 /** 570 * Add to store query configs 571 * 572 * @param array $storeQueryConfigs 573 * @param int $index 574 * @return array 575 */ 576 protected function addToStoreQueryConfigs($storeQueryConfigs, $index) 577 { 578 $keyArr = explode(',', $this->storeList); 579 $storeQueryConfigs[$index] = []; 580 foreach ($keyArr as $k) { 581 $storeQueryConfigs[$index][$k] = $this->settings[$k] ?? null; 582 } 583 return $storeQueryConfigs; 584 } 585 586 /** 587 * Save query in action 588 * 589 * @param int $uid 590 * @return bool 591 */ 592 protected function saveQueryInAction($uid) 593 { 594 if (ExtensionManagementUtility::isLoaded('sys_action')) { 595 $keyArr = explode(',', $this->storeList); 596 $saveArr = []; 597 foreach ($keyArr as $k) { 598 $saveArr[$k] = $this->settings[$k]; 599 } 600 // Show query 601 if ($saveArr['queryTable']) { 602 $this->init('queryConfig', $saveArr['queryTable'], '', $this->settings); 603 $this->makeSelectorTable($saveArr); 604 $this->enablePrefix = true; 605 $queryString = $this->getQuery($this->queryConfig); 606 607 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class) 608 ->getQueryBuilderForTable($this->table); 609 $queryBuilder->getRestrictions()->removeAll() 610 ->add(GeneralUtility::makeInstance(DeletedRestriction::class)); 611 $rowCount = $queryBuilder->count('*') 612 ->from($this->table) 613 ->where(QueryHelper::stripLogicalOperatorPrefix($queryString)) 614 ->executeQuery() 615 ->fetchOne(); 616 617 $t2DataValue = [ 618 'qC' => $saveArr, 619 'qCount' => $rowCount, 620 'qSelect' => $this->getSelectQuery($queryString), 621 'qString' => $queryString, 622 ]; 623 GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('sys_action') 624 ->update( 625 'sys_action', 626 ['t2_data' => serialize($t2DataValue)], 627 ['uid' => (int)$uid], 628 ['t2_data' => Connection::PARAM_LOB] 629 ); 630 } 631 return true; 632 } 633 return false; 634 } 635 /** 636 * Load store query configs 637 * 638 * @param array $storeQueryConfigs 639 * @param int $storeIndex 640 * @param array $writeArray 641 * @return array 642 */ 643 protected function loadStoreQueryConfigs($storeQueryConfigs, $storeIndex, $writeArray) 644 { 645 if ($storeQueryConfigs[$storeIndex]) { 646 $keyArr = explode(',', $this->storeList); 647 foreach ($keyArr as $k) { 648 $writeArray[$k] = $storeQueryConfigs[$storeIndex][$k]; 649 } 650 } 651 return $writeArray; 652 } 653 654 /** 655 * Process store control 656 * 657 * @return string 658 */ 659 protected function procesStoreControl() 660 { 661 $languageService = $this->getLanguageService(); 662 $flashMessage = null; 663 $storeArray = $this->initStoreArray(); 664 $storeQueryConfigs = unserialize($this->settings['storeQueryConfigs'] ?? '', ['allowed_classes' => false]); 665 $storeControl = GeneralUtility::_GP('storeControl'); 666 $storeIndex = (int)($storeControl['STORE'] ?? 0); 667 $saveStoreArray = 0; 668 $writeArray = []; 669 $msg = ''; 670 if (is_array($storeControl)) { 671 if ($storeControl['LOAD'] ?? false) { 672 if ($storeIndex > 0) { 673 $writeArray = $this->loadStoreQueryConfigs($storeQueryConfigs, $storeIndex, $writeArray); 674 $saveStoreArray = 1; 675 $flashMessage = GeneralUtility::makeInstance( 676 FlashMessage::class, 677 sprintf($languageService->getLL('query_loaded'), $storeArray[$storeIndex]) 678 ); 679 } elseif ($storeIndex < 0 && ExtensionManagementUtility::isLoaded('sys_action')) { 680 $actionRecord = BackendUtility::getRecord('sys_action', abs($storeIndex)); 681 if (is_array($actionRecord)) { 682 $dA = unserialize($actionRecord['t2_data'], ['allowed_classes' => false]); 683 $dbSC = []; 684 if (is_array($dA['qC'])) { 685 $dbSC[0] = $dA['qC']; 686 } 687 $writeArray = $this->loadStoreQueryConfigs($dbSC, 0, $writeArray); 688 $saveStoreArray = 1; 689 $flashMessage = GeneralUtility::makeInstance( 690 FlashMessage::class, 691 sprintf($languageService->getLL('query_from_action_loaded'), $actionRecord['title']) 692 ); 693 } 694 } 695 } elseif ($storeControl['SAVE'] ?? false) { 696 if ($storeIndex < 0) { 697 $qOK = $this->saveQueryInAction(abs($storeIndex)); 698 if ($qOK) { 699 $flashMessage = GeneralUtility::makeInstance( 700 FlashMessage::class, 701 $languageService->getLL('query_saved') 702 ); 703 } else { 704 $flashMessage = GeneralUtility::makeInstance( 705 FlashMessage::class, 706 $languageService->getLL('query_notsaved'), 707 '', 708 FlashMessage::ERROR 709 ); 710 } 711 } else { 712 if (trim($storeControl['title'])) { 713 if ($storeIndex > 0) { 714 $storeArray[$storeIndex] = $storeControl['title']; 715 } else { 716 $storeArray[] = $storeControl['title']; 717 end($storeArray); 718 $storeIndex = key($storeArray); 719 } 720 $storeQueryConfigs = $this->addToStoreQueryConfigs($storeQueryConfigs, (int)$storeIndex); 721 $saveStoreArray = 1; 722 $flashMessage = GeneralUtility::makeInstance( 723 FlashMessage::class, 724 $languageService->getLL('query_saved') 725 ); 726 } 727 } 728 } elseif ($storeControl['REMOVE'] ?? false) { 729 if ($storeIndex > 0) { 730 $flashMessage = GeneralUtility::makeInstance( 731 FlashMessage::class, 732 sprintf($languageService->getLL('query_removed'), $storeArray[$storeControl['STORE']]) 733 ); 734 // Removing 735 unset($storeArray[$storeControl['STORE']]); 736 $saveStoreArray = 1; 737 } 738 } 739 if (!empty($flashMessage)) { 740 $msg = GeneralUtility::makeInstance(FlashMessageRendererResolver::class) 741 ->resolve() 742 ->render([$flashMessage]); 743 } 744 } 745 if ($saveStoreArray) { 746 // Making sure, index 0 is not set! 747 unset($storeArray[0]); 748 $writeArray['storeArray'] = serialize($storeArray); 749 $writeArray['storeQueryConfigs'] = 750 serialize($this->cleanStoreQueryConfigs($storeQueryConfigs, $storeArray)); 751 $this->settings = BackendUtility::getModuleData( 752 $this->menuItems, 753 $writeArray, 754 $this->moduleName, 755 'ses' 756 ); 757 } 758 return $msg; 759 } 760 761 /** 762 * Get query result code 763 * 764 * @param string $type 765 * @param array $dataRows Rows to display 766 * @param string $table 767 * @return array HTML-code for "header" and "content" 768 * @throws \TYPO3\CMS\Core\Exception 769 */ 770 protected function getQueryResultCode($type, array $dataRows, $table) 771 { 772 $out = ''; 773 $cPR = []; 774 switch ($type) { 775 case 'count': 776 $cPR['header'] = 'Count'; 777 $cPR['content'] = '<br><strong>' . (int)$dataRows[0] . '</strong> records selected.'; 778 break; 779 case 'all': 780 $rowArr = []; 781 $dataRow = null; 782 foreach ($dataRows as $dataRow) { 783 $rowArr[] = $this->resultRowDisplay($dataRow, $GLOBALS['TCA'][$table], $table); 784 } 785 if (is_array($this->hookArray['beforeResultTable'] ?? false)) { 786 foreach ($this->hookArray['beforeResultTable'] as $_funcRef) { 787 $out .= GeneralUtility::callUserFunction($_funcRef, $this->settings); 788 } 789 } 790 if (!empty($rowArr)) { 791 $cPR['header'] = 'Result'; 792 $out .= '<table class="table table-striped table-hover">' 793 . $this->resultRowTitles((array)$dataRow, $GLOBALS['TCA'][$table]) . implode(LF, $rowArr) 794 . '</table>'; 795 } else { 796 $this->renderNoResultsFoundMessage(); 797 } 798 799 $cPR['content'] = $out; 800 break; 801 case 'csv': 802 $rowArr = []; 803 $first = 1; 804 foreach ($dataRows as $dataRow) { 805 if ($first) { 806 $rowArr[] = $this->csvValues(array_keys($dataRow)); 807 $first = 0; 808 } 809 $rowArr[] = $this->csvValues($dataRow, ',', '"', $GLOBALS['TCA'][$table], $table); 810 } 811 if (!empty($rowArr)) { 812 $cPR['header'] = 'Result'; 813 $out .= '<textarea name="whatever" rows="20" class="text-monospace" style="width:100%">' 814 . htmlspecialchars(implode(LF, $rowArr)) 815 . '</textarea>'; 816 if (!$this->noDownloadB) { 817 $out .= '<br><input class="btn btn-default" type="submit" name="download_file" ' 818 . 'value="Click to download file">'; 819 } 820 // Downloads file: 821 // @todo: args. routing anyone? 822 if (GeneralUtility::_GP('download_file')) { 823 $filename = 'TYPO3_' . $table . '_export_' . date('dmy-Hi') . '.csv'; 824 $mimeType = 'application/octet-stream'; 825 header('Content-Type: ' . $mimeType); 826 header('Content-Disposition: attachment; filename=' . $filename); 827 echo implode(CRLF, $rowArr); 828 die; 829 } 830 } else { 831 $this->renderNoResultsFoundMessage(); 832 } 833 $cPR['content'] = $out; 834 break; 835 case 'explain': 836 default: 837 foreach ($dataRows as $dataRow) { 838 $out .= '<br />' . DebugUtility::viewArray($dataRow); 839 } 840 $cPR['header'] = 'Explain SQL query'; 841 $cPR['content'] = $out; 842 } 843 return $cPR; 844 } 845 /** 846 * CSV values 847 * 848 * @param array $row 849 * @param string $delim 850 * @param string $quote 851 * @param array $conf 852 * @param string $table 853 * @return string A single line of CSV 854 */ 855 protected function csvValues($row, $delim = ',', $quote = '"', $conf = [], $table = '') 856 { 857 $valueArray = $row; 858 if (($this->settings['search_result_labels'] ?? false) && $table) { 859 foreach ($valueArray as $key => $val) { 860 $valueArray[$key] = $this->getProcessedValueExtra($table, $key, $val, $conf, ';'); 861 } 862 } 863 return CsvUtility::csvValues($valueArray, $delim, $quote); 864 } 865 866 /** 867 * Result row display 868 * 869 * @param array $row 870 * @param array $conf 871 * @param string $table 872 * @return string 873 */ 874 protected function resultRowDisplay($row, $conf, $table) 875 { 876 $languageService = $this->getLanguageService(); 877 $out = '<tr>'; 878 foreach ($row as $fieldName => $fieldValue) { 879 if (GeneralUtility::inList($this->settings['queryFields'] ?? '', $fieldName) 880 || !($this->settings['queryFields'] ?? false) 881 && $fieldName !== 'pid' 882 && $fieldName !== 'deleted' 883 ) { 884 if ($this->settings['search_result_labels'] ?? false) { 885 $fVnew = $this->getProcessedValueExtra($table, $fieldName, $fieldValue, $conf, '<br />'); 886 } else { 887 $fVnew = htmlspecialchars($fieldValue); 888 } 889 $out .= '<td>' . $fVnew . '</td>'; 890 } 891 } 892 $out .= '<td>'; 893 $uriBuilder = GeneralUtility::makeInstance(UriBuilder::class); 894 895 if (!($row['deleted'] ?? false)) { 896 $out .= '<div class="btn-group" role="group">'; 897 $url = (string)$uriBuilder->buildUriFromRoute('record_edit', [ 898 'edit' => [ 899 $table => [ 900 $row['uid'] => 'edit', 901 ], 902 ], 903 'returnUrl' => $GLOBALS['TYPO3_REQUEST']->getAttribute('normalizedParams')->getRequestUri() 904 . HttpUtility::buildQueryString(['SET' => (array)GeneralUtility::_POST('SET')], '&'), 905 ]); 906 $out .= '<a class="btn btn-default" href="' . htmlspecialchars($url) . '">' 907 . $this->iconFactory->getIcon('actions-open', Icon::SIZE_SMALL)->render() . '</a>'; 908 $out .= '</div><div class="btn-group" role="group">'; 909 $out .= sprintf( 910 '<a class="btn btn-default" href="#" data-dispatch-action="%s" data-dispatch-args-list="%s">%s</a>', 911 'TYPO3.InfoWindow.showItem', 912 htmlspecialchars($table . ',' . $row['uid']), 913 $this->iconFactory->getIcon('actions-document-info', Icon::SIZE_SMALL)->render() 914 ); 915 $out .= '</div>'; 916 } else { 917 $out .= '<div class="btn-group" role="group">'; 918 $out .= '<a class="btn btn-default" href="' . htmlspecialchars((string)$uriBuilder->buildUriFromRoute('tce_db', [ 919 'cmd' => [ 920 $table => [ 921 $row['uid'] => [ 922 'undelete' => 1, 923 ], 924 ], 925 ], 926 'redirect' => GeneralUtility::linkThisScript(), 927 ])) . '" title="' . htmlspecialchars($languageService->getLL('undelete_only')) . '">'; 928 $out .= $this->iconFactory->getIcon('actions-edit-restore', Icon::SIZE_SMALL)->render() . '</a>'; 929 $formEngineParameters = [ 930 'edit' => [ 931 $table => [ 932 $row['uid'] => 'edit', 933 ], 934 ], 935 'returnUrl' => GeneralUtility::linkThisScript(), 936 ]; 937 $redirectUrl = (string)$uriBuilder->buildUriFromRoute('record_edit', $formEngineParameters); 938 $out .= '<a class="btn btn-default" href="' . htmlspecialchars((string)$uriBuilder->buildUriFromRoute('tce_db', [ 939 'cmd' => [ 940 $table => [ 941 $row['uid'] => [ 942 'undelete' => 1, 943 ], 944 ], 945 ], 946 'redirect' => $redirectUrl, 947 ])) . '" title="' . htmlspecialchars($languageService->getLL('undelete_and_edit')) . '">'; 948 $out .= $this->iconFactory->getIcon('actions-delete-edit', Icon::SIZE_SMALL)->render() . '</a>'; 949 $out .= '</div>'; 950 } 951 $_params = [$table => $row]; 952 if (is_array($this->hookArray['additionalButtons'] ?? false)) { 953 foreach ($this->hookArray['additionalButtons'] as $_funcRef) { 954 $out .= GeneralUtility::callUserFunction($_funcRef, $_params); 955 } 956 } 957 $out .= '</td></tr>'; 958 return $out; 959 } 960 961 /** 962 * Get processed value extra 963 * 964 * @param string $table 965 * @param string $fieldName 966 * @param string $fieldValue 967 * @param array $conf Not used 968 * @param string $splitString 969 * @return string 970 */ 971 protected function getProcessedValueExtra($table, $fieldName, $fieldValue, $conf, $splitString) 972 { 973 $out = ''; 974 $fields = []; 975 // Analysing the fields in the table. 976 if (is_array($GLOBALS['TCA'][$table] ?? null)) { 977 $fC = $GLOBALS['TCA'][$table]['columns'][$fieldName] ?? null; 978 $fields = $fC['config'] ?? []; 979 $fields['exclude'] = $fC['exclude'] ?? ''; 980 if (is_array($fC) && $fC['label']) { 981 $fields['label'] = preg_replace('/:$/', '', trim($this->getLanguageService()->sL($fC['label']))); 982 switch ($fields['type']) { 983 case 'input': 984 if (preg_match('/int|year/i', $fields['eval'] ?? '')) { 985 $fields['type'] = 'number'; 986 } elseif (preg_match('/time/i', $fields['eval'] ?? '')) { 987 $fields['type'] = 'time'; 988 } elseif (preg_match('/date/i', $fields['eval'] ?? '')) { 989 $fields['type'] = 'date'; 990 } else { 991 $fields['type'] = 'text'; 992 } 993 break; 994 case 'check': 995 if (!$fields['items']) { 996 $fields['type'] = 'boolean'; 997 } else { 998 $fields['type'] = 'binary'; 999 } 1000 break; 1001 case 'radio': 1002 $fields['type'] = 'multiple'; 1003 break; 1004 case 'select': 1005 case 'category': 1006 $fields['type'] = 'multiple'; 1007 if ($fields['foreign_table']) { 1008 $fields['type'] = 'relation'; 1009 } 1010 if ($fields['special']) { 1011 $fields['type'] = 'text'; 1012 } 1013 break; 1014 case 'group': 1015 if (($fields['internal_type'] ?? '') !== 'folder') { 1016 $fields['type'] = 'relation'; 1017 } 1018 break; 1019 case 'user': 1020 case 'flex': 1021 case 'passthrough': 1022 case 'none': 1023 case 'text': 1024 default: 1025 $fields['type'] = 'text'; 1026 } 1027 } else { 1028 $fields['label'] = '[FIELD: ' . $fieldName . ']'; 1029 switch ($fieldName) { 1030 case 'pid': 1031 $fields['type'] = 'relation'; 1032 $fields['allowed'] = 'pages'; 1033 break; 1034 case 'cruser_id': 1035 $fields['type'] = 'relation'; 1036 $fields['allowed'] = 'be_users'; 1037 break; 1038 case 'tstamp': 1039 case 'crdate': 1040 $fields['type'] = 'time'; 1041 break; 1042 default: 1043 $fields['type'] = 'number'; 1044 } 1045 } 1046 } 1047 switch ($fields['type']) { 1048 case 'date': 1049 if ($fieldValue != -1) { 1050 // @todo Replace deprecated strftime in php 8.1. Suppress warning in v11. 1051 $out = (string)@strftime('%d-%m-%Y', (int)$fieldValue); 1052 } 1053 break; 1054 case 'time': 1055 if ($fieldValue != -1) { 1056 if ($splitString === '<br />') { 1057 // @todo Replace deprecated strftime in php 8.1. Suppress warning in v11. 1058 $out = (string)@strftime('%H:%M' . $splitString . '%d-%m-%Y', (int)$fieldValue); 1059 } else { 1060 // @todo Replace deprecated strftime in php 8.1. Suppress warning in v11. 1061 $out = (string)@strftime('%H:%M %d-%m-%Y', (int)$fieldValue); 1062 } 1063 } 1064 break; 1065 case 'multiple': 1066 case 'binary': 1067 case 'relation': 1068 $out = $this->makeValueList($fieldName, $fieldValue, $fields, $table, $splitString); 1069 break; 1070 case 'boolean': 1071 $out = $fieldValue ? 'True' : 'False'; 1072 break; 1073 default: 1074 $out = htmlspecialchars($fieldValue); 1075 } 1076 return $out; 1077 } 1078 1079 /** 1080 * Recursively fetch all descendants of a given page 1081 * 1082 * @param int $id uid of the page 1083 * @param int $depth 1084 * @param int $begin 1085 * @param string $permsClause 1086 * @return string comma separated list of descendant pages 1087 */ 1088 protected function getTreeList($id, $depth, $begin = 0, $permsClause = '') 1089 { 1090 $depth = (int)$depth; 1091 $begin = (int)$begin; 1092 $id = (int)$id; 1093 if ($id < 0) { 1094 $id = abs($id); 1095 } 1096 if ($begin == 0) { 1097 $theList = (string)$id; 1098 } else { 1099 $theList = ''; 1100 } 1101 if ($id && $depth > 0) { 1102 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages'); 1103 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class)); 1104 $statement = $queryBuilder->select('uid') 1105 ->from('pages') 1106 ->where( 1107 $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($id, \PDO::PARAM_INT)), 1108 $queryBuilder->expr()->eq('sys_language_uid', 0) 1109 ) 1110 ->orderBy('uid'); 1111 if ($permsClause !== '') { 1112 $queryBuilder->andWhere(QueryHelper::stripLogicalOperatorPrefix($permsClause)); 1113 } 1114 $statement = $queryBuilder->executeQuery(); 1115 while ($row = $statement->fetchAssociative()) { 1116 if ($begin <= 0) { 1117 $theList .= ',' . $row['uid']; 1118 } 1119 if ($depth > 1) { 1120 $theSubList = $this->getTreeList($row['uid'], $depth - 1, $begin - 1, $permsClause); 1121 if (!empty($theList) && !empty($theSubList) && ($theSubList[0] !== ',')) { 1122 $theList .= ','; 1123 } 1124 $theList .= $theSubList; 1125 } 1126 } 1127 } 1128 return $theList; 1129 } 1130 1131 /** 1132 * Make value list 1133 * 1134 * @param string $fieldName 1135 * @param string $fieldValue 1136 * @param array $conf 1137 * @param string $table 1138 * @param string $splitString 1139 * @return string 1140 */ 1141 protected function makeValueList($fieldName, $fieldValue, $conf, $table, $splitString) 1142 { 1143 $backendUserAuthentication = $this->getBackendUserAuthentication(); 1144 $languageService = $this->getLanguageService(); 1145 $from_table_Arr = []; 1146 $fieldSetup = $conf; 1147 $out = ''; 1148 if ($fieldSetup['type'] === 'multiple') { 1149 foreach ($fieldSetup['items'] as $key => $val) { 1150 if (strpos($val[0], 'LLL:') === 0) { 1151 $value = $languageService->sL($val[0]); 1152 } else { 1153 $value = $val[0]; 1154 } 1155 if (GeneralUtility::inList($fieldValue, $val[1]) || $fieldValue == $val[1]) { 1156 if ($out !== '') { 1157 $out .= $splitString; 1158 } 1159 $out .= htmlspecialchars($value); 1160 } 1161 } 1162 } 1163 if ($fieldSetup['type'] === 'binary') { 1164 foreach ($fieldSetup['items'] as $Key => $val) { 1165 if (strpos($val[0], 'LLL:') === 0) { 1166 $value = $languageService->sL($val[0]); 1167 } else { 1168 $value = $val[0]; 1169 } 1170 if ($out !== '') { 1171 $out .= $splitString; 1172 } 1173 $out .= htmlspecialchars($value); 1174 } 1175 } 1176 if ($fieldSetup['type'] === 'relation') { 1177 $dontPrefixFirstTable = 0; 1178 $useTablePrefix = 0; 1179 foreach (($fieldSetup['items'] ?? []) as $val) { 1180 if (strpos($val[0], 'LLL:') === 0) { 1181 $value = $languageService->sL($val[0]); 1182 } else { 1183 $value = $val[0]; 1184 } 1185 if (GeneralUtility::inList($fieldValue, $value) || $fieldValue == $value) { 1186 if ($out !== '') { 1187 $out .= $splitString; 1188 } 1189 $out .= htmlspecialchars($value); 1190 } 1191 } 1192 if (str_contains($fieldSetup['allowed'], ',')) { 1193 $from_table_Arr = explode(',', $fieldSetup['allowed']); 1194 $useTablePrefix = 1; 1195 if (!$fieldSetup['prepend_tname']) { 1196 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table); 1197 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class)); 1198 $statement = $queryBuilder->select($fieldName)->from($table)->executeQuery(); 1199 while ($row = $statement->fetchAssociative()) { 1200 if (str_contains($row[$fieldName], ',')) { 1201 $checkContent = explode(',', $row[$fieldName]); 1202 foreach ($checkContent as $singleValue) { 1203 if (!str_contains($singleValue, '_')) { 1204 $dontPrefixFirstTable = 1; 1205 } 1206 } 1207 } else { 1208 $singleValue = $row[$fieldName]; 1209 if ($singleValue !== '' && !str_contains($singleValue, '_')) { 1210 $dontPrefixFirstTable = 1; 1211 } 1212 } 1213 } 1214 } 1215 } else { 1216 $from_table_Arr[0] = $fieldSetup['allowed']; 1217 } 1218 if (!empty($fieldSetup['prepend_tname'])) { 1219 $useTablePrefix = 1; 1220 } 1221 if (!empty($fieldSetup['foreign_table'])) { 1222 $from_table_Arr[0] = $fieldSetup['foreign_table']; 1223 } 1224 $counter = 0; 1225 $useSelectLabels = 0; 1226 $useAltSelectLabels = 0; 1227 $tablePrefix = ''; 1228 $labelFieldSelect = []; 1229 foreach ($from_table_Arr as $from_table) { 1230 if ($useTablePrefix && !$dontPrefixFirstTable && $counter != 1 || $counter == 1) { 1231 $tablePrefix = $from_table . '_'; 1232 } 1233 $counter = 1; 1234 if (is_array($GLOBALS['TCA'][$from_table] ?? null)) { 1235 $labelField = $GLOBALS['TCA'][$from_table]['ctrl']['label'] ?? ''; 1236 $altLabelField = $GLOBALS['TCA'][$from_table]['ctrl']['label_alt'] ?? ''; 1237 if (is_array($GLOBALS['TCA'][$from_table]['columns'][$labelField]['config']['items'] ?? false)) { 1238 $items = $GLOBALS['TCA'][$from_table]['columns'][$labelField]['config']['items']; 1239 foreach ($items as $labelArray) { 1240 if (str_starts_with($labelArray[0], 'LLL:')) { 1241 $labelFieldSelect[$labelArray[1]] = $languageService->sL($labelArray[0]); 1242 } else { 1243 $labelFieldSelect[$labelArray[1]] = $labelArray[0]; 1244 } 1245 } 1246 $useSelectLabels = 1; 1247 } 1248 $altLabelFieldSelect = []; 1249 if (is_array($GLOBALS['TCA'][$from_table]['columns'][$altLabelField]['config']['items'] ?? false)) { 1250 $items = $GLOBALS['TCA'][$from_table]['columns'][$altLabelField]['config']['items']; 1251 foreach ($items as $altLabelArray) { 1252 if (str_starts_with($altLabelArray[0], 'LLL:')) { 1253 $altLabelFieldSelect[$altLabelArray[1]] = $languageService->sL($altLabelArray[0]); 1254 } else { 1255 $altLabelFieldSelect[$altLabelArray[1]] = $altLabelArray[0]; 1256 } 1257 } 1258 $useAltSelectLabels = 1; 1259 } 1260 1261 if (empty($this->tableArray[$from_table])) { 1262 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($from_table); 1263 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class)); 1264 $selectFields = ['uid', $labelField]; 1265 if ($altLabelField) { 1266 $selectFields = array_merge($selectFields, GeneralUtility::trimExplode(',', $altLabelField, true)); 1267 } 1268 $queryBuilder->select(...$selectFields) 1269 ->from($from_table) 1270 ->orderBy('uid'); 1271 if (!$backendUserAuthentication->isAdmin()) { 1272 $webMounts = $backendUserAuthentication->returnWebmounts(); 1273 $perms_clause = $backendUserAuthentication->getPagePermsClause(Permission::PAGE_SHOW); 1274 $webMountPageTree = ''; 1275 $webMountPageTreePrefix = ''; 1276 foreach ($webMounts as $webMount) { 1277 if ($webMountPageTree) { 1278 $webMountPageTreePrefix = ','; 1279 } 1280 $webMountPageTree .= $webMountPageTreePrefix 1281 . $this->getTreeList($webMount, 999, 0, $perms_clause); 1282 } 1283 if ($from_table === 'pages') { 1284 $queryBuilder->where( 1285 QueryHelper::stripLogicalOperatorPrefix($perms_clause), 1286 $queryBuilder->expr()->in( 1287 'uid', 1288 $queryBuilder->createNamedParameter( 1289 GeneralUtility::intExplode(',', $webMountPageTree), 1290 Connection::PARAM_INT_ARRAY 1291 ) 1292 ) 1293 ); 1294 } else { 1295 $queryBuilder->where( 1296 $queryBuilder->expr()->in( 1297 'pid', 1298 $queryBuilder->createNamedParameter( 1299 GeneralUtility::intExplode(',', $webMountPageTree), 1300 Connection::PARAM_INT_ARRAY 1301 ) 1302 ) 1303 ); 1304 } 1305 } 1306 $statement = $queryBuilder->executeQuery(); 1307 $this->tableArray[$from_table] = []; 1308 while ($row = $statement->fetchAssociative()) { 1309 $this->tableArray[$from_table][] = $row; 1310 } 1311 } 1312 1313 foreach ($this->tableArray[$from_table] as $key => $val) { 1314 $this->settings['labels_noprefix'] = 1315 $this->settings['labels_noprefix'] == 1 1316 ? 'on' 1317 : $this->settings['labels_noprefix']; 1318 $prefixString = 1319 $this->settings['labels_noprefix'] === 'on' 1320 ? '' 1321 : ' [' . $tablePrefix . $val['uid'] . '] '; 1322 if ($out !== '') { 1323 $out .= $splitString; 1324 } 1325 if (GeneralUtility::inList($fieldValue, $tablePrefix . $val['uid']) 1326 || $fieldValue == $tablePrefix . $val['uid']) { 1327 if ($useSelectLabels) { 1328 $out .= htmlspecialchars($prefixString . $labelFieldSelect[$val[$labelField]]); 1329 } elseif ($val[$labelField]) { 1330 $out .= htmlspecialchars($prefixString . $val[$labelField]); 1331 } elseif ($useAltSelectLabels) { 1332 $out .= htmlspecialchars($prefixString . $altLabelFieldSelect[$val[$altLabelField]]); 1333 } else { 1334 $out .= htmlspecialchars($prefixString . $val[$altLabelField]); 1335 } 1336 } 1337 } 1338 } 1339 } 1340 } 1341 return $out; 1342 } 1343 1344 /** 1345 * Render table header 1346 * 1347 * @param array $row Table columns 1348 * @param array $conf Table TCA 1349 * @return string HTML of table header 1350 */ 1351 protected function resultRowTitles($row, $conf) 1352 { 1353 $languageService = $this->getLanguageService(); 1354 $tableHeader = []; 1355 // Start header row 1356 $tableHeader[] = '<thead><tr>'; 1357 // Iterate over given columns 1358 foreach ($row as $fieldName => $fieldValue) { 1359 if (GeneralUtility::inList($this->settings['queryFields'] ?? '', $fieldName) 1360 || !($this->settings['queryFields'] ?? false) 1361 && $fieldName !== 'pid' 1362 && $fieldName !== 'deleted' 1363 ) { 1364 if ($this->settings['search_result_labels'] ?? false) { 1365 $title = $languageService->sL(($conf['columns'][$fieldName]['label'] ?? false) ?: $fieldName); 1366 } else { 1367 $title = $languageService->sL($fieldName); 1368 } 1369 $tableHeader[] = '<th>' . htmlspecialchars($title) . '</th>'; 1370 } 1371 } 1372 // Add empty icon column 1373 $tableHeader[] = '<th></th>'; 1374 // Close header row 1375 $tableHeader[] = '</tr></thead>'; 1376 return implode(LF, $tableHeader); 1377 } 1378 /** 1379 * @throws \InvalidArgumentException 1380 * @throws \TYPO3\CMS\Core\Exception 1381 */ 1382 private function renderNoResultsFoundMessage() 1383 { 1384 $flashMessage = GeneralUtility::makeInstance(FlashMessage::class, 'No rows selected!', '', FlashMessage::INFO); 1385 $flashMessageService = GeneralUtility::makeInstance(FlashMessageService::class); 1386 $defaultFlashMessageQueue = $flashMessageService->getMessageQueueByIdentifier(); 1387 $defaultFlashMessageQueue->enqueue($flashMessage); 1388 } 1389 1390 /** 1391 * Make a list of fields for current table 1392 * 1393 * @return string Separated list of fields 1394 */ 1395 protected function makeFieldList() 1396 { 1397 $fieldListArr = []; 1398 if (is_array($GLOBALS['TCA'][$this->table])) { 1399 $fieldListArr = array_keys($GLOBALS['TCA'][$this->table]['columns'] ?? []); 1400 $fieldListArr[] = 'uid'; 1401 $fieldListArr[] = 'pid'; 1402 $fieldListArr[] = 'deleted'; 1403 if ($GLOBALS['TCA'][$this->table]['ctrl']['tstamp'] ?? false) { 1404 $fieldListArr[] = $GLOBALS['TCA'][$this->table]['ctrl']['tstamp']; 1405 } 1406 if ($GLOBALS['TCA'][$this->table]['ctrl']['crdate'] ?? false) { 1407 $fieldListArr[] = $GLOBALS['TCA'][$this->table]['ctrl']['crdate']; 1408 } 1409 if ($GLOBALS['TCA'][$this->table]['ctrl']['cruser_id'] ?? false) { 1410 $fieldListArr[] = $GLOBALS['TCA'][$this->table]['ctrl']['cruser_id']; 1411 } 1412 if ($GLOBALS['TCA'][$this->table]['ctrl']['sortby'] ?? false) { 1413 $fieldListArr[] = $GLOBALS['TCA'][$this->table]['ctrl']['sortby']; 1414 } 1415 } 1416 return implode(',', $fieldListArr); 1417 } 1418 1419 /** 1420 * Init function 1421 * 1422 * @param string $name The name 1423 * @param string $table The table name 1424 * @param string $fieldList The field list 1425 * @param array $settings Module settings like checkboxes in the interface 1426 */ 1427 protected function init($name, $table, $fieldList = '', array $settings = []) 1428 { 1429 // Analysing the fields in the table. 1430 if (is_array($GLOBALS['TCA'][$table] ?? false)) { 1431 $this->name = $name; 1432 $this->table = $table; 1433 $this->fieldList = $fieldList ?: $this->makeFieldList(); 1434 $this->settings = $settings; 1435 $fieldArr = GeneralUtility::trimExplode(',', $this->fieldList, true); 1436 foreach ($fieldArr as $fieldName) { 1437 $fC = $GLOBALS['TCA'][$this->table]['columns'][$fieldName] ?? []; 1438 $this->fields[$fieldName] = $fC['config'] ?? []; 1439 $this->fields[$fieldName]['exclude'] = $fC['exclude'] ?? ''; 1440 if (($this->fields[$fieldName]['type'] ?? '') === 'user' && !isset($this->fields[$fieldName]['type']['userFunc']) 1441 || ($this->fields[$fieldName]['type'] ?? '') === 'none' 1442 ) { 1443 // Do not list type=none "virtual" fields or query them from db, 1444 // and if type is user without defined userFunc 1445 unset($this->fields[$fieldName]); 1446 continue; 1447 } 1448 if (is_array($fC) && ($fC['label'] ?? false)) { 1449 $this->fields[$fieldName]['label'] = rtrim(trim($this->getLanguageService()->sL($fC['label'])), ':'); 1450 switch ($this->fields[$fieldName]['type']) { 1451 case 'input': 1452 if (preg_match('/int|year/i', ($this->fields[$fieldName]['eval'] ?? ''))) { 1453 $this->fields[$fieldName]['type'] = 'number'; 1454 } elseif (preg_match('/time/i', ($this->fields[$fieldName]['eval'] ?? ''))) { 1455 $this->fields[$fieldName]['type'] = 'time'; 1456 } elseif (preg_match('/date/i', ($this->fields[$fieldName]['eval'] ?? ''))) { 1457 $this->fields[$fieldName]['type'] = 'date'; 1458 } else { 1459 $this->fields[$fieldName]['type'] = 'text'; 1460 } 1461 break; 1462 case 'check': 1463 if (count($this->fields[$fieldName]['items'] ?? []) <= 1) { 1464 $this->fields[$fieldName]['type'] = 'boolean'; 1465 } else { 1466 $this->fields[$fieldName]['type'] = 'binary'; 1467 } 1468 break; 1469 case 'radio': 1470 $this->fields[$fieldName]['type'] = 'multiple'; 1471 break; 1472 case 'select': 1473 case 'category': 1474 $this->fields[$fieldName]['type'] = 'multiple'; 1475 if ($this->fields[$fieldName]['foreign_table'] ?? false) { 1476 $this->fields[$fieldName]['type'] = 'relation'; 1477 } 1478 if ($this->fields[$fieldName]['special'] ?? false) { 1479 $this->fields[$fieldName]['type'] = 'text'; 1480 } 1481 break; 1482 case 'group': 1483 if (($this->fields[$fieldName]['internal_type'] ?? '') !== 'folder') { 1484 $this->fields[$fieldName]['type'] = 'relation'; 1485 } 1486 break; 1487 case 'user': 1488 case 'flex': 1489 case 'passthrough': 1490 case 'none': 1491 case 'text': 1492 default: 1493 $this->fields[$fieldName]['type'] = 'text'; 1494 } 1495 } else { 1496 $this->fields[$fieldName]['label'] = '[FIELD: ' . $fieldName . ']'; 1497 switch ($fieldName) { 1498 case 'pid': 1499 $this->fields[$fieldName]['type'] = 'relation'; 1500 $this->fields[$fieldName]['allowed'] = 'pages'; 1501 break; 1502 case 'cruser_id': 1503 $this->fields[$fieldName]['type'] = 'relation'; 1504 $this->fields[$fieldName]['allowed'] = 'be_users'; 1505 break; 1506 case 'tstamp': 1507 case 'crdate': 1508 $this->fields[$fieldName]['type'] = 'time'; 1509 break; 1510 case 'deleted': 1511 $this->fields[$fieldName]['type'] = 'boolean'; 1512 break; 1513 default: 1514 $this->fields[$fieldName]['type'] = 'number'; 1515 } 1516 } 1517 } 1518 } 1519 /* // EXAMPLE: 1520 $this->queryConfig = array( 1521 array( 1522 'operator' => 'AND', 1523 'type' => 'FIELD_space_before_class', 1524 ), 1525 array( 1526 'operator' => 'AND', 1527 'type' => 'FIELD_records', 1528 'negate' => 1, 1529 'inputValue' => 'foo foo' 1530 ), 1531 array( 1532 'type' => 'newlevel', 1533 'nl' => array( 1534 array( 1535 'operator' => 'AND', 1536 'type' => 'FIELD_space_before_class', 1537 'negate' => 1, 1538 'inputValue' => 'foo foo' 1539 ), 1540 array( 1541 'operator' => 'AND', 1542 'type' => 'FIELD_records', 1543 'negate' => 1, 1544 'inputValue' => 'foo foo' 1545 ) 1546 ) 1547 ), 1548 array( 1549 'operator' => 'OR', 1550 'type' => 'FIELD_maillist', 1551 ) 1552 ); 1553 */ 1554 } 1555 1556 /** 1557 * Set and clean up external lists 1558 * 1559 * @param string $name The name 1560 * @param string $list The list 1561 * @param string $force 1562 */ 1563 protected function setAndCleanUpExternalLists($name, $list, $force = '') 1564 { 1565 $fields = array_unique(GeneralUtility::trimExplode(',', $list . ',' . $force, true)); 1566 $reList = []; 1567 foreach ($fields as $fieldName) { 1568 if (isset($this->fields[$fieldName])) { 1569 $reList[] = $fieldName; 1570 } 1571 } 1572 $this->extFieldLists[$name] = implode(',', $reList); 1573 } 1574 1575 /** 1576 * Process data 1577 * 1578 * @param array $qC Query config 1579 */ 1580 protected function procesData($qC = []) 1581 { 1582 $this->queryConfig = $qC; 1583 $POST = GeneralUtility::_POST(); 1584 // If delete... 1585 if ($POST['qG_del'] ?? false) { 1586 // Initialize array to work on, save special parameters 1587 $ssArr = $this->getSubscript($POST['qG_del']); 1588 $workArr = &$this->queryConfig; 1589 $ssArrSize = count($ssArr) - 1; 1590 $i = 0; 1591 for (; $i < $ssArrSize; $i++) { 1592 $workArr = &$workArr[$ssArr[$i]]; 1593 } 1594 // Delete the entry and move the other entries 1595 unset($workArr[$ssArr[$i]]); 1596 $workArrSize = count((array)$workArr); 1597 for ($j = $ssArr[$i]; $j < $workArrSize; $j++) { 1598 $workArr[$j] = $workArr[$j + 1]; 1599 unset($workArr[$j + 1]); 1600 } 1601 } 1602 // If insert... 1603 if ($POST['qG_ins'] ?? false) { 1604 // Initialize array to work on, save special parameters 1605 $ssArr = $this->getSubscript($POST['qG_ins']); 1606 $workArr = &$this->queryConfig; 1607 $ssArrSize = count($ssArr) - 1; 1608 $i = 0; 1609 for (; $i < $ssArrSize; $i++) { 1610 $workArr = &$workArr[$ssArr[$i]]; 1611 } 1612 // Move all entries above position where new entry is to be inserted 1613 $workArrSize = count((array)$workArr); 1614 for ($j = $workArrSize; $j > $ssArr[$i]; $j--) { 1615 $workArr[$j] = $workArr[$j - 1]; 1616 } 1617 // Clear new entry position 1618 unset($workArr[$ssArr[$i] + 1]); 1619 $workArr[$ssArr[$i] + 1]['type'] = 'FIELD_'; 1620 } 1621 // If move up... 1622 if ($POST['qG_up'] ?? false) { 1623 // Initialize array to work on 1624 $ssArr = $this->getSubscript($POST['qG_up']); 1625 $workArr = &$this->queryConfig; 1626 $ssArrSize = count($ssArr) - 1; 1627 $i = 0; 1628 for (; $i < $ssArrSize; $i++) { 1629 $workArr = &$workArr[$ssArr[$i]]; 1630 } 1631 // Swap entries 1632 $qG_tmp = $workArr[$ssArr[$i]]; 1633 $workArr[$ssArr[$i]] = $workArr[$ssArr[$i] - 1]; 1634 $workArr[$ssArr[$i] - 1] = $qG_tmp; 1635 } 1636 // If new level... 1637 if ($POST['qG_nl'] ?? false) { 1638 // Initialize array to work on 1639 $ssArr = $this->getSubscript($POST['qG_nl']); 1640 $workArr = &$this->queryConfig; 1641 $ssArraySize = count($ssArr) - 1; 1642 $i = 0; 1643 for (; $i < $ssArraySize; $i++) { 1644 $workArr = &$workArr[$ssArr[$i]]; 1645 } 1646 // Do stuff: 1647 $tempEl = $workArr[$ssArr[$i]]; 1648 if (is_array($tempEl)) { 1649 if ($tempEl['type'] !== 'newlevel') { 1650 $workArr[$ssArr[$i]] = [ 1651 'type' => 'newlevel', 1652 'operator' => $tempEl['operator'], 1653 'nl' => [$tempEl], 1654 ]; 1655 } 1656 } 1657 } 1658 // If collapse level... 1659 if ($POST['qG_remnl'] ?? false) { 1660 // Initialize array to work on 1661 $ssArr = $this->getSubscript($POST['qG_remnl']); 1662 $workArr = &$this->queryConfig; 1663 $ssArrSize = count($ssArr) - 1; 1664 $i = 0; 1665 for (; $i < $ssArrSize; $i++) { 1666 $workArr = &$workArr[$ssArr[$i]]; 1667 } 1668 // Do stuff: 1669 $tempEl = $workArr[$ssArr[$i]]; 1670 if (is_array($tempEl)) { 1671 if ($tempEl['type'] === 'newlevel' && is_array($workArr)) { 1672 $a1 = array_slice($workArr, 0, $ssArr[$i]); 1673 $a2 = array_slice($workArr, $ssArr[$i]); 1674 array_shift($a2); 1675 $a3 = $tempEl['nl']; 1676 $a3[0]['operator'] = $tempEl['operator']; 1677 $workArr = array_merge($a1, $a3, $a2); 1678 } 1679 } 1680 } 1681 } 1682 1683 /** 1684 * Clean up query config 1685 * 1686 * @param array $queryConfig Query config 1687 * @return array 1688 */ 1689 protected function cleanUpQueryConfig($queryConfig) 1690 { 1691 // Since we don't traverse the array using numeric keys in the upcoming while-loop make sure it's fresh and clean before displaying 1692 if (!empty($queryConfig) && is_array($queryConfig)) { 1693 ksort($queryConfig); 1694 } else { 1695 // queryConfig should never be empty! 1696 if (!isset($queryConfig[0]) || empty($queryConfig[0]['type'])) { 1697 // Make sure queryConfig is an array 1698 $queryConfig = []; 1699 $queryConfig[0] = ['type' => 'FIELD_']; 1700 } 1701 } 1702 // Traverse: 1703 foreach ($queryConfig as $key => $conf) { 1704 $fieldName = ''; 1705 if (str_starts_with(($conf['type'] ?? ''), 'FIELD_')) { 1706 $fieldName = substr($conf['type'], 6); 1707 $fieldType = $this->fields[$fieldName]['type'] ?? ''; 1708 } elseif (($conf['type'] ?? '') === 'newlevel') { 1709 $fieldType = $conf['type']; 1710 } else { 1711 $fieldType = 'ignore'; 1712 } 1713 switch ($fieldType) { 1714 case 'newlevel': 1715 if (!$queryConfig[$key]['nl']) { 1716 $queryConfig[$key]['nl'][0]['type'] = 'FIELD_'; 1717 } 1718 $queryConfig[$key]['nl'] = $this->cleanUpQueryConfig($queryConfig[$key]['nl']); 1719 break; 1720 case 'userdef': 1721 break; 1722 case 'ignore': 1723 default: 1724 $verifiedName = $this->verifyType($fieldName); 1725 $queryConfig[$key]['type'] = 'FIELD_' . $this->verifyType($verifiedName); 1726 if ((int)($conf['comparison'] ?? 0) >> 5 !== (int)($this->comp_offsets[$fieldType] ?? 0)) { 1727 $conf['comparison'] = (int)($this->comp_offsets[$fieldType] ?? 0) << 5; 1728 } 1729 $queryConfig[$key]['comparison'] = $this->verifyComparison($conf['comparison'] ?? '0', ($conf['negate'] ?? null) ? 1 : 0); 1730 $queryConfig[$key]['inputValue'] = $this->cleanInputVal($queryConfig[$key]); 1731 $queryConfig[$key]['inputValue1'] = $this->cleanInputVal($queryConfig[$key], '1'); 1732 } 1733 } 1734 return $queryConfig; 1735 } 1736 1737 /** 1738 * Get form elements 1739 * 1740 * @param int $subLevel 1741 * @param string $queryConfig 1742 * @param string $parent 1743 * @return array 1744 */ 1745 protected function getFormElements($subLevel = 0, $queryConfig = '', $parent = '') 1746 { 1747 $codeArr = []; 1748 if (!is_array($queryConfig)) { 1749 $queryConfig = $this->queryConfig; 1750 } 1751 $c = 0; 1752 $arrCount = 0; 1753 $loopCount = 0; 1754 foreach ($queryConfig as $key => $conf) { 1755 $fieldName = ''; 1756 $subscript = $parent . '[' . $key . ']'; 1757 $lineHTML = []; 1758 $lineHTML[] = $this->mkOperatorSelect($this->name . $subscript, ($conf['operator'] ?? ''), (bool)$c, ($conf['type'] ?? '') !== 'FIELD_'); 1759 if (str_starts_with(($conf['type'] ?? ''), 'FIELD_')) { 1760 $fieldName = substr($conf['type'], 6); 1761 $this->fieldName = $fieldName; 1762 $fieldType = $this->fields[$fieldName]['type'] ?? ''; 1763 if ((int)($conf['comparison'] ?? 0) >> 5 !== (int)($this->comp_offsets[$fieldType] ?? 0)) { 1764 $conf['comparison'] = (int)($this->comp_offsets[$fieldType] ?? 0) << 5; 1765 } 1766 //nasty nasty... 1767 //make sure queryConfig contains _actual_ comparevalue. 1768 //mkCompSelect don't care, but getQuery does. 1769 $queryConfig[$key]['comparison'] += isset($conf['negate']) - $conf['comparison'] % 2; 1770 } elseif (($conf['type'] ?? '') === 'newlevel') { 1771 $fieldType = $conf['type']; 1772 } else { 1773 $fieldType = 'ignore'; 1774 } 1775 $fieldPrefix = htmlspecialchars($this->name . $subscript); 1776 switch ($fieldType) { 1777 case 'ignore': 1778 break; 1779 case 'newlevel': 1780 if (!$queryConfig[$key]['nl']) { 1781 $queryConfig[$key]['nl'][0]['type'] = 'FIELD_'; 1782 } 1783 $lineHTML[] = '<input type="hidden" name="' . $fieldPrefix . '[type]" value="newlevel">'; 1784 $codeArr[$arrCount]['sub'] = $this->getFormElements($subLevel + 1, $queryConfig[$key]['nl'], $subscript . '[nl]'); 1785 break; 1786 case 'userdef': 1787 $lineHTML[] = ''; 1788 break; 1789 case 'date': 1790 $lineHTML[] = '<div class="row row-cols-auto mb-2 mb-sm-0">'; 1791 $lineHTML[] = $this->makeComparisonSelector($subscript, $fieldName, $conf); 1792 if ($conf['comparison'] === 100 || $conf['comparison'] === 101) { 1793 // between 1794 $lineHTML[] = $this->getDateTimePickerField($fieldPrefix . '[inputValue]', $conf['inputValue'], 'date'); 1795 $lineHTML[] = $this->getDateTimePickerField($fieldPrefix . '[inputValue1]', $conf['inputValue1'], 'date'); 1796 } else { 1797 $lineHTML[] = $this->getDateTimePickerField($fieldPrefix . '[inputValue]', $conf['inputValue'], 'date'); 1798 } 1799 $lineHTML[] = '</div>'; 1800 break; 1801 case 'time': 1802 $lineHTML[] = '<div class="row row-cols-auto mb-2 mb-sm-0">'; 1803 $lineHTML[] = $this->makeComparisonSelector($subscript, $fieldName, $conf); 1804 if ($conf['comparison'] === 100 || $conf['comparison'] === 101) { 1805 // between: 1806 $lineHTML[] = $this->getDateTimePickerField($fieldPrefix . '[inputValue]', $conf['inputValue'], 'datetime'); 1807 $lineHTML[] = $this->getDateTimePickerField($fieldPrefix . '[inputValue1]', $conf['inputValue1'], 'datetime'); 1808 } else { 1809 $lineHTML[] = $this->getDateTimePickerField($fieldPrefix . '[inputValue]', $conf['inputValue'], 'datetime'); 1810 } 1811 $lineHTML[] = '</div>'; 1812 break; 1813 case 'multiple': 1814 case 'binary': 1815 case 'relation': 1816 $lineHTML[] = '<div class="row row-cols-auto mb-2 mb-sm-0">'; 1817 $lineHTML[] = $this->makeComparisonSelector($subscript, $fieldName, $conf); 1818 $lineHTML[] = '<div class="col mb-sm-2">'; 1819 if ($conf['comparison'] === 68 || $conf['comparison'] === 69 || $conf['comparison'] === 162 || $conf['comparison'] === 163) { 1820 $lineHTML[] = '<select class="form-select" name="' . $fieldPrefix . '[inputValue][]" multiple="multiple">'; 1821 } elseif ($conf['comparison'] === 66 || $conf['comparison'] === 67) { 1822 if (is_array($conf['inputValue'])) { 1823 $conf['inputValue'] = implode(',', $conf['inputValue']); 1824 } 1825 $lineHTML[] = '<input class="form-control t3js-clearable" type="text" value="' . htmlspecialchars($conf['inputValue']) . '" name="' . $fieldPrefix . '[inputValue]">'; 1826 } elseif ($conf['comparison'] === 64) { 1827 if (is_array($conf['inputValue'])) { 1828 $conf['inputValue'] = $conf['inputValue'][0]; 1829 } 1830 $lineHTML[] = '<select class="form-select t3js-submit-change" name="' . $fieldPrefix . '[inputValue]">'; 1831 } else { 1832 $lineHTML[] = '<select class="form-select t3js-submit-change" name="' . $fieldPrefix . '[inputValue]">'; 1833 } 1834 if ($conf['comparison'] != 66 && $conf['comparison'] != 67) { 1835 $lineHTML[] = $this->makeOptionList($fieldName, $conf, $this->table); 1836 $lineHTML[] = '</select>'; 1837 } 1838 $lineHTML[] = '</div>'; 1839 $lineHTML[] = '</div>'; 1840 break; 1841 case 'boolean': 1842 $lineHTML[] = '<div class="row row-cols-auto mb-2 mb-sm-0">'; 1843 $lineHTML[] = $this->makeComparisonSelector($subscript, $fieldName, $conf); 1844 $lineHTML[] = '<input type="hidden" value="1" name="' . $fieldPrefix . '[inputValue]">'; 1845 $lineHTML[] = '</div>'; 1846 break; 1847 default: 1848 $lineHTML[] = '<div class="row row-cols-auto mb-2 mb-sm-0">'; 1849 $lineHTML[] = $this->makeComparisonSelector($subscript, $fieldName, $conf); 1850 $lineHTML[] = '<div class="col mb-sm-2">'; 1851 if ($conf['comparison'] === 37 || $conf['comparison'] === 36) { 1852 // between: 1853 $lineHTML[] = '<input class="form-control t3js-clearable" type="text" value="' . htmlspecialchars($conf['inputValue']) . '" name="' . $fieldPrefix . '[inputValue]">'; 1854 $lineHTML[] = '<input class="form-control t3js-clearable" type="text" value="' . htmlspecialchars($conf['inputValue1']) . '" name="' . $fieldPrefix . '[inputValue1]">'; 1855 } else { 1856 $lineHTML[] = '<input class="form-control t3js-clearable" type="text" value="' . htmlspecialchars($conf['inputValue']) . '" name="' . $fieldPrefix . '[inputValue]">'; 1857 } 1858 $lineHTML[] = '</div>'; 1859 $lineHTML[] = '</div>'; 1860 } 1861 if ($fieldType !== 'ignore') { 1862 $lineHTML[] = '<div class="row row-cols-auto mb-2">'; 1863 $lineHTML[] = '<div class="btn-group">'; 1864 $lineHTML[] = $this->updateIcon(); 1865 if ($loopCount) { 1866 $lineHTML[] = '<button class="btn btn-default" title="Remove condition" name="qG_del' . htmlspecialchars($subscript) . '"><i class="fa fa-trash fa-fw"></i></button>'; 1867 } 1868 $lineHTML[] = '<button class="btn btn-default" title="Add condition" name="qG_ins' . htmlspecialchars($subscript) . '"><i class="fa fa-plus fa-fw"></i></button>'; 1869 if ($c != 0) { 1870 $lineHTML[] = '<button class="btn btn-default" title="Move up" name="qG_up' . htmlspecialchars($subscript) . '"><i class="fa fa-chevron-up fa-fw"></i></button>'; 1871 } 1872 if ($c != 0 && $fieldType !== 'newlevel') { 1873 $lineHTML[] = '<button class="btn btn-default" title="New level" name="qG_nl' . htmlspecialchars($subscript) . '"><i class="fa fa-chevron-right fa-fw"></i></button>'; 1874 } 1875 if ($fieldType === 'newlevel') { 1876 $lineHTML[] = '<button class="btn btn-default" title="Collapse new level" name="qG_remnl' . htmlspecialchars($subscript) . '"><i class="fa fa-chevron-left fa-fw"></i></button>'; 1877 } 1878 $lineHTML[] = '</div>'; 1879 $lineHTML[] = '</div>'; 1880 $codeArr[$arrCount]['html'] = implode(LF, $lineHTML); 1881 $codeArr[$arrCount]['query'] = $this->getQuerySingle($conf, $c === 0); 1882 $arrCount++; 1883 $c++; 1884 } 1885 $loopCount = 1; 1886 } 1887 $this->queryConfig = $queryConfig; 1888 return $codeArr; 1889 } 1890 1891 /** 1892 * @param string $subscript 1893 * @param string $fieldName 1894 * @param array $conf 1895 * 1896 * @return string 1897 */ 1898 protected function makeComparisonSelector($subscript, $fieldName, $conf) 1899 { 1900 $fieldPrefix = $this->name . $subscript; 1901 $lineHTML = []; 1902 $lineHTML[] = '<div class="col mb-sm-2">'; 1903 $lineHTML[] = $this->mkTypeSelect($fieldPrefix . '[type]', $fieldName); 1904 $lineHTML[] = '</div>'; 1905 $lineHTML[] = '<div class="col mb-sm-2">'; 1906 $lineHTML[] = ' <div class="input-group">'; 1907 $lineHTML[] = $this->mkCompSelect($fieldPrefix . '[comparison]', $conf['comparison'], ($conf['negate'] ?? null) ? 1 : 0); 1908 $lineHTML[] = ' <span class="input-group-addon">'; 1909 $lineHTML[] = ' <input type="checkbox" class="checkbox t3js-submit-click"' . (($conf['negate'] ?? null) ? ' checked' : '') . ' name="' . htmlspecialchars($fieldPrefix) . '[negate]">'; 1910 $lineHTML[] = ' </span>'; 1911 $lineHTML[] = ' </div>'; 1912 $lineHTML[] = ' </div>'; 1913 return implode(LF, $lineHTML); 1914 } 1915 1916 /** 1917 * Make option list 1918 * 1919 * @param string $fieldName 1920 * @param array $conf 1921 * @param string $table 1922 * @return string 1923 */ 1924 protected function makeOptionList($fieldName, $conf, $table) 1925 { 1926 $backendUserAuthentication = $this->getBackendUserAuthentication(); 1927 $from_table_Arr = []; 1928 $out = []; 1929 $fieldSetup = $this->fields[$fieldName]; 1930 $languageService = $this->getLanguageService(); 1931 if ($fieldSetup['type'] === 'multiple') { 1932 $optGroupOpen = false; 1933 foreach (($fieldSetup['items'] ?? []) as $val) { 1934 if (strpos($val[0], 'LLL:') === 0) { 1935 $value = $languageService->sL($val[0]); 1936 } else { 1937 $value = $val[0]; 1938 } 1939 if ($val[1] === '--div--') { 1940 if ($optGroupOpen) { 1941 $out[] = '</optgroup>'; 1942 } 1943 $optGroupOpen = true; 1944 $out[] = '<optgroup label="' . htmlspecialchars($value) . '">'; 1945 } elseif (GeneralUtility::inList($conf['inputValue'], $val[1])) { 1946 $out[] = '<option value="' . htmlspecialchars($val[1]) . '" selected>' . htmlspecialchars($value) . '</option>'; 1947 } else { 1948 $out[] = '<option value="' . htmlspecialchars($val[1]) . '">' . htmlspecialchars($value) . '</option>'; 1949 } 1950 } 1951 if ($optGroupOpen) { 1952 $out[] = '</optgroup>'; 1953 } 1954 } 1955 if ($fieldSetup['type'] === 'binary') { 1956 foreach ($fieldSetup['items'] as $key => $val) { 1957 if (strpos($val[0], 'LLL:') === 0) { 1958 $value = $languageService->sL($val[0]); 1959 } else { 1960 $value = $val[0]; 1961 } 1962 if (GeneralUtility::inList($conf['inputValue'], (string)(2 ** $key))) { 1963 $out[] = '<option value="' . 2 ** $key . '" selected>' . htmlspecialchars($value) . '</option>'; 1964 } else { 1965 $out[] = '<option value="' . 2 ** $key . '">' . htmlspecialchars($value) . '</option>'; 1966 } 1967 } 1968 } 1969 if ($fieldSetup['type'] === 'relation') { 1970 $useTablePrefix = 0; 1971 $dontPrefixFirstTable = 0; 1972 foreach (($fieldSetup['items'] ?? []) as $val) { 1973 if (strpos($val[0], 'LLL:') === 0) { 1974 $value = $languageService->sL($val[0]); 1975 } else { 1976 $value = $val[0]; 1977 } 1978 if (GeneralUtility::inList($conf['inputValue'], $val[1])) { 1979 $out[] = '<option value="' . htmlspecialchars($val[1]) . '" selected>' . htmlspecialchars($value) . '</option>'; 1980 } else { 1981 $out[] = '<option value="' . htmlspecialchars($val[1]) . '">' . htmlspecialchars($value) . '</option>'; 1982 } 1983 } 1984 $allowedFields = $fieldSetup['allowed'] ?? ''; 1985 if (str_contains($allowedFields, ',')) { 1986 $from_table_Arr = explode(',', $allowedFields); 1987 $useTablePrefix = 1; 1988 if (!$fieldSetup['prepend_tname']) { 1989 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table); 1990 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class)); 1991 $statement = $queryBuilder->select($fieldName) 1992 ->from($table) 1993 ->executeQuery(); 1994 while ($row = $statement->fetchAssociative()) { 1995 if (str_contains($row[$fieldName], ',')) { 1996 $checkContent = explode(',', $row[$fieldName]); 1997 foreach ($checkContent as $singleValue) { 1998 if (!str_contains($singleValue, '_')) { 1999 $dontPrefixFirstTable = 1; 2000 } 2001 } 2002 } else { 2003 $singleValue = $row[$fieldName]; 2004 if ($singleValue !== '' && !str_contains($singleValue, '_')) { 2005 $dontPrefixFirstTable = 1; 2006 } 2007 } 2008 } 2009 } 2010 } else { 2011 $from_table_Arr[0] = $allowedFields; 2012 } 2013 if (!empty($fieldSetup['prepend_tname'])) { 2014 $useTablePrefix = 1; 2015 } 2016 if (!empty($fieldSetup['foreign_table'])) { 2017 $from_table_Arr[0] = $fieldSetup['foreign_table']; 2018 } 2019 $counter = 0; 2020 $tablePrefix = ''; 2021 $outArray = []; 2022 $labelFieldSelect = []; 2023 foreach ($from_table_Arr as $from_table) { 2024 $useSelectLabels = false; 2025 $useAltSelectLabels = false; 2026 if ($useTablePrefix && !$dontPrefixFirstTable && $counter != 1 || $counter === 1) { 2027 $tablePrefix = $from_table . '_'; 2028 } 2029 $counter = 1; 2030 if (is_array($GLOBALS['TCA'][$from_table])) { 2031 $labelField = $GLOBALS['TCA'][$from_table]['ctrl']['label'] ?? ''; 2032 $altLabelField = $GLOBALS['TCA'][$from_table]['ctrl']['label_alt'] ?? ''; 2033 if ($GLOBALS['TCA'][$from_table]['columns'][$labelField]['config']['items'] ?? false) { 2034 foreach ($GLOBALS['TCA'][$from_table]['columns'][$labelField]['config']['items'] as $labelArray) { 2035 if (strpos($labelArray[0], 'LLL:') === 0) { 2036 $labelFieldSelect[$labelArray[1]] = $languageService->sL($labelArray[0]); 2037 } else { 2038 $labelFieldSelect[$labelArray[1]] = $labelArray[0]; 2039 } 2040 } 2041 $useSelectLabels = true; 2042 } 2043 $altLabelFieldSelect = []; 2044 if ($GLOBALS['TCA'][$from_table]['columns'][$altLabelField]['config']['items'] ?? false) { 2045 foreach ($GLOBALS['TCA'][$from_table]['columns'][$altLabelField]['config']['items'] as $altLabelArray) { 2046 if (strpos($altLabelArray[0], 'LLL:') === 0) { 2047 $altLabelFieldSelect[$altLabelArray[1]] = $languageService->sL($altLabelArray[0]); 2048 } else { 2049 $altLabelFieldSelect[$altLabelArray[1]] = $altLabelArray[0]; 2050 } 2051 } 2052 $useAltSelectLabels = true; 2053 } 2054 2055 if (!($this->tableArray[$from_table] ?? false)) { 2056 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($from_table); 2057 $queryBuilder->getRestrictions()->removeAll(); 2058 if (empty($this->settings['show_deleted'])) { 2059 $queryBuilder->getRestrictions()->add(GeneralUtility::makeInstance(DeletedRestriction::class)); 2060 } 2061 $selectFields = ['uid', $labelField]; 2062 if ($altLabelField) { 2063 $selectFields = array_merge($selectFields, GeneralUtility::trimExplode(',', $altLabelField, true)); 2064 } 2065 $queryBuilder->select(...$selectFields) 2066 ->from($from_table) 2067 ->orderBy('uid'); 2068 if (!$backendUserAuthentication->isAdmin()) { 2069 $webMounts = $backendUserAuthentication->returnWebmounts(); 2070 $perms_clause = $backendUserAuthentication->getPagePermsClause(Permission::PAGE_SHOW); 2071 $webMountPageTree = ''; 2072 $webMountPageTreePrefix = ''; 2073 foreach ($webMounts as $webMount) { 2074 if ($webMountPageTree) { 2075 $webMountPageTreePrefix = ','; 2076 } 2077 $webMountPageTree .= $webMountPageTreePrefix 2078 . $this->getTreeList($webMount, 999, 0, $perms_clause); 2079 } 2080 if ($from_table === 'pages') { 2081 $queryBuilder->where( 2082 QueryHelper::stripLogicalOperatorPrefix($perms_clause), 2083 $queryBuilder->expr()->in( 2084 'uid', 2085 $queryBuilder->createNamedParameter( 2086 GeneralUtility::intExplode(',', $webMountPageTree), 2087 Connection::PARAM_INT_ARRAY 2088 ) 2089 ) 2090 ); 2091 } else { 2092 $queryBuilder->where( 2093 $queryBuilder->expr()->in( 2094 'pid', 2095 $queryBuilder->createNamedParameter( 2096 GeneralUtility::intExplode(',', $webMountPageTree), 2097 Connection::PARAM_INT_ARRAY 2098 ) 2099 ) 2100 ); 2101 } 2102 } 2103 $statement = $queryBuilder->executeQuery(); 2104 $this->tableArray[$from_table] = $statement->fetchAllAssociative(); 2105 } 2106 2107 foreach (($this->tableArray[$from_table] ?? []) as $val) { 2108 if ($useSelectLabels) { 2109 $outArray[$tablePrefix . $val['uid']] = htmlspecialchars($labelFieldSelect[$val[$labelField]]); 2110 } elseif ($val[$labelField]) { 2111 $outArray[$tablePrefix . $val['uid']] = htmlspecialchars($val[$labelField]); 2112 } elseif ($useAltSelectLabels) { 2113 $outArray[$tablePrefix . $val['uid']] = htmlspecialchars($altLabelFieldSelect[$val[$altLabelField]]); 2114 } else { 2115 $outArray[$tablePrefix . $val['uid']] = htmlspecialchars($val[$altLabelField]); 2116 } 2117 } 2118 if (isset($this->settings['options_sortlabel']) && $this->settings['options_sortlabel'] && is_array($outArray)) { 2119 natcasesort($outArray); 2120 } 2121 } 2122 } 2123 foreach ($outArray as $key2 => $val2) { 2124 if (GeneralUtility::inList($conf['inputValue'], $key2)) { 2125 $out[] = '<option value="' . htmlspecialchars($key2) . '" selected>[' . htmlspecialchars($key2) . '] ' . htmlspecialchars($val2) . '</option>'; 2126 } else { 2127 $out[] = '<option value="' . htmlspecialchars($key2) . '">[' . htmlspecialchars($key2) . '] ' . htmlspecialchars($val2) . '</option>'; 2128 } 2129 } 2130 } 2131 return implode(LF, $out); 2132 } 2133 2134 /** 2135 * Print code array 2136 * 2137 * @param array $codeArr 2138 * @param int $recursionLevel 2139 * @return string 2140 */ 2141 protected function printCodeArray($codeArr, $recursionLevel = 0) 2142 { 2143 $out = []; 2144 foreach (array_values($codeArr) as $queryComponent) { 2145 $out[] = '<div class="card">'; 2146 $out[] = '<div class="card-body pb-2">'; 2147 $out[] = $queryComponent['html']; 2148 2149 if ($this->enableQueryParts) { 2150 $out[] = '<div class="row row-cols-auto mb-2">'; 2151 $out[] = '<div class="col">'; 2152 $out[] = '<code class="m-0">'; 2153 $out[] = htmlspecialchars($queryComponent['query']); 2154 $out[] = '</code>'; 2155 $out[] = '</div>'; 2156 $out[] = '</div>'; 2157 } 2158 if (is_array($queryComponent['sub'] ?? null)) { 2159 $out[] = '<div class="mb-2">'; 2160 $out[] = $this->printCodeArray($queryComponent['sub'], $recursionLevel + 1); 2161 $out[] = '</div>'; 2162 } 2163 $out[] = '</div>'; 2164 $out[] = '</div>'; 2165 } 2166 return implode(LF, $out); 2167 } 2168 2169 /** 2170 * Make operator select 2171 * 2172 * @param string $name 2173 * @param string $op 2174 * @param bool $draw 2175 * @param bool $submit 2176 * @return string 2177 */ 2178 protected function mkOperatorSelect($name, $op, $draw, $submit) 2179 { 2180 $out = []; 2181 if ($draw) { 2182 $out[] = '<div class="row row-cols-auto mb-2">'; 2183 $out[] = ' <div class="col">'; 2184 $out[] = ' <select class="form-select' . ($submit ? ' t3js-submit-change' : '') . '" name="' . htmlspecialchars($name) . '[operator]">'; 2185 $out[] = ' <option value="AND"' . (!$op || $op === 'AND' ? ' selected' : '') . '>' . htmlspecialchars($this->lang['AND']) . '</option>'; 2186 $out[] = ' <option value="OR"' . ($op === 'OR' ? ' selected' : '') . '>' . htmlspecialchars($this->lang['OR']) . '</option>'; 2187 $out[] = ' </select>'; 2188 $out[] = ' </div>'; 2189 $out[] = '</div>'; 2190 } else { 2191 $out[] = '<input type="hidden" value="' . htmlspecialchars($op) . '" name="' . htmlspecialchars($name) . '[operator]">'; 2192 } 2193 return implode(LF, $out); 2194 } 2195 2196 /** 2197 * Make type select 2198 * 2199 * @param string $name 2200 * @param string $fieldName 2201 * @param string $prepend 2202 * @return string 2203 */ 2204 protected function mkTypeSelect($name, $fieldName, $prepend = 'FIELD_') 2205 { 2206 $out = []; 2207 $out[] = '<select class="form-select t3js-submit-change" name="' . htmlspecialchars($name) . '">'; 2208 $out[] = '<option value=""></option>'; 2209 foreach ($this->fields as $key => $value) { 2210 if (!($value['exclude'] ?? false) || $this->getBackendUserAuthentication()->check('non_exclude_fields', $this->table . ':' . $key)) { 2211 $label = $this->fields[$key]['label']; 2212 if ($this->showFieldAndTableNames) { 2213 $label .= ' [' . $key . ']'; 2214 } 2215 $out[] = '<option value="' . htmlspecialchars($prepend . $key) . '"' . ($key === $fieldName ? ' selected' : '') . '>' . htmlspecialchars($label) . '</option>'; 2216 } 2217 } 2218 $out[] = '</select>'; 2219 return implode(LF, $out); 2220 } 2221 2222 /** 2223 * Verify type 2224 * 2225 * @param string $fieldName 2226 * @return string 2227 */ 2228 protected function verifyType($fieldName) 2229 { 2230 $first = ''; 2231 foreach ($this->fields as $key => $value) { 2232 if (!$first) { 2233 $first = $key; 2234 } 2235 if ($key === $fieldName) { 2236 return $key; 2237 } 2238 } 2239 return $first; 2240 } 2241 2242 /** 2243 * Verify comparison 2244 * 2245 * @param string $comparison 2246 * @param int $neg 2247 * @return int 2248 */ 2249 protected function verifyComparison($comparison, $neg) 2250 { 2251 $compOffSet = $comparison >> 5; 2252 $first = -1; 2253 for ($i = 32 * $compOffSet + $neg; $i < 32 * ($compOffSet + 1); $i += 2) { 2254 if ($first === -1) { 2255 $first = $i; 2256 } 2257 if ($i >> 1 === $comparison >> 1) { 2258 return $i; 2259 } 2260 } 2261 return $first; 2262 } 2263 2264 /** 2265 * Make field to input select 2266 * 2267 * @param string $name 2268 * @param string $fieldName 2269 * @return string 2270 */ 2271 protected function mkFieldToInputSelect($name, $fieldName) 2272 { 2273 $out = []; 2274 $out[] = '<div class="input-group mb-2">'; 2275 $out[] = ' <span class="input-group-btn">'; 2276 $out[] = $this->updateIcon(); 2277 $out[] = ' </span>'; 2278 $out[] = ' <input type="text" class="form-control t3js-clearable" value="' . htmlspecialchars($fieldName) . '" name="' . htmlspecialchars($name) . '">'; 2279 $out[] = '</div>'; 2280 2281 $out[] = '<select class="form-select t3js-addfield" name="_fieldListDummy" size="5" data-field="' . htmlspecialchars($name) . '">'; 2282 foreach ($this->fields as $key => $value) { 2283 if (!$value['exclude'] || $this->getBackendUserAuthentication()->check('non_exclude_fields', $this->table . ':' . $key)) { 2284 $label = $this->fields[$key]['label']; 2285 if ($this->showFieldAndTableNames) { 2286 $label .= ' [' . $key . ']'; 2287 } 2288 $out[] = '<option value="' . htmlspecialchars($key) . '"' . ($key === $fieldName ? ' selected' : '') . '>' . htmlspecialchars($label) . '</option>'; 2289 } 2290 } 2291 $out[] = '</select>'; 2292 return implode(LF, $out); 2293 } 2294 2295 /** 2296 * Make table select 2297 * 2298 * @param string $name 2299 * @param string $cur 2300 * @return string 2301 */ 2302 protected function mkTableSelect($name, $cur) 2303 { 2304 $out = []; 2305 $out[] = '<select class="form-select t3js-submit-change" name="' . $name . '">'; 2306 $out[] = '<option value=""></option>'; 2307 foreach ($GLOBALS['TCA'] as $tN => $value) { 2308 if ($this->getBackendUserAuthentication()->check('tables_select', $tN)) { 2309 $label = $this->getLanguageService()->sL($GLOBALS['TCA'][$tN]['ctrl']['title']); 2310 if ($this->showFieldAndTableNames) { 2311 $label .= ' [' . $tN . ']'; 2312 } 2313 $out[] = '<option value="' . htmlspecialchars($tN) . '"' . ($tN === $cur ? ' selected' : '') . '>' . htmlspecialchars($label) . '</option>'; 2314 } 2315 } 2316 $out[] = '</select>'; 2317 return implode(LF, $out); 2318 } 2319 2320 /** 2321 * Make comparison select 2322 * 2323 * @param string $name 2324 * @param string $comparison 2325 * @param int $neg 2326 * @return string 2327 */ 2328 protected function mkCompSelect($name, $comparison, $neg) 2329 { 2330 $compOffSet = $comparison >> 5; 2331 $out = []; 2332 $out[] = '<select class="form-select t3js-submit-change" name="' . $name . '">'; 2333 for ($i = 32 * $compOffSet + $neg; $i < 32 * ($compOffSet + 1); $i += 2) { 2334 if ($this->lang['comparison'][$i . '_'] ?? false) { 2335 $out[] = '<option value="' . $i . '"' . ($i >> 1 === $comparison >> 1 ? ' selected' : '') . '>' . htmlspecialchars($this->lang['comparison'][$i . '_']) . '</option>'; 2336 } 2337 } 2338 $out[] = '</select>'; 2339 return implode(LF, $out); 2340 } 2341 2342 /** 2343 * Get subscript 2344 * 2345 * @param array $arr 2346 * @return array 2347 */ 2348 protected function getSubscript($arr): array 2349 { 2350 $retArr = []; 2351 while (\is_array($arr)) { 2352 reset($arr); 2353 $key = key($arr); 2354 $retArr[] = $key; 2355 if (isset($arr[$key])) { 2356 $arr = $arr[$key]; 2357 } else { 2358 break; 2359 } 2360 } 2361 return $retArr; 2362 } 2363 2364 /** 2365 * Get query 2366 * 2367 * @param array $queryConfig 2368 * @param string $pad 2369 * @return string 2370 */ 2371 protected function getQuery($queryConfig, $pad = '') 2372 { 2373 $qs = ''; 2374 // Since we don't traverse the array using numeric keys in the upcoming whileloop make sure it's fresh and clean 2375 ksort($queryConfig); 2376 $first = true; 2377 foreach ($queryConfig as $key => $conf) { 2378 $conf = $this->convertIso8601DatetimeStringToUnixTimestamp($conf); 2379 switch ($conf['type']) { 2380 case 'newlevel': 2381 $qs .= LF . $pad . trim($conf['operator']) . ' (' . $this->getQuery( 2382 $queryConfig[$key]['nl'], 2383 $pad . ' ' 2384 ) . LF . $pad . ')'; 2385 break; 2386 default: 2387 $qs .= LF . $pad . $this->getQuerySingle($conf, $first); 2388 } 2389 $first = false; 2390 } 2391 return $qs; 2392 } 2393 2394 /** 2395 * Convert ISO-8601 timestamp (string) into unix timestamp (int) 2396 * 2397 * @param array $conf 2398 * @return array 2399 */ 2400 protected function convertIso8601DatetimeStringToUnixTimestamp(array $conf): array 2401 { 2402 if ($this->isDateOfIso8601Format($conf['inputValue'] ?? '')) { 2403 $conf['inputValue'] = strtotime($conf['inputValue']); 2404 if ($this->isDateOfIso8601Format($conf['inputValue1'] ?? '')) { 2405 $conf['inputValue1'] = strtotime($conf['inputValue1']); 2406 } 2407 } 2408 2409 return $conf; 2410 } 2411 2412 /** 2413 * Checks if the given value is of the ISO 8601 format. 2414 * 2415 * @param mixed $date 2416 * @return bool 2417 */ 2418 protected function isDateOfIso8601Format($date): bool 2419 { 2420 if (!is_int($date) && !is_string($date)) { 2421 return false; 2422 } 2423 $format = 'Y-m-d\\TH:i:s\\Z'; 2424 $formattedDate = \DateTime::createFromFormat($format, (string)$date); 2425 return $formattedDate && $formattedDate->format($format) === $date; 2426 } 2427 2428 /** 2429 * Get single query 2430 * 2431 * @param array $conf 2432 * @param bool $first 2433 * @return string 2434 */ 2435 protected function getQuerySingle($conf, $first) 2436 { 2437 $comparison = (int)($conf['comparison'] ?? 0); 2438 $qs = ''; 2439 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->table); 2440 $prefix = $this->enablePrefix ? $this->table . '.' : ''; 2441 if (!$first) { 2442 // Is it OK to insert the AND operator if none is set? 2443 $operator = strtoupper(trim($conf['operator'] ?? '')); 2444 if (!in_array($operator, ['AND', 'OR'], true)) { 2445 $operator = 'AND'; 2446 } 2447 $qs .= $operator . ' '; 2448 } 2449 $qsTmp = str_replace('#FIELD#', $prefix . trim(substr($conf['type'], 6)), $this->compSQL[$comparison] ?? ''); 2450 $inputVal = $this->cleanInputVal($conf); 2451 if ($comparison === 68 || $comparison === 69) { 2452 $inputVal = explode(',', (string)$inputVal); 2453 foreach ($inputVal as $key => $fileName) { 2454 $inputVal[$key] = $queryBuilder->quote($fileName); 2455 } 2456 $inputVal = implode(',', $inputVal); 2457 $qsTmp = str_replace('#VALUE#', $inputVal, $qsTmp); 2458 } elseif ($comparison === 162 || $comparison === 163) { 2459 $inputValArray = explode(',', (string)$inputVal); 2460 $inputVal = 0; 2461 foreach ($inputValArray as $fileName) { 2462 $inputVal += (int)$fileName; 2463 } 2464 $qsTmp = str_replace('#VALUE#', (string)$inputVal, $qsTmp); 2465 } else { 2466 if (is_array($inputVal)) { 2467 $inputVal = $inputVal[0]; 2468 } 2469 // @todo This is weired, as it seems that it quotes the value as string and remove 2470 // quotings using the trim() method. Should be investagated/refactored. 2471 $qsTmp = str_replace('#VALUE#', trim($queryBuilder->quote((string)$inputVal), '\''), $qsTmp); 2472 } 2473 if ($comparison === 37 || $comparison === 36 || $comparison === 66 || $comparison === 67 || $comparison === 100 || $comparison === 101) { 2474 // between: 2475 $inputVal = $this->cleanInputVal($conf, '1'); 2476 // @todo This is weired, as it seems that it quotes the value as string and remove 2477 // quotings using the trim() method. Should be investagated/refactored. 2478 $qsTmp = str_replace('#VALUE1#', trim($queryBuilder->quote((string)$inputVal), '\''), $qsTmp); 2479 } 2480 $qs .= trim((string)$qsTmp); 2481 return $qs; 2482 } 2483 2484 /** 2485 * Clean input value 2486 * 2487 * @param array $conf 2488 * @param string $suffix 2489 * @return string|int|float|null 2490 */ 2491 protected function cleanInputVal($conf, $suffix = '') 2492 { 2493 $comparison = (int)($conf['comparison'] ?? 0); 2494 if ($comparison >> 5 === 0 || ($comparison === 32 || $comparison === 33 || $comparison === 64 || $comparison === 65 || $comparison === 66 || $comparison === 67 || $comparison === 96 || $comparison === 97)) { 2495 $inputVal = $conf['inputValue' . $suffix] ?? null; 2496 } elseif ($comparison === 39 || $comparison === 38) { 2497 // in list: 2498 $inputVal = implode(',', GeneralUtility::intExplode(',', ($conf['inputValue' . $suffix] ?? ''))); 2499 } elseif ($comparison === 68 || $comparison === 69 || $comparison === 162 || $comparison === 163) { 2500 // in list: 2501 if (is_array($conf['inputValue' . $suffix] ?? false)) { 2502 $inputVal = implode(',', $conf['inputValue' . $suffix]); 2503 } elseif ($conf['inputValue' . $suffix] ?? false) { 2504 $inputVal = $conf['inputValue' . $suffix]; 2505 } else { 2506 $inputVal = 0; 2507 } 2508 } elseif (!is_array($conf['inputValue' . $suffix] ?? null) && strtotime($conf['inputValue' . $suffix] ?? '')) { 2509 $inputVal = $conf['inputValue' . $suffix]; 2510 } elseif (!is_array($conf['inputValue' . $suffix] ?? null) && MathUtility::canBeInterpretedAsInteger($conf['inputValue' . $suffix] ?? null)) { 2511 $inputVal = (int)$conf['inputValue' . $suffix]; 2512 } else { 2513 // TODO: Six eyes looked at this code and nobody understood completely what is going on here and why we 2514 // fallback to float casting, the whole class smells like it needs a refactoring. 2515 $inputVal = (float)($conf['inputValue' . $suffix] ?? 0.0); 2516 } 2517 return $inputVal; 2518 } 2519 2520 /** 2521 * Update icon 2522 * 2523 * @return string 2524 */ 2525 protected function updateIcon() 2526 { 2527 return '<button class="btn btn-default" title="Update" name="just_update"><i class="fa fa-refresh fa-fw"></i></button>'; 2528 } 2529 2530 /** 2531 * Get label column 2532 * 2533 * @return string 2534 */ 2535 protected function getLabelCol() 2536 { 2537 return $GLOBALS['TCA'][$this->table]['ctrl']['label']; 2538 } 2539 2540 /** 2541 * Make selector table 2542 * 2543 * @param array $modSettings 2544 * @param string $enableList 2545 * @return string 2546 */ 2547 protected function makeSelectorTable($modSettings, $enableList = 'table,fields,query,group,order,limit') 2548 { 2549 $out = []; 2550 $enableArr = explode(',', $enableList); 2551 $userTsConfig = $this->getBackendUserAuthentication()->getTSConfig(); 2552 2553 // Make output 2554 if (in_array('table', $enableArr) && !($userTsConfig['mod.']['dbint.']['disableSelectATable'] ?? false)) { 2555 $out[] = '<div class="form-group">'; 2556 $out[] = '<label for="SET[queryTable]">Select a table:</label>'; 2557 $out[] = '<div class="row row-cols-auto">'; 2558 $out[] = '<div class="col">'; 2559 $out[] = $this->mkTableSelect('SET[queryTable]', $this->table); 2560 $out[] = '</div>'; 2561 $out[] = '</div>'; 2562 $out[] = '</div>'; 2563 } 2564 if ($this->table) { 2565 // Init fields: 2566 $this->setAndCleanUpExternalLists('queryFields', $modSettings['queryFields'] ?? '', 'uid,' . $this->getLabelCol()); 2567 $this->setAndCleanUpExternalLists('queryGroup', $modSettings['queryGroup'] ?? ''); 2568 $this->setAndCleanUpExternalLists('queryOrder', ($modSettings['queryOrder'] ?? '') . ',' . ($modSettings['queryOrder2'] ?? '')); 2569 // Limit: 2570 $this->extFieldLists['queryLimit'] = $modSettings['queryLimit'] ?? ''; 2571 if (!$this->extFieldLists['queryLimit']) { 2572 $this->extFieldLists['queryLimit'] = 100; 2573 } 2574 $parts = GeneralUtility::intExplode(',', $this->extFieldLists['queryLimit']); 2575 $limitBegin = 0; 2576 $limitLength = (int)($this->extFieldLists['queryLimit'] ?? 0); 2577 if ($parts[1] ?? null) { 2578 $limitBegin = (int)$parts[0]; 2579 $limitLength = (int)$parts[1]; 2580 } 2581 $this->extFieldLists['queryLimit'] = implode(',', array_slice($parts, 0, 2)); 2582 // Insert Descending parts 2583 if ($this->extFieldLists['queryOrder']) { 2584 $descParts = explode(',', ($modSettings['queryOrderDesc'] ?? '') . ',' . ($modSettings['queryOrder2Desc'] ?? '')); 2585 $orderParts = explode(',', $this->extFieldLists['queryOrder']); 2586 $reList = []; 2587 foreach ($orderParts as $kk => $vv) { 2588 $reList[] = $vv . ($descParts[$kk] ? ' DESC' : ''); 2589 } 2590 $this->extFieldLists['queryOrder_SQL'] = implode(',', $reList); 2591 } 2592 // Query Generator: 2593 $this->procesData(($modSettings['queryConfig'] ?? false) ? unserialize($modSettings['queryConfig'] ?? '', ['allowed_classes' => false]) : []); 2594 $this->queryConfig = $this->cleanUpQueryConfig($this->queryConfig); 2595 $this->enableQueryParts = (bool)($modSettings['search_query_smallparts'] ?? false); 2596 $codeArr = $this->getFormElements(); 2597 $queryCode = $this->printCodeArray($codeArr); 2598 if (in_array('fields', $enableArr) && !($userTsConfig['mod.']['dbint.']['disableSelectFields'] ?? false)) { 2599 $out[] = '<div class="form-group form-group-with-button-addon">'; 2600 $out[] = ' <label for="SET[queryFields]">Select fields:</label>'; 2601 $out[] = $this->mkFieldToInputSelect('SET[queryFields]', $this->extFieldLists['queryFields']); 2602 $out[] = '</div>'; 2603 } 2604 if (in_array('query', $enableArr) && !($userTsConfig['mod.']['dbint.']['disableMakeQuery'] ?? false)) { 2605 $out[] = '<div class="form-group">'; 2606 $out[] = ' <label>Make Query:</label>'; 2607 $out[] = $queryCode; 2608 $out[] = '</div>'; 2609 } 2610 if (in_array('group', $enableArr) && !($userTsConfig['mod.']['dbint.']['disableGroupBy'] ?? false)) { 2611 $out[] = '<div class="form-group">'; 2612 $out[] = '<label for="SET[queryGroup]">Group By:</label>'; 2613 $out[] = '<div class="row row-cols-auto">'; 2614 $out[] = '<div class="col">'; 2615 $out[] = $this->mkTypeSelect('SET[queryGroup]', $this->extFieldLists['queryGroup'], ''); 2616 $out[] = '</div>'; 2617 $out[] = '</div>'; 2618 $out[] = '</div>'; 2619 } 2620 if (in_array('order', $enableArr) && !($userTsConfig['mod.']['dbint.']['disableOrderBy'] ?? false)) { 2621 $orderByArr = explode(',', $this->extFieldLists['queryOrder']); 2622 $orderBy = []; 2623 $orderBy[] = '<div class="row row-cols-auto align-items-center">'; 2624 $orderBy[] = '<div class="col">'; 2625 $orderBy[] = $this->mkTypeSelect('SET[queryOrder]', $orderByArr[0], ''); 2626 $orderBy[] = '</div>'; 2627 $orderBy[] = '<div class="col mt-2">'; 2628 $orderBy[] = '<div class="form-check">'; 2629 $orderBy[] = BackendUtility::getFuncCheck(0, 'SET[queryOrderDesc]', $modSettings['queryOrderDesc'] ?? '', '', '', 'id="checkQueryOrderDesc"'); 2630 $orderBy[] = '<label class="form-check-label" for="checkQueryOrderDesc">Descending</label>'; 2631 $orderBy[] = '</div>'; 2632 $orderBy[] = '</div>'; 2633 $orderBy[] = '</div>'; 2634 2635 if ($orderByArr[0]) { 2636 $orderBy[] = '<div class="row row-cols-auto align-items-center mt-2">'; 2637 $orderBy[] = '<div class="col">'; 2638 $orderBy[] = '<div class="input-group">'; 2639 $orderBy[] = $this->mkTypeSelect('SET[queryOrder2]', $orderByArr[1] ?? '', ''); 2640 $orderBy[] = '</div>'; 2641 $orderBy[] = '</div>'; 2642 $orderBy[] = '<div class="col mt-2">'; 2643 $orderBy[] = '<div class="form-check">'; 2644 $orderBy[] = BackendUtility::getFuncCheck(0, 'SET[queryOrder2Desc]', $modSettings['queryOrder2Desc'] ?? false, '', '', 'id="checkQueryOrder2Desc"'); 2645 $orderBy[] = '<label class="form-check-label" for="checkQueryOrder2Desc">Descending</label>'; 2646 $orderBy[] = '</div>'; 2647 $orderBy[] = '</div>'; 2648 $orderBy[] = '</div>'; 2649 } 2650 $out[] = '<div class="form-group">'; 2651 $out[] = ' <label>Order By:</label>'; 2652 $out[] = implode(LF, $orderBy); 2653 $out[] = '</div>'; 2654 } 2655 if (in_array('limit', $enableArr) && !($userTsConfig['mod.']['dbint.']['disableLimit'] ?? false)) { 2656 $limit = []; 2657 $limit[] = '<div class="input-group">'; 2658 $limit[] = ' <span class="input-group-btn">'; 2659 $limit[] = $this->updateIcon(); 2660 $limit[] = ' </span>'; 2661 $limit[] = ' <input type="text" class="form-control" value="' . htmlspecialchars($this->extFieldLists['queryLimit']) . '" name="SET[queryLimit]" id="queryLimit">'; 2662 $limit[] = '</div>'; 2663 2664 $prevLimit = $limitBegin - $limitLength < 0 ? 0 : $limitBegin - $limitLength; 2665 $prevButton = ''; 2666 $nextButton = ''; 2667 2668 if ($limitBegin) { 2669 $prevButton = '<input type="button" class="btn btn-default" value="previous ' . htmlspecialchars((string)$limitLength) . '" data-value="' . htmlspecialchars($prevLimit . ',' . $limitLength) . '">'; 2670 } 2671 if (!$limitLength) { 2672 $limitLength = 100; 2673 } 2674 2675 $nextLimit = $limitBegin + $limitLength; 2676 if ($nextLimit < 0) { 2677 $nextLimit = 0; 2678 } 2679 if ($nextLimit) { 2680 $nextButton = '<input type="button" class="btn btn-default" value="next ' . htmlspecialchars((string)$limitLength) . '" data-value="' . htmlspecialchars($nextLimit . ',' . $limitLength) . '">'; 2681 } 2682 2683 $out[] = '<div class="form-group">'; 2684 $out[] = ' <label>Limit:</label>'; 2685 $out[] = ' <div class="row row-cols-auto">'; 2686 $out[] = ' <div class="col">'; 2687 $out[] = implode(LF, $limit); 2688 $out[] = ' </div>'; 2689 $out[] = ' <div class="col">'; 2690 $out[] = ' <div class="btn-group t3js-limit-submit">'; 2691 $out[] = $prevButton; 2692 $out[] = $nextButton; 2693 $out[] = ' </div>'; 2694 $out[] = ' </div>'; 2695 $out[] = ' <div class="col">'; 2696 $out[] = ' <div class="btn-group t3js-limit-submit">'; 2697 $out[] = ' <input type="button" class="btn btn-default" data-value="10" value="10">'; 2698 $out[] = ' <input type="button" class="btn btn-default" data-value="20" value="20">'; 2699 $out[] = ' <input type="button" class="btn btn-default" data-value="50" value="50">'; 2700 $out[] = ' <input type="button" class="btn btn-default" data-value="100" value="100">'; 2701 $out[] = ' </div>'; 2702 $out[] = ' </div>'; 2703 $out[] = ' </div>'; 2704 $out[] = '</div>'; 2705 } 2706 } 2707 return implode(LF, $out); 2708 } 2709 2710 /** 2711 * Get select query 2712 * 2713 * @param string $qString 2714 * @return string 2715 */ 2716 protected function getSelectQuery($qString = ''): string 2717 { 2718 $backendUserAuthentication = $this->getBackendUserAuthentication(); 2719 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($this->table); 2720 $queryBuilder->getRestrictions()->removeAll(); 2721 if (empty($this->settings['show_deleted'])) { 2722 $queryBuilder->getRestrictions()->add(GeneralUtility::makeInstance(DeletedRestriction::class)); 2723 } 2724 $deleteField = $GLOBALS['TCA'][$this->table]['ctrl']['delete'] ?? ''; 2725 $fieldList = GeneralUtility::trimExplode( 2726 ',', 2727 $this->extFieldLists['queryFields'] 2728 . ',pid' 2729 . ($deleteField ? ',' . $deleteField : '') 2730 ); 2731 $queryBuilder->select(...$fieldList) 2732 ->from($this->table); 2733 2734 if ($this->extFieldLists['queryGroup']) { 2735 $queryBuilder->groupBy(...QueryHelper::parseGroupBy($this->extFieldLists['queryGroup'])); 2736 } 2737 if ($this->extFieldLists['queryOrder']) { 2738 foreach (QueryHelper::parseOrderBy($this->extFieldLists['queryOrder_SQL']) as $orderPair) { 2739 [$fieldName, $order] = $orderPair; 2740 $queryBuilder->addOrderBy($fieldName, $order); 2741 } 2742 } 2743 if ($this->extFieldLists['queryLimit']) { 2744 // Explode queryLimit to fetch the limit and a possible offset 2745 $parts = GeneralUtility::intExplode(',', $this->extFieldLists['queryLimit']); 2746 if ($parts[1] ?? null) { 2747 // Offset and limit are given 2748 $queryBuilder->setFirstResult($parts[0]); 2749 $queryBuilder->setMaxResults($parts[1]); 2750 } else { 2751 // Only the limit is given 2752 $queryBuilder->setMaxResults($parts[0]); 2753 } 2754 } 2755 2756 if (!$backendUserAuthentication->isAdmin()) { 2757 $webMounts = $backendUserAuthentication->returnWebmounts(); 2758 $perms_clause = $backendUserAuthentication->getPagePermsClause(Permission::PAGE_SHOW); 2759 $webMountPageTree = ''; 2760 $webMountPageTreePrefix = ''; 2761 foreach ($webMounts as $webMount) { 2762 if ($webMountPageTree) { 2763 $webMountPageTreePrefix = ','; 2764 } 2765 $webMountPageTree .= $webMountPageTreePrefix 2766 . $this->getTreeList($webMount, 999, 0, $perms_clause); 2767 } 2768 // createNamedParameter() is not used here because the SQL fragment will only include 2769 // the :dcValueX placeholder when the query is returned as a string. The value for the 2770 // placeholder would be lost in the process. 2771 if ($this->table === 'pages') { 2772 $queryBuilder->where( 2773 QueryHelper::stripLogicalOperatorPrefix($perms_clause), 2774 $queryBuilder->expr()->in( 2775 'uid', 2776 GeneralUtility::intExplode(',', $webMountPageTree) 2777 ) 2778 ); 2779 } else { 2780 $queryBuilder->where( 2781 $queryBuilder->expr()->in( 2782 'pid', 2783 GeneralUtility::intExplode(',', $webMountPageTree) 2784 ) 2785 ); 2786 } 2787 } 2788 if (!$qString) { 2789 $qString = $this->getQuery($this->queryConfig); 2790 } 2791 $queryBuilder->andWhere(QueryHelper::stripLogicalOperatorPrefix($qString)); 2792 2793 return $queryBuilder->getSQL(); 2794 } 2795 2796 /** 2797 * @param string $name the field name 2798 * @param string $timestamp ISO-8601 timestamp 2799 * @param string $type [datetime, date, time, timesec, year] 2800 * 2801 * @return string 2802 */ 2803 protected function getDateTimePickerField($name, $timestamp, $type) 2804 { 2805 $value = strtotime($timestamp) ? date($GLOBALS['TYPO3_CONF_VARS']['SYS']['hhmm'] . ' ' . $GLOBALS['TYPO3_CONF_VARS']['SYS']['ddmmyy'], (int)strtotime($timestamp)) : ''; 2806 $id = StringUtility::getUniqueId('dt_'); 2807 $html = []; 2808 $html[] = '<div class="col mb-sm-2">'; 2809 $html[] = ' <div class="input-group" id="' . $id . '-wrapper">'; 2810 $html[] = ' <input data-formengine-input-name="' . htmlspecialchars($name) . '" value="' . $value . '" class="form-control t3js-datetimepicker t3js-clearable" data-date-type="' . htmlspecialchars($type) . '" type="text" id="' . $id . '">'; 2811 $html[] = ' <input name="' . htmlspecialchars($name) . '" value="' . htmlspecialchars($timestamp) . '" type="hidden">'; 2812 $html[] = ' <span class="input-group-btn">'; 2813 $html[] = ' <label class="btn btn-default" for="' . $id . '">'; 2814 $html[] = ' <span class="fa fa-calendar"></span>'; 2815 $html[] = ' </label>'; 2816 $html[] = ' </span>'; 2817 $html[] = ' </div>'; 2818 $html[] = '</div>'; 2819 return implode(LF, $html); 2820 } 2821 2822 protected function getBackendUserAuthentication(): BackendUserAuthentication 2823 { 2824 return $GLOBALS['BE_USER']; 2825 } 2826 2827 protected function getLanguageService(): LanguageService 2828 { 2829 return $GLOBALS['LANG']; 2830 } 2831} 2832