CREATE OR REPLACE FUNCTION sp_DnInsert_kostroma
(
	x xml
,	"FileID" uuid default null::uuid
,	"Worker" uuid default null::uuid	
)
RETURNS TABLE (Res boolean, Err text) AS
$BODY$
declare
	_DnFileId uuid;
	_filexists 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]::text::varchar(6)                               	as MCOD
	,	(xpath('//SMO[1]/text()', z))[1]::text::varchar(5)                               	as SMO
	,	date_trunc('day', now()::timestamp)											as ReceivedDate
	,	now()::timestamp															as ReceivedTime
	from
	(
		select unnest(xpath('/DN_LIST/ZGLV', x)) as z
	) t1;
	
    Drop Table If Exists TmpZL;
	Create Temp Table TmpZL
	As
	select
		gen_random_uuid() as ItemID
	,	(xpath('//ID_PAC[1]/text()', z))[1]::varchar(36)                        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
	,	(xpath('//InsuredId[1]/text()', z))[1]::text::uuid                     as InsuredId
	from
	(
		select unnest(xpath('/DN_LIST/ZL', x)) as z
	) t1;

	Drop Table If Exists TmpCONTACTS;
	Create Temp Table TmpCONTACTS
	As
	select
		gen_random_uuid() as ContactID
	,	(xpath('//ID_PAC[1]/text()', z))[1]::varchar(36)		as ID_PAC
	,	(xpath('//CONTACT[1]/text()', con))[1]::varchar(250)	as CONTACT
	,	(xpath('//TYPE[1]/text()', con))[1]::text::varchar(1)			as TYPE
	from
	(
		select z, unnest(xpath('/ZL/CONTACTS', z)) as con
		from
		(
			select unnest(xpath('/DN_LIST/ZL', x)) as z
		) t1
	) t2;
	
	Drop Table If Exists TmpDN;
	Create Temp Table TmpDN
	As
	select
		gen_random_uuid() 																	as DNID
	,	(xpath('//ID_PAC[1]/text()', z))[1]::varchar(36)									as ZLID
	,	(xpath('//DN_ID[1]/text()', dn))[1]::varchar(36)									as DN_ID
	,	to_timestamp((xpath('//DATE_BEGIN[1]/text()', dn))[1]::varchar(10), 'YYYY-MM-DD')	as DATE_BEGIN
	,	to_timestamp((xpath('//DATE_OUT[1]/text()', dn))[1]::varchar(10), 'YYYY-MM-DD')		as DATE_OUT
	,	(xpath('//DN_PR[1]/text()', dn))[1]::text::int										as DN_PR
	,	to_timestamp((xpath('//DATE_GR[1]/text()', dn))[1]::varchar(10), 'YYYY-MM-DD')		as DATE_GR
	,	(xpath('//GROUP_DN[1]/text()', dn))[1]::text::int									as GROUP_DN
	,	(xpath('//DS[1]/text()', dn))[1]::varchar(10)										as DS
	,	(xpath('//MO[1]/text()', dn))[1]::varchar(6)										as MO
	,	(xpath('//CODE_MR[1]/text()', dn))[1]::varchar(14)									as SNILS_MR	
	,	(xpath('//SPEC[1]/text()', dn))[1]::text::int										as SPEC
	,	to_timestamp((xpath('//DATE_P[1]/text()', dn))[1]::varchar(10), 'YYYY-MM-DD')		as DATE_P
	,	(xpath('//MkbId[1]/text()', dn))[1]::text::uuid										as MkbId --Заглушка
	,   (xpath('//DuplicateId[1]/text()',dn))[1]::text::uuid 								as DuplicateId --Заглушка
	from
	(
		select z, unnest(xpath('/ZL/DN', z)) as dn
		from
		(
			select unnest(xpath('/DN_LIST/ZL', x)) as z
		) t1
	) t2;
	
	update TmpDN dn
	set MkbId = mkb.MkbID
	from ISZL.T_MKB mkb 
	where mkb.MCBCode = dn.DS
	and mkb.EndCode is null;
	
	Drop Table If Exists TmpPRESENCES;
	Create Temp Table TmpPRESENCES
	As
	select
		gen_random_uuid() 																	as PresenceIdGUID
	,	(xpath('//ID_PAC[1]/text()', z))[1]::varchar(36)									as ID_PAC
	,	(xpath('//DN_ID[1]/text()', dn))[1]::varchar(36)									as DN_ID
	,	(xpath('//CODE_MR[1]/text()', dn))[1]::varchar(14)									as SNILS_MR	
	,	(xpath('//PRESENCE_ID[1]/text()', pr))[1]::varchar(36)								as PresenceId
	,	to_timestamp((xpath('//DATE_PLAN[1]/text()', pr))[1]::varchar(10), 'YYYY-MM-DD')	as DATE_PLAN	
	,	(xpath('//PLACE[1]/text()', pr))[1]::text::int										as PLACE
	,	(xpath('//MO_P[1]/text()', pr))[1]::varchar(6)										as MO_P
	,	(xpath('//PresenceDuplicateId[1]/text()', pr))[1]::text::uuid						as PresenceDuplicateId --заглушка
	from
	(
		select z, dn, unnest(xpath('/DN/PRESENCES', dn)) as pr
		from
		(
			select z, unnest(xpath('/ZL/DN', z)) as dn
			from
			(
				select unnest(xpath('/DN_LIST/ZL', x)) as z
			) t1
		) t2
	) t3;
	
	Drop Table If Exists TmpErrors;
	Create Temp Table TmpErrors
	as
	select 0::int as Code, ('Код мкб (' || dn.DS || ') не найден в справочнике МКБ (DN_ID - ' || DN_ID || ')')::varchar(250) as Comment
	from TmpDN dn
	where dn.MkbId is null;

	insert into TmpErrors(Code, Comment)
	select 1, 'Код мкб (' || dn.DS || ') не принадлежит классификатору перечня заболеваний, при наличии которых проводится ДН'
	from TmpDN dn
	where dn.DS like 'U%';
	
	insert into TmpErrors(Code, Comment)
	select 2, 'Дата посещения (' || to_char(pres.DATE_PLAN, 'YYYY-MM-DD' ) || ') не может быть раньше, чем дата постановки на ДН (' || to_char( dn.DATE_BEGIN, 'YYYY-MM-DD') || '); DN_ID = ' || dn.DN_ID
	from TmpPRESENCES pres
	join TmpDN dn on dn.DN_ID = pres.DN_ID
	where pres.DATE_PLAN < dn.DATE_BEGIN;
	
	-- Костыль для Костромы
	
	if exists (select * from information_schema.columns c where c.column_name = 'xdn') then
		insert into TmpErrors(Code, Comment)
		select 0, 'Врач, указанный как лечащий, не может вести ДН по его специальности. ID_PAC(' || dn.ZLID ||')'
		from  TmpDN dn
		join  ISZL.t_v021_speciality s on s.id = dn.spec
		where s.xdn = '0';
	end if;

	insert into TmpErrors(Code, Comment)
	select 3, 'Некорректная дата у явки с идентификатором (' || PresenceId || '). ID_PAC('|| ID_PAC ||')'
	from TmpPRESENCES pres
	where length(EXTRACT(year from pres.DATE_PLAN)::text) <> 4;
	
	if not exists(select * from TmpErrors)
	then
	--Идентификация ЗЛ
		update TmpZL t1
		set InsuredId = 
		(
	  		select t2.InsuredID 
			from ISZL.T_INSURED t2
	  		inner join
				ISZL.T_INSURED_POLICY_RECORD ipr
				on t2.InsuredID = ipr.Insured
				and ipr.DateEnd is null
	  		inner join
				ISZL.T_POLICY iszlpolicy 
				on iszlpolicy.PolicyID = ipr.Policy
	  		where t2.LastName = t1.FAM
			and t2.FirstName = t1.IM
			and t2.Birthday = t1.DR
			and iszlpolicy.Number = t1.NPOLIS   
			limit 1
	 	)
		where t1.InsuredId is null;
	
		--step 2 (ис ЗЛ фамилия, имя, отчество, др)
		update TmpZL t1
		set InsuredId = (select t2.InsuredID from ISZL.T_INSURED t2 where t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Birthday = t1.DR limit 1)
		where t1.InsuredId is null;
	
		--step 3 (фамилия, имя, номер полиса) из коллекции полисов
		update TmpZL t1
		set InsuredId =
		(
	  		select t2.InsuredID 
			from ISZL.T_INSURED t2
	  		inner join
				ISZL.T_INSURED_POLICY_RECORD ipr 
				on t2.InsuredID = ipr.Insured and ipr.DateEnd is null
	  		inner join
				ISZL.T_POLICY iszlpolicy
				on iszlpolicy.PolicyID = ipr.Policy
	  		where t2.LastName = t1.FAM 
			and t2.FirstName = t1.IM 
			and iszlpolicy.Number = t1.NPOLIS
			limit 1
	 	)
		where t1.InsuredId is null;

		--step 4 (фамилия, др, номер полиса) из коллекции полисов
		update TmpZL t1
		set InsuredId =
		(
	  		select t2.InsuredID 
			from ISZL.T_INSURED t2
	  		inner join
				ISZL.T_INSURED_POLICY_RECORD ipr
				on t2.InsuredID = ipr.Insured
				and ipr.DateEnd is null
	  		inner join
				ISZL.T_POLICY iszlpolicy
				on iszlpolicy.policyid = ipr."policy"
	  		where t2.LastName = t1.FAM
			and t2.Birthday = t1.DR
			and iszlpolicy."number" = t1.NPOLIS
			limit 1
	 	)
		where t1.InsuredId is null;	

		--step 5 (ENP) из ЗЛ
		update TmpZL t1
		set InsuredId = (select t2.InsuredID from ISZL.T_INSURED t2 where t2.ENP = t1.ENP limit 1)
		where t1.InsuredId is null;
	
		--step 6 (NPOLIS, ID_PAC) из коллекции полисов
		update TmpZL t1
		set InsuredId = 
		(
			select t2.InsuredID
			from ISZL.T_INSURED t2
			inner join
				ISZL.T_INSURED_POLICY_RECORD ipr
				on t2.InsuredID = ipr.Insured
				and ipr.DateEnd is null
			inner join
				ISZL.T_POLICY iszlpolicy 
				on iszlpolicy.PolicyID = ipr."policy"
			where iszlpolicy."number" = t1.NPOLIS
			and t2.ID_PAC = t1.ID_PAC
			limit 1
		)
		where t1.InsuredId is null;

		--step 7 (ID_PAC) из ЗЛ
		update TmpZL t1
		set InsuredId = (select t2.InsuredID from ISZL.T_INSURED t2 where t2.ID_PAC = t1.ID_PAC limit 1)
		where t1.InsuredId is null;

		insert into TmpErrors(Code, Comment)
		select 1, 'Застрахованный не найден в БД. NPOLIS(' || zl.NPOLIS || ')'
		from TmpZL zl
		where zl.InsuredId is null;		
	end if;

	--Выявление дублей плановых записей
	update TmpPRESENCES p
	set PresenceDuplicateId = (select pr.dnpresenceid from iszl.t_dn_presence pr
								inner join iszl.t_dn_item di on di.dnitemid = pr.dnitem and di.insured = zl.InsuredId
								where (di.ds_disp = mkb.mkbid and pr.date_plan = p.DATE_PLAN and di.insured = zl.InsuredId)
								limit 1)
	from TmpDN dn
	inner join iszl.t_mkb mkb on mkb.mcbcode = dn.ds and mkb.endcode is null
	inner join TmpZL zl on zl.ID_PAC = dn.ZLID
	where dn.ZLID = p.ID_PAC;
	
	--Выявление дублей записей о состоянии на ДН
	update TmpDN dn 
	set DuplicateId = (
		select di.dnitemid
		from TmpZL z
		join TmpDN d on z.ID_PAC::uuid = dn.ZLID::uuid
		join iszl.t_dn_item di on z.InsuredId::uuid = di.insured
		join iszl.t_mkb mkb on mkb.mcbcode = d.ds
		where di.insured is not null and z.InsuredId is not null
			and z.ID_PAC = zl.ID_PAC
			and d.DNID = dn.DNID
			and d.MO = di.mo_in
			and mkb.mkbid = di.ds_disp
			and ((di.date_out is null and d.DATE_OUT is null)
				or(di.date_out is not null and d.DATE_OUT is not null
				and di.date_out = d.DATE_OUT))
				limit 1)
	from TmpZL zl
	left join TmpDN dn1 on zl.ID_PAC = dn1.ZLID
	where zl.InsuredId is not null;
	
	--Проверка на дубли presens в файле
	create temp table TmpPRESENCES_Dubl as(
	 with cte as (
		select p.PresenceId, p.DATE_PLAN, p.MO_P, p.DN_ID, p.SNILS_MR , ROW_NUMBER() OVER 
	  (PARTITION BY p.PresenceId, p.DATE_PLAN, p.MO_P, p.DN_ID, p.SNILS_MR ORDER BY p.PresenceId) as R from TmpPRESENCES p )
	  select * from cte where cte.R > 1); 
	  
	insert into TmpErrors(Code, Comment)
	select 2, 'Запись имеет дубликат в файле. NPOLIS(' || z.NPOLIS::text ||')'
	from TmpPRESENCES t1
	join TmpDN d on d.DN_ID = t1.DN_ID
	join TmpZL z on z.ID_PAC = d.ZLID
	join TmpPRESENCES_Dubl t2 on t2.PresenceId = t1.PresenceId;
	
	--файловая загрузка
	if ("FileID" is not null)and(not exists(select * from TmpErrors)) then
		_DnFileId := (select dd.FileID from ISZL.T_DN_FILE dd where dd.FILENAME = (select z.FILENAME from TmpZGLV z limit 1) limit 1);
		if _DnFileId is not null then
			---file delete
			delete from ISZL.T_DN_PRESENCE pr
			where exists(select* from ISZL.T_DN_ITEM di where di.DnItemId = pr.DnItem and di.DnFile = _DnFileId);

			delete from ISZL.T_DN_ITEM where DnFile = _DnFileId;

			delete from ISZL.T_DN_FILE where FileId = _DnFileId;

			delete from VClib.T_FILE where FileId = _DnFileId;
		end if;
		-- create file
		
		insert into vclib.t_file (failed, "name", createdatetime, filenameform, savefilelocation, useole, type_)
		select z.FileId, z.FILENAME, now()::timestamp, 1, 1, '0'::boolean, (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DN_FILE') 
		from TmpZGLV z;
	
		insert into ISZL.T_DN_FILE(FileID, VERSION, CODE_MO, ID, ReceivedDate, ReceivedTime, DATA, FILENAME)
		select z.FileId, z.VERSION, z.MCOD, null::varchar(36), z.ReceivedDate, z.ReceivedTime, z.DATA, z.FILENAME
		from TmpZGLV z;
	
		insert into ISZL.T_DN_ITEM(DnItemID, DATE_IN, DATE_OUT
		, RESULT_OUT, DN_ID, DS_DISP, SNILS_VR
		, V021Speciality, Insured, ID_PAC, DnFile, MO_IN
		,DATE_GR
		,GROUP_DN
		,DATE_P
		)
		select dn.DNID, dn.DATE_BEGIN, dn.DATE_OUT
		,case dn.DN_PR when 4 then 1 when 6 then 8 else null end, dn.DN_ID, mkb.MkbID, dn.SNILS_MR
		, dn.SPEC, zl.InsuredId, dn.ZLID, "FileID", mo.MCOD
		,dn.DATE_GR
		,dn.GROUP_DN
		,dn.DATE_P
		from TmpDN dn
		inner join 
			TmpZL zl
			on zl.ID_PAC = dn.ZLID
		inner join
			ISZL.T_MKB mkb
			on mkb.MCBCode = dn.DS
			and mkb.EndCode is null
		left join 
			ISZL.T_F003 mo 
			on mo.MCOD = dn.MO
		where dn.DuplicateId is null;
	
		update ISZL.T_DN_ITEM di
		set RESULT_OUT = case dn.DN_PR when 4 then 1 when 6 then 8 else null end
			,DN_ID = dn.DN_ID
			,SNILS_VR = dn.SNILS_MR
			,V021Speciality = dn.SPEC
			,ID_PAC = dn.ZLID
			,DnFile = "FileID"
			,DATE_GR = dn.DATE_GR
			,GROUP_DN = dn.GROUP_DN
			,DATE_P = dn.DATE_P
			,DATE_OUT = dn.DATE_OUT
		from TmpDN dn
		join TmpZL zl on zl.ID_PAC = dn.ZLID
		join ISZL.T_MKB mkb on mkb.MCBCode = dn.DS and mkb.EndCode is null
		left join ISZL.T_F003 mo on mo.MCOD = dn.MO
		where di.DnItemID = dn.DuplicateId and dn.DuplicateId is not null;
	
		insert into ISZL.T_DN_PRESENCE(DnPresenceID, DATE_PLAN, PLACE, MO_P, DnItem)
		select pres.PresenceIdGUID, pres.DATE_PLAN, pres.PLACE, lpumain.MCOD, coalesce(dn.DuplicateId, dn.DNID)
		from TmpPRESENCES pres
		inner join TmpDN dn on dn.DN_ID = pres.DN_ID
		left join ISZL.T_F003 lpumain on lpumain.MCOD = pres.MO_P
		where pres.PresenceDuplicateId is null;
		
		update iszl.t_dn_presence dp 
		set place = pres.place,
			mo_p =  lpumain.mcod
		from TmpPRESENCES pres
		inner join TmpDN dn on dn.DN_ID = pres.DN_ID
		left join iszl.t_f003 lpumain on lpumain.mcod = pres.MO_P
		where pres.PresenceDuplicateId is not null and dp.dnpresenceid = pres.PresenceDuplicateId;
		 
		insert into iszl.t_insured_contact(InsuredContactID,Insured,Contact,ContactType) 
		select gen_random_uuid(), zl.InsuredId, cont.CONTACT, t1.ContactTypeID
		from TmpCONTACTS cont
		inner join TmpZL zl on zl.ID_PAC = cont.ID_PAC
		inner join ISZL.T_CONTACT_TYPE t1 on t1."name" = cont.TYPE
		where not exists
		(
			select * from ISZL.T_INSURED_CONTACT insc where insc.contact = cont.CONTACT and insc.Insured = zl.InsuredId and insc.ContactType = t1.ContactTypeID
		);
	end if;

	--если прислали по раббиту
	if (not exists(select * from TmpErrors) and ("FileID" is null))  then
	
		insert into iszl.t_dn_item (dnitemid,date_in,date_out,result_out,dn_id,
										ds_disp,snils_vr,v021speciality,insured,id_pac,
										mo_in,date_gr,group_dn,date_p)
		select dn.DNID, dn.DATE_BEGIN, dn.DATE_OUT
		,case dn.DN_PR when 4 then 1 when 6 then 8 else null end,
		dn.DN_ID, mkb.MkbID, dn.SNILS_MR,
		dn.SPEC, zl.InsuredId, dn.ZLID,
		mo.MCOD, dn.DATE_GR, dn.GROUP_DN, dn.DATE_P
		from TmpDN dn
		inner join TmpZL zl on zl.ID_PAC = dn.ZLID
		inner join iszl.t_mkb mkb on mkb.mcbcode = dn.ds and mkb.endcode is null
		left join iszl.t_f003 mo on mo.mcod = dn.mo
		where dn.DuplicateId is null;
		
		update ISZL.T_DN_ITEM di
		set RESULT_OUT = case dn.DN_PR when 4 then 1 when 6 then 8 else null end
			,DN_ID = dn.DN_ID
			,SNILS_VR = dn.SNILS_MR
			,V021Speciality = dn.SPEC
			,ID_PAC = dn.ZLID
			,DATE_GR = dn.DATE_GR
			,GROUP_DN = dn.GROUP_DN
			,DATE_P = dn.DATE_P
			,DATE_OUT = dn.DATE_OUT
		from TmpDN dn
		join TmpZL zl on zl.ID_PAC = dn.ZLID
		join ISZL.T_MKB mkb on mkb.MCBCode = dn.DS and mkb.EndCode is null
		left join ISZL.T_F003 mo on mo.MCOD = dn.MO
		where di.DnItemID = dn.DuplicateId and dn.DuplicateId is not null;
	
		insert into ISZL.T_DN_PRESENCE(DnPresenceID, DATE_PLAN, PLACE, MO_P, DnItem)
		select pres.PresenceIdGUID, pres.DATE_PLAN, pres.PLACE, lpumain.MCOD, coalesce(dn.DuplicateId, dn.DNID)
		from TmpPRESENCES pres
		inner join TmpDN dn on dn.DN_ID = pres.DN_ID
		left join ISZL.T_F003 lpumain on lpumain.MCOD = pres.MO_P
		where pres.PresenceDuplicateId is null;
	
		update iszl.t_dn_presence dp 
		set place = pres.place,
			mo_p =  lpumain.mcod
		from TmpPRESENCES pres
		inner join TmpDN dn on dn.DN_ID = pres.DN_ID
		left join iszl.t_f003 lpumain on lpumain.mcod = pres.MO_P
		where pres.PresenceDuplicateId is not null and dp.dnpresenceid = pres.PresenceDuplicateId;
		 
		insert into iszl.t_insured_contact(InsuredContactID,Insured,Contact,ContactType) 
		select gen_random_uuid(), zl.InsuredId, cont.CONTACT, t1.ContactTypeID
		from TmpCONTACTS cont
		inner join TmpZL zl on zl.ID_PAC = cont.ID_PAC
		inner join ISZL.T_CONTACT_TYPE t1 on t1."name" = cont.TYPE
		where not exists
		(
			select * from ISZL.T_INSURED_CONTACT insc where insc.contact = cont.CONTACT and insc.Insured = zl.InsuredId and insc.ContactType = t1.ContactTypeID
		);	
	end if;
return query
select case when exists(select* from TmpErrors) then '0'::boolean else '1'::boolean end
	, case when exists(select* from TmpErrors) then '<?xml version="1.0" encoding="Windows-1251"?>
	' || 
	(
		xmlelement
		(
			name "ROOT"
		,	(
				select
				xmlagg
				(
					xmlelement
					(
						name "ERR"
					,	xmlforest
						(
							te.CODE AS "CODE"
						,	te.COMMENT AS "COMMENT"
						)
					)
				)
				FROM TmpErrors te
			)
		)
	)::text end;

	DROP TABLE if exists TmpZGLV;
	DROP TABLE if exists TmpZL;
	DROP TABLE if exists TmpCONTACTS;
	DROP TABLE if exists TmpDN;
	DROP TABLE if exists TmpPRESENCES;
	DROP TABLE if exists TmpErrors;
end;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;