Form.rst

Support for record locking is given with mode:

* *exclusive*: user can't force a write.

  * Including a timeout (default 15 mins recordLockTimeoutSeconds in :ref:`configuration`) for maximum lock time.

* *advisory*: user is only warned, but allowed to overwrite.
* *none*: no bookkeeping about locks.

For 'new' records (r=0) there is no locking at all.

The record locking protection is based on the `tablename` and the `record id`. Different `Forms`, with the same primary table,
will be protected by record locking. On the other side, action-`FormElements` updating non primary table records are not
protected by 'record locking': the QFQ record locking is *NOT 100%*.

The 'record locking' mode will be specified per `Form`. If there are multiple Forms with different modes, and there is
already a lock for a `tablename` / `record id` pair, the most restrictive will be applied.


Best practice
-------------

View: List vs. Detail
^^^^^^^^^^^^^^^^^^^^^

As 'list' a number of data/rows shown on the page is meant.

As 'detail' a form is meant, which shows one single data record and let the user edit it.

To provide an easy understandable navigation structure, it's nice for the user to stay on the same page, even the user is
in 'detail' or 'list' mode. Create a single QFQ tt-content record on a fresh page::

  form = {{form:SE}}

  10.sql = SELECT p.name, CONCAT('p:{{pageAlias:T}}&form=Person&r=', p.id) AS _pagee FROM Person AS p
  10.rend = <br>

* If the page is called without any parameter, a list of persons is shown.
* Behind each name, a button is shown. A click on it opens the form 'Person' (with the selected person record) on the same page.

Mode 'list' or 'detail' is detected automatically: if a form is given via STORE_SIP or STORE_TYPO3, the form (=detail) is
shown else the report (=list).



Custom default value only for 'new records'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Method 1
""""""""

On `Form.parameter` define a `fillStoreVar` query with a column name equal to a form field. That's all.

Example: ::

  FormElement.name = technicalContact
  Form.parameter.fillStoreVar = {{! SELECT CONCAT(p.firstName, ' ', p.name) AS technicalContact FROM Person AS p WHERE p.account='{{feUser:T}}' }}

What we use here is the default STORE prio FSRVD. If the form loads with r=0, 'F', 'S' and 'R' are empty. 'V' is filled.
If r>0, than 'F' and 'S' are empty and 'R' is filled.

Method 2
""""""""

In the specific `FormElement` set `value={{columnName:RSE}}`. The link to the form should be rendered with
'"...&columnName=<data>&..." AS _page'. The trick is that the STORE_RECORD is empty for new records, and therefore the
corresponding value from STORE_SIP will be returned. Existing records will use the already saved value.

Central configured values
^^^^^^^^^^^^^^^^^^^^^^^^^

Any variable in :ref:`configuration` 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}}*.

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  :ref:`configuration` by

* *showDebugInfo: yes* or
* *showDebugInfo: auto* and logged in in the same Browser as a Typo3 backend user.

More detailed error messages
^^^^^^^^^^^^^^^^^^^^^^^^^^^^

If *showDebugInfo* is enabled, a full stacktrace and variable contents are displayed in case of an error.

Form search
^^^^^^^^^^^

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='{{pageAlias: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 - be careful not to open a cross site scripting door: the parameter 'search' needs to be sanitized.
  20 {
    sql = SELECT CONCAT('p:{{pageAlias:T}}&form=form&r=', f.id) AS _pagee, f.id, f.name, f.title
              FROM Form AS f
              WHERE f.name LIKE  '%{{search:CE:alnumx}}%'
    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.

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


* Slave form, `ord` *FormElement*, field `value`: set

  ::

   `{{ord: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}} }}`

.. _example_class_template_group:

Icons Template Group
^^^^^^^^^^^^^^^^^^^^

This example will display graphics instead of text 'add' and 'remove'. Also there is a distance between the templateGroups.

* FormElement.parameter::

     tgClass = qfq-child-margin-top
     tgAddClass = btn alert-success
     tgAddText = <span class="glyphicon glyphicon-plus" aria-hidden="true"></span>
     tgRemoveClass = btn 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 accidentally 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

Typeahead: SQL
^^^^^^^^^^^^^^

Table: Person

+---------------------+--------------+
| Name                | Type         |
+=====================+==============+
| id                  | int          |
+---------------------+--------------+
| name                | varchar(255) |
+---------------------+--------------+

* Form:

  * Name: PersonNameTypeahead
  * Table: Person

* FormElements

  * Name: name

    * Type: text
    * Label: Name
    * Parameter: ``typeAheadSql = SELECT name FROM Person WHERE name LIKE ? OR firstName LIKE ? LIMIT 100``

Typeahead: LDAP with additional values
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Table: Person

+---------------------+--------------+
| Name                | Type         |
+=====================+==============+
| id                  | int          |
+---------------------+--------------+
| name                | varchar(255) |
+---------------------+--------------+
| firstname           | varchar(255) |
+---------------------+--------------+
| email               | varchar(255) |
+---------------------+--------------+

* Form:

  * Name: PersonNameTypeaheadSetNames
  * Table: Person
  * Parameter::

      ldapServer = directory.example.com
      ldapBaseDn = ou=Addressbook,dc=example,dc=com

* FormElements

  * Name: email

    * Class: native
    * Type: text
    * Label: Email
    * Note: Name: {{cn:LE}}<br>Email: {{mail:LE}}
    * dynamicUpdate: checked
    * Parameter::

       # Typeahead
       typeAheadLdapSearch = (|(cn=*?*)(mail=*?*))
       typeAheadLdapValuePrintf ‘%s / %s’, cn, email
       typeAheadLdapIdPrintf  ‘%s’, email

       # dynamicUpdate: show note
       fillStoreLdap
       ldapSearch = (mail={{email::alnumx}})
       ldapAttributes = cn, email

  * Name: fillLdapValues

    * Class: action
    * Type: afterSave
    * Parameter::

       fillStoreLdap
       ldapSearch = (mail={{email::alnumx}})
       ldapAttributes = cn, email

       slaveId={{id:R0}}
       sqlUpdate={{ UPDATE Person AS p SET p.name='{{cn:L:alnumx:s}}' WHERE p.id={{slaveId}} LIMIT 1 }}

.. _`import-merge-form`:

Import/merge form
-----------------

The form `copyFormFromExt` copies a form from table `ExtForm / ExtFormElement` to `Form / FormElement`. The import/merge
form:

* offers a drop down list with all forms of `ExtForm`,
* an input element for the new form name,
* create new Form.id
* copied FormElements get the new Form.id.
* the copied form will be opened in the FormEditor.

Installation:

* Play (do all sql statements on your QFQ database, e.g. via `mysql <dbname> < copyFormFromExt.sql` or `phpMyAdmin`) the
  file  *<ext_dir>/Classes/Sql/copyFormFromExt.sql*.
* Insert a link/button 'Copy form from ExtForm' to open the import/merge form. A good place is the list of all forms (see :ref:`form-editor`).
  E.g.: ::

    10.head = {{'b|p:id={{pageAlias:T}}&form=copyFormFromExt|t:Copy form from ExtForm' AS _link }} ...

If there are several T3/QFQ instances and if forms should be imported frequently/easily, set up a one shot
'import Forms from db xyz' like: ::

  10.sql = CREATE OR REPLACE table ExtForm SELECT * FROM <db xyz>.Form
  20.sql = CREATE OR REPLACE table ExtFormElement SELECT * FROM <db xyz>.FormElement


.. _`formAsFile`:

Form As File
------------

Json Form Editor
^^^^^^^^^^^^^^^^

The json form editor allows developers to view/edit/copy/paste forms in the json format.

* The json form editor can be accessed via the standard form-editor report. See: :ref:`form-editor`
* All fields of the Form and the FormElements Table are encoded into one big json object. Each formElement is represented as an object contained in the top-level array called `FormElement_ff`.
* Form and FormElement **ids are not encoded into the json string**. Therefore the json may be freely copied and pasted i.e. reused without fear of overwriting the original form.

  * **Container** : Container FormElements are referenced via their name instead of their id by other FormElements. The additional key `containerName_ff` is added to the json of a FormElemnt to reference a container.

* **Form Backups** : If a form is edited using the json form editor then a backup of the previous version is saved in the directory `form/.backup` inside the qfq project directory (:ref:`qfq-project-path-php`).

  * Cron job to remove old backups: :ref:`removeFormBackupCron`

Auto Sync
^^^^^^^^^

**ATTENTION** : The synchronisation between form files and Form/FormElement tables has been disabled in favor of the json form editor. It might be enabled again in a future version.

* **Sync** : Forms are synced between the database and form files located in the form directory contained in the qfq project directory. See: :ref:`qfq-project-path-php`

  * The form files have precedence over database forms. i.e. If the form has changed both in the file and database since the last sync, then the database changes are lost. (But there is a backup, see bellow).
  * If a form exists in the database but was never exported to a file (i.e. column `fileStats` in the Form table is empty) then the form is not deleted but kept and exported to a new form file.
  * **ATTENTION** : Form and FormElement changes in the database are only registered if they are performed by the form editor. Otherwise they might get overwritten during the next file sync!

* **First Export** : If there is no form directory then it is created and all forms are exported from the database once.
* **Form Backups** : On every change of a form (both in file and in database) a new backup file is saved in `qfqProject/form/.backup`

  * To restore a backup file just copy it into the parent folder (`form`) and rename it to `<formName>.json`. (Don't forget to adjust the file read/write rights accordingly)
  * Cron job to remove old backups: :ref:`removeFormBackupCron`

* **Form Copy** : To copy a form simply duplicate the corresponding form file `<formName>.json` and rename the duplicate accordingly `<newFormName>.json>`. (Don't forget to adjust the file read/write rights accordingly)