Report.rst

.. _column_paged:

Column: _paged
^^^^^^^^^^^^^^

These column offers a link, with a confirmation question, to delete one record (mode 'table') or a bunch of records
(mode 'form'). After deleting the record(s), the current page will be reloaded in the browser.

**Syntax** ::

    10.sql = SELECT "U:table=<tablename>&r=<record id>|q:<question>|..." AS _paged
    10.sql = SELECT "U:form=<formname>&r=<record id>|q:<question>|..." AS _paged

..

If the record to delete contains column(s), whose column name match on `%pathFileName%` and such a
column points to a real existing file, such a file will be deleted too. If the table contains records where the specific
file is multiple times referenced, than the file is not deleted (it would break the still existing references). Multiple
references are not found, if they use different colummnnames or tablenames.

Mode: table
"""""""""""

* ``table=<table name>``
* ``r=<record id>``

Deletes the record with id '<record id>' from table '<table name>'.

Mode: form
""""""""""

* ``form=<form name>``
* ``r=<record id>``

Deletes the record with id '<record id>' from the table specified in form '<form name>' as primary table.
Additional action *FormElement* of type *beforeDelete* or *afterDelete* will be fired too.

Examples
""""""""

::

    10.sql = SELECT 'U:table=Person&r=123|q:Do you want delete John Doe?' AS _paged
    10.sql = SELECT 'U:form=person-main&r=123|q:Do you want delete John Doe?' AS _paged

.. _column_ppageX:

Columns: _Page[X]
^^^^^^^^^^^^^^^^^

* Similar to ``_page[X]``
* Parameter are position dependent and therefore without a qualifier!

::

    "[<page slug>[?param=value&...]] | [text] | [tooltip] | [question parameter] | [class] | [target] | [render mode]" as _Pagee.

.. _column_ppaged:

Column: _Paged
^^^^^^^^^^^^^^

* Similar to ``_paged``
* Parameter are position dependent and therefore without a qualifier!

::

    "[table=<table name>&r=<record id>[&param=value&...] | [text] | [tooltip] | [question parameter] | [class] | [render mode]" as _Paged.
    "[form=<form name>&r=<record id>[&param=value&...] | [text] | [tooltip] | [question parameter] | [class] | [render mode]" as _Paged.

.. _column_vertical:

Column: _vertical
^^^^^^^^^^^^^^^^^

Use instead :ref:`vertical-column-title`

.. warning:: The '... AS _vertical' is deprecated - do not use it anymore.

Render text vertically. This is useful for tables with limited column width. The vertical rendering is achieved via CSS tranformations
(rotation) defined in the style attribute of the wrapping tag. You can optionally specify the rotation angle.

**Syntax** ::

    10.sql = SELECT "<text>|[<angle>]" AS _vertical

..

+-------------+--------------------------------------------------------------------------------------------------------+-----------------+
|**Parameter**| **Description**                                                                                        |**Default value**|
+=============+========================================================================================================+=================+
|<text>       | The string that should be rendered vertically.                                                         |none             |
+-------------+--------------------------------------------------------------------------------------------------------+-----------------+
|<angle>      | How many degrees should the text be rotated? The angle is measured clockwise from baseline of the text.|*270*            |
+-------------+--------------------------------------------------------------------------------------------------------+-----------------+

The text is surrounded by some HTML tags in an effort to make other elements position appropriately around it.
This works best for angles close to 270 or 90.

**Minimal Example** ::

    10.sql = SELECT "Hello" AS _vertical
    20.sql = SELECT "Hello|90" AS _vertical
    20.sql = SELECT "Hello|-75" AS _vertical

..

.. _column_mailto:

Column: _mailto
^^^^^^^^^^^^^^^

Easily create Email links.

**Syntax** ::

    10.sql = SELECT "<email address>|[<link text>]" AS _mailto

..



+--------------+----------------------------------------------------------------------------------------+-------------+
|**Parameter** |**Description**                                                                         |**Default    |
|              |                                                                                        |value**      |
+==============+========================================================================================+=============+
|<emailaddress>| The email address where the link should point to.                                      |none         |
+--------------+----------------------------------------------------------------------------------------+-------------+
|<linktext>    | The text that should be displayed on the website and be linked to the email address.   |none         |
|              | This will typically be the name of the recipient. If this parameter is omitted,        |             |
|              | the email address will be displayed as link text.                                      |             |
+--------------+----------------------------------------------------------------------------------------+-------------+


**Minimal Example** ::

    10.sql = SELECT "john.doe@example.com" AS _mailto



**Advanced Example** ::

    10.sql = SELECT "john.doe@example.com|John Doe" AS _mailto


.. _column_sendmail:

Column: _sendmail
^^^^^^^^^^^^^^^^^

Format::

    t:<TO:email[,email]>|f:<FROM:email>|s:<subject>|b:<body>
        [|c:<CC:email[,email]]>[|B:<BCC:email[,email]]>[|r:<REPLY-TO:email>]
        [|A:<flag autosubmit: on/off>][|g:<grId>][|x:<xId>][|y:<xId2>][|z:<xId3>][|h:<mail header>]
        [|e:<subject encode: encode/decode/none>][E:<body encode: encode/decode/none>][|mode:html]
        [|C][d:<filename of the attachment>][|F:<file to attach>][|u:<url>][|p:<T3 uri>]

The following parameters can also be written as complete words for ease of use::

    to:<email[,email]>|from:<email>|subject:<subject>|body:<body>
        [|cc:<email[,email]]>[|bcc:<email[,email]]>[|reply-to:<email>]
        [|autosubmit:<on/off>][|grid:<grid>][|xid:<xId>][|xid2:<xId2>][|xid3:<xId3>][|header:<mail header>]
        [|mode:html]

Send emails. Every mail will be logged in the table `mailLog`. Attachments are supported.

**Syntax** ::

    10.sql = SELECT "t:john@doe.com|f:jane@doe.com|s:Reminder tomorrow|b:Please dont miss the meeting tomorrow" AS _sendmail
    10.sql = SELECT "t:john@doe.com|f:jane@doe.com|s:Reminder tomorrow|b:Please dont miss the meeting tomorrow|A:off|g:1|x:2|y:3|z:4" AS _sendmail

..

+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
|**Token**     | **Parameter**                          |**Description**                                                                                   |**Required**|
| short / long |                                        |                                                                                                  |            |
+==============+========================================+==================================================================================================+============+
| | f          | email                                  |**FROM**: Sender of the email. Optional: 'realname <john@doe.com>'                                |    yes     |
| | from       |                                        |                                                                                                  |            |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| | t          | email[,email]                          |**TO**: Comma separated list of receiver email addresses. Optional: `realname <john@doe.com>`     |    yes     |
| | to         |                                        |                                                                                                  |            |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| | c          | email[,email]                          |**CC**: Comma separated list of receiver email addresses. Optional: 'realname <john@doe.com>'     |            |
| | cc         |                                        |                                                                                                  |    yes     |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| | B          | email[,email]                          |**BCC**: Comma separated list of receiver email addresses. Optional: 'realname <john@doe.com>'    |            |
| | bcc        |                                        |                                                                                                  |    yes     |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| | r          | REPLY-TO:email                         |**Reply-to**: Email address to reply to (if different from sender)                                |            |
| | reply-to   |                                        |                                                                                                  |    yes     |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| | s          | Subject                                |**Subject**: Subject of the email                                                                 |    yes     |
| | subject    |                                        |                                                                                                  |            |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| | b          | Body                                   |**Body**: Message - see also: :ref:`html-formatting<html-formatting>`                             |    yes     |
| | body       |                                        |                                                                                                  |            |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| | h          | Mail header                            |**Custom mail header**: Separate multiple header with \\r\\n                                      |            |
| | header     |                                        |                                                                                                  |    yes     |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| F            | Attach file                            |**Attachment**: File to attach to the mail. Repeatable.                                           |            |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| u            | Attach created PDF of a given URL      |**Attachment**: Convert the given URL to a PDF and attach it the mail. Repeatable.                |            |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| p            | Attach created PDF of a given T3 URL   |**Attachment**: Convert the given URL to a PDF and attach it the mail. Repeatable.                |            |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| d            | Filename of the attachment             |**Attachment**: Useful for URL to PDF converted attachments. Repeatable.                          |            |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| C            | Concat multiple F|p|u| together        |**Attachment**: All following (until the next 'C') 'F|p|u' concatenated to one attachment.        |            |
|              |                                        | Repeatable.                                                                                      |            |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| | A          | flagAutoSubmit  'on' / 'off'           |If 'on' (default), add mail header 'Auto-Submitted: auto-send' - suppress OoO replies             |            |
| | autosubmit |                                        |                                                                                                  |    yes     |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| | g          | grId                                   |Will be copied to the mailLog record. Helps to setup specific logfile queries                     |            |
| | grid       |                                        |                                                                                                  |    yes     |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| | x          | xId                                    |Will be copied to the mailLog record. Helps to setup specific logfile queries                     |            |
| | xid        |                                        |                                                                                                  |    yes     |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| | y          | xId2                                   |Will be copied to the mailLog record. Helps to setup specific logfile queries                     |            |
| | xid2       |                                        |                                                                                                  |    yes     |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| | z          | xId3                                   |Will be copied to the mailLog record. Helps to setup specific logfile queries                     |            |
| | xid3       |                                        |                                                                                                  |    yes     |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| e            | encode|decode|none                     |**Subject**: will be htmlspecialchar() encoded, decoded (default) or none (untouched)             |            |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| E            | encode|decode|none                     |**Body**: will be htmlspecialchar() encoded, decoded (default) or none (untouched).               |            |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+
| mode         | html                                   |**Body**: will be send as a HTML mail.                                                            |            |
+--------------+----------------------------------------+--------------------------------------------------------------------------------------------------+------------+

* **e|E**: 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 `e=none` and/or `E=none`.


**Minimal Example** ::

    10.sql = SELECT "t:john.doe@example.com|f:company@example.com|s:Latest News|b:The new version is now available." AS _sendmail

This will send an email with subject *Latest News* from company@example.com to john.doe@example.com.

**Advanced Examples** ::

    10.sql = SELECT "t:customer1@example.com,Firstname Lastname <customer2@example.com>, Firstname Lastname <customer3@example.com>| \\
                     f:company@example.com|s:Latest News|b:The new version is now available.|r:sales@example.com|A:on|g:101|x:222|c:ceo@example.com|B:backup@example.com" AS _sendmail

This will send an email with subject *Latest News* from company@example.com to customer1, customer2 and customer3 by
using a realname for customer2 and customer3 and suppress generating of OoO answer if any receiver is on vacation.
Additional the CEO as well as backup will receive the mail via CC and BCC.

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

.. _html-formatting:

**Mail Body HTML Formatting**

In order to send an email with HTML formatting, such as bold text or bullet lists, specify 'mode=html'.
The subsequent contents will be interpreted as HTML and is rendered correctly by most email programs.

.. _attachment:

Attachment
""""""""""

The following options are provided to attach files to an email:

+-------+------------------------------------------------------+--------------------------------------------------------+
| Token | Example                                              | Comment                                                |
+=======+======================================================+========================================================+
| F     | F:fileadmin/file3.pdf                                | Single file  to attach                                 |
+-------+------------------------------------------------------+--------------------------------------------------------+
| u     | u:www.example.com/index.html?key=value&...           | A URL, will be converted to a PDF and than attached.   |
+-------+------------------------------------------------------+--------------------------------------------------------+
| p     | p:?id=export&r=123&_sip=1                            | A SIP protected local T3 page.                         |
|       |                                                      | Will be converted to a PDF and than attached.          |
+-------+------------------------------------------------------+--------------------------------------------------------+
| d     | d:myfile.pdf                                         | Name of the attachment in the email.                   |
+-------+------------------------------------------------------+--------------------------------------------------------+
| C     | C|u:http://www.example.com|F:file1.pdf|C|F:file2.pdf | Concatenate all named sources to one PDF file. The     |
|       |                                                      | souces has to be PDF files or a web page, which will be|
|       |                                                      | converted to a PDF first.                              |
+-------+------------------------------------------------------+--------------------------------------------------------+

Any combination (incl. repeating them) are possible. Any source will be added as a single attachment.

Optional any number of sources can be concatenated to a single PDF file: 'C|F:<file1>|F:<file2>|p:export&a=123'.

Examples in Report::

  # One file attached.
  10.sql = SELECT "t:john.doe@example.com|f:company@example.com|s:Latest News|b:The new version is now available.|F:fileadmin/summary.pdf" AS _sendmail

  # Two files attached.
  10.sql = SELECT "t:john.doe@example.com|f:company@example.com|s:Latest News|b:The new version is now available.|F:fileadmin/summary.pdf|F:fileadmin/detail.pdf" AS _sendmail

  # Two files and a webpage (converted to PDF) are attached.
  10.sql = SELECT "t:john.doe@example.com|f:company@example.com|s:Latest News|b:The new version is now available.|F:fileadmin/summary.pdf|F:fileadmin/detail.pdf|p:?id=export&r=123|d:person.pdf" AS _sendmail

  # Two webpages (converted to PDF) are attached.
  10.sql = SELECT "t:john.doe@example.com|f:company@example.com|s:Latest News|b:The new version is now available.|p:?id=export&r=123|d:person123.pdf|p:?id=export&r=234|d:person234.pdf" AS _sendmail

  # One file and two webpages (converted to PDF) are *concatenated* to one PDF and attached.
  10.sql = SELECT "t:john.doe@example.com|f:company@example.com|s:Latest News|b:The new version is now available.|C|F:fileadmin/summary.pdf|p:?id=export&r=123|p:?id=export&r=234|d:complete.pdf" AS _sendmail

  # One T3 webpage, protected by a SIP, are attached.
  10.sql = SELECT "t:john.doe@example.com|f:company@example.com|s:Latest News|b:The new version is now available.|p:?id=export&r=123&_sip=1|d:person123.pdf" AS _sendmail

.. _column_img:

Column: _img
^^^^^^^^^^^^

Renders images. Allows to define an alternative text and a title attribute for the image. Alternative text and title text are optional.

*   If no alternative text is defined, an empty alt attribute is rendered in the img tag (since this attribute is mandatory in HTML).
*   If no title text is defined, the title attribute will not be rendered at all.

**Syntax** ::

    10.sql = SELECT "<path to image>|[<alt text>]|[<title text>]" AS _img


+--------------+-------------------------------------------------------------------------------------------+-----------------------------+
|**Parameter** |**Description**                                                                            | **Default value/behaviour** |
+==============+===========================================================================================+=============================+
|<pathtoimage> |The path to the image file.                                                                | none                        |
+--------------+-------------------------------------------------------------------------------------------+-----------------------------+
|<alttext>     |Alternative text. Will be displayed if image can't be loaded (alt attribute of img tag).   | empty string                |
+--------------+-------------------------------------------------------------------------------------------+-----------------------------+
|<titletext>   |Text that will be set as image title in the title attribute of the img tag.                | no title attribute rendered |
+--------------+-------------------------------------------------------------------------------------------+-----------------------------+


**Minimal Example** ::

    10.sql = SELECT "fileadmin/img/img.jpg" AS _img


**Advanced Examples** ::

    10.sql = SELECT "fileadmin/img/img.jpg|Aternative Text" AS _img            # alt="Alternative Text, no title
    20.sql = SELECT "fileadmin/img/img.jpg|Aternative Text|" AS _img           # alt="Alternative Text, no title
    30.sql = SELECT "fileadmin/img/img.jpg|Aternative Text|Title Text" AS _img # alt="Alternative Text, title="Title Text"
    40.sql = SELECT "fileadmin/img/img.jpg|Alternative Text" AS _img           # alt="Alternative Text", no title
    50.sql = SELECT "fileadmin/img/img.jpg" AS _img                            # empty alt, no title
    60.sql = SELECT "fileadmin/img/img.jpg|" AS _img                           # empty alt, no title
    70.sql = SELECT "fileadmin/img/img.jpg||Title Text" AS _img                # empty alt, title="Title Text"
    80.sql = SELECT "fileadmin/img/img.jpg||" AS _img                          # empty alt, no title


.. _column_exec:

Column: _exec
^^^^^^^^^^^^^

Run any command on the web server.

* The command is run via web server, so with the uid of the web server.
* The current working directory is the current web instance (e.g. ``/var/www/html``) .
* All text send to 'stdout' will be returned.
* Text send to 'stderr' is not returned at all.
* If 'stderr' should be shown, redirect the output::

        SELECT 'touch /root 2>&1' AS _exec

* If 'stdout' / 'stderr' should not be displayed, redirect the output::

        SELECT 'touch /tmp >/dev/null' AS _exec
        SELECT 'touch /root 2>&1 >/dev/null' AS _exec

* Multiple commands can be concatenated by `;`::

        SELECT 'date; date' AS _exec

* If the return code is not 0, the string '[<rc>] ', will be prepended.
* If it is not wished to see the return code, just add ``true`` to fake rc of 0 (only the last rc will be reported)::

        SELECT 'touch /root; true' AS _exec

**Syntax**

::


    <command>

..



+-------------+---------------------------------------------------+-----------------+
|**Parameter**| **Description**                                   |**Default value**|
+=============+===================================================+=================+
|<command>    | The command that should be executed on the server.|none             |
+-------------+---------------------------------------------------+-----------------+


**Minimal Examples** ::

    10.sql = SELECT "ls -s" AS _exec
    20.sql = SELECT "./batchfile.sh" AS _exec

.. _column_script:

Column: _script
^^^^^^^^^^^^^^^

Run a php function defined in an external script.

* All **column parameters are passed** as an associative array to the function as the first argument.
* The second argument (here called $qfq) is an object which acts as an **interface to QFQ functionality** (see below).
* The **current working directory** inside the function is the current web instance (e.g. location of index.php).
   * Hint: Inside the script ``dirname(__FILE__)`` gives the path of the script.
* All **output (e.g. using echo) will be rendered** by the special column as is.
* If the function returns an associative array, then the **key-value pairs will be accessible via the VARS store `V`**.
* If the function throws an **exception** then a standard QFQ error message is shown.
* Text sent to 'stderr' by the php function is not returned at all.
* The script has access to the following **qfq php functions** using the interface (see examples below):
   * $qfq::apiCall($method, $url, $data = '', $header = [], $timeout = 5)
      * arguments:
         * string $method: can be PUT/POST/GET/DELETE
         * string $url
         * string $data: a JSON string which will be added as GET parameters or as POST fields respectively.
         * array $header: is of the form ['Content-type: text/plain', 'Content-length: 100']
         * int $timeout: is the number of seconds to wait until call is aborted.
      * return array:
         * [0]: Http status code
         * [1]: API answer as string.
   * $qfq::getVar($key, $useStores = 'FSRVD', $sanitizeClass = '', &$foundInStore = '', $typeMessageViolate = 'c')
      * arguments:
         * string $key: is the name of qfq variable
         * string $useStores: are the stores in which variable is searched (in order from left to right). see :ref:`store`.
         * string $sanitizeClass: (see :ref:`sanitize-class`)
         * string $foundInStore: is filled with the name of the store in which the variable was found.
         * string $typeMessageViolate: defines what to return if the sanitize class was violated:
            * 'c' : returns '!!<sanitize class>!!'
            * '0' : returns '0'
            * 'e' : returns ''
      * return string|false:
         * The value of the variable if found.
         * A placeholder if the variable violates the sanitize class. (see argument `$typeMessageViolate`)
         * `false` if the variable was not found.

**Column Parameters**

+-------------------+----------------------------------------------------+------------------------------------------------------------------------------------+
| Token             | Example                                            | Comment                                                                            |
+===================+====================================================+====================================================================================+
| F                 | F:fileadmin/scripts/my_script.php                  | Path to the custom script relative to the current web instance                     |
+-------------------+----------------------------------------------------+------------------------------------------------------------------------------------+
| call              | call:my_function                                   | PHP function to call                                                               |
+-------------------+----------------------------------------------------+------------------------------------------------------------------------------------+
| arg               | arg:a1=Hello&a2=World                              | Arguments are parsed and passed to the function together with the other parameters |
+-------------------+----------------------------------------------------+------------------------------------------------------------------------------------+

**Example**

* QFQ report ::

   5.sql = SELECT "IAmInRecordStore" AS _savedInRecordStore
   10.sql = SELECT "F:fileadmin/scripts/my_script.php|call:my_function|arg:a1=Hello&a2=World" AS _script
   20.sql = SELECT "<br><br>Returened value: {{IAmInVarStore:V:alnumx}}"

* PHP script (`fileadmin/scripts/my_script.php`) ::

   <?php
    function my_function($param, $qfq) {

        echo 'The first argument contains all attributes including "F" and "c":<br>';
        print_r($param);

        echo '<br><br>get variable from record store:<br>';
        print_r($qfq::getVar('savedInRecordStore', 'RE'));

        echo '<br><br>Make API call:<br>';
        list($http_code, $answer) = $qfq::apiCall('GET', 'google.com');
        echo 'Http code: ' . $http_code;

        // Returned array fills VARS store
        return ["IAmInVarStore" => "FooBar"];
    }

* Output ::

   The first argument contains all parameters including "F", "call" and "arg":
   Array ( [a1] => Hello [a2] => World [F] => fileadmin/scripts/my_script.php [call] => my_function [arg] => a1=Hello&a2=World )

   get variable from record store:
   IAmInRecordStore

   Make API call:
   Http code: 301

   Returened value: FooBar

.. _column_pdf:

Column: _pdf | _file | _zip
^^^^^^^^^^^^^^^^^^^^^^^^^^^

Detailed explanation: :ref:`download`

Most of the other Link-Class attributes can be used to customize the link. ::

    10.sql = SELECT "[options]" AS _pdf, "[options]" AS _file, "[options]" AS _zip

    with: [options] = [d:<exportFilename][|p:<params>][|U:<params>][|u:<url>][|F:file[:path/file in zip]][|t:<text>][|a:<message>][|o:<tooltip>][|c:<class>][|r:<render mode>]


* Parameter are position independent.
* *<params>*: see :ref:`download-parameter-files`
* For column ``_pdf`` and ``_zip``, the element sources ``p:...``, ``U:...``, ``u:...``, ``F:...`` might repeated multiple times.
* For column ``_zip``, an optional parameter might define the path and filename inside the ZIP: `F:<orig filename>:<inside ZIP path and filename>`
* To only render the page content without menus add the parameter ``type=2``. For example: ``U:id=pageToPrint&type=2&_sip=1&r=', r.id``
* Example::

    # ... AS _file
    10.sql = SELECT "F:fileadmin/test.pdf" as _pdf
    20.sql = SELECT "p:id=export&r=1" as _pdf
    30.sql = SELECT "t:Download PDF|F:fileadmin/test.pdf" as _pdf
    40.sql = SELECT "t:Download PDF|p:id=export&r=1" as _pdf
    50.sql = SELECT "d:complete.pdf|t:Download PDF|F:fileadmin/test1.pdf|F:fileadmin/test2.pdf" as _pdf
    60.sql = SELECT "d:complete.pdf|t:Download PDF|F:fileadmin/test.pdf|p:id=export&r=1|u:www.example.com" AS _pdf

    # ... AS _file
    100.sql = SELECT "F:fileadmin/test.pdf" as _file
    110.sql = SELECT "p:id=export&r=1" as _file
    120.sql = SELECT "t:Download PDF|F:fileadmin/test.pdf" as _file
    130.sql = SELECT "t:Download PDF|p:id=export&r=1" as _file

    # ... AS _zip
    200.sql = SELECT "F:fileadmin/test.pdf" as _zip
    210.sql = SELECT "p:id=export&r=1" as _zip
    220.sql = SELECT "t:Download ZIP|F:fileadmin/test.pdf" as _zip
    230.sql = SELECT "t:Download ZIP|p:id=export&r=1" as _zip
    # Several files
    240.sql = SELECT "d:complete.zip|t:Download ZIP|F:fileadmin/test1.pdf|F:fileadmin/test2.pdf" as _zip
    # Several files with new path/filename
    250.sql = SELECT "d:complete.zip|t:Download ZIP|F:fileadmin/test1.pdf:data/file-1.pdf|F:fileadmin/test2.pdf:data/file-2.pdf" as _zip


.. _column-save-pdf:

Column: _savePdf
^^^^^^^^^^^^^^^^

Generated PDFs can be stored directly on the server with this functionality. The link query consists of the following parameters:

* One or more element sources (such as ``F:``, ``U:``, ``p:``, see :ref:`download-parameter-files`), including possible wkhtmltopdf parameters
* The export filename and path as ``d:`` - for security reasons, this path has to start with *fileadmin/* and end with *.pdf*.

Tips:

* Please note that this option does not render anything in the front end, but is executed each time it is parsed.
  You may want to add a check to prevent multiple execution.
* It is not advised to generate the filename with user input for security reasons.
* If the target file already exists it will be overwriten. To save individual files, choose a new filename,
  for example by adding a timestamp.

Example::

  SELECT "d:fileadmin/result.pdf|F:fileadmin/_temp_/test.pdf" AS _savePdf
  SELECT "d:fileadmin/result.pdf|F:fileadmin/_temp_/test.pdf|U:id=test&--orientation=landscape" AS _savePdf


.. _column-thumbnail:

Column: _thumbnail
^^^^^^^^^^^^^^^^^^

For file ``T:<pathFileName>`` a thumbnail will be rendered, saved (to be reused) and a HTML ``<img>`` tag is returned,
With the SIP encoded thumbnail.

The thumbnail:

* Size is specified via ``W:<dimension>``. The file is only rendered once and subsequent access is delivered via a local QFQ cache.
* Will be rendered, if the source file is newer than the thumbnail or if the thumbnail dimension changes.
* The caching is done by building the MD5 of pathFileName and thumbnail dimension.
* Of multi page files like PDFs, the first page is used as the thumbnail.

All file formats, which 'convert' ImageMagick (https://www.imagemagick.org/) supports, can be
used. Office file formats are not supported. Due to speed and quality reasons, SVG files will be converted by inkscape.
If a file format is not known, QFQ tries to show a corresponding file type image provided by Typo3 - such an image is not
scaled.

In :ref:`configuration` the exact location of ``convert`` and ``inkscape`` can be configured (optional) as well as the directory
names for the cached thumbnails.

+-------+--------------------------------+----------------------------------------------------------------------------+
| Token | Example                        | Comment                                                                    |
+=======+================================+============================================================================+
| T     | T:fileadmin/file3.pdf          | File render a thumbnail                                                    |
+-------+--------------------------------+----------------------------------------------------------------------------+
| W     | W:200x, W:x100, W:200x100      | Dimension of the thumbnail: '<width>x<height>. Both                        |
|       |                                | parameter are optional. If non is given the default is W:150x              |
+-------+--------------------------------+----------------------------------------------------------------------------+
| s     | s:1, s:0                       | Optional. Default: ``s:1``. If SIP is enabled, the rendered URL            |
|       |                                | is a link via ``api/download.php?..``. Else a direct pathFileName.         |
+-------+--------------------------------+----------------------------------------------------------------------------+
| r     | r:7                            | Render Mode. Default 'r:0'. With 'r:7' only the url will be delivered.     |
+-------+--------------------------------+----------------------------------------------------------------------------+

The render mode '7' is useful, if the URL of the thumbnail have to be used in another way than the provided html-'<img>'
tag. Something like ``<body style="background-image:url(bgimage.jpg)">`` could be solved with
``SELECT "<body style="background-image:url(", 'T:fileadmin/file3.pdf' AS _thumbnail, ')">'``

Example::

  # SIP protected, IMG tag, thumbnail width 150px
  10.sql = SELECT 'T:fileadmin/file3.pdf' AS _thumbnail

  # SIP protected, IMG tag, thumbnail width 50px
  20.sql = SELECT 'T:fileadmin/file3.pdf|W:50' AS _thumbnail

  # No SIP protection, IMG tag, thumbnail width 150px
  30.sql = SELECT 'T:fileadmin/file3.pdf|s:0' AS _thumbnail

  # SIP protected, only the URL to the image, thumbnail width 150px
  40.sql = SELECT 'T:fileadmin/file3.pdf|s:1|r:7' AS _thumbnail


Dimension
"""""""""

ImageMagick support various settings to force the thumbnail size.
See https://www.imagemagick.org/script/command-line-processing.php#geometry or http://www.graphicsmagick.org/GraphicsMagick.html#details-geometry.

Cleaning
""""""""

By default, the thumbnail directories are never cleaned. It's a good idea to install a cronjob which purges all files
older than 1 year: ::

  find /path/to/files -type f -mtime +365 -delete

Render
""""""

`Public` thumbnails are rendered at the time when the T3 QFQ record is executed. `Secure` thumbnails are rendered when the
'download.php?s=...' is called. The difference is, that the 'public' thumbnails blocks the page load until all thumbnails
are rendered, instead the `secure` thumbnails are loaded asynchonous via the browser - the main page is already delivered to
browser, all thumbnails appearing after a time.

A way to *pre render* thumbnails, is a periodically called (hidden) T3 page, which iterates over all new uploaded files and
triggers the rendering via column `_thumbnail`.

Thumbnail: secure
"""""""""""""""""

Mode 'secure' is activated via enabling SIP (`s:1`, default). The thumbnail is saved under the path `thumbnailDirSecure`
as configured in :ref:`configuration`.

The secure path needs to be protected against direct file access by the webmaster / webserver configuration too.

QFQ returns a HTML 'img'-tag: ::

  <img src="api/download.php?s=badcaffee1234">

Thumbnail: public
"""""""""""""""""

Mode 'public' has to be explicit activated by specifying `s:0`. The thumbnail is saved under the path `thumbnailDirPublic`
as configured in :ref:`configuration`.

QFQ returns a HTML 'img'-tag: ::

  <img src="{{thumbnailDirPublic:Y}}/<md5 hash>.png">

.. _column-monitor:

Column: _monitor
^^^^^^^^^^^^^^^^

Detailed explanation: :ref:`monitor`

**Syntax** ::

    10.sql = SELECT 'file:<filename>|tail:<number of last lines>|append:<0 or 1>|interval:<time in ms>|htmlId:<id>' AS _monitor

+-------------+-------------------------------------------------------------------------------------------+---------------------------+
|**Parameter**|**Description**                                                                            |**Default value/behaviour**|
+=============+===========================================================================================+===========================+
|<filename>   |The path to the file. Relative to T3 installation directory or absolute.                   |none                       |
+-------------+-------------------------------------------------------------------------------------------+---------------------------+
|<tail>       |Number of last lines to show                                                               |30                         |
+-------------+-------------------------------------------------------------------------------------------+---------------------------+
|<append>     |0: Retrieved content replaces current. 1: Retrieved content will be added to current.      |0                          |
+-------------+-------------------------------------------------------------------------------------------+---------------------------+
|<htmlId>     |Reference to HTML element to whose content replaced by the retrieve one.                   |monitor-1                  |
+-------------+-------------------------------------------------------------------------------------------+---------------------------+

.. _column-encrypt:

Column: _encrypt
^^^^^^^^^^^^^^^^

Encrypting selected fields or strings with AES. It is possible to give your own encryption method. If no method is given
then the default is used. See here for more information about default method: :ref:`extension-manager-qfq-configuration`

**Syntax** ::

   10.sql = SELECT firstName AS _encrypt FROM Person WHERE id = 1
   20.sql = SELECT "Words to be encrypted" AS _encrypt=AES-128

**A useful situation**::

   10.sql = SELECT "Words to be encrypted" AS '_encrypt=AES-128|encryptedValue|_hide'
   20.sql = UPDATE Person SET secret = '{{&encryptedValue:RE:all}}' WHERE id = 1

Valid encryption methods:

* AES-128
* AES-256

.. _column-decrypt:

Column: _decrypt
^^^^^^^^^^^^^^^^

Decrypting selected columns or strings which are encrypted with QFQ.

**Syntax** ::

   10.sql = SELECT secret AS _decrypt FROM Person WHERE id = 1

.. _copyToClipboard:

Copy to clipboard
^^^^^^^^^^^^^^^^^

+-------------------+--------------------------------+----------------------------------------------------------------------------+
| Token             | Example                        | Comment                                                                    |
+===================+================================+============================================================================+
| y[:<content>]     | y,  y:some content             | Initiates 'copy to clipboard' mode. Source might given text or page or url |
+-------------------+--------------------------------+----------------------------------------------------------------------------+
| F:<pathFileName>  | F:fileadmin/protected/data.R   | pathFileName in DocumentRoot                                               |
+-------------------+--------------------------------+----------------------------------------------------------------------------+

Example::

    10.sql = SELECT 'y:hello world (yank)|t:content direct (yank)' AS _yank
                    , 'y:hello world (link)|t:content direct (link)' AS _link
                    , CONCAT('F:', p.pathFileName,'|t:File (yank)|o:', p.pathFileName) AS _yank
                    , CONCAT('y|F:', p.pathFileName,'|t:File (link)|o:', p.pathFileName) AS _link
                FROM Person AS p


.. _api_call_qfq_report:

API Call QFQ Report (e.g. AJAX)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

.. note::

   QFQ Report functionality protected by SIP offered to simple API calls: ``typo3conf/ext/qfq/Classes/Api/dataReport.php?s=....``

* General use API call to fire a specific QFQ tt-content record. Useful for e.g. AJAX calls. No Typo3 is involved. *No FE-Group access control*.
* This defines just a simple API endpoint. For defining a rest API see: :ref:`restApi`.
* Custom response headers can be defined by setting the variable `apiResponseHeader` in the record store.
   * Multiple headers should be separated by `\n` or `\r\n`. e.g.: `Content-Type: application/json\ncustom-header: fooBar`
* If the api call succeeds the rendered content of the report is returned as is. (no additional formatting, no JSON encoding)
   * You can use MYSQL to create Json. See: `MYSQL create Json <https://dev.mysql.com/doc/refman/8.0/en/json-creation-functions.html>`_ and `MariaDB Json functions <https://mariadb.com/kb/en/json-functions/>`_
* If a QFQ error occurs then a http-status of 400 is returned together with a JSON encoded response of the form: `{"status":"error", "message":"..."}`

Example QFQ record JS (with tt_content.uid=12345)::

   5.sql = SELECT "See console log for output"

   # Register SIP with given arguments.
   10.sql = SELECT 'U:uid=12345&arg1=Hello&arg2=World|s|r:8' AS '_link|col1|_hide'

   # Build JS
   10.tail = <script>
       console.log('start api request');
       $.ajax({
       url: 'typo3conf/ext/qfq/Classes/Api/dataReport.php?s={{&col1:RE}}',
       data: {arg3:456, arg4:567},
       method: 'POST',
       dataType: 'TEXT',
       success: function(response, status, jqxhr) {console.log(response); console.log(jqxhr.getAllResponseHeaders());},
       error: function(jqXHR, textStatus, errorThrown) {console.log(jqXHR.responseText, textStatus, errorThrown);}
       });
     </script>

Example QFQ record called by above AJAX::

    # Create a dedicated tt-content record (on any T3 page, might be on the same page as the JS code).
    # The example above assumes that this record has the tt_content.uid=12345.
    render = api
    10.sql = SELECT '{{arg1:S}} {{arg2:S}} {{arg3:C}} {{arg4:C}}', NOW()
    , 'Content-Type: application/json\ncustom-header: fooBar' AS _apiResponseHeader

Example text returned by the above AJAX call::

   Hello World 456 5672020-09-22 18:09:47

.. _rest_client:

REST Client
^^^^^^^^^^^

.. note::

  POST and GET data to external REST interfaces or other API services.

Access to external services via HTTP / HTTPS is triggered via special column name *restClient*.
QFQ uses the php stream interface for the API calls. Because of that, allow_url_fopen needs to be set to 1 on the installation.
The received data can be processed in subsequent calls.

Example::

  # Retrieve information. Received data is delivered in JSON and decoded / copied on the fly to CLIENT store (CLIENT store is emptied beforehand)
  10.sql = SELECT 'n:https://www.dummy.ord/rest/person/id/123' AS _restClient
  20.sql = SELECT 'Status: {{http-status:C}}<br>Name: {{name:C:alnumx}}<br>Surname: {{surname:C:alnumx}}'

  # Simple POST request via https. Result is printed on the page.
  10.sql = SELECT 'n:https://www.dummy.ord/rest/person/id/123|method:POST|content:{"name":"John";"surname":"Doe"}' AS _restClient

+-------------------+----------------------------------------------------+--------------------------------------------------------+
| Token             | Example                                            | Comment                                                |
+===================+================================+============================================================================+
| n                 | n:https://www.dummy.ord/rest/person                |                                                        |
+-------------------+----------------------------------------------------+--------------------------------------------------------+
| method            | method:POST                                        | GET, POST, PUT or DELETE                               |
+-------------------+----------------------------------------------------+--------------------------------------------------------+
| content           | content:{"name":"John";"surname":"Doe"}            | Depending on the REST server JSON might be expected    |
+-------------------+----------------------------------------------------+--------------------------------------------------------+
| contentFile       | contentFile:fileadmin/_temp_/data.txt              | Replaces content, if given. Recommended for large data |
|                   |                                                    | sections, such as binary data for files.               |
+-------------------+----------------------------------------------------+--------------------------------------------------------+
| header            | *see below*                                        |                                                        |
+-------------------+----------------------------------------------------+--------------------------------------------------------+
| timeout           | timeout:5                                          | Default: 5 seconds.                                    |
+-------------------+----------------------------------------------------+--------------------------------------------------------+

**Header**

* Each header must be separated by ``\r\n`` or `\n`.
* An explicit given header will overwrite the named default header.
* Default header:

  * *content-type: application/json* - if *content* starts with a ``{``.
  * *content-type: text/plain* - if *content* does not start with a ``{``.
  * *connection: close* - Necessary for HTTP 1.1.

* Basic Authorization Example

Warning: Only use base64 for SSL encrypted connections::

  10.sql = SELECT CONCAT('n:https://sample.com/id/1234|header:Authorization: Basic ', TO_BASE64('{{username}}:{{password}}') )

**Result received**

* After a *REST client* call is fired, QFQ will wait up to *timeout* seconds for the answer.
* By default, the whole received answer will be shown. To suppress the output: ``... AS '_restClient|_hide'``
* The variable ``{{http-status:C}}`` shows the `HTTP status code<https://en.wikipedia.org/wiki/List_of_HTTP_status_codes>`_.
  A value starting with '2..' shows success.
* In case of an error, the HTTP status code is set to 0 and ``{{error-message:C:allbut}}`` shows some details.
* In case the returned answer is a valid JSON string, it is flattened and automatically copied to STORE_CLIENT with corresponding key names.
   * NOTE: The CLIENT store is emptied beforehand!

JSON answer example::

  Answer from Server:  { 'name' : 'John', 'address' : {'city' : 'Bern'} }
  Retrieve the values via:  {{name:C:alnumx}}, {{city:C:alnumx}}

.. _special-sql-functions:

Special SQL Functions (prepared statements)
-------------------------------------------

.. _qbar-escape-qfq-delimiter:

QBAR: Escape QFQ Delimiter
^^^^^^^^^^^^^^^^^^^^^^^^^^

The SQL function QBAR(text) replaces "|" with "\\|" in `text` to prevent conflicts with the QFQ special column notation.
In general this function should be used when there is a chance that unplanned '|'-characters occur.

Example::

    10.sql = SELECT CONCAT('p:notes|t:Information: ', QBAR(Note.title), '|b') AS _link FROM Note

In case 'Note.title' contains a '|' (like 'fruit | numbers'), it will confuse the '... AS _link' class. Therefore it's
necessary to 'escape' (adding a '\' in front of the problematic character) the bar which is done by using ``QBAR()``.

.. _qbar-escape-qfq-colon-coma:

QCC: Escape colon / coma
^^^^^^^^^^^^^^^^^^^^^^^^

The SQL function QCC(text) replaces ":" with "\\:" and "," with "\\," in `text` to prevent conflicts with the QFQ notation.

.. _qnl2br:

QNL2BR: Convert newline to HTML '<br>'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The SQL function QNL2BR(text) replaces `LF` or `CR/LF` by `<br>`. This can be used for data (containing LF) to output
on a HTML page with correctly displayed linefeed.

Example::

    10.sql = SELECT QNL2BR(Note.title) FROM Note

One possibility how `LF` comes into the database is with form elements of type `textarea` if the user presses `enter` inside.

.. _qnbsp:

QNBSP: Convert space to '&nbsp;'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The SQL function QNBSP(text) replaces ` ` (space) by `&nbsp;`. This prevents unwanted line breaks in text.
E.g. the title 'Prof. Dr.' should never be breaked: QNBSP('Prof. Dr.')

Example::

    10.sql = SELECT QNBSP(Person.title) FROM Person


.. _qleft-truncate-long-text:

QLEFT: Left truncate text, if cutted: add '...'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The SQL function QLEFT(text, n) is like LEFT(text, n), but adds '...' at the end if the text is cutted.
Example::

    10.sql = SELECT QLEFT("abcdefghij", 6)

Output::

    abcdef...

.. _qright-truncate-long-text:

QLEFT: Right truncate text, if cutted: insert '...'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The SQL function QRIGHT(text, n) is like RIGHT(text, n), but insert '...' at the beginning if the text is cutted.
Example::

    10.sql = SELECT QRIGHT("abcdefghij", 6)

Output::

    ...efghij


.. _qmore-truncate-long-text:

QMORE: Truncate Long Text - click for more/less
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The SQL function QMORE(text, n) truncates `text` if it is longer than `n` characters and adds a "more.." button. If the "more..."
button is clicked, the whole text is displayed. The stored procedure QMORE() will inject some HTML/CSS code.

Example::

    10.sql = SELECT QMORE("This is a text which is longer than 10 characters", 10)

Output::

  This is a `more..`

.. _qifempty:

QIFEMPTY: if empty show token
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The SQL function QIFEMPTY(input, token) returns 'token' if 'input' is 'empty string' / '0' / '0000-00-00' / '0000-00-00 00:00:00'.

Example::

    10.sql = SELECT QIFEMPTY('hello world','+'), QIFEMPTY('','-')

Output::

  hello world-

.. _qdate_format:

QDATE_FORMAT: format a timestamp, show '-' if empty
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The SQL function QDATE_FORMAT(timestamp) returns 'dd.mm.YYYY hh:mm', if 'timestamp' is 0 returns '-'

Example::

    10.sql = SELECT QDATE_FORMAT( '2019-12-31 23:55:41' ), ' / ', QDATE_FORMAT( 0 ), ' / ', QDATE_FORMAT( '' )

Output::

  31.12.2019 23:55 / - / -