DROP PROCEDURE IF EXISTS sp_f044spekmpload;
DROP FUNCTION IF EXISTS sp_f044spekmpload;

CREATE PROCEDURE public.sp_f044spekmpload(
	IN "Xml" bytea,
	IN "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_f044;
	
	CREATE TEMP TABLE temp_f044 as
	SELECT 
		IDEKMP, 
		OKTMO_P, 
		SPEC_NAMES::int, 
		STAZH::int, 
		EKS::int, 
		ZSL::int, 
		KMO::int, 
		ZAK::int, 
		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 
		IDEKMP text path './IDEKMP' null, 
		OKTMO_P text path './OKTMO_P' null, 
		SPEC_NAMES text path './SPEC_NAMES' null, 
		STAZH text path './STAZH' null, 
		EKS text path './EKS' null, 
		ZSL text path './ZSL' null, 
		KMO text path './KMO' null, 
		ZAK text path './ZAK' null, 
		DATEBEG text path './DATEBEG' null, 
		DATEEND text path './DATEEND' null );

	
	UPDATE IES.T_F044_SPEKMP t2  
		SET 
		 STAZH     	 = t1.STAZH
        ,EKS		 = t1.EKS
        ,ZSL		 = t1.ZSL
        ,KMO		 = t1.KMO
        ,ZAK		 = t1.ZAK
        ,DATEBEG	 = t1.DATEBEG
        ,DATEEND	 = t1.DATEEND
	FROM temp_f044 t1 
    WHERE  t1.IDEKMP = t2.IDEKMP and t1.OKTMO_P = t2.OKTMO_P and t1.SPEC_NAMES = t2.SPEC_NAMES;

	INSERT INTO IES.T_F044_SPEKMP
			   (F044SpekmpID
			   ,IDEKMP
			   ,OKTMO_P
			   ,SPEC_NAMES
			   ,STAZH
			   ,EKS
			   ,ZSL
			   ,KMO
			   ,ZAK
			   ,DATEBEG
			   ,DATEEND)
	   SELECT  
				gen_random_uuid()
			   ,t1.IDEKMP
			   ,t1.OKTMO_P
			   ,t1.SPEC_NAMES
			   ,t1.STAZH
			   ,t1.EKS
			   ,t1.ZSL
			   ,t1.KMO
			   ,t1.ZAK
			   ,t1.DATEBEG
			   ,t1.DATEEND
	   FROM temp_f044 t1 
	   LEFT JOIN IES.T_F044_SPEKMP t2 on t1.IDEKMP = t2.IDEKMP and t1.OKTMO_P = t2.OKTMO_P and t1.SPEC_NAMES = t2.SPEC_NAMES
	   WHERE t2.F044SpekmpID is null;


	DROP TABLE IF EXISTS temp_f044;

	return;
End
$BODY$;


