DROP function if exists sp_RecomendationsLoad_folder;
drop procedure if exists sp_RecomendationsLoad_folder;

CREATE procedure sp_RecomendationsLoad_folder
(
	x xml
,	"FileID" uuid default null::uuid
,	"Worker" uuid default null::uuid
,	inout "Res" boolean default null::boolean
,	inout "Err" xml default null::xml
)
as $BODY$
declare
	_Type varchar(6);
	_MO varchar(6);
begin
	Drop Table If Exists TmpZGLV;
	Create Temp Table TmpZGLV
	As
	select
        (xpath('//MO[1]/text()', z))[1]::varchar(6)	as MO,
		(xpath('//TYPE[1]/text()', z))[1]::varchar(6)	as Type
		
	from
	(
		select unnest(xpath('/RECOMENDATIONS/HEADER', x)) as z
	) t1;
	
	select into 
		_Type, _MO
		t.type, t.MO
	from TmpZGLV t
	limit 1;

	Drop Table If Exists TmpZAP;
	Create Temp Table TmpZAP
	As
	select
		_MO as MO
	,	_Type as type
	,	to_timestamp((xpath('//DATE_K[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD') as Date_K
	,	(xpath('//CODE_NMIC[1]/text()', z))[1]::varchar(2)                           as Code_NMIC
	,	(xpath('//THEME_C[1]/text()', z))[1]::text::int                           	 as Theme_c
	,	(xpath('//TYPE_C[1]/text()', z))[1]::text::int                           	 as Type_c
	,	(xpath('//CODE_TMK[1]/text()', z))[1]::varchar(100)                          as Code_TMK
	,	(xpath('//CODE_PAT[1]/text()', z))[1]::varchar(100)                          as Code_Pat
	,	to_timestamp((xpath('//DATE_Z[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD') as Date_Z
	,	(xpath('//TIME_Z[1]/text()', z))[1]::varchar(5)                           	 as Time_Z
	,	(xpath('//REC[1]/text()', z))[1]::varchar(4000)                           	 as Rec
	,	(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('//SNILS[1]/text()', z))[1]::varchar(14)                           		 as SS
	,	(xpath('//DOCTYPE[1]/text()', z))[1]::text::int                           	 as Doctype
	,	(xpath('//DOCSER[1]/text()', z))[1]::varchar(20)                           	 as Docser
	,	(xpath('//DOCNUM[1]/text()', z))[1]::varchar(20)                           	 as Docnum
	,	(xpath('//ENP[1]/text()', z))[1]::varchar(16)                          		 as ENP
	,	(xpath('//TPOLIS[1]/text()', z))[1]::text::int                          		 as TPOL
	,	(xpath('//SPOLIS[1]/text()', z))[1]::varchar(20)                          	 as Spolis
	,	(xpath('//NPOLIS[1]/text()', z))[1]::varchar(20)                          	 as Npolis
	,	to_timestamp((xpath('//DATA_IN[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD') 	 as Din
	,	to_timestamp((xpath('//DATA_OUT[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD') 	 as Dout
	,	(xpath('//USL_OK[1]/text()', z))[1]::text::int                          	 as USL_OK
	,	(xpath('//DS1[1]/text()', z))[1]::varchar(10)                          		 as DS1
	,	to_timestamp((xpath('//DATE_P[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD') as Date_p
	,	to_timestamp((xpath('//DATE_V[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD') as Date_v
	,	(xpath('//insured[1]/text()', z))[1]::text::uuid                          	 as insured --Заглушка
	,	(xpath('//recomendation[1]/text()', z))[1]::text::uuid                       as recomendation --Заглушка
	from
	(
		select unnest(xpath('/RECOMENDATIONS/BODY', x)) as z
	) t1;
	
	--Ищем пациентов
	--step (ENP)
	update TmpZAP t1
	set insured = (select ins.InsuredID from ISZL.T_INSURED ins where ins.ENP = t1.ENP limit 1)
	where t1.insured is null;

	--step (фамилия, имя, отчество, дата рождения)
	update TmpZAP t1
	set insured = (select ins.InsuredID from ISZL.T_INSURED ins where ins.LastName = t1.Fam and ins.FirstName = t1.Im and ins.Patronymic = t1.Ot and ins.Birthday::date = t1.Dr limit 1)
	where t1.insured is null;

	--step (фамилия, имя, дата рождения)
	update TmpZAP t1
	set insured = (select ins.InsuredID from ISZL.T_INSURED ins where ins.LastName = t1.Fam and ins.FirstName = t1.Im and ins.Birthday::date = t1.Dr limit 1)
	where t1.insured is null;

	--step (фамилия, имя, номер+серия полиса)
	update TmpZAP t1
	set insured = 
	(
		select ins.InsuredID from ISZL.T_INSURED ins
		left join lateral
		(
			select record.Policy from ISZL.T_INSURED_POLICY_RECORD record 
			where ins.InsuredID = record.Insured and (record.DateBegin::date <= t1.Date_K and (record.DateEnd is null or record.DateEnd::date >= t1.Date_K))
			limit 1
		) record1 on true
		left join 
			ISZL.T_POLICY iszlpolicy 
			on iszlpolicy.PolicyID = record1.Policy
		where ins.LastName = t1.Fam 
		and ins.FirstName = t1.Im 
		and iszlpolicy.Number = t1.Npolis 
		and (iszlpolicy.Series = t1.Spolis or (iszlpolicy.Series is null and t1.Spolis is null))
		limit 1
	)
	where t1.insured is null;

	--step (фамилия, дата рождения, номер+серия полиса)
	update TmpZAP t1
	set insured = 
	(
		select ins.InsuredID from ISZL.T_INSURED ins
		left join lateral
		(
			select record.Policy from ISZL.T_INSURED_POLICY_RECORD record 
			where ins.InsuredID = record.Insured and (record.DateBegin::date <= t1.Date_K and (record.DateEnd is null or record.DateEnd::date >= t1.Date_K))
			limit 1
		) record1 on true
		left join 
			ISZL.T_POLICY iszlpolicy 
			on iszlpolicy.PolicyID = record1.Policy
		where ins.LastName = t1.Fam 
		and ins.Birthday::date = t1.Dr::date 
		and iszlpolicy.Number = t1.Npolis 
		and (iszlpolicy.Series = t1.Spolis or (iszlpolicy.Series is null and t1.Spolis is null))
		limit 1
	)
	where t1.insured is null;

	update TmpZAP z
	set recomendation = (select r.ID from iszl.T_RECOMMENDATION r where r.Insured = z.insured and r.Code_TMK = z.Code_TMK and r.REFERRAL_MO = z.MO limit 1);

	Drop Table If Exists TmpErrors;
	Create Temp Table TmpErrors
	as
	select 1::int as Code, ('Застрахованный не найден в БД. ЕНП (' || isnull(z.ENP, '_ПУСТО_') || '). Code_TMK (' || isnull(z.Code_TMK, '_ПУСТО_') || ')')::varchar(250) as Comment
	from TmpZap z
	where z.insured is null
	union all 
	select 2, 'Дубль рекомендации. ЕНП (' || isnull(z.ENP, '_ПУСТО_') || '). Code_TMK (' || isnull(z.Code_TMK, '_ПУСТО_') || ')'
	from TmpZAP z
	where z.TYPE = 'Insert' and z.recomendation is not null
	union all
	select 3, 'Не найдена рекомендация. ЕНП (' || isnull(z.ENP, '_ПУСТО_') || '). Code_TMK (' || isnull(z.Code_TMK, '_ПУСТО_') || ')'
	from TmpZAP z
	where (z.TYPE = 'Update' or z.TYPE = 'Delete') and z.recomendation is null;
	
	--если нет ошибок
	if _TYPE = 'Insert' and not exists(select* from TmpErrors)
	then
		insert into ISZL.T_RECOMMENDATION(ID, DateRec, DateFact, Result, TypesOfConsultations, MedicalResearchCenters, ConsultationPurposes, Insured, MedicalRecommendation
		, NumberOfPatient, REFERRAL_MO, USL_OK, MKB, Diagnosis, Date_Z, Time_Z, Din, Dout, Date_p, Fam, Im, Ot, W, Dr, SS, Doctype, Docser, Docnum, ENP, TPOL
		, Spolis, Npolis, Code_TMK, FileId, DateTimeCreate)
		select gen_random_uuid(), z.Date_K, z.Date_v, null, z.Type_c, mrc.MedicalResearchCentersID, z.Theme_c, z.insured, z.Rec, z.Code_Pat, z.MO, z.USL_OK, z.DS1, null
		, z.Date_Z, z.Time_Z, z.Din, z.Dout, z.Date_p, z.Fam, z.Im, z.Ot, z.W, z.Dr, z.SS, z.Doctype, null, z.Docnum, z.ENP, z.TPOL
		, z.Spolis, z.Npolis, z.Code_TMK, "FileID", now()::timestamp
		from TmpZAP z
		left join iszl.T_MEDICAL_RESEARCH_CENTERS mrc on mrc.Code = z.Code_NMIC;
	end if;

	if _TYPE = 'Update' and not exists(select* from TmpErrors)
	then
		update ISZL.T_RECOMMENDATION r
		set DateRec = z.Date_K
      	, DateFact = z.Date_v
      	, Result = null
      	, TypesOfConsultations = z.Type_c
      	, MedicalResearchCenters = mrc.MedicalResearchCentersID
      	, ConsultationPurposes = z.Theme_c
      	, MedicalRecommendation = z.Rec
      	, NumberOfPatient = z.Code_Pat
      	, REFERRAL_MO = z.MO
      	, USL_OK = z.USL_OK
      	, MKB = z.DS1
      	, Diagnosis = null
      	, Date_Z = z.Date_Z
      	, Time_Z = z.Time_Z
      	, Din = z.Din
      	, Dout = z.Dout
      	, Date_p = z.Date_p
      	, Fam = z.Fam
      	, Im = z.Im
      	, Ot = z.Ot
      	, W = z.W
      	, Dr = z.Dr
      	, SS = z.SS
      	, Doctype = z.Doctype
      	, Docnum = z.Docnum
      	, TPOL = z.TPOL
      	, Spolis = z.Spolis
      	, Npolis = z.Npolis
      	, Npolis = z.Npolis
		from TmpZAP z 
		left join iszl.T_MEDICAL_RESEARCH_CENTERS mrc on mrc.Code = z.Code_NMIC
		where z.recomendation = r.ID;
	end if;

	if _TYPE = 'Delete' and not exists(select* from TmpErrors)
	then
		delete from ISZL.T_RECOMMENDATION r
		where exists(select* from TmpZAP z where z.recomendation = r.ID);
	end if;

   	create temp table temp_table(Res boolean,Err text);
   	insert into temp_table(Res,Err)
		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
							(
                                COALESCE(te.CODE, -1) AS "CODE"
                            ,    COALESCE(te.COMMENT, '_ПУСТО_') AS "COMMENT"
							)
						)
					)
					FROM TmpErrors te
				)
			)
		)::text end;				
	
   	select Res from temp_table into "Res";
	select Err from temp_table into "Err";

	drop table if exists temp_table;
	DROP TABLE if exists TmpZGLV;
	DROP TABLE if exists TmpZAP;
	DROP TABLE if exists TmpErrors;
end;
$BODY$ LANGUAGE plpgsql;