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.
Share on Twitter Share on Facebook
Comments
There are currently no comments
New Comment