Actualizar provincia a a partir de los 2 d铆gitos del c贸digo postal
Adjunto excel donde actualizamos el campo provincia de los contactos a partir de los 2 primeros d铆gitos de su c贸digo postal con el identificador externo de las provincias de Espa帽a en Odoo
Si tenemos la posibilidad de ejecutar consultas SQL podemos ejecutar este script que actualiza la provincia a partir del c贸digo postal de los contactos siempre que haya c贸digo postal y el pa铆s sea Espa帽a:
Para Postgres:
-- Actualiza state_id en res_partner para contactos de Espa帽a
-- usando los 2 primeros d铆gitos del c贸digo postal (ZIP)
WITH mapping(prefix, ext_id) AS (
VALUES
('01','state_es_vi'),
('02','state_es_ab'),
('03','state_es_a'),
('04','state_es_al'),
('05','state_es_av'),
('06','state_es_ba'),
('07','state_es_pm'),
('08','state_es_b'),
('09','state_es_bu'),
('10','state_es_cc'),
('11','state_es_ca'),
('12','state_es_cs'),
('13','state_es_cr'),
('14','state_es_co'),
('15','state_es_c'),
('16','state_es_cu'),
('17','state_es_gi'),
('18','state_es_gr'),
('19','state_es_gu'),
('20','state_es_ss'),
('21','state_es_h'),
('22','state_es_hu'),
('23','state_es_j'),
('24','state_es_le'),
('25','state_es_l'),
('26','state_es_lo'),
('27','state_es_lu'),
('28','state_es_m'),
('29','state_es_ma'),
('30','state_es_mu'),
('31','state_es_na'),
('32','state_es_or'),
('33','state_es_o'),
('34','state_es_p'),
('35','state_es_gc'),
('36','state_es_po'),
('37','state_es_sa'),
('38','state_es_tf'),
('39','state_es_s'),
('40','state_es_sg'),
('41','state_es_se'),
('42','state_es_so'),
('43','state_es_t'),
('44','state_es_te'),
('45','state_es_to'),
('46','state_es_v'),
('47','state_es_va'),
('48','state_es_bi'),
('49','state_es_za'),
('50','state_es_z'),
('51','state_es_ce'),
('52','state_es_me')
),
es_country AS (
SELECT id AS country_id
FROM res_country
WHERE code = 'ES'
LIMIT 1
),
map_state AS (
SELECT m.prefix::text,
i.res_id::integer AS state_id
FROM mapping m
JOIN ir_model_data i
ON i.module = 'base'
AND i.model = 'res.country.state'
AND i.name = m.ext_id
),
partners_es AS (
SELECT rp.id,
left(regexp_replace(rp.zip::text, '\D', '', 'g'), 2) AS zip_prefix
FROM res_partner rp
JOIN es_country c ON rp.country_id = c.country_id
WHERE rp.zip IS NOT NULL
)
UPDATE res_partner rp
SET state_id = ms.state_id
FROM partners_es pe
JOIN map_state ms ON pe.zip_prefix = ms.prefix
AND COALESCE(rp.state_id, 0) <> ms.state_id;
Para SQL Server:
-- Actualiza res_partner.state_id (provincia) en funci贸n de los 2 primeros d铆gitos del CP
-- Actualiza res_partner.state_id (provincia) en funci贸n de los 2 primeros d铆gitos del CP
-- Requiere: ir_model_data con los external IDs de provincias del m贸dulo "base"
WITH mapping(prefix, ext_id) AS (
VALUES
('01','state_es_vi'),
('02','state_es_ab'),
('03','state_es_a'),
('04','state_es_al'),
('05','state_es_av'),
('06','state_es_ba'),
('07','state_es_pm'),
('08','state_es_b'),
('09','state_es_bu'),
('10','state_es_cc'),
('11','state_es_ca'),
('12','state_es_cs'),
('13','state_es_cr'),
('14','state_es_co'),
('15','state_es_c'),
('16','state_es_cu'),
('17','state_es_gi'),
('18','state_es_gr'),
('19','state_es_gu'),
('20','state_es_ss'),
('21','state_es_h'),
('22','state_es_hu'),
('23','state_es_j'),
('24','state_es_le'),
('25','state_es_l'),
('26','state_es_lo'),
('27','state_es_lu'),
('28','state_es_m'),
('29','state_es_ma'),
('30','state_es_mu'),
('31','state_es_na'),
('32','state_es_or'),
('33','state_es_o'),
('34','state_es_p'),
('35','state_es_gc'),
('36','state_es_po'),
('37','state_es_sa'),
('38','state_es_tf'),
('39','state_es_s'),
('40','state_es_sg'),
('41','state_es_se'),
('42','state_es_so'),
('43','state_es_t'),
('44','state_es_te'),
('45','state_es_to'),
('46','state_es_v'),
('47','state_es_va'),
('48','state_es_bi'),
('49','state_es_za'),
('50','state_es_z'),
('51','state_es_ce'),
('52','state_es_me')
),
map_state AS (
SELECT m.prefix,
i.res_id AS state_id
FROM mapping m
JOIN ir_model_data i
ON i.module = 'base'
AND i.model = 'res.country.state'
AND i.name = m.ext_id
),
es_country AS (
SELECT id AS country_id
FROM res_country
WHERE code = 'ES'
LIMIT 1
)
UPDATE res_partner rp
SET state_id = ms.state_id
FROM map_state ms, es_country c
WHERE rp.country_id = c.country_id
AND rp.zip IS NOT NULL
AND length(rp.zip) >= 2
AND substring(rp.zip from 1 for 2) = ms.prefix
-- Opcional: solo cambia si es distinto
AND COALESCE(rp.state_id, 0) <> ms.state_id
AND rp.country_id = 68