DROP PROCEDURE IF EXISTS public.sp_mtrschetload_bulk_3_3;

CREATE PROCEDURE public.sp_mtrschetload_bulk_3_3(
	IN "SchetID" uuid DEFAULT NULL::uuid,
	IN type integer DEFAULT NULL::integer,
	IN "Worker" uuid DEFAULT NULL::uuid,
	INOUT "BulkHeaders" jsonb DEFAULT NULL::jsonb,
	INOUT "ErrorFLK" text DEFAULT ''::text)
LANGUAGE 'plpgsql'
AS $BODY$
declare

worker uuid := "Worker";
id uuid := "SchetID";
additionalSchetID uuid := (select s.SchetID
				from vcscheme."TEMP_SCHET_MTR" schet
				join vcscheme."TEMP_ZGLV_MTR" zglv on schet."ID" = zglv."ID"
				join ies.T_SCHET s on s.NSCHET = schet."NSCHET"
								and s.C_OKATO1 = zglv."C_OKATO1" 
								and s.DSCHET = schet."DSCHET"
								and s.type_ = 538 
								and s.IsDelete = 0 
								and s.SchetKind = 0
								and ((s."year" = 2022 and s."month" >= 11) or s."year" > 2022)
								limit 1);
isAdditional bool := false;
cnt int := (select count(*) 
			from( select gen_random_uuid() as SchetSluchDsID,
						 t1."DS2" as mkb,
						 t2."SchetSluchID" as SchetSluch,
						 0 as MKBType
						 from vcscheme."TEMP_DS2_MTR" t1
						 join vcscheme."TEMP_SLUCH_MTR" t2 on t2."IDCASE" = t1."IDCASE"
						 union
						 select gen_random_uuid() as SchetSluchDsID,
						 t1."DS3" as mkb,
						 t2."SchetSluchID" as SchetSluch,
						 1 as MKBType
						 from vcscheme."TEMP_DS3_MTR" t1
						 join vcscheme."TEMP_SLUCH_MTR" t2 on t2."IDCASE" = t1."IDCASE") AS query_in); 

begin
	if additionalSchetID is not null
	then
		isAdditional := true;
	end if;

--Для ФЛК N - файлов присвоим 5381
--=================================
	DECLARE _type_N INT;
	BEGIN
	_type_N := type; 
    IF EXISTS (SELECT 1 FROM ies.T_SCHET_LOAD_HISTORY f WHERE f.Schet = "SchetID" AND f.FileName ~ '^N[0-9]')
	THEN
        _type_N := 5381;
    END IF;

--=================================
	

	-- flk
	create temp table errors
	(
	oshib varchar(36),
	im_pol varchar(10),
	zn_pol varchar(100),
	nschet varchar(15),
	bas_el varchar(10),
	n_zap int,
	id_pac varchar(36),
	idcase varchar(11),
	sl_id varchar(36),
	idserv varchar(36),
	comment varchar(250)
	); 

	--call sp_GetFlkFiltersBulkMtrSchet538_q015 ("_type_N");

	insert into errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, ID_PAC, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
	select 'N_ZAP', t0."N_ZAP" as ZN_POL, (select s.nschet from ies.t_schet as s where s.schetid = id limit 1), 'ZAP', t0."N_ZAP", null as ID_PAC, null as IDCASE, null as SL_ID, null as IDSERV
	, '905', 'Дублирующий идентификатор N_ZAP'
	from vcscheme."TEMP_ZAP_MTR" as t0 
	group by t0."N_ZAP"
	having count(*) > 1;

	insert into errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, ID_PAC, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
	select 'IDCASE', t0."IDCASE" as ZN_POL, (select s.nschet from ies.t_schet as s where s.schetid = id limit 1), 'Z_SL', null as N_ZAP, null as ID_PAC, t0."IDCASE", null as SL_ID, null as IDSERV
	, '905', 'Дублирующий идентификатор IDCASE'
	from vcscheme."TEMP_Z_SLUCH_MTR" as t0 
	group by "IDCASE"
	having count(*) > 1;

	insert into errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, ID_PAC, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
	select 'SL_ID', "SL_ID" as ZN_POL, (select s.nschet from ies.t_schet as s where s.schetid = id limit 1), 'SL', null as N_ZAP, null as ID_PAC, t0."IDCASE", t0."SL_ID", null as IDSERV
	, '905', 'Дублирующий идентификатор SL_ID, в пределах законченного случая'
	from vcscheme."TEMP_SLUCH_MTR" as t0 
	group by "IDCASE", "SL_ID"
	having count(*) > 1;

	insert into errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, ID_PAC, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
	select 'IDSERV', "IDSERV" as ZN_POL, (select s.nschet from ies.t_schet as s where s.schetid = id limit 1), 'USL', null as N_ZAP, null as ID_PAC, t0."IDCASE", t0."SL_ID", t0."IDSERV" as IDSERV
	, '905', 'Дублирующий идентификатор IDSERV в пределах одного случая'
	from vcscheme."TEMP_USL_MTR" as t0 
	group by t0."IDCASE", t0."SL_ID", t0."IDSERV"
	having count(*) > 1;

	insert into errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, ID_PAC, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
	select 'S_CODE', t0."S_CODE" as ZN_POL ,(select s.nschet from ies.t_schet as s where s.schetid = id limit 1), 'SANK', null as N_ZAP, null as ID_PAC, t0."IDCASE", null as SL_ID, null as IDSERV
	, '905', 'Дубль S_CODE в пределах законченного случая'
	from vcscheme."TEMP_SANK_MTR" as t0 
	group by t0."S_CODE", t0."IDCASE", t0."N_ZAP"
	having count(*) > 1;

	insert into errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, ID_PAC, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
	select 'S_CODE', t0."S_OSN" as ZN_POL ,(select s.nschet from ies.t_schet as s where s.schetid = id limit 1), 'SANK', t1."N_ZAP" as N_ZAP, null as ID_PAC, t0."IDCASE",  t0."SL_ID" as SL_ID, null as IDSERV
	, '001F.00.0680', 'Значение не найдено в справочнике F014'
	from vcscheme."TEMP_SANK_MTR" as t0 
	join vcscheme."TEMP_Z_SLUCH_MTR" as t1 on t0."IDCASE" = t1."IDCASE"
	left join ies.t_f014_deny_reason t2 on t2.kod = t0."S_OSN"
										and t2.datebeg <= coalesce(t0."DATE_ACT", t1."DATE_Z_2")
										and (t2.dateend is null or t2.dateend >= coalesce(t0."DATE_ACT", t1."DATE_Z_2"))
	where t0."S_OSN" is not null and t2.kod is null;

	if (select count(*) from errors) > 0
	then
	select
	'<?xml version="1.0" encoding="Windows-1251"?>' + 
	xmlelement(name "FLK_P", 
	xmlagg(xmlelement(name "PR", 
	xmlforest(
	  oshib
	, im_pol
	, zn_pol
	, nschet
	, bas_el
	, n_zap
	, id_pac
	, idcase
	, sl_id
	, idserv
	, comment
	))))::text from errors
	into "ErrorFLK";
	
	else
	if isAdditional is true then 
		call sp_MtrSchetLoadisAdditional_Bulk_3_3(additionalSchetID);
	end if;

	update vcscheme."TEMP_ZGLV_MTR" set "ID" = '1';
	update vcscheme."TEMP_SCHET_MTR" set "ID" = '1';
	update vcscheme."TEMP_ZAP_MTR" set "SchetZapID" = gen_random_uuid();
	update vcscheme."TEMP_Z_SLUCH_MTR" set "SchetSluchAccomplishedID" = gen_random_uuid();
	update vcscheme."TEMP_SANK_MTR" set "SchetSluchSankID" = gen_random_uuid();
	update vcscheme."TEMP_SANK_SL_ID_MTR" set "SchetSluchSankSLID" = gen_random_uuid();
	update vcscheme."TEMP_SLUCH_MTR" set "SchetSluchID" = gen_random_uuid();
	update vcscheme."TEMP_ONK_SL_MTR" set "SchetSluchOnkID" = gen_random_uuid();
	update vcscheme."TEMP_ONK_USL_MTR" set "SchetOnkUslID" = gen_random_uuid();
	update vcscheme."TEMP_SANK_MTR" set "SchetSluchSankID" = gen_random_uuid();
	update vcscheme."TEMP_KSG_KPG_MTR" set "KsgID" = gen_random_uuid();
	update vcscheme."TEMP_USL_MTR" set "SchetUslID" = gen_random_uuid();
	update vcscheme."TEMP_SLUCH_MTR" set "NHISTORY" = case when _type_N = 5381 then 'Нет' else "NHISTORY" end;

	-- temp select table
	create temp table temp_bulkheaders(TableSchemaName varchar(100), OperationName varchar(150), RowsCount int, SelectQuery text);

	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
		values ('IES.T_SCHET', 'Счета', (select count(*) from "vcscheme"."TEMP_SCHET_MTR"),
    ('select t1."VERSION" as version,
    		t1."DATA" as data,
    		t1."C_OKATO1" as c_okato1,
    		t1."OKATO_OMS" as okato_oms,
    		t0."CODE" as code,
    		t0."YEAR" as year,
    		t0."MONTH" as month,
    		t0."NSCHET" as nschet,
    		t0."DSCHET" as dschet,
    		t0."SUMMAV" as summav,
    		t0."COMENTS" as coments,
    		t0."SUMMAP" as summap,
    		t0."SANK_MEK" as sank_mek,
    		t0."SANK_MEE" as sank_mee,
    		t0."SANK_EKMP" as sank_ekmp,
    		current_date:: timestamp as ReceivedDate,
    		now()::timestamp as ReceivedTime,
    		:Worker as Worker,
    		:SchetID as SchetID,
    		538 as type_,
    		0 as status,
    		0 as SchetKind
	from vcscheme."TEMP_SCHET_MTR" t0
	join vcscheme."TEMP_ZGLV_MTR" t1 on t0."ID" = t1."ID"' ));

	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
		values ('IES.T_SCHET_ZAP', 'информации о людях', (select count(*) from "vcscheme"."TEMP_ZAP_MTR"),
	('select t0.n_zap, left(t0.result, length(t0.result)-1) as result into temp_dost
	 from (select distinct "N_ZAP" :: varchar(36) as n_zap,
		(select string_agg(t1."N_ZAP" :: varchar(1), '','')
		 from vcscheme."TEMP_DOST_MTR" t1
		 where t1."N_ZAP" ::varchar(36) = st2."N_ZAP" ::varchar(36)
		 group by t1."N_ZAP"
		 order by t1."N_ZAP"
		 )as result
		from vcscheme."TEMP_DOST_MTR" st2) t0;
		
	select t0.n_zap, left(t0.result, length(t0.result)-1) as result into temp_dost_p
	 from (select distinct "N_ZAP" :: varchar(36) as n_zap,
		(select  string_agg(t1."N_ZAP" :: varchar(1), '','')
		 from vcscheme."TEMP_DOST_P_MTR" t1
		 where t1."N_ZAP" ::varchar(36) = st2."N_ZAP" ::varchar(36)
		 group by t1."N_ZAP" 
		 order by t1."N_ZAP"
		 )as result
		from vcscheme."TEMP_DOST_P_MTR" st2) t0;
	
	select
	 zap."SchetZapID" as SchetZapID,
	 zap."N_ZAP" as N_ZAP,
	 :SchetID as Schet,
	 505 as "type_",
	 pac."VPOLIS" AS VPOLIS,
	 pac."SPOLIS" AS SPOLIS,
	 pac."NPOLIS" AS NPOLIS,
	 pac."ENP" AS ENP,
	 pac."ST_OKATO" AS ST_OKATO,
	 pac."FAM" AS FAM,
	 pac."IM" AS IM,
	 pac."OT" AS OT,
	 pac."W" AS W,
	 pac."DR" AS DR,
	 d1."result" AS DOST,
	 pac."FAM_P" AS FAM_P,
	 pac."IM_P" AS IM_P,
	 pac."OT_P" AS OT_P,
	 pac."W_P" AS W_P,
	 pac."DR_P" AS DR_P,
	 d2."result" AS DOST_P,
	 pac."MR" AS MR,
	 pac."DOCTYPE" AS DOCTYPE,
	 pac."DOCSER" AS DOCSER,
	 pac."DOCNUM" AS DOCNUM,
	 pac."DOCDATE" AS DOCDATE,
	 pac."DOCORG" AS DOCORG,
	 pac."SNILS" AS SNILS,
	 pac."OKATOG" AS OKATOG,
	 pac."OKATOP" AS OKATOP,
	 pac."NOVOR" AS NOVOR,
	 pac."VNOV_D" AS VNOV_D,
	 pac."COMENTP" AS COMENTP
		from vcscheme."TEMP_ZAP_MTR" zap
		join vcscheme."TEMP_PACIENT_MTR" pac on pac."N_ZAP" = zap."N_ZAP"
		left join temp_dost d1 on pac."N_ZAP" = d1.n_zap
		left join temp_dost_p d2 on pac."N_ZAP" = d2.n_zap;
	
	drop table temp_dost;
	drop table temp_dost_p;'));

	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
		values ('IES.T_SCHET_SLUCH_ACCOMPLISHED', 'информации о законченных случаях лечения', (select count(*) from "vcscheme"."TEMP_Z_SLUCH_MTR"),
	('select t0.idcase, left(t0.result, length(t0.result)-1) as result into OS_SLUCH
	   from (select distinct "IDCASE" :: varchar(36) as IDCASE,
		(select string_agg(t1."OS_SLUCH" :: varchar(1), '','') 
		 from vcscheme."TEMP_OS_SLUCH_MTR" t1
		 where t1."IDCASE"= st2."IDCASE"
		 group by t1."IDCASE"
		 order by t1."IDCASE"
		 )as result
		from vcscheme."TEMP_OS_SLUCH_MTR" st2) t0;
	
	 select
	   zSL."SchetSluchAccomplishedID" AS SchetSluchAccomplishedID,
	   zap."SchetZapID" AS SchetZap,
	   zsl."IDCASE" AS IDCASE,
	   zsl."USL_OK" AS USL_OK,
	   zsl."VIDPOM" AS VIDPOM,
	   zsl."FOR_POM" AS FOR_POM,
	   zsl."NPR_MO" AS NPR_MO,
	   zsl."NPR_DATE" AS NPR_DATE,
	   zsl."P_DISP2" AS P_DISP2,
	   zsl."LPU" AS LPU,
	   zsl."DATE_Z_1" AS DATE_Z_1,
	   zsl."DATE_Z_2" AS DATE_Z_2,
	   zsl."KD_Z" AS KD_Z,
	   zsl."VNOV_M" AS VNOV_M,
	   zsl."RSLT" AS RSLT,
	   zsl."ISHOD" AS ISHOD,
	   os.result AS OS_SLUCH,
	   zsl."VB_P" AS VB_P,
	   zsl."IDSP" AS IDSP,
	   zsl."SUMV" AS SUMV,
	   zsl."OPLATA" AS OPLATA,
	   zsl."SUMP" AS SUMP,
	   zsl."SANK_IT" AS SANK_IT
	 from vcscheme."TEMP_Z_SLUCH_MTR" zsl
	 join vcscheme."TEMP_ZAP_MTR" zap on zap."N_ZAP" = zsl."N_ZAP"
	 left join OS_SLUCH os on os.idcase = zsl."IDCASE";
	
	drop table OS_SLUCH;'));

    insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
    	values ('IES.T_SCHET_SLUCH', 'информации о случаях лечения', (select count(*) from "vcscheme"."TEMP_SLUCH_MTR"),
    	('select
			sl."SchetSluchID" AS SchetSluchID,
			zap."SchetZapID" AS SchetZap,
			zSL."SchetSluchAccomplishedID" AS SchetSluchAccomplished,
			506 AS type_,
			sl."SL_ID" AS SL_ID,
			sl."VID_HMP" AS VID_HMP,
			(select v019.V019VmpMethodID from IES.T_V019_VMP_METHOD v019 where sl."METOD_HMP" = v019.IDHM 
																			and v019.DATEBEG <= zsl."DATE_Z_2"
																			and (v019.DATEEND is null or v019.DATEEND::timestamp >= zsl."DATE_Z_2") limit 1) AS METOD_HMP,
			sl."PROFIL" AS PROFIL,
			(select v020.V020BedProfileId from IES.T_V020_BED_PROFILE v020 where sl."PROFIL_K" = v020.IDK_PR
																			and v020.DATEBEG <= zsl."DATE_Z_2"
																			and (v020.DATEEND is null or v020.DATEEND::timestamp >= zsl."DATE_Z_2") limit 1) AS PROFIL_K,
			sl."DET" AS DET,
			(select v025.V025KpcID from IES.T_V025_KPC v025 where v025.IDPC = sl."P_CEL"
																and v025.DATEBEG <= zsl."DATE_Z_2"
																and (v025.DATEEND is null or v025.DATEEND::timestamp >= zsl."DATE_Z_2") limit 1) AS P_CEL,
			sl."DISP" AS DISP,
			sl."TAL_D" AS TAL_D,
			sl."NHISTORY" AS NHISTORY,
			sl."DATE_1" AS DATE_1,
			sl."DATE_2" AS DATE_2,
			sl."KD" AS KD,
			sl."DS0" AS DS0,
			sl."DS1" AS DS1,
			(select v027.V027CZabID from IES.T_V027_C_ZAB v027 where v027.IDCZ = sl."C_ZAB"
																and v027.DATEBEG <= zsl."DATE_Z_2"
																and (v027.DATEEND is null or v027.DATEEND::timestamp >= zsl."DATE_Z_2") limit 1) AS C_ZAB,
			sl."DS_ONK" AS DS_ONK,
			sl."DN" AS DN,
			sl."CODE_MES1" AS CODE_MES1,
			sl."CODE_MES2" AS CODE_MES2,
			sl."REAB" AS REAB,
			(SELECT v021.idspec FROM IES.t_v021_med_spec v021 WHERE v021.CODE_SPEC=sl."PRVS" 
																AND v021.DATEBEG <= zsl."DATE_Z_2" and
																(v021.DATEEND IS NULL OR v021.DATEEND::timestamp >= zsl."DATE_Z_2") limit 1) AS PRVS3,
			sl."VERS_SPEC" AS VERS_SPEC,
			sl."ED_COL" AS ED_COL,
			sl."TARIF" AS TARIF,
			sl."SUM_M" AS SUMV,
			sl."SUM_M" AS SUMP,
			sl."COMENTSL" AS COMENTSL
    	from vcscheme."TEMP_SLUCH_MTR" sl
    	join vcscheme."TEMP_ZAP_MTR" zap on zap."N_ZAP" = sl."N_ZAP"
    	join vcscheme."TEMP_Z_SLUCH_MTR" zsl on sl."IDCASE" = zsl."IDCASE"'));
    
    if("type" = 538) then--Входящий счет по МТР основной
    	 insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
    		values ('IES.T_SCHET_RESULTS', 'информации о результатах лечения', (select count(*) from "vcscheme"."TEMP_SLUCH_MTR"),
	    	('select gen_random_uuid() as SchetResultsID,
	    		:SchetID as MtrSchetIn,
	    		sl."SchetSluchID" as SchetSluchMain,
	    		null as SchetSluchAccepted
			  from vcscheme."TEMP_SLUCH_MTR" sl
			  join vcscheme."TEMP_ZAP_MTR" zap on zap."N_ZAP" = sl."N_ZAP"'));
    end if;
   
   insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
    	values ('IES.T_SCHET_SLUCH_DS', 'информации об диагнозах', 1,
    	('select gen_random_uuid() as SchetSluchDsID,
    			t1."DS2" as mkb,
    			t2."SchetSluchID" as SchetSluch,
    			0 as mkbtype,
    			null::int as ds2_pr,
    			null::int as pr_ds2_n
    		from vcscheme."TEMP_DS2_MTR" t1
    		join vcscheme."TEMP_SLUCH_MTR" t2 on t2."IDCASE" = t1."IDCASE" and t1."SL_ID" = t2."SL_ID"
    		union
		 select gen_random_uuid() as SchetSluchDsID,
    			t1."DS3" as mkb,
    			t2."SchetSluchID" as SchetSluch,
    			1 as mkbtype,
    			null::int as ds2_pr,
    			null::int as pr_ds2_n
			from vcscheme."TEMP_DS3_MTR" t1
			join vcscheme."TEMP_SLUCH_MTR" t2 on t2."IDCASE" = t1."IDCASE" and t1."SL_ID" = t2."SL_ID"
			union
		select  gen_random_uuid() as SchetSluchDsID,
    			t1."DS2" as mkb,
    			t2."SchetSluchID" as SchetSluch,
    			0 as mkbtype,
    			t1."DS2_PR" as ds2_pr,
    			t1."PR_DS2_N" as pr_ds2_n
			from vcscheme."TEMP_DS2_N_MTR" t1
			join vcscheme."TEMP_SLUCH_MTR" t2 on t2."IDCASE" = t1."IDCASE" and t1."SL_ID" = t2."SL_ID"'));
		
	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
	 	values ('IES.T_SCHET_USL_NAPR', 'направлений', (select count(*) from "vcscheme"."TEMP_NAPR_MTR"),
		('select gen_random_uuid() as SchetUslNaprID,
			sl."SchetSluchID" as SchetSluch,
			napr."NAPR_DATE" as NAPR_DATE,
			napr."NAPR_MO" as NAPR_MO,
			napr."NAPR_V" as NAPR_V,
			napr."MET_ISSL" as MET_ISSL,
			napr."NAPR_USL" as NAPR_USL
			from vcscheme."TEMP_NAPR_MTR" napr
			join vcscheme."TEMP_SLUCH_MTR" sl on napr."IDCASE" = sl."IDCASE" and napr."SL_ID" = sl."SL_ID"
			join vcscheme."TEMP_Z_SLUCH_MTR" zsl on zsl."IDCASE" = sl."IDCASE"'));
		
	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
	 	values ('IES.T_SCHET_SLUCH_CONS', 'сведений о проведении консилиума', (select count(*) from "vcscheme"."TEMP_CONS_MTR"),
	 	('select gen_random_uuid() as SchetSluchConsID,
	 		sl."SchetSluchID" as SchetSluch,
	 		(select n019.n019onkconsid from ies.t_n019_onk_cons n019
				where n019.id_cons = cons."PR_CONS"
				and n019.datebeg <= zsl."DATE_Z_2"
				and (n019.dateend is null or n019.dateend::timestamp >= zsl."DATE_Z_2")
			limit 1) as N019OnkCons,
	 		cons."DT_CONS" as DT_CONS
	 		from vcscheme."TEMP_CONS_MTR" cons
	 		join vcscheme."TEMP_SLUCH_MTR" sl on cons."IDCASE" = sl."IDCASE" and cons."SL_ID" = sl."SL_ID"
	 		join vcscheme."TEMP_Z_SLUCH_MTR" zsl on cons."IDCASE" = zsl."IDCASE"'));
	 	
	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
	 	values ('IES.T_SCHET_SLUCH_ONK', ' онкологических заболеваний', (select count(*) from "vcscheme"."TEMP_ONK_SL_MTR"),
	 	('select 
	 		onksl."SchetSluchOnkID" as SchetSluchOnkID,
	 		sl."SchetSluchID" as SchetSluch,
	 		(select n018.n018onkreasid from ies.t_n018_onk_reas n018 where n018.id_reas = onksl."DS1_T"
													and n018.datebeg <= zsl."DATE_Z_2"
													and (n018.dateend is null or n018.dateend >= zsl."DATE_Z_2")
													limit 1 ) as DS1_T,
			(select n002.n002stadiumid from ies.t_n002_stadium n002 where n002.id_st = onksl."STAD"
													and n002.datebeg <= zsl."DATE_Z_2"
													and (n002.dateend is null or n002.dateend >= zsl."DATE_Z_2")
													limit 1) as STAD,
			(select n003.n003tumorid from ies.t_n003_tumor n003 where n003.id_t = onksl."ONK_T"
												  	and n003.datebeg <= zsl."DATE_Z_2"
												  	and (n003.dateend is null or n003.dateend >= zsl."DATE_Z_2")
												  	limit 1) as ONK_T,
			(select n004.n004nodusid from ies.t_n004_nodus n004 where n004.id_n = onksl."ONK_N"
													and n004.datebeg <= zsl."DATE_Z_2"
													and (n004.dateend is null or n004.dateend >= zsl."DATE_Z_2")
													limit 1) as ONK_N,
			(select n005.n005metastasisid from ies.t_n005_metastasis n005 where n005.id_m = onksl."ONK_M"
													and n005.datebeg <= zsl."DATE_Z_2"
													and (n005.dateend is null or n005.dateend >= zsl."DATE_Z_2") 
													limit 1) as ONK_M,
			onksl."MTSTZ" as MTSTZ,
			onksl."SOD" as SOD,
			onksl."K_FR" as K_FR,
			onksl."WEI" as WEI,
			onksl."HEI" as HEI,
			onksl."BSA" as BSA
	 		from vcscheme."TEMP_ONK_SL_MTR" onksl
	 		join vcscheme."TEMP_SLUCH_MTR" sl on sl."IDCASE" = onksl."IDCASE" and sl."SL_ID" = onksl."SL_ID"
	 		join vcscheme."TEMP_Z_SLUCH_MTR" zsl on zsl."IDCASE" = sl."IDCASE"'));
	 	
	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
	 	values ('IES.T_SCHET_SLUCH_ONK_PROT', ' сведения об имеющихся противопоказаниях', (select count(*) from "vcscheme"."TEMP_B_PROT_MTR"),
	 	('select
	 		gen_random_uuid() as SchetSluchOnkProtID,
	 		onksl."SchetSluchOnkID" as SchetSluchOnk,
	 		prot."D_PROT" as D_PROT,
	 		(select n001.n001protid from ies.t_n001_prot n001 where n001.id_prot = prot."PROT"
	 													and n001.datebeg <= zsl."DATE_Z_2"
	 													and (n001.dateend is null or n001.dateend >= zsl."DATE_Z_2")
	 													limit 1) as PROT
	 		from vcscheme."TEMP_B_PROT_MTR" prot
	 		join vcscheme."TEMP_ONK_SL_MTR" onksl on prot."IDCASE" = onksl."IDCASE" and prot."SL_ID" = onksl."SL_ID"
	 		join vcscheme."TEMP_SLUCH_MTR" sl on sl."IDCASE" = onksl."IDCASE" and sl."SL_ID" = onksl."SL_ID"
	 		join vcscheme."TEMP_Z_SLUCH_MTR" zsl on zsl."IDCASE" = sl."IDCASE"'));
	 	
	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
	 	values ('IES.T_SCHET_USL_ONK', 'сведений об услуге при лечении онкологического заболевания', (select count(*) from "vcscheme"."TEMP_ONK_USL_MTR"), 
	 	('select
	 		onkusl."SchetOnkUslID" as SchetUslOnkID,
	 		onksl."SchetSluchOnkID" as SchetSluchOnk,
	 		(select n013.n013treattypeid from ies.t_n013_treat_type n013 where n013.id_tlech = onkusl."USL_TIP"
													and n013.datebeg <= zsl."DATE_Z_2"
													and (n013.dateend is null or n013.dateend >= zsl."DATE_Z_2")
													limit 1 ) as USL_TIP,
			(select n014.n014surgtreatid from ies.t_n014_surg_treat n014 where n014.id_thir = onkusl."HIR_TIP"
													and n014.datebeg <= zsl."DATE_Z_2"
													and (n014.dateend is null or n014.dateend >= zsl."DATE_Z_2")
													limit 1) as HIR_TIP,
			(select n015.n015drugtherapylinesid from ies.t_n015_drug_therapy_lines n015 where n015.id_tlek_l = onkusl."LEK_TIP_L"
												  	and n015.datebeg <= zsl."DATE_Z_2"
												  	and (n015.dateend is null or n015.dateend >= zsl."DATE_Z_2")
												  	limit 1) as LEK_TIP_L,
			(select n016.n016drugtherapycyclesid from ies.t_n016_drug_therapy_cycles n016 where n016.id_tlek_v = onkusl."LEK_TIP_V"
													and n016.datebeg <= zsl."DATE_Z_2"
													and (n016.dateend is null or n016.dateend >= zsl."DATE_Z_2")
													limit 1) as LEK_TIP_V,
			(select n017.n017radiationtherapytypesid from ies.t_n017_radiation_therapy_types n017 where n017.id_tluch = onkusl."LUCH_TIP"
													and n017.datebeg <= zsl."DATE_Z_2"
													and (n017.dateend is null or n017.dateend >= zsl."DATE_Z_2") 
													limit 1) as LUCH_TIP,
	 		onkusl."PPTR" as PPTR 
	 		from vcscheme."TEMP_ONK_USL_MTR" onkusl
	 		join vcscheme."TEMP_ONK_SL_MTR" onksl on onksl."IDCASE" = onkusl."IDCASE" and onksl."SL_ID" = onkusl."SL_ID"
	 		join vcscheme."TEMP_SLUCH_MTR" sl on sl."IDCASE" = onksl."IDCASE" and sl."SL_ID" = onksl."SL_ID"
	 		join vcscheme."TEMP_Z_SLUCH_MTR" zsl on zsl."IDCASE" = sl."IDCASE"'));
	 	
 	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
	 	values ('IES.T_SCHET_USL_ONK_LEK_PR', ' сведений о лекарственных средствах', (select count(*) from "vcscheme"."TEMP_LEK_PR_DATE_INJ_MTR"), 
	 	('select gen_random_uuid() as ID,
	 		t0.IDCASE,
	 		t0.SL_ID,
	 		t0.REGNUM,
	 		t0.CODE_SH,
	 		left(t0.result, length(t0.result) -1) as result
	 		into temp_lek_pr
	 		from
	 		(
	 			select distinct 
					t2."IDCASE" :: varchar(36) as IDCASE,
					t2."SL_ID" :: varchar(36) as SL_ID,
					t2."REGNUM" ::  varchar(6) as REGNUM,
					t2."CODE_SH" ::  varchar(10) as CODE_SH,
					(
						select string_agg(t1."DATE_INJ" :: varchar(40), '','') as text
						from vcscheme."TEMP_LEK_PR_DATE_INJ_MTR" t1
						where t1."IDCASE" = t2."IDCASE" and t1."SL_ID" = t2."SL_ID"
							and t1."REGNUM" = t2."REGNUM" and t1."CODE_SH" = t2."CODE_SH"
					) as result 	
 				from vcscheme."TEMP_LEK_PR_DATE_INJ_MTR" t2
 			)t0;
 		
 		select 
 		lekpr.id as SchetUslOnkLekPrID,
 		aa."SchetOnkUslID" as SchetUslOnk,
 		(select n020.n020onklekpid from ies.t_n020_onk_lekp n020 where lekpr.REGNUM = n020.id_lekp
 																	and n020.datebeg <= zsl."DATE_Z_2"
																	and (n020.dateend is null or n020.dateend >= zsl."DATE_Z_2") 
																	limit 1) as REGNUM,
 		(select v024.v024dopkrid from ies.t_v024_dop_kr v024 where lekpr.CODE_SH = v024.iddkk
 																	and v024.datebeg <= zsl."DATE_Z_2"
																	and (v024.dateend is null or v024.dateend >= zsl."DATE_Z_2") 
																	limit 1) as V024DopKr,
 		lekpr.REGNUM as N020_R
 		from temp_lek_pr lekpr
 		join vcscheme."TEMP_SLUCH_MTR" sl on sl."IDCASE" = lekpr.IDCASE and sl."SL_ID" = lekpr.SL_ID
 		join vcscheme."TEMP_Z_SLUCH_MTR" zsl on zsl."IDCASE" = sl."IDCASE"
 		inner join lateral (
 			select * from vcscheme."TEMP_ONK_USL_MTR" onkusl where lekpr.IDCASE = onkusl."IDCASE"
 																and lekpr.SL_ID = onkusl."SL_ID"
 																and (onkusl."USL_TIP" = 2 or onkusl."USL_TIP" = 4)
 																limit 1
 		) as aa on true;'));
 	
 	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
	 	values ('IES.T_SCHET_USL_ONK_LEK_PR_DATE', ' сведений о датах лекарственных средств', (select count(*) from "vcscheme"."TEMP_LEK_PR_DATE_INJ_MTR"), 
	 	('select 
	 		gen_random_uuid() as SchetUslOnkLekPrDateID,
	 		lekpr.ID as SchetUslOnkLekPr,
	 		datelek."DATE_INJ" as DATE_INJ
	 		from vcscheme."TEMP_LEK_PR_DATE_INJ_MTR" datelek
	 		join temp_lek_pr lekpr on datelek."IDCASE" = lekpr.IDCASE and dateLek."SL_ID" = lekpr.SL_ID
			 and dateLek."REGNUM" = lekpr.REGNUM and dateLek."CODE_SH" = lekpr.CODE_SH;
			drop table temp_lek_pr;'));
		
	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
	 	values ('IES.T_KSG', ' информации о КСГ в случаях', (select count(*) from "vcscheme"."TEMP_KSG_KPG_MTR"),
	 	('select
	 		ksg."KsgID" as KsgID,
	 		sl."SchetSluchID" as SchetSluch,
	 		ksg."N_KSG" as N_KSG,
	 		ksg."VER_KSG" as VER_KSG,
	 		ksg."K_ZP" as K_ZP,
	 		ksg."KSG_PG" as KSG_PG,
	 		(select v026.v026kpgid from ies.t_v026_kpg v026 where v026.k_kpg = ksg."N_KPG"
	 											and v026.datebeg <= zsl."DATE_Z_2"
												and (v026.dateend is null or v026.dateend >= zsl."DATE_Z_2") 
												limit 1) as N_KPG,
	 		ksg."KOEF_UP" as KOEF_UP,
	 		ksg."BZTSZ" as BZTSZ,
	 		ksg."KOEF_D" as KOEF_D,
	 		ksg."KOEF_U" as KOEF_U,
	 		ksg."SL_K" as SL_K,
	 		ksg."IT_SL" as IT_SL
	 		from vcscheme."TEMP_KSG_KPG_MTR" ksg
	 		join vcscheme."TEMP_SLUCH_MTR" sl on sl."SL_ID" = ksg."SL_ID" and ksg."IDCASE" = sl."IDCASE"
	 		join vcscheme."TEMP_Z_SLUCH_MTR" zsl on zsl."IDCASE" = sl."IDCASE"'));
	
 	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
	 	values ('IES.T_KSG_CRIT', ' классификационного критерия CRIT(V024)', (select count(*) from "vcscheme"."TEMP_CRIT_MTR"),
	 	('select
	 		gen_random_uuid() as KsgCritID,
	 		ksg."KsgID" as Ksg,
			(select v024.v024dopkrid from ies.t_v024_dop_kr v024 where v024.iddkk = crit."CRIT"
													and v024.datebeg <= zsl."DATE_Z_2"
													and (v024.dateend is null or v024.dateend >= zsl."DATE_Z_2") 
													limit 1) as V024DopKr
	 		from vcscheme."TEMP_CRIT_MTR" crit
	 		join vcscheme."TEMP_KSG_KPG_MTR" ksg on ksg."SL_ID" = crit."SL_ID" and ksg."IDCASE" = crit."IDCASE"
	 		join vcscheme."TEMP_SLUCH_MTR" sl on sl."SL_ID" = ksg."SL_ID" and sl."IDCASE" = ksg."IDCASE"
			join vcscheme."TEMP_Z_SLUCH_MTR" zsl on zsl."IDCASE" = sl."IDCASE"'));
	
	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
	 	values ('IES.T_KSLP', ' информации о КСЛП в случаях', (select count(*) from "vcscheme"."TEMP_SL_KOEF_MTR"),
	 	('select
	 		gen_random_uuid() as KslpID,
	 		koef."Z_SL" as KOEF,
	 		koef."IDSL" as KOEF_TYPE,
	 		ksg."KsgID" as Ksg
	 		from vcscheme."TEMP_SL_KOEF_MTR" koef
	 		join vcscheme."TEMP_KSG_KPG_MTR" ksg on ksg."SL_ID" = koef."SL_ID" and ksg."IDCASE" = koef."IDCASE"
	 		join vcscheme."TEMP_SLUCH_MTR" sl on sl."SL_ID" = ksg."SL_ID" and sl."IDCASE" = ksg."IDCASE"'));
	 	
	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
	 	values ('IES.T_SCHET_USL', ' информации об услугах', (select count(*) from "vcscheme"."TEMP_USL_MTR"),
	 	('select 
	 		usl."SchetUslID" as SchetUslID,
	 		507 as type_,
	 		sl."SchetSluchID" as SchetSluch,
	 		usl."IDSERV" as IDSERV,
	 		usl."LPU" as LPU,
	 		usl."PROFIL" as PROFIL,
	 		usl."VID_VME" as VID_VME,
	 		usl."DET" as DET,
	 		usl."DATE_IN" as DATE_IN,
	 		usl."DATE_OUT" as DATE_OUT,
	 		usl."DS" as DS,
	 		usl."CODE_USL" as CODE_USL,
	 		usl."USL" as USL,
	 		usl."KOL_USL" as KOL_USL,
	 		usl."TARIF" as TARIF,
	 		usl."SUMV_USL" as SUMV_USL,
	 		(select v021.IDSPEC from ies.t_v021_med_spec v021 where v021.code_spec = usl."PRVS"
	 															and v021.datebeg <= zsl."DATE_Z_2"
																and (v021.dateend is null or v021.dateend >= zsl."DATE_Z_2") 
																limit 1) as PRVS3,
	 		usl."COMENTU" as COMENTU
	 		from vcscheme."TEMP_USL_MTR" usl
	 		join vcscheme."TEMP_SLUCH_MTR" sl on usl."SL_ID" = sl."SL_ID" and usl."IDCASE" = sl."IDCASE"
	 		join vcscheme."TEMP_Z_SLUCH_MTR" zsl on zsl."IDCASE" = sl."IDCASE"'));
	 	
 	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
	 	values ('IES.T_SCHET_SLUCH_SANK', ' информации об удержаниях', (select count(*) from "vcscheme"."TEMP_SANK_MTR"),
	 	('select
	 		sank."SchetSluchSankID" as SchetSluchSankID,
	 		zsl."SchetSluchAccomplishedID" as SchetSluchAccomplished,
	 		sank."S_CODE" as S_CODE,
	 		sank."S_SUM" as S_SUM,
	 		sank."S_TIP" as S_TIP,
	 		(select f014.f014denyreasonid from ies.t_f014_deny_reason f014 where f014.kod = sank."S_OSN"
						 													and f014.datebeg <= coalesce(sank."DATE_ACT", zsl."DATE_Z_2")
																			and (f014.dateend is null or f014.dateend >= coalesce(sank."DATE_ACT", zsl."DATE_Z_2")) 
																			limit 1) as S_OSN,
	 		sank."S_COM" as S_COM,
	 		sank."S_IST" as S_IST,
	 		sank."DATE_ACT" as DATE_ACT,
	 		sank."NUM_ACT" as NUM_ACT,
	 		(select f006.F006ControlTypeID from ies.t_f006_control_type f006 where f006.idvid = sank."S_TIP"
	 																			and f006.datebeg <= coalesce(sank."DATE_ACT", zsl."DATE_Z_2")
																				and (f006.dateend is null or f006.dateend >= coalesce(sank."DATE_ACT", zsl."DATE_Z_2")) 
																				limit 1) as S_TIP_F006,
	 		slid."SchetSluchID" as SchetSluch
	 		from vcscheme."TEMP_SANK_MTR" sank
	 		join vcscheme."TEMP_Z_SLUCH_MTR" zsl on zsl."IDCASE" = sank."IDCASE"
	 		left join lateral ( 
	 			select sl."SchetSluchID" from vcscheme."TEMP_SANK_SL_ID_MTR" slid
 				join vcscheme."TEMP_SLUCH_MTR" sl on slid."IDCASE" = sl."IDCASE"
 					and slid."SL_ID" = sl."SL_ID"
				where zsl."IDCASE" = slid."IDCASE"
					and sank."S_CODE" = slid."S_CODE"
					and sank."S_SUM" = slid."S_SUM"
				order by sl."SUM_M" desc
				limit 1
	 		) as slid on true' ));
 	
 	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery)
	 	values ('IES.T_SCHET_SLUCH_SANK_EXP', ' информации об экспертах', (select count(*) from "vcscheme"."TEMP_CODE_EXP_MTR"),
	 	('select 
	 		gen_random_uuid() as SchetSluchSankExpID,
	 		sank."SchetSluchSankID" as SchetSluchSank,
	 		codeexp."CODE_EXP" as F004Expert
	 		from vcscheme."TEMP_CODE_EXP_MTR" codeexp
	 		join vcscheme."TEMP_SANK_MTR" sank on sank."IDCASE" = codeexp."IDCASE" and sank."S_CODE" = codeexp."S_CODE"' ));
	 	
	SELECT to_jsonb(array_agg(t)) FROM temp_bulkheaders as t into "BulkHeaders";
end if;
end;
END;
$BODY$;
