.. ==================================================
.. ==================================================
.. ==================================================
.. Header hierarchy
.. ==
..  --
..   ^^
..    ""
..     ;;
..      ,,
..
.. --------------------------------------------used to the update the records specified ------
.. Best Practice T3 reST: https://docs.typo3.org/m/typo3/docs-how-to-document/master/en-us/WritingReST/CheatSheet.html
..             Reference: https://docs.typo3.org/m/typo3/docs-how-to-document/master/en-us/WritingReST/Index.html
.. Italic *italic*
.. Bold **bold**
.. Code ``text``
.. External Links: `Bootstrap <http://getbootstrap.com/>`_
.. Internal Link: :ref:`downloadButton` (default url text) or :ref:`download Button<downloadButton>` (explicit url text)
.. Add Images:    .. image:: ./Images/a4.jpg
..
..
.. Admonitions
..           .. note::   .. important::     .. tip::     .. warning::
.. Color:   (blue)       (orange)           (green)      (red)
..
.. Definition:
.. some text becomes strong (only one line)
..      description has to indented

.. -*- coding: utf-8 -*- with BOM.

.. include:: Includes.txt


.. _variables:

Variable
========

Variables in QFQ are surrounded by double curly braces. Four different types of variable substitution functionality is
provided. Access to:

* :ref:`store-variables`
* :ref:`sql-variables`
* :ref:`row-column-variables`
* :ref:`link-function-column-variables`

Some examples, including nesting::

  # Store
  #---------------------------------------------
  {{r}}
  {{index:FS}}
  {{name:FS:alnumx:s:my default}}

  # SQL
  #---------------------------------------------
  {{SELECT name FROM Person WHERE id=1234}}

  # Row columns via line number (deprecated)
  #---------------------------------------------
  {{10.pId}}
  {{10.20.pId}}

  # Row columns via label
  #---------------------------------------------
  {{<label1>.pId}}
  {{<label2>.pId}}

  # Nesting
  #---------------------------------------------
  {{SELECT name FROM Person WHERE id={{r}} }}
  {{SELECT name FROM Person WHERE id={{key1:C:alnumx}} }} # explained below
  {{SELECT name FROM Person WHERE id={{SELECT id FROM Persfunction LIMIT 1}} }} # it's more efficient to use only one query

  # Link Columns
  {{p:form=Person&r=1|t:Edit Person|E|s AS link}}

  # Function Columns: output in {{fullname:R}}
  {{getFullname(pId) => fullname AS function}}

  # Function Columns: output direct tt_content
  {{getFullname(pId) AS function}}

Leading and trailing spaces inside curly braces are removed.

* ``{{ SELECT "Hello World"   }}`` becomes ``{{SELECT "Hello World"}}``
* ``{{ varname   }}`` becomes ``{{varname}}``

.. _`store-variables`:

Store variables
---------------

.. note::

  {{ :ref:`variable-name` : :ref:`store` : :ref:`sanitize-class` : :ref:`variable-escape` : :ref:`variable-default` : :ref:`variable-type-message-violate` }}

Example::

  {{pId}}
  {{pId:FSE}}
  {{pId:FSE:digit}}
  {{pId::digit}}
  {{name:FSE:alnumx:m}}
  {{name:::m}}
  {{name:FSE:alnumx:m:John Doe}}
  {{name::::John Doe}}
  {{name:FSE:alnumx:m:John Doe:forbidden characters}}
  {{name:::::forbidden characters}}

.. _`variable-name`:

Variable name
^^^^^^^^^^^^^

  {{**name**:store:sanitize:escape:default:message}}

* Any string.
* Some stores, like STORE_RECORD, are completely user defined variables, others, like STORE_SYSTEM, are mainly predefined.

Example::

  {{pId}}
  {{personName}}

Store
^^^^^

  {{name:**store**:sanitize:escape:default:message}}

* Check out all possible :ref:`store`.
* Zero or more stores might be specified to be searched for the given VarName.
* If no store is specified, the default for the searched stores are: **FSRVD** (=FORM > SIP > RECORD > VARS > DEFAULT).
* If the VarName is not found in one store, the next store is searched,  up to the last specified store.
* If the VarName is not found and a default value is given, the default is returned.
* If no value is found, nothing is replaced - the string ``{{<VarName>}}`` remains.
* If anywhere along the line an empty string is found, this **is** a value: therefore, the search will stop.

Example::

  {{personName:SE}}  >> look for `personName` in STORE_SIP and if not found return empty string.
  {{pId:SRF0}}       >> look for `pId` in STORE_SIP and if not found in STORE_RECORD and if not found in STORE_FORM
                        and if not found return 0.

.. _`sanitize-class`:

Sanitize class
^^^^^^^^^^^^^^

  {{name:store:**sanitize**:escape:default:message}}

Values in STORE_CLIENT *C* (Client=Browser) and STORE_FORM *F* (Form, HTTP 'post') are checked against a
sanitize class. Values from other stores are *not* checked against any sanitize class, unless a sanitize class is specified.

* Variables get by default the sanitize class defined in the corresponding `FormElement`. If not defined,
  the default class is ``digit``.
* A default sanitize class can be overwritten by individual definition: *{{a:C:alnumx}}*
* If a value violates the specific sanitize class, see :ref:`variable-type-message-violate` for default or customized message.
  By default the value becomes `!!<name of sanitize class>!!`. E.g. ``!!digit!!``.

For QFQ variables and FormElements:

+------------------+------+-------+-------------------------------------------------------------------------------------------+
| Name             | Form | Query | Pattern                                                                                   |
+==================+======+=======+===========================================================================================+
| **alnumx**       | Form | Query | [a-z][A-Z][0-9]@-_.,;: /()ÀÈÌÒÙàèìòùÁĆÉÍÓÚÝáćéíóúýÂÊÎÔÛâêîôûÃÑÕãñõÄËÏÖÜŸäëïöüÿçČčĐ𩹮žß  |
+------------------+------+-------+-------------------------------------------------------------------------------------------+
| **digit**        | Form | Query | [0-9]                                                                                     |
+------------------+------+-------+-------------------------------------------------------------------------------------------+
| **numerical**    | Form | Query | [0-9.-+]                                                                                  |
+------------------+------+-------+-------------------------------------------------------------------------------------------+
| **allbut**       | Form | Query | All characters allowed, but not [ ]  { } % \ #. The used regexp: ``^[^\[\]{}%\\#]+$',``   |
+------------------+------+-------+-------------------------------------------------------------------------------------------+
| **all**          | Form | Query | no sanitizing                                                                             |
+------------------+------+-------+-------------------------------------------------------------------------------------------+


Only in FormElement:

+------------------+------+-------+-------------------------------------------------------------------------------------------+
| **auto**         | Form |       | Only supported for FormElements. Most suitable checktype is dynamically evaluated based   |
|                  |      |       | on native column definition, the FormElement type, and other info. See below for details. |
+------------------+------+-------+-------------------------------------------------------------------------------------------+
| **email**        | Form | Query | [a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\\.[a-zA-Z]{2,}                                          |
+------------------+------+-------+-------------------------------------------------------------------------------------------+
| **pattern**      | Form |       | Compares the value against a regexp.                                                      |
+------------------+------+-------+-------------------------------------------------------------------------------------------+


Rules for CheckType Auto (by priority):

* TypeAheadSQL or TypeAheadLDAP defined: **alnumx**
* Table definition
  * integer type: **digit**
  * floating point number: **numerical**
* FE Type
  * ``password``, ``note``: **all**
  * ``editor``, ``text`` and encode = ``specialchar``: **all**
* None of the above: **alnumx**

Example::

  {{personName:CE:allbut}}  >> look for `personName` in STORE_CLIENT (apply sanitize class `allbut`) and if not found return empty string.
  {{pId:FR0}}       >> look for `pId` in STORE_FORM (apply sanitize class `digit` cause no sanitize class is defined)
                       and if still not found in STORE_RECORD and if still not found in
                       STORE_FORM and if still not found return 0.

.. _`variable-escape`:

Escape/Action class
^^^^^^^^^^^^^^^^^^^

  {{name:store:sanitize:**escape**:default:message}}

The following `escape` & `action` types are available:


+-------+----------------------------------------------------------------------------------------------------------------------------------+
| Token | Description                                                                                                                      |
+=======+==================================================================================================================================+
| c     | Config - the escape type configured in :ref:`configuration`.                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| C     | Colon ``:`` will be escaped by ``\:``                                                                                            |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| d     | Double ticks ``"`` will be escaped by ``\"``.                                                                                    |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| l     | LDAP search filter values: `ldap-escape() <http://php.net/manual/en/function.ldap-escape.php>`_ (LDAP_ESCAPE_FILTER).            |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| L     | LDAP DN values. `ldap-escape() <http://php.net/manual/en/function.ldap-escape.php>`_ (LDAP_ESCAPE_DN).                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| m     | `real_escape_string() <http://php.net/manual/en/mysqli.real-escape-string.php>`_ (m = mysql)                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| p     | Password hashing with salting. Password hashing algorithm may be configured in: :ref:`qfq.json` (default is Argon2i)             |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| s     | Single ticks ``'`` will be escaped by ``\'``.                                                                                    |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| S     | Stop replace. If the replaced value contains nested variables, they won't be replaced.                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| t     | Returns the central european timezone ``CET`` / ``CEST`` depending on the given date. If the TZ is not GMT+1 or 2, returns GMT+x |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| w     | wipe out current key/value pair from SIP store :ref:`variable-escape-wipe-key<variable-escape-wipe-key>`                         |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| X     | Throw exception if variable is not found in the given store(s). Outputs :ref:`variable-type-message-violate`                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| ''    | Nothing defined - the escape/action class type configured in :ref:`configuration`.                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| \-    | No escaping.                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| E     | Encrypt value with AES. See for more information here :ref:`variable-encrypt`                                                    |
+-------+----------------------------------------------------------------------------------------------------------------------------------+
| D     | Decrypt an encrypted value. See for more information here :ref:`variable-decrypt`                                                |
+-------+----------------------------------------------------------------------------------------------------------------------------------+


* The ``escape/action`` class is defined by the fourth parameter of the variable. E.g.: ``{{name:FE:alnumx:m}}`` (m = mysql).
* It's possible to combine multiple ``escape/action`` classes, they will be processed in the order given. E.g. ``{{name:FE:alnumx:Ls}}`` (L, s).
* Escaping is typically necessary for all user supplied content, especially if they are processed via SQL or LDAP queries.
* Be careful when escaping nested variables. Best is to escape **only** the most outer variable.
* In :ref:`configuration` a global ``escapeTypeDefault`` can be defined. The configured ``escape/action`` class applies to all substituted
  variables, who *do not* contain a *specific* ``escape/action`` class.
* Additionally a ``defaultEscapeType`` can be defined per ``Form`` (separate field in the *Form editor*). This overwrites the
  global definition of ``configuration``. By default, every ``Form.defaultEscapeType`` = 'c' (=config), which means the setting
  in :ref:`configuration`.
* To suppress an escape type, define the ``escape type`` = '-' on the specific variable. E.g.: ``{{name:FE:alnumx:-}}``.

Escape
""""""

To *escape* a character typically means: a character, which have a special meaning/function, should not treated as a special
character.
E.g. a string is surrounded by single ticks ``'``. If such a string should contain the same single tick inside,
the inside single tick has to be escaped - if not, the string end's at the second tick, not the third. This is typically
done by a backlash: \\

QFQ offers different ways of escaping. Which of them to use, depends on the situation.

Especially variables used in SQL statements might cause trouble when using: NUL (ASCII 0), \\n, \\r, \\, ', ", or Control-Z.

Action
""""""

* *password* - ``p``: transforms the value of the variable into a Typo3 salted password hash. The hash function is the one
  used by Typo3 to encrypt and salt a password. This is useful to manipulate FE user passwords via QFQ. See :ref:`setFeUserPassword`

* *stop replace*  - ``S``: typically QFQ will replace nested variables as long as there are variables to replace. This options
  stops this

* *exception* - ``X``: If a variable is not found in any given store, it's replace by a default value or an error message.
  In special situation it might be useful to do a full stop on all current actions (no further procession). A custom
  message can be defined via: :ref:`variable-type-message-violate`.

.. _`variable-escape-wipe-key`:

* *wipe* - ``w``: In special cases it might be useful to get a value via SIP only one time and after retrieving the value
  it will be deleted in STORE SIP . Further access to the variable will return *variable undefined*. At time of writing
  only the STORE SIP supports the feature *wipe*. This is useful to suppress any repeating events by using the browser history.
  The following example will send a mail only the first when it is called with a given SIP::

    10.sql = SELECT '...' AS _sendmail FROM Person AS p WHERE '{{action:S::w}}'='send' AND p.id={{pId:S}}



.. _`variable-encrypt`:

* *encryption* - ``E``: encrypts the value with AES (Advanced Encryption Standard).
To use this feature its necessary that the encryption key is configured. See :ref:`encryption-key`.
The default encryption method will be used if nothing defined in Variable. See: :ref:`extension-manager-qfq-configuration`.
Available defaults: AES-128, AES-256
Example::

      10.sql = SELECT 'my secret' AS _myValue
      20.sql = UPDATE Person SET secret='{{myValue:RE:all:E}}' WHERE id = 1
      30.sql = UPDATE Person SET secret='{{myValue:RE:all:E=AES-256}}' WHERE id = 1

.. _`variable-decrypt`:

* *decryption* - ``D``: decrypts values which are with QFQ encrypted.

.. _`variable-default`:

Default
^^^^^^^

  {{name:store:sanitize:escape:**default**:message}}

* Any string can be given to define a default value.
* If a default value is given, it makes no sense to define more than one store: with a default value given, only the
  first store is considered.
* If the default value contains a ``:``, that one needs to be escaped by ``\``
* For dedicated variables this value has a special meaning. E.g. ``{{randomUniq:V}}`` uses this as ``expire`` argument.

.. _`variable-type-message-violate`:

Type message violate
^^^^^^^^^^^^^^^^^^^^

  {{name:store:sanitize:escape:default:**message**}}

If a value violates the sanitize class, the following actions are possible:

* ``c`` - The violated class will be set as content, surrounded by *!!*. E.g. ``!!digit!!``. This is the default.
* ``e`` - Instead of the value an empty string will be set as content.
* ``0`` - Instead of the value the string *0* will be set as content.
* *custom text ...* - Instead of the value, the custom text will be set as content. If the text contains a ``:``, that one
  needs to be escaped by \\ . Check :ref:`variable-escape` qualifier ``C`` to let QFQ do the colon escaping.

.. _`sql-variables`:

SQL variables
-------------

* The detection of an SQL command is case *insensitive*.
* Leading  whitespace will be skipped.
* The following commands are interpreted as SQL commands:

  * SELECT
  * INSERT, UPDATE, DELETE, REPLACE, TRUNCATE
  * SHOW, DESCRIBE, EXPLAIN, SET

* An SQL Statement might contain variables, including additional SQL statements. Inner SQL queries will be executed first.
* All variables will be substituted one by one from inner to outer.
* The number of variables inside an input field or an SQL statement is not limited.

Result: string
^^^^^^^^^^^^^^

A result of an SQL statement will be imploded over all: concat all columns of a row, concat all rows - there is no
glue string::

   {{SELECT 'hello world'}}   >> hello world
   {{SELECT firstName FROM Person}}   >> JaneJohnLisa
   {{SELECT firstName, name FROM Person}}   >> JaneDoeJohnDoeLisaDoe

Result: row
^^^^^^^^^^^

A few functions needs more than a returned string, instead separate columns are necessary. To indicate an array
result, specify those with an ``!``::

   {{!SELECT firstName, name FROM Person LIMIT 2}}
    >> Array: [ [ 'firstName' -> 'Jane', 'name' -> 'Doe' ], [ 'firstName' -> 'John', 'name' -> 'Doe' ] ]

This manual will specify the individual QFQ elements, who needs an array instead of a string. It's an error to return
a string where an array is needed and vice versa.

Database index
^^^^^^^^^^^^^^

To access different databases in a :ref:`multi-database`  setup, the database index can be specified after the opening curly
braces. ::

  {{[1]SELECT ... }}

For using the indexData and indexQfq (:ref:`configuration`), it's a good practice to specify the variable name
instead of the numeric index. ::

   {{[{{indexData:Y}}]SELECT ...}}

If no dbIndex is given, `{{indexData:Y}}` is used.


Example::

  {{SELECT id, name FROM Person}}
  {{SELECT id, name, IF({{feUser:T0}}=0, 'Yes', 'No')  FROM Person WHERE id={{r:S}} }}
  {{SELECT id, city FROM Address AS adr WHERE adr.accId={{SELECT id FROM Account AS acc WHERE acc.name={{feUser:T0}} }} }}
  {{!SELECT id, name FROM Person}}
  {{[2]SELECT id, name FROM Form}}
  {{[{{indexQfq:Y}}]SELECT id, name FROM Form}}

.. _`row-column-variables`:

Row column variables
--------------------

Syntax:  *{{<alias>.<column>}}* or (deprecated) *{{<level>.<column>}}*

Only used in report to access outer columns. See :ref:`access-column-values` and :ref:`syntax-of-report`.

There might be name conflicts between VarName / SQL keywords and <line identifier>. QFQ checks first for *<alias>* (*<level>*),
than for *SQL keywords* and than for *VarNames* in stores.

All types might be nested with each other. There is no limit of nesting variables.

Very specific: Also, it's possible that the content of a variable is again (including curly braces) a variable - this
is sometimes used in text templates, where the template is retrieved from a record and
specific locations in the text will be (automatically by QFQ) replaced by values from other sources.

General note: using this type of variables is only the second choice. First choice is `{{column:R}}` (see
:ref:`access-column-values`) - using the STORE_RECORD is more portable cause no renumbering is needed if the level keys change.

Special column names: To access the definition of the variable created by a special column name, use `{{&...`. Further
details under :ref:`access-column-values` .

.. _`link-function-column-variables`:

Link/Function column variables
------------------------------

Link column
^^^^^^^^^^^

These variables return a link, completely rendered in HTML. The syntax and all features of :ref:`column-link` are available.
The following code will render a *new person* button::

  {{p:form&form=Person|s|N|t:new person AS link}}

Optional: For better reading, the format string might be wrapped in single or double quotes: ::

  {{"p:form&form=Person|s|N|t:new person" AS link}}


Function column
^^^^^^^^^^^^^^^

Function column variables are helpful in:

* `report`, to create create links or buttons outside of an SQL statement. E.g. in `head`, `rbeg`, ...
* `form`, to create links and buttons in labels or notes.

Definition of a qfqFunction: Report with qfqFunction. Subheader: getFullname::

    render = api
    10.sql = SELECT CONCAT(lastName, ', ', firstName) FROM Person WHERE id = {{pId:R0}}

a) Somewhere in a different report or form::

    {{getFullname(pId) AS function}}

b) Somewhere in a different report, output is returned in STORE_RECORD variable `fullname`::

    {{getFullname(pId) => fullname AS function}}