DROP PROCEDURE IF EXISTS public.sp_v041treatcomplcoefload;
DROP FUNCTION IF EXISTS public.sp_v041treatcomplcoefload;

CREATE PROCEDURE public.sp_v041treatcomplcoefload("Xml" bytea, "FileName" character varying)
LANGUAGE 'plpgsql'
AS $BODY$
	
DECLARE 
    T xml;
    tempStrXml text;
BEGIN
	
	select convert_from("Xml", 'WIN1251') into tempStrXml;	
	select replace(tempStrXml, 'encoding="windows-1251"','')::xml into T; 
	
	DROP TABLE IF EXISTS temp_treatcomplcoef_load;

	CREATE TEMP TABLE temp_treatcomplcoef_load AS
	SELECT 
		ID_SL,
		NULLIF(N_SL, '') AS N_SL,
		NULLIF(PG_SL, '') AS PG_SL,
		NULLIF(K_SL, '') :: decimal(3, 2) AS K_SL,
		DATEBEG :: timestamp,
		NULLIF(DATEEND, '') :: timestamp as DATEEND
	FROM XMLTABLE 
	(
		'/packet/zap' PASSING T 
		COLUMNS 
			ID_SL text PATH './ID_SL',
			N_SL text PATH './N_SL',
			PG_SL text PATH './PG_SL',
			K_SL text PATH './K_SL',
			DATEBEG text PATH './DATEBEG',
			DATEEND text PATH './DATEEND'
	);
	
	update ies.t_v041_treat_compl_coef t2
	set N_SL = t1.N_SL
		,DATEEND = t1.DATEEND
	from temp_treatcomplcoef_load t1
	where t1.ID_SL = t2.ID_SL and t1.DATEBEG = t2.DATEBEG;
	
	insert into ies.t_v041_treat_compl_coef (v041treatcomplcoefid, ID_SL, N_SL, PG_SL, K_SL, DATEBEG, DATEEND)
	select gen_random_uuid(), t1.ID_SL, t1.N_SL, t1.PG_SL, t1.K_SL, t1.DATEBEG, t1.DATEEND
	from temp_treatcomplcoef_load t1
	left join ies.t_v041_treat_compl_coef t2 on (t1.ID_SL = t2.ID_SL and t1.DATEBEG = t2.DATEBEG)
	where t2.v041treatcomplcoefid is null;
	
	DROP TABLE IF EXISTS temp_treatcomplcoef_load;
	
	RETURN;
END
$BODY$;