FormAsFile.php 30.7 KB
Newer Older
Marc Egger's avatar
Marc Egger committed
1
<?php
2
declare(strict_types=1);
Marc Egger's avatar
Marc Egger committed
3 4 5 6

namespace IMATHUZH\Qfq\Core\Form;

use IMATHUZH\Qfq\Core\Database\Database;
7
use IMATHUZH\Qfq\Core\Helper\HelperFile;
8
use IMATHUZH\Qfq\Core\Helper\OnString;
9
use IMATHUZH\Qfq\Core\Helper\SqlQuery;
10
use IMATHUZH\Qfq\Core\Store\Store;
Marc Egger's avatar
Marc Egger committed
11

12
// TODO: Carsten Fragen: Habe keine Unittests zu QuickFormQuery->doForm gefunden. Wird nicht getestet. FormAsFile nicht testen oder vielleicht nur mit selenium?
13 14
// TODO: Carsten Fragen: Form backups erstellen vor deleteFormFile und exportForm?
// TODO: Carsten Fragen: "git rm" anstatt "rm" benutzen falls ordner ein git repo? Einziger unterschied: macht "git add"
15
// TODO: Carsten Fragen: Form Copy ist broken. Aber jetzt auch unnötig, da man einfach die files kopieren kann.
16
// TODO: Carsten Fragen: Koennte die column "lastImport" zu Form hinzufuegen. Dieser wird dann manuell zusammen mit "changed" beim import und export gesetzt. Mittels Left Join query mit den created columns von FormElement koennte man in importForm() feststellen, ob sich ein form geaendert hat und dieses expotieren, falls der fingerabdruck des files noch gleich ist.
Marc Egger's avatar
Marc Egger committed
17
// in doForm: wenn ein fehler auftritt, nachdem die datenbank gespeichert wird und bevor das file geschrieben wurde, dann ist das file nicht mehr up to date. "lastImport" koennte dies loesen.
18
// TODO: Carsten Fragen: QFQ formulare haben id unter 1000 und user forms ueber 1000. Wird ein QFQ formular neu importiert, erhaelt es aber eine id ueber 1000. ist dies ein problem?
19
// TODO: Carsten Fragen: Kann es sein, dass records in Dirty tabelle nicht mehr richtig abegraeumt werden, weil forms die ID wechseln?
20
// TODO: Carsten Fragen: Exceptions werden teilweise ohne Developer message angezeigt (API). Deshalb habe ich die Message teilweise in User message getan.
21
// TODO: Carsten Fragen: PHP version von gitlab runner hochstellen, damit ?string und void type syntax fuer funktionen erlaubt ist? verhindert einige fehler
22
// TODO: Carsten Fragen: Regex fuer erlaubte file names und form names im editor (+ erlauben?): ^([-\.\w]+)$  VS  [a-zA-Z0-9._+-]+
Marc Egger's avatar
Marc Egger committed
23
// TODO: Carsten Fragen: add log messages somewhere?
Marc Egger's avatar
Marc Egger committed
24 25
// TODO: Carsten Fragen: ZUERST NOCHMAL GENAU NACHTEILE PRUEFEN: In doForm koennte man auch einfach pauschal importAllForms(enforce writeable=true) und exportAllForms() machen, wenn der geladene record ein Form/Formelement ist. Bei vielen forms koennte es langsam sein. ansonsten sehe ich gerade keine klaren nachteile.

26
// TODO: BEFOORE PRUDUCTION
Marc Egger's avatar
Marc Egger committed
27
    // TODO: Fix all broken tests
28
    // TODO: write tests (file test https://medium.com/weebly-engineering/phpunit-mocking-the-file-system-using-vfsstream-5d7d79b1eb2a)
29 30
    // TODO: Tests:
        // new form => form file created
31 32
        // form field change editor => changed in form file
        // bevor ein form/formelement im form editor gespeichert wird, soll es vom file geladen werden (Save.php). Falls das file importiert wurde, gibt es eine fehlermeldung.
33 34 35 36 37 38 39 40 41 42 43 44 45 46
        // form name change => old form file deleted, new form file created
        // form delete => form file deleted

        // form file not writeable => all the above abort before changes to DB are made

        // form element create => added to form file
        // form element changed => changed in form file
        // form element deleted => removed from form file

        // form file name changed => old form invalid, new form works
        // form file parameter changed => changed in form editor
        // form file form element added => added in form editor
        // form file form element changed => changed in form editor
        // form file form element removed => removed in form editor
Marc Egger's avatar
Marc Egger committed
47

48 49
        // form folder does not exist but formEditor is opened => form folder is created, all forms exported

50 51 52 53 54 55
        // QFQ komplett neu installiert => form dir existiert mit allen form files aktuell.
        // QFQ neu installation mit existierenden forms => alte forms sind noch da, native forms (form, cron usw.) werden ueberschrieben.
        // QFQ update => alte forms noch da, updates an FormEditor ersichtlich

        // importAllForms() is executed iff the rendered QFQ report selects Form/FormElement table.

56
// TODO: MAYBE
57 58 59 60 61 62 63 64 65 66 67
    // TODO: Make unittest for isFormQuery(). Example string:
        //$sql = <<<END
        //SELECT CONCAT('p:{{pageId:T}}&form=form&r=', f.id) as _pagee, f.id, f.name, f.title, f.tableName, CONCAT('form=form&r=', f.id) as _Paged FROM where
        //FormFormElement,Form AS f ORDER BY f.name
        //
        //SELECT CONCAT('p:{{pageId:T}}&form=form&r=', f.id) as _pagee, f.id, f.name, f.title, f.tableName, CONCAT('form=form&r=', f.id) as _Paged From Order by Form AS f ORDER BY f.name
        //
        //SELECT CONCAT('p:{{pageId:T}}&form=form&r=', f.id) as _pagee, f.id, f.name, f.title, f.tableName, CONCAT('form=form&r=', f.id) as _Paged FRom having Form AS f ORDER BY f.name
        //
        //SELECT CONCAT('p:{{pageId:T}}&form=form&r=', f.id) as _pagee, f.id, f.name, f.title, f.tableName, CONCAT('form=form&r=', f.id) as _Paged from Form AS f ORDER BY f.name
        //END;
68 69 70 71 72

// TODO: DON'T DO
    // TODO: DONT DO: add column import-modification-date to Form and FormElement and set them to the same as modification date in both exportForm and importForm. compare the two modification dates in the beginning. If the actual modification is after importModification and fileStats are the same in file and in form, then exportForm.
    // TODO: DONT DO: what to do if importModificationDate and fileStats have changed? > 1) export Form to new form file named <formName>mergeConflict_timestamp 2) importForm form file, overwrite DB

73
///////////////// JUST A TEST, DELETE ME! //////////////
74 75
// FormAsFile::exportForm($formName, $this->dbArray[$this->dbIndexQfq]);
// FormAsFile::importForm($formName, $this->dbArray[$this->dbIndexQfq]);
76 77
///////////////// TEST FINISHED ////////////////////////

78 79
/////// DEBUG /////////
//        throw new \UserFormException(json_encode([
80 81
//            ERROR_MESSAGE_TO_USER => json_encode([], JSON_PRETTY_PRINT),
//            ERROR_MESSAGE_TO_DEVELOPER =>  json_encode([], JSON_PRETTY_PRINT)
82 83 84
//        ]));
///////////////////////

Marc Egger's avatar
Marc Egger committed
85 86
class FormAsFile
{
87
    /**
Marc Egger's avatar
Marc Egger committed
88 89
     * Remove the form from the DB and insert it using the form file. (only if the form file was changed)
     * If the form file can't be read, then the form is deleted from the DB and an exception is thrown.
90
     *
91
     * Form file location: SYSTEM_FORM_FILE_PATH
Marc Egger's avatar
Marc Egger committed
92 93 94
     * Recognize form file change: Compare the current file stats with the ones saved in the Form table.
     * Container References: The form file uses names instead if ids to reference container formElements. These references are translated when the formElements are inserted.
     * Ignored keys: The keys 'id', 'formId', 'feIdContainer' are ignored when reading the form file.
95
     *
96 97
     * @param string $formName
     * @param Database $database
98
     * @return bool True if form has been updated.
99 100 101
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
102
     */
103
    public static function importForm(string $formName, Database $database): bool
104
    {
105
        // Get file stats both from file system and DB and exit if they are equal
106
        $pathFileName = self::formPathFileName($formName, $database);
107
        $fileReadException = new \UserFormException(json_encode([
108
            ERROR_MESSAGE_TO_USER => "Form file not found or missing permission: "  . baseName($pathFileName),
109
            ERROR_MESSAGE_TO_DEVELOPER => "Form definition file not found or no permission to read file: '$pathFileName'"]),
110
            ERROR_FORM_NOT_FOUND);
111
        if(!file_exists($pathFileName)) {
112
            self::deleteFormDB($formName, $database);
113 114
            throw $fileReadException;
        }
115 116
        $fileStatsNew = self::formFileStatsJson($pathFileName);
        if ($fileStatsNew === false) {
117
            self::deleteFormDB($formName, $database);
118 119
            throw $fileReadException;
        }
120 121 122
        list($sql, $parameterArray) = SqlQuery::selectFormByName($formName, [F_ID, F_FILE_STATS]);
        $formFromDb = $database->sql($sql, ROW_EXPECT_0_1,
            $parameterArray, "Multiple forms with the same name: '$formName'");
123
        if (array_key_exists(F_FILE_STATS, $formFromDb) && $fileStatsNew === $formFromDb[F_FILE_STATS]) {
124
            return false;
125 126 127
        }

        // Read form file
128
        $fileContents = file_get_contents($pathFileName);
129
        if ($fileContents === false) {
130
            self::deleteFormDB($formName, $database);
131
            throw $fileReadException;
132
        }
133
        $formFromFile = json_decode($fileContents, true);
134

135
        // Delete old form with that name from DB if it exists.
136
        if (array_key_exists(F_ID, $formFromDb)) {
137
            $formId = $formFromDb[F_ID];
138
            self::deleteFormDBWithId($formId, $database);
139
        }
140

141
        // Insert new Form to DB (after filtering allowed columns and adding column 'name')
142 143 144 145 146
        $formSchema = $database->getTableDefinition(TABLE_NAME_FORM);
        $formColumns = array_column($formSchema, 'Field');
        $insertValues = array_filter($formFromFile, function ($columnName) use ($formColumns) {
            return $columnName !== F_ID && in_array($columnName, $formColumns);
        }, ARRAY_FILTER_USE_KEY); // array(column => value)
147 148 149
        $insertValues[F_NAME] = $formName;
        list($sqlFormInsert, $parameterArrayFormInsert) = SqlQuery::insertRecord(TABLE_NAME_FORM, $insertValues);
        $formId = $database->sql($sqlFormInsert, ROW_REGULAR, $parameterArrayFormInsert);
150

151 152 153
        // Delete stale formElements with the new form id (these should not exist, but better make sure)
        self::deleteFormElementsDBWithFormId($formId, $database);

154
        // Insert FormElements to DB and collect container ids
155
        $containerIds = []; // array(container_name => id)
156
        foreach ($formFromFile[F_FILE_FORM_ELEMENT] as &$formElementFromFile) {
157 158 159 160 161 162 163
            $feId = self::insertFormElement($formElementFromFile, $formId, $database);
            $formElementFromFile[FE_ID] = $feId;
            if ($formElementFromFile[FE_CLASS] === FE_CLASS_CONTAINER) {
                $containerIds[$formElementFromFile[FE_NAME]] =  $feId;
            }
        }

164
        // Update container IDs for each form element which has a container name
165 166 167
        foreach ($formFromFile[F_FILE_FORM_ELEMENT] as &$formElementFromFile) {
            if (array_key_exists(FE_FILE_CONTAINER_NAME, $formElementFromFile)) {
                $containerName = $formElementFromFile[FE_FILE_CONTAINER_NAME];
168 169
                $containerId = $containerIds[$containerName];
                $feId = $formElementFromFile[FE_ID];
170 171
                list($sql, $parameterArray) = SqlQuery::updateRecord(TABLE_NAME_FORM_ELEMENT, [FE_ID_CONTAINER => $containerId], $feId);
                $database->sql($sql, ROW_REGULAR, $parameterArray);
172
            }
173
        }
174 175 176 177 178

        // Update column fileStats if everything went well
        list($sql, $parameterArray) = SqlQuery::updateRecord(TABLE_NAME_FORM, [F_FILE_STATS => $fileStatsNew], $formId);
        $database->sql($sql, ROW_REGULAR, $parameterArray);

179
        return true;
180 181
    }

182
    /**
Marc Egger's avatar
Marc Egger committed
183
     * Reads the form from the DB and saves it in the form folder as a form file.
184 185
     * Warning: Overwrites form file without any checks or warning.
     * If the form file path does not exist, it is created and all forms are exported.
186
     *
187
     * FileStats: After the export the column "fileStats" is updated with new stats from new file.
Marc Egger's avatar
Marc Egger committed
188 189 190
     * Container FormElements: The form file uses names instead if ids to reference containers. These references are translated before saving the file.
     * Ignored columns: The columns 'id', 'name' (only Form table), 'fileStats', 'formId', 'feIdContainer' are not saved in the file.
     * FormElement order: The formElements are ordered by the 'ord' column before writing them to the file.
191
     *
192 193 194 195 196 197
     * @param string $formName
     * @param Database $database
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
198
    public static function exportForm(string $formName, Database $database) // : void
199
    {
200
        // Get form from DB
201 202 203
        list($sql, $parameterArray) = SqlQuery::selectFormByName($formName);
        $form = $database->sql($sql, ROW_EXPECT_1,
            $parameterArray, 'Form "' . $formName . '" not found or multiple forms with the same name.'); // array(column name => value)
204

205
        // Remove columns: id, name, fileStats
206 207
        $formId = $form[F_ID];
        unset($form[F_ID]);
208 209
        unset($form[F_NAME]);
        unset($form[F_FILE_STATS]);
210

211
        // Get formElements from DB
212 213
        list($sql, $parameterArray) = SqlQuery::selectFormElementById($formId);
        $formElements = $database->sql($sql, ROW_REGULAR, $parameterArray); // array(array(column name => value))
214

215
        // Translate container references (id to name) and remove all id columns
216
        $containerNames = array_reduce($formElements, function ($result, $formElement) {
217 218 219 220 221 222 223 224 225 226 227 228
            if ($formElement[FE_CLASS] === FE_CLASS_CONTAINER) {
                $containerName = $formElement[FE_NAME];
                if (in_array($containerName, $result) || $containerName === '') {
                    throw new \UserFormException(json_encode([
                        ERROR_MESSAGE_TO_USER => 'Duplicate container names.',
                        ERROR_MESSAGE_TO_DEVELOPER => "Container Form Elements must have a unique and nonempty name. Container name: '$containerName'."]),
                        ERROR_FORM_INVALID_NAME);
                }
                $result[$formElement[FE_ID]] = $containerName;
            }
            return $result;
        }, []); // array(id => name)
229
        $formElements = array_map(function ($formElement) use ($containerNames) {
230 231
            $containerId = $formElement[FE_ID_CONTAINER];
            if ($containerId !== 0) {
232
                $formElement[FE_FILE_CONTAINER_NAME] = $containerNames[$containerId];
233 234
            }
            unset($formElement[FE_ID_CONTAINER]);
235 236
            unset($formElement[FE_ID]);
            unset($formElement[FE_FORM_ID]);
237 238
            return $formElement;
        }, $formElements);
239

240
        // write form as JSON to file
241
        $form[F_FILE_FORM_ELEMENT] = $formElements;
242
        $formJson = json_encode($form, JSON_PRETTY_PRINT);
243
        $pathFileName = self::formPathFileName($formName, $database);
244
        $success = file_put_contents($pathFileName, $formJson);
245 246
        if ($success === false) {
            throw new \UserFormException(json_encode([
247
                ERROR_MESSAGE_TO_USER => "Writing form file failed: " . baseName($pathFileName),
248
                ERROR_MESSAGE_TO_DEVELOPER => "Can't write to file '$pathFileName'"]),
249 250
                ERROR_IO_WRITE_FILE);
        }
251 252 253 254 255 256 257

        // Update column fileStats
        $fileStats = self::formFileStatsJson($pathFileName);
        list($sql, $parameterArray) = SqlQuery::updateRecord(TABLE_NAME_FORM, [F_FILE_STATS => $fileStats], $formId);
        $database->sql($sql, ROW_REGULAR, $parameterArray);
    }

258 259 260 261 262 263 264 265 266 267 268 269 270
    /**
     * Import Form from file if loaded record is Form/FormElement.
     * If form file was changed, import it and throw exception.
     * If form file is not writeable, throw exception.
     *
     * @param int $recordId
     * @param string $tableName
     * @param Database $database
     * @return string|null formName|null
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
271
    public static function importFormRecordId(int $recordId, string $tableName, Database $database) // : ?string
272 273 274 275 276 277 278 279 280
    {
        $recordFormName = self::formNameFromFormRelatedRecord($recordId, $tableName, $database);
        if ($recordFormName !== null) {
            if(self::importForm($recordFormName, $database)) {
                throw new \UserFormException(json_encode([
                    ERROR_MESSAGE_TO_USER => 'Form file was changed.',
                    ERROR_MESSAGE_TO_DEVELOPER => "Form definition file has been changed. Please close tab and reload the form list and Form-Editor from scratch."]),
                    ERROR_FORM_NOT_FOUND);
            }
281
            self::enforceFormFileWritable($recordFormName, $database);
282 283 284 285
        }
        return $recordFormName;
    }

286
    /**
287
     * Deletes the form file for the given form.
288
     *
289
     * @param $formName
290 291 292
     * @param Database $database
     * @throws \CodeException
     * @throws \DbException
293 294
     * @throws \UserFormException
     */
295
    public static function deleteFormFile($formName, Database $database) // : void
296
    {
297 298
        self::enforceFormFileWritable($formName, $database);
        $pathFileName = self::formPathFileName($formName, $database);
299
        if(file_exists($pathFileName)) {
300 301 302 303 304 305 306
            $success = unlink($pathFileName);
            if ($success === false) {
                throw new \UserFormException(json_encode([
                    ERROR_MESSAGE_TO_USER => "Deleting form file failed: " . baseName($pathFileName),
                    ERROR_MESSAGE_TO_DEVELOPER => "Can't delete form file '$pathFileName'"]),
                    ERROR_IO_WRITE_FILE);
            }
307
        }
Marc Egger's avatar
Marc Egger committed
308
    }
309

310
    /**
311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327
     * Multiple errors might occur after a form file import. This hint can be added to such exceptions to help users.
     * Only returns non-empty string if the table is either Form of FormElement.
     *
     * @param string $tableName
     * @return string
     */
    public static function errorHintFormImport (string $tableName = 'Form'): string
    {
        $message = '';
        if (in_array($tableName, [TABLE_NAME_FORM, TABLE_NAME_FORM_ELEMENT])) {
            $message .= "Hint: Form definition file might have changed. Please reopen the form list and Form-Editor from scratch.";
        }
        return $message;
    }

    /**
     * Throw exception if form file or form directory is not writeable.
Marc Egger's avatar
Marc Egger committed
328
     *
329
     * @param string $formName
330
     * @param Database $database
331
     * @return void
332 333
     * @throws \CodeException
     * @throws \DbException
334 335
     * @throws \UserFormException
     */
336
    public static function enforceFormFileWritable(string $formName, Database $database) // : void
337
    {
338
        $pathFileName = self::formPathFileName($formName, $database);
339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358
        if (file_exists($pathFileName)) {
            if (!is_writable($pathFileName)) {
                throw new \UserFormException(json_encode([
                    ERROR_MESSAGE_TO_USER => "Can't write to file: " . baseName($pathFileName),
                    ERROR_MESSAGE_TO_DEVELOPER => "Can't write to file '$pathFileName'. Check permissions."]),
                    ERROR_IO_WRITE_FILE);
            }
        } else {
            if (!is_writable(dirname($pathFileName))) {
                throw new \UserFormException(json_encode([
                    ERROR_MESSAGE_TO_USER => "Can't write to form directory. Check permissions.",
                    ERROR_MESSAGE_TO_DEVELOPER => "Can't write to directory: " . dirname($pathFileName)]),
                    ERROR_IO_WRITE_FILE);
            }
        }

    }

    /**
     * Return form name if given record is an existing Form or FormElement.
359
     * Return null otherwise.
360 361 362 363
     *
     * @param int $recordId
     * @param string $recordTable
     * @param Database $database
364
     * @return string formName|null
365 366 367 368
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
369
    public static function formNameFromFormRelatedRecord(int $recordId, string $recordTable, Database $database) // : ?string
370 371
    {
        if ($recordId === 0) {
372
            return null;
373 374 375
        }
        switch ($recordTable) {
            case TABLE_NAME_FORM:
376 377 378
                list($sql, $parameterArray) = SqlQuery::selectFormById($recordId);
                $formFromDb = $database->sql($sql, ROW_EXPECT_1,
                    $parameterArray, "Form with id '$recordId' not found. " . self::errorHintFormImport());
379 380 381 382 383 384 385 386
                return $formFromDb[F_NAME];
            case TABLE_NAME_FORM_ELEMENT:
                $F_NAME = F_NAME;
                $TABLE_NAME_FORM_ELEMENT = TABLE_NAME_FORM_ELEMENT;
                $TABLE_NAME_FORM = TABLE_NAME_FORM;
                $F_ID = F_ID;
                $FE_FORM_ID = FE_FORM_ID;
                $FE_ID = FE_ID;
387 388

                // Select form name by formElement id
389 390 391 392
                $formFromDb = $database->sql("SELECT `f`.`$F_NAME` FROM `$TABLE_NAME_FORM` AS f INNER JOIN `$TABLE_NAME_FORM_ELEMENT` AS fe ON f.`$F_ID`=fe.`$FE_FORM_ID` WHERE `fe`.`$FE_ID`=?", ROW_EXPECT_1,
                    [$recordId], "Form element with id '$recordId' not found. " . self::errorHintFormImport());
                return $formFromDb[F_NAME];
            default:
393
                return null;
394 395
        }
    }
396

397 398 399 400 401 402 403 404 405 406 407
    /**
     * Returns true if the sql query selects Form or FormElement table
     *
     * @param string $sql
     * @return bool
     */
    public static function isFormQuery(string $sql): bool
    {
        // find substrings whihch start with FROM and are followed by Form or FormElement
        preg_match_all('/(?i)FROM(?-i)(.*?)\b(' . TABLE_NAME_FORM . '|' . TABLE_NAME_FORM_ELEMENT . ')\b/s', $sql, $matches);

408
        // Check if no other SQL keywords are in between FROM and the table name
409 410 411 412 413 414 415 416 417 418 419
        $keywordsAfterFrom = ['WHERE', 'GROUP BY', 'HAVING', 'WINDOW', 'ORDER BY', 'LIMIT', 'FOR', 'INTO'];
        foreach($matches[0] as $match)
        {
            if(!OnString::containsOneOfWords($keywordsAfterFrom, $match)) {
                return true;
            }
        }
        return false;
    }

    /**
420 421
     * Import all form files into the database.
     * If the file folder does not exist, it is created and all forms from the DB are exported.
422
     *
423
     * @param Database $database
424 425
     * @param bool $enforceWritable Throw exception if one of the form files is not writable by current user.
     * @param bool $deleteFromDB Delete forms from DB if there are no corresponding form files.
426 427
     * @throws \CodeException
     * @throws \DbException
428 429
     * @throws \UserFormException
     */
430
    public static function importAllForms(Database $database, bool $enforceWritable = false, bool $deleteFromDB = false) // : void
431
    {
432
        // Import all form files
433 434 435 436 437
        $formFileNames = self::formFileNames($database);
        foreach ($formFileNames as $formFileName) {
            self::importForm($formFileName, $database);
            if ($enforceWritable) {
                self::enforceFormFileWritable($formFileName, $database);
438
            }
439 440
        }

441
        // Delete all forms which are in DB but not in files
442 443 444 445 446 447
        if ($deleteFromDB) {
            $formNamesDB = self::queryAllFormNames($database);
            $formsToDelete = array_diff($formNamesDB, $formFileNames);
            foreach ($formsToDelete as $formToDelete) {
                self::deleteFormDB($formToDelete, $database);
            }
448
        }
449 450
    }

451 452 453 454 455
    /**
     * Export all forms in database to form files.
     * Warning: This overwrites form files without any checks or warning. Use cautiously.
     *
     * @param Database $database
456
     * @param bool $deleteFiles Delete form files without a corresponding form record in the DB.
457 458 459 460
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
461
    public static function exportAllForms(Database $database, bool $deleteFiles = false) // : void
462 463 464 465 466
    {
        $formNamesDB = self::queryAllFormNames($database);
        foreach ($formNamesDB as $formNameDB) {
            self::exportForm($formNameDB, $database);
        }
467 468 469 470 471 472 473
        if ($deleteFiles) {
           $formFileNames = self::formFileNames($database);
           $filesToDelete = array_diff($formFileNames, $formNamesDB);
           foreach ($filesToDelete as $fileToDelete) {
               self::deleteFormFile($fileToDelete, $database);
           }
        }
474 475
    }

Marc Egger's avatar
Marc Egger committed
476
    /**
477 478 479
     * Insert formElement to the given form.
     * Keys removed before insert: id, formId, feIdContainer
     *
Marc Egger's avatar
Marc Egger committed
480 481 482 483 484 485 486 487 488 489 490 491 492 493
     * @param array $values
     * @param int $formId
     * @param Database $database
     * @return int
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function insertFormElement(array $values, int $formId, Database $database): int
    {
        // filter allowed formElement columns
        $formElementSchema = $database->getTableDefinition(TABLE_NAME_FORM_ELEMENT);
        $formElementColumns = array_column($formElementSchema, 'Field');
        $insertValues = array_filter($values, function ($columnName) use ($formElementColumns) {
494
            return !in_array($columnName, [FE_ID, FE_FORM_ID, FE_ID_CONTAINER]) && in_array($columnName, $formElementColumns);
Marc Egger's avatar
Marc Egger committed
495 496 497 498 499 500 501 502 503 504
        }, ARRAY_FILTER_USE_KEY); // array(column => value)

        // execute insert
        $insertValues[FE_FORM_ID] = $formId;
        list($sql, $parameterArray) = SqlQuery::insertRecord(TABLE_NAME_FORM_ELEMENT, $insertValues);
        $id = $database->sql($sql, ROW_REGULAR, $parameterArray);

        return $id;
    }

505 506 507 508 509 510 511 512 513 514
    /**
     * Delete form with given name and its form elements from DB
     *
     * @param string $formName
     * @param Database $database
     * @return void
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
515
    private static function deleteFormDB(string $formName, Database $database) // : void
516
    {
517 518 519
        list($sql, $parameterArray) = SqlQuery::selectFormByName($formName, [F_ID]);
        $formFromDb = $database->sql($sql, ROW_EXPECT_0_1,
            $parameterArray, "Multiple forms with the same name: '$formName'");
520
        if (array_key_exists(F_ID, $formFromDb)) {
521
            self::deleteFormDBWithId($formFromDb[F_ID], $database);
522 523 524 525 526 527 528 529 530 531 532 533
        }
    }

    /**
     * Delete form with given id and its form elements from DB
     *
     * @param $formId
     * @param Database $database
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
534
    private static function deleteFormDBWithId(int $formId, Database $database) // : void
535 536 537 538
    {
        $F_ID = F_ID; // can't use constants in strings directly
        $TABLE_NAME_FORM = TABLE_NAME_FORM; // can't use constants in strings directly
        $database->sql("DELETE FROM `$TABLE_NAME_FORM` WHERE `$F_ID`=? LIMIT 1", ROW_REGULAR, [$formId]);
539 540 541 542 543 544 545 546 547 548 549 550
        self::deleteFormElementsDBWithFormId($formId, $database);
    }

    /**
     * Delete form elements with given formId from DB
     *
     * @param $formId
     * @param Database $database
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
551
    private static function deleteFormElementsDBWithFormId(int $formId, Database $database) // : void
552
    {
553 554 555 556
        $TABLE_NAME_FORM_ELEMENT = TABLE_NAME_FORM_ELEMENT; // can't use constants in strings directly
        $FE_FORM_ID = FE_FORM_ID; // can't use constants in strings directly
        $database->sql("DELETE FROM `$TABLE_NAME_FORM_ELEMENT` WHERE `$FE_FORM_ID`=?", ROW_REGULAR, [$formId]);
    }
557 558

    /**
Marc Egger's avatar
Marc Egger committed
559 560 561
     * Return a selection of file stats as a JSON string which serves as a fingerprint of the file.
     * If any one of the stats changed the file content was probably changed as well.
     *
562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579
     * @param string $pathFileName
     * @return false|string
     */
    private static function formFileStatsJson(string $pathFileName)
    {
        $stats = stat($pathFileName);
        if ($stats === false) {
            return false;
        }
        return json_encode([
            'modified' => $stats['mtime'],
            'size' => $stats['size'],
            'inode' => $stats['ino']
        ]);
    }

    /**
     * Return correct pathFileName of form file relative to current working directory.
580
     * Create path if it doesn't exist and export all forms from DB.
581 582
     *
     * @param string $formName
583
     * @param Database $database
584
     * @return string
585 586
     * @throws \CodeException
     * @throws \DbException
587 588
     * @throws \UserFormException
     */
589
    private static function formPathFileName(string $formName, Database $database): string
590
    {
591 592 593
        // validate form name
        if (!HelperFile::isValidFileName($formName)) {
            throw new \UserFormException(json_encode([
594 595
                ERROR_MESSAGE_TO_USER => 'Reading/Writing Form file failed.',
                ERROR_MESSAGE_TO_DEVELOPER => "Form name '$formName' not valid. Name may only consist of alphanumeric characters and _ . -"]),
596 597
                ERROR_FORM_INVALID_NAME);
        }
598
        return self::formPath($database) . '/' . $formName . ".json";
599 600 601
    }

    /**
602
     * Return the path of the form directory relative to CWD.
603
     * Create path if it doesn't exist and export all forms from DB.
604
     *
605
     * @param Database $database
606
     * @return string
607 608
     * @throws \CodeException
     * @throws \DbException
609
     * @throws \UserFormException
610
     * @throws \UserReportException
611
     */
612
    private static function formPath(Database $database): string
613
    {
614 615
        $systemQfqProjectDir = Store::getInstance()->getVar(SYSTEM_QFQ_PROJECT_DIR_SECURE, STORE_SYSTEM);
        $formPath = HelperFile::correctRelativePathFileName(HelperFile::joinPathFilename($systemQfqProjectDir, 'form'));
616
        if (!is_dir($formPath)) {
617 618

            // create path
619 620 621 622 623 624 625
            $success = mkdir($formPath, 0777, true);
            if ($success === false) {
                throw new \UserFormException(json_encode([
                    ERROR_MESSAGE_TO_USER => "Can't create form file path.",
                    ERROR_MESSAGE_TO_DEVELOPER => "Can't create path: " . $formPath]),
                    ERROR_IO_WRITE_FILE);
            }
626 627

            // export all forms
628
            self::exportAllForms($database);
629 630
        }
        return $formPath;
631
    }
632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647

    /**
     * Return array of all form names in the Form table.
     *
     * @param Database $database
     * @return array [formName]
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function queryAllFormNames(Database $database): array
    {
        $NAME = F_NAME;
        $FORM = TABLE_NAME_FORM;
        return array_column($database->sql("SELECT `$NAME` FROM `$FORM`", ROW_REGULAR), $NAME);
    }
648 649

    /**
650
     * Return array of form file names contained in the form path (without suffix).
651 652
     *
     * @param Database $database
653
     * @return array|false [formName]
654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675
     * @throws \CodeException
     * @throws \DbException
     * @throws \UserFormException
     */
    private static function formFileNames(Database $database): array
    {
        $formPath = self::formPath($database);
        $files = scandir($formPath);
        if ($files === false) {
            throw new \UserFormException(json_encode([
                ERROR_MESSAGE_TO_USER => "Reading directory failed.",
                ERROR_MESSAGE_TO_DEVELOPER => "Can't read directory: " . $formPath]),
                ERROR_IO_READ_FILE);
        }
        return $jsonFileNames = array_reduce($files, function ($result, $file) {
            $fileInfo = pathinfo($file);
            if ($fileInfo['extension'] === 'json') {
                $result[] = $fileInfo['filename'];
            }
            return $result;
        }, []);
    }
Marc Egger's avatar
Marc Egger committed
676
}