Manual.rst

      * *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}}`
    * Additional attributes might be given like `downloadButton = t:Download|o:check file`. Please check `download`_.

      * The following attributes are hard coded (can't be changed): `s|M:file|d|F`

  * fileSplit, fileDestinationSplit, tableNameSplit: see 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>` - determine the path/filename. 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
-------------

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:

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:

Parameter: slaveId
""""""""""""""""""

*FormElement.parameter*:

  * *slaveId* = `<id>`:

    * Auto fill: name the action `action`-*FormElement* equal to an existing column (table from the current form definition).
      *slaveId* will be automatically filled with the value of the named column.

      * If there is no such named column name, set *slaveId* = `0`.

    * Explicit definition: *slaveId* = `123` or *slaveId* = `{{SELECT id ...}}`

Note:

  * `{{slaveId:V}}` can be used in any query of the current *FormElement*.
  * If the `action`-*FormElement* name exist as a column in the master record: Update that column *automatically* with the
    recent slaveId
  * After an INSERT the `last_insert_id()` becomes the *{{slaveId:V}}*.
  * `fillStoreVar` is fired first, than `slaveId`.
  * If `slaveId` is known in `fillStoreVar`, set: `slaveId={{someId:V}}`.



Parameter: sqlBefore / sqlInsert / sqlUpdate / sqlDelete / sqlAfter
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

  * Save values of a form to different record(s), optionally on different table(s).
  * Typically useful on 'afterSave' - be careful when using it earlier, e.g. beforeLoad.

*FormElement.parameter*:

  * *requiredList* = `<fe.name[s]>` - List of `native`-*FormElement*: only if all of those elements are filled, the current
    `action`-*FormElement* will be processed.

  * *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!
    Always add a check, if values given, not to delete the record! *sqlHonorFormElements* helps to skip such checks.
  * *sqlAfter* = `{{<query>}}` - always fired (after *sqlInsert*, *sqlUpdate* or *sqlDelete*).
  * *sqlHonorFormElements* = `<fe.name[s]>` list of *FormElement* names (this parameter is optional).

    * If one of the named *FormElements* is not empty:

      * 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

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

* Send mail(s) will be processed after:

   * saving the record ,
   * processing all uploads,
   * processing `afterSave` action `FormElements`.


* *FormElement.value* = `<string>` - Body of the email. See also: `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 `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).

* 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 `REDIRECT_ALL_MAIL_TO`_.

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'): ::

  sendMailAttachmemt = 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 `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
----------

Parameter
^^^^^^^^^

* Table column `id`: QFQ expect that each table, which will be loaded in a form, contains an autoincrement column of name `id`.
  It's not necessary to create a FormElement `id` in a form - but it won't disturb.

* 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. This acts as 'hidden magic'.

  Example: A slave record (e.g. an address of a person) has to be assigned to a master record (a person). 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

  Such parameter, which the form expects to be in the SIP url, should be specified in Form.permitNew and/or Form.permitEdit.
  It's only a check for the webmaster, not to forgot a parameter in a SIP url.

* FormElement.type = subrecord

  Subrecord's will automatically create `new`, `edit` and `delete` links. To inject parameter in those automatically created
  links, use `FormElement.parameter.detail` . See subrecord-option_.


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

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 |
+------------------+----------------------------------+------------------------------------------------+

* 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 reflect an existing record id in table `primary table`.
  * Additional columns, defined in `multiSql`, will be shown on the form in the same line, before the FormElements.


Simple
^^^^^^

General:

* It's not possible to create new records in simple mode, only existing records can be modified.

Form:

* Per row, the STORE_RECORD is filled with the whole record of the primary table, referenced
  by `multiSql.id`.

FormElement:

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

Advanced
^^^^^^^^

To handle foreign records (insert/update/delete), use the slaveId_ concept.

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


.. _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 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 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 configuration_: ::

    formLanguageAId = 1
    formLanguageALabel = English

    formLanguageBId = 2
    formLanguageBLabel = Spanish

The default language is not covered in 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 `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 `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
---------------------

Support for record locking is given with mode:

* *exclusive*: user can't force a write.

  * Including a timeout (default 15 mins recordLockTimeoutSeconds in configuration_) for maximum lock time.

* *advisory*: user is only warned, but allowed to overwrite.
* *none*: no bookkeeping about locks.

For 'new' records (r=0) there is no locking at all.

The record locking protection is based on the `tablename` and the `record id`. Different `Forms`, with the same primary table,
will be protected by record locking. On the other side, action-`FormElements` updating non primary table records are not
protected by 'record locking': the QFQ record locking is *NOT 100%*.

The 'record locking' mode will be specified per `Form`. If there are multiple Forms with different modes, and there is
already a lock for a `tablename` / `record id` pair, the most restrictive will be applied.


Best practice
-------------

Custom default value only for 'new records'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Method 1
""""""""

On `Form.parameter` define a `fillStoreVar` query with a column name equal to a form field. That's all.

Example: ::

  FormElement.name = technicalContact
  Form.parameter.fillStoreVar = {{! SELECT CONCAT(p.firstName, ' ', p.name) AS technicalContact FROM Person AS p WHERE p.account='{{feUser:T}}' }}

What we use here is the default STORE prio FSRVD. If the form loads with r=0, 'F', 'S' and 'R' are empty. 'V' is filled.
If r>0, than 'F' and 'S' are empty and 'R' is filled.

Method 2
""""""""

In the specific `FormElement` set `value={{columnName:RSE}}`. The link to the form should be rendered with
'"...&columnName=<data>&..." AS _page'. The trick is that the STORE_RECORD is empty for new records, and therefore the
corresponding value from STORE_SIP will be returned. Existing records will use the already saved value.

Central configured values
^^^^^^^^^^^^^^^^^^^^^^^^^

Any variable in configuration_ can be used by *{{<varname>:Y}}* in form or report statements.

E.g.

  TECHNICAL_CONTACT = jane.doe@example.net

Could be used in an *FormElement.type* = sendmail with *parameter*  setting *sendMailFrom={{TECHNICAL_CONTACT:Y}}*.

Debug Report
^^^^^^^^^^^^

Writing "report's" in the nested notation or long queries broken over several lines, might not interpreted as wished.
Best for debugging is to specify in the tt-content record::

  debugShowBodyText = 1

Note: Debug information is only display if it's enabled in  configuration_ by

 * *showDebugInfo: yes* or
 * *showDebugInfo: auto* and logged in in the same Browser as a Typo3 backend user.

More detailed error messages
^^^^^^^^^^^^^^^^^^^^^^^^^^^^

If *showDebugInfo* is enabled, a full stacktrace and variable contents are displayed in case of an error.

Form search
^^^^^^^^^^^

QFQ content record::

  # Creates a small form that redirects back to this page
  10 {
    sql = SELECT '_'
    head = <form action='#' method='get'><input type='hidden' name='id' value='{{pageAlias:T}}'>Search: <input type='text' name='search' value='{{search:CE:all}}'><input type='submit' value='Submit'></form>
  }

  # SQL statement will find and list all the relevant forms - be careful not to open a cross site scripting door: the parameter 'search' needs to be sanitized.
  20 {
    sql = SELECT CONCAT('p:{{pageAlias:T}}&form=form&r=', f.id) AS _pagee, f.id, f.name, f.title
              FROM Form AS f
              WHERE f.name LIKE  '%{{search:CE:alnumx}}%'
    head = <table class='table'>
    tail = </table>
    rbeg = <tr>
    rend = </tr>
    fbeg = <td>
    fend = </td>
  }


Form: compute next free 'ord' automatically
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Requirement: new records should automatically get the highest number plus 10 for their 'ord' value. Existing records
should not be altered.

Version 1
"""""""""

Compute the next 'ord' in advance in the subrecord field of the primary form. Submit that value to the new record
via SIP parameter to the secondary form.

On the secondary form: for 'new' records choose the computed value, for existing records leave the value
unchanged.

* Master form, `subrecord` *FormElement*, field `parameter`: set ::

    detail=id:formId,{{SELECT '&', IFNULL(fe.ord,0)+10 FROM Form AS f LEFT JOIN *FormElement* AS fe ON fe.formId=f.id WHERE
    f.id={{r:S0}} ORDER BY fe.ord DESC LIMIT 1}}:ord


* Slave form, `ord` *FormElement*, field `value`: set

  ::

   `{{ord:RS0}}`.

Version 2
"""""""""

Compute the next 'ord' as default value direct inside the secondary form. No change is needed for the primary form.

* Secondary form, `ord` *FormElement*, field `value`: set `{{SELECT IF({{ord:R0}}=0,  MAX(IFNULL(fe.ord,0))+10,{{ord:R0}})  FROM (SELECT 1) AS a LEFT JOIN FormElement AS fe ON fe.formId={{formId:S0}} GROUP BY fe.formId}}`.

Form: Person Wizard - firstname, city
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Requirement: A form that displays the column 'firstname' from table 'Person' and 'city' from table 'Address'. If the
records not exist, the form should create it.

Form primary table: Person

Form slave table: Address

Relation: `Person.id = Address.personId`

* Form: wizard

  * Name: wizard
  * Title: Person Wizard
  * Table: Person
  * Render: bootstrap

* *FormElement*: firstname

  * Class: **native**
  * Type: **text**
  * Name: firstname
  * Label: Firstname

* *FormElement*: email, text, 20