ALTER TABLE ies.t_q018_descr_r
    ALTER COLUMN desc_e TYPE varchar(2000);
	
DROP PROCEDURE IF EXISTS sp_q018descrrload;
DROP FUNCTION IF EXISTS sp_q018descrrload;

CREATE PROCEDURE public.sp_q018descrrload("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_q018;
	CREATE TEMP TABLE temp_q018 as
	SELECT 
			IDZAP, 
			ID_EL, 
			TYPE_D, 
			TYPE_E, 
			FORM_E, 
			NAME_E, 
			DESC_E, 
			CASE WHEN  DATEBEG  = '' THEN NULL ELSE DATEBEG::TIMESTAMP END , 
			CASE WHEN  DATEEND  = '' THEN NULL ELSE DATEEND::TIMESTAMP END ,
			MXLEN_E 
	FROM
	XMLTABLE ('/packet/zap' PASSING T 
	COLUMNS 
			IDZAP text path './IDZAP' null, 
			ID_EL text path './ID_EL' null, 
			TYPE_D text path './TYPE_D' null, 
			TYPE_E text path './TYPE_E' null, 
			FORM_E text path './FORM_E' null, 
			NAME_E text path './NAME_E' null, 
			DESC_E text path './DESC_E' null, 
			DATEBEG text path './DATEBEG' null, 
			DATEEND text path './DATEEND' null, 
			MXLEN_E text path './MXLEN_E' null );

	
	DROP TABLE IF EXISTS q018_Errors;
	CREATE TABLE q018_Errors (
		IM_POL varchar(10),
		BAS_EL varchar(10),
		N_ZAP varchar(250),
		OSHIB int,
		COMMENT varchar(250)
	);
		
	Insert into q018_Errors 
	select 'TYPE_D', 'Q018', t1.IDZAP, 904, 'Не найдено соответствие коду со значением "'||cast(t1.TYPE_D as varchar(255))||'" в справочнике Q019'
		from temp_q018 t1 
		left join IES.T_Q019_TYPE_DAT t2 on t2.IDTYPED=t1.TYPE_D 
		where t2.Q019TypeDatID is null
	union
	select 'TYPE_E', 'Q018', t1.IDZAP, 904, 'Не найдено соответствие коду со значением "'||cast(t1.TYPE_E as varchar(255))||'" в справочнике Q020'
		from temp_q018 t1
		left join IES.T_Q020_TYPE_EL t2 on t2.IDTYPE=t1.TYPE_E
		where t2.Q020TypeElID is null
	union
	select 'FORM_E', 'Q018', t1.IDZAP, 904, 'Не найдено соответствие коду со значением "'||cast(t1.FORM_E as varchar(255))||'" в справочнике Q021'
		from temp_q018 t1
		left join IES.T_Q021_FORM_EL t2 on t2.IDFORM=t1.FORM_E
		where t2.Q021FormElID is null
		;

	If (Select COUNT(*) from q018_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 q018_Errors));
		Return;
	End if;
	
		
	update IES.T_Q018_DESCR_R t2 
	set  ID_EL=t1.ID_EL
		,TYPE_D=q019.Q019TypeDatID
		,TYPE_E=q020.Q020TypeElID
		,FORM_E=q021.Q021FormElID
		,MXLEN_E=t1.MXLEN_E
		,NAME_E=t1.NAME_E
		,DESC_E=t1.DESC_E
		,DATEEND=t1.DATEEND
	from temp_q018 t1
	left join lateral ( select Q020TypeElID  from IES.T_Q020_TYPE_EL  t3 where t3.IDTYPE = t1.TYPE_E  /*AND t3.DATEBEG <= t1.DATEBEG and (t3.DATEEND >= t1.DATEBEG or t3.DATEEND is null)*/ order by case when t3.DATEBEG <= t1.DATEBEG and (t3.DATEEND >= t1.DATEBEG or t3.DATEEND is null) then 1 else 0 end DESC,case when t3.DATEBEG IS NOT NULL and t1.DATEBEG IS NOT NULL then 1 else 0 end DESC,t3.DATEBEG desc,t3.DATEEND LIMIT 1) q020 on true
	left join lateral ( select Q019TypeDatID from IES.T_Q019_TYPE_DAT t3 where t3.IDTYPED = t1.TYPE_D /*AND t3.DATEBEG <= t1.DATEBEG and (t3.DATEEND >= t1.DATEBEG or t3.DATEEND is null)*/ order by case when t3.DATEBEG <= t1.DATEBEG and (t3.DATEEND >= t1.DATEBEG or t3.DATEEND is null) then 1 else 0 end DESC,case when t3.DATEBEG IS NOT NULL and t1.DATEBEG IS NOT NULL then 1 else 0 end DESC,t3.DATEBEG desc,t3.DATEEND LIMIT 1) q019 on true
	left join lateral ( select Q021FormElID  from IES.T_Q021_FORM_EL  t3 where t3.IDFORM = t1.FORM_E  /*AND t3.DATEBEG <= t1.DATEBEG and (t3.DATEEND >= t1.DATEBEG or t3.DATEEND is null)*/ order by case when t3.DATEBEG <= t1.DATEBEG and (t3.DATEEND >= t1.DATEBEG or t3.DATEEND is null) then 1 else 0 end DESC,case when t3.DATEBEG IS NOT NULL and t1.DATEBEG IS NOT NULL then 1 else 0 end DESC,t3.DATEBEG desc,t3.DATEEND LIMIT 1) q021 on true
	where (t1.IDZAP=t2.IDZAP and t1.DATEBEG = t2.DATEBEG) and (t2.DATEEND = t1.DATEEND or t2.DATEEND is null );

	insert into IES.T_Q018_DESCR_R(Q018DescrRID,IDZAP,ID_EL,TYPE_D,TYPE_E,FORM_E,MXLEN_E,NAME_E,DESC_E,DATEBEG,DATEEND)
	select gen_random_uuid(), t1.IDZAP,t1.ID_EL,q019.Q019TypeDatID as TYPE_D,q020.Q020TypeElID as TYPE_E,q021.Q021FormElID as FORM_E,t1.MXLEN_E,t1.NAME_E,t1.DESC_E,t1.DATEBEG,t1.DATEEND
	from temp_q018 t1
	left join IES.T_Q018_DESCR_R t2 on (t1.IDZAP=t2.IDZAP and t1.DATEBEG = t2.DATEBEG) and (t2.DATEEND = t1.DATEEND or (t2.DATEEND is null and t1.DATEEND is null) )
	left join lateral ( select Q020TypeElID  from IES.T_Q020_TYPE_EL  t3 where t3.IDTYPE = t1.TYPE_E  /*AND t3.DATEBEG <= t1.DATEBEG and (t3.DATEEND >= t1.DATEBEG or t3.DATEEND is null)*/ order by case when t3.DATEBEG <= t1.DATEBEG and (t3.DATEEND >= t1.DATEBEG or t3.DATEEND is null) then 1 else 0 end DESC,case when t3.DATEBEG IS NOT NULL and t1.DATEBEG IS NOT NULL then 1 else 0 end DESC,t3.DATEBEG desc,t3.DATEEND LIMIT 1) q020 on true
	left join lateral ( select Q019TypeDatID from IES.T_Q019_TYPE_DAT t3 where t3.IDTYPED = t1.TYPE_D /*AND t3.DATEBEG <= t1.DATEBEG and (t3.DATEEND >= t1.DATEBEG or t3.DATEEND is null)*/ order by case when t3.DATEBEG <= t1.DATEBEG and (t3.DATEEND >= t1.DATEBEG or t3.DATEEND is null) then 1 else 0 end DESC,case when t3.DATEBEG IS NOT NULL and t1.DATEBEG IS NOT NULL then 1 else 0 end DESC,t3.DATEBEG desc,t3.DATEEND LIMIT 1) q019 on true
	left join lateral ( select Q021FormElID  from IES.T_Q021_FORM_EL  t3 where t3.IDFORM = t1.FORM_E  /*AND t3.DATEBEG <= t1.DATEBEG and (t3.DATEEND >= t1.DATEBEG or t3.DATEEND is null)*/ order by case when t3.DATEBEG <= t1.DATEBEG and (t3.DATEEND >= t1.DATEBEG or t3.DATEEND is null) then 1 else 0 end DESC,case when t3.DATEBEG IS NOT NULL and t1.DATEBEG IS NOT NULL then 1 else 0 end DESC,t3.DATEBEG desc,t3.DATEEND LIMIT 1) q021 on true
	where t2.Q018DescrRID is null;

	drop table if exists temp_q018;
	
	return;
END
$BODY$;

--select * from ies.T_Q018_DESCR_R
