-- DROP PROCEDURE public.sp_frmoschetload_r00(in uuid, in text, in int4, in uuid, inout text);

CREATE OR REPLACE PROCEDURE public.sp_fmoschetload(IN "SchetID" uuid, IN xml text, IN type integer, IN "Worker" uuid, INOUT "ErrorFLK" text DEFAULT ''::text)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
	result text;
	v_type int;
	xml_doc XML;
	v_record record;
	v_schet uuid = gen_random_uuid();
BEGIN
	
xml_doc := xml::xml;

--PARSING SECTION START
--Element ZL_LIST has no fields
RAISE NOTICE 'Parse element /ZL_LIST/ZGLV %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_zglv;
create temp table temp_zglv as
SELECT 
	v_schet AS zglvid
	, version
	, data
	, filename
	, sd_z
FROM XMLTABLE ('/ZL_LIST/ZGLV' PASSING xml_doc COLUMNS
	 version varchar(5) path './VERSION'
	, data timestamp path './DATA'
	, filename varchar(36) path './FILENAME'
	, sd_z int path './SD_Z'
);

RAISE NOTICE 'Parse element /ZL_LIST/OTPR %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_otpr;
create temp table temp_otpr as
SELECT 
	gen_random_uuid() AS otprid
	, code_mo
	,(
		SELECT f003.mcod
		FROM ies.t_f003_mo f003
		JOIN ies.t_f032_trmo f032 ON f003.mcod = f032.mcod
		WHERE substring(f032.idmo, 1, 8) = code_mo limit 1
	) AS code_mo_f003
	, year
	, month
	, day
	, coments
FROM XMLTABLE ('/ZL_LIST/OTPR' PASSING xml_doc COLUMNS
	 code_mo varchar(8) path './CODE_MO'
	, year int path './YEAR'
	, month int path './MONTH'
	, day int path './DAY'
	, coments varchar(250) path './COMENTS'
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_zap;
create temp table temp_zap as
SELECT 
	gen_random_uuid() AS zapid
	, n_zap
	, zap_type
	, ''::varchar(20) AS dost
	, ''::varchar(20) AS dost_p
FROM XMLTABLE ('/ZL_LIST/ZAP' PASSING xml_doc COLUMNS
	 n_zap int path './N_ZAP'
	, zap_type varchar(5) path './TYPE'
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/PACIENT %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_pacient;
create temp table temp_pacient as
SELECT 
	gen_random_uuid() AS pacientid
	, n_zap
	, vpolis
	, enp
	, snpolis
	, smo_ok
	, smo
	, w
	, dr
	, novor
	, num_child
	, fam
	, im
	, ot
	, tel
	, mr
	, doctype
	, docser
	, docnum
	, docdate
	, docorg
	, snils
	, okatog
	, okatop
	, comentp
	, fam_p
	, im_p
	, ot_p
	, w_p
	, dr_p
FROM XMLTABLE ('/ZL_LIST/ZAP/PACIENT' PASSING xml_doc COLUMNS
	 vpolis int path './VPOLIS'
	, n_zap int path '../N_ZAP'
	, enp varchar(16) path './ENP'
	, snpolis varchar(39) path './SNPOLIS'
	, smo_ok varchar(5) path './SMO_OK'
	, smo varchar(5) path './SMO'
	, w int path './W'
	, dr timestamp path './DR'
	, novor int path './NOVOR'
	, num_child int path './NUM_CHILD'
	, fam varchar(40) path './FAM'
	, im varchar(40) path './IM'
	, ot varchar(40) path './OT'
	, tel varchar(100) path './TEL'
	, mr varchar(100) path './MR'
	, doctype varchar(2) path './DOCTYPE'
	, docser varchar(10) path './DOCSER'
	, docnum varchar(20) path './DOCNUM'
	, docdate timestamp path './DOCDATE'
	, docorg varchar(1000) path './DOCORG'
	, snils varchar(14) path './SNILS'
	, okatog varchar(11) path './OKATOG'
	, okatop varchar(11) path './OKATOP'
	, comentp varchar(250) path './COMENTP'
	, fam_p varchar(40) path './FAM_P'
	, im_p varchar(40) path './IM_P'
	, ot_p varchar(40) path './OT_P'
	, w_p int path './W_P'
	, dr_p timestamp path './DR_P'
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/NPR %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_npr;
create temp table temp_npr as
SELECT 
	gen_random_uuid() AS nprid
	, n_zap
	, idnpr
	, idnpr_mis
	, tal_d
	, tal_num
	, npr_mo
	,(
		SELECT f003.mcod
		FROM ies.t_f003_mo f003
		JOIN ies.t_f032_trmo f032 ON f003.mcod = f032.mcod
		WHERE substring(f032.idmo, 1, 8) = npr_mo limit 1
	) AS npr_mo_f003
	, povod
	, ds1
	, usl_ok
	, vidpom
	, for_pom
	, profil
	, gr_hmp
	, vid_hmp
	, resh_hmp
	, resh
	, podr
	, podr_name
	, profil_k
	, tal_p
FROM XMLTABLE ('/ZL_LIST/ZAP/NPR' PASSING xml_doc COLUMNS
	 idnpr varchar(36) path './IDNPR'
	, n_zap int path '../N_ZAP'
	, idnpr_mis varchar(36) path './IDNPR_MIS'
	, tal_d timestamp path './TAL_D'
	, tal_num varchar(30) path './TAL_NUM'
	, npr_mo varchar(8) path './NPR_MO'
	, povod int path './POVOD'
	, ds1 varchar(10) path './DS1'
	, usl_ok int path './USL_OK'
	, vidpom int path './VIDPOM'
	, for_pom int path './FOR_POM'
	, profil int path './PROFIL'
	, gr_hmp int path './GR_HMP'
	, vid_hmp varchar(12) path './VID_HMP'
	, resh_hmp int path './RESH_HMP'
	, resh int path './RESH'
	, podr varchar(12) path './PODR'
	, podr_name varchar(100) path './PODR_NAME'
	, profil_k int path './PROFIL_K'
	, tal_p timestamp path './TAL_P'
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_z_sl;
create temp table temp_z_sl as
SELECT 
	gen_random_uuid() AS z_slid
	, n_zap
	, idcase
	, idcase_link
	, osn_link
	, idcase_mis
	, adr_gar
	, adr_name
	, usl_ok
	, vidpom
	, for_pom
	, date_z_1
	, date_z_2
	, kd_z
	, CASE WHEN wei = '' THEN NULL ELSE wei::numeric(4,1) END
	, rslt
	, ishod
	, is_prerv
	, koef_prerv
	, koef_priv
	, koef_spec
	, sred_nfz
	, koef_d
	, sumv
FROM XMLTABLE ('/ZL_LIST/ZAP/Z_SL' PASSING xml_doc COLUMNS
	 idcase varchar(36) path './IDCASE'
	, n_zap int path '../N_ZAP'
	, idcase_link varchar(36) path './IDCASE_LINK'
	, osn_link varchar(3) path './OSN_LINK'
	, idcase_mis varchar(36) path './IDCASE_MIS'
	, adr_gar varchar(36) path './ADR_GAR'
	, adr_name varchar(300) path './ADR_NAME'
	, usl_ok int path './USL_OK'
	, vidpom int path './VIDPOM'
	, for_pom int path './FOR_POM'
	, date_z_1 timestamp path './DATE_Z_1'
	, date_z_2 timestamp path './DATE_Z_2'
	, kd_z int path './KD_Z'
	, wei TEXT path './WEI'
	, rslt int path './RSLT'
	, ishod int path './ISHOD'
	, is_prerv bool path './IS_PRERV'
	, koef_prerv numeric(7,5) path './KOEF_PRERV'
	, koef_priv numeric(7,5) path './KOEF_PRIV'
	, koef_spec numeric(7,5) path './KOEF_SPEC'
	, sred_nfz numeric(8,2) path './SRED_NFZ'
	, koef_d numeric(7,5) path './KOEF_D'
	, sumv numeric(17,2) path './SUMV'
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/PACIENT/DOST %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_dost;
create temp table temp_dost as
SELECT xmltable.*
FROM XMLTABLE ('/ZL_LIST/ZAP/PACIENT/DOST' PASSING xml_doc
COLUMNS
dostid uuid not null default gen_random_uuid()
, n_zap int PATH '../../N_ZAP' NOT NULL
, dost int PATH '.' 
);



RAISE NOTICE 'Parse element /ZL_LIST/ZAP/PACIENT/DOST_P %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_dost_p;
create temp table temp_dost_p as
SELECT xmltable.*
FROM XMLTABLE ('/ZL_LIST/ZAP/PACIENT/DOST_P' PASSING xml_doc
COLUMNS
dost_pid uuid not null default gen_random_uuid()
, n_zap int PATH '../../N_ZAP' NOT NULL
, dost_p int PATH '.' 
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/NPR/DS2 %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_npr_ds2;
create temp table temp_npr_ds2 as
SELECT xmltable.*
FROM XMLTABLE ('/ZL_LIST/ZAP/NPR/DS2' PASSING xml_doc
COLUMNS
ds2id uuid not null default gen_random_uuid()
, n_zap int PATH '../../N_ZAP' NOT NULL
, idnpr varchar(36) PATH '../IDNPR' 
, ds2 varchar(10) PATH '.' 
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/NPR/DS3 %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_npr_ds3;
create temp table temp_npr_ds3 as
SELECT xmltable.*
FROM XMLTABLE ('/ZL_LIST/ZAP/NPR/DS3' PASSING xml_doc
COLUMNS
ds3id uuid not null default gen_random_uuid()
, n_zap int PATH '../../N_ZAP' NOT NULL
, idnpr varchar(36) PATH '../IDNPR' 
, ds3 varchar(10) PATH '.' 
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_sl;
create temp table temp_sl as
SELECT 
	gen_random_uuid() AS slid
	, n_zap
	, idcase
	, sl_id
	, podr
	, podr_name
	, oid_frmo
	, profil
	, profil_k
	, nhistory
	, ds_gr
	, ds1
	, illact
	, illreason
	, c_zab
	, gr_hmp
	, vid_hmp
	, metod_hmp
	, model_hmp
	, lech_hmp
	, dzp
	, nfz
	, tarif
	, comentsl
FROM XMLTABLE ('/ZL_LIST/ZAP/Z_SL/SL' PASSING xml_doc COLUMNS
	 sl_id varchar(36) path './SL_ID'
	, n_zap int path '../../N_ZAP'
	, idcase varchar(36) path '../IDCASE'
	, podr varchar(12) path './PODR'
	, podr_name varchar(100) path './PODR_NAME'
	, oid_frmo varchar(100) path './OID_FRMO'
	, profil int path './PROFIL'
	, profil_k int path './PROFIL_K'
	, nhistory varchar(50) path './NHISTORY'
	, ds_gr varchar(10) path './DS_GR'
	, ds1 varchar(10) path './DS1'
	, illact int path './ILLACT'
	, illreason int path './ILLREASON'
	, c_zab int path './C_ZAB'
	, gr_hmp int path './GR_HMP'
	, vid_hmp varchar(12) path './VID_HMP'
	, metod_hmp int path './METOD_HMP'
	, model_hmp int path './MODEL_HMP'
	, lech_hmp int path './LECH_HMP'
	, dzp numeric(7,5) path './DZP'
	, nfz numeric(8,2) path './NFZ'
	, tarif numeric(17,2) path './TARIF'
	, comentsl varchar(250) path './COMENTSL'
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL/DS2 %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_ds2;
create temp table temp_ds2 as
SELECT xmltable.*
FROM XMLTABLE ('/ZL_LIST/ZAP/Z_SL/SL/DS2' PASSING xml_doc
COLUMNS
ds2id uuid not null default gen_random_uuid()
, n_zap int PATH '../../../N_ZAP' NOT NULL
, idcase varchar(36) PATH '../../IDCASE' NOT NULL
, sl_id varchar(36) PATH '../SL_ID' NOT NULL
, ds2 varchar(10) PATH '.' 
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL/DS3 %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_ds3;
create temp table temp_ds3 as
SELECT xmltable.*
FROM XMLTABLE ('/ZL_LIST/ZAP/Z_SL/SL/DS3' PASSING xml_doc
COLUMNS
ds3id uuid not null default gen_random_uuid()
, n_zap int PATH '../../../N_ZAP' NOT NULL
, idcase varchar(36) PATH '../../IDCASE' NOT NULL
, sl_id varchar(36) PATH '../SL_ID' NOT NULL
, ds3 varchar(10) PATH '.' 
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL/CONS %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_cons;
create temp table temp_cons as
SELECT 
	gen_random_uuid() AS consid
	, n_zap
	, idcase
	, sl_id
	, pr_cons
	, dt_cons
FROM XMLTABLE ('/ZL_LIST/ZAP/Z_SL/SL/CONS' PASSING xml_doc COLUMNS
	 pr_cons int path './PR_CONS'
	, n_zap int path '../../../N_ZAP'
	, idcase varchar(36) path '../../IDCASE'
	, sl_id varchar(36) path '../SL_ID'
	, dt_cons timestamp path './DT_CONS'
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL/ONK_SL %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_onk_sl;
create temp table temp_onk_sl as
SELECT 
	gen_random_uuid() AS onk_slid
	, n_zap
	, idcase
	, sl_id
	, ds1_t
	, stad
	, onk_t
	, onk_n
	, onk_m
	, mtstz
	, sod
	, k_fr
	, wei
	, hei
	, bsa
FROM XMLTABLE ('/ZL_LIST/ZAP/Z_SL/SL/ONK_SL' PASSING xml_doc COLUMNS
	 ds1_t int path './DS1_T'
	, n_zap int path '../../../N_ZAP'
	, idcase varchar(36) path '../../IDCASE'
	, sl_id varchar(36) path '../SL_ID'
	, stad int path './STAD'
	, onk_t int path './ONK_T'
	, onk_n int path './ONK_N'
	, onk_m int path './ONK_M'
	, mtstz int path './MTSTZ'
	, sod numeric(6,2) path './SOD'
	, k_fr int path './K_FR'
	, wei numeric(4,1) path './WEI'
	, hei int path './HEI'
	, bsa numeric(3,2) path './BSA'
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL/KSG_KPG %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_ksg_kpg;
create temp table temp_ksg_kpg as
SELECT 
	gen_random_uuid() AS ksg_kpgid
	, n_zap
	, idcase
	, sl_id
	, n_ksg
	, gr
	, ver_ksg
	, koef_z
	, sl_k
	, it_sl
	, not_onk_sl
FROM XMLTABLE ('/ZL_LIST/ZAP/Z_SL/SL/KSG_KPG' PASSING xml_doc COLUMNS
	 n_ksg varchar(20) path './N_KSG'
	, n_zap int path '../../../N_ZAP'
	, idcase varchar(36) path '../../IDCASE'
	, sl_id varchar(36) path '../SL_ID'
	, gr varchar(10) path './GR'
	, ver_ksg int path './VER_KSG'
	, koef_z numeric(7,5) path './KOEF_Z'
	, sl_k bool path './SL_K'
	, it_sl numeric(6,5) path './IT_SL'
	, not_onk_sl bool path './NOT_ONK_SL'
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL/LEK_PR %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_sl_lek_pr;
create temp table temp_sl_lek_pr as
SELECT 
	gen_random_uuid() AS lek_prid
	, n_zap
	, idcase
	, sl_id
	, data_inj
	, code_sh
	, regnum
	, cod_mark
	, lek_pr_xmls
FROM XMLTABLE ('/ZL_LIST/ZAP/Z_SL/SL/LEK_PR' PASSING xml_doc COLUMNS
	 data_inj timestamp path './DATA_INJ'
	, n_zap int path '../../../N_ZAP'
	, idcase varchar(36) path '../../IDCASE'
	, sl_id varchar(36) path '../SL_ID'
	, code_sh varchar(10) path './CODE_SH'
	, regnum varchar(10) path './REGNUM'
	, cod_mark varchar(200) path './COD_MARK'
	, lek_pr_xmls xml PATH '.' NULL
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL/USL %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_usl;
create temp table temp_usl as
SELECT 
	gen_random_uuid() AS uslid
	, n_zap
	, idcase
	, sl_id
	, idserv
	, vid_vme
	, comentu
FROM XMLTABLE ('/ZL_LIST/ZAP/Z_SL/SL/USL' PASSING xml_doc COLUMNS
	 idserv varchar(36) path './IDSERV'
	, n_zap int path '../../../N_ZAP'
	, idcase varchar(36) path '../../IDCASE'
	, sl_id varchar(36) path '../SL_ID'
	, vid_vme varchar(15) path './VID_VME'
	, comentu varchar(250) path './COMENTU'
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL/KSG_KPG/CRIT %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_crit;
create temp table temp_crit as
SELECT xmltable.*
FROM XMLTABLE ('/ZL_LIST/ZAP/Z_SL/SL/KSG_KPG/CRIT' PASSING xml_doc
COLUMNS
critid uuid not null default gen_random_uuid()
, n_zap int PATH '../../../../N_ZAP' NOT NULL
, idcase varchar(36) PATH '../../../IDCASE' NOT NULL
, sl_id varchar(36) PATH '../../SL_ID' NOT NULL
, crit varchar(10) PATH '.' 
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL/KSG_KPG/SL_KOEF %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_sl_koef;
create temp table temp_sl_koef as
SELECT 
	gen_random_uuid() AS sl_koefid
	, n_zap
	, idcase
	, sl_id
	, idsl
	, z_sl
FROM XMLTABLE ('/ZL_LIST/ZAP/Z_SL/SL/KSG_KPG/SL_KOEF' PASSING xml_doc COLUMNS
	 idsl int path './IDSL'
	, n_zap int path '../../../../N_ZAP'
	, idcase varchar(36) path '../../../IDCASE'
	, sl_id varchar(36) path '../../SL_ID'
	, z_sl numeric(6,5) path './Z_SL'
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL/LEK_PR/LEK_DOSE %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_lek_dose;
create temp table temp_lek_dose as
SELECT 
	gen_random_uuid() AS lek_doseid
	, n_zap
	, idcase
	, sl_id
	, ed_izm
	, dose_inj
	, method_inj
	, col_inj
	, lek_prid
FROM temp_sl_lek_pr,
XMLTABLE ('/LEK_PR/LEK_DOSE' PASSING lek_pr_xmls COLUMNS
	 ed_izm int path './ED_IZM'	
	, dose_inj numeric(7,2) path './DOSE_INJ'
	, method_inj int path './METHOD_INJ'
	, col_inj int path './COL_INJ'
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL/USL/MED_DEV %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_med_dev;
create temp table temp_med_dev as
SELECT 
	gen_random_uuid() AS med_devid
	, n_zap
	, idcase
	, sl_id
	, idserv
	, date_med
	, code_meddev
	, number_ser
FROM XMLTABLE ('/ZL_LIST/ZAP/Z_SL/SL/USL/MED_DEV' PASSING xml_doc COLUMNS
	 date_med timestamp path './DATE_MED'
	, n_zap int path '../../../../N_ZAP'
	, idcase varchar(36) path '../../../IDCASE'
	, sl_id varchar(36) path '../../SL_ID'
	, idserv varchar(36) path '../IDSERV'
	, code_meddev int path './CODE_MEDDEV'
	, number_ser varchar(100) path './NUMBER_SER'
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL/ONK_SL/B_DIAG %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_b_diag;
create temp table temp_b_diag as
SELECT 
	gen_random_uuid() AS b_diagid
	, n_zap
	, idcase
	, sl_id
	, diag_date
	, diag_tip
	, diag_code
	, diag_rslt
	, rec_rslt
FROM XMLTABLE ('/ZL_LIST/ZAP/Z_SL/SL/ONK_SL/B_DIAG' PASSING xml_doc COLUMNS
	 diag_date timestamp path './DIAG_DATE'
	, n_zap int path '../../../../N_ZAP'
	, idcase varchar(36) path '../../../IDCASE'
	, sl_id varchar(36) path '../../SL_ID'
	, diag_tip int path './DIAG_TIP'
	, diag_code int path './DIAG_CODE'
	, diag_rslt int path './DIAG_RSLT'
	, rec_rslt int path './REC_RSLT'
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL/ONK_SL/B_PROT %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_b_prot;
create temp table temp_b_prot as
SELECT 
	gen_random_uuid() AS b_protid
	, n_zap
	, idcase
	, sl_id
	, prot
	, d_prot
FROM XMLTABLE ('/ZL_LIST/ZAP/Z_SL/SL/ONK_SL/B_PROT' PASSING xml_doc COLUMNS
	 prot int path './PROT'
	, n_zap int path '../../../../N_ZAP'
	, idcase varchar(36) path '../../../IDCASE'
	, sl_id varchar(36) path '../../SL_ID'
	, d_prot timestamp path './D_PROT'
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL/ONK_SL/ONK_USL %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_onk_usl;
create temp table temp_onk_usl as
SELECT 
	gen_random_uuid() AS onk_uslid
	, n_zap
	, idcase
	, sl_id
	, usl_tip
	, hir_tip
	, lek_tip_l
	, lek_tip_v
	, pptr
	, luch_tip
	, onk_usl_xmls
FROM XMLTABLE ('/ZL_LIST/ZAP/Z_SL/SL/ONK_SL/ONK_USL' PASSING xml_doc COLUMNS
	 usl_tip int path './USL_TIP'
	, n_zap int path '../../../../N_ZAP'
	, idcase varchar(36) path '../../../IDCASE'
	, sl_id varchar(36) path '../../SL_ID'
	, hir_tip int path './HIR_TIP'
	, lek_tip_l int path './LEK_TIP_L'
	, lek_tip_v int path './LEK_TIP_V'
	, pptr int path './PPTR'
	, luch_tip int path './LUCH_TIP'
	, onk_usl_xmls xml PATH '.' NULL
);

RAISE NOTICE 'Parse element /ZL_LIST/ZAP/Z_SL/SL/ONK_SL/ONK_USL/LEK_PR %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists temp_onk_usl_lek_pr;
create temp table temp_onk_usl_lek_pr as
SELECT 
	gen_random_uuid() AS lek_prid
	, n_zap
	, idcase
	, sl_id
	, regnum 
	, code_sh
	, date_inj
	, dose_inj
	, onk_uslid
FROM temp_onk_usl,
XMLTABLE ('/ONK_USL/LEK_PR' PASSING onk_usl_xmls COLUMNS
	 regnum  varchar(6) path './REGNUM '
	, code_sh varchar(10) path './CODE_SH'
	, date_inj timestamp path './DATE_INJ'
	, dose_inj varchar(20) path './DOSE_INJ'	
);
--PARSING SECTION END

--CALC SECTION START
RAISE NOTICE 'GET FMO_SCHET ENTITYID %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
SELECT entityid INTO v_type FROM vclib.t_entities te WHERE te.entname = 'FMO_SCHET';

RAISE NOTICE 'DELETE OLD SCHET %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
for v_record in select schetid
       from ies.t_schet s
       WHERE s.filename = (SELECT filename FROM temp_zglv LIMIT 1)       
loop
	RAISE NOTICE 'drop old schet with id: % | %', coalesce(v_record.schetid::varchar(36), 'null'), to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
	PERFORM public.sp_dropschet(v_record.schetid, v_type, 1);
end loop;

RAISE NOTICE 'Собираем dost, dost_p %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');

drop table if exists zap_dost;
create temp table zap_dost AS
SELECT n_zap, string_agg(dost::varchar(1), ', ') AS dost
FROM temp_dost GROUP BY n_zap;

drop table if exists zap_dost_p;
create temp table zap_dost_p AS
SELECT n_zap, string_agg(dost_p::varchar(1), ', ') AS dost_p
FROM temp_dost_p GROUP BY n_zap;

UPDATE temp_zap t0
SET dost = t1.dost
FROM zap_dost t1
WHERE t0.n_zap = t1.n_zap;

UPDATE temp_zap t0
SET dost_p = t1.dost_p
FROM zap_dost_p t1
WHERE t0.n_zap = t1.n_zap;

--CALC SECTION END

RAISE NOTICE 'FLK START %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
drop table if exists errors;
create temp table errors
(
oshib varchar(100),
im_pol varchar(100),
zn_pol varchar(100),
nschet varchar(100),
bas_el varchar(100),
n_zap int,
id_pac varchar(36),
idcase varchar(36),
sl_id varchar(36),
idserv varchar(36),
comment varchar(250)
);
	
--q015 begin
--q015 preamb begin
--q015 rules end
--q015 end
--custom rules begin
insert into errors (
	im_pol
	,zn_pol
	,nschet
	,bas_el
	,n_zap
	,id_pac
	,idcase
	,sl_id
	,idserv
	,oshib
	,comment
	)
select 'code_mo'
	,t0.code_mo as zn_pol
	,null as nschet
	,'otpr'
	,null as n_zap
	,null as id_pac
	,null as idcase
	,null as sl_id
	,null as idserv
	,'902'
	,'Значение не найдено в справочнике f003'
from temp_otpr t0
where t0.code_mo_f003 IS NULL;

insert into errors (
	im_pol
	,zn_pol
	,nschet
	,bas_el
	,n_zap
	,id_pac
	,idcase
	,sl_id
	,idserv
	,oshib
	,comment
	)
select 'npr_mo'
	,t0.npr_mo as zn_pol
	,null as nschet
	,'npr'
	,t0.n_zap as n_zap
	,null as id_pac
	,null as idcase
	,null as sl_id
	,null as idserv
	,'902'
	,'Значение не найдено в справочнике f003'
from temp_npr t0
where t0.npr_mo_f003 is NULL;
--custom rules end	
RAISE NOTICE 'FLK END %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');





--INSERT SECTION START
--Element ZL_LIST has no fields
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 as "OSHIB"
, im_pol as "IM_POL"
, zn_pol as "ZN_POL"
, nschet as "NSCHET"
, bas_el as "BAS_EL"
, n_zap as "N_ZAP"
, id_pac as "ID_PAC"
, idcase as "IDCASE"
, sl_id as "SL_ID"
, idserv as "IDSERV"
, comment as "COMMENT"
	))))::text from errors
	into "ErrorFLK";
ELSE

RAISE NOTICE 'Insert element /ZL_LIST/ZGLV %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_schet(
	 schetid
	 , version
	 , data
	 , filename
	 , sd_z
 	 , code_mo
	 , year
	 , month
	 , day
	 , coments
	 , type_ 
	 , dschet
	 , summav 
	 ,receiveddate
	 ,receivedtime
	 ,status 
	 ,schetkind 
	)
select	 v_schet
	 , t0.version
	 , t0.data
	 , t0.filename
	 , t0.sd_z
	 , t1.code_mo_f003
	 , t1.year
	 , t1.month
	 , t1.day
	 , t1.coments
	 , v_type
	 , current_date
	 , COALESCE((SELECT sum(sumv) FROM temp_z_sl),0)
	 , NOW()
	 , NOW()
	 , 0
	 , 0
from temp_zglv t0
join temp_otpr t1 ON 1 = 1;

RAISE NOTICE 'Insert element /ZL_LIST/ZAP %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_schet_zap(
	 schetzapid
	 , n_zap
	 , fmo_type
	 , vpolis
	 , enp
	 , snpolis
	 , smo_ok
	 , smo
	 , w
	 , dr
	 , novor
	 , num_child
	 , fam
	 , im
	 , ot
	 , tel
	 , mr
	 , doctype
	 , docser
	 , docnum
	 , docdate
	 , docorg
	 , snils
	 , okatog
	 , okatop
	 , comentp
	 , fam_p
	 , im_p
	 , ot_p
	 , w_p
	 , dr_p
	 , schet
	 , type_
 	 , dost
	 , dost_p
	)
select	 zapid
	 , t0.n_zap
	 , t0.zap_type
	 , t1.vpolis
	 , t1.enp
	 , t1.snpolis
	 , t1.smo_ok
	 , t1.smo
	 , t1.w
	 , t1.dr
	 , t1.novor
	 , t1.num_child
	 , t1.fam
	 , t1.im
	 , t1.ot
	 , t1.tel
	 , t1.mr
	 , t1.doctype
	 , t1.docser
	 , t1.docnum
	 , t1.docdate
	 , t1.docorg
	 , t1.snils
	 , t1.okatog
	 , t1.okatop
	 , t1.comentp
	 , t1.fam_p
	 , t1.im_p
	 , t1.ot_p
	 , t1.w_p
	 , t1.dr_p
	 , v_schet
	 , (SELECT entityid FROM vclib.t_entities WHERE entname='FMO_SCHET_ZAP'  LIMIT 1)
 	 , t0.dost
	 , t0.dost_p
from temp_zap t0
JOIN temp_pacient t1 ON t0.n_zap = t1.n_zap;



RAISE NOTICE 'Insert element /ZL_LIST/ZAP/NPR %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_fmo_npr(
	 fmonprid
	 , idnpr
	 , idnpr_mis
	 , tal_d
	 , tal_num
	 , npr_mo
	 , povod
	 , ds1
	 , usl_ok
	 , vidpom
	 , for_pom
	 , profil
	 , gr_hmp
	 , vid_hmp
	 , resh_hmp
	 , resh
	 , podr
	 , podr_name
	 , profil_k
	 , tal_p
	 , schetzap
	)
select	 nprid
	 , t0.idnpr
	 , t0.idnpr_mis
	 , t0.tal_d
	 , t0.tal_num	 
	 , t0.npr_mo_f003
	 , (
		SELECT fmopovodid
		FROM ies.t_fmo_povod p
		WHERE p.code = t0.povod
			AND t0.povod IS NOT NULL
		LIMIT 1
		)	 
	 , t0.ds1
	 , t0.usl_ok
	 , t0.vidpom
	 , t0.for_pom
	 , t0.profil
	 , t0.gr_hmp
	 , t0.vid_hmp
	 ,(
		select rhmp.fmoreshhmpid
		from ies.t_fmo_resh_hmp rhmp
		where rhmp.code = t0.resh_hmp
		LIMIT 1
	  ) as resh_hmp	 
	 ,(
		select rsmp.fmoreshsmpid
		from ies.t_fmo_resh_smp rsmp
		where rsmp.code = t0.resh
		LIMIT 1
		) as resh
	 , t0.podr
	 , t0.podr_name
	 ,(
		SELECT v020bedprofileid		
		FROM ies.t_v020_bed_profile
		WHERE t0.profil_k = idk_pr
			AND datebeg <= t0.tal_d
			AND (
				dateend IS NULL
				OR dateend >= t0.tal_d
				)
		LIMIT 1
	 ) AS profil_k
	 , t0.tal_p
	 , t1.zapid
from temp_npr t0
JOIN temp_zap t1 ON t0.n_zap = t1.n_zap;


RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_schet_sluch_accomplished(
	 schetsluchaccomplishedid
	 , idcase
	 , idcase_link
	 , osn_link
	 , idcase_mis
	 , adr_gar
	 , adr_name
	 , usl_ok
	 , vidpom
	 , for_pom
	 , date_z_1
	 , date_z_2
	 , kd_z
	 , wei
	 , rslt
	 , ishod
	 , is_prerv
	 , koef_prerv
	 , koef_priv
	 , koef_spec
	 , sred_nfz
	 , koef_d
	 , sumv
	 , type_
	 , schetzap
	)
select	 z_slid
	 , t0.idcase
	 , t0.idcase_link
	 , t0.osn_link
	 , t0.idcase_mis
	 , t0.adr_gar
	 , t0.adr_name
	 , t0.usl_ok
	 , t0.vidpom
	 , t0.for_pom
	 , t0.date_z_1
	 , t0.date_z_2
	 , t0.kd_z
	 , t0.wei
	 , t0.rslt
	 , t0.ishod
	 , t0.is_prerv
	 , t0.koef_prerv
	 , t0.koef_priv
	 , t0.koef_spec
	 , t0.sred_nfz
	 , t0.koef_d
	 , t0.sumv
	 , (SELECT entityid FROM vclib.t_entities WHERE entname='FMO_SCHET_SLUCH_ACCOMPLISHED'  LIMIT 1)
	 , t1.zapid
from temp_z_sl t0
JOIN temp_zap t1 ON t0.n_zap = t1.n_zap
;


RAISE NOTICE 'Insert element /ZL_LIST/ZAP/NPR/DS2 %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');

insert into ies.t_fmo_ds (
	fmodsid
	,ds
	,dstype
	,fmonpr
	)
select gen_random_uuid()
	,t0.ds2
	,0
	,t1.nprid
from temp_npr_ds2 t0
join temp_npr t1 on t0.idnpr = t1.idnpr
	and t0.n_zap = t1.n_zap;

insert into ies.t_fmo_ds (
	fmodsid
	,ds
	,dstype
	,fmonpr
	)
select gen_random_uuid()
	,t0.ds3
	,1
	,t1.nprid
from temp_npr_ds3 t0
join temp_npr t1 on t0.idnpr = t1.idnpr
	and t0.n_zap = t1.n_zap;



RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL/SL %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_schet_sluch(
	 schetsluchid
	 , sl_id
	 , podr
	 , podr_name
	 , oid_frmo
	 , profil
	 , profil_k
	 , nhistory
	 , ds_gr
	 , ds1
	 , illact
	 , illreason
	 , c_zab
	 , gr_hmp
	 , vid_hmp
	 , metod_hmp
	 , model_hmp
	 , lech_hmp
	 , dzp
	 , nfz
	 , tarif
	 , comentsl
	 , schetsluchaccomplished 
	 , schetzap 
	 , type_
	 , date_1 
	 , date_2
	 , sumv
	)
select	 slid
	 , t0.sl_id
	 , t0.podr
	 , t0.podr_name
	 , t0.oid_frmo
	 , t0.profil
	 ,(
		select v020bedprofileid
		from ies.t_v020_bed_profile
		where profil_k = idk_pr
			and datebeg <= t1.date_z_2
			and (
				dateend is null
				or dateend >= t1.date_z_2
				)
		) as profil_k
	 , t0.nhistory
	 , t0.ds_gr
	 , t0.ds1
	 , t0.illact
	 , t0.illreason
	 ,(
		select v027czabid
		from ies.t_v027_c_zab zab
		where idcz = t0.c_zab
			and datebeg <= t1.date_z_2
			and (
				dateend is null
				or dateend >= t1.date_z_2
				)
		LIMIT 1
		) as c_zab
	 , t0.gr_hmp
	 , t0.vid_hmp
	 ,(
		select v019vmpmethodid
		from ies.t_v019_vmp_method v019
		where v019.idhm = t0.metod_hmp
		order by coalesce(v019.dateend, current_date) desc
			,v019.datebeg DESC
		LIMIT 1
		) as metod_hmp
	,(
		select v022.v022modpacid
		from ies.t_v022_modpac v022
		where v022.idmpac = t0.model_hmp
			and datebeg <= date_z_2
			and (
				dateend is null
				or dateend >= date_z_2
				)
		LIMIT 1
		) as model_hmp
	 , t0.lech_hmp
	 , t0.dzp
	 , t0.nfz
	 , t0.tarif
	 , t0.comentsl
	 , t1.z_slid
	 , t2.zapid
	 , (SELECT entityid FROM vclib.t_entities WHERE entname='FMO_SCHET_SLUCH' LIMIT 1)
	 , DATE_Z_1
	 , DATE_Z_2
	 , t1.sumv
from temp_sl t0
JOIN temp_z_sl t1 ON t0.n_zap = t1.n_zap AND t0.idcase = t1.idcase
JOIN temp_zap t2 ON t0.n_zap = t2.n_zap
;


RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL/SL/DS2 %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');

insert into ies.t_schet_sluch_ds (
	schetsluchdsid
	,schetsluch
	,mkb
	,mkbtype
	)
select 
	t1.ds2id
	,t2.slid
	,t1.ds2
	,0
from temp_ds2 t1
join temp_sl t2 on t2.sl_id = t1.sl_id
	and t1.idcase = t2.idcase
	and t1.n_zap = t2.n_zap;

insert into ies.t_schet_sluch_ds (
	schetsluchdsid
	,schetsluch
	,mkb
	,mkbtype
	)
select 
	t1.ds3id
	,t2.slid
	,t1.ds3
	,1
from temp_ds3 t1
join temp_sl t2 on t2.sl_id = t1.sl_id
	and t1.idcase = t2.idcase
	and t1.n_zap = t2.n_zap;


RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL/SL/CONS %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_schet_sluch_cons(
	 schetsluchconsid
	 , n019onkcons
	 , dt_cons
	 , schetsluch
	)
select	 consid
	 ,(
		SELECT n019.n019onkconsid
		from ies.t_n019_onk_cons n019
		where n019.id_cons = t0.pr_cons
		LIMIT 1
		)
	 , t0.dt_cons
	 , t1.slid
from temp_cons t0
JOIN temp_sl t1 ON t0.sl_id = t1.sl_id 
AND t0.idcase = t1.idcase
AND t0.n_zap = t1.n_zap;


RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL/SL/ONK_SL %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_schet_sluch_onk(
	 schetsluchonkid
	 , ds1_t
	 , stad
	 , onk_t
	 , onk_n
	 , onk_m
	 , mtstz
	 , sod
	 , k_fr
	 , wei
	 , hei
	 , bsa
	 , schetsluch 
	)
select	 onk_slid
	 ,(
		select n018.n018onkreasid
		from ies.t_n018_onk_reas n018
		where n018.id_reas = ds1_t
			and datebeg <= date_z_2
			and (
				dateend is null
				or dateend >= date_z_2
				)
		LIMIT 1
		)
	,(
		select tn002.n002stadiumid
		from ies.t_n002_stadium tn002
		where tn002.id_st = t0.stad
			and datebeg <= date_z_2
			and (
				dateend is null
				or dateend >= date_z_2
				)
		LIMIT 1
		)
	,(
		SELECT tn003.n003tumorid
		from ies.t_n003_tumor tn003
		where tn003.id_t = t0.onk_t
			and datebeg <= date_z_2
			and (
				dateend is null
				or dateend >= date_z_2
				)
		LIMIT 1
		)
	,(
		select tn004.n004nodusid
		from ies.t_n004_nodus tn004
		where tn004.id_n = t0.onk_n
			and datebeg <= date_z_2
			and (
				dateend is null
				or dateend >= date_z_2
				)
		LIMIT 1
		)
	,(
		select tn005.n005metastasisid
		from ies.t_n005_metastasis tn005
		where tn005.id_m = t0.onk_m
			and datebeg <= date_z_2
			and (
				dateend is null
				or dateend >= date_z_2
				)
		LIMIT 1
		)
	 , t0.mtstz
	 , t0.sod
	 , t0.k_fr
	 , t0.wei
	 , t0.hei
	 , t0.bsa
	 , t1.slid
from temp_onk_sl t0
JOIN temp_sl t1 ON t0.sl_id = t1.sl_id 
	AND t0.idcase = t1.idcase
	AND t0.n_zap = t1.n_zap
JOIN temp_z_sl t2 ON t0.idcase = t2.idcase
	AND t0.n_zap = t2.n_zap;

RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL/SL/KSG_KPG %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_ksg(
	 ksgid
	 , n_ksg
	 , gr
	 , ver_ksg
	 , koef_z
	 , sl_k
	 , it_sl
	 , not_onk_sl
	 , schetsluch 
	)
select	 ksg_kpgid
	 , t0.n_ksg
	 , t0.gr
	 , t0.ver_ksg
	 , t0.koef_z
	 , t0.sl_k
	 , t0.it_sl
	 , COALESCE(t0.not_onk_sl,FALSE)
	 , t1.slid
from temp_ksg_kpg t0
JOIN temp_sl t1 ON t0.sl_id = t1.sl_id 
	AND t0.idcase = t1.idcase
	AND t0.n_zap = t1.n_zap
JOIN temp_z_sl t2 ON t0.idcase = t2.idcase
	AND t0.n_zap = t2.n_zap;


RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL/SL/LEK_PR %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL/SL/LEK_PR/LEK_DOSE %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_schet_sluch_lek_pr(
	 schetsluchlekprid
	 , data_inj
	 , code_sh
	 , regnum
	 , cod_mark
	 , ed_izm
	 , dose_inj
	 , method_inj
	 , col_inj
	 , schetsluch 
	)
select	 t0.lek_prid
	 , t0.data_inj
	 ,(
		SELECT v032.v032druggrregid		
		from ies.t_v032_drug_gr_reg v032
		where t0.code_sh = v032.schedruggrcd
			and v032.datebeg <= t2.date_z_2
			and (
				v032.dateend is null
				or v032.dateend >= t2.date_z_2
				)
		LIMIT 1
		) as code_sh
	,(
		select n020.n020onklekpid
		from ies.t_n020_onk_lekp n020
		where n020.id_lekp = t0.regnum
			and n020.datebeg <= t2.date_z_2
			and (
				n020.dateend is null
				or n020.dateend >= t2.date_z_2
				)
		LIMIT 1
		) as regnum
	,t0.cod_mark
	,(
		select eut.ehrunittypeid
		--select *
		from ies.t_ehr_unit_type eut
		where t3.ed_izm = eut.id
		LIMIT 1
		) as ed_izm
	,t3.dose_inj
	,(
		select emi.ehrmetintroid
		from ies.t_ehr_met_intro emi
		where t3.method_inj = emi.id
		LIMIT 1
		) as method_inj
	 , t3.col_inj
	 , t1.slid
from temp_sl_lek_pr t0
JOIN temp_sl t1 ON t0.sl_id = t1.sl_id 
	AND t0.idcase = t1.idcase
	AND t0.n_zap = t1.n_zap
JOIN temp_z_sl t2 ON t0.idcase = t2.idcase
	AND t0.n_zap = t2.n_zap
JOIN temp_lek_dose t3 ON t3.lek_prid = t0.lek_prid;

RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL/SL/USL %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_schet_usl(
	 schetuslid
	 , idserv
	 , vid_vme
	 , comentu
	 , schetsluch 
	 , date_in 
	 , date_out
	 , type_
	)
SELECT uslid
	 , t0.idserv
	 , t0.vid_vme
	 , t0.comentu
	 , t1.slid
	 , t2.date_z_1
	 , t2.date_z_2
	 , 710
from temp_usl t0
JOIN temp_sl t1 ON t0.sl_id = t1.sl_id 
	AND t0.idcase = t1.idcase
	AND t0.n_zap = t1.n_zap
JOIN temp_z_sl t2 ON t0.idcase = t2.idcase
	AND t0.n_zap = t2.n_zap;


RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL/SL/KSG_KPG/CRIT %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_ksg_crit(
	 ksgcritid
	 , Ksg
	 , v024dopkr
	)
select
	 critid
	 , t1.ksg_kpgid
	 ,(
		select v024dopkrid
		from ies.t_v024_dop_kr
		where iddkk = t0.crit
		limit 1
		) as v024dopkr
		--SELECT *
from temp_crit t0
JOIN temp_ksg_kpg t1 ON t0.sl_id = t1.sl_id 
	AND t0.idcase = t1.idcase
	AND t0.n_zap = t1.n_zap;


RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL/SL/KSG_KPG/SL_KOEF %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_kslp(
	 kslpid
	 , koef_type
	 , koef
	 , ksg
	 , omsschetsluch
	)
select	 sl_koefid
	 , t0.idsl
	 , t0.z_sl
	 , t1.ksg_kpgid
	 , t2.slid
from temp_sl_koef t0
JOIN temp_ksg_kpg t1 ON t0.sl_id = t1.sl_id 
	AND t0.idcase = t1.idcase
	AND t0.n_zap = t1.n_zap
JOIN temp_sl t2 ON t0.sl_id = t2.sl_id 
	AND t0.idcase = t2.idcase
	AND t0.n_zap = t2.n_zap
	;

RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL/SL/USL/MED_DEV %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_schet_usl_med_dev(
	 schetuslmeddevid
	 , date_med
	 , code_meddev
	 , number_ser
	 , schetusl 
	)
select	 med_devid
	 , t0.date_med
	 , t0.code_meddev
	 , t0.number_ser
	 , t1.uslid
from temp_med_dev t0
JOIN temp_usl t1 ON t0.sl_id = t1.sl_id 
	AND t0.idcase = t1.idcase
	AND t0.n_zap = t1.n_zap
	AND t0.idserv = t1.idserv
;


RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL/SL/ONK_SL/B_DIAG %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_schet_sluch_onk_diag(
	 schetsluchonkdiagid
	 , diag_date
	 , diag_tip
	 , diag_code
	 , diag_rslt
	 , rec_rslt
	 , schetsluchonk
	)
select	 b_diagid
	 , t0.diag_date
	 , t0.diag_tip
	 , t0.diag_code
	 , t0.diag_rslt
	 , t0.rec_rslt
	 , t1.onk_slid
from temp_b_diag t0
JOIN temp_onk_sl t1 ON t0.sl_id = t1.sl_id 
	AND t0.idcase = t1.idcase
	AND t0.n_zap = t1.n_zap;


RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL/SL/ONK_SL/B_PROT %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_schet_sluch_onk_prot(
	 schetsluchonkprotid
	 , prot
	 , d_prot
	 , schetsluchonk 
	)
select	 b_protid
	 , (
		select tn001.n001protid
		from ies.t_n001_prot tn001
		where tn001.id_prot = t0.prot
		LIMIT 1
		)
	 , t0.d_prot
	 , t1.onk_slid
from temp_b_prot t0
JOIN temp_onk_sl t1 ON t0.sl_id = t1.sl_id 
	AND t0.idcase = t1.idcase
	AND t0.n_zap = t1.n_zap;

RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL/SL/ONK_SL/ONK_USL %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_schet_usl_onk(
	 schetuslonkid
	 , usl_tip
	 , hir_tip
	 , lek_tip_l
	 , lek_tip_v
	 , pptr
	 , luch_tip
	 , schetsluchonk 
	)
select	 onk_uslid
	 ,(
		select tn013.n013treattypeid
		from ies.t_n013_treat_type tn013
		where tn013.id_tlech = t0.usl_tip
			and datebeg <= date_z_2
			and (
				dateend is null
				or dateend >= date_z_2
				)
		LIMIT 1
		)
	,(
		select tn014.n014surgtreatid
		from ies.t_n014_surg_treat tn014
		where tn014.id_thir = t0.hir_tip
			and datebeg <= date_z_2
			and (
				dateend is null
				or dateend >= date_z_2
				)
		limit 1
		)
	 ,(
		select tn015.n015drugtherapylinesid
		from ies.t_n015_drug_therapy_lines tn015
		where tn015.id_tlek_l = t0.lek_tip_l
			and datebeg <= date_z_2
			and (
				dateend is null
				or dateend >= date_z_2
				)
		LIMIT 1
		)
	 ,(
		select tn016.n016drugtherapycyclesid
		from ies.t_n016_drug_therapy_cycles tn016
		where tn016.id_tlek_v = t0.lek_tip_v
			and datebeg <= date_z_2
			and (
				dateend is null
				or dateend >= date_z_2
				)
		LIMIT 1
		)
	 , t0.pptr
	 ,(
		select tn017.n017radiationtherapytypesid
		from ies.t_n017_radiation_therapy_types tn017
		where tn017.id_tluch = t0.luch_tip
			and datebeg <= date_z_2
			and (
				dateend is null
				or dateend >= date_z_2
				)
		LIMIT 1
		) as luch_tip
	 , t1.onk_slid
from temp_onk_usl t0
JOIN temp_onk_sl t1 ON t0.sl_id = t1.sl_id 
	AND t0.idcase = t1.idcase
	AND t0.n_zap = t1.n_zap
JOIN temp_z_sl t2 ON t0.idcase = t2.idcase
	AND t0.n_zap = t2.n_zap;


RAISE NOTICE 'Insert element /ZL_LIST/ZAP/Z_SL/SL/ONK_SL/ONK_USL/LEK_PR %', to_char(clock_timestamp(), 'DD.MM.YYYY HH24:MI:SS');
INSERT INTO ies.t_schet_usl_onk_lek_pr(
	 schetuslonklekprid
	 , regnum 
	 , v024dopkr
	 , date_inj
	 , dose_inj
	 , schetuslonk 
	)
select	 lek_prid
	 ,(
		select n020onklekpid
		from ies.t_n020_onk_lekp n020
		where n020.id_lekp = regnum
		LIMIT 1
		)
	 ,(
		select v024dopkrid
		from ies.t_v024_dop_kr
		where code_sh = iddkk
		LIMIT 1
		)
	 , t0.date_inj
	 , t0.dose_inj
	 , t0.onk_uslid
from temp_onk_usl_lek_pr t0;

INSERT INTO ies.t_schet_usl_onk_lek_pr_date(
	 schetuslonklekprdateid
	 , schetuslonklekpr 
	 , date_inj
	)
select	 lek_prid
	 , lek_prid	 
	 , t0.date_inj
from temp_onk_usl_lek_pr t0;
END if;
--INSERT SECTION END
/*
--*/


end;
$procedure$
;