dolibarr project export including contacts (for tryton import)

(0 comments)

Since the dolibarr export for projects was not too useful for me directly I decided (reluctantly - due to the half english, half french nature of the DB tables, columns,..) to dive into the database again directly.

Here 2 useful SQL scripts to get some basic info (with some very basic html tag stripping, as I will not be needing the HTML at all:

\copy (
SELECT
p.rowid AS proj_id, TRIM(p.ref) AS number,
p.dateo AS start_date, pe.date_start_extra,p.datee AS end_date, pe.date_end_extra,
TRIM(REPLACE(p.title,'Bestellung', '')) AS cust_ref, TRIM(pe.commission) AS commission,
TRIM(p.description) AS description, p.date_close, TRIM(REGEXP_REPLACE(REGEXP_REPLACE(p.note_public, E'<[^>]+>', '', 'gi'), E'[\\n\\r]+', ' ', 'gi')) AS note,
soc.rowid AS customer_id, soc.nom AS customer_name
FROM llx_projet p
INNER JOIN llx_societe soc ON soc.rowid = p.fk_soc
INNER JOIN llx_projet_extrafields pe ON pe.fk_object = p.rowid
ORDER BY p.rowid DESC
) TO 'dolibarr_projects_2021-04-25.csv' WITH csv HEADER;

and the project contacts:

\copy (
SELECT
p.rowid AS proj_id, TRIM(p.ref) AS number,
sp.rowid AS contact_id, CONCAT_WS(' ', sp.lastname, sp.firstname) AS contact_name
FROM llx_projet p
INNER JOIN llx_element_contact ec on ec.element_id = p.rowid
INNER JOIN llx_socpeople sp on SP.rowid = ec.fk_socpeople
ORDER BY p.rowid DESC
) TO 'dolibarr_projects_contacts_2021-04-25.csv' WITH csv HEADER;

I only really needed the ids, but I added the number and name just for verification purposes.

Currently unrated

Comments

There are currently no comments

New Comment

required

required (not published)

optional

required

Recent Posts

Archive

2021
2020
2019
2018
2014
2012
2011
2010
2009
2008
2007

Categories

Authors

Feeds

RSS / Atom