-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2019-12-19
-- Description: Загрузка счета
-- =============================================
DROP FUNCTION IF EXISTS sp_SchetLoad(uuid, xml);
DROP FUNCTION IF EXISTS sp_SchetLoad(uuid, text);
DROP FUNCTION IF EXISTS sp_SchetLoad(uuid, varchar);
CREATE OR REPLACE FUNCTION sp_SchetLoad
(
	"FileID" uuid default null::uuid,
	"XmlText" varchar default null
)
RETURNS void AS
$BODY$
declare
	_VERSION varchar(5);
	_DATA timestamp;
	_FILENAME varchar(26);
	_SchID uuid;
	_CODE_MO varchar(6);
	"Xml" xml;
begin
	"XmlText" := replace("XmlText", '<?xml version="1.0" encoding="Windows-1251" ?>', '');
	"XmlText" := replace("XmlText", '<?xml version="1.0" encoding="windows-1251"?>', '');
  "Xml" := "XmlText"::xml;
	
	select into _VERSION, _DATA, _FILENAME
		(xpath('//VERSION[1]/text()', z))[1]::text
	,	(xpath('//DATA[1]/text()', z))[1]::text::timestamp
	,	(xpath('//FILENAME[1]/text()', z))[1]::text
	from
	(
		select unnest(xpath('/ZL_LIST/ZGLV', "Xml")) as z
	) t1;


	Drop Table If Exists TmpSCHET;
	Create Temp Table TmpSCHET
	As
	select
		gen_random_uuid()																as SchetID
	,	_VERSION																		as VERSION
	,	_DATA																			as DATA
	,	_FILENAME																		as FILENAME
	,	(xpath('//YEAR[1]/text()', z))[1]::text::int     								as YEAR
	,	(xpath('//MONTH[1]/text()', z))[1]::text::int     								as MONTH
	,	(xpath('//NSCHET[1]/text()', z))[1]::varchar(15)								as NSCHET
	,	to_timestamp((xpath('//DSCHET[1]/text()', z))[1]::varchar(10), 'YYYY-MM-DD')	as DSCHET
	,	(xpath('//CODE_MO[1]/text()', z))[1]::varchar(6)                                as CODE_MO
	,	(xpath('//CODE[1]/text()', z))[1]::varchar(50)                                	as CODE
	,	(xpath('//PLAT[1]/text()', z))[1]::varchar(5)                           		as PLAT
	,	(xpath('//DISP[1]/text()', z))[1]::varchar(3)                           		as DISP
	from
	(
		select unnest(xpath('/ZL_LIST/SCHET', "Xml")) as z
	) t1;		
	
	for _SchID in 
		select t1.SchetID from TmpSCHET s 
		inner join
			ISZL.T_SCHET t1 
			on s.NSCHET = t1.NSCHET 
			and s.YEAR = t1.YEAR
			and s.MONTH = t1.MONTH
			and s.CODE_MO = t1.CODE_MO
			and s.CODE = t1.CODE
			AND coalesce(s._FILENAME, gen_random_uuid()::varchar) = coalesce(t1.FILENAME, gen_random_uuid()::varchar)
		order by t1.SchetID
	loop
		perform sp_SchetDelete(_SchID);
	end loop;
	
	--грузим SCHET
	insert into ISZL.T_SCHET(SchetID, VERSION, DATA, YEAR, MONTH, NSCHET, DSCHET, ReceivedDate, FILENAME
	, CODE_MO, PLAT, SchetFile, CODE, DISP)
	select s.SchetID, s.VERSION, s.DATA, s.YEAR, s.MONTH, s.NSCHET, s.DSCHET, now()::timestamp, s.FILENAME
	, s.CODE_MO, s.PLAT, "FileID", s.CODE, s.DISP
	from TmpSCHET s;
	
	Drop Table If Exists TmpSCHET_ZAP;
	Create Temp Table TmpSCHET_ZAP
	As
	select
		gen_random_uuid()											as SchetZapID
	,	(xpath('//N_ZAP[1]/text()', z))[1]::text::int           	as N_ZAP
	,	(xpath('//ID_PAC[1]/text()', pac))[1]::varchar(36)			as ID_PAC
	,	(xpath('//VPOLIS[1]/text()', pac))[1]::text::int			as VPOLIS
	,	(xpath('//SPOLIS[1]/text()', pac))[1]::varchar(10)			as SPOLIS
	,	(xpath('//NPOLIS[1]/text()', pac))[1]::varchar(20)			as NPOLIS
	,	(xpath('//ENP[1]/text()', pac))[1]::varchar(20)			as ENP
	,	(xpath('//SMO[1]/text()', pac))[1]::varchar(5)				as SMO
	from
	(
		select z, unnest(xpath('/ZAP/PACIENT', z)) as pac
		from
		(
			select unnest(xpath('/ZL_LIST/ZAP', "Xml")) as z
		) t1
	) t2;
	
	insert into ISZL.T_SCHET_ZAP(SchetZapID, SPOLIS, NPOLIS, FAM, IM, OT, DR, DOCSER, DOCNUM, SNILS, VPOLIS
	, W, DOCTYPE, Schet, InsuredPolicyRecord, F002smo, DOCDATE, DOCORG, ID_PAC, ENP, TEL, Insured, N_ZAP)
	select sz.SchetZapID, sz.SPOLIS, COALESCE(sz.NPOLIS, sz.ENP), null, null, null, null, null, null, null, sz.VPOLIS, null, null
	, (select SchetID from TmpSCHET limit 1), null, sz.SMO, null, null, sz.ID_PAC, null, null, null, sz.N_ZAP
	from TmpSCHET_ZAP sz;

	create index IX_TmpSCHET_ZAP_N_ZAP on TmpSCHET_ZAP(N_ZAP, ID_PAC, NPOLIS, SchetZapID);
	
	_CODE_MO := (select CODE_MO from TmpSchet limit 1);
	
	Drop Table If Exists TmpSSA;
	Create Temp Table TmpSSA
	As
	select
		gen_random_uuid()															as SchetSluchAccomplishedAID
	,	(xpath('//N_ZAP[1]/text()', z))[1]::text::int           					as N_ZAP
	,	(xpath('//IDCASE[1]/text()', zsl))[1]::varchar(11)							as IDCASE
	,	(xpath('//USL_OK[1]/text()', zsl))[1]::text::int           					as USL_OK
	,	_CODE_MO																	as MO
	,	to_timestamp((xpath('//DATE_Z_1[1]/text()', zsl))[1]::text, 'YYYY-MM-DD')	as DATE_Z_1
	,	to_timestamp((xpath('//DATE_Z_2[1]/text()', zsl))[1]::text, 'YYYY-MM-DD')	as DATE_Z_2
	,	(xpath('//P_OTK[1]/text()', zsl))[1]::text::bool           					as P_OTK
	,	(xpath('//RSLT_D[1]/text()', zsl))[1]::text::int           					as RSLT_D
	,	(xpath('//RSLT[1]/text()', zsl))[1]::text::int           					as RSLT
	,	(xpath('//ISHOD[1]/text()', zsl))[1]::text::int           					as ISHOD
	,	(xpath('//VIDPOM[1]/text()', zsl))[1]::text::int           					as VIDPOM
	,	(xpath('//FOR_POM[1]/text()', zsl))[1]::text::int           				as FOR_POM
	,	(xpath('//NPR_MO[1]/text()', zsl))[1]::varchar(6)							as NPR_MO
	,	to_timestamp((xpath('//NPR_DATE[1]/text()', zsl))[1]::text, 'YYYY-MM-DD')	as NPR_DATE
	,	(xpath('//NPR_N[1]/text()', zsl))[1]::varchar(10)							as NPR_N --этих сведений нету в формате по 79 приказу
	,	(xpath('//P_DISP2[1]/text()', zsl))[1]::text::int           				as P_DISP2 --этих сведений нету в формате по 79 приказу
	,	(xpath('//ID_PAC[1]/text()', pac))[1]::varchar(36)							as ID_PAC
	,	(xpath('//VPOLIS[1]/text()', pac))[1]::text::int							as VPOLIS
	,	(xpath('//SPOLIS[1]/text()', pac))[1]::varchar(10)							as SPOLIS
	,	(xpath('//NPOLIS[1]/text()', pac))[1]::varchar(20)							as NPOLIS
	,	(xpath('//SMO[1]/text()', pac))[1]::varchar(5)								as SMO
	from
	(
		select z, unnest(xpath('/ZAP/PACIENT', z)) as pac, unnest(xpath('/ZAP/Z_SL', z)) as zsl
		from
		(
			select unnest(xpath('/ZL_LIST/ZAP', "Xml")) as z
		) t1
	) t2;
	
	--create index IX_TmpSSA_N_ZAP on TmpSSA(N_ZAP, ID_PAC, NPOLIS);
	create index IX_TmpSSA_IDCASE on TmpSSA(IDCASE, N_ZAP);
	
	insert into ISZL.T_SCHET_SLUCH_ACCOMPLISHED(SchetSluchAccomplishedID, IDCASE, USL_OK, DATE_Z_1, DATE_Z_2
	, RSLT, ISHOD, SchetZap, P_OTK, RSLT_D, MO, VIDPOM, FOR_POM, NPR_MO, NPR_DATE, NPR_N, P_DISP2)
	select ssa.SchetSluchAccomplishedAID, ssa.IDCASE, ssa.USL_OK, ssa.DATE_Z_1, ssa.DATE_Z_2, ssa.RSLT, ssa.ISHOD
	, sz.SchetZapID, ssa.P_OTK, ssa.RSLT_D, ssa.MO, ssa.VIDPOM, ssa.FOR_POM, ssa.NPR_MO, ssa.NPR_DATE, ssa.NPR_N
	, ssa.P_DISP2
	from TmpSSA ssa
	inner join
		TmpSCHET_ZAP sz
		on sz.N_ZAP = ssa.N_ZAP; --and sz.ID_PAC = ssa.ID_PAC and sz.NPOLIS = ssa.NPOLIS;
		
	Drop Table If Exists TmpSS;
	Create Temp Table TmpSS
	As
	select
		gen_random_uuid()														as SchetSluchID
	,	(xpath('//N_ZAP[1]/text()', z))[1]::text::int           				as N_ZAP
	,	(xpath('//IDCASE[1]/text()', zsl))[1]::varchar(11)						as IDCASE	
	,	(xpath('//IDCASE[1]/text()', sl))[1]::text::bigint           				as IDCASE_OLDID
	,	(xpath('//SL_ID[1]/text()', sl))[1]::varchar(36)           				as SL_ID
	,	(xpath('//NHISTORY[1]/text()', sl))[1]::varchar(50)        				as NHISTORY
	,	to_timestamp((xpath('//DATE_1[1]/text()', sl))[1]::text, 'YYYY-MM-DD')	as DATE_1
	,	to_timestamp((xpath('//DATE_2[1]/text()', sl))[1]::text, 'YYYY-MM-DD')	as DATE_2
	,	(xpath('//DS1[1]/text()', sl))[1]::varchar(10)        					as DS1
	,	(xpath('//DS2[1]/text()', sl))[1]::varchar(10)        					as DS2
	,	_CODE_MO																as MO
	,	(xpath('//RSLT[1]/text()', sl))[1]::text::int           				as RSLT
	,	(xpath('//ISHOD[1]/text()', sl))[1]::text::int           				as ISHOD
	,	(xpath('//PROFIL_K[1]/text()', sl))[1]::text::int           			as PROFIL_K
	,	(xpath('//PR_D_N[1]/text()', sl))[1]::text::int           				as PR_D_N
	,	(xpath('//DN[1]/text()', sl))[1]::text::int           					as DN
	,	(xpath('//RSLT_D[1]/text()', sl))[1]::text::int           				as RSLT_D
	,	(xpath('//DISP[1]/text()', sl))[1]::text::boolean           			as DISP
	,	(xpath('//C_ZAB[1]/text()', sl))[1]::text::int           				as C_ZAB
	,	(xpath('//PRVS[1]/text()', sl))[1]::text::int           				as PRVS3
	,	(xpath('//DS_ONK[1]/text()', sl))[1]::text::boolean           			as DSONK
	,	(xpath('//CODE_MES1[1]/text()', sl))[1]::varchar(20)        			as CODE_MES1
	,	(xpath('//CODE_MES2[1]/text()', sl))[1]::varchar(20)        			as CODE_MES2
	,	(xpath('//VID_HMP[1]/text()', sl))[1]::varchar(16)        				as VID_HMP
	,	(xpath('//METOD_HMP[1]/text()', sl))[1]::text::int        				as METOD_HMP
	,	(xpath('//P_CEL[1]/text()', sl))[1]::varchar(3)        					as P_CEL
	from
	(
		select z, zsl, unnest(xpath('/Z_SL/SL', zsl)) as sl 
		from
		(
			select z, unnest(xpath('/ZAP/Z_SL', z)) as zsl
			from
			(
				select unnest(xpath('/ZL_LIST/ZAP', "Xml")) as z
			) t1
		) t2
	) t3;
	
	create index IX_TmpSS_N_ZAP on TmpSS(N_ZAP, IDCASE);
	create index IX_TmpSS_IDCASE on TmpSS(IDCASE, SL_ID, SchetSluchID);
	
	update TmpSS ss
	set DISP = case when _FILENAME like 'D%' then '1'::boolean else 0::boolean end
	where ss.DISP is null;
	
	insert into ISZL.T_SCHET_SLUCH(SchetSluchID, IDCASE, SL_ID, NHISTORY, DATE_1, DATE_2, DS1, DS2, LPU, RSLT
	, ISHOD, SchetZap, Mkb, SchetSluchAccomplished, PROFIL_K, DN, RSLT_D, DISP, C_ZAB, PRVS3, DSONK, CODE_MES1
	, CODE_MES2, VID_HMP, METOD_HMP, P_CEL)--, IDDOKT)
	select ss.SchetSluchID, ss.IDCASE_OLDID, ss.SL_ID, ss.NHISTORY, ss.DATE_1, ss.DATE_2, ss.DS1, ss.DS2, ss.MO
	, ss.RSLT, ss.ISHOD, sz.SchetZapID
	, (select m.MkbID from ISZL.T_MKB m where m.MCBCode = ss.DS1 limit 1)
	, ssa.SchetSluchAccomplishedAID, ss.PROFIL_K, coalesce(ss.DN, ss.PR_D_N), ss.RSLT_D, ss.DISP
	, ss.C_ZAB, ss.PRVS3, coalesce(ss.DSONK, '0'), ss.CODE_MES1, ss.CODE_MES2, ss.VID_HMP, ss.METOD_HMP
	, ss.P_CEL--, ss.IDDOKT--,[PRVS],[PRVS2]
	from TmpSS ss
	inner join 
		TmpSCHET_ZAP sz
		on sz.N_ZAP = ss.N_ZAP --and sz.ID_PAC = ss.ID_PAC and sz.NPOLIS = ss.NPOLIS
	inner join
		TmpSSA ssa
		on ssa.N_ZAP = ss.N_ZAP
		and ssa.IDCASE = ss.IDCASE;--and ssa.ID_PAC = ss.ID_PAC and ssa.NPOLIS = ss.NPOLIS
	
	Drop Table If Exists TmpSSONK;
	Create Temp Table TmpSSONK
	As
	select
		gen_random_uuid()											as SchetSluchOnkID
	,	(xpath('//IDCASE[1]/text()', zsl))[1]::varchar(11)			as IDCASE	
	,	(xpath('//IDCASE[1]/text()', sl))[1]::text::bigint           	as IDCASE_OLDID
	,	(xpath('//SL_ID[1]/text()', sl))[1]::varchar(36)           	as SL_ID
	,	(xpath('//NHISTORY[1]/text()', sl))[1]::varchar(50)        	as NHISTORY	
	,	(xpath('//WEI[1]/text()', onk_sl))[1]::text::decimal(4, 1)	as WEI
	,	(xpath('//K_FR[1]/text()', onk_sl))[1]::text::int          	as K_FR
	,	(xpath('//SOD[1]/text()', onk_sl))[1]::text::decimal(6, 2)	as SOD
	,	(xpath('//STAD[1]/text()', onk_sl))[1]::text::int          	as STAD
	,	(xpath('//DS1_T[1]/text()', onk_sl))[1]::text::int         	as DS1_T
	,	(xpath('//ONK_T[1]/text()', onk_sl))[1]::text::int          as ONK_T
	,	(xpath('//ONK_N[1]/text()', onk_sl))[1]::text::int          as ONK_N
	,	(xpath('//ONK_M[1]/text()', onk_sl))[1]::text::int          as ONK_M
	,	(xpath('//MTSTZ[1]/text()', onk_sl))[1]::text::boolean     	as MTSTZ
	,	(xpath('//HEI[1]/text()', onk_sl))[1]::text::int          	as HEI
	,	(xpath('//BSA[1]/text()', onk_sl))[1]::text::decimal(5, 2)	as BSA
	from
	(
		select zsl, sl, unnest(xpath('/SL/ONK_SL', sl)) as onk_sl 
		from
		(
			select zsl, unnest(xpath('/Z_SL/SL', zsl)) as sl
			from
			(
				select unnest(xpath('/ZL_LIST/ZAP/Z_SL', "Xml")) as zsl
			) t1
		) t2
	) t3;

	insert into ISZL.T_SCHET_SLUCH_ONK(SchetSluchOnkID, WEI, K_FR, SOD, Diagnosis, STAD, DS1_T, ONK_T
	, ONK_N, ONK_M, MTSTZ, SchetSluch, HEI, BSA)
	select ssonk.SchetSluchOnkID, ssonk.WEI, ssonk.K_FR, ssonk.SOD
	, (select m.Name from ISZL.T_MKB m where m.MCBCode = ss.DS1 limit 1)
	, ssonk.STAD, ssonk.DS1_T, ssonk.ONK_T, ssonk.ONK_N, ssonk.ONK_M, coalesce(ssonk.MTSTZ, '0')
	, ss.SchetSluchID, ssonk.HEI, ssonk.BSA
	from TmpSSONK ssonk
	inner join
		TmpSS ss 
		on ss.IDCASE = ssonk.IDCASE
		and ss.SL_ID = ssonk.SL_ID;
	
	Drop Table If Exists TmpB_DIAG;
	Create Temp Table TmpB_DIAG
	As
	select
		gen_random_uuid()															as SchetSluchOnkDiagID
	,	(xpath('//IDCASE[1]/text()', zsl))[1]::varchar(11)							as IDCASE	
	,	(xpath('//SL_ID[1]/text()', sl))[1]::varchar(36)           					as SL_ID	
	,	to_timestamp((xpath('//DIAG_DATE[1]/text()', b))[1]::text, 'YYYY-MM-DD')	as DIAG_DATE
	,	(xpath('//DIAG_TIP[1]/text()', b))[1]::text::int          					as DIAG_TIP
	,	(xpath('//DIAG_CODE[1]/text()', b))[1]::text::int          					as DIAG_CODE
	,	(xpath('//DIAG_RSLT[1]/text()', b))[1]::text::int          					as DIAG_RSLT
	,	(xpath('//REC_RSLT[1]/text()', b))[1]::text::boolean       					as REC_RSLT
	from
	(
		select zsl, sl, unnest(xpath('/SL/ONK_SL/B_DIAG', sl)) as b 
		from
		(
			select zsl, unnest(xpath('/Z_SL/SL', zsl)) as sl
			from
			(
				select unnest(xpath('/ZL_LIST/ZAP/Z_SL', "Xml")) as zsl
			) t1
		) t2
	) t3;
	
	insert into ISZL.T_SCHET_SLUCH_ONK_DIAG(SchetSluchOnkDiagID, DIAG_DATE, DIAG_TIP, DIAG_CODE, DIAG_RSLT
	, REC_RSLT, SchetSluchOnk)
	select bd.SchetSluchOnkDiagID, bd.DIAG_DATE, bd.DIAG_TIP, bd.DIAG_CODE, bd.DIAG_RSLT
	, coalesce(bd.REC_RSLT, '0'), ssonk.SchetSluchOnkID
	from TmpB_DIAG bd
	inner join
		TmpSS ss 
		on ss.IDCASE = bd.IDCASE
		and ss.SL_ID = bd.SL_ID --ss.N_ZAP=bd.N_ZAP and ss.ID_PAC = bd.ID_PAC and ss.NPOLIS = bd.NPOLIS and ss.NHISTORY = bd.NHISTORY 
	inner join
		ISZL.T_SCHET_SLUCH_ONK ssonk 
		on ssonk.SchetSluch = ss.SchetSluchID;
	
	Drop Table If Exists TmpB_PROT;
	Create Temp Table TmpB_PROT
	As
	select
		gen_random_uuid()														as SchetSluchOnkProtID
	,	(xpath('//IDCASE[1]/text()', zsl))[1]::varchar(11)						as IDCASE	
	,	(xpath('//SL_ID[1]/text()', sl))[1]::varchar(36)           				as SL_ID	
	,	to_timestamp((xpath('//D_PROT[1]/text()', b))[1]::text, 'YYYY-MM-DD')	as D_PROT
	,	(xpath('//PROT[1]/text()', b))[1]::text::int          					as PROT
	from
	(
		select zsl, sl, unnest(xpath('/SL/ONK_SL/B_PROT', sl)) as b 
		from
		(
			select zsl, unnest(xpath('/Z_SL/SL', zsl)) as sl
			from
			(
				select unnest(xpath('/ZL_LIST/ZAP/Z_SL', "Xml")) as zsl
			) t1
		) t2
	) t3;
	
	insert into ISZL.T_SCHET_SLUCH_ONK_PROT(SchetSluchOnkProtID, D_PROT, PROT, SchetSluchOnk)
	select bp.SchetSluchOnkProtID, bp.D_PROT, bp.PROT, ssonk.SchetSluchOnkID
	from TmpB_PROT bp
	inner join
		TmpSS ss
		on ss.IDCASE = bp.IDCASE
		and ss.SL_ID = bp.SL_ID--ss.N_ZAP=bp.N_ZAP and ss.ID_PAC = bp.ID_PAC and ss.NPOLIS = bp.NPOLIS and ss.NHISTORY = bp.NHISTORY 
	inner join
		ISZL.T_SCHET_SLUCH_ONK ssonk
		on ssonk.SchetSluch = ss.SchetSluchID;
	
	Drop Table If Exists TmpONK_USL;
	Create Temp Table TmpONK_USL
	As
	select
		gen_random_uuid()										as SchetUslOnkID
	,	(xpath('//IDCASE[1]/text()', zsl))[1]::varchar(11)		as IDCASE	
	,	(xpath('//SL_ID[1]/text()', sl))[1]::varchar(36)        as SL_ID
	,	(xpath('//USL_TIP[1]/text()', onkusl))[1]::text::int    as USL_TIP
	,	(xpath('//HIR_TIP[1]/text()', onkusl))[1]::text::int    as HIR_TIP
	,	(xpath('//LEK_TIP_L[1]/text()', onkusl))[1]::text::int  as LEK_TIP_L
	,	(xpath('//LEK_TIP_V[1]/text()', onkusl))[1]::text::int  as LEK_TIP_V
	,	(xpath('//LEK_PR', onkusl))[1]          				as LEK_PR
	,	(xpath('//PPTR[1]/text()', onkusl))[1]::text::boolean	as PPTR
	,	(xpath('//LUCH_TIP[1]/text()', onkusl))[1]::text::int	as LUCH_TIP
	from
	(
		select zsl, sl, unnest(xpath('/SL/ONK_SL/ONK_USL', sl)) as onkusl 
		from
		(
			select zsl, unnest(xpath('/Z_SL/SL', zsl)) as sl
			from
			(
				select unnest(xpath('/ZL_LIST/ZAP/Z_SL', "Xml")) as zsl
			) t1
		) t2
	) t3;
	
	insert into ISZL.T_SCHET_USL_ONK(SchetUslOnkID, USL_TIP, HIR_TIP, LEK_TIP_L, LEK_TIP_V, PPTR, SchetSluchOnk, LUCH_TIP)
	select ou.SchetUslOnkID, ou.USL_TIP, ou.HIR_TIP, ou.LEK_TIP_L, ou.LEK_TIP_V
	, coalesce(ou.PPTR, '0'), ssonk.SchetSluchOnkID, ou.LUCH_TIP
	from TmpONK_USL ou
	inner join 
		TmpSS ss
		on  ss.IDCASE = ou.IDCASE
		and ss.SL_ID = ou.SL_ID --ss.N_ZAP=ou.N_ZAP and ss.ID_PAC = ou.ID_PAC and ss.NPOLIS = ou.NPOLIS andss.NHISTORY = ou.NHISTORY 
	inner join
		ISZL.T_SCHET_SLUCH_ONK ssonk 
		on ssonk.SchetSluch = ss.SchetSluchID;
	
	--грузим сведения о введенном противоопухолевом лекарственном препарате
	Drop Table If Exists TmpLEK_PR;
	Create Temp Table TmpLEK_PR
	As
	select gen_random_uuid() as SchetUslOnkLekPrID
	, tr.SchetUslOnkID
	, (xpath('//REGNUM[1]/text()', tr.LEK_PR))[1]::varchar(6) as REGNUM
	, (xpath('//CODE_SH[1]/text()', tr.LEK_PR))[1]::varchar(10) as CODE_SH
	, tr.LEK_PR
	from TmpONK_USL tr;

	INSERT INTO ISZL.T_SCHET_USL_ONK_LEK_PR(SchetUslOnkLekPrID, SchetUslOnk, REGNUM, CODE_SH)
	SELECT lp.SchetUslOnkLekPrID, lp.SchetUslOnkID, lp.REGNUM, lp.CODE_SH
	from TmpLEK_PR lp;

	insert into ISZL.T_SCHET_USL_ONK_LEK_PR_DATE(SchetUslOnkLekPrDateID, DATE_INJ, SchetUslOnkLekPr)
	select gen_random_uuid(), to_timestamp((xpath('//DATE_INJ[1]/text()', di))[1]::text, 'YYYY-MM-DD')
	, lp.SchetUslOnkLekPrID
	from TmpLEK_PR lp, unnest(xpath('/LEK_PR/DATE_INJ', lp.LEK_PR)) di;
	
	--грузим SCHET_SLUCH_SANK Санкция по случаю 
	Drop Table If Exists TmpSANK;
	Create Temp Table TmpSANK
	As
	select
		gen_random_uuid()															as SchetSluchSankID
	,	(xpath('//IDCASE[1]/text()', zsl))[1]::varchar(11)							as IDCASE	
	,	(xpath('//SL_ID[1]/text()', sank))[1]::varchar(36)        					as SL_ID
	,	(xpath('//S_CODE[1]/text()', sank))[1]::varchar(36)							as S_CODE
	,	(xpath('//S_TIP[1]/text()', sank))[1]::text::int							as S_TIP
	,	(xpath('//S_VID[1]/text()', sank))[1]::text::int							as S_VID --в 79 нету этого тега. мб в страых форматах?
	,	to_timestamp((xpath('//DATE_ACT[1]/text()', sank))[1]::text, 'YYYY-MM-DD')	as DATE_ACT
	,	(xpath('//NUM_ACT[1]/text()', sank))[1]::varchar(30)						as NUM_ACT
	,	(xpath('//S_IST[1]/text()', sank))[1]::text::int							as S_IST
	,	(xpath('//S_OSN[1]/text()', sank))[1]::text::int							as S_OSN
	from
	(
		select zsl, unnest(xpath('/Z_SL/SANK', zsl)) as sank
		from
		(
			select unnest(xpath('/ZL_LIST/ZAP/Z_SL', "Xml")) as zsl
		) t1
	) t2;
	
	INSERT INTO ISZL.T_SCHET_SLUCH_SANK(SchetSluchSankID, S_CODE, SchetSluch, S_TIP, S_IST, S_VID
	, SchetSluchAccomplished, DATE_ACT, NUM_ACT, S_OSN)
	SELECT sank.SchetSluchSankID, sank.S_CODE, ss.SchetSluchID, sank.S_TIP, sank.S_IST, sank.S_VID
	, ssa.SchetSluchAccomplishedAID, sank.DATE_ACT, sank.NUM_ACT, sank.S_OSN
	from TmpSANK sank
    inner join
		TmpSSA ssa 
		on ssa.IDCASE = sank.IDCASE --ssa.N_ZAP=sank.N_ZAP and ssa.ID_PAC = sank.ID_PAC and ssa.NPOLIS = sank.NPOLIS and
	left join
		TmpSS ss
		on ss.SL_ID = sank.SL_ID
		and ss.IDCASE = ssa.IDCASE and ss.N_ZAP = ssa.N_ZAP;
	
	--грузим SCHET_SLUCH_SANK 2
	Drop Table If Exists TmpSANK_SL;
	Create Temp Table TmpSANK_SL
	As
	select
		gen_random_uuid()															as SchetSluchSankID
	,	(xpath('//IDCASE[1]/text()', zsl))[1]::varchar(11)							as IDCASE	
	,	(xpath('//SL_ID[1]/text()', sl))[1]::varchar(36)        					as SL_ID
	,	(xpath('//S_CODE[1]/text()', sank))[1]::varchar(36)							as S_CODE
	,	(xpath('//S_TIP[1]/text()', sank))[1]::text::int							as S_TIP
	,	(xpath('//S_VID[1]/text()', sank))[1]::text::int							as S_VID --в 79 нету этого тега. мб в страых форматах?
	,	to_timestamp((xpath('//DATE_ACT[1]/text()', sank))[1]::text, 'YYYY-MM-DD')	as DATE_ACT
	,	(xpath('//NUM_ACT[1]/text()', sank))[1]::varchar(30)						as NUM_ACT
	,	(xpath('//S_IST[1]/text()', sank))[1]::text::int							as S_IST
	,	(xpath('//S_OSN[1]/text()', sank))[1]::text::int							as S_OSN
	from
	(
		select zsl, sl, unnest(xpath('/SL/SANK', sl)) as sank
		from
		(
			select zsl, unnest(xpath('/Z_SL/SL', zsl)) as sl
			from
			(
				select unnest(xpath('/ZL_LIST/ZAP/Z_SL', "Xml")) as zsl
			) t1
		) t2
	) t3;	
	
	INSERT INTO ISZL.T_SCHET_SLUCH_SANK(SchetSluchSankID, S_CODE, SchetSluch, S_TIP, S_IST, S_VID
	, SchetSluchAccomplished, DATE_ACT, NUM_ACT, S_OSN)
	SELECT sank.SchetSluchSankID, sank.S_CODE, ss.SchetSluchID, sank.S_TIP, sank.S_IST, sank.S_VID
	, ssa.SchetSluchAccomplishedAID, sank.DATE_ACT, sank.NUM_ACT, sank.S_OSN
	from TmpSANK_SL sank
    inner join
		TmpSSA ssa
		on ssa.IDCASE = sank.IDCASE --ssa.N_ZAP=sank.N_ZAP and ssa.ID_PAC = sank.ID_PAC and ssa.NPOLIS = sank.NPOLIS and
	left join
		TmpSS ss
		on ss.SL_ID = sank.SL_ID
		and ss.IDCASE = ssa.IDCASE and ss.N_ZAP = ssa.N_ZAP;
	
	Drop Table If Exists TmpDS;
	Create Temp Table TmpDS
	As
	select 
		(xpath('//IDCASE[1]/text()', zsl))[1]::varchar(11)	as IDCASE
	,	(xpath('//SL_ID[1]/text()',sl))[1]::varchar(36)	as SL_ID
	,	(xpath('//DS2[1]/text()',ds))[1]::varchar(10)	as DS
	,	null::boolean as DS2_PR
	,	null::int as PR_DS2_N
	,	0 as DS_TYPE
	from
	(
		select zsl, sl, unnest(xpath('/SL/DS2', sl)) as ds
		from
		(
			select zsl, unnest(xpath('/Z_SL/SL', zsl)) as sl
			from
			(
				select unnest(xpath('/ZL_LIST/ZAP/Z_SL', "Xml")) as zsl
			) t1
		) t2
	) t3
	union all
	select 
		(xpath('//IDCASE[1]/text()', zsl))[1]::varchar(11)	as IDCASE
	,	(xpath('//SL_ID[1]/text()',sl))[1]::varchar(36)	as SL_ID
	,	(xpath('//DS2[1]/text()',ds))[1]::varchar(10)	as DS
	,	(xpath('//DS2_PR[1]/text()',ds))[1]::text::boolean	as DS2_PR
	,	(xpath('//PR_DS2_N[1]/text()',ds))[1]::text::int as PR_DS2_N
	,	0 as DS_TYPE
	from
	(
		select zsl, sl, unnest(xpath('/SL/DS2_N', sl)) as ds
		from
		(
			select zsl, unnest(xpath('/Z_SL/SL', zsl)) as sl
			from
			(
				select unnest(xpath('/ZL_LIST/ZAP/Z_SL', "Xml")) as zsl
			) t1
		) t2
	) t3
	union all
	select 
		(xpath('//IDCASE[1]/text()', zsl))[1]::varchar(11)	as IDCASE
	,	(xpath('//SL_ID[1]/text()',sl))[1]::varchar(36)	as SL_ID
	,	(xpath('//DS3[1]/text()',ds))[1]::varchar(10)	as DS
	,	null::boolean	as DS2_PR
	,	(xpath('//PR_DS2_N[1]/text()',ds))[1]::text::int as PR_DS2_N
	,	1 as DS_TYPE
	from
	(
		select zsl, sl, unnest(xpath('/SL/DS3', sl)) as ds
		from
		(
			select zsl, unnest(xpath('/Z_SL/SL', zsl)) as sl
			from
			(
				select unnest(xpath('/ZL_LIST/ZAP/Z_SL', "Xml")) as zsl
			) t1
		) t2
	) t3;

	INSERT INTO ISZL.T_SCHET_SLUCH_DS(SchetSluchDsID, MKB, DS2_PR, SchetSluch, MKBType, PR_DS2_N)
	SELECT gen_random_uuid(), ds.DS, ds.DS2_PR, ss.SchetSluchID, ds.DS_TYPE, ds.PR_DS2_N
	FROM TmpDS ds
	INNER JOIN
		TmpSS ss
		on ss.SL_ID = ds.SL_ID
		and ss.IDCASE = ds.IDCASE;-- and ss.N_ZAP = sz.N_ZAP
	
	--грузим SchetUslNaprs Направления по онкологии
	Drop Table If Exists TmpNAPR;
	Create Temp Table TmpNAPR
	As
	select
		gen_random_uuid()															as SchetUslNaprID
	,	(xpath('//IDCASE[1]/text()', zsl))[1]::varchar(11)							as IDCASE
	,	(xpath('//SL_ID[1]/text()',sl))[1]::varchar(36)								as SL_ID
	,	to_timestamp((xpath('//NAPR_DATE[1]/text()',napr))[1]::text, 'YYYY-MM-DD')	as NAPR_DATE
	,	(xpath('//NAPR_MO[1]/text()',napr))[1]::varchar(6)							as NAPR_MO
	,	(xpath('//NAPR_V[1]/text()',napr))[1]::text::int							as NAPR_V
	,	(xpath('//MET_ISSL[1]/text()',napr))[1]::text::int							as MET_ISSL
	,	(xpath('//NAPR_USL[1]/text()',napr))[1]::varchar(16)						as NAPR_USL
	from
	(
		select zsl, sl, unnest(xpath('/SL/NAPR', sl)) as napr
		from
		(
			select zsl, unnest(xpath('/Z_SL/SL', zsl)) as sl
			from
			(
				select unnest(xpath('/ZL_LIST/ZAP/Z_SL', "Xml")) as zsl
			) t1
		) t2
	) t3;
	
	INSERT INTO ISZL.T_SCHET_USL_NAPR(SchetUslNaprID, NAPR_DATE, NAPR_MO, NAPR_V, MET_ISSL, NAPR_USL, SchetSluch)
	SELECT napr.SchetUslNaprID, napr.NAPR_DATE, napr.NAPR_MO, napr.NAPR_V, napr.MET_ISSL, napr.NAPR_USL, ss.SchetSluchID
	from TmpNAPR napr
	inner join
		TmpSS ss
		on ss.SL_ID = napr.SL_ID 
		and ss.IDCASE = napr.IDCASE
		and ss.SL_ID = napr.SL_ID; --and ss.N_ZAP = napr.N_ZAP
	
	--грузим SchetSluchConss Сведения о проведении консилиума 
	Drop Table If Exists TmpCONS;
	Create Temp Table TmpCONS
	As
	select
		gen_random_uuid()															as SchetSluchConsID
	,	(xpath('//IDCASE[1]/text()', zsl))[1]::varchar(11)							as IDCASE
	,	(xpath('//SL_ID[1]/text()',sl))[1]::varchar(36)								as SL_ID
	,	to_timestamp((xpath('//DT_CONS[1]/text()',cons))[1]::text, 'YYYY-MM-DD')	as DT_CONS
	,	(xpath('//PR_CONS[1]/text()',cons))[1]::text::int							as PR_CONS
	from
	(
		select zsl, sl, unnest(xpath('/SL/CONS', sl)) as cons
		from
		(
			select zsl, unnest(xpath('/Z_SL/SL', zsl)) as sl
			from
			(
				select unnest(xpath('/ZL_LIST/ZAP/Z_SL', "Xml")) as zsl
			) t1
		) t2
	) t3;
	
	INSERT INTO ISZL.T_SCHET_SLUCH_CONS(SchetSluchConsID, SchetSluch, DT_CONS, PR_CONS)
	SELECT cons.SchetSluchConsID, ss.SchetSluchID, cons.DT_CONS, cons.PR_CONS
	from TmpCONS cons
	inner join
		TmpSS ss
		on ss.SL_ID = cons.SL_ID
		and ss.IDCASE = cons.IDCASE
		and ss.SL_ID = cons.SL_ID; --and ss.N_ZAP = cons.N_ZAP
	
	Drop Table If Exists TmpUSL;
	Create Temp Table TmpUSL
	As
	select
		gen_random_uuid()															as SchetUslID
	,	(xpath('//IDCASE[1]/text()', zsl))[1]::varchar(11)							as IDCASE
	,	(xpath('//SL_ID[1]/text()',sl))[1]::varchar(36)								as SL_ID
	,	(xpath('//IDSERV[1]/text()',usl))[1]::varchar(36)							as IDSERV
	,	(xpath('//LPU[1]/text()',usl))[1]::varchar(6)								as LPU
	,	to_timestamp((xpath('//DATE_IN[1]/text()',usl))[1]::text, 'YYYY-MM-DD')		as DATE_IN
	,	to_timestamp((xpath('//DATE_OUT[1]/text()',usl))[1]::text, 'YYYY-MM-DD')	as DATE_OUT
	,	(xpath('//DS[1]/text()',usl))[1]::varchar(10)								as DS
	,	(xpath('//KOL_USL[1]/text()',usl))[1]::text::decimal(6,2)					as KOL_USL
	,	(xpath('//USL[1]/text()',usl))[1]::varchar(254)								as USL
	,	(xpath('//VID_VME[1]/text()',usl))[1]::varchar(254)							as VID_VME
	,	(xpath('//CODE_USL[1]/text()',usl))[1]::varchar(20)							as CODE_USL
	,	(xpath('//P_OTK[1]/text()',usl))[1]::text::boolean								as P_OTK
	,	(xpath('//CODE_MD[1]/text()',usl))[1]::varchar(25)							as CODE_MD
	from
	(
		select zsl, sl, unnest(xpath('/SL/USL', sl)) as usl
		from
		(
			select zsl, unnest(xpath('/Z_SL/SL', zsl)) as sl
			from
			(
				select unnest(xpath('/ZL_LIST/ZAP/Z_SL', "Xml")) as zsl
			) t1
		) t2
	) t3;
	
	create index IX_TmpUSL_IDCASE on TmpUSL(IDCASE, SL_ID);
	
	insert into ISZL.T_SCHET_USL(SchetUslID, IDSERV, LPU, DATE_IN, DATE_OUT, DS, KOL_USL, USL, VID_VME
	, SchetSluch, CODE_USL, P_OTK, CODE_MD)
	select u.SchetUslID, u.IDSERV, u.LPU, u.DATE_IN, u.DATE_OUT, u.DS, u.KOL_USL, u.USL, u.VID_VME
	, ss.SchetSluchID, u.CODE_USL, u.P_OTK, u.CODE_MD
	from TmpUSL u
	inner join
		TmpSS ss
		on ss.SL_ID = u.SL_ID
		and ss.IDCASE = u.IDCASE; --ss.N_ZAP=u.N_ZAP and ss.ID_PAC = u.ID_PAC and ss.NPOLIS = u.NPOLIS and ss.NHISTORY = u.NHISTORY 
	
	Drop Table If Exists TmpNAZ;
	Create Temp Table TmpNAZ
	As
	select
		gen_random_uuid()															as SchetSluchNazID
	,	(xpath('//IDCASE[1]/text()', zsl))[1]::varchar(11)							as IDCASE
	,	(xpath('//SL_ID[1]/text()',sl))[1]::varchar(36)								as SL_ID
	,	(xpath('//NAZ_N[1]/text()',naz))[1]::text::int								as NAZ_N
	,	(xpath('//NAZR[1]/text()',naz))[1]::text::int								as NAZR
	,	(xpath('//NAZ_SP[1]/text()',naz))[1]::text::int								as NAZ_SP
	,	(xpath('//NAZ_V[1]/text()',naz))[1]::text::int								as NAZ_V
	,	(xpath('//NAZ_PK[1]/text()',naz))[1]::text::int								as NAZ_PK
	,	(xpath('//NAZ_USL[1]/text()',naz))[1]::varchar(15)							as NAZ_USL
	,	to_timestamp((xpath('//NAPR_DATE[1]/text()',naz))[1]::text, 'YYYY-MM-DD')	as NAPR_DATE
	,	(xpath('//NAPR_MO[1]/text()',naz))[1]::varchar(6)							as NAPR_MO
	from
	(
		select zsl, sl, unnest(xpath('/SL/NAZ', sl)) as naz
		from
		(
			select zsl, unnest(xpath('/Z_SL/SL', zsl)) as sl
			from
			(
				select unnest(xpath('/ZL_LIST/ZAP/Z_SL', "Xml")) as zsl
			) t1
		) t2
	) t3;
	
	insert into ISZL.T_SCHET_SLUCH_NAZ(SchetSluchNazID, NAZ_N, NAZR, NAZ_SP, NAZ_V, NAZ_PK, SchetSluch
	, NAZ_USL, NAPR_DATE, NAPR_MO)
	select n.SchetSluchNazID, n.NAZ_N, n.NAZR, n.NAZ_SP, n.NAZ_V, n.NAZ_PK, ss.SchetSluchID
	, n.NAZ_USL, n.NAPR_DATE, n.NAPR_MO
	from TmpNAZ n
	inner join
		TmpSS ss
		on ss.SL_ID = n.SL_ID
		and ss.IDCASE = n.IDCASE; --ss.N_ZAP=n.N_ZAP and ss.ID_PAC = n.ID_PAC and ss.NPOLIS = n.NPOLIS and ss.NHISTORY = n.NHISTORY
	
	drop table if exists TmpNAZ;
	drop table if exists TmpUSL;
	drop table if exists TmpCONS;
	drop table if exists TmpNAPR;
	drop table if exists TmpDS;
	drop table if exists TmpSANK_SL;
	drop table if exists TmpSANK;
	drop table if exists TmpLEK_PR;
	drop table if exists TmpONK_USL;
	drop table if exists TmpB_PROT;
	drop table if exists TmpB_DIAG;
	drop table if exists TmpSSONK;
	drop table if exists TmpSS;
	drop table if exists TmpSSA;
	drop table if exists TmpSCHET_ZAP;
	drop table if exists TmpSCHET;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE;
