.. ==================================================
.. ==================================================
.. ==================================================
.. 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): ::
* * * * * /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/...`