-- =============================================
-- Author:        Georgiy Dubovitskiy
-- Create date: 2019/10/28
-- Description:    Загрузка диспансерного наблюдения
-- =============================================
-- DROP FUNCTION sp_DnInsert(xml, uuid, uuid);
CREATE OR REPLACE FUNCTION sp_DnInsert
(
	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
		(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 ID_PAC
	,	(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 --Заглушка
	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	
	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%';

	--если отправляют файл, то нужно сделать идентификацию
	if "FileID" is not null
	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;

		if	exists(select* from TmpErrors)
		then
			return query
			select '0'::boolean
			, '<?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;					
		else
		
			---file delete
			_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
				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(FileID, 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.ID_PAC, "FileID", mo.MCOD
			,dn.DATE_GR
			,dn.GROUP_DN
			,dn.DATE_P
			from TmpDN dn
			inner join 
				TmpZL zl
				on zl.ID_PAC = dn.ID_PAC
			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;

			insert into ISZL.T_DN_PRESENCE(DnPresenceID, DATE_PLAN, PLACE, MO_P, DnItem)
			select pres.PresenceIdGUID, pres.DATE_PLAN, pres.PLACE, lpumain.MCOD, 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;

			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;
	else
		if	exists(select* from TmpErrors)
		then
			return query
			select '0'::boolean
			, '<?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;					
		else
			--прислали по раббиту
			update TmpZL zl
			set InsuredId = ins.InsuredID
			from ISZL.T_INSURED ins 
			where ins.ENP = zl.ENP;

			--step 0
			update TmpZL t1
			set InsuredId = 
			(
				select t2.InsuredID from ISZL.T_INSURED t2
				where t2.ID_PAC = t1.ID_PAC or t2.ENP = t1.ENP
				limit 1
			)
			where t1.InsuredId is null;

			--step 1
			update TmpZL t1
			set InsuredId = 
			(
				select t2.InsuredID 
				from ISZL.T_INSURED t2
				left join lateral
				(
					select ipr.InsuredPolicyRecordID, ipr.Policy
					from ISZL.T_INSURED_POLICY_RECORD ipr 
					where t2.InsuredID = ipr.Insured 
					and (ipr.DateStop is null or ipr.DateStop >= _DATA)
					order by ipr.DateBegin desc
					limit 1
				) record on true
	  			inner join 
					ISZL.T_POLICY iszlpolicy 
					on iszlpolicy.PolicyID = record.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
				left join lateral
				(
					select ipr.InsuredPolicyRecordID, ipr.Policy
					from ISZL.T_INSURED_POLICY_RECORD ipr 
					where t2.InsuredID = ipr.Insured 
					 (ipr.DateStop is null or ipr.DateStop >= _DATA)
					order by ipr.DateBegin desc
					limit 1
				) record on true
				inner join 
					ISZL.T_POLICY iszlpolicy 
					on iszlpolicy.PolicyID = record.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
				left join lateral
				(
					select ipr.InsuredPolicyRecordID, ipr.Policy
					from ISZL.T_INSURED_POLICY_RECORD ipr 
					where t2.InsuredID = ipr.Insured 
					and  (ipr.DateStop is null or ipr.DateStop >= _DATA)
					order by ipr.DateBegin desc
					limit 1
				) record on true
				inner join 
					ISZL.T_POLICY iszlpolicy 
					on iszlpolicy.PolicyID = record.Policy
	  			where t2.LastName = t1.FAM and t2.Birthday = t1.DR and iszlpolicy.Number = t1.NPOLIS
				limit 1
			)
			where t1.InsuredId is null;

	
			--удаление дублей
			delete from TmpPRESENCES del where del.PresenceIdGUID not in (select max(t1.PresenceIdGUID) from TmpPRESENCES t1 group by t1.date_plan, t1.snils_mr, t1.dn_id);
			--удаление дублей СЛУЧАЕВ
			delete from TmpDN del 
			where del.DNID not in 
			(
				select max(t1.DNID) 
				from TmpDN t1
				inner join
					TmpZL zl
					on t1.ID_PAC = zl.ID_PAC
				inner join
					ISZL.T_MKB mkb
					on mkb.MCBCode = t1.DS 
					and mkb.EndCode is null
				left join
					ISZL.T_F003 mo
					on mo.MCOD = t1.MO 
				group by zl.InsuredId, mkb.MkbID, mo.MCOD
			);
	
			--новый формат
			if not exists(select* from TmpPRESENCES pres where pres.PresenceId is null)
			then
				--все старые dn
				Drop Table If Exists TmpOLDDN;
				create Temp table TmpOLDDN
				as
				select 
					t1.*,
					t2.DnItemID
				from TmpDN t1
				inner join
					TmpZL t3
					on t1.ID_PAC = t3.ID_PAC
				inner join
					ISZL.T_MKB mkb
					on mkb.MCBCode = t1.DS
					and mkb.EndCode is null
				inner join
					ISZL.T_DN_ITEM t2
					on t2.Insured = t3.InsuredId::uuid
					and t2.DS_DISP = mkb.MkbID
					and t1.DN_ID = t2.DN_ID;
		
				--все новые dn
				Drop Table If Exists TmpNEWDN;
				create Temp table TmpNEWDN
				as
				select
					t1.*,
					t2.*
				from TmpDN t1
				inner join
					TmpZL t2
					on t1.ID_PAC = t2.ID_PAC
				where not exists(select * from TmpOLDDN t3 where t1.DNID = t3.DNID);

				--все пресенсы с присланными старыми ДН
				Drop Table If Exists TmpOLDDNPRESENCES;
				create Temp table TmpOLDDNPRESENCES
				as
				select
					t1.DATE_PLAN
				,	t1.ID_PAC
				,	t1.MO_P
				,	t1.PLACE
				,	t1.PresenceId
				,	t1.PresenceIdGUID
				,	t1.DN_ID
				,	t1.SNILS_MR
				,	t2.DnItemId
				from TmpPRESENCES t1
				inner join
					TmpOLDDN t2
					on t1.DN_ID = t2.DN_ID;
		
				--все новые ЯВКИ с присланными новыми ДН
				Drop Table If Exists TmpNEWDNPRESENCES;
				create Temp table TmpNEWDNPRESENCES
				as
				select
					t1.DATE_PLAN
				,	t1.ID_PAC
				,	t1.MO_P
				,	t1.PLACE
				,	t1.PresenceId
				,	t1.PresenceIdGUID
				,	t1.DN_ID
				,	t1.SNILS_MR
				,	t2.DNID
				from TmpPRESENCES t1
				inner join
					TmpNEWDN t2
					on t1.DN_ID = t2.DN_ID;
		
				--обновление снилсов врачей, у которых совпадает заплан. дата и не совпадает снилс врача
				update ISZL.T_DN_PRESENCE t1
				set SNILS_VR1 = pres.SNILS_MR
				from TmpOLDDNPRESENCES pres 
				where t1.DnItem = pres.DnItemID
				and t1.DATE_PLAN::timestamp = pres.DATE_PLAN::timestamp
				and t1.SNILS_VR1 <> pres.SNILS_MR;
		
				--вставка новых ЯВОК, в старых ДН
				insert into ISZL.T_DN_PRESENCE(DnPresenceID, DATE_PLAN, PLACE, MO_P, DnItem, SNILS_VR1, PRESENCE_ID)
				select pres.PresenceIdGUID, pres.DATE_PLAN::timestamp as DATE_PLAN, PLACE, MCOD, DnItemID, SNILS_MR, PresenceId
				from TmpOLDDNPRESENCES pres
				left join
					ISZL.T_F003 mo
					on pres.MO_P = mo.MCOD
				--не существуют такие, что совпадают заплан. дата и снилс врача, т.е. - новые
				where not exists
				(
					select * from ISZL.T_DN_PRESENCE t1 where t1.DnItem = pres.DnItemID and t1.DATE_PLAN::timestamp = pres.DATE_PLAN::timestamp and t1.SNILS_VR1 = pres.SNILS_MR
				);
		
				--обновление presence
				update ISZL.T_DN_PRESENCE t1
				set PRESENCE_ID = t2.PresenceId
				from TmpOLDDNPRESENCES t2 
				where t1.DnItem = t2.DnItemID
				and t1.PRESENCE_ID is null;
		
				--вставка новых ДН
				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::uuid, dn.ID_PAC
				, mo.MCOD			
				,dn.DATE_GR
				,dn.GROUP_DN
				,dn.DATE_P
				from TmpNEWDN dn
				inner join 
					TmpZL zl
					on zl.ID_PAC = dn.ID_PAC
				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;

				--вставка новых явок
				insert into ISZL.T_DN_PRESENCE(DnPresenceID, DATE_PLAN, PLACE, MO_P, DnItem, SNILS_VR1, PRESENCE_ID)
				select pres.PresenceIdGUID, pres.DATE_PLAN, pres.PLACE, lpumain.MCOD, pres.DNID, pres.SNILS_MR, pres.PresenceId
				from TmpNEWDNPRESENCES pres
				left join
					ISZL.T_F003 lpumain
					on lpumain.MCOD = pres.MO_P;
		
				drop table if exists TmpOLDDNPRESENCES;
				drop table if exists TmpNEWDNPRESENCES;
				drop table if exists TmpOLDDN;
				drop table if exists TmpNEWDN;
			else
				_filexists := 
				(
					select dnfile.FileID 
					from ISZL.T_DN_FILE dnfile
					join VCLib.T_FILE f on dnfile.FileID = f.FileID
					where f.Name = (select zglv.FILENAME from TmpZGLV zglv limit 1)
					limit 1
				);

				if _filexists is not null
				then
					delete from ISZL.T_DN_PRESENCE pres
					where exists
					(
						select* from ISZL.T_DN_ITEM dnitem
						where dnitem.DnFile = _filexists and dnitem.DnItemID = pres.DnItem
					);

					delete from ISZL.T_DN_ITEM dnitem where dnitem.DnFile = _filexists;

					delete from ISZL.T_DN_FILE dnfile where dnfile.FileID = _filexists;

					delete from VCLib.T_FILE vcfile where vcfile.FileID = _filexists;
				end if;
		
				"FileID" := gen_random_uuid();
			
				insert into VCLib.T_FILE(FileID, Name, FileNameForm, SaveFileLocation, UseOle, type_)
				select "FileID", zglv.FILENAME, 1, 1, '0', (select ent.EntityID from VCLib.T_ENTITIES ent where ent.EntName = 'DN_FILE')
				from TmpZGLV zglv;

				insert into ISZL.T_DN_FILE(FileID, FILENAME, ReceivedDate, ReceivedTime, DATA, MCOD)
				select "FileID", zglv.FILENAME, now()::timestamp, now()::timestamp, zglv.DATA, zglv.MCOD
				from TmpZGLV zglv;

				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.ID_PAC, "FileID", mo.MCOD
				,dn.DATE_GR
				,dn.GROUP_DN
				,dn.DATE_P
				from TmpDN dn
				inner join 
					TmpZL zl
					on zl.ID_PAC = dn.ID_PAC
				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;

				insert into ISZL.T_DN_PRESENCE(DnPresenceID, DATE_PLAN, PLACE, MO_P, SNILS_VR1, DnItem)
				select pres.PresenceIdGUID, pres.DATE_PLAN, pres.PLACE, lpumain.MCOD, pres.SNILS_MR, 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;

				--добавляем только таких, которые отличаются по типу и контакту в ЗЛ
				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;
		end if;
	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;