Report.rst

Table: vertical text via CSS
^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Use class `vertical` and `qfq-vertical-text`. Example::

  <table>
    <tr>
      <th class="qfq-vertical"><span class="qfq-vertical-text">Column 1</span></th>
      <th class="qfq-vertical"><span class="qfq-vertical-text">2</span></th>
      <th class="qfq-vertical"><span class="qfq-vertical-text">Very long column title</span></th>
      <th class="qfq-vertical"><span class="qfq-vertical-text">4</span></th>
      <th class="qfq-vertical"><span class="qfq-vertical-text">5</span></th>
    </tr>
    <tr><td>1</td><td>2</td><td>3</td><td>very wide text</td><td>5</td></tr>
  </table>

Same effect is also possible via special column name `_vertical` (ref:`special-column-names`).

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.

Example::

    10.sql = SELECT "p:home&r=0|t:Home|c:qfq-100 qfq-left" AS _pages

Tablesorter
-----------

QFQ includes a third-party client-side table sorter: https://mottie.github.io/tablesorter/docs/index.html

To turn any table into a sortable table:

* Ensure that your QFQ installation imports the appropriate js/css files, see :ref:`setup-css-js`.
* Add the `class="tablesorter"` to your `<table>` element.
* Take care the `<table>` has a `<thead>` and `<tbody>` tag.
* Every table with active tablesorter should have a uniq HTML id.

.. important::

   Custom settings will be saved per table automatically in the browser local storage. To distinguish different table
   settings, define an uniq HTML id per table.
   Example: `<table class="tablesorter" id="{{pageSlug:T}}-person">` - the `{{pageSlug:T}}` makes it easy to keep the
   overview over given name on the site.


The *tablesorter* options:

* Class `tablesorter-filter` enables row filtering.
* Class `tablesorter-pager` adds table paging functionality. A page navigation
  is shown.
* Class `tablesorter-column-selector` adds a column selector widget.

.. _tablesorter-view-saver:

Tablesorter View Saver
^^^^^^^^^^^^^^^^^^^^^^

* Tablesorter view saver: inside of a HTML `table`-tag the command::

   {{ '<uniqueName>' AS _tablesorter-view-saver }}

  This adds a menu to save the current view (column filters, selected columns, sort order).

  * `<uniqueName>` should be a name which is unique. Example::

    <table {{ 'allperson' AS _tablesorter-view-saver }} class="tablesorter tablesorter-filter tablesorter-column-selector" id="{{pageSlug:T}}-demo"> ... </table>

  * 'Views' can be saved as:

    * group: every user will see the `view` and can modify it.
    * personal: only the user who created the `view` will see/modify it.
    * readonly: manually mark a `view` as readonly (no FE User can change it) by setting column `readonly='true'` in table
       `Setting` of the corresponding view (identified by `name`).

  * Views will be saved in the QFQ system DB table 'Setting'.
  * Every setting is saved with the T3 FE username. If there is no T3 FE username, the current QFQ cookie is used instead.
  * Include 'font-awesome' CSS in your T3 page setup: `typo3conf/ext/qfq/Resources/Public/Css/font-awesome.min.css` to get the icons.
  * The view 'Clear' is always available and can't be modified.
  * To preselect a view, append a HTML anker to the current URL. Get the anker by selecting the view and copy it from the
    browser address bar. Example::

      https://localhost/index.php?id=person#allperson=public:email

    * 'allperson' is the '<uniqueName>' of the `tablesorter-view-saver` command.
    * 'public' means the view is tagged as 'public' visible.
    * 'email' is the name of the view, as it is shown in the dropdown list.

  * If there is a public view with the name 'Default' and a user has no choosen a view earlier, that one will be selected.

.. _tablesorter-export-csv:

Tablesorter CSV Export
^^^^^^^^^^^^^^^^^^^^^^

* You can export your tablesorter tables as CSV files using the output widget (be sure to include the separate JS file):

  * Create a button to trigger the export with the following Javascript::

         $('table.tablesorter').trigger('outputTable');

  * Default export file name: `tableExport.csv`
  * Exported with column separator `;`
  * Only currently filtered rows are exported.
  * Values are exported as text, without HTML tags
  * You can change the formatting/value of each cell as follows::

         <td data-name="12345">CHF 12,345.-</td>

  * Headers and footers are exported as well.

Customization of tablesorter
^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  * See docs for more options: https://mottie.github.io/tablesorter/docs/index.html
  * Add the desired classes or data attributes to your table html, e.g. a dropdown: ``class="filter-select"``
    (`example-widget-filter-custom <https://mottie.github.io/tablesorter/docs/example-widget-filter-custom.html>`_)

+-----------------------------+----------------------------------------------------------------------------------------+
| Description                 | Syntax                                                                                 |
+=============================+========================================================================================+
| Disable sorter              | ``<th class="sorter-false">...``                                                       |
+-----------------------------+----------------------------------------------------------------------------------------+
| Disable filter              | ``<th class="filter-false">...``                                                       |
+-----------------------------+----------------------------------------------------------------------------------------+
| Filter as dropdown          | ``<th class="filter-select">...``                                                      |
+-----------------------------+----------------------------------------------------------------------------------------+
| Ignore entire row           | Wrap ``<tr>`` inside a ``<tfoot>``. Caution: May cause undesired print behavior.       |
|                             | Use ``<tfoot style = "display:table-row-group;"> </tfoot>``                            |
+-----------------------------+----------------------------------------------------------------------------------------+
| Custom value for cell       | ``<td data-text="...">...``                                                            |
+-----------------------------+----------------------------------------------------------------------------------------+
| Sorting for tables with     | ``<tr class="tablesorter-hasChildRow">...</tr>``                                       |
| child rows (e.g. drilldown) | ``<tr class="tablesorter-childRow">...</tr>``                                          |
+-----------------------------+----------------------------------------------------------------------------------------+


    * You can pass in a default configuration object for the main `tablesorter()` function by using the attribute
      `data-tablesorter-config` on the table.
      Use JSON syntax when passing in your own configuration, such as: ::

        data-tablesorter-config='{"theme":"bootstrap","widthFixed":true,"headerTemplate":"{content} {icon}","dateFormat":"ddmmyyyy","widgets":["uitheme","filter","saveSort","columnSelector","output"],"widgetOptions":{"filter_columnFilters":true,"filter_reset":".reset","filter_cssFilter":"form-control","columnSelector_mediaquery":false,"output_delivery":"download","output_saveFileName":"tableExport.csv","output_separator":";"} }'

    * If the above customization options are not enough, you can output your own HTML for the pager and/or column selector,
      as well as your own `$(document).ready()` function with the desired config. In this case, it is recommended not to
      use the above *tablesorter* classes since the QFQ javascript code could interfere with your javascript code.

Example::

    10 {
      sql = SELECT id, CONCAT('form&form=person&r=', id) AS _Pagee, lastName, title FROM Person
      head = <table class="table tablesorter tablesorter-filter tablesorter-pager tablesorter-column-selector" id="{{pageSlug:T}}-ts1">
          <thead><tr><th>Id</th><th class="filter-false sorter-false">Edit</th>
          <th>Name</th><th class="filter-select" data-placeholder="Select a title">Title</th>
          </tr></thead><tbody>
      tail = </tbody></table>
      rbeg = <tr>
      rend = </tr>
      fbeg = <td>
      fend = </td>
    }

.. _monitor:

Monitor
-------

Display a (log)file from the server, inside the browser, which updates automatically by a user defined interval. Access
to the file is SIP protected. Any file on the server is possible.

* On a Typo3 page, define a HTML element with a unique html-id. E.g.::

    10.head = <pre id="monitor-1">Please wait</pre>

* On the same Typo3 page, define an SQL column '_monitor' with the necessary parameter::

    10.sql = SELECT 'file:fileadmin/protected/log/sql.log|tail:50|append:1|refresh:1000|htmlId:monitor-1' AS _monitor


* Short version with all defaults used to display system configured sql.log::

    10.sql = SELECT 'file:{{sqlLog:Y}}' AS _monitor, '<pre id="monitor-1" style="white-space: pre-wrap;">Please wait</pre>'

.. _calendar_view:

Calendar View
-------------

QFQ is shipped with the JavaScript library https://fullcalendar.io/ (respect that QFQ uses V3) to provides various calendar views.

Docs: https://fullcalendar.io/docs/v3

Include the JS & CSS files via Typoscript

* typo3conf/ext/qfq/Resources/Public/Css/fullcalendar.min.css
* typo3conf/ext/qfq/Resources/Public/JavaScript/moment.min.js
* typo3conf/ext/qfq/Resources/Public/JavaScript/fullcalendar.min.js

Integration: Create a `<div>` with

* CSS class "qfq-calendar"
* Tag `data-config`. The content is a Javascript object.

Example::

   10.sql = SELECT 'Calendar, Standard'
   10.tail = <div class="qfq-calendar"
                  data-config='{
                       "themeSystem": "bootstrap3",
                       "height": "auto",
                       "defaultDate": "2020-01-13",
                       "weekends": false,
                       "defaultView": "agendaWeek",
                       "minTime": "05:00:00",
                       "maxTime": "20:00:00",
                       "businessHours": { "dow": [ 1, 2, 3, 4 ], "startTime": "10:00", "endTime": "18:00" },
                       "events": [
                         { "id": "a", "title": "my event",
                         "start": "2020-01-21"},
                         { "id": "b", "title": "my other event", "start": "2020-01-16T09:00:00", "end": "2020-01-16T11:30:00"}
                        ]}'>
               </div>

   # "now" is in the past to switchoff 'highlight of today'
   20.sql = SELECT 'Calendar, 3 day, custom color, agend&list' AS '_+h2'
   20.tail = <div class="qfq-calendar"
                  data-config='{
                       "themeSystem": "bootstrap3",
                       "height": "auto",
                       "header": {
                         "left": "title",
                         "center": "",
                         "right": "agenda,listWeek"
                        },
                       "defaultDate": "2020-01-14",
                       "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:{{pageSlug: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:{{pageSlug:T}}?order=count|t:Order by count|b|s' AS _link,
                    'p:{{pageSlug:T}}?order=sum|t:Order by sum|b|s' AS _link,
                    'p:{{pageSlug:T}}?step=10|t:Step=10|b|s' AS _link,
                    'p:{{pageSlug:T}}?step=50|t:Step=50|b|s' AS _link,
                    'p:{{pageSlug:T}}?direction=ASC|t:Order by up|b|s' AS _link,
                    'p:{{pageSlug: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:{{pageSlug:T}}?feUser=account1|t:Become "account1"|b|s' AS _link,
                    'p:{{pageSlug: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:{{pageSlug: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:{{pageSlug: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>
    }