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

CREATE PROCEDURE  public.sp_k005avgcostnormload(
	IN "Xml" bytea,
	IN "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_k005;

	--парсим xml_doc в временную таблицу
	CREATE TEMP TABLE temp_k005 AS 
	SELECT 
		UID_SN::int, 
		TF_OKATO_SN, 
		NUM_SN::int, 
		NAM_SN, 
		Z_SN::decimal(15,2), 
		USL_OK_SN, 
		VIDPOM_SN, 
		VID_HMP_SN, 
		METOD_HMP_SN, 
		PROFIL_SN, 
		CASE WHEN DISP_SN = '1' THEN true ELSE false END AS DISP_SN,
		DS1_SN,
		N_KSG_SN,
		CODE_USL_SN, 
		IDSP_SN::int, 
		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 
		UID_SN text path './UID_SN' null, 
		TF_OKATO_SN text path './TF_OKATO_SN' null, 
		NUM_SN text path './NUM_SN' null, 
		NAM_SN text path './NAM_SN' null, 
		Z_SN text path './Z_SN' null, 
		USL_OK_SN text path './USL_OK_SN' null, 
		VIDPOM_SN text path './VIDPOM_SN' null, 
		VID_HMP_SN text path './VID_HMP_SN' null, 
		METOD_HMP_SN text path './METOD_HMP_SN' null, 
		PROFIL_SN text path './PROFIL_SN' null, 
		DISP_SN text path './DISP_SN' null,
		DS1_SN text path './DS1_SN' null, 
		N_KSG_SN text path './N_KSG_SN' null,
		CODE_USL_SN text path './CODE_USL_SN' null, 
		IDSP_SN text path './IDSP_SN' null, 
		DATEBEG text path './DATEBEG' null, 
		DATEEND text path './DATEEND' null);

	-- обновляем существующие записи
	update ies.t_k005_avg_cost_norm as t2
	set
		 tf_okato_sn	  = t1.tf_okato_sn
		,num_sn           = t1.num_sn         
		,nam_sn			  = t1.nam_sn			
		,z_sn			  = t1.z_sn			
		,usl_ok_sn		  = t1.usl_ok_sn		
		,vidpom_sn		  = t1.vidpom_sn		
		,vid_hmp_sn		  = t1.vid_hmp_sn		
		,metod_hmp_sn	  = t1.metod_hmp_sn	
		,profil_sn		  = t1.profil_sn		
		,disp_sn		  = t1.disp_sn		
		,ds1_sn			  = t1.ds1_sn			
		,n_ksg_sn		  = t1.n_ksg_sn		
		,code_usl_sn	  = t1.code_usl_sn	
		,idsp_sn		  = t1.idsp_sn		
		,datebeg		  = t1.datebeg		
		,dateend		  = t1.dateend	
	from
		temp_k005 as t1
	where
		t1.uid_sn=t2.uid_sn;

	-- вставляем новые записи, если они есть
	insert into ies.t_k005_avg_cost_norm 
	(
		 k005avgcostnormid
		,uid_sn
		,tf_okato_sn	
		,num_sn         
		,nam_sn			
		,z_sn			
		,usl_ok_sn		
		,vidpom_sn		
		,vid_hmp_sn		
		,metod_hmp_sn	
		,profil_sn		
		,disp_sn		
		,ds1_sn			
		,n_ksg_sn		
		,code_usl_sn	
		,idsp_sn		
		,datebeg		
		,dateend		
	)
	select 
		 gen_random_uuid()
		,t1.uid_sn
		,t1.tf_okato_sn	
		,t1.num_sn         
		,t1.nam_sn			
		,t1.z_sn			
		,t1.usl_ok_sn		
		,t1.vidpom_sn		
		,t1.vid_hmp_sn		
		,t1.metod_hmp_sn	
		,t1.profil_sn		
		,t1.disp_sn		
		,t1.ds1_sn			
		,t1.n_ksg_sn		
		,t1.code_usl_sn	
		,t1.idsp_sn		
		,t1.datebeg		
		,t1.dateend		
	from temp_k005 t1
	left join  ies.t_k005_avg_cost_norm  t2 on t1.uid_sn=t2.uid_sn
	where t2.uid_sn is null;

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

