CREATE OR REPLACE FUNCTION public.sp_loadpplist(x xml DEFAULT NULL::xml, "FileID" uuid DEFAULT NULL::uuid, "Worker" uuid DEFAULT NULL::uuid)
 RETURNS TABLE(res boolean, err text)
 LANGUAGE plpgsql
AS $function$
declare 
	_FILENAME varchar(50);	
	_ExistFile uuid;
begin
		Drop Table If Exists TmpZGLV;
	Create Temp Table TmpZGLV
	As
	select
		"FileID" as FileId
	,	(xpath('//VERSION[1]/text()', z))[1]::varchar(5)                            as VERSION
	,	to_timestamp((xpath('//DATA[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD')	as DATA
	,	(xpath('//FILENAME[1]/text()', z))[1]::varchar(50)                          as FILENAME
	,	(xpath('//MCOD[1]/text()', z))[1]::varchar(6)                               as MCOD
	,	date_trunc('day', now()::timestamp)											as ReceivedDate
	,	now()::timestamp															as ReceivedTime
	from
	(
		select unnest(xpath('/PP_LIST/ZGLV', x)) as z
	) t1;
	
	select 
		into _FILENAME
		t.FileName
	from TmpZGLV t
	limit 1;
	
    Drop Table If Exists TmpPERS;
	Create Temp Table TmpPERS
	As
	select
		gen_random_uuid() as DispDirectFactId
		, (xpath('//ID_PAC[1]/text()', z))[1]::varchar(36)::uuid as ID_PAC
		, (xpath('//FAM[1]/text()', z))[1]::varchar(40) as FAM
		, (xpath('//IM[1]/text()', z))[1]::varchar(40) as IM
		, (xpath('//OT[1]/text()', z))[1]::varchar(40) as OT
		, to_timestamp((xpath('//DR[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD') as DR
		, (xpath('//ENP[1]/text()', z))[1]::varchar(16) as ENP
		, (xpath('//VPOLIS[1]/text()', z))[1]::text::int as VPOLIS
		, (xpath('//SPOLIS[1]/text()', z))[1]::varchar(10) as SPOLIS
		, (xpath('//NPOLIS[1]/text()', z))[1]::varchar(20) as NPOLIS
		, (xpath('//SNILS[1]/text()', z))[1]::varchar(14) as SNILS
		, to_timestamp((xpath('//PP[1]/DATA_1[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD') as DATA_1
		, to_timestamp((xpath('//PP[1]/DATA_2[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD') as DATA_2
		, (xpath('//PP[1]/DISP[1]/text()', z))[1]::varchar(3) as DISP
		, (xpath('//PP[1]/P_OTK[1]/text()', z))[1]::text::bool as P_OTK
		, (xpath('//PP[1]/RSLT_D[1]/text()', z))[1]::text::int as RSLT_D
		, (xpath('//PP[1]/NHISTORY[1]/text()', z))[1]::varchar(50) as NHISTORY
		, (xpath('//PP[1]/DS1[1]/text()', z))[1]::varchar(10) as DS1
		, (xpath('//PP[1]/DS1_PR[1]/text()', z))[1]::text::bool as DS1_PR
		, (xpath('//PP[1]/DS_ONK[1]/text()', z))[1]::text::bool as DS_ONK
		, (xpath('//PP[1]/PR_D_N[1]/text()', z))[1]::text::int as PR_D_N
		, to_timestamp((xpath('//DATA[1]/text()', zglv))[1]::varchar(10), 'YYYY-MM-DD')	 as DATA	
		, (xpath('//MCOD[1]/text()', zglv))[1]::varchar(6) as MCOD
--	,	_DATA																		as DATA
--	,	_MCOD																		as CODE_MO
		,	(xpath('//V016Dispt[1]/text()', z))[1]::varchar(36)::uuid as V016Dispt -- заглушка
		,	(xpath('//InsuredPolicyRecordID[1]/text()', z))[1]::varchar(36)::uuid as InsuredPolicyRecordID -- заглушка	
		,	(xpath('//DispDirectFactIdOld[1]/text()', z))[1]::varchar(36)::uuid as DispDirectFactIdOld -- заглушка	
		,	(xpath('//Insured[1]/text()', z))[1]::text::uuid as Insured -- заглушка
	from
	(
		select zglv, unnest(xpath('/PP_LIST/ZL', x)) as z		
		from
		(
			select unnest(xpath('/PP_LIST/ZGLV', x)) as zglv
		) t1
	) t2;
	
	Drop Table If Exists TmpUSL;
	Create Temp Table TmpUSL
	As
	select
	  (xpath('./ID_PAC[1]/text()', z))[1]::varchar(36)::uuid		as ID_PAC	
	, to_timestamp((xpath('//DATE_IN[1]/text()', usl))[1]::varchar(10), 'YYYY-MM-DD') as DATE_IN
	, to_timestamp((xpath('//DATE_OUT[1]/text()', usl))[1]::varchar(10), 'YYYY-MM-DD') as DATE_OUT
	, (xpath('//P_OTK[1]/text()', usl))[1]::text::bool as P_OTK
	, (xpath('//CODE_USL[1]/text()', usl))[1]::varchar(20) as CODE_USL
	, (xpath('./USL[1]/text()', usl))[1]::varchar(254) as USL
	, (xpath('./PRVS[1]/text()', usl))[1]::text::int as PRVS
	, (xpath('./CODE_MD[1]/text()', usl))[1]::varchar(25) as CODE_MD
	from
	(
		select z, unnest(xpath('/ZL/PP/USL', z)) as usl
		from
		(
			select unnest(xpath('/PP_LIST/ZL', x)) as z
		) t1
	) t2;


	update TmpPERS p
	set V016Dispt = v016.V016ID
	from iszl.T_V016_DISPT v016 
	where v016.IDDT = p.DISP
	and v016.DATEEND is null;

	Drop Table If Exists TmpErrors;
	Create Temp Table TmpErrors
	As
	select 1::int as Code, 'Значение поля IDDT не содержится в справочнике V016 ID_PAC(' || t1.ID_PAC::varchar(36) || ') NPOLIS(' || t1.NPOLIS || ')'::varchar(250) as Comment
	from TmpPERS t1
	where t1.V016Dispt is null;
	
	--0. Ищем полис по номеру полиса. Не важно актуальный он или нет
	--step 0.
	update TmpPERS t1
	set InsuredPolicyRecordID = 
	(
		select t2.InsuredPolicyRecordID 
		from ISZL.T_INSURED_POLICY_RECORD t2
		join ISZL.T_POLICY p on p.PolicyID = t2.Policy
		where t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Patronymic = t1.OT and t2.Birthday = t1.DR 
		and p.Number = t1.NPOLIS
		limit 1
	)
	where t1.InsuredPolicyRecordID is null 
	and not exists(Select* from TmpErrors);
	--step 1. без отчества
	update TmpPERS t1
	set InsuredPolicyRecordID = 
	(
		select t2.InsuredPolicyRecordID 
		from ISZL.T_INSURED_POLICY_RECORD t2
		join ISZL.T_POLICY p on p.PolicyID = t2.Policy
		where t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Birthday = t1.DR and p.Number = t1.NPOLIS
		limit 1
	)	
	where t1.InsuredPolicyRecordID is null
	and not exists(select* from TmpErrors);
	--step 2. без отчества и ДР
	update TmpPERS t1
	set InsuredPolicyRecordID = 
	(
		select t2.InsuredPolicyRecordID 
		from ISZL.T_INSURED_POLICY_RECORD t2
		join ISZL.T_POLICY p on p.PolicyID = t2.Policy
	  	where t2.LastName = t1.FAM and t2.FirstName = t1.IM and p.Number = t1.NPOLIS
		limit 1
	 )
	where t1.InsuredPolicyRecordID is null
	and not exists(select* from TmpErrors);
	--step 3. без отчества и Фамилии
	update TmpPERS t1
	set InsuredPolicyRecordID = 
	(
	  	select t2.InsuredPolicyRecordID 
	  	from ISZL.T_INSURED_POLICY_RECORD t2
		join ISZL.T_POLICY p on p.PolicyID = t2.Policy
		where t2.FirstName = t1.IM and t2.Birthday = t1.DR and p.Number = t1.NPOLIS
		limit 1
	)
	where t1.InsuredPolicyRecordID is null
	and not exists(select* from TmpErrors);

	insert into TmpErrors(CODE, COMMENT)
	select 1, 'Не найден полис из учета полисов застрахованного ID_PAC(' || t1.ID_PAC::varchar(36) || ') NPOLIS(' || t1.NPOLIS || ')'
	from TmpPERS t1
	where t1.InsuredPolicyRecordID is null;

	insert into TmpErrors(CODE, COMMENT)
	select 1, 'FileId имеет значение null'
	from TmpZGLV t1
	where t1.fileid is null;

	INSERT INTO TmpErrors(CODE, COMMENT)
	SELECT 2
		,'Дубль ID_PAC ' || t1.ID_PAC::varchar(36)
	FROM TmpPERS t1
	GROUP BY t1.ID_PAC
	having count(*) > 1;

	if exists(select* from TmpErrors)
		then
			return query
			select '0'::boolean
			, '<?xml version="1.0" encoding="utf-8"?>
			' || 
			(
				xmlelement
				(
					name "ROOT"
				,	(
						select
						xmlagg
						(
							xmlelement
							(
								name "ERR"
							,	xmlforest
								(
									te.CODE AS "CODE"
								,	te.COMMENT AS "COMMENT"
								)
							)
						)
						FROM TmpErrors te
					)
				)
			)::text;
			raise notice 'has errors';
		else
		begin		
			raise notice 'set insured';
			UPDATE TmpPERS as t0
			SET Insured = t1.Insured
			from ISZL.T_INSURED_POLICY_RECORD t1 
			where t0.InsuredPolicyRecordID = t1.InsuredPolicyRecordID;		

			insert into VCLib.T_FILE(FileID, Name, CreateDateTime, FileNameForm, SaveFileLocation, UseOle, type_)
			select t.FileId, t.FILENAME, now()::timestamp, 1, 1, '0'
			, (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT_FILE' and e.schema = 'ISZL' limit 1)
			from TmpZGLV t;
		
			INSERT INTO iszl.t_disp_direct_fact_file
			(fileid, "version", receiveddate, receivedtime, worker, "year", "month", v016dispt, filename, "data")
			select
			"FileID", "version", receiveddate, receivedtime, "Worker", null, null, null, filename, "data"
			from TmpZGLV t0;

			raise notice 'find existing disp direct fact';
			UPDATE TmpPERS as t0
			SET DispDirectFactIdOld = t1.DispDirectFactID
			from ISZL.T_DISP_DIRECT_FACT t1
			where t0.MCOD = t1.CODE_MO
				AND t0.data = t1.DateBegin
				AND t0.V016Dispt = t1.V016Dispt
				AND t0.Insured = t1.Insured;
		
			raise notice 'update existing disp direct fact';
			UPDATE ISZL.T_DISP_DIRECT_FACT as t0
			SET  DateEnd = t1.DATA_2
				,P_OTK = t1.P_OTK
				,RSLT_D = t1.RSLT_D
				,NHISTORY = t1.NHISTORY
				,DS1 = t1.DS1
				,DS1_PR = t1.DS1_PR
				,DS_ONK = t1.DS_ONK
				,PR_D_N = t1.PR_D_N
			FROM TmpPERS t1
			where t0.DispDirectFactID = t1.DispDirectFactIdOld;	
     
			raise notice 'delete existing disp direct fact usls';
			DELETE
			FROM ISZL.T_DISP_DIRECT_FACT_USL as t0
			using TmpPERS t1
			where t0.DispDirectFact = t1.DispDirectFactIdOld;
		
			raise notice 'чтобы вставить услуги скопом поменяем новый идентификатор на уже существующий';		
			UPDATE TmpPERS
			SET DispDirectFactId = DispDirectFactIdOld
			WHERE DispDirectFactIdOld IS NOT null;
			
			INSERT INTO iszl.t_disp_direct_fact
			(dispdirectfactid, datebegin, dateend, fam, im, ot, rslt_d, dispdirect, dr, enp, code_mo, ishod, nschet, "year", "month", v016dispt, dispdirectfactfile, "comments", pr_d_n, ds1, insured, creationdate, schetsluch, vpolis, spolis, npolis, snils, p_otk, nhistory, ds1_pr, ds_onk)
			select
			t0.DispDirectFactId
			,t0.DATA_1 --<DateBegin, datetime,>
			,t0.DATA_2 --<DateEnd, datetime,>
			,t0.FAM --<FAM, varchar(40),>
			,t0.IM --<IM, varchar(40),>
			,t0.OT --<OT, varchar(40),>
			,t0.RSLT_D --<RSLT_D, int,>
			,NULL --<DispDirect, uniqueidentifier,>
			,t0.DR --<DR, datetime,>
			,t0.ENP --<ENP, varchar(16),>
			,t0.MCOD --<CODE_MO, varchar(6),>
			,NULL --<ISHOD, int,>
			,NULL --<NSCHET, varchar(15),>
			,NULL --<YEAR, int,>
			,NULL --<MONTH, int,>
			,coalesce(t0.V016Dispt,'8691f4f7-3e7a-48af-9c8b-79b98946a167') --<V016Dispt, uniqueidentifier,>
			,"FileID" --<DispDirectFactFile, uniqueidentifier,>
			,NULL --<Comments, varchar(4000),>
			,t0.PR_D_N --<PR_D_N, int,>
			,t0.DS1 --<DS1, varchar(10),>
			,t0.Insured --<Insured, uniqueidentifier,>
			,now()::timestamp --<CreationDate, datetime,>
			,NULL --<SchetSluch, uniqueidentifier,>
			,t0.VPOLIS --<VPOLIS, int,>
			,t0.SPOLIS --<SPOLIS, varchar(255),>
			,t0.NPOLIS --<NPOLIS, varchar(255),>
			,t0.SNILS --<SNILS, varchar(255),>
			,t0.P_OTK --<P_OTK, bit,>
			,t0.NHISTORY --<NHISTORY, varchar(255),>
			,t0.DS1_PR --<DS1_PR, bit,>
			,t0.DS_ONK --<DS_ONK, bit,>)
			from TmpPERS t0
			WHERE t0.DispDirectFactIdOld IS null;
		
			raise notice 'insert old and new T_DISP_DIRECT_FACT_USLs';
			INSERT INTO iszl.t_disp_direct_fact_usl
			(dispdirectfactuslid, date_in, date_out, p_otk, code_usl, usl, prvs, code_md, dispdirectfact)
			select 
			gen_random_uuid() --(<DispDirectFactUslID, uniqueidentifier,>
			,t0.DATE_IN --<DATE_IN, datetime,>
			,t0.DATE_OUT --<DATE_OUT, datetime,>
			,t0.P_OTK --<P_OTK, bit,>
			,t0.CODE_USL --<CODE_USL, varchar(20),>
			,t0.USL --<USL, varchar(254),>
			,t0.PRVS --<PRVS, int,>
			,t0.CODE_MD --<CODE_MD, varchar(25),>
			,t1.DispDirectFactId --<DispDirectFact, uniqueidentifier,>)
			from TmpUSL t0
			join TmpPERS t1 on t0.ID_PAC = t1.ID_PAC;
		
			raise notice 'insert old and new T_DISP_DIRECT_FACT_USLs';
			Drop Table If Exists TmpLink;
			Create Temp Table TmpLink
			As			
			select t0.DispDirectID
			, (
				select  t100.DispDirectFactId
				from ISZL.T_DISP_DIRECT_FACT t100
				join TmpPERS t101 on t100.DispDirectFactID = t100.DispDirectFactID
				--по ид файла нельзя т.к. часть записей ушло в старый файл а часть в новый
				where t100.Insured = t0.Insured
				AND t100.V016Dispt = t0.V016Dispt
				AND date_part('year',t100.DateBegin) = t0."year" 
				and t100.DispDirect is null
				limit 1
			) as DispDirectFactId
			from iszl.T_DISP_DIRECT t0
			where not exists (select 1
			from ISZL.T_DISP_DIRECT_FACT t100
			where t100.DispDirect = t0.DispDirectId);
		
			UPDATE ISZL.T_DISP_DIRECT_FACT as t0
			SET DispDirect = t1.DispDirectID
			from TmpLink t1
			where t0.DispDirectFactID = t1.DispDirectFactId;
		
			_ExistFile := (select FileID from ISZL.T_DISP_DIRECT_FILE where FILENAME = _FILENAME limit 1);

			if _ExistFile is not null
			then
				perform sp_DispDirectFactFileDel(_ExistFile);
			end if;			
		end;
	end if;
end;
$function$
;
