Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
.. ==================================================
.. ==================================================
.. ==================================================
.. Header hierarchy
.. ==
.. --
.. ^^
.. ""
.. ;;
.. ,,
..
.. --------------------------------------------used to the update the records specified ------
.. Best Practice T3 reST: https://docs.typo3.org/m/typo3/docs-how-to-document/master/en-us/WritingReST/CheatSheet.html
.. Reference: https://docs.typo3.org/m/typo3/docs-how-to-document/master/en-us/WritingReST/Index.html
.. Italic *italic*
.. Bold **bold**
.. Code ``text``
.. External Links: `Bootstrap <http://getbootstrap.com/>`_
.. Add Images: .. image:: ../Images/a4.jpg
..
..
.. Admonitions
.. .. note:: .. important:: .. tip:: .. warning::
.. Color: (blue) (orange) (green) (red)
..
.. Definition:
.. some text becomes strong (only one line)
.. description has to indented
.. -*- coding: utf-8 -*- with BOM.
.. include:: Includes.txt
.. _`system`:
System
======
.. _form-editor-usage:
FormEditor with usage
---------------------
With a large number of forms, it's important to know how often a form has been used, on which pages it's used and when
has the form be used. The following report includes the regular :ref:`form-editor` as well::
#
# Form
#
# a) List of forms: {{form:S}}='', {{formIdHistory:S}}=''
# b) Edit Form: {{form:S}} - Open form {{form:S}} with record {{r:S}} - typically the FormEditor
# c) Use history of a given form: {{formIdHistory:S}}
#
# {{form:S}}
# {{formIdHistory:S0}} - usage history of form '{{formIdHistory:S}}'
form={{form:SE}}
10 {
# List of Forms: Do not show this list of forms if there is a form given by SIP.
# Table header.
sql = SELECT '<th data-sorter="false" class="filter-false">'
, CONCAT('p:{{pageAlias:T}}&form=Form&') as _pagen
, '</th><th>Name'
, '</th><th>Title'
, '</th><th>Table'
, '</th><th>#'
, '</th><th><em>First</em>'
, '</th><th><em>Last</em>'
, '</th><th><em>PageId</em></th>'
FROM (SELECT '') AS fake
WHERE {{formIdHistory:S0}}=0
head = <table class="table table-hover qfq-table-50 tablesorter tablesorter-filter" id="{{pageAlias:T}}-form">
rbeg = <thead class="qfq-sticky"><tr>
rend = </tr></thead><tbody>
tail = </tbody></table>
20 {
# All forms
sql = SELECT CONCAT('p:{{pageAlias:T}}&form=Form&r=', f.id) as _pagee
, CONCAT(f.name, ' <span class="text-muted">(', f.id, ')</span>')
, QMORE(strip_tags(f.title),50)
, f.tableName
, CONCAT('p:{{pageAlias:T}}&formIdHistory=', f.id, '|s|b|t:<span class="badge">', COUNT(fsl.id), '</span>'
, IF(COUNT(fsl.id)=0, '|r:3','') ) as _link
, CONCAT( '<em><span title="',MIN(fsl.created), '">', DATE_FORMAT( MIN( fsl.created), '%d.%m.%Y'), '</span></em>')
, CONCAT( '<em><span title="',MAX(fsl.created), '">', DATE_FORMAT( MAX( fsl.created), '%d.%m.%Y'), '</span></em>')
, CONCAT('<em>', GROUP_CONCAT(DISTINCT fsl.pageId ORDER BY fsl.pageId), '<em>')
FROM Form AS f
LEFT JOIN FormSubmitLog AS fsl
ON fsl.formId=f.id
WHERE {{formIdHistory:S0}}=0
GROUP BY f.id
ORDER BY f.name
rbeg = <tr>
rend = </tr>
fbeg = <td>
fend = </td>
}
}
30 {
# History of a Form {{formIdHistory:S0}}
sql = SELECT f.name
, fsl.feUser
, fsl.recordId
, fsl.pageId
, fsl.created
FROM FormSubmitLog AS fsl, Form AS f
WHERE fsl.formId={{formIdHistory:S0}}
AND fsl.formId=f.id
ORDER BY fsl.created DESC
head = <table class="table table-hover qfq-table-50 tablesorter tablesorter-filter" id="{{pageAlias:T}}-formHistory">
<thead class="qfq-sticky"><tr><th>Form</th><th>feUser</th><th>recordId</th><th>pageId</th><th>Submit</th></tr></thead><tbody>
tail = </tbody></table>
rbeg = <tr>
rend = </tr>
fbeg = <td>
fend = </td>
altsql = SELECT IF('{{formIdHistory:S0}}'='0', '', '<div class="alert alert-warning">History is empty</div>')
}
.. _`autocron`:
AutoCron
--------
The `AutoCron` service fires periodically jobs like `open a webpage` (typically a QFQ page which does some database
actions) or `send mail`.
* AutoCron will be triggered via system cron. Minimal time distance therefore is 1 minute. If this is not sufficient,
any process who starts `.../typo3conf/ext/qfq/Classes/External/autocron.php` via `/usr/bin/php` frequently might be used.
* Custom start time and frequency.
* Per job:
* If a job still runs and receives the next trigger, the running job will be completed first.
* If more than one trigger arrives during a run, only one trigger will be processed.
* If the system misses a run, it will be played as soon as the system is online again.
* If multiple runs are missed, only one run is fired as soon as the system is online again.
* Running and processed jobs can easily be monitored via *lastRun, lastStatus, nextRun, inProgress*.
Setup
^^^^^
* Setup a system cron entry, typically as the webserver user ('www-data' on debian).
* Necessary privileges:
* Read for `.../typo3conf/ext/qfq/*`
* Write, if a logfile should be written (specified per cron job) in the custom specified directory.
Cron task (Ubuntu: [root] crontab -e): ::
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
* * * * * /usr/bin/php /var/www/html/typo3conf/ext/qfq/Classes/External/autocron.php
AutoCron Jobs of type 'website' needs the php.ini setting: ::
allow_url_fopen = On
Remember: if a cron job fails for whatever reason, the cron daemon will send a mail to the userid who started the cron
job. E.g. www-data. Setup a email forward of such account to a frequently read email account.
Create / edit `AutoCron` jobs
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Create a T3 page with a QFQ record (similar to the formeditor). Such page should be access restricted and is only needed
to edit `AutoCron` jobs::
dbIndex={{indexQfq:Y}}
form={{form:S}}
10 {
# Table header.
sql = SELECT CONCAT('p:{{pageAlias:T}}&form=cron') AS _pagen, 'id', 'Next run','Frequency','Comment'
, 'Last run','In progress', 'Status', 'Auto generated'
head = <table class='table table-hover qfq-table-50'>
tail = </table>
rbeg = <thead><tr>
rend = </tr></thead>
fbeg = <th>
fend = </th>
10 {
# All Cron Jobs
sql = SELECT CONCAT('<tr class="'
, IF(c.lastStatus LIKE 'Error%','danger','')
, IF(c.inProgress!=0 AND DATE_ADD(c.inProgress, INTERVAL 10 MINUTE)<NOW(),' warning','')
, IF(c.status='enable','',' text-muted'),'" '
, IF(c.inProgress!=0 AND DATE_ADD(c.inProgress, INTERVAL 10 MINUTE)<NOW(),'title="inProgress > 10mins"'
, IF(c.lastStatus LIKE 'Error%','title="Status: Error"',''))
, '>')
, '<td>', CONCAT('p:{{pageAlias:T}}&form=cron&r=', c.id) AS _pagee, '</td><td>'
, c.id, '</td><td>'
, IF( c.nextrun=0,"", DATE_FORMAT(c.nextrun, "%d.%m.%y %H:%i:%s")), '</td><td>'
, c.frequency, '</td><td>'
, c.comment, '</td><td>'
, IF(c.lastrun=0,"", DATE_FORMAT(c.lastrun,"%d.%m.%y %H:%i:%s")), '</td><td>'
, IF(c.inProgress=0,"", DATE_FORMAT(c.inProgress,"%d.%m.%y %H:%i:%s")), '</td><td>'
, LEFT(c.laststatus,40) AS '_+pre', '</td><td>'
, c.autoGenerated
, CONCAT('U:form=cron&r=', c.id) AS _paged, '</td></tr>'
FROM Cron AS c
ORDER BY c.id
}
}
Usage
^^^^^
The system `cron` service will call the `QFQ AutoCron` every minute. `QFQ AutoCron` checks if there is a pending job, by looking
for jobs with `nextRun` <= `NOW()`. All found jobs will be fired - depending on their type, such jobs will send mail(s) or
open a `webpage`. A `webpage` will mostly be a local T3 page with at least one QFQ record on it. Such a QFQ record might
do some manipulation on the database or any other task.
A job with `nextRun`=0 or `inProgress`!=0 won't never be started.
Due to checking `inProgress`, jobs will never run in parallel, even if a job needs more than 1 minute (interval system
cron).
Job: repeating
""""""""""""""
* frequency: '1 MINUTE', '2 DAY', '3 MONTH', ....
After finishing a job, `nextRun` will be increased by `frequency`. If `nextRun` still points in the past, it will be
increased by `frequency` again, until it points to the future.
Job: asynchronous
"""""""""""""""""
* frequency: <empty>
An 'AutoCron' job becomes 'asynchronous' if `frequency` is empty. Then, auto repeating is switched off.
If `nextRun` is > 0 and in the past, the job will be fired. After the job has been done, `nextRun` will be set to 0.
This is useful for jobs which have to be fired from time to time.
To fire such an asynchronous job, just set `nextRun=NOW()` and wait for the next system cron run.
If such a job is running and a new `nextRun=NOW()` is applied, the 'AutoCron' job will be fired again during the next
system cron run.
Type: Mail
""""""""""
At the moment there is a special sendmail notation - this will change in the future.
* `Mail`: ::
{{!SELECT 'john@doe.com' AS sendMailTo, 'Custom subject' AS sendMailSubject, 'jane@doe.com' AS sendMailFrom, 123 AS sendMailGrId, 456 AS sendMailXId}}
AutoCron will send as many mails as records are selected by the SQL query in field `Mail`. Field `Mail body` provides
the mail text.
Type: Website
"""""""""""""
The page specified in `URL` will be opened.
Optional the output of that page can be logged to a file (take care to have write permissions on that file).
* `Log output to file`=`output.log` - creates a file in the Typo3 host directory.
* `Log output to file`=`/var/log/output.log` - creates a file in `/var/log/` directory.
Also `overwrite` or `append` can be selected for the output file. In case of `append` a file rotation should be setup on
OS level.
To check for a successful DB connection, it's a good practice to report a custom token on the T3 page / QFQ record like
'DB Connect: ok'. Such a string can be checked via `Pattern to look for on output=/DB Connect: ok/`. The pattern
needs to be written in PHP PCRE syntax. For a simple search string, just surround them with '/'.
If the pattern is found on the page, the job get's 'Ok' - else 'Error - ...'.
Access restriction
;;;;;;;;;;;;;;;;;;
To protect AutoCron pages not to be triggered accidental or by unprivileged access, access to those page tree might be
limited to localhost. Some example Typoscript: ::
# Access allowed for any logged in user or via 'localhost'
[usergroup = *] || [IP = 127.0.0.1]
page.10 < styles.content.get
[else]
# Error Message
page.10 = TEXT
page.10.value = <h2>Access denied</h2>Please log in or access this page from an authorized host. Your current IP address:
page.20 = TEXT
page.20.data = getenv : REMOTE_ADDR
[global]
AutoCron / website: HTTPS protocol
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
* For `https` the PHP extension `php_openssl` has to be installed.
* All certificates are accepted, even self signed without a correct chain or hostnames, not listed in the certificate.
This is useful if there is a general 'HTTP >> HTTPS' redirection configured and the website is accessed via `https://localhost/...`