elements. Easy way to make checkboxes or radio vertical instead of horizontal.
* 2: Any number >1 makes the 'select' input 'multiple' ready.
* See: https://www.w3.org/TR/html5/forms.html#file-upload-state-(type=file)
* All 'native' *FormElements* like 'input', 'checkbox', ...
'autofocus': The first *FormElement* with this attribute will get the focus after form load. If there is no such attribute
given to any *FormElement*, the attribute will be automatically assigned to the first editable *FormElement*.
To disable 'autofocus' on a form, set 'autofocus=0' on the first editable *FormElement*.
Note: If there are multiple pills defined on a form, only the first pill will be set with 'autofocus'.
Type: checkbox
^^^^^^^^^^^^^^
Checkboxes can be rendered in mode:
* *single*:
* One column in a table corresponds to one checkbox.
* The value for statuses *checked* and *unchecked* are free to choose.
* This mode is selected, if a) *checkBoxMode* = single, or b) *checkBoxMode* is missing **and** the number of fields of the column definition is <3.
* *FormElement.parameter*:
* *checkBoxMode* = single (optional)
* *checked* =
(optional, the value which represents 'checked')
* If *checked* is empty or missing: If *type* = 'enum' or 'set', get first item of the definition. If *type* = string, get default.
* *unchecked* = (optional, the value which represents 'unchecked')
* If *unchecked* is empty or missing: If *type* = 'enum' or 'set', get second item of checked. If *type* = 'string', get ''.
* *label2* = (Text right beside checkbox) (optional)
* *multi*:
* One column in a table represents multiple checkboxes. This is typically useful for the column type *set*.
* The value for status *checked* are free to choose, the value for status *unchecked* is always the empty string.
* Each field key (or the corresponding value from the key/value pair) will be rendered right beside the checkbox.
* *FormElement.parameter*
* *checkBoxMode*: multi
* *itemList* - E.g.:
* ``itemList=red,blue,orange``
* ``itemList=1:red,2:blue,3:orange``
* ``itemList={{!SELECT id, value FROM someTable}}``
* *FormElement.maxlength* - vertical or horizontal alignment:
* Value: '', 0, 1 - The check boxes will be aligned vertical.
* Value: >1 - The check boxes will be aligned horizontal, with a linebreak every 'value' elements.
* *FormElement.parameter*:
* *emptyHide*: Existence of this item hides an entry with an empty string. This is useful for e.g. Enums, which have an empty
entry, but the empty value should not be selectable.
* *emptyItemAtStart*: Existence of this item inserts an empty entry at the beginning of the selectlist.
* *emptyItemAtEnd*: Existence of this item inserts an empty entry at the end of the selectlist.
* *buttonClass*: Instead of the plain HTML checkbox fields, Bootstrap
`buttons `_. are rendered as `checkbox` elements. Use
one of the following `classes `_:
* `btn-default` (default, grey),
* `btn-primary` (blue),
* `btn-success` (green),
* `btn-info` (light blue),
* `btn-warning` (orange),
* `btn-danger` (red).
With a given *buttonClass*, all buttons (=radios) are rendered horizontal. A value in *FormElement.maxlength* has no effect.
* *No preselection*:
* If a form is in 'new' mode and if there is a default value configured on a table column, such a value is shown by default.
There might be situations, where the user should be forced to select a value (e.g. specifying the gender). An unwanted
default value can be suppressed by specifying an explicit definition on the FormElement field `value`::
{{:RZ}}
For existing records the shown value is as expected the value of the record. For new records, it's the value `0`,
which is typically not one of the ENUM / SET values and therefore nothing is selected.
Type: date
^^^^^^^^^^
* Range datetime: '1000-01-01' to '9999-12-31' or '0000-00-00'. (http://dev.mysql.com/doc/refman/5.5/en/datetime.html)
* Optional:
* *FormElement.parameter.dateFormat*: yyyy-mm-dd | dd.mm.yyyy
Type: datetime
^^^^^^^^^^^^^^
* Range datetime: '1000-01-01 00:00:00' to '9999-12-31 23:59:59' or '0000-00-00 00:00:00'. (http://dev.mysql.com/doc/refman/5.5/en/datetime.html)
* Optional:
* *FormElement.parameter*:
* *dateFormat*: yyyy-mm-dd | dd.mm.yyyy
* *showSeconds*: 0|1 - shows the seconds. Independent if the user specifies seconds, they are displayed '1' or not '0'.
* *showZero*: 0|1 - For an empty timestamp, With '0' nothing is displayed. With '1' the string '0000-00-00 00:00:00' is displayed.
Type: extra
^^^^^^^^^^^
* Element is not shown in the browser.
* The element can be used to define / precalculate values for a column, which do not already exist as a native *FormElement*.
* The element is build / computed on form load and saved alongside with the SIP parameter of the current form.
* Access the value without specifying any store (default store priority is sufficient).
Type: text
^^^^^^^^^^
* General input for text and number.
* *FormElement.size*:
* : width of input element in characters. Lineheight = 1.
* ,: input element = textarea, width=, height=
* *FormElement.parameter*:
* *retype* = 1 (optional): Current input element will be rendered twice. The form can only submitted if both elements are equal.
* *retypeLabel* = (optional): The label of the second element.
* *retypeNote* = (optional): The note of the second element.
* Also check the :ref:`fe-parameter-attributes` *data-...-error* to customize error messages shown by the validator.
Type: editor
^^^^^^^^^^^^
* TinyMCE (https://www.tinymce.com, community edition) is used as the QFQ Rich Text Editor.
* The content will be saved as HTML inside the database.
* All configuration and plugins will be configured via the 'parameter' field. Just prepend the word 'editor-' in front
of each TinyMCE keyword. Check possible options under https://www.tinymce.com/docs/configure/,
https://www.tinymce.com/docs/plugins/, https://www.tinymce.com/docs/advanced/editor-control-identifiers/#toolbarcontrols
* Bars:
* Top: *menubar* - by default hidden.
* Top: *toolbar* - by default visible.
* Bottom: *statusbar* - by default hidden, exception: *min_height* and *max_height* are given via size parameter.
* The default setting in *FormElement.parameter* is::
editor-plugins=code link searchreplace table textcolor textpattern visualchars
editor-toolbar=code searchreplace undo redo | styleselect link table | fontselect fontsizeselect | bullist numlist outdent indent | forecolor backcolor bold italic editor-menubar=false
editor-statusbar=false
* *FormElement.size*:
* ,: in pixels, including top and bottom bars. E.g.: 300,600
Type: note
^^^^^^^^^^
An FormElement without any 'input' functionality -just to show some text. Use the typical fields 'label', 'value' and
'note' to be displayed in the corresponding three standard columns.
Type: password
^^^^^^^^^^^^^^
* Like a `text` element, but every character is shown as an asterisk.
Type: radio
^^^^^^^^^^^
* Radio Buttons will be built from one of three sources:
1. 'sql1': E.g. *{{!SELECT type AS label FROM car }}* or *{{!SELECT type AS label, typeNr AS id FROM car}}* or *{{!SHOW tables}}*.
* Resultset format 'named': column 'label' and optional a column 'id'.
* Resultset format 'index':
* One column in resultset >> first column represents *label*
* Two or more columns in resultset >> first column represents *id* and second column represents *label*.
2. *FormElement.parameter*:
* *itemList* = `` E.g.: *itemList=red,blue,orange* or *itemList=1:red,2:blue:3:orange*
3. Definition of the *enum* or *set* field (only labels, ids are not possible).
* *FormElement.maxlength* = ``
* Applies only to 'plain' radio elements (not the Bootstrap 'buttonClass' from below)
* *vertical* or *horizontal* alignment:
* ``: '', 0, 1 - The radios will be aligned *vertical*.
* ``: >1 - The readios will be aligned *horizontal*, with a linebreak every 'value' elements.
* *FormElement.parameter*:
* *emptyHide*: Existence of this item hides an entry with an empty string. This is useful for e.g. Enums, which have an empty
entry, but the empty value should not be selectable.
* *emptyItemAtStart*: Existence of this item inserts an empty entry at the beginning of the selectlist.
* *emptyItemAtEnd*: Existence of this item inserts an empty entry at the end of the selectlist.
* *buttonClass*: Instead of the plain radio fields, Bootstrap
`buttons `_. are rendered as `radio` elements. Use
one of the following `classes `_:
* `btn-default` (default, grey),
* `btn-primary` (blue),
* `btn-success` (green),
* `btn-info` (light blue),
* `btn-warning` (orange),
* `btn-danger` (red).
With a given *buttonClass*, all buttons (=radios) are rendered horizontal. A value in *FormElement.maxlength* has no effect.
* *No preselection*:
* If there is a default configured on a table column, such a value is selected by default. If the user should actively
choose an option, the 'preselection' can be omitted by specifying an explicit definition on the FormElement field `value`::
{{:RZ}}
For existing records the shown value is as expected the value of the record. For new records, it's the value `0`,
which is typically not one of the ENUM values and therefore nothing is selected.
Type: select
^^^^^^^^^^^^
* Select lists will be built from one of three sources:
* 'sql1': E.g. *{{!SELECT type AS label FROM car }}* or *{{!SELECT type AS label, typeNr AS id FROM car}}* or *{{!SHOW tables}}*.
* Resultset format 'named': column 'label' and optional a column 'id'.
* Resultset format 'index':
* One column in resultset >> first column represents *label*
* Two or more columns in resultset >> first column represents *id* and second column represents *label*.
* *FormElement.parameter*:
* *itemList* = `` - E.g.: *itemList=red,blue,orange* or *itemList=1:red,2:blue:3:orange*
* Definition of the *enum* or *set* field (only labels, ids are not possible).
* *FormElement.size*: ``
* ``: |0|1: Dropdown list.
* ``: >1: Select field with *size* rows height. Multiple selection of items is possible.
* *FormElement.parameter*:
* *emptyItemAtStart*: Existence of this item inserts an empty entry at the beginning of the selectlist.
* *emptyItemAtEnd*: Existence of this item inserts an empty entry at the end of the selectlist.
* *emptyHide*: Existence of this item hides the empty entry. This is useful for e.g. Enums, which have an empty
entry and the empty value should not be an option to be selected.
Type: subrecord
^^^^^^^^^^^^^^^
The *FormElement* type 'subrecord' renders a list of records (so called secondary records), typically to show, edit, delete
or add new records. The list is defined as a SQL query. The number of records shown is not limited. These *FormElement*
will be rendered inside the form as a HTML table.
* *sql1*: SQL query to select records. E.g.::
{{!SELECT a.id AS id, CONCAT(a.street, a.streetnumber) AS a, a.city AS b, a.zip AS c FROM Address AS a}}
* Notice the **exclamation mark** after '{{' - this is necessary to return an array of elements, instead of a single string.
* Exactly one column **'id'** has to exist; it specifies the primary record for the target form.
In case the id should not be visible to the user, it has to be named **'_id'**.
* Columnname: *[title=][|[width=]<number>][|nostrip][|icon][|link][|url][|mailto][|_rowClass][|_rowTitle]*
* All parameter are position independet.
* Separate parameter by '|'.
* *[title=]<text>*: Title of the column. The keyword 'title=' is optional. Columns with a title starting with '_' won't be rendered.
* *[width=]<number>*: Max. width of chars displayed per cell. The keyword 'width=' is optional. Default max width: 20.
This setting also affects the title of the column.
* *nostrip*: by default, html tags will be stripped off the cell content before rendering. This protects the table
layout. 'nostrip' deactivates the cleaning to make pure html possible.
* *icon*: the cell value contains the name of an icon in *typo3conf/ext/qfq/Resources/Public/icons*. Empty cell values
will omit an html image tag (=nothing rendered in the cell).
* *link*: value will be rendered as described under :ref:`column-link`
* *url*: value will be rendered as a href url.
* *mailto*: value will be rendered as a href mailto.
* *_rowClass*
* The value is a CSS class name(s) which will be rendered in the *<tr class="<_rowClass>">* of the subrecord table.
* The column itself is hidden to the user.
* By using Bootstrap, the following predefined classes are available:
* Text color: *text-muted|text-primary|text-success|text-info|text-warning|text-danger* (http://getbootstrap.com/css/#helper-classes)
* Row background: *active|success|info|warning|danger* (http://getbootstrap.com/css/#tables-contextual-classes)
* *_rowTitle*
* Defines the title attribute of a subrecod table row (tooltip).
* Examples::
SELECT note1 AS 'Comment', note2 AS 'Comment|50' , note3 AS 'title=Comment|width=100|nostrip', note4 AS '50|Comment',
'checked.png' AS 'Status|icon', email AS 'mailto', CONCAT(homepage, '|Homepage') AS 'url',
ELT(status,'info','warning','danger') AS '_rowClass', help AS '_rowTitle' ...
* *FormElement.parameter*
* *form*: Target form, e.g. *form=person*
* *page*: Target page with detail form. If none specified, use the current page.
* *title*: Title displayed over the table in the current form.
* *extraDeleteForm*: Optional. The per row delete Button will reference the form specified here (for deleting) instead of the default (*form*).
* *detail*: Mapping of values from the primary form to the target form (defined via `form=...`).
* Syntax::
<source table column name 1|&constant 1>:<target column name 1>[,<source table column name 2|&constant 2>:<target column name 2>][...]
* Example: *detail=id:personId,&12:xId,&{{a}}:personId*
* By default, the given value will overwrite values on the target record. In most situations, this is the wished behaviour.
* Exceptions of the default behaviour have to be defined on the target form in the corresponding *FormElement* in the
field *value* by changing the default Store priority definition. E.g. `{{<columnname>:RS0}}` - For existing records,
the store `R` will provide a value. For new records, store `R` is empty and store S will be searched for a value:
the value defined in `detail` will be choosen. At last the store '0' is defined as a fallback.
* *source table column name*: E.g. A person form is opened with person.id=5 (r=5). The definition `detail=id:personId`
and `form=address` maps person.id to address.personId. On the target record, the column personId becomes '5'.
* *Constant '&'*: Indicate a 'constant' value. E.g. `&12:xId` or `{{...}}` (all possibilities, incl. further SELECT
statements) might be used.
Type: time
^^^^^^^^^^
* Range time: '00:00:00' to '23:59:59' or '00:00:00'. (http://dev.mysql.com/doc/refman/5.5/en/datetime.html)
* Optional:
* *FormElement.parameter*
* *showSeconds*: 0|1 - shows the seconds. Independent if the user specifies seconds, they are displayed '1' or not '0'.
* *showZero*: 0|1 - For an empty timestamp, With '0' nothing is displayed. With '1' the string '00:00[:00]' is displayed.
Type: upload
^^^^^^^^^^^^
* See: https://www.w3.org/TR/html5/forms.html#file-upload-state-(type=file)
An upload element is based on a 'file browse'-button and a 'trash'-button (=delete). Only one of them is shown at a time.
The 'file browse'-button is displayed, if there is no file uploaded already.
The 'trash'-button is displayed, if there is a file uploaded already.
After clicking on the browse brutton , the user can select a file from the local filesystem.
After choosing the file, the upload starts immediately, shown by a turning wheel. When the server received the whole file
and accepts the file, the 'file browse'-button dissappears and the filename is shown, followed by a 'trash'-button.
Either the user is satisfied now or the user can delete the uploaded file (and maybe upload another one).
Until this point, the file is cached on the server but not copied to the `fileDestination`. The user have to save the
current record, either to finalize the upload or to delete a previous uploaded file.
The FormElement behaves like a 'native FormElement' (showing controls/text on the form) as well as an 'action FormElement'
by fireing queries and doing some additional actions during form save. Inside the *Form editor* it's shown as a 'native FormElement'.
* *FormElement.parameter*:
* *accept*: `image/*,video/*,audio/*,.doc,.docx,.pdf,<mime type>`
* *fileDestination*: Destination where to copy the file. A good practice is to specify a relative `fileDestination` -
such an installation (filesystem and database) are moveable.
* If the original filename should be part of `fileDestination`, the variable *{{filename}}* (STORE_VARS) can be used. Example ::
fileDestination={{SELECT 'fileadmin/user/pictures/', p.name, '-{{filename}}' FROM Person AS p WHERE p.id={{id:R0}} }}
* The original filename will be sanatized: only alnum characters are allowed. German 'umlaut' will be replaced by
'ae', 'ue', 'oe'. All non valid characters will be replaced by '-'.
* If a file already exist under `fileDestination`, an error message is shown and 'save' is aborted. The user has no
possibility to overwrite the already existing file. If the whole workflow is correct, this situation should no
arise. Check also *fileReplace* below.
* All necessary subdirectories in `fileDestination` are automatically created.
* Using the current record id in the `fileDestination`: Using {{r}} is problematic for a 'new' primary record: that
one is still '0' at the time of saving. Use `{{id:R0}}` instead.
* *slaveId*, *sqlBefore*, *sqlInsert*, *sqlUpdate*, *sqlDelete*, *sqlUpdate*, *sqlAfter*: Only used in :ref:`Upload advanced mode`.
* *fileReplace=always*: If `fileDestination` exist - replace it by the new one.
Deleting a record and the referenced file
'''''''''''''''''''''''''''''''''''''''''
If the user deletes a record (e.g. pressing the delete button on a form) which contains reference(s) to files, such files
are deleted too. Slave records, which might be also deleted through a 'delete'-form, are *not* checked for file references
and therefore such files are not deleted on the filesystem.
Only columns where the columname contains `pathFileName` are checked for file references. Therefore, always choose a
columnanme which contains `pathFileName`.
If there are other records, which references the same file, such files are not deleted.
It's a very basic check: just the current column of the current table is compared. In general it's not a good idea to
have mutliple references to a single file. Therefore this check is just a fallback.
.. _Upload simple mode:
Upload simple mode
;;;;;;;;;;;;;;;;;;
Requires: *'upload'-FormElement.name = 'column name'* of an column in the primary table.
After moving the file to `fileDestination`, the current record/column will be updated to `fileDestination`.
The database definition of the named column has to be a string variant (varchar, text but not numeric or else).
On form load, the column value will be displayed as path/filename.
Deleting an uploaded file in the form (by clicking on the trash near beside) will delete
the file on the filesystem as well. The column will be updated to an empty string.
This happens automatically without any further definiton in the 'upload'-FormElement.
Multiple 'upload'-FormElements per form are possible. Each of it needs an own table column.
.. _Upload advanced mode:
Upload advanced mode
;;;;;;;;;;;;;;;;;;;;
Requires: *'upload'-FormElement.name* is unknown as a column in the primary table.
This mode will serve further database structure scenarios.
A typical name for such an 'upload'-FormElement, to show that the name does not exist in the table, might start with 'my', e.g. 'myUpload1'.
* *FormElement.value*: The path/filename, shown during 'form load' to indicate a previous uploaded file, has to be queried
with this field. E.g.::
{{SELECT pathFilenamePicture FROM Note WHERE id={{slaveId}} }}
* *FormElement.parameter*:
* *fileDestination*: determine the path/filename. E.g.::
fileDestination=fileadmin/person/{{name:R0}}_{{id:R}}/uploads/picture_{{filename}}
* *slaveId*: Defines the target record where to retrieve and store the path/filename of the uploaded file. Check also :ref:`slave-id`. E.g.::
slaveId={{SELECT id FROM Note WHERE pId={{id:R0}} AND type='picture' LIMIT 1}}
* *sqlBefore*: fired during a form save, before the following queries are fired.
* *sqlInsert*: fired if `slaveId=0` and an upload exist (user has choosen a file)::
sqlInsert={{INSERT INTO Note (pId, type, pathFileName) VALUE ({{id:R0}}, 'image', '{{fileDestination}}') }}
* *sqlUpdate*: fired if `slaveId>0` and an upload exist (user has choosen a file). E.g.::
sqlUpdate={{UPDATE Note SET pathFileName = '{{fileDestination}}' WHERE id={{slaveId}} LIMIT 1}}
* *sqlDelete*: fired if `slaveId>0` and no upload exist (user has not choosen a file). E.g.::
sqlDelete={{DELETE FROM Note WHERE id={{slaveId:V}} LIMIT 1}}
* *sqlAfter*: fired after all previous queries have been fired. Might update the new created id to a primary record. E.g.::
sqlUpdate={{UPDATE Person SET noteIdPicture = {{slaveId}} WHERE id={{id:R0}} LIMIT 1 }}
.. _class-action:
Class: Action
-------------
Type: before... | after...
^^^^^^^^^^^^^^^^^^^^^^^^^^
These type of 'action' *FormElements* will be used to implement data validation or creating/updating additional records.
Types:
* beforeLoad
* good to grant access permission.
* afterLoad
* beforeSave
* good to prohibit creating of duplicate records.
* afterSave
* good to create & update additional records.
* beforeInsert
* afterInsert
* beforeUpdate
* afterUpdate
* beforeDelete
* afterDelete
sqlValidate
'''''''''''
Perform checks by fireing a SQL query and expecting a predefined number of selected records.
* OK: the `expectRecords` number of records has been selected. Continue processing the next *FormElement*.
* Fail: the `expectRecords` number of records has not been selected (less or more): Display the error message
`messageFail` and abort the whole (!) current form load or save.
*FormElement.parameter*:
* *requiredList* - List of `native`-*FormElement* names: only if all of those elements are filled (!=0 and !=''), the *current*
`action`-*FormElement* will be processed. This will enable or disable the check, based on the user input! If no
`native`-*FormElement* names are given, the specified check will always be performed.
* *sqlValidate* - validation query. E.g.: `sqlValidate={{!SELECT id FROM Person AS p WHERE p.name LIKE {{name:F:all}} AND p.firstname LIKE {{firstname:F:all}} }}`
* Pay attention to `{{!...` after the equal sign.
* *expectRecords* - number of expected records.
* *expectRecords* = `0` or *expectRecords* = `0,1` or *expectRecords* = `{{SELECT COUNT(id) FROM Person}}`
* Separate multiple valid record numbers by ','. If at least one of those matches, the check will pass successfully.
* *messageFail* - Message to show. E.g.: *messageFail* = `There is already a person called {{firstname:F:all}} {{name:F:all}}`
.. _slave-id:
slaveId
'''''''
*FormElement.parameter*:
* *slaveId*:
* Auto fill: name the action `action`-*FormElement* equal to an existing column (table from the current form definition).
*slaveId* will be automatically filled with the value of the named column.
* If there is no such named columnname, set *slaveId* = `0`.
* Explicit definition: *slaveId* = `123` or *slaveId* = `{{SELECT id ...}}`
Note:
* `{{slaveId}}` can be used in any query of the current *FormElement*.
* If the `action`-*FormElement* name exist as a column in the master record: Update that column *automatically* with the
recent slaveId
* After an INSERT the `last_insert_id()` becomes the *slaveId*).
sqlBefore / sqlInsert / sqlUpdate / sqlDelete / sqlAfter
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
* Save values of a form to different record(s), optionally on different table(s).
* Typically useful on 'afterSave' - be careful when using it earlier, e.g. beforeLoad.
*FormElement.parameter*:
* *requiredList* - List of `native`-*FormElement*: only if all of those elements are filled, the current
`action`-*FormElement* will be processed.
* *sqlBefore*: always fired (before any *sqlInsert* or *sqlUpdate*)
* *sqlInsert*: fired if *slaveId* = `0` or *slaveId* = `''`.
* *sqlUpdate*: fired if *slaveId* > `0`:
* *sqlDelete*: always fired (after *sqlInsert* or *sqlUpdate*) - the definition, when this query is fired, might change in the future.
* *sqlAfter*: always fired (after *sqlInsert*, *sqlUpdate* or *sqlDelete*).
Example
'''''''
Situation 1: master.x_id=slave.id (1:1)
* Name the action element 'x_id': than {{slaveId}} will be automatically set to the value of 'master.x_id'
* {{slaveId}} == 0 ? 'sqlInsert' will be fired.
* {{slaveId}} != 0 ? 'sqlUpdate' will be fired.
* In case of fireing 'sqlInsert', the 'slave.id' of the new created record are copied to master.x_id (the database will
be updated automatically).
* If the automatic update of the master record is not suitable, the action element should have no name or a name
which does not exist as a column of the master record. Define `slaveId={{SELECT id ...}}`
Situation 2: master.id=slave.x_id (1:n)
* Name the action element different to any columnname of the master record (or no name).
* Determine the slaveId: `slaveId={{SELECT id FROM slave WHERE slave.xxx={{...}} LIMIT 1}}`
* {{slaveId}} == 0 ? 'sqlInsert' will be fired.
* {{slaveId}} != 0 ? 'sqlUpdate' will be fired.
Type: sendmail
^^^^^^^^^^^^^^
* Send mail(s) after saving the record.
* *FormElement.value*: Body of the email.
* *FormElement.parameter*:
* *sendMailTo* - Comma-separated list of receiver email addresses. Optional: 'realname <john@doe.com>. If there
is no recipient email address, *no* mail will be sent.
* *sendMailCc* - Comma-separated list of receiver email addresses. Optional: 'realname <john@doe.com>.
* *sendMailBcc* - Comma-separated list of receiver email addresses. Optional: 'realname <john@doe.com>.
* *sendMailFrom* - Sender of the email. Optional: 'realname <john@doe.com>'. **Mandatory**.
* *sendMailSubject* - Subject of the email.
* *sendMailReplyTo* - Reply this email address. Optional: 'realname <john@doe.com>'.
* *sendMailFlagAutoSubmit* - **on|off** - If 'on' (default), the mail contains the header
'Auto-Submitted: auto-send' - this suppress a) OoO replies, b) forwarding of emails.
* *sendMailGrId* - Will be copied to the mailLog record. Helps to setup specific logfile queries.
* *sendMailXId* - Will be copied to the mailLog record. Helps to setup specific logfile queries.
* To use values of the submitted form, use the STORE_FORM. E.g. `{{name:F:allbut}}`
* To use the `id` of a new created or already existing one, use the STORE_RECORD. E.g. `{{id:R}}`
.. _dynamic-update:
Dynamic Update
--------------
The 'Dynamic Update' feature makes a form more interactive. If a user change a *FormElement* who is tagged with
'dynamicUpdate', *all* elements who are tagged with 'DynamicUpdate', will be recalculated and rerendered.
The following fields will be recalculated during 'Dynamic Update'
* 'modeSql' - Possible values: 'show', 'required', 'readonly', 'hidden'
* 'value'
* 'parameter.*' - especially 'itemList'
To make a form dynamic:
* Mark all *FormElements* with {dynamic update}=enabled, which should send **or** receive a 'do update' signal.
* Define the receiving *FormElements* in a way, that they will interpret the recent user change. The form variable of the
specific sender *FormElement* `{{<sender element>:F:<sanitize>}}` should be part of one of the above fields to get an
impact. E.g.:
::
[receiving *FormElement*].parameter: itemList={{ SELECT IF({{carPriceRange:FE:alnumx}}='expensive','Ferrari,Tesla,Jaguar','General Motors,Honda,Seat,Fiat') }}
* Label and Description are not 'Dynamic Update' aware (Feature Request #2081).
.. _form-layout:
Form Layout
-----------
The forms will be rendered with Bootstrap CSS classes, based on the 12 column grid model (Bootstrap 3.x).
Generally a 3 column layout for *label* columns on the left side, an *input* field column in the middle and a *note*
column on the right side will be rendered.
The used default column (=bootstrap grid) width is *3,6,3* for *label, input, note*.
* The system wide default can be changed via config.qfq.ini :ref:`config-qfq-ini` - the new settings are the default
settings for all forms.
* Per *Form* settings can be done in the *Form* parameter field. They overwrite the system wide default.
* Per *FormElement* settings can be done in the *FormElement* parameter field. They overwrite the *Form* setting.
A column will be switched off (no wrapping via `<div class='col-md-?>`) by setting a `0` on the respective column.
Custom field width
^^^^^^^^^^^^^^^^^^
Per *FormElement* set `BS Label Columns`, `BS Input Columns` or `BS Note Columns` to customize an individual width.
The sum of these three columns should always be 12.
Multiple Elements per row
^^^^^^^^^^^^^^^^^^^^^^^^^
Every row is by default wrapped in a `<div class='form-group'>` and every column is wrapped in a `<div class='col-md-?>`.
To display multiple input elements in one row, the wrapping of the *FormElement* row and of the three columns can be
customized via the checkboxes of `Label / Input / Note`. Every open and every close tag can be individually switched on
or off.
E.g. to display 2 *FormElements* in a row with one label (first *FormElement*) and one note (last *FormElement*) we need
the following (switch off all non named):
* First *FormElement*
* open row tag: `row` ,
* open and close label tag: `label`, `/label`,
* open and close field tag: `input`, `/input`,
* Second *FormElement*
* open and close field tag: `input`, `/input`,
* open and close note tag: `note`, `/note`,
* close row tag: `/row` ,
Report
======
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 first_name and last_name. To create a simple list of all persons, we can do the following:
::
10.sql = SELECT id AS person_id, CONCAT(first_name, " ", last_name, " ") 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:
::
Marc MusterElton JohnSpeedy Gonzales
..
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 person_id, CONCAT(first_name, " ", last_name, " ") AS name FROM person
10.sep = <br />
HTML output:
::
Marc Muster<br />Elton John<br />Speedy Conzales<br />
..
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 :ref:`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 :ref:`wrapping-rows-and-columns`
Debug the bodytext
------------------
The parsed bodytext could be displayed by activating 'showDebugInfo' (:ref:`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: :ref:`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 _person_id, CONCAT(first_name, " ", last_name, " ") AS name FROM person
10.rsep = <br />
10.10.sql = SELECT CONCAT(postal_code, " ", city) FROM address WHERE p_id = {{10.person_id}}
10.10.rbeg = (
10.10.rend = )
..
This would result in
::
Marc Muster (3004 Bern)
Elton John (8008 Zürich)
Speedy Conzales (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.person_id}} will be replaced by the value in the person_id 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 |Is a level with one qualifier. E.g.: 10 |
|levels** | |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|**Sub |Is a level with more than one qualifier. E.g. levels **20.25** and **20.30.10** |
|levels** | |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|**Child** |The level **20** has one child **20.25** |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|**Parent** |The level 20.25 has a parent **20** |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|**Example |**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 |
|explanation**|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 p_id, p.first_name, " - ", p.last_name 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.p_id={{20.p_id}} 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:
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:
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 | |Mail |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:
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:
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', |**Expected "=" to follow "see"** | |
| |the link will be called | | |
+-------------+-------------------------------------------------------------------------------------------------+----------------------------------------------------------+---------------------------------------------------------------+
|<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 |none |
| |link text. | |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
**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 :ref:`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.first_name FROM exp_person AS p
..
Result:
::
BillieElvisLouisDiana
..
Real data, two columns
::
10.sql = SELECT p.first_name, p.last_name 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.first_name, " " , p.last_name, "'<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 _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.p_id='{{10.p_id}}'
10.10.rend = <br />
Same as above, but written in the nested notation ::
10 {
sql = SELECT p.id AS _p_id, p.name FROM exp_person AS p
rend = <br />
10 {
# inner query
sql = SELECT a.street FROM exp_address AS a WHERE a.p_id='{{10.p_id}}'
rend = <br />
}
}
* Columns starting with a '_' won't be printed but can be accessed as regular columns.
Best practice: Form
===================
Debug Report
------------
Writing "report's" in the nested notation or long queries broken over several lines, might not interpreted as wished.
Best for debugging is to specify in the tt-content record::
debugShowBodyText = 1
Note: Debug information is only display if it's enabled in *config.ini* by
* *SHOW_DEBUG_INFO=yes* or
* *SHOW_DEBUG_INFO=auto* and logged in in the same Browser as a Typo3 backend user.
More detailed error messages
----------------------------
If *SHOW_DEBUG_INFO* is enabled, a full stacktrace and variable contents are displayed in case of an error.
Person search form
------------------
QFQ content record::
# Creates a small form that redirects back to this page
10 {
sql = SELECT '_'
head = <form action='#' method='get'><input type='hidden' name='id' value='{{pageId:T}}'>Search: <input type='text' name='search' value='{{search:CE:all}}'><input type='submit' value='Submit'></form>
}
# SQL statement will find and list all the relevant forms
20 {
sql = SELECT CONCAT('?detail&form=form&r=', f.id) AS _Pagee, f.id, f.name, f.title
FROM Form AS f
WHERE f.name LIKE '%{{search:CE:all}}%'
head = <table class='table'>
tail = </table>
rbeg = <tr>
rend = </tr>
fbeg = <td>
fend = </td>
}
Form: compute next free 'ord' automatically
-------------------------------------------
Requirement: new records should automatically get the highest number plus 10 for their 'ord' value. Existing records
should not be altered.
Version 1
^^^^^^^^^
Compute the next 'ord' in advance in the subrecord field of the primary form. Submit that value to the new record
via SIP parameter to the secondary form.
On the secondary form: for 'new' records choose the computed value, for existing records leave the value
unchanged.
* Primary form, `subrecord` *FormElement*, field `parameter`: set ::
detail=id:formId,{{SELECT '&', IFNULL(fe.ord,0)+10 FROM Form AS f LEFT JOIN *FormElement* AS fe ON fe.formId=f.id WHERE
f.id={{r:S0}} ORDER BY fe.ord DESC LIMIT 1}}:ord
* Secondary form, `ord` *FormElement*, field `value`: set
::
`{{RS0}}`.
Version 2
^^^^^^^^^
Compute the next 'ord' as default value direct inside the secondary form. No change is needed for the primary form.
* Secondary form, `ord` *FormElement*, field `value`: set `{{SELECT IF({{ord:R0}}=0, MAX(IFNULL(fe.ord,0))+10,{{ord:R0}}) FROM (SELECT 1) AS a LEFT JOIN FormElement AS fe ON fe.formId={{formId:S0}} GROUP BY fe.formId}}`.
Form: Person Wizard - firstname, city
-------------------------------------
Requirement: A form that displays the column 'firstname' from table 'Person' and 'city' from table 'Address'. If the
records not exist, the form should create it.
Form primary table: Person
Form slave table: Address
Relation: `Person.id = Address.personId`
* Form: wizard
* Name: wizard
* Title: Person Wizard
* Table: Person
* Render: bootstrap
* *FormElement*: firstname
* Class: **native**
* Type: **text**
* Name: firstname
* Label: Firstname
* *FormElement*: email, text, 20
* Class: **native**
* Type: **text**
* Name: city
* Label: City
* Value: `{{SELECT city FROM Address WHERE personId={{r}} ORDER BY id LIMIT 1}}`
* *FormElement*: insert/update address record
* Class: **action**
* Type: **afterSave**
* Label: Manage Address
* Parameter:
* `slaveId={{SELECT id FROM Address WHERE personId={{r}} ORDER BY id LIMIT 1}}`
* `sqlInsert={{INSERT INTO Address (personId, city) VALUES ({{r}}, '{{city:F:allbut:s}}') }}`
* `sqlUpdate={{UPDATE Address SET city='{{city:F:allbut:s}}' WHERE id={{slaveId:V}} }}`
* `sqlDelete={{DELETE FROM Address WHERE id={{slaveId:V}} AND ''='{{city:F:allbut:s}}' LIMIT 1}}`
Form: Person Wizard - firstname, single note
--------------------------------------------
Requirement: A form that displays the column 'firstname' from table 'Person' and 'note' from table 'Note'.
If the records don't exist, the form should create it.
Column Person.noteId points to Note.id
Form primary table: Person
Form slave table: Address
Relation: `Person.id = Address.personId`
* Form: wizard
* Name: wizard
* Title: Person Wizard
* Table: Person
* Render: bootstrap
* *FormElement*: firstname
* Class: **native**
* Type: **text**
* Name: firstname
* Label: Firstname
* *FormElement*: email, text, 20
* Class: **native**
* Type: **text**
* Name: note
* Label: Note
* Value: `{{SELECT Note FROM Note AS n, Person AS p WHERE p.id={{r}} AND p.noteId=n.id ORDER BY id }}`
* *FormElement*: insert/update address record
* Class: **action**
* Type: **afterSave**
* Name: noteId
* Label: Manage Note
* Parameter:
* `sqlInsert={{INSERT INTO Note (note) VALUES ('{{note:F:allbut:s}}') }}`
* `sqlUpdate={{UPDATE Note SET note='{{note:F:allbut:s}}' WHERE id={{slaveId:V}} }}`
Best Practise
=============
Central configured values
-------------------------
Any variable in *config.qfq.ini* can be used by *{{<varname>:Y}}* in form or report statements.
E.g.
TECHNICAL_CONTACT = jane.doe@example.net
Could be used in an *FormElement.type* = sendmail with *parameter* setting *sendMailFrom={{TECHNICAL_CONTACT:Y}}*.
Icons Template Group
--------------------
* FormElement.parameter
::
tgAddClass=btn alert-success
tgAddText=<span class="glyphicon glyphicon-plus" aria-hidden="true"></span>
tgRemoveClass=btn btn-danger alert-danger
tgRemoveText=<span class="glyphicon glyphicon-remove" aria-hidden="true"></span>
Chart
-----
* QFQ delivers a chart JavaScript lib: https://github.com/nnnick/Chart.js.git. Docs: http://www.chartjs.org/docs/
* The library is not sourced in the HTML page automatically. To do it, either include the lib
`typo3conf/ext/qfq/Resources/Public/JavaScript/Chart.min.js`:
* in the specific tt_content record (shown below in the example) or
* system wide via Typo3 Template record.
* By splitting HTML and JavaScript code over several lines, take care not accidently to create a 'nesting'-end token.
Check the line after `10.tail =`. It's '}' alone on one line. This is a valid 'nesting'-end token!. There are two options
to circumvent this:
* Don't nest the HTML & JavaScript code - bad workaround, this is not human readable.
* Select different nesting token, e.g. '<' / '>' (check the first line on the following example).
::
# <
10.sql = SELECT '_'
10.head =
<div style="height: 1024px; width: 640px;">
<h3>Distribution of FormElement types over all forms</h3>
<canvas id="barchart" width="1240" height="640"></canvas>
</div>
<script src="typo3conf/ext/qfq/Resources/Public/JavaScript/Chart.min.js"></script>
<script>
$(function () {
var ctx = document.getElementById("barchart");
var barChart = new Chart(ctx, {
type: 'bar',
data: {
10.tail =
}
});
});
</script>
# Labels
10.10 <
sql = SELECT "'", fe.type, "'" FROM FormElement AS fe GROUP BY fe.type ORDER BY fe.type
head = labels: [
tail = ],
rsep = ,
>
# Data
10.20 <
sql = SELECT COUNT(fe.id) FROM FormElement AS fe GROUP BY fe.type ORDER BY fe.type
head = datasets: [ { data: [
tail = ], backgroundColor: "steelblue", label: "FormElements" } ]
rsep = ,
>
Upload Form Simple
------------------
Table Person
+---------------------+--------------+
| Name | Type |
+=====================+==============+
| id | int |
+---------------------+--------------+
| name | varchar(255) |
+---------------------+--------------+
| pathFileNamePicture | varchar(255) |
+---------------------+--------------+
| pathFileNameAvatar | varchar(255) |
+---------------------+--------------+
* Form:
* Name: UploadSimple
* Table: Person
* FormElements:
* Name: name
* Type: text
* Label: Name
* Name: pathFileNamePicture
* Type: upload
* Label: Picture
* Parameter::
fileDestination=fileadmin/user/{{id:R0}}-picture-{{filename}}
* Name: pathFileNameAvatar
* Type: upload
* Label: Avatar
* Parameter::
fileDestination=fileadmin/user/{{id:R0}}-avatar-{{filename}}
Upload Form Advanced 1
----------------------
Table: Person
+---------------------+--------------+
| Name | Type |
+=====================+==============+
| id | int |
+---------------------+--------------+
| name | varchar(255) |
+---------------------+--------------+
Table: Note
+---------------------+--------------+
| Name | Type |
+=====================+==============+
| id | int |
+---------------------+--------------+
| pId | int |
+---------------------+--------------+
| type | varchar(255) |
+---------------------+--------------+
| pathFileName | varchar(255) |
+---------------------+--------------+
* Form:
* Name: UploadAdvanced1
* Table: Person
* FormElements
* Name: name
* Type: text
* Label: Name
* Name: mypathFileNamePicture
* Type: upload
* Label: Picture
* Value: {{SELECT pathFileName FROM Note WHERE id={{slaveId}} }}
* Parameter::
fileDestination=fileadmin/user/{{id:R0}}-picture-{{filename}}
slaveId={{SELECT id FROM Note WHERE pId={{id:R0}} AND type='picture' LIMIT 1}}
sqlInsert={{INSERT INTO Note (pathFileName, type, pId) VALUE ('{{fileDestination}}', 'picture', {{id:R0}}) }}
sqlUpdate={{UPDATE Note SET pathFileName = '{{fileDestination}}' WHERE id={{slaveId}} LIMIT 1}}
sqlDelete={{DELETE FROM Note WHERE id={{slaveId}} LIMIT 1}}
* Name: mypathFileNameAvatar
* Type: upload
* Label: Avatar
* Value: {{SELECT pathFileName FROM Note WHERE id={{slaveId}} }}
* Parameter::
fileDestination=fileadmin/user/{{id:R0}}-avatar-{{filename}}
slaveId={{SELECT id FROM Note WHERE pId={{id:R0}} AND type='avatar' LIMIT 1}}
sqlInsert={{INSERT INTO Note (pathFileName, type, pId) VALUE ('{{fileDestination}}', 'avatar', {{id:R0}}) }}
sqlUpdate={{UPDATE Note SET pathFileName = '{{fileDestination}}' WHERE id={{slaveId}} LIMIT 1}}
sqlDelete={{DELETE FROM Note WHERE id={{slaveId}} LIMIT 1}}
Upload Form Advanced 2
----------------------
Table: Person
+---------------------+--------------+
| Name | Type |
+=====================+==============+
| id | int |
+---------------------+--------------+
| name | varchar(255) |
+---------------------+--------------+
| noteIdPicture | int |
+---------------------+--------------+
| noteIdAvatar | int |
+---------------------+--------------+
Table: Note
+---------------------+--------------+
| Name | Type |
+=====================+==============+
| id | int |
+---------------------+--------------+
| pathFileName | varchar(255) |
+---------------------+--------------+
* Form:
* Name: UploadAdvanced2
* Table: Person
* FormElements
* Name: name
* Type: text
* Label: Name
* Name: mypathFileNamePicture
* Type: upload
* Label: Picture
* Value: {{SELECT pathFileName FROM Note WHERE id={{slaveId}} }}
* Parameter::
fileDestination=fileadmin/user/{{id:R0}}-picture-{{filename}}
slaveId={{SELECT id FROM Note WHERE id={{noteIdPicture}} LIMIT 1}}
sqlInsert={{INSERT INTO Note (pathFileName) VALUE ('{{fileDestination}}') }}
sqlUpdate={{UPDATE Note SET pathFileName = '{{fileDestination}}' WHERE id={{slaveId}} LIMIT 1}}
sqlDelete={{DELETE FROM Note WHERE id={{slaveId}} LIMIT 1}}
sqlAfter={{UPDATE Person SET noteIdPicture={{slaveId}} WHERE id={{id:R0}} LIMIT 1
* Name: mypathFileNameAvatar
* Type: upload
* Label: Avatar
* Value: {{SELECT pathFileName FROM Note WHERE id={{slaveId}} }}
* Parameter::
fileDestination=fileadmin/user/{{id:R0}}-avatar-{{filename}}
slaveId={{SELECT id FROM Note WHERE id={{noteIdAvatar}} LIMIT 1}}
sqlInsert={{INSERT INTO Note (pathFileName) VALUE ('{{fileDestination}}') }}
sqlUpdate={{UPDATE Note SET pathFileName = '{{fileDestination}}' WHERE id={{slaveId}} LIMIT 1}}
sqlDelete={{DELETE FROM Note WHERE id={{slaveId}} LIMIT 1}}
sqlAfter={{UPDATE Person SET noteIdAvatar={{slaveId}} WHERE id={{id:R0}} LIMIT 1
FAQ
===
* Q: A variable {{<var>}} is shown as empty string, but there should be a value.
* A: The sanatize rule is violeted and therefore the value has been removed. Set {{<var>:<store>:all}} as a test.
Only STORE_CLIENT and STORE_FORM will be sanatized.