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.
738 lines
27 KiB
738 lines
27 KiB
return: text
|
|
lang: plpgsql
|
|
parameters:
|
|
p0:
|
|
type: bigint
|
|
name: i_expert_controle_id
|
|
src: |
|
|
DECLARE
|
|
result TEXT;
|
|
|
|
|
|
BEGIN
|
|
RAISE NOTICE '%' , 'Recuperation OPTIMISER';
|
|
|
|
DROP TABLE IF EXISTS w_dboptimizer_line;
|
|
CREATE TEMP TABLE w_dboptimizer_line(in_data text)
|
|
;
|
|
|
|
BEGIN
|
|
PERFORM base.cti_execute(
|
|
'COPY w_dboptimizer_line FROM ''' || t_settings.value || 'modules/rh/settings/RH_dboptimizer.xml'' WITH DELIMITER AS E''\02'' CSV HEADER QUOTE AS E''\01'''
|
|
,1)
|
|
FROM base.t_settings
|
|
WHERE code = 'ENVIRONMENT/DIR'
|
|
;
|
|
EXCEPTION
|
|
WHEN others THEN RETURN 'Dossier application non trouvé';
|
|
END;
|
|
|
|
|
|
DROP TABLE IF EXISTS w_dboptimizer;
|
|
CREATE TEMP TABLE w_dboptimizer AS
|
|
SELECT array_to_string(base.cti_array_accum(translate(in_data,'éêè','e')),'') AS in_data_txt
|
|
FROM w_dboptimizer_line
|
|
WHERE in_data IS NOT NULL
|
|
ORDER BY row_number() OVER()
|
|
;
|
|
|
|
DROP TABLE IF EXISTS w_dboptimizer_xml;
|
|
CREATE TEMP TABLE w_dboptimizer_xml AS
|
|
SELECT xmlroot(xmlparse(document in_data_txt), version '1.0', standalone yes) AS in_data_xml
|
|
FROM w_dboptimizer
|
|
;
|
|
|
|
|
|
DROP TABLE IF EXISTS w_dboptimizer_VIEWNAMEOPTIMIZER;
|
|
CREATE TEMP TABLE w_dboptimizer_VIEWNAMEOPTIMIZER AS
|
|
SELECT *
|
|
FROM
|
|
(
|
|
SELECT i AS VIEWNAMEOPTIMIZER_index,
|
|
(xpath('/DBOPTIMIZER/VIEWNAMEOPTIMIZER['||i||']', in_data_xml))[1] AS VIEWNAMEOPTIMIZER_path,
|
|
(xpath('/DBOPTIMIZER/VIEWNAMEOPTIMIZER['||i||']/@viewName', in_data_xml))[1]::text AS viewName,
|
|
(xpath('/DBOPTIMIZER/VIEWNAMEOPTIMIZER['||i||']/@defaultViewName', in_data_xml))[1]::text AS defaultViewName
|
|
FROM w_dboptimizer_xml
|
|
JOIN generate_series(1,100) AS i ON 1=1
|
|
) subview
|
|
WHERE VIEWNAMEOPTIMIZER_path IS NOT NULL
|
|
;
|
|
|
|
|
|
DROP TABLE IF EXISTS w_dboptimizer_LEFTJOININDICATOR;
|
|
CREATE TEMP TABLE w_dboptimizer_LEFTJOININDICATOR AS
|
|
SELECT *
|
|
FROM
|
|
(
|
|
SELECT VIEWNAMEOPTIMIZER_index,
|
|
i AS LEFTJOININDICATOR_index,
|
|
(xpath('/VIEWNAMEOPTIMIZER/LEFTJOININDICATOR['||i||']', VIEWNAMEOPTIMIZER_path))[1] AS LEFTJOININDICATOR_path,
|
|
(xpath('/VIEWNAMEOPTIMIZER/LEFTJOININDICATOR['||i||']/@leftjoin', VIEWNAMEOPTIMIZER_path))[1]::text AS leftjoin
|
|
FROM w_dboptimizer_VIEWNAMEOPTIMIZER
|
|
JOIN generate_series(1,100) AS i ON 1=1
|
|
) subview
|
|
WHERE LEFTJOININDICATOR_path IS NOT NULL
|
|
;
|
|
|
|
|
|
DROP TABLE IF EXISTS w_dboptimizer_FIELD;
|
|
CREATE TEMP TABLE w_dboptimizer_FIELD AS
|
|
SELECT *
|
|
FROM
|
|
(
|
|
SELECT VIEWNAMEOPTIMIZER_index,
|
|
LEFTJOININDICATOR_index,
|
|
i AS FIELD_index,
|
|
(xpath('/LEFTJOININDICATOR/FIELD['||i||']', LEFTJOININDICATOR_path))[1] AS FIELD_path,
|
|
(xpath('/LEFTJOININDICATOR/FIELD['||i||']/@name', LEFTJOININDICATOR_path))[1]::text AS FIELD_name
|
|
FROM w_dboptimizer_LEFTJOININDICATOR
|
|
JOIN generate_series(1,100) AS i ON 1=1
|
|
) subview
|
|
WHERE FIELD_path IS NOT NULL
|
|
;
|
|
|
|
|
|
|
|
RAISE NOTICE '%' , 'Identification indicateurs';
|
|
|
|
|
|
DROP TABLE IF EXISTS w_expert_controle_data;
|
|
CREATE TEMP TABLE w_expert_controle_data (
|
|
expert_controle_id bigint,
|
|
expert_controle_rule_id bigint,
|
|
expert_controle_rule_index bigint,
|
|
data_type text,
|
|
data_id bigint,
|
|
expert_controle_code text,
|
|
contrat_mois_data text[],
|
|
indicateur_value numeric,
|
|
date_signalement date,
|
|
etat text,
|
|
date_correction date,
|
|
date_evenement date,
|
|
data_code_original text,
|
|
texte text)
|
|
;
|
|
|
|
UPDATE rh.t_expert_controle
|
|
SET date_validite_debut =
|
|
date(
|
|
CASE start_periode
|
|
WHEN 'A0' THEN date_trunc('year',now())
|
|
WHEN 'A1' THEN date_trunc('year',now())-interval '1 year'
|
|
WHEN 'A2' THEN date_trunc('year',now())-interval '2 year'
|
|
WHEN 'M12' THEN date_trunc('month',now())-interval '12 month'
|
|
WHEN 'M24' THEN date_trunc('month',now())-interval '24 month'
|
|
ELSE date_trunc('year',now())-interval '1 year' END
|
|
)
|
|
;
|
|
|
|
-- Période par défaut
|
|
UPDATE rh.t_expert_controle
|
|
SET start_periode = 'A1'
|
|
WHERE oid = 0 AND
|
|
start_periode = ''
|
|
;
|
|
|
|
UPDATE rh.t_expert_controle
|
|
SET date_validite_debut = t_expert_controle0.date_validite_debut
|
|
FROM rh.t_expert_controle t_expert_controle0
|
|
WHERE t_expert_controle0.oid = 0 AND
|
|
t_expert_controle.oid <> 0 AND
|
|
t_expert_controle.start_periode = ''
|
|
;
|
|
|
|
DROP TABLE IF EXISTS w_indicateurs;
|
|
CREATE TEMP TABLE w_indicateurs AS
|
|
SELECT t_expert_controle.oid AS expert_controle_id,
|
|
t_expert_controle.code AS expert_controle_code,
|
|
-- GREATEST(COALESCE(t_expert_controle.date_validite_debut,'00010101'),COALESCE(t_expert_controle0.date_validite_debut,'00010101')) AS date_validite_debut,
|
|
COALESCE(t_expert_controle.date_validite_debut,'00010101') AS date_validite_debut,
|
|
t_expert_controle_rule.oid AS expert_controle_rule_id,
|
|
CASE WHEN indicateur_2_id <> 0 OR indicateur_3_id <> 0 THEN 1::bigint ELSE 0::bigint END AS expert_controle_rule_index,
|
|
t_expert_controle_rule.data_type,
|
|
CASE WHEN t_expert_controle_rule.formula <> '' THEN t_expert_controle_rule.formula ELSE '<> 0' END AS formula,
|
|
NULL::integer[] AS VIEWNAMEOPTIMIZER_index_array,
|
|
t_indicateurs.total_function,
|
|
t_indicateurs.table_name,
|
|
column_name,
|
|
t_expert_controle_rule.indicateur_condition_id,
|
|
t_expert_controle_rule.conditions_operateur AS conditions_operateur,
|
|
''::text AS sqlcmd,
|
|
''::text AS sqlresult
|
|
FROM rh.t_expert_controle
|
|
JOIN rh.t_expert_controle t_expert_controle0 ON
|
|
t_expert_controle0.oid = 0 AND
|
|
t_expert_controle0.is_active = '1'
|
|
JOIN rh.t_expert_controle_rule ON expert_controle_id = t_expert_controle.oid
|
|
JOIN rh.t_indicateurs On indicateur_id = t_indicateurs.oid
|
|
WHERE t_expert_controle.is_active = '1' AND
|
|
(
|
|
i_expert_controle_id = 0 OR
|
|
t_expert_controle.oid = i_expert_controle_id
|
|
)
|
|
;
|
|
|
|
|
|
|
|
|
|
INSERT INTO w_indicateurs
|
|
SELECT t_expert_controle.oid AS expert_controle_id,
|
|
t_expert_controle.code AS expert_controle_code,
|
|
-- GREATEST(COALESCE(t_expert_controle.date_validite_debut,'00010101'),COALESCE(t_expert_controle0.date_validite_debut,'00010101')) AS date_validite_debut,
|
|
COALESCE(t_expert_controle.date_validite_debut,'00010101') AS date_validite_debut,
|
|
t_expert_controle_rule.oid AS expert_controle_rule_id,
|
|
2::bigint AS expert_controle_rule_index,
|
|
t_expert_controle_rule.data_type,
|
|
CASE WHEN t_expert_controle_rule.formula_2 <> '' THEN t_expert_controle_rule.formula_2 ELSE '<> 0' END AS formula,
|
|
NULL::integer[] AS VIEWNAMEOPTIMIZER_index_array,
|
|
t_indicateurs.total_function,
|
|
t_indicateurs.table_name,
|
|
column_name,
|
|
t_expert_controle_rule.indicateur_condition_2_id,
|
|
t_expert_controle_rule.conditions_operateur_2 AS conditions_operateur,
|
|
''::text AS sqlcmd,
|
|
''::text AS sqlresult
|
|
FROM rh.t_expert_controle
|
|
JOIN rh.t_expert_controle t_expert_controle0 ON
|
|
t_expert_controle0.oid = 0 AND
|
|
t_expert_controle0.is_active = '1'
|
|
JOIN rh.t_expert_controle_rule ON expert_controle_id = t_expert_controle.oid
|
|
JOIN rh.t_indicateurs On indicateur_2_id = t_indicateurs.oid
|
|
WHERE t_expert_controle.is_active = '1' AND
|
|
indicateur_2_id <> 0 AND
|
|
(
|
|
i_expert_controle_id = 0 OR
|
|
t_expert_controle.oid = i_expert_controle_id
|
|
)
|
|
;
|
|
|
|
INSERT INTO w_indicateurs
|
|
SELECT t_expert_controle.oid AS expert_controle_id,
|
|
t_expert_controle.code AS expert_controle_code,
|
|
GREATEST(COALESCE(t_expert_controle.date_validite_debut,'00010101'),COALESCE(t_expert_controle0.date_validite_debut,'00010101')) AS date_validite_debut,
|
|
t_expert_controle_rule.oid AS expert_controle_rule_id,
|
|
3::bigint AS expert_controle_rule_index,
|
|
t_expert_controle_rule.data_type,
|
|
CASE WHEN t_expert_controle_rule.formula_3 <> '' THEN t_expert_controle_rule.formula_3 ELSE '<> 0' END AS formula,
|
|
NULL::integer[] AS VIEWNAMEOPTIMIZER_index_array,
|
|
t_indicateurs.total_function,
|
|
t_indicateurs.table_name,
|
|
column_name,
|
|
t_expert_controle_rule.indicateur_condition_3_id,
|
|
t_expert_controle_rule.conditions_operateur_3 AS conditions_operateur,
|
|
''::text AS sqlcmd,
|
|
''::text AS sqlresult
|
|
FROM rh.t_expert_controle
|
|
JOIN rh.t_expert_controle t_expert_controle0 ON
|
|
t_expert_controle0.oid = 0 AND
|
|
t_expert_controle0.is_active = '1'
|
|
JOIN rh.t_expert_controle_rule ON expert_controle_id = t_expert_controle.oid
|
|
JOIN rh.t_indicateurs On indicateur_3_id = t_indicateurs.oid
|
|
WHERE t_expert_controle.is_active = '1' AND
|
|
indicateur_3_id <> 0 AND
|
|
(
|
|
i_expert_controle_id = 0 OR
|
|
t_expert_controle.oid = i_expert_controle_id
|
|
)
|
|
;
|
|
|
|
-- Constitution requete SQL
|
|
UPDATE w_indicateurs
|
|
SET column_name = 'CASE WHEN ' || condition_where || ' THEN ' || column_name || ' ELSE ' || CASE WHEN total_function <> 'COUNT' THEN '0' ELSE 'NULL' END || ' END'
|
|
FROM
|
|
(
|
|
SELECT
|
|
expert_controle_rule_id,
|
|
expert_controle_rule_index,
|
|
array_to_string(
|
|
base.cti_array_accum('('||check_where||')'),
|
|
' '||MAX(conditions_operateur)||' '
|
|
) AS condition_where
|
|
FROM w_indicateurs
|
|
JOIN rh.t_indicateur_condition ON t_indicateur_condition.oid = ANY(indicateur_condition_id)
|
|
JOIN rh.t_indicateur_condition_table ON t_indicateur_condition_table.indicateur_condition_id = t_indicateur_condition.oid
|
|
WHERE check_where <> '' AND t_indicateur_condition_table.table_name NOT IN ('CALENDRIER', 'BUDGET_RH')
|
|
GROUP BY 1,2
|
|
) subview
|
|
WHERE w_indicateurs.expert_controle_rule_id = subview.expert_controle_rule_id AND
|
|
w_indicateurs.expert_controle_rule_index = subview.expert_controle_rule_index
|
|
;
|
|
|
|
UPDATE w_indicateurs
|
|
SET column_name = trim(replace('CASE WHEN (' || column_name ||') IS NOT NULL THEN 1 ELSE 0 END','DISTINCT',''))
|
|
WHERE total_function = 'COUNT'
|
|
;
|
|
|
|
UPDATE rh.t_expert_controle SET
|
|
last_execution_ok = '0',
|
|
last_execution_timestamp = now(),
|
|
last_execution_count = 0
|
|
WHERE is_active = '1' AND
|
|
(
|
|
i_expert_controle_id = 0 OR
|
|
t_expert_controle.oid = i_expert_controle_id
|
|
)
|
|
;
|
|
|
|
|
|
UPDATE w_indicateurs
|
|
SET sqlcmd =
|
|
'@INSERT INTO w_expert_controle_data
|
|
SELECT ' || expert_controle_id::text || '::bigint AS expert_controle_id, ' ||
|
|
expert_controle_rule_id::text || '::bigint AS expert_controle_rule_id, ' ||
|
|
expert_controle_rule_index::text || '::bigint AS expert_controle_rule_index,
|
|
''' ||
|
|
CASE
|
|
WHEN data_type = '' THEN table_name || 'ITM'
|
|
|
|
WHEN data_type = 'ITM' THEN table_name
|
|
WHEN data_type = 'ITMTAB' THEN table_name
|
|
ELSE data_type
|
|
END || '''::text AS data_type,'
|
|
||
|
|
CASE
|
|
WHEN data_type = '' THEN 'source_id'
|
|
WHEN data_type = 'contrat' THEN 'contrat_id'
|
|
WHEN data_type = 'paie' THEN 'contrat_mois_id'
|
|
WHEN data_type = 'ITM' AND table_name = 'v_contrats_mois_1' THEN 'contrat_mois_id'
|
|
WHEN data_type = 'ITMTAB' THEN table_name
|
|
WHEN data_type = 'salarie' THEN 'salarie_id'
|
|
ELSE '0' END ||
|
|
'::bigint AS data_id, ' ||
|
|
'''' || expert_controle_code || '''::text AS expert_controle_code,
|
|
MIN(ARRAY[date_debut_activite::text, date_fin_activite::text, contrat_code::text]) AS contrat_mois_data,
|
|
SUM(' || column_name || ')::numeric AS indicateur_value,
|
|
MAX(date(now())) AS date_signalement,
|
|
''''::text AS etat,
|
|
''20991231'' AS date_correction,
|
|
MIN(' || v_schema_settings_1.property_array[2] || ' ) AS date_evenement,
|
|
''''::text AS data_code_original,
|
|
''''::text AS texte
|
|
FROM ' || v_schema_settings_1.property_array[1] || '
|
|
#LEFTJOIN#
|
|
WHERE ' || v_schema_settings_1.property_array[2] || ' >= ''' || date_validite_debut || ''' AND ' ||
|
|
CASE WHEN formula <> '=0' THEN '( ' || column_name || ' ) ' || replace(formula,'''','''''') ELSE '1=1' END || '
|
|
GROUP BY 1,2,3,4,5 ' ||
|
|
CASE WHEN formula <> '=0' THEN 'HAVING SUM( ' || column_name || ' ) ' || replace(formula,'''','''''') ELSE '' END || '
|
|
;
|
|
'
|
|
FROM rh.v_schema_settings_1
|
|
WHERE table_name||'#1' = v_schema_settings_1.code AND
|
|
v_schema_settings_1.setting_type = 'FROM'
|
|
;
|
|
|
|
PERFORM base.cti_execute('
|
|
UPDATE w_indicateurs SET
|
|
sqlcmd = replace(sqlcmd , ''#''||viewName||''#'', defaultViewName),
|
|
VIEWNAMEOPTIMIZER_index_array = array_append(VIEWNAMEOPTIMIZER_index_array,VIEWNAMEOPTIMIZER_index)
|
|
FROM w_dboptimizer_VIEWNAMEOPTIMIZER
|
|
WHERE w_indicateurs.sqlcmd LIKE ''%#''||viewName||''#%''
|
|
',50)
|
|
;
|
|
|
|
UPDATE w_indicateurs
|
|
SET sqlcmd = replace(sqlcmd,'#LEFTJOIN#',leftjoin)
|
|
FROM
|
|
(
|
|
SELECT expert_controle_rule_id, array_to_string(base.cti_array_accum(leftjoin),chr(10)) AS leftjoin
|
|
FROM
|
|
(
|
|
SELECT expert_controle_rule_id, w_dboptimizer_LEFTJOININDICATOR.VIEWNAMEOPTIMIZER_index, w_dboptimizer_LEFTJOININDICATOR.LEFTJOININDICATOR_index,
|
|
MAX(leftjoin) AS leftjoin
|
|
FROM w_indicateurs
|
|
JOIN w_dboptimizer_LEFTJOININDICATOR ON w_dboptimizer_LEFTJOININDICATOR.VIEWNAMEOPTIMIZER_index = ANY(VIEWNAMEOPTIMIZER_index_array)
|
|
JOIN w_dboptimizer_FIELD ON
|
|
w_dboptimizer_FIELD.VIEWNAMEOPTIMIZER_index = w_dboptimizer_LEFTJOININDICATOR.VIEWNAMEOPTIMIZER_index AND
|
|
w_dboptimizer_FIELD.LEFTJOININDICATOR_index = w_dboptimizer_LEFTJOININDICATOR.LEFTJOININDICATOR_index AND
|
|
w_indicateurs.sqlcmd LIKE '% ' || field_name|| '%'
|
|
GROUP BY 1,2,3
|
|
ORDER BY 1,2,3
|
|
) subview
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE w_indicateurs.expert_controle_rule_id = subview.expert_controle_rule_id
|
|
;
|
|
|
|
UPDATE w_indicateurs
|
|
SET sqlcmd = replace(sqlcmd,'#LEFTJOIN#','')
|
|
WHERE sqlcmd LIKE '%#LEFTJOIN#%'
|
|
;
|
|
|
|
RAISE NOTICE '%' , (SELECT array_to_string(base.cti_array_accum(sqlcmd),'; ') FROM w_indicateurs);
|
|
|
|
RAISE NOTICE '%' , 'Extraction donnees';
|
|
|
|
|
|
UPDATE w_indicateurs
|
|
SET sqlresult = base.cti_execute(sqlcmd,1)
|
|
;
|
|
|
|
ANALYSE w_expert_controle_data
|
|
;
|
|
|
|
RAISE NOTICE '%' , (SELECT array_to_string(base.cti_array_accum('INSERT INTO w_expert_controle_data
|
|
SELECT ' || t_expert_controle_rule.expert_controle_id || ', ' ||
|
|
t_expert_controle_rule.oid || ',
|
|
0::numeric,
|
|
data_type,
|
|
data_id,
|
|
contrat_mois_data,
|
|
expert_controle_code,
|
|
CASE WHEN MAX(CASE WHEN expert_controle_rule_index = 1 THEN COALESCE(indicateur_value,0) ELSE 0 END) ' || replace(CASE WHEN formula <> '' THEN formula ELSE '<>0' END,'''','''''') || ' THEN 1 ELSE 0 END +
|
|
CASE WHEN MAX(CASE WHEN expert_controle_rule_index = 2 THEN COALESCE(indicateur_value,0) ELSE 0 END) ' || replace(CASE WHEN formula_2 <> '' THEN formula_2 ELSE '<>0' END,'''','''''') || ' THEN 1 ELSE 0 END +
|
|
CASE WHEN MAX(CASE WHEN expert_controle_rule_index = 3 THEN COALESCE(indicateur_value,0) ELSE 0 END) ' || replace(CASE WHEN formula_3 <> '' THEN formula_3 ELSE '<>0' END,'''','''''') || ' THEN 1 ELSE 0 END
|
|
,
|
|
date_signalement,
|
|
etat,
|
|
date_correction,
|
|
date_evenement,
|
|
data_code_original,
|
|
texte
|
|
FROM w_expert_controle_data
|
|
GROUP BY 1,2,3,4,5,6,7,9,10,11,12,13,14
|
|
HAVING
|
|
CASE WHEN MAX(CASE WHEN expert_controle_rule_index = 1 THEN COALESCE(indicateur_value,0) ELSE 0 END) ' || replace(CASE WHEN formula <> '' THEN formula ELSE '<>0' END,'''','''''') || ' THEN 1 ELSE 0 END +
|
|
CASE WHEN MAX(CASE WHEN expert_controle_rule_index = 2 THEN COALESCE(indicateur_value,0) ELSE 0 END) ' || replace(CASE WHEN formula_2 <> '' THEN formula_2 ELSE '<>0' END,'''','''''') || ' THEN 1 ELSE 0 END +
|
|
CASE WHEN MAX(CASE WHEN expert_controle_rule_index = 3 THEN COALESCE(indicateur_value,0) ELSE 0 END) ' || replace(CASE WHEN formula_3 <> '' THEN formula_3 ELSE '<>0' END,'''','''''') || ' THEN 1 ELSE 0 END
|
|
= ' || (1 + CASE WHEN indicateur_2_id <> 0 THEN 1 ELSE 0 END + CASE WHEN indicateur_3_id <> 0 THEN 1 ELSE 0 END)::text ||'
|
|
'),'; ')
|
|
FROM rh.t_expert_controle_rule
|
|
WHERE oid IN (SELECT expert_controle_rule_id FROM w_expert_controle_data) AND
|
|
(
|
|
t_expert_controle_rule.indicateur_2_id <> 0 OR
|
|
t_expert_controle_rule.indicateur_3_id <> 0
|
|
)) ;
|
|
|
|
|
|
|
|
|
|
|
|
-- Plusieurs indicateurs dans condition ?
|
|
PERFORM base.cti_execute(
|
|
'INSERT INTO w_expert_controle_data
|
|
SELECT ' || t_expert_controle_rule.expert_controle_id || ', ' ||
|
|
t_expert_controle_rule.oid || ',
|
|
0::numeric,
|
|
data_type,
|
|
data_id,
|
|
contrat_mois_data,
|
|
expert_controle_code,
|
|
CASE WHEN MAX(CASE WHEN expert_controle_rule_index = 1 THEN COALESCE(indicateur_value,0) ELSE 0 END) ' || replace(CASE WHEN formula <> '' THEN formula ELSE '<>0' END,'''','''''') || ' THEN 1 ELSE 0 END +
|
|
CASE WHEN MAX(CASE WHEN expert_controle_rule_index = 2 THEN COALESCE(indicateur_value,0) ELSE 0 END) ' || replace(CASE WHEN formula_2 <> '' THEN formula_2 ELSE '<>0' END,'''','''''') || ' THEN 1 ELSE 0 END +
|
|
CASE WHEN MAX(CASE WHEN expert_controle_rule_index = 3 THEN COALESCE(indicateur_value,0) ELSE 0 END) ' || replace(CASE WHEN formula_3 <> '' THEN formula_3 ELSE '<>0' END,'''','''''') || ' THEN 1 ELSE 0 END
|
|
,
|
|
date_signalement,
|
|
etat,
|
|
date_correction,
|
|
date_evenement,
|
|
data_code_original,
|
|
texte
|
|
FROM w_expert_controle_data
|
|
GROUP BY 1,2,3,4,5,6,7,9,10,11,12,13,14
|
|
HAVING
|
|
CASE WHEN MAX(CASE WHEN expert_controle_rule_index = 1 THEN COALESCE(indicateur_value,0) ELSE 0 END) ' || replace(CASE WHEN formula <> '' THEN formula ELSE '<>0' END,'''','''''') || ' THEN 1 ELSE 0 END +
|
|
CASE WHEN MAX(CASE WHEN expert_controle_rule_index = 2 THEN COALESCE(indicateur_value,0) ELSE 0 END) ' || replace(CASE WHEN formula_2 <> '' THEN formula_2 ELSE '<>0' END,'''','''''') || ' THEN 1 ELSE 0 END +
|
|
CASE WHEN MAX(CASE WHEN expert_controle_rule_index = 3 THEN COALESCE(indicateur_value,0) ELSE 0 END) ' || replace(CASE WHEN formula_3 <> '' THEN formula_3 ELSE '<>0' END,'''','''''') || ' THEN 1 ELSE 0 END
|
|
= ' || (1 + CASE WHEN indicateur_2_id <> 0 THEN 1 ELSE 0 END + CASE WHEN indicateur_3_id <> 0 THEN 1 ELSE 0 END)::text ||'
|
|
',1)
|
|
FROM rh.t_expert_controle_rule
|
|
WHERE oid IN (SELECT expert_controle_rule_id FROM w_expert_controle_data) AND
|
|
(
|
|
t_expert_controle_rule.indicateur_2_id <> 0 OR
|
|
t_expert_controle_rule.indicateur_3_id <> 0
|
|
)
|
|
;
|
|
|
|
DELETE FROM w_expert_controle_data
|
|
WHERE expert_controle_rule_index > 0
|
|
;
|
|
|
|
UPDATE rh.t_expert_controle_rule
|
|
SET last_execution_result = CASE WHEN sqlresult NOT LIKE 'OK%' THEN sqlresult ELSE 'OK' END
|
|
FROM w_indicateurs
|
|
WHERE w_indicateurs.expert_controle_rule_id = t_expert_controle_rule.oid
|
|
;
|
|
|
|
UPDATE rh.t_expert_controle
|
|
SET last_execution_ok = subview.execution_ok
|
|
FROM
|
|
(
|
|
SELECT expert_controle_id,
|
|
MIN(CASE WHEN last_execution_result LIKE 'KO%' THEN '0' ELSE '1' END) AS execution_ok
|
|
FROM rh.t_expert_controle_rule
|
|
WHERE indicateur_id <> 0
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE expert_controle_id = t_expert_controle.oid
|
|
;
|
|
|
|
UPDATE rh.t_expert_controle
|
|
SET last_execution_count = subview.nb
|
|
FROM
|
|
(
|
|
SELECT expert_controle_id,
|
|
count(*) AS nb
|
|
FROM w_expert_controle_data
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE expert_controle_id = t_expert_controle.oid
|
|
;
|
|
|
|
ANALYSE w_expert_controle_data
|
|
;
|
|
|
|
|
|
|
|
-- Identifiants des tables
|
|
if 1<>1 then
|
|
PERFORM base.cti_execute('@UPDATE w_expert_controle_data SET
|
|
data_code_original = t_source.code_original, texte = ''' || t_listes_tables.texte || ' '' || t_source.texte || '' ('' || t_source.code || '')''
|
|
FROM rh.' || t_listes_tables.name || ' t_source
|
|
WHERE data_type = ''' || t_listes_tables.code || ''' AND t_source.oid = data_id
|
|
',1)
|
|
FROM rh.t_listes_tables
|
|
WHERE t_listes_tables.code <> 'salarie' AND
|
|
t_listes_tables.code IN (SELECT data_type FROM w_expert_controle_data)
|
|
;
|
|
PERFORM base.cti_execute('@UPDATE w_expert_controle_data SET
|
|
data_code_original = t_source.code_original, texte = ''Séjour '' || t_source.no_salarie
|
|
FROM rh.' || t_listes_tables.name || ' t_source
|
|
WHERE data_type = ''' || t_listes_tables.code || ''' AND t_source.oid = data_id
|
|
',1)
|
|
FROM rh.t_listes_tables
|
|
WHERE t_listes_tables.code = 'salarie' AND
|
|
t_listes_tables.code IN (SELECT data_type FROM w_expert_controle_data)
|
|
;
|
|
end if;
|
|
|
|
|
|
-- Identifiants des items
|
|
UPDATE w_expert_controle_data SET
|
|
data_code_original = p_contrats.code_original,
|
|
texte = 'Contrat ' ||
|
|
numero_contrat ||
|
|
' du ' || to_char(p_contrats.date_debut,'DD/MM/YYYY') ||
|
|
' au ' || to_char(p_contrats.date_fin,'DD/MM/YYYY')
|
|
FROM rh.p_contrats_mois
|
|
JOIN rh.p_contrats ON p_contrats_mois.contrat_id = p_contrats.oid
|
|
WHERE data_type IN ('contrat', 'salarie') AND
|
|
p_contrats_mois.date_debut = w_expert_controle_data.contrat_mois_data[1] AND
|
|
p_contrats_mois.date_fin = w_expert_controle_data.contrat_mois_data[2] AND
|
|
p_contrats.numero_contrat = w_expert_controle_data.contrat_mois_data[3]
|
|
;
|
|
UPDATE w_expert_controle_data SET
|
|
data_code_original = p_contrats.code_original || to_char(p_contrats_mois.date_fin,'YYYYMMDD'),
|
|
texte = 'Contrat ' ||
|
|
p_contrats.numero_contrat ||
|
|
'Paie du ' || to_char(p_contrats_mois.date_debut,'DD/MM/YYYY') ||
|
|
' au ' || to_char(p_contrats_mois.date_fin,'DD/MM/YYYY')
|
|
FROM rh.p_contrats_mois
|
|
JOIN rh.p_contrats ON p_contrats_mois.contrat_id = p_contrats.oid
|
|
WHERE data_type = 'paie' AND
|
|
p_contrats_mois.date_debut = w_expert_controle_data.contrat_mois_data[1] AND
|
|
p_contrats_mois.date_fin = w_expert_controle_data.contrat_mois_data[2] AND
|
|
p_contrats.numero_contrat = w_expert_controle_data.contrat_mois_data[3]
|
|
;
|
|
|
|
|
|
-- Actualisation des oid des tables
|
|
if 1<>1 then
|
|
PERFORM base.cti_execute('@UPDATE rh.p_expert_controle_data SET
|
|
data_id = t_source.oid
|
|
FROM rh.' || t_listes_tables.name || ' t_source
|
|
WHERE data_type = ''' || t_listes_tables.code || ''' AND
|
|
data_code_original = t_source.code_original AND
|
|
data_id <> t_source.oid
|
|
',1)
|
|
FROM rh.t_listes_tables
|
|
WHERE t_listes_tables.code IN (SELECT data_type FROM rh.p_expert_controle_data)
|
|
;
|
|
end if;
|
|
|
|
|
|
-- Corrigé si erreur a disparu
|
|
UPDATE rh.p_expert_controle_data SET
|
|
etat = 'T',
|
|
date_correction = date(now())
|
|
WHERE etat = '' AND
|
|
expert_controle_id NOT IN (SELECT t_expert_controle.oid FROM rh.t_expert_controle WHERE t_expert_controle.oid > 0 AND last_execution_ok <> '1') AND
|
|
(
|
|
i_expert_controle_id = 0 OR
|
|
p_expert_controle_data.expert_controle_id = i_expert_controle_id
|
|
) AND
|
|
p_expert_controle_data.expert_controle_id::text||'#'||p_expert_controle_data.data_type||'#'||p_expert_controle_data.data_id::text
|
|
NOT IN (SELECT w_expert_controle_data.expert_controle_id::text||'#'||w_expert_controle_data.data_type||'#'||w_expert_controle_data.data_id FROM w_expert_controle_data)
|
|
;
|
|
|
|
-- L'erreur réapparait aprés avoir disparu, réactivation
|
|
UPDATE rh.p_expert_controle_data SET
|
|
etat = '',
|
|
date_correction = '20991231'
|
|
WHERE etat = 'T' AND
|
|
(
|
|
i_expert_controle_id = 0 OR
|
|
p_expert_controle_data.expert_controle_id = i_expert_controle_id
|
|
) AND
|
|
p_expert_controle_data.expert_controle_id::text||'#'||p_expert_controle_data.data_type||'#'||p_expert_controle_data.data_id::text
|
|
IN (SELECT w_expert_controle_data.expert_controle_id::text||'#'||w_expert_controle_data.data_type||'#'||w_expert_controle_data.data_id FROM w_expert_controle_data)
|
|
;
|
|
|
|
INSERT INTO rh.p_expert_controle_data(
|
|
expert_controle_id,
|
|
date_item,
|
|
date_signalement,
|
|
etat,
|
|
date_evenement,
|
|
date_correction,
|
|
contrat_mois_data,
|
|
data_type,
|
|
data_id,
|
|
data_code_original,
|
|
texte,
|
|
nb_cree,
|
|
nb_portee,
|
|
nb_cloture
|
|
)
|
|
SELECT
|
|
expert_controle_id,
|
|
date_signalement AS date_item,
|
|
date_signalement,
|
|
etat,
|
|
date_evenement,
|
|
date_correction,
|
|
contrat_mois_data,
|
|
data_type,
|
|
data_id,
|
|
data_code_original,
|
|
texte,
|
|
1::numeric AS nb_cree,
|
|
0::numeric AS nb_portee,
|
|
0::numeric AS nb_cloture
|
|
FROM w_expert_controle_data
|
|
WHERE (w_expert_controle_data.expert_controle_id::text||'#'||w_expert_controle_data.data_type||'#'||w_expert_controle_data.data_id)
|
|
NOT IN (SELECT p_expert_controle_data.expert_controle_id::text||'#'||p_expert_controle_data.data_type||'#'||p_expert_controle_data.data_id FROM rh.p_expert_controle_data)
|
|
;
|
|
|
|
ANALYSE rh.p_expert_controle_data
|
|
;
|
|
|
|
DELETE FROM rh.p_expert_controle_data
|
|
WHERE expert_controle_id IN (SELECT oid FROM rh.t_expert_controle WHERE is_active <> '1')
|
|
;
|
|
|
|
DELETE FROM rh.p_expert_controle_data
|
|
WHERE nb_cree = 0
|
|
;
|
|
|
|
|
|
UPDATE rh.p_expert_controle_data
|
|
SET expert_controle_data_id = oid
|
|
WHERE expert_controle_data_id <> oid
|
|
;
|
|
|
|
|
|
INSERT INTO rh.p_expert_controle_data
|
|
(
|
|
oid,
|
|
expert_controle_data_id,
|
|
expert_controle_id,
|
|
date_item,
|
|
date_signalement,
|
|
etat,
|
|
date_evenement,
|
|
date_correction,
|
|
contrat_mois_data,
|
|
data_type,
|
|
data_id,
|
|
data_code_original,
|
|
texte,
|
|
nb_cree,
|
|
nb_portee,
|
|
nb_cloture
|
|
)
|
|
SELECT
|
|
0::bigint AS oid,
|
|
expert_controle_data_id,
|
|
expert_controle_id,
|
|
date_evenement AS date_item,
|
|
date_signalement,
|
|
etat,
|
|
date_evenement,
|
|
date_correction,
|
|
contrat_mois_data,
|
|
data_type,
|
|
data_id,
|
|
data_code_original,
|
|
texte,
|
|
0::bigint AS nb_cree,
|
|
1::bigint AS nb_portee,
|
|
0::bigint AS nb_cloture
|
|
FROM rh.p_expert_controle_data
|
|
WHERE nb_cree = 1 AND
|
|
date_evenement <> '20991231'
|
|
;
|
|
|
|
INSERT INTO rh.p_expert_controle_data
|
|
(
|
|
oid,
|
|
expert_controle_data_id,
|
|
expert_controle_id,
|
|
date_item,
|
|
date_signalement,
|
|
etat,
|
|
date_evenement,
|
|
date_correction,
|
|
contrat_mois_data,
|
|
data_type,
|
|
data_id,
|
|
data_code_original,
|
|
texte,
|
|
nb_cree,
|
|
nb_portee,
|
|
nb_cloture
|
|
)
|
|
SELECT
|
|
0::bigint AS oid,
|
|
expert_controle_data_id,
|
|
expert_controle_id,
|
|
date_correction AS date_item,
|
|
date_signalement,
|
|
etat,
|
|
date_evenement,
|
|
date_correction,
|
|
contrat_mois_data,
|
|
data_type,
|
|
data_id,
|
|
data_code_original,
|
|
texte,
|
|
0::bigint AS nb_cree,
|
|
0::bigint AS nb_portee,
|
|
1::bigint AS nb_cloture
|
|
FROM rh.p_expert_controle_data
|
|
WHERE nb_cree = 1 AND
|
|
date_correction <> '20991231'
|
|
;
|
|
|
|
ANALYSE rh.p_expert_controle_data
|
|
;
|
|
|
|
UPDATE rh.t_expert_controle
|
|
SET last_execution_count = subview.nb
|
|
FROM
|
|
(
|
|
SELECT t_expert_controle.oid,
|
|
count(p_expert_controle_data.expert_controle_id) AS nb
|
|
FROM rh.t_expert_controle
|
|
LEFT JOIN rh.p_expert_controle_data ON
|
|
expert_controle_id = t_expert_controle.oid AND
|
|
nb_cree = 1 AND
|
|
date_correction = '20991231'
|
|
GROUP BY 1
|
|
) subview
|
|
WHERE t_expert_controle.oid = subview.oid AND
|
|
last_execution_count <> subview.nb
|
|
;
|
|
|
|
RETURN 'OK';
|
|
END;
|