DROP PROCEDURE IF EXISTS sp_f020smoload;
DROP FUNCTION IF EXISTS sp_f020smoload;

CREATE PROCEDURE public.sp_f020smoload("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_f020;
		
		CREATE TEMP TABLE temp_f020 as 
		SELECT 
			SMOCOD,
			OGRN, 
			KPP,
			NAM_SMOP,
			NAM_SMOK,
			ADDR_F,
			FAM_RUK,
			IM_RUK,
			OT_RUK,
			PHONE,
			FAX,
			HOT_LINE,
			E_MAIL
		FROM
		XMLTABLE ('/packet/zap' PASSING T 
		COLUMNS 
			SMOCOD text path './CODE' null, 
			OGRN text path './OGRN' null, 
			KPP text path './KPP' null,
			NAM_SMOP text path './NAM_P' null,
			NAM_SMOK text path './NAM_K' null,
			ADDR_F text path './ADDR_F' null,
			FAM_RUK text path './FAM_RUK' null,
			IM_RUK text path './IM_RUK' null, 
			OT_RUK text path './OT_RUK' null, 
			PHONE text path './PHONE' null,
			FAX text path './FAX' null,
			HOT_LINE text path './HOT_LINE' null,
			E_MAIL text path './E_MAIL' null);

		update IES.T_F020_SMO t1
			set Actual = false 
			   ,Date_edit_actual = current_timestamp
			where not exists(select 1 from temp_f020 t2 where t1.smocod = t2.SMOCOD) and t1.Actual = true;
		
		update IES.T_F020_SMO t2
			set  FullSmoName=t1.NAM_SMOP
				,ShortSmoName=t1.NAM_SMOK
				,OGRN=t1.OGRN
				,KPP=t1.KPP
				,Addr_F=t1.ADDR_F
				,Fam_Ruk=t1.FAM_RUK
				,Im_Ruk=t1.IM_RUK
				,Ot_Ruk=t1.OT_RUK
				,Phone = t1.PHONE
				,Fax = t1.FAX
				,Hotline_Phone = t1.HOT_LINE
				,Email = t1.E_MAIL
			from temp_f020 t1
			where t1.SMOCOD=t2.smocod and t2.Actual = true;
		
		insert into IES.T_F020_SMO
				   (F020SmoID,
				   Smocod,
				   OGRN,
				   KPP,
				   FullSmoName,
				   ShortSmoName,
				   Addr_F,
				   Fam_Ruk,
				   Im_Ruk,
				   Ot_Ruk,
				   Phone,
				   Fax,
				   Hotline_Phone,
				   Email,
				   Date_Load,
				   Actual
				   )
			select gen_random_uuid(),
				   t1.SMOCOD,
				   t1.OGRN,
				   t1.KPP, 
				   t1.NAM_SMOP, 
				   t1.NAM_SMOK, 
				   t1.ADDR_F,
				   t1.FAM_RUK,
				   t1.IM_RUK,
				   t1.OT_RUK,
				   t1.PHONE, 
				   t1.FAX, 
				   t1.HOT_LINE, 
				   t1.E_MAIL,
				   current_timestamp,
				   true
			from temp_f020 t1
			left join IES.T_F020_SMO t2 on (t1.SMOCOD=t2.smocod and t2.Actual = true)
			where t2.F020SmoID is null;
		
		DROP TABLE IF EXISTS temp_f020;
		
	End
$BODY$;