Report.php

<?php
/***************************************************************
 *  Copyright notice
 *
 *  (c) 2010 Glowbase GmbH
 *
 *  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!
 ***************************************************************/

namespace IMATHUZH\Qfq\Core\Report;

use IMATHUZH\Qfq\Core\Database\Database;
use IMATHUZH\Qfq\Core\Evaluate;
use IMATHUZH\Qfq\Core\Form\FormAsFile;
use IMATHUZH\Qfq\Core\Helper\HelperFile;
use IMATHUZH\Qfq\Core\Helper\KeyValueStringParser;
use IMATHUZH\Qfq\Core\Helper\OnString;
use IMATHUZH\Qfq\Core\Helper\Sanitize;
use IMATHUZH\Qfq\Core\Helper\Support;
use IMATHUZH\Qfq\Core\Store\Sip;
use IMATHUZH\Qfq\Core\Store\Store;

const DEFAULT_QUESTION = 'question';
const DEFAULT_ICON = 'icon';
const DEFAULT_BOOTSTRAP_BUTTON = 'bootstrapButton';

/**
 * Class Report
 * @package qfq
 */
class Report {

    /**
     * @var SIP
     */
    private $sip = null;

    /**
     * @var Link
     */
    private $link = null;

    /**
     * @var Store
     */
    private $store = null;

    /**
     * @var string
     */
    private $dbAlias = '';

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

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

    // TODO to explain
//	private $resultArray = array();
    private $levelCount = 0;
    //private $counter = 0;

    /**
     * @var Variables
     */
    private $variables = null;

    /**
     * @var Database
     */
    private $db = null;

    private $dbIndexData = false;

    /**
     * @var Thumbnail
     */
    private $thumbnail = null;

    /**
     * @var Monitor
     */
    private $monitor = null;

    /**
     * @var array
     */
    private $pageDefaults = array();

    /**
     * @var array - Emulate global variable: will be set much earlier in other functions. Will be shown in error
     *      messages.
     */
    private $fr_error = array('uid' => '', 'pid' => '', 'row' => '', 'debug_level' => '0', 'full_level' => '');

    private $phpUnit = false;

    private $showDebugInfoFlag = false;

    /**
     * Report constructor.
     *
     * @param array $t3data
     * @param Evaluate $eval
     * @param bool $phpUnit
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function __construct(array $t3data, Evaluate $eval, $phpUnit = false) {

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

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

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

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

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

        $this->checkUpdateSqlLog();

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

        $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;

        $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;

        // Default should already set in QuickFormQuery() Constructor
        $this->dbIndexData = $this->store->getVar(TOKEN_DB_INDEX, STORE_TYPO3);
        if ($this->dbIndexData === false) {
            $this->dbIndexData = DB_INDEX_DEFAULT;
        }

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

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

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

        // Sanitize function for POST and GET Parameters.
        // Merged URL-Parameter (key1, id etc...) in resultArray.
        $this->variables->resultArray = array_merge($this->variables->resultArray, array("global." => $this->variables->collectGlobalVariables()));

    }

    /**
     * If a variable 'sqlLog' is given in STORE_TYPO3 (=Bodytext) make them relative to SYSTEM_PATH_EXT and copy it to
     * STORE_SYSTEM
     *
     * @throws \CodeException
     * @throws \UserFormException
     */
    private function checkUpdateSqlLog() {

        $sqlLog = $this->store->getVar(TYPO3_SQL_LOG, STORE_TYPO3);
        if (false !== $sqlLog) {
            $sqlLog = HelperFile::joinPathFilename($this->store->getVar(SYSTEM_EXT_PATH, 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);
        }
    }

    /**
     * Main function. Parses bodytext and iterates over all queries.
     *
     * @param $bodyText
     * @return string
     * @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
     */
    public function process($bodyText) {

        //phpUnit Test: clean environment
        $this->frArray = array();
        $this->indexArray = array();
        $this->levelCount = 0;

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

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

        // Sort array
        $this->sortIndexArray($this->indexArray, $this->generateSortArg());

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

        return $content;
    }

    /**
     * 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
     *
     * @throws \UserReportException
     */
    private function parseLine($ttLine) {

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

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

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

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

        // select ... - if needed, trim surrounding single ticks
        $value = trim($arr[1]);
        $value = OnString::trimQuote($value);

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

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

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

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

    /**
     * @param $level
     * @param $frCmd
     * @param $value
     */
    private function setLine($level, $frCmd, $value) {

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

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

        // set defaults
        if ($frCmd === TOKEN_SQL) {
            $arr = explode('|', TOKEN_VALID_LIST);
            foreach ($arr as $key) {
                if (!isset($this->frArray[$level . "." . $key])) {
                    $this->frArray[$level . "." . $key] = '';
                }
            }
        }
    }

    /**
     * Sorts the associative array.
     *
     * @param array $ary : The unsorted Level Array
     * @param string $clause : the sort argument 0 ASC, 1 ASC... according to the number of columns
     * @param bool|true $ascending
     */
    private function sortIndexArray(array &$ary, $clause, $ascending = true) {

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

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

            usort($ary, $sortFn);

            error_reporting($errorSet);
            error_clear_last();
        }
    }

    /**
     * generateSortArg
     *
     * @return string
     */
    private function generateSortArg() {

        $sortArg = "";

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

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

        return $sortArg;
    }

    /**
     * Executes the queries recursive. This Method is called for each sublevel.
     *
     * ROOTLEVEL
     * This method is called once from the main method.
     * For the first call the method executes the rootlevels
     *
     * SUBLEVEL
     * For each rootlevel the method calls it self with the level mode 0
     * 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
     * @param array $super_level_array
     * @param int $counter
     * @return string
     * @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 \Twig_Error_Loader
     * @throws \Twig_Error_Runtime
     * @throws \Twig_Error_Syntax
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function triggerReport($cur_level = 1, array $super_level_array = array(), $counter = 0) {
        $keys = array();
        $stat = array();
        $content = "";

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

        // Superlevel "10.10"
        if (!empty($super_level_array) && is_array($super_level_array)) {
            $full_super_level = implode(".", $super_level_array) . '.';
        } else {
            $full_super_level = '';
        }

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

            //True: The cur_level is a subquery -> continue
            if ($cur_level != count($this->indexArray[$counter])) {
                ++$counter;
                if (isset($this->indexArray[$counter]) && is_array($this->indexArray[$counter])) {
                    $fullLevel = implode(".", $this->indexArray[$counter]);
                } else {
                    $fullLevel = '';
                }
                continue;
            }

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

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

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

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

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

            // import form files if changed
            if (FormAsFile::isFormQuery($sql)) {
                FormAsFile::updateAllForms();
            }

            //Execute SQL. All errors have been already catched.
            unset($result);
            $result = $this->db->sql($sql, ROW_KEYS, array(), '', $keys, $stat);

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

            $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;


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

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

            ///////////////////////
            //    Render rows    //
            ///////////////////////

            if (is_array($result)) {

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

                // Prepare skip wrapping of indexed columns
                $fSkipWrap = array();
                if ('' != ($str = ($this->frArray[$fullLevel . "." . TOKEN_FSKIPWRAP]) ?? '')) {
                    $str = str_replace(' ', '', $str);
                    $fSkipWrap = explode(',', $str);
                    // Decrement all values to start counting with 0.
                    foreach ($fSkipWrap as $key => $value) {
                        $fSkipWrap[$key] = $value - 1;
                    }

                    $fSkipWrap = array_flip($fSkipWrap);
                }

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

                //---------------------------------
                // Process each row of result set
                $columnValueSeparator = "";
                $rowIndex = 0;

                foreach ($result as $row) {
                    // increment record number counter
                    $this->variables->resultArray[$fullLevel . ".line."][LINE_COUNT] = ++$rowIndex;

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

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

                    // RBEG
                    $rbeg = $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_RBEG]);

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

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

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

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

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


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

            if ($rowTotal > 0) {
                // tail
                $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_TAIL]);
            } else {
                // althead
                $contentLevel .= $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_ALT_HEAD]);
                // altsql
                $sql = $this->variables->doVariables($this->frArray[$fullLevel . "." . TOKEN_ALT_SQL]);
                if (!empty($sql)) {
                    $result = $this->db->sql($sql, ROW_KEYS, array(), '', $keys, $stat);

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

                    $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)) {
                        foreach ($result as $row) {
                            $rowIndex = 0;
                            $contentLevel .= $this->collectRow($row, $newKeys, $fullLevel, $rowIndex);
                        }
                    }
                }
            }

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


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

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

                case TOKEN_CONTENT_HIDE_LEVEL:
                    $this->variables->resultArray[$fullLevel . ".line."][TOKEN_CONTENT] = $contentLevel;
                    $contentLevel = $contentSubqueries;
                    break;
                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;
                case TOKEN_CONTENT_SHOW:
                case '':
                    break;

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

            $content .= $contentLevel;


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

            ++$counter;
            if (isset($this->indexArray[$counter]) && is_array($this->indexArray[$counter])) {
                $fullLevel = implode(".", $this->indexArray[$counter]);
            } else {
                $fullLevel = '';
            }
        }
        return $content;
    }

    /**
     * Called with an array of column names.
     * Each column name can be split in multiple string by '|': [s1[|s2[|s3]]]
     * Each s1|s2|s3 can be:  {title}, _{special colum name}, _hide, _noWrap, _={title},  _+{tag}, _<{tag1}><{tag2}>
     *
     * 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()) {

        // Split key names in title / specialColumnName / fSkipWrap / hide
        $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;
    }

    /**
     * Render given Twig template with content from $result
     *
     * @param $twig_template
     * @param $result
     * @param $keys
     * @return string
     * @throws \CodeException
     * @throws \Twig_Error_Loader
     * @throws \Twig_Error_Runtime
     * @throws \Twig_Error_Syntax
     * @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:") {
            $loader = new \Twig\Loader\FilesystemLoader([".", "typo3conf/ext/qfq/Resources/Public/twig_templates"]);
            $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);

        // Json decode Filter
        // E.g.: {% set obj = '["this is one", "this is two"]' | json_decode%}
        $filter = new \Twig\TwigFilter('json_decode', function ($string) {
            return json_decode($string, true);
        }, ['is_safe' => ['html']]);
        $twig->addFilter($filter);

        // 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),
            )
        ));
        return $contentTwig;
    }

    /**
     * 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
     *
     * @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
     *
     * @return   string  The calculated value.
     */
    private function getValueParentDefault($level_key, $full_super_level, $full_level, $cur_level, $default) {

        if (!empty($this->frArray[$full_level . "." . $level_key])) {
            $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);
    }

    /**
     * Steps through 'row' and collects all columns
     *
     * @param array $row Recent row fetch from sql resultset.
     * @param array $keys List of all columnnames
     * @param string $full_level Recent position to work on.
     * @param string $rowIndex Index of recent row in resultset.
     *
     * @return string               Collected content of all printable columns
     * @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 collectRow(array $row, array $keys, $full_level, $rowIndex) {
        $content = "";
        $assoc = array();

        $fsep = '';

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

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

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

            $keyAssoc = OnString::stripFirstCharIf(TOKEN_COLUMN_CTRL, $keys[$ii][C_TITLE]);
            $keyAssoc = OnString::stripFirstCharIf(COLUMN_STORE_USER, $keyAssoc);
            if ($keyAssoc != '') {
                $assoc[$keyAssoc] = $row[$ii];
                $assoc[REPORT_TOKEN_FINAL_VALUE . $keyAssoc] = $renderedColumn;
            }

            if ($flagOutput) {
                //prints

                if (!isset($keys[$ii][C_NO_WRAP])) {
                    $content .= $this->variables->doVariables($fsep);
                    $content .= $this->variables->doVariables($this->frArray[$full_level . "." . TOKEN_FBEG]);
                }

                $content .= $renderedColumn;

                if (!isset($keys[$ii][C_NO_WRAP])) {
                    $content .= $this->variables->doVariables($this->frArray[$full_level . "." . TOKEN_FEND]);
                }

                $fsep = $this->frArray[$full_level . "." . TOKEN_FSEP];
            }
        }

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

        return ($content);
    }

    /**
     * Renders column depending of column name (if name is a reserved column name)
     *
     * @param string $columnIndex
     * @param array $columnCtrl
     * @param string $columnValue
     * @param string $full_level
     * @param string $rowIndex
     * @param $flagOutput
     *
     * @return string rendered column
     * @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 renderColumn($columnIndex, array $columnCtrl, $columnValue, $full_level, $rowIndex, &$flagOutput) {
        $content = "";
        $flagControl = false;
        $flagOutput = true;

        // Special column name:  '_...'? Empty column names are allowed: check with isset
        if (($columnCtrl[C_SPECIAL][0] ?? '') === TOKEN_COLUMN_CTRL) {
            $flagControl = true;
            $columnName = substr($columnCtrl[C_SPECIAL], 1);

            // Special column name and hide output
            if (isset($columnCtrl[C_HIDE])) {
                $flagOutput = false;
            }

            switch ($columnName) {
                case COLUMN_LINK:
                    $content .= $this->link->renderLink($columnValue);
                    break;

                case COLUMN_EXEC:
                    $rc = '';
                    $content .= Support::qfqExec($columnValue, $rc);
                    break;

                // 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:
                    $lowerColumnName = strtolower($columnName);
                    $tokenizedValue = $this->doFixColPosPage($columnName, $columnValue);
                    $linkValue = $this->doPage($lowerColumnName, $tokenizedValue);
                    $content .= $this->link->renderLink($linkValue);
                    break;

                // 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:
                    $linkValue = $this->doPage($columnName, $columnValue);
                    $content .= $this->link->renderLink($linkValue);
                    break;

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

                case COLUMN_PPDF:
                case COLUMN_FFILE:
                case COLUMN_ZZIP:
                    $lowerColumnName = strtolower($columnName);
                    $tokenizedValue = $this->doFixColPosDownload($columnValue);
                    $linkValue = $this->doDownload($lowerColumnName, $tokenizedValue);
                    $content .= $this->link->renderLink($linkValue);
                    break;

                case COLUMN_PDF:
                case COLUMN_FILE:
                case COLUMN_ZIP:
                case COLUMN_EXCEL: