Report.php 60.3 KB
Newer Older
1
2
3
4
<?php
/***************************************************************
 *  Copyright notice
 *
Carsten  Rose's avatar
Carsten Rose committed
5
 *  (c) 2010 Glowbase GmbH
6
 *
Carsten  Rose's avatar
Carsten Rose committed
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 *  This script is part of the TYPO3 project. The TYPO3 project is
 *  free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  The GNU General Public License can be found at
 *  http://www.gnu.org/copyleft/gpl.html.
 *
 *  This script is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  This copyright notice MUST APPEAR in all copies of the script!
22
23
 ***************************************************************/

Marc Egger's avatar
Marc Egger committed
24
25
26
27
namespace IMATHUZH\Qfq\Core\Report;

use IMATHUZH\Qfq\Core\Database\Database;
use IMATHUZH\Qfq\Core\Evaluate;
28
use IMATHUZH\Qfq\Core\Form\FormAsFile;
Marc Egger's avatar
Marc Egger committed
29
30
31
use IMATHUZH\Qfq\Core\Helper\HelperFile;
use IMATHUZH\Qfq\Core\Helper\KeyValueStringParser;
use IMATHUZH\Qfq\Core\Helper\OnString;
32
use IMATHUZH\Qfq\Core\Helper\Path;
Marc Egger's avatar
Marc Egger committed
33
34
35
36
use IMATHUZH\Qfq\Core\Helper\Sanitize;
use IMATHUZH\Qfq\Core\Helper\Support;
use IMATHUZH\Qfq\Core\Store\Sip;
use IMATHUZH\Qfq\Core\Store\Store;
37

38
39
const DEFAULT_QUESTION = 'question';
const DEFAULT_ICON = 'icon';
40
const DEFAULT_BOOTSTRAP_BUTTON = 'bootstrapButton';
41

42
43
44
45
/**
 * Class Report
 * @package qfq
 */
46
47
class Report {

Carsten  Rose's avatar
Carsten Rose committed
48
49
50
51
52
    /**
     * @var SIP
     */
    private $sip = null;

Carsten  Rose's avatar
Carsten Rose committed
53
54
55
56
57
    /**
     * @var Link
     */
    private $link = null;

58
59
60
61
62
    /**
     * @var Store
     */
    private $store = null;

63
64
65
66
67
    /**
     * @var Evaluate
     */
    private $evaluate = null;

68
69
70
71
72
73
74
    /**
     * @var string
     */
    private $dbAlias = '';

    // frArray[10.50.5.sql][select ...]
    private $frArray = array();
75

76
77
    // $indexArray[10][50][5]   one entry per 'sql' statement
    private $indexArray = array();
78

79
80
81
82
    // TODO to explain
//	private $resultArray = array();
    private $levelCount = 0;
    //private $counter = 0;
83

84
85
86
87
    /**
     * @var Variables
     */
    private $variables = null;
88

89
    /**
90
     * @var Database
91
92
     */
    private $db = null;
93

94
    private $dbIndexData = false;
95

96
97
98
99
100
    /**
     * @var Thumbnail
     */
    private $thumbnail = null;

101
102
103
104
105
    /**
     * @var Monitor
     */
    private $monitor = null;

106
107
108
109
    /**
     * @var array
     */
    private $pageDefaults = array();
110

111
    /**
Carsten  Rose's avatar
Carsten Rose committed
112
113
     * @var array - Emulate global variable: will be set much earlier in other functions. Will be shown in error
     *      messages.
114
     */
115
116
    private $fr_error = array('uid' => '', 'pid' => '', 'row' => '', 'debug_level' => '0', 'full_level' => '');

117
118
    private $phpUnit = false;

119
    private $showDebugInfoFlag = false;
120

121
    /**
122
     * Report constructor.
123
     *
Carsten  Rose's avatar
Carsten Rose committed
124
     * @param array $t3data
125
     * @param Evaluate $evaluate
Carsten  Rose's avatar
Carsten Rose committed
126
     * @param bool $phpUnit
Marc Egger's avatar
Marc Egger committed
127
128
129
130
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
131
     */
132
    public function __construct(array $t3data, Evaluate $evaluate, $phpUnit = false) {
133

134
        #TODO: rewrite $phpUnit to: "if (!defined('PHPUNIT_QFQ')) {...}"
135
        $this->phpUnit = $phpUnit;
136

137
        $t3data["uid"] = $t3data["uid"] ?? 0;
138

139
        $this->sip = new Sip($phpUnit);
140
141
        if ($phpUnit) {
            $this->sip->sipUniqId('badcaffee1234');
142
143
            //TODO Webserver Umgebung besser faken
            $_SERVER['REQUEST_URI'] = 'localhost';
144
145
        }

146
        $this->evaluate = $evaluate;
147
        $this->store = Store::getInstance();
148

149
        $this->showDebugInfoFlag = (Support::findInSet(SYSTEM_SHOW_DEBUG_INFO_YES, $this->store->getVar(SYSTEM_SHOW_DEBUG_INFO, STORE_SYSTEM)));
150

151
152
        $this->checkUpdateSqlLog();

153
154
        $this->pageDefaults[DEFAULT_QUESTION]["pagec"] = "Please confirm!:info";
        $this->pageDefaults[DEFAULT_QUESTION]["paged"] = "Do you really want to delete the record?:warning";
155

156
157
158
159
160
161
        $this->pageDefaults[DEFAULT_ICON]["paged"] = TOKEN_DELETE;
        $this->pageDefaults[DEFAULT_ICON]["pagee"] = TOKEN_EDIT;
        $this->pageDefaults[DEFAULT_ICON]["pageh"] = TOKEN_HELP;
        $this->pageDefaults[DEFAULT_ICON]["pagei"] = TOKEN_INFO;
        $this->pageDefaults[DEFAULT_ICON]["pagen"] = TOKEN_NEW;
        $this->pageDefaults[DEFAULT_ICON]["pages"] = TOKEN_SHOW;
162

163
164
165
166
167
168
        $this->pageDefaults[DEFAULT_BOOTSTRAP_BUTTON]["pagec"] = TOKEN_BOOTSTRAP_BUTTON;
        $this->pageDefaults[DEFAULT_BOOTSTRAP_BUTTON]["paged"] = TOKEN_BOOTSTRAP_BUTTON;
        $this->pageDefaults[DEFAULT_BOOTSTRAP_BUTTON]["pagee"] = TOKEN_BOOTSTRAP_BUTTON;
        $this->pageDefaults[DEFAULT_BOOTSTRAP_BUTTON]["pagen"] = TOKEN_BOOTSTRAP_BUTTON;
        $this->pageDefaults[DEFAULT_BOOTSTRAP_BUTTON]["pages"] = TOKEN_BOOTSTRAP_BUTTON;

169
        // Default should already set in QuickFormQuery() Constructor
Carsten  Rose's avatar
Carsten Rose committed
170
171
172
173
        $this->dbIndexData = $this->store->getVar(TOKEN_DB_INDEX, STORE_TYPO3);
        if ($this->dbIndexData === false) {
            $this->dbIndexData = DB_INDEX_DEFAULT;
        }
174

175
        $this->db = new Database($this->dbIndexData);
176
        $this->variables = new Variables($evaluate, $t3data["uid"]);
177

178
179
        $this->link = new Link($this->sip, $this->dbIndexData, $phpUnit);

180
        // Set static values, which won't change during this run.
181
        $this->fr_error["pid"] = isset($this->variables->resultArray['global.']['page_id']) ? $this->variables->resultArray['global.']['page_id'] : 0;
182
        $this->fr_error["uid"] = $t3data['uid'];
183
        $this->fr_error["debug_level"] = 0;
184

Carsten  Rose's avatar
Carsten Rose committed
185
        // Sanitize function for POST and GET Parameters.
186
        // Merged URL-Parameter (key1, id etc...) in resultArray.
187
        $this->variables->resultArray = array_merge($this->variables->resultArray, array("global." => $this->variables->collectGlobalVariables()));
188
189
190

    }

191
    /**
Carsten  Rose's avatar
Carsten Rose committed
192
193
     * If a variable 'sqlLog' is given in STORE_TYPO3 (=Bodytext) make them relative to SYSTEM_PATH_EXT and copy it to
     * STORE_SYSTEM
194
     *
Marc Egger's avatar
Marc Egger committed
195
196
     * @throws \CodeException
     * @throws \UserFormException
197
198
199
     */
    private function checkUpdateSqlLog() {

200
        $sqlLog = $this->store->getVar(TYPO3_SQL_LOG_ABSOLUTE, STORE_TYPO3);
201
        if (false !== $sqlLog) {
202
            $sqlLogAbsolute = HelperFile::joinPathFilename(Path::absoluteApp(), $sqlLog);
203
            Path::setAbsoluteSqlLogFile($sqlLogAbsolute);
204
205
206
207
208
209
210
211
        }

        $sqlLogMode = $this->store->getVar(TYPO3_SQL_LOG_MODE, STORE_TYPO3);
        if (false !== $sqlLogMode) {
            $this->store->setVar(SYSTEM_SQL_LOG_MODE, $sqlLogMode, STORE_SYSTEM);
        }
    }

212
213
214
    /**
     * Main function. Parses bodytext and iterates over all queries.
     *
215
     * @param $bodyText
216
     * @return string
Marc Egger's avatar
Marc Egger committed
217
218
219
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
220
221
222
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
223
224
225
226
227
     * @throws \Twig\Error\LoaderError
     * @throws \Twig\Error\RuntimeError
     * @throws \Twig\Error\SyntaxError
     * @throws \UserFormException
     * @throws \UserReportException
228
     */
229
    public function process($bodyText) {
230

231
232
233
234
235
        //phpUnit Test: clean environment
        $this->frArray = array();
        $this->indexArray = array();
        $this->levelCount = 0;

236
        // Iteration over Bodytext
237
        $ttLineArray = explode("\n", $bodyText);
238

239
240
        foreach ($ttLineArray as $index => $line) {
            // Fill $frArray, $indexArray, $resultArray
241
            $this->parseLine($line);
242
        }
243

244
245
246
247
248
249
        // Sort array
        $this->sortIndexArray($this->indexArray, $this->generateSortArg());

        // Report
        $content = $this->triggerReport();

250
        return $content;
251
    }
252
253
254
255
256

    /**
     * Split line in level, command, content and fill 'frArray', 'levelCount', 'indexArray'
     * Example: 10.50.5.sql = select * from person
     *
257
     * @param string $ttLine : line to split in level, command, content
Carsten  Rose's avatar
Carsten Rose committed
258
     *
Marc Egger's avatar
Marc Egger committed
259
     * @throws \UserReportException
260
     */
261
    private function parseLine($ttLine) {
262
263
264
265

        // 10.50.5.sql = select ...
        $arr = explode("=", trim($ttLine), 2);

266
        // no elements or only one: do nothing
267
        if (count($arr) < 2) {
268
            return;
269
        }
270

271
272
273
274
        // 10.50.5.sql
        $key = strtolower(trim($arr[0]));

        // comment ?
275
        if (empty($key) || $key[0] === "#") {
276
            return;
277
        }
278

279
        // select ... - if needed, trim surrounding single ticks
280
        $value = trim($arr[1]);
281
        $value = OnString::trimQuote($value);
282
283
284
285
286
287

        // 10.50.5.sql
        $arrKey = explode('.', $key);

        // frCmd = "sql"
        $frCmd = $arrKey[count($arrKey) - 1];
288
        // Check if token is known
289
        if (strpos('|' . strtolower(TOKEN_VALID_LIST) . '|', '|' . $frCmd . '|') === false) {
Marc Egger's avatar
Marc Egger committed
290
            throw new \UserReportException ("Unknown token: $frCmd in Line '$ttLine''", ERROR_UNKNOWN_TOKEN);
291
292
        }

293
294
295
296
297
298
299
300
301
302
303
        // remove last item (cmd)
        unset($arrKey[count($arrKey) - 1]);

        // save elements only if there is a level specified
        if (count($arrKey)) {
            // level = "10.50.5"
            $level = implode(".", $arrKey);

            // fill Array
            $this->setLine($level, $frCmd, $value);
        }
304
    }
305

306
307
308
309
310
    /**
     * @param $level
     * @param $frCmd
     * @param $value
     */
311
312
    private function setLine($level, $frCmd, $value) {

313
314
315
        $index = $level . "." . $frCmd;

        // throw exception if this level was already defined
316
        if (!empty($this->frArray[$index])) {
317
318
            throw new \UserReportException ("Double definition: $index is defined more than once.", ERROR_DOUBLE_DEFINITION);
        }
319
        // store complete line reformatted in frArray
320
        $this->frArray[$index] = $value;
321
322

        // per sql command
323
        //pro sql cmd wird der Indexarray abgefüllt. Dieser wird später verwendet um auf den $frArray zuzugreifen
324
        if ($frCmd === TOKEN_SQL || $frCmd === TOKEN_FUNCTION) {
325
326
327
328
329
            // Remember max level
            $this->levelCount = max(substr_count($level, '.') + 1, $this->levelCount);
            // $indexArray[10][50][5]
            $this->indexArray[] = explode(".", $level);
        }
330
331
332

        // set defaults
        if ($frCmd === TOKEN_SQL) {
333
334
            $arr = explode('|', TOKEN_VALID_LIST);
            foreach ($arr as $key) {
335
                if (!isset($this->frArray[$level . "." . $key])) {
336
                    $this->frArray[$level . "." . $key] = '';
337
                }
338
339
            }
        }
340
    }
341
342
343
344

    /**
     * Sorts the associative array.
     *
Carsten  Rose's avatar
Carsten Rose committed
345
     * @param array $ary : The unsorted Level Array
346
     * @param string $clause : the sort argument 0 ASC, 1 ASC... according to the number of columns
347
     * @param bool|true $ascending
348
     */
349
    private function sortIndexArray(array &$ary, $clause, $ascending = true) {
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393

        $clause = str_ireplace('order by', '', $clause);
        $clause = preg_replace('/\s+/', ' ', $clause);
        $keys = explode(',', $clause);
        $dirMap = array('desc' => 1, 'asc' => -1);
        $def = $ascending ? -1 : 1;

        $keyAry = array();
        $dirAry = array();
        foreach ($keys as $key) {
            $key = explode(' ', trim($key));
            $keyAry[] = trim($key[0]);
            if (isset($key[1])) {
                $dir = strtolower(trim($key[1]));
                $dirAry[] = $dirMap[$dir] ? $dirMap[$dir] : $def;
            } else {
                $dirAry[] = $def;
            }
        }
        $fnBody = '';
        for ($i = count($keyAry) - 1; $i >= 0; $i--) {
            $k = $keyAry[$i];
            $t = $dirAry[$i];
            $f = -1 * $t;
            $aStr = '$a[\'' . $k . '\']';
            $bStr = '$b[\'' . $k . '\']';

            if (strpos($k, '(') !== false) {
                $aStr = '$a->' . $k;
                $bStr = '$b->' . $k;
            }

            if ($fnBody == '') {
                $fnBody .= "if({$aStr} == {$bStr}) { return 0; }\n";
                $fnBody .= "return ({$aStr} < {$bStr}) ? {$t} : {$f};\n";
            } else {
                $fnBody = "if({$aStr} == {$bStr}) {\n" . $fnBody;
                $fnBody .= "}\n";
                $fnBody .= "return ({$aStr} < {$bStr}) ? {$t} : {$f};\n";
            }
        }

        if ($fnBody) {
            $sortFn = create_function('$a,$b', $fnBody);
394
395
396
397
398

            // TODO: at the moment, $sortFn() triggers some E_NOTICE warnings. We stop these here for a short time.
            $errorSet = error_reporting();
            error_reporting($errorSet & ~E_NOTICE);

399
            usort($ary, $sortFn);
400
401

            error_reporting($errorSet);
Carsten  Rose's avatar
Carsten Rose committed
402
            error_clear_last();
403
        }
404
    }
405

406
407
408
409
410
    /**
     * generateSortArg
     *
     * @return string
     */
411
412
413
414
415
416
417
    private function generateSortArg() {

        $sortArg = "";

        for ($i = 0; $i < $this->levelCount; $i++) {
            $sortArg = $sortArg . $i . " ASC, ";
        }
418

419
        $sortArg = substr($sortArg, 0, strlen($sortArg) - 2);
420

421
        return $sortArg;
422
    }
423

424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
    /**
     * @param $cmd
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     * @throws \Twig\Error\LoaderError
     * @throws \Twig\Error\RuntimeError
     * @throws \Twig\Error\SyntaxError
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function doFunction($cmd) {

        // Explode cmd
        OnString::splitFunctionCmd($cmd, $rcFunctionName, $rcFunctionParam, $rcReturnParam);

        // Save STORE_RECORD
        $storeRecord = $this->store->getStore(STORE_RECORD);

        // Fill STORE_RECORD with parameter
        $tmp = array();
        foreach ($rcFunctionParam as $key) {
            $tmp[$key] = $storeRecord[$key] ?? '';
        }
        $this->store->setStore($tmp, STORE_RECORD, true);

        // Get tt_content record bodytext
454
455
456
        $bodytextArr = $this->db->getBodyText($rcFunctionName);

        $report = new Report(array(), $this->evaluate, $this->phpUnit);
457

458
459
460
        // Fire bodytext. output is purged
        $output = $report->process($bodytextArr[T3DATA_BODYTEXT]);
        unset($report);
461
462
463
464
465
466
467
468
469
470

        // Restore and fill STORE_RECORD
        $tmp = $this->store->getStore(STORE_RECORD);
        foreach ($rcReturnParam as $key) {
            $storeRecord[$key] = $tmp[$key] ?? '';
        }
        $this->store->setStore($storeRecord, STORE_RECORD, true);

    }

471
    /**
472
     * Executes the queries recursive. This Method is called for each sublevel.
473
474
475
476
477
478
     *
     * ROOTLEVEL
     * This method is called once from the main method.
     * For the first call the method executes the rootlevels
     *
     * SUBLEVEL
479
     * For each rootlevel the method calls it self with the level mode 0
480
481
482
     * If the next Level is a Sublevel it will be executed and $this->counter will be added by 1
     * The sublevel calls the method again for a following sublevel
     *
483
484
485
486
     * @param int $cur_level
     * @param array $super_level_array
     * @param int $counter
     * @return string
Marc Egger's avatar
Marc Egger committed
487
488
489
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
490
491
492
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
493
494
495
     * @throws \Twig\Error\LoaderError
     * @throws \Twig\Error\RuntimeError
     * @throws \Twig\Error\SyntaxError
Carsten  Rose's avatar
Carsten Rose committed
496
497
498
     * @throws \Twig_Error_Loader
     * @throws \Twig_Error_Runtime
     * @throws \Twig_Error_Syntax
499
500
     * @throws \UserFormException
     * @throws \UserReportException
501
     */
502
503
    private function triggerReport($cur_level = 1, array $super_level_array = array(), $counter = 0) {
        $keys = array();
504
        $stat = array();
505
506
507
        $content = "";

        // CurrentLevel "10.10.50"
508
        if (isset($this->indexArray[$counter]) && is_array($this->indexArray[$counter])) {
509
            $fullLevel = implode(".", $this->indexArray[$counter]);
510
        } else {
511
            $fullLevel = '';
512
513
        }

514
        // Superlevel "10.10"
515
516
        if (!empty($super_level_array) && is_array($super_level_array)) {
            $full_super_level = implode(".", $super_level_array) . '.';
517
518
519
        } else {
            $full_super_level = '';
        }
520
521
522

        //condition1: indexArray
        //condition2: full_level == Superlevel (but at the length of the Superlevel)
523
524
        while ($counter < count($this->indexArray) && $full_super_level == substr($fullLevel, 0, strlen($full_super_level))) {
            $contentLevel = '';
Nicola Chiapolini's avatar
Nicola Chiapolini committed
525
            $contentSubqueries = '';
526
527
528

            //True: The cur_level is a subquery -> continue
            if ($cur_level != count($this->indexArray[$counter])) {
529
530
                ++$counter;
                if (isset($this->indexArray[$counter]) && is_array($this->indexArray[$counter])) {
531
                    $fullLevel = implode(".", $this->indexArray[$counter]);
532
                } else {
533
                    $fullLevel = '';
534
                }
535
536
537
538
                continue;
            }

            // Set debug, if one is specified else keep the parent one.
539
            $lineDebug = $this->getValueParentDefault(TOKEN_DEBUG, $full_super_level, $fullLevel, $cur_level, 0);
540
541

            // Prepare Error reporting
542
543
            $this->store->setVar(SYSTEM_SQL_RAW, $this->frArray[$fullLevel . "." . TOKEN_SQL], STORE_SYSTEM);
            $this->store->setVar(SYSTEM_REPORT_FULL_LEVEL, $fullLevel, STORE_SYSTEM);
544

545
            // Prepare SQL: replace variables. Actual 'line.total' or 'line.count' will recalculated: don't replace them now!
546
547
            unset($this->variables->resultArray[$fullLevel . ".line."][LINE_TOTAL]);
            unset($this->variables->resultArray[$fullLevel . ".line."][LINE_COUNT]);
548

549
550
551
552
553
            // If defined, fire QFQ function
            if ($this->frArray[$fullLevel . "." . TOKEN_FUNCTION] != '') {
                $this->doFunction($this->frArray[$fullLevel . "." . TOKEN_FUNCTION]);
            }

554
            $sql = $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_SQL]);
555

556
            $this->store->setVar(SYSTEM_SQL_FINAL, $sql, STORE_SYSTEM);
557

558
            // import form files if changed + delete Forms without form file
559
            if (FormAsFile::isFormQuery($sql)) {
560
                FormAsFile::importAllForms($this->db, false, true);
561
562
            }

563
564
565
566
            $stat = array();
            $stat[DB_NUM_ROWS] = 0;
            $result = array();

Nicola Chiapolini's avatar
Nicola Chiapolini committed
567
            //Execute SQL. All errors have been already catched.
568
569
570
            if ($sql != '') {
                $result = $this->db->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
            }
571
572

            // If an array is returned, $sql was a query, otherwise an 'insert', 'update', 'delete', ...
573
            // Query: total number of rows
574
575
            // insert, delete, update: number of affected rows
            $rowTotal = isset($stat[DB_NUM_ROWS]) ? $stat[DB_NUM_ROWS] : $stat[DB_AFFECTED_ROWS];
576

577
578
579
            $this->variables->resultArray[$fullLevel . ".line."][LINE_TOTAL] = $rowTotal;
            $this->variables->resultArray[$fullLevel . ".line."][LINE_COUNT] = is_array($result) ? 1 : 0;
            $this->variables->resultArray[$fullLevel . ".line."][LINE_INSERT_ID] = $stat[DB_INSERT_ID] ?? 0;
580

Nicola Chiapolini's avatar
Nicola Chiapolini committed
581
582
583
584
585

            /////////////////////////////////
            //    Render SHEAD and HEAD    //
            /////////////////////////////////

586
            $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_SHEAD]);
587
            // HEAD: If there is at least one record, do 'head'.
588
            if ($rowTotal > 0) {
589
                $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_HEAD]);
590
            }
591

Nicola Chiapolini's avatar
Nicola Chiapolini committed
592
593
594
595
            ///////////////////////
            //    Render rows    //
            ///////////////////////

596
            if (is_array($result)) {
597
598

                // Prepare row alteration
599
                $arrRbgd = explode("|", $this->frArray[$fullLevel . "." . TOKEN_RBGD], 2);
600
601
602
603
604
                if (count($arrRbgd) < 2) {
                    $arrRbgd[] = '';
                    $arrRbgd[] = '';
                }

Nicola Chiapolini's avatar
Nicola Chiapolini committed
605
                // Prepare skip wrapping of indexed columns
Carsten  Rose's avatar
Carsten Rose committed
606
607
608
609
                $fSkipWrap = array();
                if ('' != ($str = ($this->frArray[$fullLevel . "." . TOKEN_FSKIPWRAP]) ?? '')) {
                    $str = str_replace(' ', '', $str);
                    $fSkipWrap = explode(',', $str);
610
611
612
613
614
                    // Decrement all values to start counting with 0.
                    foreach ($fSkipWrap as $key => $value) {
                        $fSkipWrap[$key] = $value - 1;
                    }

Carsten  Rose's avatar
Carsten Rose committed
615
616
617
                    $fSkipWrap = array_flip($fSkipWrap);
                }

618
619
                $newKeys = $this->splitColumnNames($keys, $fSkipWrap);

620
                //---------------------------------
Carsten  Rose's avatar
Carsten Rose committed
621
                // Process each row of result set
622
                $columnValueSeparator = "";
623
                $rowIndex = 0;
Carsten  Rose's avatar
Carsten Rose committed
624

625
                foreach ($result as $row) {
Nicola Chiapolini's avatar
Nicola Chiapolini committed
626
                    // increment record number counter
627
                    $this->variables->resultArray[$fullLevel . ".line."][LINE_COUNT] = ++$rowIndex;
628
629
630

                    // replace {{<level>.line.count}} and {{<level>.line.total}} in __result__, if the variables specify their own full_level. This can't be replaced before firing the query.
                    for ($ii = 0; $ii < count($row); $ii++) {
631
632
                        $row[$ii] = str_replace("{{" . $fullLevel . ".line.count}}", $rowIndex, $row[$ii]);
                        $row[$ii] = str_replace("{{" . $fullLevel . ".line.total}}", $rowTotal, $row[$ii]);
633
                    }
634

635
                    // SEP set separator (empty on first run)
636
637
                    $contentLevel .= $columnValueSeparator;
                    $columnValueSeparator = $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_RSEP]);
638

Carsten  Rose's avatar
Carsten Rose committed
639
                    // RBEG
640
                    $rbeg = $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_RBEG]);
641

642
                    // RBGD: even/odd rows
643
                    $contentLevel .= str_replace(TOKEN_RBGD, $arrRbgd[$rowIndex % 2], $rbeg);
644

645
646
                    //-----------------------------
                    // COLUMNS: Collect all columns
647
                    $contentLevel .= $this->collectRow($row, $newKeys, $fullLevel, $rowIndex);
648

649
                    // REND
650
                    $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_REND]);
651

652
                    // Trigger subqueries of this level
Nicola Chiapolini's avatar
Nicola Chiapolini committed
653
654
655
                    $contentSubquery = $this->triggerReport($cur_level + 1, $this->indexArray[$counter], $counter + 1);
                    $contentSubqueries .= $contentSubquery;
                    $contentLevel .= $contentSubquery;
656

657
                    // RENR
658
                    $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_RENR]);
659
                }
660
661
            }

Nicola Chiapolini's avatar
Nicola Chiapolini committed
662
663
664
665
666

            /////////////////////////////////////////////////
            //    Render TAIL, ALT_HEAD, ALT_SQL, STAIL    //
            /////////////////////////////////////////////////

667
            if ($rowTotal > 0) {
668
                // tail
669
                $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_TAIL]);
670
            } else {
671
                // althead
672
                $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_ALT_HEAD]);
673
                // altsql
674
                $sql = $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_ALT_SQL]);
675
676
                if (!empty($sql)) {
                    $result = $this->db->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
677

678
679
                    $newKeys = $this->splitColumnNames($keys);

680
681
682
683
684
                    $this->variables->resultArray[$fullLevel . ".line."][LINE_ALT_TOTAL] = $rowTotal;
                    $this->variables->resultArray[$fullLevel . ".line."][LINE_ALT_COUNT] = is_array($result) ? 1 : 0;
                    $this->variables->resultArray[$fullLevel . ".line."][LINE_ALT_INSERT_ID] = $stat[DB_INSERT_ID] ?? 0;

                    if (is_array($result)) {
685
686
                        foreach ($result as $row) {
                            $rowIndex = 0;
687
                            $contentLevel .= $this->collectRow($row, $newKeys, $fullLevel, $rowIndex);
688
                        }
689
690
                    }
                }
691
            }
692

693
694
            $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_STAIL]);

Nicola Chiapolini's avatar
Nicola Chiapolini committed
695
696
697
698
699
700
701
702
703
704
705
706
707
708

            ///////////////////////
            //    render TWIG    //
            ///////////////////////

            $twig_template = $this->frArray[$fullLevel . "." . TOKEN_TWIG];
            if ($twig_template !== '') {
                $contentLevel = $this->renderTwig($twig_template, $result, $keys);
            }

            ////////////////////////////////////////////////////////////
            //    Show / Hide / Save Level Content  (TOKEN_CONTENT)   //
            ////////////////////////////////////////////////////////////

709
710
711
            $token = $this->frArray[$fullLevel . "." . TOKEN_CONTENT];
            switch ($token) {

Nicola Chiapolini's avatar
Nicola Chiapolini committed
712
713
714
715
                case TOKEN_CONTENT_HIDE_LEVEL:
                    $this->variables->resultArray[$fullLevel . ".line."][TOKEN_CONTENT] = $contentLevel;
                    $contentLevel = $contentSubqueries;
                    break;
716
717
718
719
720
721
722
                case TOKEN_CONTENT_HIDE:
                    $this->variables->resultArray[$fullLevel . ".line."][TOKEN_CONTENT] = $contentLevel;
                    $contentLevel = '';
                    break;
                case TOKEN_CONTENT_STORE:
                    $this->variables->resultArray[$fullLevel . ".line."][TOKEN_CONTENT] = $contentLevel;
                    break;
723
                case TOKEN_CONTENT_SHOW:
724
725
726
727
                case '':
                    break;

                default:
Marc Egger's avatar
Marc Egger committed
728
                    throw new \UserReportException ("Unknown token: $token in Line '$fullLevel''", ERROR_UNKNOWN_TOKEN);
729
730
731
732
                    break;
            }

            $content .= $contentLevel;
733

Nicola Chiapolini's avatar
Nicola Chiapolini committed
734
735
736
737
738

            ///////////////////////////////
            //    Switch to next Level   //
            ///////////////////////////////

739
740
            ++$counter;
            if (isset($this->indexArray[$counter]) && is_array($this->indexArray[$counter])) {
741
                $fullLevel = implode(".", $this->indexArray[$counter]);
742
            } else {
743
                $fullLevel = '';
744
            }
745
746
        }
        return $content;
747
    }
748

749
750
    /**
     * Called with an array of column names.
Carsten  Rose's avatar
Carsten Rose committed
751
     * Each column name can be split in multiple string by '|': [s1[|s2[|s3]]]
752
     * Each s1|s2|s3 can be:  {title}, _{special column name}, _hide, _noWrap, _={title},  _+{tag}, _<{tag1}><{tag2}>
753
754
755
756
757
758
759
760
761
     *
     * Return an Array: newKeys[idx][C_FULL|C_TITLE|C_NO_WRAP|C_HIDE]
     *
     * @param array $keys
     * @param array $fSkipWrap
     * @return array
     */
    private function splitColumnNames(array $keys, array $fSkipWrap = array()) {

Carsten  Rose's avatar
Carsten Rose committed
762
        // Split key names in title / specialColumnName / fSkipWrap / hide
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
        $ii = 0;
        $newKeys = array();

        foreach ($keys as $key) {
            $newKeys[$ii][C_FULL] = $key;

            $arr = explode(PARAM_DELIMITER, $key);
            foreach ($arr as $kk) {
                if (($kk[0] ?? '') == TOKEN_COLUMN_CTRL) {
                    switch ($kk) {
                        case TOKEN_COLUMN_CTRL . COLUMN_NO_WRAP:
                            $newKeys[$ii][C_NO_WRAP] = 1;
                            break;
                        case TOKEN_COLUMN_CTRL . COLUMN_HIDE:
                            $newKeys[$ii][C_HIDE] = 1;
                            break;
                        default:
                            $newKeys[$ii][C_SPECIAL] = $kk;
                            break;
                    }
                } else {
                    $newKeys[$ii][C_TITLE] = $kk;
                }
            }

            // Explicit given fSkipWrap
            if (isset($fSkipWrap[$ii])) {
                $newKeys[$ii][C_NO_WRAP] = $fSkipWrap[$ii];
            }

            // Fallback, if no dedicated title is given.
            if (!isset($newKeys[$ii][C_TITLE])) {
                // If no title is given, check if there is a specialColumnName (backward compatibility)
                $newKeys[$ii][C_TITLE] = isset($newKeys[$ii][C_SPECIAL]) ? $newKeys[$ii][C_SPECIAL] : $newKeys[$ii][C_FULL];
            }

            if (($newKeys[$ii][C_TITLE][0] ?? '') == TOKEN_COLUMN_CTRL) {
                $newKeys[$ii][C_TITLE] = substr($newKeys[$ii][C_TITLE], 1);
            }
            $ii++;
        }

        return $newKeys;
    }

Nicola Chiapolini's avatar
Nicola Chiapolini committed
808
809
810
    /**
     * Render given Twig template with content from $result
     *
811
812
813
814
     * @param $twig_template
     * @param $result
     * @param $keys
     * @return string
Nicola Chiapolini's avatar
Nicola Chiapolini committed
815
     * @throws \CodeException
816
817
818
     * @throws \Twig_Error_Loader
     * @throws \Twig_Error_Runtime
     * @throws \Twig_Error_Syntax
Nicola Chiapolini's avatar
Nicola Chiapolini committed
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function renderTwig($twig_template, $result, $keys) {
        if (count(array_unique($keys)) !== count($keys)) {
            throw new \UserReportException("Twig Error: There are multiple columns with the same name in the SQL query.", ERROR_TWIG_COLUMN_NOT_UNIQUE);
        }

        // Turn Result into Associative array
        $resultAssoc = array();
        foreach ($result as $i => $row) {
            foreach ($row as $j => $value) {
                $resultAssoc[$i][$keys[$j]] = $value;
            }
        }

        $tmpl_start = substr($twig_template, 0, 5);

        if ($tmpl_start == "file:") {
838
            $loader = new \Twig\Loader\FilesystemLoader([".", Path::absoluteExt(Path::EXT_TO_TWIG_TEMPLATES)]);
Nicola Chiapolini's avatar
Nicola Chiapolini committed
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
            $twig_template = substr($twig_template, 5);
        } else {
            $loader = new \Twig\Loader\ArrayLoader(array(
                "string_template" => trim($twig_template, '"\''))  # trim is needed for backward compatibility for MNF
            );
            $twig_template = "string_template";
        }

        $twig = new \Twig\Environment($loader, array());

        // Add QFQ Link Filter
        $filter = new \Twig\TwigFilter('qfqlink', function ($string) {
            return $this->link->renderLink($string);
        }, ['is_safe' => ['html']]);
        $twig->addFilter($filter);

Marc Egger's avatar
Marc Egger committed
855
856
857
        // Json decode Filter
        // E.g.: {% set obj = '["this is one", "this is two"]' | json_decode%}
        $filter = new \Twig\TwigFilter('json_decode', function ($string) {
858
            return json_decode($string, true);
Marc Egger's avatar
Marc Egger committed
859
860
861
        }, ['is_safe' => ['html']]);
        $twig->addFilter($filter);

Nicola Chiapolini's avatar
Nicola Chiapolini committed
862
863
864
865
866
867
868
869
870
871
        // render Twig
        $contentTwig = $twig->render($twig_template, array(
            'context' => $resultAssoc,  // backward compatibility for MNF
            'result' => $resultAssoc,
            'store' => array(
                'record' => $this->store->getStore(STORE_RECORD),
                'sip' => $this->store->getStore(STORE_SIP),
                'typo3' => $this->store->getStore(STORE_TYPO3),
                'user' => $this->store->getStore(STORE_USER),
                'system' => $this->store->getStore(STORE_SYSTEM),
Marc Egger's avatar
Marc Egger committed
872
                'var' => $this->store->getStore(STORE_VAR),
Nicola Chiapolini's avatar
Nicola Chiapolini committed
873
874
875
876
877
            )
        ));
        return $contentTwig;
    }

878
879
880
881
882
883
884
    /**
     * Determine value:
     * 1) if one specified in line: take it
     * 2) if one specified in upper level: take it
     * 3) if none above take default
     * Set value on $full_level
     *
885
886
887
888
889
     * @param string $level_key - 'db' or 'debug'
     * @param string $full_super_level - f.e.: 10.10.
     * @param string $full_level - f.e.: 10.10.10.
     * @param string $cur_level - f.e.: 2
     * @param string $default - f.e.: 0
Carsten  Rose's avatar
Carsten Rose committed
890
     *
891
892
893
894
     * @return   string  The calculated value.
     */
    private function getValueParentDefault($level_key, $full_super_level, $full_level, $cur_level, $default) {

895
        if (!empty($this->frArray[$full_level . "." . $level_key])) {
896
897
898
899
900
            $value = $this->frArray[$full_level . "." . $level_key];
        } else {
            if ($cur_level == 1) {
                $value = $default;
            } else {
901
                $value = $this->variables->resultArray[$full_super_level . ".line."][$level_key] ?? '';
902
903
904
905
906
            }
        }
        $this->variables->resultArray[$full_level . ".line."][$level_key] = $value;

        return ($value);
907
    }
908
909
910
911

    /**
     * Steps through 'row' and collects all columns
     *
Carsten  Rose's avatar
Carsten Rose committed
912
913
     * @param array $row Recent row fetch from sql resultset.
     * @param array $keys List of all columnnames
914
915
     * @param string $full_level Recent position to work on.
     * @param string $rowIndex Index of recent row in resultset.
Carsten  Rose's avatar
Carsten Rose committed
916
     *
917
     * @return string               Collected content of all printable columns
Marc Egger's avatar
Marc Egger committed
918
919
920
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
921
922
923
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
924
925
926
927
928
     * @throws \Twig\Error\LoaderError
     * @throws \Twig\Error\RuntimeError
     * @throws \Twig\Error\SyntaxError
     * @throws \UserFormException
     * @throws \UserReportException
929
     */
930
    private function collectRow(array $row, array $keys, $full_level, $rowIndex) {
931
        $content = "";
932
        $assoc = array();
933

934
        $fsep = '';
Carsten  Rose's avatar
Carsten Rose committed
935

936
937
        for ($ii = 0; $ii < count($keys); $ii++) {

938
            // Debugging
939
            $this->store->setVar(SYSTEM_REPORT_COLUMN_INDEX, $ii + 1, STORE_SYSTEM);
940
            $this->store->setVar(SYSTEM_REPORT_COLUMN_NAME, $keys[$ii][C_FULL], STORE_SYSTEM);
941
            $this->store->setVar(SYSTEM_REPORT_COLUMN_VALUE, $row[$ii], STORE_SYSTEM);
942

943
944
            $flagOutput = false;
            $renderedColumn = $this->renderColumn($ii, $keys[$ii], $row[$ii], $full_level, $rowIndex, $flagOutput);
945

946
            $keyAssoc = OnString::stripFirstCharIf(TOKEN_COLUMN_CTRL, $keys[$ii][C_TITLE]);
947
            $keyAssoc = OnString::stripFirstCharIf(COLUMN_STORE_USER, $keyAssoc);
948
949
            if ($keyAssoc != '') {
                $assoc[$keyAssoc] = $row[$ii];
950
                $assoc[REPORT_TOKEN_FINAL_VALUE . $keyAssoc] = $renderedColumn;
951
952
            }

953
            if ($flagOutput) {
954
                //prints
Carsten  Rose's avatar
Carsten Rose committed
955

956
                if (!isset($keys[$ii][C_NO_WRAP])) {
Carsten  Rose's avatar
Carsten Rose committed
957
958
959
960
                    $content .= $this->variables->doVariables($fsep);
                    $content .= $this->variables->doVariables($this->frArray[$full_level . "." . TOKEN_FBEG]);
                }

961
                $content .= $renderedColumn;
Carsten  Rose's avatar
Carsten Rose committed
962

963
                if (!isset($keys[$ii][C_NO_WRAP])) {
Carsten  Rose's avatar
Carsten Rose committed
964
965
966
                    $content .= $this->variables->doVariables($this->frArray[$full_level . "." . TOKEN_FEND]);
                }

967
                $fsep = $this->frArray[$full_level . "." . TOKEN_FSEP];
968
969
            }
        }
Carsten  Rose's avatar
Carsten Rose committed
970

971
        $this->store->appendToStore($assoc, STORE_RECORD);
972

973
        return ($content);
974
    }
975
976
977
978

    /**
     * Renders column depending of column name (if name is a reserved column name)
     *
979
     * @param string $columnIndex
980
     * @param array $columnCtrl
981
982
983
     * @param string $columnValue
     * @param string $full_level
     * @param string $rowIndex
984
     * @param bool $flagOutput
Carsten  Rose's avatar
Carsten Rose committed
985
     *
986
     * @return string rendered column
Marc Egger's avatar
Marc Egger committed
987
988
989
     * @throws \CodeException
     * @throws \DbException
     * @throws \DownloadException
990
991
992
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
993
994
995
996
997
     * @throws \Twig\Error\LoaderError
     * @throws \Twig\Error\RuntimeError
     * @throws \Twig\Error\SyntaxError
     * @throws \UserFormException
     * @throws \UserReportException
998
     */
999
    private function renderColumn($columnIndex, array $columnCtrl, $columnValue, $full_level, $rowIndex, &$flagOutput) {
1000
        $content = "";