-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника Форм выпуска препарата(TRN) в(из) RDS
-- =============================================
CREATE OR REPLACE FUNCTION sp_rds_billofgoods
(
	"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(255);
	_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_BILL_OF_GOODS_DOSE;
		CREATE TEMP TABLE TEMP_BILL_OF_GOODS_DOSE(BillOfGoodsID uuid, OtherID INT, Active BIT, CTrn uuid, DoseView INT, Rate INT, PackQuantity INT, IsVital boolean
												  , Name VARCHAR, FederalID INT, ExportCode INT, EAN VARCHAR, ProductInfo VARCHAR, Dlo boolean, FedCode VARCHAR
												  , RegCode VARCHAR, Gtin VARCHAR, MedicamentsOfRoszdravnadzor uuid, EhrRus2m69i03gtd INT, EhrRus2m69i03gtdVer VARCHAR(8)
												  , EhrRus9a9g09e8kl INT, EhrRus9a9g09e8klVer VARCHAR(8), MedicationForms uuid, MedicamentProducer uuid, EhrRusClp14fvirj INT, EhrRusClp14fvirjVer VARCHAR(8));

		_sql := 'insert into TEMP_BILL_OF_GOODS_DOSE(BillOfGoodsID, OtherID, Active, CTrn, DoseView, Rate, PackQuantity, IsVital, Name, FederalID, ExportCode, EAN, ProductInfo, Dlo
		, FedCode, RegCode, Gtin, MedicamentsOfRoszdravnadzor, EhrRus2m69i03gtd, EhrRus2m69i03gtdVer, EhrRus9a9g09e8kl, EhrRus9a9g09e8klVer, MedicationForms, MedicamentProducer, EhrRusClp14fvirj, EhrRusClp14fvirjVer) ' 
			|| ' SELECT bg.BillOfGoodsID, bg.OtherID, bg.Active, bg.CTrn, bg.DoseView, bg.Rate, bg.PackQuantity, bg.IsVital, bg.Name, bg.FederalID, bg.ExportCode, bg.EAN, bg.ProductInfo
			, bg.Dlo, bg.FedCode, bg.RegCode, bg.Gtin, bg.MedicamentsOfRoszdravnadzor, bg.EhrRus2m69i03gtd, bg.EhrRus2m69i03gtdVer, bg.EhrRus9a9g09e8kl, bg.EhrRus9a9g09e8klVer
			, bg.MedicationForms, bg.MedicamentProducer, bg.EhrRusClp14fvirj, bg.EhrRusClp14fvirjVer'
			|| 'FROM dblink(''dbname=' || "PATH" 
			|| ''','
			|| ''' SELECT bgl.BillOfGoodsID, bgl.OtherID, bgl.Active, bgl.CTrn, dv.Value, bgl.Rate, bgl.PackQuantity, bgl.IsVital, bgl.Name, bgl.FederalID, bgl.ExportCode
			, bgl.EAN, bgl.ProductInfo, bgl.Dlo, bgl.FedCode, bgl.RegCode, bgl.Gtin, bgl.MedicamentsOfRoszdravnadzor, ehr.IdEHR, ehr.CurrVersion
			, ehr1.IdEHR, ehr1.CurrVersion, bgl.MedicationForms, bgl.MedicamentProducer, ehr2.IdEHR, ehr2.CurrVersion
				FROM PHARM.T_BILL_OF_GOODS bgl
				left join PHARM.S_DLO_DoseView dv on dv.id = bgl.DoseView
				left join lateral
				(
					SELECT er.IdEHR, rrb.CurrVersion 
					FROM EHR.T_EHR_RUS_2M69I03GTD er 
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					WHERE er.EhrRusID = bgl.EhrRus2m69i03gtd
					order by rrb.CurrVersion desc
					limit 1
				) ehr on true
				left join lateral
				(
					SELECT er.IdEHR, rrb.CurrVersion 
					FROM EHR.T_EHR_RUS_9A9G09E8KL er
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks						
					WHERE er.EhrRusID = bgl.EhrRus9a9g09e8kl
					order by rrb.CurrVersion desc
					limit 1
				) ehr1 on true
				left join lateral
				(
					SELECT er.IdEHR, rrb.CurrVersion 
					FROM EHR.T_EHR_RUS_CLP14FVIRJ er
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks						
					WHERE er.EhrRusID = bgl.EhrRusClp14fvirj
					order by rrb.CurrVersion desc
					limit 1
				) ehr2 on true '') ' 
				|| ' AS bg(BillOfGoodsID uuid, OtherID INT, Active BIT, CTrn uuid, DoseView INT, Rate INT, PackQuantity INT, IsVital boolean
						  , Name VARCHAR, FederalID INT, ExportCode INT, EAN VARCHAR, ProductInfo VARCHAR, Dlo boolean, FedCode VARCHAR
						  , RegCode VARCHAR, Gtin VARCHAR, MedicamentsOfRoszdravnadzor uuid, EhrRus2m69i03gtd INT, EhrRus2m69i03gtdVer VARCHAR(8)
						  , EhrRus9a9g09e8kl INT, EhrRus9a9g09e8klVer VARCHAR(8), MedicationForms uuid, MedicamentProducer uuid, EhrRusClp14fvirj INT, EhrRusClp14fvirjVer VARCHAR(8)) ';

		EXECUTE _SQL;
		
		-- Первичная загрузка
		-- Загружаем новые DoseView
		INSERT INTO PHARM.S_DLO_DoseView(Value)
		SELECT DISTINCT bgl.DoseView
		FROM TEMP_BILL_OF_GOODS_DOSE bgl
		WHERE bgl.DoseView IS NOT NULL 
		AND NOT EXISTS(SELECT* FROM PHARM.S_DLO_DoseView dv where dv.Value = bgl.DoseView)
		GROUP BY bgl.DoseView;
	
		-- TRN не из справочника RDS переводим в неактивные
		UPDATE PHARM.T_BILL_OF_GOODS bf
		set Active = '0'
		where not exists(select* from Temp_BILL_OF_GOODS_DOSE bgl where bgl.BillOfGoodsID = bf.BillOfGoodsID);
	
		-- Обновляем имеющиеся TRN
		update PHARM.T_BILL_OF_GOODS bf
		set DoseView = dv.Id
		,	OtherID = bgl.OtherID
		,	IsVital = coalesce(bgl.IsVital, '0')
		,	Active = bgl.Active
		,	Rate = coalesce(bgl.Rate, 1)
		,	ExportCode = bgl.ExportCode
		,	PackQuantity = bgl.PackQuantity
		,	Name = coalesce(bgl.Name, bf.Name)
		,	FederalID = bgl.FederalID
		,	MedicamentProducer = bgl.MedicamentProducer
		,	CTrn = bgl.CTrn
		,	MedicationForms = bgl.MedicationForms
		,	ProductInfo = bgl.ProductInfo
		,	Dlo = coalesce(bgl.Dlo, '0')
		,	Gtin = bgl.Gtin
		,	EAN = bgl.EAN
		,	MedicamentsOfRoszdravnadzor = bgl.MedicamentsOfRoszdravnadzor
		,	RegCode = bgl.RegCode
		,	FedCode = bgl.FedCode
		,	EhrRus2m69i03gtd = ehr.EhrRusID
		,	EhrRus9a9g09e8kl = ehr1.EhrRusID
		,	EhrRusClp14fvirj = ehr2.EhrRusID
		from Temp_BILL_OF_GOODS_DOSE bgl
	    left join 
			PHARM.S_DLO_DoseView dv 
			on dv.Value = bgl.DoseView
		left join lateral
		(
			select er.EhrRusID 
			from EHR.T_EHR_RUS_2M69I03GTD er
			LEFT JOIN 
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
			limit 1
		) ehr on true
		left join lateral
		(
			select er.EhrRusID 
			from EHR.T_EHR_RUS_9A9G09E8KL er
			LEFT JOIN 
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = bgl.EhrRus9a9g09e8kl AND rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
			limit 1
		) ehr1 on true
		left join lateral
		(
			select er.EhrRusID 
			from EHR.T_EHR_RUS_CLP14FVIRJ er
			LEFT JOIN 
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = bgl.EhrRusClp14fvirj AND rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
			limit 1
		) ehr2 on true
		where bgl.BillOfGoodsID = bf.BillOfGoodsID;
	
		-- Загружаем новые TRN
		INSERT INTO PHARM.T_BILL_OF_GOODS(BillOfGoodsID, CTrn, Active, MedicamentProducer, OtherID, MedicationForms, DoseView, Rate, IsVital, PackQuantity, Name
						, ExportCode, FederalID, ProductInfo, Dlo, Gtin, EAN, MedicamentsOfRoszdravnadzor, RegCode, FedCode, EhrRus2m69i03gtd, EhrRus9a9g09e8kl, EhrRusClp14fvirj)
		SELECT bgl.BillOfGoodsID, bgl.CTrn, bgl.Active, bgl.MedicamentProducer, bgl.OtherID, bgl.MedicationForms, dv.Id, coalesce(bgl.Rate, 1)
		, coalesce(bgl.IsVital, '0'), bgl.PackQuantity, coalesce(bgl.Name, ''), bgl.ExportCode, bgl.FederalID, bgl.ProductInfo, coalesce(bgl.Dlo, '0')
		, bgl.Gtin, bgl.EAN, bgl.MedicamentsOfRoszdravnadzor, bgl.RegCode, bgl.FedCode, ehr.EhrRusID, ehr1.EhrRusID, ehr2.EhrRusID
		FROM Temp_BILL_OF_GOODS_DOSE bgl
	    left join 
			PHARM.S_DLO_DoseView dv 
			on dv.Value = bgl.DoseView
		left join lateral
		(
			select er.EhrRusID 
			from EHR.T_EHR_RUS_2M69I03GTD er
			LEFT JOIN 
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
			limit 1
		) ehr on true
		left join lateral
		(
			select er.EhrRusID 
			from EHR.T_EHR_RUS_9A9G09E8KL er
			LEFT JOIN 
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = bgl.EhrRus9a9g09e8kl and rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
			limit 1
		) ehr1 on true
		left join lateral
		(
			select er.EhrRusID 
			from EHR.T_EHR_RUS_CLP14FVIRJ er
			LEFT JOIN 
				EHR.T_EHR_RUS_REF_BOOKS RRB 
				ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = bgl.EhrRusClp14fvirj and rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
			limit 1
		) ehr2 on true
		WHERE NOT EXISTS(SELECT* FROM PHARM.T_BILL_OF_GOODS bg WHERE bg.BillOfGoodsID = bgl.BillOfGoodsID);
	ELSE
		IF "Xml" is null
		then
			-- Инициализация
			_ContentEncoding := 'utf-8';
			_Type := 'BILL_OF_GOODS';
			_OwnerID := "ID";
	
			--выясняем тип операции
			if "ID" is null
			then
				_OperationType := _FTLOAD;
			elsif "IsNew" = '1'
			then
				_OperationType := _INSERT;
			elsif "IsNew" = '0' and exists(select* from pharm.T_BILL_OF_GOODS bg where bg.BillOfGoodsID = "ID")
			then
				_OperationType := _UPDATE;
			elsif "IsNew" = '0' and not exists(select* from pharm.T_BILL_OF_GOODS bg where bg.BillOfGoodsID = "ID")
			then
				_OperationType := _DELETE;
				_Result := XMLELEMENT(name "BILL_OF_GOODS", XMLELEMENT(name header, XMLFOREST(_OperationType as "TYPE", "ID" as "ID")));
			end if;
	
			-- Обработка операции
			if _OperationType = _FTLOAD
			then
				-- Первичная загрузка
				select 			    
				XMLELEMENT
				(
					name "BILL_OF_GOODS"
				,	XMLELEMENT
					(
						name header
					,	XMLFOREST(_FTLOAD as "TYPE")
					)
				,	XMLELEMENT
					(
						name body
					,	XMLELEMENT
						(
							name "BILL_OF_GOODS_LIST"
						,	XMLELEMENT
							(
								name "BILL_OF_GOODS"
							,	XMLFOREST
								(
									
									bg.BillOfGoodsID	as "ID"
								,	dv.Value		as "DoseView"
								,	bg.OtherID		as "OtherID"
								,	bg.IsVital		as "IsVital"
								,	bg.Active		as "Active"
								,	bg.Rate			as "Rate"
								,	bg.ExportCode		as "ExportCode"
								,	bg.PackQuantity		as "PackQuantity"
								,	bg.Name			as "Name"
								,	bg.FederalID		as "FederalID"
								,	bg.MedicamentProducer	as "MedicamentProducer"
								,	bg.CTrn			as "CTrn"
								,	bg.MedicationForms	as "MedicationForms"
								,	bg.ProductInfo		as "ProductInfo"
								,	bg.Dlo			as "Dlo"
								,	bg.Gtin							as "Gtin"
								,	bg.EAN							as "EAN"
								,	bg.MedicamentsOfRoszdravnadzor	as "MedicamentsOfRoszdravnadzor"
								,	bg.RegCode						as "RegCode"
								,	bg.FedCode						as "FedCode"
								,	er.IdEHR						as "EhrRus2m69i03gtd"
								,	RRB.CurrVersion					as "EhrRus2m69i03gtdVer"
								,	er1.IdEHR						as "EhrRus9a9g09e8kl"
								,	RRB1.CurrVersion				as "EhrRus9a9g09e8klVer"
								,	er2.IdEHR						as "EhrRusClp14fvirj"
								,	RRB2.CurrVersion				as "EhrRusClp14fvirjVer"
								)
							)
						)
					)
				)
				from PHARM.T_BILL_OF_GOODS bg
				left join
					PHARM.S_DLO_DoseView dv
					on dv.Id = bg.DoseView
				left join
					EHR.T_EHR_RUS_2M69I03GTD er 
					on er.EhrRusID = bg.EhrRus2m69i03gtd
				LEFT JOIN
					EHR.T_EHR_RUS_REF_BOOKS RRB 
					ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				left join 
					EHR.T_EHR_RUS_9A9G09E8KL er1
					on er1.EhrRusID = bg.EhrRus9a9g09e8kl
				LEFT JOIN 
					EHR.T_EHR_RUS_REF_BOOKS RRB1 
					ON RRB1.EhrRusRefBooksID = er1.EhrRusRefBooks
				left join 
					EHR.T_EHR_RUS_CLP14FVIRJ er2
					on er2.EhrRusID = bg.EhrRusClp14fvirj
				LEFT JOIN 
					EHR.T_EHR_RUS_REF_BOOKS RRB2 
					ON RRB2.EhrRusRefBooksID = er2.EhrRusRefBooks
				into _Result;		
			elsif _OperationType <> _DELETE
			then
				-- собираем BILL_OF_GOODS
				select
				XMLELEMENT
				(
					name "BILL_OF_GOODS"
				,	XMLELEMENT
					(
						name header
					,	XMLForest
						(
							_OperationType as "TYPE"
						,	"ID" as "ID"
						)
					)
				,	XMLELEMENT
					(
						name body
					,	XMLFOREST
						(
							dv.Value		as "DoseView"
						,	bg.OtherID		as "OtherID"
						,	bg.IsVital		as "IsVital"
						,	bg.Active		as "Active"
						,	bg.Rate			as "Rate"
						,	bg.ExportCode		as "ExportCode"
						,	bg.PackQuantity		as "PackQuantity"
						,	bg.Name			as "Name"
						,	bg.FederalID		as "FederalID"
						,	bg.MedicamentProducer	as "MedicamentProducer"
						,	bg.CTrn			as "CTrn"
						,	bg.MedicationForms	as "MedicationForms"
						,	bg.ProductInfo		as "ProductInfo"
						,	bg.Dlo			as "Dlo"
						,	bg.Gtin							as "Gtin"
					  	,	bg.EAN							as "EAN"
					  	,	bg.MedicamentsOfRoszdravnadzor	as "MedicamentsOfRoszdravnadzor"
					  	,	RegCode							as "RegCode"
					  	,	FedCode							as "FedCode"
					  	,	er.IdEHR						as "EhrRus2m69i03gtd"
					  	,	RRB.CurrVersion					as "EhrRus2m69i03gtdVer"
					  	,	er1.IdEHR						as "EhrRus9a9g09e8kl"
					  	,	RRB1.CurrVersion				as "EhrRus9a9g09e8klVer"
					  	,	er2.IdEHR						as "EhrRusClp14fvirj"
					  	,	RRB2.CurrVersion				as "EhrRusClp14fvirjVer"
						)
					)					
				)
				from Pharm.T_BILL_OF_GOODS bg
				left join 
					pharm.S_DLO_DoseView dv 
					on dv.Id = mf.DoseView
				left join 
					EHR.T_EHR_RUS_2M69I03GTD er 
					on er.EhrRusID = bg.EhrRus2m69i03gtd
				LEFT JOIN 
					EHR.T_EHR_RUS_REF_BOOKS RRB 
					ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
				left join 
					EHR.T_EHR_RUS_9A9G09E8KL er1 
					on er1.EhrRusID = bg.EhrRus9a9g09e8kl
				LEFT JOIN 
					EHR.T_EHR_RUS_REF_BOOKS RRB1 
					ON RRB1.EhrRusRefBooksID = er1.EhrRusRefBooks
				left join 
					EHR.T_EHR_RUS_CLP14FVIRJ er2
					on er2.EhrRusID = bg.EhrRusClp14fvirj
				LEFT JOIN 
					EHR.T_EHR_RUS_REF_BOOKS RRB2 
					ON RRB2.EhrRusRefBooksID = er2.EhrRusRefBooks
				into _Result
				where mf.BillOfGoodsID = "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('/BILL_OF_GOODS/header[1]/TYPE[1]/text()', "Xml"))[1]::text::varchar(36);
	
				-- Generate table #BILL_OF_GOODS_LIST
				Drop Table If Exists Tmp_BILL_OF_GOODS_LIST;
				Create Temp Table Tmp_BILL_OF_GOODS_LIST
				as 
				SELECT	(xpath('/BILL_OF_GOODS/ID[1]/text()', t.x))[1]::text::UUID as ID
				,	(xpath('/BILL_OF_GOODS/DoseView[1]/text()', t.x))[1]::varchar(255) as DoseView
				,	(xpath('/BILL_OF_GOODS/OtherID[1]/text()', t.x))[1]::text::int as OtherID
				,	(xpath('/BILL_OF_GOODS/IsVital[1]/text()', t.x))[1]::text::boolean as IsVital
				,	(xpath('/BILL_OF_GOODS/Active[1]/text()', t.x))[1]::text::boolean as Active
				,	(xpath('/BILL_OF_GOODS/Rate[1]/text()', t.x))[1]::text::int as Rate
				,	(xpath('/BILL_OF_GOODS/ExportCode[1]/text()', t.x))[1]::text::int as ExportCode
				,	(xpath('/BILL_OF_GOODS/PackQuantity[1]/text()', t.x))[1]::text::int as PackQuantity
				,	(xpath('/BILL_OF_GOODS/Name[1]/text()', t.x))[1]::varchar(1000) as Name
				,	(xpath('/BILL_OF_GOODS/FederalID[1]/text()', t.x))[1]::text::int as FederalID
				,	(xpath('/BILL_OF_GOODS/MedicamentProducer[1]/text()', t.x))[1]::text::int as MedicamentProducer
				,	(xpath('/BILL_OF_GOODS/CTrn[1]/text()', t.x))[1]::text::UUID as CTrn
				,	(xpath('/BILL_OF_GOODS/MedicationForms[1]/text()', t.x))[1]::text::int as MedicationForms
				,	(xpath('/BILL_OF_GOODS/ProductInfo[1]/text()', t.x))[1]::text as ProductInfo
				,	(xpath('/BILL_OF_GOODS/Dlo[1]/text()', t.x))[1]::text::boolean as Dlo				
				,	(xpath('/BILL_OF_GOODS/Gtin[1]/text()', t.x))[1]::varchar(4) as Gtin
				,	(xpath('/BILL_OF_GOODS/EAN[1]/text()', t.x))[1]::varchar(255) as EAN
				,	(xpath('/BILL_OF_GOODS/MedicamentsOfRoszdravnadzor[1]/text()', t.x))[1]::text::uuid as MedicamentsOfRoszdravnadzor
				,	(xpath('/BILL_OF_GOODS/RegCode[1]/text()', t.x))[1]::varchar(50) as RegCode
				,	(xpath('/BILL_OF_GOODS/FedCode[1]/text()', t.x))[1]::varchar(50) as FedCode
				,	(xpath('/BILL_OF_GOODS/EhrRus2m69i03gtd[1]/text()', t.x))[1]::text::int as EhrRus2m69i03gtd
				,	(xpath('/BILL_OF_GOODS/EhrRus2m69i03gtdVer[1]/text()', t.x))[1]::varchar(8) as EhrRus2m69i03gtdVer
				,	(xpath('/BILL_OF_GOODS/EhrRus9a9g09e8kl[1]/text()', t.x))[1]::text::int as EhrRus9a9g09e8kl
				,	(xpath('/BILL_OF_GOODS/EhrRus9a9g09e8klVer[1]/text()', t.x))[1]::varchar(8) as EhrRus9a9g09e8klVer
				,	(xpath('/BILL_OF_GOODS/EhrRusClp14fvirj[1]/text()', t.x))[1]::text::int as EhrRusClp14fvirj
				,	(xpath('/BILL_OF_GOODS/EhrRusClp14fvirjVer[1]/text()', t.x))[1]::varchar(8) as EhrRusClp14fvirjVer
				from
				(
					select	unnest(xpath('/BILL_OF_GOODS/body/BILL_OF_GOODS_LIST/BILL_OF_GOODS', "Xml"::xml)) as x
				) t
				where _OperationType = _FTLOAD;
				
				-- Generate table #BILL_OF_GOODS
				Drop Table If Exists Tmp_BILL_OF_GOODS;
				Create Temp Table Tmp_BILL_OF_GOODS
				as
				SELECT	(xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::UUID as ID
				,	(xpath('/body[1]/DoseView[1]/text()', t.x2))[1]::varchar(255) as DoseView
				,	(xpath('/body[1]/OtherID[1]/text()', t.x2))[1]::text::int as OtherID
				,	(xpath('/body[1]/IsVital[1]/text()', t.x2))[1]::text::boolean as IsVital
				,	(xpath('/body[1]/Active[1]/text()', t.x2))[1]::text::boolean as Active
				,	(xpath('/body[1]/Rate[1]/text()', t.x2))[1]::text::int as Rate
				,	(xpath('/body[1]/ExportCode[1]/text()', t.x2))[1]::text::int as ExportCode
				,	(xpath('/body[1]/PackQuantity[1]/text()', t.x2))[1]::text::int as PackQuantity
				,	(xpath('/body[1]/Name[1]/text()', t.x2))[1]::varchar(1000) as Name
				,	(xpath('/body[1]/FederalID[1]/text()', t.x2))[1]::text::int as FederalID
				,	(xpath('/body[1]/MedicamentProducer[1]/text()', t.x2))[1]::text::int as MedicamentProducer
				,	(xpath('/body[1]/CTrn[1]/text()', t.x2))[1]::text::UUID as CTrn
				,	(xpath('/body[1]/MedicationForms[1]/text()', t.x2))[1]::text::int as MedicationForms
				,	(xpath('/body[1]/ProductInfo[1]/text()', t.x2))[1]::text as ProductInfo
				,	(xpath('/body[1]/Dlo[1]/text()', t.x2))[1]::text::boolean as Dlo
				,	(xpath('/body[1]/Gtin[1]/text()', t.x2))[1]::varchar(4) as Gtin
				,	(xpath('/body[1]/EAN[1]/text()', t.x2))[1]::varchar(255) as EAN
				,	(xpath('/body[1]/MedicamentsOfRoszdravnadzor[1]/text()', t.x2))[1]::text::uuid as MedicamentsOfRoszdravnadzor
				,	(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]/EhrRus2m69i03gtd[1]/text()', t.x2))[1]::text::int as EhrRus2m69i03gtd
				,	(xpath('/body[1]/EhrRus2m69i03gtdVer[1]/text()', t.x2))[1]::varchar(8) as EhrRus2m69i03gtdVer
				,	(xpath('/body[1]/EhrRus9a9g09e8kl[1]/text()', t.x2))[1]::text::int as EhrRus9a9g09e8kl
				,	(xpath('/body[1]/EhrRus9a9g09e8klVer[1]/text()', t.x2))[1]::varchar(8) as EhrRus9a9g09e8klVer
				,	(xpath('/body[1]/EhrRusClp14fvirj[1]/text()', t.x2))[1]::text::int as EhrRusClp14fvirj
				,	(xpath('/body[1]/EhrRusClp14fvirjVer[1]/text()', t.x2))[1]::varchar(8) as EhrRusClp14fvirjVer
				from
				(
					select	unnest(xpath('/BILL_OF_GOODS/header', x)) as x1
					,	unnest(xpath('/BILL_OF_GOODS/body', x)) as x2
					from unnest(xpath('/BILL_OF_GOODS', "Xml"::xml)) x
					where _OperationType <> _FTLOAD
				) t;
							
				-- Обработка операции
				if _OperationType = _FTLOAD
				then
					-- Первичная загрузка
					-- Загружаем новые DoseView
					INSERT INTO pharm.S_DLO_DoseView(Value)
					SELECT bgl.DoseView
					FROM Tmp_BILL_OF_GOODS_LIST bgl
					where not exists
					(
					    select* from pharm.S_DLO_DoseView dv where dv.Value = bgl.DoseView
					)
					group by bgl.DoseView;
	
					-- TRN не из справочника RDS переводим в неактивные
					update pharm.T_BILL_OF_GOODS bf
					set Active = '0'
					where not exists
					(
						select* from Tmp_BILL_OF_GOODS_LIST bgl where bgl.ID = bf.BillOfGoodsID
					);
	
					-- Обновляем имеющиеся TRN
					with bgl
					as 
					(
						select bgl.ID, dv.Id as DoseView, bgl.OtherID, coalesce(bgl.IsVital, '0') as IsVital, bgl.Active
						, coalesce(bgl.Rate, 1) as Rate, bgl.ExportCode, bgl.PackQuantity, bgl.Name, bgl.FederalID
						, bgl.MedicamentProducer, bgl.CTrn, bgl.MedicationForms, bgl.ProductInfo, coalesce(bgl.Dlo, '0') as Dlo
						, bgl.Gtin, bgl.EAN, bgl.MedicamentsOfRoszdravnadzor, bgl.RegCode, bgl.FedCode, ehr.EhrRusID, ehr1.EhrRusID as EhrRusID2, ehr2.EhrRusID as EhrRusID3
						from Tmp_BILL_OF_GOODS_LIST bgl
						left join 
							PHARM.S_DLO_DoseView dv 
							on dv.Value = bgl.DoseView
						left join lateral
						(
							select er.EhrRusID 
							from EHR.T_EHR_RUS_2M69I03GTD er
							LEFT JOIN 
								EHR.T_EHR_RUS_REF_BOOKS RRB 
								ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
							limit 1
						) ehr  on true
						left join lateral
						(
							select er.EhrRusID 
							from EHR.T_EHR_RUS_9A9G09E8KL er
							LEFT JOIN 
								EHR.T_EHR_RUS_REF_BOOKS RRB 
								ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							where er.IdEHR = bgl.EhrRus9a9g09e8kl and rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
							limit 1
						) ehr1 on true
						left join lateral
						(
							select er.EhrRusID 
							from EHR.T_EHR_RUS_CLP14FVIRJ er
							LEFT JOIN 
								EHR.T_EHR_RUS_REF_BOOKS RRB 
								ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
							where er.IdEHR = bgl.EhrRusClp14fvirj and rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
							limit 1
						) ehr2 on true
					)
					update pharm.T_BILL_OF_GOODS bf
					set	DoseView = bgl.DoseView
					,	OtherID = bgl.OtherID
					,	IsVital = bgl.IsVital
					,	Active = bgl.Active
					,	Rate = bgl.Rate
					,	ExportCode = bgl.ExportCode
					,	PackQuantity = bgl.PackQuantity
					,	Name = coalesce(bgl.Name, bf.Name)
					,	FederalID = bgl.FederalID
					,	MedicamentProducer = bgl.MedicamentProducer
					,	CTrn = bgl.CTrn
					,	MedicationForms = bgl.MedicationForms
					,	ProductInfo = bgl.ProductInfo
					,	Dlo = bgl.Dlo
					,	Gtin = bgl.Gtin
			   		,	EAN = bgl.EAN
			   		,	MedicamentsOfRoszdravnadzor = bgl.MedicamentsOfRoszdravnadzor
			   		,	RegCode = bgl.RegCode
			   		,	FedCode = bgl.FedCode
			   		,	EhrRus2m69i03gtd = bgl.EhrRusID
			   		,	EhrRus9a9g09e8kl = bgl.EhrRusID2
			   		,	EhrRusClp14fvirj = bgl.EhrRusID3
					from bgl 
					where bgl.ID = bf.BillOfGoodsID;
	
					-- Загружаем новые TRN
					INSERT INTO pharm.T_BILL_OF_GOODS(BillOfGoodsID, CTrn, Active, MedicamentProducer, OtherID, MedicationForms
					, DoseView, Rate, IsVital, PackQuantity, Name, ExportCode, FederalID, ProductInfo, Dlo, Gtin, EAN, MedicamentsOfRoszdravnadzor
					, RegCode, FedCode, EhrRus2m69i03gtd, EhrRus9a9g09e8kl, EhrRusClp14fvirj)
					SELECT bgl.ID, bgl.CTrn, bgl.Active, bgl.MedicamentProducer, bgl.OtherID, bgl.MedicationForms
					, dv.Id, coalesce(bgl.Rate, 1), coalesce(bgl.IsVital, '0'), bgl.PackQuantity, coalesce(bgl.Name, '')
					, bgl.ExportCode, bgl.FederalID, bgl.ProductInfo, coalesce(bgl.Dlo, '0'), bgl.Gtin, bgl.EAN, bgl.MedicamentsOfRoszdravnadzor
					, bgl.RegCode, bgl.FedCode, ehr.EhrRusID, ehr1.EhrRusID, ehr2.EhrRusID
					from Tmp_BILL_OF_GOODS_LIST bgl
					left join 
						PHARM.S_DLO_DoseView dv 
						on dv.Value = bgl.DoseView
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_2M69I03GTD er
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
						limit 1
					) ehr on true
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_9A9G09E8KL er
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = bgl.EhrRus9a9g09e8kl and rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
						limit 1
					) ehr1 on true
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_CLP14FVIRJ er
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = bgl.EhrRusClp14fvirj and rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
						limit 1
					) ehr2 on true
					where not exists
					(
					    select* from pharm.T_BILL_OF_GOODS bg where bg.BillOfGoodsID = bgl.ID
					);
	
				elsif _OperationType = _DELETE
				then			
					-- Удаление
					update pharm.T_BILL_OF_GOODS bf
					set Active = '0'
					from Tmp_BILL_OF_GOODS nbg 
					where nbg.ID = bf.BillOfGoodsID;
				else
					-- Загружаем новые DoseView
					INSERT INTO pharm.S_DLO_DoseView(Value)
					SELECT nbg.DoseView
					FROM Tmp_BILL_OF_GOODS nbg
					where not exists
					(
					    select* from Pharm.S_DLO_DoseView dv where dv.Value = nbg.DoseView
					)
					group by nbg.DoseView;
	
					-- Обновляем TRN
					update pharm.T_BILL_OF_GOODS bf
					set	DoseView = dv.Id
					,	OtherID = bgl.OtherID
					,	IsVital = coalesce(bgl.IsVital, '0')
					,	Active = bgl.Active
					,	Rate = coalesce(bgl.Rate, 1)
					,	ExportCode = bgl.ExportCode
					,	PackQuantity = bgl.PackQuantity
					,	Name = coalesce(bgl.Name, bf.Name)
					,	FederalID = bgl.FederalID
					,	MedicamentProducer = bgl.MedicamentProducer
					,	CTrn = bgl.CTrn
					,	MedicationForms = bgl.MedicationForms
					,	ProductInfo = bgl.ProductInfo
					,	Dlo = bgl.Dlo
					,	Gtin = bgl.Gtin
					,	EAN = bgl.EAN
					,	MedicamentsOfRoszdravnadzor = bgl.MedicamentsOfRoszdravnadzor
					,	RegCode = bgl.RegCode
					,	FedCode = bgl.FedCode
					,	EhrRus2m69i03gtd = ehr.EhrRusID
					,	EhrRus9a9g09e8kl = ehr1.EhrRusID
					,	EhrRusClp14fvirj = ehr2.EhrRusID
					from Tmp_BILL_OF_GOODS bgl 
					left join 
						PHARM.S_DLO_DoseView dv 
						on dv.Value = bgl.DoseView
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_2M69I03GTD er
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
						limit 1
					) ehr on true
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_9A9G09E8KL er
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = bgl.EhrRus9a9g09e8kl and rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
						limit 1
					) ehr1 on true
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_CLP14FVIRJ er
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = bgl.EhrRusClp14fvirj and rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
						limit 1
					) ehr2 on true
					where _OperationType = _UPDATE;
	
					-- Загружаем новый TRN
					INSERT INTO pharm.T_BILL_OF_GOODS(BillOfGoodsID, CTrn, Active, MedicamentProducer, OtherID, MedicationForms
					, DoseView, Rate, IsVital, PackQuantity, Name, ExportCode, FederalID, ProductInfo, Dlo, Gtin, EAN, MedicamentsOfRoszdravnadzor
					, RegCode, FedCode, EhrRus2m69i03gtd, EhrRus9a9g09e8kl, EhrRusClp14fvirj)
					SELECT bgl.ID, bgl.CTrn, bgl.Active, bgl.MedicamentProducer, bgl.OtherID, bgl.MedicationForms
					, dv.Id, coalesce(bgl.Rate, 1), coalesce(bgl.IsVital, '0'), bgl.PackQuantity, coalesce(bgl.Name, '')
					, bgl.ExportCode, bgl.FederalID, bgl.ProductInfo, coalesce(bgl.Dlo, '0'), bgl.Gtin, bgl.EAN, MedicamentsOfRoszdravnadzor
					, bgl.RegCode, bgl.FedCode, ehr.EhrRusID, ehr1.EhrRusID, ehr2.EhrRusID				
					from Tmp_BILL_OF_GOODS bgl
					left join 
						PHARM.S_DLO_DoseView dv 
						on dv.Value = bgl.DoseView
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_2M69I03GTD er
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
						limit 1
					) ehr on true
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_9A9G09E8KL er
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = bgl.EhrRus9a9g09e8kl and rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
						limit 1
					) ehr1 on true
					left join lateral
					(
						select er.EhrRusID 
						from EHR.T_EHR_RUS_CLP14FVIRJ er
						LEFT JOIN 
							EHR.T_EHR_RUS_REF_BOOKS RRB 
							ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						where er.IdEHR = bgl.EhrRusClp14fvirj and rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
						limit 1
					) ehr2 on true
					where not exists(select* from pharm.T_BILL_OF_GOODS bg where bg.BillOfGoodsID = nbg.ID)
					and _OperationType = _INSERT;
				end if;
				
        		_EntId := (select EntityID from VCLib.T_ENTITIES where EntName = 'BILL_OF_GOODS' limit 1);
				if exists 
				(
					select* from AKUZ.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.BILL_OF_GOODS' Limit 1
				)
				then
					---- генерируем след. номер
					--perform public.sp_GenerateNumber(1916, 'PHARM.BILL_OF_GOODS');
						
					update AKUZ.T_GEN_NUMBERS
					set NextNumber = (select MAX(OtherID) from PHARM.T_BILL_OF_GOODS) --+ 1
					where EntityID = _EntId
					and NumberName = 'PHARM.BILL_OF_GOODS';
				else
					INSERT INTO AKUZ.T_GEN_NUMBERS(GenNumbersID, EntityID, NumberName, NextNumber)
					VALUES(uuid_generate_v4(), _EntId, 'PHARM.BILL_OF_GOODS', coalesce((select MAX(OtherID) from PHARM.T_BILL_OF_GOODS), 1));
				end if;
					
				-- Drop tables
				DROP TABLE if exists Tmp_BILL_OF_GOODS;
				DROP TABLE if exists Tmp_BILL_OF_GOODS_LIST;
			end if;
		END if;
	end if;
End;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sp_rds_billofgoods(xml, boolean, uuid, varchar(100), timestamp, uuid, text)
  OWNER TO postgres;