DROP FUNCTION IF EXISTS public.sp_q023mekmprload;
DROP PROCEDURE IF EXISTS public.sp_q023mekmprload;

CREATE PROCEDURE public.sp_q023mekmprload(
	"Xml" bytea,
	"FileName" character varying)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
	--меняем кодировку исходного файла на кодировку базы данных, убираем указание на старую кодировку
	xml_doc XML = REPLACE(convert_from("Xml", 'WIN1251'), 'encoding="windows-1251"', '') :: XML;
BEGIN
	DROP TABLE IF EXISTS temp_q023;

	CREATE TEMP TABLE temp_q023 AS
	SELECT
		id_test,
		desc_test,
		id_el,
		nsi_obj,
		nsi_el,
		usl_test,
		val_el,
		comment,
		CASE WHEN datebeg = '' THEN NULL ELSE datebeg :: TIMESTAMP END,
		CASE WHEN dateend = '' THEN NULL ELSE dateend :: TIMESTAMP END
	FROM
		XMLTABLE ('/packet/zap' PASSING xml_doc COLUMNS
			id_test TEXT PATH './ID_TEST',
			desc_test TEXT PATH './DESC_TEST',
			id_el TEXT PATH './ID_EL',
			nsi_obj TEXT PATH './NSI_OBJ',
			nsi_el TEXT PATH './NSI_EL',
			usl_test TEXT PATH './USL_TEST',
			val_el TEXT PATH './VAL_EL',
			comment TEXT PATH './COMMENT',
			datebeg TEXT PATH './DATEBEG',
			dateend TEXT PATH './DATEEND'
		)
	;

	DROP TABLE IF EXISTS temp_q023_type_md;

	CREATE TEMP TABLE temp_q023_type_md AS 
	SELECT
		gen_random_uuid() AS q023mekmprtypemdid,
		id_test,
		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 xml_doc COLUMNS
			id_test TEXT PATH '../../ID_TEST',
			datebeg TEXT PATH '../../DATEBEG',
			dateend TEXT PATH '../../DATEEND',
			type_d TEXT PATH '.'
		)
	;

	DROP TABLE IF EXISTS errors;

	CREATE TABLE errors (
		im_pol VARCHAR(10),
		bas_el VARCHAR(10),
		n_zap VARCHAR(250),
		oshib INT,
		comment VARCHAR(250)
	)
	;

	INSERT INTO errors(
		im_pol,
		bas_el,
		n_zap,
		oshib,
		comment
	)
	SELECT
		'TYPE_D',
		'Q023',
		tmp2.id_test,
		904,
		'Не найдено соответствие коду со значением "' || tmp2.type_d || '" в справочнике Q019'
	FROM
		temp_q023_type_md AS tmp2
	LEFT JOIN
		ies.t_q019_type_dat AS q019
		ON 	q019.idtyped = tmp2.type_d
	WHERE
		q019.q019typedatid IS NULL
	UNION
	SELECT
		'ID_EL',
		'Q023',
		tmp.id_test,
		904,
		'Не найдено соответствие коду со значением "' || tmp.id_el || '" в справочнике Q018'
	FROM
		temp_q023 AS tmp
	LEFT JOIN
		ies.t_q018_descr_r AS q018
		ON q018.id_el = tmp.id_el
	WHERE
		q018.q018descrrid IS NULL
	;
	
	--бросаем исключение, если есть ошибки флк
	IF (SELECT COUNT(*) FROM errors) > 0 THEN
		DECLARE
			flk_result TEXT;
		BEGIN
			SELECT 
				'<?xml version="1.0" encoding="Windows-1251"?>' 
				|| xmlelement(
					name "FLK_P",
					xmlagg(
						xmlelement(
							name "PR",
							xmlforest(
								 im_pol AS "IM_POL",
								 bas_el AS "BAS_EL",
								 n_zap AS "N_ZAP",
								 oshib AS "OSHIB",
								 comment AS "COMMENT"
							)
						)
					)
				)
			INTO flk_result
			FROM errors
			;
		RAISE EXCEPTION '%', flk_result;
		END;
	END IF;

	-- это было в хп ms sql
	-- DELETE FROM ies.t_q023_mek_mpr_type_md;
	-- DELETE FROM ies.t_q023_mek_mpr;

	UPDATE ies.t_q023_mek_mpr AS q023
	SET
		desc_test = tmp.desc_test,
		q018descrr = q018.q018descrrid,
		nsi_obj = tmp.nsi_obj,
		nsi_el = tmp.nsi_el,
		usl_test = tmp.usl_test,
		val_el = tmp.val_el,
		comment = tmp.comment,
		dateend = tmp.dateend
	FROM
		temp_q023 AS tmp
	CROSS JOIN LATERAL (
		SELECT
			q018s.q018descrrid
		FROM
			ies.t_q018_descr_r AS q018s
		WHERE
			q018s.id_el = tmp.id_el
		ORDER BY
			CASE 
				WHEN q018s.datebeg <= tmp.datebeg 
				AND (q018s.dateend >= tmp.datebeg OR q018s.dateend IS NULL) THEN 1
				ELSE 0
			END DESC,
			CASE
				WHEN q018s.datebeg IS NOT NULL
				AND tmp.datebeg IS NOT NULL THEN 1
				ELSE 0
			END DESC,
			q018s.datebeg DESC,
			q018s.dateend
		LIMIT 1
	) AS q018
	WHERE
		q023.id_test = tmp.id_test
		AND q023.datebeg = tmp.datebeg
		AND (
			q023.dateend = tmp.dateend
			OR (q023.dateend IS NULL AND tmp.dateend IS NULL)
		)
	;

	INSERT INTO ies.t_q023_mek_mpr(
		q023mekmprid,
		id_test,
		desc_test,
		q018descrr,
		nsi_obj,
		nsi_el,
		usl_test,
		val_el,
		comment,
		datebeg,
		dateend
	)
	SELECT
		gen_random_uuid(),
		tmp.id_test,
		tmp.desc_test,
		q018.q018descrrid,
		tmp.nsi_obj,
		tmp.nsi_el,
		tmp.usl_test,
		tmp.val_el,
		tmp.comment,
		tmp.datebeg,
		tmp.dateend
	FROM
		temp_q023 AS tmp
	CROSS JOIN LATERAL (
		SELECT
			q018s.q018descrrid
		FROM
			ies.t_q018_descr_r AS q018s
		WHERE
			q018s.id_el = tmp.id_el
		ORDER BY
			CASE 
				WHEN q018s.datebeg <= tmp.datebeg 
				AND (q018s.dateend >= tmp.datebeg OR q018s.dateend IS NULL) THEN 1
				ELSE 0
			END DESC,
			CASE
				WHEN q018s.datebeg IS NOT NULL
				AND tmp.datebeg IS NOT NULL THEN 1
				ELSE 0
			END DESC,
			q018s.datebeg DESC,
			q018s.dateend
		LIMIT 1		
	) AS q018
	LEFT JOIN
		ies.t_q023_mek_mpr AS q023
		ON q023.id_test = tmp.id_test
		AND q023.datebeg = tmp.datebeg
		AND (
			q023.dateend = tmp.dateend
			OR (q023.dateend IS NULL AND tmp.dateend IS NULL)
		)
	WHERE
		q023.q023mekmprid IS NULL
	;

	INSERT INTO ies.t_q023_mek_mpr_type_md (
		q023mekmprtypemdid,
		q023mekmpr,
		q019typedat
	)
	SELECT
		gen_random_uuid(),
		q023.q023mekmprid,
		q019.q019typedatid
	FROM
		temp_q023_type_md AS tmp2
	CROSS JOIN LATERAL (
		SELECT
			q019s.q019typedatid
		FROM
			ies.t_q019_type_dat AS q019s
		WHERE
			q019s.idtyped = tmp2.type_d
		ORDER BY
			CASE
				WHEN q019s.datebeg <= tmp2.datebeg AND (q019s.dateend >= tmp2.datebeg OR q019s.dateend IS NULL) THEN 1
				ELSE 0
			END DESC,
			CASE
				WHEN q019s.datebeg IS NOT NULL AND tmp2.datebeg IS NOT NULL THEN 1
				ELSE 0
			END DESC,
			q019s.datebeg DESC,
			q019s.dateend
		LIMIT 1			
	) AS q019
	INNER JOIN
		ies.t_q023_mek_mpr AS q023
		ON q023.id_test = tmp2.id_test
		AND q023.datebeg = tmp2.datebeg
		AND (q023.dateend = tmp2.dateend OR (q023.dateend IS NULL AND tmp2.dateend IS NULL))
	LEFT JOIN
		ies.t_q023_mek_mpr_type_md AS q023_md
		ON q023_md.q023mekmpr = q023.q023mekmprid
		AND (q023_md.q019typedat = q019.q019typedatid OR (q023_md.q019typedat IS NULL AND q019.q019typedatid IS NULL))
	WHERE
		q023_md.q023mekmprtypemdid IS NULL
	;

	DROP TABLE IF EXISTS temp_q023;
	DROP TABLE IF EXISTS temp_q023_type_md;
END;
$BODY$;