Report.rst

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