Report.rst

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

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

How to write Twig templates is documented by the Twig Project.

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:

tables/default.html.twig

A basic table with column headers, sorting and column filters using tablesorter and bootstrap.

tables/single_vertical.html.twig

A template to display the values of a single record in a vertical table.

Links

The link syntax described in :ref:`column-link` is available inside Twig templates using the qfqlink filter:

{{ "u:http://www.example.com" | qfqlink }}

will render a link to http://www.example.com.

Json Decode

A String can be JSON decoded in Twig the following way:

{% set decoded = '["this is one", "this is two"]' | json_decode%}

This can then be used as a normal object in Twig:

{{ decoded[0] }}

will render this is one.

Available Store Variables

QFQ also provides access to the following stores via the template context.

• record
• sip
• typo3
• user
• system
• var

All stores are accessed using their lower case name as attribute of the context variable store. The active Typo3 front-end user is therefore available as:

{{ store.typo3.feUser }}

Example

The following block shows an example of a QFQ report.

10.sql selects all users who have been assigned files in our file tracker.

10.10 then selects all files belonging to this user, prints the username as header and then displays the files in a nice table with links to the files.

10.sql = SELECT assigned_to AS _user FROM FileTracker
            WHERE assigned_to IS NOT NULL
            GROUP BY _user
            ORDER BY _user

10.10.sql = SELECT id, path_scan FROM FileTracker
WHERE assigned_to = '{{user:R}}'
10.10.twig = <h2>{{ store.record.user }}</h2>
<table class="table table-hover tablesorter" id="{{pageAlias:T}}-twig">
  <thead><tr><th>ID</th><th>File</th></tr></thead>
  <tbody>
  {% for row in result %}
    <tr>
      <td>{{ row.id }}</td>
      <td>{{ ("d:|M:pdf|s|t:"~ row.path_scan ~"|F:" ~ row.path_scan ) | qfqlink }}</td>
    </tr>
  {% endfor %}
  </tbody>
</table>

Debug the bodytext

The parsed bodytext could be displayed by activating 'showDebugInfo' (:ref:`debug`) and specifying:

debugShowBodyText = 1

A small symbol with a tooltip will be shown, where the content record will be displayed on the webpage. Note: :ref:`debug` information will only be shown with showDebugInfo: yes in :ref:`configuration`.

Inline Report editing

For quick changes it can be bothersome to go to the TYPO3 backend to update the page content and reload the page. For this reason, QFQ offers an inline report editing feature whenever there is a TYPO3 BE user logged in. A small link symbol will appear on the right-hand side of each report record. Please note that the TYPO3 Frontend cache is also deleted upon each inline report save.

In order for the inline report editing to work, QFQ needs to be able to access the T3 database. This database is assumed to be accessible with the same credentials as specified with indexQfq.

Structure

A report can be divided into several levels. This can make report definitions more readable because it allows for splitting of otherwise excessively long SQL queries. For example, if your SQL query on the root level selects a number of person records from your person table, you can use the SQL query on the second level to look up the city where each person lives.

See the example below:

10.sql = SELECT id AS _pId, CONCAT(firstName, " ", lastName, " ") AS name FROM Person
10.rsep = <br>

10.10.sql = SELECT CONCAT(postal_code, " ", city) FROM Address WHERE pId = {{10.pId}}
10.10.rbeg = (
10.10.rend = )

This would result in:

John Doe (3004 Bern)
Jane Miller (8008 Zürich)
Frank Star (3012 Bern)

Text across several lines

To get better human readable SQL queries, it's possible to split a line across several lines. Lines with keywords are on their own (:ref:`QFQ Keywords (Bodytext)<qfq_keywords>` start a new line). If a line is not a 'keyword' line, it will be appended to the last keyword line. 'Keyword' lines are detected on:

• <level>.<keyword> =
• {
• <level>[.<sub level] {

Example:

10.sql = SELECT 'hello world'
           FROM Mastertable
10.tail = End

20.sql = SELECT 'a warm welcome'
           'some additional', 'columns'
           FROM AnotherTable
           WHERE id>100

20.head = <h3>
20.tail = </h3>

Join mode: SQL

This is the default. All lines are joined with a space in between. E.g.:

10.sql = SELECT 'hello world'
           FROM Mastertable

Results to: 10.sql = SELECT 'hello world' FROM Mastertable

Notice the space between "...world'" and "FROM ...".

Join mode: strip whitespace

Ending a line with a \ strips all leading and trailing whitespaces of that line joins the line directly (no extra space in between). E.g.:

10.sql = SELECT 'hello world', 'd:final.pdf \
                                |p:id=export  \
                                |t:Download' AS _pdf \

Results to: 10.sql = SELECT 'hello world', 'd:final.pdf|p:id=export|t:Download' AS _pdf

Note: the \ does not force the joining, it only removes the whitespaces.

To get the same result, the following is also possible:

10.sql = SELECT 'hello world', CONCAT('d:final.pdf'
                                '|p:id=export',
                                '|t:Download') AS _pdf