Report¶
The QFQ extension is activated through tt-content records. One or more tt-content records per page are necessary to render forms and reports.
QFQ content element¶
QFQ is used by configuring Typo3 content elements. Insert one or more QFQ content elements on a Typo3 page. 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.
QFQ Keywords (Bodytext)¶
Name Explanation form Formname defined in ttcontent record bodytext * Fix. E.g.: form = person * by SIP: form = {{form}} * by SQL: form = {{SELECT c.form FROM conference AS c WHERE c.id={{a:C}} }} r <record id> The form will load the record with the specified id * Variants: r = 123, by SQL: r = {{SELECT ...}} * If not specified, the default is ‘0’ <level>.db Select a DB. Only necessary if a different than the standard DB should be used. <level>.fbeg Start token for every field (=column) <level>.fend End token for every field (=column) <level>.head Start token for whole <level> <level>.tail End token for whole <level> <level>.rbeg Start token for row. <level>.rbgd Alternating (per row) token <level>.rend End token for row. Will be rendered before subsequent levels are processed <level>.renr End token for row. Will be rendered after subsequent levels are processed <level>.rsep Seperator token between rows <level>.fsep Seperator token between fields (=columns) <level>.sql SQL Query <level>.althead If <level>.sql is empty, these token will be rendered debugShowBodyText If =‘1’ and config.qfq.ini:showDebugInfo=yes - shows a tooltip with bodytext
General¶
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 id AS pId, CONCAT(firstName, " ", lastName, " ") AS name FROM person
10 Stands for a root level of the report (see section Structure). 10.sql defines a SQL query for this 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:
John DoeJane MillerFrank Star
I.e., QFQ will simply output the content of the SQL result row after row for each single level.
However, we can modify (wrap) the output by setting the values of various keys for each level: 10.rsep=<br/> for example tells QFQ to seperate the rows of the result by a HTML-line break. The final result in this case is:
10.sql = SELECT id AS personId, CONCAT(firstName, " ", lastName, " ") AS name FROM person
10.sep = <br>
HTML output:
John Doe<br>Jane Miller<br>Frank Star
Syntax¶
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 always 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 the SQL-Query gets executed:
Column values of the recent rows: {{<level>.<columnname>}}
Global variables: {{global.<name>}}
Variables from specific stores: {{<name>[:<store/s>[:<sanitize class>]]}}
Current row index: {{<level>.line.count}}
Total rows (num_rows for SELECT and SHOW, affected_rows for UPDATE and INSERT): {{<level>.line.total}}
Last insert id for INSERT: {{<level>.line.insertId}}
See Variables for a full list of all available variables.
Be aware that line.count / line.total have to be known before the query is fired. E.g. 10.sql = SELECT {{10.line.count}}, ... WHERE {{10.line.count}} = ... won’t work as expected. {{10.line.count}} can’t be replaced before the query is fired, but will be replaced during processing the result!
Different types of SQL queries are possible: SELECT, INSERT, UPDATE, DELETE, SHOW
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. This might be useful to select values which will be accessed later on in another query via the {{level.columnname}} variable. To suppress printing of a column, use a underscore as column name prefix.
Reserved column names have a special meaning and will be processed in a special way. See Processing of columns in the SQL result for details.
There are extensive ways to wrap columns and rows automatically. See Wrapping rows and columns: Level keys
Debug the bodytext¶
The parsed bodytext could be displayed by activating ‘showDebugInfo’ (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: Debug information will only be shown with showDebugInfo=yes in config.ini .
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 make SQL queries, or QFQ records in general, more readable, it’s possible to split a line across several lines. Lines with keywords are on their own (QFQ Keywords (Bodytext) 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>[.<level] {
Example:
10.sql = SELECT 'hello world'
FROM mastertable
10.tail = End
20.sql = SELECT 'a warm welcome'
'some additional', 'columns'
FROM smartTable
WHERE id>100
20.head = <h3>
20.tail = </h3>
Nesting of levels¶
Levels can be nested. E.g.:
10 {
sql = SELECT ...
5 {
sql = SELECT ...
head = ...
}
}
This is equal to:
10.sql = SELECT ...
10.5.sql = SELECT ...
10.5.head = ...
By default, curly braces ‘{}’ are used for nesting. Alternatively angle braces ‘<>’, round braces ‘()’ or square braces ‘[]’ are also possible. To define the braces to use, the first line of the bodytext has to be a comment line and the last character of that line must be one of ‘{}[]()<>’. The corresponding braces are used for that QFQ record. E.g.:
# Specific code. >
10 <
sql = SELECT
head = <script>
data = [
{
10, 20
}
]
</script>
>
Per QFQ tt-content record, only one type of nesting braces can be used.
Be careful to:
write nothing else than whitespaces/newline behind an open brace
the closing brace has to be alone on a line.
10.sql = SELECT 'hello world' 20 { sql = SELECT 'a new query' head = <h1> tail = </h1> } 30 { sql = SELECT 'a third query' head = <h1> tail = </h1> 40 { sql = SELECT 'a nested nested query' } } 30.40.tail = End 50 { sql = SELECT 'A query with braces on their own' }
Access to upper column values¶
Columns of the upper level result can be accessed via variables, eg. {{10.pId}} will be replaced by the value in the pId column.
Levels | A report is divided into levels. Example 1 has 3 levels 10, 20.25, 20.25.10 |
Qualifier | A level is divided into qualifiers 20.30.10 has 3 qualifiers 20, 30, 10 |
Root levels | Is a level with one qualifier. E.g.: 10 |
Sub levels | Is a level with more than one qualifier. E.g. levels 20.25 and 20.30.10 |
Child | The level 20 has one child 20.25 |
Parent | The level 20.25 has a parent 20 |
Example explanation | 10 and 20 are root level and will be executed independently. 10 don’t have a sub level. 20.25 will be executed as many times as 20 has row numbers. 20.30.10 won’t be executed because there isn’t any 20.30 level |
Report Example 1:
# Displays current date
10.sql = SELECT CURDATE()
# Show all students from the person table
20.sql = SELECT p.id AS pId, p.firstName, " - ", p.lastName FROM person AS p WHERE p.typ LIKE "student"
# Show all the marks from the current student ordered chronological
20.25.sql = SELECT e.mark FROM exam AS e WHERE e.pId={{20.pId}} ORDER BY e.date
# This query will never be fired, cause there is no direct parent called 20.30.
20.30.10.sql = SELECT 'never fired'
Wrapping rows and columns: Level keys¶
Order and nesting of queries, will be defined with a typoscript-like syntax: level.sublevel1.subsublevel2. ... Each ‘level’ directive needs a final key, e.g: 20.30.10. sql. A key sql is necessary in order to process a level. All QFQ Keywords (Bodytext).
Processing of columns in the SQL result¶
- The content of all columns of all rows will be printed sequentially, without separator.
- Rows with Special column names will be processed in a special way.
Special column names¶
- Special column names always start with ‘_’.
- Column names, which start with a ‘_’ and which are not reserved (=special column name), will not be printed. Nevertheless, access to it via the {{<level>.<column>}} variable (without ‘_’) are possible.
- The input parameters for the processing function are stored as column values.
- Single parameters are delimited by the ‘|’ character.
- Parameters are identified by the function either
- by their order
- or by a one character qualifier followed by the ‘:’ character, placed in front of the actual parameter value.
Reserved column name | Purpose |
---|---|
_link | Easily create links with different features. |
_mailto | Quickly create email links. A click on the link will open the default mailer. The address is encrypted via JS against email bots. |
_pageX or _PageX | Shortcut version of the link interface for fast creation of internal links. The column name is composed of the string page/Page and a optional character to specify the type of the link. |
_sendmail | Send emails. |
_exec | Run batch files or executables on the webserver. |
_vertical | Render Text vertically. This is useful for tables with limited column width. |
_img | Display images. |
_bullet | Display a blue/gray/green/pink/red/yellow bullet. If none color specified, show nothing |
_check | Display a blue/gray/green/pink/red/yellow checked sign. If none color specified, show nothing |
_<nonReservedName> | Suppress output. Column names with leading underscore are used to select data from the database and make it available in other parts of the report without generating any output. |
Column: _link¶
{{url | display | i (internal), e(external) | - (same),n (new), p (parent), t(top) | -, (e(edit), c(copy), n(new), d(delete), i(insert) , f(file)) }}
- Most URLs will be rendered via class link.
- Column names like _pagee, _mailto, ... are wrapper to class link.
- The parameters for link contains a prefix to make them position-independet.
URL | IMG | Meaning | Qualifier | Example | Description |
---|---|---|---|---|---|
x | URL | u:<url> | u:http://www.example.com | If an image is specified, it will be rendered inside the link, default link class: external | |
x | m:<email> | m:info@example.com | Default link class: email | ||
x | Page | p:<pageId> | p:impressum | Prepend ‘?’ or ‘?id=’, no hostname qualifier (automatically set by browser), default link class: internal, default value: {{pageId}} | |
Text | t:<text> | t:Firstname Lastname | |||
Render | r:<mode> | r:[0-5] | See: render-mode, Default: 0 | ||
x | Picture | P:<filename> | P:bullet-red.gif | Picture ‘<img src=”bullet-red.gif”alt=”....”>’, default link class: internal. | |
x | Edit | E | E | Show ‘edit’ icon as image | |
x | New | N | N | Show ‘new’ icon as image | |
x | Delete | D | D | Show ‘delete’ icon as image (only the icon, no database record ‘delete’ functionality) | |
x | Help | H | H | Show ‘help’ icon as image | |
x | Info | I | I | Show ‘information’ icon as image | |
x | Show | S | S | Show ‘show’ icon as image | |
x | Bullet | B:[<color>] | B:green | Show bullet with ‘<color>’. Colors: blue, gray, green, pink, red, yellow. Default Color: green. | |
x | Check | C:[<color>] | C:green | Show checked with ‘<color>’. Colors: blue, gray, green, pink, red, yellow. Default Color: green. | |
URL Params | U:<key1>=<value1>[&<keyN>=<valueN>] | U:a=value1&b=value2&c=... | Any number of additional Params. Links to forms: U:form=Person&r=1234 | ||
Tooltip | o:<text> | o:More information here | Tooltip text | ||
Alttext | a:<text> | a:Name of person | Alttext for images | ||
Class | c:[n|i|e|<text>] | c:i | CSS class for link. n:no class attribut, i:internal (ext_localconf.php)(default), e:external (ext_localconf.php), <text>: explicit named | ||
Target | g:<text> | g:_blank | target=_blank,_self,_parent,<custom>. Default: no target | ||
Question | q:<text> | q:please confirm | See: question. Link will be executed only if user clicks ok/cancel, default: ‘Please confirm’ | ||
Encryption | e:0|1|... | e:1 | Encryption of the e-mail: 0: no encryption, 1:via Javascript (default) | ||
Right | R | R | Defines picture position: Default is ‘left’ (no definition) of the ‘text’. ‘R’ means ‘right’ of the ‘text’ | ||
SIP | s[:0|1] | s, s:0, s:1 | If ‘s’ or ‘s:1’ a SIP entry is generated with all non Typo 3 Parameters. The URL contains only parameter ‘s’ and Typo 3 parameter | ||
Delete record | x[:a|r|c] | x, x:r, x:c | a: ajax (only QFQ internal used), r: report (default), c: close (current page, open last page) |
Render mode¶
Mode | Both: url & text | Only: url | Only: text | Description |
---|---|---|---|---|
0 (default) | <a href=url>text</a> | <a href=url>url</a> | text or image will be shown, only if there is a url, page or mailto | |
1 | <a href=url>text</a> | <a href=url>url</a> | text | Text or image will be shown, independet of there is a url |
2 | <a href=url>text</a> | no link if text is empty | ||
3 | text | url | text | no link, only text or image |
4 | url | url | text | no link, show text, if text is empty, show url |
5 | nothing at all |
Link Examples¶
SQL-Query | Result |
---|---|
SELECT “m:info@example.com” AS _link | info@example.com as linked text, encrypted with javascript, class=external |
SELECT “m:info@example.com|c:0” AS _link | info@example.com as linked text, not encrypted, class=external |
SELECT “m:info@example.com|P:mail.gif” AS _link | info@example.com as linked image mail.gif, encrypted with javascript, class=external |
SELECT “m:info@example.com|P:mail.gif|o:Email” AS _link | info@example.com as linked image mail.gif, encrypted with javascript, class=external, tooltip: “sendmail” |
SELECT “m:info@example.com|t:mailto:info@example.com|o:Email” AS link | ‘mail to info@example.com‘ as linked text, encrypted with javascript, class=external |
SELECT “u:www.example.com” AS _link | www.example as link, class=external |
SELECT “u:http://www.example.com” AS _link | http://www.example as link, class=external |
SELECT “u:www.example.com|q:Please confirm” AS _link | www.example as link, class=external, See: question |
SELECT “u:www.example.com|c:i” AS _link | http://www.example as link, class=internal |
SELECT “u:www.example.com|c:nicelink” AS _link | http://www.example as link, class=nicelink |
SELECT “p:form_person|c:e” AS _link | <a class=”external” href=”?form_person”>Text</a> |
SELECT “p:form_person¬e=Text|t:Person” AS _link | <a class=”internal” href=”?form_person¬e=Text”>Person</a> |
SELECT “p:form_person|E” AS _link | <a class=”internal” href=”?form_person”><img alttext=”Edit” src=”typo3conf/ext/qfq/Resources/Public/icons/edit.gif”></a> |
SELECT “p:form_person|E|g:_blank” AS _link | <a target=”_blank” class=”internal” href=”?form_person”><img alttext=”Edit” src=”typo3conf/ext/qfq/Resources/Public/icons/edit.gif”></a> |
SELECT “p:form_person|C” AS _link | <a class=”internal” href=”?form_person”><img alttext=”Check” src=”typo3conf/ext/qfq/Resources/Public/icons/checked-green.gif”></a> |
SELECT “p:form_person|C:green” AS _link | <a class=”internal” href=”?form_person”><img alttext=”Check” src=”typo3conf/ext/qfq/Resources/Public/icons/checked-green.gif”></a> |
SELECT “U:form=Person&r=123|x|D” as _link | <a href=”typo3conf/ext/qfq/qfq/api/delete.php?s=badcaffee1234”><span class=”glyphicon glyphicon-trash” ></span>”></a> |
SELECT “U:form=Person&r=123|x|t:Delete” as _link | <a href=”typo3conf/ext/qfq/qfq/api/delete.php?s=badcaffee1234”>Delete</a> |
Question¶
Syntax
q[:<alert text>[:<level>[:<positive button text>[:<negative button text>[:<timeout>[:<flag modal>]]]]]]
- If a user clicks on a link, an alert is shown. If the user answers the alert by clicking on the ‘positive button’, the browser opens the specified link. If the user click on the negative answer (or waits for timout), the alert is closed and the browser does nothing.
- All parameter are optional.
- Parameter are seperated by ‘:’
- To use ‘:’ inside the text, the colon has to be escaped by ‘\’. E.g. ‘ok\: I understand’.
Parameter | Description |
---|---|
Text | The text shown by the alert. HTML is allowed to format the text. Any ‘:’ needs to be escaped. Default: ‘Please confirm’. |
Level | success, info, warning, danger |
Positive button text | Default: ‘Ok’ |
Negative button text | Default: ‘Cancel’ |
Timeout in seconds | 0: no timeout, >0: after the specified time in seconds, the alert will dissapear and behaves like ‘negative answer’ |
Flag modal | 0: Alert behaves not modal. 1: (default) Alert behaves modal. |
Examples:
SQL-Query | Result |
---|---|
SELECT “p:form_person|q:Edit Person:warn” AS _link | Shows alert with level ‘warn’ |
SELECT “p:form_person|q:Edit Person::I do:No way” AS _link | Instead of ‘Ok’ and ‘Cancel’, the button text will be ‘I do’ and ‘No way’ |
SELECT “p:form_person|q:Edit Person:::10” AS _link | The Alert will be shown 10 seconds |
SELECT “p:form_person|q:Edit Person:::10:0” AS _link | The Alert will be shown 10 seconds and is not modal. |
Columns: _page[X]¶
The colum name is composed of the string page and a trailing character to specify the type of the link.
Syntax
SELECT "[options]" AS _page[<link type>]
with: [options] = [p:<page & param>]|[t:<text>]|[o:<tooltip>]|[q:<question parameter>]|[c:<class>]|[g:<target>]|[r:<render mode>]
<link type> = c,d,e,h,i,n,s
column name | Purpose | default value of question parameter | Mandatory parameters |
---|---|---|---|
_page | Internal link without a grafic | empty | p:<pageId>[¶m] |
_pagec | Internal link without a grafic, with question | Please confirm! | p:<pageId>[¶m] |
_paged | Internal link with delete icon (trash) | Delete record ? | U:form=<formname>&r=<record id> or U:table=<tablename>&r=<record id> |
_pagee | Internal link with edit icon (pencil) | empty | p:<pageId>[¶m] |
_pageh | Internal link with help icon (question mark) | empty | p:<pageId>[¶m] |
_pagei | Internal link with information icon (i) | empty | p:<pageId>[¶m] |
_pagen | Internal link with new icon (sheet) | empty | p:<pageId>[¶m] |
_pages | Internal link with how icon (magnifier) | empty | p:<pageId>[¶m] |
- All parameter are optional.
- Optional set of predefined icons.
- Optional set of dialog boxes.
Parameter | Description | Default value | Example |
---|---|---|---|
<page> | TYPO3 page id or page alias. | The current page: {{pageId}} | 45 application application&N_param1=1045 |
<text> | Text, wrapped by the link. If there is an icon, text will be displayed to the right of it. | empty string | |
<tooltip> | Text to appear as a ToolTip | empty string | |
<question> | If there is a question text given, an alert will be opened. Only if the user clicks on ‘ok’, the link will be called | Expected “=” to follow “see” | |
<class> | CSS Class for the <a> tag | The default class defined for internal links in ext_localconf.php (see ...) | |
<target> | Parameter for HTML ‘target=’. F.e.: Opens a new window | empty | P |
<rendermode> | Show/render a link at all or not. See render-mode 0-5 | ||
<create sip> | s | ‘s’: create a SIP |
Column: _paged¶
These column offers a link, with a confirmation question, to delete one record (mode ‘table’) or a bunch of records (mode ‘form’). After deleting the record(s), the current page will be reloaded in the browser.
Syntax
SELECT "U:table=<tablename>&r=<record id>|q:<question>|..." AS _paged
SELECT "U:form=<formname>&r=<record id>|q:<question>|..." AS _paged
If the record to delete contains column(s), whose columnname match on %pathFileName% and such a column points to a real existing file, such a file will be deleted too. If the table contains records where the specific file is multiple times referenced, than the file is not deleted (it would break the still existing references). Multiple references are not found, if they use different colummnnames or tablenames.
Mode: table¶
- table=<table name>
- r=<record id>
Deletes the record with id ‘<record id>’ from table ‘<table name>’.
Mode: form¶
- form=<form name>
- r=<record id>
Deletes the record with id ‘<record id>’ from the table specified in form ‘<form name>’ as primary table. Additional action FormElement of type beforeDelete or afterDelete will be fired too.
Examples:¶
SELECT 'U:table=Person&r=123|q:Do you want delete John Doe?' AS _paged
SELECT 'U:form=person-main&r=123|q:Do you want delete John Doe?' AS _paged
Columns: _Page[X]¶
- Similar to _page[X]
- Parameter are position dependent and therefore without a qualifier!
"[<page id|alias>[¶m=value&...]] | [text] | [tooltip] | [question parameter] | [class] | [target] | [render mode]" as _Pagee.
Column: _Paged¶
- Similar to _paged
- Parameter are position dependent and therefore without a qualifier!
"[table=<table name>&r-<record id>[¶m=value&...] | [text] | [tooltip] | [question parameter] | [class] | [render mode]" as _Paged.
"[form=<form name>&r-<record id>[¶m=value&...] | [text] | [tooltip] | [question parameter] | [class] | [render mode]" as _Paged.
Column: _vertical¶
Render text vertically. This is useful for tables with limited column width. The vertical rendering is achieved via CSS tranformations (rotation) defined in the style attribute of the wrapping tag. You can optionally specify the rotation angle.
Syntax
SELECT "<text>|[<angle>]|[<width>]|[<height>]|[<wrap tag>]" AS _vertical
Parameter | Description | Default value |
---|---|---|
<text> | The string that should be rendered vertically. | none |
<angle> | How many degrees should the text be rotated? The angle is measured clockwise from baseline of the text. | 270 |
<width> | Width (of what?). Needs to have a CSS_unit (e.g. px, em) specified. (Implemented?) | 1em |
<height> | Height (of what?). Needs to have a CSS-unit (e.g. px, em) specified. (Implemented?) | none |
<wraptag> | What tag should be used to wrap the vertical text? Possible options are div, span, etc. | div |
Minimal Example
10.sql = SELECT "Hallo" AS _vertical
Advanced Examples
10.sql = SELECT "Hallo|90" AS _vertical
20.sql = SELECT "Hallo|90|3em|7em|span" AS _vertical
Column: _mailto¶
Easily create Email links.
Syntax
SELECT "<email address>|[<link text>]" AS _mailto
Parameter | Description | Default value |
---|---|---|
<emailaddress> | The email address where the link should point to. | none |
<linktext> | The text that should be displayed on the website and be linked to the email address. This will typically be the name of the recipient. If this parameter is omitted, the email address will be displayed as link text. | none |
Minimal Example
10.sql = SELECT "john.doe@example.com" AS _mailto
Advanced Example
10.sql = SELECT "john.doe@example.com|John Doe" AS _mailto
Column: _sendmail¶
<TO:email[,email]>|<FROM:email>|<subject>|<body>|[<REPLY-TO:email>]|[<flag autosubmit: on /off>]|[<grid>]|[xId]|<CC:email[,email]>|<BCC:email[,email]>
Send text emails. Every mail will be logged in the table mailLog.
Syntax
SELECT "john@doe.com|jane@doe.com|Reminder tomorrow|Please dont miss the meeting tomorrow" AS _sendmail
Parameter | Description | Required |
---|---|---|
TO:email[,email] | Comma-separated list of receiver email addresses. Optional: realname <john@doe.com> | yes |
FROM:email | Sender of the email. Optional: ‘realname <john@doe.com>’ | yes |
subject | Subject of the email | yes |
body | Message | yes |
REPLY-TO:email | Email address to reply to (if different from sender) | |
flagAutoSubmit ‘on’ / ‘off’ | If ‘on’ (default), add mail header ‘Auto-Submitted: auto-send’ - suppress OoO replies | |
grId | Will be copied to the mailLog record. Helps to setup specific logfile queries | |
xId | Will be copied to the mailLog record. Helps to setup specific logfile queries | |
CC:email[,email] | Comma-separated list of receiver email addresses. Optional: ‘realname <john@doe.com>’ | |
BCC:email[,email] | Comma-separated list of receiver email addresses. Optional: ‘realname <john@doe.com>’ |
Minimal Example
10.sql = SELECT "john.doe@example.com|company@example.com|Latest News|The new version is now available." AS _sendmail
This will send an email with subject Latest News from company@example.com to john.doe@example.com.
Advanced Examples
10.sql = SELECT "customer1@example.com,Firstname Lastname <customer2@example.com>, Firstname Lastname <customer3@example.com>|company@example.com|Latest News|The new version is now available.|sales@example.com|on|101|222|ceo@example.com|backup@example.com" AS _sendmail
This will send an email with subject Latest News from company@example.com to customer1, customer2 and customer3 by using a realname for customer2 and customer3 and suppress generating of OoO answer if any receiver is on vacation. Additional the CEO as well as backup will receive the mail via CC and BCC.
Column: _img¶
Renders images. Allows to define an alternative text and a title attribute for the image. Alternative text and title text are optional.
- If no alternative text is defined, an empty alt attribute is rendered in the img tag (since this attribute is mandatory in HTML).
- If no title text is defined, the title attribute will not be rendered at all.
Syntax
SELECT "<path to image>|[<alt text>]|[<title text>]" AS _img
Parameter | Description | Default value/behaviour |
---|---|---|
<pathtoimage> | The path to the image file. | none |
<alttext> | Alternative text. Will be displayed if image can’t be loaded (alt attribute of img tag). | empty string |
<titletext> | Text that will be set as image title in the title attribute of the img tag. | no title attribute rendered |
Minimal Example
10.sql = SELECT "fileadmin/img/img.jpg" AS _img
Advanced Examples
10.sql = SELECT "fileadmin/img/img.jpg|Aternative Text" AS _img # alt="Alternative Text, no title
20.sql = SELECT "fileadmin/img/img.jpg|Aternative Text|" AS _img # alt="Alternative Text, no title
30.sql = SELECT "fileadmin/img/img.jpg|Aternative Text|Title Text" AS _img # alt="Alternative Text, title="Title Text"
40.sql = SELECT "fileadmin/img/img.jpg|Alternative Text" AS _img # alt="Alternative Text", no title
50.sql = SELECT "fileadmin/img/img.jpg" AS _img # empty alt, no title
60.sql = SELECT "fileadmin/img/img.jpg|" AS _img # empty alt, no title
70.sql = SELECT "fileadmin/img/img.jpg||Title Text" AS _img # empty alt, title="Title Text"
80.sql = SELECT "fileadmin/img/img.jpg||" AS _img # empty alt, no title
Column: _exec¶
Runs batch files or executables on the webserver. In case of an error, returncode and errormessage will be returned.
Syntax
<command>
Parameter | Description | Default value |
---|---|---|
<command> | The command that should be executed on the server. | none |
Minimal Examples
10.sql = SELECT "ls -s" AS _exec
20.sql = SELECT "./batchfile.sh" AS _exec
Column: _F¶
Challenge 1¶
Due to the limitations of MySQL, reserved column names can’t be further concatenated. Assume you want to display an image:
# This is valid:
10.sql = SELECT concat("/static/directory/", p.foto) AS _img FROM person AS p WHERE ...
# Returns:
<img src=...>
Now assume you want to wrap the image in a div tag:
# This is valid:
10.sql = SELECT "<div>", CONCAT("/static/directory/", p.foto) AS _img, "</div>" FROM person AS p WHERE ...
# Returns:
<div><img src=...></div>
The example above works fine - however, as soon as you want to use field wrappers, things get messy:
# This is valid:
10.sql = SELECT "<div>", CONCAT("/static/directory/", p.foto) AS _img, "</div>" FROM person AS p WHERE ...
10.fbeg = <td>
10.fend = </td>
# Returns:
<td><div></td><td><img src=...></td><td></div></td>
To achieve the desired result, one might want to try something like this:
# This is NOT valid:
10.sql = SELECT CONCAT("<div>", concat("/static/directory/", p.foto) AS _img, "</div>") FROM person AS p WHERE ...
10.fbeg = <td>
10.fend = </td>
# Returns a MySQL error because nesting concat() -functions is not allowed
Challenge 2¶
Assume you have multiple columns with reserved names in the same query and want to use one of them in a later query:
10.sql = SELECT CONCAT("/static/directory/", g.picture) AS _img, CONCAT("/static/preview/", g.thumbnail) AS _img FROM gallery AS g WHERE ...
20.sql = SELECT "{{10.img}}", d.text FROM description AS d ...
The example above will fail because there are two img columns which can not be distinguished.
Solution¶
The reserved column ‘F’(=Format) can be used to
- further wrap columns with a reserved name
- assign an arbitrary name to a column built through a reserved name to make it accessible in later queries.
Solution for #Challenge_1:
10.sql = SELECT CONCAT("Q:img|T:div") AS wrappedImg FROM person AS p WHERE ...
10.fbeg = <td>
10.fend = </td>
# Returns:
<td><div><img src=...></div></td>
Solution for #Challenge_2:
10.sql = SELECT CONCAT("Q:img|V:mypic") AS wrappedImg FROM person AS p WHERE ...
20.sql = SELECT "{{10.mypic}}" ...
Parameter | Description | Required |
---|---|---|
Q | Any of the reserved column names | |
Z | Process the column but don’t display it | |
X | Strip tags / Remove all tags | |
T | Wrap the column with the defined tag. F.e.: T:tdcolspan=”2” | |
V | Define an unambiguous variable name for this colum. F.e.: V:someName | |
Add all the parameters required for the column defined with Q: |
Include a form via link/url¶
In most occasions it is handy if a formname does not have to be hard-coded in report but can be passed to the page as a parameter. To achieve this, first build a link on page A which includes the required parameters:
page A
10.sql = SELECT CONCAT("p:pageB|U:form=Person&r=", id) AS _pagee FROM ...
The above example builds a link to pageB - refer to the Column: _link-manual for details. The link tells page B to render the form with name formname and load the record with id id for editing.
QFQ CSS Classes¶
- qfq-table-50, qfq-table-80 - release the default width of 100% and specify minwidth=50% resp. 80%.
- Background Color: qfq-color-grey-1, qfq-color-grey-2 (table, row, cell)
- Table: table
- Table > hover: table-hover
- Table > condensed: table-condensed
E.g.:
10.sql = SELECT id, name, firstName, ...
10.head = <table class='table table-condensed qfq-table-50'>
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 exp_person AS p
Result:
BillieElvisLouisDiana
Real data, two columns
10.sql = SELECT p.firstName, p.lastName FROM exp_person AS p
Result:
BillieHolidayElvisPresleyLouisArmstrongDianaRoss
The result of the SQL query is output row by row and column by column without adding any formatting information. See Formatting Examples for examples of how the output can be formatted.
Formatting Examples¶
Formating (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 formating information as a coloum
10.sql = SELECT p.firstName, " " , p.lastName, "'<br /'>" FROM exp_person AS p
Result:
Billie Holiday
Elvis Presley
Louis Armstrong
Diana Ross
One column ‘rend’
10.sql = SELECT p.name FROM exp_person AS p
10.rend = <br />
Result:
Billie Holiday
Elvis Presley
Louis Armstrong
Diana Ross
More HTML
10.sql = SELECT p.name FROM exp_person 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 exp_person AS p
head = <ul>
tail = </ul>
rbeg = <li>
rend = </li>
}
Two queries:
10.sql = SELECT p.name FROM exp_person AS p
10.rend = <br />
20.sql = SELECT a.street FROM exp_address AS a
20.rend = <br />
Two queries: nested
# outer query
10.sql = SELECT p.name FROM exp_person AS p
10.rend = <br />
# inner query
10.10.sql = SELECT a.street FROM exp_address 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 exp_person AS p
10.rend = <br />
# inner query
10.10.sql = SELECT a.street FROM exp_address 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 exp_person AS p
10.rend = <br />
# inner query
10.10.sql = SELECT a.street FROM exp_address 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 exp_person AS p
rend = <br />
10 {
# inner query
sql = SELECT a.street FROM exp_address AS a WHERE a.pId='{{10.pId}}'
rend = <br />
}
}
- Columns starting with a ‘_’ won’t be printed but can be accessed as regular columns.