cti
/
eco-gitlab
12
0
Fork 0
Code Issues 0 Pull Requests 0 Projects 0 Releases 0 Wiki Activity
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.
3 Commits
1 Branch
5.8 MiB
 
 
 
Branch: master
Branches Tags
${ item.name }
Create branch ${ searchTerm }
from 'master'
${ noResults }
eco-gitlab/db/schema2/functions/cti_reorganize_indicateur_c...

454 lines
19 KiB
Raw Permalink Blame History

return: text
lang: plpgsql
src: |
DECLARE
sqlcmd text;
def_cursor refcursor;
_schemaname text;
result TEXT;
BEGIN
-- Types d'articles
DROP TABLE IF EXISTS w_indicateur_condition;
CREATE TEMP TABLE w_indicateur_condition AS
SELECT
'CTI_TYPEARTICLE#'::text || v_source.oid::text AS code, 'Type article'::text AS table_texte, 'TYA'::text AS table_code,
v_source.texte AS texte,
v_source.texte AS texte_court,
'type_article_id'::text AS column_name,
v_source.oid AS value,
'TYPE_ARTICLE='||v_source.oid AS view_select_rule,
ARRAY['9'::text, COALESCE('DT#'||t_source.code,v_source.texte)]::text[] AS arbre_code,
ARRAY['Valeurs', v_source.texte]::text[] AS arbre_texte
FROM eco.v_types_articles_1 v_source
LEFT JOIN eco.t_types_articles t_source ON v_source.oid LIKE 'DT%' AND t_source.oid = substr(v_source.oid,3)
WHERE v_source.oid NOT LIKE 'LC%' AND
v_source.oid <> 'DT0' AND
v_source.oid NOT LIKE '-%'
;
-- Categorie Articles
INSERT INTO w_indicateur_condition
SELECT
'CTI_CATARTICLE#' || v_source.oid::text AS code, 'Catégorie article' AS table_texte, 'CAA' AS table_code,
v_source.texte AS texte,
v_source.texte AS texte_court,
'categorie_article_id'::text AS column_name,
v_source.oid AS value,
'CATEGORIE_ARTICLE='||v_source.oid AS view_select_rule,
ARRAY['9'::text, COALESCE('DT#'||t_source.code,v_source.texte)] AS arbre_code,
ARRAY['Valeurs', v_source.texte] AS arbre_texte
FROM eco.v_categories_articles_1 v_source
LEFT JOIN eco.t_categories_articles t_source ON v_source.oid LIKE 'DT%' AND t_source.oid = substr(v_source.oid,3)
WHERE v_source.oid NOT LIKE 'LC%' AND
v_source.oid <> 'DT0' AND
v_source.oid NOT LIKE '-%'
;
-- Famille Articles
INSERT INTO w_indicateur_condition
SELECT
'CTI_FAMARTICLE#' || v_source.oid::text AS code, 'Famille article' AS table_texte, 'FAA' AS table_code,
v_source.texte AS texte,
v_source.texte AS texte_court,
'famille_article_id'::text AS column_name,
v_source.oid AS value,
'FAMILLE_ARTICLE='||v_source.oid AS view_select_rule,
ARRAY['9'::text, COALESCE('DT#'||t_source.code,v_source.texte)] AS arbre_code,
ARRAY['Valeurs', v_source.texte] AS arbre_texte
FROM eco.v_familles_articles_1 v_source
LEFT JOIN eco.t_familles_articles t_source ON v_source.oid LIKE 'DT%' AND t_source.oid = substr(v_source.oid,3)
WHERE v_source.oid NOT LIKE 'LC%' AND
v_source.oid <> 'DT0' AND
v_source.oid NOT LIKE '-%'
;
-- Articles
INSERT INTO w_indicateur_condition
SELECT
'CTI_ARTICLE#' || v_source.oid::text AS code, 'Article' AS table_texte, 'ART' AS table_code,
v_source.texte AS texte,
v_source.texte AS texte_court,
'article_id'::text AS column_name,
v_source.oid AS value,
'ARTICLE='||v_source.oid AS view_select_rule,
ARRAY['9'::text, COALESCE('DT#'||t_source.code,v_source.texte)] AS arbre_code,
ARRAY['Valeurs', v_source.texte] AS arbre_texte
FROM eco.v_articles_1 v_source
LEFT JOIN eco.t_articles t_source ON v_source.oid LIKE 'DT%' AND t_source.oid = substr(v_source.oid,3)
WHERE v_source.oid NOT LIKE 'LC%' AND
v_source.oid NOT LIKE 'DT%' AND
v_source.oid <> 'DT0' AND
v_source.oid NOT LIKE '-%'
;
-- Fournisseurs
INSERT INTO w_indicateur_condition
SELECT
'CTI_FOURNISSEUR#' || v_source.oid::text AS code, 'Fournisseur' AS table_texte, 'FOU' AS table_code,
v_source.texte AS texte,
v_source.texte AS texte_court,
'fournisseur_id'::text AS column_name,
v_source.oid AS value,
'FOURNISSEUR='||v_source.oid AS view_select_rule,
ARRAY['9'::text, COALESCE('DT#'||t_source.code,v_source.texte)] AS arbre_code,
ARRAY['Valeurs', v_source.texte] AS arbre_texte
FROM eco.v_fournisseurs_1 v_source
LEFT JOIN eco.t_fournisseurs t_source ON v_source.oid LIKE 'DT%' AND t_source.oid = substr(v_source.oid,3)
WHERE v_source.oid NOT LIKE 'LC%' AND
v_source.oid NOT LIKE 'DT%' AND
v_source.oid <> 'DT0' AND
v_source.oid NOT LIKE '-%'
;
-- Types mouvement
INSERT INTO w_indicateur_condition
SELECT
'CTI_TYPEMOUVEMENT#' || v_source.oid::text AS code, 'Type mvt' AS table_texte, 'TYM' AS table_code,
v_source.texte AS texte,
v_source.texte AS texte_court,
'type_mouvement_id'::text AS column_name,
v_source.oid AS value,
'TYPE_MOUVEMENT='||v_source.oid AS view_select_rule,
ARRAY['9'::text, COALESCE('DT#'||t_source.code,v_source.texte)] AS arbre_code,
ARRAY['Valeurs', v_source.texte] AS arbre_texte
FROM eco.v_types_mouvements_1 v_source
LEFT JOIN eco.t_types_mouvements t_source ON v_source.oid LIKE 'DT%' AND t_source.oid = substr(v_source.oid,3)
WHERE v_source.oid NOT LIKE 'LC%' AND
v_source.oid <> 'DT0' AND
v_source.oid NOT LIKE '-%'
;
-- Types compte
INSERT INTO w_indicateur_condition
SELECT
'CTI_COMPTE#' || v_source.oid::text AS code, 'Compte' AS table_texte, 'CPT' AS table_code,
v_source.texte AS texte,
v_source.texte AS texte_court,
'compte_article_id'::text AS column_name,
v_source.oid AS value,
'COMPTE_ARTICLE='||v_source.oid AS view_select_rule,
ARRAY['9'::text, COALESCE('DT#'||t_source.code,v_source.texte)] AS arbre_code,
ARRAY['Valeurs', v_source.texte] AS arbre_texte
FROM eco.v_compte_1 v_source
LEFT JOIN eco.t_compte t_source ON v_source.oid LIKE 'DT%' AND t_source.oid = substr(v_source.oid,3)
WHERE v_source.oid NOT LIKE 'LC%' AND
v_source.oid <> 'DT0' AND
v_source.oid NOT LIKE '-%'
;
-- Unite de sortie
INSERT INTO w_indicateur_condition
SELECT
'CTI_UFMOUVEMENT#' || v_source.oid::text AS code, 'UF Mvt' AS table_texte, 'UFO' AS table_code,
v_source.texte AS texte,
v_source.texte AS texte_court,
'unite_fonctionnelle_id'::text AS column_name,
v_source.oid AS value,
'UNITE_FONCTIONNELLE='||v_source.oid AS view_select_rule,
ARRAY['9'::text, COALESCE('DT#'||t_source.code,v_source.texte)] AS arbre_code,
ARRAY['Valeurs', v_source.texte] AS arbre_texte
FROM eco.v_unites_fonctionnelles_1 v_source
LEFT JOIN eco.t_unites_fonctionnelles t_source ON v_source.oid LIKE 'DT%' AND t_source.oid = substr(v_source.oid,3)
WHERE v_source.oid NOT LIKE 'LC%' AND
v_source.oid <> 'DT0' AND
v_source.oid NOT LIKE '-%'
;
-- Unite de sortie
INSERT INTO w_indicateur_condition
SELECT
'CTI_POLE#'::text || v_source.oid::text AS code, 'Pole'::text AS table_texte, 'POL'::text AS table_code,
v_source.texte AS texte,
v_source.texte AS texte_court,
'pole_id'::text AS column_name,
v_source.oid AS value,
'POL='||v_source.oid AS view_select_rule,
ARRAY['9'::text, COALESCE('DT#'||t_source.code,v_source.texte)] AS arbre_code,
ARRAY['Valeurs'::text, v_source.texte] AS arbre_texte
FROM eco.v_pole_2 v_source
LEFT JOIN eco.t_pole t_source ON v_source.oid LIKE 'DT%' AND t_source.oid = substr(v_source.oid,3)
WHERE v_source.oid NOT LIKE 'LC%' AND
v_source.oid <> 'DT0' AND
v_source.oid NOT LIKE '-%'
;
-- Ne pas garder les listes vides
-- Ajout des valeurs à zéro
INSERT INTO w_indicateur_condition
SELECT *
FROM
(
SELECT
split_part(code,'#',1)||'#'||'DT0' AS code,
MAX(table_texte) AS table_texte,
MAX(table_code) AS table_code,
chr(127)||MAX(table_texte) || ' non renseigné(e)' AS texte,
'Non renseigné(e)'::text AS texte_court,
MAX(column_name) AS column_name,
'DT0'::text AS value,
MAX(split_part(view_select_rule,'=',1)||'=DT0') AS view_select_rule,
ARRAY['9','DT'||chr(127)||'**'] AS arbre_code,
ARRAY['Valeurs','Non renseigné(e)'] AS arbre_texte
FROM w_indicateur_condition
WHERE value LIKE 'DT%'
GROUP BY 1
) subview
WHERE code NOT IN (SELECT code FROM w_indicateur_condition)
;
-- Intitulés pour les classes et les listes
DROP TABLE IF EXISTS w_indicateur_condition_classes;
CREATE TEMP TABLE w_indicateur_condition_classes AS
SELECT
w_indicateur_condition.code,
t_classes.texte || '. ' || t_classes_sections.texte AS texte,
t_classes_sections.texte AS texte_court,
ARRAY[
'1-Classe'||to_char(t_classes.sequence,'FM00'),
'LS#'||table_code||':'||CASE WHEN t_classes.code <> '' THEN t_classes.code ELSE '*CLS'||to_char(t_classes.sequence,'FM00') END||':'||t_classes_sections.code
]::text[] AS arbre_code,
ARRAY[
t_classes.texte,
t_classes_sections.texte
]::text[] AS arbre_texte,
w_indicateur_condition.value AS value
FROM w_indicateur_condition
JOIN eco.t_listes_tables ON t_listes_tables.code = table_code
JOIN eco.t_classes ON
t_classes.table_id = t_listes_tables.oid AND
to_char(t_classes.sequence,'FM00') = substr(w_indicateur_condition.value,3,2)
JOIN eco.t_classes_sections ON
t_classes_sections.classe_id = t_classes.oid AND
t_classes_sections.oid::text = substr(w_indicateur_condition.value,5)
WHERE w_indicateur_condition.code LIKE '%LS%'
;
INSERT INTO w_indicateur_condition_classes
SELECT
w_indicateur_condition.code,
t_classes.texte || '. Sections attribuées' AS texte,
'Sections attribuées' AS texte_court,
ARRAY[
'1-Classe'||to_char(t_classes.sequence,'FM00'),
'LC#'||table_code||':'||CASE WHEN t_classes.code <> '' THEN t_classes.code ELSE '*CLS'||to_char(t_classes.sequence,'FM00') END
]::text[] AS arbre_code,
ARRAY[
t_classes.texte,
'Sections attribuées'::text
]::text[] AS arbre_texte,
w_indicateur_condition.value AS value
FROM w_indicateur_condition
JOIN eco.t_listes_tables ON t_listes_tables.code = table_code
JOIN eco.t_classes ON
t_classes.table_id = t_listes_tables.oid AND
to_char(t_classes.sequence,'FM00') = substr(w_indicateur_condition.value,3,2) AND
t_classes.oid::text = substr(w_indicateur_condition.value,5)
WHERE w_indicateur_condition.code LIKE '%LC%'
;
INSERT INTO w_indicateur_condition_classes
SELECT
w_indicateur_condition.code,
chr(127)||t_classes.texte || '. ' || 'Section non attribuée' AS texte,
'Section non attribuée' AS texte_court,
ARRAY[
'1-Classe'||to_char(t_classes.sequence,'FM00'),
chr(127)||'LS#'||table_code||':'||CASE WHEN t_classes.code <> '' THEN t_classes.code ELSE '*CLS'||to_char(t_classes.sequence,'FM00') END||':'
] AS arbre_code,
ARRAY[
t_classes.texte,
'Section non attribuée'
] AS arbre_texte,
'LX'||t_classes.oid AS value
FROM w_indicateur_condition
JOIN eco.t_listes_tables ON t_listes_tables.code = table_code
JOIN eco.t_classes ON
t_classes.table_id = t_listes_tables.oid AND
to_char(t_classes.sequence,'FM00') = substr(split_part(w_indicateur_condition.code,'#',2),3,2)
WHERE w_indicateur_condition.code LIKE '%LS%' AND
substr(split_part(w_indicateur_condition.code,'#',2),5) = '0'
;
INSERT INTO w_indicateur_condition_classes
SELECT
w_indicateur_condition.code,
table_texte || ' ' || t_listes.texte AS texte,
t_listes.texte AS texte_court,
ARRAY[
CASE WHEN t_listes.is_cti = '1' THEN '2-Listes CTI' ELSE '3-Listes ETS' END,
'LI#'||table_code||':'||CASE WHEN t_listes.code <> '' THEN t_listes.code ELSE '*LST'||t_listes.oid END
] AS arbre_code,
ARRAY[
CASE WHEN t_listes.is_cti = '1' THEN '2-Listes CTI' ELSE '3-Listes ETS' END,
t_listes.texte
] AS arbre_texte,
w_indicateur_condition.value AS value
FROM w_indicateur_condition
JOIN eco.t_listes_tables ON t_listes_tables.code = table_code
JOIN eco.t_listes ON
t_listes.table_id = t_listes_tables.oid AND
t_listes.oid::text = substr(split_part(w_indicateur_condition.code,'#',2),3)
WHERE w_indicateur_condition.code LIKE '%#LI%'
;
INSERT INTO w_indicateur_condition_classes
SELECT
w_indicateur_condition.code,
table_texte || ' sauf ' || t_listes.texte AS texte,
'Sauf ' || t_listes.texte AS texte_court,
ARRAY[
CASE WHEN t_listes.is_cti = '1' THEN '2-Listes CTI' ELSE '3-Listes ETS' END,
'SL#'||table_code||':'||CASE WHEN t_listes.code <> '' THEN t_listes.code ELSE '*LST'||t_listes.oid END
] AS arbre_code,
ARRAY[
CASE WHEN t_listes.is_cti = '1' THEN '2-Listes CTI' ELSE '3-Listes ETS' END,
'Sauf ' || t_listes.texte
] AS arbre_texte,
w_indicateur_condition.value AS value
FROM w_indicateur_condition
JOIN eco.t_listes_tables ON t_listes_tables.code = table_code
JOIN eco.t_listes ON
t_listes.table_id = t_listes_tables.oid AND
t_listes.oid::text = substr(split_part(w_indicateur_condition.code,'#',2),3)
WHERE w_indicateur_condition.code LIKE '%#SL%'
;
UPDATE w_indicateur_condition SET
texte = w_indicateur_condition_classes.texte,
texte_court = w_indicateur_condition_classes.texte_court,
arbre_code = w_indicateur_condition_classes.arbre_code,
arbre_texte = w_indicateur_condition_classes.arbre_texte,
value = w_indicateur_condition_classes.value
FROM w_indicateur_condition_classes
WHERE w_indicateur_condition.code = w_indicateur_condition_classes.code
;
INSERT INTO eco.t_indicateur_condition(code, texte,texte_court,column_name,column_texte,value,view_select_rule, arbre_code, arbre_texte)
SELECT code, texte,texte_court,column_name,table_texte,value,view_select_rule, arbre_code, arbre_texte
FROM w_indicateur_condition
WHERE code NOT IN (SELECT code FROM eco.t_indicateur_condition WHERE code IS NOT NULL)
;
UPDATE eco.t_indicateur_condition SET
texte = w_indicateur_condition.texte,
texte_court = w_indicateur_condition.texte_court,
column_name = w_indicateur_condition.column_name,
column_texte = w_indicateur_condition.table_texte,
value = w_indicateur_condition.value,
view_select_rule = w_indicateur_condition.view_select_rule,
arbre_code = w_indicateur_condition.arbre_code,
arbre_texte = w_indicateur_condition.arbre_texte
FROM w_indicateur_condition
WHERE w_indicateur_condition.code = t_indicateur_condition.code AND
(
t_indicateur_condition.texte IS DISTINCT FROM w_indicateur_condition.texte OR
t_indicateur_condition.texte_court IS DISTINCT FROM w_indicateur_condition.texte_court OR
t_indicateur_condition.column_name IS DISTINCT FROM w_indicateur_condition.column_name OR
t_indicateur_condition.column_texte IS DISTINCT FROM w_indicateur_condition.table_texte OR
t_indicateur_condition.value IS DISTINCT FROM w_indicateur_condition.value OR
t_indicateur_condition.view_select_rule IS DISTINCT FROM w_indicateur_condition.view_select_rule OR
t_indicateur_condition.arbre_code IS DISTINCT FROM w_indicateur_condition.arbre_code OR
t_indicateur_condition.arbre_texte IS DISTINCT FROM w_indicateur_condition.arbre_texte
)
;
DELETE FROM eco.t_indicateur_condition
WHERE code LIKE 'CTI%' AND
code NOT IN (SELECT code FROM w_indicateur_condition)
;
UPDATE eco.t_indicateur_condition
SET external_code = split_part(code,'#',1)||'#'||CASE
WHEN arbre_code[1] LIKE '1-Classe%' AND arbre_code[2] NOT LIKE '%:%' THEN 'LS'::text||substr(arbre_code[1],9) || '-'::text
WHEN arbre_code[1] LIKE '1-Classe%' AND arbre_code[2] LIKE '%:%' THEN ''
ELSE '' END||CASE WHEN code NOT LIKE '%#DT0' THEN arbre_code[2] ELSE 'DT0' END
WHERE external_code IS DISTINCT FROM (split_part(code,'#',1)||'#'||CASE
WHEN arbre_code[1] LIKE '1-Classe%' AND arbre_code[2] NOT LIKE '%:%' THEN 'LS'::text||substr(arbre_code[1],9) || '-'::text
WHEN arbre_code[1] LIKE '1-Classe%' AND arbre_code[2] LIKE '%:%' THEN ''
ELSE '' END||CASE WHEN code NOT LIKE '%#DT0' THEN arbre_code[2] ELSE 'DT0' END)
;
UPDATE eco.t_indicateur_condition SET
column_texte = CASE column_name
WHEN 'type_article_id' THEN 'Type article'
WHEN 'unite_fonctionnelle_id' THEN 'UF'
WHEN 'compte_article_id' THEN 'Compte'
ELSE replace(Upper(substr(split_part(column_name,'_id',1),1,1)) || substr(split_part(column_name,'_id',1),2),'_',' ') END
WHERE
column_texte IS DISTINCT FROM
CASE column_name
WHEN 'type_article_id' THEN 'Type article'
WHEN 'unite_fonctionnelle_id' THEN 'UF'
WHEN 'compte_article_id' THEN 'Compte'
ELSE replace(Upper(substr(split_part(column_name,'_id',1),1,1)) || substr(split_part(column_name,'_id',1),2),'_',' ') END
;
DROP TABLE IF EXISTS w_indicateur_condition_table;
CREATE TEMP TABLE w_indicateur_condition_table AS
SELECT t_indicateur_condition.oid AS indicateur_condition_id,
''::text As table_name,
t_indicateur_condition.column_name || ' ' ||
CASE
WHEN t_indicateur_condition.value LIKE 'LS%' THEN 'IN'
WHEN t_indicateur_condition.value LIKE 'LI%' THEN 'IN'
WHEN t_indicateur_condition.value LIKE 'SL%' THEN 'NOT IN'
WHEN t_indicateur_condition.value LIKE 'DT%' THEN '='
ELSE '='
END || ' ' ||
CASE
WHEN t_indicateur_condition.value LIKE 'LS%' THEN
'(SELECT to_id FROM eco.t_classes_sections_elements WHERE section_id = ' || substr(value,5) || ')'
WHEN t_indicateur_condition.value LIKE 'LI%' THEN
'(SELECT to_id FROM eco.t_listes_contenu WHERE liste_id = ' || substr(value,3) || ')'
WHEN t_indicateur_condition.value LIKE 'SL%' THEN
'(SELECT to_id FROM eco.t_listes_contenu WHERE liste_id = ' || substr(value,3) || ')'
WHEN t_indicateur_condition.value LIKE 'DT%' THEN
substr(value,3)
ELSE substr(value,3)
END AS check_where
FROM eco.t_indicateur_condition
WHERE t_indicateur_condition.code LIKE 'CTI%'
;
INSERT INTO eco.t_indicateur_condition_table(indicateur_condition_id, table_name, check_where)
SELECT indicateur_condition_id, table_name, check_where
FROM w_indicateur_condition_table
WHERE (indicateur_condition_id::text || '-' || table_name) NOT IN
(SELECT indicateur_condition_id::text || '-' || table_name FROM eco.t_indicateur_condition_table WHERE table_name IS NOT NULL)
;
UPDATE eco.t_indicateur_condition_table SET
check_where = w_indicateur_condition_table.check_where
FROM w_indicateur_condition_table
WHERE t_indicateur_condition_table.indicateur_condition_id = w_indicateur_condition_table.indicateur_condition_id AND
t_indicateur_condition_table.table_name = w_indicateur_condition_table.table_name AND
(
t_indicateur_condition_table.check_where IS DISTINCT FROM w_indicateur_condition_table.check_where
)
;
DELETE FROM eco.t_indicateur_condition_table
USING eco.t_indicateur_condition
WHERE t_indicateur_condition.oid = t_indicateur_condition_table.indicateur_condition_id AND
t_indicateur_condition.code LIKE 'CTI%' AND
(indicateur_condition_id::text || '-' || table_name) NOT IN
(SELECT indicateur_condition_id::text || '-' || table_name FROM w_indicateur_condition_table WHERE table_name IS NOT NULL)
;
RETURN 'OK';
END;