The QFQ extension is activated through tt-content records. One or more tt-content records per page are necessary to render forms, reports (exports) or to perform delete and save commands.
Name Explanation form Formname defined in ttcontent record bodytext * test fix. E.g.: form = person * via SIP. E.g. form = {{form}} debugShowStack Any exception will show the call stack. E.g. debugShowStack = 1
Most fields of a form specification might contain: * ‘’constants’’ (=strings), this is the standard use case. * ‘’variables’’ retrieved from the stores (see below), * ‘’SQL statements’’ (limited set of), * or any combination of the above.
A variable (or SQL) statement is surrounded by curely braces:
{{VarName[:<store / prio>[:<sanitize class>]]}}
Example:
{{recordid}}
{{SELECT name FROM person WHERE id=1234}}
{{SELECT name FROM person WHERE id={{recordid}} }}
{{SELECT name FROM person WHERE id={{key1:C:ALNUMX}} }}
Leading and tailing spaces inside curly braces are removed.
There are several stores, from where to retrieve the value. If a value is not found in one store, take the next store, until a value has been found.
If there is an empty string found, this ‘’‘is’‘’ a value: value found >> stop search.
If no value is found, the value is an <empty string>.
Only variables, which are known in a specified store, can be substituted.
Name Description Content F Form: data still not saved in database. All native form elements. Recent values from the Browser. S SIP: Client parameter ‘s’ will indicate the current SIP, which will be loaded from the SESSION repo to the SIP-Store. sip, r (record_id), form R Record - the one who will be edited. For new records: empty. All columns of the current record from the current table P Parent record. E.g.: on multi forms the current record of the outer query All columns of the MultiSQL Statement from the for the current row D The table.column specified default value. M The table.column specified type C Client: POST variable, if not found: GET variable Parameter send from the Client (=Browser). T Typo3: a) Bodytext (ttcontent record), b) Typo3 internal varibles like fe_user_uid, ... See Typo3 tt_content record configuration 0 Value: 0, might helpfull if variable is empty but used in an SQL statement, which might produce a SQL error otherwise if substituted with an empty string All possible keys Y System: a) Database credentials, b) helper vars for logging/debugging: SYSTEM_SQL_RAW ... SYSTEM_FORM_ELEMENT_COLUMN
Name Explanation s =SIP r record id. Typically stored in SIP, rarely specified on the URL keySemId always current Semester Id keySemIdUser {{keySemIdUser}}, may be changed by user pageId current T3 page Id pageType T3 GET Parameter ‘type’ pageLanguage T3 GET Parameter ‘L’ HTTP_HOST current HTTP HOST REMOTE_ADDR Client IP address ‘$_SERVER[*]’ All other variables accessable by $_SERVER[]. Only the often used have a pre defined sanitize class. form Unique name of current form random random string with length of 32 chars, alphanum ANREDE {{sex}} == male >> Sehr geehrter Herr, {{sex}} == female Sehr geehrte Frau EANREDE {{sex}} == male >> Dear Mr., {{sex}} == female >> Dear Mrs.
Name Explanation form Formname defined in ttcontent record bodytext * fix. E.g.: form = person * via SIP. E.g. `form = {{form}} debugShowStack Any exception will show the call stack. E.g. debugShowStack = 1 debugLoad Debug Level for ‘load’, defined in ttcontent record bodytext debugSave Debug Level for ‘save’, defined in ttcontent record bodytext fe_user Logged in Typo3 FE User fe_user_uid Logged in Typo3 FE User uid fe_user_group FE groups of logged in Typo3 FE User
Name Explanation FormElement name Name of native formelement. To get, exactly and only, the specified form element(for ‘p_id’): {{p_id:F}}
Name Explanation record column name Name of a column of the primary table (as defined in the current form). To get, exactly and only, the specified form element: {{p_id:R}}
Name Explanation SELECT ... reserved and indicates a SQL Statememt INSERT ... reserved and indicates a SQL Statememt UPDATE ... reserved and indicates a SQL Statememt DELETE ... reserved and indicates a SQL Statememt SHOW ... reserved and indicates a SQL Statememt
SQL Statement:
{{[!]SELECT ...|UPDATE ...|INSERT ...|SHOW ...|LAST_INSERT_ID ...}}
Example:
{{SELECT ... id, name, ... [<PARAM1>] ... FROM person ... [<PARAM2>] [...]}}
A SQL Statement might contain parameter, including additional SQL statements. Inner SQL queries will be fired first.
All variables will be substituted one by one from inner to outer.
Maximum recursion depth: 5 (a recursion depth of 2 is sometimes used for mailing with templates, 3 and more probably confuses too much and are therefore not practicable, but supported until depth of 5)
The number of variables inside an input field or a SQL statement is not limited.
A resultset of a SQL statement will be imploded over all (concat all columns of a row, concat all rows - there is no glue string).
Array: {{!SELECT ...}}
Name | Explanation | Description |
---|---|---|
id | int, autoincrement | created by by MySQL |
name | string | unique and speaking name of the form. Form will be identified by this name |
title | string / query | Title, shown on/above the form. |
noteInternal | textarea | Internal notes: special functionality, used variables, ... |
tableName | string | Primay table of the form |
permitNew | enum(‘sip’, ‘logged_in’, ‘logged_out’, ‘always’, ‘never’) | Default: sip |
permitEdit | enum(‘sip’, ‘logged_in’, ‘logged_out’, ‘always’, ‘never’) | Default: sip |
permitUrlParameter | textarea | Braucht es das wircklich? per line one GET-‘parameter name’ with a class DIGIT, ALNUMX, ALL. F.e.: email:ALPHANUM n postalcode:DIGIT |
render | enum(‘plain’,’table’, ‘bootstrap’) | Default bootstrap |
multiMode | enum(‘none’,’horizontal’,’vertical’) | Default ‘none’ |
multiSql | text | Optional. SQL Query which selects all records to edit. |
multiDetailForm | string | Optional. Form to open, if a record is selected to edit (double click on record line) |
multiDetailFormParameter | string | Optional. Translated Parameter submitted to detailform (like subrecord parameter) |
forwardMode | string: ‘auto|no|page’. | |
forwardPage | string / query | If $forward==”page”: page to jump to |
bsLabelColumns | string | title: default number of ‘bootstrap 12grid’ columns |
bsInputColumns | string | input: default number of ‘bootstrap 12grid’ columns |
bsNoteColumns | string | note: default number of ‘bootstrap 12grid’ columns |
parameter | text | Misc additional parameters. See ‘Form.parameter’ |
deleted | string | ‘yes’|’no’. |
modified | timestamp | updated autmatically throught stored procedure |
created | datetime | set once through QFQ |
Name | Type | Description |
---|---|---|
maxVisiblePill | int | Show pills upto <maxVisiblePill> as button, all further in a dropdown menu. Eg.: maxVisiblePill=3 |
class | string | HTML div with given class, surrounding the whole form. Eg.: class=container-fluid |
Name | Type | Description |
---|---|---|
id | int | |
formId | int | |
feIdContainer | int | |
enabled | enum(‘yes’|’no’) | |
name | string | |
label | string | Label of formelement. Depending on layout model, left or on top of the formelement |
mode | enum(‘show’, ‘readonly’, ‘required’, ‘lock’, ‘disable’ ) | Default: normal - Normal: regular user input field. Readonly : user can’t change any data. Important : user manipulated data wont be saved. Required User has to specify a value. Typically, an <empty string> represents ‘no value’ . lock form element is read only and grayed out, disable: form element is not visible |
class | enum(‘native’, ‘action’, | Details below. |
‘container’) | ||
type | enum(‘checkbox’, ‘dateJQW’, ‘datetimeJQW’, ‘gridJQW’, ‘hidden’, ‘text’, ‘note’, ‘password’, ‘radio’, ‘select’, ‘subrecord’, | |
‘textarea’, ‘timeJQW’, ‘upload’, ‘fieldset’, ‘pill’, ‘before_load’, ‘before_save’, ‘before_insert’, ‘before_update’, | ||
‘before_delete’, ‘after_load’, ‘after_save’, ‘after_insert’, ‘after_update’, ‘after_delete’, ‘feGroup’, ‘sendmail’ )’. | ||
checkType | enum(‘min|max’, ‘pattern’, ‘number’, ‘email’) | |
checkPattern | ‘regexp’ | if $check_type==’pattern’: pattern to match |
onChange | string |
|
ord | string | display order of form elements (‘order’ is a reserved keyword) |
tabindex | string | HTML tabindex attribute |
size | string | Visible length of input element. Might be ommited, depending on the choosen form layout |
maxLength | string | Maximum characters for input. |
note | string | Note of formelement. Depending on layout model, right or below of the formelement |
tooltip | text | Display this text as tooltip on mouse over |
placeholder | string | text, displayed inside the input element in light grey |
clientJs | text | Javascript called on ‘on change’ formelements |
value | text | Default value |
sql1 | text | SQL query (‘sql’ is a reserved keyword) |
sql2 | text | second SQL query |
parameter | text | might contain misc parameter. Depends on the type of formelement. |
feGroup | string | Comma separated list of Typo3 FE Group ID. NOT SURE IF THIS WILL BE IMPLEMENTED. Natvie formElements, fieldsets and pills can be assigned to feGroups. Group status: show, hidden, disabled. Group Access: FE-Groups. User will be assigned to FE-Groups and the form defintion reference such FE-groups. Easy way of granting permission. |
deleted | string | ‘yes’|’no’. |
modified | timestamp | updated autmatically throught stored procedure |
created | datetime | set once through QFQ |
Attribute | checkbox | dateJQW | datetimeJQW | gridJQW | hidden | input | note | password | radio | select | subrecord | textarea | timeJQW | upload |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
id | <-14> Internal id | |||||||||||||
formId | <-14> Form | |||||||||||||
containerId | <-14> Assign the Formelement to user defined fieldSet or pill | |||||||||||||
enabled | <-14> Formelement is active or not | |||||||||||||
name | <-14> Name of a column of the primary table. Formelements with a corresponding table will be saved automatically. | |||||||||||||
label | <-14> Label shown to the user. | |||||||||||||
mode | <-14> show, readonly, required, lock, disable. | |||||||||||||
class | <-14> native | |||||||||||||
type | checkbox | dateJQW | datetimeJQW | gridJQW | hidden | input | note | password | radio | select | subrecord | textarea | timeJQW | upload |
checkType |
|
|
|
|
|
|
||||||||
checkPattern |
|
|
|
|
|
|
||||||||
onChange |
|
|
|
|
|
|
|
|
|
|
||||
ord |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
tabindex |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
size |
|
|
|
|
|
|
|
|
||||||
maxLength |
|
|
||||||||||||
note |
|
|
|
|
|
|
|
|
|
|
|
|
||
tooltip |
|
|
|
|
|
|
|
|
|
|
||||
placeholder |
|
|
|
|
|
|
||||||||
clientJs |
|
|
|
|
|
|
|
|
|
|
||||
value |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
sql1 | <-14> ? | |||||||||||||
sql2 | <-14> ? | |||||||||||||
<-15 rowbgcolor=”#eeeeee”> Additional attributes in Field ‘parameter’. Typically in key=value format. | ||||||||||||||
type | checkbox | dateJQW | datetimeJQW | gridJQW | hidden | input | note | password | radio | select | subrecord | textarea | timeJQW | upload |
accept | <-15> ? | |||||||||||||
alt | <-15> ? | |||||||||||||
autocomplete |
|
|
|
|
|
|||||||||
autofocus |
|
|
|
|
|
|
|
|
|
|
||||
checkBoxMode |
|
|
||||||||||||
checked |
|
|
|
|||||||||||
unchecked |
|
|
|
|||||||||||
label2 |
|
|
||||||||||||
itemList |
|
|
|
|||||||||||
emptyItemAtStart |
|
|||||||||||||
emptyItemAtEnd |
|
|||||||||||||
accept |
|
Radio Buttons will be build from one of three sources:
FormElement.’‘’parameter’‘’:
Select lists will be build from one of three sources:
FormElement.’‘’size’‘’:
FormElement.’‘’parameter’‘’:
‘subrecord’ will be rendered as html table.
FormElement.’‘’sql1’‘’: SQL query to select records. E.g.:
{{!SELECT a.id AS id, CONCAT(a.strasse, a.hausnummer) AS a, a.city AS b, a.zip AS c FROM adresse AS a}}
Columnname: <title>[|<number>][|width=<number>][|nostrip][|icon][|url][|mailto]
Exactly one column ‘id’ has to exist and specifies the primary record for the target form.
FormElement.’‘’parameter’‘’
Typical not used. Usefull if user wisches an explicit ‘Submit’ Button.
To display a report on a given TYPO3 page, create a content element of type formreport (plugin) on that page. You can then define what data the report should display in which format via the text field and the syntax described in section ? Syntax.
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 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. formreport 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 formreport to seperate the rows of the result by a HTML-line break. The final result then is
Marc Muster<br />Elton John<br />Speedy Conzales<br />
which gives us the desired simple list (we use linebreaks for simplicity here) when displayed by a browser:
Marc Muster
Elton John
Speedy Conzales
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 replace before the SQL-Query gets executed:
Column values of the recent rows: ~<level>.<columnname>
Global variables: ~global.<name>
Sanitized URL (Post/Get) variables: ~url.<name>
Row index and total rows: ~<level>.line.count|total
‘last_insert_id’ and ‘affected_rows’: ~<level>.line.total
See ?Variables for a full list of all available variables.
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 accesed 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.
A report can be divided into several levels. This can make report definitions more readable because it allows to split 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.sep = <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)
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.
Certain column names have a special meaning. Their content will be processes in a special way. For example columns with a leading _ in their name will not get displayed, their content will only be available as variables (see _person_id in the example above).
Levels | A report is divided into levels. Example 1 has 3 levels 10, 20.20, 20.30.10 |
Qualifier | A level is divided into qualifiers 20.30.10 has 3 qualifiers 20, 30, 10 |
Root levels | Is a level with one qualifier. Example 1 has 2 root levels 10 and 20. |
Sub levels | Is a level with more than one qualifier. Example 1 has 2 sub levels 20.20 and 20.30.10 |
Child | The level 20 has one child 20.20 |
Parent | The level 20.20 has a parent 20 |
Example explanation | 10 and 20 is a root level and will be executed independently. 10 don’t have a sub level. 20.20 will be executed as many times as 20 has row numbers. 20.30.10 won’t be executed because there isn’t any 20.30 level |
Report Example 1:
# Displays current date
10.sql = SELECT CURDATE()
# Show all students from the person table (_p_id won't be shown because of the leading underscore)
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.20.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'
Order and nesting of queries, will be defined with a typoscript similar syntax: level.sublevel.subsublevel. ...
Each ‘level’ can have multiple a ‘keys’, which come after the the level. F.e.: 20.30.10.**sql**.
Each ‘level’, which should be processed, needs at least a key sql or form (see form manual).
For each level, different additional pre/post definitions can be defined using the keys in the table below.
Level key | Syntax | Description |
form | 10.form=formname | Renders a form. this can’t have any sub form. A level must have a form or sql argument |
db | 10.db=dbalias | Predefined: ‘db’, ‘t3’ - Additional db’s may be configured in ext_localconf.php. Choosen DB will be activated for this level and all it’s sublevels |
debug | 10.debug=2 | Debuglevel. Default:0. Level will be activated for this level and all it’s sublevels. 0:no debug, 1:sql queries, 2:basic, 3:verbose, 4:extreme |
fbeg | 10.fbeg=<td> | Prints <td> at the field begin |
fend | 10.fend=</td> | Prints </td> at the field end |
head | 10.head=<table> | Prints <table> at the start of the level output |
rbeg | 10.rbeg=<tr> | Prints <tr> at the row begin |
rbgd | 10.rbgd=#dddddd|#eeeeee | Alternate the row color in rbeg between #dddddd and #eeeeee . rbeg has to be like 10.rbeg=<trbgcolor=rbgd>:the string rbgd will be replaced. |
rend | 10.rend=</tr> | Prints </tr> at the row end before subqueries are processed |
renr | 10.renr=</tr> | Prints </tr> at the row end after subqueries are processed |
rsep | 10.rsep=,%nbsp; | Prints , after each row. It’s like rend with the difference, that it won’t be printed the last time. |
fsep | 10.fsep=- | Prints - after each field. It’s like fend with the difference, that it wont’t be printed the last time. |
sql | 10.sql=SELECTname,firstnameFROMpersonWHEREnameLIKE’%son’ | This is a backbone of report. Per result row the sub level will be. triggert (if there are any). Prints the result with the values from the other level keys. A level must have a form or sql argument. |
head | 10.head=<table> | Prints <table> at the top of the level output. Only printed if there is at least one record. |
tail | 10.tail=</table> | Prints </table> at the end of the level output. Only printed if there is at least one record. Otherwise the althead is printed. |
althead | 10.althead=Empty | Prints Empty at the top of the level output if query 10.sql selects no row |
In general, the content of all columns of all rows will be printed sequentially, without separator.
Rows with ?reserved column names will processed in special ways.
Column names, which start with a ‘_’, will not be printed. This is useful if you only want to access the content of some columns during later on via the ~<level>.<column> variable but don’t want to print the content of those columns.
The content of columns with reserved names will be processed by special functions.
Depending on the column name, different parameters are required as input to the function.
The input parameters for the processing function are stored as 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.
The following table gives an overview of all reserved column names. Click on the link in the first column to jump to the detailed description of the column (including available parameters and examples).
Reserved column name | Purpose |
_<someName> | 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. |
?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 excutables on the webserver. |
?vertical | Render Text vertically. This is usefull for tables with limited column width. |
?img | Display images. |
?F | Wrap/modify content. Undocumented. |
{{url | display | i (intern), e(extern) | - (same),n (new), p (parent), t(top) | -, (e(edit), c(copy), n(new), d(delete), i(insert) , f(file)) }}
Most of URLs will be rendered via link.
Columnnames like pagee, mailto,... are wrapper to link.
The parameters for link contains a prefix to make them position independet.
For less conflicts:
A:<u|m|p>:<url|mail|page> (A=Anchor)
G:<ENTIMC> (G=Grafic)
G:<PbC>:<color|Text> (G=Grafic)
URL | IMG | Meaning | Qualifier | Example | Description |
Anchor | A:... | See above | Superclass for regular URL defnition | ||
Grafic | G:... | See above | Superclass for grafic definiton | ||
x | URL | u:<url> | u:http://www.example.com | If an image is specified, it will be rendered inside the link, default link class: external | |
x | m:<email> | m:info@example.com | Default link class: email | ||
x | Page | p:<pageId> | p:impressum | ‘?’ is prepended, no hostname qualifier (will be set automatically by the browser), default link class: internal | |
Text | t:<text> | t:Firstname Lastname |
|
||
Render | r:<mode> | r:[0-5] | Rendering mode: see below. Default: 0 | ||
x | Picture | P:<filename> | P:bullet-red.gif | Picture ‘<imgsrc=”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 iamge | |
x | Help | H | H | Show ‘help’ icon as iamge | |
x | Info | I | I | Show ‘information’ icon as iamge | |
x | Show | S | S | Show ‘show’ icon as iamge | |
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, Default: no target | ||
Question | q:<text> | q:please confirm | Link will be executed only if user clicks ok | ||
Encryption | e:0|1|... | e:1 | Encryption of the 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’ | ||
Hash | h | h | An hash entry is generated with all Parameter. No other URL parameter than ‘S_hash’ (=hash) |
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 | url | no link, show text, if text is empty, show url |
5 | nothing at all |
SQL-Query | Result |
select”m:info@example.com”aslink | info@example.com as linked text, encrypted with javascript, class=external |
select”m:info@example.com|c:0”aslink | info@example.com as linked text, not encrypted, class=external |
select”m:info@example.com|P:mail.gif”aslink | info@example.com as linked image mail.gif, encrypted with javascript, class=external |
select”m:info@example.com|P:mail.gif|o:sendmail”aslink | info@example.com as linked image mail.gif, encrypted with javascript, class=external, tooltip: “sendmail” |
select”m:info@example.com|t:mailtoinfo@example.com|o:sendmail”aslink | ‘mail to info@example.com‘ as linked text, encrypted with javascript, class=external |
select”u:www.example.com”aslink | www.example as link, class=external |
select”u:http://www.example.com”aslink | http://www.example as link, class=external |
select”u:www.example.com|q:Pleaseconfirm”aslink | www.example as link, class=external, ?JavaScript Window which has to be confirmed with click on ‘ok’ |
select”u:www.example.com|c:i”aslink | http://www.example as link, class=internal |
select”u:www.example.com|c:nicelink”aslink | http://www.example as link, class=nicelink |
select”p:form_person|c:e”aslink | <a class=”external” href=”?form_person”>Text</a> |
select”p:form_person&S_person=Text|t:Person”aslink | <a class=”internal” href=”?form_person&S_person=Text”>Person</a> |
select”p:form_person|e”aslink | <a class=”internal” href=”?form_person”><img alttext=”Edit” src=”fileadmin/typo3conf/ext/formreport/icons/edit.gif”></a> |
select”p:form_person|e|g:_blank”aslink | <a target=”_blank” class=”internal” href=”?form_person”><img alttext=”Edit” src=”fileadmin/typo3conf/ext/formreport/icons/edit.gif”></a> |
select”p:form_person|C”aslink | <a class=”internal” href=”?form_person”><img alttext=”Check” src=”fileadmin/typo3conf/ext/formreport/icons/checked-green.gif”></a> |
select”p:form_person|C:green”aslink | <a class=”internal” href=”?form_person”><img alttext=”Check” src=”fileadmin/typo3conf/ext/formreport/icons/checked-green.gif”></a> |
select”A:p:form_person|G:C”aslink | <a class=”internal” href=”?form_person”><img alttext=”Check” src=”fileadmin/typo3conf/ext/formreport/icons/checked-green.gif”></a> |
select”A:u:www.example.com|G:P:home.gif|t:Home”aslink | <a class=”internal” href=”?form_person”><img alttext=”Check” src=”ffileadmin/typo3conf/ext/formreport/icons/home.gif”>Home</a> |
These columns provide a shortcut version of the link interface to use for fast creation of internal links. The colum name is composed of the string page and a optional character to specify the type of the link.
[<page id|alias>[¶m=value&...]] | [record id] | [text] | [tooltip] | [msgbox] | [class] | [target] | [render mode] | [create hash] “” as pagee Fastest way to create links, inside of the current T3 installation. Main purpose is the automatic hash to be used by the forms
Syntax
select "[options]" as page[<link type>]
where
[options] = [<page>]|[<record id>]|[<text>]|[<tooltip>]|[<msgbox>]|[<class>]|[<target>]|[<render mode>]|[<create hash>]
<link type> = c,d,e,h,i,n,s
The following table summarizes all available page colomns. For most link types, all parameters are optional. If some parameters are required by a certain link type, this is indicated in the Mandatory parameters colomn of the table below.
column name | Purpose | create hash | default value of msgbox parameter | Mandatory parameters |
page | Internal link without a grafic | no | empty | p:<pageId> |
pagec | Internat link without a grafic, with messagebox | no | Please confirm! | p:<pageId> |
paged | Internal link with delete icon (trash) | yes | Delete record ? | p:<pageId>,i:<id>,T:<table name>|f:<form name> |
pagee | Internal link with edit icon (pencil) | yes | empty | p:<pageId>,i:<id> |
pageh | Internal link with help icon (questionmark) | yes | empty | p:<pageId> |
pagei | Internal link with information icon (i) | no | empty | p:<pageId> |
pagen | Internal link with new icon (sheet) | yes | empty | p:<pageId> |
pages | Internal link with how icon (magnifier) | yes | empty | p:<pageId> |
Parameter | Description | Default value | Example |
<page> | TYPO3 page id or page alias. | The current page: ~pageId | 45 application application&N_param1=1045 |
<recordid> | Effective Record ID stored in hash array. | <empty> | 7011 |
<text> | Text, wraped by the link. If there is an icon, text will be displayed right of it. | empty string | |
<tooltip> | Text to appear as a ?ToolTip | empty string | |
<msgbox> | If there is a msgbox text given, a msgbox will be opened. Only if the user clicks on ok, the link will be called | Expected “=” to follow “see” | |
<class> | CSS Class for the a tag | The default class defined for internal links in ext_localconf.php (see ...) | |
<target> | arameter for HTML ‘target=’. F.e.: Opens a new window | empty | P |
<rendermode> | Easy way not to show/render a link at all. Render modes (0-5) are defined in table Render mode in section ?link | 0 | 5 |
<createhash> | h | see below | ‘h’: create a hash, ‘H’: create no hash. Specify only if default is not suitable |
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
Easily create Email links.
Syntax
select "<email address>|[<link text>]" as mailto
Parameter | Description | Default value |
<emailaddress> | The email address where the link should point to. | none |
<linktext> | The text that should be displayed on the website and be linked to the email address. This will typically be the name of the recipient. If this parameter is omitted, the email address will be displayed as link text. | none |
Minimal Example
10.sql = select "john.doe@example.com" as mailto
Advanced Example
10.sql = select "john.doe@example.com|John Doe" as mailto
Send simple plain text emails. Every mail will be logged in the mail log. The logfile can be configured in ext_localconf.php via $TYPO3_CONF_VARS[$_EXTKEY][‘log’][‘mail’].
Syntax
select "receiver@domain.com[:john doe],receiver2@domain.com[:jane doe]|sender@domain.com[:willi wutzmann]|subject|body" as sendmail
Parameter | Description | Required |
receiver@domain.com[:johndoe],receiver2@domain.com[:janedoe] | Comma-separated list of Email-receiver(s). An optional name can be added using a colon (:) | |
sender@domain.com[:williwutzmann] | Sender of the email. An optional name can be added using a colon (:) | |
subject | Subject of the email | |
body | Message |
Minimal Example
10.sql = select "john.doe@example.com|company@example.com|Latest News|The new version of FormReport 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, customer2@example.com|company@example.com|Latest News|The new version of FormReport is now available." as sendmail
This will send an email with subject Latest news from company@example.com to customer1@example.com and to customer2@example.com.
Send plain text/html emails. This is identical to ?t#Column:_sendmail, but allows to additionaly set the cc:, bcc: and reply-to: -headers. Every mail will be logged in the mail log. The logfile can be configured in ext_localconf.php via $TYPO3_CONF_VARS[$_EXTKEY][‘log’][‘mail’].
Syntax
select "receiver@domain.com[:john doe],receiver2@domain.com[:jane doe]|sender@domain.com[:willi wutzmann]|subject|cc1@domain.com[:willi wutzmann]|bcc1@domain.com[:george wutzmann]|replyto@domain.com[:Support-Desk]|format|body" as sendmail
Parameter | Description | required |
receiver@domain.com[:johndoe],receiver2@domain.com[:janedoe] | Comma-separated list of Email-receiver(s). An optional name can be added using a colon (:) | |
sender@domain.com[:williwutzmann] | Sender of the email. An optional name can be added using a colon (:) | |
subject | Subject of the email | |
cc1@domain.com[:williwutzmann] | Comma-separated list of CC-receiver(s). An optional name can be added using a colon (:) | |
bcc1@domain.com[:georgewutzmann] | Comma-separated list of BCC-receiver(s). An optional name can be added using a colon (:) | |
replyto@domain.com[:Support-Desk] | Reply-to address. An optional name can be added using a colon (:) | |
format | Flag indicating if this is a plaintext or html message. Possible values are ‘plain’ and ‘html’ | |
body | Message (plain text or html) |
Render images. Allows to define a 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).
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 the image can’t be loaded (altattribute of img tag). | empty string |
<titletext> | Text that will be output 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
Runs batch files or excutables 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
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
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.
The reserved column ‘F’(=Format) can be used to
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 | Striptags / 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: |
Including a form in report follows the same rules for nesting as any other report sql query:
Include one form at level 10:
10.form = (...)
Include one form for every row in the resultset of 10.sql:
10.sql = select (...)
10.10.form = (...)
There are two ways to include a form in report:
form = formname
This will render the form with name formname which can then be used to create new records.
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 ‘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.
The follwing section gives some examples of typical reports
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
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 ?Formating Examples for examples of how the output can be formatted.
Formating (i.e. wrapping of data with HTML tags etc.) can be achieved in two different ways:
One can either add formatting output directely 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
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 />
# 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 20 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 />
Wiki IT: projekt/qfq/qfq-jqwidgets/Documentation/Report (last edited 2016-03-02 07:43:55 by crose)