Manual.rst

| chmodFile, chmodDir,   |        |                                                                                                          |
| slaveId,               |        |                                                                                                          |
| sqlBefore,             |        |                                                                                                          |
| sqlInsert,             |        |                                                                                                          |
| sqlUpdate,             |        |                                                                                                          |
| sqlDelete,             |        |                                                                                                          |
| sqlAfter,              |        |                                                                                                          |
| importToTable,         |        |                                                                                                          |
| importToColumns,       |        |                                                                                                          |
| importRegion,          |        |                                                                                                          |
| importMode,            |        |                                                                                                          |
| importType,            |        |                                                                                                          |
| importNamedSheetsOnly, |        |                                                                                                          |
| importSetReadDataOnly, |        |                                                                                                          |
| importListSheetNames,  |        |                                                                                                          |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| checkBoxMode           | string | See `input-checkbox`_, `input-radio`_, `input-select`_                                                   |
| checked                |        |                                                                                                          |
| unchecked              |        |                                                                                                          |
| label2                 |        |                                                                                                          |
| itemList               |        |                                                                                                          |
| emptyHide              |        |                                                                                                          |
| emptyItemAtStart       |        |                                                                                                          |
| emptyItemAtEnd         |        |                                                                                                          |
| buttonClass            |        |                                                                                                          |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| dateFormat             | string | yyyy-mm-dd / dd.mm.yyyy                                                                                  |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| data-pattern-error     | string | Pattern violation: Text for error message                                                                |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| data-required-error    | string | Required violation: Text for error message                                                               |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| data-match-error       | string | Match violation: Text for error message                                                                  |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| data-error             | string | Violation of 'check-type': Text for error message                                                        |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| decimalFormat          | string | [precision,scale] Limits and formats input to a decimal number with the specified precision and scale.   |
|                        |        | If no precision and scale are specified, the decimal format is pulled from the table definition.         |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| htmlAfter              | string | HTML Code wrapped after the complete *FormElement*                                                       |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| htmlBefore             | string | HTML Code wrapped before the complete *FormElement*                                                      |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| extraButtonLock        | none   | [0|1] Show a 'lock' on the right side of the input element. See `extraButtonLock`_                       |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| extraButtonPassword    | none   | [0|1] Show an 'eye' on the right side of the input element. See `extraButtonPassword`_                   |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| extraButtonInfo        | string | Text. Show an 'i' on the right side of the input element. See `extraButtonInfo`_                         |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| extraButtonInfoClass   | string | By default empty. Specify any class to be assigned to wrap extraButtonInfo                               |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| editor-plugins,        | string | See `input-editor`_                                                                                      |
| editor-toolbar,        |        |                                                                                                          |
| editor-statusbar,      |        |                                                                                                          |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| fileButtonText         | string | Overwrite default 'Choose File'                                                                          |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| fillStoreVar           | string | Fill the STORE_VAR with custom values. See `STORE_VARS`_.                                                |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| form,                  | string | See `subrecord-option`_                                                                                  |
| page,                  |        |                                                                                                          |
| title,                 |        |                                                                                                          |
| extraDeleteForm,       |        |                                                                                                          |
| detail,                |        |                                                                                                          |
| subrecordTableClass,   |        |                                                                                                          |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| min                    | s/d/n  | Minimum and/or maximum allowed values for input field. Can be used for numbers, dates, or strings.       |
+------------------------+--------+                                                                                                          |
| max                    | s/d/n  | *Always use the international format 'yyyy-mm-dd[ hh:mm[:ss]]*                                           |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| processReadOnly        | [n]    | [0|1] By default FE's with type='readonly' are not processed during 'save'.                              |
|                        |        | This option forces to process them during 'save' as well.                                                |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| retype,                | string | See `input-text`_                                                                                        |
| retypeLabel,           |        |                                                                                                          |
| retypeNote,            |        |                                                                                                          |
| characterCountWrap,    |        |                                                                                                          |
| hideZero,              |        |                                                                                                          |
| emptyMeansNull,        |        |                                                                                                          |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| showSeconds            | string | 0|1 - Shows the seconds on form load. Default: 0                                                         |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| showZero               | string | 0|1 - Empty timestamp: '0'(default) - nothing shown, '1' - the string '0000-00-00 00:00:00' is displayed |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| timeIsOptional         | string | 0|1 - Used for datetime input. 0 (default): Time is required - 1: Entering a time is optional            |
|                        |        | (defaults to 00:00:00 if none entered).                                                                  |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| typeAheadLimit,        | string | See `input-typeahead`_                                                                                   |
| typeAheadMinLength,    |        |                                                                                                          |
| typeAheadSql,          |        |                                                                                                          |
| typeAheadSqlPrefetch,  |        |                                                                                                          |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| wrapRow                | string | If specified, skip default wrapping (`<div class='col-md-?'>`). Instead the given string is used.        |
+------------------------+--------+                                                                                                          |
| wrapLabel              | string |                                                                                                          |
+------------------------+--------+                                                                                                          |
| wrapInput              | string |                                                                                                          |
+------------------------+--------+                                                                                                          |
| wrapNote               | string |                                                                                                          |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| trim                   | string | By default, whitespace is trimmed. To disable, use 'trim=none'. You can also specify custom trim         |
|                        |        | characters: 'trim=\\ ' only trims spaces.                                                                |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| sqlValidate            | string | See `sqlValidate`_                                                                                       |
+------------------------+--------+                                                                                                          |
| expectRecords          | int    |                                                                                                          |
+------------------------+--------+                                                                                                          |
| messageFail            | string |                                                                                                          |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| dataReference          | string | Optional. See `applicationTest`_                                                                         |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+
| requiredPosition       | int    | See requiredPosition_ .                                                                                  |
+------------------------+--------+----------------------------------------------------------------------------------------------------------+


 * `s/d/n`: string or date or number.

Native *FormElements*
"""""""""""""""""""""

* Like 'input', 'checkbox', ...

.. _`input-option-autofocus`:

autofocus
;;;;;;;;;

The first *FormElement* with this attribute will get the focus after form load. If there is no such attribute
given to any *FormElement*, the attribute will be automatically assigned to the first editable *FormElement*.

To disable 'autofocus' on a form, set 'autofocus=0' on the first editable *FormElement*.

Note: If there are multiple pills defined on a form, only the first pill will be set with 'autofocus'.

.. _`extraButtonLock`:

extraButtonLock
;;;;;;;;;;;;;;;

* The user has to click on the lock, before it's possible to change the value. This will protect data against unwanted modification.
* After Form load, the value is shown, but not editable.
* Shows a 'lock' on the right side of an input element of type `text`, `date`, `time` or `datetime`.
* This option is not available for FormElements with `mode=readonly`.
* There is no value needed for this parameter.

.. _`extraButtonPassword`:

extraButtonPassword
;;;;;;;;;;;;;;;;;;;

* The user has to click on the eye (unhide) to see the value.
* After Form load, the data is hided by asteriks.
* Shows an 'eye' on the right side of an input element of type `text`, `date`, `time` or `datetime`.
* There is no value needed for this parameter.

.. _`extraButtonInfo`:

extraButtonInfo
;;;;;;;;;;;;;;;

* After Form load,  the `info` button/icon is shown but the information message is hidden.
* The user has to click on the `info` button/icon to see an additional message.
* The value of this parameter is the text shown to the user.
* Shows an `info` button/icon, depending of `extraButtonInfoPosition` in configuration_

  * `auto`, depending on `FormElement` type:

    * on the right side of an input element for type `text`, `date`, `time` or `datetime`,
    * below the FormElement for all other types.

  * `below`: below the FormElement for all types.

* For `FormElement` with mode `below`, a `span` element with the given class in `extraButtonInfoClass` (FE, F, configuration_)
  will be applied. E.g. this might be `pull-right` to align the `info` button/icon on the right side below the input element.


.. _`requiredPosition`:

Required Position
^^^^^^^^^^^^^^^^^

By default, input elements with `Mode=required` will be displayed with a 'red asterix' right beside the label. The position
of the 'red asterix' can be choosen via the `parameter` field::

   requiredPosition = label-left|label-right|input-left|input-right|note-left|note-right

The default is 'label-right'.

The definition can be set per Form (=affects all FormElements) or per FormElement.

.. _`input-checkbox`:

Type: checkbox
^^^^^^^^^^^^^^

Checkboxes can be rendered in mode:

* *single*:

  * One column in a table corresponds to one checkbox.
  * The value for statuses *checked* and *unchecked* are free to choose.
  * This mode is selected, if a) *checkBoxMode* = single, or b) *checkBoxMode* is missing **and** the number of fields of the column definition is <3.
  * *FormElement.parameter*:

    * *checkBoxMode* = single (optional)
    * *checked* = <value> (optional, the value which represents 'checked')

      * If *checked* is empty or missing: If *type* = 'enum' or 'set', get first item of the definition. If *type* = string, get default.

    * *unchecked* = <value> (optional, the value which represents 'unchecked')

      * If *unchecked* is empty or missing: If *type* = 'enum' or 'set', get second item of checked. If *type* = 'string', get ''.

    * *label2* = <value>       (Text right beside checkbox) (optional)


* *multi*:

  * One column in a table represents multiple checkboxes. This is typically useful for the column type *set*.
  * The value for status *checked* are free to choose, the value for status *unchecked* is always the empty string.
  * Each field key (or the corresponding value from the key/value pair) will be rendered right beside the checkbox.
  * *FormElement.parameter*

    * *checkBoxMode* = multi
    * *itemList* - E.g.:

      * ``itemList=red,blue,orange``
      * ``itemList=1:red,2:blue,3:orange``
      * If ':' or ',' are part of key or value, it needs to escaped by \\ .
        E.g.: `itemList=1:red\\: (with colon),2:blue\\, (with comma),3:orange``

  * *FormElement.sql1* = ``{{!SELECT id, value FROM someTable}}``
  * *FormElement.maxlength* - vertical or horizontal alignment:

     * Value: '', 0, 1 - The check boxes will be aligned vertical.
     * Value: >1 - The  check boxes will be aligned horizontal, with a linebreak every 'value' elements.

* *FormElement.parameter*:

  * *emptyHide*: Existence of this item hides an entry with an empty string. This is useful for e.g. Enums, which have an empty
    entry, but the empty value should not be selectable.
  * *emptyItemAtStart*: Existence of this item inserts an empty entry at the beginning of the selectlist.
  * *emptyItemAtEnd*: Existence of this item inserts an empty entry at the end of the selectlist.
  * *buttonClass*: Instead of the plain HTML  checkbox fields, Bootstrap
    `buttons <http://getbootstrap.com/docs/3.4/javascript/#buttons-checkbox-radio>`_. are rendered as `checkbox` elements. Use
    one of the following `classes <http://getbootstrap.com/docs/3.4/css/#buttons-options>`_:

    * `btn-default` (default, grey),
    * `btn-primary` (blue),
    * `btn-success` (green),
    * `btn-info` (light blue),
    * `btn-warning` (orange),
    * `btn-danger` (red).

    With a given *buttonClass*, all buttons (=radios) are rendered horizontal. A value in *FormElement.maxlength* has no effect.

* *No preselection*:

  * If a form is in 'new' mode and if there is a default value configured on a table column, such a value is shown by default.
    There might be situations, where the user should be forced to select a value (e.g. specifying the gender). An unwanted
    default value can be suppressed by specifying an explicit definition on the FormElement field `value`::

      {{<columnName>:RZ}}

    For existing records the shown value is as expected the value of the record. For new records, it's the value `0`,
    which is typically not one of the ENUM / SET values and therefore nothing is selected.


Type: date
^^^^^^^^^^

* Range datetime: '1000-01-01' to '9999-12-31' or '0000-00-00'. (http://dev.mysql.com/doc/refman/5.5/en/datetime.html)
* Optional:

  * *FormElement.parameter.dateFormat*: yyyy-mm-dd | dd.mm.yyyy

Type: datetime
^^^^^^^^^^^^^^

* Range datetime: '1000-01-01 00:00:00' to '9999-12-31 23:59:59' or '0000-00-00 00:00:00'. (http://dev.mysql.com/doc/refman/5.5/en/datetime.html)
* Optional:

  * *FormElement.parameter*:

    * *dateFormat* = yyyy-mm-dd | dd.mm.yyyy
    * *showSeconds* = 0|1 - shows the seconds. Independent if the user specifies seconds, they are displayed '1' or not '0'.
    * *showZero* = 0|1 - For an empty timestamp, With '0' nothing is displayed. With '1' the string '0000-00-00 00:00:00' is displayed.

Type: extra
^^^^^^^^^^^

* The element behaves like, and can be used as, a HTML hidden input element - with the difference & advantage, that the
  element never leaves the server and therefore can't be manipulated by a user.
* The following names are reserved and can't be used to name 'extra' FormElements: 'id', 'type', 'L'.
* The element is not transferred to the the browser.
* The element can be used to define / pre calculate values for a column, which do not already exist as a native *FormElement*.
* The element is build / computed on form load and saved alongside with the SIP parameter of the current form.
* The element is not recalculated during save - it's stored during 'Form Load' inside the current form SIP handle.
* Access the value without specifying any store (default store priority is sufficient).

.. _`input-text`:

Type: text
^^^^^^^^^^

* General input for text and number.
* *FormElement.size* = [<width>[,<min height>[,<max height>]]]

  * The 'size' parameter might have 0-3 parameter.
  * If 0-2 parameter are given, the height is static.
  * If 3 parameter are given, the `auto height` mode is activated and the height is dynamically calculated, with respect to
    given min/max height.
  * Width measured in characters, height measured in lines.
  * If only the width is given, the height becomes automatically '1'.
  * In general, the *visible* width of an input element is defined by the Bootstrap column width (and *not* the width given
    here). The width value here only has an impact on calculating the final height in the `auto height` mode.
  * The dynamic Bootstrap width of the shown textarea element makes it hard to calculate the real needed height. The
    implementation is only a 'best guess'.
  * `<width>,<min height>`: with a `<min height>` > 1 the form element is of type 'textarea' (else 'input').
  * `<width>,<min height>,<max height>`: height=`counting lines of current value`.
    If a line is longer than `<width>`, the height of the line counts as `length(<line>) / <width> + 1` lines. The height
    is never less than `<min height>` or greater than `<max height>`.



* *FormElement.parameter*:

  * *retype* = 1 (optional): Current input element will be rendered twice. The form can only submitted if both elements are equal.

    * *retypeLabel* = <text> (optional): The label of the second element.
    * *retypeNote* = <text> (optional): The note of the second element.

  * *characterCountWrap* = <span class="qfq-cc-style">Count: | </span> (optional).
    Displays a character counter below the input/textarea element.
  * Also check the  fe-parameter-attributes_ *data-...-error* to customize error messages shown by the validator.
  * *hideZero* = 0|1 (optional): `with hideZero=1` a '0' in the value will be replaced by an empty string.
  * *emptyMeansNull* = [0|1] (optional): with `emptyMeansNull` or `emptyMeansNull=1` a NULL value will be written if
    the value is an empty string
  * *inputType* = number (optional). Typically the HTML tag 'type' will be 'text', 'textarea' or 'number' (detected automatically).
    If necessary, the HTML tag 'type' might be forced to a specific given value.
  * *step* = Step size of the up/down buttons which increase/decrease the number of in the input field. Optional.
    Default 1. Only useful with `inputType=number` (defined explicit via `inputType` or detected automatically).

.. _`input-typeahead`:

Type Ahead
""""""""""

Activating `typeahead` functionality offers an instant lookup of data and displaying them to the user, while the user is
typing, a drop-down box offers the results. As datasource the regular SQL connection or a LDAP query can be used.
With every keystroke (starting from the *typeAheadMinLength* characters), the already typed value will be transmitted to
the server, the lookup will be performed and the result, upto *typeAheadLimit* entries, are displayed as a drop-down box.

* *FormElement.parameter*:

  * *typeAheadLimit* = <number>. Max numbers of result records to be shown. Default is 20.
  * *typeAheadMinLength* = <number>. Minimum length to type before the first lookup starts. Default is 2.

Depending of the `typeahead` setup, the given FormElement will contain the displayed `value` or `id` (if an id/value dict is
configured).

Configuration via Form / FormElement
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

All of the `typeAhead*` (except `typeAheadLdap`) and `ldap*` parameter can be specified either in
*Form.parameter* or in *FormElement.parameter*.

SQL
;;;

* *FormElement.parameter*:

  * *typeAheadSql* = `SELECT ... AS 'id', ... AS 'value' WHERE name LIKE ? OR firstName LIKE ? LIMIT 100`

    * If there is only one column in the SELECT statement, that one will be used and there is no dict (key/value pair).
    * If there is no column `id` or no column `value`, then the first column becomes `id` and the second column becomes `value`.
    * The query will be fired as a 'prepared statement'.
    * The value, typed by the user, will be replaced on all places where a `?` appears.
    * All `?` will be automatically surrounded by '%'. Therefore wildcard search is implemented: `... LIKE '%<?>%' ...`

  * *typeAheadSqlPrefetch* = `SELECT firstName, ' ', lastName FROM person WHERE id = ?`

    * If the query returns several results, only the first one is returned and displayed.
    * If the query selects multiple columns, the columns are concatenated.

LDAP
;;;;

See :ref:`LDAP_Typeahead`

.. _`input-editor`:

Type: editor
^^^^^^^^^^^^

* TinyMCE (https://www.tinymce.com, community edition) is used as the QFQ Rich Text Editor.
* The content will be saved as HTML inside the database.
* All configuration and plugins will be configured via the 'parameter' field. Just prepend the word 'editor-' in front
  of each TinyMCE keyword. Check possible options under:

  * https://www.tinymce.com/docs/configure/,
  * https://www.tinymce.com/docs/plugins/,
  * https://www.tinymce.com/docs/advanced/editor-control-identifiers/#toolbarcontrols

* Bars:

  * Top: *menubar* - by default hidden.
  * Top: *toolbar* - by default visible.
  * Bottom: *statusbar* - by default hidden, exception: *min_height* and *max_height* are given via size parameter.


* The default setting in *FormElement.parameter* is::

    editor-plugins=code link lists searchreplace table textcolor textpattern visualchars
    editor-toolbar=code searchreplace undo redo | styleselect link table | fontselect fontsizeselect | bullist numlist outdent indent | forecolor backcolor bold italic editor-menubar=false
    editor-statusbar=false

* To deactivate the surrouding `<p>` tag, configure in *FormElement.parameter*::

     editor-forced_root_block = false

  This might have impacts on the editor. See https://www.tinymce.com/docs/configure/content-filtering/#forced_root_block

* Set 'extended_valid_elements' to enable HTML tags and their attributes. Example: ::

    editor-extended_valid_elements = span[class|style]

* Set 'editor-content_css' to use a custom CSS to style elements inside the editor. Example: ::

    editor-content_css = fileadmin/custom.css

* *FormElement.size* = <min_height>,<max_height>: in pixels, including top and bottom bars. E.g.: 300,600


Type: annotate
^^^^^^^^^^^^^^

Annotate image or text. Typically the image or text has been uploaded during a previous step. The annotation will be
saved in *FormElement.name* column of the current record. The uploaded file itself will not be modified. The annotations
can be shown in edit (and might be  modified) or in readonly mode.

Two modes are available:

grafic
    A simple grafic editor to paint on top of the image (best by a tablet with pen or grafic tablet). The uploaded image
    is shown in the background. All drawings are saved as a JSON fabric.js data string. Supported file types:
    **png, svg**. PDF files can be easily divided into per page SVG files during upload - see `split-pdf-upload`_

text
    Per code line, annotation(s) can be added. Different users can add individual annotations. A user can only edit the
    own annotations. The annotations are saved as QFQ internal JSON string.


.. note::

    Drawing with fabric.js might produce a lot data. Take care the column type/size is big enough (>=64kB).


Grafic
""""""

An image, specified by ``FormElement.parameter.imageSource={{pathFileName}}``, will be displayed in the background. On
form load, both, the image and an optional already given grafical annotations, will be displayed. The image is SIP
protected and will be loaded on demand.

**Form.parameter**

+-------------------+-----------------------+----------------------------------------------------------------------------------+
| Attribute         | Value                 | Description                                                                      |
+===================+=======================+==================================================================================+
| annotateType      | grafic                | *grafic|text*. Default is *grafic*. Select mode.                                 |
+-------------------+-----------------------+----------------------------------------------------------------------------------+
| imageSource       | <path filename>       | Background image. E.g. `fileadmin/images/scan.png`                               |
+-------------------+-----------------------+----------------------------------------------------------------------------------+
| defaultPenColor   | <rgb hex value>       | Pen default color, after loading the fabric element. Default is '0000FF' (blue). |
+-------------------+----------------------------------------------------------------------------------------------------------+

.. note::

    By using the the `FormElement` `annotate`, the JS code `fabric.min.js` and `qfq.fabric.min.js` has to be included. See setup-css-js_.

Code
""""

**Form.parameter**

+--------------------+-----------------------+----------------------------------------------------------------------------------+
| Attribute          | Value                 | Description                                                                      |
+====================+=======================+==================================================================================+
| annotateType       | text                  | *grafic|text*. Default is *grafic*. Select mode.                                 |
+--------------------+-----------------------+----------------------------------------------------------------------------------+
| textSource         | <path filename>       | Text file to annotate.                                                           |
+--------------------+-----------------------+----------------------------------------------------------------------------------+
| annotateUserName   | <john doe>            | Will be shown at annotation line.                                                |
+--------------------+-----------------------+----------------------------------------------------------------------------------+
| annotateUserUid    | <123>                 | Will be shown at annotation line.                                                |
+--------------------+-----------------------+----------------------------------------------------------------------------------+
| annotateUserAvatar | <https://gravatar...> | Will be shown at annotation line.                                                |
+--------------------+-----------------------+----------------------------------------------------------------------------------+
| highlight          | auto                  | off,auto,javascript,qfq,python,matlab                                            |
+--------------------+-----------------------+----------------------------------------------------------------------------------+


Type: imageCut
^^^^^^^^^^^^^^

Uploaded images can be cut or rotate via QFQ (via fabric.js). The modified image is saved under the given pathFileName.

* The 'value' of the `FormElement` has to be a valid PathFileName to an image.
* Valid image file formats are SVG, PNG, JPG, GIF.
* Invalid or missing filenames results to an empty 'imageCut' element.

* *FormElement.parameter*:

  * *resizeWidth* = <empty>|[width in pixel] - the final width of the modified image. If empty (or not given), no change.
  * *keepOriginal* = <empty>|[string] - By default: '.save'. If empty (no string given), don't keep the original. If an
    extension is given and if there is not already a <pathFileName><.extension>, than the original file is to copied to it.


Type: note
^^^^^^^^^^

An FormElement without any 'input' functionality -just to show some text. Use the typical fields 'label', 'value' and
'note' to be displayed in the corresponding three standard columns.

Type: password
^^^^^^^^^^^^^^

* Like a `text` element, but every character is shown as an asterisk.

.. _`input-radio`:

Type: radio
^^^^^^^^^^^

* Radio Buttons will be built from one of three sources:

  1. 'sql1': E.g. *{{!SELECT type AS label FROM car }}* or *{{!SELECT type AS label, typeNr AS id FROM car}}* or *{{!SHOW tables}}*.

    * Resultset format 'named': column 'label' and optional a column 'id'.
    * Resultset format 'index':

      * One column in resultset >> first column represents *label*
      * Two or more columns in resultset >> first column represents *id* and second column represents *label*.

  2. *FormElement.parameter*:

    * *itemList* = `<attribute>` E.g.: *itemList=red,blue,orange* or *itemList=1:red,2:blue,3:orange*
    * If ':' or ',' are part of key or value, it needs to escaped by \\ . E.g.: `itemList=1:red\\: (with colon),2:blue\\, (with comma),3:orange`

  3. Definition of the *enum* or *set* field (only labels, ids are not possible).


* *FormElement.maxlength* = `<value>`

   * Applies only to 'plain' radio elements (not the Bootstrap 'buttonClass' from below)
   * *vertical* or *horizontal* alignment:

     * `<value>`: '', 0, 1 - The radios will be aligned *vertical*.
     * `<value>`: >1 - The readios will be aligned *horizontal*, with a linebreak every 'value' elements.


* *FormElement.parameter*:

  * *emptyHide*: Existence of this item hides an entry with an empty string. This is useful for e.g. Enums, which have an empty
    entry, but the empty value should not be selectable.
  * *emptyItemAtStart*: Existence of this item inserts an empty entry at the beginning of the selectlist.
  * *emptyItemAtEnd*: Existence of this item inserts an empty entry at the end of the selectlist.
  * *buttonClass* = <class> - Instead of the plain radio fields, Bootstrap
    `buttons <http://getbootstrap.com/docs/3.4/javascript/#buttons-checkbox-radio>`_. are rendered as `radio` elements. Use
    one of the following `classes <http://getbootstrap.com/docs/3.4/css/#buttons-options>`_:

    * `btn-default` (default, grey),
    * `btn-primary` (blue),
    * `btn-success` (green),
    * `btn-info` (light blue),
    * `btn-warning` (orange),
    * `btn-danger` (red).

    With a given *buttonClass*, all buttons (=radios) are rendered horizontal. A value in *FormElement.maxlength* has no effect.

* *No preselection*:

  * If there is a default configured on a table column, such a value is selected by default. If the user should actively
    choose an option, the 'preselection' can be omitted by specifying an explicit definition on the FormElement field `value`::

      {{<columnName>:RZ}}

    For existing records the shown value is as expected the value of the record. For new records, it's the value `0`,
    which is typically not one of the ENUM values and therefore nothing is selected.

.. _`input-select`:

Type: select
^^^^^^^^^^^^

* Select lists will be built from one of three sources:

  * *FormElement.sql1* = `{{!<SQL Query}}`

    * E.g. *{{!SELECT type AS label FROM car }}* or *{{!SELECT type AS label, typeNr AS id FROM car}}* or *{{!SHOW tables}}*.

    * Resultset format 'named': column 'label' and optional a column 'id'.
    * Resultset format 'index':

      * One column in resultset >> first column represents *label*
      * Two or more columns in resultset >> first column represents *id* and second column represents *label*.

  * *FormElement.parameter*:

    * *itemList* = `<attribute>` - E.g.: *itemList=red,blue,orange* or *itemList=1:red,2:blue:3:orange*
    * If ':' or ',' are part of key or value, it needs to escaped by \\ . E.g.: `itemList=1:red\\: (with colon),2:blue\\, (with comma),3:orange`

  * Definition of the *enum* or *set* field (only labels, ids are not possible).

* *FormElement.size* = `<value>`

  * `<value>`: <empty>|0|1: drop-down list.
  * `<value>`: >1: Select field with *size* rows height. Multiple selection of items is possible.

* *FormElement.parameter*:

  * *emptyItemAtStart*: Existence of this item inserts an empty entry at the beginning of the selectlist.
  * *emptyItemAtEnd*: Existence of this item inserts an empty entry at the end of the selectlist.
  * *emptyHide*: Existence of this item hides the empty entry. This is useful for e.g. Enums, which have an empty
    entry and the empty value should not be an option to be selected.
  * *datalist*: Similar to 'typeAhead'. Enables the user to select a predefined option (sql1, itemList) or supply any
     free text. Attention: Safari (and some other) browsers do not support this fully - https://caniuse.com/#search=datalist.

.. _`subrecord-option`:

Type: subrecord
^^^^^^^^^^^^^^^

The *FormElement* type 'subrecord' renders a list of records (so called secondary records), typically to show, edit, delete
or add new records. The list is defined as a SQL query. The number of records shown is not limited. These *FormElement*
will be rendered inside the form as a HTML table.

* *mode / modeSql* = `<type/value>`

  * *show / required*: the regular mode to show the subrecords
  * *readonly*: New / Edit / Delete Buttons are disabled
  * *hidden*: The FormElement is rendered, but hidden with `display='none'`.

* *dynamicUpdate* - not supported at the moment.

* *sql1* = `{{!SQL Query}}`

   * SQL query to select records. E.g.::

      {{!SELECT addr.id AS id, CONCAT(addr.street, addr.streetnumber) AS a, addr.city AS b, addr.zip AS c FROM Address AS addr}}

  * Notice the **exclamation mark** after '{{' - this is necessary to return an array of elements, instead of a single string.
  * Exactly one column **'id'** has to exist; it specifies the primary record for the target form.
    In case the id should not be visible to the user, it has to be named **'_id'**.
  * Column name: *[title=]<title>[|[maxLength=]<number>][|nostrip][|icon][|link][|url][|mailto][|_rowClass][|_rowTooltip]*

    * If the keyword is used, all parameter are position independent.
    * Parameter are separated by '|'.
    * *[title=]<text>*: Title of the column. The keyword 'title=' is optional. Columns with a title starting with '_' won't be rendered.
    * *[maxLength=]<number>*: Max. number of characters displayed per cell. The keyword 'maxLength=' is optional. Default
       maxLength '20'. A value of '0' means no limit. This setting also affects the title of the column.
    * *nostrip*: by default, html tags will be stripped off the cell content before rendering. This protects the table
      layout. 'nostrip' deactivates the cleaning to make pure html possible.
    * *icon*: the cell value contains the name of an icon in *typo3conf/ext/qfq/Resources/Public/icons*. Empty cell values
      will omit an html image tag (=nothing rendered in the cell).
    * *link*: value will be rendered as described under :ref:`column-link`
    * *url*: value will be rendered as a href url.
    * *mailto*: value will be rendered as a href mailto.
    * *_rowClass*

      * The value is a CSS class name(s) which will be rendered in the *<tr class="<_rowClass>">* of the subrecord table.
      * The column itself is not rendered.
      * By using Bootstrap, the following predefined classes are available:

        * Text color: *text-muted|text-primary|text-success|text-info|text-warning|text-danger* (http://getbootstrap.com/docs/3.4/css/#helper-classes)
        * Row background: *active|success|info|warning|danger* (http://getbootstrap.com/docs/3.4/css/#tables-contextual-classes)

    * *_rowTooltip*

      * Defines the title attribute (=tooltip) of a subrecord table row.

    * Examples::

         {{!SELECT id, note1 AS 'Comment', note2 AS 'Comment|50' , note3 AS 'title=Comment|maxLength=100|nostrip', note4 AS '50|Comment',
         'checked.png' AS 'Status|icon', email AS 'mailto', CONCAT(homepage, '|Homepage') AS 'url',
         CONCAT('d|s|F:', pathFileName) AS 'Download|link',
         ELT(status,'info','warning','danger') AS '_rowClass', help AS '_rowTooltip' ...}}

* *FormElement.parameter*

  * *form* = `<form name>` - Target form, e.g. *form=person*
  * *page* = `<T3 page alias or id>` - Target page with detail form. If none specified, use the current page.
  * *extraDeleteForm*: Optional. The per row delete Button will reference the form specified here (for deleting) instead of the default (*form*).
  * *detail* = `<string>` - Mapping of values from

    * a) the primary form,
    * b) the current row,
    * c) any constant or '{{...}}' -

    to the target form (defined via `form=...`).

    * Syntax::

        <source table column name 1|&constant 1>:<target column name 1>[,<source table column name 2|&constant 2>:<target column name 2>][...]

    * Example: *detail=id:personId,rowId:secId,&12:xId,&{{a}}:personId*  (rowId is a column of the current selected row defined by sql1)
    * By default, the given value will overwrite values on the target record. In most situations, this is the wished behaviour.
    * Exceptions of the default behaviour have to be defined on the target form in the corresponding *FormElement* in the
      field *value* by changing the default Store priority definition. E.g. `{{<columnName>:RS0}}` - For existing records,
      the store `R` will provide a value. For new records, store `R` is empty and store S will be searched for a value:
      the value defined in `detail` will be choosen. At last the store '0' is defined as a fallback.
    * *source table column name*: E.g. A person form is opened with person.id=5 (r=5). The definition `detail=id:personId`
      and `form=address` maps person.id to address.personId. On the target record, the column personId becomes '5'.
    * *Constant '&'*: Indicate a 'constant' value. E.g. `&12:xId` or `{{...}}` (all possibilities, incl. further SELECT
      statements) might be used.

  * *subrecordTableClass*: Optional. Default: 'table table-hover qfq-subrecord-table qfq-color-grey-2'. If given, the default will be
     overwritten. Example: ::

        subrecordTableClass = table table-hover qfq-subrecord-table qfq-table-50

  * Tablesorter in Subrecord:

       subrecordTableClass = table table-hover qfq-subrecord-table tablesorter tablesorter-pager tablesorter-filter

  * *subrecordColumnTitleEdit*: Optional. Will be rendered as the column title for the new/edit column.
  * *subrecordColumnTitleDelete*: Optional. Will be rendered as the column title for the delete column.

**Subrecord DragAndDrop**

Subrecords inherently support drag-and-drop, see also `drag_and_drop`_.
The following parameters can be used in the `parameter` field to customize/activate drag-and-drop:

* *orderInterval*: The order interval to be used, default is 10.
* *dndTable*: The table that contains the records to be ordered.
  If not given, the table name of the form specified via `form=...` is used.
* *orderColumn*: The dedicated order column in the specified dndTable (needs to match a column in the table definition).
  Default is `ord`.

If `dndTable` is an actual table with a column `orderColumn`, QFQ automatically applies drag-and-drop logic
to the rendered subrecord. It does so by using the subrecord field *sql1*. The `sql1` query should
therefore include both a column id (or _id) and ord (or _ord).

Tips:

* If you want to deactivate a drag-and-drop that QFQ automatically renders, set the `orderColumn` to a non-existing column.
  E.g., `orderColumn = nonExistingColumn`. This will deactivate drag-and-drop.
* In order to evaluate the `sql1` query dynamically during a drag-and-drop event, the fill store R (with the current form)
  is loaded. Currently, SIP parameters and other variables are not supplied to be evaluated during a drag-and-drop event.
  This may be added later upon request.
* If the subrecord is rendered with drag-and-drop active, but the order is not affected upon reload, there is
  most likely a problem with evaluating the `sql1` query at runtime.

Type: time
^^^^^^^^^^

* Range time: '00:00:00' to '23:59:59' or '00:00:00'. (http://dev.mysql.com/doc/refman/5.5/en/datetime.html)
* Optional:
* *FormElement.parameter*

  * *showSeconds* = `0|1` - shows the seconds. Independent if the user specifies seconds, they are displayed '1' or not '0'.
  * *showZero* =  `0|1` - For an empty timestamp, With '0' nothing is displayed. With '1' the string '00:00[:00]' is displayed.

.. _`input-upload`:

Type: upload
^^^^^^^^^^^^

An upload element is based on a 'file browse'-button and a 'trash'-button (=delete). Only one of them is shown at a time.
The 'file browse'-button is displayed, if there is no file uploaded already.
The 'trash'-button is displayed, if there is a file uploaded already.

After clicking on the browse button, the user select a file from the local filesystem.
After choosing the file, the upload starts immediately, shown by a turning wheel. When the server received the whole file
and accepts (see below) the file, the 'file browse'-button disappears and the filename is shown, followed by a 'trash'-button.
Either the user is satisfied now or the user can delete the uploaded file (and maybe upload another one).

Until this point, the file is cached on the server but not copied to the `fileDestination`. The user have to save the
current record, either to finalize the upload and/or to delete a previously uploaded file.

The FormElement behaves like a

* 'native FormElement' (showing controls/text on the form) as well as an
* 'action FormElement' by firing queries and doing some additional actions during form save.

Inside the *Form editor* it's shown as a 'native FormElement'.
During saving the current record, it behaves like an action FormElement
and will be processed after saving the primary record and before any action FormElements are processed.

* *FormElement.value* = `<string>` - By default, the full path of any already uploaded file is shown. To show something
  different, e.g. only the filename, define: ::

   a) {{filenameBase:V}}
   b) {{SELECT SUBSTRING_INDEX( '{{pathFileName:R}}', '/', -1)  }}

See also `downloadButton`_ to offer a download of an uploaded file.

* *FormElement.parameter*:

  * *capture* = `camera` - On a smartphone, after pressing the 'open file' button, the camera will be opened and a
    choosen picture will be uploaded. Automatically set/overwrite `accept=image/*`.

  * *accept* = `<mime type>,image/*,video/*,audio/*,.doc,.docx,.pdf`

    * List of mime types (also known as 'media types'): http://www.iana.org/assignments/media-types/media-types.xhtml
    * If none mime type is specified, 'application/pdf' is set. This forces that always (!) one type is specified.
    * To allow any type, specify ``*`` or ``*/*`` or ``*.*``.
    * One or more media types might be specified, separated by ','.
    * Different browser respect the given definitions in different ways. Typically the 'file choose' dialog offer:

      * the specified mime type (some browers only show 'custom', if more than one mime type is given),
      * the option 'All files' (the user is always free to **try** to upload other file types) - but the server won't accept them,
      * the 'file choose' dialog only offers files of the selected (in the dialog) type.

    * If for a specific file type is no mime type available, the definition of file extension(s) is possible. This is **less
      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.

  * *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}}* (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 `store_vars_form_element_upload`_.

      * 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 `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}}`
    * 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 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 }}