.. ================================================== .. Header hierarchy .. == .. -- .. ^^ .. "" .. ;; .. ,, .. .. -------------------------------------------------- .. 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 `__ .. Internal Link: :ref:`downloadButton` (default url text) or :ref:`Download Button` (explicit url text) .. Anker for internal link: .. _`download`: .. Add Image: .. image:: ./Images/a4.jpg .. .. Add image with caption: .. figure:: ./Images/black_dot.png .. :class: with-border .. :width: 20px .. Add Attachment (place in Documentation/_static): `Download CSV <_static/yourfile.csv>`__ .. .. Admonitions .. .. note:: .. important:: .. tip:: .. warning:: .. Color: (blue) (orange) (green) (red) .. .. Definition: .. some text becomes strong (only one line) .. description has to indented .. .. Code Block: .. code-block:: sql .. .. -*- coding: utf-8 -*- with BOM. .. include:: Includes.txt .. _`qfqtutorial`: Tutorial ======== .. note:: The tutorial is intended for new employees in the QFQ team. Important Links --------------- You should have received personal access credentials to the following resources. * phpMyAdmin (database) ``_ * Typo3 Backend ``_ * Frontend ``_ * Typo3 Backend (Solutions) `https://webwork22.math.uzh.ch/qfqtutorial/typo3 `_ kpweb: qfqtutorial (pma & t3) * Frontend (Solutions) `https://webwork22.math.uzh.ch/qfqtutorial `_ Tutorial DB ----------- Open phpMyAdmin and execute `Tutorial.example.sql <_static/Tutorial.example.sql>`__ in your personal database. This will create the tables and data required for the upcoming tasks. With MariaDB (a fork of MySQL) you can work directly via the command line, or use one of the many MySQL database frontends. In the QFQ team, phpMyAdmin is primarily used (link to the current installation see above). For the introduction, we recommend using a frontend, not the command line. SQL - Basic ----------- .. image:: ./Images/Tutorial/tutorial-erd-simple.png Source DrawIO: `tutorial-erd.drawio <_static/tutorial-erd.drawio>`__ `Solutions to the tasks `__ Task 1 - SELECT ^^^^^^^^^^^^^^^ a) Output the names of all books. b) Output the names of all books, except the book with the name "The Hobbit". Task 2 - JOIN, NOW, CONCAT ^^^^^^^^^^^^^^^^^^^^^^^^^^ a) Output the names of all books in the first column and the last names of the authors in the second column. b) Output a list of all current, non-overdue rentals with the name of the book, the name of the person currently borrowing it, and the due date ("end"). For a current (non-overdue) rental, the current time must be between "start" and "end" and the book must not yet have been returned (returned = 0). c) Output a list containing in a single column all books like this: "The book '(Bookname)' was written by '(Author Firstname)' '(Author Lastname)'" Task 3 - ORDER BY, LIMIT ^^^^^^^^^^^^^^^^^^^^^^^^ a) Output the names of all books in reverse alphabetical order. b) Sort the result of task 2b by due date (ascending). Show only the first three results. Task 4 - COUNT, SUM ^^^^^^^^^^^^^^^^^^^ a) Output the number of books by the author "Dan Brown". b) Output the total number of pages of all books. Task 5 - GROUP BY, LEFT JOIN, GROUP_CONCAT, NULL ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ a) In the first column, output the names of all books, and in the second column all rental dates of the book ("start") separated by commas. b) In the first column, output the names of all authors (persons who have written at least one book), and in the second column all works of the respective author (comma separated). Task 6 - IF, DATE_FORMAT ^^^^^^^^^^^^^^^^^^^^^^^^ a) In the first column, output the names of all persons. In the second column, output "yes" if the person has already borrowed a book. Otherwise, output "no". b) In the first column, output the names of all books. In the second column, output "available now" if the book is currently not borrowed. If the book is currently borrowed, then output "available from (dd.mm.yyyy)". Task 7 - INSERT ^^^^^^^^^^^^^^^ a) Insert a new book into the database. The name should be "New Book", it has 100 pages and was written by the author with id 1. Write down or remember the id of the new book. Use the SQL INSERT statement. Do not use the phpMyAdmin Insert function. Task 8 - UPDATE ^^^^^^^^^^^^^^^ a) Update the new book using the id remembered earlier. Set the name to "Revised New Book" and the number of pages to 101. b) Change the author of all books to "J. R. R. Tolkien" (do not update via id). Task 9 - DELETE ^^^^^^^^^^^^^^^ a) Delete everything from the Rental table. b) Delete the book with the id from task 7. Task 10 - Grp, Glue ^^^^^^^^^^^^^^^^^^^ Grp and Glue are not SQL keywords. They are a best practice we often use, see :ref:`cg-multi-purpose-tables`. a) Look at the following diagram and think about what Grp and Glue could stand for. Replace Grp and Glue with other table names that would make sense in this context. (For now ignore that Grp has a recursive relation with itself.) .. image:: ./Images/Tutorial/tutorial-erd.png Source DrawIO: `tutorial-erd.drawio <_static/tutorial-erd.drawio>`__ b) Why is the table called 'Grp' and not simply 'Group'? c) What do we generally achieve by using Grp and Glue? d) Why does Grp have a 'grId' that points to another Grp? e) Think of a scenario in which Grp is connected via Glue to the table 'Person'. Example of Grp and Glue interaction with tables Person and Book """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" .. image:: ./Images/Tutorial/grp_glue.png * **Glue as a Bridge:** * The 'Glue' table serves as a connection between the 'Grp' table and the 'Person' and 'Book' tables. * **Understanding Glue Columns**: * The 'Glue.xId' represents the id from either the 'Person' or 'Book' table, depending on which entity it is connecting. * The 'Glue.grId' corresponds to the 'Grp.id', creating the link between an entity (Person or Book) and a group. * **Practical Example**: * For instance, 'Book_1' (with 'id:1') is connected to 'grId:2', which in turn links to 'Grp.id:2', identifying it as part of the "Fantasy" genre. * **Grp Hierarchy**: * The 'Grp' table has a hierarchical structure where rows with 'grId:0' act as parents. The child records have their `grId` matching the id of the parent, establishing the parent-child relationship within the groups. SQL - Advanced -------------- .. tip:: ``GROUP BY`` Always specify, if aggregate functions are used - if not, MySQL will do it, which can be different from what is expected. Performance ^^^^^^^^^^^ * GROUP BY * If possible, use AND to minimize resultset. * Do not use GROUP BY if you are not understanding why it is necessary. * SUBSELECT() * Remember: for each selected row of the outer SELECT, the subselect is fired. * Example: Outer SELECT has 100 rows, than 101 queries are fired. .. warning:: ``SELECT p.id FROM Person AS p WHERE p.id in (SELECT adr.pId FROM Address AS adr WHERE adr.pId=p.id)`` This is a huge waste of database performance! * Use the same CHARACTER SET and COLLATION on all of your tables. If they are mixed, the indexes can still be used, but MariaDB have to convert it/reread - which practicaly means a deep impact. .. note:: ``ALTER TABLE CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;`` Prerequisites Web Development ----------------------------- CMS / Typo3 ^^^^^^^^^^^ You will receive an introduction to Typo3; specifically, the following points should be conveyed to you: * What is a CMS? * Pages (Title, Page Slug, Hide in Menu), PageContent * FeUser, BeUser * Permissions HTML Basics ^^^^^^^^^^^ It is assumed that you are already familiar with the basics of HTML. This HTML section is intended to refresh your knowledge. Task 1 - HTML Table """"""""""""""""""" Create a new Typo3 page and add a Page Content section of type *HTML*. In it, create the following table using pure HTML (no CSS styling), to refresh your knowledge. .. image:: ./Images/Tutorial/ex1_table.png Task 2 - Self-study """"""""""""""""""" This is a list of HTML topics and skills. Work through the list. What you already know well, you can skip. If you are not familiar with something, look it up on the Internet (e.g. https://www.w3schools.com/html/ ) and try it directly in your own HTML Page Content! * HTML Editors * HTML Basic * HTML Elements * HTML Attributes * HTML Headings * HTML Paragraphs * HTML Styles * HTML Formatting * HTML Quotations * HTML Comments * HTML CSS * HTML Links * HTML Images * HTML Tables * HTML Lists * HTML Blocks * HTML Classes * HTML JavaScript * HTML File Paths * HTML Head * HTML Layout * HTML Responsive * HTML Computercode * HTML Entities * HTML Symbols * HTML URL Encode * HTML Forms * HTML FormElements * HTML Input Types * HTML Input Attributes * HTML Examples * HTML Quiz * HTML Exercises * HTML Certificate * HTML Summary *Skills* * Text formatting (Heading, Bold, Italic) Links: * Fully qualified * Relative * Parameters * Anchor CSS / Bootstrap ^^^^^^^^^^^^^^^ It is assumed that you already have basic knowledge of CSS. If not, you can work through it in self-study. For example, there is a CSS tutorial at https://www.w3schools.com/css/ Instead of writing your own CSS classes or even individual styles per element, we use Bootstrap wherever possible, or the CSS classes delivered with QFQ (:ref:`qfq-css-classes`). Bootstrap is a CSS/JavaScript library that provides a set of unified styles for various elements (tables, buttons, etc.). Bootstrap also includes complete components, such as a modal pop-up dialog or progress bars. You do not need to worry about including Bootstrap – within your Typo3 instance, Bootstrap is already available. Pay attention to the currently integrated BS version and the corresponding documentation. Task 1 - Bootstrap Grid """"""""""""""""""""""" Another important aspect of Bootstrap is that it makes it easy for us to build our page *responsive*. A responsive page automatically adapts to the screen of the end device on which it is viewed. Bootstrap solves this mainly with its own *grid* system, which allows you to build a multi-column layout. The number of columns displayed side by side depends on the screen size. If the screen is too narrow and the grid is set up correctly, then a column automatically moves below another instead of being displayed next to it. Create a Typo3 page (HTML Page Content) and add a Bootstrap grid with three columns containing any content. The columns should behave as follows: * 3 columns should be displayed side by side on large screens ("lg") * 2 columns should be displayed side by side on medium screens ("md") * 1 column should take up the full width on small screens ("sm") Use the documentation for grids: https://getbootstrap.com/docs/3.4/css/#grid Task 2 - Bootstrap Table and Buttons """""""""""""""""""""""""""""""""""" The previously created Page Content (the table from the HTML Task 1) should now be improved with Bootstrap. Add a "Bootstrap style" to both the table and the buttons that you like. Again, use the documentation: https://getbootstrap.com/docs/3.4/css/#tables and https://getbootstrap.com/docs/3.4/css/#buttons Task 3 - Bootstrap Form """"""""""""""""""""""" Create a form with Bootstrap styling that allows the creation of a new book. A book should have the same attributes as seen in HTML Task 1: * A book has a title (text field) * A book has an author – this should be selectable from a list of authors (dropdown) * A book belongs to one or more genres (checkboxes) * A book is either available for rental or already rented (radio buttons) The Bootstrap documentation will be helpful: https://getbootstrap.com/docs/3.4/css/#forms Note: The form does not need to *do* anything – when clicking *submit*, nothing has to happen. JavaScript / jQuery ^^^^^^^^^^^^^^^^^^^ You will receive a short introduction to JavaScript and jQuery from your supervisor. Task 1 - Show / Hide an Element with jQuery """"""""""""""""""""""""""""""""""""""""""" Use JavaScript and (optionally) jQuery to extend the form from the previous task as follows: * The radio button "Available" should be set to "yes" by default. * When switched to "no", three new input fields should appear below: * A dropdown with a person selection "Currently Rented by". * A date field "Rental Start Date". * A date field "Rental Due Date". * When the radio button is switched back to "yes", these three elements should be hidden again. Note: For a date field, there are many possible solutions – native Bootstrap does not include one directly. Search the Internet for a datepicker solution that can be integrated! (e.g. "jQuery Datepicker") Basic Knowledge --------------- * Here only a small collection of important topices are explained. * See also :ref:`nativeClassExtra`. Read through it once and come back here. General Tips ^^^^^^^^^^^^ * The :ref:`dbg-dev-panel` will show latest STORE_RECORD values and others. * Debug a value: show value in the FormElement under *Layout → Note* field. * Debug a query: Execute directly on the database. Tools like https://www.phpmyadmin.net/ or `mysql` are useful. * Always have a sharp look at the :ref:`dbg-error-exception` - it helps a lot. * In FormEditor, you'll find the table definition of the primary always at the last pill. * Define a strategy how to name forms: * E.g. `person`, `person-admin`, `person...` (all person forms listed together). * For subrecords, some might find it useful to prefix with *sub*. * Per sub-tool / sub-functionality, define prefixes and name the forms accordingly. * Add the columns *modified* and *created* to every table (:ref:`cg-table-structure`). This helps debugging. Especially in the `Glue` table, this is useful to directly see whether a (new) entry was created, modified or untouched. * Pre-Render: In `Report`, links/buttons can be created at an early position, save the button in a variable via *AS* and hide it (`AS '_link|newPersonBtn|_hide'`). Afterwards, simply call the button at the place where necessary: ````. * In `Report` work with code blocks ``{}``. Name them by simply writing a label before the opening bracket. * Documentation: Sections :ref:`store`, :ref:`Form` and :ref:`report` are most important. * Under *Report* → :ref:`qfq-css-classes`, :ref:`bootstrap` and :ref:`tablesorter` find styling classes for tables in the report. * Under *Form* → :ref:`class-native` find explanation of the different FormElement types. FormEditor: Form ---------------- :ref:`form-main` Pill: Basic ^^^^^^^^^^^ Field: Name """"""""""" The name under which the form is saved. Field: Title """""""""""" The headline of the form, visible to the user. It can be dynamically adjusted by logic – depending on the usage of the form. .. code-block:: text {{SELECT IF( {{id:R0}} = 0, "Add a Genre", "Edit a Genre") }} If a new form with ID 0 is created, *Add a Genre* will be displayed, otherwise *Edit a Genre*. The R stands for the Record Store. How it works and what it contains at what time can be found in the *Store* chapter. Field: Note """"""""""" Note for the developer in case they want to describe the form in more detail. Field: Table """""""""""" The table the form points to. The records can directly access the columns of this table without having to be selected first. Pill: Form Element ^^^^^^^^^^^^^^^^^^ This is where the elements that make up the form are created. Pill: Layout ^^^^^^^^^^^^ Field: Show Button """""""""""""""""" This sets which buttons in the top right of the form can be used by the user. The *New* button is important here, because otherwise the user could create new entries unintentionally. Field: Label Align """""""""""""""""" Choose to which side the content should align within its field. This field is defined by the columns. .. Parameter """"""""" Columns """"""" This defines the width for the three fields that a FormElement has. Access """""" Multi """"" Pill: Table Definition ^^^^^^^^^^^^^^^^^^^^^^ This displays all columns from the table that was selected under *Basic → Table*. FormEditor: FormElement ----------------------- Pill: Basic ^^^^^^^^^^^ Field: Name """"""""""" * The name points directly to the table columns selected in the form. * Under *Table Definition* you can see the table with the column name/type/size/default. * Non-primary column names: * If a column from another table should be displayed, the name can be freely chosen as long as it does not match a column from the primary table (some more action is necessary to use read/save values). * Best practice is to start `non-primary columns` with *my*. Choose a self-explanatory name to make it's use obvious. Field: Label """""""""""" This is the name you give the FormElement to distinguish it from others – especially if there are two or more pointing to the same column. Field: Mode """"""""""" The mode determines the behaviour. The names are self-explanatory. Field: Mode SQL """"""""""""""" Here the mode can be dynamically adjusted to the FormElement based on logic. .. code-block:: text {{SELECT IF( '{{roleUser:UE}}' = 'course_role_admin', 'required', 'readonly' ) }} In this example, it checks which role the user has and decides based on this role whether they may edit the content or not. The role comes here from the User Store. Field: Class """""""""""" Categorize FormElements: * *Native*: fields the user interacts directly with. * *Actions*: consist of logic executed in the background, e.g. validation so the same value cannot be selected twice. Be aware of `FormElement.parameter.sql....` in native FormElements - they behave very similar to `FE.class=action`. * *Container*: are different sections in which you can group FormElements. They are explained in more detail in the tutorial. Link to documentation :ref:`class-container` Field: Type """"""""""" Link to documentation :ref:`class-native` Field: Value ^^^^^^^^^^^^ The *Value* field is meant for outputting individual values – whether selected or hardcoded. This value can also be used in *sql1*, if it is e.g. an ID. .. code-block:: text SELECT id AS _id, p.firstName FROM Person The *_id* then takes the value that is in the *Value* field. Field: sql1 ^^^^^^^^^^^ This field is used for SQL queries and is mainly intended for when more than one value is returned. That is also why the *!* is in front of the *SELECT* – it means the values will be returned as an array and not as a string. Field: Parameter ^^^^^^^^^^^^^^^^ In this field, properties are defined that every FormElement can have. For this you can look in the documentation of the individual *Native* classes. There the available parameters are always described. Concept: Store -------------- Link to documentation :ref:`store` STORE_RECORD ^^^^^^^^^^^^ The Record Store is filled automically during form load with the given record. However, it needs an ID to know which person it should load. That’s why it is filled when editing a form, because the edit button passes the ID of e.g. a person. When leaving the page or reloading, the Record Store is overwritten with new data. STORE_FORM ^^^^^^^^^^ This store is filled with the inputs a user makes in a form in the moment when the user presses 'save'. It is often used together with the Record Store. The Form Store is overwritten with latest user data on each 'save'. STORE_USER ^^^^^^^^^^ This store is persistant during the user (=browser) session (limit: timeout or browser restart). Data remain in the store as long as the user is logged in and are overwritten when the user logs out. Miscellaneous ------------- Subrecord ^^^^^^^^^ If a FormElement *Subrecord* is created in a form, it establishes the connection between e.g. a specific person and the roles. In the *Parameter* field, the `detail`-form (=sub form) is referenced. E.g. all roles assigned to a person. In the detail form you then create a FormElement *Select*, where all roles, that a person can be assigned to, are offered to choose. This ensures that in the first step a specific person can be chosen and in the second step all roles are displayed. Since we usually look at a linking table (Glue) in the subrecord, but must come from the Person table, we must specify this connection in the *Parameter* field. .. code-block:: text Parameter form = subSettingRole detail = id:xId *subSettingRole* is my second form with the *Select*. The ID comes from the Person table and the xId belongs to the Glue table. If I want to assign a role to a person, this happens by creating a Glue record. That’s why the first form selects *Glue.id AS _id* and the role *Grp.name AS Role*. Important: The first form only selects – it does not write. The second form points to the Glue table, because this form writes an entry into the Glue table. The FormElement points to *Glue.grId*. Thus, a new Glue entry can be created: * ID auto generated, * xId from the parameter, * and the grId is now selected. So in the *sql1* field the *gr.id* (linked to the grId) and the role *gr.name* are selected. Action ------ Link to documentation :ref:`class-action` beforeSave Validator ^^^^^^^^^^^^^^^^^^^^ In the FormElement under *Basic*, *action* is selected and the type *beforeSave*. Our code goes into the *Parameter* field and starts with *sqlValidate = ...*. Then follows a normal SQL query where we fetch one value from the DB and one from the Form & Record Store. It is also important here to specify the correct sanitize class (e.g. `alnumx`, `digit`, etc.). The *alert* is a pop-up with selectable content. .. code-block:: text sqlValidate = {{!SELECT p.account FROM Person p WHERE p.account = '{{account:FRE:alnumx}}' }} expectRecords = 0 alert = Account name already exist Further information: :ref:`class-action`. Action in Typo3 Report ^^^^^^^^^^^^^^^^^^^^^^ In my case, I created a button that opens a pop-up with an "Execute Yes/No" question. If you click *Yes*, the action is executed. The button looked like this: .. code-block:: sql CONCAT('p:{{pageSlug:T}}?action=dropEnroll&enrollId=', e.id ,'|b|s|D|q:Do you realy want to drop the course?') AS '_link|studentCancelEnroll' Action now contains `dropEnroll` and enrollId contains the *e.id* from the respective table row. Since the button stores these in the SIP Store, we first pass them into the Record Store. .. code-block:: text { sql = SELECT '{{enrollId:SE}}' AS _enrollId ,'{{action:SE::w}}' AS _action } Through *AS*, both are loaded from the SIP into the Record Store. Additionally, we wipe the SIP Store with the small *w* to avoid errors by using browser back/forth. Afterwards we can make changes with normal SQL statements that the button should perform. In my example, that would be a status change. .. code-block:: text parent { sql = SELECT '' FROM DUAL WHERE '{{action:RE}}' = 'dropEnroll' child { sql = UPDATE Enrollment SET statusId = (SELECT grs.id FROM (Grp grs) WHERE grs.reference = 'course_status_dropped' ) WHERE '{{enrollId:R0}}' = id } } Since I query the *action* variable in the Dual table, I can execute multiple *child* blocks if desired. It can also be done – as here – in a single SQL statement: .. code-block:: text { sql = UPDATE Enrollment SET statusId = (SELECT gr.id FROM Grp gr WHERE gr.reference = 'course_status_dropped') WHERE '{{enrollId:R0}}' = id AND '{{action:RE}}' = 'dropEnroll' } Here the whole thing was solved via a subselect. .. _nativeClassExtra: Native Class: Extra ^^^^^^^^^^^^^^^^^^^ I used this class to set IDs in a table. In a registration table, the user had filled out very little, the rest were all IDs from other tables. Here I used the native class *extra* to connect these IDs via *SELECT*. It is a class that runs in the background together with the form. Since I set an ID, I used the *Value* field to select it. Once I also just used an ID from a store. QFQ --- In this tutorial you will learn what QFQ is and how to use its many features. For each section there is an exercise to practice. What is QFQ? ^^^^^^^^^^^^ QFQ (Quick Form Query) is an extension for the Typo3 CMS. Its backend is written in PHP and its frontend in Javascript. First and foremost it allows the developer to connect their website to a custom database and display dynamic content. But displaying data is not everything QFQ is good at. QFQ stands for 'Quick Form Query' and at its core is an easy to use but powerful Form Editor. The Form Editor allows the developer to build custom forms that allow for user input, which can then easily be stored in the connected database. So QFQ can display dynamic content and process user inputs. But this is not everything! QFQ comes with many more features, such as: * Sending emails (optional with attachment, maybe dynamically generated PDFs). * Execute a script (shell script, PHP, ...) on the webserver and then process its output. * Execute SQL * Access to Typo3 Variables such as the currently logged in user, his/her user groups, etc. * and much more! Report ^^^^^^ When we talk about a Report in QFQ, what we really mean is a Typo3 page content of the type 'QFQ Element'. In the most common and simple case, we just use Reports to take data from our database and show it to the user. However, reports are very powerful and can be used for anything you want on your website. The one thing reports are not meant for are forms to handle user input. Technically you could build your own form on a report, but QFQ comes with its own Form Editor feature and we highly recommend to use that instead. Basic Report Syntax """"""""""""""""""" Let's start at the beginning. The first thing we want to do is to take data from our database and display it on our website. For this we are going to use - you guessed it - a report! Before we can start, we have to create a Typo3 page and add a page content of type 'QFQ Element' to it. When we edit this page content, we are greeted with a large empty box where we are supposed to write our QFQ code. QFQ code works by directly executing SQL queries, and using the result of these queries to render plain HTML. To do this, QFQ code has its own syntax. It's really easy - start by looking at the following code: .. code-block:: sql { sql = SELECT title FROM Book } { sql = SELECT lastName FROM Person } The first thing to notice is that this code is divided into two blocks. Curly braces are used to define what's inside these blocks. You will also see ``10 {}`` in older code, which stands for the order of execution, where ``10 {}`` executes before ``20 {}`` regardless of position. This is deprecated and no longer best practice and has been replaced by top-down code execution. Inside each block you see an SQL statement. SQL statements are the core of each block, and since the whole report is made out of blocks, the SQL statements are the most important part of a QFQ report. The SQL is preceded by ``sql =``, which is necessary because a block can consist of more than just an sql statement. But more on that later. What's the result of this simple report? It is something like: *The HobbitDonQuixoteTheDavinciCodeTolkiende CervantesBrown*. First we selected the titles of all books - and they are automatically rendered as the output of the QFQ code. The same goes for the lastnames of all People. The information in this form is of little use to a visitor of our website. What we need is formatting! So let's add something to our first block: .. code-block:: sql { sql = SELECT title FROM Book head =
    tail =
rbeg =
  • rend =
    • } The SQL statement is still the same - but we have introduced 4 new keywords (you'll find all keywords under :ref:`qfq_keywords`): * The **head** will be rendered before any of the sql. However, if the SQL returns nothing (0 rows), then no head will be rendered. If you want to render the head even if the SQL returns nothing, use **shead** instead ('static head'). * The **tail** will be rendered after all of the sql. However, if the SQL returns nothing (0 rows), then no tail will be rendered. If you want to render the tail even if the SQL returns nothing, use **stail** instead ('static tail'). * **rbeg** stands for 'row begin'. rbeg will be rendered before every row of the sql result. * **rend** stands for 'row end'. rend will be rendered after every row of the sql result. And just like that, the output of our first block is now formatted HTML: .. code-block:: html
    • The Hobbit
    • Don Quixote
    • The Da Vinci Code
    Let's go a bit further for with the second block. We'll change it to: .. code-block:: sql { sql = SELECT firstName, lastName FROM Person head =
    {{&newPersonBtn:RE::-}}
    tail =
    Firstname Lastname
    rbeg = rend = fbeg = fend = } First of all, we are no longer using an unordered list to display our data here. We are now rendering an HTML table. We can use the **head** to create column headings too. Furthermore, in our SQL query we now select firstname and lastname. The result of this SQL query is a table with two columns. This means there are two fields per row now - a chance to build in some additional formatting: * **fbeg** can be used to render something before each field (but after rbeg). * **fend** can be used to render something after each field (but before rend). And just like that we have changed the output of the second block to: .. code-block:: html
    Firstname Lastname
    J. R. R. Tolkien
    Miguel de Cervantes
    Dan Brown
    Note: There are even more QFQ body keywords such as **rsep** and **renr**! Please check :ref:`qfq_keywords`. Exercise 1 ^^^^^^^^^^ * Create a new page 'Person' and add a QFQ content Element with the above QFQ code to it. (The code to create a table that contains all people) Exercise 2 ^^^^^^^^^^ * Add another column to that table which contains the email-address of the person Exercise 3 ^^^^^^^^^^ * Create another page, call it 'Books' and add a QFQ Element to it. * Display a table with all books on this page. * Add at least two columns - one for the title and one for the name of the author. Nested Blocks and the Record Store """""""""""""""""""""""""""""""""" There are a few things going on in the following example: .. code-block:: sql parent { sql = SELECT '

    ', firstName, lastName, '

    ', id AS _pId FROM Person child { sql = SELECT title FROM Book WHERE authorId = {{pId:R}} head =
      tail =
    rbeg =
  • rend =
    • } } We can see a **parent** block - `parent { }` - and a **child** block - `child { }`. The child block will be executed for each row that is returned by the parent block's SQL statement. If we compare this with conventional procedural programming, we could say that the nested blocks create a **for each loop**. But there is more going on in the example: * You might have noticed that the 'h1' Element is directly part of the SQL SELECT statement. The same could have been achieved by using the ``rbeg`` and ``rend`` attributes of the block. In fact, we advocate for the use of these attributes. Nevertheless, the possibility of including HTML directly in the SQL statements can be really useful sometimes! * In the parent block, we selected ``id AS _pId``. In plain old SQL, writing ``id AS pId`` (underscore is missing) would be used to set the column heading to 'pId'. However, QFQ is doing more. By giving the id-field an alias (pId), **QFQ automatically creates a variable named pId**. * This variable is put into the **Record Store**. There are several places where QFQ can store variables and the record store is arguably the most used. Other 'Stores' - places to store variables - will be discussed in later chapters. * By putting the underscore (AS _pId) in front of the alias, we can prevent QFQ from rendering this specific field. We don't want random id-numbers appearing on our page, we just want to remember the id because we need it in the nested block. * In the nested block's SQL statement we access the id we have stored before. We do this by typing ``{{pId:R0}}``. The double curly brackets are just the way to tell QFQ that we would like to access a variable. 'pId' is obviously the name of the variable we want. This is followed by ':R', meaning that we fetch it from the Record Store. The zero at the end means that we replace this term with 0 if there is no variable 'pId'. The result of our QFQ code might look like this: .. code-block:: html

    J. R. R. Tolkien

    • The Hobbit
    • The Lord of the Rings
      • {id:

    Miguel de Cervantes

    • Don Quixote

    Dan Brown

    • The Da Vinci Code
    Exercises ^^^^^^^^^ 1. Create a new page and call it 'Genres'. Add a QFQ Element to it. Write QFQ code that generates a heading (``@

    @``) for each genre. Hint: We are storing genres in the table ``Grp``, but not all records in this table represent a genre. Only the records with ``@grId = [id of the Grp record with reference = 'library_genre']@`` are genres. Below each genre-heading, display a list (``@
      @``) of all Books in that genre. Use a nested QFQ block and make use of the record store! Client-Store, Zero and Empty ---------------------------- In the previous chapter it was briefly mentioned that the Record Store is not the only place QFQ can get data from. Introducing: The **Client-Store**! First we should explain how data is put into the client store. This is simple: * Existing HTTP POST variables are automatically put into the Client-Store. * This means that user data, which is submitted to the page via form, is in the Client-Store. * All parameters of the user's requested URL are in the Client-Store. * In this case: https://www.mypage.com?id=100&customparam=abc we have 2 variables in the Client-Store: ``id`` and ``customparam``. To access values from the Client-Store in a QFQ report, we can use: .. code-block:: text {{variableName:C}} However, you are more likely to be using it in one of these two ways: .. code-block:: text {{integerName:C0}} {{stringName:CE:alnumx}} The first example is very similar to the one shown at the beginning. The difference is that we write: .. code-block:: text {{integerName:C0}} instead of ``{{integerName:C}}`` after the variable name. This should be used if you are sure that the variable contains an integer. The ``0`` at the end simply means that the whole expression will be replaced by ``0`` instead of the variable's value, if the variable does not exist. The second example, .. code-block:: text {{stringName:CE:alnumx}} should be used if the variable contains a string. Here's what's important: * Instead of ``0``, we append ``E`` after the ``C`` that stands for "Client-Store". ``E`` means "Empty" – the whole expression is omitted if the variable does not exist. * There is another colon after ``CE``, followed by ``alnumx``. This is the sanitize class. With ``alnumx`` QFQ outputs only numbers, Latin letters and a few special characters. If the value does not match, an error occurs. See the `official documentation `_. * Any variable retrieved from the client store uses ``digit`` as the default sanitize class. Therefore we must set a class explicitly for strings, but not for integers. * If the variable is a string and you use it inside an SQL statement, add quotes around it (e.g. ``'{{stringVariable:CE:alnumx}}'``). Otherwise the resulting SQL is invalid. Exercise 1 ^^^^^^^^^^ 1. Create a page and call it ``Book detail`` (add a QFQ content element). 2. If the URL contains a parameter ``bookId``, then title, author and number of pages for the book with this id should be displayed. 3. If there is no parameter ``bookId`` in the URL, then display the text ``No book selected``. Example URL: http://webwork20:````/home/bookdetail 4. If a ``bookId`` was stated in the URL, but there is no book with this id in the DB, display ``Book not found``. Example URL: http://webwork20:````/home/bookdetail?bookId=111 Tips """" The value (most likely HTML code) that is assigned to ``althead`` is displayed when the SQL in the same block returns nothing: .. code-block:: sql { sql = ... althead =

      Alt Element

      } You can use the ``DUAL`` table and ``''`` to use ``WHERE`` in SQL. In SQL, ``DUAL`` is a special dummy table used to select a value or perform a function when no actual table data is needed: .. code-block:: sql { sql = SELECT '' FROM DUAL WHERE althead =

      Alt Element

      } In this example we now ``SELECT`` something if the condition is **TRUE** and if **FALSE** we return the ``

      Alt Element

      ``. 5. On the page ``Book``, add a link to the Book Detail page for each book (add a new column or build a link directly around the book title). Links and SIP ------------- We have already seen two *Stores*. We used the Record Store within a report as our "short term memory", saving an id to use it in another SQL query. We have also used the Client-Store to get the values of HTTP GET or POST variables. There is another very important Store, the **SIP-Store**. Here's what's important: * The **S** in SIP stands for *session*. If you find a parameter ``s`` in your URL, then its value is the session id. A session id in the URL might look like this: ``s=60fed01b63542`` * The server will have one or more variables associated with a session, which is identified by the session id. * The SIP adds a great deal of security to our website, because SIP variables never leave the server! * Our website can be opened with an existing session id (passed via URL). * The server then decides how to display the requested page depending on the variables associated with the session. This can be done within a QFQ report! * Even if someone else knows our session id, they won't be able to access the session! This is because every session is device-specific and access will be denied if tried from another device. But how do we create a session in the first place? Take a look at the following QFQ code: .. code-block:: sql { sql = SELECT 'p:/home?a=topSecret&b=3|s|t:Click Me' AS _link } There are quite a few things going on in this one line. * The whole string is selected with ``AS _link``. With your current knowledge you might expect that the string will be put in the Record Store under the name ``link``. However, this is not the case. QFQ has *special column names*, which are keywords that can completely change how a field is selected. Depending on the keyword, additional functionality might be triggered! ``link`` is one of those special column names and it is used to build links of various types. The kind of link that is built depends entirely on the string that is used. The string needs to follow a certain logic – see the `official QFQ documentation `_. * In our example, the string starts with ``p:/home`` – meaning that the link will point to the page ``/home``. ``/home`` is a so-called *page slug*, which is a unique identifier for the page and part of the URL. The page slug of any given Typo3 page can be viewed and edited in the Typo3 backend. If the link should not point to another page, but instead to the page the user is already on, you can use ``p:{{pageSlug:T}}`` – this variable is automatically replaced by the slug of the current page from the Typo3 Store. * After the page, the string is split by ``?``. This means that one or more parameters will follow. Additional parameters are separated by ``&``. In our example, we assign the value ``topSecret`` to a variable we call ``a`` and we assign ``3`` to the variable ``b``. * After all variables are set, there is another special character which further splits the string: the vertical bar (``|``), also called "pipe". In our example, we put an ``s`` behind the pipe, declaring that we want our variable to be SIP-encoded and not directly accessible in the URL. After another pipe, we then set the link-text to *Click Me*, using the character ``t``. The sequence does not matter in most cases: ``|s|t:Click Me`` = ``|t:Click Me|s``. QFQ will now create a session id and associate the variables ``a`` and ``b`` with it. The actual output will be an HTML link: .. code-block:: html Click Me As you can see, we now have a link which is using a session id – and this session is only valid for our own device! Somewhere on the server, there is a note of this session – and also of the variables we assigned to it earlier! The server knows that the session with id ``badcaffee1234`` has a variable called ``a`` with the value ``topSecret`` and a variable ``b`` with the value ``3``. We pointed our link to the Typo3 page ``/home``, therefore we can make use of our SIP variable on this page. Let's add the following QFQ element there: .. code-block:: sql { sql = SELECT IF('{{a:SE}}' != '', 'The variable a is {{a:SE}} and b={{b:S0}}', 'There is no session variable.') } If we open the page ``/home`` normally, we would see the text ``There is no session variable.`` However, if we open the page via the link we created earlier, then we will get: ``The variable a is topSecret and b=3`` pageSlug example ^^^^^^^^^^^^^^^^ .. code-block:: text URL Before: https://project.math.uzh.ch/projects/praktikum sql = SELECT 'p:{{pageSlug:T}}/tutorial?r=0|s|t:Click Me' AS _link URL After: https://project.math.uzh.ch/projects/praktikum/tutorial?s=badcaffee1234 How SIP works ^^^^^^^^^^^^^ .. image:: ./Images/sip-concept.drawio.png :alt: SIP process diagram :width: 1000px Exercise 1 ^^^^^^^^^^ 1. Add a page ``Person Detail``. Then modify the page ``Person`` so that there is a SIP-link for each person to the page Person Detail. The SIP should contain the person's id. 2. Edit the page ``Person Detail`` so that it checks for the person id in the SIP. If there is no id or the person with the id is not in the database, display ``Person not found``. Otherwise display the person's firstname, lastname and email address. Integrating Forms on our Report ------------------------------- Here it all comes together. Let's make a page on which we can see a table that lists all books. We also want an *Edit* button for each book, as well as a *New Book* button. Our goal is to link these buttons to forms we will create later on! We are using the following QFQ code. This time the code should explain itself, because we have added comments to it. Comments in the right place can be invaluable. In QFQ, you can start a comment with the ``#`` symbol. Inside SQL queries you can also use block comments. **If there is a form in the SIP, it will be displayed!** .. code-block:: sql form = {{form:SE}} # ?form=Book => clicking on the link opens the Book form # &r=0 => the Book form is opened with record id = 0. # This means a new book will be created instead of # opening the form with data of an existing book # |N => (NEW) Display a button with a plus-symbol instead of # an unformatted link. # If you want your link formatted as a button, but still # choose your own text, you can use |b|t:Buttontext # # AS '*link|newBookButton|*hide' => link is a special column name and # even with the underscore in front # of it, it will be rendered. # Unless we add '_hide' (another # special column name)! # To have a reference to the created # link, we give it a custom name # (newBookButton). # Because we are using more than one # name / special column name, # we have to separate them with pipes # (|) and put single ticks around the # whole expression. { sql = SELECT 'p:{{pageSlug:T}}?form=Book&r=0|s|N' AS '*link|newBookButton|*hide' } **Render table with all books. We insert the ``newBookButton`` in the table header** .. code-block:: sql # &r=... => we set the record id to the book's id, so # that our form will load the data of that # specific book when opened # {{&newBookButton:RE::-}} => The '&' means, that we want the computed # value of the link, and not the original string! # If we don't use the '&', we'll simply get # the string 'p:/books&form=Book&r=0|s|b|N' # => Notice the RE::-? After the store (RE) # we could specify a 'Sanitize Class', which # we don't need. # This is why there is nothing between the # two colons. # After the next colon we can specify the # escape/action class. The dash (-) means # 'no escaping'. # This is needed here, because # '{{&newBookButton:RE}}' contains an # HTML button and some of the characters are # by default escaped by QFQ, # resulting in invalid HTML if we don't # tell QFQ not to escape it. { sql = SELECT CONCAT('p:{{pageSlug:T}}?form=Book&r=', b.id, '|s|b|E') AS _link, b.title, CONCAT(p.firstName, ' ', p.lastName) FROM Book AS b, Person AS p WHERE p.id = b.authorId shead = stail =
      {{&newBookButton:RE::-}} Title Author
      rbeg = rend = fbeg = fend = } Finished page Code ------------------ .. code-block:: sql form = {{form:SE}} { sql = SELECT 'p:{{pageSlug:T}}?form=Book&r=0|s|N' AS '*link|newBookButton|*hide' } { sql = SELECT CONCAT('p:{{pageSlug:T}}?form=Book&r=', b.id, '|s|b|E') AS _link, b.title, CONCAT(p.firstName, ' ', p.lastName) FROM Book AS b, Person AS p WHERE p.id = b.authorId shead = stail =
      {{&newBookButton:RE::-}} Title Author
      rbeg = rend = fbeg = fend = } Here's what we see when we open our new book-page! .. image:: ./Images/book_page.png :alt: Book page with empty table Since there are no books in our database, our table only has one row – the header! By the way, if we hadn't used ``shead`` and ``stail`` (and used ``head`` / ``tail`` instead) we wouldn't see anything at all. The ``+`` button doesn't do anything yet, because we have not created the Book form. We will do that in the next chapter. Exercises ^^^^^^^^^ 1. Modify your existing Book page so that there is a button to create new books and a button for each book to edit (just like in the example above). You don't need the Form yet, assume its name is ``Book``. 2. Do the same for your Person page. Assume the Form name is ``Person``. Forms ----- Let's talk forms. Handling complex user input within a report would be entirely possible, but QFQ has another special solution for that – the **Form Editor**. The Form Editor is a Form that allows you to create forms – it's *Formception*! Before we can manage our forms, we need to set up the page on which we do that. You can simply create a Typo3 page, call it ``Forms`` and add a *QFQ Element* to it with the content: .. code-block:: text file=_formEditor In this case, we are loading the integrated QFQ code for the form management page from a file, so we don't have to code a custom solution for that. You should also set the page slug for this Typo3 page to ``/form``. You can do this by editing the *URL Segment* in the *General* tab when editing the Typo3 page. If you open your ``Forms`` page on the frontend, it should look like this: .. image:: ./Images/form-mgmt.png :alt: Form management page For now you can ignore all the *Json* buttons – we will be working with the *normal* create/edit feature. .. note:: Make sure your average user does **not** have permission to access the Forms page before your website goes live! Creating a Form --------------- Note that we are still using our Library-Database. The definitions of tables and columns are important when creating a form, because we will use that form to save data to these tables! First we want to create a form on which we can edit Books for our library! We create a new form by clicking the ``+`` button on the top left side of our Forms page. We are now in the Form Editor! There are several tabs (we also call them *pills*) that will appear **after** we save, but for now we just fill out the *Basic* tab and then hit the save button in the top right: .. image:: ./Images/form_1.png :alt: Form Editor basic tab Form fields to fill in: 1. **Name** – Choose a name for your form, you will need it later to refer to the form. Since we will use the form to create books we will simply call it ``Book``. 2. **Title** – The title is displayed at the top of the form (where the arrow is pointing in the screenshot above). 3. **Note** – You can take notes here – they will not be displayed to the user. 4. **Table** – Choose the table from your database for which you want to create/edit records. We choose the table ``Book``, because we want to create and edit books with our form! Think of this as the *primary* table of the form. It is still possible to show data from other tables, but anything from the chosen table will be much easier. Exercise 1 ^^^^^^^^^^ 1. Set up the Forms page if you haven't already. 2. Create two forms: one called ``Person`` (table: Person) and one called ``Book`` (table: Book). FormElements ------------- Now that our form is created we can start adding FormElements to it. FormElements fall in one of three classes: 1. **Native FormElements** – the basic building blocks of your forms. They are either input elements such as textfields, checkboxes and dropdowns or elements to display data, like the *note* element. 2. **Container FormElements** – elements used to group together other form elements. You have already seen the tabs/pills in the Form Editor itself – these tabs are container FormElements. 3. **Action FormElements** – elements which are not displayed on the form, but they contain a trigger event and an action that is executed when triggered. For our Book form we will stick to *Native FormElements*. To add a FormElement, we switch to the *Formelements* tab and click the ``+`` button. Another form opens, which allows us to define our first FormElement: .. image:: ./Images/form_element.png :alt: Add FormElement FormElement fields to fill in: 1. **Name** – In many cases, the name of your FormElement has to be exactly the name of a column on the primary table you chose for your form. In this example we are creating a textbox for book titles. Since we chose ``Book`` as our primary table and ``title`` is the column on that table, we simply choose ``title``. If your FormElement does not represent a specific column on the chosen primary table, we suggest following a naming convention. We usually start these names with ``my`` – e.g. ``myTitle``. 2. **Label** – The label that will be displayed next to our textbox. ``Title`` makes total sense here, because the user should type a title. 3. **Class** and **Type** – You can see the aforementioned FormElement class ``native`` here. Because we want a simple textbox, we can leave both class and type at their default values. Now we click the save icon on the top right side, and then the close icon right next to it. We are now back in our other form and we can see that our Form Element for the title was added to the list! .. image:: ./Images/form_element_2.png :alt: Added FormElement This form is still very basic, but let's see if it works! To do that we can open the Books page we have created in the previous chapter and click the ``+`` button in the top left. Our little form opens up! It looks like this: .. image:: ./Images/form_2.png :alt: Basic book form Exercise 1 ^^^^^^^^^^ 1. Recreate the Book Form with the ``title`` FormElement. 2. Create a new form: * Name the form ``Person`` and set its table to ``Person``. * Add two text inputs, one for ``firstName`` and one for ``lastName``. SELECT Statements and Variables inside Forms -------------------------------------------- Our form does not exactly behave the way we want: When we enter a title for a book and hit save, then we get a message that our book was saved. The new book also appears in our database, meaning it was really saved. But the form's title still says *Create New Book*, even though the form contains Exercise 1 ^^^^^^^^^^ 1. Modify the title of your Book form so that it displays either *Create* or *Edit* (as seen in this chapter). 2. Do the same for the Person form. Dropdown and sql1 ----------------- We want to add a dropdown to select the author of the book from a list of names. The HTML tag for a dropdown is ``select`` and QFQ also calls them that. Let's add another FormElement to the Book form and fill out the *Basic* tab: .. image:: ./Images/form_element_3.png :alt: Dropdown FormElement basic tab So far we told QFQ to render a dropdown, but not what values should be available to select. We can configure that in the *Value* tab: .. image:: ./Images/form_element_4.png :alt: Dropdown value tab The above screenshot shows a simple way of filling our dropdown with values directly from the database: * We use the *sql1* text area. * Since we are writing an SQL statement, we have to put everything in double curly brackets ``{{ ... }}``. * This specific way of filling the dropdown requires the data to be in an array format. - We achieve this by putting an exclamation mark at the beginning: ``{{! ... }}``. * We are selecting the person's id as ``id`` and the person's full name as ``label``. - The **id** is the important part that is needed for correct referencing of the selected person. - The **label** is what will be displayed – it's the text of the option. - This is the explicit way of doing it. If you don't write ``AS id`` and ``AS label``, then QFQ will implicitly take the first column as the id and the second as the label. Id as Id example for a list of people ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ * For every person in the table ``People`` a new dataset is created: ``{id:'name lastName'}``. * These datasets are then used to fill the select element so that when selected we know to which ID the name and last name belong. Exercise 1 """""""""" 1. Add the author dropdown as shown in this chapter to your Book form. 2. FormElements like the dropdown (``select``) have more customization options! Check the official documentation to see what's possible: http://docs.qfq.io/master/Form.html#type-select Use a parameter (find it in the documentation) on the Author dropdown to add an "empty" option. 3. Create a new form, call it ``Rental``. This form should contain: * A dropdown to select the book that was rented * A dropdown to select who rented the book * A date FormElement for the date it was rented * A date FormElement for the due date for returning the book * A datetime FormElement to store when it was actually returned Check & Order ------------- Now we have two FormElements. They are displayed in the same order as we have added them. But what if we want to display the author dropdown first and the title textfield second? In the list of FormElements you can simply **drag and drop FormElements to change their order**. This automatically adjusts the *Order* field of the FormElements. We can also set a value for *Order* manually in the *Check & Order* tab of the FormElement. .. image:: ./Images/check_and_order.png :alt: Check & Order tab Apart from ordering, this tab is all about data validation. With the field **Check Type** we can define what kind of input is allowed for the FormElement and what's not. For example you might have an input text field for an email. Simply select the Check Type ``email`` and you are done! QFQ will stop the user from submitting the form, if the user hasn't entered a valid email. For our Book form, we are adding a FormElement of type ``text`` where the user can enter the number of pages the book has. What we need here is Check Type ``digit`` to allow integers only! But actually we don't have to bother, because the Check Type is on ``auto`` by default and QFQ noticed that our ``nrOfPages`` are stored as an integer in the database. That's why auto mode chooses ``digit`` for us and QFQ even adjusts the ``text`` input element to optimize it for numbers: .. image:: ./Images/check_digit.png :alt: Digit check type example So you probably won't have to come to the *Check & Order* tab very often, because QFQ is *auto-checking* and the ordering is easier to do by dragging and dropping. But in cases like email it is very useful. In a more complex scenario you can choose Check Type ``pattern`` and write your own regular expression. Exercise 1 ^^^^^^^^^^ 1. Add the *Number of Pages* FormElement you have seen in this chapter to your Book form. 2. Add a FormElement to the Person form where the person's email address can be entered. The field should only accept valid email addresses. Layout ------ QFQ is delivered together with Bootstrap! That's why we can use Bootstrap classes anywhere on our website and also why QFQ forms already have that Bootstrap look. By default, our FormElements are placed inside a *row* which takes up the full width of the form. Furthermore, each FormElement is divided into three areas: * **Label** – Displays the text or HTML from the *Label* field in the Basic tab of the Form Editor. * **Input** – Displays the chosen input element from the *Type* field in the Basic tab of the Form Editor (e.g. a textbox, dropdown, etc.). * **Note** – Displays the text or HTML from the *Note* field in the *Layout* tab of the Form Editor. (You can see this field on the next screenshot) Each of these areas has a width assigned to them. The default is 3 units for the label, 6 for the input and 3 for the note – making a total of 12! As you might know, a Bootstrap (BS) row is 12 units wide. You can change the individual widths of the 3 areas in the *Layout* tab of the Form Editor. .. image:: ./Images/form_layout.png :alt: Form layout tab Suppose you wanted to display the label above the input. To achieve this you can simply give the label a width of 12 units – making a full row and forcing the following elements below the label. Specifically, you would type ``12`` into the field *BS Label Columns*. You can also use the full Bootstrap classes with screen size conditions like ``col-lg-6 col-md-12``. In the screenshot above you see the whole Layout tab, so let's go through some simple but useful options we have there: * **Label / Input / Note** – with these checkboxes you have full control over the HTML tags that will be rendered for the three areas. You could even uncheck ``/row`` for a FormElement and uncheck ``row`` for the following FormElement. In the resulting HTML both FormElements will share the same row. This then allows you to place them next to each other! * **Maxlength** – The maximum length of characters which the user may enter. This is only relevant for text inputs. * **Note** – Text or HTML to display in the *Note* area, which is to the right of the input by default. * **Tooltip** – A text to be displayed when the user hovers over the Form Element. * **Placeholder** – The placeholder text will be displayed if the user has not typed anything yet. This is only relevant for text inputs. Keep in mind that you can use ``{{qfqVariables:RE}}`` and ``{{SELECT 'Statements'}}`` in these fields within the Form Editor. Especially in the *Note* field this can be very useful. Exercise 1 ^^^^^^^^^^ 1. The *Number of Pages* input field looks too wide. Make it smaller! 2. Modify the firstname and lastname FormElements on the Person form so that they only take one line and have placeholders. It should look like this: .. image:: ./Images/form_element_layout.png :alt: Person form layout with placeholders 3. **Also acceptable**: .. image:: ./Images/form_element_layout_bug.png :alt: Alternative layout variant Value ----- Let's talk about the *Value* tab in the FormElement Editor. In total there are three fields in the *Value* tab: 1. **value** This field can be used to specify the default value of the FormElement when the form is opened. If a form is opened with an existing record (``r > 0``) then the value of the FormElements is loaded from the corresponding column (as configured in the FormElement's name) in the database. This means that if we want a default value, we will often have to write a statement like this: .. code-block:: sql {{SELECT IF( {{id:R0}} = 0, 'New Book', '{{title:RE}}' )}} This expression will fill a text field with *New Book* if a new book is being created, but keep the existing book's title if the form is opened to edit an existing record. 2. **sql1** If a FormElement requires more data, this field can often be used to define that data in the form of an SQL ``SELECT`` statement. We have already used this to generate all the options when we were creating a dropdown. 3. **parameter** FormElements can often be configured beyond their default settings with this field. The parameters that can be used depend on the type of the FormElement. You can look up valid parameters for a specific FormElement in the official documentation. Exercises ^^^^^^^^^ 1. Set the default value of the title field on the Book form to *New Book*. 2. Set the default value of the *number of pages* field on the Book form to ``100``. Subrecord --------- Let's make use of the *Value* tab now and add a subrecord! What's a subrecord? A subrecord, by our own definition, is a table within a form that contains multiple elements *belonging* to the primary record displayed in the form. These elements often have their own forms where they can be edited. In our subrecord we can display links to those forms to quickly edit the *sub-elements*. We would like to add a subrecord to our Book form that lists every rental of the book (the person who has rented the book, start date, due date to return the book and the date when the book was returned). The following screenshot shows what our finished subrecord will look like: .. image:: ./Images/subrecord.png :alt: Subrecord with rentals First we create a few records in our database in the table *Rental* – otherwise our list of rentals would be empty. On our Book form we then create a new FormElement of the class *native* and the type *subrecord*. Look at the screenshots below to see how we configure the subrecord Form Element: .. image:: ./Images/subrecord_settings_1.png .. image:: ./Images/subrecord_settings_2.png .. admonition:: sql1 Code .. code-block:: sql SELECT r.id AS '_id', CONCAT(p.firstName, ' ', p.lastName) AS 'Person', DATE_FORMAT(r.start, '%d.%m.%Y') AS 'From', DATE_FORMAT(r.end, '%d.%m.%Y') AS 'Due', DATE_FORMAT(r.returned, '%d.%m.%Y') AS 'Returned' FROM Rental AS r, Person AS p WHERE r.bookId = {{id:R0}} AND r.personId = p.id Notes: * In the first screenshot, notice that there is an additional option at the bottom called *Subrecord Option*. By checking all the boxes (*new*, *edit*, *delete*) we will automatically get buttons to create, edit and delete our rental records. * Notice how we chose ``myRental`` as the name of the FormElement. - The rentals are not stored in the same record as the book, so we cannot hoose a column name from our primary table (*Book*). - We prepend *my* to the name as a best practice, so that any developer can quickly see that the data in this FormElement is not from the primary table. * In the second screenshot, you can see that we can use the **sql1** field to specify what data will be displayed in the subrecord. - By choosing custom column names we can set the column headers of the subrecord. - We need to include the id in our SELECT statement so that QFQ can build valid new/edit/delete-links. - To display only rentals of the book that is currently opened in the form, it is important that we use the condition: .. code-block:: sql WHERE r.bookId = {{id:R0}} * In the **parameter** field we wrote: .. code-block:: text form=Rental All of the buttons on this subrecord (*new*, *edit*, *delete*) are now linked to a form with the name *Rental*. For example, if we click the edit button on a rental record, the Rental form should open up and load the data of the chosen rental record. * Something else you will most likely want to add to the **parameter** field is: .. code-block:: text detail=id:bookId This will pass the id of the subrecord's form (in this case the Book form) to the form that is linked to the subrecord (in this case the Rental form). Not only is the id passed on, but it is mapped to the field ``bookId`` on the new form. This means that the Rental form will have the correct book preselected when opened via our subrecord! Exercise 1 ^^^^^^^^^^ 1. Create the subrecord for rentals on your Book form as seen in this chapter. 2. Create another subrecord for rentals on your Person form. This time the subrecord should list all the books the person has rented. 3. Create another subrecord on the Person form, call it *authoredBooks*. It should show all the books the person has written. Container FormElements ----------------------- Our subrecord for the rentals seems a bit out of place where we are displaying it right now. It does not belong right next to very basic information about a book, like its title or author. Let's divide the form into two tabs – one for general information and the second for rentals. To do this, we create two FormElements, both of **class container** and **type pill**: .. image:: ./Images/general_pill.png .. image:: ./Images/rental_pill.png Now we want to place all of our other FormElements in one of the pills. Let's rearrange the order of our FormElements, so that the pills are above the elements they should contain: .. image:: ./Images/pills_order.png All the other FormElements are now displayed with a red background, because these elements are not yet assigned to a container. In the Basic tab of all these FormElements, we can now assign them to the pills: .. image:: ./Images/assign_pill.png Once we have done that for all of our FormElements, here's the result: .. image:: ./Images/pills_done.png Pills are the container we use the most. But there are also two other container types: * **fieldset** – can be used to further group elements within a pill (a normal HTML fieldset). * **template group** – is used to group a few elements together and repeat them *x* times. The user can create a new set of these elements by clicking a ``+`` button and remove any entry by clicking the ``-`` button next to it. Template groups are similar to subrecords since they are used to manage sub-records of the primary record on a form. Exercise 1 ^^^^^^^^^^ 1. Create the *General* pill and the *Rental* pill on your Book form and assign all FormElements to their pill. 2. Create a *General*, *Rental* and *Authored Books* pill on the Person form. Assign the subrecords to their corresponding pill and the rest of the Form Elements to the *General* pill. Dynamic Update and the Form-Store --------------------------------- In a previous exercise, we made this form to create and edit Rental records. .. image:: ./Images/rental_form.png :alt: Rental Form Let's make a little *quality of life* upgrade for the librarian. Suppose books can be rented only for one month at a time. If a librarian manually creates a new rental, he or she will always have to type both start and due date. To make life easier, we could automatically fill in the due date when the start date is entered. But how can we change the value of the due date input whenever the start date input changes? Without QFQ, we would most likely solve this with a few lines of JavaScript. In QFQ it is even simpler than that: what we are looking for is called **Dynamic Update**. Dynamic Update is a setting that can be enabled or disabled on any FormElement. By default it is disabled, but we need to enable it on the *start* FormElement and on the *due* FormElement. Simply check the box in the *Basic* tab of the FormElement: .. image:: ./Images/start_dyn_update.png :alt: Dynamic Update on start field .. image:: ./Images/end_dyn_update.png :alt: Dynamic Update on end field What does it do though? **If the value of any FormElement with Dynamic Update changes, then all FormElements with Dynamic Update will be refreshed.** Refreshed in this case means: all of the fields you can configure for the FormElement are recalculated. Since we want to automatically set the value of the due date, let's add this to the **value** field of our *due date* element: .. code-block:: text {{ SELECT IF('{{end:RE}}' = '', DATE_FORMAT(DATE_ADD('{{start:RE}}', INTERVAL 1 MONTH), '%Y-%m-%d'), '{{end:RE}}') }} In this query, we check if there is already a due date entered. If there is one, we keep it – otherwise our code might accidentally overwrite a date the user has set manually. If there is no due date yet, we set it to *start date + 1 month*. In MySQL this is done with ``DATE_ADD`` and ``INTERVAL``. We also have to use ``DATE_FORMAT`` so that the output matches the expected formatting of the date field. However, recalculating the fields has no visible effect yet, because the result is still the same. We are missing one piece of the puzzle. Compare the above code to this one: .. code-block:: text {{ SELECT IF('{{end:FRE:alnumx}}' = '', DATE_FORMAT(DATE_ADD('{{start:FRE:alnumx}}', INTERVAL 1 MONTH), '%Y-%m-%d'), '{{end:FRE:alnumx}}') }} The difference is that we are now also using the **Form Store**. * **The Record Store** within a form only contains values that are already saved in the database. Record Store variables therefore only change after saving again – that’s the issue here. * **The Form Store**, however, contains the values of all FormElements on the form. These values are updated immediately when a Dynamic Update happens! * Just like the Client Store, the Form Store by default tries to cast your variable into a digit, which is why we use the sanitize class ``alnumx``. * When we specify ``FRE`` as the store of our variable, QFQ will first try to get it from the Form Store, then from the Record Store, and if neither exists, it returns an empty string. Exercise 1 ^^^^^^^^^^ 1. Modify the Author dropdown on your Book form. Use the *Note* field of the FormElement (Layout tab) to display all other books this author has written (e.g. *Author of: The Hobbit, …*). This text must update dynamically when another author is selected from the dropdown! 2. In the Rental form, add a FormElement of type *note* that says **Overdue!**. This info should only be displayed if: - the book has not been returned yet (``returned = 0``), **and** - the current date is past the due date. Use the **mode sql** field in the *Basic* tab of your FormElement to show or hide it! - If your SQL returns ``show``, the element will appear. - If it returns ``hidden``, it will not. Furthermore, this info must update dynamically as the user edits the form values. Action FormElements -------------------- There is a third class of FormElements we haven't talked about: **Action FormElements**. These elements execute an action when a specified event happens. Most of them already have their trigger in the name: * **beforeLoad / afterLoad** – execute before/after the form loads * **beforeSave / afterSave** – execute before/after the form is saved (both on INSERT and UPDATE) * **beforeInsert / afterInsert** – execute before/after the form saves a new record (``r=0`` → INSERT) * **beforeUpdate / afterUpdate** – execute before/after the form updates an existing record (``r!=0`` → UPDATE) * **beforeDelete / afterDelete** – execute before/after the record in the form is deleted But how do we specify what exactly happens when one of these events is triggered? Example: When a Book is deleted from the database, Rental records may remain that still reference this book. We want to add a *beforeDelete* action to the Book form that deletes all Rental records linked to the deleted book. For this we can use parameters that contain the actual action. The simplest one is **sqlAfter**. The name is confusing because it says "after". But since we already defined that the action belongs to *beforeDelete*, QFQ knows when to execute it. If there are multiple actions of the same class (e.g. multiple beforeDelete elements), ``sqlBefore`` runs before ``sqlAfter``. If you use only ``sqlAfter``, then execution order is determined solely by the *Order* field of the Action Elements. The *sql* in sqlAfter means that we are going to execute SQL when the action is triggered. Here is the configuration of our beforeDelete element: .. image:: ./Images/before_delete_1.png :alt: Action FormElement beforeDelete (settings 1) .. image:: ./Images/before_delete_2.png :alt: Action FormElement beforeDelete (settings 2) Now, if we delete a book, all Rental records referencing this book are also deleted. Alternative solutions: * Configure CASCADE rules in the MySQL database. This requires proper foreign keys. * Instead of deleting, many applications just mark a record as "deleted" (soft delete). That way no references are broken, since the data still exists. Exercise 1 ^^^^^^^^^^ 1. Add the beforeDelete element from this chapter to your Book form. 2. Add another beforeDelete element to the Person form that deletes all Rentals of this person. 3. On the Book form, it is currently possible to enter a negative number of pages. Use an Action FormElement to validate user input when the form is saved and display an error message if the number is invalid. Instead of one parameter (``sqlAfter``), you will need three in this case: ``sqlValidate``, ``expectRecords``, ``messageFail``. Check the documentation to see exactly how an Action FormElement can be used this way: http://docs.qfq.io/master/Form.html?#formelement-parameter-sqlvalidate **Tip:** ``sqlValidate``, ``expectRecords`` and ``messageFail`` all go into the Form.Parameter. You might need to use ``{{! ... }}`` for the sqlValidate parameter. Example: .. code-block:: text sqlValidate = expectRecords = messageFail = "FAIL" Using Report-Syntax inside a Form """"""""""""""""""""""""""""""""" There are (very few) cases, in which normal QFQ FormElements are not able to achieve exactly what we want. Luckily, we have the option to write in QFQ-Report Syntax within a Form! This is a hidden, but very useful feature - because anything is possible in a Report! No actual use case is shown here, because these are often the most complicated of cases and they have no place in this tutorial. We are just going to show how it is done. On our Book Form in the rental tab we will add a FormElement with type **note**. Usually it is used to display a small text or a little bit of HTML. We configured it like this: - **Name**: reportAvailable - **Type**: note - **BS Label Columns**: 0 - **BS Input Columns**: 12 - **BS Note Columns**: 0 - **Value**: .. image:: ./Images/form_report_value.png The result is the following label, which states whether the book is currently available or not: .. image:: ./Images/form_report_result.png The takeaway from this is: You can use the power of the Report syntax in a Form by starting with **#!report**. However, you should only use this when needed, because things get messy when this is overused! Exercise 1 ^^^^^^^^^^ 1. Add a Note FormElement to any of your forms and use it to write Report Syntax. It doesn't matter what your report does - go wild! Bonus Exercises ^^^^^^^^^^^^^^^ Solution """""""" The solutions for Bonus & Advanced are both under Advanced. Depending on QFQ version there might be slight differences in style. Typo3 Backend: `https://webwork22.math.uzh.ch/qfqtutorial/typo3 `_ | Frontend: `https://webwork22.math.uzh.ch/qfqtutorial `_ Exercise 1 ^^^^^^^^^^ 1. When we create a new 'Rental' via subrecord on the Book-Form, then we would like the Book to be preselected on the Rental-Form that opens. * Study the 'FormElement.parameter' section of http://docs.qfq.io/master/Form.html#type-subrecord and find out how we can pass the book's id from the Book Form to the Rental Form * Pass the person's id instead, if the Rental Form is opened via subrecord on the Person Form. * .. image:: ./Images/rental_preselect_book.png 2. Create a Form BookGenre. The Form should have a 'readonly' (FormElement.mode) dropdown that has a preselected Book and a dropdown that contains a list of all Genres. .. image:: ./Images/add_new_genre_to_book.png 3. Create a 'Genres' subrecord on the Book Form. When a Genre is added, the correct Book needs to be preselected on the BookGenre Form! .. image:: ./Images/book_subrecord_of_genre.png 4. Create a beforeSave action FormElement on the BookGenre Form that stops the user from adding the same genre to a book twice! * QFQ beforeSave Doc: http://docs.qfq.io/master/Form.html#formelement-type-before-after 5. Create a Page "Rentals". * Create a *Summary* section at the top of this page. Show the following statistics: * Number of books rented (all time) * Number of books currently rented * Number of books overdue and not returned * Top 3 most rented books * Top 3 people who rented most books .. image:: ./Images/summary_result_example.png * Below the Summary, create a table with all Rentals. Show person name, book title, start, due and returned dates. * The whole row should have a green/success background color, if the book has already been returned. If the book is overdue, the row's backgroundcolor should be red. * Bootstrap Doc: https://getbootstrap.com/docs/3.4/css/#tables-contextual-classes * Depending on the approach, you might also need _noWrap: http://docs.qfq.io/master/Report.html#special-column-names * Order the whole table so that the Rentals with the earliest due dates appear at the top. * Make a button above the table that allows you to hide all Rentals that have already been returned. If the user is currently hiding these records, the button should say 'Show returned Rentals' instead. .. image:: ./Images/summary_table_example.png 6. Create a Page "Settings" in the Admin-area. Create a report on this page that shows a table with all Grp-records with ``grId=0`` (Show Edit-Button, Name, Reference and number of sub-elements). Also create a 'new' button. .. image:: ./Images/settings_report_example.png 7. Create a Form to edit Grp-records (grId, name, reference, subrecord for all child-Grps). 8. Add an upload Form-Element to your Book Form, where the cover-image of the book can be uploaded. * Study the upload element in the official documentation (Upload Simple mode): http://docs.qfq.io/master/Form.html#type-upload * You will be saving the image path in the column *coverPathFileName* of the Book Table in your database. The image itself will be saved somewhere in the 'fileadmin' folder. **Display the uploaded cover image in two places:** * On the Book-page as a small thumbnail (50px wide): http://docs.qfq.io/master/Report.html#column-thumbnail .. image:: ./Images/book_table_thumbnail_example.png * In the Book-Form below the upload-element (no size restrictions): .. image:: ./Images/book_upload_cover_example.png Advanced ^^^^^^^^ These things are not part of the tutorial, but are good to know! * System-Store (Y), QFQ-Config * More special columns - sendmail (mit 'action-Pattern'). **Might not work depending on Docker configurations** - pagee, pagen and paged http://docs.qfq.io/master/Report.html#columns-page-x - thumbnail http://docs.qfq.io/master/Report.html#column-thumbnail * Functions * T3 feUser/feUsergroups, custom Person and Person_Role tables linked via 'account' column - sync Person with T3 feUser/feUsergroups - LDAP connection * User Store, Switch User * More FormElements http://docs.qfq.io/master/Form.html#class-native - Template Groups - Upload - Sendmail http://docs.qfq.io/master/Form.html#type-sendmail **Might not work depending on Docker configurations** - Checkbox & Checkboxlist - Radio & RadioList - Editor - Typeahead - Tag-FE * Tablesorter Small Project ------------- Requirements ^^^^^^^^^^^^ You can choose a generic example, e.g. students with lectures. A task description should then be formulated that requires the following features: * Create / Edit / Delete records * Subrecord or Template Group * File Upload (pdf) * Email sending * Download (pdf) Concept ^^^^^^^ * Create a mockup, e.g. with draw.io, balsamiq or by hand… * Create a database schema Implementation ^^^^^^^^^^^^^^ The learner should implement the self-created concept as independently as possible after consultation with the supervisor. For specific problems (e.g. file upload, subrecord, email sending …) links to the relevant sections in the QFQ documentation will be provided. Testing ^^^^^^^ Test cases will be derived from the requirements. A test protocol should be created and afterwards the tests should be carried out. Typo3 Basic Setup ^^^^^^^^^^^^^^^^^ Install Extensions QFQ and Typo3: - https://qfq.io/download/?dir=snapshots (Take actual master or develop version) - https://www.math.uzh.ch/repo/?dir=uzhcd/UZH_CD_2023 (Take actual version) .. image:: ./Images/Tutorial/tutorial-report-installedExtensions.png Create a new Home page and define UZH CD template: .. image:: ./Images/Tutorial/tutorial-report-newSubpage.png .. image:: ./Images/Tutorial/tutorial-report-templateExtension.png .. image:: ./Images/Tutorial/tutorial-report-editTemplateOptions.png .. image:: ./Images/Tutorial/tutorial-report-editTemplate.png .. image:: ./Images/Tutorial/tutorial-report-editPage.png .. image:: ./Images/Tutorial/tutorial-report-editPageAppearance.png Create this qfq tt-content in your Home page: .. code-block:: sql # Link for qfq tutorial # Title { sql = SELECT '

      QFQ Dev Environment

      ' } # Todo info { sql = SELECT '- Execute the exercises from the tutorial links below in your qfq dev environment' , 'Feel free to create your own new subpages and qfq reports on backend side: T3 Backend' , 'You need new tables/columns or changes in your database? Here we are: Database' , 'QFQ Framework Documentation: QFQ Doc' head =

      Todo:

      fend =
      } # Tutorial link info { sql = SELECT 'SQL Basics' , 'HTML/CSS/JS Basics' , 'QFQ' head =

      Tutorial Links:

      fbeg =
      } # Credentials and url info { sql = SELECT 'QFQ_DEV_INFO.txt' head =

      Hints:

      Your initial credentials and the urls for frontend/backend/database can be found in file }