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

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}}') }}"
        }
    ]
}

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}} }}"
        }
    ]
}

SELECT list in report, SIP encoded

  • In a report create a dropdown list with the last 20 years.

  • By selecting a year, the page will be reloaded with SIP parameter filterYear=<year>

  • In table cnt are at least 20 records. The table has a column id, starting at 1.

  • The parameter filterYear can’t be manipulated by the user.

Example:

10 {
  sql = SELECT @filterYear:=YEAR( NOW()) - cnt.id+1

              # Create the SIP parameter filterYear=<year>
              , '<option value="', CONCAT('p:fake?filterYear=', @filterYear, '|s|r:8') AS _link, '"'
                # Pre select the choosen year
                , IF('{{filterYear:S0}}'=@filterYear,' selected="selected"','')
                , '>', @filterYear
              , '</option>'
        FROM cnt
        ORDER BY cnt.id
        LIMIT 20

  head = <select name="selectfield" id="selectfield" class="btn btn-default"
            onchange="var selValue = document.getElementById('selectfield').value;  location.href = '{{baseUrl:Y}}{{pageSlug:T}}?s=' + selValue; ">
  tail = </select>
}

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