ALTER TABLE ies.t_q016_mek_mp
    ALTER COLUMN usl_test TYPE varchar(1000);

DROP PROCEDURE IF EXISTS sp_q016mekmpload;
DROP FUNCTION IF EXISTS sp_q016mekmpload;

CREATE PROCEDURE public.sp_q016mekmpload("Xml" bytea, "FileName" character varying)
LANGUAGE 'plpgsql'
AS $BODY$
	
declare T xml;
declare tempStrXml text;
BEGIN
	select convert_from("Xml", 'WIN1251') into tempStrXml;
	
	select replace(tempStrXml, 'encoding="windows-1251"','')::xml into T; 
	
	DROP TABLE IF EXISTS temp_q016;
	CREATE TEMP TABLE temp_q016 as
	SELECT 
		ID_TEST, 
		ID_EL, 
		DESC_TEST, 
		CASE WHEN  DATEBEG  = '' THEN NULL ELSE DATEBEG::TIMESTAMP END,
		CASE WHEN  DATEEND  = '' THEN NULL ELSE DATEEND::TIMESTAMP END,  
		COMMENT, 
		VAL_EL, 
		USL_TEST, 
		NSI_EL, 
		NSI_OBJ 
	FROM
	XMLTABLE ('/packet/zap' PASSING T 
	COLUMNS 
		ID_TEST text path './ID_TEST' null, 
		ID_EL text path './ID_EL' null, 
		DESC_TEST text path './DESC_TEST' null, 
		DATEBEG text path './DATEBEG' null, 
		DATEEND text path './DATEEND' null, 
		COMMENT text path './COMMENT' null, 
		VAL_EL text path './VAL_EL' null, 
		USL_TEST text path './USL_TEST' null, 
		NSI_EL text path './NSI_EL' null, 
		NSI_OBJ text path './NSI_OBJ' null );
	
	
	DROP TABLE IF EXISTS temp_q016_type_d ;
	CREATE TEMP TABLE temp_q016_type_d as 
	SELECT 
		ID_TEST, 
		ID_EL, 
		CASE WHEN  DATEBEG  = '' THEN NULL ELSE DATEBEG::TIMESTAMP END,
		CASE WHEN  DATEEND  = '' THEN NULL ELSE DATEEND::TIMESTAMP END, 
		TYPE_D
	FROM
	XMLTABLE ('/packet/zap/TYPE_MD/TYPE_D' PASSING T 
	COLUMNS 
		ID_TEST text path '../../ID_TEST' null, 
		ID_EL text path '../../ID_EL' null,
		DATEBEG text path '../../DATEBEG' null,
		DATEEND text path '../../DATEEND' null, 
		TYPE_D text path '.' null );
		
	DROP TABLE IF EXISTS q016_Errors;
	CREATE TABLE q016_Errors (
		IM_POL varchar(10),
		BAS_EL varchar(10),
		N_ZAP varchar(250),
		OSHIB int,
		COMMENT varchar(250)
	);
		
	Insert into q016_Errors 
	select 'TYPE_D', 'Q016', t1.ID_TEST, 904, 'Не найдено соответствие коду со значением "'||cast(t1.TYPE_D as varchar(255))||'" в справочнике Q019'
		from temp_q016_type_d t1 
		left join IES.T_Q019_TYPE_DAT t2 on t2.IDTYPED=t1.TYPE_D 
		where t2.Q019TypeDatID is null
	union
	select 'ID_EL', 'Q016', t1.ID_TEST, 904, 'Не найдено соответствие коду со значением "'||cast(t1.ID_EL as varchar(255))||'" в справочнике Q018'
		from temp_q016 t1
		left join IES.T_Q018_DESCR_R t2 on t2.ID_EL=t1.ID_EL
		where t2.Q018DescrRID is null
		;

	If (Select COUNT(*) from q016_Errors) > 0 then
		--вывод ошибок сделано так , тк нет обработки OUT параметров
		raise exception '%',
		(select '<?xml version="1.0" encoding="Windows-1251"?>' || 
		(SELECT 
				xmlforest(
					xmlagg(
							xmlforest (
								 xmlconcat (
											xmlforest(IM_POL), 
											xmlforest(BAS_EL), 
											xmlforest(N_ZAP),
											xmlforest(OSHIB),
											xmlforest(COMMENT)
											) as PR 
										)	
							) as FLK_P )
		FROM q016_Errors));
		Return;
	End if;

	Update IES.T_Q016_MEK_MP t11
		set 
		 NSI_OBJ   = t1.NSI_OBJ
		,ID_EL     = q018.Q018DescrRID
		,NSI_EL    = t1.NSI_EL
		,DESC_TEST = t1.DESC_TEST
		,USL_TEST  = t1.USL_TEST
		,VAL_EL    = t1.VAL_EL
		,COMMENT   = t1.COMMENT
		,DATEEND   = t1.DATEEND
	from temp_q016 t1
	join lateral(select t2.Q018DescrRID from IES.T_Q018_DESCR_R t2 where t2.ID_EL=t1.ID_EL /*AND t2.DATEBEG <= t1.DATEBEG and (t2.DATEEND >= t1.DATEBEG or t2.DATEEND is null)*/ order by case when t2.DATEBEG <= t1.DATEBEG and (t2.DATEEND >= t1.DATEBEG or t2.DATEEND is null) then 1 else 0 end DESC,case when t2.DATEBEG IS NOT NULL and t1.DATEBEG IS NOT NULL then 1 else 0 end DESC,t2.DATEBEG desc,t2.DATEEND limit 1 ) q018 on true
	where t11.ID_TEST = t1.ID_TEST and t11.DATEBEG = t1.DATEBEG and (t11.DATEEND = t1.DATEEND or t11.DATEEND is null );

	Insert into IES.T_Q016_MEK_MP(Q016MekMpID ,ID_TEST ,ID_EL ,DESC_TEST ,NSI_OBJ ,NSI_EL ,USL_TEST ,VAL_EL ,COMMENT ,DATEBEG ,DATEEND)
	Select gen_random_uuid(),t1.ID_TEST ,q018.Q018DescrRID ,t1.DESC_TEST ,t1.NSI_OBJ ,t1.NSI_EL ,t1.USL_TEST ,t1.VAL_EL ,t1.COMMENT ,t1.DATEBEG ,t1.DATEEND
	from temp_q016 t1
	join lateral(select t2.Q018DescrRID from IES.T_Q018_DESCR_R t2 where t2.ID_EL=t1.ID_EL /*AND t2.DATEBEG <= t1.DATEBEG and (t2.DATEEND >= t1.DATEBEG or t2.DATEEND is null)*/ order by case when t2.DATEBEG <= t1.DATEBEG and (t2.DATEEND >= t1.DATEBEG or t2.DATEEND is null) then 1 else 0 end DESC,case when t2.DATEBEG IS NOT NULL and t1.DATEBEG IS NOT NULL then 1 else 0 end DESC,t2.DATEBEG desc,t2.DATEEND limit 1 ) q018 on true
	left join ies.T_Q016_MEK_MP t11 on t11.ID_TEST = t1.ID_TEST and t11.DATEBEG = t1.DATEBEG and (t11.DATEEND = t1.DATEEND or (t11.DATEEND is null and  t1.DATEEND is null) )
	where t11.Q016MekMpID is null; 

	Insert into IES.T_Q016_MEK_MP_TYPE_MD(Q016MekMpTypeMdID ,Q016MekMp ,Q019TypeDat)
	Select gen_random_uuid() ,t11.Q016MekMpID ,q019.Q019TypeDatID
	from temp_q016_type_d t1
	join lateral(select t2.Q019TypeDatID from IES.T_Q019_TYPE_DAT t2 where t2.IDTYPED=t1.TYPE_D /*AND t2.DATEBEG <= t1.DATEBEG and (t2.DATEEND >= t1.DATEBEG or t2.DATEEND is null)*/ order by case when t2.DATEBEG <= t1.DATEBEG and (t2.DATEEND >= t1.DATEBEG or t2.DATEEND is null) then 1 else 0 end DESC,case when t2.DATEBEG IS NOT NULL and t1.DATEBEG IS NOT NULL then 1 else 0 end DESC,t2.DATEBEG desc,t2.DATEEND limit 1) q019 on true
	join IES.T_Q016_MEK_MP t11 on t11.ID_TEST = t1.ID_TEST and t11.DATEBEG = t1.DATEBEG and (t11.DATEEND = t1.DATEEND or (t11.DATEEND is null and  t1.DATEEND is null) )
	left join IES.T_Q016_MEK_MP_TYPE_MD t10 on t10.Q016MekMp = t11.Q016MekMpID and (t10.Q019TypeDat = q019.Q019TypeDatID or (t10.Q019TypeDat is null and q019.Q019TypeDatID is null) ) 
	where t10.Q016MekMpTypeMdID is null; 

	drop table if exists temp_q016;
	drop table if exists temp_q016_type_d;
	
	return;
END
$BODY$;
