-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-12-22
-- Description: Выгрузка/загрузка справочника Торговое наименование препарата (ТРН) в(из) RDS
-- =============================================
CREATE OR REPLACE FUNCTION sp_rds_ctrn
(
	"Xml" xml DEFAULT NULL::xml
,	"IsNew" boolean DEFAULT '1'::boolean
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" varchar(100) DEFAULT 'RDSSync'::varchar(100)
,	"dateTime" timestamp DEFAULT NULL::timestamp
,	"User" uuid DEFAULT NULL::uuid
,	"PATH" text DEFAULT NULL::text
)
RETURNS void AS
$BODY$
Declare	_Result Text;
	_ContentEncoding varchar(255);
	_Type varchar(255);
	_OperationType varchar(6);
	_OwnerID UUID;
	-- Operation types
	_INSERT varchar(6);
	_UPDATE varchar(6);
	_DELETE varchar(6);
	_FTLOAD varchar(6);
	_sql text;
	_EntId int;
Begin
	"dateTime" := coalesce("dateTime", Current_TimeStamp::TIMESTAMP);
	-- Operation types
	_INSERT := 'Insert';
	_UPDATE := 'Update';
	_DELETE := 'Delete';
	_FTLOAD := 'FTLoad';

	IF "PATH" IS NOT NULL
	then
		DROP TABLE IF EXISTS Temp_C_TRN;
		CREATE TEMP TABLE Temp_C_TRN(CTrnID uuid, OtherID INT, NameRus VARCHAR, NameEng VARCHAR, Active boolean, IsDrug boolean, Characteristic VARCHAR
			, PharmacologyAction VARCHAR, Practice VARCHAR, Contraindications VARCHAR, Restrictions VARCHAR, UseOfPregnancy VARCHAR, SideEffects VARCHAR
			, Interaction VARCHAR, Overdose VARCHAR, InjectionMethod VARCHAR, Caution VARCHAR, Composition VARCHAR, PharmDescription VARCHAR, Pharmacodynamics VARCHAR
			, Indications VARCHAR, ExpirationDate VARCHAR, MNNName VARCHAR, Manufacturer VARCHAR, CMnn uuid, Atc VARCHAR, ProductType INT, NameLatR VARCHAR
			, FedCode VARCHAR, RegCode VARCHAR, EhrRusP08TSH954N INT, EhrRusP08TSH954NVer VARCHAR, EhrRus575VSF88OG INT, EhrRus575VSF88OGVer VARCHAR);
			
		_sql := 'insert into Temp_C_TRN(CTrnID, OtherID, NameRus, NameEng, Active, IsDrug, Characteristic, PharmacologyAction, Practice, Contraindications
			, Restrictions, UseOfPregnancy, SideEffects, Interaction, Overdose, InjectionMethod, Caution, Composition, PharmDescription, Pharmacodynamics
			, Indications, ExpirationDate, MNNName, Manufacturer, CMnn, Atc, ProductType, NameLatR, FedCode, RegCode, EhrRusP08TSH954N, EhrRusP08TSH954NVer
			, EhrRus575VSF88OG, EhrRus575VSF88OGVer); ' 
			|| ' SELECT trn.BillOfGoodsID, trn.OtherID, trn.Active, trn.CTrn, trn.DoseView, trn.Rate, trn.PackQuantity, trn.IsVital, trn.Name, trn.FederalID
			, trn.ExportCode, trn.EAN, trn.ProductInfo, trn.Dlo, trn.FedCode, trn.RegCode, trn.Gtin, trn.MedicamentsOfRoszdravnadzor, trn.EhrRus2m69i03gtd
			, trn.EhrRus2m69i03gtdVer, trn.EhrRus9a9g09e8kl, trn.EhrRus9a9g09e8klVer, trn.MedicationForms, trn.MedicamentProducer '
			|| 'FROM dblink(''dbname=' || "PATH" 
			|| ''','
			|| ''' SELECT nc.CTrnID, nc.OtherID, nc.NameRus, nc.NameEng, nc.Active, nc.IsDrug, nc.Characteristic, nc.PharmacologyAction, nc.Practice
			, nc.Contraindications, nc.Restrictions, nc.UseOfPregnancy, nc.SideEffects, nc.Interaction, nc.Overdose, nc.InjectionMethod, nc.Caution
			, nc.Composition, nc.PharmDescription, nc.Pharmacodynamics, nc.Indications, nc.ExpirationDate, nc.MNNName, nc.Manufacturer, nc.CMnn, nc.Atc
			, nc.ProductType, nc.NameLatR, nc.FedCode, nc.RegCode, eh1.IdEHR, eh1.CurrVersion, eh2.IdEHR, eh2.CurrVersion
			FROM PHARM.T_C_TRN nc
			left join lateral
			(
				SELECT EH.IDEHR, rrb.CurrVersion 
				FROM EHR.T_EHR_RUS_P08TSH954N EH
				LEFT JOIN
					EHR.T_EHR_RUS_REF_BOOKS RRB 
					ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
				WHERE NC.EHRRUSP08TSH954N = EH.EHRRUSID
				limit 1
			) EH1 on true
			left join lateral
			(
				SELECT EH.IDEHR, RRB.CurrVersion 
				FROM EHR.T_EHR_RUS_575VSF88OG EH
				LEFT JOIN 
					EHR.T_EHR_RUS_REF_BOOKS RRB 
					ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
				WHERE NC.EHRRUS575VSF88OG = EH.EHRRUSID
				limit 1
			) EH2 on true '') ' 
				|| ' AS trn(CTrnID uuid, OtherID INT, NameRus VARCHAR, NameEng VARCHAR, Active boolean, IsDrug boolean, Characteristic VARCHAR
			, PharmacologyAction VARCHAR, Practice VARCHAR, Contraindications VARCHAR, Restrictions VARCHAR, UseOfPregnancy VARCHAR, SideEffects VARCHAR
			, Interaction VARCHAR, Overdose VARCHAR, InjectionMethod VARCHAR, Caution VARCHAR, Composition VARCHAR, PharmDescription VARCHAR, Pharmacodynamics VARCHAR
			, Indications VARCHAR, ExpirationDate VARCHAR, MNNName VARCHAR, Manufacturer VARCHAR, CMnn uuid, Atc VARCHAR, ProductType INT, NameLatR VARCHAR
			, FedCode VARCHAR, RegCode VARCHAR, EhrRusP08TSH954N INT, EhrRusP08TSH954NVer VARCHAR, EhrRus575VSF88OG INT, EhrRus575VSF88OGVer VARCHAR) ';

		EXECUTE _SQL;
			
		-- МНН не из справочника RDS переводим в неактивные
		UPDATE PHARM.T_C_TRN c
		SET Active = '0'
		WHERE NOT EXISTS(SELECT* FROM Temp_C_TRN nc WHERE nc.CTrnID = c.CTrnID);
	
		-- Обновляем имеющиеся TRN
		update PHARM.T_C_TRN c
		set NameEng = nc.NameEng
		,	NameRus = nc.NameRus
		,	NameLatR = nc.NameLatR
		,	Active = nc.Active
		,	IsDrug = nc.IsDrug
		,	OtherID = nc.OtherID
		,	Characteristic = nc.Characteristic
		,	PharmacologyAction = nc.PharmacologyAction
		,	Practice = nc.Practice
		,	Contraindications = nc.Contraindications
		,	Restrictions = nc.Restrictions
		,	UseOfPregnancy = nc.UseOfPregnancy
		,	SideEffects = nc.SideEffects
		,	Interaction = nc.Interaction
		,	Overdose = nc.Overdose
		,	InjectionMethod = nc.InjectionMethod
		,	Caution = nc.Caution
		,	Composition = nc.Composition
		,	PharmDescription = nc.PharmDescription
		,	Pharmacodynamics = nc.Pharmacodynamics
		,	Indications = nc.Indications
		,	ExpirationDate = nc.ExpirationDate
		,	MNNName = nc.MNNName
		,	Manufacturer = nc.Manufacturer
		,	CMnn = nc.CMnn
		,	Atc = nc.Atc
		,	ProductType = nc.ProductType
		,	RegCode = nc.RegCode
		,	FedCode = nc.FedCode
		,	EhrRusP08TSH954N = eh1.EhrRusID
		,	EhrRus575VSF88OG = eh2.EhrRusID
		from Temp_C_TRN nc
		left join lateral
		(
			select eh.EhrRusID 
			from EHR.T_EHR_RUS_P08TSH954N eh
			LEFT JOIN 
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.CodeEHR = nc.EhrRusP08TSH954N and rrb.CurrVersion = nc.EhrRusP08TSH954NVer
			limit 1
		) eh1 on true
		left join lateral
		(
			select eh.EhrRusID 
			from EHR.T_EHR_RUS_575VSF88OG eh
			LEFT JOIN
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.CodeEHR = nc.EhrRus575VSF88OG and rrb.CurrVersion = nc.EhrRus575VSF88OGVer
			limit 1
		) eh2 on true
		where nc.CTrnID = c.CTrnID;
	
		-- Загружаем новые
		INSERT INTO PHARM.T_C_TRN(CTrnID , NameRus, NameEng, Active, IsDrug, OtherID, Characteristic, PharmacologyAction, Practice, Contraindications, Restrictions
				, UseOfPregnancy, SideEffects, Interaction, Overdose, InjectionMethod, Caution, Composition, PharmDescription, Pharmacodynamics, Indications
				, ExpirationDate, MNNName, Manufacturer, CMnn, Atc, ProductType, RegCode, FedCode, EhrRusP08TSH954N, EhrRus575VSF88OG, NameLatR)
		SELECT nc.CTrnID, nc.NameRus, nc.NameEng, nc.Active, nc.IsDrug, nc.OtherID, nc.Characteristic, nc.PharmacologyAction, nc.Practice, nc.Contraindications
		, nc.Restrictions, nc.UseOfPregnancy, nc.SideEffects, nc.Interaction, nc.Overdose, nc.InjectionMethod, nc.Caution, nc.Composition, nc.PharmDescription
		, nc.Pharmacodynamics, nc.Indications, nc.ExpirationDate, nc.MNNName, nc.Manufacturer, nc.CMnn, nc.Atc, nc.ProductType, nc.RegCode, nc.FedCode
		, eh1.EhrRusID, eh2.EhrRusID, nc.NameLatR
		from Temp_C_TRN nc
		left join lateral
		(
			select eh.EhrRusID 
			from EHR.T_EHR_RUS_P08TSH954N eh
			LEFT JOIN 
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.CodeEHR = nc.EhrRusP08TSH954N and rrb.CurrVersion = nc.EhrRusP08TSH954NVer
			limit 1
		) eh1 on true
		left join lateral 
		(
			select eh.EhrRusID 
			from EHR.T_EHR_RUS_575VSF88OG eh
			LEFT JOIN 
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.CodeEHR = nc.EhrRus575VSF88OG and rrb.CurrVersion = nc.EhrRus575VSF88OGVer
			limit 1
		) eh2 on true
		where not exists(select* from PHARM.T_C_TRN c where c.CTrnID = nc.CTrnID);
	ELSE
		if "Xml" is null
		then
			-- Инициализация
			_ContentEncoding := 'utf-8';
			_Type := 'C_TRN';
			_OwnerID := "ID";
			--выясняем тип операции
			IF "ID" is null
			then
				_OperationType := _FTLOAD;
			elsif "IsNew" = '1'
			then
				_OperationType := _INSERT;
			elsif "IsNew" = '0' and exists(select* from Pharm.T_C_TRN c where c.CTrnID = "ID")
			then
				_OperationType := _UPDATE;
			elsif "IsNew" = '0' and not exists(select* from Pharm.T_C_TRN c where c.CTrnID = "ID")
			then
				_OperationType := _DELETE;
				_Result := XMLELEMENT(name "C_TRN", XMLELEMENT(name header, XMLFOREST(_OperationType as "TYPE", "ID" as "ID")));
			end if;

			-- Обработка операции
			if _OperationType = _FTLOAD
			then
				-- Первичная загрузка
				_Result := XMLELEMENT
					  (
						name "C_TRN"
					  ,	XMLELEMENT
						(
							name header
						,	XMLFOREST(_FTLOAD as "TYPE")
						)
					  ,	XMLELEMENT
						(
							name body
						,	xmlelement
							(
								name "C_TRNS"
							,	xmlelement
								(
									name "C_TRN"
								,	(
										select 
										xmlagg
										(
											XMLFOREST
											(
												c.CTrnID  as "ID"
											,	c.Active  as "Active"
											,	c.NameRus as "NameRus"
											,	c.NameEng as "NameEng"
											,	c.NameLatR as "NameLatR"
											,	c.OtherID as "OtherID"
											,	c.IsDrug  as "IsDrug"
											,	c.Characteristic     	as "Characteristic"
											,	c.PharmacologyAction 	as "PharmacologyAction"
											,	c.Practice		as "Practice"
											,	c.Contraindications  	as "Contraindications"
											,	c.Restrictions		as "Restrictions"
											,	c.UseOfPregnancy	as "UseOfPregnancy"
											,	c.SideEffects		as "SideEffects"
											,	c.Interaction		as "Interaction"
											,	c.Overdose		as "Overdose"
											,	c.InjectionMethod	as "InjectionMethod"
											,	c.Caution		as "Caution"
											,	c.Composition		as "Composition"
											,	c.PharmDescription	as "PharmDescription"
											,	c.Pharmacodynamics	as "Pharmacodynamics"
											,	c.Indications		as "Indications"
											,	c.ExpirationDate	as "ExpirationDate"
											,	c.MNNName		as "MNNName"
											,	c.Manufacturer		as "Manufacturer"
											,	c.CMnn			as "CMnn"
											,	c.Atc			as "Atc"
											,	c.ProductType		as "ProductType"
											,	c.RegCode			as "RegCode"
								  			,	c.FedCode			as "FedCode"
								  			,	ehr1.CodeEHR		as "EhrRusP08TSH954N"
								  			,	RRB.CurrVersion		as "EhrRusP08TSH954NVer"
								  			,	ehr2.CodeEHR		as "EhrRus575VSF88OG"
								  			,	RRB1.CurrVersion	as "EhrRus575VSF88OGVer"
											)
										)
										from pharm.T_C_TRN c
										left join 
											EHR.T_EHR_RUS_P08TSH954N ehr1 
											on ehr1.EhrRusID = c.EhrRusP08TSH954N
										LEFT JOIN 
											EHR.T_EHR_RUS_REF_BOOKS RRB 
											ON RRB.EhrRusRefBooksID = ehr1.EhrRusRefBooks
										left join 
											EHR.T_EHR_RUS_575VSF88OG ehr2 
											on ehr2.EhrRusID = c.EhrRus575VSF88OG
										LEFT JOIN 
											EHR.T_EHR_RUS_REF_BOOKS RRB1 
											ON RRB.EhrRusRefBooksID = ehr2.EhrRusRefBooks
									)
								)
							)
						)
					  );	
			elsif _OperationType <> _DELETE
			then
				-- собираем C_TRN
				select 
				XMLELEMENT
				(
					name "C_TRN"
				,	XMLELEMENT
					(
						name header
					,	XMLFOREST
						(
							_OperationType as "TYPE"
						,	"ID" as "ID"
						)
					)
				,	XMLELEMENT
					(
						name body
					,	XMLFOREST
						(
							c.Active       as "Active"
						,   c.NameRus      as "NameRus"
						,   c.NameEng      as "NameEng"
						,   c.NameLatR      as "NameLatR"
						,   c.OtherID      as "OtherID"
						,	c.IsDrug       as "IsDrug"
						,	c.Characteristic	as "Characteristic"
						,	c.PharmacologyAction	as "PharmacologyAction"
						,	c.Practice		as "Practice"
						,	c.Contraindications 	as "Contraindications"
						,	c.Restrictions		as "Restrictions"
						,	c.UseOfPregnancy	as "UseOfPregnancy"
						,	c.SideEffects		as "SideEffects"
						,	c.Interaction		as "Interaction"
						,	c.Overdose		as "Overdose"
						,	c.InjectionMethod	as "InjectionMethod"
						,	c.Caution		as "Caution"
						,	c.Composition		as "Composition"
						,	c.PharmDescription	as "PharmDescription"
						,	c.Pharmacodynamics	as "Pharmacodynamics"
						,	c.Indications		as "Indications"
						,	c.ExpirationDate	as "ExpirationDate"
						,	c.MNNName		as "MNNName"
						,	c.Manufacturer		as "Manufacturer"
						,	c.CMnn			as "CMnn"
						,	c.Atc			as "Atc"
						,	c.ProductType		as "ProductType"
						,	c.RegCode			AS "RegCode"
						,	c.FedCode			AS "FedCode"
						,	ehr1.CodeEHR		AS "EhrRusP08TSH954N"
						,	RRB.CurrVersion		AS "EhrRusP08TSH954NVer"
						,	ehr2.CodeEHR		AS "EhrRus575VSF88OG"
						,	RRB1.CurrVersion	AS "EhrRus575VSF88OGVer"
						)
					)
				)				
				from pharm.T_C_TRN c
				left join 
					EHR.T_EHR_RUS_P08TSH954N ehr1 
					on ehr1.EhrRusID = c.EhrRusP08TSH954N
				LEFT JOIN 
					EHR.T_EHR_RUS_REF_BOOKS RRB 
					ON RRB.EhrRusRefBooksID = ehr1.EhrRusRefBooks
				left join 
					EHR.T_EHR_RUS_575VSF88OG ehr2 
					on ehr2.EhrRusID = c.EhrRus575VSF88OG
				LEFT JOIN 
					EHR.T_EHR_RUS_REF_BOOKS RRB1 
					ON RRB.EhrRusRefBooksID = ehr2.EhrRusRefBooks
				into _Result
				where c.CTrnID = "ID";
			end if;

			-- Отправка
			INSERT INTO akuz.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
			select uuid_generate_v4(), "QueueName", ConvertTextToBinaryZip(_Result, ''), null, null, _ContentEncoding, _Type, "User", 0, "dateTime", _OwnerID;
		else
			if "Xml" is not null
			then      
				-- Получаем тип операции
				_OperationType := (xpath('/C_TRN/header[1]/TYPE[1]/text()', "Xml"))[1]::varchar(36);

				-- Generate table Tmp_C_TRNS
				Drop Table If Exists Tmp_C_TRNS;
				Create Temp Table Tmp_C_TRNS
				as 
				select	(xpath('/C_TRN/ID[1]/text()', x))[1]::text::UUID as ID
				,	(xpath('/C_TRN/Active[1]/text()', x))[1]::text::boolean as Active
				,	(xpath('/C_TRN/NameRus[1]/text()', x))[1]::varchar(4000) as NameRus
				,	(xpath('/C_TRN/NameEng[1]/text()', x))[1]::varchar(4000) as NameEng
				,	(xpath('/C_TRN/NameLatR[1]/text()', x))[1]::varchar(4000) as NameLatR
				,	(xpath('/C_TRN/OtherID[1]/text()', x))[1]::text::int as OtherId
				,	(xpath('/C_TRN/IsDrug[1]/text()', x))[1]::text::boolean as IsDrug
				,	(xpath('/C_TRN/Characteristic[1]/text()', x))[1]::varchar(4000) as Characteristic
				,	(xpath('/C_TRN/PharmacologyAction[1]/text()', x))[1]::varchar(4000) as PharmacologyAction
				,	(xpath('/C_TRN/Practice[1]/text()', x))[1]::text as Practice
				,	(xpath('/C_TRN/Contraindications[1]/text()', x))[1]::varchar(4000) as Contraindications
				,	(xpath('/C_TRN/Restrictions[1]/text()', x))[1]::text as Restrictions
				,	(xpath('/C_TRN/UseOfPregnancy[1]/text()', x))[1]::text as UseOfPregnancy
				,	(xpath('/C_TRN/SideEffects[1]/text()', x))[1]::text as SideEffects
				,	(xpath('/C_TRN/Interaction[1]/text()', x))[1]::text as Interaction
				,	(xpath('/C_TRN/Overdose[1]/text()', x))[1]::varchar(1000) as Overdose
				,	(xpath('/C_TRN/InjectionMethod[1]/text()', x))[1]::varchar(255) as InjectionMethod
				,	(xpath('/C_TRN/Caution[1]/text()', x))[1]::text as Caution
				,	(xpath('/C_TRN/Composition[1]/text()', x))[1]::varchar(255) as Composition
				,	(xpath('/C_TRN/PharmDescription[1]/text()', x))[1]::text as PharmDescription
				,	(xpath('/C_TRN/Pharmacodynamics[1]/text()', x))[1]::text as Pharmacodynamics
				,	(xpath('/C_TRN/Indications[1]/text()', x))[1]::varchar(8000) as Indications
				,	(xpath('/C_TRN/ExpirationDate[1]/text()', x))[1]::varchar(300) as ExpirationDate
				,	(xpath('/C_TRN/MNNName[1]/text()', x))[1]::varchar(8000) as MNNName
				,	(xpath('/C_TRN/Manufacturer[1]/text()', x))[1]::varchar(3000) as Manufacturer
				,	(xpath('/C_TRN/CMnn[1]/text()', x))[1]::text::UUID as CMnn
				,	(xpath('/C_TRN/Atc[1]/text()', x))[1]::varchar(7) as Atc
				,	(xpath('/C_TRN/ProductType[1]/text()', x))[1]::text::int as ProductType
				,	(xpath('/C_TRN/RegCode[1]/text()', x))[1]::varchar(50) as RegCode
				,	(xpath('/C_TRN/FedCode[1]/text()', x))[1]::varchar(50) as FedCode
				,	(xpath('/C_TRN/EhrRusP08TSH954N[1]/text()', x))[1]::varchar(52) as EhrRusP08TSH954N
				,	(xpath('/C_TRN/EhrRusP08TSH954NVer[1]/text()', x))[1]::varchar(52) as EhrRusP08TSH954NVer
				,	(xpath('/C_TRN/EhrRus575VSF88OG[1]/text()', x))[1]::varchar(52) as EhrRus575VSF88OG
				,	(xpath('/C_TRN/EhrRus575VSF88OGVer[1]/text()', x))[1]::varchar(52) as EhrRus575VSF88OGVer
				from unnest(xpath('/C_TRN/body/C_TRNS/C_TRN', "Xml")) as x
				where _OperationType = _FTLOAD;

				-- Generate table Tmp_C_TRN
				Drop Table If Exists Tmp_C_TRN;
				Create Temp Table Tmp_C_TRN
				as
				SELECT	(xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::UUID as ID
				,	(xpath('/body[1]/Active[1]/text()', t.x2))[1]::text::boolean as Active
				,	(xpath('/body[1]/NameRus[1]/text()', t.x2))[1]::varchar(4000) as NameRus
				,	(xpath('/body[1]/NameEng[1]/text()', t.x2))[1]::varchar(4000) as NameEng
				,	(xpath('/body[1]/NameLatR[1]/text()', t.x2))[1]::varchar(4000) as NameLatR
				,	(xpath('/body[1]/OtherID[1]/text()', t.x2))[1]::text::int as OtherID
				,	(xpath('/body[1]/IsDrug[1]/text()', t.x2))[1]::text::boolean as IsDrug
				,	(xpath('/body[1]/Characteristic[1]/text()', t.x2))[1]::varchar(4000) as Characteristic
				,	(xpath('/body[1]/PharmacologyAction[1]/text()', t.x2))[1]::varchar(4000) as PharmacologyAction
				,	(xpath('/body[1]/Practice[1]/text()', t.x2))[1]::text as Practice
				,	(xpath('/body[1]/Contraindications[1]/text()', t.x2))[1]::varchar(4000) as Contraindications
				,	(xpath('/body[1]/Restrictions[1]/text()', t.x2))[1]::text as Restrictions
				,	(xpath('/body[1]/UseOfPregnancy[1]/text()', t.x2))[1]::text as UseOfPregnancy
				,	(xpath('/body[1]/SideEffects[1]/text()', t.x2))[1]::text as SideEffects
				,	(xpath('/body[1]/Interaction[1]/text()', t.x2))[1]::text as Interaction
				,	(xpath('/body[1]/Overdose[1]/text()', t.x2))[1]::varchar(1000) as Overdose
				,	(xpath('/body[1]/InjectionMethod[1]/text()', t.x2))[1]::varchar(255) as InjectionMethod
				,	(xpath('/body[1]/Caution[1]/text()', t.x2))[1]::text as Caution
				,	(xpath('/body[1]/Composition[1]/text()', t.x2))[1]::varchar(255) as Composition
				,	(xpath('/body[1]/PharmDescription[1]/text()', t.x2))[1]::text as PharmDescription
				,	(xpath('/body[1]/Pharmacodynamics[1]/text()', t.x2))[1]::text as Pharmacodynamics
				,	(xpath('/body[1]/Indications[1]/text()', t.x2))[1]::varchar(8000) as Indications
				,	(xpath('/body[1]/ExpirationDate[1]/text()', t.x2))[1]::varchar(300) as ExpirationDate
				,	(xpath('/body[1]/MNNName[1]/text()', t.x2))[1]::text::varchar(8000) as MNNName
				,	(xpath('/body[1]/Manufacturer[1]/text()', t.x2))[1]::varchar(3000) as Manufacturer
				,	(xpath('/body[1]/CMnn[1]/text()', t.x2))[1]::text::UUID as CMnn
				,	(xpath('/body[1]/Atc[1]/text()', t.x2))[1]::varchar(7) as Atc
				,	(xpath('/body[1]/ProductType[1]/text()', t.x2))[1]::text::int as ProductType
				,	(xpath('/body[1]/RegCode[1]/text()', x))[1]::varchar(50) as RegCode
				,	(xpath('/body[1]/FedCode[1]/text()', x))[1]::varchar(50) as FedCode
				,	(xpath('/body[1]/EhrRusP08TSH954N[1]/text()', x))[1]::varchar(52) as EhrRusP08TSH954N
				,	(xpath('/body[1]/EhrRusP08TSH954NVer[1]/text()', x))[1]::varchar(52) as EhrRusP08TSH954NVer
				,	(xpath('/body[1]/EhrRus575VSF88OG[1]/text()', x))[1]::varchar(52) as EhrRus575VSF88OG
				,	(xpath('/body[1]/EhrRus575VSF88OGVer[1]/text()', x))[1]::varchar(52) as EhrRus575VSF88OGVer
				from
				(
					select	unnest(xpath('/C_TRN/header', x)) as x1
					,	unnest(xpath('/C_TRN/body', x)) as x2
					from unnest(xpath('/C_TRN', "Xml"::xml)) x
				) t
				where _OperationType <> _FTLOAD;

				-- Обработка операции
				if _OperationType = _FTLOAD
				then
					-- Первичная загрузка
					-- МНН не из справочника RDS переводим в неактивные
					update pharm.T_C_TRN c
					set Active = '0'
					where not exists(select* from Tmp_C_TRNS nc where nc.ID = c.CTrnID);

					-- Обновляем имеющиеся TRN
					update pharm.T_C_TRN c		
					set	NameEng = nc.NameEng
					,	NameRus = nc.NameRus
					,	NameLatR = nc.NameLatR
					,	Active = nc.Active
					,	IsDrug = nc.IsDrug
					,	OtherID = nc.OtherID
					,	Characteristic = nc.Characteristic
					,	PharmacologyAction = nc.PharmacologyAction
					,	Practice = nc.Practice
					,	Contraindications = nc.Contraindications
					,	Restrictions = nc.Restrictions
					,	UseOfPregnancy = nc.UseOfPregnancy
					,	SideEffects = nc.SideEffects
					,	Interaction = nc.Interaction
					,	Overdose = nc.Overdose
					,	InjectionMethod = nc.InjectionMethod
					,	Caution = nc.Caution
					,	Composition = nc.Composition
					,	PharmDescription = nc.PharmDescription
					,	Pharmacodynamics = nc.Pharmacodynamics
					,	Indications = nc.Indications
					,	ExpirationDate = nc.ExpirationDate
					,	MNNName = nc.MNNName
					,	Manufacturer = nc.Manufacturer
					,	CMnn = nc.CMnn
					,	Atc = nc.Atc
					,	ProductType = nc.ProductType
					,	RegCode = nc.RegCode
			   		,	FedCode = nc.FedCode
			   		,	EhrRusP08TSH954N = eh1.EhrRusID
			   		,	EhrRus575VSF88OG = eh2.EhrRusID
					from Tmp_C_TRNS nc 
					left join lateral
					(
						select eh.EhrRusID 
						from EHR.T_EHR_RUS_P08TSH954N eh
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
						where eh.CodeEHR = nc.EhrRusP08TSH954N and rrb.CurrVersion = nc.EhrRusP08TSH954NVer
						limit 1
					) eh1 on true
					left join lateral
					(
						select eh.EhrRusID 
						from EHR.T_EHR_RUS_575VSF88OG eh
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
						where eh.CodeEHR = nc.EhrRus575VSF88OG and rrb.CurrVersion = nc.EhrRus575VSF88OGVer
						limit 1
					) eh2 on true
					where nc.ID = c.CTrnID;

					-- Загружаем новые
					INSERT INTO pharm.T_C_TRN(CTrnID, NameRus, NameEng, Active, IsDrug, OtherID, Characteristic, PharmacologyAction
					, Practice, Contraindications, Restrictions, UseOfPregnancy, SideEffects, Interaction, Overdose, InjectionMethod
					, Caution, Composition, PharmDescription, Pharmacodynamics, Indications, ExpirationDate, MNNName, Manufacturer
					, CMnn, Atc, ProductType, RegCode, FedCode, EhrRusP08TSH954N, EhrRus575VSF88OG, NameLatR)
					SELECT nc.ID, nc.NameRus, nc.NameEng, nc.Active, nc.IsDrug, nc.OtherID, nc.Characteristic, nc.PharmacologyAction
					, nc.Practice, nc.Contraindications, nc.Restrictions, nc.UseOfPregnancy, nc.SideEffects, nc.Interaction
					, nc.Overdose, nc.InjectionMethod, nc.Caution, nc.Composition, nc.PharmDescription, nc.Pharmacodynamics
					, nc.Indications, nc.ExpirationDate, nc.MNNName, nc.Manufacturer, nc.CMnn, nc.Atc, nc.ProductType, nc.RegCode
					, nc.FedCode, eh1.EhrRusID, eh2.EhrRusID, nc.NameLatR
					from Tmp_C_TRNS nc
					left join lateral
					(
						select eh.EhrRusID 
						from EHR.T_EHR_RUS_P08TSH954N eh
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
						where eh.CodeEHR = nc.EhrRusP08TSH954N and rrb.CurrVersion = nc.EhrRusP08TSH954NVer
						limit 1
					) eh1 on true
					left join lateral
					(
						select eh.EhrRusID 
						from EHR.T_EHR_RUS_575VSF88OG eh
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
						where eh.CodeEHR = nc.EhrRus575VSF88OG and rrb.CurrVersion = nc.EhrRus575VSF88OGVer
						limit 1
					) eh2 on true
					where not exists(select* from Pharm.T_C_TRN c where c.CTrnID = nc.ID);

					---- Удаляем неактивные, непривязанные трн
					--delete from pharm.T_C_TRN c
					--where c.Active = '0'
					--and not exists(select* from pharm.T_BILL_OF_GOODS bg where bg.CTrn = c.CTrnID);
				elsif _OperationType = _DELETE
				then
					-- Удаление
					update pharm.T_C_TRN c
					set Active = '0'
					from Tmp_C_TRN nc 
					where nc.ID = c.CTrnID;
				else
					-- update pharm.T_C_TRN
					update pharm.T_C_TRN c
					set NameEng = nc.NameEng
					,	NameRus = nc.NameRus
					,	NameLatR = nc.NameLatR
					,	OtherID = nc.OtherID
					,	Characteristic = nc.Characteristic
					,	PharmacologyAction = nc.PharmacologyAction
					,	Practice = nc.Practice
					,	Contraindications = nc.Contraindications
					,	Restrictions = nc.Restrictions
					,	UseOfPregnancy = nc.UseOfPregnancy
					,	SideEffects = nc.SideEffects
					,	Interaction = nc.Interaction
					,	Overdose = nc.Overdose
					,	InjectionMethod = nc.InjectionMethod
					,	Caution = nc.Caution
					,	Composition = nc.Composition
					,	PharmDescription = nc.PharmDescription
					,	Pharmacodynamics = nc.Pharmacodynamics
					,	Indications = nc.Indications
					,	ExpirationDate = nc.ExpirationDate
					,	MNNName = nc.MNNName
					,	Manufacturer = nc.Manufacturer
					,	CMnn = nc.CMnn
					,	Atc = nc.Atc
					,	ProductType = nc.ProductType
					,	RegCode = nc.RegCode
					,	FedCode = nc.FedCode
					,	EhrRusP08TSH954N = eh1.EhrRusID
					,	EhrRus575VSF88OG = eh2.EhrRusID
					from Tmp_C_TRN nc
					left join lateral
					(
						select eh.EhrRusID 
						from EHR.T_EHR_RUS_P08TSH954N eh
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
						where eh.CodeEHR = nc.EhrRusP08TSH954N and rrb.CurrVersion = nc.EhrRusP08TSH954NVer
						limit 1
					) eh1 on true
					left join lateral
					(
						select eh.EhrRusID 
						from EHR.T_EHR_RUS_575VSF88OG eh
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
						where eh.CodeEHR = nc.EhrRus575VSF88OG and rrb.CurrVersion = nc.EhrRus575VSF88OGVer
						limit 1
					) eh2 on true
					where nc.ID = c.CTrnID 
					and _OperationType = _UPDATE;

					-- insert pharm.T_C_TRN
					INSERT INTO pharm.T_C_TRN(CTrnID, NameRus, NameEng, Active, IsDrug, OtherID, Characteristic, PharmacologyAction
					, Practice, Contraindications, Restrictions, UseOfPregnancy, SideEffects, Interaction, Overdose, InjectionMethod
					, Caution, Composition, PharmDescription, Pharmacodynamics, Indications, ExpirationDate, MNNName, Manufacturer
					, CMnn, Atc, ProductType, RegCode, FedCode, EhrRusP08TSH954N, EhrRus575VSF88OG, NameLatR)
					SELECT nc.ID, nc.NameRus, nc.NameEng, nc.Active, nc.IsDrug, nc.OtherID, nc.Characteristic, nc.PharmacologyAction
					, nc.Practice, nc.Contraindications, nc.Restrictions, nc.UseOfPregnancy, nc.SideEffects, nc.Interaction
					, nc.Overdose, nc.InjectionMethod, nc.Caution, nc.Composition, nc.PharmDescription, nc.Pharmacodynamics
					, nc.Indications, nc.ExpirationDate, nc.MNNName, nc.Manufacturer, nc.CMnn, nc.Atc, nc.ProductType
					, nc.RegCode, nc.FedCode, eh1.EhrRusID, eh2.EhrRusID, nc.NameLatR
					from Tmp_C_TRN nc
					left join lateral
					(
						select eh.EhrRusID 
						from EHR.T_EHR_RUS_P08TSH954N eh
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
						where eh.CodeEHR = nc.EhrRusP08TSH954N and rrb.CurrVersion = nc.EhrRusP08TSH954NVer
						limit 1
					) eh1 on true
					left join lateral
					(
						select eh.EhrRusID 
						from EHR.T_EHR_RUS_575VSF88OG eh
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
						where eh.CodeEHR = nc.EhrRus575VSF88OG and rrb.CurrVersion = nc.EhrRus575VSF88OGVer
						limit 1
					) eh2 on true
					where not exists(select* from pharm.T_C_TRN c where c.CTrnID = nc.ID) 
					and _OperationType = _INSERT;
				END if;
				
				_EntId := (select EntityID from VCLib.T_ENTITIES where EntName = 'C_TRN' limit 1);
				if exists 
				(
					select* from AKUZ.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.C_TRN' Limit 1
				)
				then
					---- генерируем след. номер
					--perform public.sp_GenerateNumber(@EntId, 'PHARM.C_TRN');
						
					update AKUZ.T_GEN_NUMBERS
					set NextNumber = (select MAX(OtherID) from PHARM.T_C_TRN) --+ 1
					where EntityID = _EntId
					and NumberName = 'PHARM.C_TRN';
				else
					INSERT INTO AKUZ.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
					VALUES(uuid_generate_v4(), _EntId, 'PHARM.C_TRN', coalesce((select MAX(OtherID) from PHARM.T_C_TRN), 1));
				end if;
				
				DROP TABLE if exists Tmp_C_TRN;
				DROP TABLE if exists Tmp_C_TRNS;
			END if;
		end if;
	end if;
End;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_rds_ctrn(xml, boolean, uuid, varchar(100), timestamp, uuid, text)
  OWNER TO postgres;