drop function if exists sp_OmsSchetLoadAll_Bulk_3_2_LO;
drop procedure if exists sp_OmsSchetLoadAll_Bulk_3_2_LO;
create procedure sp_OmsSchetLoadAll_Bulk_3_2_LO
(
	"SchetID" uuid default null::uuid,
	"type" integer default null::integer,
	"Worker" uuid default null::uuid,
	inout "BulkHeaders" jsonb default null::jsonb,
	inout "ErrorFLK" text default ''
)	
language plpgsql
as $body$
declare
ActualDate timestamp := (select "DSCHET" from vcscheme."TEMP_SCHET" limit 1);
filename1 varchar(26) := (select "FILENAME" from vcscheme."TEMP_ZGLV"
			order by "FILENAME"
			limit 1);
sumsluch decimal := (select sum("SUM_M") from vcscheme."TEMP_SLUCH" limit 1);
sumsanks decimal := (select sum("SANK_IT") from vcscheme."TEMP_Z_SLUCH" limit 1);
sumschet decimal := (select sum("SUMMAV") from vcscheme."TEMP_SCHET" limit 1);
id uuid := "SchetID";

begin
	
	if "sumsluch" <> "sumschet" then
		raise exception 'ОШИБКА В ФАЙЛЕ СЧЕТ-РЕЕСТРА: Сумма на случаях не равна сумме на счете';
	end if;
	
	drop table if exists errors;
	drop table if exists temp_bulkheaders;
	
	-- 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)
	);
	
	insert into errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, ID_PAC, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
	select 'N_ZAP', t1."N_ZAP" as ZN_POL, (select s.nschet from ies.t_schet as s where s.schetid = id limit 1), 'ZAP', t1."N_ZAP", null as ID_PAC, null as IDCASE, null as SL_ID, null as IDSERV, '000F.00.0001'
	, 'Дубль N_ZAP'
	from vcscheme."TEMP_ZAP" as t0 
	join vcscheme."TEMP_Z_SLUCH" as t1  on t0."N_ZAP" = t1."N_ZAP"
	group by t1."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,  '000F.00.0002'
	, 'Дубль IDCASE'
	from vcscheme."TEMP_Z_SLUCH" 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, '000F.00.0003'
	, 'Дубль SL_ID в пределах законченного случая'
	from vcscheme."TEMP_SLUCH" 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, t1."IDCASE", t0."SL_ID", t0."IDSERV" as IDSERV, '000F.00.0004'
	, 'Дубль IDSERV'
	from vcscheme."TEMP_USL" as t0 
	join vcscheme."TEMP_Z_SLUCH" as t1  on t0."IDCASE" = t1."IDCASE"
	group by t1."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', null as ZN_POL ,(select s.nschet from ies.t_schet as s where s.schetid = id limit 1), 'SANK', t1."N_ZAP", null as ID_PAC, t0."IDCASE", null as SL_ID, null as IDSERV, '000F.00.0005'
	, 'Дубль S_CODE в пределах законченного случая'
	from vcscheme."TEMP_SANK" as t0 
	join vcscheme."TEMP_Z_SLUCH" as t1 on t0."IDCASE" = t1."IDCASE"
	group by t0."S_CODE", t0."IDCASE", t1."N_ZAP"
	having count(*) > 1;

	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

	update vcscheme."TEMP_ZGLV" set "ID" = '1';
	update vcscheme."TEMP_SCHET" set "ID" = '1';
	update vcscheme."TEMP_ZAP" set "SchetZapID" = gen_random_uuid();
	update vcscheme."TEMP_Z_SLUCH" set "SchetSluchAccomplishedID" = gen_random_uuid();
	update vcscheme."TEMP_SANK" set "SchetSluchSankID" = gen_random_uuid();
	update vcscheme."TEMP_SANK_SL_ID" set "SchetSluchSankSLID" = gen_random_uuid();
	update vcscheme."TEMP_SLUCH" set "SchetSluchID" = gen_random_uuid();
	update vcscheme."TEMP_ONK_SL" set "SchetSluchOnkID" = gen_random_uuid();
	update vcscheme."TEMP_ONK_USL" set "SchetOnkUslID" = gen_random_uuid();
	update vcscheme."TEMP_SANK" set "SchetSluchSankID" = gen_random_uuid();
	update vcscheme."TEMP_KSG_KPG" set "KsgID" = gen_random_uuid();
	update vcscheme."TEMP_USL" set "SchetUslID" = gen_random_uuid();

	-- 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"),
    (
    'select
	 "VERSION" as version
      ,"DATA" as data
      ,"CODE" as code
      ,"YEAR" as year
      ,"MONTH" as month
      ,"NSCHET" as nschet
      ,"DSCHET" as dschet
      ,"SUMMAV" as summap
      ,"COMENTS" as coments
      ,"SUMMAV" as summav
      ,case when :type = 693 then 0 else "SANK_MEE" end as sank_mee
      ,case when :type = 693 then 0 else "SANK_EKMP" end as sank_ekmp
      ,current_date as receiveddate
      ,(select current_timestamp(0)::timestamp without time zone) as receivedtime
      ,"FILENAME" as filename
      ,"CODE_MO" as code_mo
      ,"PLAT" as plat
      ,:Worker as worker
      ,:SchetID as schetid
      ,:type as type_
      ,0 as status
      ,0 as schetkind
      ,(select V016ID from ies.T_V016_DISPT where IDDT = "DISP" and DATEBEG<=(select "DSCHET" from vcscheme."TEMP_SCHET" limit 1) and (DATEEND is null or DATEEND>= (select "DSCHET" from vcscheme."TEMP_SCHET" limit 1))) as disp
      ,"SD_Z" as sd_z
      ,null as schetmain
      ,(select "FILENAME" from vcscheme."TEMP_PERS_ZGLV" limit 1) as filename_1
      ,(select COUNT(*) from vcscheme."TEMP_Z_SLUCH") as schetsluchaccomplishedcount
      from vcscheme."TEMP_SCHET" t1  
      JOIN vcscheme."TEMP_ZGLV" t2 on t2."ID" = t1."ID"'));
     
      insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery) values ('IES.T_SCHET_ZAP', 'информации о людях', (select count(*) from vcscheme."TEMP_ZAP"),
	   ( 'select 
		"SchetZapID" as SchetZapID
		,"SPOLIS" as spolis
		,"NPOLIS" as npolis
		,"NOVOR" as novor
		,zap."N_ZAP" as n_zap
		,(select f008.IDDOC from ies.T_F008_OMS_TYPE f008 WHERE f008.IDDOC = "VPOLIS" limit 1) as vpolis
		,cast(pac."ID_PAC" as varchar(36)) as id_pac
		,(select mo.SMOCOD from IES.T_F002_SMO mo WHERE mo.SMOCOD = pac."SMO" limit 1) as smo
		,"SMO_OGRN" as smo_ogrn
		,"SMO_OK" as smo_ok
		,"SMO_NAM" as smo_nam
		,"PR_NOV" as pr_nov
		,:SchetID as schet
		,698 as type_
		,"ST_OKATO" as st_okato
		,nullif("VNOV_D", 0) as vnov_d
		,"FAM" as fam
		,"IM" as im
		,"OT" as ot
		,"W" as w
		,"DR" as dr
		,"FAM_P" as fam_p
		,"IM_P" as im_p
		,"OT_P" as ot_p
		,"W_P" as w_p
		,"DR_P" as dr_p
		,"MR" as mr
		,"DOCTYPE" as doctype
		,"DOCSER" as docser
		,"DOCNUM" as docnum
		,"DOCDATE" as docdate
		,"DOCORG" as docorg
		,"SNILS" as snils
		,"OKATOG" as okatog
		,"OKATOP" as okatop
		,"COMENTP" as comentp
		,"MO" as attachmentmo
		,dost.rezult as dost
		,dost_p.rezult as dost_p
		,"INV" as inv
		,pac."MSE" as mse
		,"TEL" as tel
		,"ENP" as enp
		from vcscheme."TEMP_ZAP" as zap
		join vcscheme."TEMP_PACIENT" as pac on pac."N_ZAP" = zap."N_ZAP"
		left join vcscheme."TEMP_PERS" as pers on cast(pers."ID_PAC" as varchar(36)) = cast(pac."ID_PAC" as varchar(36))
		left join 
		(select t0.id_pac, Left(t0."rezult", Length(t0."rezult") - 1) as rezult
					from
					(
						select distinct cast("ID_PAC" as varchar(36)) as ID_PAC, 
							(
								select cast(t1."DOST" as varchar(1)) || '','' as text
								from vcscheme."TEMP_DOST" as t1
								where cast(t1."ID_PAC" as varchar(36)) = cast(ST2."ID_PAC" as varchar(36))
								order by cast(t1."ID_PAC" as varchar(36))
							) as rezult
						from vcscheme."TEMP_DOST" as ST2
					) as t0)
   		as dost on (cast(dost.id_pac as varchar(36)) = cast(pac."ID_PAC" as varchar(36)))
		left join 
		(select t0.id_pac, Left(t0.rezult, Length(t0.rezult)-1) as rezult
					from
					(
						select distinct cast("ID_PAC" as varchar(36)) as ID_PAC, 
							(
								select cast(t1."DOST_P" as varchar(1)) || '','' as text
								from vcscheme."TEMP_DOST_P" t1
								where cast(t1."ID_PAC" as varchar(36)) = cast(ST2."ID_PAC" as varchar(36))
								ORDER BY cast(t1."ID_PAC" as varchar(36))
							) rezult
						from vcscheme."TEMP_DOST_P" ST2
					) t0)
		as dost_p on (cast(dost_p.ID_PAC as varchar(36)) = cast(pac."ID_PAC" as varchar(36)))'));
	
	
	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery) values ('IES.T_SCHET_SLUCH_ACCOMPLISHED', 'информации о законченных случаях лечения', (select count(*) from vcscheme."TEMP_Z_SLUCH"),
		('select 
		"SchetSluchAccomplishedID" as schetsluchaccomplishedid
		, "IDCASE" as idcase
		,(case when (select "FILENAME" from vcscheme."TEMP_ZGLV"
			order by "FILENAME"
			limit 1) like ''D%''
					then 3 else "USL_OK" end) as usl_ok
		, "VIDPOM" as vidpom
		,(case when (select "FILENAME" from vcscheme."TEMP_ZGLV"
			order by "FILENAME"
			limit 1) like ''D%''
					then 3 else "FOR_POM" end) as for_pom
		, "NPR_MO" as npr_mo
		, "NPR_DATE" as npr_date
		, "LPU" as lpu
		, "DATE_Z_1" as date_z_1
		, "DATE_Z_2" as date_z_2
		, "KD_Z" as kd_z
		, "VNOV_M" as vnov_m
		, "RSLT" as rslt
		,(case when (select "FILENAME" from vcscheme."TEMP_ZGLV"
			order by "FILENAME"
			limit 1) like ''D%'' then 304 else "ISHOD" end) as ishod
		, "OS_SLUCH" os_sluch
		, "VB_P" as vb_p
		, "IDSP" as idsp
		, "SUMV" as sumv
		, "OPLATA" as oplata
		, (case when :type = 693 then "SUMV" else "SUMP" end) as sump	
		, (case when :type != 693 then 0 else "SUMP" end) as sump_r
		, (case when :type = 693 then 0 else "SANK_IT" end) as sank_it
		, t2."SchetZapID" as schetzap
		, "VBR" as vbr
		, "P_OTK" as p_otk
		, (select v017.IDDR from ies.T_V017_DISP_RESULT as v017 where t1."RSLT_D" = v017.IDDR limit 1) as rslt_d
		, "P_DISP2" as p_disp2
		FROM vcscheme."TEMP_Z_SLUCH" as t1 
		JOIN vcscheme."TEMP_ZAP" as t2 on t2."N_ZAP" = t1."N_ZAP"'));
	
	
	insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery) values ('IES.T_SCHET_SLUCH', 'информации о случаях лечения', (select count(*) from vcscheme."TEMP_SLUCH"),
		('select  t1."SchetSluchID" as schetsluchid
		,t1."SL_ID" as sl_id
		,t1."NHISTORY" as nhistory
		,t1."DATE_1" as date_1
		,t1."DATE_2" as date_2
		,t1."DS0" as ds0
		,t1."DS1" as ds1
		,t1."CODE_MES1" as code_mes1
		,t1."CODE_MES2" as code_mes2
		,t1."ED_COL" as ed_col
		,t1."TARIF" as tarif
		,t1."SUM_M" as sumv
		,t1."SUM_M" as sump
		,t1."COMENTSL" as comentsl
		,t1."PROFIL" as profil
		,(select V020BedProfileId from ies.T_V020_BED_PROFILE where  t1."PROFIL_K" = idk_pr
							 and DATEBEG<=t1."DATE_1"
							 order by DATEBEG desc, V020BedProfileId limit 1) as profil_k
		,(select V027CZabID from ies.T_V027_C_ZAB zab
							 where idcz=t1."C_ZAB" and DATEBEG<=t1."DATE_1"
							 and (DATEEND is null) order by DATEBEG desc, V027CZabID limit 1) as c_zab
		,(case when "VERS_SPEC" = ''V004'' then "PRVS" else null end) as prvs
		,(case when "VERS_SPEC" = ''V015'' then "PRVS" else null end) as prvs2
		,(case when "VERS_SPEC" = ''V021'' or "VERS_SPEC" is null then
							 (select IDSPEC from ies.T_V021_MED_SPEC as v021 where code_spec=t1."PRVS"
							 and DATEBEG<=(select "DSCHET" from vcscheme."TEMP_SCHET" limit 1)
							 and (DATEEND is null) order by DATEBEG desc, IDSPEC limit 1)
							 else null end) as prvs3
		,(case when 
			(select "FILENAME" from vcscheme."TEMP_ZGLV"
			order by "FILENAME"
			limit 1) like ''D%'' and (t1."PROFIL" = 68 or
							 (select v002.PRNAME from ies.T_V002_PROFILE as v002 where v002.IDPR = t1."PROFIL" limit 1)
							 like ''%детск%'') then 1
			when (select "FILENAME" from vcscheme."TEMP_ZGLV"
			order by "FILENAME"
			limit 1) like ''D%'' then 0
			else t1."DET" end) AS det
		,"PODR" as podr
		,"LPU_1" as lpu_1
		,"IDDOKT" as iddokt
		,700 as type_
		,t2."SchetZapID" as schetzap
		,(case when (select "FILENAME" from vcscheme."TEMP_ZGLV"
			order by "FILENAME"
			limit 1) like ''D%'' then 1
			else null end) AS disp
		,(case when nullif("USL_OK", 0) != 3 then null
			else (select V025KpcID from ies.T_V025_KPC
							 where idpc = "P_CEL"
							 and DATEBEG<=t1."DATE_1"
							 and (DATEEND is null) order by DATEBEG desc, V025KpcID limit 1) end) as p_cel
		, t1."VID_HMP" as vid_hmp
		, t5.V019VmpMethodID as metod_hmp
		, t1."KD" as kd
		, t1."WEI" as wei
		, t1."P_PER" as p_per
		, t1."TAL_D" as tal_d
		, t1."TAL_NUM" as tal_num
		, t1."TAL_P" as tal_p
		, t1."DS1_PR" as ds1_pr
		, t1."DN" as dn
		, t1."REAB" as reab
		, t1."PR_D_N" as pr_d_n
		, t3."SchetSluchAccomplishedID" as schetsluchaccomplished
		, t1."DS_ONK" as ds_onk
		,(CASE WHEN (select "FILENAME" from vcscheme."TEMP_ZGLV"
			order by "FILENAME"
			limit 1) like ''D%'' then ''V021'' else t1."VERS_SPEC" end) as vers_spec
		FROM vcscheme."TEMP_SLUCH" as t1    
		JOIN vcscheme."TEMP_ZAP" as t2 on t2."N_ZAP" = t1."N_ZAP"
		JOIN vcscheme."TEMP_Z_SLUCH" as t3 on t1."IDCASE" = t3."IDCASE"
		left join (select t0."IDCASE", t0."SL_ID", substring(t0."COMENTSL",1,5) as model
			from vcscheme."TEMP_SLUCH" as t0
			where (select "FILENAME" from vcscheme."TEMP_ZGLV"
				order by "FILENAME"
				limit 1) like ''T%'' and (select "YEAR" from vcscheme."TEMP_SCHET" limit 1) >= 2021
			and t0."COMENTSL" is not null) as t4
			on t4."IDCASE" = t1."IDCASE" and t4."SL_ID" = t1."SL_ID"
						LEFT JOIN LATERAL 
			(select V019VmpMethodId
			from ies.T_V019_VMP_METHOD as t100
			left join ies.T_V022_MODPAC as t200 on t200.V022ModpacID = t100.V022Modpac
			where
			t100.idhm = t1."METOD_HMP"
			and t100.DATEBEG<="DATE_Z_2" and (t100.DATEEND is null or t100.DATEEND>="DATE_Z_2")
			and t100.V022Modpac is null
			or (select "YEAR" from vcscheme."TEMP_SCHET" limit 1) <= 2020
			or (( right(''00000'' || t200.IDMPAC::varchar(5),5)) = nullif(t4.model,''''))limit 1) as t5 on true'));
		
		
		insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery) values ('IES.T_SCHET_SLUCH_LEK_PR', 'сведения о введенном лекарственном препарате', (select count(*) from vcscheme."TEMP_SLUCH_LEK_PR"),
		('SELECT 
		gen_random_uuid() as schetsluchlekprid
		,ts."SchetSluchID" as schetsluch
		,pr."DATA_INJ" as data_inj
		,(select v032.V032DrugGrRegID from ies.T_V032_DRUG_GR_REG as v032 where pr."CODE_SH" = v032.ScheDrugGrCd and v032.DATEBEG<=ts."DATE_2" and (v032.DATEEND is null or v032.DATEEND>=ts."DATE_2") limit 1) as code_sh
		,(select N020.N020OnkLekpID from ies.T_N020_ONK_LEKP as N020 where N020.ID_LEKP = pr."REGNUM" and N020.DATEBEG<=t3."DATE_Z_2" and (N020.DATEEND is null or N020.DATEEND>=ts."DATE_2") limit 1) as regnum
		,pr."COD_MARK" as cod_mark
		,(select eut.EhrUnitTypeID from ies.T_EHR_UNIT_TYPE as eut where pr."ED_IZM" = eut.ID::text limit 1) as ed_izm
		,pr."DOSE_INJ" as dose_inj
		,(select emi.EhrMetIntroID from ies.T_EHR_MET_INTRO as emi where pr."METHOD_INJ" = emi.ID::text limit 1) as method_inj
		,pr."COL_INJ" as col_inj
		FROM vcscheme."TEMP_SLUCH_LEK_PR" as pr
		JOIN vcscheme."TEMP_SLUCH" as ts on ts."SL_ID" = pr."SL_ID" and pr."IDCASE" = ts."IDCASE"
		join vcscheme."TEMP_Z_SLUCH" as t3 on (t3."IDCASE" = ts."IDCASE")'));	
	
	
		insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery) values ('IES.T_SCHET_SLUCH_SANK', 'информации об удержаниях', (select count(*) from vcscheme."TEMP_SANK"),
		('SELECT 
		nullif(t4."SchetSluchSankSLID", t0."SchetSluchSankID") as schetsluchsankid
		, t1."SchetSluchAccomplishedID" as schetsluchaccomplished
		, t0."S_CODE" s_code
		, (case when (rank() over(partition by t0."IDCASE", t0."S_CODE" order by t4."SL_ID")) = 1 then t0."S_SUM" else 0 end) as s_sum
		, t0."S_TIP" as s_tip
		,(select f014.F014DenyReasonId from ies.T_F014_DENY_REASON f014 where f014.Kod = t0."S_OSN"
		and f014.DATEBEG<=t1."DATE_Z_2" and (f014.DATEEND is null or DATEEND>=t1."DATE_Z_2") limit 1) as s_osn
		, t0."S_COM" as s_com
		, t0."S_IST" as s_ist
		, t0."DATE_ACT" as date_act
		, t0."NUM_ACT" as num_act
		,case when t4."SL_ID" is null then (select "SchetSluchID" from vcscheme."TEMP_SLUCH" as s where s."IDCASE" = t0."IDCASE" limit 1) 
			else (select "SchetSluchID" from vcscheme."TEMP_SLUCH" as s where s."IDCASE" = t4."IDCASE" and s."SL_ID" = t4."SL_ID" limit 1) end as schetsluch
		,(select f006.F006ControlTypeID from ies.T_F006_CONTROL_TYPE as f006 where f006.IDVID = t0."S_TIP"
			and f006.DATEBEG<=nullif(t0."DATE_ACT",t1."DATE_Z_2") and (f006.DATEEND is null or f006.DATEEND>=nullif(t0."DATE_ACT", t1."DATE_Z_2")) limit 1) as s_tip_f006
		FROM vcscheme."TEMP_SANK" as t0
		JOIN vcscheme."TEMP_Z_SLUCH" as t1 ON (t0."IDCASE" = t1."IDCASE")	
		LEFT JOIN vcscheme."TEMP_SANK_SL_ID" as t4 ON (t4."S_CODE" = t0."S_CODE" AND t4."IDCASE" = t0."IDCASE")'));
	
		insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery) values ('IES.T_SCHET_SLUCH_SANK_EXP', 'информации об удержаниях', (select count(*) from vcscheme."TEMP_CODE_EXP"),
		('SELECT gen_random_uuid() AS schetsluchsankexpid
		, nullif(t4."SchetSluchSankSLID", t1."SchetSluchSankID") AS schetsluchsank
		, t0."CODE_EXP" AS f004expert
		from vcscheme."TEMP_CODE_EXP" as t0 
		JOIN vcscheme."TEMP_SANK" as t1 ON t0."IDCASE" = t1."IDCASE"
						AND t0."S_CODE" = t1."S_CODE"
		LEFT JOIN LATERAL(SELECT * from vcscheme."TEMP_SANK_SL_ID" as t4 WHERE t4."S_CODE" = t1."S_CODE" AND t4."IDCASE" = t1."IDCASE"
		ORDER BY CASE t4."SL_ID" WHEN t1."SL_ID" THEN 1 ELSE 0 END DESC limit 1) as t4 on true'));


		insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery) values ('IES.T_KSG', 'информации о КСГ в случаях', (select count(*) from vcscheme."TEMP_KSG_KPG"),
		('SELECT 
		"KsgID" as ksgid
		, "KSG_PG" as ksg_pg
		, "SL_K" as sl_k
		, "IT_SL" as it_sl
		, ts."SchetSluchID" as schetsluch
		, (select V026KpgID from ies.T_V026_KPG where K_KPG = t1."N_KPG" limit 1) as n_kpg
		,  t1."N_KSG" as n_ksg
		, (select V024DopKrID from ies.T_V024_DOP_KR where IDDKK = t1."DKK2" limit 1) as dkk2
		, (select V024DopKrID from ies.T_V024_DOP_KR where IDDKK = t1."DKK1" limit 1) as dkk1
		, "VER_KSG" as ver_ksg
		, "KOEF_Z" as koef_z
		, "KOEF_UP" as koef_up
		, "BZTSZ" as bztsz
		, "KOEF_D" as koef_d
		, "KOEF_U" as koef_u
		, t1."K_ZP" as k_zp
		FROM vcscheme."TEMP_KSG_KPG" as t1 
		JOIN vcscheme."TEMP_SLUCH" as ts on ts."SL_ID" = t1."SL_ID" and t1."IDCASE" = ts."IDCASE"
		join vcscheme."TEMP_Z_SLUCH" as t3 on (t3."IDCASE" = ts."IDCASE")'));
	
	
		insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery) values ('IES.T_KSG_CRIT', 'Классификационный критерий (V024)', (select count(*) from vcscheme."TEMP_CRIT"),
		('SELECT 
		"KsgID" as ksg
		, gen_random_uuid() as ksgcritid
		, (select V024DopKrID from ies.T_V024_DOP_KR where IDDKK = t1."CRIT" limit 1) as v024dopkr
		FROM vcscheme."TEMP_CRIT" as t1
		join vcscheme."TEMP_KSG_KPG" as t2 on t2."SL_ID" = t1."SL_ID" and t1."IDCASE" = t2."IDCASE" 
		JOIN vcscheme."TEMP_SLUCH" as ts on ts."SL_ID" = t2."SL_ID" and t2."IDCASE" = ts."IDCASE"'));
	
	
		insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery) values ('IES.T_KSLP', 'информации о КСЛП в случаях', (select count(*) from vcscheme."TEMP_SL_KOEF"),
		('SELECT 
		gen_random_uuid() as kslpid
		, t1."Z_SL" as koef
		, t1."IDSL" as koef_type
		, t3."KsgID" as ksg
		FROM vcscheme."TEMP_SL_KOEF" as t1
		JOIN vcscheme."TEMP_SLUCH" as t2 on t2."SL_ID" = t1."SL_ID" and t1."IDCASE" = t2."IDCASE"
		JOIN vcscheme."TEMP_KSG_KPG" as t3 on t2."SL_ID" = t3."SL_ID" and t2."IDCASE" = t3."IDCASE"'));
	
		
		insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery) values ('IES.T_SCHET_USL', 'информации об услугах', (select count(*) from vcscheme."TEMP_USL"),
		('SELECT 
		 t1."SchetUslID" as schetuslid
		,t1."IDSERV" as idserv
		,t1."LPU" as lpu
		,t1."DATE_IN" as date_in
		,t1."DATE_OUT" as date_out
		,t1."DS" as ds
		,(CASE WHEN 
				(select "FILENAME" 
				from vcscheme."TEMP_ZGLV" as s limit 1) not like ''D%''
				THEN cast (t1."KOL_USL" as decimal(6,2)) ELSE 1 END) As kol_usl
		,cast(t1."TARIF" as decimal(15,2)) as tarif
		,cast(t1."SUMV_USL" as decimal(10,2)) as sumv_usl
		,t1."COMENTU" as comentu
		,t1."PROFIL" as profil
		,t1."NPL" as npl
		,t1."DET" as det
		,t1."LPU_1" as lpu_1
		,t1."PODR" as podr
		,t1."CODE_USL" as code_usl
		,t1."CODE_MD" as code_md
		,t2."SchetSluchID" as schetsluch
		,710 as type_
		,"VID_VME" as vid_vme
		,case when t2."VERS_SPEC" = ''V004'' THEN t1."PRVS" else null END as prvs
		,case when t2."VERS_SPEC" = ''V015'' THEN (select RECID from ies.T_V015_MEDSPEC where CODE = t1."PRVS" limit 1) ELSE NULL END as prvs2
		,case when t2."VERS_SPEC" = ''V021'' or t2."VERS_SPEC" is null THEN (select IDSPEC from ies.T_V021_MED_SPEC as v021 where CODE_SPEC=t1."PRVS" and DATEBEG<=((select "DSCHET" from vcscheme."TEMP_SCHET" limit 1)) and (DATEEND is null or DATEEND>=(select "DSCHET" from vcscheme."TEMP_SCHET" limit 1)) limit 1) ELSE null END as prvs3
		,t1."P_OTK" as p_otk
		FROM vcscheme."TEMP_USL" as t1
		JOIN vcscheme."TEMP_SLUCH" as t2 on t2."SL_ID" = t1."SL_ID" and t2."IDCASE" = t1."IDCASE"
		JOIN vcscheme."TEMP_Z_SLUCH" as t3 on t2."IDCASE" = t3."IDCASE"'));
	
	
				insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery) values ('IES.T_MR_USL_N', 'информации об мед.работниках выполнивших услугу', (select count(*) from vcscheme."TEMP_MR_USL_N"),
				('SELECT
				gen_random_uuid() as mruslnid
				,t1."SchetUslID" as schetusl
				,t0."MR_N" as mr_n
				,(select IDSPEC from ies.T_V021_MED_SPEC as v021 where CODE_SPEC=t0."PRVS" and DATEBEG<=t2."DATE_Z_2" and (DATEEND is null or DATEEND>=t2."DATE_Z_2") limit 1) as prvs
				,t0."CODE_MD" as code_md
				FROM vcscheme."TEMP_MR_USL_N" as t0
				JOIN vcscheme."TEMP_USL" as t1 ON t1."IDSERV" = t0."IDSERV" and t1."SL_ID" = t0."SL_ID" and t1."IDCASE" = t0."IDCASE"
				join vcscheme."TEMP_Z_SLUCH" as t2 on t1."IDCASE" = t2."IDCASE"'));
			
			
		
				insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery) values ('IES.T_SCHET_USL_MED_DEV', 'cведения о медицинских изделиях, имплантируемых в организм человека', (select count(*) from vcscheme."TEMP_MED_DEV"),
					('SELECT
					gen_random_uuid() as schetuslmeddevid
					,t1."SchetUslID" as schetusl
					,t0."DATE_MED" as date_med
					,t0."CODE_MEDDEV" as code_meddev
					,t0."NUMBER_SER" as number_ser
					FROM vcscheme."TEMP_MED_DEV" as t0
					JOIN vcscheme."TEMP_USL" as t1 ON t1."IDSERV" = t0."IDSERV" and t1."SL_ID" = t0."SL_ID" and t1."IDCASE" = t0."IDCASE"
					join vcscheme."TEMP_Z_SLUCH" as t2 on t1."IDCASE" = t2."IDCASE"'));
				
				
		insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery) values ('IES.T_SCHET_SLUCH_DS', 'информации об диагнозах', (select count(*)
					from
					(
						SELECT gen_random_uuid() as schetsluchdsid, t1."DS2" as mkb, t2."SchetSluchID" as schetsluch,0 as mkbtype
						FROM 
						vcscheme."TEMP_DS2" t1
						JOIN vcscheme."TEMP_SLUCH" as t2 on t2."IDCASE" = t1."IDCASE"
						union
						SELECT gen_random_uuid() as schesSluchdsid, t1."DS3" as mkb, t2."SchetSluchID" as schetsluch,1 as mkbtype
						FROM 
						vcscheme."TEMP_DS3" as t1
						JOIN vcscheme."TEMP_SLUCH" as t2 on t2."IDCASE" = t1."IDCASE"
					) as aa),
			('SELECT gen_random_uuid() as schetsluchdsid, t1."DS2" as mkb, t2."SchetSluchID" as schetsluch,0 as mkbtype, null as ds2_pr, null as  pr_ds2_n
			FROM 
			vcscheme."TEMP_DS2" as t1
			JOIN vcscheme."TEMP_SLUCH" as 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 as ds2_pr, null as pr_ds2_n
			FROM 
			vcscheme."TEMP_DS3" as t1
			JOIN vcscheme."TEMP_SLUCH" as 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, "DS2_PR"::text as ds2_pr, "PR_DS2_N"::text as pr_ds2_n
			FROM 
			vcscheme."TEMP_DS2_N" as t1
			JOIN vcscheme."TEMP_SLUCH" as 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_SLUCH_CONS', 'Сведения о проведении консилиума', (select count(*) from vcscheme."TEMP_CONS"),
			('SELECT 
			gen_random_uuid() as schetsluchconsid
			, t2."SchetSluchID" as schetsluch
			,(select n019.N019OnkConsID from ies.T_N019_ONK_CONS as N019 where N019.ID_CONS = "PR_CONS" limit 1) as n019onkcons
			, "DT_CONS" as dt_cons
			from vcscheme."TEMP_CONS" as t1 
			JOIN vcscheme."TEMP_SLUCH" as t2 on (t1."IDCASE" = t2."IDCASE" and t1."SL_ID" = t2."SL_ID")'));
		
		
				insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery) values ('IES.T_SCHET_SLUCH_NAZ', 'информации о назначениях', (select count(*) from vcscheme."TEMP_NAZ"),
			('SELECT  
			gen_random_uuid() as schetsluchnazid
			, tn."NAZ_N" as naz_n
			, tn."NAZ_R" as nazr
			, tn."NAZ_IDDOKT" as naz_iddokt					
			, (select IDSPEC from IES.T_V021_MED_SPEC where IDSPEC = tn."NAZ_SP" limit 1) as naz_sp
			, tn."NAZ_PMP" as naz_pmp
			, (select V029MetIsslID from IES.T_V029_MET_ISSL where IDMET = tn."NAZ_V" limit 1) as naz_v
			, (select V020BedProfileID from IES.T_V020_BED_PROFILE where IDK_PR = tn."NAZ_PK" limit 1) as naz_pk
			, ts."SchetSluchID" as schetsluch
			, tn."NAPR_DATE" as napr_date
			, tn."NAPR_MO" as napr_mo
			, tn."NAZ_USL" as naz_usl
			FROM vcscheme."TEMP_NAZ" as tn  
			JOIN vcscheme."TEMP_SLUCH" as ts on ts."SL_ID" = tn."SL_ID" and tn."IDCASE" = ts."IDCASE"'));
		
		
				insert into temp_bulkheaders(TableSchemaName, OperationName, RowsCount, SelectQuery) values ('IES.T_SCHET_USL_NAPR', 'направления', (select count(*) from vcscheme."TEMP_NAPR"),
			('SELECT 
			gen_random_uuid() as schetuslnaprid
			, t2."SchetSluchID" as schetsluch
			, "NAPR_DATE" as napr_date
			, "NAPR_V" as napr_v
			, "MET_ISSL" as met_issl
			, "NAPR_USL" as napr_usl
			, (case when "NAPR_MO" = t3."LPU" and "NAPR_MO" is not null then null 
							else "NAPR_MO"
							end) as napr_mo
			from vcscheme."TEMP_NAPR" as t1 
			JOIN vcscheme."TEMP_SLUCH" t2 on (t1."IDCASE" = t2."IDCASE" and t1."SL_ID" = t2."SL_ID") 
			JOIN vcscheme."TEMP_Z_SLUCH" t3 on (t2."IDCASE" = t3."IDCASE")'));
		
		SELECT to_jsonb(array_agg(t)) FROM temp_bulkheaders as t into "BulkHeaders";
end if;
end;
$body$;

