Skip to content

Data Sources

We have the following data sources:

  • Prato
  • Deltaworx Platform (usually referred to just as "the Platform")

During the consulting we also talked about other data sources, not included in this project.

  • Yuki (accounting)
  • Leadcamp (ERP)
  • Google Analytics
  • Meta ads

There are also have a lot of google sheets, they use Google Workspace for everything.
You can connect google sheets with Power BI via connector.

Prato

  • Prato(Flex) is an ERP, invoicing and payrolling solution specialized for recruitment agencies.

  • It is a SaaS solution.

  • del uses it to register the hours of the students and to send invoices to clients.

  • They have a REST API

Registration with the API

We use the key from #del, provided from Tyfun. Check out planio for details.

We also have a user from Jamy to access the web app. Again, see planio.

Data

We need invoices and hour data from prato flex.
All data is available from the REST API.

Technology

Deltaworx already has a contract (Mid-Office Optie Basic API) to get the data from Prato into their own app.
We can use the same API keys.

API General Documentation

https://deltaworx.prato.be/webservice/swagger/ui/index#/

If questions, contact lead developer Delta: tayfun@deltaworx.eu

Entities

Invoice Lines

Invoices per week:

del-invoice-lines.png

Or, per day:

del-invoice-lines-per-day.png

The code (e.g. 201, 684) is the Looncode, i.e. the code for Codesoort = 1000.

Salary Lines

del-salary-lines.png

  • The code in the Prestaties (top part, e.g. 5.33AD) is a DagCode
  • The code in the Pemies (bottom part, e.g. 2000) is a LoonCode

We can get a list of prestaties from reports > Detail

del-detail-salarylines.png

Codes

In Prato, Codes are stored generically, with a code type (Codesoort).

You can find the codes in Prato in Menu > Beheer > Onderhoud code

del-codes.png

There are dozens of code types. For this project, the following are important:

  • 1000: Looncodes (Salary Codes)

  • 2000: DagCodes

There is an endpoint, e.g.:

https://deltaworx.prato.be/webservice/integration/codes?kind=1000&language=n
Looncodes (Codesoort = 1000)

This is (especially in the consulting) sometimes referred to as Salary Codes.

del-looncodes.png

DagCodes (Codesoort = 2000)

This is also referred to as Facturation Codes.

del-dagcodes.png

del-salary-code.png

Sekties (Codesoort = 55)

This describes the Job Sector.

del-sekties.png

TAX = Tax Percentage

del-prato-invoice.png

del-prato-invoice-2.png

We need them to calculate the actual salary costs. Each Sektie has a different cost that needs to be paid by Deltaworx for social security, according to the tax table:

del-salary-codes-sekties.png

Cost

Cost: match salary line for cost same student same day same salary code

del-prato-salary-line-overview.png

del-prato-salary-line.png

  1. Take invoice line

  2. Find the respective salary line, matching:

    • student
    • day
    • salary code <!-- QUESTION: That's not correct, right? We have many more codes on the Salary than on the invoice, e.g. not all salary codes are charged to the client, e.g. dimona cost>

  3. Get tax percentage from "tax table" based on:

    • employee code
    • salary code

  4. Multiply the taxes to the salary e.g. €10 * 1.15 = €11.5

Tax Table

del-taxtable.jpg

NOTE by Christoph: As discussed in the call on 2022-11-02, these should be resolved.
- Add Sub-division per Job in Platform (Payroll, Ad hoc, Stable, Talent-based)
- Planning Consultant: Who assigned a student to a shift? → This is the qc_consultant

Deltaworx Platform

  • The Deltaworx platform is the beating heart of the operations, it is the bridge between the students and the clients.

  • For example, #del uses it to:

    • Planning Consultant enters the job details into the platform
    • Job appears in the app
    • Student sees the jobs in the app that matches with his/her skills
    • Student applies for the job in the app
    • Planning Consultant assigns student to the job
    • Student confirms in the app and is allocated a shift
    • Admin creates day contract, also in app or in Prato?

Registration

This requires lots of secrets, passwords and keys. See planio for details.

We will need a user to look around in the app as an admin.
We could also install the app and have a look as a student.

Data

Jobs, shifts, students, skills, locations, clients...

  • vacancy: contains the vacancies, and once converted the shifts

  • fos_user: contains all the users of the app (including students). The table users is empty.

<!--

Legend

Value (left) Value (right) Meaning
|o o| Zero or one
|| || Exactly one
}o o{ Zero or more (no upper limit)
}| |{ One or more (no upper limit)

-->

Tables:

  • project:

  • vacancy:

    • is_open = true: not filled

  • student

  • job_function

    • name (Parkingboy, Kitchen Aid, etc.)

  • jobs: this is the job description that will be shown in the app. There is lots of HTML in there.

    • name
    • → client
    • state: draft, finished, polished

  • office: the deltaworx offices

    • title
    • city (Brussels, Ghent, Antwerp, etc.)

  • client

    • → office
    • → responsible_consultant
    • business_number
    • address

  • consultant

    • → office

  • locations

    • per client
    • city is always null

  • base_timeslot

  • skill

    • "Handle three plates"

Technology

The platform data ist stored in a postgres database in AWS.
If questions, contact lead developer Delta: tayfun@deltaworx.eu

Excel

Tax Table

An Excel table, stored in Sharepoint, containing:

Sekties Code (55) Dagcode (2000) Tax Rate
115 AD 15%

QUESTION: Jaimy provided in a mail on 2022-11-04. However, she wanted to check with their tax advisor. Also, Tax rates don't seem to depend on the Dagcode, but only on the Sektie.