DROP PROCEDURE IF EXISTS sp_q022flkmprload;
DROP FUNCTION IF EXISTS sp_q022flkmprload;

CREATE PROCEDURE public.sp_q022flkmprload("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_q022;
	DROP TABLE IF EXISTS temp_q022_type_md;

	CREATE TEMP TABLE temp_q022 as
	SELECT 
		ID_TEST, 
		ID_EL,
		NSI_OBJ,
		NSI_EL,
		USL_TEST,
		VAL_EL,
		CASE WHEN  MIN_LEN  = '' THEN NULL ELSE MIN_LEN END, 
		CASE WHEN  MAX_LEN  = '' THEN NULL ELSE MAX_LEN END,
		MASK_VAL,
		COMMENT,
		CASE WHEN  DATEBEG  = '' THEN NULL ELSE DATEBEG::TIMESTAMP END , 
		CASE WHEN  DATEEND  = '' THEN NULL ELSE DATEEND::TIMESTAMP END 
	
	FROM
		
	XMLTABLE 
		(
			'/packet/zap' PASSING T 
			COLUMNS 
			ID_TEST text path './ID_TEST' null, 
			ID_EL text path './ID_EL' null,
			NSI_OBJ text path './NSI_OBJ' null, 
			NSI_EL text path './NSI_EL' null,
			USL_TEST text path './USL_TEST' null, 
			VAL_EL text path './VAL_EL' null,
			MIN_LEN text path './MIN_LEN' null, 
			MAX_LEN text path './MAX_LEN' null,
			MASK_VAL text path './MASK_VAL' null, 
			COMMENT text path './COMMENT' null,
			DATEBEG text path './DATEBEG' null, 
			DATEEND text path './DATEEND' null 
		);


	CREATE TEMP TABLE temp_q022_type_md 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 temp_errors;
    
    CREATE TEMP TABLE temp_errors (
        im_pol VARCHAR(10),
        bas_el VARCHAR(10),
        n_zap VARCHAR(250),
        oshib INT,
        comment TEXT
    );

   
    INSERT INTO temp_errors (im_pol, bas_el, n_zap, oshib, comment)
    SELECT 
        'TYPE_D', 
        'Q022', 
        t1.id_test, 
        904, 
        'Не найдено соответствие коду со значением "' || t1.type_d || '" в справочнике Q019'
    FROM 
        temp_q022_type_md t1 
    LEFT JOIN 
        ies.t_q019_type_dat t2 ON t2.idtyped = t1.type_d 
    WHERE 
        t2.q019typedatid IS NULL

    UNION

    SELECT 
        'ID_EL', 
        'Q022', 
        t1.id_test, 
        904, 
        'Не найдено соответствие коду со значением "' || t1.id_el || '" в справочнике Q018'
    FROM 
        temp_q022 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 temp_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 temp_errors
			)
		);
		Return;
	End if;


	UPDATE IES.T_Q022_FLK_MPR t11
	SET 
		NSI_OBJ = t1.NSI_OBJ, 
		Q018DescrR = q018.Q018DescrRID, 
		NSI_EL = t1.NSI_EL, 
		USL_TEST = t1.USL_TEST, 
		VAL_EL = t1.VAL_EL, 
		MIN_LEN = t1.MIN_LEN, 
		MAX_LEN = t1.MAX_LEN, 
		MASK_VAL = t1.MASK_VAL, 
		COMMENT = t1.COMMENT, 
		DATEEND = t1.DATEEND
	FROM temp_q022_flk_mpr t1
	JOIN LATERAL (
		SELECT t2.Q018DescrRID
		FROM IES.T_Q018_DESCR_R t2
		WHERE t2.ID_EL = t1.ID_EL
		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 AND t1.DATEEND IS NULL));
	
	
	INSERT INTO IES.T_Q022_FLK_MPR(
		Q022FlkMprID, 
		ID_TEST, 
		Q018DescrR, 
		NSI_OBJ, 
		NSI_EL, 
		USL_TEST, 
		VAL_EL, 
		MIN_LEN, 
		MAX_LEN, 
		MASK_VAL, 
		COMMENT, 
		DATEBEG, 
		DATEEND
	)
	SELECT 
		gen_random_uuid(), 
		t1.ID_TEST, 
		q018.Q018DescrRID, 
		t1.NSI_OBJ, 
		t1.NSI_EL, 
		t1.USL_TEST, 
		t1.VAL_EL, 
		t1.MIN_LEN, 
		t1.MAX_LEN, 
		t1.MASK_VAL, 
		t1.COMMENT, 
		t1.DATEBEG, 
		t1.DATEEND
	FROM temp_q022 t1
	JOIN LATERAL (
		SELECT t2.Q018DescrRID
		FROM IES.T_Q018_DESCR_R t2
		WHERE t2.ID_EL = t1.ID_EL
		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_Q022_FLK_MPR 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.Q022FlkMprID IS NULL;
	
	
	INSERT INTO IES.T_Q022_FLK_MPR_TYPE_MD(
		Q022FlkMprTypeMdID, 
		Q022FlkMpr, 
		Q019TypeDat
	)
	SELECT 
		gen_random_uuid(), 
		t11.Q022FlkMprID, 
		q019.Q019TypeDatID
	FROM temp_q022_type_md t1
	JOIN LATERAL (
		SELECT t2.Q019TypeDatID
		FROM IES.T_Q019_TYPE_DAT t2
		WHERE t2.IDTYPED = t1.TYPE_D
		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_Q022_FLK_MPR 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_Q022_FLK_MPR_TYPE_MD t10 ON 
		t10.Q022FlkMpr = t11.Q022FlkMprID 
		AND (t10.Q019TypeDat = q019.Q019TypeDatID OR (t10.Q019TypeDat IS NULL AND q019.Q019TypeDatID IS NULL))
	WHERE t10.Q022FlkMprTypeMdID IS NULL;

	drop table if exists temp_q022;
	drop table if exists temp_q022_type_md;
	
	return;
END
$BODY$;