DROP PROCEDURE IF EXISTS sp_f039bankmoload;
DROP FUNCTION IF EXISTS sp_f039bankmoload;

CREATE PROCEDURE public.sp_f039bankmoload(
	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_f039;

	CREATE TEMP TABLE temp_f039 as
	SELECT 
		 IDMO
		,MCOD
		,UIDMO
		,ACCOUNTTYPE
		,ACCOUNTNUM
		,CORRACCOUNT
		,BANKNAME
		,BANKBIK
		,INN
		,KPP
		,STA_CHARACTER
		,TOFK_BIK
		,TOFK_CODE
		,TOFK_NAME
		,PERSONAL_ACCOUNT
		,FUNCTION_PERSONAL_ACCOUNT
		,NAME_AGENCY	
		,PERSONAL_ACCOUNT_INCOME_ADMIN
		,INN_INCOME_ADMIN
		,NAME_INCOME_ADMIN
		,CODE_TOFK
		,KPP_INCOME_ADMIN
		,TREASURE_ACC_DEP_NAME
		,TREASURE_ACC_DEP_INN 
		,TREASURE_ACC_DEP_KPP
		,DOG_NUM 
		,CASE WHEN  DOG_DATE  = '' THEN NULL ELSE DOG_DATE::TIMESTAMP END 
		,DOG_TERM::int
		,CASE WHEN  DOG_TERM_DATE  = '' THEN NULL ELSE DOG_TERM_DATE::TIMESTAMP 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 
		IDMO text path './IDMO' null,                                              
		MCOD text path './MCOD' null, 
		UIDMO text path './UIDMO' null, 
		ACCOUNTTYPE int path './ACCOUNTTYPE' null, 
		ACCOUNTNUM text path './ACCOUNTNUM' null, 
		BANKBIK text path './BANKBIK' null, 
		CORRACCOUNT text path './CORRACCOUNT' null, 
		BANKNAME text path './BANKNAME' null, 
		INN text path './INN' null, 
		KPP text path './KPP' null, 
		STA_CHARACTER text path './STA_CHARACTER' null, 
		TOFK_BIK text path './TOFK_BIK' null, 
		TOFK_CODE text path './TOFK_CODE' null, 
		TOFK_NAME text path './TOFK_NAME' null, 
		PERSONAL_ACCOUNT text path './PERSONAL_ACCOUNT' null, 
		FUNCTION_PERSONAL_ACCOUNT text path './FUNCTION_PERSONAL_ACCOUNT' null, 
		NAME_AGENCY text path './NAME_AGENCY' null, 
		PERSONAL_ACCOUNT_INCOME_ADMIN boolean path './PERSONAL_ACCOUNT_INCOME_ADMIN' null, 
		INN_INCOME_ADMIN text path './INN_INCOME_ADMIN' null, 
		NAME_INCOME_ADMIN text path './NAME_INCOME_ADMIN' null, 
		CODE_TOFK text path './CODE_TOFK' null, 
		KPP_INCOME_ADMIN text path './KPP_INCOME_ADMIN' null, 
		TREASURE_ACC_DEP_NAME text path './TREASURE_ACC_DEP_NAME' null, 
		TREASURE_ACC_DEP_INN text path './TREASURE_ACC_DEP_INN' null, 
		TREASURE_ACC_DEP_KPP text path './TREASURE_ACC_DEP_KPP' null,
		DOG_NUM  text path './DOG_NUM' null,
		DOG_DATE text path './DOG_DATE' null,
		DOG_TERM text path './DOG_TERM' null,
		DOG_TERM_DATE text path './DOG_TERM_DATE' null,	
		DATEBEG text path './DATEBEG' null, 
		DATEEND text path './DATEEND' null );

	DROP TABLE IF EXISTS  f032_filtered; 
	create temp table  f032_filtered AS 
	select 
	distinct on (f032.UIDMO) f032.UIDMO, f032.F032TrmoID 
	from ies.T_F032_TRMO f032 
	where  f032.Actual = true 
	order by f032.UIDMO,f032.D_BEGIN desc;

	-- обновляем актуальность у записей которых уже нет в Фед. справочнике
	update ies.t_f039_bank_mo t1 
		set Actual = false  
	where not exists(select 1 from temp_f039 t2 
					join f032_filtered f032 on f032.UIDMO = t2.UIDMO
					join ies.T_F003_MO f003 on t2.MCOD = f003.MCOD
	where f032.F032TrmoID = t1.F032Trmo and   t1.DATEBEG = t2.DATEBEG and t2.DOG_NUM = t1.DOG_NUM )
	and t1.Actual = true;

	-- обновляем актуальность у записей которые есть в Фед. справочнике
	update ies.t_f039_bank_mo t1 
		set Actual = true 
	where exists(select 1 from temp_f039 t2 
					join f032_filtered f032 on f032.UIDMO = t2.UIDMO
					join ies.T_F003_MO f003 on t2.MCOD = f003.MCOD
	where f032.F032TrmoID = t1.F032Trmo and   t1.DATEBEG = t2.DATEBEG and t2.DOG_NUM = t1.DOG_NUM )
	and t1.Actual = false;

	INSERT INTO ies.t_f039_bank_mo
				(F039BankMoID, F031Ermo, F003Mo, F032Trmo, ACCOUNTTYPE, ACCOUNTNUM, BANKBIK, CORRACCOUNT, BANKNAME, INN, KPP, STA_CHARACTER, TOFK_BIK, TOFK_CODE, TOFK_NAME
				, PERSONAL_ACCOUNT, FUNCTION_PERSONAL_ACCOUNT, NAME_AGENCY, PERSONAL_ACCOUNT_INCOME_ADMIN, INN_INCOME_ADMIN, NAME_INCOME_ADMIN, CODE_TOFK, KPP_INCOME_ADMIN
				, TREASURE_ACC_DEP_NAME, TREASURE_ACC_DEP_INN, TREASURE_ACC_DEP_KPP, DATEBEG, DATEEND ,DOG_NUM, DOG_DATE, DOG_TERM, DOG_TERM_DATE,Actual)
		select
				gen_random_uuid(), null, t1.MCOD, f032.F032TrmoID, t1.ACCOUNTTYPE, t1.ACCOUNTNUM, t1.BANKBIK, t1.CORRACCOUNT, t1.BANKNAME, t1.INN, t1.KPP, t1.STA_CHARACTER, t1.TOFK_BIK, t1.TOFK_CODE, t1.TOFK_NAME
				, t1.PERSONAL_ACCOUNT, t1.FUNCTION_PERSONAL_ACCOUNT, t1.NAME_AGENCY, t1.PERSONAL_ACCOUNT_INCOME_ADMIN , t1.INN_INCOME_ADMIN, t1.NAME_INCOME_ADMIN, t1.CODE_TOFK, t1.KPP_INCOME_ADMIN
				, t1.TREASURE_ACC_DEP_NAME, t1.TREASURE_ACC_DEP_INN, t1.TREASURE_ACC_DEP_KPP, t1.DATEBEG, t1.DATEEND, t1.DOG_NUM, t1.DOG_DATE, t1.DOG_TERM, t1.DOG_TERM_DATE,true
	FROM temp_f039 t1
	join f032_filtered f032 on f032.UIDMO = t1.UIDMO
	join ies.T_F003_MO f003 on t1.MCOD = f003.MCOD
	left join ies.t_f039_bank_mo t2 on t2.F032Trmo = f032.F032TrmoID  and t2.DATEBEG = t1.DATEBEG and t2.DOG_NUM = t1.DOG_NUM and t2.Actual = true
	where t2.F039BankMoID is null;

DROP TABLE IF EXISTS temp_f039;
DROP TABLE IF EXISTS f032_filtered;

return;
End
$BODY$;

