Report.rst

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

The answer is written to output and stored in the given column (in this case 'websocket' or 'link').

.. tip::

To suppress the direct output, add '_hide' to the column name::

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

.. tip::

To define a uniq column name (to access it later via STORE_RECORD)::

  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.

Example::

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

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.
* Evey 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="{{pageAlias:T}}-person">` - the `{{pageAlias: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.



* Activate/Save/Delete `views`: Insert inside of a table html-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 at least unique inside the typo3 content element. Example::

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

  * 'Views' can be saved as:

    * public: every user will see the `view` and can modify it.
    * private: 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 table 'Setting'.
  * 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.

Customization of tablesorter:

* Add the desired classes or data attributes to your table html, e.g.:

  * Disable sorting `class="sorter-false"` on a '<th>' to disable sorting on that column (or: `data-sorter="false"`).
  * Disable filter `class="filter-false"` on a '<th>' to hide the filter field for that column
  * see docs for more options: https://mottie.github.io/tablesorter/docs/index.html

* 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"],"widgetOptions":{"filter_columnFilters":true,"filter_reset":".reset","filter_cssFilter":"form-control","columnSelector_mediaquery":false} }'

* 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="{{pageAlias: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>

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