DROP PROCEDURE IF EXISTS public.sp_f001ffomsload;
DROP FUNCTION IF EXISTS public.sp_f001ffomsload;

DROP PROCEDURE IF EXISTS public.sp_f001tfomsload;
DROP FUNCTION IF EXISTS public.sp_f001tfomsload;


CREATE PROCEDURE public.sp_f001tfomsload(
	"Xml" bytea, "FileName" character varying)
    LANGUAGE 'plpgsql'
AS $BODY$
declare T_xml xml;
declare tempStrXml text;
	BEGIN
select convert_from("Xml", 'WIN1251') into tempStrXml;

select replace(tempStrXml, 'encoding="windows-1251"','')::xml into T_xml; 

Drop Table If Exists TmpF001;
Create Temp Table TmpF001 AS
select 
	nullif(trim(xpath('//tf_okato/text()', T)::text,'{}"'),'')::varchar(5) as tf_okato,
	nullif(trim(xpath('//tf_kod/text()', T)::text,'{}"'),'')::varchar(2) as tf_kod,
	nullif(trim(xpath('//name_tfp/text()', T)::text,'{}"'),'')::varchar(250) as name_tfp,
	nullif(trim(xpath('//name_tfk/text()', T)::text,'{}"'),'')::varchar(250) as name_tfk,
	nullif(trim(xpath('//index/text()', T)::text,'{}"'),'')::varchar(12) as index,
	nullif(trim(xpath('//tf_ogrn/text()', T)::text,'{}"'),'')::varchar(20) as tf_ogrn,
	nullif(trim(xpath('//address/text()', T)::text,'{}"'),'')::varchar(254) as address,
	nullif(trim(xpath('//fam_dir/text()', T)::text,'{}"'),'')::varchar(40) as fam_dir,
	nullif(trim(xpath('//im_dir/text()', T)::text,'{}"'),'')::varchar(40) as im_dir,
	nullif(trim(xpath('//ot_dir/text()', T)::text,'{}"'),'')::varchar(40) as ot_dir,
	nullif(trim(xpath('//phone/text()', T)::text,'{}"'),'')::varchar(40) as phone,
	nullif(trim(xpath('//fax/text()', T)::text,'{}"'),'')::varchar(40) as fax,
	nullif(trim(xpath('//e_mail/text()', T)::text,'{}"'),'')::varchar(50) as e_mail,
	nullif(trim(xpath('//www/text()', T)::text,'{}"'),'')::varchar(100) as www,
	nullif(trim(xpath('//kf_tf/text()', T)::text,'{}"'),'')::int as kf_tf,
	nullif(trim(xpath('//d_end/text()', T)::text,'{}"'),'')::date as d_end,
	nullif(trim(xpath('//d_edit/text()', T)::text,'{}"'),'')::date as d_edit,
	nullif(trim(xpath('//MTR[1]/bic/text()', T)::text,'{}"'),'')::varchar(9) as bic,
	nullif(trim(xpath('//MTR[1]/inn/text()', T)::text,'{}"'),'')::varchar(10) as inn,
	nullif(trim(xpath('//MTR[1]/kpp/text()', T)::text,'{}"'),'')::varchar(9) as kpp,
	nullif(trim(xpath('//MTR[1]/kbk/text()', T)::text,'{}"'),'')::varchar(20) as kbk,
	nullif(trim(xpath('//MTR[1]/oktmo/text()', T)::text,'{}"'),'')::varchar(8) as oktmo,
	nullif(trim(xpath('//MTR[1]/MTR_POL/L_NAIM/text()', T)::text,'{}"'),'')::varchar(250) as l_naim,
	nullif(trim(xpath('//MTR[1]/MTR_POL/L_B/text()', T)::text,'{}"'),'')::varchar(100) as l_b,
	nullif(trim(xpath('//MTR[1]/MTR_POL/L_RS/text()', T)::text,'{}"'),'')::varchar(20) as l_rs,
	nullif(trim(xpath('//MTR[1]/MTR_PL/T_NAIM/text()', T)::text,'{}"'),'')::varchar(250) as t_naim,
	nullif(trim(xpath('//MTR[1]/MTR_PL/T_B/text()', T)::text,'{}"'),'')::varchar(100) as t_b,
	nullif(trim(xpath('//MTR[1]/MTR_PL/T_RS/text()', T)::text,'{}"'),'')::varchar(20) as t_rs
from unnest(xpath('/packet/TFOMS', T_xml)) as T;

UPDATE ies.t_f001_tfoms t1
	   SET TF_KOD = t2.TF_KOD
		  ,TF_OKATO = t2.TF_OKATO
		  ,TF_OGRN = t2.TF_OGRN
		  ,NAME_TFP = t2.NAME_TFP
		  ,NAME_TFK = t2.NAME_TFK
		  ,INDEX_ = t2.INDEX
		  ,ADDRESS_ = t2.ADDRESS
		  ,FAM_DIR = t2.FAM_DIR
		  ,IM_DIR = t2.IM_DIR
		  ,OT_DIR = t2.OT_DIR
		  ,PHONE = t2.Phone
		  ,FAX = t2.FAX
		  ,E_MAIL = t2.E_MAIL
		  ,WWW = t2.WWW
		  ,KF_TF=t2.kf_tf
		  ,D_EDIT = t2.D_EDIT
		  ,D_END = t2.D_END
		  ,BIK = t2.BIC
		  ,INN = t2.INN
		  ,KPP = t2.KPP
		  ,KBK = t2.KBK
		  ,OKTMO = t2.OKTMO
		  ,L_NAIM = t2.L_NAIM
		  ,L_B = t2.L_B
		  ,L_RS = t2.L_RS
		  ,T_NAIM = t2.T_NAIM
		  ,T_B = t2.T_B
		  ,T_RS = t2.T_RS
	FROM TmpF001 t2
	where t2.tf_kod = t1.tf_kod;

INSERT INTO ies.t_f001_tfoms
           (TF_KOD, TF_OKATO, TF_OGRN, NAME_TFP, NAME_TFK, INDEX_, ADDRESS_, FAM_DIR, IM_DIR, OT_DIR
           , PHONE, FAX, E_MAIL,KF_TF, WWW, D_EDIT, D_END, BIK, INN, KPP, KBK, OKTMO, L_NAIM, L_B, L_RS, T_NAIM, T_B, T_RS)
    select 
	        t1.TF_KOD
           ,t1.TF_OKATO
           ,t1.TF_OGRN
           ,t1.NAME_TFP
           ,t1.NAME_TFK
           ,t1.INDEX
           ,t1.ADDRESS
           ,t1.FAM_DIR
           ,t1.IM_DIR
           ,t1.OT_DIR
           ,t1.PHONE
           ,t1.FAX
           ,t1.E_MAIL
		   ,t1.kf_tf
           ,t1.WWW
           ,t1.D_EDIT
           ,t1.D_END
		   ,t1.BIC
		   ,t1.INN
		   ,t1.KPP
		   ,t1.KBK
		   ,t1.OKTMO
		   ,t1.L_NAIM
		   ,t1.L_B
		   ,t1.L_RS
		   ,t1.T_NAIM
		   ,t1.T_B
		   ,t1.T_RS
FROM TmpF001 t1
left join IES.T_F001_TFOMS t2 on t2.tf_kod = t1.tf_kod
where t2.TF_KOD is null;

drop table if exists TmpF001;

END;
$BODY$;


