Report.rst

.. _qslugify:

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

.. _qent_squote:

QENT_SQUOTE: convert single tick to HTML entity &apos;
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Convert all single ticks in a string to the HTML entity "&apos;"

Example::

  10.sql = SELECT QENT_SQUOTE("John's car")

Output::

  John&apos;s car

.. _qent_dquote:

QENT_DQUOTE: convert double tick to HTML entity &quot;
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Convert all double ticks in a string to the HTML entity "&quot;"

Example::

  10.sql = SELECT QENT_SQUOTE('A "nice" event')

Output::

  A &quot;nice&quot; event

.. _qesc_squote:

QESC_SQUOTE: escape single tick
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Escape all single ticks with a backslash. Double escaped single ticks (two backslashes) will be replaced by a single
escaped single tick.

Example::

  Be Music.style = "Rock'n' Roll"
  10.sql = SELECT QESC_SQUOTE(style) FROM Music

Output::

  Rock\'n\'n Roll

.. _qesc_dquote:

QESC_DQUOTE: escape double tick
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Escape all double ticks with a backslash. Double escaped double ticks (two backslashes) will replaced by a single
escaped double tick.

Example::

  Set Comment.note = 'A "nice" event'
  10.sql = SELECT QESC_DQUOTE(style) FROM Music

Output::

  Rock\'n\'n Roll


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

.. _qfq_function:

QFQ Function
------------

QFQ SQL reports can be reused, similar to a function in a regular programming language, including call parameter and return
values.

* Per tt-content record the field 'subheader' (in Typo3 backend) defines the function name. The function
  can also referenced by using the tt-content number (`uid`) - but this is less readable.
* The calling report calls the function by defining ``<level>.function = <function name>(var1, var2, ...) => return1, return2, ...``
* STORE_RECORD will be saved before calling the function and will be restored when the function has been finished.
* The function has only access to STORE_RECORD variables which has been explicitly defined in the braces (var1, var2, ...).
* The function return values will be copied to STORE_RECORD after the function finished.
* Inside the QFQ function, all other STORES are fully available.
* If ``<level>.function`` and ``<level>.sql`` are both given, ``<level>.function`` is processed first.
* If ``<level>.function`` is given, but ``<level>.sql`` not, the values of ``shead, stail, althead`` are even processed.
* If a function outputs something, this is *not* shown.
* The output of a QFQ function is accessible via ``{{_output:R}}``.
* It is possible to call functions inside of a function.
* If ``render = api`` is defined in the function, both tt-content records can be saved on the *same* page and won't interfere.
* FE Groups are not respected - don't use them on QFQ functions.

Example tt-content record for the function::

  Subheader: getFirstName
  Code:
        #
        # {{pId:R}}
        #
        render = api

        100 {
        sql = SELECT p.firstName AS _firstName
                     , NOW() AS now
                     , CONCAT('p:{{pageAlias:T}}&form=person&r=', p.id ) AS '_pagee|_hide|myLink'
                FROM Person AS p
                WHERE p.id={{pId:R}}
        }


Example tt-content record for the calling report::

    #
    # Example how to use `<level>.function = ...`
    #

    10 {
        sql = SELECT p.id AS _pId, p.name FROM Person AS p ORDER BY p.name
        head = <table class="table"><tr><th>Name</th><th>Firstname</th><th>Link (final)</th><th>Link (source)</th><th>NOW() (via Output)</th></tr>
        tail = </table>
        rbeg = <tr>
        renr = </tr>
        fbeg = <td>
        fend = </td>

        20 {
            function = getFirstName(pId) => firstName, myLink
        }

        30 {
            sql = SELECT '{{firstName:R}}', "{{myLink:R}}", "{{&myLink:R}}", '{{_output:R}}'
            fbeg = <td>
            fend = </td>
        }
    }

Explanation:

* Level 10 iterates over all `person`.
* Level 10.20 calls QFQ function `getFirstName()` by delivering the `pId` via STORE_RECORD. The function expects the return
  value `firstName` and `myLink`.
* The function selects in level 100 the person given by ``{{pId:R}}``. The `firstName` is not printed but a hidden column.
  Column ``now`` is printed. Column 'myLink' is a rendered link, but not printed.
* Level 10.30 prints the return values ``firstName`` and ``myLink`` (as rendered link and as source definition). The last
  column is the output of the function - the value of ``NOW()``

.. tip::

If there are STORE_RECORD variables which can't be replaced: typically they have been not defined as function parameter
or return values.

.. _download:

Download
--------

+--------------------+--------------------------------------------------------+----------------------------------------+
| Mode               | Security                                               | Note                                   |
+====================+========================================================+========================================+
| Direct File access | | Files are public available. No access restriction    | | Use ``<a href="...">``               |
|                    | | Pro: Simple, links can be copied.                    | | Merge multiple sources: no           |
|                    | | Con: Directory access, guess of filenames, only      | | but check :ref:`column-save-pdf`     |
|                    | | removing the file will deny access.                  | | Custom 'save as filename': no        |
+--------------------+--------------------------------------------------------+----------------------------------------+
| Persistent Link    | | Access is be defined by a SQL statement. In *T3/BE   | | Use ``..., 'd:1234|s:0' AS _link``   |
|                    | | > Extension > QFQ > File > download* define a SQL    | | Merge multiple sources: yes          |
|                    | | statement.                                           | | Custom 'save as filename': yes       |
|                    | | Pro: speaking URL, link can be copied, access can    |                                        |
|                    | | can be defined a SQL statement.                      |                                        |
|                    | | Con: **Key might be altered by user**, permission    |                                        |
|                    | | can't be user logged in dependent.                   |                                        |
+--------------------+--------------------------------------------------------+----------------------------------------+
| Secure Link        | | **Default**. SIP protected link.                     | | Use ``..., 'd|F:file.pdf' AS _link`` |
|                    | | Pro: Parameter can't be altered, most easy definition| | Merge multiple sources: yes          |
|                    | | in QFQ, access might be logged in user dependent.    | | Custom 'save as filename': yes       |
|                    | | Cons: Links are assigned to a browser session and    |                                        |
|                    | | can't be copied                                      |                                        |
+--------------------+--------------------------------------------------------+----------------------------------------+

The rest of this section applies only to `Persistent Link` and `Secure Link`. 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.

By using the ``_link`` column name:

* the option ``d:...`` initiate creating the download link and optional specifies

  * in ``SIP`` mode: an export filename (),
  * in ``persistent link`` mode: path download script (optional) and key(s) to identify the record with the PathFilename
    information (see below).

* the optional ``M:...`` (Mode) specifies the export type (file, pdf, qfqpdf, zip, export),
* 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` (pdf: wkhtml) 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. Check `secure-direct-file-access`_.

.. _download-parameter-files:

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

* mode `secure link` (s:1) - *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.

* mode `persistent link` (s:0) - *download*:  `d:[<path/name>]<key1>[/<keyN>]`

  This setup is divided in part a) and b):

  Part a) - offering the download link.

  * The whole part a) is optional. The download itself will work without it.

  * (Optional) *path/name* = of the QFQ `download.php` script. By default``typo3conf/ext/qfq/Classes/Api/download.php``.
    Three further possibilities: ``dl.php`` or ``dl2.php`` or ``dl3.php`` (see below).

  * *key1* = give a uniq identifier to select the wished record.

  Part b) - process the download

  * In the QFQ extension config: File > Query for direct download mode: `download.php` or `dl.php` or `dl2.php` or `dl3.php`
    up to 4 different SQL statements can be given with the regular QFQ download link syntax (skip the visual elements
    like button, text, glyph icon, question,...)::

        SELECT CONCAT('d|F:', n.pathFileName) FROM Note AS n WHERE n.id=?

    All `?` in the SQL statement will be replaced by the specified parameter. If there are more `?` than parameter,
    the last parameter will be reused for all pending `?`.

    E.g. ``10.sql = SELECT 'd:1234|t:File.pdf' AS _link`` creates a link
    ``<a href="typo3conf/ext/qfq/Classes/Api/download.php/1234"><span class="btn btn-default">File.pdf</span></span>``.
    If the user clicks on the link, QFQ will extract the `1234` argument and via ``download.php`` the query (defined in
    the Typo QFQ extension config) will be prepared and fires ``SELECT CONCAT('d|F:', n.pathFileName, '|t:File.pdf') FROM Note AS n WHERE n.id=1234``.
    The download of the file, specified by ``n.pathFileName``, will start.

    If no record ist selected, a custom error will be shown. If the query selectes more than one record, a general error will be shown.

    If one of ``dl.php`` or ``dl2.php`` or ``dl3.php`` should be used, please initially create the symlink(s), e.g. in the
    application directory (same level as typo3conf) ``ln -s typo3conf/ext/qfq/Classes/Api/download.php dl.php`` (or dl2.ph, dl3.php).

  Speaking URL)

  Instead of using a numeric value reference key, also a text can be used. Always take care that exactly one record is
  selected. The key is transferred by URL therefore untrusted: The sanitize class :ref:`alnumx<sanitize-class>` is applied.
  Example::

        Query: SELECT CONCAT('d|F:', n.pathFileName) FROM Person AS p WHERE p.name=? AND p.firstName=? AND p.publish='yes'
        Link:  https://example.com/dl.php/doe/john


* *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 | qfqpdf | zip | excel>

    * pdf: `wkhtml` will be used to render the pdf.
    * qfqpdf: `qfqpdf` will be used to render the pdf.

    * 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``, ``M:qfqpdf`` 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:<function name>`` - the output is treated as HTML (will be converted to PDF) or EXCEL data.

    * The called tt-content record is identified by `function name`, specified in the subheader field. Optional
      the numeric id of the tt-content record (=uid) can be given.
    * Only the specified  QFQ content record will be rendered, 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 or Execl data.
    * Parameters can be passed: ``uid:<tt-content record id>[&arg1=value1][&arg2=value2][...]`` and will be available via
      STORE_SIP in the QFQ PageContent, or passed as wkhtmltopdf arguments, if applicable.
    * For more obviously structuring, put the additional tt-content record on the same Typo3 page (where the QFQ
      tt-content record is located which produces the link) and specify ``render = api`` (`report-render`_).

  * *source*: ``source:<function name>[&arg1=value1][&arg2=value2][&...]`` - (similar to a `uid`) the output is treated
    as further sources. Example result reported by *function name* might be: ``F:file.pdf1|uid:myData&arg=2|...``

    * Use this functionality to define a *central managed download* function, which can be reused anywhere by just specify the
      *function name* and required arguments.
    * The called tt-content record is identified by `function name`, specified in the subheader field. Optional
      the numeric id of the tt-content record (=uid) can be given.
    * The output of the tt-content record will be treated as further source arguments. Nothing else than valid source
      references should be printed. Separate the references as usual by '|'.
    * The supplied arguments are available via STORE_SIP (this is different from `qfq_function`_).
    * Tip: For more obviously structuring, put the additional tt-content record on the same Typo3 page (where the QFQ
      tt-content record is located which produces the link) and specify ``render = api`` (`report-render`_).

  * 'M:pdf' - *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.

  * 'M:qfqpdf' - *qfqpdf Options* for `page`, `urlParam` or `url`:

    * The 'HTML to PDF' will be done via `qfqpdf`.
    * Check https://puppeteer.github.io/puppeteer and https://git.math.uzh.ch/bbaer/qfqpdf/-/tree/master
    * All possible options, suitable for `qfqpdf`, can be submitted in the `p:...`, `u:...` or `U:...` element source.
      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.
    * Page numbering is done via HTML templating / CSS classes:  ``--header-template '<div style="font-size:5mm;" class="pageNumber"></div>'``

  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

  # qfqpdf - three sources: two pages and one file
  SELECT "d:complete.pdf|M:qfqpdf|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

  # One indirect source reference
  SELECT "d:complete.pdf|s|t:Complete PDF|source:centralPdf&pId=1234" AS _link

  An additional tt-content record is defined with `sub header: centralPdf`. One or multiple attachments might be concatenated.
  10.sql = SELECT '|F:', a.pathFileName FROM Attachments AS a WHERE a.pId={{pId:S}}

..

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

  10.sql = SELECT '<div class="letter-receiver"><p>', p.name AS '_+br', p.street AS '_+br', p.city AS '_+br', '</p>'
             FROM Person AS p
             WHERE p.id={{pId:S}}


Export area
^^^^^^^^^^^

This description might be interesting if a page can't be protected by SIP.

To offer protected pages, e.g. directly referenced files (remember: this is not recommended) in download links, the
regular FE_GROUPs can't be used, cause the download does not have the current user privileges (it's a separate process,
started as the webserver user).

Create a separated export tree in Typo3 Backend, which is IP access restricted. Only localhost or the FE_GROUP 'admin'
is allowed to access: ::

   tmp.restrictedIPRange = 127.0.0.1,::1
   [IP = {$tmp.restrictedIPRange} ][usergroup = admin]
      page.10 < styles.content.get
   [else]
      page.10 = TEXT
      page.10.value = Please access from localhost or log in as 'admin' user.
   [global]

.. _excel-export:

Excel export
^^^^^^^^^^^^

This chapter explains how to create Excel files on the fly.

Hint: For just up/downloading of excel files (without modification), check the generic Form
:ref:`input-upload` element and the report 'download' (`column_pdf`_) function.

The Excel file is build in the moment when the user request it, by clicking on a
download link.

Mode building:

* `New`: The export file will be completely build from scratch.
* `Template`: The export file is based on an earlier uploaded xlsx file (template). The template itself is unchanged.

Injecting data into the Excel file is done in the same way in both modes: a Typo3 page (rendered without any HTML header
or tags) contains one or more Typo3 QFQ records. Those QFQ records will create plain ASCII output.

If the export file has to be customized (colors, pictures, headlines, ...), the `Template` mode is the preferred option.
It's much easier to do all customizations via Excel and creating a template than by coding in QFQ / Excel export notation.

Setup
"""""

* Create a special column name `_excel` (or `_link`) in QFQ/Report. As a source, define a T3 PageContent, which has to
  deliver the dynamic content (also :ref:`excel-export-sample<excel-export-sample>`). ::

    SELECT CONCAT('d:final.xlsx|M:excel|s:1|t:Excel (new)|uid:<tt-content record id>') AS _link

* Create a T3 PageContent which delivers the content.

  * It is recommended to use the ``uid:<tt-content record id>`` syntax for excel imports, because there should be no html code on the
    resulting content. QFQ will retrieve the PageContent's bodytext from the Typo3 database, parse it, and pass the
    result as the instructions for filling the excel file.
  * Parameters can be passed: ``uid:<tt-content record id>?param=<value1>&param2=<value2>`` and will be accessible in the SIP Store (S) in the
    QFQ PageContent.
  * Use the regular QFQ Report syntax to create output.
  * The newline at the end of every line needs to be CHAR(10). To make it simpler, the special column name ``... AS _XLS``
    (see _XLS, _XLSs, _XLSb, _XLSn) can be used.
  * One option per line.
  * Empty lines will be skipped.
  * Lines starting with '#' will be skipped (comments). Inline comment signs are NOT recognized as comment sign.
  * Separate <keyword> and <value> by '='.

+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| Keyword     | Example              | Description                                                                                       |
+=============+======================+===================================================================================================+
| 'worksheet' | worksheet=main       | Select a worksheet in case the excel file has multiple of them.                                   |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| 'mode'      | mode=insert          | Values: insert,overwrite.                                                                         |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| 'position'  | position=A1          | Default is 'A1'. Use the excel notation.                                                          |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| 'newline'   | newline              | Start a new row. The column will be the one of the last 'position' statement.                     |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| 'str', 's'  | s=hello world        | Set the given string on the given position. The current position will be shift one to the right.  |
|             |                      | If the string contains newlines, option'b' (base64) should be used.                               |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| 'b'         | b=aGVsbG8gd29ybGQK   | Same as 's', but the given string has to Base64 encoded and will be decoded before export.        |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| 'n'         | n=123                | Set number on the given position. The current position will be shift one to the right.            |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+
| 'f'         | f==SUM(A5:C6)        | Set a formula on the given position. The current position will be shift one to the right.         |
+-------------+----------------------+---------------------------------------------------------------------------------------------------+

Create a output like this: ::

    position=D11
    s=Hello
    s=World
    s=First Line
    newline
    s=Second line
    n=123

This fills D11, E11, F11, D12

In Report Syntax::

    # With ... AS _XLS (token explicit given)
    10.sql = SELECT 'position=D10' AS _XLS
                    , 's=Hello' AS _XLS
                    , 's=World' AS _XLS
                    , 's=First Line' AS _XLS
                    , 'newline' AS _XLS
                    , 's=Second line' AS _XLS
                    , 'n=123' AS _XLS

    # With ... AS _XLSs (token generated internally)
    20.sql = SELECT 'position=D20' AS _XLS
                    , 'Hello' AS _XLSs
                    , 'World' AS _XLSs
                    , 'First Line' AS _XLSs
                    , 'newline' AS _XLS
                    , 'Second line' AS _XLSs
                    , 'n=123' AS _XLS

    # With ... AS _XLSb (token generated internally and content is base64 encoded)
    30.sql = SELECT 'position=D30' AS _XLS
                    , '<some content with special characters like newline/carriage return>' AS _XLSb

.. _`excel-export-sample`:

Excel export samples (54 is a example <tt-content record id>)::

    # From scratch (both are the same, one with '_excel' the other with '_link')
    SELECT CONCAT('d:new.xlsx|t:Excel (new)|uid:54') AS _excel
    SELECT CONCAT('d:new.xlsx|t:Excel (new)|uid:54|M:excel|s:1') AS _link

    # Template
    SELECT CONCAT('d:final.xlsx|t:Excel (template)|F:fileadmin/template.xlsx|uid:54') AS _excel

    # With parameter (via SIP) - get the Parameter on page 'exceldata' with '{{arg1:S}}' and '{{arg2:S}}'
    SELECT CONCAT('d:final.xlsx|t:Excel (parameter)|uid:54&arg1=hello&arg2=world') AS _excel

Best practice
"""""""""""""

To keep the link of the Excel export close to the Excel export definition, the option :ref:`report-render`  can be used.

On a **single** T3 page create **two** QFQ tt-content records:

tt-content record 1:

* Type: QFQ
* Content::

      render = single
      10.sql = SELECT CONCAT('d:new.xlsx|t:Excel (new)|uid:54|M:excel|s:1') AS _link

tt-content record 2 (uid=54):

* Type: QFQ
* Content::

      render = api
      10.sql = SELECT 'position=D10' AS _XLS
                      , 's=Hello' AS _XLS
                      , 's=World' AS _XLS
                      , 's=First Line' AS _XLS
                      , 'newline' AS _XLS
                      , 's=Second line' AS _XLS
                      , 'n=123' AS _XLS

.. _dropdownMenu:

Dropdown Menu
-------------

Creates a menu with custom links. The same notation and options are used as with regular QFQ links.

Format String::

  <dropdown menu symbol options>||<menu entry 1>||<menu entry 2>||...

Each menu entry is separated by two bars! A menu entry itself might contain multiple single bars.

Example 1::

  SELECT 'z||p:home|t:Home|o:Jump to home||p:person&form=person&r=123|t:Edit: John Doe|s' AS _link

This defines a menu (three vertical buttons) - a click on it shows two menu entries: 'Home' and 'Edit: John Doe'

Format the dropdown menu symbol:

  * *Glyph*: Via ``G:<glyphicon name>`` any glyphicon can be defined. To hide the default glyph, specify: `G:0`.
  * *Text*: Via ``t:Menu`` an additional text will be displayed for the menu symbol.
  * *Tooltip*: Via ``o:Detail menu`` a tooltip is defined.
  * *Render mode*: Via ``r:3`` the menu is disabled. No menu entries / links / sip are rendered.
  * *Button*: Via ``b`` the dropdown meny symbol will be rendered with a button. Also `b:<style>` might set the BS color.

Format a menu entry:

* *qfq link*: All options as with a regular QFQ link.
* *header*: If a text starts with '===', it becomes a header in the dropdown menu. Multiple headers are possible.
  Headers can't be a link. An additional `r:1` is necessary.
* *separator*: If a text is exactly '---', it becomes a separator line between two menu entries. An additional ``r:1``
  is necessary.
* *disabled menu entry*: If a text starts with '---' (like separator), the following text becomes a disable menu entry.
  An additional ``r:1`` is necessary.

Example 2::

    SELECT CONCAT('z|t:Menu|G:0|o:Please select an option',
                  '||p:home|t:Home|o:Jump to home|G:glyphicon-home|b:0',
                  '||r:1|t:---',
                  '||p:person&form=person&r=123|t:Edit: John Doe|s|q:Really edit?|G:glyphicon-user|b:0',
                  '||t:===Header|r:1',
                  '||d|p:form&form=person&r=',p.id,'|s|t:Download Person|b:0',
                  '||r:1|t:---Disabled entry') AS _link

Line 1: The dropdown menu symbol definition, with a text 'Menu' ``t:Menu``, but without the three vertical bullets ``G:0``
and a tooltip for the menu ``o:Please select an option``.

Line 2: First menu entry. Directs to T3 page 'home' ``p:home``. ``t:Home`` sets the menu entry text. ``G:glyphicon-home`` set's
glyphicon symbol in the menu entry. ``b:0`` switches off the button, which has been implicit activated by the use of ``G:...``.

Line 3: A separator line.

Line 4: A SIP encoded edit record link, with a question dialog.

Line 5: A header line.

Line 6: A PDF download.

Line 7: A disabled menu entry.

.. _websocket:

WebSocket
---------

Sending messages via WebSocket and receiving the answer is done via: ::

  SELECT 'w:ws://<host>:<port>/<path>|t:<message>' AS _websocket

Instead of ``... AS _websocket`` it's also possible to use ``... AS _link`` (same syntax).

The answer from the socket (if there is something) is written to output and stored in STORE_RECORD by given column (in this
case 'websocket' or 'link').

.. tip::

  To suppress the direct output, add ``|_hide`` to the column name.

Example::

  SELECT 'w:ws://<host>:<port>/<path>|t:<message>' AS '_websocket|_hide'

.. tip::

  To define a uniq column name (easy access by column name via STORE_RECORD) add ``|myName`` (replace *myName*).

Example::

  SELECT 'w:ws://<host>:<port>/<path>|t:<message>' AS '_websocket|myName'

.. tip::

  Get the answer from STORE_RECORD by using ``{{&...``. Check `access-column-values`_.

Example::

  SELECT 'w:ws://<host>:<port>/<path>|t:<message>' AS '_websocket|myName'

  Results:

    '{{myName:R}}'  >> 'w:ws://<host>:<port>/<path>|t:<message>'
    '{{&myName:R}}'  >> '<received socket answer>'

.. _drag_and_drop:

Drag and drop
-------------

Order elements
^^^^^^^^^^^^^^

Ordering of elements via `HTML5 drag and drop` is supported via QFQ. Any element to order
should be represented by a database record with an order column. If the elements are unordered, they will be ordered after
the first 'drag and drop' move of an element.

Functionality divides into:

* Display: the records will be displayed via QFQ/report.
* Order records: updates of the order column are managed by a specific definition form. The form is not a regular form
  (e.g. there are no FormElements), instead it's only a container to held the SQL update query as well as providing
  access control via SIP. The form is automatically called via AJAX.

Part 1: Display list
""""""""""""""""""""

Display the list of elements via a regular QFQ content record. All 'drag and drop' elements together have to be nested by a HTML
element. Such HTML element:

* With ``class="qfq-dnd-sort"``.
* With a form name: ``{{'form=<form name>' AS _data-dnd-api}}`` (will be replaced by QFQ)
* Only *direct* children of such element can be dragged.
* Every children needs a unique identifier ``data-dnd-id="<unique>"``. Typically this is the corresponding record id.
* The record needs a dedicated order column, which will be updated through API calls in time.

A ``<div>`` example HTML output (HTML send to the browser): ::

    <div class="qfq-dnd-sort" data-dnd-api="typo3conf/ext/qfq/Classes/Api/dragAndDrop.php?s=badcaffee1234">
        <div class="anyClass" id="<uniq1>" data-dnd-id="55">
            Numbero Uno
        </div>
        <div class="anyClass" id="<uniq2>" data-dnd-id="18">
            Numbero Deux
        </div>
        <div class="anyClass" id="<uniq3>" data-dnd-id="27">
            Numbero Tre
        </div>
    </div>


A typical QFQ report which generates those ``<div>`` HTML::

    10 {
      sql = SELECT '<div id="anytag-', n.id,'" data-dnd-id="', n.id,'">' , n.note, '</div>'
                   FROM Note AS n
                   WHERE grId=28
                   ORDER BY n.ord

      head = <div class="qfq-dnd-sort" {{'form=dndSortNote&grId=28' AS _data-dnd-api}}">
      tail = </div>
    }


A ``<table>`` based setup is also possible. Note the attribute  ``data-columns="3"`` - this generates a dropzone
which is the same column width as the outer table. ::

    <table>
        <tbody class="qfq-dnd-sort" data-dnd-api="typo3conf/ext/qfq/Classes/Api/dragAndDrop.php?s=badcaffee1234" data-columns="3">
            <tr> class="anyClass" id="<uniq1>" data-dnd-id="55">
                <td>Numbero Uno</td><td>Numbero Uno.2</td><td>Numbero Uno.3</td>
            </tr>
            <tr class="anyClass" id="<uniq2>" data-dnd-id="18">
                <td>Numbero Deux</td><td>Numbero Deux.2</td><td>Numbero Deux.3</td>
            </tr>
            <tr class="anyClass" id="<uniq3>" data-dnd-id="27">
                <td>Numbero Tre</td><td>Numbero Tre.2</td><td>Numbero Tre.3</td>
            </tr>
        </tbody>
    </table>

A typical QFQ report which generates this HTML::

    10 {
      sql = SELECT '<tr id="anytag-', n.id,'" data-dnd-id="', n.id,'" data-columns="3">' , n.id AS '_+td', n.note AS '_+td', n.ord AS '_+td', '</tr>'
                   FROM Note AS n
                   WHERE grId=28
                   ORDER BY n.ord

      head = <table><tbody class="qfq-dnd-sort" {{'form=dndSortNote&grId=28' AS _data-dnd-api}} data-columns="3">
      tail = </tbody><table>
    }

Show / update order value in the browser
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

The 'drag and drop' action does not trigger a reload of the page. In case the order number is shown and the user does
a 'drag and drop', the order number shows the old. To update the dragable elements with the latest order number, a
predefined html id has to be assigned them. After an update, all changed order number (referenced by the html id) will
be updated via AJAX.

The html id per element is defined by `qfq-dnd-ord-id-<id>` where `<id>` is the record id. Same example as above, but
with an updated `n.ord` column::

    10 {
      sql = SELECT '<tr id="anytag-', n.id,'" data-dnd-id="', n.id,'" data-columns="3">' , n.id AS '_+td', n.note AS '_+td',
                   '<td id="qfq-dnd-ord-id-', n.id, '">', n.ord, '</td></tr>'
                   FROM Note AS n
                   WHERE grId=28
                   ORDER BY n.ord

      head = <table><tbody class="qfq-dnd-sort" {{'form=dndSortNote&grId=28' AS _data-dnd-api}} data-columns="3">
      tail = </tbody><table>
    }

Part 2: Order records
"""""""""""""""""""""

A dedicated `Form`, without any `FormElements`, is used to define the reorder logic (database update definition).

Fields:

* Name: <custom form name> - used in Part 1 in the  ``_data-dnd-api`` variable.
* Table: <table with the element records> - used to update the records specified by `dragAndDropOrderSql`.

* Parameter:

+-------------------------------------------------------+--------------------------------------------------------------+
| Attribute                                             | Description                                                  |
+=======================================================+==============================================================+
| orderInterval = <number>                              | Optional. By default '10'. Might be any number > 0.          |
+-------------------------------------------------------+--------------------------------------------------------------+
| orderColumn = <column name>                           | Optional. By default 'ord'.                                  |
+-------------------------------------------------------+--------------------------------------------------------------+
| dragAndDropOrderSql =                                 | Query to selects the *same* records as the report in the     |
| {{!SELECT n.id AS id, n.ord AS ord FROM Note AS n     | same *order!* Inconsistencies results in order differences.  |
| ORDER BY n.ord}}                                      | The columns `id` and `ord` are *mandatory.*                  |
+-------------------------------------------------------+--------------------------------------------------------------+

The form related to the example of part 1 ('div' or 'table'): ::

  Form.name: dndSortNote
  Form.table: Note
  Form.parameter: orderInterval = 1
  Form.parameter: orderColumn = ord
  Form.parameter: dragAndDropOrderSql = {{!SELECT n.id AS id, n.ord AS ord FROM Note AS n WHERE n.grId={{grId:S0}} ORDER BY n.ord}}

Re-Order:

QFQ iterates over the result set of `dragAndDropOrderSql`. The value of column `id` have to correspond to the dragged HTML
 element (given by `data-dnd-id`). Reordering always start with `orderInterval` and is incremented by `orderInterval` with each
 record of the result set. The client reports a) the id of the dragged HTML element, b) the id of the hovered element and
 c) the dropped position of above or below the hovered element. This information is compared to the result set and
 changes are applied where appropriate.

 Take care that the query of part 1 (display list) does a) select the same records and b) in the same order as the query
 defined in part 2 (order records) via `dragAndDropOrderSql`.

 If you find that the reorder does not work at expected, those two sql queries are not identical.

QFQ Icons
---------

Located under ``typo3conf/ext/qfq/Resources/Public/icons``::

   black_dot.png     bullet-red.gif      checked-red.gif     emoji.svg    mail.gif    pointer.svg    up.gif
   blue_dot.png      bullet-yellow.gif   checked-yellow.gif  gear.svg     marker.svg  rectangle.svg  upload.gif
   bulb.png          checkboxinvert.gif  construction.gif    help.gif     new.gif     resize.svg     wavy-underline.gif
   bullet-blue.gif   checked-blue.gif    copy.gif            home.gif     note.gif    show.gif       zoom.svg
   bullet-gray.gif   checked-gray.gif    delete.gif          icons.svg    pan.svg     trash.svg
   bullet-green.gif  checked-green.gif   down.gif            info.gif     paste.gif   turnLeft.svg
   bullet-pink.gif   checked-pink.gif    edit.gif            loading.gif  pencil.svg  turnRight.svg


QFQ CSS Classes
---------------

* `qfq-table-50`, `qfq-table-80`, `qfq-table-100` - assigned to `<table>`, set min-width and column width to 'auto'.
* Background Color: `qfq-color-grey-1`, `qfq-color-grey-2` - assigned to different tags (table, row, cell).
* `qfq-100` - assigned to different tags, makes an element 'width: 100%'.
* `qfq-left`- assigned to different tags, Text align left.
* `qfq-sticky` - assigned to `<thead>`, makes the header sticky.
* `qfq-badge`, `qfq-badge-error`, `qfq-badge-warning`, `qfq-badge-success`, `qfq-badge-info`, `qfq-badge-invers` - colorized BS3 badges.
* `letter-no-break` - assigned to a `div` will protect a paragraph (CSS: page-break-before: avoid;) not to break around
  a page border (converted to PDF via wkhtml). Take care that `qfq-letter.css` is included in TypoScript setup.

Bootstrap
---------

* Table: `table`
* Table > hover: `table-hover`
* Table > condensed: `table-condensed`

Example::

  10.sql = SELECT id, name, firstName, ...
  10.head = <table class='table table-condensed qfq-table-50'>

* `qfq-100`, `qfq-left` - makes e.g. a button full width and aligns the text left.