DROP PROCEDURE IF EXISTS sp_q019typedatload;
DROP FUNCTION IF EXISTS sp_q019typedatload;

CREATE PROCEDURE public.sp_q019typedatload("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_q019;

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

	insert into IES.T_Q019_TYPE_DAT(Q019TypeDatID,IDTYPED,NAMTYPED,DATEBEG,DATEEND)
	select gen_random_uuid(),t1.IDTYPED,t1.NAMTYPED,t1.DATEBEG,t1.DATEEND
	from temp_q019 t1
	left join IES.T_Q019_TYPE_DAT t2 on (t1.IDTYPED=t2.IDTYPED and t1.DATEBEG = t2.DATEBEG) and (t2.DATEEND = t1.DATEEND or (t2.DATEEND is null and t1.DATEEND is null) )
	where t2.Q019TypeDatID is null;

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

