return: text
lang: plpgsql
src: |
  DECLARE	--select * from eco.p_stock order by date_fin desc
  	result TEXT;
  BEGIN
  	IF ((SELECT valeur FROM eco.t_divers WHERE code = 'CLE_REPARTITION') != 
  	(
  	SELECT 
  		t_listes_tables.code 
  	FROM 
  		eco.t_classes 
  	JOIN eco.t_listes_tables ON table_id = t_listes_tables.oid
  	WHERE t_classes.code = 'CTI_CLE_REP'))
  	THEN
  		RAISE NOTICE '%','Valeur changé - Mise à jour';
  		-- enregistrement des sections CTI
  		DROP TABLE IF EXISTS w_sections;
  		CREATE TEMP TABLE w_sections AS
  		SELECT t_classes_sections.code, t_classes_sections.texte 
  		FROM eco.t_classes 
  		JOIN eco.t_classes_sections ON classe_id = t_classes.oid
  		WHERE t_classes.code = 'CTI_CLE_REP'
  		;
  
  		-- enregistrement des conditions de rapport CTI liées à la clef de répartition
  		DROP TABLE IF EXISTS w_conditions;
  		CREATE TEMP TABLE w_conditions AS
  		SELECT t_rapports.oid, ARRAY[
  				section_0.code,
  				section_1.code,
  				section_2.code,
  				section_3.code,
  				section_4.code] AS code_section FROM eco.t_rapports
  			LEFT JOIN eco.t_indicateur_condition condition_0 ON t_rapports.rapport_indicateur_condition_id[1] = condition_0.oid
  			LEFT JOIN eco.t_classes_sections section_0 ON section_0.oid = right(split_part(condition_0.code,'#',2),-4) AND left(split_part(condition_0.code,'#',2),4) = 'LS30'
  			LEFT JOIN eco.t_indicateur_condition condition_1 ON t_rapports.rapport_indicateur_condition_id[2] = condition_1.oid
  			LEFT JOIN eco.t_classes_sections section_1 ON section_1.oid = right(split_part(condition_1.code,'#',2),-4) AND left(split_part(condition_1.code,'#',2),4) = 'LS30'
  			LEFT JOIN eco.t_indicateur_condition condition_2 ON t_rapports.rapport_indicateur_condition_id[3] = condition_2.oid
  			LEFT JOIN eco.t_classes_sections section_2 ON section_2.oid = right(split_part(condition_2.code,'#',2),-4) AND left(split_part(condition_2.code,'#',2),4) = 'LS30'
  			LEFT JOIN eco.t_indicateur_condition condition_3 ON t_rapports.rapport_indicateur_condition_id[4] = condition_3.oid
  			LEFT JOIN eco.t_classes_sections section_3 ON section_3.oid = right(split_part(condition_3.code,'#',2),-4) AND left(split_part(condition_3.code,'#',2),4) = 'LS30'
  			LEFT JOIN eco.t_indicateur_condition condition_4 ON t_rapports.rapport_indicateur_condition_id[5] = condition_4.oid
  			LEFT JOIN eco.t_classes_sections section_4 ON section_4.oid = right(split_part(condition_4.code,'#',2),-4) AND left(split_part(condition_4.code,'#',2),4) = 'LS30'
  		WHERE t_rapports.code LIKE 'CTI_ESS%'			
  			;
  		
  		-- effacement de la classe CTI_CLE_REP
  		DELETE FROM eco.t_classes_sections_elements
  		WHERE section_id IN (
  			SELECT t_classes_sections.oid 
  			FROM eco.t_classes 
  			JOIN eco.t_classes_sections ON classe_id = t_classes.oid
  			WHERE t_classes.code = 'CTI_CLE_REP'
  			)
  		;
  		DELETE FROM eco.t_classes_sections
  		WHERE oid IN (
  			SELECT t_classes_sections.oid 
  			FROM eco.t_classes 
  			JOIN eco.t_classes_sections ON classe_id = t_classes.oid
  			WHERE t_classes.code = 'CTI_CLE_REP'
  			)
  		; 
  		DELETE FROM eco.t_classes
  		WHERE code = 'CTI_CLE_REP'
  		;
  		
  		-- création de la classe CTI_CLE_REP
  		INSERT INTO eco.t_classes(code, texte, table_id, is_cti, sequence)     
  		SELECT 
  			'CTI_CLE_REP', 
  			'Clef de répartition CTI', 
  			oid, 
  			0, 
  			30
  		FROM 
  			eco.t_listes_tables       
  		WHERE 
  			code = (SELECT valeur FROM eco.t_divers WHERE code = 'CLE_REPARTITION')
  		;
  
  		INSERT INTO eco.t_classes_sections (code, texte, classe_id)
  		SELECT w_sections.code, w_sections.texte, oid
  		FROM w_sections LEFT JOIN eco.t_classes ON t_classes.code = 'CTI_CLE_REP'
  		;
  
  		PERFORM eco.cti_reorganize_indicateur_condition();
  		
  		-- ajustement des condition de rapports essentiels CTI
  		UPDATE eco.t_rapports
  		SET
  			rapport_indicateur_condition_id = ARRAY[condition_0.oid,condition_1.oid,condition_2.oid,condition_3.oid,condition_4.oid]
  		FROM
  			w_conditions 
  			JOIN eco.t_listes_tables ON t_listes_tables.code = (SELECT valeur FROM eco.t_divers WHERE code = 'CLE_REPARTITION')
  			JOIN eco.t_classes ON table_id = t_listes_tables.oid AND sequence = 30
  			LEFT JOIN eco.t_classes_sections section_0 ON t_classes.oid = section_0.classe_id AND section_0.code = code_section[1]
  			LEFT JOIN eco.t_indicateur_condition condition_0 ON regexp_replace(regexp_replace(regexp_replace(regexp_replace(t_listes_tables.name,'^t_',''),'x',''),'s_','','g'),'s$','') || 'id' = regexp_replace(condition_0.column_name,'_','','g')
  				AND t_classes.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_classes_sections section_1 ON t_classes.oid = section_1.classe_id AND section_1.code = code_section[2]
  			LEFT JOIN eco.t_indicateur_condition condition_1 ON regexp_replace(regexp_replace(regexp_replace(regexp_replace(t_listes_tables.name,'^t_',''),'x',''),'s_','','g'),'s$','') || 'id' = regexp_replace(condition_1.column_name,'_','','g')
  				AND t_classes.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_classes_sections section_2 ON t_classes.oid = section_2.classe_id AND section_2.code = code_section[3]
  			LEFT JOIN eco.t_indicateur_condition condition_2 ON regexp_replace(regexp_replace(regexp_replace(regexp_replace(t_listes_tables.name,'^t_',''),'x',''),'s_','','g'),'s$','') || 'id' = regexp_replace(condition_2.column_name,'_','','g')
  				AND t_classes.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_classes_sections section_3 ON t_classes.oid = section_3.classe_id AND section_3.code = code_section[4]
  			LEFT JOIN eco.t_indicateur_condition condition_3 ON regexp_replace(regexp_replace(regexp_replace(regexp_replace(t_listes_tables.name,'^t_',''),'x',''),'s_','','g'),'s$','') || 'id' = regexp_replace(condition_3.column_name,'_','','g')
  				AND t_classes.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_classes_sections section_4 ON t_classes.oid = section_4.classe_id AND section_4.code = code_section[5]
  			LEFT JOIN eco.t_indicateur_condition condition_4 ON regexp_replace(regexp_replace(regexp_replace(regexp_replace(t_listes_tables.name,'^t_',''),'x',''),'s_','','g'),'s$','') || 'id' = regexp_replace(condition_4.column_name,'_','','g')
  				AND t_classes.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.oid = w_conditions.oid
  				;
  			
  	END IF;
  		RETURN 'OK';
  END;