-- =============================================
-- Author:        Vitacore (с)
-- Create date: 2015-07-29
-- Description: Загрузка справочника лекарственных форм
-- =============================================
DROP PROCEDURE IF EXISTS sp_UpdateMediactionFormsFromRDS;

CREATE PROCEDURE sp_UpdateMediactionFormsFromRDS
(
	"Xml" xml DEFAULT NULL::xml
,	"QueueName" character varying DEFAULT 'RDSSync'::character varying
,	"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
)
    language plpgsql
as
$$
Begin
	"dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);

	-- Получаем данные из XML
	Drop Table If Exists Tmp_medicationforms;
	Create Temp Table Tmp_medicationforms
	--(Name varchar(255), Code int);
	as
	SELECT	
		nullif(trim(xpath('/MEDICATION_FORM/Name[1]/text()', x)::text,'{}"'),'')::varchar(255) as Name
	,	nullif(trim(xpath('/MEDICATION_FORM/Code[1]/text()', x)::text,'{}"'),'')::int as Code
	from
	(
		select	unnest(xpath('/MEDICATION_FORMS/body/MEDICATION_FORMS/MEDICATION_FORM', x)) as x
		from 
		(
			select "Xml" as x
		) root
	) MEDICATION_FORM;

	-- Загружаем новые лекарственные формы
	INSERT INTO pharm.T_MEDICATION_FORMS(Code, Name, Active)
	SELECT nmf.Code, nmf.Name, '1'
	from Tmp_medicationforms nmf
	where not exists
   (
		select *
		from pharm.T_MEDICATION_FORMS mf
		where mf.Code = nmf.Code
   );

	-- Обновляем имеющиеся лекарственные формы
	update pharm.T_MEDICATION_FORMS
	set Name = nmf.Name
	from Tmp_medicationforms nmf 
	where nmf.Code = pharm.T_MEDICATION_FORMS.Code;
	-- Drop Table
	DROP TABLE if exists Tmp_medicationforms;
End;
$$;