Tutorial.rst

.. ==================================================
.. Header hierachy
.. ==
..  --
..   ^^
..    ''
..     ;;
..      ,,
..
.. --------------------------------------------------
.. Best Practice T3 reST  https://docs.typo3.org/typo3cms/drafts/github/xperseguers/RstPrimer/
.. External Links: `Bootstrap <http://getbootstrap.com/>`_:
.. Add Images: https://wiki.typo3.org/ReST_Syntax#Images
..
.. -*- coding: utf-8 -*- with BOM.


Installation of QFQ
===================
First you have to log in into your Typo3. It is going to look like this:

.. figure:: Images/Typo3FirstLogin.png

Then you have to install the QFQ Extension. Go to https://qfq.io/ to download the extension.
In Typo3: Go to the Extensions Menu and click on the upload button. There you choose the the file,
you just downloaded. Now the QFQ extension is installed. If you get an error message that the file is
too big, you have to edit your php.ini file and increase the memory limit.

Copy/rename the file *<Documentroot>/typo3conf/ext/<ext_dir>/config.example.qfq.ini* to
*<Documentroot>/typo3conf/config.qfq.ini* and configure the necessary values: *config.qfq.ini*
The configuration file is outside the extension directory to not loose it during updates.

First we want to include Bootstrap and Javascript. For that we create a template.
Go to list and create a new page.

.. figure:: Images/Typo3FirstPage1.png

Click on Page (inside)

You can choose some Page Title. I am calling it Home. Next you go to Behaviour:

.. figure:: Images/Typo3FirstPage2.png

Here you choose a URL Alias. I am going to call it home. So in my case, to access this page, if would have
to go to the following website: *www.<nameOfYourWebsite>.<domain>/home*

Then you save it. Now you see a new page which is disabled for now. You have to enable it (so the site
will be visible on the website). Right click the Page Home and click 'Enable'.

.. figure:: Images/Typo3Enable.png

Now on that page you want to include Bootstrap and CSS. For that purpose you click on Home and you add
a new content element. Click on template:

.. figure:: Images/Typo3CreateTemplate2.png

Now we name the Template: ext:site (actually you can call it whatever you want)

Under setup you paste the following:

::

    page = PAGE

    page.meta {
      X-UA-Compatible = IE=edge
      X-UA-Compatible.attribute = http-equiv
      viewport=width=device-width, initial-scale=1
    }

    page.includeCSS {
      file1 = typo3conf/ext/qfq/Resources/Public/Css/bootstrap.min.css
      file2 = typo3conf/ext/qfq/Resources/Public/Css/bootstrap-theme.min.css
      file3 = typo3conf/ext/qfq/Resources/Public/Css/jqx.base.css
      file4 = typo3conf/ext/qfq/Resources/Public/Css/jqx.bootstrap.css
      file5 = typo3conf/ext/qfq/Resources/Public/Css/qfq-bs.css
    }

    page.includeJS {
      file01 = typo3conf/ext/qfq/Resources/Public/JavaScript/jquery.min.js
      file02 = typo3conf/ext/qfq/Resources/Public/JavaScript/bootstrap.min.js
      file03 = typo3conf/ext/qfq/Resources/Public/JavaScript/validator.min.js
      file04 = typo3conf/ext/qfq/Resources/Public/JavaScript/jqx-all.js
      file05 = typo3conf/ext/qfq/Resources/Public/JavaScript/globalize.js
      file06 = typo3conf/ext/qfq/Resources/Public/JavaScript/tinymce.min.js
      file07 = typo3conf/ext/qfq/Resources/Public/JavaScript/EventEmitter.min.js
      file08 = typo3conf/ext/qfq/Resources/Public/JavaScript/typeahead.bundle.min.js
      file09 = typo3conf/ext/qfq/Resources/Public/JavaScript/qfq.min.js

      # Only needed in case FormElement 'annotate' is used.
      file10 = typo3conf/ext/qfq/Resources/Public/JavaScript/fabric.min.js
      file11 = typo3conf/ext/qfq/Resources/Public/JavaScript/qfq.fabric.min.js
    }

    page.10 = TEXT
    page.10.value = Hello World

    page.20 < styles.content.get

The last 3 lines can be removed later. They are just there to see if it works. If it worked on the webpage
we should see the text: "Hello world".

Next you go to Options and mark Constants, Setup und Rootlevel.

.. figure:: Images/Typo3CreateTemplateOptions.png

Next you go to Includes,  Include static and you select: Conten Elements (fluid_styled_content)

.. figure:: Images/Typo3CreateTemplateIncludes.png

Now you are ready to see for a first time your Website (which has for now only one line of text (Hello World)).
To take a look at the website make a right click on Home and press on show.

.. figure:: Images/Typo3Show.png

So now QFQ is installed and Javascript and Bootstrap can be used. Now we want to add the FormEditor to the
website such that from now on we are going to be able to create Forms and FormElements on the website. For that
purpose we Create a new QFQ element and past in a SQL Query:

First you create a new Page called Setup:

.. figure:: Images/Typo3CreateSetup1.png
.. figure:: Images/Typo3CreateSetup2.png

As Page Title you type in Setup (As always you are free to name it however you want).
Next you have to go to Behaviour and under URL Alias you type in setup.
As before you have to enable the page.

From now on I am going to use another template. I do this because with my template the webpage is going to look better.
So from now on my webpage will look not the same as your website. Don't worry about it.

Now Create a subpage of Setup. There we will put the FormEditor in.
In order to do that right click Setup, go to Page Actions and click on New:

.. figure:: Images/Typo3FormElement1.png
.. figure:: Images/Typo3FormElement2.png

Here You Type in Form as Page Title and form as an alias.

Next you have to create a template for this page. You can copy the template from the home page.
In order to do that go to Home and copy the Template:

.. figure:: Images/Typo3CopyTemplate.png

Now you go to Setup and click on the past Button:

.. figure:: Images/Typo3CopyTemplate2.png

Now you click on the Form Page and create a new QFQ element.

.. figure:: Images/Typo3FormQFQ.png

There you click on Page Content.
After that Chose the type QFQ Element.

.. figure:: Images/Typo3FormQFQ2.png

Now past in the following code into the Text field:

::

    form={{form:SE}}
    10 {
        sql = SELECT CONCAT('{{pageId:T}}&form=Form&') as _Pagen, '#', 'Name', 'Title', 'Table', '' FROM (SELECT 1) AS fake WHERE  '{{form:SE}}'=''
        head = <table class="table table-hover qfq-table-50">
        tail = </table>
        rbeg = <thead><tr>
        rend = </tr></thead>
        fbeg = <th>
        fend = </th>
        10 {
            # All forms
            sql = SELECT CONCAT('{{pageId:T}}&form=Form&r=', f.id) as _Pagee, f.id, f.name, f.title, f.tableName, CONCAT('form=form&r=', f.id) as _Paged FROM Form AS f  ORDER BY f.name
            rbeg = <tr>
            rend = </tr>
            fbeg = <td>
            fend = </td>
        }
    }


Now your page should look like this:

.. figure:: Images/FormEditor.png

It is important to say that your screen can look different. Probably your list is going to be empty. If so, don't worry.
It doesnt matter as these are all forms which we are not going to use. We are going to create new Forms.

Creating Forms
==============

Now we are going make a little project. We want to create a website where you can apply for different jobs.
Step by step we will create a little recruiting tool out of it. Our first step will be that we create a form where the
applicant can fill out some information about himself like his name, first name, date of birth etc.
To store the data the applicant fills into the form we will create a database, and a little
visualisation of the database.

First of all let's create our database. In order to do that log in into your phpMyAdmin.

There you have to create A database. When you have your Database go to SQL and past in the following code: ::

    CREATE TABLE  `Application`
    (
       `id` INTEGER NOT NULL AUTO_INCREMENT ,
       `name` VARCHAR( 255 ) ,
       `firstName` VARCHAR( 255 ) ,
       `gender` ENUM( 'male','female' ) ,
       `dateOfBirth` DATE ,
       `adrStreet` VARCHAR( 255 ) ,
       `adrZipCity` VARCHAR( 255 ) ,
       `uploadPath` VARCHAR( 255 ) ,
       PRIMARY KEY `id`(`id`)
    ) ENGINE = INNODB DEFAULT CHARSET = utf8

The Query above creates a table with some rows in it. The purpose of that database is that whenever somebody fills
out a form, we will be able to save the data into this database.
The next step is, that we should create our fist Visualisation of our database. At the beginning of a project it seems
silly to do that, but the further we go and the more tables we use in that project, the
more complicated our datastructure is going to become. At that point we are not only happy about the dia, but we are
even dependent of that visualisation of our Database as elsewise we would lose track of our datastructure.
So it is very important to keep in mind, that whenever you have some new tables or new ways the different tables are
connected to each other, to add this into the visualisation.

As soon as our database structure is a little bit more complicated the dia is going to look something like this:

.. figure:: Images/DiaFull.png

The programm we are going to use is called Dia. You can use whatever programm you want. If you want to use the same
program as me you can download it here: http://dia-installer.de/

When you first start the program it will look something like this:

.. figure:: Images/DiaEmpty.png

First you have to create a Diagramm in mode Database.

.. figure:: Images/DiaSettings.png

Next you have to create a rectangle:

.. figure:: Images/DiaRectangle.png

You double-click the box, go to Attributes, and add all the table rows.
At the end it should look like this:

.. figure:: Images/DiaApplicationTable.png

Now we are going to create our first form. Whenever a person wants to apply for a job, he or she has to fill out this form.
For that purpose we go to our website, go to Form and create a new form by clicking on the + sign:

.. figure:: Images/FormEditorFirstForm.png

Then you will see the following screen:

.. figure:: Images/FormEditorBasic.png

For the name you can type in whatever you want to. I am going to call it Application.
The most imporant thing here is that you chose the right table. The data, entered by the user will be saved in that Database Table.
We select of course the Application Table.

.. figure:: Images/FormEditorBasic2.png

Now we are ready to create our first Form Elements. For this purpose go to the Pill Form Element.
Then we click on the + sign.

.. figure:: Images/FormEditorFormElement1.png

First we want to create a textbox, where the user can input his name. So what we want to do is create
a Form Element of the type text, which stores the users input into the row name of the table Application. So what we do is the follwing:
- Name: We type in name (name of the database row)
- Label: Name (is what will be desplayed in front of the textbox)
- Type: Text
Then you can save the Form Element and click at the eye to take a look at the form (how the form looks like).

.. figure:: Images/FormEditorName.png

It should look something like this:

.. figure:: Images/FormEditorName2.png

Now do the same thing for the First Name. It works exactely the same way. I won't explain it again. Now your form should look like this.

.. figure:: Images/FormEditorName3.png

Next we want to create a checkbox, where the user can enter his gender. As there are only 2 genders.
For that purpose we create a new form Element of the type checkbox.

- Name: We type in gender (name of the database row)
- Label: Gender (is what will be displayed in front of the Checkboxes)
- Type: Radio

Detailed information: https://docs.typo3.org/typo3cms/drafts/github/T3DocumentationStarter/Public-Info-053/Manual.html#type-radio

.. figure:: Images/FormEditorBasicGender2.png

But here it is not enough to only create a checkbox. In addition we have to define which possibilities the user has for an input.
We do that as follows: We go to the pill value and write the following into the textbox parameter: ::

    itemList=male,female

Now it is going to look like this:

.. figure:: Images/FormEditorGender3.png

But we want that the 2 radios are at the same line. For that purpose we edit the Form Element. We go to the Pill Layout and
change MaxLength to 2.

.. figure:: Images/FormEditorGender4.png

In general if you want to put n elements on the same line, Put n as the MaxLength.

Next we want to create a textbox, where the user can enter his date of birth. We do the exactely same thing as what we did
when we wanted to create a textbox for the name or the first name. But this time the type of the Form Element is going to be
Date.
Details: https://docs.typo3.org/typo3cms/drafts/github/T3DocumentationStarter/Public-Info-053/Manual.html#type-date


.. figure:: Images/FormEditorDateOfBirth.png

As an exercise for you, you can add 2 more Form Elements (Textboxes) where the user can enter his Street (incl. number) and
zip. It should look like this:

.. figure:: Images/FormEditorAdresses.png

Now we have a few Form Elements but it doesn't look very nice. Moreover everything is on the same pill. So we want to create 2 pills.
One pill for your personal data like name, first Name, date of birth and gender and another pill for your adress.
For that purpose we are going create a new Form element. This time it will be a Form element of class container und of type pill.
Details: https://docs.typo3.org/typo3cms/drafts/github/T3DocumentationStarter/Public-Info-053/Manual.html#type-pill-tab

.. figure:: Images/FormEditorPill1.png

Next we want to assign all Form elements which contain personal data to that pill. To do that, edit one of the form Elements which
should be in this pill and assign it to the corresponding pill:

.. figure:: Images/FormEditorPill2.png

We do the exact same thing for all the elements which contain personal data. I am not going to show it for each element,
as it's the same for all.

Next we have to create a second pill which contains all form Elements for the adress. As before create a new pill:

.. figure:: Images/FormEditorPill3.png

And assign the 2 left form Elements to that pill (excately as before).

Now your Form should look something like this:

.. figure:: Images/FormEditorPill4.png


Report
======


Next we want to create a page where we can see a list of all applications so far. Because the problem is that people have the
possibility to open the Form and write their names into the Form but that information is displayed nowhere.
So now, in order to see later anything in that list of all applications, it would be useful if you open the Form and type something in.
Because if we don't, the list of applications will be empty of corse.
Next go to Typo3 and create a new page.

.. figure:: Images/Typo3ReportCreateNewPage.png

.. figure:: Images/Typo3ReportCreateNewPage2.png

Next you have to give the page a title. I am going to call it Applications:

.. figure:: Images/Typo3ReportCreateNewPage3.png

And you have to give the page a page Alias.

.. figure:: Images/Typo3ReportCreateNewPage4.png

As always you have to enable the page.

.. figure:: Images/Typo3ReportCreateNewPage5.png

Next you have to put some content on the Applications page. For that purpose create a new QFQ Element:

.. figure:: Images/Typo3ReportCreateNewPage6.png

Click on Page Content:

.. figure:: Images/Typo3ReportCreateNewPage7.png

Chose the type QFQ Element.

.. figure:: Images/Typo3ReportCreateNewPage8.png

Now you can put SQL Queries into the Text box.

You can paste the following SQL Query into the Text box: ::

    10 {
      sql = SELECT a.id, a.name, a.firstname, a.gender, a.dateOfBirth, a.adrStreet, a.adrZipCity FROM Application AS a
        head = <table class='table table-hover qfq-table-50'><thead><tr><th>#</th><th>name</th><th>First Name</th> <th>Gender</th><th>Date of birth</th><th>Street</th><th>Zip</th></tr><thead><tbody>
        tail = </tbody></table>
        rbeg = <tr>
        rend = </tr>
        fbeg = <td>
        fend = </td>

    }

This code displays all Applications so far. The first part (The select statement) is just usual SQL Code. You should know that
already. The interesting part is the part below the SQL statement. Here a little explaination of that part of the code:

*  head = ... : This line is being executed before the SQL statement gets executed. So it is generating a table header.
*  tail = ... : This line is being executed as soon as the SQL stement is fininshed and all the data has been displayed on the screen.
*  rbeg = ... : Rbeg means row beginn. At every row beginn a <tr> is being placed.
*  rend is row end, fbeg is field beginn and fend is field end. So whenever a field or a row starts or ends, one of the HTML statements
are going to be executed.

Details: https://docs.typo3.org/typo3cms/drafts/github/T3DocumentationStarter/Public-Info-053/Manual.html#qfq-keywords-bodytext

So now your page is going to look like this:

.. figure:: Images/Typo3RecordDone.png

Our next goal is to add a little putton at the left of every row of the table. It is going to look like this:

.. figure:: Images/Typo3RecordButtonEditShow.png

When we click on the button we will open the Form Application of the respective person. So the idea is the following.
When you open up a form with a few parameters like for example the id of the person logged in, you can edit the data of
that certain person. So if for example Kevin Miller who lives at Examplestreet 123 changes his adress to Examplestreet
128 you can open the Form Application with his id as a parameter and edit his data. If you would open up the Form without
any id the Form would't know from which person you want to change the data. The result: You are going to create a new Application
instead of changing an existing user.
So what you want to do: Whenever you open the Form Application, you want to pass a parameter namely your id.
So first we create the button you can see in the image above. To do that go back to your QFQ Record on the Application page and
edit the QFQ Element.

Delete the whole Query and paste in the following SQL Statement: ::

    form={{form}}

    10 {
      sql = SELECT CONCAT('{{pageId:T}}&form=Application&') as _Pagee, a.id, a.name, a.firstname, a.gender, a.dateOfBirth, a.adrStreet, a.adrZipCity FROM Application AS a, (SELECT 1) AS fake WHERE  '{{form:SE}}'=''
        head = <table class='table table-hover qfq-table-50'><thead><tr><th>Form</th><th>#</th><th>name</th><th>First Name</th> <th>Gender</th><th>Date of birth</th><th>Street</th><th>Zip</th></tr><thead><tbody>
        tail = </tbody></table>
        rbeg = <tr>
        rend = </tr>
        fbeg = <td>f
        fend = </td>

    }

This SQL statement is a little more complicated, but most of the content didn't change at all. Now I am going to give an explanation
of the new lines of code:

- *form = {{form}}*: this line tells the website, that on that specific page we want to create a button, which opens up a Form.
If this line would miss, we could't open the form. If you don't know what I mean, just delete the first line of code, save
the QFQ element, go to the webpage and try to open the Form with the little button at the left. You will see, the button is
not working.

Details: https://docs.typo3.org/typo3cms/drafts/github/T3DocumentationStarter/Public-Info-053/Manual.html#qfq-keywords-bodytext

- *SELECT CONCAT('{{pageId:T}}&form=Application&') as _Pagee*: This line create the button which opens up the Form.
  Details: https://docs.typo3.org/typo3cms/drafts/github/T3DocumentationStarter/Public-Info-053/Manual.html#store-typo3-bodytext-t

  - *{{pageId:T}}* is just a number. When you go to the Typo3 Page, and you hover over the page of Application, you will see
    the Page Alias and the Page id. Here the Statement *{{pageId:T}}* goes to Typo3 and checks what the PageId of the current page is.
    The reason why *{{pageId:T}}* is a part of the URL is simple. We don't want to create a new page just to open up a Form. The Form
    is going to be opened on the Application page.

  - *&form=Application&*: This line opens up the Form Application. Whenever you want to create a link, where a specific Form is
    going to be opened, you have to specify which Form you want to open up, with this notation.

  - *AS _Pagee: This part tells QFQ that we want to create a button with an edit symbol on it.

- *(SELECT 1) AS fake WHERE  '{{form:SE}}'=''*: This statement is at the end of the SQL Statement and looks complicated. What it does:
If this line of code wouldn't be there you would have the following problem. When you open up the link, you want to see only the Form,
and not the list of all other Applicants under the form. This line of code does exactely that. It does hide the list of
people as soon as you open up the form.
If it is not clear what is meant by that I'll show you a few screenshots to illustrate the problem:

If you would delete the following statement: *(SELECT 1) AS fake WHERE  '{{form:SE}}'=''*, this would happen:

When I open up the Application page it will look something like this:

.. figure:: Images/Typo3FakeExample1.png

So far it is fine. But if we click on the edit button, we see that the Form displayed as wished, but the list of applicants is still
bellow the Form:

.. figure:: Images/Typo3FakeExample2.png

But we don't want that. We want that if we open up the Form, the rest of the list won't be displayed anymore. So if we paste back
the following line of code: ::

    (SELECT 1) AS fake WHERE  '{{form:SE}}'=''

into the SQL statement, when we open the Form, the list is going to be hidden.

Now we have a page, where all Applications are listed. But there is still a problem: When we open the form of a person, the form is
empty. But what we want is that whatever data the Applicant entered and saved, when he opened the Form, should be displayed when we open
the form.
To fix that problem we have to do the following:

When we click on the Editbutton on the Applications page, we have to "tell the button" which person's record we want to change.
So we go back to the Typo3 page and edit the QFQ element again.
Delete your query and paste the following: ::

    form={{form}}

    10 {
      sql = SELECT CONCAT('{{pageId:T}}&form=Application&r=',a.id) as _Pagee, a.id, a.name, a.firstname, a.gender, a.dateOfBirth, a.adrStreet, a.adrZipCity FROM Application AS a, (SELECT 1) AS fake WHERE  '{{form:SE}}'=''
        head = <table class='table table-hover qfq-table-50'><thead><tr><th>Form</th><th>#</th><th>name</th><th>First Name</th> <th>Gender</th><th>Date of birth</th><th>Street</th><th>Zip</th></tr><thead><tbody>
        tail = </tbody></table>
        rbeg = <tr>
        rend = </tr>
        fbeg = <td>
        fend = </td>

    }

Now when you open the form, you can see all available information about the person.

Login
=====

Now we want a LogIn page.
For that we have to create a folder in Typo3 where we define all FE Users. We have to define all usergroups and users.
Later you might want to integrate an LDAP database for that purpose. But more on that later. Moreover you have to
create a first user with username and password in order to be able to login.
In this tutorial I am not going to explain that in detail.

Next go to your Typo3 page and add a login Element on the page "Home".

.. figure:: Images/Typo3Login1.png

When you go to the Pill Pluin you can change the html template of the login page if you don't like the standart
design.
Moreover you have to add you FE User Folder to the "User Storage Page"

.. figure:: Images/Typo3Login2.png

Now when you try to connect to the webpage, you first have to login with your credentials.

Now whenever somebody is logging in, he can see everything. He can edit forms, he can go to the admin panel etc. Of course
we don't want that. We want that every Typo3 page has a FE User Group, assigned to it. So if for example a person is logged in
and this is assigned to the user group Base, he can't access the admin panel.
On order to do that, open Typo3 and click on the Page Setup. Edit the page and go to Access.
There you will se the subsection Usergroup Access Rights. Here you can assign the admin to the Page Setup. ( In the FE User
Folder I called the admin setup). So in my example I am going to assign the Setup Page to the setup Usergroup.

.. figure:: Images/Typo3AccessSetup.png


Now we want to create the Form where we can add new Reviewers to the Table Person. In the FormEditor I enter the following
data:

.. figure:: Images/FormEditorPersonFrom1.png

Now we want to create a form where we the admin of the page can add new Reviewers to the page. That form should contain
5 Textboxes, (name, first name, email adress, adress (street), adress (zip with city) ). Additionally you should add a
radio where you can chose the gender with an itemlist and a datebox, where the user can input his date of birth.
As the primary table you chose the Person table. Since we already did almost the same form I am not going to show you
how it works again. If you are unsure please look how we did the first form.

But the problem is that so far there exists no table named person. So we have to create it first. You can paste
the follwing query into the SQL prompt in order to get that table: ::

    CREATE TABLE  `JobOffer`
    (
        `id` INTEGER NOT NULL AUTO_INCREMENT ,
        `name` VARCHAR( 255 ) ,
        `firstName` VARCHAR( 255 ) ,
        `gender` ENUM('male','female') ,
        `dateOfBirth` DATE ,
        `adrStreet` VARCHAR( 255 ) ,
        `adrZipCity` VARCHAR(255) ,
        `email` VARCHAR( 255 ) ,
        `created` DATETIME ,
        `modified` DATETIME ,
        PRIMARY KEY `id`(`id`)
    ) ENGINE = INNODB DEFAULT CHARSET = utf8

Your Form you just created should look like this:

.. figure:: Images/FormEditorPersonFrom2.png

Important to mention is that you don't add Applicants with that form. You only add Reviewers and Admins to the page.
But for now there is no way how you can distinguish between admin and reviewer since both are saved in the table person.
So what we want to do:
In this form, you should be able to tell the form if the person you want to add to the database should be a reviewer or an
admin.
So first we create a new row in the table Person named flag. The idea is that every reviewer has *flag = 0*, every
admin has *flag = 1*. With the following command you can add the new row to the database: ::

    ALTER TABLE `Person` ADD `flag` INT(11) NOT NULL AFTER `email`;

Next you have to add a radio to the form Person where 1 = admin and 0 = reviewer

.. figure:: Images/FormEditorFlag1.png

and in the pill value paste the following into the textbox parameter.

Now we need a page where we can access that form. So we want a query where we can edit existing reviewers and existing
admins. Moreover we want to see, if the given people are admins or reviewer. So create a new Typo3 page and add a QFQ
element to it. Don't forget to add a page alias. Now try to create the following query:

At the top of the page we want to have a plus sign where you can create a new reviewer/admin. Under that you want 2 tables:
One table for all the admins and the other table for all the reviewers. Moreover you should have an edit button at the left
of every person where you can edit their data.
Here a working query if yours is not working: ::

    form={{form}}
    10 {
      sql = SELECT CONCAT('{{pageId:T}}&form=person&r=0') as _Pagen, '<br><br>' FROM  (SELECT 1) AS
      fake WHERE  '{{form:SE}}'=''
    }
    20 {
      sql = SELECT p.flag AS _flag, IF(p.flag=0,'Reviewer','Admin') AS _type FROM Person AS p GROUP BY p.flag

      10 {
        sql = SELECT CONCAT('{{pageId:T}}&form=person&r=',p.id) as _Pagee, p.id, p.name,
       p.firstname, p.gender,  p.dateOfBirth, p.adrStreet, p.adrZipCity, p.email FROM Person AS p,
        (SELECT 1) AS fake WHERE '{{form:SE}}'='' AND p.flag = '{{flag:R}}'
        head =  <table class='table table-hover' style="width: unset"><b>{{type:R0}}</b>
        tail = </tbody></table>
        rbeg = <tr>
        rend = </tr>
        fbeg = <td>
        fend = </td>

      }
    }


Job Advertisements
=================

Until now it is only possible to apply to a job and to add reviewers, which evaluate all the applications. But we
can't put any job advertisements online. So that's our next task. We want to create three things:

1) A page where the admin can add new Job advertisement. He should be able to write a should description of the job and
   the job requirements. Moreover it should be possible that the admin can set a start and end date. The advertisement
   will only appear on the website in that time intervall. the rest of the time it will be hidden.

2) A page where all the Job Advertisements shown. So that if somebody is looking for a job in that firm, he can look thow
   all the job advertisments on one page and there should be a button apply where we open the Application Form Automatically.

3) Secondly we want to edit the Application Form slightly. It is very important that the person is able not only able to
   apply for a job, but also chose on the form, for which job he is applying. Moreover the applicant should be able to
   upload a few files (like the CV, the letter of motivation or certificate of employment from previous jobs).

So let's start with task 1:
So first thing we have to create a new table in the database and of course as always add it to our database visualisation.
The new table is going to be called job offer and every record contains 1 joboffer with all important information about
the job.
As always I will give you the Query which you can paste into the SQL Prompt to create the table: ::

    CREATE TABLE  `JobOffer`
    (
        `id` INTEGER NOT NULL AUTO_INCREMENT ,
        `type` enum('student','staff') ,
        `title` VARCHAR( 255 ) ,
        `start` DATE ,
        `end` DATE ,
        `status` ENUM( 'open','intranet','closed','finished','trash' ) ,
        `pathFileNameDetail` VARCHAR(255) ,
        `description` TEXT ,
        `emailContact` VARCHAR( 255 ) ,
        `created` DATETIME ,
        `modified` DATETIME ,
    PRIMARY KEY `id`(`id`)
    ) ENGINE = INNODB DEFAULT CHARSET = utf8

Next we add it to the Database visualisation:


.. figure:: Images/DiaJobOffer.png

Next we want to create the form where the admin can add and edit existing job Offers:
For that create a new form, call it job offer and assign the table jobOffer as its primary table:

First we want to create a Textbox Job Title:

.. figure:: Images/FormEditorJobOffer1.png

Next we want to know if the job is primary a student job, or a job for people with a degree. For that purpuse we are going
to create our first select FormElement. Create a new Form Element of the type select, save the data into the column type:
Details: https://docs.typo3.org/typo3cms/drafts/github/T3DocumentationStarter/Public-Info-053/Manual.html#type-select

.. figure:: Images/FormEditorJobOfferType.png

go to values and type in the following into parameter:

.. figure:: Images/FormEditorJobOfferType2.png

Here we work with an itemlist. Usually you don't want to do that. But for now we don't want to put too much complexity
into the database structure, so we leave it like this for now.

Next thing we want to add is a from to date (meaning that the Job advertisement will only be available on the webpage in
this time interval. First we want to create the starting date: So create a new form element with type date:

.. figure:: Images/FormEditorOnline1.png

And we want to create a second form element for the end date:

.. figure:: Images/FormEditorOnline2.png

But the problem now is that the two of them are on 2 different lines. Our goal is, that at the end, it look something
like this:

.. figure:: Images/FormEditorOnline3.png

So now is the first time that we actually do some styling to a form element. We do that as follows:
Edit your textbox where the startpoint of the intervall is chosen.

.. figure:: Images/FormEditorOnline4.png

 Go to the pill layout and do the following changes:

 .. figure:: Images/FormEditorOnline5.png

BS Label Column defines how big the label is renderes.
BS Input Column defines how big the input box is renderes.
BS Note Column defines how big the note is renderes. (until now we never used notes) so don't worry if you don't know
about what note I am talking about.
Important to mention is that the sum of all BS Label Column, BS Input Column and BS Note Column has to sum up to 12 for
each row. Now I might think that in our case it only sums up to 5 and I made an error. But earlier I mentioned that we
want the start and the end date on the same row. So if for example we have 2 form elements in the same row the sum of
all BS Label Columns, BS Input Columns nad BS Note Columns have to add up to 12. That's why I didn't made a mistake so far.

Second thing to mention: We also disabeld */row*. Usually if */row* is enabled, after the form element QFQ will create a new
row. But if we disable it the next form element will be on the same row. That's exactely what we want.

Details: https://docs.typo3.org/typo3cms/drafts/github/T3DocumentationStarter/Public-Info-053/Manual.html#multiple-elements-per-row

Now let's edit the end date form element. First go to the basic pill and change the label:

 .. figure:: Images/FormEditorOnline6.png

Next you go to the pill value and make almost the same changes as before:

 .. figure:: Images/FormEditorOnline7.png

Here instead of disabling */row*, we diable *row*. The reason. We dont want that at the beginning of the form element a new
line is generated.

Next we want to create a first Upload form element. For first you have to know exactely where you want to save your files.
As this is no Typo3 Tutorial I am not going to expain that. So at that time I am assuming that you already know exactely where
the files should be saved.

Create a new from element of type upload:

.. figure:: Images/FormEditorUpload1.png

Next you have to go to the pill value and paste the following Query into the value textbox: ::

    {{SELECT SUBSTRING_INDEX(jo.pathFileNameDetail, '/', -1) FROM JobOffer AS jo WHERE id={{slaveId}} }}

and the follwing Query into the parameter textbox: ::

    slaveId={{id:R0}}
    fileDestination={{SELECT 'fileadmin/jobOffer/', jo.id, '-{{filename}}' FROM JobOffer AS jo WHERE jo.id={{id:R0}} }}
    sqlUpdate={{UPDATE JobOffer SET pathFileNameDetail = '{{fileDestination}}' WHERE id={{slaveId}} LIMIT 1}}

    fileReplace=always

Details: https://docs.typo3.org/typo3cms/drafts/github/T3DocumentationStarter/Public-Info-053/Manual.html#type-upload

Next we want to give the admin the opportunity to write a little description about the job. But the admin should be
able to create a good looking textbox where words can be bold or italic, the admin should be able to chose the font etc.
So we dont want to only give him a regular textbox. We want to create a form element of the type editor:

.. figure:: Images/FormEditorEditor1.png

We want that the textbox is a little bigger than usual. So we go to the pill layout and chose the size 200,600:

.. figure:: Images/FormEditorEditor2.png

Next we go to the pill value and paste the following into the textbox parameter: ::

    editor-plugins=code link lists table textcolor textpattern visualchars

    editor-toolbar=code searchreplace undo redo | styleselect link table | fontselect fontsizeselect | bullist numlist outdent indent | forecolor backcolor bold italic

    editor-menubar=false
    editor-statusbar=false

It is not important to know what every line of code does. The important thing is that this is the code which makes the
editor look good in the form.

The last thing we want to add is a simple textbox where the contact email address for that specific job is stored.

.. figure:: Images/FormEditorEmailContact.png

Now your form should look something like this:

.. figure:: Images/FormEditorJobOffer1.png

Now we need a page from which this form can be accessed. So in the admin area we want to create a page where we can edit
job Offers, create new and delete old. Moreover we should have a list of all current job Offers.

For that purpose we want to create a new Typo3 page in the admin panel. As always we want to give the page a useful title
and don't forget to add a page alias. Moreover we have to enable the page and add a QFQ element. I am not going to
do that step by step as it already has been explained earlier.

You can paste the following Query into the QFQ element: ::

    form={{form}}

    10 {
      sql = SELECT CONCAT('{{pageId:T}}&form=jobOffer&r=0') as _Pagen FROM  (SELECT 1) AS fake WHERE  '{{form:SE}}'=''
    }
    20 {
      sql = SELECT CONCAT('{{pageId:T}}&form=jobOffer&r=',jo.id) as _Pagee, jo.id, jo.type, jo.title, jo.start, jo.end, jo.description, jo.emailContact FROM JobOffer AS jo, (SELECT 1) AS fake WHERE  '{{form:SE}}'='' AND jo.start<=NOW() AND NOW()<= jo.end
        head = <table class='table table-hover qfq-table-50'><thead><tr><th>Form</th><th>#</th><th>Type</th><th>Title</th> <th>Start</th><th>End</th><th>Description</th><th>Email</th></tr><thead><tbody>
        tail = </tbody></table>
        rbeg = <tr>
        rend = </tr>
        fbeg = <td>
        fend = </td>

    }

Since this is all either usual SQL or already explained QFQ notation, i am going to leave it as an exercise for you to
understand this code.
It might be better if instead of copying my code, trying to do it first on your own.

Next we want to create a page where potential applicants can apply. We can use almost the same Query for this new page
than for the existing admin page.

As as always create a new Typo3 page and add a QFQ element. You can paste the following Query into the QFQ Element: ::

    form={{form}}

    10 {
      sql = SELECT CONCAT('u:{{pageId:T}}&form=Application&r=0','|t:Apply|b:1|s:1') as _link, jo.id, jo.type, jo.title, jo.start, jo.end, jo.description, jo.emailContact FROM JobOffer AS jo, (SELECT 1) AS fake WHERE  '{{form:SE}}'='' AND jo.start<=NOW() AND NOW()<= jo.end
        head = <table class='table table-hover qfq-table-50'><thead><tr><th>Form</th><th>#</th><th>Type</th><th>Title</th> <th>Start</th><th>End</th><th>Description</th><th>Email</th></tr><thead><tbody>
        tail = </tbody></table>
        rbeg = <tr>
        rend = </tr>
        fbeg = <td>
        fend = </td>

    }

Here we see the following statement which might be new for you: ::

    CONCAT('u:{{pageId:T}}&form=Application&r=0','|t:Apply|b:1|s:1') as _link

Until now whenever we wanted to create a button to open a form, we always worked with *_Pagen* or *Pagee*. These are most
of the time good enough. But this time we needed something more. We needed a button with the word "Apply" on it.
For that purpuse we used the button *_link* insted of *_Pagee*. One more thing you might find confunsing are the qualifier.
We well go throw all qualifiers used in this example:

u: The u qualifier is used in the first part of the link *(u:{{pageId:T}}&form=Application&r=0')*. The u qualifier just
tells QFQ that whatever comes in between u: and | is going to be the link.

t: Whatever comes in between t: and | is going to be written on the button.

b: For b you have a few choices. b:1 just means that you render a button, b:danger would render a red button. These are
just bootstrap buttons.

s: *s:0* means no sip, *s:1* means a sip.

Details: https://docs.typo3.org/typo3cms/drafts/github/T3DocumentationStarter/Public-Info-053/Manual.html#column-link

Now the problem is that when we click on the link, the application form is called. But we don't know for which Job
the applicant wants to apply. That's why we have to modify the form application a little bit.
We add a new column to the Table Application. In this new Column we are going to store for which job the person just applied.
Here the SQL Query to add the new Column: ::

    ALTER TABLE `Application` ADD `joId` int(11) NOT NULL AFTER `id`

As always we have to make that change also in the visualisation of the database. But this time we also want to make a new
connection between the table JobOffer and the Table Application:

.. figure:: Images/DiaAddJoId.png

So you might ask yourself, why we just did that. We want the following: Whenever we click on the apply button, our
goal is of course to know for which job this person just applied for. So we have to save in the database this information.
The way we do that is the following. Let's suppose you are a user of this website and you want to apply for a job.
Your first step is going to be to click on the apply button on the Page "Jobs" for whatever job you are best suited for.
But if you click on that button "Apply", we automatically open up the form Application. But we want that the form Application
knows exactly for which Job we want to apply for. So how do we do that?
We just check on which "Apply" buttons you click. We take the parameter joId (which is the unique id of the job) as a
parameter for the form. So the form knows which job you are applying for and saves that automatically in the database.

If you don't know what I am talking about, don't worry. We try to go though this together.
If we click on the button apply now, the form is not going to know for which job we are applying for. So we want to change
that. In order to do that go to Typo3 and edit the QFQ element of the page Jobs. Delete the Query and paste in the
following Query: ::

    form={{form}}

    10 {
      sql = SELECT CONCAT('u:{{pageId:T}}&form=Application&r=0&joId=',jo.id,'|t:Apply|b:1|s:1') as _link, jo.id, jo.type, jo.title, jo.start, jo.end, jo.description, jo.emailContact FROM JobOffer AS jo, (SELECT 1) AS fake WHERE  '{{form:SE}}'='' AND jo.start<=NOW() AND NOW()<= jo.end
        head = <table class='table table-hover qfq-table-50'><thead><tr><th>Form</th><th>#</th><th>Type</th><th>Title</th> <th>Start</th><th>End</th><th>Description</th><th>Email</th></tr><thead><tbody>
        tail = </tbody></table>
        rbeg = <tr>
        rend = </tr>
        fbeg = <td>
        fend = </td>

    }

The only difference from before to now is the following: we changed the link from : ::

    CONCAT('u:{{pageId:T}}&form=Application&r=0|t:Apply|b:1|s:1') as _link

to: ::

    CONCAT('u:{{pageId:T}}&form=Application&r=0&joId=',jo.id,'|t:Apply|b:1|s:1') as _link

So now you can see that we added a new parameter to the link. The jo.id so the unique id which tells the form exactly
for which job the applicant just applied for.

So our next step is to edit the form in a way that the job title is displayed at the top of the page and additionally
stores the data into the database. So you have to edit the form Application. Add a new FormElement and and make a
readonly textbox out of it:

.. figure:: Images/FormElementReadonlyTextbox.png

Next go to the pill value and paste into the textbox value the following Query: ::

    {{SELECT jo.title FROM JobOffer AS jo WHERE {{joId:S0}} = jo.id}}

This Query just selects the title of the Job which has the id of the job, the user just applied for.
A confusing part of this Query might be the follwoing: *{{joId:S0}}*.
This expression is a variable in QFQ. joId is just the name of the parameter we opened the form with and the S0 means
that it looks for that information in the sip store.

Now the form is going to look like this:

.. figure:: Images/FormEditorReadonly2.png

So we see that when we open the link now the form knows exactly for which job you applied for. But we want that the
job is at the top of the Form. So we edit the FormElement and go to the pill Check&Order and set the order to 5. Then
this element will be on top.

.. figure:: Images/FormElementOrder.png

But our work is not done. We don't just want that the job type is shown at the webpage. We also want that information
stored in the database (in the Table Application).
So you have to create a new FormElement and of the Type Extra.

Details: https://docs.typo3.org/typo3cms/drafts/github/T3DocumentationStarter/Public-Info-053/Manual.html#type-extra

.. figure:: Images/FormEditorExtraJoId1.png

Go to the pill value and paste in the following Query into the textbox value: ::

    {{joId:S0}}

Now whenever somebody wants to apply for a job, we can see in the database for which job he acutally applied for.

But now we have the following problem: If now we go to tha page applications (that's the page where the admin of the page
can see all the applications) the admin can't see who applied for which job. So we want a better looking page where the admin
has a seperate list for all jobs which are offered. We additionally only want to display the job offers for jobs, which are
still online. So let's change the query in such a way. But first you should add a few jobs and a few applications so that
you see a difference to before.

So now your application page will look like this:

.. figure:: Images/Typo3ApplicationCathegories.png

Delete your query on the page Applications and paste in the follwoing: ::

    form={{form}}
    10 {
      sql = SELECT CONCAT('{{pageId:T}}&form=Application&r=0') as _Pagen, '<br><br>' FROM  (SELECT 1) AS
      fake WHERE  '{{form:SE}}'=''
    }
    20 {
      sql = SELECT jo.id AS _joId, jo.title AS _joTitle FROM JobOffer AS jo WHERE
      jo.start <= NOW() AND NOW() <= jo.end

      10 {
        sql = SELECT CONCAT('{{pageId:T}}&form=Application&r=',a.id) as _Pagee, a.id, a.name,
        a.firstname, a.gender,  a.dateOfBirth, a.adrStreet, a.adrZipCity FROM Application AS a,
        (SELECT 1) AS fake WHERE '{{form:SE}}'='' AND a.joId = '{{joId:R}}'
        head =  <table class='table table-hover' style="width: unset"><b>{{joTitle:R0}}</b>
        tail = </tbody></table>
        rbeg = <tr>
        rend = </tr>
        fbeg = <td>
        fend = </td>

      }
    }

Since everything should be clear in this query I am not going to explain it in detail. But you should still read it and
try to understand it. Even better: try it yourself without copying my code.

Next we want that the applicant can upload his CV and his letter of motivation. Now we run into a problem:
Unfortunately we only have one row named pathFileName in the table Application. So if we want that the user can upload 1
single file, we are fine. The reason: We can save the path in the row pathFileName. Now we want that the user can upload
2 different files. A CV and the letter of motivation. So you could add a new row and call the one pathFileNameCV and the
other one pathFileNameLetterOfMotivation. But what if one day a third upload file should be handed in. For example
certificate of employment or language certificates. In this case it is a bad idea to just create a new row for every
single upload.
We will do it way more dynamic. We will create a new Table named Ggroup. In this Ggroup we are going to save all path.

To create that now Table named Ggroup paste in the following query into the SQL prompt: ::