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.
174 lines
14 KiB
174 lines
14 KiB
-- 1/3 : Création de la table temporaire
|
|
DROP TABLE IF EXISTS w_dbsetup
|
|
;
|
|
|
|
CREATE TEMP TABLE w_dbsetup AS
|
|
SELECT
|
|
t_rapports.oid,
|
|
t_rapports.code,
|
|
t_rapports.texte,
|
|
t_rapports.is_cti,
|
|
template.code AS template_code,
|
|
t_rapports.is_template_updatable,
|
|
ARRAY[
|
|
CASE WHEN NOT (classe_0.sequence = 30 AND table_0.code = (SELECT valeur FROM eco.t_divers WHERE code = 'CLE_REPARTITION')) THEN table_0.code ELSE 'TYA' END ||'|'||classe_0.code||'|'||section_0.code,
|
|
CASE WHEN NOT (classe_1.sequence = 30 AND table_1.code = (SELECT valeur FROM eco.t_divers WHERE code = 'CLE_REPARTITION')) THEN table_1.code ELSE 'TYA' END ||'|'||classe_1.code||'|'||section_1.code,
|
|
CASE WHEN NOT (classe_2.sequence = 30 AND table_2.code = (SELECT valeur FROM eco.t_divers WHERE code = 'CLE_REPARTITION')) THEN table_2.code ELSE 'TYA' END ||'|'||classe_2.code||'|'||section_2.code,
|
|
CASE WHEN NOT (classe_3.sequence = 30 AND table_3.code = (SELECT valeur FROM eco.t_divers WHERE code = 'CLE_REPARTITION')) THEN table_3.code ELSE 'TYA' END ||'|'||classe_3.code||'|'||section_3.code,
|
|
CASE WHEN NOT (classe_4.sequence = 30 AND table_4.code = (SELECT valeur FROM eco.t_divers WHERE code = 'CLE_REPARTITION')) THEN table_4.code ELSE 'TYA' END ||'|'||classe_4.code||'|'||section_4.code] AS condition_codes,
|
|
t_rapports.is_essentiel
|
|
FROM eco.t_rapports
|
|
LEFT JOIN eco.t_rapports template ON t_rapports.rapport_template_id = template.oid
|
|
|
|
LEFT JOIN eco.t_indicateur_condition condition_0 ON t_rapports.rapport_indicateur_condition_id[1] = condition_0.oid
|
|
LEFT JOIN eco.t_listes_tables table_0 ON regexp_replace(regexp_replace(regexp_replace(regexp_replace(table_0.name,'^t_',''),'s_','','g'),'x',''),'s$','') || 'id' = regexp_replace(condition_0.column_name,'_','','g')
|
|
LEFT JOIN eco.t_classes classe_0 ON table_0.oid = classe_0.table_id AND classe_0.sequence = substring(split_part(condition_0.code,'#',2),3,2) AND left(split_part(condition_0.code,'#',2),2) = 'LS'
|
|
LEFT JOIN eco.t_classes_sections section_0 ON classe_0.oid = section_0.classe_id AND section_0.oid = right(split_part(condition_0.code,'#',2),-4)
|
|
|
|
LEFT JOIN eco.t_indicateur_condition condition_1 ON t_rapports.rapport_indicateur_condition_id[2] = condition_1.oid
|
|
LEFT JOIN eco.t_listes_tables table_1 ON regexp_replace(regexp_replace(regexp_replace(regexp_replace(table_1.name,'^t_',''),'s_','','g'),'x',''),'s$','') || 'id' = regexp_replace(condition_1.column_name,'_','','g')
|
|
LEFT JOIN eco.t_classes classe_1 ON table_1.oid = classe_1.table_id AND classe_1.sequence = substring(split_part(condition_1.code,'#',2),3,2) AND left(split_part(condition_1.code,'#',2),2) = 'LS'
|
|
LEFT JOIN eco.t_classes_sections section_1 ON classe_1.oid = section_1.classe_id AND section_1.oid = right(split_part(condition_1.code,'#',2),-4)
|
|
|
|
LEFT JOIN eco.t_indicateur_condition condition_2 ON t_rapports.rapport_indicateur_condition_id[3] = condition_2.oid
|
|
LEFT JOIN eco.t_listes_tables table_2 ON regexp_replace(regexp_replace(regexp_replace(regexp_replace(table_2.name,'^t_',''),'s_','','g'),'x',''),'s$','') || 'id' = regexp_replace(condition_2.column_name,'_','','g')
|
|
LEFT JOIN eco.t_classes classe_2 ON table_2.oid = classe_2.table_id AND classe_2.sequence = substring(split_part(condition_2.code,'#',2),3,2) AND left(split_part(condition_2.code,'#',2),2) = 'LS'
|
|
LEFT JOIN eco.t_classes_sections section_2 ON classe_2.oid = section_2.classe_id AND section_2.oid = right(split_part(condition_2.code,'#',2),-4)
|
|
|
|
LEFT JOIN eco.t_indicateur_condition condition_3 ON t_rapports.rapport_indicateur_condition_id[4] = condition_3.oid
|
|
LEFT JOIN eco.t_listes_tables table_3 ON regexp_replace(regexp_replace(regexp_replace(regexp_replace(table_3.name,'^t_',''),'s_','','g'),'x',''),'s$','') || 'id' = regexp_replace(condition_3.column_name,'_','','g')
|
|
LEFT JOIN eco.t_classes classe_3 ON table_3.oid = classe_3.table_id AND classe_3.sequence = substring(split_part(condition_3.code,'#',2),3,2) AND left(split_part(condition_3.code,'#',2),2) = 'LS'
|
|
LEFT JOIN eco.t_classes_sections section_3 ON classe_3.oid = section_3.classe_id AND section_3.oid = right(split_part(condition_3.code,'#',2),-4)
|
|
|
|
LEFT JOIN eco.t_indicateur_condition condition_4 ON t_rapports.rapport_indicateur_condition_id[5] = condition_4.oid
|
|
LEFT JOIN eco.t_listes_tables table_4 ON regexp_replace(regexp_replace(regexp_replace(regexp_replace(table_4.name,'^t_',''),'s_','','g'),'x',''),'s$','') || 'id' = regexp_replace(condition_4.column_name,'_','','g')
|
|
LEFT JOIN eco.t_classes classe_4 ON table_4.oid = classe_4.table_id AND classe_4.sequence = substring(split_part(condition_4.code,'#',2),3,2) AND left(split_part(condition_4.code,'#',2),2) = 'LS'
|
|
LEFT JOIN eco.t_classes_sections section_4 ON classe_4.oid = section_4.classe_id AND section_4.oid = right(split_part(condition_4.code,'#',2),-4)
|
|
|
|
WHERE (t_rapports.is_cti = '1' OR t_rapports.code LIKE 'CTI_%') AND
|
|
t_rapports.code <> 'CTI_PORTFO'
|
|
ORDER BY t_rapports.code
|
|
LIMIT 0
|
|
;
|
|
|
|
-- 2/3 : Peuplement de la table temporaire
|
|
INSERT INTO w_dbsetup(
|
|
oid,
|
|
code,
|
|
texte,
|
|
is_cti,
|
|
template_code,
|
|
is_template_updatable,
|
|
condition_codes,
|
|
is_essentiel
|
|
)
|
|
VALUES
|
|
(1, 'CTI_ESS_01', 'Essentiels CTI', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '1'),
|
|
(1, 'CTI_ESS_02', 'Dispositifs Médicaux', '1', 'CTI_ESS_01', '0', '{"TYA|CTI_CLE_REP|DM", "NULL", "NULL", "NULL", "NULL"}', '1'),
|
|
(1, 'CTI_ESS_03', 'Dispostifs Médicaux Implantables', '1', 'CTI_ESS_01', '0', '{"TYA|CTI_CLE_REP|DMI", "NULL", "NULL", "NULL", "NULL"}', '1'),
|
|
(1, 'CTI_ESS_04', 'Médicaments', '1', 'CTI_ESS_01', '0', '{"TYA|CTI_CLE_REP|MED", "NULL", "NULL", "NULL", "NULL"}', '1'),
|
|
(1, 'CTI_ESS_05', 'Parapharmacie', '1', 'CTI_ESS_01', '0', '{"TYA|CTI_CLE_REP|PAR", "NULL", "NULL", "NULL", "NULL"}', '1'),
|
|
(1, 'CTI_ESS_06', 'Economat', '1', 'CTI_ESS_01', '0', '{"TYA|CTI_CLE_REP|ECO", "NULL", "NULL", "NULL", "NULL"}', '1'),
|
|
(1, 'CTI_ESS_07', 'Cuisine', '1', 'CTI_ESS_01', '0', '{"TYA|CTI_CLE_REP|CUI", "NULL", "NULL", "NULL", "NULL"}', '1'),
|
|
(1, 'CTI_ESS_08', 'Lingerie', '1', 'CTI_ESS_01', '0', '{"TYA|CTI_CLE_REP|LIN", "NULL", "NULL", "NULL", "NULL"}', '1'),
|
|
(1, 'CTI_ESS_09', 'Atelier', '1', 'CTI_ESS_01', '0', '{"TYA|CTI_CLE_REP|ATE", "NULL", "NULL", "NULL", "NULL"}', '1'),
|
|
(1, 'CTI_LIST_CMD', 'Justificatif Commandes', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_LIST_CMD2', 'Justificatif Délais et litiges', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_LIST_MVT', 'Justificatif Mouvements', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_LIST_MVT100', 'Justificatif Mouvements et Stocks', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_LIST_MVT2', 'Justificatif Entrées', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_LIST_MVT3', 'Justificatif Sorties', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_LIST_MVT4', 'Justificatif Quantités', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_LIST_MVT5', 'Justificatif Montants', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_LIST_MVT6', 'Justificatif Montants Entrées', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_LIST_MVT7', 'Justificatif Montants sorties', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_VIEW_CMD', 'Commandes', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_VIEW_CMD2', 'Délais et litiges', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_VIEW_CMD3', 'Commandes (engagement)', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_VIEW_MVS', 'Stocks calculés', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_VIEW_MVT', 'Quantités et Montants Mouvements', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_VIEW_MVT2', 'Quantités et Montants Entrées', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_VIEW_MVT3', 'Quantités et Montants Sorties', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_VIEW_MVT4', 'Quantités Mouvements', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_VIEW_MVT5', 'Montants Mouvements', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_VIEW_MVT6', 'Montants Entrées', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_VIEW_MVT7', 'Montants Sorties', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_VIEW_STK', 'Stocks Mensuels', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_VIEW_STK2', 'Quantités stocks mensuels', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0'),
|
|
(1, 'CTI_VIEW_STK3', 'Montants Stocks Mensuels', '1', '', '0', '{"NULL", "NULL", "NULL", "NULL", "NULL"}', '0')
|
|
;
|
|
|
|
-- 3/3 : Màj de la table iCTI
|
|
SELECT eco.cti_reorganize_indicateur_condition();
|
|
|
|
INSERT INTO eco.t_rapports(oid, code, texte, is_cti, is_essentiel)
|
|
SELECT
|
|
oid,
|
|
code,
|
|
texte,
|
|
is_cti,
|
|
is_essentiel
|
|
FROM w_dbsetup
|
|
WHERE oid = 0 AND oid NOT IN (SELECT oid FROM eco.t_rapports);
|
|
|
|
INSERT INTO eco.t_rapports(code, texte, is_cti, is_essentiel)
|
|
SELECT
|
|
code,
|
|
texte,
|
|
is_cti,
|
|
is_essentiel
|
|
FROM w_dbsetup
|
|
WHERE oid <> 0
|
|
AND code NOT IN (SELECT code FROM eco.t_rapports)
|
|
;
|
|
|
|
UPDATE eco.t_rapports SET
|
|
texte = w_dbsetup.texte,
|
|
is_cti = w_dbsetup.is_cti,
|
|
is_essentiel = w_dbsetup.is_essentiel,
|
|
rapport_template_id = template.oid,
|
|
is_template_updatable = w_dbsetup.is_template_updatable,
|
|
rapport_indicateur_condition_id = ARRAY[condition_0.oid,condition_1.oid,condition_2.oid,condition_3.oid,condition_4.oid]
|
|
FROM w_dbsetup
|
|
LEFT JOIN eco.t_rapports template ON template_code = template.code
|
|
|
|
LEFT JOIN eco.t_listes_tables table_0 ON split_part(condition_codes[1],'|',1) = table_0.code
|
|
OR table_0.code = (SELECT t_listes_tables.code FROM eco.t_listes_tables JOIN eco.t_classes ON t_classes.code = 'CTI_CLE_REP' AND table_id = t_listes_tables.oid)
|
|
LEFT JOIN eco.t_classes classe_0 ON table_0.oid = classe_0.table_id AND classe_0.code = split_part(condition_codes[1] ,'|',2)
|
|
LEFT JOIN eco.t_classes_sections section_0 ON classe_0.oid = section_0.classe_id AND section_0.code = split_part(condition_codes[1] ,'|',3)
|
|
LEFT JOIN eco.t_indicateur_condition condition_0 ON regexp_replace(regexp_replace(regexp_replace(regexp_replace(table_0.name,'^t_',''),'s_','','g'),'x',''),'s$','') || 'id' = regexp_replace(condition_0.column_name,'_','','g')
|
|
AND classe_0.sequence = substring(split_part(condition_0.code,'#',2),3,2) AND left(split_part(condition_0.code,'#',2),2) = 'LS'
|
|
AND section_0.oid = right(split_part(condition_0.code,'#',2),-4)
|
|
|
|
LEFT JOIN eco.t_listes_tables table_1 ON split_part(condition_codes[2],'|',1) = table_1.code
|
|
OR table_1.code = (SELECT t_listes_tables.code FROM eco.t_listes_tables JOIN eco.t_classes ON t_classes.code = 'CTI_CLE_REP' AND table_id = t_listes_tables.oid)
|
|
LEFT JOIN eco.t_classes classe_1 ON table_1.oid = classe_1.table_id AND classe_1.code = split_part(condition_codes[2] ,'|',2)
|
|
LEFT JOIN eco.t_classes_sections section_1 ON classe_1.oid = section_1.classe_id AND section_1.code = split_part(condition_codes[2] ,'|',3)
|
|
LEFT JOIN eco.t_indicateur_condition condition_1 ON regexp_replace(regexp_replace(regexp_replace(regexp_replace(table_1.name,'^t_',''),'s_','','g'),'x',''),'s$','') || 'id' = regexp_replace(condition_1.column_name,'_','','g')
|
|
AND classe_1.sequence = substring(split_part(condition_1.code,'#',2),3,2) AND left(split_part(condition_1.code,'#',2),2) = 'LS'
|
|
AND section_1.oid = right(split_part(condition_1.code,'#',2),-4)
|
|
|
|
LEFT JOIN eco.t_listes_tables table_2 ON split_part(condition_codes[3],'|',1) = table_2.code
|
|
OR table_2.code = (SELECT t_listes_tables.code FROM eco.t_listes_tables JOIN eco.t_classes ON t_classes.code = 'CTI_CLE_REP' AND table_id = t_listes_tables.oid)
|
|
LEFT JOIN eco.t_classes classe_2 ON table_2.oid = classe_2.table_id AND classe_2.code = split_part(condition_codes[3] ,'|',2)
|
|
LEFT JOIN eco.t_classes_sections section_2 ON classe_2.oid = section_2.classe_id AND section_2.code = split_part(condition_codes[3] ,'|',3)
|
|
LEFT JOIN eco.t_indicateur_condition condition_2 ON regexp_replace(regexp_replace(regexp_replace(regexp_replace(table_2.name,'^t_',''),'s_','','g'),'x',''),'s$','') || 'id' = regexp_replace(condition_2.column_name,'_','','g')
|
|
AND classe_2.sequence = substring(split_part(condition_2.code,'#',2),3,2) AND left(split_part(condition_2.code,'#',2),2) = 'LS'
|
|
AND section_2.oid = right(split_part(condition_2.code,'#',2),-4)
|
|
|
|
LEFT JOIN eco.t_listes_tables table_3 ON split_part(condition_codes[4],'|',1) = table_3.code
|
|
OR table_3.code = (SELECT t_listes_tables.code FROM eco.t_listes_tables JOIN eco.t_classes ON t_classes.code = 'CTI_CLE_REP' AND table_id = t_listes_tables.oid)
|
|
LEFT JOIN eco.t_classes classe_3 ON table_3.oid = classe_3.table_id AND classe_3.code = split_part(condition_codes[4] ,'|',2)
|
|
LEFT JOIN eco.t_classes_sections section_3 ON classe_3.oid = section_3.classe_id AND section_3.code = split_part(condition_codes[4] ,'|',3)
|
|
LEFT JOIN eco.t_indicateur_condition condition_3 ON regexp_replace(regexp_replace(regexp_replace(regexp_replace(table_3.name,'^t_',''),'s_','','g'),'x',''),'s$','') || 'id' = regexp_replace(condition_3.column_name,'_','','g')
|
|
AND classe_3.sequence = substring(split_part(condition_3.code,'#',2),3,2) AND left(split_part(condition_3.code,'#',2),2) = 'LS'
|
|
AND section_3.oid = right(split_part(condition_3.code,'#',2),-4)
|
|
|
|
LEFT JOIN eco.t_listes_tables table_4 ON split_part(condition_codes[5],'|',1) = table_4.code
|
|
OR table_4.code = (SELECT t_listes_tables.code FROM eco.t_listes_tables JOIN eco.t_classes ON t_classes.code = 'CTI_CLE_REP' AND table_id = t_listes_tables.oid)
|
|
LEFT JOIN eco.t_classes classe_4 ON table_4.oid = classe_4.table_id AND classe_4.code = split_part(condition_codes[5] ,'|',2)
|
|
LEFT JOIN eco.t_classes_sections section_4 ON classe_4.oid = section_4.classe_id AND section_4.code = split_part(condition_codes[5] ,'|',3)
|
|
LEFT JOIN eco.t_indicateur_condition condition_4 ON regexp_replace(regexp_replace(regexp_replace(regexp_replace(table_4.name,'^t_',''),'s_','','g'),'x',''),'s$','') || 'id' = regexp_replace(condition_4.column_name,'_','','g')
|
|
AND classe_4.sequence = substring(split_part(condition_4.code,'#',2),3,2) AND left(split_part(condition_4.code,'#',2),2) = 'LS'
|
|
AND section_4.oid = right(split_part(condition_4.code,'#',2),-4)
|
|
WHERE t_rapports.code = w_dbsetup.code;
|