DROP PROCEDURE IF EXISTS sp_q020typeelload;
DROP FUNCTION IF EXISTS sp_q020typeelload;

CREATE PROCEDURE public.sp_q020typeelload("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_q020;

	CREATE TEMP TABLE temp_q020 as
	SELECT 
		IDTYPE, 
		NAMTYPE,
		COMMENT, 
		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 
		IDTYPE text path './IDTYPE' null, 
		NAMTYPE text path './NAMTYPE' null,
		COMMENT text path './COMMENT' null,
		DATEBEG text path './DATEBEG' null, 
		DATEEND text path './DATEEND' null );
	
			update IES.T_Q020_TYPE_EL t2 
	set  NAMTYPE=t1.NAMTYPE
		,COMMENT=t1.COMMENT
		,DATEEND=t1.DATEEND
	from temp_q020 t1
	where (t1.IDTYPE=t2.IDTYPE and t1.DATEBEG = t2.DATEBEG) 
										and (t2.DATEEND = t1.DATEEND or t2.DATEEND is null );

	insert into IES.T_Q020_TYPE_EL(Q020TypeElID,IDTYPE,NAMTYPE,COMMENT,DATEBEG,DATEEND)
	select gen_random_uuid(),t1.IDTYPE,t1.NAMTYPE, t1.COMMENT,t1.DATEBEG,t1.DATEEND
	from temp_q020 t1
	left join IES.T_Q020_TYPE_EL t2 on (t1.IDTYPE=t2.IDTYPE and t1.DATEBEG = t2.DATEBEG) 
											and (t2.DATEEND = t1.DATEEND or (t2.DATEEND is null and t1.DATEEND is null) )
	where t2.Q020TypeElID is null;

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