-- =============================================
-- 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;
--$$;