-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника Международных непатентованных наименований (МНН) в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS dbo.sp_rds_cmnn;

CREATE OR REPLACE PROCEDURE APS.sp_rds_cmnn
(
	"Xml" xml DEFAULT NULL::xml
,	"IsNew" boolean DEFAULT '1'
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" character varying DEFAULT 'RDSSync'::character varying
,	"dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
,	"User" uuid DEFAULT NULL::uuid
,	"PATH"  character varying DEFAULT null::character varying
)
    language plpgsql
as
$$
Begin

	Declare	_Result text;
		_ContentEncoding varchar(255) := 'utf-8';
		_Type varchar(255) := 'C_MNN';
		_OperationType varchar(255);
		_OwnerID UUID := "ID";
		-- Operation types
		_INSERT varchar(6) := 'Insert';
		_UPDATE varchar(6) := 'Update';
		_DELETE varchar(6) := 'Delete';
		_FTLOAD varchar(6) := 'FTLoad';
		_sql text;
		_EntId int;
	Begin
		"dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);

		IF "PATH" IS NOT NULL
		then
			DROP TABLE IF EXISTS Temp_C_MNN;
			CREATE TEMP TABLE Temp_C_MNN
			(
				CMnnID				uuid, 
				NameRus				VARCHAR(1000), 
				NameEng				VARCHAR(1000), 
				OtherID				INT, 
				Active				boolean, 
				UsedIn				INT, 
				ChemicalName		VARCHAR(600),
				BruttoFormula		VARCHAR(100),
				Characteristic		VARCHAR(4000), 
				Pharmacology		text, 
				Practice			VARCHAR(6000), 
				Contraindications	VARCHAR(4000), 
				Restrictions		text, 
				UseOfPregnancy		text, 
				SideEffects			text, 
				Interaction			text, 
				InjectionMethod		VARCHAR(2000), 
				Caution				text, 
				Overdose			VARCHAR(4000), 
				CASCode				VARCHAR(50), 
				Indications			VARCHAR(8000), 
				SingleDose			DECIMAL(17),
				MaxDose				DECIMAL(17), 
				SingleDoseView		INT, 
				MaxDoseView			INT, 
				NameLatR			VARCHAR(4000), 
				FedCode				VARCHAR(50), 
				RegCode				VARCHAR(50), 
				EhrRusDvl105		INT, 
				EhrRusDvl105Ver		VARCHAR(8), 
				EhrRusNsv101		INT,
				EhrRusNsv101Ver		VARCHAR(8)
			);
			_sql := 'insert into Temp_C_MNN
				(
					CMnnID, 
					NameRus, 
					NameEng, 
					OtherID, 
					Active, 
					UsedIn, 
					ChemicalName, 
					BruttoFormula, 
					Characteristic, 
					Pharmacology, 
					Practice, 
					Contraindications, 
					Restrictions, 
					UseOfPregnancy, 
					SideEffects, 
					Interaction, 
					InjectionMethod, 
					Caution, 
					Overdose, 
					CASCode, 
					Indications, 
					SingleDose, 
					MaxDose, 
					SingleDoseView, 
					MaxDoseView, 
					NameLatR, 
					FedCode, 
					RegCode, 
					EhrRusDvl105, 
					EhrRusDvl105Ver, 
					EhrRusNsv101, 
					EhrRusNsv101Ver
				) SELECT * FROM dblink(''dbname=' || "PATH" 
				|| ''', SELECT CMnnID, NameRus, NameEng, OtherID, Active, UsedIn, ChemicalName, BruttoFormula, Characteristic, Pharmacology, Practice
				, Contraindications, Restrictions, UseOfPregnancy, SideEffects, Interaction, InjectionMethod, Caution, Overdose, CASCode, Indications
				, SingleDose, MaxDose, SingleDoseView, MaxDoseView, NameLatR, FedCode, RegCode, ehr.IdEHR, ehr.CurrVersion, ehr1.IdEHR, ehr1.CurrVersion
				FROM PHARM.T_C_MNN mnn
				left join lateral
					(
						SELECT er.IdEHR, rrb.CurrVersion 
						FROM EHR.T_EHR_RUS_DVL105 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks						
						WHERE er.EhrRusID = mnn.EhrRusDvl105
						order by rrb.CurrVersion desc
						limit 1
					) ehr on true
				left join lateral
					(
						SELECT er.IdEHR, rrb.CurrVersion 
						FROM EHR.T_EHR_RUS_NSV101 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks						
						WHERE er.EhrRusID = mnn.EhrRusNsv101
						order by rrb.CurrVersion desc
						limit 1
					) ehr1 on true
				'') AS tt(CMnnID uuid, NameRus VARCHAR, NameEng VARCHAR, OtherID INT, Active boolean, UsedIn INT, ChemicalName VARCHAR
					, BruttoFormula VARCHAR, Characteristic VARCHAR, Pharmacology VARCHAR, Practice VARCHAR, Contraindications VARCHAR, Restrictions VARCHAR
					, UseOfPregnancy VARCHAR, SideEffects VARCHAR, Interaction VARCHAR, InjectionMethod VARCHAR, Caution VARCHAR, Overdose VARCHAR, CASCode VARCHAR
					, Indications VARCHAR, SingleDose DECIMAL, MaxDose DECIMAL, SingleDoseView INT, MaxDoseView INT, NameLatR VARCHAR, FedCode VARCHAR, RegCode VARCHAR, EhrRusDvl105 INT, EhrRusDvl105Ver VARCHAR(8), EhrRusNsv101 INT, EhrRusNsv101Ver VARCHAR(8))';

			EXECUTE _SQL;
			
			-- Первичная загрузка
			-- МНН не из справочника RDS переводим в неактивные
			UPDATE PHARM.T_C_MNN c
				SET Active = '0'
			WHERE NOT EXISTS
				(
					SELECT * 
						FROM Temp_C_MNN nc 
					WHERE nc.CMnnID = c.CMnnID
				)
				and c.UsedIn in (1, 3);

			-- Обновляем имеющиеся МНН
			update PHARM.T_C_MNN c
			set	NameEng = nc.NameEng
				,NameRus = nc.NameRus
				,NameLatR = nc.NameLatR
				,Active = nc.Active
				,OtherID = nc.OtherID
				,ChemicalName = nc.ChemicalName
				,BruttoFormula = nc.BruttoFormula
				,Characteristic = nc.Characteristic
				,Pharmacology = nc.Pharmacology
				,Practice = nc.Practice
				,Contraindications = nc.Contraindications
				,Restrictions = nc.Restrictions
				,UseOfPregnancy = nc.UseOfPregnancy
				,SideEffects = nc.SideEffects
				,Interaction = nc.Interaction
				,InjectionMethod = nc.InjectionMethod
				,Caution = nc.Caution
				,Overdose = nc.Overdose
				,CASCode = nc.CASCode
				,UsedIn = nc.UsedIn
				,RegCode = nc.RegCode
				,FedCode = nc.FedCode
				,EhrRusDvl105 = ehr.EhrRusID
				,EhrRusNsv101 = ehr1.EhrRusID
			from Temp_C_MNN nc 
				left join lateral 
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_DVL105 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = nc.EhrRusDvl105 and rrb.CurrVersion = nc.EhrRusDvl105Ver
					limit 1
				) ehr on true
				left join lateral
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_NSV101 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = nc.EhrRusNsv101 and rrb.CurrVersion = nc.EhrRusNsv101Ver
					limit 1
				) ehr1 on true
				where nc.CMnnID = c.CMnnID;
		--where c.UsedIn in (1,3)
		
			-- Загружаем новые
			INSERT INTO PHARM.T_C_MNN
			(
				CMnnID, 
				NameRus, 
				NameEng, 
				Active, 
				OtherID, 
				UsedIn, 
				ChemicalName, 
				BruttoFormula, 
				Characteristic, 
				Pharmacology, 
				Practice, 
				Contraindications, 
				Restrictions, 
				UseOfPregnancy, 
				SideEffects, 
				Interaction, 
				InjectionMethod, 
				Caution, 
				Overdose, 
				CASCode, 
				RegCode, 
				FedCode, 
				NameLatR, 
				EhrRusDvl105, 
				EhrRusNsv101
			)
			SELECT 
				nc.CMnnID, 
				nc.NameRus, 
				nc.NameEng, 
				nc.Active, 
				nc.OtherID, 
				nc.UsedIn, 
				nc.ChemicalName, 
				nc.BruttoFormula, 
				nc.Characteristic, 
				nc.Pharmacology, 
				nc.Practice, 
				nc.Contraindications, 
				nc.Restrictions, 
				nc.UseOfPregnancy, 
				nc.SideEffects, 
				nc.Interaction, 
				nc.InjectionMethod, 
				nc.Caution, 
				nc.Overdose, 
				nc.CASCode, 
				nc.RegCode, 
				nc.FedCode, 
				nc.NameLatR, 
				ehr.EhrRusID, 
				ehr1.EhrRusID
			FROM Temp_C_MNN nc
			left join lateral
			(
				select er.EhrRusID 
				from EHR.T_EHR_RUS_DVL105 er
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				where er.IdEHR = nc.EhrRusDvl105 and rrb.CurrVersion = nc.EhrRusDvl105Ver
				limit 1
			) ehr on true
			left join lateral
			(
				select er.EhrRusID 
				from EHR.T_EHR_RUS_NSV101 er
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				where er.IdEHR = nc.EhrRusNsv101 and rrb.CurrVersion = nc.EhrRusNsv101Ver
				limit 1
			) ehr1 on true
			WHERE NOT EXISTS
				(
					SELECT * 
						FROM PHARM.T_C_MNN c 
					WHERE c.CMnnID = nc.CMnnID
				);
		ELSE	
			if "Xml" is null
			then
				--выясняем тип операции
				if "ID" is null
				then
					_OperationType := _FTLOAD;
				elsif "IsNew" = '1'
				then
					_OperationType := _INSERT;
				elsif "IsNew" = '0' and exists(select* from Pharm.T_C_MNN c where c.CMnnID = "ID")
				then
					_OperationType := _UPDATE;
				elsif "IsNew" = '0' and not exists(select* from Pharm.T_C_MNN c where c.CMnnID = "ID")
				then
					_OperationType := _DELETE;
					_Result := XMLELEMENT(name "C_MNN", XMLELEMENT(name header, XMLFOREST(_OperationType as "TYPE", "ID" as "ID")));
				end if;

				-- Обработка операции
				if _OperationType = _FTLOAD
				then	-- Первичная загрузка
					_Result :=
					XMLELEMENT
					(
						name "C_MNN"
					,	XMLELEMENT
						(
							name header
						,	XMLFOREST
							(
								_FTLOAD as "TYPE"
							)
						)
					,	XMLELEMENT
						(
							name body
						,	xmlelement
							(
								name "C_MNNS"
							,	xmlelement
								(
									name "C_MNN"
								,	(
										select	
										xmlagg
										(
											xmlforest
											(
												c.CMnnID			as "ID"
											,	c.Active			as "Active"
											,	c.NameRus			as "NameRus"
											,	c.NameEng			as "NameEng"
											,	c.OtherID			as "OtherID"
											,	c.ChemicalName		as "ChemicalName"
											,	c.BruttoFormula		as "BruttoFormula"
											,	c.Characteristic	as "Characteristic"
											,	c.Pharmacology		as "Pharmacology"
											,	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.InjectionMethod	as "InjectionMethod"
											,	c.Caution			as "Caution"
											,	c.Overdose			as "Overdose"
											,	c.CASCode			as "CASCode"
											,	c.UsedIn			as "UsedIn"
											,	c.RegCode			as "RegCode"
									  		,	c.FedCode			as "FedCode"
											,	er.IdEHR			as "EhrRusDvl105"
											,	RRB.CurrVersion		as "EhrRusDvl105Ver"
											,	er1.IdEHR			as "EhrRusNsv101"
											,	RRB1.CurrVersion	as "EhrRusNsv101Ver"
											)
										)
										from pharm.T_C_MNN c
										left join EHR.T_EHR_RUS_DVL105 er on er.EhrRusID = bg.EhrRusDvl105
										LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
										left join EHR.T_EHR_RUS_NSV101 er1 on er1.EhrRusID = bg.EhrRusNsv101
										LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er1.EhrRusRefBooks
										where c.UsedIn in (1,3)
									)
								)
							)
						)
					);
				elsif _OperationType <> _DELETE
				then
					-- собираем C_MNN
					select 
					XMLELEMENT
					(
						name "C_MNN"
					,	XMLFOREST
						(
							XMLFOREST
							(
								_OperationType as "TYPE"
							,	"ID" as "ID"
							) as header
						)
					,	XMLFOREST
						(
							XMLFOREST
							(
								c.Active       		as "Active"
							,   c.NameRus      		as "NameRus"
							,   c.NameEng      		as "NameEng"
							,   c.NameLatR      	as "NameLatR"
							,   c.OtherID      		as "OtherID"
							,	c.ChemicalName		as "ChemicalName"
							,	c.BruttoFormula		as "BruttoFormula"
							,	c.Characteristic	as "Characteristic"
							,	c.Pharmacology		as "Pharmacology"
							,	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.InjectionMethod	as "InjectionMethod"
							,	c.Caution			as "Caution"
							,	c.Overdose			as "Overdose"
							,	c.CASCode			as "CASCode"
							,	c.UsedIn			as "UsedIn"
							,	c.RegCode			as "RegCode"
							,	c.FedCode			as "FedCode"
							,	er.IdEHR			as "EhrRusDvl105"
							,	RRB.CurrVersion		as "EhrRusDvl105Ver"
							,	er1.IdEHR			as "EhrRusNsv101"
							,	RRB1.CurrVersion	as "EhrRusNsv101Ver"
							) as body
						)
					)
					into _Result
					from pharm.T_C_MNN c
					left join EHR.T_EHR_RUS_DVL105 er on er.EhrRusID = bg.EhrRusDvl105
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					left join EHR.T_EHR_RUS_NSV101 er1 on er1.EhrRusID = bg.EhrRusNsv101
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er1.EhrRusRefBooks
					where c.CMnnID = "ID"
					and c.UsedIn in (1,3);
				END if;
				-- Отправка
				INSERT INTO APS.T_RABBIT_SEND_OBJECT
					(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
				select 
					gen_random_uuid(),	
					"QueueName", 
					ConvertTextToBinaryZip(_Result, ''), 
					null, 
					null, 
					_ContentEncoding, 
					_Type, 
					"User", 
					0, 
					"dateTime", 
					_OwnerID;
			else 
				if "Xml" is not null
				then
						-- Получаем тип операции
					_OperationType := (xpath('/C_MNN/header[1]/TYPE[1]/text()', "Xml"))[1]::varchar(6);

					-- Generate table Tmp_C_MNNS
					Drop Table If Exists Tmp_C_MNNS;
					Create Temp Table Tmp_C_MNNS
					as
					SELECT
						(xpath('/C_MNN/ID[1]/text()', x))[1]::text::UUID as ID
					,	(xpath('/C_MNN/Active[1]/text()', x))[1]::text::boolean as Active
					,	(xpath('/C_MNN/NameRus[1]/text()', x))[1]::varchar(1000) as NameRus
					,	(xpath('/C_MNN/NameEng[1]/text()', x))[1]::varchar(1000) as NameEng
					,	(xpath('/C_MNN/NameLatR[1]/text()', x))[1]::varchar(4000) as NameLatR
					,	(xpath('/C_MNN/OtherID[1]/text()', x))[1]::varchar(255) as OtherId
					,	(xpath('/C_MNN/ChemicalName[1]/text()', x))[1]::varchar(600) as ChemicalName
					,	(xpath('/C_MNN/BruttoFormula[1]/text()', x))[1]::varchar(100) as BruttoFormula
					,	(xpath('/C_MNN/Characteristic[1]/text()', x))[1]::varchar(4000) as Characteristic
					,	(xpath('/C_MNN/Pharmacology[1]/text()', x))[1]::text as Pharmacology
					,	(xpath('/C_MNN/Practice[1]/text()', x))[1]::varchar(6000) as Practice
					,	(xpath('/C_MNN/Contraindications[1]/text()', x))[1]::varchar(4000) as Contraindications
					,	(xpath('/C_MNN/Restrictions[1]/text()', x))[1]::text as Restrictions
					,	(xpath('/C_MNN/UseOfPregnancy[1]/text()', x))[1]::text as UseOfPregnancy
					,	(xpath('/C_MNN/SideEffects[1]/text()', x))[1]::text as SideEffects
					,	(xpath('/C_MNN/Interaction[1]/text()', x))[1]::text as Interaction
					,	(xpath('/C_MNN/InjectionMethod[1]/text()', x))[1]::varchar(4000) as InjectionMethod
					,	(xpath('/C_MNN/Caution[1]/text()', x))[1]::text as Caution
					,	(xpath('/C_MNN/Overdose[1]/text()', x))[1]::varchar(4000) as Overdose
					,	(xpath('/C_MNN/CASCode[1]/text()', x))[1]::varchar(50) as CASCode
					,	(xpath('/C_MNN/UsedIn[1]/text()', x))[1]::text::int as UsedIn
					,	(xpath('/C_MNN/RegCode[1]/text()', x))[1]::varchar(50) as RegCode
					,	(xpath('/C_MNN/FedCode[1]/text()', x))[1]::varchar(50) as FedCode
					,	(xpath('/C_MNN/EhrRusDvl105[1]/text()', t.x))[1]::text::int as EhrRusDvl105
					,	(xpath('/C_MNN/EhrRusDvl105Ver[1]/text()', t.x))[1]::varchar(8) as EhrRusDvl105Ver
					,	(xpath('/C_MNN/EhrRusNsv101[1]/text()', t.x))[1]::text::int as EhrRusNsv101
					,	(xpath('/C_MNN/EhrRusNsv101Ver[1]/text()', t.x))[1]::varchar(8) as EhrRusNsv101Ver
					from unnest(xpath('/C_MNN/body/C_MNNS/C_MNN', "Xml")) as x
					where _OperationType = _FTLOAD;			

					-- Generate table Tmp_C_MNN
					Drop Table If Exists Tmp_C_MNN;
					Create Temp Table Tmp_C_MNN
					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(1000) as NameRus
					,	(xpath('/body[1]/NameEng[1]/text()', t.x2))[1]::varchar(1000) as NameEng
					,	(xpath('/body[1]/NameLatR[1]/text()', t.x2))[1]::varchar(4000) as NameLatR
					,	(xpath('/body[1]/OtherID[1]/text()', t.x2))[1]::varchar(255) as OtherID
					,	(xpath('/body[1]/ChemicalName[1]/text()', t.x2))[1]::varchar(600) as ChemicalName
					,	(xpath('/body[1]/BruttoFormula[1]/text()', t.x2))[1]::varchar(100) as BruttoFormula
					,	(xpath('/body[1]/Characteristic[1]/text()', t.x2))[1]::varchar(4000) as Characteristic
					,	(xpath('/body[1]/Pharmacology[1]/text()', t.x2))[1]::text as Pharmacology
					,	(xpath('/body[1]/Practice[1]/text()', t.x2))[1]::varchar(6000) 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]/InjectionMethod[1]/text()', t.x2))[1]::varchar(4000) as InjectionMethod
					,	(xpath('/body[1]/Caution[1]/text()', t.x2))[1]::text as Caution
					,	(xpath('/body[1]/Overdose[1]/text()', t.x2))[1]::varchar(4000) as Overdose
					,	(xpath('/body[1]/CASCode[1]/text()', t.x2))[1]::varchar(50) as CASCode
					,	(xpath('/body[1]/UsedIn[1]/text()', t.x2))[1]::text::int as UsedIn
					,	(xpath('/body[1]/RegCode[1]/text()', t.x2))[1]::varchar(50) as RegCode
					,	(xpath('/body[1]/FedCode[1]/text()', t.x2))[1]::varchar(50) as FedCode
					,	(xpath('/body[1]/EhrRusDvl105[1]/text()', t.x2))[1]::text::int as EhrRusDvl105
					,	(xpath('/body[1]/EhrRusDvl105Ver[1]/text()', t.x2))[1]::varchar(8) as EhrRusDvl105Ver
					,	(xpath('/body[1]/EhrRusNsv101[1]/text()', t.x2))[1]::text::int as EhrRusNsv101
					,	(xpath('/body[1]/EhrRusNsv101Ver[1]/text()', t.x2))[1]::varchar(8) as EhrRusNsv101Ver
					from
					(
						select	unnest(xpath('/C_MNN/header', x)) as x1
						,	unnest(xpath('/C_MNN/body', x)) as x2
						from unnest(xpath('/C_MNN', "Xml"::xml)) x
					) t
					where _OperationType <> _FTLOAD;

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

						-- Обновляем имеющиеся МНН
						update pharm.T_C_MNN c
						set	NameEng				= nc.NameEng
							,NameRus			= nc.NameRus
							,NameLatR			= nc.NameLatR
							,OtherID			= nc.OtherID
							,Active				= nc.Active				
							,ChemicalName		= nc.ChemicalName
							,BruttoFormula		= nc.BruttoFormula
							,Characteristic		= nc.Characteristic
							,Pharmacology		= nc.Pharmacology
							,Practice			= nc.Practice
							,Contraindications	= nc.Contraindications
							,Restrictions		= nc.Restrictions
							,UseOfPregnancy		= nc.UseOfPregnancy
							,SideEffects		= nc.SideEffects
							,Interaction		= nc.Interaction
							,InjectionMethod	= nc.InjectionMethod
							,Caution			= nc.Caution
							,Overdose			= nc.Overdose
							,CASCode			= nc.CASCode
							,UsedIn				= nc.UsedIn
							,RegCode			= nc.RegCode
				   			,FedCode			= nc.FedCode
				   			,EhrRusDvl105		= ehr.EhrRusID
				   			,EhrRusNsv101		= ehr1.EhrRusID
						from Tmp_C_MNNS nc 
						left join lateral
						(
							select er.EhrRusID 
							from EHR.T_EHR_RUS_DVL105 er
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							where er.IdEHR = nc.EhrRusDvl105 and RRB.CurrVersion = nc.EhrRusDvl105Ver
							limit 1
						) ehr on true
						left join lateral
						(
							select er.EhrRusID 
							from EHR.T_EHR_RUS_NSV101 er
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							where er.IdEHR = nc.EhrRusNsv101 and RRB.CurrVersion = nc.EhrRusNsv101Ver
							limit 1
						) ehr1 on true
						where nc.ID = c.CMnnID;

						-- Загружаем новые
						INSERT INTO pharm.T_C_MNN
							(
								CMnnID, 
								NameRus, 
								NameEng, 
								Active, 
								OtherID, 
								ChemicalName, 
								BruttoFormula, 
								Characteristic, 
								Pharmacology, 
								Practice, 
								Contraindications, 
								Restrictions, 
								UseOfPregnancy, 
								SideEffects, 
								Interaction, 
								InjectionMethod, 
								Caution, 
								Overdose, 
								CASCode, 
								UsedIn, 
								RegCode, 
								FedCode, 
								NameLatR, 
								EhrRusDvl105, 
								EhrRusNsv101
							)
						SELECT 
							nc.ID, 
							nc.NameRus, 
							nc.NameEng, 
							nc.Active, 
							nc.OtherID, 
							nc.ChemicalName, 
							nc.BruttoFormula, 
							nc.Characteristic,
							nc.Pharmacology, 
							nc.Practice, 
							nc.Contraindications, 
							nc.Restrictions, 
							nc.UseOfPregnancy, 
							nc.SideEffects, 
							nc.Interaction, 
							nc.InjectionMethod, 
							nc.Caution, 
							nc.Overdose, 
							nc.CASCode, 
							nc.UsedIn, 
							nc.RegCode, 
							nc.FedCode, 
							nc.NameLatR, 
							ehr.EhrRusID, 
							ehr1.EhrRusID
						from Tmp_C_MNNS nc
						left join lateral
						(
							select er.EhrRusID 
							from EHR.T_EHR_RUS_DVL105 er
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							where er.IdEHR = nc.EhrRusDvl105 and RRB.CurrVersion = nc.EhrRusDvl105Ver
							limit 1
						) ehr on true
						left join lateral
						(
							select er.EhrRusID 
							from EHR.T_EHR_RUS_NSV101 er
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							where er.IdEHR = nc.EhrRusNsv101 and RRB1.CurrVersion = nc.EhrRusNsv101Ver
							limit 1
						) ehr1 on true
						where not exists
							(
								select * 
								from pharm.T_C_MNN c 
								where c.CMnnID = nc.ID
							);
					elsif _OperationType = _DELETE
					then
						-- Удаление
						update pharm.T_C_MNN c
						set Active = '0'
						from Tmp_C_MNN nc 					
						where nc.ID = c.CMnnID;
					else
						-- update pharm.T_C_MNN
						update pharm.T_C_MNN c
						set	NameEng = nc.NameEng
							,NameRus = nc.NameRus
							,NameLatR = nc.NameLatR
							,Active = nc.Active
							,OtherID = nc.OtherID
							,ChemicalName = nc.ChemicalName
							,BruttoFormula = nc.BruttoFormula
							,Characteristic = nc.Characteristic
							,Pharmacology = nc.Pharmacology
							,Practice = nc.Practice
							,Contraindications = nc.Contraindications
							,Restrictions = nc.Restrictions
							,UseOfPregnancy = nc.UseOfPregnancy
							,SideEffects = nc.SideEffects
							,Interaction = nc.Interaction
							,InjectionMethod = nc.InjectionMethod
							,Caution = nc.Caution
							,Overdose = nc.Overdose
							,CASCode = nc.CASCode
							,UsedIn = nc.UsedIn
							,RegCode = nc.RegCode
				   			,FedCode = nc.FedCode
							,EhrRusDvl105 = ehr.EhrRusID
							,EhrRusNsv101 = ehr1.EhrRusID
						from Tmp_C_MNN nc 		
						left join lateral
						(
							select er.EhrRusID 
							from EHR.T_EHR_RUS_DVL105 er
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							where er.IdEHR = nc.EhrRusDvl105 and RRB.CurrVersion = nc.EhrRusDvl105Ver
							limit 1
						) ehr on true
						left join lateral
						(
							select er.EhrRusID 
							from EHR.T_EHR_RUS_NSV101 er
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							where er.IdEHR = nc.EhrRusNsv101 and RRB1.CurrVersion = nc.EhrRusNsv101Ver
							limit 1
						) ehr1 on true
						where nc.ID = c.CMnnID
						and _OperationType = _UPDATE;

						-- insert pharm.T_C_MNN
						INSERT INTO pharm.T_C_MNN
							(CMnnID, NameRus, NameEng, Active, OtherID, ChemicalName, BruttoFormula, Characteristic, Pharmacology, Practice, Contraindications, 
							   Restrictions, UseOfPregnancy, SideEffects, Interaction, InjectionMethod, Caution, Overdose, CASCode, UsedIn, RegCode, FedCode, NameLatR, EhrRusDvl105, EhrRusNsv101)
						SELECT 
							nc.ID, 
							nc.NameRus, 
							nc.NameEng, 
							nc.Active, 
							nc.OtherID, 
							nc.ChemicalName, 
							nc.BruttoFormula, 
							nc.Characteristic, 
							nc.Pharmacology, 
							nc.Practice, 
							nc.Contraindications, 
							nc.Restrictions, 
							nc.UseOfPregnancy, 
							nc.SideEffects, 
							nc.Interaction, 
							nc.InjectionMethod, 
							nc.Caution, 
							nc.Overdose, 
							nc.CASCode, 
							nc.UsedIn, 
							nc.RegCode, 
							nc.FedCode, 
							nc.NameLatR, 
							ehr.EhrRusID, 
							ehr1.EhrRusID
						from Tmp_C_MNN nc
						left join lateral
						(
							select er.EhrRusID 
							from EHR.T_EHR_RUS_DVL105 er
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							where er.IdEHR = nc.EhrRusDvl105 and rrb.CurrVersion = nc.EhrRusDvl105Ver
							limit 1
						) ehr on true
						left join lateral
						(
							select er.EhrRusID 
							from EHR.T_EHR_RUS_NSV101 er
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							where er.IdEHR = nc.EhrRusNsv101 and rrb.CurrVersion = nc.EhrRusNsv101Ver
							limit 1
						) ehr1 on true
						where not exists
							(
								select * 
								from pharm.T_C_MNN c 
								where c.CMnnID = nc.ID) 
						and _OperationType = _INSERT;
					END if;
					
					_EntId := (select EntityID from VCLib.T_ENTITIES where EntName = 'C_MNN' limit 1);
					if exists 
					(
						select * from VCLib.T_GEN_NUMBERS 
							where EntityID = _EntId and NumberName = 'PHARM.C_MNN' Limit 1
					)
					then
						---- генерируем след. номер
						--perform public.sp_GenerateNumber(@EntId, 'PHARM.C_MNN');
							
						update VCLib.T_GEN_NUMBERS
						set NextNumber = (select MAX(OtherID) from PHARM.T_C_MNN) --+ 1
						where EntityID = _EntId and NumberName = 'PHARM.C_MNN';
					else
						INSERT INTO VCLib.T_GEN_NUMBERS
							(GenNumbersID, EntityID, NumberName, NextNumber)
						VALUES
							(gen_random_uuid(), _EntId, 'PHARM.C_MNN', coalesce((select MAX(OtherID) from PHARM.T_C_MNN), 1));
					end if;
					-- Drop tables
					Drop Table If Exists Tmp_C_MNN;
					Drop Table If Exists Tmp_C_MNNS;
				end if;
			END if;
		end if;
	End;
End;
$$;