Report.rst

                       "now": "1999-12-31",
                       "allDaySlot": false,
                       "weekends": false,
                       "defaultView": "agenda",
                       "dayCount": 3,
                       "minTime": "08:00:00",
                       "maxTime": "18:00:00",
                       "businessHours": { "dow": [ 1, 2, 3, 4 ], "startTime": "10:00", "endTime": "18:00" },
                       "events": [
                         { "id": "a", "title": "my event",       "start": "2020-01-15T10:15:00", "end": "2020-01-15T11:50:00", "color": "#25adf1", "textColor": "#000"},
                         { "id": "b", "title": "my other event", "start": "2020-01-16T09:00:00", "end": "2020-01-16T11:30:00", "color": "#5cb85c", "textColor": "#000"},
                         { "id": "c", "title": "Eventli",        "start": "2020-01-15T13:10:00", "end": "2020-01-15T16:30:00", "color": "#fbb64f", "textColor": "#000"},
                         { "id": "d", "title": "Evento",         "start": "2020-01-15T13:50:00", "end": "2020-01-15T15:00:00", "color": "#fb4f4f", "textColor": "#000"},
                         { "id": "d", "title": "Busy",           "start": "2020-01-14T09:00:00", "end": "2020-01-14T12:00:00", "color": "#ccc",    "textColor": "#000"},
                         { "id": "e", "title": "Banana",         "start": "2020-01-16T13:30:00", "end": "2020-01-16T16:00:00", "color": "#fff45b", "textColor": "#000"}
                        ]}'>
               </div>

.. _reportAsFile:

Report As File
--------------

* If the toplevel token `file` is present inside the body of a QFQ tt-content element then the given report file is loaded and rendered.

  * The tt-content body is ignored in that case.

* The path to the report file must be given relative to the report directory inside the qfq project directory. See :ref:`qfq-project-path-php`

  * QFQ provides some special system reports which are located inside the extension directory `typo3conf/ext/qfq/Resources/Private/Report` and can be directly rendered by prepending an underscore and omitting the file extension:

    * `file=_formEditor` will render the standard formEditor report

* If the QFQ setting `reportAsFileAutoExport` (see :ref:`extension-manager-qfq-configuration`) is enabled, then every QFQ tt-content element which does not contain the `file` keyword is exported automatically when the report is rendered the first time.

  * The path of the created file is given by the typo3 page structure
  * The tt-content element body is replaced with `file=<path-to-new-file>`

* **Backups** : Whenever a report file is edited via the frontend report editor then a backup of the previous version is saved in the `.backup` directory located in the same directory as the report file.

Example tt-content body::

   file=Home/myPage/qfq-report.qfqr

   # Everything else is ignored!!
   10.sql = SELECT 'This is ignored!!'

Example Home/myPage/qfq-report.qfqr::

   # Some comment
   10.sql = SELECT 'The file content is executed.'

Example of rendered report::

   The file content is executed.

Report Examples
---------------

The following section gives some examples of typical reports.

Basic Queries
^^^^^^^^^^^^^

One simple query::

    10.sql = SELECT "Hello World"


Result::

    Hello World


Two simple queries::

    10.sql = SELECT "Hello World"
    20.sql = SELECT "Say hello"

Result::

    Hello WorldSay hello

..



Two simple queries, with break::

    10.sql = SELECT "Hello World<br>"
    20.sql = SELECT "Say hello"


Result::

    Hello World
    Say hello


Accessing the database
^^^^^^^^^^^^^^^^^^^^^^

Real data, one single column::

    10.sql = SELECT p.firstName FROM ExpPerson AS p


Result::

    BillieElvisLouisDiana


Real data, two columns::

    10.sql = SELECT p.firstName, p.lastName FROM ExpPerson AS p

Result::

    BillieHolidayElvisPresleyLouisArmstrongDianaRoss


The result of the SQL query is an output, row by row and column by column, without adding any formatting information.
See :ref:`Formatting Examples<Formatting Examples>` for examples of how the output can be formatted.

.. _`Formatting Examples`:

Formatting Examples
^^^^^^^^^^^^^^^^^^^

Formatting (i.e. wrapping of data with HTML tags etc.) can be achieved in two different ways:

One can add formatting output directly into the SQL by either putting it in a separate column of the output or by using
concat to concatenate data and formatting output in a single column.

One can use 'level' keys to define formatting information that will be put before/after/between all rows/columns of the
actual levels result.

Two columns::

    # Add the formatting information as a column
    10.sql = SELECT p.firstName, " " , p.lastName, "<br>" FROM ExpPerson AS p


Result::

    Billie Holiday
    Elvis Presley
    Louis Armstrong
    Diana Ross

One column 'rend' as linebreak - no extra column '<br>' needed::

    10.sql = SELECT p.firstName, " " , p.lastName, " ", p.country FROM ExpPerson AS p
    10.rend = <br>

Result::

    Billie Holiday USA
    Elvis Presley USA
    Louis Armstrong USA
    Diana Ross USA

Same with 'fsep' (column " " removed):

    10.sql = SELECT p.firstName, p.lastName, p.country FROM ExpPerson AS p
    10.rend = <br>
    10.fsep = " "

Result::

    Billie Holiday USA
    Elvis Presley USA
    Louis Armstrong USA
    Diana Ross USA



More HTML::

    10.sql = SELECT p.name FROM ExpPerson AS p
    10.head = <ul>
    10.tail = </ul>
    10.rbeg = <li>
    10.rend = </li>

Result::

    o Billie Holiday
    o Elvis Presley
    o Louis Armstrong
    o Diana Ross

The same as above, but with braces::

  10 {
    sql = SELECT p.name FROM ExpPerson AS p
    head = <ul>
    tail = </ul>
    rbeg = <li>
    rend = </li>
  }

Two queries::

    10.sql = SELECT p.name FROM ExpPerson AS p
    10.rend = <br>
    20.sql = SELECT a.street FROM ExpAddress AS a
    20.rend = <br>

Two queries: nested::

    # outer query
    10.sql = SELECT p.name FROM ExpPerson AS p
    10.rend = <br>

    # inner query
    10.10.sql = SELECT a.street FROM ExpAddress AS a
    10.10.rend = <br>

* For every record of '10', all records of 10.10 will be printed.

Two queries: nested with variables::

    # outer query
    10.sql = SELECT p.id, p.name FROM ExpPerson AS p
    10.rend = <br>

    # inner query
    10.10.sql = SELECT a.street FROM ExpAddress AS a WHERE a.pId='{{10.id}}'
    10.10.rend = <br>

* For every record of '10', all assigned records of 10.10 will be printed.

Two queries: nested with hidden variables in a table::

    10.sql = SELECT p.id AS _pId, p.name FROM ExpPerson AS p
    10.rend = <br>

    # inner query
    10.10.sql = SELECT a.street FROM ExpAddress AS a WHERE a.pId='{{10.pId}}'
    10.10.rend = <br>

Same as above, but written in the nested notation::

  10 {
    sql = SELECT p.id AS _pId, p.name FROM ExpPerson AS p
    rend = <br>

    10 {
    # inner query
      sql = SELECT a.street FROM ExpAddress AS a WHERE a.pId='{{10.pId}}'
      rend = <br>
    }
  }

Best practice *recommendation* for using parameter - see :ref:`access-column-values`::

  10 {
    sql = SELECT p.id AS _pId, p.name FROM ExpPerson AS p
    rend = <br>

    10 {
    # inner query
      sql = SELECT a.street FROM ExpAddress AS a WHERE a.pId='{{pId:R}}'
      rend = <br>
    }
  }

Create HTML tables. Each column is wrapped in ``<td>``, each row is wrapped in ``<tr>``::

  10 {
    sql = SELECT p.firstName, p.lastName, p.country FROM Person AS p
    head = <table class="table">
    tail = </table>
    rbeg = <tr>
    rend = </tr>
    fbeg = <td>
    fend = </td>
  }

Maybe a few columns belongs together and should be in one table column.

Joining columns, variant A: firstName and lastName in one table column::

  10 {
    sql = SELECT CONCAT(p.firstName, ' ', p.lastName), p.country FROM Person AS p
    head = <table class="table">
    tail = </table>
    rbeg = <tr>
    rend = </tr>
    fbeg = <td>
    fend = </td>
  }

Joining columns, variant B: firstName and lastName in one table column::

  10 {
    sql = SELECT '<td>', p.firstName, ' ', p.lastName, '</td><td>', p.country, '</td>' FROM Person AS p
    head = <table class="table">
    tail = </table>
    rbeg = <tr>
    rend = </tr>
  }

Joining columns, variant C: firstName and lastName in one table column. Notice ``fbeg``, ``fend` and ``fskipwrap``::

  10 {
    sql = SELECT '<td>', p.firstName, ' ', p.lastName, '</td>', p.country FROM Person AS p
    head = <table class="table">
    tail = </table>
    rbeg = <tr>
    rend = </tr>
    fbeg = <td>
    fend = </td>
    fskipwrap = 1,2,3,4,5
  }

Joining columns, variant D: firstName and lastName in one table column. Notice ``fbeg``, ``fend` and ``fskipwrap``::

  10 {
    sql = SELECT CONCAT('<td>', p.firstName, ' ', p.lastName, '</td>') AS '_noWrap', p.country FROM Person AS p
    head = <table class="table">
    tail = </table>
    rbeg = <tr>
    rend = </tr>
    fbeg = <td>
    fend = </td>
  }

Recent List
^^^^^^^^^^^

A nice feature is to show a list with last changed records. The following will show the 10 last modified (Form or
FormElement) forms::

  10 {
    sql = SELECT CONCAT('p:{{pageAlias:T}}&form=form&r=', f.id, '|t:', f.name,'|o:', GREATEST(MAX(fe.modified), f.modified)) AS _page
            FROM Form AS f
            LEFT JOIN FormElement AS fe
              ON fe.formId = f.id
            GROUP BY f.id
            ORDER BY GREATEST(MAX(fe.modified), f.modified) DESC
            LIMIT 10
    head = <h3>Recent Forms</h3>
    rsep = ,&ensp;
  }

.. _`vertical-column-title`:

Table: vertical column title
^^^^^^^^^^^^^^^^^^^^^^^^^^^^

To orientate a column title vertical, use the QFQ CSS classe `qfq-vertical` in td|th and `qfq-vertical-text` around the text.

HTML example (second column title is vertical)::

  <table><thead>
    <tr>
      <th>horizontal</th>
      <th class="qfq-vertical"><span class="qfq-vertical-text">text vertical</span></th>
    </tr>
  </thead></table>


QFQ example::

  10 {
    sql = SELECT title FROM Settings ORDER BY title
    fbeg = <th class="qfq-vertical"><span class="qfq-vertical-text">
    fend = </span></th>
    head = <table><thead><tr>
    rend = </tr></thead>
    tail = </table>

    20.sql = SELECT ...
  }


.. _`store_user_examples`:

STORE_USER examples
^^^^^^^^^^^^^^^^^^^

Keep variables per user session.

Two pages (pass variable)
"""""""""""""""""""""""""

Sometimes it's useful to have variables per user (=browser session). Set a variable on page 'A' and retrieve the value
on page 'B'.

Page 'A' - set the variable::

    10.sql = SELECT 'hello' AS '_=greeting'

Page 'B' - get the value::

    10.sql = SELECT '{{greeting:UE}}'

If page 'A' has never been opened with the current browser session, nothing is printed (STORE_EMPTY gives an empty string).
If page 'A' is called, page 'B' will print 'hello'.

One page (collect variables)
""""""""""""""""""""""""""""

A page will be called with several SIP variables, but not at all at the same time. To still get all variables at any time::

    # Normalize
    10.sql = SELECT '{{order:USE:::sum}}' AS '_=order', '{{step:USE:::5}}' AS _step, '{{direction:USE:::ASC}}' AS _direction

    # Different links
    20.sql = SELECT 'p:{{pageAlias:T}}&order=count|t:Order by count|b|s' AS _link,
                    'p:{{pageAlias:T}}&order=sum|t:Order by sum|b|s' AS _link,
                    'p:{{pageAlias:T}}&step=10|t:Step=10|b|s' AS _link,
                    'p:{{pageAlias:T}}&step=50|t:Step=50|b|s' AS _link,
                    'p:{{pageAlias:T}}&direction=ASC|t:Order by up|b|s' AS _link,
                    'p:{{pageAlias:T}}&direction=DESC|t:Order by down|b|s' AS _link

    30.sql = SELECT * FROM Items ORDER BY {{order:U}} {{direction:U}} LIMIT {{step:U}}

Simulate/switch user: feUser
""""""""""""""""""""""""""""

Just set the STORE_USER variable 'feUser'.

All places with `{{feUser:T}}` has to be replaced by `{{feUser:UT}}`::

    # Normalize
    10.sql = SELECT '{{feUser:UT}}' AS '_=feUser'

    # Offer switching feUser
    20.sql = SELECT 'p:{{pageAlias:T}}&feUser=account1|t:Become "account1"|b|s' AS _link,
                    'p:{{pageAlias:T}}&feUser={{feUser:T}}|t:Back to own identity|b|s' AS _link,


Semester switch (remember last choice)
""""""""""""""""""""""""""""""""""""""

A current semester is defined via configuration in STORE_SYSTEM '{{semId:Y}}'. The first column in 10.sql
`'{{semId:SUY}}' AS '_=semId'` saves
the semester to STORE_USER via '_=semId'. The priority 'SUY' takes either the latest choose (STORE_SIP) or reuse the
last used (STORE_USER) or (first time call during browser session) takes the default from config (STORE_SYSTEM)::

    # Semester switch
    10 {
      sql = SELECT '{{semId:SUY}}' AS '_=semId'
                   , CONCAT('p:{{pageAlias:T}}&semId=', sp.id, '|t:', QBAR(sp.name), '|s|b|G:glyphicon-chevron-left') AS _link
                   , ' <button class="btn disabled ',   IF({{semId:Y0}}=sc.id, 'btn-success', 'btn-default'), '">',sc.name, '</button> '
                   , CONCAT('p:{{pageAlias:T}}&semId=', sn.id, '|t:', QBAR(sn.name), '|s|b|G:glyphicon-chevron-right|R') AS _link
              FROM Semester AS sc

              LEFT JOIN semester AS sp
                ON sp.id=sc.id-1

              LEFT JOIN semester AS sn
                ON sc.id+1=sn.id AND sn.show_semester_from<=CURDATE()

              WHERE sc.id={{semId:SUY}}
              ORDER BY sc.semester_von
      head = <div class="btn-group" style="position: absolute; top: 15px; right: 25px;">
      tail = </div><p></p>
    }