DROP PROCEDURE IF EXISTS sp_n011markvalueload;
DROP FUNCTION IF EXISTS sp_n011markvalueload;

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

	CREATE TEMP TABLE temp_n011 as
	SELECT 
		ID_R_I::int, 
		KOD_R_I,
		R_I_NAME,
		ID_Igh::int, 
		CASE WHEN  DATEBEG  = '' THEN NULL ELSE DATEBEG::TIMESTAMP END , 
		CASE WHEN  DATEEND  = '' THEN NULL ELSE DATEEND::TIMESTAMP END 
	FROM
	XMLTABLE ('/packet/zap' PASSING T 
	COLUMNS 
		ID_R_I text path './ID_R_I' null, 
		KOD_R_I text path './KOD_R_I' null,
		R_I_NAME text path './R_I_NAME' null,
		ID_Igh text path './ID_Igh' null, 
		DATEBEG text path './DATEBEG' null, 
		DATEEND text path './DATEEND' null );
	
		update IES.T_N011_MARK_VALUE t2 
	set  KOD_R_I=t1.KOD_R_I
		,R_I_NAME=t1.R_I_NAME
		,DATEEND=t1.DATEEND
	from temp_n011 t1
	where (t1.ID_R_I=t2.ID_R_I and t1.DATEBEG = t2.DATEBEG);

	insert into IES.T_N011_MARK_VALUE(N011MarkValueID, ID_R_I, KOD_R_I, R_I_NAME, N010Mark, DATEBEG, DATEEND)
	select gen_random_uuid(),t1.ID_R_I,t1.KOD_R_I,t1.R_I_NAME,t3.N010MarkID,t1.DATEBEG,t1.DATEEND
	from temp_n011 t1
	left join IES.T_N011_MARK_VALUE t2 on (t1.ID_R_I=t2.ID_R_I and t1.DATEBEG = t2.DATEBEG)
	join IES.T_N010_MARK t3 on (t1.ID_Igh = t3.ID_Igh)
	where t2.N011MarkValueID is null;

	drop table if exists temp_n011;
	
	return;
END
$BODY$;
