drop procedure if exists sp_GetSetCtrn;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-24
-- Description: Выгрузка/загрузка справочника Торговое наименование препарата (ТРН) в(из) RDS
-- =============================================
CREATE PROCEDURE sp_GetSetCtrn
(
	"Xml" text DEFAULT NULL
	,"IsNew" boolean DEFAULT '1'
	,"ID" uuid DEFAULT NULL::uuid
	,"QueueName" character varying DEFAULT null::character varying
	,"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
	,"User" uuid DEFAULT NULL::uuid
)
as
$$
Begin
    declare 
		_Result text;
		_ContentEncoding varchar(255) := 'utf-8';
		_Type varchar(255) := 'C_TRN';
		_OperationType varchar(255);
		_OwnerID UUID := "ID";
		_EntId int;
	begin

		"dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);
		if ("Xml" is null)
		then
			--выясняем тип операции
			if ("ID" is null)
			then
				_OperationType := 'FTLoad';
			end if;
			elsif ("IsNew" = '1')
			then
				_OperationType := 'Insert';
			end if;
			elsif ("IsNew" = '0' and exists (select * from PHARM.T_C_TRN c where c.CTrnID = "ID"))
			then
			    _OperationType = 'Update';
			end if;
			elsif ("IsNew" = '0' and not exists (select * from PHARM.T_C_TRN c where c.CTrnID = "ID"))
			then
				_OperationType = 'Delete';
				select
				xmlelement
				(
					name "C_TRN",
					xmlforest
					(
						_OperationType as "TYPE"
						,"ID" as "ID"
					)
				)
				into _Result;
			end if;

			if (_OperationType = 'FTLoad')
			then
				select
				xmlelement
				(
					name "C_TRN",
					xmlelement
					(
						name header,
						xmlforest ('FTLoad' as "TYPE")
					),
					xmlelement
					(
						name body,
						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.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.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"
							,atc.IdEHR					as "Atc"
							,RRBAtc.CurrVersion			as "AtcVer"
						)
					)
				)
				into _Result
				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 RRB1.EhrRusRefBooksID = ehr2.EhrRusRefBooks
					left join EHR.T_EHR_RUS_M00002 atc on atc.EhrRusID = c.Atc
					    LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = atc.EhrRusRefBooks;
			end if;
			elsif (_OperationType <> 'Delete')
			then
				select 
				xmlelement 
				(
					name "C_TRN",
					xmlelement
					(
						name header,
						xmlelement
						(
							_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.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.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"
							,atc.IdEHR			    as "Atc"
							,RRBAtc.CurrVersion	    as "AtcVer"
						)
					)
				)
				into _Result
				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 RRB1.EhrRusRefBooksID = ehr2.EhrRusRefBooks
				left join EHR.T_EHR_RUS_M00002 atc on atc.EhrRusID = c.Atc
				    LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = atc.EhrRusRefBooks
                where c.CTrnID = "ID";
			end if;

			INSERT INTO RDS.T_RABBIT_SEND_OBJECT (RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
			select uuid_generate_v4()--(<RabbitSendObjectID, uniqueidentifier,>
			      ,s.ServerName--,<QueueName, varchar(255),>
			      ,ConvertTextToBinaryZip(_Result, '')--,<Content, text,>
			      ,null--,<MessageID, varchar(255),>
			      ,null--,<Headers, varchar(255),>
			      ,_ContentEncoding--,<ContentEncoding, varchar(255),>
			      ,_Type--,<Type, varchar(255),>
			      ,"User"--,<UserID, varchar(255),>
			      ,0--,<State, int,>)
			      ,"dateTime"--,<CreateDateTime, datetime,>)
			      ,_OwnerID--,<OwnerID, varchar(255),>
			from RDS.T_SYNC_SERVERS s
			where ("QueueName" is not null and s.ServerName = "QueueName") or ("QueueName" is null and s.ServerType in (0, 1)); -- Рассылаем пакет всем подписанным серверам AKUZ-а и APS, если не указана очередь
		end if;

		elsif ("Xml" is not null)
		then
			_OperationType := (xpath('/C_TRN/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);
            _OwnerID := (xpath('/C_TRN/header[1]/ID[1]/text()', "Xml"::xml))[1]::text::uuid;
            _Result := "Xml"::xml;

			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/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]::integer							as Atc
				,(xpath('/C_TRN/AtcVer[1]/text()', x))[1]::varchar(52)					as AtcVer
				,(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"::xml)) as x
			where _OperationType = 'FTLoad';

			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]/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]::text::int						as Atc
				,(xpath('/body[1]/AtcVer[1]/text()', t.x2))[1]::varchar(52)					as AtcVer
				,(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(8)		as EhrRusP08TSH954NVer
				,(xpath('/body[1]/EhrRus575VSF88OG[1]/text()', x))[1]::varchar(52)			as EhrRus575VSF88OG
				,(xpath('/body[1]/EhrRus575VSF88OGVer[1]/text()', x))[1]::varchar(8)		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
					,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				= atc.EhrRusID
					,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
				left join lateral
				(
					select eh.EhrRusID 
					from EHR.T_EHR_RUS_M00002 eh
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer limit 1
				) atc on true
				where nc.ID = c.CTrnID;

				-- Загружаем новые
				INSERT INTO pharm.T_C_TRN
				(
					CTrnID, 
					NameRus, 
					NameEng, 
					Active, 
					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.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 ,
					atc.EhrRusID,
					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
				left join lateral
				(
					select eh.EhrRusID 
					from EHR.T_EHR_RUS_M00002 eh
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer limit 1
				) atc on true
				where not exists (select * from PHARM.T_C_TRN c where c.CTrnID = nc.ID);
			end if;
			elsif (_OperationType = 'Delete')
			then
				update PHARM.T_C_TRN c
				set c.Active = '0'
				from Tmp_C_TRN nc
				where nc.ID = c.CTrnID;
			end if;
			else
			then
				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					= atc.EhrRusID
					,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
				left join lateral
				(
					select eh.EhrRusID 
					from EHR.T_EHR_RUS_M00002 eh
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer limit 1
				) atc 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, 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.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 ,
				    atc.EhrRusID,
					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
				left join lateral
				(
					select eh.EhrRusID 
					from EHR.T_EHR_RUS_M00002 eh
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer limit 1
				) atc on true
				where not exists
				(select* from pharm.T_C_TRN c where c.CTrnID = nc.ID) 
				and _OperationType = 'Insert';
			end if;

			INSERT INTO RDS.T_RABBIT_SEND_OBJECT
            (RabbitSendObjectID ,QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
			select uuid_generate_v4()--(<RabbitSendObjectID, uniqueidentifier,>
			      ,s.ServerName--,<QueueName, varchar(255),>
			      ,ConvertTextToBinaryZip(_Result, '')--,<Content, text,>
			      ,null--,<MessageID, varchar(255),>
			      ,null--,<Headers, varchar(255),>
			      ,_ContentEncoding--,<ContentEncoding, varchar(255),>
			      ,_Type--,<Type, varchar(255),>
			      ,"User"--,<UserID, varchar(255),>
			      ,0--,<State, int,>)
			      ,"dateTime"--,<CreateDateTime, datetime,>)
			      ,_OwnerID--,<OwnerID, varchar(255),>
			from RDS.T_SYNC_SERVERS s
			where s.ServerName <> "QueueName" and @OperationType <> 'FTLoad';

			_EntId := (select EntityID from VCLib.T_ENTITIES where EntName = 'C_TRN' limit 1);
			if exists (select* from VCLIB.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.C_TRN' Limit 1)
			then
				update VCLIB.T_GEN_NUMBERS
				set NextNumber = (select MAX(OtherID) from PHARM.T_C_TRN) --+ 1
				where EntityID = _EntId
				and NumberName = 'PHARM.C_TRN';
			end if;
			else
			then
				INSERT INTO VCLIB.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;
End;
$$;
