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

(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;

Currently unrated

Comments

There are currently no comments

New Comment

required

required (not published)

optional

required

Recent Posts

Archive

2019
2018
2014
2012
2011
2010
2009
2008
2007

Categories

Authors

Feeds

RSS / Atom