DROP PROCEDURE IF EXISTS sp_f018bankpayorgload;
DROP FUNCTION IF EXISTS sp_f018bankpayorgload;

CREATE PROCEDURE public.sp_f018bankpayorgload("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_f018;
		
		CREATE TEMP TABLE temp_f018 as
		SELECT 
			idzap, 
			tf_okato,
			orgcod::int,
			billcod,
			accounttype::int,
			accountnum,
			bankbik,
			corraccount,
			bankname,
			inn,
			kpp,
			sta_character,
			tofk_bik,
			tofk_code,
			tofk_name,
			personal_account,
			function_personal_account,
			name_agency,
			--с учетом того, что у столбца убрать ограничение NOT NULL, в НСИ пустой у всех записей
			CASE WHEN  personal_account_income_admin  = '' THEN NULL ELSE personal_account_income_admin::boolean END, 
			inn_income_admin,
			name_income_admin,
			code_tofk,
			kpp_income_admin,
			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 
			idzap text path './idzap' null, 
			tf_okato text path './tf_okato' null,
			orgcod text path './orgcod' null,
			billcod text path './billcod' null,
			accounttype text 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 text 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,
			datebeg text path './datebeg' null,
			dateend text path './dateend' null);
		
		update IES.T_F018_BANK_PAY_ORG t2 
			set  DateEnd=t1.dateend
		from temp_f018 t1
		where t1.idzap=t2.idzap;
		
		insert into IES.T_F018_BANK_PAY_ORG
			( F018BankPayOrgID
			, IDZAP			
			, TF_OKATO
			, OrgCod
			, BillCod
			, AccountType
			, AccountNum
			, BankBik
			, CorrAccount			
			, BankName
			, Inn
			, KPP
			, Sta_Character
			, TofkBik			
			, TofkCode
			, TofkName
			, PersonalAccount
			, FunctionPersonalAccount
			, NameAgency
			, PersonalAccountIncomeAdmin
			, InnIncomeAdmin
			, NameIncomeAdmin
			, CodeTofk
			, KppIncomeAdmin			
			, DateBeg
			, DateEnd)
		select 
			  gen_random_uuid()
			, t1.IDZAP			
			, t1.TF_OKATO
			, ab.F019PersAccOrgID
			, aa.F017BillTypesID
			, 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.DateBeg
			, t1.DateEnd
		FROM temp_f018 t1
		LEFT JOIN IES.T_F018_BANK_PAY_ORG t2 ON t2.IDZAP = t1.idzap
		JOIN LATERAL (SELECT * FROM IES.T_F017_BILL_TYPES f017 WHERE f017.Billcod = t1.billcod ORDER BY f017.DATEBEG DESC LIMIT 1) aa ON true
		JOIN LATERAL (SELECT * FROM IES.T_F019_PERS_ACC_ORG f019 WHERE f019.ORG_COD = t1.orgcod ORDER BY f019.D_BEGIN DESC LIMIT 1) ab ON true
		WHERE t2.F018BankPayOrgID IS NULL;
		
		DROP TABLE IF EXISTS temp_f018;
		
	End
$BODY$;
