¶ 1. Overview
There is a high demand for data integration between the various platforms and systems available in the market. T6 Enterprise enables this integration through the use of WebServices or REST API, transferring data in JSON format in three ways:
-
Retrieve data from an API/REST: Consuming data from a service provided by the client/supplier.
-
Send data to an API/REST: Encapsulating the data stored in T6 Enterprise and sending it to a service provided by the client/supplier.
-
Provide data for consultation: Offering a data structure through our API of forms so that a service can consume the data as needed.
To view the complete list of T6 APIs, we can use Swagger, for information on configuration and use, visit our help center: Swagger
Internally in T6 Enterprise, data loading (ETL) is created through our workflow process, allowing various controls, email notifications, and user interaction. Below is an example of a data loading flow:
¶ 2. DataloadREST
The tool we provide to interact with any external API/REST to T6 is DataloadREST. Through it, we can trigger calls using various protocols, methods, and authentications, allowing us to consume data or send data to WebServices available on the network.
¶ 2.1. GET Request
By default, the service uses the POST method, but it is also possible to use the GET method by passing the parameters concatenated in the URL as shown in the example below. As in the previous case, the insert is within a Stored Procedure, which dynamically concatenates the year and month in the URL.
{
"Url": "https://apiqas.cliente.com.br:6244/teste-e/sysphera/integracao?ano='+@ano+'&mes='+@mes
"RequestMethod": "GET",
"AuthenticateType": "None",
"Body": ""
}
¶ 2.2. Basic Authentication
This is the simplest configuration, using the POST method. It includes the service URL, the basic authentication method, username, password, and the parameters passed in the body (classic POST method passing):
{
"Url": "http://sapqas.cliente.com:8001/sysphera/sysphera_dados?sap-client=300",
"RequestMethod" : "POST" ,
"AuthenticateType": "BASIC",
"UserName": "SYSPHERA",
"Password": "agrC4DkbyJuHSFwCU@",
"Body": "{
\"I_ANO\": \"2019\",
\"I_MES\": \"02\"
}"
}
¶ 2.3. Request Without Authentication or Included in the Service (URL)
When the service does not require authentication, set the parameters to None.
{
"Url": "https://api.cliente.com.br:6244/sysphera/listar/volume",
"AuthenticateType": "None",
"UserName": "None",
"Password": "None",
"Body": "{
\"ANO\": \"2019\",
\"MES\": \"01\"
}"
}')
¶ 2.4. Request with Parameter Passing in "URL Encoded" HEADER
In this example, there is a need to include some information in the header, such as Content-Type and some parameters using URL Encoded.
{
"Url" : "https://api-homologacao.cliente.com.br/credenciamento/auth/oauth/v2/token" ,
"AuthenticateType" : "BASIC" ,
"UserName" : "0fd71627-a1a1-b1b1-9cd4-69c6ef34fb74" ,
"Password" : "e34af389-6054-9958-a8c4-c304244c9b81" ,
"RequestMethod" : "POST" ,
"Header": "Content-Type:application/x-www-form-urlencoded",
"Body": "scope=onboarding-sap&grant_type=client_credentials"
}'
¶ 2.5. Request Passing a Cookie
There are cases where it is necessary to pass the TOKEN within a Cookie, which is located in the Header.
{
"Url": "https://servicos.cliente.com.br/isw/api/v1/siteware/acompanhamentoFinanceiroProjeto",
"RequestMethod": "POST",
"Header": "Cookie: iPlanetDirectoryPro=\"My00YTk0LTljZDQtNjljNmVmMzRmYjc0OmUzN\"; Content-Typ
"Body": "[{\"ano\": 2020, \"codigo\": 900001, \"despesaOrcado\": 1.50, \"despesaRealizado\":
}
¶ 2.6. POST Request Sending Data
Sending data to an API follows the same messaging logic; the only difference is that the JSON with the data to be sent will be in the Body.
{
"Url": "http://sapqas.cliente.com:8001/sysphera/sysphera_getdata",
"RequestMethod" : "POST" ,
"AuthenticateType": "BASIC",
"UserName": "SYSPHERA",
"Password": "agrC4DkbyJuHSFwCU@",
"Body": "[
{\"ano\": 2023, \"codigo\": 900001, \"despesaOrcado\": 1.50, \"despesaRealizado\": 1.60, \"mes
{\"ano\": 2023, \"codigo\": 900002, \"despesaOrcado\": 2.40, \"despesaRealizado\": 2.30, \"mes
{\"ano\": 2023, \"codigo\": 900003, \"despesaOrcado\": 3.20, \"despesaRealizado\": 3.50, \"mes
{\"ano\": 2023, \"codigo\": 900004, \"despesaOrcado\": 1.80, \"despesaRealizado\": 1.60, \"mes
...
]"
}
¶ 2.7. oAuth Request
The service allows the use of the oAuth protocol. This protocol functions as if there were two separate calls, one for authentication and another for the actual service, but everything is configured in a single execution as shown in the example below.
{
"Url": "https://dev-api.cliente.com.br/sb/dados-contabeis/v1/contas?Periodo=01/01/2020",
"AuthenticateType": "oAuth",
"Header": "client_id:5ca627ad-a1a1-3680-87ac-0ededaffc59d; access_token:{{oAuthAccessToken}};
"RequestMethod": "POST",
"UserName": "",
"Password": "",
"Body":"",
"oAuthAuthEndpoint": "https://api.cliente.com.br/oauth/grant-code",
"oAuthAuthRequestMethod": "POST",
"oAuthAuthRequestHeader": "Content-Type:application/json",
"oAuthAuthRequestBody": "{\"client_id\": \"5ca627ad-608a-3680-87ac-0ededaffc59d\", \"redirect_
"oAuthAuthRequestAuthenticateType": "",
"oAuthAuthRequestUserName": "",
"oAuthAuthRequestPassword": "",
"oAuthTokenEndpoint": "https://api.cliente.com.br/oauth/access-token",
"oAuthTokenRequestMethod": "POST",
"oAuthTokenRequestHeader": "Content-Type:application/x-www-form-urlencoded",
"oAuthTokenRequestBody": "grant_type=authorization_code&code={{oAuthGrantCode}}",
"oAuthTokenRequestAuthenticateType": "Basic",
"oAuthTokenRequestUserName": "5ca627ad-608a-3680-87ac-0ededaffc59d",
"oAuthTokenRequestPassword": "87d147d1-a607-3d0f-9de2-78d9a11c0fe8"
}')
¶ 3. REST API for Data Forms
T6 Enterprise offers a second way to make data available for client consumption through a REST API embedded in each window-type data form. There are two authenticated API calls using a token. The first API returns the metadata of the query, including the number of rows and column descriptions, while the second API returns the dataset with the form's data. The first call is not mandatory, and the second API (offset) allows pagination when dealing with a large volume of data.
¶ 3.1. Access Token Configuration
To utilize the APIs, an admin user with multi-application privileges needs to create an access token. This should be done during user creation, in the Services tab as shown in the figure below.
The token is a set of characters that can have an expiration date and will allow access to the form APIs. It will authenticate instead of using a username and password. This token can have an expiration period and can be revoked/deleted at any time.
¶ 3.2. Data Form
The application managers will define which form(s) will be used to access the data via the API. Any window-type form can be created, regardless of whether it was created through a data table or through a query.
The important information to use in the API is the form code as shown below:
¶ 3.3 Using the Execute API
The API used is "Services/Execute". It is of type POST and has a JSON body with the configurations we will use.
From your address on Tech6Cloud, concatenate "/api/Services/Execute" at the end. Below is an example from our DEV training environment:
https://dev-training.tech6cloud.com/api/Services/Execute
The JSON has two or three parameters:
- URL: API to be called;
- Token: the created token to be used for access
- Data: Optional. When calling a POST API and we need to pass something in the BODY.
¶ 3.4. CreateOrGet API
This is the first API we will call. In it, we will have a list with the names of the columns we will access and the number of rows existing in the Dataset.
For this call, we won't need the data parameter, we will only use the URL and Token.
The URL will be Worksheet/CreateOrGet?type=dataform&typeCode=1996, where the typeCode (in this example 1996) is the code of the data form to be consumed.
{
"Url":"Worksheet/CreateOrGet?type=dataform&typeCode=1996",
"Token":"1fae0b70e3d24d88b67208301b1c1eb1"
}
Below is the API call and the response, showing that the form has 51 rows and the column headers.
By default, the Content-Type will come with the Text/Plain type. Since the Body contains a JSON, we need to change the Content-Type to Application/JSON. Otherwise, the following error will occur: 415 - Unsupported Media Type.
¶ 3.5. API Offset
The Offset API is effectively used to consume the data. Depending on the data volume, it can be paginated because, in addition to the data form code (typeCode), we also have as attributes the initial row and the number of rows we will retrieve.
The URL will be Worksheet/Offset?code=&type=dataform&typecode=1996&start=0&length=20&metadata=false. In this example, I am starting from the first row "0" and retrieving only 20 rows out of the total 51.
In this call, the Data parameter in the Body is required and will be fixed, always passed as follows:
"Data":
{
"Url":"Worksheet/Offset?code=&type=dataform&typecode=1996&start=0&length=20&metadata=false
"Token":"1fae0b70e3d24d88b67208301b1c1eb1",
"Data":{"Comparator":0,"Operator":0,"Filters":[]}
}
Below is the API call returning the data. The response will always be in the same format of a linked list with ROWS and COLUMNS.
Note that we start with the row RowIndex=0 and then all its columns ColumnIndex=0, 1, 2... The value is what is in the VALUE field. That is, the cell {0,5} has the value "10".
In this example above, checking in the form what we have in ROW=0 and COLUMN=5, we have the value 10.
¶ 3.6. Parameter Values in APIs
To locate the parameter values of an API, we will use a tool to capture network traffic during the execution of T6Enterprise. This will allow us to view the values being used during actions performed in the system. In this example, we will use the API Offset and demonstrate how to find the necessary parameters to apply filters through the Data field in the API's request body.
To apply filters in the Offset API, we need a tool to capture network traffic (in this example, we will use Fiddler Classic).
- Run Fiddler;
- Access T6Enterprise in your browser while Fiddler is running;
- Select a Data Form object that will be used in the API call;
- The object's code will be needed in the API's request body.
- Access the form, and in the ribbon, click Filters;
- A new side panel will be displayed, where you can filter the values you want;
- After modifying the filters as desired, return to Fiddler to locate the API used when applying the filters, as the information generated will be necessary to complete our request body.
- When accessing Fiddler after modifying the filters in the data form, locate the triggered API in the list on the left and follow the steps as shown in the image below:
- Step 1: Select the API starting with
/api/Worksheet/Offset...by double-clicking it; - Step 2: Select the Inspectors tab;
- Step 3: Within the Inspectors tab, select the Raw tab;
- Step 4: In the last line, you will find the information needed to complete your request body. Copy the entire line and then insert it into the Data field of your API's request body.
{
"Url":"Worksheet/Offset?code=&type=dataform&typecode=3232&start=0&length=20&metadata=false
"Token":"1fae0b70e3d24d88b67268302b1d1eb1",
"Data":{"Comparator":0,"Operator":0,"Filters":[{"Comparator":0,"Operator":0,"Filters":[],"Code":"413|codUser","Value":"admin"},{"Comparator":0,"Operator":0,"Filters":[{"Comparator":0,"Operator":0,"Filters":[],"Type":"column","Code":"1026","Value":"Brindes"}],"Type":"column","Code":"1026"}]}
}
This way, when executing the API, you will receive a response containing only the data that matches the applied filters.