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