Save.php

<?php
/**
 * Created by PhpStorm.
 * User: crose
 * Date: 1/30/16
 * Time: 7:59 PM
 */

namespace IMATHUZH\Qfq\Core;

use IMATHUZH\Qfq\Core\Database\Database;
use IMATHUZH\Qfq\Core\Form\FormAction;
use IMATHUZH\Qfq\Core\Helper\HelperFile;
use IMATHUZH\Qfq\Core\Helper\HelperFormElement;
use IMATHUZH\Qfq\Core\Helper\KeyValueStringParser;
use IMATHUZH\Qfq\Core\Helper\Logger;
use IMATHUZH\Qfq\Core\Helper\OnArray;
use IMATHUZH\Qfq\Core\Helper\Path;
use IMATHUZH\Qfq\Core\Helper\Sanitize;
use IMATHUZH\Qfq\Core\Helper\SqlQuery;
use IMATHUZH\Qfq\Core\Helper\Support;
use IMATHUZH\Qfq\Core\Store\FillStoreForm;
use IMATHUZH\Qfq\Core\Store\Sip;
use IMATHUZH\Qfq\Core\Store\Store;
use ZipArchive;

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

    private $formSpec = array();  // copy of the loaded form
    private $feSpecAction = array(); // copy of all formElement.class='action' of the loaded form
    private $feSpecNative = array(); // copy of all formElement.class='native' of the loaded form
    private $feSpecNativeRaw = array(); // copy of all formElement.class='native' of the loaded form

    /**
     * @var FormAction
     */
    private $formAction = null;

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

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

    private $evaluate = null;

    private $qfqLogFilename = '';

    /**
     * @param array $formSpec
     * @param array $feSpecAction
     * @param array $feSpecNative
     * @param array $feSpecNativeRaw
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function __construct(array $formSpec, array $feSpecAction, array $feSpecNative, array $feSpecNativeRaw) {

        $this->formSpec = $formSpec;
        $this->feSpecAction = $feSpecAction;
        $this->feSpecNative = $feSpecNative;
        $this->feSpecNativeRaw = $feSpecNativeRaw;
        $this->store = Store::getInstance();
        $this->db = new Database($formSpec[F_DB_INDEX]);
        $this->evaluate = new Evaluate($this->store, $this->db);
        $this->formAction = new FormAction($formSpec, $this->db);

        $this->qfqLogFilename = Path::absoluteQfqLogFile();
    }

    /**
     * Starts save process. Returns recordId.
     *
     * @return int
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function process() {
        $rc = 0;

        if ($this->formSpec[F_MULTI_SQL] == '') {
            $recordId = $this->store->getVar(SIP_RECORD_ID, STORE_SIP . STORE_ZERO);
            $rc = $this->elements($recordId);
        } else {
            $rc = $this->saveMultiForm();
        }

        return $rc;
    }

    /**
     * @return int|string
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function saveMultiForm() {

        $parentRecords = $this->evaluate->parse($this->formSpec[F_MULTI_SQL], ROW_REGULAR);

        // No rows: This must be an error, cause MultiForms must have at least one record.
        if (empty($parentRecords)) {
            throw new \UserFormException(
                json_encode([ERROR_MESSAGE_TO_USER => $this->formSpec[F_MULTI_MSG_NO_RECORD],
                    ERROR_MESSAGE_TO_DEVELOPER => 'Query selects no records: ' . $this->formSpec[F_MULTI_SQL]]),
                ERROR_MISSING_EXPECT_RECORDS);
        }

        // Check for 'id' or '_id' as column name
        $idName = isset($parentRecords[0]['_' . F_MULTI_COL_ID]) ? '_' . F_MULTI_COL_ID : F_MULTI_COL_ID;

        // Check that an column 'id' is given
        if (!isset($parentRecords[0][$idName])) {
            throw new \UserFormException(
                json_encode([ERROR_MESSAGE_TO_USER => 'Missing column "_' . F_MULTI_COL_ID . '"', ERROR_MESSAGE_TO_DEVELOPER => $this->formSpec[F_MULTI_SQL]]),
                ERROR_INVALID_OR_MISSING_PARAMETER);
        }

        $fillStoreForm = new FillStoreForm();
        $storeVarBase = $this->store->getStore(STORE_VAR);

        foreach ($parentRecords as $row) {

            // Always start with a clean STORE_VAR
            $this->store->setStore($storeVarBase, STORE_VAR, true);

            $this->store->setStore($row, STORE_PARENT_RECORD, true);
            $this->store->setVar(F_MULTI_COL_ID, $row[$idName], STORE_PARENT_RECORD); // In case '_id' is used, both '_id' and 'id' should be accessible.

            $record = $this->db->sql('SELECT * FROM `' . $this->formSpec[F_TABLE_NAME] . '` WHERE `id`=' . $row[$idName], ROW_EXPECT_1);
            $this->store->setStore($record, STORE_RECORD, true);

            // Fake current recordId
            $this->store->setVar(SIP_RECORD_ID, $row[$idName], STORE_SIP);
            $fillStoreForm->process(FORM_SAVE);

            $rc = $this->elements($row[$idName]);
        }

        return $rc;
    }

    /**
     * Create empty FormElements based on templateGroups, for those who not already exist.
     *
     * @param array $formValues
     *
     * @return array
     * @throws \UserFormException
     */
    private function createEmptyTemplateGroupElements(array $formValues) {

        foreach ($this->feSpecNative as $formElement) {

            switch ($formElement[FE_TYPE]) {
//                case FE_TYPE_EXTRA:
                case FE_TYPE_NOTE:
                case FE_TYPE_SUBRECORD:
                    continue 2;
                default:
                    break;
            }
            $feName = $formElement[FE_NAME];

            // #7705. Skip FE, which are not already expanded. Detect them by '%' (== '%d')
            if (!isset($formValues[$feName]) && false === stripos($feName, '%d') && $this->isMemberOfTemplateGroup($formElement)) {
                $formValues[$feName] = $formElement[FE_VALUE];
            }
        }

        return $formValues;
    }

    /**
     * Check if the current $formElement is member of a templateGroup.
     *
     * @param array $formElement
     * @param int $depth
     * @return bool
     * @throws \UserFormException
     */
    private function isMemberOfTemplateGroup(array $formElement, $depth = 0) {
        $depth++;

        if ($depth > 15) {
            throw new \UserFormException('FormElement nested too much (in each other - endless?): stop recursion', ERROR_FE_NESTED_TOO_MUCH);
        }

        if ($formElement[FE_TYPE] == FE_TYPE_TEMPLATE_GROUP) {
            return true;
        }

        if ($formElement[FE_ID_CONTAINER] == 0) {
            return false;
        }

        // Get the parent element
        $formElementArr = OnArray::filter($this->feSpecNativeRaw, FE_ID, $formElement[FE_ID_CONTAINER]);
        if (isset($formElementArr[0])) {
            return $this->isMemberOfTemplateGroup($formElementArr[0], $depth);
        }

        return false; // This should not be reached,
    }

    /**
     *
     * @param $feName
     *
     * @return bool
     */
    private function isSetEmptyMeansNull($feName) {

        $fe = OnArray::filter($this->feSpecNative, FE_NAME, $feName);

        $flag = isset($fe[0][FE_EMPTY_MEANS_NULL]) && $fe[0][FE_EMPTY_MEANS_NULL] != '0';

        return $flag;
    }

    /**
     * Build an array of all values which should be saved. Values must exist as a 'form value' as well as a regular
     * 'table column'.
     *
     * @param $recordId
     *
     * @return int    record id (in case of insert, it's different from $recordId)
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function elements($recordId) {
        $columnCreated = false;
        $columnModified = false;
        $realColumnFound = false;

        $newValues = array();

        $tableColumns = array_keys($this->store->getStore(STORE_TABLE_COLUMN_TYPES));
        $formValues = $this->store->getStore(STORE_FORM);
        $formValues = $this->createEmptyTemplateGroupElements($formValues);

        // Iterate over all table.columns. Built an assoc array $newValues.
        foreach ($tableColumns as $column) {

            // Never save a predefined 'id': autoincrement values will be given by database..
            if ($column === COLUMN_ID) {
                continue;
            }

            // Skip Upload Elements: those will be processed later.
            if ($this->isColumnUploadField($column)) {
                $realColumnFound = true;
                continue;
            }

            if ($column === COLUMN_CREATED) {
                $columnCreated = true;
            }

            if ($column === COLUMN_MODIFIED) {
                $columnModified = true;
            }

            // Is there a value? Do not forget SIP values. Those do not have necessarily a FormElement.
            if (!isset($formValues[$column])) {
                continue;
            }

            $this->store->setVar(SYSTEM_FORM_ELEMENT, "Column: $column", STORE_SYSTEM);

            // Check if an empty string has to be converted to null.
            if (isset($formValues[$column]) && $formValues[$column] == '' && $this->isSetEmptyMeansNull($column)) {
                $formValues[$column] = null;
            } else {
                Support::setIfNotSet($formValues, $column);
            }
            $newValues[$column] = $formValues[$column];
            $realColumnFound = true;
        }

        // Only save record if real columns exist.
        if ($realColumnFound) {

            if ($columnModified && !isset($newValues[COLUMN_MODIFIED])) {
                $newValues[COLUMN_MODIFIED] = date('YmdHis');
            }

            if ($recordId == 0) {
                if ($columnCreated && !isset($newValues[COLUMN_CREATED])) {
                    $newValues[COLUMN_CREATED] = date('YmdHis');
                }
                $recordId = $this->insertRecord($this->formSpec[F_TABLE_NAME], $newValues);

            } else {
                $this->updateRecord($this->formSpec[F_TABLE_NAME], $newValues, $recordId, $this->formSpec[F_PRIMARY_KEY]);
            }
        }

        // Reload fresh saved record and fill STORE_RECORD with it. Do this before nativeDoSlave().
        $this->store->fillStoreWithRecord($this->formSpec[F_TABLE_NAME], $recordId, $this->db, $this->formSpec[F_PRIMARY_KEY]);

        $this->nativeDoSlave($recordId);

        return $recordId;
    }

    /**
     * Process sqlBefore, sqlInsert|.... for all native FE.
     *
     * @param $recordId
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function nativeDoSlave($recordId) {

        foreach ($this->feSpecNative as $fe) {
            // Preparation for Log, Debug
            $this->store->setVar(SYSTEM_FORM_ELEMENT, Logger::formatFormElementName($fe), STORE_SYSTEM);
            $this->store->setVar(SYSTEM_FORM_ELEMENT_ID, $fe[FE_ID], STORE_SYSTEM);

            $this->formAction->doSqlBeforeSlaveAfter($fe, $recordId, false);
            $this->typeAheadDoTagGlue($fe);
        }
    }

    /**
     * typeAhead: if given, process Tag or Glue.
     *
     * @param array $fe
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function typeAheadDoTagGlue(array $fe) {

        // Update 'glue' records?
        if (($fe[FE_TYPEAHEAD_TAG] ?? '0') == '0' || (!isset($fe[FE_TYPEAHEAD_GLUE_INSERT]) && !isset($fe[FE_TYPEAHEAD_TAG_INSERT]))) {
            return;
        }

        if (empty($fe[FE_TYPEAHEAD_GLUE_INSERT]) || empty($fe[FE_TYPEAHEAD_GLUE_DELETE])) {
            throw new \UserFormException("Missing 'typeAheadGlueInsert' or 'typeAheadGlueDelete'", ERROR_MISSING_REQUIRED_PARAMETER);
        }

        // Extract assigned tags: last
        $tagLast = KeyValueStringParser::parse($this->evaluate->parse($fe[FE_VALUE], ROW_EXPECT_0_1));

        // Extract assigned tags: new
        $tagNew = KeyValueStringParser::parse($this->store->getVar($fe[FE_NAME], STORE_FORM,
            ($fe[FE_CHECK_TYPE] == SANITIZE_ALLOW_AUTO) ? SANITIZE_ALLOW_ALNUMX : $fe[FE_CHECK_TYPE]));

        // Get all tags who are new
        $result = array_diff_assoc($tagNew, $tagLast);

        // Create glue records
        foreach ($result as $id => $value) {

            $this->store->setVar(VAR_TAG_ID, $id, STORE_VAR);
            $this->store->setVar(VAR_TAG_VALUE, $value, STORE_VAR);

            if ($id == 0) {
                if (empty($fe[FE_TYPEAHEAD_TAG_INSERT])) {
                    throw new \UserFormException("Missing 'typeAheadTagInsert'", ERROR_MISSING_REQUIRED_PARAMETER);
                }
                // Create tag
                $id = $this->evaluate->parse($fe[FE_TYPEAHEAD_TAG_INSERT]);
                $this->store->setVar(VAR_TAG_ID, $id, STORE_VAR);
            }

            // Create glue
            $this->evaluate->parse($fe[FE_TYPEAHEAD_GLUE_INSERT]);
        }

        // Get all tags who has been removed
        $result = array_diff_assoc($tagLast, $tagNew);

        // Delete Glue records
        foreach ($result as $id => $value) {
            $this->store->setVar(VAR_TAG_ID, $id, STORE_VAR);
            $this->store->setVar(VAR_TAG_VALUE, $value, STORE_VAR);
            // Delete glue
            $this->evaluate->parse($fe[FE_TYPEAHEAD_GLUE_DELETE]);
        }
    }

    /**
     * Checks if there is a formElement with name '$feName' of type 'upload'
     *
     * @param $feName
     * @return bool
     */
    private function isColumnUploadField($feName) {

        foreach ($this->feSpecNative as $formElement) {
            if ($formElement[FE_NAME] === $feName && $formElement[FE_TYPE] == FE_TYPE_UPLOAD)
                return true;
        }

        return false;
    }

    /**
     * Insert new record in table $this->formSpec['tableName'].
     *
     * @param $tableName
     * @param array $values
     *
     * @return int  last insert id
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public function insertRecord($tableName, array $values) {

        if (count($values) === 0)
            return 0; // nothing to write, last insert id=0

        list($sql, $parameterArray) = SqlQuery::insertRecord($tableName, $values);

        $rc = $this->db->sql($sql, ROW_REGULAR, $parameterArray);

        return $rc;
    }

    /**
     * @param string $tableName
     * @param array $values
     * @param int $recordId
     * @param string $primaryKey
     *
     * @return bool|int     false if $values is empty, else affected rows
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    public function updateRecord($tableName, array $values, $recordId, $primaryKey = F_PRIMARY_KEY_DEFAULT) {

        if (count($values) === 0)
            return 0; // nothing to write, 0 rows affected

        list($sql, $parameterArray) = SqlQuery::updateRecord($tableName, $values, $recordId, $primaryKey);

        $rc = $this->db->sql($sql, ROW_REGULAR, $parameterArray);

        return $rc;
    }

    /**
     * Process all Upload Formelements for the given $recordId. After processing &$formValues will be updated with the
     * final filenames.
     *
     * Constellation: # FILE OLD   FILE NEW     FILESIZE
     *                1 none       none
     *                2 none       new
     *                3 exist      no change
     *                4 delete     none
     *                5 delete     new
     *
     * @param $recordId
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public function processAllUploads($recordId) {

        $sip = new Sip(false);
        $newValues = array();

        $flagDoUnzip = false;

        $formValues = $this->store->getStore(STORE_FORM);
        $primaryRecord = $this->store->getStore(STORE_RECORD); // necessary to check if the current formElement exist as a column of the primary table.

        // Upload - Take care the necessary target directories exist.
        $cwd = getcwd();
        $sitePath = Path::cwdToApp();
        if ($cwd === false || $sitePath === false || !HelperFile::chdir($sitePath)) {
            throw new \UserFormException(
                json_encode([ERROR_MESSAGE_TO_USER => 'getcwd() failed or SITE_PATH undefined or chdir() failed', ERROR_MESSAGE_TO_DEVELOPER => "getcwd() failed or SITE_PATH undefined or chdir('$sitePath') failed."]),
                ERROR_IO_CHDIR);
        }

        foreach ($this->feSpecNative as $formElement) {
            // skip non upload formElements
            if ($formElement[FE_TYPE] != FE_TYPE_UPLOAD) {
                continue;
            }

            // Preparation for Log, Debug
            $this->store->setVar(SYSTEM_FORM_ELEMENT, Logger::formatFormElementName($formElement), STORE_SYSTEM);
            $this->store->setVar(SYSTEM_FORM_ELEMENT_ID, $formElement[FE_ID], STORE_SYSTEM);

            $formElement = HelperFormElement::initUploadFormElement($formElement);
            if (isset($formElement[FE_FILL_STORE_VAR])) {
                $formElement[FE_FILL_STORE_VAR] = $this->evaluate->parse($formElement[FE_FILL_STORE_VAR], ROW_EXPECT_0_1);
                $this->store->appendToStore($formElement[FE_FILL_STORE_VAR], STORE_VAR);
            }

            $column = $formElement[FE_NAME];

            $statusUpload = $this->store->getVar($formValues[$column] ?? '', STORE_EXTRA);
            // Get file stats
            $vars = array();
            $vars[VAR_FILE_SIZE] = $statusUpload[FILES_SIZE] ?? '';
            $vars[VAR_FILE_MIME_TYPE] = $statusUpload[FILES_TYPE] ?? '';

            // Check for 'unzip'.
            if (isset($formElement[FE_FILE_UNZIP])
                && $formElement[FE_FILE_UNZIP] != '0'
                && $vars[VAR_FILE_MIME_TYPE] == 'application/zip') {
                $flagDoUnzip = true;
            }

            // Do upload
            $pathFileName = $this->doUpload($formElement, ($formValues[$column] ?? ''), $sip, $modeUpload);
            if ($flagDoUnzip && $pathFileName != '') {
                if ($formElement[FE_FILE_UNZIP] == '' || $formElement[FE_FILE_UNZIP] == '1') {
                    // Set default dir.
                    $formElement[FE_FILE_UNZIP] = HelperFile::joinPathFilename(dirname($pathFileName), FE_FILE_UNPACK_DIR);
                }

                // Backup STORE_VAR - will be changed in doUnzip()
                $tmpStoreVar = $this->store->getStore(STORE_VAR);
                $this->doUnzip($formElement, $pathFileName);
                // Restore STORE_VAR
                $this->store->setStore($tmpStoreVar, STORE_VAR, true);
            }

            if ($modeUpload == UPLOAD_MODE_DELETEOLD && $pathFileName == '') {
                $pathFileNameTmp = '';  // see '4'
            } else {
                if (empty($pathFileName)) {
                    $pathFileNameTmp = $primaryRecord[$column] ?? ''; // see '3'. Attention: in case of Advanced Upload, $primaryRecord[$column] does not exist.
                } else {
                    $pathFileNameTmp = $pathFileName; // see '1,2,5'
                }
            }

            // Get latest file information
            if ($pathFileNameTmp == '') {
                // No new upload and no existing: take care to remove previous upload file statistics.
                $this->store->unsetVar(VAR_FILE_MIME_TYPE, STORE_VAR);
                $this->store->unsetVar(VAR_FILE_SIZE, STORE_VAR);
            } else {

                $this->store->appendToStore($vars, STORE_VAR);
            }

            // If given: fire a sqlBefore query
            if (!$flagDoUnzip) {
                $this->evaluate->parse($formElement[FE_SQL_BEFORE]);
            }

            // Upload Type: Simple or Advanced
            // If (isset($primaryRecord[$column])) { - see #5048 - isset does not deal correctly with NULL!
            if (array_key_exists($column, $primaryRecord)) {
                // 'Simple Upload': no special action needed, just process the current (maybe modified) value.
                if ($pathFileName !== false) {
                    $newValues[$column] = $pathFileName;

                    if (isset($primaryRecord[COLUMN_FILE_SIZE])) {
                        $newValues[COLUMN_FILE_SIZE] = $vars[VAR_FILE_SIZE];
                    }

                    if (isset($primaryRecord[COLUMN_MIME_TYPE])) {
                        $newValues[COLUMN_MIME_TYPE] = $vars[VAR_FILE_MIME_TYPE];
                    }
                }
            } elseif (isset($formElement[FE_IMPORT_TO_TABLE]) && !isset($formElement[FE_SLAVE_ID])) {
                // Excel import on nonexisting column -> no upload
            } elseif ($flagDoUnzip) {
                // If ZIP and advanced upload: process it not here but via doUnzip.
            } else {
                // 'Advanced Upload'
                $this->doUploadSlave($formElement, $modeUpload);
            }

            // If given: fire a sqlAfter query
            if (!$flagDoUnzip) {
                $this->evaluate->parse($formElement[FE_SQL_AFTER]);
            }
        }

        // Clean up
        HelperFile::chdir($cwd);

        // Only used in 'Simple Upload'
        if (count($newValues) > 0) {
            $this->updateRecord($this->formSpec[F_TABLE_NAME], $newValues, $recordId, $this->formSpec[F_PRIMARY_KEY]);
        }
    }

    /**
     * Unzip $pathFileName to $formElement[FE_FILE_UNZIP]. Before final extract, fire FE_SQL_VALIDATE.
     * For each file in ZIP:
     * - Fill STORE_VAR with VAR_FILENAME, VAR_FILENAME_ONLY, VAR_FILENAME_BASE, VAR_FILENAME_EXT, VAR_FILE_MIME_TYPE, VAR_FILE_SIZE.
     * - Fire $formElement[FE_SQL_VALIDATE]
     * - Fire FE_SLAVE_ID, FE_SQL_BEFORE, FE_SQL_INSERT, FE_SQL_UPDATE, FE_SQL_DELETE, FE_SQL_AFTER
     *
     * @param array $formElement
     * @param string $pathFileName
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function doUnzip(array $formElement, $pathFileName) {

        if (!is_readable($pathFileName)) {
            throw new \UserFormException(json_encode([ERROR_MESSAGE_TO_USER => "Open ZIP file failed",
                ERROR_MESSAGE_TO_DEVELOPER => "File: " . $pathFileName]),
                ERROR_IO_ZIP_OPEN);
        }

        $zip = new ZipArchive();
        $res = $zip->open($pathFileName);
        if ($res !== true) {
            throw new \UserFormException(json_encode([ERROR_MESSAGE_TO_USER => "Open ZIP file failed" . HelperFile::zipFileErrMsg($res),
                ERROR_MESSAGE_TO_DEVELOPER => "File: " . $pathFileName]), ERROR_IO_ZIP_OPEN);
        }

        // Extract
        if (false === $zip->extractTo($formElement[FE_FILE_UNZIP])) {
            throw new \UserFormException("Failed to extract ZIP.", ERROR_IO_ZIP_OPEN);
        }

        // Do sqlValidate() - to get mime type of zipped items, the archive has already been extracted.
        if (!empty($formElement[FE_SQL_VALIDATE])) {
            for ($i = 0; $i < $zip->numFiles; $i++) {
                $stat = $zip->statIndex($i);

                $itemPathFileName = HelperFile::joinPathFilename($formElement[FE_FILE_UNZIP], $stat['name']);
                $this->store->appendToStore(HelperFile::getFileStat($itemPathFileName), STORE_VAR);
                $this->store->appendToStore(HelperFile::pathinfo($itemPathFileName), STORE_VAR);

                HelperFormElement::sqlValidate($this->evaluate, $formElement);
            }
        }

        // Process
        if (!isset($formElement[FE_SLAVE_ID])) {
            $formElement[FE_SLAVE_ID] = '';
        }

        if (!empty($formElement[FE_SLAVE_ID] . $formElement[FE_SQL_BEFORE] . $formElement[FE_SQL_INSERT] .
            $formElement[FE_SQL_UPDATE] . $formElement[FE_SQL_DELETE] . $formElement[FE_SQL_AFTER])) {
            for ($i = 0; $i < $zip->numFiles; $i++) {
                $stat = $zip->statIndex($i);

                $itemPathFileName = HelperFile::joinPathFilename($formElement[FE_FILE_UNZIP], $stat['name']);
                $this->store->appendToStore(HelperFile::getFileStat($itemPathFileName), STORE_VAR);
                $this->store->appendToStore(HelperFile::pathinfo($itemPathFileName), STORE_VAR);

                $this->evaluate->parse($formElement[FE_SQL_BEFORE]);
                $this->doUploadSlave($formElement, UPLOAD_MODE_NEW);
                $this->evaluate->parse($formElement[FE_SQL_AFTER]);
            }
        }

        // Close Zip
        if (false === $zip->close()) {
            throw new \UserFormException("Failed to close ZIP.", ERROR_IO_ZIP_OPEN);
        }
    }

    /**
     * Process all Upload FormElements for the given $recordId.
     * After processing, &$formValues will be updated with the final filename.
     *
     * @throws \CodeException
     * @throws \UserFormException
     */
    public function processAllImageCutFE() {

        foreach ($this->feSpecNative as $formElement) {
            // skip non upload formElements
            if ($formElement[FE_TYPE] != FE_TYPE_IMAGE_CUT) {
                continue;
            }

            $this->extractImageDataReplaceFile($formElement);
        }
    }


    /**
     * Iterates over all FE and checks all 'required' (mode & modeSql) FE.
     * If a required FE is empty, throw an exception.
     * Take care to remove all FE with modeSql='hidden'.
     *
     * Typically, the browser does not allow a submit if a required field is empty.
     *
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    public function checkRequiredHidden() {

        $formModeGlobal = Support::getFormModeGlobal($this->formSpec[F_MODE_GLOBAL] ?? '');
        $reportRequiredFailed = true;

        switch ($formModeGlobal) {
            case F_MODE_REQUIRED_OFF:
            case F_MODE_REQUIRED_OFF_BUT_MARK:
                $reportRequiredFailed = false;
                break;
        }

        $clientValues = $this->store::getStore(STORE_FORM);

        $flagAllRequiredGiven = 1;

        foreach ($this->feSpecNative as $key => $formElement) {

            // Do not check retype slave FE.
            if (isset($formElement[FE_RETYPE_SOURCE_NAME])) {
                continue;
            }

            $this->store->setVar(SYSTEM_FORM_ELEMENT, "Column: " . $formElement[FE_NAME], STORE_SYSTEM);

            // Normalize FE_MODE
            $mode = Support::handleEscapeSpaceComment($formElement[FE_MODE_SQL]);
            $mode = empty($mode) ? $formElement[FE_MODE] : $this->evaluate->parse($mode);
            $this->feSpecNative[$key][FE_MODE] = $formElement[FE_MODE] = $mode;
            $this->feSpecNative[$key][FE_MODE_SQL] = $formElement[FE_MODE_SQL] = '';

            if (isset($formElement[FE_ACCEPT_ZERO_AS_REQUIRED]) && $formElement[FE_ACCEPT_ZERO_AS_REQUIRED] != '0' &&
                isset($clientValues[$formElement[FE_NAME]]) && $clientValues[$formElement[FE_NAME]] == '0') {
                $mode = 'fake'; // The next if() should never be true.
            }

            if ($mode == FE_MODE_REQUIRED && empty($clientValues[$formElement[FE_NAME]])) {
                $flagAllRequiredGiven = 0;
                if ($reportRequiredFailed) {
                    throw new \UserFormException("Missing required value: " . $formElement[FE_LABEL], ERROR_REQUIRED_VALUE_EMPTY);
                }
            }

            if ($mode == FE_MODE_HIDDEN) {
                // Removing the value from the store, forces that the value won't be stored.
                $this->store::unsetVar($formElement[FE_NAME], STORE_FORM);
            }
        }

        // Save 'allRequiredGiven in STORE_VAR
        $this->store::setVar(VAR_ALL_REQUIRED_GIVEN, $flagAllRequiredGiven, STORE_VAR, true);
    }

    /**
     *
     * @param array $formElement
     * @throws \CodeException
     * @throws \UserFormException
     */
    private function extractImageDataReplaceFile(array $formElement) {

        // Take care the necessary target directories exist.
        $cwd = getcwd();
        $sitePath = Path::absoluteApp();
        if ($cwd === false || $sitePath === false || !HelperFile::chdir($sitePath)) {
            throw new \UserFormException(
                json_encode([ERROR_MESSAGE_TO_USER => 'getcwd() failed or SITE_PATH undefined or chdir() failed', ERROR_MESSAGE_TO_DEVELOPER => "getcwd() failed or SITE_PATH undefined or chdir('$sitePath') failed."]),
                ERROR_IO_CHDIR);
        }

        // Get original pathFileName
        $field = HelperFormElement::AppendFormElementNameImageCut($formElement);
        $pathFileName = $this->store->getVar($field, STORE_SIP);
        if ($pathFileName == '' || !file_exists($pathFileName)) {
            throw new \UserFormException(
                json_encode([ERROR_MESSAGE_TO_USER => 'Empty file or file not found', ERROR_MESSAGE_TO_DEVELOPER => 'Empty file or file not found: ' . $pathFileName]),
                ERROR_IO_FILE_NOT_FOUND);
        }

        // 'data:image/png;base64,AAAFBfj42Pj4...';
        $data = $this->store->getVar($formElement[FE_NAME], STORE_FORM, SANITIZE_ALLOW_ALLBUT);
        // Replace data by pathFileName (that is stored in DB).
        $this->store->setVar($formElement[FE_NAME], $pathFileName, STORE_FORM, true);

        if ($data == '') {
            return; // Nothing to do
        }

        // Split base64 encoded image: 'data:image/png;base64,AAAFBfj42Pj4...'
        list($type, $imageData) = explode(';', $data, 2); // $type= 'data:image/png;', $imageData='base64,AAAFBfj42Pj4...'
        list(, $extension) = explode('/', $type); // $type='png'
        list(, $imageData) = explode(',', $imageData); // $imageData='AAAFBfj42Pj4...'

        // If undefined: set default. BTW: Defined and empty means "no original".
        if (!isset($formElement[FE_IMAGE_CUT_KEEP_ORIGINAL])) {
            $formElement[FE_IMAGE_CUT_KEEP_ORIGINAL] = FE_IMAGE_CUT_ORIGINAL_EXTENSION;
        }
        $extSave = $formElement[FE_IMAGE_CUT_KEEP_ORIGINAL];

        $pathParts = pathinfo($pathFileName);
        // Keep the original file?
        if ($extSave != '') {

            // In case the leading '.' is missing.
            if ($extSave[0] != ".") {
                $extSave = '.' . $extSave;
            }

            // Check if there is already an original - don't create an additional one.
            if (!file_exists($pathFileName . $extSave) &&
                !file_exists($pathParts['dirname'] . $pathParts['filename'] . $extSave)
            ) {
                HelperFile::rename($pathFileName, $pathFileName . $extSave);
            }
        }

        if ($extension != $pathParts['extension']) {
            $pathFileName .= "." . $extension;
        }

        if (false === file_put_contents($pathFileName, base64_decode($imageData))) {
            throw new \UserFormException(
                json_encode([ERROR_MESSAGE_TO_USER => 'Write new image failed', ERROR_MESSAGE_TO_DEVELOPER => "Write new image failed: $pathFileName"]),
                ERROR_IO_WRITE);
        }

        $this->store->setVar($formElement[FE_NAME], $pathFileName, STORE_FORM, true);
    }

    /**
     * Process upload for the given Formelement. If necessary, delete a previous uploaded file.
     * Calculate the final path/filename and move the file to the new location.
     *
     * Check also: Documentation-develop/CODING.md
     *
     * @param array $formElement FormElement 'upload'
     * @param string $sipUpload SIP
     * @param Sip $sip
     * @param string $modeUpload UPLOAD_MODE_UNCHANGED | UPLOAD_MODE_NEW | UPLOAD_MODE_DELETEOLD |
     *                            UPLOAD_MODE_DELETEOLD_NEW
     * @return false|string New pathFilename or false on error
     * @throws \CodeException
     * @throws \DbException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \UserFormException
     * @throws \UserReportException
     * @internal param $recordId
     */
    private function doUpload($formElement, $sipUpload, Sip $sip, &$modeUpload) {
        $flagDelete = false;
        $modeUpload = UPLOAD_MODE_UNCHANGED;
        $pathFileName = '';

        // Status information about upload file
        $statusUpload = $this->store->getVar($sipUpload, STORE_EXTRA);
        if ($statusUpload === false) {
            return false;
        }

        if (isset($formElement[FE_IMPORT_TO_TABLE]) && isset($statusUpload[FILES_TMP_NAME])) {
            // Import
            $tmpFile = Support::extendFilename($statusUpload[FILES_TMP_NAME], UPLOAD_CACHED);
            $this->doImport($formElement, $tmpFile);
        }

        // Delete existing old file.
        if (isset($statusUpload[FILES_FLAG_DELETE]) && $statusUpload[FILES_FLAG_DELETE] == '1') {
            $arr = $sip->getVarsFromSip($sipUpload);
            $oldFile = $arr[EXISTING_PATH_FILE_NAME];
            if (file_exists($oldFile)) {
                //TODO: it might be possible to delete a file, which is referenced by another record - a check would be nice.
                HelperFile::unlink($oldFile, $this->qfqLogFilename);
            }
            $flagDelete = ($oldFile != '');
        }

        // Set $modeUpload
        if (isset($statusUpload[FILES_TMP_NAME]) && $statusUpload[FILES_TMP_NAME] != '') {
            $modeUpload = $flagDelete ? UPLOAD_MODE_DELETEOLD_NEW : UPLOAD_MODE_NEW;
        } else {
            $modeUpload = $flagDelete ? UPLOAD_MODE_DELETEOLD : UPLOAD_MODE_UNCHANGED;
        }

        Logger::logMessageWithPrefix(UPLOAD_LOG_PREFIX . ': modeUpload= ' . $modeUpload, $this->qfqLogFilename);

        // skip uploading the file, if this is an import without a specified file destination
        if (!isset($formElement[FE_IMPORT_TO_TABLE]) || isset($formElement[FE_FILE_DESTINATION])) {
            $pathFileName = $this->copyUploadFile($formElement, $statusUpload);

            $msg = UPLOAD_LOG_PREFIX . ': ';
            $msg .= ($pathFileName == '') ? 'Remove old upload / no new upload' : 'File "' . $statusUpload[FILES_TMP_NAME] . '" >> "' . $pathFileName . '"';
            Logger::logMessageWithPrefix($msg, $this->qfqLogFilename);
        }

        // Delete current used uniq SIP
        $this->store->setVar($sipUpload, array(), STORE_EXTRA);

        return $pathFileName;
    }

    /**
     * Excel Import
     *
     * @param $formElement
     * @param $fileName
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    private function doImport($formElement, $fileName) {
        $importNamedSheetsOnly = array();

        Support::setIfNotSet($formElement, FE_IMPORT_TYPE, FE_IMPORT_TYPE_AUTO);

        if (!empty($formElement[FE_IMPORT_NAMED_SHEETS_ONLY])) {
            $importNamedSheetsOnly = explode(',', $formElement[FE_IMPORT_NAMED_SHEETS_ONLY]);
        }

        // Check for keywords which needs an explicit given document type.
        if ($formElement[FE_IMPORT_TYPE] === FE_IMPORT_TYPE_AUTO) {

            $list = [FE_IMPORT_LIST_SHEET_NAMES, FE_IMPORT_READ_DATA_ONLY, FE_IMPORT_LIST_SHEET_NAMES];
            foreach ($list as $token) {
                if (isset($formElement[$token])) {
                    throw new \UserFormException('If ' . $token .
                        ' is given, an explicit document type (like ' . FE_IMPORT_TYPE . '=xlsx) should be set.', ERROR_IMPORT_MISSING_EXPLICIT_TYPE);
                }
            }
        }

        switch ($formElement[FE_IMPORT_TYPE]) {
            case FE_IMPORT_TYPE_AUTO:
                $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($fileName);
                break;

            case FE_IMPORT_TYPE_XLS:
            case FE_IMPORT_TYPE_XLSX:
            case FE_IMPORT_TYPE_CSV:
            case FE_IMPORT_TYPE_ODS:
                $inputFileType = ucfirst($formElement[FE_IMPORT_TYPE]);
                $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);

                // setReadDataOnly
                if (($formElement[FE_IMPORT_READ_DATA_ONLY] ?? '0') != '0') {
                    $reader->setReadDataOnly(true);
                }

                // setLoadSheetsOnly
                if (!empty ($importNamedSheetsOnly)) {
                    $reader->setLoadSheetsOnly($importNamedSheetsOnly);
                }

                if (($formElement[FE_IMPORT_LIST_SHEET_NAMES] ?? '0') != '0') {
                    $sheetNames = $reader->listWorksheetNames($fileName);
                    throw new \UserFormException("Worksheets: " . implode(', ', $sheetNames),
                        ERROR_IMPORT_LIST_SHEET_NAMES);
                }

                $spreadsheet = $reader->load($fileName);
                break;

            default:
                throw new \UserFormException("Unknown Excel import type: '" . $formElement[FE_IMPORT_TYPE] . "'.",
                    ERROR_UNKNOWN_EXCEL_IMPORT_TYPE);
        }

        $tableName = $formElement[FE_IMPORT_TO_TABLE];
        $regions = OnArray::trimArray(explode('|', $formElement[FE_IMPORT_REGION] ?? ''));
        $columnNames = OnArray::trimArray(explode(',', $formElement[FE_IMPORT_TO_COLUMNS] ?? ''));
        $importMode = $formElement[FE_IMPORT_MODE] ?? FE_IMPORT_MODE_APPEND;

        foreach ($regions as $region) {
            // region: tab, startColumn, startRow, endColumn, endRow
            $region = OnArray::trimArray(explode(',', $region));
            $tab = 1;
            if (!empty($region[0])) {
                $tab = $region[0];
            }