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.
263 lines
10 KiB
263 lines
10 KiB
return: text
|
|
lang: plpgsql
|
|
parameters:
|
|
p0:
|
|
type: bigint
|
|
name: i_indicateur_id
|
|
p1:
|
|
type: integer
|
|
name: i_level
|
|
strict: STRICT
|
|
src: |
|
|
DECLARE
|
|
formule_indicateur TEXT;
|
|
rapports_cursor refcursor;
|
|
column_name TEXT;
|
|
table_name TEXT;
|
|
total_function TEXT;
|
|
liste_code_start integer;
|
|
to_column_name TEXT;
|
|
o_level INTEGER;
|
|
column_name_a1 TEXT;
|
|
column_name_a2 TEXT;
|
|
column_name_a3 TEXT;
|
|
column_name_a4 TEXT;
|
|
column_name_a5 TEXT;
|
|
column_name_a6 TEXT;
|
|
column_name_a7 TEXT;
|
|
column_name_a8 TEXT;
|
|
column_name_a9 TEXT;
|
|
column_name_a10 TEXT;
|
|
column_name_a11 TEXT;
|
|
column_name_a12 TEXT;
|
|
column_name_a13 TEXT;
|
|
column_name_a14 TEXT;
|
|
column_name_a15 TEXT;
|
|
column_name_a16 TEXT;
|
|
column_name_a17 TEXT;
|
|
column_name_a18 TEXT;
|
|
column_name_a19 TEXT;
|
|
column_name_a20 TEXT;
|
|
table_name_a1 TEXT;
|
|
table_name_a2 TEXT;
|
|
table_name_a3 TEXT;
|
|
table_name_a4 TEXT;
|
|
table_name_a5 TEXT;
|
|
table_name_a6 TEXT;
|
|
table_name_a7 TEXT;
|
|
table_name_a8 TEXT;
|
|
table_name_a9 TEXT;
|
|
table_name_a10 TEXT;
|
|
table_name_a11 TEXT;
|
|
table_name_a12 TEXT;
|
|
table_name_a13 TEXT;
|
|
table_name_a14 TEXT;
|
|
table_name_a15 TEXT;
|
|
table_name_a16 TEXT;
|
|
table_name_a17 TEXT;
|
|
table_name_a18 TEXT;
|
|
table_name_a19 TEXT;
|
|
table_name_a20 TEXT;
|
|
total_function_a1 TEXT;
|
|
total_function_a2 TEXT;
|
|
total_function_a3 TEXT;
|
|
total_function_a4 TEXT;
|
|
total_function_a5 TEXT;
|
|
total_function_a6 TEXT;
|
|
total_function_a7 TEXT;
|
|
total_function_a8 TEXT;
|
|
total_function_a9 TEXT;
|
|
total_function_a10 TEXT;
|
|
total_function_a11 TEXT;
|
|
total_function_a12 TEXT;
|
|
total_function_a13 TEXT;
|
|
total_function_a14 TEXT;
|
|
total_function_a15 TEXT;
|
|
total_function_a16 TEXT;
|
|
total_function_a17 TEXT;
|
|
total_function_a18 TEXT;
|
|
total_function_a19 TEXT;
|
|
total_function_a20 TEXT;
|
|
|
|
champ_associe_1 TEXT;
|
|
champ_associe_2 TEXT;
|
|
champ_associe_3 TEXT;
|
|
champ_associe_4 TEXT;
|
|
champ_associe_5 TEXT;
|
|
champ_associe_6 TEXT;
|
|
champ_associe_7 TEXT;
|
|
champ_associe_8 TEXT;
|
|
champ_associe_9 TEXT;
|
|
champ_associe_10 TEXT;
|
|
champ_associe_11 TEXT;
|
|
champ_associe_12 TEXT;
|
|
champ_associe_13 TEXT;
|
|
champ_associe_14 TEXT;
|
|
champ_associe_15 TEXT;
|
|
champ_associe_16 TEXT;
|
|
champ_associe_17 TEXT;
|
|
champ_associe_18 TEXT;
|
|
champ_associe_19 TEXT;
|
|
champ_associe_20 TEXT;
|
|
champ_calcule TEXT;
|
|
i int2;
|
|
ch TEXT;
|
|
BEGIN
|
|
|
|
formule_indicateur = '0';
|
|
IF (i_indicateur_id = 0) THEN
|
|
return formule_indicateur;
|
|
END IF;
|
|
IF (i_indicateur_id IS NULL) THEN
|
|
return formule_indicateur;
|
|
END IF;
|
|
IF (i_level > 10) THEN
|
|
return formule_indicateur;
|
|
END IF;
|
|
|
|
o_level = i_level + 1;
|
|
OPEN rapports_cursor FOR
|
|
SELECT
|
|
t_indicateurs.column_name, t_indicateurs.table_name, t_indicateurs.total_function,
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_1_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_2_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_3_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_4_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_5_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_6_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_7_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_8_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_9_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_10_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_11_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_12_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_13_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_14_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_15_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_16_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_17_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_18_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_19_id,0), o_level),
|
|
compta.get_formule_indicateur(COALESCE(t_indicateurs.indicateur_associe_20_id,0), o_level)
|
|
FROM compta.t_indicateurs
|
|
|
|
WHERE t_indicateurs.oid = i_indicateur_id ;
|
|
FETCH rapports_cursor INTO column_name , table_name, total_function,
|
|
column_name_a1,
|
|
column_name_a2,
|
|
column_name_a3,
|
|
column_name_a4,
|
|
column_name_a5,
|
|
column_name_a6,
|
|
column_name_a7,
|
|
column_name_a8,
|
|
column_name_a9,
|
|
column_name_a10,
|
|
column_name_a11,
|
|
column_name_a12,
|
|
column_name_a13,
|
|
column_name_a14,
|
|
column_name_a15,
|
|
column_name_a16,
|
|
column_name_a17,
|
|
column_name_a18,
|
|
column_name_a19,
|
|
column_name_a20 ;
|
|
IF (table_name != 'SEP') THEN
|
|
IF (table_name != '*CALC' AND table_name != '*CALC_SUM') THEN
|
|
to_column_name = '';
|
|
liste_code_start = 0;
|
|
FOR i IN 1..length(column_name) LOOP
|
|
IF (liste_code_start = 0) THEN
|
|
IF(substring(column_name,i,7)='[LISTE:') THEN
|
|
liste_code_start = i + 7;
|
|
ELSE
|
|
to_column_name = to_column_name || substring(column_name,i,1);
|
|
END IF;
|
|
ELSE
|
|
IF(substring(column_name,i,1)=']') THEN
|
|
to_column_name = to_column_name || '(SELECT to_id FROM compta.t_listes, compta.t_listes_contenu WHERE t_listes.oid = t_listes_contenu.liste_id AND t_listes.code = ''' || substring(column_name,liste_code_start,i - liste_code_start) || ''')';
|
|
liste_code_start = 0;
|
|
END IF;
|
|
END IF;
|
|
END LOOP;
|
|
liste_code_start = 0;
|
|
column_name = to_column_name;
|
|
to_column_name = '';
|
|
FOR i IN 1..length(column_name) LOOP
|
|
IF (liste_code_start = 0) THEN
|
|
IF(substring(column_name,i,8)='[CLASSE:') THEN
|
|
liste_code_start = i + 8;
|
|
ELSE
|
|
to_column_name = to_column_name || substring(column_name,i,1);
|
|
END IF;
|
|
ELSE
|
|
IF(substring(column_name,i,1)=']') THEN
|
|
to_column_name = to_column_name || '(SELECT to_id FROM compta.t_classes JOIN compta.t_classes_sections ON classe_id = t_classes.oid JOIN compta.t_classes_sections_elements ON section_id = t_classes_sections.oid WHERE t_classes.code || '':'' || t_classes_sections.code = ''' || substring(column_name,liste_code_start,i - liste_code_start) || ''')';
|
|
liste_code_start = 0;
|
|
END IF;
|
|
END IF;
|
|
END LOOP;
|
|
IF (to_column_name = '') THEN
|
|
to_column_name = '0';
|
|
END IF;
|
|
formule_indicateur = total_function || '(' || to_column_name || ') ' ;
|
|
ELSE
|
|
|
|
IF (table_name != '*CALC_SUM') THEN
|
|
|
|
champ_calcule = column_name;
|
|
champ_calcule = replace(champ_calcule,'[1]','(' || column_name_a1 || ')');
|
|
champ_calcule = replace(champ_calcule,'[2]','(' || column_name_a2 || ')');
|
|
champ_calcule = replace(champ_calcule,'[3]','(' || column_name_a3 || ')');
|
|
champ_calcule = replace(champ_calcule,'[4]','(' || column_name_a4 || ')');
|
|
champ_calcule = replace(champ_calcule,'[5]','(' || column_name_a5 || ')');
|
|
champ_calcule = replace(champ_calcule,'[6]','(' || column_name_a6 || ')');
|
|
champ_calcule = replace(champ_calcule,'[7]','(' || column_name_a7 || ')');
|
|
champ_calcule = replace(champ_calcule,'[8]','(' || column_name_a8 || ')');
|
|
champ_calcule = replace(champ_calcule,'[9]','(' || column_name_a9 || ')');
|
|
champ_calcule = replace(champ_calcule,'[10]','(' || column_name_a10 || ')');
|
|
champ_calcule = replace(champ_calcule,'[11]','(' || column_name_a11 || ')');
|
|
champ_calcule = replace(champ_calcule,'[12]','(' || column_name_a12 || ')');
|
|
champ_calcule = replace(champ_calcule,'[13]','(' || column_name_a13 || ')');
|
|
champ_calcule = replace(champ_calcule,'[14]','(' || column_name_a14 || ')');
|
|
champ_calcule = replace(champ_calcule,'[15]','(' || column_name_a15 || ')');
|
|
champ_calcule = replace(champ_calcule,'[16]','(' || column_name_a16 || ')');
|
|
champ_calcule = replace(champ_calcule,'[17]','(' || column_name_a17 || ')');
|
|
champ_calcule = replace(champ_calcule,'[18]','(' || column_name_a18 || ')');
|
|
champ_calcule = replace(champ_calcule,'[19]','(' || column_name_a19 || ')');
|
|
champ_calcule = replace(champ_calcule,'[20]','(' || column_name_a20 || ')');
|
|
ELSE
|
|
champ_calcule = '(' || column_name_a1 || ') + ' ||
|
|
'(' || column_name_a2 || ') + ' ||
|
|
'(' || column_name_a3 || ') + ' ||
|
|
'(' || column_name_a4 || ') + ' ||
|
|
'(' || column_name_a5 || ') + ' ||
|
|
'(' || column_name_a6 || ') + ' ||
|
|
'(' || column_name_a7 || ') + ' ||
|
|
'(' || column_name_a8 || ') + ' ||
|
|
'(' || column_name_a9 || ') + ' ||
|
|
'(' || column_name_a10 || ') + ' ||
|
|
'(' || column_name_a11 || ') + ' ||
|
|
'(' || column_name_a12 || ') + ' ||
|
|
'(' || column_name_a13 || ') + ' ||
|
|
'(' || column_name_a14 || ') + ' ||
|
|
'(' || column_name_a15 || ') + ' ||
|
|
'(' || column_name_a16 || ') + ' ||
|
|
'(' || column_name_a17 || ') + ' ||
|
|
'(' || column_name_a18 || ') + ' ||
|
|
'(' || column_name_a19 || ') + ' ||
|
|
'(' || column_name_a20 || ')';
|
|
END IF;
|
|
|
|
formule_indicateur = champ_calcule;
|
|
END IF;
|
|
ELSE
|
|
formule_indicateur = '0';
|
|
END IF;
|
|
IF formule_indicateur = '' THEN
|
|
formule_indicateur = '0';
|
|
END IF;
|
|
|
|
RETURN formule_indicateur ;
|
|
END;
|