Admin-IS / Admin-Consult API Werking voor de Softwareleverancier

Na opzet van de Admin-IS / Admin-Consult API kan je er mee aan de slag.
Onderstaande artikel is vooral bedoeld voor developers, die met de API aan de slag kunnen gaan.

Belangrijk: Steeds juiste charset mee in de header opnemen, zodat juist wordt omgegaan met "é" en andere database specifieke encoding. Content-Type =>

"application/json;charset=UTF-8" 

Inhoudstafel

Link naar database

Data Filters

Paging

Data changes

Dynamic API

Link naar database

Van de accountant ontvangt u de URL + sleutel om de Consult API te kunnen gebruiken. De Swagger API bevindt zich op de "/doc" mapping. 

De eerste stap is een token ophalen met de gekregen sleutel. Met Swagger kan je dit ook testen:

Note: Indien u via de interface werkt, scroll naar beneden in de lijst tot bij 'token' gezien de onderdelen alfabetisch staan. 

Het resultaat is een geldig token (indien de sofware op actief gezet is).

Dit token is dus geldig tot TimeToLive. De levensduur  van de token kan je aanpassen in de instellingen. Wanneer dit vervallen is moet je een nieuw token aanvragen. Het token moet gebruikt worden in de andere API calls.

Data Filters

Het resultaat van enkele API's kan gefilterd worden zodat er een beperkte set van data wordt teruggegeven. Hier wordt de werking van de filters uitgelegd, deze technische werking is bewust in het Engels geschreven.

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

• /api/v1/customers
• /api/v1/customer/changes
• ​/api​/v1​/customeremployees
• /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’.

• Strings must be enclosed with single quotes, as in 'manager name'.
• Numbers require no special formatting.
• Dates format 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')	Like 'xxx%'	StartsWith (Name, 'Mi')
EndsWith(..., 'x')	Like '%xxx'	EndsWith (Name, 'ing')
Contains(..., 'x')	Like '%xxx%'	Contains (Name, 'Van')
Like ‘X_Y’ for '%' use StartsWith, EndsWith or Contains	Single character	Name like ‘P_L’

Paging

Een dataset wordt altijd in pagina's teruggegeven, met of zonder een filter (zie hierboven).

De technische details zijn bewust in het Engels uitgeschreven.

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 response 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

CustomerId

TabelName

specifies the type of data: customer, customer_bank, customer_jur, customer_vat, customer_tax,  customer_soc or customer_adress(also CustomerId)

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: