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