CREATE OR REPLACE FUNCTION public.sp_loadproflist(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);
	_SMO varchar(5);
	_MCOD varchar(6);
	_DATA timestamp;
	_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
	,	(xpath('//SMO[1]/text()', z))[1]::varchar(5)                               	as SMO
	,	date_trunc('day', now()::timestamp)											as ReceivedDate
	,	now()::timestamp															as ReceivedTime
	from
	(
		select unnest(xpath('/PROF_LIST/ZGLV', x)) as z
	) t1;
	
	select 
		into _FILENAME, _SMO, _MCOD, _DATA
		t.FileName, t.Smo, t.Mcod, t.Data
	from TmpZGLV t
	limit 1;
	
    Drop Table If Exists TmpPERS;
	Create Temp Table TmpPERS
	As
	select
		gen_random_uuid()	as ItemID
	,	(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
	,	(xpath('//W[1]/text()', z))[1]::text::int                             		as W
	,	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
	,	(xpath('//PROF[1]/MONTH[1]/text()', z))[1]::text::int                       as MONTH
	,	(xpath('//PROF[1]/YEAR[1]/text()', z))[1]::text::int                        as YEAR
	,	(xpath('//PROF[1]/TYPE[1]/text()', z))[1]::text::int                        as TYPE
	,	(xpath('//PROF[1]/IDDT[1]/text()', z))[1]::varchar(3)                       as IDDT
	,	_DATA																		as DATA
	,	_SMO																		as SMO
	,	_MCOD																		as CODE_MO
	,	(xpath('//V016[1]/text()', z))[1]::varchar(36)::uuid           				as V016 -- заглушка
	,	(xpath('//InsuredPolicyRecordID[1]/text()', z))[1]::varchar(36)::uuid     	as InsuredPolicyRecordID -- заглушка	
	,	(xpath('//DispDirectID[1]/text()', z))[1]::varchar(36)::uuid   			  	as DispDirectID -- заглушка	
	,	(xpath('//DuplicateCount[1]/text()', z))[1]::text::int 				    	as DuplicateCount -- заглушка
	from
	(
		select unnest(xpath('/PROF_LIST/ZL', x)) as z
	) t1;
	
	Drop Table If Exists TmpCONTACTS;
	Create Temp Table TmpCONTACTS
	As
	select
		(xpath('//ID_PAC[1]/text()', z))[1]::varchar(36)::uuid		as ID_PAC
	,	(xpath('//ENP[1]/text()', z))[1]::varchar(16)          		as ENP
	,	(xpath('//CONTACT[1]/text()', con))[1]::varchar(255)		as CONTACT
	,	(xpath('//TYPE[1]/text()', con))[1]::text::varchar(2)		as TYPE
	from
	(
		select z, unnest(xpath('/ZL/CONTACTS', z)) as con
		from
		(
			select unnest(xpath('/PERS_LIST/ZL', x)) as z
		) t1
	) t2;
	
	
	update TmpPERS p
	set V016 = v016.V016ID
	from iszl.T_V016_DISPT v016 
	where v016.IDDT = p.IDDT
	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.V016 is null;

/*	-- проверка на дубликат
	update TmpPERS p 
	set DuplicateCount = (select count(*) 
	from iszl.t_disp_direct dd
		left join iszl.t_organization o on o.organizationid = dd.mo and o.type_ = (select entityid from vclib.t_entities e where e.entname = 'MO' limit 1)
	where not (dd.fam = p.FAM
		and dd.im = p.IM
		and dd.ot = p.OT
		and dd.dr = p.DR)
	and dd.type_ = (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT' limit 1)
	and dd.v016dispt = p.V016
	and dd."year" = p."year"
	and dd.month_q = p."month"
	and o.code = p.CODE_MO);
	
	insert into TmpErrors(CODE, COMMENT)
	select 4, 'Уже существует запись с такими параметрами'
	from TmpPERS p
	where p.DuplicateCount > 0;*/ 
	
	--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 3, 'Повторное направление на проф. мероприятия в отчетном году. ID_PAC(' || t1.ID_PAC::varchar(36) || ') NPOLIS(' || t1.NPOLIS || ')'
	from TmpPERS t1
	join iszl.T_INSURED_POLICY_RECORD ipr on ipr.InsuredPolicyRecordID = t1.InsuredPolicyRecordID
	where (t1.IDDT = 'ОПВ' or t1.IDDT = 'ДВ4') and t1.type = 0 and exists (
					select t2.DispDirectID  
					from ISZL.T_DISP_DIRECT t2
						join iszl.T_V016_DISPT v016 on v016.V016ID = t2.V016Dispt
					where (t2.YEAR = t1.YEAR and t2.Insured = ipr.Insured and (v016.IDDT = 'ОПВ' or v016.IDDT = 'ДВ4'))   
						and t2.type_ = (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT' limit 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;
	else
		if "FileID" is not null
		then
			--------------------
			--Сheck File Exist--
			--------------------
			_ExistFile := (select FileID from ISZL.T_DISP_DIRECT_FILE where FILENAME = _FILENAME limit 1);

			if _ExistFile is not null
			then
				perform sp_Prof_Delete(_ExistFile);
			end if;

			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_FILE(FileID, VERSION, DATA, FILENAME, receiveddate, recievedtime
			, YEAR, MONTH, CODE_MO, Worker, ID_MO)
			select t.FileId, t.VERSION, t.DATA, t.FILENAME, now()::timestamp, now()::timestamp, null, null
			, t.MCOD, "Worker", null
			from TmpZGLV t;
		else
		
			--DELETE FROM ISZL.T_DISP_DIRECT WHERE FileName = _FILENAME;


			update iszl.T_DISP_DIRECT t1
			set DispDirectID = (
					select t2.DispDirectID  
					from ISZL.T_DISP_DIRECT t2
					where (t2.YEAR = t1.YEAR and t2.V016Dispt = t1.V016 and t2.Insured = ipr.Insured) 
					and t2.type_ = (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT' limit 1)
					limit 1  
				)
			from TmpPERS p
			join iszl.T_INSURED_POLICY_RECORD ipr on ipr.InsuredPolicyRecordID = t1.InsuredPolicyRecordID
			where t1.type_ = (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT' limit 1);

		end if;

		------------------
		--Добавляем только новых в iszl.T_DISP_DIRECT из #PERS  p.[TYPE] = 0
	 	INSERT INTO iszl.T_DISP_DIRECT(DispDirectID, DispDirectFile, ID_PAC, FAM, IM, OT, Sex, DR, ENP, VPOLIS, SPOLIS
		, NPOLIS, SNILS_P, YEAR, V016Dispt, MONTH_Q, Smo, Mo, Insured, InsuredPolicyRecord, FileName)
	 	select p.ItemID, "FileID", p.ID_PAC, p.FAM, p.IM, p.OT, p.W, p.DR, p.ENP, p.VPOLIS, p.SPOLIS
		, p.NPOLIS, p.SNILS, p.YEAR, p.V016, p.MONTH, orgsmo.OrganizationID, orgmo.OrganizationID
		, ipr.Insured, p.InsuredPolicyRecordID, _FILENAME
	 	from TmpPERS p
	 	inner join
			iszl.T_INSURED_POLICY_RECORD ipr 
			on ipr.InsuredPolicyRecordID = p.InsuredPolicyRecordID
	 	left join
			ISZL.T_ORGANIZATION orgsmo 
			on orgsmo.Code = p.SMO
	 	left join
			ISZL.T_ORGANIZATION orgmo
			on orgmo.Code = p.CODE_MO
		where p.TYPE = 0;


		--обновляем данные где type = 1
		update iszl.T_DISP_DIRECT dd
		set DispDirectFile = "FileID"
		, FAM = p.FAM
		, IM = p.IM
		, OT = p.OT
		, Sex = p.W
		, DR = p.DR
		, ENP = p.ENP
		, VPOLIS = p.VPOLIS
		, SPOLIS = p.SPOLIS
		, NPOLIS = p.NPOLIS
		, SNILS_P = p.SNILS
		, Smo = orgsmo.OrganizationID
		, Mo = orgmo.OrganizationID
		, V016Dispt = p.V016
		, MONTH_Q = p.MONTH
		from TmpPERS p
		left join ISZL.T_ORGANIZATION orgsmo on orgsmo.Code = p.SMO
		left join ISZL.T_ORGANIZATION orgmo on orgmo.Code = p.CODE_MO
		where dd.ENP = p.ENP and dd.YEAR = p.YEAR and dd.V016Dispt = p.V016 and p.TYPE = 1
		and not exists(select* from iszl.T_DISP_DIRECT_FACT fact where fact.DispDirect = dd.DispDirectID)
		and dd.type_ = (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT' limit 1);
	 
		------------------
		--Удаляем из iszl.T_DISP_DIRECT 

		delete from iszl.T_DISP_DIRECT dd
		where exists
		(
			select* from TmpPERS p where dd.ENP = p.ENP and dd.YEAR = p.YEAR and p.TYPE = 2 and dd.V016Dispt = p.V016
		)
		--and dd.DispDirectFile = "FileID"
		and dd.type_ = (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT' limit 1);

		--------------------
		INSERT INTO iszl.T_INSURED_CONTACT(InsuredContactID, Contact, ContactType, Insured)
		select gen_random_uuid(), con.CONTACT
		, (select ct.ContactTypeID from iszl.T_CONTACT_TYPE ct where ct.Name = con.TYPE limit 1)
		, ipr.Insured
		from TmpCONTACTS con
		inner join
			TmpPERS p
			on con.ID_PAC = p.ID_PAC
		inner join
			iszl.T_INSURED_POLICY_RECORD ipr
			on ipr.InsuredPolicyRecordID = p.InsuredPolicyRecordID
		where  p.TYPE = 0 
		and not exists(select* from iszl.T_INSURED_CONTACT c where c.Contact = con.CONTACT and c.Insured = ipr.Insured);
	end if;
end;
$function$
;
