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\Core\Database; 17 18use TYPO3\CMS\Backend\Utility\BackendUtility; 19use TYPO3\CMS\Core\Authentication\BackendUserAuthentication; 20use TYPO3\CMS\Core\Database\Query\QueryHelper; 21use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction; 22use TYPO3\CMS\Core\Localization\LanguageService; 23use TYPO3\CMS\Core\Type\Bitmask\Permission; 24use TYPO3\CMS\Core\Utility\GeneralUtility; 25use TYPO3\CMS\Core\Utility\MathUtility; 26use TYPO3\CMS\Core\Utility\StringUtility; 27 28/** 29 * Class for generating front end for building queries 30 * 31 * @deprecated since v11, will be removed in v12 32 */ 33class QueryGenerator 34{ 35 /** 36 * @var array 37 */ 38 public $lang = [ 39 'OR' => 'or', 40 'AND' => 'and', 41 'comparison' => [ 42 // Type = text offset = 0 43 '0_' => 'contains', 44 '1_' => 'does not contain', 45 '2_' => 'starts with', 46 '3_' => 'does not start with', 47 '4_' => 'ends with', 48 '5_' => 'does not end with', 49 '6_' => 'equals', 50 '7_' => 'does not equal', 51 // Type = number , offset = 32 52 '32_' => 'equals', 53 '33_' => 'does not equal', 54 '34_' => 'is greater than', 55 '35_' => 'is less than', 56 '36_' => 'is between', 57 '37_' => 'is not between', 58 '38_' => 'is in list', 59 '39_' => 'is not in list', 60 '40_' => 'binary AND equals', 61 '41_' => 'binary AND does not equal', 62 '42_' => 'binary OR equals', 63 '43_' => 'binary OR does not equal', 64 // Type = multiple, relation, offset = 64 65 '64_' => 'equals', 66 '65_' => 'does not equal', 67 '66_' => 'contains', 68 '67_' => 'does not contain', 69 '68_' => 'is in list', 70 '69_' => 'is not in list', 71 '70_' => 'binary AND equals', 72 '71_' => 'binary AND does not equal', 73 '72_' => 'binary OR equals', 74 '73_' => 'binary OR does not equal', 75 // Type = date,time offset = 96 76 '96_' => 'equals', 77 '97_' => 'does not equal', 78 '98_' => 'is greater than', 79 '99_' => 'is less than', 80 '100_' => 'is between', 81 '101_' => 'is not between', 82 '102_' => 'binary AND equals', 83 '103_' => 'binary AND does not equal', 84 '104_' => 'binary OR equals', 85 '105_' => 'binary OR does not equal', 86 // Type = boolean, offset = 128 87 '128_' => 'is True', 88 '129_' => 'is False', 89 // Type = binary , offset = 160 90 '160_' => 'equals', 91 '161_' => 'does not equal', 92 '162_' => 'contains', 93 '163_' => 'does not contain', 94 ], 95 ]; 96 97 /** 98 * @var array 99 */ 100 public $compSQL = [ 101 // Type = text offset = 0 102 '0' => '#FIELD# LIKE \'%#VALUE#%\'', 103 '1' => '#FIELD# NOT LIKE \'%#VALUE#%\'', 104 '2' => '#FIELD# LIKE \'#VALUE#%\'', 105 '3' => '#FIELD# NOT LIKE \'#VALUE#%\'', 106 '4' => '#FIELD# LIKE \'%#VALUE#\'', 107 '5' => '#FIELD# NOT LIKE \'%#VALUE#\'', 108 '6' => '#FIELD# = \'#VALUE#\'', 109 '7' => '#FIELD# != \'#VALUE#\'', 110 // Type = number, offset = 32 111 '32' => '#FIELD# = \'#VALUE#\'', 112 '33' => '#FIELD# != \'#VALUE#\'', 113 '34' => '#FIELD# > #VALUE#', 114 '35' => '#FIELD# < #VALUE#', 115 '36' => '#FIELD# >= #VALUE# AND #FIELD# <= #VALUE1#', 116 '37' => 'NOT (#FIELD# >= #VALUE# AND #FIELD# <= #VALUE1#)', 117 '38' => '#FIELD# IN (#VALUE#)', 118 '39' => '#FIELD# NOT IN (#VALUE#)', 119 '40' => '(#FIELD# & #VALUE#)=#VALUE#', 120 '41' => '(#FIELD# & #VALUE#)!=#VALUE#', 121 '42' => '(#FIELD# | #VALUE#)=#VALUE#', 122 '43' => '(#FIELD# | #VALUE#)!=#VALUE#', 123 // Type = multiple, relation, offset = 64 124 '64' => '#FIELD# = \'#VALUE#\'', 125 '65' => '#FIELD# != \'#VALUE#\'', 126 '66' => '#FIELD# LIKE \'%#VALUE#%\' AND #FIELD# LIKE \'%#VALUE1#%\'', 127 '67' => '(#FIELD# NOT LIKE \'%#VALUE#%\' OR #FIELD# NOT LIKE \'%#VALUE1#%\')', 128 '68' => '#FIELD# IN (#VALUE#)', 129 '69' => '#FIELD# NOT IN (#VALUE#)', 130 '70' => '(#FIELD# & #VALUE#)=#VALUE#', 131 '71' => '(#FIELD# & #VALUE#)!=#VALUE#', 132 '72' => '(#FIELD# | #VALUE#)=#VALUE#', 133 '73' => '(#FIELD# | #VALUE#)!=#VALUE#', 134 // Type = date, offset = 32 135 '96' => '#FIELD# = \'#VALUE#\'', 136 '97' => '#FIELD# != \'#VALUE#\'', 137 '98' => '#FIELD# > #VALUE#', 138 '99' => '#FIELD# < #VALUE#', 139 '100' => '#FIELD# >= #VALUE# AND #FIELD# <= #VALUE1#', 140 '101' => 'NOT (#FIELD# >= #VALUE# AND #FIELD# <= #VALUE1#)', 141 '102' => '(#FIELD# & #VALUE#)=#VALUE#', 142 '103' => '(#FIELD# & #VALUE#)!=#VALUE#', 143 '104' => '(#FIELD# | #VALUE#)=#VALUE#', 144 '105' => '(#FIELD# | #VALUE#)!=#VALUE#', 145 // Type = boolean, offset = 128 146 '128' => '#FIELD# = \'1\'', 147 '129' => '#FIELD# != \'1\'', 148 // Type = binary = 160 149 '160' => '#FIELD# = \'#VALUE#\'', 150 '161' => '#FIELD# != \'#VALUE#\'', 151 '162' => '(#FIELD# & #VALUE#)=#VALUE#', 152 '163' => '(#FIELD# & #VALUE#)=0', 153 ]; 154 155 /** 156 * @var array 157 */ 158 public $comp_offsets = [ 159 'text' => 0, 160 'number' => 1, 161 'multiple' => 2, 162 'relation' => 2, 163 'date' => 3, 164 'time' => 3, 165 'boolean' => 4, 166 'binary' => 5, 167 ]; 168 169 /** 170 * @var string 171 */ 172 public $noWrap = ' nowrap'; 173 174 /** 175 * Form data name prefix 176 * 177 * @var string 178 */ 179 public $name; 180 181 /** 182 * Table for the query 183 * 184 * @var string 185 */ 186 public $table; 187 188 /** 189 * @var array 190 */ 191 public $tableArray; 192 193 /** 194 * Field list 195 * 196 * @var string 197 */ 198 public $fieldList; 199 200 /** 201 * Array of the fields possible 202 * 203 * @var array 204 */ 205 public $fields = []; 206 207 /** 208 * @var array 209 */ 210 public $extFieldLists = []; 211 212 /** 213 * The query config 214 * 215 * @var array 216 */ 217 public $queryConfig = []; 218 219 /** 220 * @var bool 221 */ 222 public $enablePrefix = false; 223 224 /** 225 * @var bool 226 */ 227 public $enableQueryParts = false; 228 229 /** 230 * @var string 231 */ 232 protected $formName = ''; 233 234 /** 235 * @var string 236 */ 237 protected $fieldName; 238 239 /** 240 * @var array Settings, usually from the controller, previously known as MOD_SETTINGS 241 */ 242 protected $settings = []; 243 244 public function __construct() 245 { 246 trigger_error(__CLASS__ . ' will be removed in TYPO3 v12.', E_USER_DEPRECATED); 247 } 248 249 /** 250 * Make a list of fields for current table 251 * 252 * @return string Separated list of fields 253 */ 254 public function makeFieldList() 255 { 256 $fieldListArr = []; 257 if (is_array($GLOBALS['TCA'][$this->table])) { 258 $fieldListArr = array_keys($GLOBALS['TCA'][$this->table]['columns'] ?? []); 259 $fieldListArr[] = 'uid'; 260 $fieldListArr[] = 'pid'; 261 $fieldListArr[] = 'deleted'; 262 if ($GLOBALS['TCA'][$this->table]['ctrl']['tstamp'] ?? false) { 263 $fieldListArr[] = $GLOBALS['TCA'][$this->table]['ctrl']['tstamp']; 264 } 265 if ($GLOBALS['TCA'][$this->table]['ctrl']['crdate'] ?? false) { 266 $fieldListArr[] = $GLOBALS['TCA'][$this->table]['ctrl']['crdate']; 267 } 268 if ($GLOBALS['TCA'][$this->table]['ctrl']['cruser_id'] ?? false) { 269 $fieldListArr[] = $GLOBALS['TCA'][$this->table]['ctrl']['cruser_id']; 270 } 271 if ($GLOBALS['TCA'][$this->table]['ctrl']['sortby'] ?? false) { 272 $fieldListArr[] = $GLOBALS['TCA'][$this->table]['ctrl']['sortby']; 273 } 274 } 275 return implode(',', $fieldListArr); 276 } 277 278 /** 279 * Init function 280 * 281 * @param string $name The name 282 * @param string $table The table name 283 * @param string $fieldList The field list 284 * @param array $settings Module settings like checkboxes in the interface 285 */ 286 public function init($name, $table, $fieldList = '', array $settings = []) 287 { 288 // Analysing the fields in the table. 289 if (is_array($GLOBALS['TCA'][$table])) { 290 $this->name = $name; 291 $this->table = $table; 292 $this->fieldList = $fieldList ?: $this->makeFieldList(); 293 $this->settings = $settings; 294 $fieldArr = GeneralUtility::trimExplode(',', $this->fieldList, true); 295 foreach ($fieldArr as $fieldName) { 296 $fC = $GLOBALS['TCA'][$this->table]['columns'][$fieldName]; 297 $this->fields[$fieldName] = $fC['config']; 298 $this->fields[$fieldName]['exclude'] = $fC['exclude']; 299 if ($this->fields[$fieldName]['type'] === 'user' && !isset($this->fields[$fieldName]['type']['userFunc']) 300 || $this->fields[$fieldName]['type'] === 'none' 301 ) { 302 // Do not list type=none "virtual" fields or query them from db, 303 // and if type is user without defined userFunc 304 unset($this->fields[$fieldName]); 305 continue; 306 } 307 if (is_array($fC) && $fC['label']) { 308 $this->fields[$fieldName]['label'] = rtrim(trim($this->getLanguageService()->sL($fC['label'])), ':'); 309 switch ($this->fields[$fieldName]['type']) { 310 case 'input': 311 if (preg_match('/int|year/i', $this->fields[$fieldName]['eval'])) { 312 $this->fields[$fieldName]['type'] = 'number'; 313 } elseif (preg_match('/time/i', $this->fields[$fieldName]['eval'])) { 314 $this->fields[$fieldName]['type'] = 'time'; 315 } elseif (preg_match('/date/i', $this->fields[$fieldName]['eval'])) { 316 $this->fields[$fieldName]['type'] = 'date'; 317 } else { 318 $this->fields[$fieldName]['type'] = 'text'; 319 } 320 break; 321 case 'check': 322 if (!$this->fields[$fieldName]['items'] || count($this->fields[$fieldName]['items']) <= 1) { 323 $this->fields[$fieldName]['type'] = 'boolean'; 324 } else { 325 $this->fields[$fieldName]['type'] = 'binary'; 326 } 327 break; 328 case 'radio': 329 $this->fields[$fieldName]['type'] = 'multiple'; 330 break; 331 case 'select': 332 case 'category': 333 $this->fields[$fieldName]['type'] = 'multiple'; 334 if ($this->fields[$fieldName]['foreign_table']) { 335 $this->fields[$fieldName]['type'] = 'relation'; 336 } 337 if ($this->fields[$fieldName]['special']) { 338 $this->fields[$fieldName]['type'] = 'text'; 339 } 340 break; 341 case 'group': 342 if (($this->fields[$fieldName]['internal_type'] ?? '') !== 'folder') { 343 $this->fields[$fieldName]['type'] = 'relation'; 344 } 345 break; 346 case 'user': 347 case 'flex': 348 case 'passthrough': 349 case 'none': 350 case 'text': 351 default: 352 $this->fields[$fieldName]['type'] = 'text'; 353 } 354 } else { 355 $this->fields[$fieldName]['label'] = '[FIELD: ' . $fieldName . ']'; 356 switch ($fieldName) { 357 case 'pid': 358 $this->fields[$fieldName]['type'] = 'relation'; 359 $this->fields[$fieldName]['allowed'] = 'pages'; 360 break; 361 case 'cruser_id': 362 $this->fields[$fieldName]['type'] = 'relation'; 363 $this->fields[$fieldName]['allowed'] = 'be_users'; 364 break; 365 case 'tstamp': 366 case 'crdate': 367 $this->fields[$fieldName]['type'] = 'time'; 368 break; 369 case 'deleted': 370 $this->fields[$fieldName]['type'] = 'boolean'; 371 break; 372 default: 373 $this->fields[$fieldName]['type'] = 'number'; 374 } 375 } 376 } 377 } 378 /* // EXAMPLE: 379 $this->queryConfig = array( 380 array( 381 'operator' => 'AND', 382 'type' => 'FIELD_space_before_class', 383 ), 384 array( 385 'operator' => 'AND', 386 'type' => 'FIELD_records', 387 'negate' => 1, 388 'inputValue' => 'foo foo' 389 ), 390 array( 391 'type' => 'newlevel', 392 'nl' => array( 393 array( 394 'operator' => 'AND', 395 'type' => 'FIELD_space_before_class', 396 'negate' => 1, 397 'inputValue' => 'foo foo' 398 ), 399 array( 400 'operator' => 'AND', 401 'type' => 'FIELD_records', 402 'negate' => 1, 403 'inputValue' => 'foo foo' 404 ) 405 ) 406 ), 407 array( 408 'operator' => 'OR', 409 'type' => 'FIELD_maillist', 410 ) 411 ); 412 */ 413 $this->initUserDef(); 414 } 415 416 /** 417 * Set and clean up external lists 418 * 419 * @param string $name The name 420 * @param string $list The list 421 * @param string $force 422 */ 423 public function setAndCleanUpExternalLists($name, $list, $force = '') 424 { 425 $fields = array_unique(GeneralUtility::trimExplode(',', $list . ',' . $force, true)); 426 $reList = []; 427 foreach ($fields as $fieldName) { 428 if ($this->fields[$fieldName]) { 429 $reList[] = $fieldName; 430 } 431 } 432 $this->extFieldLists[$name] = implode(',', $reList); 433 } 434 435 /** 436 * Process data 437 * 438 * @param array $qC Query config 439 */ 440 public function procesData($qC = []) 441 { 442 $this->queryConfig = $qC; 443 $POST = GeneralUtility::_POST(); 444 // If delete... 445 if ($POST['qG_del']) { 446 // Initialize array to work on, save special parameters 447 $ssArr = $this->getSubscript($POST['qG_del']); 448 $workArr = &$this->queryConfig; 449 $ssArrSize = count($ssArr) - 1; 450 $i = 0; 451 for (; $i < $ssArrSize; $i++) { 452 $workArr = &$workArr[$ssArr[$i]]; 453 } 454 // Delete the entry and move the other entries 455 unset($workArr[$ssArr[$i]]); 456 $workArrSize = count($workArr); 457 for ($j = $ssArr[$i]; $j < $workArrSize; $j++) { 458 $workArr[$j] = $workArr[$j + 1]; 459 unset($workArr[$j + 1]); 460 } 461 } 462 // If insert... 463 if ($POST['qG_ins']) { 464 // Initialize array to work on, save special parameters 465 $ssArr = $this->getSubscript($POST['qG_ins']); 466 $workArr = &$this->queryConfig; 467 $ssArrSize = count($ssArr) - 1; 468 $i = 0; 469 for (; $i < $ssArrSize; $i++) { 470 $workArr = &$workArr[$ssArr[$i]]; 471 } 472 // Move all entries above position where new entry is to be inserted 473 $workArrSize = count($workArr); 474 for ($j = $workArrSize; $j > $ssArr[$i]; $j--) { 475 $workArr[$j] = $workArr[$j - 1]; 476 } 477 // Clear new entry position 478 unset($workArr[$ssArr[$i] + 1]); 479 $workArr[$ssArr[$i] + 1]['type'] = 'FIELD_'; 480 } 481 // If move up... 482 if ($POST['qG_up']) { 483 // Initialize array to work on 484 $ssArr = $this->getSubscript($POST['qG_up']); 485 $workArr = &$this->queryConfig; 486 $ssArrSize = count($ssArr) - 1; 487 $i = 0; 488 for (; $i < $ssArrSize; $i++) { 489 $workArr = &$workArr[$ssArr[$i]]; 490 } 491 // Swap entries 492 $qG_tmp = $workArr[$ssArr[$i]]; 493 $workArr[$ssArr[$i]] = $workArr[$ssArr[$i] - 1]; 494 $workArr[$ssArr[$i] - 1] = $qG_tmp; 495 } 496 // If new level... 497 if ($POST['qG_nl']) { 498 // Initialize array to work on 499 $ssArr = $this->getSubscript($POST['qG_nl']); 500 $workArr = &$this->queryConfig; 501 $ssArraySize = count($ssArr) - 1; 502 $i = 0; 503 for (; $i < $ssArraySize; $i++) { 504 $workArr = &$workArr[$ssArr[$i]]; 505 } 506 // Do stuff: 507 $tempEl = $workArr[$ssArr[$i]]; 508 if (is_array($tempEl)) { 509 if ($tempEl['type'] !== 'newlevel') { 510 $workArr[$ssArr[$i]] = [ 511 'type' => 'newlevel', 512 'operator' => $tempEl['operator'], 513 'nl' => [$tempEl], 514 ]; 515 } 516 } 517 } 518 // If collapse level... 519 if ($POST['qG_remnl']) { 520 // Initialize array to work on 521 $ssArr = $this->getSubscript($POST['qG_remnl']); 522 $workArr = &$this->queryConfig; 523 $ssArrSize = count($ssArr) - 1; 524 $i = 0; 525 for (; $i < $ssArrSize; $i++) { 526 $workArr = &$workArr[$ssArr[$i]]; 527 } 528 // Do stuff: 529 $tempEl = $workArr[$ssArr[$i]]; 530 if (is_array($tempEl)) { 531 if ($tempEl['type'] === 'newlevel') { 532 $a1 = array_slice($workArr, 0, $ssArr[$i]); 533 $a2 = array_slice($workArr, $ssArr[$i]); 534 array_shift($a2); 535 $a3 = $tempEl['nl']; 536 $a3[0]['operator'] = $tempEl['operator']; 537 $workArr = array_merge($a1, $a3, $a2); 538 } 539 } 540 } 541 } 542 543 /** 544 * Clean up query config 545 * 546 * @param array $queryConfig Query config 547 * @return array 548 */ 549 public function cleanUpQueryConfig($queryConfig) 550 { 551 // Since we don't traverse the array using numeric keys in the upcoming while-loop make sure it's fresh and clean before displaying 552 if (is_array($queryConfig)) { 553 ksort($queryConfig); 554 } else { 555 // queryConfig should never be empty! 556 if (!isset($queryConfig[0]) || empty($queryConfig[0]['type'])) { 557 // Make sure queryConfig is an array 558 $queryConfig = []; 559 $queryConfig[0] = ['type' => 'FIELD_']; 560 } 561 } 562 // Traverse: 563 foreach ($queryConfig as $key => $conf) { 564 $fieldName = ''; 565 if (strpos($conf['type'], 'FIELD_') === 0) { 566 $fieldName = substr($conf['type'], 6); 567 $fieldType = $this->fields[$fieldName]['type']; 568 } elseif ($conf['type'] === 'newlevel') { 569 $fieldType = $conf['type']; 570 } else { 571 $fieldType = 'ignore'; 572 } 573 switch ($fieldType) { 574 case 'newlevel': 575 if (!$queryConfig[$key]['nl']) { 576 $queryConfig[$key]['nl'][0]['type'] = 'FIELD_'; 577 } 578 $queryConfig[$key]['nl'] = $this->cleanUpQueryConfig($queryConfig[$key]['nl']); 579 break; 580 case 'userdef': 581 $queryConfig[$key] = $this->userDefCleanUp($queryConfig[$key]); 582 break; 583 case 'ignore': 584 default: 585 $verifiedName = $this->verifyType($fieldName); 586 $queryConfig[$key]['type'] = 'FIELD_' . $this->verifyType($verifiedName); 587 if ($conf['comparison'] >> 5 != $this->comp_offsets[$fieldType]) { 588 $conf['comparison'] = $this->comp_offsets[$fieldType] << 5; 589 } 590 $queryConfig[$key]['comparison'] = $this->verifyComparison($conf['comparison'], $conf['negate'] ? 1 : 0); 591 $queryConfig[$key]['inputValue'] = $this->cleanInputVal($queryConfig[$key]); 592 $queryConfig[$key]['inputValue1'] = $this->cleanInputVal($queryConfig[$key], '1'); 593 } 594 } 595 return $queryConfig; 596 } 597 598 /** 599 * Get form elements 600 * 601 * @param int $subLevel 602 * @param string $queryConfig 603 * @param string $parent 604 * @return array 605 */ 606 public function getFormElements($subLevel = 0, $queryConfig = '', $parent = '') 607 { 608 $codeArr = []; 609 if (!is_array($queryConfig)) { 610 $queryConfig = $this->queryConfig; 611 } 612 $c = 0; 613 $arrCount = 0; 614 $loopCount = 0; 615 foreach ($queryConfig as $key => $conf) { 616 $fieldName = ''; 617 $subscript = $parent . '[' . $key . ']'; 618 $lineHTML = []; 619 $lineHTML[] = $this->mkOperatorSelect($this->name . $subscript, $conf['operator'], $c > 0, $conf['type'] !== 'FIELD_'); 620 if (strpos($conf['type'], 'FIELD_') === 0) { 621 $fieldName = substr($conf['type'], 6); 622 $this->fieldName = $fieldName; 623 $fieldType = $this->fields[$fieldName]['type']; 624 if ($conf['comparison'] >> 5 != $this->comp_offsets[$fieldType]) { 625 $conf['comparison'] = $this->comp_offsets[$fieldType] << 5; 626 } 627 //nasty nasty... 628 //make sure queryConfig contains _actual_ comparevalue. 629 //mkCompSelect don't care, but getQuery does. 630 $queryConfig[$key]['comparison'] += isset($conf['negate']) - $conf['comparison'] % 2; 631 } elseif ($conf['type'] === 'newlevel') { 632 $fieldType = $conf['type']; 633 } else { 634 $fieldType = 'ignore'; 635 } 636 $fieldPrefix = htmlspecialchars($this->name . $subscript); 637 switch ($fieldType) { 638 case 'ignore': 639 break; 640 case 'newlevel': 641 if (!$queryConfig[$key]['nl']) { 642 $queryConfig[$key]['nl'][0]['type'] = 'FIELD_'; 643 } 644 $lineHTML[] = '<input type="hidden" name="' . $fieldPrefix . '[type]" value="newlevel">'; 645 $codeArr[$arrCount]['sub'] = $this->getFormElements($subLevel + 1, $queryConfig[$key]['nl'], $subscript . '[nl]'); 646 break; 647 case 'userdef': 648 $lineHTML[] = $this->userDef($fieldPrefix, $conf, $fieldName, $fieldType); 649 break; 650 case 'date': 651 $lineHTML[] = '<div class="form-inline">'; 652 $lineHTML[] = $this->makeComparisonSelector($subscript, $fieldName, $conf); 653 if ($conf['comparison'] === 100 || $conf['comparison'] === 101) { 654 // between 655 $lineHTML[] = $this->getDateTimePickerField($fieldPrefix . '[inputValue]', $conf['inputValue'], 'date'); 656 $lineHTML[] = $this->getDateTimePickerField($fieldPrefix . '[inputValue1]', $conf['inputValue1'], 'date'); 657 } else { 658 $lineHTML[] = $this->getDateTimePickerField($fieldPrefix . '[inputValue]', $conf['inputValue'], 'date'); 659 } 660 $lineHTML[] = '</div>'; 661 break; 662 case 'time': 663 $lineHTML[] = '<div class="form-inline">'; 664 $lineHTML[] = $this->makeComparisonSelector($subscript, $fieldName, $conf); 665 if ($conf['comparison'] === 100 || $conf['comparison'] === 101) { 666 // between: 667 $lineHTML[] = $this->getDateTimePickerField($fieldPrefix . '[inputValue]', $conf['inputValue'], 'datetime'); 668 $lineHTML[] = $this->getDateTimePickerField($fieldPrefix . '[inputValue1]', $conf['inputValue1'], 'datetime'); 669 } else { 670 $lineHTML[] = $this->getDateTimePickerField($fieldPrefix . '[inputValue]', $conf['inputValue'], 'datetime'); 671 } 672 $lineHTML[] = '</div>'; 673 break; 674 case 'multiple': 675 case 'binary': 676 case 'relation': 677 $lineHTML[] = '<div class="form-inline">'; 678 $lineHTML[] = $this->makeComparisonSelector($subscript, $fieldName, $conf); 679 if ($conf['comparison'] === 68 || $conf['comparison'] === 69 || $conf['comparison'] === 162 || $conf['comparison'] === 163) { 680 $lineHTML[] = '<select class="form-select" name="' . $fieldPrefix . '[inputValue][]" multiple="multiple">'; 681 } elseif ($conf['comparison'] === 66 || $conf['comparison'] === 67) { 682 if (is_array($conf['inputValue'])) { 683 $conf['inputValue'] = implode(',', $conf['inputValue']); 684 } 685 $lineHTML[] = '<input class="form-control t3js-clearable" type="text" value="' . htmlspecialchars($conf['inputValue']) . '" name="' . $fieldPrefix . '[inputValue]">'; 686 } elseif ($conf['comparison'] === 64) { 687 if (is_array($conf['inputValue'])) { 688 $conf['inputValue'] = $conf['inputValue'][0]; 689 } 690 $lineHTML[] = '<select class="form-select t3js-submit-change" name="' . $fieldPrefix . '[inputValue]">'; 691 } else { 692 $lineHTML[] = '<select class="form-select t3js-submit-change" name="' . $fieldPrefix . '[inputValue]">'; 693 } 694 if ($conf['comparison'] != 66 && $conf['comparison'] != 67) { 695 $lineHTML[] = $this->makeOptionList($fieldName, $conf, $this->table); 696 $lineHTML[] = '</select>'; 697 } 698 $lineHTML[] = '</div>'; 699 break; 700 case 'boolean': 701 $lineHTML[] = '<div class="form-inline">'; 702 $lineHTML[] = $this->makeComparisonSelector($subscript, $fieldName, $conf); 703 $lineHTML[] = '<input type="hidden" value="1" name="' . $fieldPrefix . '[inputValue]">'; 704 $lineHTML[] = '</div>'; 705 break; 706 default: 707 $lineHTML[] = '<div class="form-inline">'; 708 $lineHTML[] = $this->makeComparisonSelector($subscript, $fieldName, $conf); 709 if ($conf['comparison'] === 37 || $conf['comparison'] === 36) { 710 // between: 711 $lineHTML[] = '<input class="form-control t3js-clearable" type="text" value="' . htmlspecialchars($conf['inputValue']) . '" name="' . $fieldPrefix . '[inputValue]">'; 712 $lineHTML[] = '<input class="form-control t3js-clearable" type="text" value="' . htmlspecialchars($conf['inputValue1']) . '" name="' . $fieldPrefix . '[inputValue1]">'; 713 } else { 714 $lineHTML[] = '<input class="form-control t3js-clearable" type="text" value="' . htmlspecialchars($conf['inputValue']) . '" name="' . $fieldPrefix . '[inputValue]">'; 715 } 716 $lineHTML[] = '</div>'; 717 } 718 if ($fieldType !== 'ignore') { 719 $lineHTML[] = '<div class="btn-group" style="margin-top: .5em;">'; 720 $lineHTML[] = $this->updateIcon(); 721 if ($loopCount) { 722 $lineHTML[] = '<button class="btn btn-default" title="Remove condition" name="qG_del' . htmlspecialchars($subscript) . '"><i class="fa fa-trash fa-fw"></i></button>'; 723 } 724 $lineHTML[] = '<button class="btn btn-default" title="Add condition" name="qG_ins' . htmlspecialchars($subscript) . '"><i class="fa fa-plus fa-fw"></i></button>'; 725 if ($c != 0) { 726 $lineHTML[] = '<button class="btn btn-default" title="Move up" name="qG_up' . htmlspecialchars($subscript) . '"><i class="fa fa-chevron-up fa-fw"></i></button>'; 727 } 728 if ($c != 0 && $fieldType !== 'newlevel') { 729 $lineHTML[] = '<button class="btn btn-default" title="New level" name="qG_nl' . htmlspecialchars($subscript) . '"><i class="fa fa-chevron-right fa-fw"></i></button>'; 730 } 731 if ($fieldType === 'newlevel') { 732 $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>'; 733 } 734 $lineHTML[] = '</div>'; 735 $codeArr[$arrCount]['html'] = implode(LF, $lineHTML); 736 $codeArr[$arrCount]['query'] = $this->getQuerySingle($conf, $c === 0); 737 $arrCount++; 738 $c++; 739 } 740 $loopCount = 1; 741 } 742 $this->queryConfig = $queryConfig; 743 return $codeArr; 744 } 745 746 /** 747 * @param string $subscript 748 * @param string $fieldName 749 * @param array $conf 750 * 751 * @return string 752 */ 753 protected function makeComparisonSelector($subscript, $fieldName, $conf) 754 { 755 $fieldPrefix = $this->name . $subscript; 756 $lineHTML = []; 757 $lineHTML[] = $this->mkTypeSelect($fieldPrefix . '[type]', $fieldName); 758 $lineHTML[] = ' <div class="input-group">'; 759 $lineHTML[] = $this->mkCompSelect($fieldPrefix . '[comparison]', $conf['comparison'], $conf['negate'] ? 1 : 0); 760 $lineHTML[] = ' <div class="input-group-addon">'; 761 $lineHTML[] = ' <input type="checkbox" class="checkbox t3js-submit-click"' . ($conf['negate'] ? ' checked' : '') . ' name="' . htmlspecialchars($fieldPrefix) . '[negate]">'; 762 $lineHTML[] = ' </div>'; 763 $lineHTML[] = ' </div>'; 764 return implode(LF, $lineHTML); 765 } 766 767 /** 768 * Make option list 769 * 770 * @param string $fieldName 771 * @param array $conf 772 * @param string $table 773 * @return string 774 */ 775 public function makeOptionList($fieldName, $conf, $table) 776 { 777 $from_table_Arr = []; 778 $out = []; 779 $fieldSetup = $this->fields[$fieldName]; 780 $languageService = $this->getLanguageService(); 781 if ($fieldSetup['type'] === 'multiple') { 782 $optGroupOpen = false; 783 foreach ($fieldSetup['items'] as $val) { 784 if (strpos($val[0], 'LLL:') === 0) { 785 $value = $languageService->sL($val[0]); 786 } else { 787 $value = $val[0]; 788 } 789 $itemVal = (string)($val[1] ?? ''); 790 if ($itemVal === '--div--') { 791 if ($optGroupOpen) { 792 $out[] = '</optgroup>'; 793 } 794 $optGroupOpen = true; 795 $out[] = '<optgroup label="' . htmlspecialchars($value) . '">'; 796 } elseif (GeneralUtility::inList($conf['inputValue'], $itemVal)) { 797 $out[] = '<option value="' . htmlspecialchars($itemVal) . '" selected>' . htmlspecialchars($value) . '</option>'; 798 } else { 799 $out[] = '<option value="' . htmlspecialchars($itemVal) . '">' . htmlspecialchars($value) . '</option>'; 800 } 801 } 802 if ($optGroupOpen) { 803 $out[] = '</optgroup>'; 804 } 805 } 806 if ($fieldSetup['type'] === 'binary') { 807 foreach ($fieldSetup['items'] as $key => $val) { 808 if (strpos($val[0], 'LLL:') === 0) { 809 $value = $languageService->sL($val[0]); 810 } else { 811 $value = $val[0]; 812 } 813 $itemVal = (string)(2 ** $key); 814 if (GeneralUtility::inList($conf['inputValue'], $itemVal)) { 815 $out[] = '<option value="' . $itemVal . '" selected>' . htmlspecialchars($value) . '</option>'; 816 } else { 817 $out[] = '<option value="' . $itemVal . '">' . htmlspecialchars($value) . '</option>'; 818 } 819 } 820 } 821 if ($fieldSetup['type'] === 'relation') { 822 $useTablePrefix = 0; 823 $dontPrefixFirstTable = 0; 824 if ($fieldSetup['items']) { 825 foreach ($fieldSetup['items'] as $val) { 826 if (strpos($val[0], 'LLL:') === 0) { 827 $value = $languageService->sL($val[0]); 828 } else { 829 $value = $val[0]; 830 } 831 $outputValue = (string)($val[1] ?? ''); 832 if ($outputValue && GeneralUtility::inList($conf['inputValue'], $outputValue)) { 833 $out[] = '<option value="' . htmlspecialchars($outputValue) . '" selected>' . htmlspecialchars($value) . '</option>'; 834 } else { 835 $out[] = '<option value="' . htmlspecialchars($outputValue) . '">' . htmlspecialchars($value) . '</option>'; 836 } 837 } 838 } 839 if (str_contains($fieldSetup['allowed'], ',')) { 840 $from_table_Arr = explode(',', $fieldSetup['allowed']); 841 $useTablePrefix = 1; 842 if (!$fieldSetup['prepend_tname']) { 843 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($table); 844 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class)); 845 $statement = $queryBuilder->select($fieldName) 846 ->from($table) 847 ->execute(); 848 while ($row = $statement->fetchAssociative()) { 849 if (str_contains($row[$fieldName], ',')) { 850 $checkContent = explode(',', $row[$fieldName]); 851 foreach ($checkContent as $singleValue) { 852 if (!str_contains($singleValue, '_')) { 853 $dontPrefixFirstTable = 1; 854 } 855 } 856 } else { 857 $singleValue = $row[$fieldName]; 858 if ($singleValue !== '' && !str_contains($singleValue, '_')) { 859 $dontPrefixFirstTable = 1; 860 } 861 } 862 } 863 } 864 } else { 865 $from_table_Arr[0] = $fieldSetup['allowed']; 866 } 867 if ($fieldSetup['prepend_tname']) { 868 $useTablePrefix = 1; 869 } 870 if ($fieldSetup['foreign_table']) { 871 $from_table_Arr[0] = $fieldSetup['foreign_table']; 872 } 873 $counter = 0; 874 $tablePrefix = ''; 875 $backendUserAuthentication = $this->getBackendUserAuthentication(); 876 $outArray = []; 877 $labelFieldSelect = []; 878 foreach ($from_table_Arr as $from_table) { 879 $useSelectLabels = false; 880 $useAltSelectLabels = false; 881 if ($useTablePrefix && !$dontPrefixFirstTable && $counter != 1 || $counter === 1) { 882 $tablePrefix = $from_table . '_'; 883 } 884 $counter = 1; 885 if (is_array($GLOBALS['TCA'][$from_table])) { 886 $labelField = $GLOBALS['TCA'][$from_table]['ctrl']['label']; 887 $altLabelField = $GLOBALS['TCA'][$from_table]['ctrl']['label_alt']; 888 if ($GLOBALS['TCA'][$from_table]['columns'][$labelField]['config']['items']) { 889 foreach ($GLOBALS['TCA'][$from_table]['columns'][$labelField]['config']['items'] as $labelArray) { 890 if (strpos($labelArray[0], 'LLL:') === 0) { 891 $labelFieldSelect[$labelArray[1]] = $languageService->sL($labelArray[0]); 892 } else { 893 $labelFieldSelect[$labelArray[1]] = $labelArray[0]; 894 } 895 } 896 $useSelectLabels = true; 897 } 898 $altLabelFieldSelect = []; 899 if ($GLOBALS['TCA'][$from_table]['columns'][$altLabelField]['config']['items']) { 900 foreach ($GLOBALS['TCA'][$from_table]['columns'][$altLabelField]['config']['items'] as $altLabelArray) { 901 if (strpos($altLabelArray[0], 'LLL:') === 0) { 902 $altLabelFieldSelect[$altLabelArray[1]] = $languageService->sL($altLabelArray[0]); 903 } else { 904 $altLabelFieldSelect[$altLabelArray[1]] = $altLabelArray[0]; 905 } 906 } 907 $useAltSelectLabels = true; 908 } 909 910 if (!$this->tableArray[$from_table]) { 911 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($from_table); 912 if (isset($this->settings['show_deleted']) && $this->settings['show_deleted']) { 913 $queryBuilder->getRestrictions()->removeAll(); 914 } else { 915 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class)); 916 } 917 $selectFields = ['uid', $labelField]; 918 if ($altLabelField) { 919 $selectFields = array_merge($selectFields, GeneralUtility::trimExplode(',', $altLabelField, true)); 920 } 921 $queryBuilder->select(...$selectFields) 922 ->from($from_table) 923 ->orderBy('uid'); 924 if (!$backendUserAuthentication->isAdmin()) { 925 $webMounts = $backendUserAuthentication->returnWebmounts(); 926 $perms_clause = $backendUserAuthentication->getPagePermsClause(Permission::PAGE_SHOW); 927 $webMountPageTree = ''; 928 $webMountPageTreePrefix = ''; 929 foreach ($webMounts as $webMount) { 930 if ($webMountPageTree) { 931 $webMountPageTreePrefix = ','; 932 } 933 $webMountPageTree .= $webMountPageTreePrefix 934 . $this->getTreeList($webMount, 999, 0, $perms_clause); 935 } 936 if ($from_table === 'pages') { 937 $queryBuilder->where( 938 QueryHelper::stripLogicalOperatorPrefix($perms_clause), 939 $queryBuilder->expr()->in( 940 'uid', 941 $queryBuilder->createNamedParameter( 942 GeneralUtility::intExplode(',', $webMountPageTree), 943 Connection::PARAM_INT_ARRAY 944 ) 945 ) 946 ); 947 } else { 948 $queryBuilder->where( 949 $queryBuilder->expr()->in( 950 'pid', 951 $queryBuilder->createNamedParameter( 952 GeneralUtility::intExplode(',', $webMountPageTree), 953 Connection::PARAM_INT_ARRAY 954 ) 955 ) 956 ); 957 } 958 } 959 $statement = $queryBuilder->execute(); 960 $this->tableArray[$from_table] = []; 961 while ($row = $statement->fetchAssociative()) { 962 $this->tableArray[$from_table][] = $row; 963 } 964 } 965 966 foreach ($this->tableArray[$from_table] as $key => $val) { 967 if ($useSelectLabels) { 968 $outArray[$tablePrefix . $val['uid']] = htmlspecialchars($labelFieldSelect[$val[$labelField]]); 969 } elseif ($val[$labelField]) { 970 $outArray[$tablePrefix . $val['uid']] = htmlspecialchars($val[$labelField]); 971 } elseif ($useAltSelectLabels) { 972 $outArray[$tablePrefix . $val['uid']] = htmlspecialchars($altLabelFieldSelect[$val[$altLabelField]]); 973 } else { 974 $outArray[$tablePrefix . $val['uid']] = htmlspecialchars($val[$altLabelField]); 975 } 976 } 977 if (isset($this->settings['options_sortlabel']) && $this->settings['options_sortlabel'] && is_array($outArray)) { 978 natcasesort($outArray); 979 } 980 } 981 } 982 foreach ($outArray as $key2 => $val2) { 983 $key2 = (string)$key2; 984 if (GeneralUtility::inList($conf['inputValue'], $key2)) { 985 $out[] = '<option value="' . htmlspecialchars($key2) . '" selected>[' . htmlspecialchars($key2) . '] ' . htmlspecialchars($val2) . '</option>'; 986 } else { 987 $out[] = '<option value="' . htmlspecialchars($key2) . '">[' . htmlspecialchars($key2) . '] ' . htmlspecialchars($val2) . '</option>'; 988 } 989 } 990 } 991 return implode(LF, $out); 992 } 993 994 /** 995 * Print code array 996 * 997 * @param array $codeArr 998 * @param int $recursionLevel 999 * @return string 1000 */ 1001 public function printCodeArray($codeArr, $recursionLevel = 0) 1002 { 1003 $out = []; 1004 foreach ($codeArr as $k => $v) { 1005 $out[] = '<div class="card">'; 1006 $out[] = '<div class="card-body">'; 1007 $out[] = $v['html']; 1008 1009 if ($this->enableQueryParts) { 1010 $out[] = '<pre>'; 1011 $out[] = htmlspecialchars($v['query']); 1012 $out[] = '</pre>'; 1013 } 1014 if (is_array($v['sub'])) { 1015 $out[] = '<div>'; 1016 $out[] = $this->printCodeArray($v['sub'], $recursionLevel + 1); 1017 $out[] = '</div>'; 1018 } 1019 $out[] = '</div>'; 1020 $out[] = '</div>'; 1021 } 1022 return implode(LF, $out); 1023 } 1024 1025 /** 1026 * Make operator select 1027 * 1028 * @param string $name 1029 * @param string $op 1030 * @param bool $draw 1031 * @param bool $submit 1032 * @return string 1033 */ 1034 public function mkOperatorSelect($name, $op, $draw, $submit) 1035 { 1036 $out = []; 1037 if ($draw) { 1038 $out[] = '<div class="form-inline">'; 1039 $out[] = '<select class="form-select' . ($submit ? ' t3js-submit-change' : '') . '" name="' . htmlspecialchars($name) . '[operator]">'; 1040 $out[] = ' <option value="AND"' . (!$op || $op === 'AND' ? ' selected' : '') . '>' . htmlspecialchars($this->lang['AND']) . '</option>'; 1041 $out[] = ' <option value="OR"' . ($op === 'OR' ? ' selected' : '') . '>' . htmlspecialchars($this->lang['OR']) . '</option>'; 1042 $out[] = '</select>'; 1043 $out[] = '</div>'; 1044 } else { 1045 $out[] = '<input type="hidden" value="' . htmlspecialchars($op) . '" name="' . htmlspecialchars($name) . '[operator]">'; 1046 } 1047 return implode(LF, $out); 1048 } 1049 1050 /** 1051 * Make type select 1052 * 1053 * @param string $name 1054 * @param string $fieldName 1055 * @param string $prepend 1056 * @return string 1057 */ 1058 public function mkTypeSelect($name, $fieldName, $prepend = 'FIELD_') 1059 { 1060 $out = []; 1061 $out[] = '<select class="form-select t3js-submit-change" name="' . htmlspecialchars($name) . '">'; 1062 $out[] = '<option value=""></option>'; 1063 foreach ($this->fields as $key => $value) { 1064 if (!$value['exclude'] || $this->getBackendUserAuthentication()->check('non_exclude_fields', $this->table . ':' . $key)) { 1065 $label = $this->fields[$key]['label']; 1066 $out[] = '<option value="' . htmlspecialchars($prepend . $key) . '"' . ($key === $fieldName ? ' selected' : '') . '>' . htmlspecialchars($label) . '</option>'; 1067 } 1068 } 1069 $out[] = '</select>'; 1070 return implode(LF, $out); 1071 } 1072 1073 /** 1074 * Verify type 1075 * 1076 * @param string $fieldName 1077 * @return string 1078 */ 1079 public function verifyType($fieldName) 1080 { 1081 $first = ''; 1082 foreach ($this->fields as $key => $value) { 1083 if (!$first) { 1084 $first = $key; 1085 } 1086 if ($key === $fieldName) { 1087 return $key; 1088 } 1089 } 1090 return $first; 1091 } 1092 1093 /** 1094 * Verify comparison 1095 * 1096 * @param string $comparison 1097 * @param int $neg 1098 * @return int 1099 */ 1100 public function verifyComparison($comparison, $neg) 1101 { 1102 $compOffSet = $comparison >> 5; 1103 $first = -1; 1104 for ($i = 32 * $compOffSet + $neg; $i < 32 * ($compOffSet + 1); $i += 2) { 1105 if ($first === -1) { 1106 $first = $i; 1107 } 1108 if ($i >> 1 === $comparison >> 1) { 1109 return $i; 1110 } 1111 } 1112 return $first; 1113 } 1114 1115 /** 1116 * Make field to input select 1117 * 1118 * @param string $name 1119 * @param string $fieldName 1120 * @return string 1121 */ 1122 public function mkFieldToInputSelect($name, $fieldName) 1123 { 1124 $out = []; 1125 $out[] = '<div class="input-group" style="margin-bottom: .5em;">'; 1126 $out[] = ' <span class="input-group-btn">'; 1127 $out[] = $this->updateIcon(); 1128 $out[] = ' </span>'; 1129 $out[] = ' <input type="text" class="form-control t3js-clearable" value="' . htmlspecialchars($fieldName) . '" name="' . htmlspecialchars($name) . '">'; 1130 $out[] = '</div>'; 1131 1132 $out[] = '<select class="form-select t3js-addfield" name="_fieldListDummy" size="5" data-field="' . htmlspecialchars($name) . '">'; 1133 foreach ($this->fields as $key => $value) { 1134 if (!$value['exclude'] || $this->getBackendUserAuthentication()->check('non_exclude_fields', $this->table . ':' . $key)) { 1135 $label = $this->fields[$key]['label']; 1136 $out[] = '<option value="' . htmlspecialchars($key) . '"' . ($key === $fieldName ? ' selected' : '') . '>' . htmlspecialchars($label) . '</option>'; 1137 } 1138 } 1139 $out[] = '</select>'; 1140 return implode(LF, $out); 1141 } 1142 1143 /** 1144 * Make table select 1145 * 1146 * @param string $name 1147 * @param string $cur 1148 * @return string 1149 */ 1150 public function mkTableSelect($name, $cur) 1151 { 1152 $out = []; 1153 $out[] = '<select class="form-select t3js-submit-change" name="' . $name . '">'; 1154 $out[] = '<option value=""></option>'; 1155 foreach ($GLOBALS['TCA'] as $tN => $value) { 1156 if ($this->getBackendUserAuthentication()->check('tables_select', $tN)) { 1157 $out[] = '<option value="' . htmlspecialchars($tN) . '"' . ($tN === $cur ? ' selected' : '') . '>' . htmlspecialchars($this->getLanguageService()->sL($GLOBALS['TCA'][$tN]['ctrl']['title'])) . '</option>'; 1158 } 1159 } 1160 $out[] = '</select>'; 1161 return implode(LF, $out); 1162 } 1163 1164 /** 1165 * Make comparison select 1166 * 1167 * @param string $name 1168 * @param string $comparison 1169 * @param int $neg 1170 * @return string 1171 */ 1172 public function mkCompSelect($name, $comparison, $neg) 1173 { 1174 $compOffSet = $comparison >> 5; 1175 $out = []; 1176 $out[] = '<select class="form-select t3js-submit-change" name="' . $name . '">'; 1177 for ($i = 32 * $compOffSet + $neg; $i < 32 * ($compOffSet + 1); $i += 2) { 1178 if ($this->lang['comparison'][$i . '_']) { 1179 $out[] = '<option value="' . $i . '"' . ($i >> 1 === $comparison >> 1 ? ' selected' : '') . '>' . htmlspecialchars($this->lang['comparison'][$i . '_']) . '</option>'; 1180 } 1181 } 1182 $out[] = '</select>'; 1183 return implode(LF, $out); 1184 } 1185 1186 /** 1187 * Get subscript 1188 * 1189 * @param array $arr 1190 * @return array 1191 */ 1192 public function getSubscript($arr): array 1193 { 1194 $retArr = []; 1195 while (\is_array($arr)) { 1196 reset($arr); 1197 $key = key($arr); 1198 $retArr[] = $key; 1199 if (isset($arr[$key])) { 1200 $arr = $arr[$key]; 1201 } else { 1202 break; 1203 } 1204 } 1205 return $retArr; 1206 } 1207 1208 /** 1209 * Init user definition 1210 */ 1211 public function initUserDef() 1212 { 1213 } 1214 1215 /** 1216 * User definition 1217 * 1218 * @param string $fieldPrefix 1219 * @param array $conf 1220 * @param string $fieldName 1221 * @param string $fieldType 1222 * 1223 * @return string 1224 */ 1225 public function userDef($fieldPrefix, $conf, $fieldName, $fieldType) 1226 { 1227 return ''; 1228 } 1229 1230 /** 1231 * User definition clean up 1232 * 1233 * @param array $queryConfig 1234 * @return array 1235 */ 1236 public function userDefCleanUp($queryConfig) 1237 { 1238 return $queryConfig; 1239 } 1240 1241 /** 1242 * Get query 1243 * 1244 * @param array $queryConfig 1245 * @param string $pad 1246 * @return string 1247 */ 1248 public function getQuery($queryConfig, $pad = '') 1249 { 1250 $qs = ''; 1251 // Since we don't traverse the array using numeric keys in the upcoming whileloop make sure it's fresh and clean 1252 ksort($queryConfig); 1253 $first = true; 1254 foreach ($queryConfig as $key => $conf) { 1255 $conf = $this->convertIso8601DatetimeStringToUnixTimestamp($conf); 1256 switch ($conf['type']) { 1257 case 'newlevel': 1258 $qs .= LF . $pad . trim($conf['operator']) . ' (' . $this->getQuery( 1259 $queryConfig[$key]['nl'], 1260 $pad . ' ' 1261 ) . LF . $pad . ')'; 1262 break; 1263 case 'userdef': 1264 $qs .= LF . $pad . $this->getUserDefQuery($conf, $first); 1265 break; 1266 default: 1267 $qs .= LF . $pad . $this->getQuerySingle($conf, $first); 1268 } 1269 $first = false; 1270 } 1271 return $qs; 1272 } 1273 1274 /** 1275 * Convert ISO-8601 timestamp (string) into unix timestamp (int) 1276 * 1277 * @param array $conf 1278 * @return array 1279 */ 1280 protected function convertIso8601DatetimeStringToUnixTimestamp(array $conf): array 1281 { 1282 if ($this->isDateOfIso8601Format($conf['inputValue'])) { 1283 $conf['inputValue'] = strtotime($conf['inputValue']); 1284 if ($this->isDateOfIso8601Format($conf['inputValue1'])) { 1285 $conf['inputValue1'] = strtotime($conf['inputValue1']); 1286 } 1287 } 1288 1289 return $conf; 1290 } 1291 1292 /** 1293 * Checks if the given value is of the ISO 8601 format. 1294 * 1295 * @param mixed $date 1296 * @return bool 1297 */ 1298 protected function isDateOfIso8601Format($date): bool 1299 { 1300 if (!is_int($date) && !is_string($date)) { 1301 return false; 1302 } 1303 $format = 'Y-m-d\\TH:i:s\\Z'; 1304 $formattedDate = \DateTime::createFromFormat($format, (string)$date); 1305 return $formattedDate && $formattedDate->format($format) === $date; 1306 } 1307 1308 /** 1309 * Get single query 1310 * 1311 * @param array $conf 1312 * @param bool $first 1313 * @return string 1314 */ 1315 public function getQuerySingle($conf, $first) 1316 { 1317 $qs = ''; 1318 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable($this->table); 1319 $prefix = $this->enablePrefix ? $this->table . '.' : ''; 1320 if (!$first) { 1321 // Is it OK to insert the AND operator if none is set? 1322 $operator = strtoupper(trim($conf['operator'])); 1323 if (!in_array($operator, ['AND', 'OR'], true)) { 1324 $operator = 'AND'; 1325 } 1326 $qs .= $operator . ' '; 1327 } 1328 $qsTmp = str_replace('#FIELD#', $prefix . trim(substr($conf['type'], 6)), $this->compSQL[$conf['comparison']]); 1329 $inputVal = $this->cleanInputVal($conf); 1330 if ($conf['comparison'] === 68 || $conf['comparison'] === 69) { 1331 $inputVal = explode(',', $inputVal); 1332 foreach ($inputVal as $key => $fileName) { 1333 $inputVal[$key] = $queryBuilder->quote($fileName); 1334 } 1335 $inputVal = implode(',', $inputVal); 1336 $qsTmp = str_replace('#VALUE#', $inputVal, $qsTmp); 1337 } elseif ($conf['comparison'] === 162 || $conf['comparison'] === 163) { 1338 $inputValArray = explode(',', $inputVal); 1339 $inputVal = 0; 1340 foreach ($inputValArray as $fileName) { 1341 $inputVal += (int)$fileName; 1342 } 1343 $qsTmp = str_replace('#VALUE#', (string)$inputVal, $qsTmp); 1344 } else { 1345 if (is_array($inputVal)) { 1346 $inputVal = $inputVal[0]; 1347 } 1348 $qsTmp = str_replace('#VALUE#', trim($queryBuilder->quote($inputVal), '\''), $qsTmp); 1349 } 1350 if ($conf['comparison'] === 37 || $conf['comparison'] === 36 || $conf['comparison'] === 66 || $conf['comparison'] === 67 || $conf['comparison'] === 100 || $conf['comparison'] === 101) { 1351 // between: 1352 $inputVal = $this->cleanInputVal($conf, '1'); 1353 $qsTmp = str_replace('#VALUE1#', trim($queryBuilder->quote($inputVal), '\''), $qsTmp); 1354 } 1355 $qs .= trim((string)$qsTmp); 1356 return $qs; 1357 } 1358 1359 /** 1360 * Clear input value 1361 * 1362 * @param array $conf 1363 * @param string $suffix 1364 * @return string 1365 */ 1366 public function cleanInputVal($conf, $suffix = '') 1367 { 1368 if ($conf['comparison'] >> 5 === 0 || ($conf['comparison'] === 32 || $conf['comparison'] === 33 || $conf['comparison'] === 64 || $conf['comparison'] === 65 || $conf['comparison'] === 66 || $conf['comparison'] === 67 || $conf['comparison'] === 96 || $conf['comparison'] === 97)) { 1369 $inputVal = $conf['inputValue' . $suffix]; 1370 } elseif ($conf['comparison'] === 39 || $conf['comparison'] === 38) { 1371 // in list: 1372 $inputVal = implode(',', GeneralUtility::intExplode(',', $conf['inputValue' . $suffix])); 1373 } elseif ($conf['comparison'] === 68 || $conf['comparison'] === 69 || $conf['comparison'] === 162 || $conf['comparison'] === 163) { 1374 // in list: 1375 if (is_array($conf['inputValue' . $suffix])) { 1376 $inputVal = implode(',', $conf['inputValue' . $suffix]); 1377 } elseif ($conf['inputValue' . $suffix]) { 1378 $inputVal = $conf['inputValue' . $suffix]; 1379 } else { 1380 $inputVal = 0; 1381 } 1382 } elseif (!is_array($conf['inputValue' . $suffix]) && strtotime($conf['inputValue' . $suffix])) { 1383 $inputVal = $conf['inputValue' . $suffix]; 1384 } elseif (!is_array($conf['inputValue' . $suffix]) && MathUtility::canBeInterpretedAsInteger($conf['inputValue' . $suffix])) { 1385 $inputVal = (int)$conf['inputValue' . $suffix]; 1386 } else { 1387 // TODO: Six eyes looked at this code and nobody understood completely what is going on here and why we 1388 // fallback to float casting, the whole class smells like it needs a refactoring. 1389 $inputVal = (float)$conf['inputValue' . $suffix]; 1390 } 1391 return $inputVal; 1392 } 1393 1394 /** 1395 * Get user definition query 1396 * 1397 * @param array $qcArr 1398 * @param bool $first 1399 */ 1400 public function getUserDefQuery($qcArr, $first) 1401 { 1402 } 1403 1404 /** 1405 * Update icon 1406 * 1407 * @return string 1408 */ 1409 public function updateIcon() 1410 { 1411 return '<button class="btn btn-default" title="Update" name="just_update"><i class="fa fa-refresh fa-fw"></i></button>'; 1412 } 1413 1414 /** 1415 * Get label column 1416 * 1417 * @return string 1418 */ 1419 public function getLabelCol() 1420 { 1421 return $GLOBALS['TCA'][$this->table]['ctrl']['label']; 1422 } 1423 1424 /** 1425 * Make selector table 1426 * 1427 * @param array $modSettings 1428 * @param string $enableList 1429 * @return string 1430 */ 1431 public function makeSelectorTable($modSettings, $enableList = 'table,fields,query,group,order,limit') 1432 { 1433 $out = []; 1434 $enableArr = explode(',', $enableList); 1435 $userTsConfig = $this->getBackendUserAuthentication()->getTSConfig(); 1436 1437 // Make output 1438 if (in_array('table', $enableArr) && !$userTsConfig['mod.']['dbint.']['disableSelectATable']) { 1439 $out[] = '<div class="form-group">'; 1440 $out[] = ' <label for="SET[queryTable]">Select a table:</label>'; 1441 $out[] = $this->mkTableSelect('SET[queryTable]', $this->table); 1442 $out[] = '</div>'; 1443 } 1444 if ($this->table) { 1445 // Init fields: 1446 $this->setAndCleanUpExternalLists('queryFields', $modSettings['queryFields'], 'uid,' . $this->getLabelCol()); 1447 $this->setAndCleanUpExternalLists('queryGroup', $modSettings['queryGroup']); 1448 $this->setAndCleanUpExternalLists('queryOrder', $modSettings['queryOrder'] . ',' . $modSettings['queryOrder2']); 1449 // Limit: 1450 $this->extFieldLists['queryLimit'] = $modSettings['queryLimit']; 1451 if (!$this->extFieldLists['queryLimit']) { 1452 $this->extFieldLists['queryLimit'] = 100; 1453 } 1454 $parts = GeneralUtility::intExplode(',', $this->extFieldLists['queryLimit']); 1455 $limitBegin = 0; 1456 $limitLength = (int)($this->extFieldLists['queryLimit'] ?? 0); 1457 if ($parts[1]) { 1458 $limitBegin = (int)$parts[0]; 1459 $limitLength = (int)$parts[1]; 1460 } 1461 $this->extFieldLists['queryLimit'] = implode(',', array_slice($parts, 0, 2)); 1462 // Insert Descending parts 1463 if ($this->extFieldLists['queryOrder']) { 1464 $descParts = explode(',', $modSettings['queryOrderDesc'] . ',' . $modSettings['queryOrder2Desc']); 1465 $orderParts = explode(',', $this->extFieldLists['queryOrder']); 1466 $reList = []; 1467 foreach ($orderParts as $kk => $vv) { 1468 $reList[] = $vv . ($descParts[$kk] ? ' DESC' : ''); 1469 } 1470 $this->extFieldLists['queryOrder_SQL'] = implode(',', $reList); 1471 } 1472 // Query Generator: 1473 $this->procesData($modSettings['queryConfig'] ? unserialize($modSettings['queryConfig'], ['allowed_classes' => false]) : []); 1474 $this->queryConfig = $this->cleanUpQueryConfig($this->queryConfig); 1475 $this->enableQueryParts = (bool)$modSettings['search_query_smallparts']; 1476 $codeArr = $this->getFormElements(); 1477 $queryCode = $this->printCodeArray($codeArr); 1478 if (in_array('fields', $enableArr) && !$userTsConfig['mod.']['dbint.']['disableSelectFields']) { 1479 $out[] = '<div class="form-group form-group-with-button-addon">'; 1480 $out[] = ' <label for="SET[queryFields]">Select fields:</label>'; 1481 $out[] = $this->mkFieldToInputSelect('SET[queryFields]', $this->extFieldLists['queryFields']); 1482 $out[] = '</div>'; 1483 } 1484 if (in_array('query', $enableArr) && !$userTsConfig['mod.']['dbint.']['disableMakeQuery']) { 1485 $out[] = '<div class="form-group">'; 1486 $out[] = ' <label>Make Query:</label>'; 1487 $out[] = $queryCode; 1488 $out[] = '</div>'; 1489 } 1490 if (in_array('group', $enableArr) && !$userTsConfig['mod.']['dbint.']['disableGroupBy']) { 1491 $out[] = '<div class="form-group form-inline">'; 1492 $out[] = ' <label for="SET[queryGroup]">Group By:</label>'; 1493 $out[] = $this->mkTypeSelect('SET[queryGroup]', $this->extFieldLists['queryGroup'], ''); 1494 $out[] = '</div>'; 1495 } 1496 if (in_array('order', $enableArr) && !$userTsConfig['mod.']['dbint.']['disableOrderBy']) { 1497 $orderByArr = explode(',', $this->extFieldLists['queryOrder']); 1498 $orderBy = []; 1499 $orderBy[] = $this->mkTypeSelect('SET[queryOrder]', $orderByArr[0], ''); 1500 $orderBy[] = '<div class="form-check">'; 1501 $orderBy[] = BackendUtility::getFuncCheck(0, 'SET[queryOrderDesc]', $modSettings['queryOrderDesc'], '', '', 'id="checkQueryOrderDesc"'); 1502 $orderBy[] = ' <label class="form-check-label" for="checkQueryOrderDesc">'; 1503 $orderBy[] = 'Descending'; 1504 $orderBy[] = ' </label>'; 1505 $orderBy[] = '</div>'; 1506 1507 if ($orderByArr[0]) { 1508 $orderBy[] = $this->mkTypeSelect('SET[queryOrder2]', $orderByArr[1], ''); 1509 $orderBy[] = '<div class="form-check">'; 1510 $orderBy[] = BackendUtility::getFuncCheck(0, 'SET[queryOrder2Desc]', $modSettings['queryOrder2Desc'], '', '', 'id="checkQueryOrder2Desc"'); 1511 $orderBy[] = ' <label class="form-check-label" for="checkQueryOrder2Desc">'; 1512 $orderBy[] = 'Descending'; 1513 $orderBy[] = ' </label>'; 1514 $orderBy[] = '</div>'; 1515 } 1516 $out[] = '<div class="form-group form-inline">'; 1517 $out[] = ' <label>Order By:</label>'; 1518 $out[] = implode(LF, $orderBy); 1519 $out[] = '</div>'; 1520 } 1521 if (in_array('limit', $enableArr) && !$userTsConfig['mod.']['dbint.']['disableLimit']) { 1522 $limit = []; 1523 $limit[] = '<div class="input-group">'; 1524 $limit[] = ' <span class="input-group-btn">'; 1525 $limit[] = $this->updateIcon(); 1526 $limit[] = ' </span>'; 1527 $limit[] = ' <input type="text" class="form-control" value="' . htmlspecialchars($this->extFieldLists['queryLimit']) . '" name="SET[queryLimit]" id="queryLimit">'; 1528 $limit[] = '</div>'; 1529 1530 $prevLimit = $limitBegin - $limitLength < 0 ? 0 : $limitBegin - $limitLength; 1531 $prevButton = ''; 1532 $nextButton = ''; 1533 1534 if ($limitBegin) { 1535 $prevButton = '<input type="button" class="btn btn-default" value="previous ' . htmlspecialchars((string)$limitLength) . '" data-value="' . htmlspecialchars($prevLimit . ',' . $limitLength) . '">'; 1536 } 1537 if (!$limitLength) { 1538 $limitLength = 100; 1539 } 1540 1541 $nextLimit = $limitBegin + $limitLength; 1542 if ($nextLimit < 0) { 1543 $nextLimit = 0; 1544 } 1545 if ($nextLimit) { 1546 $nextButton = '<input type="button" class="btn btn-default" value="next ' . htmlspecialchars((string)$limitLength) . '" data-value="' . htmlspecialchars($nextLimit . ',' . $limitLength) . '">'; 1547 } 1548 1549 $out[] = '<div class="form-group">'; 1550 $out[] = ' <label>Limit:</label>'; 1551 $out[] = ' <div class="form-inline">'; 1552 $out[] = implode(LF, $limit); 1553 $out[] = ' <div class="btn-group t3js-limit-submit">'; 1554 $out[] = $prevButton; 1555 $out[] = $nextButton; 1556 $out[] = ' </div>'; 1557 $out[] = ' <div class="btn-group t3js-limit-submit">'; 1558 $out[] = ' <input type="button" class="btn btn-default" data-value="10" value="10">'; 1559 $out[] = ' <input type="button" class="btn btn-default" data-value="20" value="20">'; 1560 $out[] = ' <input type="button" class="btn btn-default" data-value="50" value="50">'; 1561 $out[] = ' <input type="button" class="btn btn-default" data-value="100" value="100">'; 1562 $out[] = ' </div>'; 1563 $out[] = ' </div>'; 1564 $out[] = '</div>'; 1565 } 1566 } 1567 return implode(LF, $out); 1568 } 1569 1570 /** 1571 * Recursively fetch all descendants of a given page 1572 * 1573 * @param int $id uid of the page 1574 * @param int $depth 1575 * @param int $begin 1576 * @param string $permClause 1577 * @return string comma separated list of descendant pages 1578 */ 1579 public function getTreeList($id, $depth, $begin = 0, $permClause = '') 1580 { 1581 $depth = (int)$depth; 1582 $begin = (int)$begin; 1583 $id = (int)$id; 1584 if ($id < 0) { 1585 $id = abs($id); 1586 } 1587 if ($begin === 0) { 1588 $theList = $id; 1589 } else { 1590 $theList = ''; 1591 } 1592 if ($id && $depth > 0) { 1593 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages'); 1594 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class)); 1595 $queryBuilder->select('uid') 1596 ->from('pages') 1597 ->where( 1598 $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($id, \PDO::PARAM_INT)), 1599 $queryBuilder->expr()->eq('sys_language_uid', 0) 1600 ) 1601 ->orderBy('uid'); 1602 if ($permClause !== '') { 1603 $queryBuilder->andWhere(QueryHelper::stripLogicalOperatorPrefix($permClause)); 1604 } 1605 $statement = $queryBuilder->execute(); 1606 while ($row = $statement->fetchAssociative()) { 1607 if ($begin <= 0) { 1608 $theList .= ',' . $row['uid']; 1609 } 1610 if ($depth > 1) { 1611 $theSubList = $this->getTreeList($row['uid'], $depth - 1, $begin - 1, $permClause); 1612 if (!empty($theList) && !empty($theSubList) && ($theSubList[0] !== ',')) { 1613 $theList .= ','; 1614 } 1615 $theList .= $theSubList; 1616 } 1617 } 1618 } 1619 return $theList; 1620 } 1621 1622 /** 1623 * Get select query 1624 * 1625 * @param string $qString 1626 * @return string 1627 */ 1628 public function getSelectQuery($qString = ''): string 1629 { 1630 $backendUserAuthentication = $this->getBackendUserAuthentication(); 1631 $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($this->table); 1632 if (isset($this->settings['show_deleted']) && $this->settings['show_deleted']) { 1633 $queryBuilder->getRestrictions()->removeAll(); 1634 } else { 1635 $queryBuilder->getRestrictions()->removeAll()->add(GeneralUtility::makeInstance(DeletedRestriction::class)); 1636 } 1637 $fieldList = GeneralUtility::trimExplode( 1638 ',', 1639 $this->extFieldLists['queryFields'] 1640 . ',pid' 1641 . ($GLOBALS['TCA'][$this->table]['ctrl']['delete'] ? ',' . $GLOBALS['TCA'][$this->table]['ctrl']['delete'] : '') 1642 ); 1643 $queryBuilder->select(...$fieldList) 1644 ->from($this->table); 1645 1646 if ($this->extFieldLists['queryGroup']) { 1647 $queryBuilder->groupBy(...QueryHelper::parseGroupBy($this->extFieldLists['queryGroup'])); 1648 } 1649 if ($this->extFieldLists['queryOrder']) { 1650 foreach (QueryHelper::parseOrderBy($this->extFieldLists['queryOrder_SQL']) as $orderPair) { 1651 [$fieldName, $order] = $orderPair; 1652 $queryBuilder->addOrderBy($fieldName, $order); 1653 } 1654 } 1655 if ($this->extFieldLists['queryLimit']) { 1656 // Explode queryLimit to fetch the limit and a possible offset 1657 $parts = GeneralUtility::intExplode(',', $this->extFieldLists['queryLimit']); 1658 if ($parts[1] ?? null) { 1659 // Offset and limit are given 1660 $queryBuilder->setFirstResult($parts[0]); 1661 $queryBuilder->setMaxResults($parts[1]); 1662 } else { 1663 // Only the limit is given 1664 $queryBuilder->setMaxResults($parts[0]); 1665 } 1666 } 1667 1668 if (!$backendUserAuthentication->isAdmin()) { 1669 $webMounts = $backendUserAuthentication->returnWebmounts(); 1670 $perms_clause = $backendUserAuthentication->getPagePermsClause(Permission::PAGE_SHOW); 1671 $webMountPageTree = ''; 1672 $webMountPageTreePrefix = ''; 1673 foreach ($webMounts as $webMount) { 1674 if ($webMountPageTree) { 1675 $webMountPageTreePrefix = ','; 1676 } 1677 $webMountPageTree .= $webMountPageTreePrefix 1678 . $this->getTreeList($webMount, 999, 0, $perms_clause); 1679 } 1680 // createNamedParameter() is not used here because the SQL fragment will only include 1681 // the :dcValueX placeholder when the query is returned as a string. The value for the 1682 // placeholder would be lost in the process. 1683 if ($this->table === 'pages') { 1684 $queryBuilder->where( 1685 QueryHelper::stripLogicalOperatorPrefix($perms_clause), 1686 $queryBuilder->expr()->in( 1687 'uid', 1688 GeneralUtility::intExplode(',', $webMountPageTree) 1689 ) 1690 ); 1691 } else { 1692 $queryBuilder->where( 1693 $queryBuilder->expr()->in( 1694 'pid', 1695 GeneralUtility::intExplode(',', $webMountPageTree) 1696 ) 1697 ); 1698 } 1699 } 1700 if (!$qString) { 1701 $qString = $this->getQuery($this->queryConfig); 1702 } 1703 $queryBuilder->andWhere(QueryHelper::stripLogicalOperatorPrefix($qString)); 1704 1705 return $queryBuilder->getSQL(); 1706 } 1707 1708 /** 1709 * @param string $name the field name 1710 * @param string $timestamp ISO-8601 timestamp 1711 * @param string $type [datetime, date, time, timesec, year] 1712 * 1713 * @return string 1714 */ 1715 protected function getDateTimePickerField($name, $timestamp, $type) 1716 { 1717 $value = strtotime($timestamp) ? date($GLOBALS['TYPO3_CONF_VARS']['SYS']['hhmm'] . ' ' . $GLOBALS['TYPO3_CONF_VARS']['SYS']['ddmmyy'], (int)strtotime($timestamp)) : ''; 1718 $id = StringUtility::getUniqueId('dt_'); 1719 $html = []; 1720 $html[] = '<div class="input-group" id="' . $id . '-wrapper">'; 1721 $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 . '">'; 1722 $html[] = ' <input name="' . htmlspecialchars($name) . '" value="' . htmlspecialchars($timestamp) . '" type="hidden">'; 1723 $html[] = ' <span class="input-group-btn">'; 1724 $html[] = ' <label class="btn btn-default" for="' . $id . '">'; 1725 $html[] = ' <span class="fa fa-calendar"></span>'; 1726 $html[] = ' </label>'; 1727 $html[] = ' </span>'; 1728 $html[] = '</div>'; 1729 return implode(LF, $html); 1730 } 1731 1732 /** 1733 * Sets the current name of the input form. 1734 * 1735 * @param string $formName The name of the form. 1736 */ 1737 public function setFormName($formName) 1738 { 1739 $this->formName = trim($formName); 1740 } 1741 1742 /** 1743 * @return BackendUserAuthentication 1744 */ 1745 protected function getBackendUserAuthentication() 1746 { 1747 return $GLOBALS['BE_USER']; 1748 } 1749 1750 /** 1751 * @return LanguageService 1752 */ 1753 protected function getLanguageService() 1754 { 1755 return $GLOBALS['LANG']; 1756 } 1757} 1758