Interface description

Importing the key values

The easiest and fastest way to import key values into maXzie is the interface via REST API. This interface is available both when operating in the cloud and on-premises. On-premises, there is also the option of importing directly from the database.

Import using the REST-API

To import performance values, CSV files containing the required values can be uploaded to an HTTP REST endpoint. maXzie provides a URL for each configured performance indicator to which data can be sent via HTTP POST. The URL is composed as follows:

https://<maXzie_url>/api/v1/leistungskennzahlen/<UUID>/import

This URL with inserted UUID can be retrieved in the settings of the performance indicators.

Performance indicator settings

Cumulate

If the item “Cumulate” is not activated in the definition of the performance indicator, the already cumulated values must be imported for each period.

If the item “Cumulate” is activated in the definition of the performance indicator, the non-cumulated values must be imported for each period. maXzie then adds up the values for the individual periods.

Token

For a successful import of the values via the maXzie API the passwords (“tokens”) configured in maXzie for the respective performance indicators must be passed in a special header. The header has the following format:

Authorization: LKZ <UUID> <Password>

Here <UUID> corresponds to the unique identifier and <Password> to the password (“Token”) of the corresponding performance indicator configured in maXzie.

Query-Parameter

`accumulation_timeframe`

Empty parameter

The query parameter ‘accumulation_timeframe’ is omitted if individual values per employee and period are transferred and no totals should be formed for the individual time periods.

Without the query parameter ‘accumulation_timeframe’, there may only be one value per employee and per required time period. If, in this case, no line is transferred for a required partial period for an employee, any existing values will not changed.

Set parameter

When using the query parameter accumulation_timeframe, the URL is composed as follows:

https://<maXzie_url>/api/v1/leistungskennzahlen/<UUID>/import?accumulation_timeframe=from```to

Here from and to are given in ISO-8601 format (YYYY-MM-DD), for example “2020-01-31”.

The query parameter accumulation_timeframe restricts the import to the specified total period. Totals are also formed for partial periods that are only partially within the total period passed by the query parameter (see also example scenario 6). If the parameter accumulation_timeframe is set and no row is transferred for a required partial period for an employee or group, this results in a total of 0 for the employee and period.

`skipRows`

To skip rows at the beginning of the transmitted file, the query parameter skipRows can be used with the number of rows to be skipped. For example, if there is a header row with headings, skipRows=1 can be used. If the parameter is omitted, no rows are skipped.

CSV files

CSV files must use UTF-8 character encoding without signature/BOM. Semicolons (“;”) are expected as column separators and dots (“.”) as decimal separators. Thousands separators are not used. Dates are transmitted in ISO-8601 format (YYYY-MM-DD), for example “2020-04-01”.

Global/Companywide performance indicators

For performance indicators without assignment of values to individual employees or groups the following columns are expected:

Column	Title	Example
1	Date	`2020-01-28`
2	Value	`12345.67`

Each value is assigned to the period that contains the date specified in column 1. Each date within this period can be used equally without distinction.

An example CSV file would look like this:

2020-01-31;47.0215
2020-02-28;4198.42
2020-03-31;0.00123

Performance indicators with group assignment

For performance indicators with assignment of values to groups, the following columns are expected:

Column	Title	Example
1	Date	`2020-01-28`
2	Group import key	`group_sales`
3	Value	`12345.67`

Group import keys are entered in the core data. A value is assigned to the group whose core data group import key corresponds to the group import key transferred in the line.

An example CSV file would look like this:

2020-01-31;group_sales;47.0215
2020-02-28;group_office_service;4198.42
2020-03-31;group_field_service;0.00123

Performance indicators with employee assignment

For performance indicators with assignment of values to employees, the following columns are expected:

Column	Title	Example
1	Date	`2020-01-28`
2	Employee key	`P101`
3	Value	`12345.67`

Employee keys are entered in the core data. A value is assigned to the employee whose core data employee key corresponds to the employee key transferred in the line.

An example CSV file would look like this:

2020-01-31;P2000284;47.0215
2020-02-28;P2487684;4198.42
2020-03-31;P0000001;0.00123

Example scripts

Importing performance values via the maXzie API can be done as follows:

Import with curl on Linux

URL="https://<maXzie_url>/api/v1/leistungskennzahlen/<uuid>/import"
HEADER="Authorization: LKZ <uuid> <password>"
curl -k -X POST -H "Content-Type: text/csv" -H "$HEADER" ```data-binary "@<import_file>" "$URL"

Import on Windows (Powershell)

[System.Net.ServicePointManager]::ServerCertificateValidationCallback = {$true}
$AllProtocols = [System.Net.SecurityProtocolType]'Tls12'
[System.Net.ServicePointManager]::SecurityProtocol = $AllProtocols

$url='https://<maXzie_url>/api/v1/leistungskennzahlen/<uuid>/import'
$header=@{'Authorization'='LKZ <uuid> <password>'}
$ep=Get-ExecutionPolicy

Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope CurrentUser
Invoke-WebRequest -Uri $url -Headers $header -ContentType 'text/csv' -Method POST -InFile <import_file>
Set-ExecutionPolicy -ExecutionPolicy $ep -Scope CurrentUser

Other

If values are imported for time periods that are already filled with values, previous values for this time period and, if applicable, for this employee or group will be overwritten.

Example scenarios

Scenario 1: “Cumulate” not set, accumulation_timeframe not set

Performance value:
- Cumulate: not set
- Reporting timeframe: monthly, starting at 01.01.2020
- Required parameter: None (global performance indicator)
Query-Parameter:
- accumulation_timeframe: not set

CSV-File:

2020-01-31;10
2020-02-28;15
2020-03-31;25

Since “Cumulate” is not set for the performance indicator, the values are taken over individually for each month and are not cumulated. Since accumulation_timeframe is not set, there may be at most one value for each month, as there is no accumulation within months. The resulting values in maXzie from this import are:

Timeframe	Value
January 2020	10
February 2020	15
March 2020	25

Scenario 2: “Cumulate” set, accumulation_timeframe not set

Performance value:
- Cumulate: set
- Reporting timeframe: monthly, starting at 01.01.2020
- Required parameter: None (global performance indicator)
Query-Parameter:
- accumulation_timeframe: not set

CSV-File:

2020-01-31;10
2020-02-28;15
2020-03-31;25

Since “Cumulate” is set for the performance indicator, the values are cumulated from month to month by maXzie. Since accumulation_timeframe is not set, there may be at most one value for each month, as there is no accumulation within months. The resulting values in maXzie from this import are:

Timeframe	Value
January 2020	10
February 2020	25
March 2020	50

Scenario 3: “Cumulate” not set, accumulation_timeframe set

Performance value:
- Cumulate: not set
- Reporting timeframe: monthly, starting at 01.01.2020
- Required parameter: None (global performance indicator)
Query-Parameter:
- accumulation_timeframe: set as
  - from: 2020-01-01
  - to: 2020-03-31

This configuration is currently not supported.

Scenario 4: “Cumulate” set, accumulation_timeframe set

Performance value:
- Cumulate: set
- Reporting timeframe: monthly, starting at 01.01.2020
- Required parameter: None (global performance indicator)
Query-Parameter:
- accumulation_timeframe: set as
  - from: 2020-01-01
  - to: 2020-03-31

CSV-File:

2020-01-15;3
2020-01-20;7
2020-02-10;11
2020-02-23;4
2020-03-31;25

Since “Cumulate” is set for the performance indicator, the values are cumulated from month to month by maXzie. Since accumulation_timeframe is set, the values are added up within the individual months. The resulting values in maXzie from this import are:

Timeframe	Value
January 2020	10
February 2020	25
March 2020	50

Scenario 5: “Cumulate” set, accumulation_timeframe set, Performance indicator with employee assignment

Performance value:
- Cumulate: set
- Reporting timeframe: monthly, starting at 01.01.2020
- Required parameter: Employee (performance indicator with employee assignment)
Query-Parameter:
- accumulation_timeframe: set as
  - from: 2020-01-01
  - to: 2020-03-31

CSV-File:

2020-01-15;P101;3
2020-01-20;P101;7
2020-02-10;P101;11
2020-02-23;P101;4
2020-03-31;P101;25
2020-01-11;P102;3
2020-01-25;P102;2
2020-02-04;P102;6
2020-02-21;P102;4

The order of the rows in the CSV file is arbitrary. Since the values for the employee “P102” are missing for March, no values are imported here and the value in maXzie is set empty. If the first values are imported in April and the item “Past values should be completed” is activated for the performance indicator, a zero is set as the value for March. The resulting values in maXzie from this import are:

for employee “P101”

Timeframe	Value
January 2020	10
February 2020	25
March 2020	50

for employee “P102”

Timeframe	Value
January 2020	5
February 2020	15
March 2020	-

Scenario 6: “Cumulate” set, accumulation_timeframe set, Reporting timeframe weekly

Performance value:
- Cumulate: set
- Reporting timeframe: weekly, starting at 06.01.2020 (Monday)
- Required parameter: None (global performance indicator)
Query-Parameter:
- accumulation_timeframe: set as
  - from: 2020-01-01
  - to: 2020-02-29

CSV-File:

2020-01-15;3
2020-01-17;3
2020-01-21;4
2020-02-10;5
2020-02-13;6
2020-02-23;3
2020-02-28;1

Since “Cumulate” is set for the performance indicator, the values are cumulated from month to month by maXzie. Since accumulation_timeframe is set, the values are added up within the individual weeks (e.g. values from 2020-01-15 and 2020-01-17 are added up for the value of week 2).

Since week 8 (24.02.-01.03) is partly in the ‘accumulation_timeframe’ (01.01.2020-29.02.2020), values are also imported for this period. The resulting values in maXzie from this import are:

Timeframe	Value
CW1 2020 (06.01.2020-12.01.)	0
CW2 2020 (13.01.2020-19.01.)	6
CW3 2020 (20.01.2020-26.01.)	10
CW4 2020 (27.01.2020-02.02.)	10
CW5 2020 (03.02.2020-09.02.)	10
CW6 2020 (10.02.2020-16.02.)	21
CW7 2020 (17.02.2020-23.02.)	24
CW8 2020 (24.02.2020-01.03.)	25

Example Python script for import from Postgres database

#!/usr/bin/python3

import psycopg2
import datetime
from psycopg2.extensions import AsIs
from requests.auth import HTTPBasicAuth
import csv
import requests

host = "{Host}"
user = "{Benutzer}"
database = "{databsename}"
password = "{password}"
url = "https://customer.maxzie.de/api/v1/leistungskennzahlen/{Leistungskennzahl-ID}/import"
token = "{token}"
currentDate = datetime.datetime(2021, 1, 17)

def get_CsvFromPostgresDatabase():
    connection = None
    try:
        connection = psycopg2.connect(
            database=database,
            user=user,
            host=host,
            password=password)
        connection.set_session(
            isolation_level=psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE)
        cursor = connection.cursor()
        sql_select = """SELECT personal_number, order_date, SUM(revenue)
                        FROM Order_Intake
                        WHERE EXTRACT(YEAR FROM order_date) = %(year)s
                        AND EXTRACT(MONTH FROM order_date) = %(month)s
                        GROUP BY personal_number, order_date"""
        cursor.execute(
            sql_select,
            {"year": currentDate.year, "month": currentDate.month})
        csvLines = []
        if cursor.rowcount > 0:
            rows = cursor.fetchall()
            for row in rows:
                line = row[1].strftime("%Y-%m-%d") + ";" + row[0] + ";" + str(row[2])
                csvLines.append(line)
    finally:
        if connection is not None:
            connection.close()

    return csvLines

def main():
    csvLines = get_CsvAusPostgresDatenbank()
    response = requests.post(
        url=url,
        data="\r\n".join(csvLines),
        headers={"Authorization": "LKZ {Leistungskennzahl-ID} "
            + token})
    print(response)
    print(response.text)

if __name__ == '__main__':
    main()

Import from Microsoft SQL Server or PostgreSQL

The import of performance values is realised via SQL queries, which are executed by maXzie in the customer’s database at regular intervals and read out the values required by maXzie there.

Queries and the times at which the queries are to be executed by maXzie are configured in the web interface for each performance indicator individually in its settings page. To do this, the automatic import must first be activated and the import from “Microsoft SQL Server” or “PostgreSQL” selected. The input fields “Trigger”, “URL”, “User”, “Password” and “Query” are then available.

Configuration fields “URL”, “User” and “Password”

The information of the configuration fields “URL”, “User” and “Password” enable maXzie to access a specific database of the client. Example specifications for “URL” are:

For SQL Server: jdbc:sqlserver://10.1.0.2:1433;DatabaseName=customerdata
For PostgreSQL: jdbc:postgresql://10.1.0.2:5432/customerdata

If necessary, further parameters are useful, e.g. for encryption of the data transmission. Further information can be found in the documentation of the database system.

Configuration field “Trigger”

The configuration field “Trigger” determines times and frequency of the database query. Cron expressions are used here. Example values are:

Import every night at 5 a.m.: * * 5 * * *
Import every Sunday at 6 a.m.: * * 6 * * 7
Import at every full hour: 0 0 * * * *

For more information and examples, see https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/scheduling/support/CronExpression.html#parse-java.lang.String-

Configuration field “Query”

In the configuration field “Query”, an SQL-SELECT query must be stored, which is executed in the specified database of the customer. This query has the task of reading out the values required by maXzie. The periods for which values are needed are contained in a temporary table created by maXzie. The query must link the other tables in the database to this temporary table to produce the required values as a result.

This temporary table with the time periods of the required values is created before the execution of the specified query and only exists within the transaction in which the specified query is executed. The name of the temporary table is set by maXzie, the placeholder #neededValues is replaced by the name of the temporary table at the time of the query. The structure of this table is as follows (created automatically by maXzie):

CREATE TABLE #neededValues (
    leistungskennzahlwert_id CHAR(36) NOT NULL,
    subjectId VARCHAR(255),
    fromYear INTEGER NOT NULL, fromMonth INTEGER NOT NULL, fromDay INTEGER NOT NULL,
    toYear INTEGER NOT NULL, toMonth INTEGER NOT NULL, toDay INTEGER NOT NULL
);

subjectId here identifies an employee or group if the required values are referring to an employee or group. The necessary fields must be configured by the {ni} during setup and are then available in the core data forms. When configuring the import in the performance indicators, the desired core data field can then be selected under “Employee mapping”.

fromYear, fromMonth, fromDay, toYear, toMonth, toDay refer to the time interval for which a value is to be determined by the query. Both designated days are still part of the time interval; a whole month is then expressed by the values (2015, 1, 1, 2015, 1, 31), for example.

The result of the query must contain two columns. The first column contains the value of performance_id from the temporary table described above. The second column contains a numerical value with the determined value (integer, fixed point or floating point number).

For example, a query for PostgreSQL might look like the following and could be entered into the “Query” configuration field like this:

SELECT nv.leistungskennzahlwert_id, (
    SELECT COALESCE(SUM(sub.amount), 0)
    FROM (SELECT o.amount, make_date(o.year, o.month, o.day) AS date FROM Orders o) sub
    WHERE sub.date BETWEEN make_date(nv.fromYear, nv.fromMonth, nv.fromDay)
                        AND make_date(nv.toYear, nv.toMonth, nv.toDay))
FROM #neededValues nv;

For example, a query for Microsoft SQL Server might look like the following and could be entered into the “Query” configuration field like this:

SELECT nv.leistungskennzahlwert_id, (
    SELECT COALESCE(SUM(sub.amount), 0)
    FROM (SELECT o.amount, DATEFROMPARTS(o.year, o.month, o.day) AS date FROM Orders o) sub
    WHERE sub.date BETWEEN DATEFROMPARTS(nv.fromYear, nv.fromMonth, nv.fromDay)
                        AND DATEFROMPARTS(nv.toYear, nv.toMonth, nv.toDay))
FROM #neededValues nv;

Exporting the payment data

The export of the payment data calculated by maXzie a csv- or excel-file can be downloaded in the web interface. The standard export file has the following format:

Last name	First name	Balance before payment	Recommended payment	Payment	Currency	Date
Zie	Max	1000,00	800,00	800,00	EUR	2024-01-01

The format of the file can be customized by the maXzie support.