Form.rst

    secure**, cause there is no *content* check on the server after the upload.

* *maxFileSize* = `<size>` - max filesize in bytes (no unit), kilobytes (k/K) or megabytes (m/M) for an uploaded file.
  If empty or not given, take value from Form, System or System default.

* *fileTrash* = [0|1] - Default: '1'. This option en-/disables the trash button right beside the file chooser. By default
  the trash is visible. The trash is only visible if a) there is already a file uploaded or b) a new file has been chosen.

* *fileTrashText* = `<string>` - Default: ''. Will be shown right beside the trash glyph-icon.

* *fileDestination* = `<pathFileName>` - Destination where to copy the file. A good practice is to specify a relative `fileDestination` -
  such an installation (filesystem and database) are moveable.

  * If the original filename should be part of `fileDestination`, the variable *{{filename}}*
    (see :ref:`STORE_VARS`) can be used. Example ::

      fileDestination={{SELECT 'fileadmin/user/pictures/', p.name, '-{{filename}}' FROM Person AS p WHERE p.id={{id:R0}} }}

    * Several more variants of the filename and also mimetype and filesize are available. See :ref:`STORE_VARS`.

    * The original filename will be sanitized: only '<alnum>', '.' and '_' characters are allowed. German 'umlaut' will
      be replaced by 'ae', 'ue', 'oe'. All non valid characters will be replaced by '_'.

  * If a file already exist under `fileDestination`, an error message is shown and 'save' is aborted. The user has no
    possibility to overwrite the already existing file. If the whole workflow is correct, this situation should no
    arise. Check also *fileReplace* below.

  * All necessary subdirectories in `fileDestination` are automatically created.

  * Using the current record id in the `fileDestination`: Using {{r}} is problematic for a 'new' primary record: that
    one is still '0' at the time of saving. Use `{{id:R0}}` instead.

  * Uploading of malicious code (e.g. PHP files) is hard to detect. The default mime type check can be easily faked
    by an attacker. Therefore it's recommended to use a `fileDestination`-directory, which is secured against script
    execution (even if the file has been uploaded, the webserver won't execute it) - see :ref:`SecureDirectFileAccess`.

* *sqlBefore*,  *sqlAfter*: available in :ref:`Upload simple mode`  and :ref:`Upload advanced mode`.
* *slaveId*,  *sqlInsert*, *sqlUpdate*, *sqlDelete*, *sqlUpdate*: available only in :ref:`Upload advanced mode`.

* `fileSize` / `mimeType`

  * In :ref:`Upload simple mode` the information of `fileSize` and `mimeType` will be automatically updated on the current
    record, if table columns `fileSize` and/or `mimeType` exist.

    * If there are more than one Upload FormElement in a form, the automatically update for `fileSize` and/or `mimeType`
      are not done automatically.

  * In :ref:`Upload advanced mode`  the `fileSize` and / or `mimeType`  have to be updated with an explicit SQL statement::

       sqlAfter = {{UPDATE Data SET mimeType='{{mimeType:V}}', fileSize={{fileSize:V}} WHERE id={{id:R}} }}

* *fileReplace* = `always` - If `fileDestination` exist - replace it by the new one.

* *chmodFile* = <unix file permission mode> - e.g. `660` for owner and group read and writeable. Only the numeric mode is allowed.
* *chmodDir* = <unix file permission mode> - e.g. `770` for owner and group read, writeable and executable. Only the
  numeric mode is allowed. Will be applied to all new created directories.

* *autoOrient:* images might contain EXIF data (e.g. captured via mobile phones) incl. an orientation tag like TopLeft,
  BottomRight and so on. Web-Browser and other grafic programs often understand and respect those information and rotate
  such images automatically. If not, the image might be displayed in an unwanted oritentation.
  With active option 'autoOrient', QFQ tries to normalize such images via 'convert' (part of ImageMagick). Especially
  if images are processed by the QFQ internal 'Fabric'-JS it's recommended to normalize images first. The normalization
  process does not solve all orientation problems.

  * *autoOrient* = [0|1]
  * *autoOrientCmd* = 'convert -auto-orient {{fileDestination:V}} {{fileDestination:V}}.new; mv {{fileDestination:V}}.new {{fileDestination:V}}'
  * *autoOrientMimeType* = image/jpeg,image/png,image/tiff

  If the defaults for `autoOrientCmd` and `autoOrientMimeType` are sufficient, it's not necessary to specify them.

.. _`downloadButton`:

* *downloadButton* = `t:<string>` - If given, shows a button to download the previous uploaded file - instead of the string given in
  `fe.value`. The button is only shown if `fe.value` points to a readable file on the server.

  * If `downloadButton` is empty, just shows the regular download glyph.
  * To just show the filename: `downloadButton = t:{{filenameOnly:V}}` (see :ref:`STORE_VARS`)
  * Additional attributes might be given like `downloadButton = t:Download|o:check file|G:0`. Please check :ref:`download`.

* *fileUnzip* - If the file is a ZIP file (only then) it will be unzipped. If no directory is given via ``fileUnzip``, the
  basedir of ``fileDestination`` is taken, appended by ``unpack``.

  If an unzip will be done, for each file of the archive STORE_VAR will be filled (name, path of the extracted file,
  mime type, size) and the following will be triggered: *sqlValidate, slaveId, sqlBefore, sqlAfter, sqlInsert, sqlUpdate*.

  Example::

    fileDestination = fileadmin/file_{{id:R}}.zip
    fileUnzip
    sqlValidate ={{! SELECT '' FROM (SELECT '') AS fake WHERE '{{mimeType:V}}' LIKE 'application/pdf%' }}
    expectRecords=1
    messageFail=Unexpected filetype

    # Set new
    sqlAfter={{INSERT INTO Upload (pathFileName) VALUES '{{filename:V}}' }}

* `fileSplit`, `fileDestinationSplit`, `tableNameSplit`: see :ref:`split-pdf-upload`

* Excel Import: QFQ offers functionality to directly import excel data into the database. This functionality can
  optionally be combined with saving the file by using the above parameters like `fileDestination`.
  The data is imported without formatting. Please note that this means Excel dates will be imported as a number
  (e.g. 43214), which is the serial value date in Excel. To convert such a number to a MariaDb date, use:
  `DATE_ADD('1899-12-30', INTERVAL serialValue DAY)`.

  * *importToTable* = <[db.]tablename> - **Required**. Providing this parameter activates the import. If the table
    doesn't exist, it will be created.
  * *importToColumns* = <col1>,<col2>,... - If none provided, the Excel column names A, B, ... are used. Note: These
    have to match the table's column names if the table already exists.
  * *importRegion* = [tab],[startColumn],[startRow],[endColumn],[endRow]|... - All parts are optional (default:
    entire 1st sheet). Tab can either be given as an index (1-based) or a name. start/endColumn can be given either
    numerically (1, 2, ...) or by column name (A, B, ...). Note that you can specify several regions to import.
  * *importMode* = `append` (default) | `replace` - The data is either appended or replace in the specified table.
  * *importType* = `auto` (default) | `xls` | `xlsx` | `ods` | `csv` - Define what kind of data should be expected by the
    Spreadsheet Reader.
  * *importNamedSheetsOnly* = <comma separated list of sheet names>. Use this option if specific sheets cause problems
    during import and should be skipped, by naming only those sheets, who will be read. This will also reduce the memory
    usage.
  * *importSetReadDataOnly* = 0|1. Read only cell data, not the cell formatting. Warning: cell types other than numerical
    will be misinterpreted.
  * *importListSheetNames* = 0|1. For debug use only. Will open a dialog and report all found worksheet names.


Immediately after the upload finished (before the user press save), the file will be checked on the server for it's
content or file extension (see 'accept').

The maximum size is defined by the minimum of `upload_max_filesize`, `post_max_size` and `memory_limit` (PHP script) in the php.ini.

In case of broken uploads, please also check `max_input_time` in php.ini.

Deleting a record and the referenced file
"""""""""""""""""""""""""""""""""""""""""

If the user deletes a record (e.g. pressing the delete button on a form) which contains reference(s) to files, such files
are deleted too. Slave records, which might be also deleted through a 'delete'-form, are *not* checked for file references
and therefore such files are not deleted on the filesystem.

Only column(name)s which contains `pathFileName` as part of their name, are checked for file references.

If there are other records, which references the same file, such files are not deleted.
It's a very basic check: just the current column of the current table is compared. In general it's not a good idea to
have multiple references to a single file. Therefore this check is just a fallback.

.. _Upload simple mode:

Upload simple mode
""""""""""""""""""

Requires: *'upload'-FormElement.name = 'column name'* of an column in the primary table.

After moving the file to `fileDestination`, the current record/column will be updated to `fileDestination`.
The database definition of the named column has to be a string variant (varchar, text but not numeric or else).
On form load, the column value will be displayed as the whole value (pathFileName)

Deleting an uploaded file in the form (by clicking on the trash near beside) will delete
the file on the filesystem as well. The column will be updated to an empty string.

This happens automatically without any further definiton in the 'upload'-FormElement.

Multiple 'upload'-FormElements per form are possible. Each of it needs an own table column.

.. _Upload advanced mode:

Upload advanced mode
""""""""""""""""""""

Requires: *'upload'-FormElement.name* is unknown as a column in the primary table.

This mode will serve further database structure scenarios.

A typical name for such an 'upload'-FormElement, to show that the name does not exist in the primary table, might start
with 'my', e.g. 'myUpload1'.

* *FormElement.value* = `<string>` - The path/filename, shown during 'form load' to indicate a previous uploaded file, has to be queried
  with this field. E.g.::

      {{SELECT pathFileNamePicture FROM Note WHERE id={{slaveId}} }}

* *FormElement.parameter*:

  * *fileDestination* = `<pathFileName>` - define the path/filename
    (see :ref:`STORE_VARS`). E.g.::

     fileDestination=fileadmin/person/{{name:R0}}_{{id:R}}/uploads/picture_{{filename}}

  * *slaveId* = `<id>` - Defines the target record where to retrieve and store the path/filename of the uploaded file. Check also :ref:`slave-id`. E.g.::

      slaveId={{SELECT id FROM Note WHERE pId={{id:R0}} AND type='picture' LIMIT 1}}

  * *sqlBefore* = `{{<query>}}` - fired during a form save, before the following queries are fired.

  * *sqlInsert* = `{{<query>}}` - fired if `slaveId=0` and an upload exist (user has choosen a file)::

      sqlInsert={{INSERT INTO Note (pId, type, pathFileName) VALUE ({{id:R0}}, 'image', '{{fileDestination}}') }}

  * *sqlUpdate* = `{{<query>}}` - fired if `slaveId>0` and an upload exist (user has choosen a file). E.g.::

      sqlUpdate={{UPDATE Note SET pathFileName = '{{fileDestination}}' WHERE id={{slaveId}} LIMIT 1}}

  * *sqlDelete* = `{{<query>}}` - fired if `slaveId>0` and no upload exist (user has not choosen a file). E.g.::

      sqlDelete={{DELETE FROM Note WHERE id={{slaveId:V}}  LIMIT 1}}

  * *sqlAfter* = `{{<query>}}` - fired after all previous queries have been fired. Might update the new created id to a primary record. E.g.::

      sqlAfter={{UPDATE Person SET noteIdPicture = {{slaveId}} WHERE id={{id:R0}} LIMIT 1 }}


.. _split-pdf-upload:

Split PDF Upload
""""""""""""""""

Additional to the upload, it's possible to split the uploaded file (only PDF files) into several SVG or JPEG files, one
file per PDF page. The split is done via a) http://www.cityinthesky.co.uk/opensource/pdf2svg/ or b) Image Magick `convert`.

Currently, QFQ can only split PDF files.

If the source file is not of type PDF, activating ``fileSplit`` has no impact: no split and NO complain about invalid
file type.

* *FormElement.parameter*:

  * *fileSplit* = `<type>` - Activate the splitting process. Possible values: `svg` or `jpeg`. No default.
  * *fileSplitOptions* = `<command line options>`.

    * [svg] - no default
    * [jpeg] - default: `-density 150 -quality 90`

  * *fileDestinationSplit* = `<pathFileName (pattern)>` - Target directory and filename pattern for the created &
    split'ed files. Default <fileDestination>.split/split.<nr>.<fileSplit>.
    If explicit given, respect that SVG needs a printf style for <nr>, whereas JPEG is numbered automatically. E.g. ::

       [svg] fileDestinationSplit = fileadmin/protected/{{id:R}}.{{filenameBase:V}}.%02d.svg
       [jpeg] fileDestinationSplit = fileadmin/protected/{{id:R}}.{{filenameBase:V}}.jpg

  * *tableNameSplit* = `<tablename>` - Default: name of table of current form. This name will be saved in table `Split`


The splitting happens immediately after the user pressed `save`.

To easily access the split files via QFQ, per file one record is created in table 'Split'.

Table 'Split':

+--------------+--------------------------------------------------------------------------------------------+
| Column       | Description                                                                                |
+==============+============================================================================================+
| id           | Uniq auto increment index                                                                  |
+--------------+--------------------------------------------------------------------------------------------+
| tableName    | Name of the table, where the reference to the original file (multipage PDF file) is saved. |
+--------------+--------------------------------------------------------------------------------------------+
| xId          | Primary id of the reference record.                                                        |
+--------------+--------------------------------------------------------------------------------------------+
| pathFileName | Path/filename reference to one of the created files                                        |
+--------------+--------------------------------------------------------------------------------------------+

One usecase why to split an upload: annotate individual pages by using the `FormElement`.type=`annotate`.

.. _class-action:

Class: Action
-------------

FormElement.type: before... | after...
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

These type of 'action' *FormElements* will be used to implement data validation or creating/updating additional records.

Types:

* beforeLoad (e.g. good to check access permission)
* afterLoad
* beforeSave (e.g. to prohibit creating of duplicate records)
* afterSave (e.g. to to create & update additional records)
* beforeInsert
* afterInsert
* beforeUpdate
* afterUpdate
* beforeDelete (e.g. to delete slave records)
* afterDelete
* paste (configure copy/paste forms)

.. _sqlValidate:

FormElement.parameter: sqlValidate
""""""""""""""""""""""""""""""""""

  Perform checks by firing an SQL query and expecting a predefined number of selected records.

* OK: the `expectRecords` number of records has been selected. Continue processing the next *FormElement*.
* Fail: the `expectRecords` number of records has not been selected (less or more): Display the error message
  `messageFail` and abort the whole (!) current form load or save.

*FormElement.parameter*:

* *requiredList* = `<fe.name[s]>` - List of `native`-*FormElement* names: only if all of those elements are filled
  (!=0 and !=''), the *current* `action`-*FormElement* will be processed. This will enable or disable the check,
  based on the user input! If no `native`-*FormElement* names are given, the specified check will always be performed.

* *sqlValidate* = `{{<query>}}` - validation query. E.g.: `sqlValidate={{SELECT id FROM Person AS p WHERE p.name LIKE {{name:F:all}} AND p.firstname LIKE {{firstname:F:all}} }}`

* *expectRecords* = `<value>`- number of expected records.

  * *expectRecords* = `0` or *expectRecords* = `0,1` or *expectRecords* = `{{SELECT COUNT(id) FROM Person}}`
  * Separate multiple valid record numbers by ','. If at least one of those matches, the check will pass successfully.

* *messageFail* = `<string>` - Message to show. E.g.: *messageFail* = `There is already a person called {{firstname:F:all}} {{name:F:all}}`

.. _slave-id:

FormElement.parameter: slaveId
""""""""""""""""""""""""""""""

Most of the slaveId concept is part of sqlInsert / sqlUpdate - see below.

.. note::

    * *slaveId*: 0 (default) or any integer which references a record.
    * Set *slaveId* explicit or by query: ``slaveId = 123`` or ``slaveId = {{SELECT id ...}}``.
    * *fillStoreVar* is fired first, than *slaveId*. Don't  use ``{{slaveId:V}}`` in *fillStoreVar*.
    * To set *slaveId*, a value from STORE_VARS can be used:  ``slaveId={{someId:V}}``.
    * ``{{slaveId:V}}`` can be used in any query of the current *FormElement* (but not *fillStoreVar*).
    * If the *FormElement* name is equal to a column of the primary table: QFQ updates the current loaded primary table
      record with the latest *slaveId*.

.. important::

    After an INSERT (= *sqlInsert*) the `last_insert_id()` is copied to *{{slaveId:V}}* automatically.

FormElement.parameter: sqlBefore / sqlInsert / sqlUpdate / sqlDelete / sqlAfter
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

.. tip::

    * Flexible way to update record(s), even on different table(s).
    * Often used by *FormElement.type=afterSave* or similar.

.. note::

    Side note: a) Form.type *beforeLoad|Save|Insert|Update* is independent of b) Form.parameter *sqlBefore* / *sqlAfter*.
    Think of that a) represents a class and b) is a property of a class.

All of the following attributes are optional:

* *requiredList = [<fe.name>,]* - List of `native`-*FormElement* names.

   * Simplifies to completely enable or disable the current FormElement.
   * If empty: process the current FormElement. *This the typical situation*.
   * If not empty, all named FormElements will be checked: if all of them are filled, the current
     *FormElement* will be processed else not.
   * Note: The *requiredList* is independent of *FormElement.mode=required*.

* *sqlBefore = {{<query>}}* - always fired (before any *sqlInsert*, *sqlUpdate*, ..)
* *sqlInsert = {{<query>}}* - fired if *slaveId == 0* or *slaveId == ''*.
* *sqlUpdate = {{<query>}}* - fired if *slaveId > 0*.
* *sqlDelete = {{<query>}}* - fired if *slaveId > 0*, after *sqlInsert* or *sqlUpdate*. Be careful not to delete
  filled records! Look for *sqlHonorFormElements* to simplify checks.
* *sqlAfter = {{<query>}}* - always fired (after *sqlInsert*, *sqlUpdate* or *sqlDelete*).
* *sqlHonorFormElements = [<fe.name>,]* list of *FormElement* names.

  * If one of the named *FormElements* is given:

    * fire *sqlInsert* if *slaveId == 0*
    * fire *sqlUpdate* if *slaveId* > 0*

  * If all of the named *FormElements* are empty:

    * fire *sqlDelete* if *slaveId > 0*


Example
"""""""

Situation 1: master.xId=slave.id (1:1)

* Name the action element 'xId': than {{slaveId}} will be automatically set to the value of 'master.xId'

  * {{slaveId}} == 0 ? 'sqlInsert' will be fired.
  * {{slaveId}} != 0 ? 'sqlUpdate' will be fired.

* In case of firing 'sqlInsert', the 'slave.id' of the new created record are copied to master.xId (the database will
  be updated automatically).

* If the automatic update of the master record is not suitable, the action element should have no name or a name
  which does not exist as a column of the master record. Define  `slaveId={{SELECT id ...}}`

* Two *FormElements*  `myStreet` and `myCity`:

  * Without *sqlHonorFormElements*. Parameter: ::

       sqlInsert = {{INSERT INTO address (`street`, `city`) VALUES ('{{myStreet:FE:alnumx:s}}', '{{myCity:FE:alnumx:s}}') }}
       sqlUpdate = {{UPDATE address SET `street` = '{{myStreet:FE:alnumx:s}}', `city` = '{{myCity:FE:alnumx:s}}'  WHERE id={{slaveId}} LIMIT 1 }}
       sqlDelete = {{DELETE FROM Address WHERE id={{slaveId}} AND '{{myStreet:FE:alnumx:s}}'='' AND '{{myCity:FE:alnumx:s}}'='' LIMIT 1 }}

  * With *sqlHonorFormElements*. Parameter: ::

       sqlHonorFormElements = myStreet, myCity     # Non Templategroup
       sqlInsert = {{INSERT INTO address (`street`, `city`) VALUES ('{{myStreet:FE:alnumx:s}}', '{{myCity:FE:alnumx:s}}') }}
       sqlUpdate = {{UPDATE address SET `street` = '{{myStreet:FE:alnumx:s}}', `city` = '{{myCity:FE:alnumx:s}}'  WHERE id={{slaveId}} LIMIT 1 }}
       sqlDelete = {{DELETE FROM Address WHERE id={{slaveId}} LIMIT 1 }}

       # For Templategroups: sqlHonorFormElements = myStreet%d, myCity%d

Situation 2: master.id=slave.xId (1:n)

* Name the action element *different* to any column name of the master record (or no name).
* Determine the slaveId: `slaveId={{SELECT id FROM Slave WHERE slave.xxx={{...}} LIMIT 1}}`

  * {{slaveId}} == 0 ? 'sqlInsert' will be fired.
  * {{slaveId}} != 0 ? 'sqlUpdate' will be fired.

* Two *FormElements*  `myStreet` and `myCity`. The `person` is the master record, `address` is the slave:

  * Without *sqlHonorFormElements*. Parameter: ::

       slaveId = {{SELECT id FROM Address WHERE personId={{id}} ORDER BY id LIMIT 1 }}
       sqlInsert = {{INSERT INTO address (`personId`, `street`, `city`) VALUES ({{id}}, '{{myStreet:FE:alnumx:s}}', '{{myCity:FE:alnumx:s}}') }}
       sqlUpdate = {{UPDATE address SET `street` = '{{myStreet:FE:alnumx:s}}', `city` = '{{myCity:FE:alnumx:s}}'  WHERE id={{slaveId}} LIMIT 1 }}
       sqlDelete = {{DELETE FROM Address WHERE id={{slaveId}} AND '{{myStreet:FE:alnumx:s}}'='' AND '{{myCity:FE:alnumx:s}}'='' LIMIT 1 }}

  * With *sqlHonorFormElements*. Parameter: ::

       slaveId = {{SELECT id FROM Address WHERE personId={{id}} ORDER BY id LIMIT 1 }}
       sqlHonorFormElements = myStreet, myCity       # Non Templategroup
       sqlInsert = {{INSERT INTO address (`personId`, `street`, `city`) VALUES ({{id}}, '{{myStreet:FE:alnumx:s}}', '{{myCity:FE:alnumx:s}}') }}
       sqlUpdate = {{UPDATE address SET `street` = '{{myStreet:FE:alnumx:s}}', `city` = '{{myCity:FE:alnumx:s}}'  WHERE id={{slaveId}} LIMIT 1 }}
       sqlDelete = {{DELETE FROM Address WHERE id={{slaveId}} LIMIT 1 }}

       # For Templategroups: sqlHonorFormElements = myStreet%d, myCity%d

FormElement.parameter: saveFormJson, saveFormJsonName (System)
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

* *FormElement.parameter.saveFormJson* and *FormElement.parameter.saveFormJsonName*

  * These parameters are used in the json form editor. See :ref:`formAsFile`.
  * If both parameters are present in an action FormElement then the form with name given by `saveFormJsonName` is
    overwritten by the json string given by `saveFormJson` when the action element is processed.
  * A backup of the previous version of the form is saved before overwriting. See :ref:`formAsFile`.

Type: sendmail
^^^^^^^^^^^^^^

* Send mail(s) will be processed after:

  * saving the record ,
  * processing all uploads,
  * together with `after...` action `FormElements` in the given order.


* *FormElement.value* = `<string>` - Body of the email. See also: :ref:`html-formatting<html-formatting>`

* *FormElement.parameter*:

  * *sendMailTo* = `<string>` - Comma-separated list of receiver email addresses. Optional: 'realname <john@doe.com>.
    If there is no recipient email address, **no** mail will be sent.
  * *sendMailCc* = `<string>` - Comma-separated list of receiver email addresses. Optional: 'realname <john@doe.com>.
  * *sendMailBcc* = `<string>` - Comma-separated list of receiver email addresses. Optional: 'realname <john@doe.com>.
  * *sendMailFrom* = `<string>` - Sender of the email. Optional: 'realname <john@doe.com>'. **Mandatory**.
  * *sendMailSubject* = `<string>` - Subject of the email.
  * *sendMailReplyTo* = `<string>` - Reply this email address. Optional: 'realname <john@doe.com>'.
  * *sendMailAttachment* = `<string>` - List of 'sources' to attach to the mail as files. Check :ref:`attachment` for options.
  * *sendMailHeader* = `<string>` - Specify custom header.
  * *sendMailFlagAutoSubmit* = `<string>` - **on|off** - If 'on' (default), the mail contains the header
    'Auto-Submitted: auto-send' - this suppress a) OoO replies, b) forwarding of emails.
  * *sendMailGrId* = `<string>` - Will be copied to the mailLog record. Helps to setup specific logfile queries.
  * *sendMailXId* = `<string>` - Will be copied to the mailLog record. Helps to setup specific logfile queries.
  * *sendMailXId2* = `<string>` - Will be copied to the mailLog record. Helps to setup specific logfile queries.
  * *sendMailXId3* = `<string>` - Will be copied to the mailLog record. Helps to setup specific logfile queries.
  * *sendMailMode* = `<string>` - **html** - if set, the e-mail body will be rendered as html.
  * *sendMailSubjectHtmlEntity* = `<string>` - **encode|decode|none** - the mail subject will be htmlspecialchar() encoded / decoded (default) or none (untouched).
  * *sendMailBodyHtmlEntity*= `<string>`  - **encode|decode|none** - the mail body will be htmlspecialchar() encoded, decoded (default) or none (untouched).
  * *sqlBefore* / *sqlAfter* = `<string>` - can be used like with other action elements (will be fired before/after sending the e-mail).

* An **empty** *sendMailTo* will **cancel** any sendmail action, even if *sendMailCc|Bcc* is set. This can be used to
  determine during runtime if sending is wished.
* To use values of the submitted form, use the STORE_FORM. E.g. `{{name:F:allbut}}`
* To use the `id` of a new created or already existing primary record, use the STORE_RECORD. E.g. `{{id:R}}`.
* By default, QFQ stores values 'htmlspecialchars()' encoded. If such values have to send by email, the html entities are
  unwanted. Therefore the default setting for 'subject' und 'body' is to decode the values via 'htmlspecialchars_decode()'.
  If this is not wished, it can be turned off by `sendMailSubjectHtmlEntity=none` and/or `sendMailBodyHtmlEntity=none`.

* For debugging, please check :ref:`REDIRECT_ALL_MAIL_TO`.

.. tip::

  If you encounter ``\r`` at EOL character in your emails, you probably use a QFQ variable for the *body*.
  Switch of the escaping by using ``-`` as escape/action class. E.g.: ``{{body:F:all:-}}``

Example to attach one ``file1.pdf`` (with the attachment filename ``readme.pdf``) and concatenate two PDF, created on the fly
from the www.example.com and ?export (with the attachment filename 'personal.pdf'): ::

  sendMailAttachment = F:fileadmin/file1.pdf|d:readme.pdf|C|u:http://www.example.com|p:?id=export&r=123&_sip=1|d:personal.pdf

Type: paste
^^^^^^^^^^^

See also :ref:`copy-form`.

* *sql1* = `{{<query>}}` - e.g. `{{!SELECT {{id:P}} AS id, '{{myNewName:FE:allbut}}' AS name}}` (only one record) or `{{!SELECT i.id AS id, {{basketId:P}} AS basketId FROM Item AS i WHERE i.basketId={{id:P}} }}` (multiple records)

  * Pay attention to '!'.
  * For every row, a new record is created in `recordDestinationTable`.
  * Column 'id' is not copied.
  * The `recordSourceTable` together with column `id` will identify the source record.
  * Columns not specified, will be copied 1:1 from source to destination.
  * Columns specified, will overwrite the source value.

* *FormElement.parameter*:

  * *recordSourceTable* = `<tableName>` - Optional: table from where the records will be copied. Default: <recordDestinationTable>
  * *recordDestinationTable* = `<tableName>`  - table where the new records will be copied to.
  * *translateIdColumn* = `<column name>`  - column name to update references of newly created id's.

.. _form-magic:

Form Magic
----------

* Read the following carefully to understand and use QFQ form functionality.
* Check also the :ref:`Form process order<form-process-order>`.

Parameter
^^^^^^^^^

.. important::

    SIP parameter name = primary table column name

    Parameter (one or more) in the SIP url, which *exist* as a column in the form table (SIP parameter name is equal to a
    table column name), will be automatically saved in the record!

.. Comment to separate block

Example: A slave record (e.g. an address of a person) has to be assigned to a master record (a person):
``person.id=address.pId``. Just give the `pId` in the link who calls the address form. The following creates a 'new'
button for an address for all persons, and the pId will be automatically saved in the address table: ::

    SELECT CONCAT('p:{{pageAlias:T}}&form=address&r=0&pId=', p.id) AS _pagen FROM Person AS p

Remember: it's a good practice to specify such parameter in Form.permitNew and/or Form.permitEdit. It's only a check for
the webmaster, not to forgot a parameter in a SIP url.

.. note::

    FormElement.type = subrecord

    Subrecord's typically use `new`, `edit` and `delete` links. To inject parameter in those QFQ created
    links, use `FormElement.parameter.detail` . See :ref:`subrecord-option`.

.. note::

    FormElement.type = extra

    If a table column should be saved with a specific value, and the value should not be shown to the user, the FE.type='extra'
    will do the job. The value could be static or calculated on the fly. Often it's easier to specify such a parameter/value
    in the SIP url, but if the form is called from multiple places, an `extra` element is more suitable.
    In traditional web applications HTML input fields of type hidden are often used for this. Such content can be tempered by
    an attacker. It's much safer to use SIP parameter or *FormElement.type=extra* fields.


.. note::

    slaveId concept

    For each *native* and *action* FormElement a few custom SQL command can be fired (*sqlBefore, sqlAfter, sqlInsert,
    sqlUpdate, sqlDelete*). To assist the application developer the slaveId concept automatically checks if a

    * *sqlInsert* or *sqlUpdate* has to be fired
    * or even a *sqlDelete*.
    * automatically update the named column.

    For details see :ref:`slave-id`


Variables
^^^^^^^^^

* Form.parameter.fillStoreVar / FormElement.parameter.fillStoreVar

  An SQL statement will fill STORE_VARS. Such values can be used during form load and/or save.

Action
^^^^^^

* Action FE

  Via `FormElement.parameter.requiredList` an element can be enabled / disabled, depending of a user provided input
  in one of the specified required FEs.


.. _multi-form:

Multi Form
----------

`Multi Forms` are like a regular form with the difference that the shown FormElements are repeated for *each* selected
record (defined by `multiSql`).


+------------------+----------------------------------+------------------------------------------------+
| Name             |                                  |                                                |
+==================+==================================+================================================+
| multiSql         | {{!SELECT id, name FROM Person}} |  Query to select MulitForm records             |
+------------------+----------------------------------+------------------------------------------------+
| multiMgsNoRecord | Default: No data                 | Message shown if `multiSql` selects no records |
+------------------+----------------------------------+------------------------------------------------+

.. important::

    Multi Form do not use 'record-locking' at all.


The Form is shown as a HTML table.

* `multiSql`: Selects the records where the defined FormElements will work on each.

  * A uniq column `id` or `_id` (not shown) is mandatory and has to reference an existing record in table `primary table`.
  * Additional columns, defined in `multiSql`, will be shown on the form on the same line, before the FormElements.
  * Per row, the STORE_PARENT is filled with the current record of the primary table.

The following definition of *Simple* and *Advanced* is just for explanation, there is no *flag* or *mode* which
has to be set. Also the *Simple* and *Advanced* variant can be mixed in the same Multi Form.

Simple
^^^^^^

General:

* All FormElements uses columns of the primary table.
* QFQ handles all updates - that's why it's called *Simple*.
* It's not possible to create new records in simple mode, only existing records can be modified.

FormElement:

* The FormElement.name represents a column of the defined primary table.
* The existing values of such FormElements are automatically loaded.
* No further definition (`sqlInsert`, `sqlUpdate`, ...) is required.

Advanced
^^^^^^^^

To handle foreign records (insert/update/delete), use the :ref:`slave-id` concept.

Typically the `FormElement.name` is not a column of the primary table.

Example of how to edit the address of each person, saved in a separate address record::

    # Iterate over all person in the database. If there are 10 persons in the table person, than 10 rows are shown.
    Form.multiSql = {{!SELECT p.name, p.id AS _id FROM Person AS p}}

    # Only one input field in this example: street. It's saved in table *Address*.

    FE.name = myStreet
    FE.type = text

    # ``{{street:VE}}`` will be set via ``fillStoreVar``.
    FE.value = {{street:VE}}

    # Select the first address record owned by the current person.id = ``{{id:R}}`` ( or ``{{id:P}}``)
    # Task 1: Get the ``slaveId`` (Address.id).
    # Task 2: Get the value of column *street*.
    FE.parameter.fillStoreVar={{!SELECT a.id AS aId, a.street FROM Address AS a WHERE a.personId={{id:R}} ORDER BY a.id LIMIT 1}}

    # Set the slaveId. If there is no address, than {{aId:V}} doesn't exist and will be replaced by 0.
    FE.parameter.slaveId={{aId:V0}}

    # Update existing Address record.
    FE.parameter.sqlUpdate={{UPDATE Address SET street='{{myStreet:FE:allbut}}' WHERE id={{slaveId:V}} }}

    # Create new Address record.
    FE.parameter.sqlInsert={{INSERT INTO Address (personId, street) VALUES ({{id:R0}}, '{{myStreet:FE:allbut}}')  }}

    # In case FE *myStreet* is empty, remove the whole Address record.
    FE.parameter.sqlHonorFormElements = myStreet
    FE.parameter.sqlDelete = {{DELETE FROM Address WHERE id={{slaveId:V}} }}


.. _multiple-languages:

Multiple languages
------------------

QFQ Forms might be configured for up to 5 different languages. Per language there is one extra field in the *Form editor*.
Which field represents which language is configured in :ref:`configuration`.

* The Typo3 installation needs to be configured to handle different languages - this is independent of QFQ and not covered
  here. QFQ will use the Typo3 internal variable 'pageLanguage', which typically correlates to the URL parameter 'L' in the URL.
* In :ref:`configuration` the Typo3 language index (value of 'L') and a language label have to be configured for each language.
  Only than, the additional language fields in the *Form editor* will be shown.

Example
^^^^^^^

Assuming the Typo3 page has the

* default language, L=0
* English, L=1
* Spanish, L=2

Configuration in :ref:`configuration`: ::

    formLanguageAId = 1
    formLanguageALabel = English

    formLanguageBId = 2
    formLanguageBLabel = Spanish

The default language is not covered in :ref:`configuration`.

The *Form editor* now shows on the pill 'Basic' (Form and FormEditor) for both languages each an additional parameter
input field. Any input field in the *Form editor* can be redeclared in the corresponding language parameter field. Any
missing definition means 'take the default'. E.g.:

* Form: 'person'

  +--------------------+--------------------------+
  | Column             | Value                    |
  +====================+==========================+
  | title              | Eingabe Person           |
  +--------------------+--------------------------+
  | languageParameterA | title=Input Person       |
  +--------------------+--------------------------+
  | languageParameterB | title=Persona de entrada |
  +--------------------+--------------------------+

* FormElement 'firstname' in Form 'person':

  +--------------------+------------------------------------------------+
  | Column             | Value                                          |
  +====================+================================================+
  | title              | Vorname                                        |
  +--------------------+------------------------------------------------+
  | note               | Bitte alle Vornamen erfassen                   |
  +--------------------+------------------------------------------------+
  | languageParameterA | | title=Firstname                              |
  |                    | | note=Please give all firstnames              |
  +--------------------+------------------------------------------------+
  | languageParameterB | | title=Persona de entrada                     |
  |                    | | note=Por favor, introduzca todos los nombres |
  +--------------------+------------------------------------------------+


The following fields are possible:

* Form: *title, showButton, forwardMode, forwardPage, bsLabelColumns, bsInputColumns, bsNoteColumns, recordLockTimeoutSeconds*
* FormElement: *label, mode, modeSql, class, type, subrecordOption, encode, checkType, ord, size, maxLength,*
  *bsLabelColumns, bsInputColumns, bsNoteColumns,rowLabelInputNote, note, tooltip, placeholder, value, sql1, feGroup*

.. _dynamic-update:

Dynamic Update
--------------

The 'Dynamic Update' feature makes a form more interactive. If a user changes a *FormElement* who is tagged with
'dynamicUpdate', *all* elements who are tagged with 'dynamicUpdate', will be recalculated and rerendered.

The following fields will be recalculated during 'Dynamic Update'

* 'modeSql' - Possible values: 'show', 'required', 'readonly', 'hidden'
* 'label'
* 'value'
* 'note'
* 'parameter.*' - especially 'itemList'

To make a form dynamic:

* Mark all *FormElements* with `dynamic update`=`enabled`, which should **initiate** or **receive** updates.

See #3426 / Dynamic Update: Inputs loose the new content and shows the old value:

* On **all** `dynamic update` *FormElements* an explicit definition of `value`, including a sanitize class, is necessary
  (except the field is numeric). **A missing definition let's the content overwrite all the time with the old value**.
  A typical definition for `value` looks like (default store priority is: FSRVD)::

     {{<FormElement name>::alnumx}}

* Define the receiving *FormElements* in a way, that they will interpret the recent user change! The form variable of the
  specific sender *FormElement* `{{<sender element>:F:<sanitize>}}` should be part of one of the above fields to get an
  impact. E.g.:
  ::

    [receiving *FormElement*].parameter: itemList={{ SELECT IF({{carPriceRange:FE:alnumx}}='expensive','Ferrari,Tesla,Jaguar','General Motors,Honda,Seat,Fiat') }}

  Remember to specify a 'sanitize' class - a missing sanitize class means 'digit', every content, which is not numeric,
  violates the sanitize class and becomes therefore an empty string!

* If the dynamic update should work on existing and *new* records, it's important to guarantee that the query result is not empty!
  even if the primary record does not exist! E.g. use a `LEFT JOIN`. The following query is ok for `new` and `edit`. ::

    {{SELECT IF( IFNULL(adr.type,'') LIKE '%token%','show','hidden') FROM (SELECT 1) AS fake LEFT JOIN Address AS adr ON adr.type='{{type:FR0}}' LIMIT 1}}

Examples
^^^^^^^^

* Master FormElement 'music' is a radio/enum of 'classic', 'jazz', 'pop'.

Content of a select list
""""""""""""""""""""""""

* Slave FormElement 'interpret' is 'select'-list, depending of 'music'

::

   sql={{!SELECT name FROM Interpret WHERE music={{music:FE:alnumx}} ORDER BY name}}

Show / Hide a *FormElement*
"""""""""""""""""""""""""""

* Slave 'interpret' is displayed only for 'pop'. Field 'modeSql':

::

    {{SELECT IF( '{{music:FR:alnumx}}'='pop' ,'show', 'hidden' ) }}

.. _form-layout:

Form Layout
-----------

The forms will be rendered with Bootstrap CSS classes, based on the 12 column grid model (Bootstrap 3.x).
Generally a 3 column layout for *label* columns on the left side, an *input* field column in the middle and a *note*
column on the right side will be rendered.

The used default column (=bootstrap grid) width is *3,6,3* (col-md , col-lg) for *label, input, note*.

* The system wide defaults can be changed via :ref:`configuration`.
* Per *Form* settings can be done in the *Form* parameter field. They overwrite the system wide default.
* Per *FormElement* settings can be done in the *FormElement* parameter field. They overwrite the *Form* setting.

A column will be switched off (no wrapping via `<div class='col-md-?>`) by setting a `0` on the respective column.

.. _bs-custom-field-width:

Custom field width
^^^^^^^^^^^^^^^^^^

Per *FormElement* set `BS Label Columns`, `BS Input Columns` or `BS Note Columns` to customize an individual width.
If only a number is specified, it's used as `col-md-<number>`. Else the whole text string is used as CSS class, e.g.
`col-md-3 col-lg-2`.

Multiple Elements per row
^^^^^^^^^^^^^^^^^^^^^^^^^

Every row is by default wrapped in a `<div class='form-group'>` and every column is wrapped in a `<div class='col-md-?'>`.
To display multiple input elements in one row, the wrapping of the *FormElement* row and of the three columns can be
customized via the checkboxes of `Label / Input / Note`. Every open and every close tag can be individually switched on
or off.

E.g. to display 2 *FormElements* in a row with one label (first *FormElement*) and one note (last *FormElement*) we need
the following (switch off all non named):

* First *FormElement*

  * open row tag: `row` ,
  * open and close label tag: `label`, `/label`,
  * open and close field tag: `input`, `/input`,

* Second *FormElement*

  * open and close field tag: `input`, `/input`,
  * open and close note tag: `note`, `/note`,
  * close row tag: `/row` ,

.. _`copy-form`:

Copy Form
---------

Records (=master) and child records can be duplicated (=copied) by a regular `Form`, extended by `FormElements` of type 'paste'.
A 'copy form' works either in:

* 'copy and paste now' mode: the 'select' and 'paste' `Form` is merged in one form, only one master record is possible,
* 'copy now, paste later' mode: the 'select' `Form` selects master record(s), the 'paste' Form paste's them later.

Concept
^^^^^^^

A 'select action' (e.g. a `Form` or a button click) creates record(s) in the table `Clipboard`. Each clipboard record contains:

* the 'id(s)' of the record(s) to duplicate,
* the 'paste' form id (that `Form` defines, to which table the master records belongs to, as well as rules of how to
  duplicate any slave records) and where to copy the new records
* user identifier (QFQ cookie) to separate clipboard records of different users inside the Clipboard table.

The 'select action' is also responsible to delete old clipboard records of the current user, before new clipboard records are
created.

The 'paste form' iterates over all master record id(s) in the `Clipboard` table. For each master record id, all FormElements
of type `paste` are fired (incl. the creating of slave records).

E.g. if there is a basket with different items and you want to duplicate the whole basket including new items, create a
form with the following parameter

* Form

  * Name: `copyBasket`
  * Table: `Clipboard`
  * Show Button: only `close` and `save`

* FormElement 1: Record id of the source record.

  * Name: `idSrc`
  * Lable: `Source Form`
  * Class: `native`
  * Type: `select`
  * sql1: `{{! SELECT id, title FROM Basket }}`

* FormElement 2: New name of the copied record.

  * Name: `myNewName`
  * Class: `native`
  * Type: `text`

* FormElement 3: a) Check that there is no name conflict. b)Purge any old clipboard content of the current user.

  * Name: `clearClipboard`
  * Class: `action`
  * Type: `beforeSave`
  * Parameter:

    * `sqlValidate={{SELECT f.id FROM Form AS f WHERE f.name LIKE '{{myName:FE:alnumx}}' LIMIT 1}}`
    * `expectRecords = 0`
    * `messageFail = There is already a form with this name`
    * `sqlAfter={{DELETE FROM Clipboard WHERE cookie='{{cookieQfq:C0:alnumx}}' }}`

* FormElement 4: Update the clipboard source reference, with current {{cookieQfq:C}} identifier.

  * Name: `updateClipboardRecord`
  * Class: `action`
  * Type: `afterSave`
  * Parameter: `sqlAfter={{UPDATE Clipboard SET cookie='{{cookieQfq:C0:alnumx}}', formIdPaste={{formId:S0}} /* PasteForm */  WHERE id={{id:R}} LIMIT 1 }}`

* FormElement 5: Copy basket identifier.

  * Name: `basketId`
  * Class: `action`
  * Type: `paste`
  * sql1: `{{!SELECT {{id:P}} AS id,  '{{myNewName:FE:allbut}}' AS name}}`
  * Parameter: `recordDestinationTable=Basket`

* FormElement 6: Copy items of basket.

  * Name: `itemId`
  * Class: `action`
  * Type: `paste`
  * sql1: `{{!SELECT i.id AS id, {{basketId:P}} AS basketId FROM Item AS i WHERE i.basketId={{id:P}} }}`
  * Parameter: `recordDestinationTable=Item`


Table self referencing records
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Records might contain references to other records in the same table. E.g. native FormElements might assigned to a fieldSet,
templateGroup or pill, a fieldSet might assigned to other fieldsets or pills and so on. When duplicating a `Form` and the
corresponding `FormElements` all internal references needs to be updated as well.

On each FormElement.type=`paste` record, the column to be updated is defined via:

* parameter: translateIdColumn = <column name>

For the 'copyForm' this would be 'feIdContainer'.

The update of the records is started after all records have been copied (of the specific FormElement.type=`paste` record).

.. _delete-record:

Delete Record
-------------

Deleting record(s) via QFQ might be solved by either:

* using the `delete` button on a form on the top right corner.
* by letting :ref:`report` creating a special link (see below). The link contains the record id and:

  * a form name, or
  * a table name.

Deleting a record just by specifying a table name, will only delete the defined record (no slave records).

* By using a delete button via `report` or in a `subrecord` row, a ajax request is send.
* By using a delete button on the top right corner of the form, the form will be closed after deleting the record.

Example for report::

   SELECT p.name, CONCAT('U:form=person&r=', p.id) AS _paged FROM Person AS p
   SELECT p.name, CONCAT('U:table=Person&r=', p.id) AS _paged FROM Person AS p

To automatically delete slave records, use a form and create `beforeDelete` FormElement(s) on the form:

  * class: action
  * type: beforeDelete
  * parameter: sqlAfter={{DELETE FROM <slaveTable> WHERE <slaveTable>.<masteId>={{id:R}} }}

You might also check the form 'form' how the slave records 'FormElement' will be deleted.

.. _locking-record:

Locking Record / Form
---------------------