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