Admin-IS / Admin-Consult API pour le vendeur de logiciels

Apres l'installation du Admin-IS / Admin-Consult API vous pouvez commencer.
L'article suivant est principalement destiné aux développeurs, qui peuvent commencer à travailler avec l'API

Important : Toujours inclure le bon jeu de caractères dans le header afin d'assurer une gestion correcte de "é" et d'autres encodages spécifiques à la base de données. Content-Type =>

"application/json;charset=UTF-8" 

Table des matières

Liens vers la Base

Data Filters

Paging

Data changes

Dynamic API

Liens vers la Base

Le comptable vous fournira l'URL et la clé pour utiliser l'API Consult. L'API Swagger est située sur le mapping « /doc ».

La première étape consiste à prendre un token avec la clé que vous avez obtenue. Vous pouvez également le tester avec Swagger :

Le résultat est un token valide (si le logiciel est activé).

Ce token est valable jusqu'au TimeToLive. Lorsque ce dernier est expiré, vous devez demander un nouveau token. Le token doit être utilisé dans les autres appels API.

Data Filters

Le résultat d'une API peut être filtré pour renvoyer un ensemble limité de données.

Ici, le fonctionnement des filtres est expliqué, cette opération technique est délibérément rédigée en anglais.

You can filter the result of the API's with large result sets f.i.:

• /api/v1/customers
• /api/v1/customer/changes
• /api/v1/dms/getdocuments
• /api/v1/expensedeclarations
• ...

The Swagger info shows which API support filtering:

 With parameters, you can filter the result of an API for one or more values, even if those values contain spaces or special characters. The basic syntax is fairly straightforward; start with the API URL, add a question mark, and then add your filter syntax.
URL?Filter=Field eq 'value'

e.g. http://consultapi.syneton.be:2100/api/v1/customers?Filter=IsCompany eq 1

• Fieldnames and value are case in
• Fields must be part of the returned modelg. IsCompany is part of the Customer model.

Field types

Field type can be a number, date, boolean or string and the type used must match the type set in the model. For example, specifying a field of type "string" won't work if you're looking for a date or numeric value in a result column set as a date, such as InvoiceDate eq ‘John’.

• Stringsmust be enclosed with single quotes, as in 'manager name'.
• Numbersrequire no special formatting.
• Datesformat is ‘yyyy-mm-dd’ e.g. ?Filter=InvoiceDate gt ‘2020-01-01’
• Boolean 1(=True), 0 (=False)

Filter on a field

Let’s assume that the URL to our report is the following.

http://consultapi.syneton.be:2100/api/v1/customers

And we see in our result above that we have Dutch speaking customers. NL is the value that represents Dutch in the Language field of the Customer model. To filter the report to show data only for Dutch speaking customers, we append this string to the URL:

?Filter=Language eq 'NL'

So the url becomes:

http://consultapi.syneton.be:2100/api/v1/customers?Filter=Language eq 'NL'

Where Language and ‘NL’ are case insensitive.

Filter on more than one value in a field

To filter on more than one value in a single field, you use the in operator instead of the and operator. The syntax is:

?Filter=Field in ('value1', 'value2')

Using the same example, to filter the result to show data only for customers speaking French (‘FR’) or English (‘EN’) append the URL with the following;

?Filter=Language in ('FR', ‘EN’)

Operators

The API filter supports many operators in addition to 'and'. The table below lists those operators along with the content type they support.

Operator	Definition	Example
and	And	InvoiceAmount le 3000 and InvoiceAmount gt 1000
eq	Equals	City eq 'Bornem'
ne	not equal	City ne 'Bornem'
ge	greater than or equal	InvoiceAmount ge 1000
gt	greater than	InvoiceAmount gt 1000
le	less than or equal	InvoiceAmount le 1000
lt	less than	InvoiceAmount lt 1000
in	Including	Age in (27, 29)
StartsWith(..., 'x')	Starts with	StartsWith (Name, 'Mi')
EndsWith(..., 'x')	Ends with	EndsWith (Name, 'ing')
Contains(..., 'x')	Contains *	Contains (Name, 'Van')
Like ‘X_Y’	Single character	Name like ‘P_L’

* The operator Like '%...%'  is deprecated, please use Contains (..., 'x').

Paging

Un dataset sera toujour psi en pages, avec ou sans filtre (voir ici en haut) 

Les details technique sont ecrit en Anglais.

Each result returns pages with a certain number of records that you can define in the settings of the Consult API. Here you can set both the default number of results per page and the maximum number of results per page. Requesting more records than the maximum record in one call, will return the default number of results.

The paging information is returned in the http header and can be used to create html hyperlinks:

<first> <prev> <next> <last>

<<    <     >     >>

The first request: http://consultapi.syneton.be:2100/api/v1/customers

http responde header link:

<http://consultapi.syneton.be:2100/api/v1/customers?page=2&per_page=20>; rel="next", <http://consultapi.syneton.be:2100/api/v1/customers?page=67&per_page=20>; rel="last", <http://consultapi.syneton.be:2100/api/v1/customers?page=1&per_page=20>; rel="first",

If you want the next page, use the rel="next" link being http://consultapi.syneton.be:2100/api/v1/customers?page=67&per_page=20

The new paging info in the http responde header:

link:

<http://consultapi.syneton.be:2100/api/v1/customers?page=1&per_page=20>; rel="prev", <http://consultapi.syneton.be:2100/api/v1/customers?page=3&per_page=20>; rel="next", <http://consultapi.syneton.be:2100/api/v1/customers?page=67&per_page=20>; rel="last", <http://consultapi.syneton.be:2100/api/v1/customers?page=1&per_page=20>; rel="first",

Notice the extra link "prev"

You can use any returned link or create you're own like 40 records per page: http://consultapi.syneton.be:2100/api/v1/customers?page=1&per_page=40 

Data changes

Get changes to keep data in sync.

The examples below uses the online test API http://consultapi.syneton.be:2100/api/v1/. See the artikel Admin-IS / Admin-Consult API activatie en configuratie to get access. 

The header of each request includes the synetontoken parameter.

Customer data

1) Typically you start to get all the data

http://consultapi.syneton.be:2100/api/v1/customer?page=1&per_page=100

See Paging in the artikel to get the next pages.

2) Get changes since your last sync date

http://consultapi.syneton.be:2100/api/v1/customer/changes?page=1&per_page=100&Filter=DateAction gt '2020-12-01'  

Same rule here, see Paging in the artikel to get the next pages.

All properties can be used to filter the request, see Data Filters in this artikel.

The Change API returns the Change model:

ActionType 

INSERT, UPDATE, DELETE

DateAction

date when the data in TableName was changed. Notice the format yyyy-mm-dd.

Id

customer_id

TabelName

specifies the type of data: customer, customer_bank, customer_jur, customer_vat, customer_tax or customer_soc

All data

All changes to all data, you need specific access to the module "ADMINISTRATIEVE GEGEVENS"

http://consultapi.syneton.be:2100/api/v1/changedetails?page=1&per_page=100&Filter=DateAction gt '2020-12-01'  

Same rule here, see Paging in the artikel to get the next pages.

All properties can be used to filter the request, see Data Filters in this artikel.

The Change API returns the Change model:

ActionType 

INSERT, UPDATE, DELETE

ColumnName

Name of the column that has been changed

DateAction

date when the data in TableName was changed. Notice the format yyyy-mm-dd.

Dbuser

DB connected user accoutable for the change (mostly DBA)

DisplayName

not used, will be removed in next version

LogId

unique id(PK) of a change

NewValue

new value when ActionType is UPDATE or INSERT

OldValue

old value when ActionType is UPDATE or DELETE

PersonName

logon name of the person accoutable for the change

RowIdentification

unique id(PK) of the table in TableName

TableName

specifies the type of data, including the ones of the changes API (see above)

Dynamic API

To create a dynamic API endpoint with the ExternalFile API to get the result set of a database view in the the Admin-IS / Admin-Consult database.

This API requires read access to the module "Extern bestand", if not allowed the API returns "No valid configuration for token.".

1.Views
Before you can create the definition file, you have to know the name of the view. Only database views are allowed, like this example in the test environment:

V_CUSTOMER_CHILDREN: Relations of type Customer(klant) who have children.

2. Create a definition file

A definition file (json) has to comply with a certain structure and is made up of:

• 'Info' section (mandatory)
• 'Source' (mandatory): the name of the view in the database of Admin-IS / Admin-Consult.
• 'Filter' (optional): parameter values are enclosed in [ ].
• 'Sort' (mandatory): to be able to execute the paging correctly
• 'Parameters' (optional): if used in the definition file, they must be included in the API call.
• 'Results' (mandatory): these are the fields from the view that constitute the result of the endpoint. Put a name between single quotes if it contain spaces. 

Example of definition-file with no parameters:

{ "Info": {
"Name": "CustomerKids",
"Version": "1.0",
"Description": "Relation with CRM type customer with children"
},
"Source": "V_CUSTOMER_CHILDREN",
"Sort": "name",
"Results": [
{
"Name": "name"
},
{
"Name": "nat_number"
},
{
"Name": "email"
},
{
"Name": "numberOfChildren"
}
]
}

Example of definition-file with parameters:

{ "Info": {
"Name": "CustomerKids",
"Version": "1.0",
"Description": "Relation with CRM type 'customer' with children"
},
"Source": "V_CUSTOMER_CHILDREN",
"Filter": "name = [name]",
"Sort": "name",
"Parameters": [
{
"Name": "name",
"Type": "varchar(125)"
}
],
"Results": [
{
"Name": "name"
},
{
"Name": "nat_number"
},
{
"Name": "email"
},
{
"Name": "numberOfChildren"
}
]
}

3. Upload the definition file

In the configuration, you can upload a file under the 'Beheer Externe bestanden' section. Click on the "+" sign. Via 'browse' you search for the file. Add a name of max. 25 characters and click 'OK'. Your file is being validated. By default, the file is set to 'active', but here you can uncheck it.

4. Test the API call

The general API endpoint for this External File API is

/api/v1/externalfile/{extname}

'Extname' is the name entered when uploading the file. Attention: this name is case-sensitive.

Example of API result with no parameters:

If you have specified parameters in the definition file, you need to enter them in the API call as well.
Use single quotes in the parameters of the API call.

Example of API result with parameters: