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\Logger;
use IMATHUZH\Qfq\Core\Helper\OnArray;
use IMATHUZH\Qfq\Core\Helper\Sanitize;
use IMATHUZH\Qfq\Core\Helper\Support;
use IMATHUZH\Qfq\Core\Store\FillStoreForm;
use IMATHUZH\Qfq\Core\Store\Sip;
use IMATHUZH\Qfq\Core\Store\Store;

/**
 * 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 = $this->store->getVar(SYSTEM_QFQ_LOG, STORE_SYSTEM);

    }

    /**
     * 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];
            if (!isset($formValues[$feName]) && $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;

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

        }

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

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

        } else {
            if (!empty($newValues)) {
                $this->updateRecord($this->formSpec[F_TABLE_NAME], $newValues, $recordId, $this->formSpec[F_PRIMARY_KEY]);
            }
            $rc = $recordId;
        }

        $this->nativeDoSlave($rc);

        return $rc;
    }

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

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

        $paramList = str_repeat('?, ', count($values));
        $paramList = substr($paramList, 0, strlen($paramList) - 2);
        $columnList = '`' . implode('`, `', array_keys($values)) . '`';

        $sql = "INSERT INTO $tableName ( " . $columnList . " ) VALUES ( " . $paramList . ' )';

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

        return $rc;
    }

    /**
     * @param string $tableName
     * @param array $values
     * @param int $recordId
     * @param string $primaryKey
     *
     * @return bool|int     false if $values is empty, else affectedrows
     * @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

        if ($recordId === 0) {
            throw new \CodeException('RecordId=0 - this is not possible for update.', ERROR_RECORDID_0_FORBIDDEN);
        }

        $sql = 'UPDATE `' . $tableName . '` SET ';

        foreach ($values as $column => $value) {

            $sql .= '`' . $column . '` = ?, ';
        }

        $sql = substr($sql, 0, strlen($sql) - 2) . " WHERE $primaryKey = ?";
        $values[] = $recordId;

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

        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();
        $vars = array();

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

        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];
            $pathFileName = $this->doUpload($formElement, ($formValues[$column] ?? ''), $sip, $modeUpload);

            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);
                $vars[VAR_FILE_SIZE] = 0;
                $vars[VAR_FILE_MIME_TYPE] = '';
            } else {
                $vars = HelperFile::getFileStat($pathFileNameTmp);
                $this->store->appendToStore($vars, STORE_VAR);
            }

            // If given: fire a sqlBefore query
            $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
            } else {
                // 'Advanced Upload'
                $this->doUploadSlave($formElement, $modeUpload);
            }

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

        }

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

    /**
     * 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 = $this->store->getVar(F_MODE_GLOBAL, STORE_SIP);
        $reportRequiredFailed = ($formModeGlobal != F_MODE_REQUIRED_OFF && $formModeGlobal != F_MODE_REQUIRED_SHOW_ONLY);

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

            if (empty($formElement[FE_MODE_SQL])) {
                $mode = $formElement[FE_MODE];
            } else {
                $mode = $this->evaluate->parse($formElement[FE_MODE_SQL]);
                $this->feSpecNative[$key][FE_MODE_SQL] = $mode;
            }

            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 = $this->store->getVar(SYSTEM_SITE_PATH, STORE_SYSTEM);
        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: doc/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 \UserFormException
     * @throws \UserReportException
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @internal param $recordId
     */
    private function doUpload($formElement, $sipUpload, Sip $sip, &$modeUpload) {
        $flagDelete = false;
        $modeUpload = UPLOAD_MODE_UNCHANGED;

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

        // Upload - Take care the necessary target directories exist.
        $cwd = getcwd();
        $sitePath = $this->store->getVar(SYSTEM_SITE_PATH, STORE_SYSTEM);
        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);
        }

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

        HelperFile::chdir($cwd);

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

        return $pathFileName;
    }

    /**
     * @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];
            }

            try {
                if (is_numeric($tab)) {
                    $worksheet = $spreadsheet->getSheet($tab - 1); // 0-based
                } else {
                    $worksheet = $spreadsheet->getSheetByName($tab);
                    if ($worksheet === null) {
                        throw new \PhpOffice\PhpSpreadsheet\Exception(
                            "No sheet with the name '$tab' could be found."
                        );
                    }
                }
            } catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
                throw new \UserFormException($e->getMessage());
            }

            // Set up requested region
            $columnStart = '1';
            $columnEnd = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($worksheet->getHighestColumn());
            $rowStart = 1;
            $rowEnd = $worksheet->getHighestRow();
            if (!empty($region[1])) { // startColumn
                if (!is_numeric($region[1])) $region[1] = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($region[1]);
                if ($region[1] >= $columnStart && $region[1] <= $columnEnd) {
                    $columnStart = $region[1];
                }
            }
            if (!empty($region[3])) { // endColumn
                if (!is_numeric($region[3])) $region[3] = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($region[3]);
                if ($region[3] >= $columnStart && $region[3] <= $columnEnd) {
                    $columnEnd = $region[3];
                }
            }
            if (!empty($region[2]) && $region[2] >= $rowStart && $region[2] <= $rowEnd) {
                $rowStart = $region[2];
            }
            if (!empty($region[4]) && $region[4] >= $rowStart && $region[4] <= $rowEnd) {
                $rowEnd = $region[4];
            }
            // Read the specified region
            $rangeStr = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnStart) . $rowStart . ':' .
                \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnEnd) . $rowEnd;
            $worksheetData = $worksheet->rangeToArray($rangeStr, '', true, false);

            $columnDefinitionArr = [];
            $columnListArr = [];
            for ($column = $columnStart; $column <= $columnEnd; ++$column) {
                if (!empty($columnNames[$column - $columnStart])) {
                    $columnName = $columnNames[$column - $columnStart];
                } else {
                    $columnName = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($column);
                }
                $columnDefinitionArr[] = "`$columnName`   TEXT       NOT NULL  DEFAULT ''";
                $columnListArr[] = "$columnName";
            }

            // SQL time!
            $createTableSql = "CREATE TABLE IF NOT EXISTS `$tableName` (" .
                "`id`        INT(11)    NOT NULL  AUTO_INCREMENT," .
                implode(', ', $columnDefinitionArr) . ',' .
                "`modified`  TIMESTAMP  NOT NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP," .
                "`created`   DATETIME   NOT NULL  DEFAULT CURRENT_TIMESTAMP," .
                "PRIMARY KEY (`id`) )" .
                "ENGINE = InnoDB  DEFAULT CHARSET = utf8 AUTO_INCREMENT = 0;";
            $this->db->sql($createTableSql);

            if ($importMode === FE_IMPORT_MODE_REPLACE) {
                $this->db->sql("TRUNCATE $tableName");
                $importMode = FE_IMPORT_MODE_APPEND;
            }

            // Import the data
            foreach ($worksheetData AS $rowIndex => $row) {
                $columnList = implode(',', $columnListArr);
                $paramPlaceholders = str_repeat('?,', count($worksheetData[0]) - 1) . '?';
                $insertSql = "INSERT INTO `$tableName` ($columnList) VALUES ($paramPlaceholders)";
                $this->db->sql($insertSql, ROW_REGULAR, $row);
            }
        }
    }

    /**
     * Copy uploaded file from temporary location to final location.
     *
     * Check also: doc/CODING.md
     *
     * @param array $formElement
     * @param array $statusUpload
     * @return array|mixed|null|string
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function copyUploadFile(array $formElement, array $statusUpload) {
        $pathFileName = '';

        if (!isset($statusUpload[FILES_TMP_NAME]) || $statusUpload[FILES_TMP_NAME] === '') {
            // nothing to upload: e.g. user has deleted a previous uploaded file.
            return '';
        }

        $srcFile = Support::extendFilename($statusUpload[FILES_TMP_NAME], UPLOAD_CACHED);

        if (isset($formElement[FE_FILE_DESTINATION])) {

            // Provide variable 'filename'. Might be substituted in $formElement[FE_PATH_FILE_NAME].
            $origFilename = Sanitize::safeFilename($statusUpload[FILES_NAME]);
            $this->store->appendToStore(HelperFile::pathinfo($origFilename), STORE_VAR);

            $pathFileName = $this->evaluate->parse($formElement[FE_FILE_DESTINATION]);
            $pathFileName = Sanitize::safeFilename($pathFileName, false, true); // Dynamically calculated pathFileName might contain invalid characters.

            // Saved in store for later use during 'Advanced Upload'-post processing
            $this->store->setVar(VAR_FILE_DESTINATION, $pathFileName, STORE_VAR);
        }

        if ($pathFileName === '') {
            throw new \UserFormException("Upload failed, no target '" . FE_FILE_DESTINATION . "' specified.", ERROR_NO_TARGET_PATH_FILE_NAME);
        }

        // If given, get chmodDir. Needs to be prefixed with a 0 (=octal) - it should not be quoted! Symbolic mode is not allowed. E.g.: 0660, or 01777
        if (empty($formElement[FE_FILE_CHMOD_DIR])) {
            $chmodDir = false;
        } else {
            $chmodDir = octdec($formElement[FE_FILE_CHMOD_DIR]);
        }

        $overwrite = isset($formElement[FE_FILE_REPLACE_MODE]) && $formElement[FE_FILE_REPLACE_MODE] == FE_FILE_REPLACE_MODE_ALWAYS;
        Support::moveFile($srcFile, $pathFileName, $overwrite, $chmodDir);

        // get chmodFile
        if (empty($formElement[FE_FILE_CHMOD_FILE])) {
            $chmodFile = false;
        } else {
            $chmodFile = octdec($formElement[FE_FILE_CHMOD_FILE]);
        }

        $this->autoOrient($formElement, $pathFileName);
        HelperFile::chmod($pathFileName, $chmodFile);

        $this->splitUpload($formElement, $pathFileName, $chmodFile, $statusUpload);

        return $pathFileName;
    }

    /**
     * If fe['autoOrient'] is given and the MimeType corresponds to fe['autoOrientMimeType']: the given {{pathFileName:V}} will be converted.
     * ImageMagick 'convert' seems to do a better job than GraficsMagick (Orientation is stable even if multiple times applied).
     *
     * @param array $formElement
     * @param $pathFileName
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function autoOrient(array $formElement, $pathFileName) {

        // 'autoOrient' wished?
        if (!isset($formElement[FE_FILE_AUTO_ORIENT]) || $formElement[FE_FILE_AUTO_ORIENT] == '0') {
            return; // No
        }

        // Upload has matching MimeType?
        $mimeTypeList = empty($formElement[FE_FILE_AUTO_ORIENT_MIME_TYPE]) ? 'image/jpeg,image/png,image/tiff' : $formElement[FE_FILE_AUTO_ORIENT_MIME_TYPE];
        if (!HelperFile::checkFileType($pathFileName, $pathFileName, $mimeTypeList)) {
            return;
        }

        // Get 'autoOrient' command
        $cmd = empty($formElement[FE_FILE_AUTO_ORIENT_CMD]) ? FE_FILE_AUTO_ORIENT_CMD_DEFAULT : $formElement[FE_FILE_AUTO_ORIENT_CMD];
        $cmd = $this->evaluate->parse($cmd);

        // Do 'autoOrient' command
        $output = Support::qfqExec($cmd, $rc);
        if ($rc != 0) {
            throw new \UserFormException(json_encode([ERROR_MESSAGE_TO_USER => 'copy failed', ERROR_MESSAGE_TO_DEVELOPER => "[cmd=$cmd]$output"]), ERROR_IO_COPY);
        }
    }

    /**
     * Check's if the file $pathFileName should be splitted in one file per page. If no: do nothing and return.
     * The only possible split target file format is 'svg': fileSplit=svg.
     * The splitted files will be saved under fileDestinationSplit=some/path/to/file.%02d.svg. A printf style token,
     * like '%02d', is needed to create distinguished filename's. See 'man pdf2svg' for further details.
     * For every created file, a record in table 'Split' is created (see splitSvg() ), storing the pathFileName of the
     * current page/file.
     *
     * @param array $formElement
     * @param string $pathFileName
     * @param int $chmod
     * @param array $statusUpload
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
     */
    private function splitUpload(array $formElement, $pathFileName, $chmod, array $statusUpload) {


        // Should the file be split?
        if (empty($formElement[FE_FILE_SPLIT])) {
            return;
        }

        // Is it technically possible to split the file?
        // BTW: the $statusUpload['type'] delivers 'octetstream' for PDF files who do not have the extension '.pdf' - therefore get mimetype again.
        $mime = HelperFile::getMimeType($pathFileName);
        if (false === strpos($mime, MIME_TYPE_SPLIT_CAPABLE) && $statusUpload[FILES_TYPE] != MIME_TYPE_SPLIT_CAPABLE) {
            return;
        }

        $fileDestinationSplit = $this->evaluate->parse($formElement[FE_FILE_DESTINATION_SPLIT] ?? '');
        $fileSplitType = $this->evaluate->parse($formElement[FE_FILE_SPLIT] ?? '');
        $fileSplitTypeOptions = $this->evaluate->parse($formElement[FE_FILE_SPLIT_OPTIONS] ?? '');
        $fileSplitTableName = $this->evaluate->parse($formElement[FE_FILE_SPLIT_TABLE_NAME] ?? '');

        if (empty($fileSplitTableName)) {
            $fileSplitTableName = $this->formSpec[F_TABLE_NAME];
        }

        if ($fileDestinationSplit == '') {
            $ext = ($fileSplitType == FE_FILE_SPLIT_SVG) ? '.%02d.svg' : '.jpg';
            $fileDestinationSplit = $pathFileName . '.split/split' . $ext;
        }

        HelperFile::mkDirParent($fileDestinationSplit);

        // Save CWD
        $cwd = getcwd();

        // Create temporary directory
        $tempDir = HelperFile::mktempdir();
        $newSrc = $tempDir . DIRECTORY_SEPARATOR . QFQ_TEMP_SOURCE;
        HelperFile::copy($pathFileName, $newSrc);