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

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

	--парсим xml_doc в временную таблицу
	CREATE TEMP TABLE temp_f010 AS 
	SELECT
		kod_tf,
		kod_okato,
		okrug,
		subname,
		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
			kod_tf TEXT PATH './KOD_TF',
			kod_okato TEXT PATH './KOD_OKATO',
			okrug INT PATH './OKRUG',
			subname TEXT PATH './SUBNAME',
			datebeg TEXT PATH './DATEBEG',
			dateend TEXT PATH './DATEEND'
		)
	;
	
	-- обновляем существующие записи
	UPDATE ies.t_f010_subject AS f010
	SET
		subname = tmp.subname,
		dateend = tmp.dateend
	FROM
		temp_f010 AS tmp
	WHERE
		tmp.kod_tf = f010.kod_tf
		AND tmp.datebeg = f010.datebeg;

	select count(1) into row_count 
	from temp_f010 tmp
	left join ies.t_f010_subject  f010 on (tmp.kod_tf = f010.kod_tf	AND tmp.datebeg = f010.datebeg)
	where f010.subjectid is null;

	call public.sp_generateidforentity('IES.F010_SUBJECT', 'PrimaryKey', row_count, start_id);

	INSERT INTO ies.t_f010_subject (
		subjectid,
		kod_tf,
		kod_okato,
		subname,
		okrug,
		datebeg,
		dateend
	)
	SELECT
		start_id + row_number() over (order by tmp.kod_tf) - 1,
		tmp.kod_tf,
		tmp.kod_okato,
		tmp.subname,
		tmp.okrug,
		tmp.datebeg,
		tmp.dateend
	FROM
		temp_f010 AS tmp
	LEFT JOIN
		ies.t_f010_subject AS f010
		ON tmp.kod_tf = f010.kod_tf
		AND tmp.datebeg = f010.datebeg
	WHERE
		f010.subjectid IS NULL;

	DROP TABLE IF EXISTS temp_f010;
END;
$BODY$;