-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-11-29
-- Description: Список пациентов, направленных на идентификацию формате XML (DIRECT_IDENT)
-- =============================================
drop procedure if exists sp_DirectDispancerInfo;
CREATE PROCEDURE sp_DirectDispancerInfo("INFORDS" xml)
As 
$$
Begin

	-- Generate table #ZGLV
	Drop Table If Exists TmpZGLV;
	Create Temp Table TmpZGLV
	as 
	SELECT	(xpath('/ZGLV[1]/VERSION[1]/text()', t.x))[1]::varchar(5) as VERSION
	,	(xpath('/ZGLV[1]/FILENAME[1]/text()', t.x))[1]::varchar(26) as FILENAME
	,	(xpath('/ZGLV[1]/SMO[1]/text()', t.x))[1]::text::int as SMO
	from
	(
		select	unnest(xpath('/INFO_LIST/ZGLV', "INFORDS")) as x
	) t;
	
	-- Generate table #INFO
	Drop Table If Exists TmpINFO;
	Create Temp Table TmpINFO
	as 
	SELECT	(xpath('/INFO[1]/CODE_MO[1]/text()', t.x))[1]::text::int as CODE_MO
	,	(xpath('/INFO[1]/YEAR[1]/text()', t.x))[1]::text::int as YEAR
	,	(xpath('/INFO[1]/FAM[1]/text()', t.x))[1]::varchar(40) as FAM	
	,	(xpath('/INFO[1]/IM[1]/text()', t.x))[1]::varchar(40) as IM
	,	(xpath('/INFO[1]/OT[1]/text()', t.x))[1]::varchar(40) as OT
	,	(xpath('/INFO[1]/DR[1]/text()', t.x))[1]::text::timestamp as DR
	,	(xpath('/INFO[1]/ENP[1]/text()', t.x))[1]::varchar(16) as ENP
	,	(xpath('/INFO[1]/S_POL[1]/text()', t.x))[1]::varchar(10) as S_POL
	,	(xpath('/INFO[1]/N_POL[1]/text()', t.x))[1]::varchar(20) as N_POL
	,	(xpath('/INFO[1]/IDDT[1]/text()', t.x))[1]::varchar(3) as IDDT
	,	(xpath('/INFO[1]/F_INF[1]/text()', t.x))[1]::text::int as F_INF	
	,	(xpath('/INFO[1]/D_INF[1]/text()', t.x))[1]::text::timestamp as D_INF
	,	(xpath('/INFO[1]/N_INF[1]/text()', t.x))[1]::varchar(50) as N_INF
	,	(xpath('/INFO[1]/TIP_INFO[1]/text()', t.x))[1]::text::int as TIP_INFO	
	,	(xpath('/INFO[1]/RESULT[1]/text()', t.x))[1]::varchar(4000) as RESULT
	from
	(
		select	unnest(xpath('/INFO_LIST/INFO', "INFORDS")) as x
	) t;
			
	insert into AKUZ.T_RDS_DISP_INFO(RdsdispInfoID, ClinicExaminationItem, VERSION, DATA, FILENAME, SMO, CODE_MO, YEAR
									, FAM, IM, OT, DR, ENP, S_POL, N_POL, IDDT, F_INF, D_INF, N_INF, TIP_INFO, RESULT)
	select  uuid_generate_v4(), cei.ClinicExaminationItemID, zz.VERSION, now()::timestamp, zz.VERSION, zz.SMO, i.CODE_MO, i.YEAR
	, i.FAM, i.IM, i.OT, i.DR, i.ENP, i.S_POL, i.N_POL, i.IDDT, i.F_INF, i.D_INF, i.N_INF, i.TIP_INFO, i.RESULT
	from TmpINFO i
	inner join 
		AKUZ.T_PATIENT p 
		ON p.LastName = i.FAM 
		AND p.FirstName = i.IM
		AND p.FatherName = i.OT
		AND p.Birthday = i.DR
	inner join 
		AKUZ.T_CLINIC_EXAMINATION_ITEM cei 
		on cei.Patient = p.PatientID
	left join lateral
	(
		select z.VERSION, z.SMO from TmpZGLV z limit 1
	) zz on true
	where not exists(select * from AKUZ.T_RDS_DISP_INFO rdi  where rdi.ENP = i.ENP and rdi.YEAR = i.YEAR);

	DROP TABLE if exists TmpZGLV;
	DROP TABLE if exists TmpINFO;
End;
$$ Language plpgsql;			