-- DROP FUNCTION sp_RecomendationsLoad_folder(xml, uuid, uuid);
CREATE OR REPLACE FUNCTION sp_RecomendationsLoad_folder
(
	x xml
,	"FileID" uuid default null::uuid
,	"Worker" uuid default null::uuid	
)
RETURNS TABLE (Res boolean, Err text) AS
$BODY$
declare
	_Type varchar(6);
	_MO varchar(6);
begin
	Drop Table If Exists TmpZGLV;
	Create Temp Table TmpZGLV
	As
	select
		(xpath('//Type[1]/text()', z))[1]::varchar(6)	as Type
	,	(xpath('//LPU_T[1]/text()', z))[1]::varchar(6)	as MO
	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('//SS[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('//TPOL[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('//Din[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD') 	 as Din
	,	to_timestamp((xpath('//Dout[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, ('Застрахованный не найден в БД. ЕНП (' || z.ENP || '). Code_TMK (' || z.Code_TMK || ')')::varchar(250) as Comment
	from TmpZap z
	where z.insured is null
	union all 
	select 2, 'Дубль рекомендации. ЕНП (' || z.ENP || '). Code_TMK (' || z.Code_TMK || ')'
	from TmpZAP z
	where z.TYPE = 'Insert' and z.recomendation is not null
	union all
	select 3, 'Не найдена рекомендация. ЕНП (' || z.ENP || '). Code_TMK (' || 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 uuid_generate_v4(), 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, z.Docser, 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
      	, Docser = z.Docser
      	, 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;

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 TmpZAP;
	DROP TABLE if exists TmpErrors;
end;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;
