Report.rst

(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 id|alias>[&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_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][|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.
* 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::

    10.sql = SELECT "F:fileadmin/test.pdf" as _pdf,  "F:fileadmin/test.pdf" as _file,  "F:fileadmin/test.pdf" as _zip
    10.sql = SELECT "p:id=export&r=1" as _pdf,  "p:id=export&r=1" as _file,  "p:id=export&r=1" as _zip

    10.sql = SELECT "t:Download PDF|F:fileadmin/test.pdf" as _pdf,  "t:Download PDF|F:fileadmin/test.pdf" as _file,  "t:Download ZIP|F:fileadmin/test.pdf" as _zip
    10.sql = SELECT "t:Download PDF|p:id=export&r=1" as _pdf,  "t:Download PDF|p:id=export&r=1" as _file,  "t:Download ZIP|p:id=export&r=1" as _zip

    10.sql = SELECT "d:complete.pdf|t:Download PDF|F:fileadmin/test1.pdf|F:fileadmin/test2.pdf" as _pdf, "d:complete.zip|t:Download ZIP|F:fileadmin/test1.pdf|F:fileadmin/test2.pdf" as _zip

    10.sql = SELECT "d:complete.pdf|t:Download PDF|F:fileadmin/test.pdf|p:id=export&r=1|u:www.example.com" AS _pdf

.. _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 otional. 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                  |
+-------------+-------------------------------------------------------------------------------------------+---------------------------+

.. _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/Api/dataReport.php``

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

Example::

    # Register SIP with given arguments.
    10.sql = SELECT 'U:uid=12345&arg1=Hello&arg2=World|s|r:8' AS '_link|col1'
    # Build JS
    10.tail = <script>
      function writeYourOwnAjax(){
        $.ajax({
        url: 'typo3conf/ext/qfq/Api/dataReport.php',
        data: {s:{{col1:RE}}, arg3:45678},
        method: 'POST',
        dataType: 'JSON',
        success: function(response) {ajaxSuccess(response);},
        error: function(jqXHR, textStatus, errorThrown) {ajaxError(jqXHR, textStatus, errorThrown);}
        });
        }
      </script>

    # 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}}', NOW()


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

.. _qmore-truncate-long-text:

QMORE: Truncate Long Text - 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 / - / -

.. _qlugify:

QSLUGIFY: clean a string
^^^^^^^^^^^^^^^^^^^^^^^^

Convert a string to only use alphanumerical characters and '-'. Characters with accent will be replaced without the accent.
Non alphanumerical characters are stripped off. Spaces are replaced by '-'. All characters are lowercase.

Example::

    10.sql = SELECT QSLUGIFY('abcd ABCD ae.ä.oe.ö.ue.ü z[]{}()<>.,?Z')

Output::

  abcd-abcd-ae-a-oe-o-ue-u-z-z

.. _strip_tags:

strip_tags: strip html tags
^^^^^^^^^^^^^^^^^^^^^^^^^^^

The SQL function strip_tags(input) returns 'input' without any HTML tags.

Example::

   10.sql = SELECT strip_tags('<a href="https://example.com"><b>my name</b> <i>is john</i></a> - end of sentence')

Output::

  my name is john - end of sentence

.. _download:

Download
--------

Download offers:

* Single file - download a single file (any type),
* PDF create - one or concatenate several files (uploaded) and/or web pages (=HTML to PDF) into one PDF output file,
* ZIP archive - filled with several files ('uploaded' or 'HTML to PDF'-converted).
* Excel - created from scratch or fill a template xlsx with database values.

The downloads are SIP protected. Only the current user can use the link to download files.

By using the `_link` column name:

* the option `d:...` initiate creating the download link and optional specifies an export filename,
* the optional `M:...` (Mode) specifies the export type (file, pdf, zip, export),
* setting `s:1` is recommended for the download function (file / path name is hidden to the user),
* the alttext `a:...` specifies a message in the download popup.

By using `_pdf`,  `_Pdf`, `_file`, `_File`, `_zip`, `_Zip`, `_excel` as column name, the options `d`, `M` and `s`
will be set.

All files will be read by PHP - therefore the directory might be protected against direct web access. This is the
preferred option to offer secure downloads via QFQ.

In case the download needs a persistant URL (no SIP, no user session), a regular
link, pointing directly to a file, have to be used - the download functionality described here is not appropriate for
such a scenario. If necessary, :ref:`column-save-pdf` can be used to generate such a file.

.. _download-parameter-files:

Parameter and (element) sources
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

* *download*: `d[:<exportFilename>]`

  * *exportFilename* = <filename for save as> - Name, offered in the 'File save as' browser dialog. Default: 'output.<ext>'.

    If there is no `exportFilename` defined, then the original filename is taken (if there is one, else: output...).

    The user typically expects meaningful and distinct file names for different download links.

* *popupMessage*: `a:<text>` - will be displayed in the popup window during download. If the creating/download is fast, the window might disappear quickly.

* *mode*: `M:<mode>`

  * *mode* = <file | pdf | zip | excel>

    * If `M:file`, the mime type is derived dynamically from the specified file. In this mode, only one element source
      is allowed per download link (no concatenation).

    * In case of multiple element sources, only `pdf`, `zip` and `excel` (template mode) is supported.
    * If `M:zip` is used together with `p:...`, `U:...` or `u:..`, those HTML pages will be converted to PDF. Those files
      get generic filenames inside the archive.
    * If not specified, the **default** 'Mode' depends on the number of specified element sources (=file or web page):

      * If only one `file` is specified, the default is `file`.
      * If there is a) a page defined or b) multiple elements, the default is `pdf`.

* *element sources* - for `M:pdf` or `M:zip`, all of the following element sources may be specified multiple times.
  Any combination and order of these options are allowed.

  * *file*: `F:<pathFileName>` - relative or absolute pathFileName offered for a) download (single), or to be concatenated
    in a PDF or ZIP.
  * *page*: `p:id=<t3 page>&<key 1>=<value 1>&<key 2>=<value 2>&...&<key n>=<value n>`.

    * By default, the options given to wkhtml will *not* be encoded by a SIP!
    * To encode the parameter via SIP: Add '_sip=1' to the URL GET parameter.

      E.g. `p:id=form&_sip=1&form=Person&r=1`.

      In that way, specific sources for the `download` might be SIP encrypted.

    * Any current HTML cookies will be forwarded to/via `wkhtml`. This includes the current FE Login as well as any
      QFQ session. Also the current User-Agent are faked via the `wkhtml` page request.

    * If there are trouble with accessing FE_GROUP protected content, please check :ref:`wkhtmltopdf<wkhtml>`.

  * *url*: `u:<url>` - any URL, pointing to an internal or external destination.

  * *uid*: `uid:<tt-content record id>` - the tt_content.uid of a QFQ PageContent record (shown on hover in the backend). This will render
    only the specified  QFQ content record, without any Typo3 layout elements (Menu, Body,...)
    QFQ will retrieve the tt-content's bodytext from the Typo3 database, parse it, and render it as a PDF. Parameters can be
    passed: `uid:<tt-content record id>[&arg1=value1][&arg2=value2][...]` and will be available in the SIP store for the QFQ PageContent,
    or passed as wkhtmltopdf arguments, if applicable.

  * *WKHTML Options* for `page`, `urlParam` or `url`:

    * The 'HTML to PDF' will be done via `wkhtmltopdf`.
    * All possible options, suitable for `wkhtmltopdf`, can be submitted in the `p:...`, `u:...` or `U:...` element source.
      Check `wkhtmltopdf.txt <https://wkhtmltopdf.org/usage/wkhtmltopdf.txt>`_ for possible options. Be aware that
      key/value tuple in the  documentation is separated by a space, but to respect the QFQ key/value notation of URLs,
      the key/value tuple in `p:...`, `u:...` or `U:...` has to be separated by '='. Please see last example below.
    * If an option contains an '&' it must be escaped with double \\ . See example.

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

Example `_link`: ::

  # single `file`. Specifying a popup message window text is not necessary, cause a file directly accessed is fast.
  SELECT "d:file.pdf|s|t:Download|F:fileadmin/pdf/test.pdf" AS _link

  # single `file`, with mode
  SELECT "d:file.pdf|M:pdf|s|t:Download|F:fileadmin/pdf/test.pdf" AS _link

  # three sources: two pages and one file
  SELECT "d:complete.pdf|s|t:Complete PDF|p:id=detail&r=1|p:id=detail2&r=1|F:fileadmin/pdf/test.pdf" AS _link

  # three sources: two pages and one file
  SELECT "d:complete.pdf|s|t:Complete PDF|p:id=detail&r=1|p:id=detail2&r=1|F:fileadmin/pdf/test.pdf" AS _link

  # three sources: two pages and one file, parameter to wkhtml will be SIP encoded
  SELECT "d:complete.pdf|s|t:Complete PDF|p:id=detail&r=1&_sip=1|p:id=detail2&r=1&_sip=1|F:fileadmin/pdf/test.pdf" AS _link

  # three sources: two pages and one file, the second page will be in landscape and pagesize A3
  SELECT "d:complete.pdf|s|t:Complete PDF|p:id=detail&r=1|p:id=detail2&r=1&--orientation=Landscape&--page-size=A3|F:fileadmin/pdf/test.pdf" AS _link

  # One source and a header file. Note: the parameter to the header URL is escaped with double backslash.
  SELECT "d:complete.pdf|s|t:Complete PDF|p:id=detail2&r=1&--orientation=Landscape&--header={{URL:R}}?indexp.php?id=head\\&L=1|F:fileadmin/pdf/test.pdf" AS _link

..

Example `_pdf`, `_zip`: ::

  # File 1: p:id=1&--orientation=Landscape&--page-size=A3
  # File 2: p:id=form
  # File 3: F:fileadmin/file.pdf
  SELECT 't:PDF|a:Creating a new PDF|p:id=1&--orientation=Landscape&--page-size=A3|p:id=form|F:fileadmin/file.pdf' AS _pdf

  # File 1: p:id=1
  # File 2: u:http://www.example.com
  # File 3: F:fileadmin/file.pdf
  SELECT 't:PDF - 3 Files|a:Please be patient|p:id=1|u:http://www.example.com|F:fileadmin/file.pdf' AS _pdf

  # File 1: p:id=1
  # File 2: p:id=form
  # File 3: F:fileadmin/file.pdf
  SELECT CONCAT('t:ZIP - 3 Pages|a:Please be patient|p:id=1|p:id=form|F:', p.pathFileName) AS _zip

..

Use the `--print-media-type` as wkhtml option to access the page with media type 'printer'. Depending on the website
configuration this switches off navigation and background images.



Rendering PDF letters
^^^^^^^^^^^^^^^^^^^^^

`wkhtmltopdf`, with the header and footer options, can be used to render multi page PDF letters (repeating header,
pagination) in combination with dynamic content. Such PDFs might look-alike official letters, together with logo and signature.

Best practice:

#. Create a clean (=no menu, no website layout) letter layout in a separated T3 branch: ::

      page = PAGE
      page.typeNum = 0
      page.includeCSS {
        10 = typo3conf/ext/qfq/Resources/Public/Css/qfq-letter.css
      }

      // Grant access to any logged in user or specific development IPs
      [usergroup = *] || [IP = 127.0.0.1,192.168.1.* ]
        page.10 < styles.content.get
      [else]
        page.10 = TEXT
        page.10.value = access forbidden
      [global]

#. Create a T3 `body` page (e.g. page alias: 'letterbody') with some content. Example static HTML content: ::

      <div class="letter-receiver">
        <p>Address</p>
      </div>
      <div class="letter-sender">
       <p><b>firstName name</b><br>
        Phone +00 00 000 00 00<br>
        Fax +00 00 000 00 00<br>
       </p>
      </div>

      <div class="letter-date">
        Zurich, 01.12.2017
      </div>

      <div class="letter-body">
       <h1>Subject</h1>

       <p>Dear Mrs...</p>
       <p>Lucas ipsum dolor sit amet organa solo skywalker darth c-3p0 anakin jabba mara greedo skywalker.</p>

       <div class="letter-no-break">
       <p>Regards</p>
       <p>Company</p>
       <img class="letter-signature" src="">
       <p>Firstname Name<br>Function</p>
       </div>
      </div>

#. Create a T3 letter-`header` page (e.g. page alias: 'letterheader') , with only the header information: ::

        <header>
        <img src="fileadmin/logo.png" class="letter-logo">

        <div class="letter-unit">
          <p class="letter-title">Department</p>
          <p>
           Company name<br>
           Company department<br>
           Street<br>
           City
          </p>
        </div>
        </header>

#. Create a) a link (Report) to the PDF letter or b) attach the PDF (on the fly rendered) to a mail. Both will call the
   `wkhtml` via the `download` mode and forwards the necessary parameter.

Use in `report`::

  sql = SELECT CONCAT('d:Letter.pdf|t:',p.firstName, ' ', p.name
                       , '|p:id=letterbody&pId=', p.id, '&_sip=1'
                       , '&--margin-top=50mm'
                       , '&--header-html={{BASE_URL_PRINT:Y}}?id=letterheader'

                       # IMPORTANT: set margin-bottom to make the footer visible!
                       , '&--margin-bottom=20mm'
                       , '&--footer-right="Seite: [page]/[toPage]"'
                       , '&--footer-font-size=8&--footer-spacing=10') AS _pdf

                FROM Person AS p ORDER BY p.id


Sendmail. Parameter: ::

  sendMailAttachment={{SELECT 'd:Letter.pdf|t:', p.firstName, ' ', p.name, '|p:id=letterbody&pId=', p.id, '&_sip=1&--margin-top=50mm&--margin-bottom=20mm&--header-html={{BASE_URL_PRINT:Y}}?id=letterheader&--footer-right="Seite: [page]/[toPage]"&--footer-font-size=8&--footer-spacing=10' FROM Person AS p WHERE p.id={{id:S}} }}

Replace the static content elements from 2. and 3. by QFQ Content elements as needed::