on getting party (company and people) data from dolibarr to import into tryton ..

Posted by: lordvan 5 years, 2 months ago

(0 comments)

I tried with just exporting from postgresql like this:

COPY (SELECT ROW_TO_JSON(t)
FROM (SELECT * FROM llx_societe) t) to '/path/to/file/llx_societe_extrafields.json';

but that gives me so much that I do not need and also still keeps the half-french colum names (which as someone who doesn't speak french is driving me mad and slowing me down..)

Warning: Postgresql does not seem to escape " from HTML so you need to escape it or remove it (which is hwat i did since I do not need it)

so I'll just make query and/or view to deal with this:

SELECT  s.rowid AS s_row,
        s.nom AS s_name,
        s.phone AS s_phone,
        s.fax AS s_fax,
        s.email AS s_email,
        s.url AS s_url,
        s.fax AS s_fax,
        s.address AS s_address,
        s.town AS s_town,
        s.zip AS s_zip,
        s.note_public AS s_note_public,
        s.note_private AS s_note_private,
        s.ape AS s_fbno,
        s.idprof4 AS s_dvrno,
        s.tva_assuj AS s_UST,
        s.tva_intra AS s_uid,
        s.code_client AS s_code_client,
        s.name_alias AS s_name_alias,
        s.siren AS s_siren,
        s.siret AS s_siret,
        s.client AS s_client,
        s_dep.nom AS s_county,
        s_reg.nom AS s_country,
        s.fk_forme_juridique,
        se.pn_name AS s_pn_name,
        sp.rowid AS sp_rowid,
        sp.lastname AS sp_lastname,
        sp.firstname AS sp_firstname,
        sp.address as sp_address,
        sp.civility AS sp_civility,
        sp.address AS sp_address,
        sp.zip AS sp_zip,
        sp.town AS sp_town,
        sp_dep.nom AS sp_county,
        sp_reg.nom AS sp_country,
        sp.fk_pays AS sp_fk_pays,
        sp.birthday AS sp_birthday,
        sp.poste AS sp_poste,
        sp.phone AS sp_phone,
        sp.phone_perso AS sp_phone_perso,
        sp.phone_mobile AS sp_phone_mobile,
        sp.fax AS sp_fax,
        sp.email AS sp_email,
        sp.priv AS sp_priv,
        sp.note_private AS sp_note_private,
        sp.note_public AS sp_note_public
        

FROM llx_societe AS s
INNER JOIN llx_societe_extrafields AS se ON se.fk_object = s.rowid
LEFT JOIN llx_socpeople AS sp ON sp.fk_soc = s.rowid
LEFT JOIN llx_c_departements AS s_dep ON s.fk_departement = s_dep.rowid
LEFT JOIN llx_c_regions AS s_reg ON s_dep.fk_region = s_reg.rowid
LEFT JOIN llx_c_departements AS sp_dep ON sp.fk_departement = sp_dep.rowid
LEFT JOIN llx_c_regions AS sp_reg ON sp_dep.fk_region = sp_reg.rowid
ORDER BY s_name, sp_lastname, sp_firstname;

• ← Using proteus to browse / edit / .. tryton's data
• Radeon RX590 + Ryzen7 2700X + MSI X470 Mainboard on linux amdgpu crash on boot →