DROP PROCEDURE IF EXISTS sp_n021onklpshload;
DROP FUNCTION IF EXISTS sp_n021onklpshload;

CREATE PROCEDURE public.sp_n021onklpshload("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_n021;

	CREATE TEMP TABLE temp_n021 as
	SELECT 
		ID_ZAP, 
		CODE_SH,
		ID_LEKP,
		LEKP_EXT,
		CASE WHEN ID_LEKP_EXT = '' THEN NULL ELSE ID_LEKP_EXT END,		
		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_ZAP text path './ID_ZAP' null, 
		CODE_SH text path './CODE_SH' null,
		ID_LEKP text path './ID_LEKP' null,
		LEKP_EXT text path './LEKP_EXT' null,
		ID_LEKP_EXT text path './ID_LEKP_EXT' null,
		DATEBEG text path './DATEBEG' null, 
		DATEEND text path './DATEEND' null );
	
	update IES.T_N021_ONK_LPSH t2 
	set  CODE_SH=t1.CODE_SH
		,ID_LEKP=aa.N020OnkLekpID
		,DATEEND=t1.DATEEND
		,LEKP_EXT=t1.LEKP_EXT
		,ID_LEKP_EXT=t1.ID_LEKP_EXT
	from temp_n021 t1
	join lateral(select * from ies.T_N020_ONK_LEKP n020 where n020.ID_LEKP = t1.ID_LEKP and t1.DATEBEG >= n020.DATEBEG and (t1.DATEEND <= n020.DATEEND or n020.DATEEND is null) limit 1) aa on true
	where (t1.ID_ZAP=t2.ID_ZAP and t1.DATEBEG = t2.DATEBEG);
	

	insert into IES.T_N021_ONK_LPSH(N021OnkLpshID, ID_ZAP, CODE_SH, ID_LEKP, LEKP_EXT, ID_LEKP_EXT, DATEBEG, DATEEND)
	select gen_random_uuid(),t1.ID_ZAP,t1.CODE_SH, aa.N020OnkLekpID, t1.LEKP_EXT, t1.ID_LEKP_EXT,  t1.DATEBEG,t1.DATEEND
	from temp_n021 t1
	left join IES.T_N021_ONK_LPSH t2 on (t1.ID_ZAP=t2.ID_ZAP and t1.DATEBEG = t2.DATEBEG)
	join lateral(select * from ies.T_N020_ONK_LEKP n020 where n020.ID_LEKP = t1.ID_LEKP and t1.DATEBEG >= n020.DATEBEG and (t1.DATEEND <= n020.DATEEND or n020.DATEEND is null) limit 1) aa on true
	where t2.N021OnkLpshID is null;

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