Save.php

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

namespace qfq;

require_once(__DIR__ . '/store/Store.php');
require_once(__DIR__ . '/store/Sip.php');
require_once(__DIR__ . '/Constants.php');
require_once(__DIR__ . '/Evaluate.php');
require_once(__DIR__ . '/helper/HelperFile.php');
require_once(__DIR__ . '/helper/OnArray.php');
//require_once(__DIR__ . '/../qfq/exceptions/UserException.php');
//require_once(__DIR__ . '/../qfq/exceptions/CodeException.php');
//require_once(__DIR__ . '/../qfq/exceptions/DbException.php');
//require_once(__DIR__ . '/../qfq/Evaluate.php');


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

    }

    /**
     * Starts save process. On succcess, returns forwardmode/page.
     *
     * @return int
     * @throws CodeException
     * @throws DbException
     * @throws UserFormException
     */
    public function process() {
        $rc = 0;

        if ($this->formSpec['multiMode'] !== 'none') {

            $parentRecords = $this->db->sql($this->formSpec['multiSql']);
            foreach ($parentRecords as $row) {
                $this->store->setStore($row, STORE_PARENT_RECORD, true);
                $rc = $this->elements($row['_id']);
            }
        } else {
            $recordId = $this->store->getVar(SIP_RECORD_ID, STORE_SIP . STORE_ZERO);
            $rc = $this->elements($recordId);
        }

        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
     */
    public 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 && !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 {
            $this->updateRecord($this->formSpec[F_TABLE_NAME], $newValues, $recordId, $this->formSpec[F_PRIMARY_KEY]);
            $rc = $recordId;
        }

        return $rc;
    }

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

        foreach ($this->feSpecNative AS $formElement) {
            if ($formElement[FE_NAME] === $feName && $formElement[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);
            } 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() {

        $requiredOff = ($this->store->getVar(F_MODE_GLOBAL, STORE_SIP) == F_MODE_REQUIRED_OFF);

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

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

            $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 (!$requiredOff && $mode == FE_MODE_REQUIRED && empty($clientValues[$formElement[FE_NAME]])) {
                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);
            }
        }
    }

    /**
     *
     * @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_SUPPORT => "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_SUPPORT => '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_SUPPORT => "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_SUPPORT => "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) {

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

        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);
                $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_SUPPORT => "[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) {

        if (empty($formElement[FE_FILE_SPLIT]) || $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 = Support::createTempDir();
        $newSrc = $tempDir . DIRECTORY_SEPARATOR . QFQ_TEMP_SOURCE;
        HelperFile::copy($pathFileName, $newSrc);

        HelperFile::chdir($tempDir);

        // Split destination.
        $pathParts = pathinfo($fileDestinationSplit);
        if (empty($pathParts['filename']) || empty($pathParts['basename'])) {
            throw new UserFormException('Missing filename in ' . FE_FILE_DESTINATION_SPLIT, ERROR_MISSING_FILE_NAME);
        }

        // Extract filename from destination directory.
        $fileNameDest = $pathParts['basename'];

        switch ($fileSplitType) {
            case FE_FILE_SPLIT_SVG:
                $cmd = 'pdf2svg "' . $newSrc . '" "' . $fileNameDest . '" all';
                break;
            case FE_FILE_SPLIT_JPEG:
                if ($fileSplitTypeOptions == '') {
                    $fileSplitTypeOptions = FE_FILE_SPLIT_OPTIONS_JPEG;
                }
                $cmd = "convert $fileSplitTypeOptions '$newSrc' '$fileNameDest'";
                break;
            default:
                throw new UserFormException("Unknown 'fileSplit' type: " . $formElement[FE_FILE_SPLIT], ERROR_UNKNOWN_TOKEN);
        }

        // Split PDF
        $output = Support::qfqExec($cmd, $rc);
        if ($rc != 0) {
            throw new UserFormException(
                json_encode([ERROR_MESSAGE_TO_USER => 'pdf2svg failed', ERROR_MESSAGE_SUPPORT => "[$cwd][cmd=$cmd]$output"]),
                ERROR_PDF2SVG);
        }

        // Array of created file names.
        if (false === ($files = scandir('.'))) {
            throw new UserFormException(
                json_encode([ERROR_MESSAGE_TO_USER => 'Splitted files not found', ERROR_MESSAGE_SUPPORT => "[cwd=$cwd] scandir(.)" . HelperFile::errorGetLastAsString()]),
                ERROR_PDF2JPEG);
        }

        $xId = $this->store->getVar(COLUMN_ID, STORE_RECORD);

        // Clean optional existing old split records and files from further uploads.
        $this->db->deleteSplitFileAndRecord($xId, $fileSplitTableName);

        // IM 'convert' will produce files <file>-1.jpg <file>-10.jpg ... - bring them in natural sort order
        natsort($files);

        // Create DB records according to the extracted filenames.
        $tableName = TABLE_NAME_SPLIT;
        $sql = "INSERT INTO $tableName (`tableName`, `xId`, `pathFilename`) VALUES (?,?,?)";

        // 1) Move split files to final location. 2) Created records to reference each split file.
        foreach ($files as $file) {

            if ($file == '.' || $file == '..' || $file == QFQ_TEMP_SOURCE) {
                continue;
            }

            if (!empty($pathParts['dirname'])) {
                $fileDestination = $pathParts['dirname'] . '/' . $file;
            } else {
                $fileDestination = $file;
            }

            Support::moveFile($file, Support::joinPath($cwd, $fileDestination), true);
            HelperFile::chmod($fileDestination, $chmod);

            // Insert records.
            $this->db->sql($sql, ROW_REGULAR, [$fileSplitTableName, $xId, $fileDestination]);
        }

        // Pop directory
        HelperFile::chdir($cwd);

        // Remove duplicated source
        HelperFile::unlink($newSrc);

        // Remove empty directory
        HelperFile::rmdir($tempDir);
    }

    /**
     * Create/update or delete the slave record.
     *
     * @param array $fe
     * @param $modeUpload
     * @return int
     * @throws CodeException
     * @throws DbException
     * @throws UserFormException
     * @throws UserReportException
     */
    private function doUploadSlave(array $fe, $modeUpload) {
        $sql = '';
        $flagUpdateSlaveId = false;
        $flagSlaveDeleted = false;

        if (!isset($fe[FE_SLAVE_ID])) {
            throw new UserFormException("Missing 'slaveId'-definition", ERROR_MISSING_SLAVE_ID_DEFINITION);
        }

        // Get the slaveId
        $slaveId = Support::falseEmptyToZero($this->evaluate->parse($fe[FE_SLAVE_ID]));
        // Store the slaveId: it's used and replaced in the update statement.
        $this->store->setVar(VAR_SLAVE_ID, $slaveId, STORE_VAR, true);

        $mode = ($slaveId == '0') ? 'I' : 'U'; // I=Insert, U=Update
        $mode .= ($modeUpload == UPLOAD_MODE_NEW || $modeUpload == UPLOAD_MODE_DELETEOLD_NEW) ? 'N' : ''; // N=New File, '' if no new file.
        $mode .= ($modeUpload == UPLOAD_MODE_DELETEOLD) ? 'D' : ''; // Delete slave record only if there is no new and not 'unchanged'.
        switch ($mode) {
            case 'IN':
                $sql = $fe[FE_SQL_INSERT];
                $flagUpdateSlaveId = true;
                break;
            case 'UN':
                $sql = $fe[FE_SQL_UPDATE];
                break;
            case 'I':
            case 'U':
                $sql = ''; // no old file and no new file.
                break;
            case 'UD':
                $sql = $fe[FE_SQL_DELETE];
                $flagSlaveDeleted = true;
                break;
            default:
                throw new CodeException('Unknown mode: ' . $mode, ERROR_UNKNOWN_MODE);
        }

        $rc = $this->evaluate->parse($sql);
        // Check if the slave record has been deleted: if yes, set slaveId=0
        if ($flagSlaveDeleted && $rc > 0) {
            $rc = 0;
            $flagUpdateSlaveId = true;
        }

        if ($flagUpdateSlaveId) {
            // Store the slaveId: it's used and replaced in the update statement.
            $this->store->setVar(VAR_SLAVE_ID, $rc, STORE_VAR, true);
            $slaveId = $rc;
        }

        return $slaveId;
    }
}