You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
651 lines
30 KiB
651 lines
30 KiB
return: text
|
|
lang: plpgsql
|
|
parameters:
|
|
p0:
|
|
type: text
|
|
name: i_code
|
|
p1:
|
|
type: text
|
|
name: i_file
|
|
src: |
|
|
DECLARE
|
|
file TEXT;
|
|
ok TEXT;
|
|
result TEXT;
|
|
BEGIN
|
|
-- Import des données du fichier
|
|
IF i_file <> '' THEN
|
|
ok = '0';
|
|
|
|
IF i_code = 'CACIC_ATC' THEN
|
|
IF i_file ILIKE '%ATC%' THEN
|
|
ok = '1';
|
|
END IF;
|
|
END IF;
|
|
IF i_code = 'CACIC_ARTICLE_SP' THEN
|
|
IF i_file ILIKE '%SP%' THEN
|
|
ok = '1';
|
|
END IF;
|
|
END IF;
|
|
IF i_code = 'CACIC_ARTICLE_DM' THEN
|
|
IF i_file ILIKE '%DM%' THEN
|
|
ok = '1';
|
|
END IF;
|
|
END IF;
|
|
IF i_code = 'CACIC_ARTICLE_ASSOC' THEN
|
|
IF i_file ILIKE '%ASSOC%' AND i_file ILIKE '%ART%' THEN
|
|
ok = '1';
|
|
END IF;
|
|
END IF;
|
|
IF i_code = 'CACIC_FOURNISSEUR_ASSOC' THEN
|
|
IF i_file ILIKE '%ASSOC%' AND i_file ILIKE '%FOU%' THEN
|
|
ok = '1';
|
|
END IF;
|
|
END IF;
|
|
IF ok = '0' THEN
|
|
RETURN 'KO. Fichier ' || i_file || ' incompatible avec ' || i_code;
|
|
END IF ;
|
|
|
|
IF NOT EXISTS (SELECT * FROM pg_class WHERE relnamespace = pg_my_temp_schema() AND relname = 'w_reference_import') THEN
|
|
DROP TABLE IF EXISTS w_reference_import;
|
|
CREATE TEMP TABLE w_reference_import (
|
|
data text
|
|
);
|
|
END IF;
|
|
TRUNCATE w_reference_import;
|
|
RAISE NOTICE '%' , 'Copie des donnees ' || i_file;
|
|
file = replace(i_file,chr(92),'/');
|
|
BEGIN
|
|
EXECUTE ('COPY w_reference_import (data)
|
|
FROM ''' || file || '''
|
|
WITH DELIMITER AS E''' || chr(92) || '01''');
|
|
|
|
EXCEPTION
|
|
WHEN others THEN RAISE NOTICE 'Erreur %' , 'Fichier ' || file || ' incompatible';
|
|
RETURN 'KO. Structure fichier ' || file || ' incorrecte';
|
|
END;
|
|
|
|
DELETE FROM eco.t_reference_input_data
|
|
USING eco.t_reference_input
|
|
WHERE t_reference_input_data.reference_input_id = t_reference_input.oid AND
|
|
code = i_code;
|
|
DROP SEQUENCE IF EXISTS w_reference_input_data_sequence;
|
|
CREATE TEMP SEQUENCE w_reference_input_data_sequence;
|
|
INSERT INTO eco.t_reference_input_data
|
|
(
|
|
reference_input_id,
|
|
no_ligne,
|
|
data
|
|
)
|
|
SELECT
|
|
t_reference_input.oid,
|
|
nextval('w_reference_input_data_sequence'::regclass),
|
|
replace(data,E'\t','||')
|
|
FROM w_reference_import
|
|
JOIN eco.t_reference_input ON code = i_code;
|
|
|
|
|
|
END IF;
|
|
|
|
|
|
-- Génération
|
|
RAISE NOTICE '%' , 'Generation ' || i_code;
|
|
|
|
IF NOT EXISTS (SELECT * FROM pg_class WHERE relnamespace = pg_my_temp_schema() AND relname = 'w_reference_import_ligne') THEN
|
|
CREATE TEMP TABLE w_reference_import_ligne(
|
|
import_no_ligne bigint,
|
|
import_data text,
|
|
import_columns text[]
|
|
);
|
|
END IF;
|
|
-- ATC
|
|
IF i_code = 'CACIC_ATC' OR i_code = '*' THEN
|
|
TRUNCATE w_reference_import_ligne;
|
|
INSERT INTO w_reference_import_ligne(
|
|
import_no_ligne,
|
|
import_data,
|
|
import_columns
|
|
)
|
|
SELECT
|
|
no_ligne,
|
|
data,
|
|
string_to_array(data,'||')
|
|
FROM eco.t_reference_input_data
|
|
JOIN eco.t_reference_input ON t_reference_input_data.reference_input_id = t_reference_input.oid
|
|
WHERE t_reference_input.code = 'CACIC_ATC' AND
|
|
data <> '' AND
|
|
trim(split_part(data,'||',1)) <> '' AND
|
|
no_ligne <> 1;
|
|
UPDATE w_reference_import_ligne SET
|
|
import_columns[1] = trim(import_columns[1]),
|
|
import_columns[2] = trim(import_columns[2])
|
|
WHERE import_columns[1] IS DISTINCT FROM trim(import_columns[1]) OR
|
|
import_columns[2] IS DISTINCT FROM trim(import_columns[2])
|
|
;
|
|
INSERT INTO eco.t_classification_atc (code_original, code, texte, texte_court)
|
|
SELECT import_columns[1],
|
|
import_columns[1],
|
|
import_columns[2],
|
|
import_columns[2]
|
|
FROM w_reference_import_ligne
|
|
WHERE import_columns[1] <> '' AND
|
|
import_columns[1] NOT IN (SELECT code_original FROM eco.t_classification_atc WHERE code_original IS NOT NULL);
|
|
UPDATE eco.t_classification_atc SET
|
|
texte = import_columns[2],
|
|
texte_court = import_columns[2]
|
|
FROM w_reference_import_ligne
|
|
WHERE t_classification_atc.code_original = import_columns[1] AND
|
|
(
|
|
t_classification_atc.texte IS DISTINCT FROM import_columns[2] OR
|
|
t_classification_atc.texte_court IS DISTINCT FROM import_columns[2]
|
|
);
|
|
PERFORM eco.cti_reorganize_classification_atc();
|
|
UPDATE eco.t_reference_input SET
|
|
import_date = date(now()),
|
|
nb_lignes = COALESCE((SELECT count(*) FROM w_reference_import_ligne),0)
|
|
WHERE code = 'CACIC_ATC';
|
|
END IF;
|
|
|
|
-- SP
|
|
IF i_code = 'CACIC_ARTICLE_SP' OR i_code = '*' THEN
|
|
TRUNCATE w_reference_import_ligne;
|
|
INSERT INTO w_reference_import_ligne(
|
|
import_no_ligne,
|
|
import_data,
|
|
import_columns
|
|
)
|
|
SELECT
|
|
no_ligne,
|
|
data,
|
|
string_to_array(data,'||')
|
|
FROM eco.t_reference_input_data
|
|
JOIN eco.t_reference_input ON t_reference_input_data.reference_input_id = t_reference_input.oid
|
|
WHERE t_reference_input.code = 'CACIC_ARTICLE_SP' AND
|
|
data <> '' AND
|
|
trim(split_part(data,'||',1)) <> '' AND
|
|
no_ligne <> 1;
|
|
UPDATE w_reference_import_ligne SET
|
|
import_columns[1] = trim(import_columns[1]),
|
|
import_columns[2] = trim(import_columns[2]),
|
|
import_columns[3] = trim(import_columns[3]),
|
|
import_columns[4] = trim(import_columns[4]),
|
|
import_columns[5] = trim(import_columns[5]),
|
|
import_columns[6] = trim(import_columns[6]),
|
|
import_columns[7] = trim(import_columns[7]),
|
|
import_columns[8] = trim(import_columns[8]),
|
|
import_columns[9] = trim(import_columns[9]),
|
|
import_columns[10] = trim(import_columns[10]),
|
|
import_columns[11] = trim(import_columns[11]),
|
|
import_columns[12] = trim(import_columns[12]),
|
|
import_columns[13] = trim(import_columns[13]),
|
|
import_columns[14] = trim(import_columns[14]),
|
|
import_columns[15] = trim(import_columns[15]),
|
|
import_columns[16] = trim(import_columns[16]),
|
|
import_columns[17] = trim(import_columns[17]),
|
|
import_columns[18] = trim(import_columns[18]),
|
|
import_columns[19] = trim(import_columns[19]),
|
|
import_columns[20] = trim(import_columns[20])
|
|
WHERE import_columns[1] IS DISTINCT FROM trim(import_columns[1]) OR
|
|
import_columns[2] IS DISTINCT FROM trim(import_columns[2]) OR
|
|
import_columns[3] IS DISTINCT FROM trim(import_columns[3]) OR
|
|
import_columns[4] IS DISTINCT FROM trim(import_columns[4]) OR
|
|
import_columns[5] IS DISTINCT FROM trim(import_columns[5]) OR
|
|
import_columns[6] IS DISTINCT FROM trim(import_columns[6]) OR
|
|
import_columns[7] IS DISTINCT FROM trim(import_columns[7]) OR
|
|
import_columns[8] IS DISTINCT FROM trim(import_columns[8]) OR
|
|
import_columns[9] IS DISTINCT FROM trim(import_columns[9]) OR
|
|
import_columns[10] IS DISTINCT FROM trim(import_columns[10]) OR
|
|
import_columns[11] IS DISTINCT FROM trim(import_columns[11]) OR
|
|
import_columns[12] IS DISTINCT FROM trim(import_columns[12]) OR
|
|
import_columns[13] IS DISTINCT FROM trim(import_columns[13]) OR
|
|
import_columns[14] IS DISTINCT FROM trim(import_columns[14]) OR
|
|
import_columns[15] IS DISTINCT FROM trim(import_columns[15]) OR
|
|
import_columns[16] IS DISTINCT FROM trim(import_columns[16]) OR
|
|
import_columns[17] IS DISTINCT FROM trim(import_columns[17]) OR
|
|
import_columns[18] IS DISTINCT FROM trim(import_columns[18]) OR
|
|
import_columns[19] IS DISTINCT FROM trim(import_columns[19]) OR
|
|
import_columns[20] IS DISTINCT FROM trim(import_columns[20])
|
|
;
|
|
|
|
INSERT INTO eco.t_fournisseur_reference_cacic (code_original, code, texte, texte_court)
|
|
SELECT import_columns[6],
|
|
import_columns[6],
|
|
MAX(import_columns[7]),
|
|
MAX(import_columns[7])
|
|
FROM w_reference_import_ligne
|
|
WHERE import_columns[6] <> '' AND
|
|
import_columns[6] NOT IN (SELECT code_original FROM eco.t_fournisseur_reference_cacic WHERE code_original IS NOT NULL)
|
|
GROUP BY 1,2;
|
|
|
|
UPDATE eco.t_fournisseur_reference_cacic SET
|
|
code = import_columns[6],
|
|
texte = import_columns[7]
|
|
FROM w_reference_import_ligne
|
|
WHERE t_fournisseur_reference_cacic.code_original = import_columns[6] AND
|
|
(
|
|
t_fournisseur_reference_cacic.code IS DISTINCT FROM import_columns[6] OR
|
|
t_fournisseur_reference_cacic.texte IS DISTINCT FROM import_columns[7]
|
|
);
|
|
INSERT INTO eco.t_article_reference_cacic (code_original, code, texte, texte_court, ucd_id, classification_atc_id, fournisseur_reference_cacic_id)
|
|
SELECT import_columns[1],
|
|
import_columns[1],
|
|
CASE WHEN import_columns[19] <> '' THEN import_columns[19] ELSE import_columns[8] || ' ' || import_columns[9] END,
|
|
CASE WHEN import_columns[19] <> '' THEN import_columns[19] ELSE import_columns[8] || ' ' || import_columns[9] END,
|
|
COALESCE(t_ucd.oid,0) AS ucd_id,
|
|
COALESCE(t_classification_atc.oid,0) AS classification_atc_id,
|
|
COALESCE(t_fournisseur_reference_cacic.oid,0) AS fournisseur_reference_cacic_id
|
|
FROM w_reference_import_ligne
|
|
LEFT JOIN base.t_ucd ON import_columns[3] = t_ucd.code
|
|
LEFT JOIN eco.t_classification_atc ON import_columns[16] = t_classification_atc.code
|
|
LEFT JOIN eco.t_fournisseur_reference_cacic ON import_columns[6] = t_fournisseur_reference_cacic.code_original
|
|
WHERE import_columns[1] NOT IN (SELECT code_original FROM eco.t_article_reference_cacic);
|
|
UPDATE eco.t_article_reference_cacic SET
|
|
code = import_columns[1],
|
|
texte = CASE WHEN import_columns[19] <> '' THEN import_columns[19] ELSE import_columns[8] || ' ' || import_columns[9] END,
|
|
texte_court = CASE WHEN import_columns[19] <> '' THEN import_columns[19] ELSE import_columns[8] || ' ' || import_columns[9] END,
|
|
ucd_id = COALESCE(t_ucd.oid,0),
|
|
classification_atc_id = COALESCE(t_classification_atc.oid,0),
|
|
fournisseur_reference_cacic_id = COALESCE(t_fournisseur_reference_cacic.oid,0)
|
|
FROM w_reference_import_ligne
|
|
LEFT JOIN base.t_ucd ON import_columns[3] = t_ucd.code
|
|
LEFT JOIN eco.t_classification_atc ON import_columns[16] = t_classification_atc.code
|
|
LEFT JOIN eco.t_fournisseur_reference_cacic ON import_columns[6] = t_fournisseur_reference_cacic.code
|
|
WHERE t_article_reference_cacic.code_original = import_columns[1] AND
|
|
(
|
|
t_article_reference_cacic.code IS DISTINCT FROM import_columns[1] OR
|
|
t_article_reference_cacic.texte IS DISTINCT FROM CASE WHEN import_columns[19] <> '' THEN import_columns[19] ELSE import_columns[8] || ' ' || import_columns[9] END OR
|
|
t_article_reference_cacic.texte_court IS DISTINCT FROM CASE WHEN import_columns[19] <> '' THEN import_columns[19] ELSE import_columns[8] || ' ' || import_columns[9] END OR
|
|
t_article_reference_cacic.ucd_id IS DISTINCT FROM COALESCE(t_ucd.oid,0) OR
|
|
t_article_reference_cacic.classification_atc_id IS DISTINCT FROM COALESCE(t_classification_atc.oid,0) OR
|
|
fournisseur_reference_cacic_id IS DISTINCT FROM COALESCE(t_fournisseur_reference_cacic.oid,0)
|
|
);
|
|
INSERT INTO eco.t_types_articles(code_original, code, texte, texte_court)
|
|
SELECT code, code, texte, texte
|
|
FROM
|
|
(
|
|
SELECT 'CACIC_SP'::text AS code,'CACIC SP'::text AS texte
|
|
) subview
|
|
WHERE subview.code NOT IN (SELECT code_original FROM eco.t_types_articles WHERE code_original IS NOT NULL);
|
|
|
|
UPDATE eco.t_article_reference_cacic SET
|
|
type_id = t_types_articles.oid
|
|
FROM eco.t_types_articles
|
|
WHERE t_types_articles.code = 'CACIC_SP' AND
|
|
type_id IS DISTINCT FROM t_types_articles.oid;
|
|
UPDATE eco.t_reference_input SET
|
|
import_date = date(now()),
|
|
nb_lignes = COALESCE((SELECT count(*) FROM w_reference_import_ligne),0)
|
|
WHERE code = 'CACIC_ARTICLE_SP';
|
|
END IF;
|
|
-- DM
|
|
IF i_code = 'CACIC_ARTICLE_DM' OR i_code = '*' THEN
|
|
TRUNCATE w_reference_import_ligne;
|
|
INSERT INTO w_reference_import_ligne(
|
|
import_no_ligne,
|
|
import_data,
|
|
import_columns
|
|
)
|
|
SELECT
|
|
no_ligne,
|
|
data,
|
|
string_to_array(data,'||')
|
|
FROM eco.t_reference_input_data
|
|
JOIN eco.t_reference_input ON t_reference_input_data.reference_input_id = t_reference_input.oid
|
|
WHERE t_reference_input.code = 'CACIC_ARTICLE_DM' AND
|
|
data <> '' AND
|
|
trim(split_part(data,'||',1)) <> '' AND
|
|
no_ligne <> 1;
|
|
UPDATE w_reference_import_ligne SET
|
|
import_columns[1] = trim(import_columns[1]),
|
|
import_columns[2] = trim(import_columns[2]),
|
|
import_columns[3] = trim(import_columns[3]),
|
|
import_columns[4] = trim(import_columns[4]),
|
|
import_columns[5] = trim(import_columns[5]),
|
|
import_columns[6] = trim(import_columns[6]),
|
|
import_columns[7] = trim(import_columns[7]),
|
|
import_columns[8] = trim(import_columns[8]),
|
|
import_columns[9] = trim(import_columns[9]),
|
|
import_columns[10] = trim(import_columns[10]),
|
|
import_columns[11] = trim(import_columns[11]),
|
|
import_columns[12] = trim(import_columns[12]),
|
|
import_columns[13] = trim(import_columns[13]),
|
|
import_columns[14] = trim(import_columns[14]),
|
|
import_columns[15] = trim(import_columns[15]),
|
|
import_columns[16] = trim(import_columns[16]),
|
|
import_columns[17] = trim(import_columns[17]),
|
|
import_columns[18] = trim(import_columns[18]),
|
|
import_columns[19] = trim(import_columns[19]),
|
|
import_columns[20] = trim(import_columns[20])
|
|
WHERE import_columns[1] IS DISTINCT FROM trim(import_columns[1]) OR
|
|
import_columns[2] IS DISTINCT FROM trim(import_columns[2]) OR
|
|
import_columns[3] IS DISTINCT FROM trim(import_columns[3]) OR
|
|
import_columns[4] IS DISTINCT FROM trim(import_columns[4]) OR
|
|
import_columns[5] IS DISTINCT FROM trim(import_columns[5]) OR
|
|
import_columns[6] IS DISTINCT FROM trim(import_columns[6]) OR
|
|
import_columns[7] IS DISTINCT FROM trim(import_columns[7]) OR
|
|
import_columns[8] IS DISTINCT FROM trim(import_columns[8]) OR
|
|
import_columns[9] IS DISTINCT FROM trim(import_columns[9]) OR
|
|
import_columns[10] IS DISTINCT FROM trim(import_columns[10]) OR
|
|
import_columns[11] IS DISTINCT FROM trim(import_columns[11]) OR
|
|
import_columns[12] IS DISTINCT FROM trim(import_columns[12]) OR
|
|
import_columns[13] IS DISTINCT FROM trim(import_columns[13]) OR
|
|
import_columns[14] IS DISTINCT FROM trim(import_columns[14]) OR
|
|
import_columns[15] IS DISTINCT FROM trim(import_columns[15]) OR
|
|
import_columns[16] IS DISTINCT FROM trim(import_columns[16]) OR
|
|
import_columns[17] IS DISTINCT FROM trim(import_columns[17]) OR
|
|
import_columns[18] IS DISTINCT FROM trim(import_columns[18]) OR
|
|
import_columns[19] IS DISTINCT FROM trim(import_columns[19]) OR
|
|
import_columns[20] IS DISTINCT FROM trim(import_columns[20])
|
|
;
|
|
INSERT INTO eco.t_fournisseur_reference_cacic (code_original, code, texte, texte_court)
|
|
SELECT import_columns[4],
|
|
import_columns[4],
|
|
MAX(import_columns[5]),
|
|
MAX(import_columns[5])
|
|
FROM w_reference_import_ligne
|
|
WHERE import_columns[4] <> '' AND
|
|
import_columns[4] NOT IN (SELECT code_original FROM eco.t_fournisseur_reference_cacic WHERE code_original IS NOT NULL)
|
|
GROUP BY 1,2;
|
|
|
|
UPDATE eco.t_fournisseur_reference_cacic SET
|
|
code = import_columns[4],
|
|
texte = import_columns[5]
|
|
FROM w_reference_import_ligne
|
|
WHERE t_fournisseur_reference_cacic.code_original = import_columns[4] AND
|
|
(
|
|
t_fournisseur_reference_cacic.code IS DISTINCT FROM import_columns[4] OR
|
|
t_fournisseur_reference_cacic.texte IS DISTINCT FROM import_columns[5]
|
|
);
|
|
INSERT INTO eco.t_article_reference_cacic (code_original, code, texte, texte_court, fournisseur_reference_cacic_id)
|
|
SELECT import_columns[1],
|
|
import_columns[1],
|
|
import_columns[6],
|
|
import_columns[6],
|
|
COALESCE(t_fournisseur_reference_cacic.oid,0) AS fournisseur_reference_cacic_id
|
|
FROM w_reference_import_ligne
|
|
LEFT JOIN eco.t_fournisseur_reference_cacic ON import_columns[4] = t_fournisseur_reference_cacic.code_original
|
|
WHERE import_columns[1] NOT IN (SELECT code_original FROM eco.t_article_reference_cacic);
|
|
UPDATE eco.t_article_reference_cacic SET
|
|
code = import_columns[1],
|
|
texte = import_columns[6],
|
|
texte_court = import_columns[6],
|
|
fournisseur_reference_cacic_id = COALESCE(t_fournisseur_reference_cacic.oid,0)
|
|
FROM w_reference_import_ligne
|
|
LEFT JOIN eco.t_fournisseur_reference_cacic ON import_columns[4] = t_fournisseur_reference_cacic.code
|
|
WHERE t_article_reference_cacic.code_original = import_columns[1] AND
|
|
(
|
|
t_article_reference_cacic.code IS DISTINCT FROM import_columns[1] OR
|
|
t_article_reference_cacic.texte IS DISTINCT FROM import_columns[6] OR
|
|
t_article_reference_cacic.texte_court IS DISTINCT FROM import_columns[6] OR
|
|
fournisseur_reference_cacic_id IS DISTINCT FROM COALESCE(t_fournisseur_reference_cacic.oid,0)
|
|
);
|
|
INSERT INTO eco.t_types_articles(code_original, code, texte, texte_court)
|
|
SELECT code, code, texte, texte
|
|
FROM
|
|
(
|
|
SELECT 'CACIC_DM'::text AS code,'CACIC DM'::text AS texte
|
|
) subview
|
|
WHERE subview.code NOT IN (SELECT code_original FROM eco.t_types_articles WHERE code_original IS NOT NULL);
|
|
UPDATE eco.t_article_reference_cacic SET
|
|
type_id = t_types_articles.oid
|
|
FROM eco.t_types_articles
|
|
WHERE t_types_articles.code = 'CACIC_DM' AND
|
|
type_id IS DISTINCT FROM t_types_articles.oid;
|
|
|
|
UPDATE eco.t_reference_input SET
|
|
import_date = date(now()),
|
|
nb_lignes = COALESCE((SELECT count(*) FROM w_reference_import_ligne),0)
|
|
WHERE code = 'CACIC_ARTICLE_DM';
|
|
END IF;
|
|
-- Articles ASSOC
|
|
IF i_code = 'CACIC_ARTICLE_ASSOC' OR i_code = '*' THEN
|
|
TRUNCATE w_reference_import_ligne;
|
|
INSERT INTO w_reference_import_ligne(
|
|
import_no_ligne,
|
|
import_data,
|
|
import_columns
|
|
)
|
|
SELECT
|
|
no_ligne,
|
|
data,
|
|
string_to_array(data,'||')
|
|
FROM eco.t_reference_input_data
|
|
JOIN eco.t_reference_input ON t_reference_input_data.reference_input_id = t_reference_input.oid
|
|
WHERE t_reference_input.code = 'CACIC_ARTICLE_ASSOC' AND
|
|
data <> '' AND
|
|
trim(split_part(data,'||',1)) <> '' AND
|
|
no_ligne <> 1;
|
|
UPDATE w_reference_import_ligne SET
|
|
import_columns[1] = trim(import_columns[1]),
|
|
import_columns[2] = trim(import_columns[2]),
|
|
import_columns[3] = trim(import_columns[3]),
|
|
import_columns[4] = trim(import_columns[4]),
|
|
import_columns[5] = trim(import_columns[5]),
|
|
import_columns[6] = trim(import_columns[6]),
|
|
import_columns[7] = trim(import_columns[7]),
|
|
import_columns[8] = trim(import_columns[8]),
|
|
import_columns[9] = trim(import_columns[9]),
|
|
import_columns[10] = trim(import_columns[10]),
|
|
import_columns[11] = trim(import_columns[11]),
|
|
import_columns[12] = trim(import_columns[12]),
|
|
import_columns[13] = trim(import_columns[13]),
|
|
import_columns[14] = trim(import_columns[14]),
|
|
import_columns[15] = trim(import_columns[15]),
|
|
import_columns[16] = trim(import_columns[16]),
|
|
import_columns[17] = trim(import_columns[17]),
|
|
import_columns[18] = trim(import_columns[18]),
|
|
import_columns[19] = trim(import_columns[19]),
|
|
import_columns[20] = trim(import_columns[20])
|
|
WHERE import_columns[1] IS DISTINCT FROM trim(import_columns[1]) OR
|
|
import_columns[2] IS DISTINCT FROM trim(import_columns[2]) OR
|
|
import_columns[3] IS DISTINCT FROM trim(import_columns[3]) OR
|
|
import_columns[4] IS DISTINCT FROM trim(import_columns[4]) OR
|
|
import_columns[5] IS DISTINCT FROM trim(import_columns[5]) OR
|
|
import_columns[6] IS DISTINCT FROM trim(import_columns[6]) OR
|
|
import_columns[7] IS DISTINCT FROM trim(import_columns[7]) OR
|
|
import_columns[8] IS DISTINCT FROM trim(import_columns[8]) OR
|
|
import_columns[9] IS DISTINCT FROM trim(import_columns[9]) OR
|
|
import_columns[10] IS DISTINCT FROM trim(import_columns[10]) OR
|
|
import_columns[11] IS DISTINCT FROM trim(import_columns[11]) OR
|
|
import_columns[12] IS DISTINCT FROM trim(import_columns[12]) OR
|
|
import_columns[13] IS DISTINCT FROM trim(import_columns[13]) OR
|
|
import_columns[14] IS DISTINCT FROM trim(import_columns[14]) OR
|
|
import_columns[15] IS DISTINCT FROM trim(import_columns[15]) OR
|
|
import_columns[16] IS DISTINCT FROM trim(import_columns[16]) OR
|
|
import_columns[17] IS DISTINCT FROM trim(import_columns[17]) OR
|
|
import_columns[18] IS DISTINCT FROM trim(import_columns[18]) OR
|
|
import_columns[19] IS DISTINCT FROM trim(import_columns[19]) OR
|
|
import_columns[20] IS DISTINCT FROM trim(import_columns[20])
|
|
;
|
|
-- Mise à jour par le code
|
|
UPDATE eco.t_articles
|
|
SET article_reference_cacic_id = t_article_reference_cacic.oid
|
|
FROM w_reference_import_ligne
|
|
JOIN eco.t_article_reference_cacic ON eco.t_article_reference_cacic.code = w_reference_import_ligne.import_columns[2]
|
|
WHERE t_articles.code = w_reference_import_ligne.import_columns[1] AND
|
|
t_articles.article_reference_cacic_id IS DISTINCT FROM t_article_reference_cacic.oid;
|
|
-- Mise à jour par le code original
|
|
UPDATE eco.t_articles
|
|
SET article_reference_cacic_id = t_article_reference_cacic.oid
|
|
FROM w_reference_import_ligne
|
|
JOIN eco.t_article_reference_cacic ON eco.t_article_reference_cacic.code = w_reference_import_ligne.import_columns[2]
|
|
WHERE ltrim(t_articles.code_original,'0') = w_reference_import_ligne.import_columns[1] AND
|
|
t_articles.article_reference_cacic_id IS DISTINCT FROM t_article_reference_cacic.oid;
|
|
|
|
UPDATE eco.t_reference_input SET
|
|
import_date = date(now()),
|
|
nb_lignes = COALESCE((SELECT count(*) FROM w_reference_import_ligne),0)
|
|
WHERE code = 'CACIC_ARTICLE_ASSOC';
|
|
END IF;
|
|
-- Articles ASSOC
|
|
IF i_code = 'CACIC_FOURNISSEUR_ASSOC' OR i_code = '*' THEN
|
|
TRUNCATE w_reference_import_ligne;
|
|
INSERT INTO w_reference_import_ligne(
|
|
import_no_ligne,
|
|
import_data,
|
|
import_columns
|
|
)
|
|
SELECT
|
|
no_ligne,
|
|
data,
|
|
string_to_array(data,'||')
|
|
FROM eco.t_reference_input_data
|
|
JOIN eco.t_reference_input ON t_reference_input_data.reference_input_id = t_reference_input.oid
|
|
WHERE t_reference_input.code = 'CACIC_FOURNISSEUR_ASSOC' AND
|
|
data <> '' AND
|
|
trim(split_part(data,'||',1)) <> '' AND
|
|
no_ligne <> 1;
|
|
UPDATE w_reference_import_ligne SET
|
|
import_columns[1] = trim(import_columns[1]),
|
|
import_columns[2] = trim(import_columns[2]),
|
|
import_columns[3] = trim(import_columns[3]),
|
|
import_columns[4] = trim(import_columns[4]),
|
|
import_columns[5] = trim(import_columns[5]),
|
|
import_columns[6] = trim(import_columns[6]),
|
|
import_columns[7] = trim(import_columns[7]),
|
|
import_columns[8] = trim(import_columns[8]),
|
|
import_columns[9] = trim(import_columns[9]),
|
|
import_columns[10] = trim(import_columns[10]),
|
|
import_columns[11] = trim(import_columns[11]),
|
|
import_columns[12] = trim(import_columns[12]),
|
|
import_columns[13] = trim(import_columns[13]),
|
|
import_columns[14] = trim(import_columns[14]),
|
|
import_columns[15] = trim(import_columns[15]),
|
|
import_columns[16] = trim(import_columns[16]),
|
|
import_columns[17] = trim(import_columns[17]),
|
|
import_columns[18] = trim(import_columns[18]),
|
|
import_columns[19] = trim(import_columns[19]),
|
|
import_columns[20] = trim(import_columns[20])
|
|
WHERE import_columns[1] IS DISTINCT FROM trim(import_columns[1]) OR
|
|
import_columns[2] IS DISTINCT FROM trim(import_columns[2]) OR
|
|
import_columns[3] IS DISTINCT FROM trim(import_columns[3]) OR
|
|
import_columns[4] IS DISTINCT FROM trim(import_columns[4]) OR
|
|
import_columns[5] IS DISTINCT FROM trim(import_columns[5]) OR
|
|
import_columns[6] IS DISTINCT FROM trim(import_columns[6]) OR
|
|
import_columns[7] IS DISTINCT FROM trim(import_columns[7]) OR
|
|
import_columns[8] IS DISTINCT FROM trim(import_columns[8]) OR
|
|
import_columns[9] IS DISTINCT FROM trim(import_columns[9]) OR
|
|
import_columns[10] IS DISTINCT FROM trim(import_columns[10]) OR
|
|
import_columns[11] IS DISTINCT FROM trim(import_columns[11]) OR
|
|
import_columns[12] IS DISTINCT FROM trim(import_columns[12]) OR
|
|
import_columns[13] IS DISTINCT FROM trim(import_columns[13]) OR
|
|
import_columns[14] IS DISTINCT FROM trim(import_columns[14]) OR
|
|
import_columns[15] IS DISTINCT FROM trim(import_columns[15]) OR
|
|
import_columns[16] IS DISTINCT FROM trim(import_columns[16]) OR
|
|
import_columns[17] IS DISTINCT FROM trim(import_columns[17]) OR
|
|
import_columns[18] IS DISTINCT FROM trim(import_columns[18]) OR
|
|
import_columns[19] IS DISTINCT FROM trim(import_columns[19]) OR
|
|
import_columns[20] IS DISTINCT FROM trim(import_columns[20])
|
|
;
|
|
UPDATE eco.t_fournisseurs
|
|
SET fournisseur_reference_cacic_id = t_fournisseur_reference_cacic.oid
|
|
FROM w_reference_import_ligne
|
|
JOIN eco.t_fournisseur_reference_cacic ON eco.t_fournisseur_reference_cacic.code = w_reference_import_ligne.import_columns[2]
|
|
WHERE t_fournisseurs.code = w_reference_import_ligne.import_columns[1] AND
|
|
t_fournisseurs.fournisseur_reference_cacic_id IS DISTINCT FROM t_fournisseur_reference_cacic.oid;
|
|
|
|
UPDATE eco.t_reference_input SET
|
|
import_date = date(now()),
|
|
nb_lignes = COALESCE((SELECT count(*) FROM w_reference_import_ligne),0)
|
|
WHERE code = 'CACIC_FOURNISSEUR_ASSOC';
|
|
END IF;
|
|
-- Impacts sur base de données
|
|
UPDATE eco.t_articles
|
|
SET article_reference_cacic_id = subview.article_reference_cacic_id
|
|
FROM (
|
|
SELECT ucd_id, MAX(oid) AS article_reference_cacic_id
|
|
FROM eco.t_article_reference_cacic
|
|
WHERE ucd_id <> 0
|
|
GROUP BY 1
|
|
HAVING count(*) = 1
|
|
) subview
|
|
WHERE t_articles.ucd_id = subview.ucd_id AND
|
|
t_articles.article_reference_cacic_id = 0;
|
|
UPDATE eco.t_articles
|
|
SET article_reference_cacic_id = subview.article_reference_cacic_id
|
|
FROM (
|
|
SELECT ucd_id,
|
|
classification_atc_id,
|
|
MAX(oid) AS article_reference_cacic_id
|
|
FROM eco.t_article_reference_cacic
|
|
WHERE ucd_id <> 0 AND classification_atc_id <> 0
|
|
GROUP BY 1,2
|
|
HAVING count(*) = 1
|
|
) subview
|
|
WHERE t_articles.ucd_id = subview.ucd_id AND
|
|
t_articles.classification_atc_id = subview.classification_atc_id AND
|
|
t_articles.article_reference_cacic_id = 0;
|
|
UPDATE eco.t_articles
|
|
SET article_reference_cacic_id = subview.article_reference_cacic_id
|
|
FROM
|
|
(SELECT t_articles.oid AS article_id, t_article_reference_cacic.texte, MAX(t_article_reference_cacic.oid) AS article_reference_cacic_id
|
|
FROM eco.t_article_reference_cacic
|
|
JOIN eco.t_articles ON upper(t_article_reference_cacic.texte) = upper(t_articles.texte)
|
|
WHERE t_articles.oid <> 0 AND
|
|
t_articles.article_reference_cacic_id = 0 AND
|
|
t_article_reference_cacic.oid <> 0
|
|
GROUP BY 1,2
|
|
HAVING count(*) = 1
|
|
) subview
|
|
WHERE t_articles.oid = subview.article_id AND
|
|
t_articles.article_reference_cacic_id = 0;
|
|
UPDATE eco.t_articles
|
|
SET classification_atc_id = t_article_reference_cacic.classification_atc_id
|
|
FROM eco.t_article_reference_cacic
|
|
WHERE t_articles.article_reference_cacic_id = t_article_reference_cacic.oid AND
|
|
t_articles.classification_atc_id = 0 AND
|
|
t_article_reference_cacic.classification_atc_id <> 0;
|
|
UPDATE eco.t_articles
|
|
SET ucd_id = t_article_reference_cacic.ucd_id
|
|
FROM eco.t_article_reference_cacic
|
|
WHERE t_articles.article_reference_cacic_id = t_article_reference_cacic.oid AND
|
|
t_articles.ucd_id = 0 AND
|
|
t_article_reference_cacic.ucd_id <> 0;
|
|
UPDATE eco.t_articles SET
|
|
article_reference_cacic_code = t_article_reference_cacic.code,
|
|
article_reference_cacic_texte = t_article_reference_cacic.texte_court,
|
|
article_reference_cacic_section_id = t_article_reference_cacic.section_id,
|
|
article_reference_cacic_section_code = t_article_reference_cacic.section_code,
|
|
article_reference_cacic_section_texte = t_article_reference_cacic.section_texte
|
|
FROM eco.t_article_reference_cacic
|
|
WHERE article_reference_cacic_id = t_article_reference_cacic.oid AND
|
|
(
|
|
article_reference_cacic_code IS DISTINCT FROM t_article_reference_cacic.code OR
|
|
article_reference_cacic_texte IS DISTINCT FROM t_article_reference_cacic.texte_court OR
|
|
article_reference_cacic_section_id IS DISTINCT FROM t_article_reference_cacic.section_id OR
|
|
article_reference_cacic_section_code IS DISTINCT FROM t_article_reference_cacic.section_code OR
|
|
article_reference_cacic_section_texte IS DISTINCT FROM t_article_reference_cacic.section_texte
|
|
);
|
|
UPDATE eco.t_fournisseurs
|
|
SET fournisseur_reference_cacic_id = subview.fournisseur_reference_cacic_id
|
|
FROM
|
|
(
|
|
SELECT texte, MAX(oid) AS fournisseur_reference_cacic_id
|
|
FROM eco.t_fournisseur_reference_cacic
|
|
GROUP BY 1
|
|
HAVING count(*) = 1
|
|
) subview
|
|
WHERE subview.texte ILIKE t_fournisseurs.texte AND
|
|
t_fournisseurs.fournisseur_reference_cacic_id IS DISTINCT FROM subview.fournisseur_reference_cacic_id
|
|
;
|
|
|
|
UPDATE eco.t_fournisseurs SET
|
|
fournisseur_reference_cacic_code = t_fournisseur_reference_cacic.code,
|
|
fournisseur_reference_cacic_texte = t_fournisseur_reference_cacic.texte_court,
|
|
fournisseur_reference_cacic_section_id = t_fournisseur_reference_cacic.section_id,
|
|
fournisseur_reference_cacic_section_code = t_fournisseur_reference_cacic.section_code,
|
|
fournisseur_reference_cacic_section_texte = t_fournisseur_reference_cacic.section_texte
|
|
FROM eco.t_fournisseur_reference_cacic
|
|
WHERE fournisseur_reference_cacic_id = t_fournisseur_reference_cacic.oid AND
|
|
(
|
|
fournisseur_reference_cacic_code IS DISTINCT FROM t_fournisseur_reference_cacic.code OR
|
|
fournisseur_reference_cacic_texte IS DISTINCT FROM t_fournisseur_reference_cacic.texte_court OR
|
|
fournisseur_reference_cacic_section_id IS DISTINCT FROM t_fournisseur_reference_cacic.section_id OR
|
|
fournisseur_reference_cacic_section_code IS DISTINCT FROM t_fournisseur_reference_cacic.section_code OR
|
|
fournisseur_reference_cacic_section_texte IS DISTINCT FROM t_fournisseur_reference_cacic.section_texte
|
|
);
|
|
|
|
|
|
|
|
RETURN 'OK';
|
|
END;
|