Report.rst

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

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

.. include:: Includes.txt


.. _`report`:

Report
======

QFQ Report Keywords
-------------------

See :ref:`qfq_keywords`.

QFQ content element
-------------------

The QFQ extension is activated through tt-content records. One or more tt-content records per page are necessary to render
*forms* and *reports*. Specify column and language per content record as wished.

The title of the QFQ content element will not be rendered. It's only visible in the backend for orientation of the webmaster.

To display a report on any given TYPO3 page, create a content element of type 'QFQ Element' (plugin) on that page.

A simple example
^^^^^^^^^^^^^^^^

Assume that the database has a table person with columns firstName and lastName. To create a simple list of all persons, we can do the following::

    10.sql = SELECT firstName, lastName FROM Person

The '10' indicates a *root level* of the report (see section :ref:`Structure<Structure>`). The expression '10.sql' defines an SQL query
for the specific level. When the query is executed, it will return a result having one single column name containing first and last name
separated by a space character.

The HTML output, displayed on the page, resulting from only this definition, could look as follows::

    JohnDoeJaneMillerFrankStar

I.e., QFQ will simply output the content of the SQL result row after row for each single level.

Format output: mix style and content
""""""""""""""""""""""""""""""""""""

Variant 1: pure SQL
;;;;;;;;;;;;;;;;;;;

To format the upper example, just create additional columns::

    10.sql = SELECT firstName, ", ", lastName, "<br>" FROM Person

HTML output::

    Doe, John<br>
    Miller, Jane<br>
    Star, Frank<br>

Variant 2: SQL plus QFQ helper
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

QFQ provides several helper functions to wrap rows and columns, or to separate them. E.g. ``fsep`` stands for `field
separate` and ``rend`` for `row end`::

    10.sql = SELECT firstName, lastName FROM Person
    10.fsep = ', '
    10.rend = <br>

HTML output::

    Doe, John<br>
    Miller, Jane<br>
    Star, Frank<br>

Check out all QFQ helpers under :ref:`qfq_keywords`.

Due to mixing style and content, this becomes harder to maintain with more complex layout.

Format output: separate style and content
"""""""""""""""""""""""""""""""""""""""""

The result of the query can be passed to the `Twig template engine <https://twig.symphony.com/>`_
in order to fill a template with the data.::

    10.sql = SELECT firstName, lastName FROM Person
    10.twig = {% for row in result %}
        {{ row.lastName }}, {{ row.firstName }<br />
    {% endfor %}

HTML output::

    Doe, John<br>
    Miller, Jane<br>
    Star, Frank<br>

Check out :ref:`using-twig`.

.. _`syntax-of-report`:

Syntax of *report*
------------------

All **root level queries** will be fired in the order specified by 'level' (Integer value).

For **each** row of a query (this means *all* queries), all subqueries will be fired once.

* E.g. if the outer query selects 5 rows, and a nested query select 3 rows, than the total number of rows are 5 x 3 = 15 rows.

There is a set of **variables** that will get replaced before ('count' also after) the SQL-Query gets executed:

``{{<name>[:<store/s>[:...]]}}``
  Variables from specific stores.

``{{<name>:R}}`` - use case of the above generic definition. See also :ref:`access-column-values`.

``{{<level>.<columnName>}}``
  Similar to  ``{{<name>:R}}`` but more specific. There is no sanitize class, escape mode or default value.

See :ref:`qfq_keywords` for a full list.

See :ref:`variables` for a full list of all available variables.

Different types of SQL queries are possible: SELECT, INSERT, UPDATE, DELETE, SHOW, REPLACE

Only SELECT and SHOW queries will fire subqueries.

Processing of the resulting rows and columns:

  * In general, all columns of all rows will be printed out sequentially.
  * On a per column base, printing of columns can be suppressed by starting the column name with an underscore '_'. E.g.
    ``SELECT id AS _id``.

     This might be useful to store values, which will be used later on in another query via the ``{{id:R}}`` or
     ``{{<level>.columnName}}`` variable. To suppress printing of a column, use a underscore as column name prefix. E.g.
     ``SELECT id AS _id``

*Reserved column names* have a special meaning and will be processed in a special way. See
:ref:`Processing of columns in the SQL result<Processing of columns in the SQL result>` for details.

There are extensive ways to wrap columns and rows. See :ref:`wrapping-rows-and-columns`

.. _`using-twig`:

Using Twig
----------

How to write Twig templates is documented by the `Twig Project <https://twig.symphony.com/>`_.

QFQ passes the result of a given query to the corresponding Twig template using the Twig variable
`result`. So templates need to use this variable to access the result of a query.

Specifying a Template
^^^^^^^^^^^^^^^^^^^^^
By default the string passed to the **twig**-key is used as template directly,
as shown in the simple example above::

   10.twig = {% for row in result %}
       {{ row.lastName }}, {{ row.firstName }<br />
   {% endfor %}

However, if the string starts with **file:**, the template is read from the
file specified.::

   10.twig = file:table_sortable.html.twig

The file is searched relative to *<site path>* and if the file is not found there, it's searched relative to
QFQ's *twig_template* folder where the included base templates are stored.

The following templates are available: