Save.php 46.1 KB
Newer Older
1 2 3 4 5 6 7 8
<?php
/**
 * Created by PhpStorm.
 * User: crose
 * Date: 1/30/16
 * Time: 7:59 PM
 */

Marc Egger's avatar
Marc Egger committed
9 10 11
namespace IMATHUZH\Qfq\Core;

use IMATHUZH\Qfq\Core\Database\Database;
12
use IMATHUZH\Qfq\Core\Form\FormAction;
13
use IMATHUZH\Qfq\Core\Helper\HelperFile;
Marc Egger's avatar
Marc Egger committed
14 15
use IMATHUZH\Qfq\Core\Helper\HelperFormElement;
use IMATHUZH\Qfq\Core\Helper\Logger;
16
use IMATHUZH\Qfq\Core\Helper\OnArray;
Marc Egger's avatar
Marc Egger committed
17 18
use IMATHUZH\Qfq\Core\Helper\Sanitize;
use IMATHUZH\Qfq\Core\Helper\Support;
19
use IMATHUZH\Qfq\Core\Store\FillStoreForm;
Marc Egger's avatar
Marc Egger committed
20
use IMATHUZH\Qfq\Core\Store\Sip;
21
use IMATHUZH\Qfq\Core\Store\Store;
22

23 24 25 26
/**
 * Class Save
 * @package qfq
 */
27 28 29 30 31
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
32
    private $feSpecNativeRaw = array(); // copy of all formElement.class='native' of the loaded form
33 34 35 36 37 38

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

39 40 41 42
    /**
     * @var null|Store
     */
    private $store = null;
Carsten  Rose's avatar
Carsten Rose committed
43 44 45 46

    /**
     * @var Database
     */
47 48 49 50
    private $db = null;

    private $evaluate = null;

51 52
    private $qfqLogFilename = '';

53 54 55 56
    /**
     * @param array $formSpec
     * @param array $feSpecAction
     * @param array $feSpecNative
57
     * @param array $feSpecNativeRaw
Marc Egger's avatar
Marc Egger committed
58 59 60 61
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
62
     */
63
    public function __construct(array $formSpec, array $feSpecAction, array $feSpecNative, array $feSpecNativeRaw) {
64

65 66 67
        $this->formSpec = $formSpec;
        $this->feSpecAction = $feSpecAction;
        $this->feSpecNative = $feSpecNative;
68
        $this->feSpecNativeRaw = $feSpecNativeRaw;
69
        $this->store = Store::getInstance();
70
        $this->db = new Database($formSpec[F_DB_INDEX]);
71
        $this->evaluate = new Evaluate($this->store, $this->db);
72
        $this->formAction = new FormAction($formSpec, $this->db);
73

Marc Egger's avatar
Marc Egger committed
74
        $this->qfqLogFilename = $this->store->getVar(SYSTEM_SITE_PATH, STORE_SYSTEM) . '/' . $this->store->getVar(SYSTEM_QFQ_LOG, STORE_SYSTEM);
75

76 77 78
    }

    /**
Carsten  Rose's avatar
Carsten Rose committed
79
     * Starts save process. Returns recordId.
80
     *
81
     * @return int
Marc Egger's avatar
Marc Egger committed
82 83 84
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
85
     * @throws \UserReportException
86 87
     */
    public function process() {
88
        $rc = 0;
89

90
        if ($this->formSpec[F_MULTI_SQL] == '') {
91 92
            $recordId = $this->store->getVar(SIP_RECORD_ID, STORE_SIP . STORE_ZERO);
            $rc = $this->elements($recordId);
93 94
        } else {
            $rc = $this->saveMultiForm();
95
        }
96 97

        return $rc;
98 99
    }

100 101 102 103 104 105 106 107 108 109 110
    /**
     * @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);

111
        // No rows: This must be an error, cause MultiForms must have at least one record.
112
        if (empty($parentRecords)) {
113 114 115 116
            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);
117 118 119 120 121 122 123 124 125 126 127 128 129
        }

        // 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();
130
        $storeVarBase = $this->store->getStore(STORE_VAR);
131 132

        foreach ($parentRecords as $row) {
133 134 135 136

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

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

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

143 144 145 146 147 148 149 150 151 152
            // Fake current recordId
            $this->store->setVar(SIP_RECORD_ID, $row[$idName], STORE_SIP);
            $fillStoreForm->process(FORM_SAVE);

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

        return $rc;
    }

153
    /**
Carsten  Rose's avatar
Carsten Rose committed
154 155
     * Create empty FormElements based on templateGroups, for those who not already exist.
     *
156
     * @param array $formValues
Carsten  Rose's avatar
Carsten Rose committed
157
     *
158
     * @return array
Marc Egger's avatar
Marc Egger committed
159
     * @throws \UserFormException
160 161 162 163 164
     */
    private function createEmptyTemplateGroupElements(array $formValues) {

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

165 166 167 168 169 170 171 172
            switch ($formElement[FE_TYPE]) {
//                case FE_TYPE_EXTRA:
                case FE_TYPE_NOTE:
                case FE_TYPE_SUBRECORD:
                    continue 2;
                default:
                    break;
            }
173
            $feName = $formElement[FE_NAME];
174 175 176

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

181 182 183
        return $formValues;
    }

184
    /**
185 186 187 188 189
     * Check if the current $formElement is member of a templateGroup.
     *
     * @param array $formElement
     * @param int $depth
     * @return bool
Marc Egger's avatar
Marc Egger committed
190
     * @throws \UserFormException
191 192 193 194 195
     */
    private function isMemberOfTemplateGroup(array $formElement, $depth = 0) {
        $depth++;

        if ($depth > 15) {
Marc Egger's avatar
Marc Egger committed
196
            throw new \UserFormException('FormElement nested too much (in each other - endless?): stop recursion', ERROR_FE_NESTED_TOO_MUCH);
197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217
        }

        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,
    }

    /**
     *
218
     * @param $feName
Carsten  Rose's avatar
Carsten Rose committed
219
     *
220 221 222 223 224 225 226 227 228 229 230
     * @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;
    }

231
    /**
Carsten  Rose's avatar
Carsten Rose committed
232 233
     * Build an array of all values which should be saved. Values must exist as a 'form value' as well as a regular
     * 'table column'.
Carsten  Rose's avatar
Carsten Rose committed
234
     *
235
     * @param $recordId
Carsten  Rose's avatar
Carsten Rose committed
236
     *
237
     * @return int   record id (in case of insert, it's different from $recordId)
Marc Egger's avatar
Marc Egger committed
238 239
     * @throws \CodeException
     * @throws \DbException
240 241
     * @throws \UserFormException
     * @throws \UserReportException
242
     */
243
    private function elements($recordId) {
244 245
        $columnCreated = false;
        $columnModified = false;
Carsten  Rose's avatar
Carsten Rose committed
246

247 248 249
        $newValues = array();

        $tableColumns = array_keys($this->store->getStore(STORE_TABLE_COLUMN_TYPES));
250
        $formValues = $this->store->getStore(STORE_FORM);
251
        $formValues = $this->createEmptyTemplateGroupElements($formValues);
252 253 254

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

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

Carsten  Rose's avatar
Upload:  
Carsten Rose committed
261 262 263 264 265
            // Skip Upload Elements: those will be processed later.
            if ($this->isColumnUploadField($column)) {
                continue;
            }

266 267 268 269 270 271 272 273
            if ($column === COLUMN_CREATED) {
                $columnCreated = true;
            }

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

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

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

281 282 283 284 285 286
            // 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);
            }
287
            $newValues[$column] = $formValues[$column];
288

289 290
        }

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

295
        if ($recordId == 0) {
296 297 298
            if ($columnCreated && !isset($newValues[COLUMN_CREATED])) {
                $newValues[COLUMN_CREATED] = date('YmdHis');
            }
299
            $rc = $this->insertRecord($this->formSpec[F_TABLE_NAME], $newValues);
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
300

301
        } else {
302 303 304
            if (!empty($newValues)) {
                $this->updateRecord($this->formSpec[F_TABLE_NAME], $newValues, $recordId, $this->formSpec[F_PRIMARY_KEY]);
            }
305 306 307
            $rc = $recordId;
        }

308 309
        $this->nativeDoSlave($rc);

310
        return $rc;
311 312
    }

313 314
    /**
     * @param $recordId
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
315
     *
316 317 318 319
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
320
     */
321 322 323 324 325 326 327 328 329 330 331
    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);
        }
    }

332
    /**
333 334
     * Checks if there is a formElement with name '$feName' of type 'upload'
     *
335 336 337
     * @param $feName
     * @return bool
     */
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
338 339 340
    private function isColumnUploadField($feName) {

        foreach ($this->feSpecNative AS $formElement) {
341
            if ($formElement[FE_NAME] === $feName && $formElement[FE_TYPE] == FE_TYPE_UPLOAD)
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
342 343
                return true;
        }
Carsten  Rose's avatar
Carsten Rose committed
344

Carsten  Rose's avatar
Upload:  
Carsten Rose committed
345 346 347 348 349 350
        return false;
    }

    /**
     * Insert new record in table $this->formSpec['tableName'].
     *
351
     * @param $tableName
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
352
     * @param array $values
Carsten  Rose's avatar
Carsten Rose committed
353
     *
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
354
     * @return int  last insert id
Marc Egger's avatar
Marc Egger committed
355 356 357
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
358 359 360 361 362 363 364 365 366 367
     */
    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)) . '`';

368
        $sql = "INSERT INTO $tableName ( " . $columnList . " ) VALUES ( " . $paramList . ' )';
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
369 370 371 372 373 374 375 376

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

        return $rc;
    }

    /**
     * @param string $tableName
Carsten  Rose's avatar
Carsten Rose committed
377 378
     * @param array $values
     * @param int $recordId
379
     * @param string $primaryKey
Carsten  Rose's avatar
Carsten Rose committed
380
     *
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
381
     * @return bool|int     false if $values is empty, else affectedrows
Marc Egger's avatar
Marc Egger committed
382 383 384
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
385
     */
386
    public function updateRecord($tableName, array $values, $recordId, $primaryKey = F_PRIMARY_KEY_DEFAULT) {
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
387 388 389 390

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

391
        if ($recordId === 0) {
Marc Egger's avatar
Marc Egger committed
392
            throw new \CodeException('RecordId=0 - this is not possible for update.', ERROR_RECORDID_0_FORBIDDEN);
393
        }
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
394 395 396 397 398 399 400 401

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

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

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

402
        $sql = substr($sql, 0, strlen($sql) - 2) . " WHERE $primaryKey = ?";
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
403 404 405 406 407 408 409
        $values[] = $recordId;

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

        return $rc;
    }

410
    /**
Carsten  Rose's avatar
Carsten Rose committed
411 412
     * Process all Upload Formelements for the given $recordId. After processing &$formValues will be updated with the
     * final filenames.
413 414 415 416 417 418 419 420
     *
     * Constellation: # FILE OLD   FILE NEW     FILESIZE
     *                1 none       none
     *                2 none       new
     *                3 exist      no change
     *                4 delete     none
     *                5 delete     new
     *
421
     * @param $recordId
Marc Egger's avatar
Marc Egger committed
422 423 424 425
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
426 427
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
428
     */
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
429 430 431 432
    public function processAllUploads($recordId) {

        $sip = new Sip(false);
        $newValues = array();
433
        $vars = array();
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
434 435

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

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

444 445 446 447
            // 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);

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

454
            $column = $formElement[FE_NAME];
455
            $pathFileName = $this->doUpload($formElement, ($formValues[$column] ?? ''), $sip, $modeUpload);
456

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

467
            // Get latest file information
468
            if ($pathFileNameTmp == '') {
469 470 471
                // 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);
472 473
                $vars[VAR_FILE_SIZE] = 0;
                $vars[VAR_FILE_MIME_TYPE] = '';
474 475 476 477 478
            } else {
                $vars = HelperFile::getFileStat($pathFileNameTmp);
                $this->store->appendToStore($vars, STORE_VAR);
            }

479 480 481
            // If given: fire a sqlBefore query
            $this->evaluate->parse($formElement[FE_SQL_BEFORE]);

482
            // Upload Type: Simple or Advanced
483 484 485
            // 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.
486 487
                if ($pathFileName !== false) {
                    $newValues[$column] = $pathFileName;
488 489 490 491 492 493 494 495

                    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];
                    }
496
                }
497 498
            } elseif (isset($formElement[FE_IMPORT_TO_TABLE]) && !isset($formElement[FE_SLAVE_ID])) {
                // Excel import on nonexisting column -> no upload
499 500
            } else {
                // 'Advanced Upload'
501
                $this->doUploadSlave($formElement, $modeUpload);
502
            }
503 504 505 506

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

507
        }
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
508

509
        // Only used in 'Simple Upload'
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
510
        if (count($newValues) > 0) {
511
            $this->updateRecord($this->formSpec[F_TABLE_NAME], $newValues, $recordId, $this->formSpec[F_PRIMARY_KEY]);
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
512
        }
513 514
    }

515
    /**
516 517
     * Process all Upload FormElements for the given $recordId.
     * After processing, &$formValues will be updated with the final filename.
518
     *
Marc Egger's avatar
Marc Egger committed
519 520
     * @throws \CodeException
     * @throws \UserFormException
521 522 523 524 525 526 527 528 529 530 531 532 533
     */
    public function processAllImageCutFE() {

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

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

534 535 536 537 538 539 540 541

    /**
     * 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.
     *
Marc Egger's avatar
Marc Egger committed
542 543 544 545
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
546 547 548
     */
    public function checkRequiredHidden() {

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

552 553 554 555 556 557
        switch ($formModeGlobal) {
            case F_MODE_REQUIRED_OFF:
            case F_MODE_REQUIRED_OFF_BUT_MARK:
                $reportRequiredFailed = false;
                break;
        }
558 559 560

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

561 562
        $flagAllRequiredGiven = 1;

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

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

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

572 573 574 575 576
            // 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] = '';
577

578 579 580
            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.
581 582
            }

583 584 585 586 587
            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);
                }
588 589 590 591 592 593 594
            }

            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);
            }
        }
595 596 597

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

600 601 602
    /**
     *
     * @param array $formElement
Marc Egger's avatar
Marc Egger committed
603 604
     * @throws \CodeException
     * @throws \UserFormException
605 606 607 608 609 610
     */
    private function extractImageDataReplaceFile(array $formElement) {

        // Take care the necessary target directories exist.
        $cwd = getcwd();
        $sitePath = $this->store->getVar(SYSTEM_SITE_PATH, STORE_SYSTEM);
611
        if ($cwd === false || $sitePath === false || !HelperFile::chdir($sitePath)) {
Marc Egger's avatar
Marc Egger committed
612
            throw new \UserFormException(
Marc Egger's avatar
Marc Egger committed
613
                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."]),
614
                ERROR_IO_CHDIR);
615 616
        }

617
        // Get original pathFileName
618 619 620
        $field = HelperFormElement::AppendFormElementNameImageCut($formElement);
        $pathFileName = $this->store->getVar($field, STORE_SIP);
        if ($pathFileName == '' || !file_exists($pathFileName)) {
Marc Egger's avatar
Marc Egger committed
621
            throw new \UserFormException(
Marc Egger's avatar
Marc Egger committed
622
                json_encode([ERROR_MESSAGE_TO_USER => 'Empty file or file not found', ERROR_MESSAGE_TO_DEVELOPER => 'Empty file or file not found: ' . $pathFileName]),
623
                ERROR_IO_FILE_NOT_FOUND);
624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658
        }

        // '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)
            ) {
659
                HelperFile::rename($pathFileName, $pathFileName . $extSave);
660 661 662 663 664 665 666 667
            }
        }

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

        if (false === file_put_contents($pathFileName, base64_decode($imageData))) {
Marc Egger's avatar
Marc Egger committed
668
            throw new \UserFormException(
Marc Egger's avatar
Marc Egger committed
669
                json_encode([ERROR_MESSAGE_TO_USER => 'Write new image failed', ERROR_MESSAGE_TO_DEVELOPER => "Write new image failed: $pathFileName"]),
670
                ERROR_IO_WRITE);
671 672 673 674 675
        }

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

676
    /**
677 678 679
     * 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.
     *
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
680 681
     * Check also: doc/CODING.md
     *
Carsten  Rose's avatar
Carsten Rose committed
682 683 684 685
     * @param array $formElement FormElement 'upload'
     * @param string $sipUpload SIP
     * @param Sip $sip
     * @param string $modeUpload UPLOAD_MODE_UNCHANGED | UPLOAD_MODE_NEW | UPLOAD_MODE_DELETEOLD |
Carsten  Rose's avatar
Carsten Rose committed
686 687
     *                            UPLOAD_MODE_DELETEOLD_NEW
     *
688
     * @return false|string New pathFilename or false on error
Marc Egger's avatar
Marc Egger committed
689 690 691 692
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
693 694
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
695 696
     * @internal param $recordId
     */
697 698 699
    private function doUpload($formElement, $sipUpload, Sip $sip, &$modeUpload) {
        $flagDelete = false;
        $modeUpload = UPLOAD_MODE_UNCHANGED;
700

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

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

713
        // Upload - Take care the necessary target directories exist.
714 715
        $cwd = getcwd();
        $sitePath = $this->store->getVar(SYSTEM_SITE_PATH, STORE_SYSTEM);
716
        if ($cwd === false || $sitePath === false || !HelperFile::chdir($sitePath)) {
Marc Egger's avatar
Marc Egger committed
717
            throw new \UserFormException(
Marc Egger's avatar
Marc Egger committed
718
                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."]),
719
                ERROR_IO_CHDIR);
720
        }
721

722 723 724 725 726
        // 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)) {
727
                //TODO: it might be possible to delete a file, which is referenced by another record - a check would be nice.
728
                HelperFile::unlink($oldFile, $this->qfqLogFilename);
729 730 731 732 733 734 735 736 737 738 739
            }
            $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;
        }

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

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

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

751
        HelperFile::chdir($cwd);
752 753 754 755 756 757 758 759 760

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

        return $pathFileName;
    }

    /**
     * @param $formElement
761
     * @param $fileName
Marc Egger's avatar
Marc Egger committed
762 763 764
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
765 766 767 768
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    private function doImport($formElement, $fileName) {
769
        $importNamedSheetsOnly = array();
770

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

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

Carsten  Rose's avatar
Carsten Rose committed
777
        // Check for keywords which needs an explicit given document type.
778 779 780 781 782
        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])) {
Marc Egger's avatar
Marc Egger committed
783
                    throw new \UserFormException('If ' . $token .
784 785 786 787 788
                        ' is given, an explicit document type (like ' . FE_IMPORT_TYPE . '=xlsx) should be set.', ERROR_IMPORT_MISSING_EXPLICIT_TYPE);
                }
            }
        }

789 790 791 792 793 794 795 796 797 798 799
        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);
800 801 802 803 804 805 806 807 808 809 810 811 812

                // 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);
Marc Egger's avatar
Marc Egger committed
813
                    throw new \UserFormException("Worksheets: " . implode(', ', $sheetNames),
814 815 816
                        ERROR_IMPORT_LIST_SHEET_NAMES);
                }

817 818 819 820
                $spreadsheet = $reader->load($fileName);
                break;

            default:
Marc Egger's avatar
Marc Egger committed
821
                throw new \UserFormException("Unknown Excel import type: '" . $formElement[FE_IMPORT_TYPE] . "'.",
822 823 824 825
                    ERROR_UNKNOWN_EXCEL_IMPORT_TYPE);
        }

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

        foreach ($regions as $region) {
            // region: tab, startColumn, startRow, endColumn, endRow
832
            $region = OnArray::trimArray(explode(',', $region));
833 834 835 836 837 838 839 840 841 842 843 844 845 846
            $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."
                        );
847 848
                    }
                }
849
            } catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
Marc Egger's avatar
Marc Egger committed
850
                throw new \UserFormException($e->getMessage());
851
            }
852

853 854 855 856 857 858 859 860 861
            // 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];
862 863
                }
            }
864 865 866 867 868
            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];
                }
869
            }
870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887
            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);
888
                }
889 890
                $columnDefinitionArr[] = "`$columnName`   TEXT       NOT NULL  DEFAULT ''";
                $columnListArr[] = "$columnName";
891 892
            }

893 894 895 896 897 898 899 900 901 902 903 904 905
            // 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;
906
            }
907

908 909 910 911 912 913 914
            // 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);
            }
915
        }
916 917 918
    }

    /**
Carsten  Rose's avatar
Upload:  
Carsten Rose committed
919 920 921 922
     * Copy uploaded file from temporary location to final location.
     *
     * Check also: doc/CODING.md
     *
923 924 925
     * @param array $formElement
     * @param array $statusUpload
     * @return array|mixed|null|string
Marc Egger's avatar
Marc Egger committed
926 927 928 929
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
930
     */
931
    private function copyUploadFile(array $formElement, array $statusUpload) {
932 933
        $pathFileName = '';

Carsten  Rose's avatar
Upload:  
Carsten Rose committed
934
        if (!isset($statusUpload[FILES_TMP_NAME]) || $statusUpload[FILES_TMP_NAME] === '') {
935 936 937 938
            // nothing to upload: e.g. user has deleted a previous uploaded file.
            return '';
        }

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

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

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

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

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

954
        if ($pathFileName === '') {
Marc Egger's avatar
Marc Egger committed
955
            throw new \UserFormException("Upload failed, no target '" . FE_FILE_DESTINATION . "' specified.", ERROR_NO_TARGET_PATH_FILE_NAME);
956 957
        }

958 959 960 961 962 963 964
        // 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]);
        }

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

968
        // get chmodFile
969 970
        if (empty($formElement[FE_FILE_CHMOD_FILE])) {
            $chmodFile = false;
971
        } else {
972
            $chmodFile = octdec($formElement[FE_FILE_CHMOD_FILE]);
973 974
        }

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

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

980 981
        return $pathFileName;
    }
982

983 984
    /**
     * If fe['autoOrient'] is given and the MimeType corresponds to fe['autoOrientMimeType']: the given {{pathFileName:V}} will be converted.
985
     * ImageMagick 'convert' seems to do a better job than GraficsMagick (Orientation is stable even if multiple times applied).
986 987 988
     *
     * @param array $formElement
     * @param $pathFileName
Marc Egger's avatar
Marc Egger committed
989 990 991 992
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     * @throws \UserReportException
993
     */
994
    private function autoOrient(array $formElement, $pathFileName) {
995 996

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

        // Upload has matching MimeType?
1002
        $mimeTypeList = empty($formElement[FE_FILE_AUTO_ORIENT_MIME_TYPE]) ? 'image/jpeg,image/png,image/tiff' : $formElement[FE_FILE_AUTO_ORIENT_MIME_TYPE];
1003
        if (!HelperFile::checkFileType($pathFileName, $pathFileName, $mimeTypeList)) {
1004 1005 1006 1007 1008 1009 1010 1011
            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