Forum GLPI-Project

ilbicelli

Member

Registered: 2022-11-21

Posts: 7

ERP Connector: API or Direct Database Connection

Hello,
long time user, first time poster (I think) :-)

I'm in the process to build an ERP connector for ingesting tasks into a billing document.

For doing this I'll export the task list (Entity ID, Entity Tag,Ticket number, task ID, task category, task duration) then flag in the database of the ERP connector which tasks were exported.

Upon export I'll create a billing document following the logic defined in ERP (hourly rates) based on task category.

For this kind of integration it looks like a query or a view is the most effective approach, but in future maybe I could add entity creation by reading ERP customer records or maybe other useful stuff.

if you were in my shoes, what would you do? I'm considering a mixed approach.

Cheers!

LaDenrée

HELPER

Registered: 2012-11-19

Posts: 6,168

Re: ERP Connector: API or Direct Database Connection

but in future maybe I could add entity creation by reading ERP customer records or maybe other useful stuff.

for this reason, I would use API.
and also because API will still work after GLPI upgrade, which might not be the case if database physical model changes especialy with major versions

---

Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1  Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6

ilbicelli

Member

Registered: 2022-11-21

Posts: 7

Re: ERP Connector: API or Direct Database Connection

After some test and research I opted for a mixed solution.
Since I need to extract tasks from tickets and changes It's better to directly query the database.
It's dead simple and even if database model changes, the query could be rewritten.

In case anyone is interested, queries are:

#tickets
SELECT
'ticket' as ticket_type,  
glpi_entities.tag as entity_tag,
glpi_entities.name as entity_name, 
glpi_tickets.name, 
glpi_tickets.id as tickets_id,
glpi_tickettasks.id as task_id,
glpi_taskcategories.name as task_category,
glpi_tickettasks.date,
glpi_tickettasks.content,
glpi_tickettasks.is_private,
glpi_tickettasks.actiontime,
glpi_tickettasks.state
FROM glpi_tickettasks 
INNER JOIN glpi_tickets ON glpi_tickettasks.tickets_id=glpi_tickets.id
INNER JOIN glpi_entities ON glpi_tickets.entities_id=glpi_entities.id 
LEFT JOIN glpi_taskcategories ON glpi_tickettasks.taskcategories_id=glpi_taskcategories.id
order by glpi.glpi_tickettasks.date;

#tasks
SELECT
'change' as ticket_type,
glpi_entities.tag as entity_tag,
glpi_entities.name as entity_name, 
glpi_changes.name, 
glpi_changes.id as tickets_id,
glpi_changetasks.id as task_id,
glpi_taskcategories.name as task_category,
glpi_changetasks.date,
glpi_changetasks.content,
glpi_changetasks.is_private,
glpi_changetasks.actiontime,
glpi_changetasks.state
FROM glpi_changetasks 
INNER JOIN glpi_changes ON glpi_changetasks.changes_id=glpi_changes.id
INNER JOIN glpi_entities ON glpi_changes.entities_id=glpi_entities.id 
LEFT JOIN glpi_taskcategories ON glpi_changetasks.taskcategories_id=glpi_taskcategories.id
order by glpi.glpi_changetasks.date;

Last edited by ilbicelli (2023-11-11 18:03:33)

cconard96

Moderator

Registered: 2018-07-31

Posts: 2,424

Website

Re: ERP Connector: API or Direct Database Connection

but in future maybe I could add entity creation by reading ERP customer records or maybe other useful stuff.

for this reason, I would use API.
and also because API will still work after GLPI upgrade, which might not be the case if database physical model changes especialy with major versions

Actually, the current API is very close to the database. If a database column changes, it affects the API.

The new API coming with GLPI 10.1 is more abstract and can provide better compatibility between versions.
Also in 10.1 will be an outgoing webhook feature that utilizes the new API. So, when tickets, followups, etc are created/updated/deleted, the data can be sent to an external server.
The webhook feature offers customizable payloads, OAuth support, customizable headers/URL (including letting you use data from the payload in the headers and external URL),

---

GLPI Collaborator and Plugin Developer.
My non-English comments are automated translations. Sorry for any confusion that causes.
Mes commentaires non anglais sont des traductions automatiques. Désolé pour toute confusion qui cause.
Mis comentarios que no están en inglés son traducciones automáticas. Perdón por cualquier confusión que cause.