UseCase.rst

.. ==================================================
.. ==================================================
.. ==================================================
.. 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/>`_
.. Internal Link: :ref:`downloadButton` (default url text) or :ref:`download Button<downloadButton>` (explicit url text)
.. 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

.. _`use-case`:

Use Case
========

To install the following use cases, please:

* Create the page and tt-content records.
* Copy the JSON form code to a new empty form (open Form in JSON mode).


.. _`self-registration`:

Self Registration
-----------------

**Concept:**

* The T3 FE User record will be created on the fly in the *last* step, when the user set's the password.
* Form *Registration*

  * Input: *last name, first name, email*.
  * Create record in table `Person`, if no name or email sibling is found. Else give a hint to do a password reset.
  * Set a new ``Person.auth`` token.
  * Set ``Person.authExpired = NOW() + INTERVAL 1 DAY``.
  * Send an email to the given email address with a password reset link (incl. the token) which is time limited.
  * Further steps: See *Set password*.

* Form *Request password reset link*

  * This is optional, but useful to offer the opportunity to reset the own password.
  * Input: *email address*.
  * Send an email to the given email address with a password reset link (incl. a token) which is time limited.

    * Only send email if the email address is known!
    * If email is not found, the user should not be noticed. This prohibits misusing the form to validate email adresses.
    * Set a new ``Person.auth`` token.
    * Set ``Person.authExpired = NOW() + INTERVAL 1 DAY``.

* Form *Set password*

  * The user types in the new password.
  * On save:

    * If the FE account does not exist, it will be created.
    * Set the FE user password.
    * Clear ``Person.authExpired``.

Table: Person
^^^^^^^^^^^^^
::

    CREATE TABLE `Person` (
      `id` int(11) UNSIGNED NOT NULL,
      `lastName` varchar(64) NOT NULL DEFAULT '',
      `firstName` varchar(64) NOT NULL DEFAULT '',
      `email` varchar(128) NOT NULL,
      `account` varchar(128) NOT NULL,
      `auth`  varchar(32) NOT NULL DEFAULT '',
      `authExpire` datetime DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PACK_KEYS=1;

    ALTER TABLE `Person` ADD PRIMARY KEY (`id`);
    ALTER TABLE `Person` MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

or::

    ALTER TABLE `Person` ADD `auth` VARCHAR(32) NOT NULL DEFAULT '' AFTER `account`, ADD `authExpire` DATETIME DEFAULT NULL AFTER `auth`;

Registration
^^^^^^^^^^^^

Page: Registration
""""""""""""""""""

* Page Alias: registration

QFQ content record::

    #
    # {{action:SE}}: thanksRegistration
    #


    form={{SELECT IF('{{action:SE}}' = '','registration','') }}

    20.sql = SELECT "<p>Thank you for your registration.</p><p>An email with further instructions has been sent to you.</p>"
                    , "<p>You should receive the mail during the next 5 minutes. If not, please check your JUNK folder.</p>"

                FROM (SELECT '') AS fake
                WHERE '{{action:SE}}' = 'thanksRegistration'
                  AND '{{form:SE}}'=''


Form: registration
""""""""""""""""""

.. note::

  Take care that the QFQ STORE_SYTEM variable `ADMINISTRATIVE_EMAIL` is set.

JSON Form
::

    {
        "title": "Registration",
        "tableName": "Person",
        "permitNew": "always",
        "permitEdit": "never",
        "showButton": "close,save",
        "forwardMode": "url-sip-skip-history",
        "forwardPage": "?id=registration&action=thanksRegistration",
        "parameter": "submitButtonText=Register",
        "FormElement_ff": [
            {
                "enabled": "yes",
                "name": "firstName",
                "label": "First name",
                "mode": "required",
                "type": "text"
            },
            {
                "enabled": "yes",
                "name": "lastName",
                "label": "Last name",
                "mode": "required",
                "type": "text"
            },
            {
                "enabled": "yes",
                "name": "email",
                "label": "Email",
                "mode": "required",
                "class": "native",
                "type": "text",
                "checkType": "email",
                "parameter": "retype\r\nretypeLabel=Retype email"
            },
            {
                "enabled": "yes",
                "label": "Check for double registration",
                "mode": "show",
                "modeSql": "",
                "class": "action",
                "type": "beforeSave",
                "parameter": "sqlValidate={{!SELECT p.id FROM Person AS p WHERE p.email='{{email:F:alnumx}}'  OR ('{{firstName:F:allbut}}'=p.firstName AND  '{{lastName:F:allbut}}'=p.lastName ) LIMIT 1 }}\r\nexpectRecords=0\r\nmessageFail=Sorry, person already registered by name or email (in case of a namesake please contact <a href="mailto:{{ADMINISTRATIVE_EMAIL:Y}}">{{ADMINISTRATIVE_EMAIL:Y}}</a>). Please just reset the password under <a href='?id=reset'>reset</a>"
            },
            {
                "enabled": "yes",
                "label": "auth, deadline",
                "class": "action",
                "type": "afterSave",
                "parameter": "# Set token & expiration\r\nsqlBefore={{UPDATE Person SET auth='{{random:V}}', authExpire=DATE_ADD(NOW(), INTERVAL 1 DAY) WHERE id={{id:R}} }}"
            },
            {
                "enabled": "yes",
                "label": "a) sendEmail, b) fe user",
                "mode": "show",
                "class": "action",
                "type": "sendMail",
                "value": "Dear new user\r\nPlease set a new password under {{baseUrl:Y}}?id=set&auth={{auth:R}}\r\nRegards.",
                "parameter": "sendMailTo={{email:R}}\r\nsendMailSubject=Registration confirmation\r\nsendMailFrom={{ADMINISTRATIVE_EMAIL:Y}}\r\n\r\n# Create User in T3\r\nsqlAfter={{INSERT INTO {{dbNameT3:Y}}.fe_users (pid, usergroup, username, password, first_name, last_name, name, email) VALUES (6, '1', '{{email:F:alnumx}}','$p$initial invalid hash',  '{{firstName:R}}', '{{lastName:R}}', '{{firstName:R}} {{lastName:R}}', '{{email:F:alnumx}}') }}"
            }
        ]
    }

Request password reset link
^^^^^^^^^^^^^^^^^^^^^^^^^^^

Page: reset
"""""""""""

* Page Alias: reset

QFQ content record::

    #
    # {{action:SE}}: passwordReset
    #
    # Empty: {{action:SE}}='' > shows the form angezeigt 'passwordReset'.
    # Given: {{action:SE}}='confirmation' > show confirmation.
    #
    form={{SELECT IF('{{action:SE}}' = '','passwordReset','') }}
    r=1

    10 {
      sql = SELECT '' FROM (SELECT '') AS fake WHERE '{{action:SE}}'='confirmation'
      head = <div class="alert alert-success">
               <p>Thank you.</p>
               <p>If the email address is known in our database, we sent a password reset link to it.</p>
               <p>The mail should be received during the next minutes. If not, please check you JUNK folder.</p>
               <p>To set a new password, please click on the link provided in the email.</p>
             </div>
    }

Form: passwordReset
"""""""""""""""""""

.. note::

  * Take care that there is one dummy person record with person.id=1.
  * Update 'example.com' references.
  * Update sendMailGrId=123, sendMailXId=456

Form 'passwordReset'::

    {
        "title": "Password reset",
        "tableName": "Person",
        "permitNew": "never",
        "permitEdit": "always",
        "escapeTypeDefault": "c",
        "render": "bootstrap",
        "dirtyMode": "exclusive",
        "showButton": "save",
        "multiMode": "none",
        "forwardMode": "url-sip-skip-history",
        "forwardPage": "{{pageSlug:T}}?action=confirmation",
        "labelAlign": "default",
        "parameter": "submitButtonText = Send password reset email",
        "deleted": "no",
        "FormElement_ff": [
            {
                "enabled": "yes",
                "name": "emailValue",
                "label": "Email",
                "mode": "show",
                "class": "native",
                "type": "text",
                "encode": "specialchar",
                "checkType": "email",
                "ord": 10
            },
            {
                "enabled": "yes",
                "label": "Check for *example.com email",
                "mode": "show",
                "class": "action",
                "type": "beforeSave",
                "ord": 20,
                "parameter": "sqlValidate={{!SELECT 'fake' FROM (SELECT '')  AS fake WHERE '{{emailValue:F:alnumx}}' LIKE '%example.com'  }}\r\n expectRecords=0\r\nmessageFail=Sorry, Password reset is not possible for *example.com."
            },
            {
                "enabled": "yes",
                "label": "a) set auth, expire, b) send email",
                "mode": "show",
                "class": "action",
                "type": "sendMail",
                "encode": "specialchar",
                "checkType": "auto",
                "ord": 50,
                "value": "Dear user\r\nPlease set a new password under {{baseUrl:Y}}?id=set&auth={{auth:V}}\r\nRegards.",
                "parameter": "fillStoreVar={{!SELECT CONCAT(p.firstName , ' ', p.lastName) AS name, p.id AS _pId, @expire:=DATE_ADD(NOW(), INTERVAL 4 DAY) AS expireTs, QDATE_FORMAT(@expire) AS expire, p.email, '{{random:V}}' AS auth FROM Person AS p WHERE p.email='{{emailValue:F:alnumx}}' AND p.email!='' LIMIT 1}}\r\n\r\nsendMailTo={{email:VE}}\r\nsendMailSubject=Password Reset\r\nsendMailFrom={{ADMINISTRATIVE_EMAIL:Y}}\r\nsendMailGrId=123\r\nsendMailXId=456\r\n\r\n# Set token & expiration\r\nsqlAfter = {{UPDATE Person SET auth='{{auth:V}}', authExpire='{{expireTs:V}}' WHERE email='{{emailValue:F:alnumx}}' AND email!='' LIMIT 1}}"
            }
        ]
    }

Set new password
^^^^^^^^^^^^^^^^

Page: set
"""""""""

* Page

  * Alias: set
  * Hide in menu: yes

QFQ content record::

    #
    # {{auth:CE}} - empty >> Form 'setPassword'
    # {{auth:CE}} - unknown | expired >> Error message
    # {{auth:SE}} - valid >> Set Password
    #
    # {{action:CE}} - 'thanks'

    form={{SELECT IF( ISNULL(p.id), '', 'passwordSet' )
              FROM (SELECT '') AS fake
              LEFT JOIN Person AS p
                ON p.auth='{{auth:C:alnumx}}'
                  AND p.auth!=''
                  AND NOW()<p.authExpire }}

    r={{SELECT IFNULL(p.id, 0) FROM (SELECT '') AS fake LEFT JOIN Person AS p ON p.auth='{{auth:C:alnumx}}' AND p.auth!='' AND NOW()<p.authExpire}}


    10 {
      sql = SELECT IF( ISNULL(p.id)
                       , 'Token invalid'
                       , IF( NOW()<p.authExpire
                            ,''
                            , IF( p.authExpire=0 OR ISNULL(p.authExpire), 'Password already set', 'Token expired') )
                     )
              FROM (SELECT '') AS fake
              LEFT JOIN Person AS p
                ON p.auth='{{auth:C:alnumx}}'
                  AND p.auth!=''
              WHERE '{{action:SE}}'=''
                  AND (ISNULL(p.id) OR NOW()>=p.authExpire)
      head = <div class="alert alert-warning" role="alert">
      tail = </div>
    }

    20.sql = SELECT 'Thanks for setting the password. Please <a href="?id=login">log in</a> now.'
               FROM (SELECT '') AS fake
               WHERE '{{action:SE}}'='thanks'

Form: passwordSet
"""""""""""""""""

.. note::

    Please adjust the numbers for ``usergroup`` and ``pid`` in FormElement `Update fe_user.password > parameter > sqlInsert`
    to your needs. Remember that every FE-User needs at least one FE-Group to log in successfully.

Form 'passwordSet'::

    {
        "title": "Set password",
        "tableName": "Person",
        "permitNew": "never",
        "permitEdit": "always",
        "escapeTypeDefault": "c",
        "render": "bootstrap",
        "dirtyMode": "exclusive",
        "showButton": "save",
        "multiMode": "none",
        "forwardMode": "url-sip-skip-history",
        "forwardPage": "{{pageSlug:T}}?action=thanks",
        "parameter": "submitButtonText='Set password'",
        "FormElement_ff": [
            {
                "enabled": "yes",
                "name": "myValue",
                "label": "Password",
                "mode": "show",
                "class": "native",
                "type": "password",
                "checkType": "pattern",
                "checkPattern": "[a-zA-Z0-9-_+ *\\\/.,:;]{10,}",
                "ord": 10,
                "parameter": "retype\r\nretypeLabel=Retype password\r\ndata-pattern-error=At least 10 characters are required. Valid characters: a-z A-Z 0-9 -_+*\/.,:;\r\nextraButtonPassword"
            },
            {
                "enabled": "yes",
                "label": "Update fe_user.password",
                "mode": "show",
                "class": "action",
                "type": "afterSave",
                "encode": "specialchar",
                "ord": 20,
                "parameter": "slaveId={{SELECT fe.uid FROM {{dbNameT3:Y}}.fe_users AS fe WHERE fe.username='{{email:RE}}' AND fe.username!='' AND fe.deleted=0 LIMIT 1}}\r\n\r\n# Create FE User. Please update values of `pid`, `usergroup` to your setup.\r\nsqlInsert = {{INSERT INTO {{dbNameT3:Y}}.fe_users ( `pid`, `usergroup`, `username`, `email`, `name`, `password`,`crdate`) VALUES ( 5 , 1 , '{{email:RE}}', '{{email:RE}}', '{{lastName:RE}}, {{firstName:RE}}', '{{myValue:FE:all:p}}',  UNIX_TIMESTAMP() ) }}\r\n\r\nsqlUpdate = {{UPDATE {{dbNameT3:Y}}.fe_users SET password='{{myValue:FE:all:p}}' WHERE uid={{slaveId:V0}}  }}\r\n\r\nsqlAfter={{UPDATE Person SET authExpire=0 WHERE id={{id:R}} }}"
            }
        ]
    }

.. _`multi-db-use-case`:

Multi-DB use case
-----------------

The Form Editor (two forms: `Form` and `FormElement`) should show (on the last pill) the table definiton. This is quite
easy as long as the QFQ forms (table `Form` or table `FormElement`) and the QFQ data (where the final data is stored)
are in the same database. The example
shows that in `Form` the table name is easy to retrieve via `{{tableName:R}}` from the current `Form` record. For
`FormElement` an extra preparation step is necessary to fetch the `tableName` from the Form-defintion ::

  Form:
  10.sql = SHOW COLUMNS FROM {{tableName:RE}}

  FormElement:
  10.sql = SELECT tableName AS _tableName FROM Form AS f WHERE f.id={{formId:SR0}}
  10.20.sql = SHOW COLUMNS FROM {{tableName:RE}}

It becomes more tricky if QFQ System and QFQ Data are in separate databases (both databases on the same host). One option
is defining the database name (which is available via STORE_SYSTEM in `{{dbNameData:Y}}`) in front of the table (standard
SQL syntax)::

  Form:
  10.sql = SHOW COLUMNS FROM {{dbNameData:Y}}.{{tableName:RE}}

  FormElement:
  10.sql = SELECT tableName AS _tableName FROM {{dbNameQfq:Y}}.Form AS f WHERE f.id={{formId:SR0}}
  10.20.sql = SHOW COLUMNS FROM {{dbNameData:Y}}.{{tableName:RE}}

Unfortunately, this breaks if the two databases are on different hosts. The solution is to switch DB access via `dbIndex`::

  Form:
  dbIndex={{indexData:Y}}
  10.sql = SHOW COLUMNS FROM {{tableName:RE}}

  FormElement:
  dbIndex={{indexQfq:Y}}
  10.sql = SELECT tableName AS _tableName FROM Form AS f WHERE f.id={{formId:SR0}}
  10.20.dbIndex={{indexData:Y}}
  10.20.sql = SHOW COLUMNS FROM {{tableName:RE}}