DROP PROCEDURE IF EXISTS sp_f043frekmpload;
DROP FUNCTION IF EXISTS sp_f043frekmpload;

CREATE PROCEDURE public.sp_f043frekmpload(
	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_f043;
	
	CREATE TEMP TABLE temp_f043 as
	SELECT 
		IDEKMP,
		N_EXPERT,
		CASE WHEN D_BEGIN = '' THEN NULL ELSE D_BEGIN::TIMESTAMP END,
		CASE WHEN D_END = '' THEN NULL ELSE D_END::TIMESTAMP END,
		NAME_E, 
		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, 
		N_EXPERT text path './N_EXPERT' null, 
		D_BEGIN text path './D_BEGIN' null,
		D_END text path './D_END' null,
		NAME_E text path './NAME_E' 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_F043_FREKMP t2 
		SET 
            N_EXPERT       	  = t1.N_EXPERT
           ,D_BEGIN			  = t1.D_BEGIN
           ,D_END			  = t1.D_END
           ,NAME_E			  = t1.NAME_E
           ,WORKPHONE		  = t1.WORKPHONE
           ,MOBILEPHONE		  = t1.MOBILEPHONE
           ,EMAIL			  = t1.EMAIL
           ,MEE				  = t1.MEE
           ,KMP				  = t1.KMP
           ,DATEBEG			  = t1.DATEBEG
           ,DATEEND			  = t1.DATEEND
	FROM temp_f043 t1 
	WHERE  t1.IDEKMP = t2.IDEKMP ;


	INSERT INTO IES.T_F043_FREKMP
           (F043FrekmpID
           ,IDEKMP
           ,N_EXPERT
           ,D_BEGIN
           ,D_END
           ,NAME_E
           ,WORKPHONE
           ,MOBILEPHONE
           ,EMAIL
           ,MEE
           ,KMP
           ,DATEBEG
           ,DATEEND)
    SELECT 
			gen_random_uuid()
           ,t1.IDEKMP
           ,t1.N_EXPERT
           ,t1.D_BEGIN
           ,t1.D_END
           ,t1.NAME_E
           ,t1.WORKPHONE
           ,t1.MOBILEPHONE
           ,t1.EMAIL
           ,t1.MEE
           ,t1.KMP
           ,t1.DATEBEG
           ,t1.DATEEND
	FROM temp_f043 t1 
	LEFT JOIN IES.T_F043_FREKMP t2 ON t1.IDEKMP = t2.IDEKMP
	WHERE t2.F043FrekmpID is null;


	DROP TABLE IF EXISTS temp_f043;

	return;
End
$BODY$;


