TP.NET DAL API

TP.NET DAL API documentation

/dal/help.rev: 20211128

With this API you will be able to create, read, update and delete records in the TP.NET database. (CRUD database operations)

The API will manage internally that the operations performed are executed in a consistent manner so that the data in the registers have the appropriate values according to the TP.NET requirements.

Communication with the API is based on GET and POST methods.

Basically, 4 types of operations can be performed:

GET Method:

Read records (SELECT)

POST Method:

Create new record (INSERT)
Update records (UPDATE)
Delete records (DELETE)

Some tables have some actions restricted, for example, it will not be possible to perform the delete action in the AUDIT table.

List of available table names:

AUDITORIA (xml root object: DAL_Auditoria)
CLIENTES (xml root object: DAL_Cliente)
CONDUCTORES (xml root object: DAL_Conductor)
CUSTOM1 (xml root object: DAL_Custom)
CUSTOM2 (xml root object: DAL_Custom)
ENVASES (xml root object: DAL_Envase)
EXPEDIDORES (xml root object: DAL_TransportistaOperadorTransporteExpedidor)
OPERADORESTRANSPORTE (xml root object: DAL_TransportistaOperadorTransporteExpedidor)
PESADAS (xml root object: DAL_Pesadas)
PRECIOS (xml root object: DAL_Precio)
PRODUCTOS (xml root object: DAL_Producto)
REGULARIZACIONES (xml root object: DAL_Regularizacion)
TRANSPORTISTAS (xml root object: DAL_TransportistaOperadorTransporteExpedidor)
VARIOS1 (xml root object: DAL_Varios)
VARIOS2 (xml root object: DAL_Varios)
VEHICULOS (xml root object: DAL_Vehiculo)

You can provide the table name in lowercase or uppercase. It doesn't matter.

SELECT records

Method

http GET

url

http(s)://ipaddress:port/dal/TABLE_NAME/?fields=Axxx&filter=Bxxx&limit=L&offset=O&sort=Sxxx

Examples:

http://10.0.0.103:55013/dal/clientes

http://10.0.0.103:55013/dal/conductores/?fields=codigo,nombre&sort=nombre desc

http://10.0.0.103:55013/dal/pesadas/?fields=resguardo,albaran,matricula,neto&filter=tipopesada=1 AND cliente_codigo='0X876'&limit=10&offset=2&sort=albaran

parameters

fields:

Allows you to specify the list of fields to be returned. The fields must be separated by commas (no spaces).
The value * returns all the fields of the TABLE_NAME.

Example #1: fields=codigo,nombre,cp
Example #2: fields=*

Default value (if no fields parameter is specified in the url): fields=*

filter:

Filter applied to the query to be performed. Several operators are supported, such as = and AND.

Example #1: filter=codigo='23'
Example #2: filter=codigo='23' AND tipo=2

limit:

Maximum number of records to return. By default it returns a maximum of 100 records. If limit=0 is specified, ALL records in the table are retrieved.

Example #1: limit=25
Example #2: limit=999

Default value (if no limit parameter is specified in the url): limit=100

offset:

Number of records to be omitted at the beginning of the result list. Together with the parameter 'limit' is useful for paginating the results.

Example #1: offset=25
Example #2: offset=100

Default value (if no offset parameter is specified in the url): offset=0

sort:

List of fields to sort, separated by commas (no spaces). Optionally, at the end of each field the order is reported, asc for ascending and desc for descending.

Example #1: sort=nombre
Example #2: sort=nombre desc,tipo asc

Headers

Accept:

Format of the answer. Possible values:

application/json
application/xml

Default value if no 'Accept' header is specified: application/xml

Authorization:

Credentials User and password according to 'Basic HTTP Authentication Schema'.

Answer

http response code: 200. (OK) Array with the records of the query. Format as indicated by the 'Accept' header.
http response code: 400. (Error) Error message.

INSERT record

Method

http POST

url

http(s)://ipaddress:port/dal/TABLE_NAME

Body

Object to be inserted with the format according to 'Content-Type' header.

Body example (xml 'Content-Type' for CLIENTES table)

<DAL_Cliente>
  <CODIGO>PROV1</CODIGO>
  <NOMBRE>TEST DEL PROVEEDOR</NOMBRE>
  <NIFCIF>B34234234</NIFCIF>
  <DIRECCION />
  <TIPO>2</TIPO>
  <CP>08840</CP>
  <POBLACION>VILADECANS</POBLACION>
  <PROVINCIA>BARCELONA</PROVINCIA>
  <PAIS />
  <NOTAS />
  <DATOS1>XX1</DATOS1>
  <DATOS2>XX2</DATOS2>
  <DATOS3 />
</DAL_Cliente>

Body example (json 'Content-Type' for CLIENTES table)

{
  "CODIGO":"PROV1",
  "CP":"08840",
  "DATOS1":"XX1",
  "DATOS2":"XX2",
  "DATOS3":"",
  "DIRECCION":"",
  "NIFCIF":"B34234234",
  "NOMBRE":"TEST DEL PROVEEDOR",
  "NOTAS":"",
  "PAIS":"",
  "POBLACION":"VILADECANS",
  "PROVINCIA":"BARCELONA",
  "TIPO":"2"
}

'Empty' fields can be omited. Body example (xml 'Content-Type' for CLIENTES table)

<DAL_Cliente>
  <CODIGO>PROV1</CODIGO>
  <NOMBRE>TEST DEL PROVEEDOR</NOMBRE>
  <NIFCIF>B34234234</NIFCIF>
  <TIPO>2</TIPO>
  <CP>08840</CP>
  <POBLACION>VILADECANS</POBLACION>
  <PROVINCIA>BARCELONA</PROVINCIA>
  <DATOS1>XX1</DATOS1>
  <DATOS2>XX2</DATOS2>
</DAL_Cliente>

Special behavior for some tables:

The insert operation cannot be performed on the following tables:

AUDITORIA (This is 'SELECT' only table)
REGULARIZACIONES (This is 'SELECT/DELETE' only table)
PESADAS (This is 'SELECT/DELETE/UPDATE only EXTERNALx fields' only table)

Headers

Accept:

Format of the answer. Possible values:

application/json
application/xml

Authorization:

Credentials User and password according to 'Basic HTTP Authentication Schema'.

Content-Type:

Body Format. Possible values:

application/json
application/xml

Action:

Transaction to be performed. 'Action' header value to perform an insertion: insert

Answer

http response code: 200. (OK) Inserted object. Format as indicated in the 'Accept' header.
http response code: 400. (Error) Error message.

UPDATE records

Method

http POST

url

http(s)://ipaddress:port/dal/TABLE_NAME/?keys=Kxxx

parameters

keys:

Allows you to specify the list of fields and their values for the update condition. Several operators are supported, such as = and AND.

If the values of keys affect 1 record, 1 record will be modified. If keys values affect n records, n records will be modified.

Example #1: keys=codigo='23'
Example #2: keys=cp='08840' AND matricula='44654HHD'

Body

Object containing the information to be modified according to the format indicated in the 'Content-Type' header.

Example:

Original NOMBRE = TEST DEL PROVEEDOR for CLIENTE.CODIGO = PROV1
We want to update NOMBRE to SOMETHING NEW NAME

url: http(s)://ipaddress:port/dal/clientes/?keys=codigo='PROV1'

Body example (xml 'Content-Type' for CLIENTES table):

<DAL_Cliente>
<NOMBRE>SOMETHING NEW NAME</NOMBRE>
</DAL_Cliente>

Body example (json 'Content-Type' for CLIENTES table)

{
"NOMBRE":"SOMETHING NEW NAME"
}

As you can see, only the data to be modified is sent, it is not necessary to send the whole field structure

'Empty' fields will not be updated:

<DAL_Cliente>
  <CODIGO>PROV1</CODIGO>
  <NOMBRE>SOMETHING NEW NAME</NOMBRE>
  <NIFCIF>B34234234</NIFCIF>
  <DIRECCION />
  <PAIS />
  <NOTAS />
  <DATOS1>XX1</DATOS1>
  <DATOS2>XX2</DATOS2>
  <DATOS3 />
</DAL_Cliente>

In the above example, DIRECCION, PAIS NOTAS and DATOS3 will not be evaluated or updated. This xml is the 'same' as this one:

<DAL_Cliente>
  <CODIGO>PROV1</CODIGO>
  <NOMBRE>SOMETHING NEW NAME</NOMBRE>
  <NIFCIF>B34234234</NIFCIF>
  <DATOS1>XX1</DATOS1>
  <DATOS2>XX2</DATOS2>
</DAL_Cliente>

If you want to update a certain field so that the value is an 'empty string', you must indicate the value @string.empty. For example:

<DAL_Cliente>
  <NOMBRE>SOMETHING NEW NAME</NOMBRE>
  <DATOS1>@string.empty</DATOS1>
  <DATOS2>@string.empty</DATOS2>
</DAL_Cliente>

In the above example, DATOS1 and DATOS2 field will be updated to an 'empty string' value

You can send the primary key of the table in the xml or json body, however the primary key will not be evaluated or updated.

Special behavior for some tables:

The update operation cannot be performed on the following tables:

AUDITORIA (This is 'SELECT' only table)
REGULARIZACIONES (This is 'SELECT/DELETE' only table)
PESADAS (This is 'SELECT/DELETE/UPDATE only EXTERNALx fields' only table)

Headers

Accept:

Format of the answer. Possible values:

application/json
application/xml

Authorization:

Credentials User and password according to 'Basic HTTP Authentication Schema'.

Content-Type:

Body Format. Possible values:

application/json
application/xml

Action:

Transaction to be performed. 'Action' header value to perform a modification: update

Answer

http response code: 200. (OK) Modified object. It can be a list if the modification affected several records. Format as indicated in the 'Accept' header.
http response code: 400. (Error) Error message.
http response code: 404. (Warn) No record was found that fulfills the condition in keys parameter.

DELETE records

Method

http POST

url

http(s)://ipaddress:port/dal/TABLE_NAME/?keys=Kxxx

parameters

keys:

Allows you to specify the list of fields and their values for the delete condition. Several operators are supported, such as = and AND.

If the values of keys affect 1 record, 1 record will be deleted. If keys values affect n records, n records will be deleted.

Example #1: keys=codigo='23'
Example #2: keys=cp='08840' AND matricula='44654HHD'

Special behavior for some tables:

The delete operation cannot be performed on the following tables:

AUDITORIA (This is 'SELECT' only table)

Headers

Accept:

Format of the answer. Possible values:

application/json
application/xml

Authorization:

Credentials User and password according to 'Basic HTTP Authentication Schema'.

Action:

Transaction to be performed. 'Action' header value to perform an deletion: delete

Answer

http response code: 200. (OK) Number of deleted objects. Format as indicated in the 'Accept' header.
http response code: 400. (Error) Error message.
http response code: 404. (Warn) No record was found that fulfills the condition in keys parameter.

OPERATORS supported

In url parameters where operators can be used (filter and keys), the following operators may be used:

equal. Operator =

Examples:
http://10.0.0.103:55013/dal/conductores/?filter=poblacion='Barcelona'
http://10.0.0.103:55013/dal/vehiculos/?keys=matricula='0086HDR'

not equal. Operator <>

Examples:
http://10.0.0.103:55013/dal/conductores/?filter=poblacion<>'Barcelona'
http://10.0.0.103:55013/dal/clientes/?keys=poblacion<>'Barcelona'

Logical. Operator AND, OR

Examples:
http://10.0.0.103:55013/dal/pesadas/?filter=poblacion='Barcelona' AND estado=1
http://10.0.0.103:55013/dal/pesadas/?filter=estado=1 OR anulado=1
http://10.0.0.103:55013/dal/vehiculos/?keys=tipo=1 OR remolque='R9962FGK'

Substring likewise. Operator LIKE

Examples:
http://10.0.0.103:55013/dal/pesadas/?filter=poblacion LIKE 'B%'
http://10.0.0.103:55013/dal/pesadas/?filter=codigo LIKE '%0007'
http://10.0.0.103:55013/dal/vehiculos/?keys=matricula LIKE '%9HHF'
http://10.0.0.103:55013/dal/pesadas/?filter=cliente_nombre LIKE '%hormigon%' AND poblacion='Barcelona'