Report.php 48.8 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
24
25
26
27
28
29
 ***************************************************************/

namespace qfq;

//use qfq;

require_once(__DIR__ . '/Define.php');
require_once(__DIR__ . '/Variables.php');
30
//require_once(__DIR__ . '/Error.php');
31
require_once(__DIR__ . '/../database/Database.php');
32
require_once(__DIR__ . '/Link.php');
33
require_once(__DIR__ . '/SendMail.php');
34
require_once(__DIR__ . '/../exceptions/UserReportException.php');
35
require_once(__DIR__ . '/../Evaluate.php');
36
require_once(__DIR__ . '/../helper/KeyValueStringParser.php');
37
38
require_once(__DIR__ . '/../helper/Token.php');
require_once(__DIR__ . '/Thumbnail.php');
39
//require_once(__DIR__ . '/Monitor.php');
40

41
42
const DEFAULT_QUESTION = 'question';
const DEFAULT_ICON = 'icon';
43
const DEFAULT_BOOTSTRAP_BUTTON = 'bootstrapButton';
44

45
46
47
48
/**
 * Class Report
 * @package qfq
 */
49
50
class Report {

Carsten  Rose's avatar
Carsten Rose committed
51
52
53
54
55
    /**
     * @var SIP
     */
    private $sip = null;

Carsten  Rose's avatar
Carsten Rose committed
56
57
58
59
60
    /**
     * @var Link
     */
    private $link = null;

61
62
63
64
65
    /**
     * @var Store
     */
    private $store = null;

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

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

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

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

82
83
84
85
    /**
     * @var Variables
     */
    private $variables = null;
86

87
    /**
88
     * @var Database
89
90
     */
    private $db = null;
91

92
    private $dbIndexData = false;
93

94
95
96
97
98
    /**
     * @var Thumbnail
     */
    private $thumbnail = null;

99
100
101
102
103
    /**
     * @var Monitor
     */
    private $monitor = null;

104
105
106
107
    /**
     * @var array
     */
    private $pageDefaults = array();
108

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

115
116
    private $phpUnit = false;

117
    private $showDebugInfoFlag = false;
118

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

        $this->phpUnit = $phpUnit;
133

134
135
        Support::setIfNotSet($t3data, "uid", 0);

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

143
        $this->store = Store::getInstance();
144

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

147
148
        $this->checkUpdateSqlLog();

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

152
153
154
155
156
157
        $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;
158

159
160
161
162
163
164
        $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;

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

171
        $this->db = new Database($this->dbIndexData);
172
        $this->variables = new Variables($eval, $t3data["uid"]);
173

174
175
        $this->link = new Link($this->sip, $this->dbIndexData, $phpUnit);

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

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

    }

187
    /**
Carsten  Rose's avatar
Carsten Rose committed
188
189
     * If a variable 'sqlLog' is given in STORE_TYPO3 (=Bodytext) make them relative to SYSTEM_PATH_EXT and copy it to
     * STORE_SYSTEM
190
191
192
     *
     * @throws CodeException
     * @throws UserFormException
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
     */
    private function checkUpdateSqlLog() {

        $sqlLog = $this->store->getVar(TYPO3_SQL_LOG, STORE_TYPO3);
        if (false !== $sqlLog) {
            if ($sqlLog != '' && $sqlLog[0] !== '/') {
                $sqlLog = $this->store->getVar(SYSTEM_PATH_EXT, STORE_SYSTEM) . '/' . $sqlLog;
            }

            $this->store->setVar(SYSTEM_SQL_LOG, $sqlLog, STORE_SYSTEM);
        }

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

211
212
213
    /**
     * Main function. Parses bodytext and iterates over all queries.
     *
214
     * @param $bodyText
215
     * @return string
216
217
     * @throws CodeException
     * @throws DbException
218
     * @throws DownloadException
219
220
     * @throws UserFormException
     * @throws UserReportException
221
222
223
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
224
     */
225
    public function process($bodyText) {
226

227
228
229
230
231
        //phpUnit Test: clean environment
        $this->frArray = array();
        $this->indexArray = array();
        $this->levelCount = 0;

232
        // Iteration over Bodytext
233
        $ttLineArray = explode("\n", $bodyText);
234

235
236
        foreach ($ttLineArray as $index => $line) {
            // Fill $frArray, $indexArray, $resultArray
237
            $this->parseLine($line);
238
        }
239

240
241
242
243
244
245
        // Sort array
        $this->sortIndexArray($this->indexArray, $this->generateSortArg());

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

246
        return $content;
247
    }
248
249
250
251
252
253

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

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

262
        // no elements or only one: do nothing
263
        if (count($arr) < 2) {
264
            return;
265
        }
266

267
268
269
270
        // 10.50.5.sql
        $key = strtolower(trim($arr[0]));

        // comment ?
271
        if (empty($key) || $key[0] === "#") {
272
            return;
273
        }
274

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

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

        // frCmd = "sql"
        $frCmd = $arrKey[count($arrKey) - 1];

285
        if (strpos('|' . strtolower(TOKEN_VALID_LIST) . '|', '|' . $frCmd . '|') === false) {
286
            throw new UserReportException ("Unknown token: $frCmd in Line '$ttLine''", ERROR_UNKNOWN_TOKEN);
287
288
        }

289
290
291
292
293
294
295
296
297
298
299
        // 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);
        }
300
    }
301

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

        // store complete line reformatted in frArray
        $this->frArray[$level . "." . $frCmd] = $value;

        // per sql command
313
        //pro sql cmd wird der Indexarray abgefüllt. Dieser wird später verwendet um auf den $frArray zuzugreifen
314
        //if(preg_match("/^sql/i", $frCmd) == 1){
315
316
//        if ($frCmd === TOKEN_FORM || $frCmd === TOKEN_SQL) {
        if ($frCmd === TOKEN_SQL) {
317
318
319
320
321
            // Remember max level
            $this->levelCount = max(substr_count($level, '.') + 1, $this->levelCount);
            // $indexArray[10][50][5]
            $this->indexArray[] = explode(".", $level);
        }
322
323
324

        // set defaults
        if ($frCmd === TOKEN_SQL) {
325
326
            $arr = explode('|', TOKEN_VALID_LIST);
            foreach ($arr as $key) {
327
                if (!isset($this->frArray[$level . "." . $key])) {
328
                    $this->frArray[$level . "." . $key] = '';
329
                }
330
331
            }
        }
332
    }
333
334
335
336

    /**
     * Sorts the associative array.
     *
Carsten  Rose's avatar
Carsten Rose committed
337
     * @param array $ary : The unsorted Level Array
338
     * @param string $clause : the sort argument 0 ASC, 1 ASC... according to the number of columns
339
     * @param bool|true $ascending
340
     */
341
    private function sortIndexArray(array &$ary, $clause, $ascending = true) {
342
343
344
345
346
347
348
349
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

        $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);
386
387
388
389
390

            // 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);

391
            usort($ary, $sortFn);
392
393

            error_reporting($errorSet);
394
        }
395
    }
396

397
398
399
400
401
    /**
     * generateSortArg
     *
     * @return string
     */
402
403
404
405
406
407
408
    private function generateSortArg() {

        $sortArg = "";

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

410
        $sortArg = substr($sortArg, 0, strlen($sortArg) - 2);
411

412
        return $sortArg;
413
    }
414
415

    /**
416
     * Executes the queries recursive. This Method is called for each sublevel.
417
418
419
420
421
422
     *
     * ROOTLEVEL
     * This method is called once from the main method.
     * For the first call the method executes the rootlevels
     *
     * SUBLEVEL
423
     * For each rootlevel the method calls it self with the level mode 0
424
425
426
427
     * 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
     *
     * @param int $cur_level Which level it will call [10] = level 1, [10.10] = level 2 ...
428
     * @param array $super_level_array The Value-Array of the indexarray [0=>10, 1=>50]
Carsten  Rose's avatar
Carsten Rose committed
429
     * @param int $counter The outer numeric Arraykey of indexarray
Carsten  Rose's avatar
Carsten Rose committed
430
     *
431
     * @return string                       The content that is displayed on the website
432
433
     * @throws CodeException
     * @throws DbException
434
     * @throws DownloadException
435
     * @throws UserFormException
436
     * @throws UserReportException
437
438
439
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
440
441
     */

442
443
    private function triggerReport($cur_level = 1, array $super_level_array = array(), $counter = 0) {
        $keys = array();
444
        $stat = array();
445
446
447
448
449
450

        $lineDebug = 0;
        $content = "";
        $rowTotal = 0;

        // CurrentLevel "10.10.50"
451
        if (isset($this->indexArray[$counter]) && is_array($this->indexArray[$counter])) {
452
            $fullLevel = implode(".", $this->indexArray[$counter]);
453
        } else {
454
            $fullLevel = '';
455
456
        }

457
        // Superlevel "10.10"
458
459
        if (!empty($super_level_array) && is_array($super_level_array)) {
            $full_super_level = implode(".", $super_level_array) . '.';
460
461
462
        } else {
            $full_super_level = '';
        }
463
464
465

        //condition1: indexArray
        //condition2: full_level == Superlevel (but at the length of the Superlevel)
466
467
        while ($counter < count($this->indexArray) && $full_super_level == substr($fullLevel, 0, strlen($full_super_level))) {
            $contentLevel = '';
468
469
470

            //True: The cur_level is a subquery -> continue
            if ($cur_level != count($this->indexArray[$counter])) {
471
472
                ++$counter;
                if (isset($this->indexArray[$counter]) && is_array($this->indexArray[$counter])) {
473
                    $fullLevel = implode(".", $this->indexArray[$counter]);
474
                } else {
475
                    $fullLevel = '';
476
                }
477
478
479
480
                continue;
            }

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

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

487
            // Prepare SQL: replace variables. Actual 'line.total' or 'line.count' will recalculated: don't replace them now!
488
489
            unset($this->variables->resultArray[$fullLevel . ".line."]["total"]);
            unset($this->variables->resultArray[$fullLevel . ".line."]["count"]);
490

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

493
            $this->store->setVar(SYSTEM_SQL_FINAL, $sql, STORE_SYSTEM);
494

495
            //Execute SQL. All errors have been already catch'd.
496
            unset($result);
497
            $result = $this->db->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
498
499

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

504
505
506
            $this->variables->resultArray[$fullLevel . ".line."]["total"] = $rowTotal;
            $this->variables->resultArray[$fullLevel . ".line."]["count"] = is_array($result) ? 1 : 0;
            $this->variables->resultArray[$fullLevel . ".line."]["insertId"] = isset($stat[DB_INSERT_ID]) ? $stat[DB_INSERT_ID] : 0;
507

508
            $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_SHEAD]);
509
            // HEAD: If there is at least one record, do 'head'.
510
            if ($rowTotal > 0) {
511
                $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_HEAD]);
512
            }
513
514


515
            if (is_array($result)) {
516
517

                // Prepare row alteration
518
                $arrRbgd = explode("|", $this->frArray[$fullLevel . "." . TOKEN_RBGD], 2);
519
520
521
522
523
                if (count($arrRbgd) < 2) {
                    $arrRbgd[] = '';
                    $arrRbgd[] = '';
                }

524
525
                //---------------------------------
                // Process each row of resultset
526
                $columnValueSeparator = "";
527
528
529
                $rowIndex = 0;
                foreach ($result as $row) {
                    // record number counter
530
                    $this->variables->resultArray[$fullLevel . ".line."]["count"] = ++$rowIndex;
531
532
533

                    // 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++) {
534
535
                        $row[$ii] = str_replace("{{" . $fullLevel . ".line.count}}", $rowIndex, $row[$ii]);
                        $row[$ii] = str_replace("{{" . $fullLevel . ".line.total}}", $rowTotal, $row[$ii]);
536
                    }
537

538
                    // SEP set separator (empty on first run)
539
540
                    $contentLevel .= $columnValueSeparator;
                    $columnValueSeparator = $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_RSEP]);
541

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

545
                    // RBGD: even/odd rows
546
                    $contentLevel .= str_replace(TOKEN_RBGD, $arrRbgd[$rowIndex % 2], $rbeg);
547

548
549
                    //-----------------------------
                    // COLUMNS: Collect all columns
550
                    $contentLevel .= $this->collectRow($row, $keys, $fullLevel, $rowIndex);
551

552
                    // REND
553
                    $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_REND]);
554

555
                    // Trigger subqueries of this level
556
                    $contentLevel .= $this->triggerReport($cur_level + 1, $this->indexArray[$counter], $counter + 1);
557

558
                    // RENR
559
                    $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_RENR]);
560
                }
561
562
563
            }

            if ($rowTotal > 0) {
564
                // tail
565
                $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_TAIL]);
566
            } else {
567
                // althead
568
                $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_ALT_HEAD]);
569
                // altsql
570
                $sql = $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_ALT_SQL]);
571
572
573
574
                if (!empty($sql)) {
                    $result = $this->db->sql($sql, ROW_KEYS, array(), '', $keys, $stat);
                    foreach ($result as $row) {
                        $rowIndex = 0;
575
                        $contentLevel .= $this->collectRow($row, $keys, $fullLevel, $rowIndex);
576
577
                    }
                }
578
            }
579
580
581
582
583
584
585
586
587
588
589
590
            $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_STAIL]);

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

                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;
591
                case TOKEN_CONTENT_SHOW:
592
593
594
595
596
597
598
599
600
                case '':
                    break;

                default:
                    throw new UserReportException ("Unknown token: $token in Line '$fullLevel''", ERROR_UNKNOWN_TOKEN);
                    break;
            }

            $content .= $contentLevel;
601

602
603
            ++$counter;
            if (isset($this->indexArray[$counter]) && is_array($this->indexArray[$counter])) {
604
                $fullLevel = implode(".", $this->indexArray[$counter]);
605
            } else {
606
                $fullLevel = '';
607
            }
608
609
610
        }

        return $content;
611
    }
612
613
614
615
616
617
618
619

    /**
     * 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
     *
Carsten  Rose's avatar
Carsten Rose committed
620
     * @param    string $level_key - 'db' or 'debug'
621
622
     * @param    string $full_super_level - f.e.: 10.10.
     * @param    string $full_level - f.e.: 10.10.10.
Carsten  Rose's avatar
Carsten Rose committed
623
624
     * @param    string $cur_level - f.e.: 2
     * @param    string $default - f.e.: 0
Carsten  Rose's avatar
Carsten Rose committed
625
     *
626
627
628
629
     * @return   string  The calculated value.
     */
    private function getValueParentDefault($level_key, $full_super_level, $full_level, $cur_level, $default) {

630
        if (!empty($this->frArray[$full_level . "." . $level_key])) {
631
632
633
634
635
636
637
638
639
640
641
            $value = $this->frArray[$full_level . "." . $level_key];
        } else {
            if ($cur_level == 1) {
                $value = $default;
            } else {
                $value = $this->variables->resultArray[$full_super_level . ".line."][$level_key];
            }
        }
        $this->variables->resultArray[$full_level . ".line."][$level_key] = $value;

        return ($value);
642
    }
643
644
645
646

    /**
     * Steps through 'row' and collects all columns
     *
Carsten  Rose's avatar
Carsten Rose committed
647
648
     * @param array $row Recent row fetch from sql resultset.
     * @param array $keys List of all columnnames
649
650
     * @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
651
     *
652
     * @return string               Collected content of all printable columns
653
654
     * @throws CodeException
     * @throws DbException
655
     * @throws DownloadException
656
657
     * @throws UserFormException
     * @throws UserReportException
658
659
660
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
661
662
663
     */
    private function collectRow(array $row, array $keys, $full_level, $rowIndex) {
        $content = "";
664
        $assoc = array();
665

666
        $fsep = '';
667
668
        for ($ii = 0; $ii < count($keys); $ii++) {

669
            // Debugging
670
671
672
            $this->store->setVar(SYSTEM_REPORT_COLUMN_INDEX, $ii + 1, STORE_SYSTEM);
            $this->store->setVar(SYSTEM_REPORT_COLUMN_NAME, $keys[$ii], STORE_SYSTEM);
            $this->store->setVar(SYSTEM_REPORT_COLUMN_VALUE, $row[$ii], STORE_SYSTEM);
673

674
675
            $flagOutput = false;
            $renderedColumn = $this->renderColumn($ii, $keys[$ii], $row[$ii], $full_level, $rowIndex, $flagOutput);
676

677
            $keyAssoc = OnString::stripFirstCharIf(TOKEN_COLUMN_CTRL, $keys[$ii]);
678
            $keyAssoc = OnString::stripFirstCharIf(COLUMN_STORE_USER, $keyAssoc);
679
680
            if ($keyAssoc != '') {
                $assoc[$keyAssoc] = $row[$ii];
681
                $assoc[REPORT_TOKEN_FINAL_VALUE . $keyAssoc] = $renderedColumn;
682
683
            }

684
            if ($flagOutput) {
685
                //prints
686
                $content .= $this->variables->doVariables($fsep);
687
                $content .= $this->variables->doVariables($this->frArray[$full_level . "." . TOKEN_FBEG]);
688
                $content .= $renderedColumn;
689
690
                $content .= $this->variables->doVariables($this->frArray[$full_level . "." . TOKEN_FEND]);
                $fsep = $this->frArray[$full_level . "." . TOKEN_FSEP];
691
692
            }
        }
Carsten  Rose's avatar
Carsten Rose committed
693

694
        $this->store->appendToStore($assoc, STORE_RECORD);
695

696
        return ($content);
697
    }
698
699
700
701

    /**
     * Renders column depending of column name (if name is a reserved column name)
     *
702
703
704
705
706
     * @param string $columnIndex
     * @param string $columnName
     * @param string $columnValue
     * @param string $full_level
     * @param string $rowIndex
707
     * @param $flagOutput
Carsten  Rose's avatar
Carsten Rose committed
708
     *
709
     * @return string rendered column
710
     * @throws CodeException
Carsten  Rose's avatar
Carsten Rose committed
711
     * @throws DbException
712
     * @throws DownloadException
713
714
     * @throws UserFormException
     * @throws UserReportException
715
716
717
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
718
     */
719
    private function renderColumn($columnIndex, $columnName, $columnValue, $full_level, $rowIndex, &$flagOutput) {
720
        $content = "";
721
722
        $flagControl = false;
        $flagOutput = true;
723

724
        // Special column name:  '_...'? Empty column names are allowed: check with isset
725
        if (isset($columnName[0]) && $columnName[0] === TOKEN_COLUMN_CTRL) {
726
            $flagControl = true;
727
            $columnName = substr($columnName, 1);
728
729
730
731
732
733

            // Special column name and hide output: '__...'? (double '_' at the beginning)
            if (isset($columnName[0]) && $columnName[0] === TOKEN_COLUMN_CTRL) {
                $flagOutput = false;
                $columnName = substr($columnName, 1);
            }
734
        }
735

736
        //TODO: reserved names,not starting with '_' will be still accepted - stop this!
737
        switch ($columnName) {
738
            case COLUMN_LINK:
Carsten  Rose's avatar
Carsten Rose committed
739
                $content .= $this->link->renderLink($columnValue);
740
                break;
741

742
743
            case COLUMN_EXEC:
                $rc = '';
744
                $content .= Support::qfqExec($columnValue, $rc);
745
746
                break;

747
748
749
750
751
752
753
754
755
            // Uppercase 'P'
            case COLUMN_PPAGE:
            case COLUMN_PPAGEC:
            case COLUMN_PPAGED:
            case COLUMN_PPAGEE:
            case COLUMN_PPAGEH:
            case COLUMN_PPAGEI:
            case COLUMN_PPAGEN:
            case COLUMN_PPAGES:
756
                $lowerColumnName = strtolower($columnName);
757
                $tokenizedValue = $this->doFixColPosPage($columnName, $columnValue);
758
759
                $linkValue = $this->doPage($lowerColumnName, $tokenizedValue);
                $content .= $this->link->renderLink($linkValue);
760
761
                break;

762
763
764
765
766
767
768
769
770
            // Lowercase 'P'
            case COLUMN_PAGE:
            case COLUMN_PAGEC:
            case COLUMN_PAGED:
            case COLUMN_PAGEE:
            case COLUMN_PAGEH:
            case COLUMN_PAGEI:
            case COLUMN_PAGEN:
            case COLUMN_PAGES:
771
                $linkValue = $this->doPage($columnName, $columnValue);
772
                $content .= $this->link->renderLink($linkValue);
773
774
                break;

775
776
777
778
779
            case COLUMN_YANK:
                $linkValue = $this->doYank($columnName, $columnValue);
                $content .= $this->link->renderLink($linkValue);
                break;

780
781
782
783
            case COLUMN_PPDF:
            case COLUMN_FFILE:
            case COLUMN_ZZIP:
                $lowerColumnName = strtolower($columnName);
Carsten  Rose's avatar
Carsten Rose committed
784
                $tokenizedValue = $this->doFixColPosDownload($columnValue);
785
                $linkValue = $this->doDownload($lowerColumnName, $tokenizedValue);
Carsten  Rose's avatar
Carsten Rose committed
786
                $content .= $this->link->renderLink($linkValue);
Carsten  Rose's avatar
Carsten Rose committed
787
788
                break;

789
790
791
            case COLUMN_PDF:
            case COLUMN_FILE:
            case COLUMN_ZIP:
792
            case COLUMN_EXCEL:
793
                $linkValue = $this->doDownload($columnName, $columnValue);
Carsten  Rose's avatar
Carsten Rose committed
794
                $content .= $this->link->renderLink($linkValue);
Carsten  Rose's avatar
Carsten Rose committed
795
796
                break;

797
798
            case COLUMN_SAVE_PDF:
                $tokenGiven = [];
799
                $vars = $this->link->fillParameter($columnValue, $tokenGiven);
800
801
802
803
804
805
                $vars[DOWNLOAD_MODE] = DOWNLOAD_MODE_PDF;
                $vars[SIP_DOWNLOAD_PARAMETER] = implode(PARAM_DELIMITER, $vars[NAME_COLLECT_ELEMENTS]);

                // Save file with specified export filename
                $pathFileName = $vars[DOWNLOAD_EXPORT_FILENAME];
                $sanitizedFileName = Sanitize::safeFilename($pathFileName, false, true);
806
                if ($pathFileName == '' ||
807
808
                    substr($pathFileName, 0, strlen("fileadmin/")) !== "fileadmin/" ||
                    substr($pathFileName, -4) !== '.pdf') {
809
                    throw new UserReportException("savePdf filenames need to be in the fileadmin/ directory and end in .pdf for security reasons.", ERROR_INVALID_SAVE_PDF_FILENAME);
810
                } elseif ($pathFileName !== $sanitizedFileName) {
811
812
                    throw new UserReportException("The provided filename '$pathFileName' does not meet sanitize criteria. Use '$sanitizedFileName' instead.", ERROR_INVALID_SAVE_PDF_FILENAME);
                } else {
813
                    $download = new Download();
814
                    $file = $download->process($vars, OUTPUT_MODE_FILE);
815
                    Support::moveFile($file, $pathFileName, true);
816
817
818
                }
                break;

819
820
821
822
            case COLUMN_THUMBNAIL:
                if ($this->thumbnail == null) {
                    $this->thumbnail = new Thumbnail();
                }
823
                $content .= $this->thumbnail->process($columnValue);
824
825
                break;

826
            case COLUMN_MONITOR:
827
                $content .= $this->link->renderLink(TOKEN_MONITOR . '|' . $columnValue);
828
829
                break;

830
            case COLUMN_MIME_TYPE:
831
                $content .= HelperFile::getMimeType($columnValue, true);
832
833
834
835
836
837
                break;

            case COLUMN_FILE_SIZE:
                $content .= filesize($columnValue);
                break;

838
839
840
841
842
843
844
845
846
847
848
849
            case COLUMN_NL2BR:
                $content .= nl2br($columnValue);
                break;

            case COLUMN_HTMLENTITIES:
                $content .= htmlentities($columnValue);
                break;

            case COLUMN_STRIPTAGS:
                $content .= strip_tags($columnValue);
                break;

850
851
852
853
            case COLUMN_EXCEL_PLAIN:
                $content .= $columnValue . PHP_EOL;
                break;
            case COLUMN_EXCEL_STRING:
854
                $content .= EXCEL_STRING . '=' . $columnValue . PHP_EOL;
855
                break;
856
857
858
            case COLUMN_EXCEL_BASE64:
                $content .= EXCEL_BASE64 . '=' . base64_encode($columnValue) . PHP_EOL;
                break;
859
            case COLUMN_EXCEL_NUMERIC:
860
                $content .= EXCEL_NUMERIC . '=' . $columnValue . PHP_EOL;
861
                break;
862

863
            case "bullet":
864
                if ($columnValue === '') {
865
                    break;
866
                }
867

Carsten  Rose's avatar
Carsten Rose committed
868
869
                // r:3|B:
                $linkValue = TOKEN_RENDER . ":3|" . TOKEN_BULLET . ":" . $columnValue;
Carsten  Rose's avatar
Carsten Rose committed
870
                $content .= $this->link->renderLink($linkValue);
871
872
873
                break;

            case "check":
874
                if ($columnValue === '') {
875
                    break;
876
                }
877

Carsten  Rose's avatar
Carsten Rose committed
878
879
                // "r:3|C:
                $linkValue = TOKEN_RENDER . ":3|" . TOKEN_CHECK . ":" . $columnValue;
Carsten  Rose's avatar
Carsten Rose committed
880
                $content .= $this->link->renderLink($linkValue);
881
882
                break;

883
884
            case "img":
                // "<path to image>|[alttext]|[text behind]" renders to: <img src="<path to image>" alt="[alttext]">[text behind]
885
                if (empty($columnValue)) {
Carsten  Rose's avatar
Carsten Rose committed
886
                    break;
887
                }
Carsten  Rose's avatar
Carsten Rose committed
888

889
                $mailConfig = explode("|", $columnValue, 3);
890
891

                // Fake values for tmp[1], tmp[2] to suppress access errors.
892
893
                $mailConfig[] = '';
                $mailConfig[] = '';
894

895
                if (empty($mailConfig[0])) {
896
                    break;
897
                }
898
899
                $attribute = Support::doAttribute('src', $mailConfig[0]);
                $attribute .= Support::doAttribute('alt', $mailConfig[1]);
900

901
                $content .= '<img ' . $attribute . '>' . $mailConfig[2];
902
903
904
905
                break;

            case "mailto":
                // "<email address>|[Real Name]"  renders to (encrypted via JS): <a href="mailto://<email address>"><email address></a> OR <a href="mailto://<email address>">[Real Name]</a>
906
907
                $mailConfig = explode("|", $columnValue, 2);
                if (empty($mailConfig[0])) {
908
                    break;
909
                }
910

911
                $t1 = explode("@", $mailConfig[0], 2);
912
                $content .= "<script language=javascript><!--" . chr(10);
913
914
                if (empty($mailConfig[1])) {
                    $mailConfig[1] = $mailConfig[0