System

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 FormEditor 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

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 (user cron tab):

* * * * * /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

Currently QFQ uses 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.

All columns of the SQL are available in STORE_PARENT.

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:&nbsp;
  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/…