DROP PROCEDURE IF EXISTS sp_f041erekmpload;
DROP FUNCTION IF EXISTS sp_f041erekmpload;

CREATE PROCEDURE public.sp_f041erekmpload(
	IN "Xml" bytea,
	IN "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_f041;
	
	CREATE TEMP TABLE temp_f041 as
	SELECT 
		IDEKMP, 
		CASE WHEN D_BEGIN = '' THEN NULL ELSE D_BEGIN::TIMESTAMP END, 
		FAM, 
		IM, 
		OT, 
		CER_NOM, 
		CASE WHEN CER_DATEBEG = '' THEN NULL ELSE CER_DATEBEG::TIMESTAMP END , 
		CASE WHEN CER_DATEEND = '' THEN NULL ELSE CER_DATEEND::TIMESTAMP END, 
		CER_ORG, 
		WORKPHONE, 
		MOBILEPHONE, 
		EMAIL, 
		MEE::bool, 
		KMP::bool, 
		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 
		IDEKMP text path './IDEKMP' null, 
		D_BEGIN text path './D_BEGIN' null, 
		FAM text path './FAM' null, 
		IM text path './IM' null, 
		OT text path './OT' null, 
		CER_NOM text path './CER_NOM' null, 
		CER_DATEBEG text path './CER_DATEBEG' null, 
		CER_DATEEND text path './CER_DATEEND' null, 
		CER_ORG text path './CER_ORG' null, 
		WORKPHONE text path './WORKPHONE' null, 
		MOBILEPHONE text path './MOBILEPHONE' null, 
		EMAIL text path './EMAIL' null, 
		MEE text path './MEE' null, 
		KMP text path './KMP' null, 
		DATEBEG text path './DATEBEG' null, 
		DATEEND text path './DATEEND' null );


	UPDATE IES.T_F041_EREKMP t2 
	SET 
		 D_BEGIN		  =  t1.D_BEGIN
		,FAM              =  t1.FAM
		,IM			  	  =  t1.IM
		,OT			 	  =  t1.OT
		,CER_NOM		  =  t1.CER_NOM
		,CER_DATEBEG	  =  t1.CER_DATEBEG
		,CER_DATEEND	  =  t1.CER_DATEEND
		,CER_ORG		  =  t1.CER_ORG
		,WORKPHONE	 	  =  t1.WORKPHONE
		,MOBILEPHONE	  =  t1.MOBILEPHONE
		,EMAIL		 	  =  t1.EMAIL
		,MEE			  =  t1.MEE
		,KMP			  =  t1.KMP
		,DATEBEG		  =  t1.DATEBEG
		,DATEEND		  =  t1.DATEEND
	 FROM temp_f041 t1 
	 WHERE  t1.IDEKMP = t2.IDEKMP;
	 
	
	INSERT INTO IES.T_F041_EREKMP
			   (F041ErekmpID
			   ,IDEKMP
			   ,D_BEGIN
			   ,FAM
			   ,IM
			   ,OT
			   ,CER_NOM
			   ,CER_DATEBEG
			   ,CER_DATEEND
			   ,CER_ORG
			   ,WORKPHONE
			   ,MOBILEPHONE
			   ,EMAIL
			   ,MEE
			   ,KMP
			   ,DATEBEG
			   ,DATEEND)
	 SELECT		gen_random_uuid()
			   ,t1.IDEKMP
			   ,t1.D_BEGIN
			   ,t1.FAM
			   ,t1.IM
			   ,t1.OT
			   ,t1.CER_NOM
			   ,t1.CER_DATEBEG
			   ,t1.CER_DATEEND
			   ,t1.CER_ORG
			   ,t1.WORKPHONE
			   ,t1.MOBILEPHONE
			   ,t1.EMAIL
			   ,t1.MEE
			   ,t1.KMP
			   ,t1.DATEBEG
			   ,t1.DATEEND 
	 FROM temp_f041 t1 
	 LEFT JOIN IES.T_F041_EREKMP t2 ON t1.IDEKMP = t2.IDEKMP
	 WHERE t2.F041ErekmpID is null;

	DROP TABLE IF EXISTS temp_f041;

	return;
End
$BODY$;


