DROP PROCEDURE IF EXISTS sp_GetSetBillOfGoods;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-24
-- Description: Выгрузка/загрузка справочника Форм выпуска препарата(TRN) в(из) RDS
-- =============================================
CREATE PROCEDURE sp_GetSetBillOfGoods
(
	"Xml" xml DEFAULT NULL::xml
	,"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
	,"PATH"  character varying DEFAULT null::character varying
)
	language plpgsql
as
$$
begin
    declare 
		_Result text;
		_ContentEncoding varchar(255) := 'utf-8';
		_Type varchar(255) := 'BILL_OF_GOODS';
		_OperationType varchar(255);
		_OwnerID UUID := "ID";
	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_BILL_OF_GOODS bg where bg.BillOfGoodsID = "ID"))
		then
			_OperationType = 'Update';
		end if;
		elsif ("IsNew" = 0 and not exists (select * from PHARM.T_BILL_OF_GOODS bg where bg.BillOfGoodsID = "ID"))
        then 
			_OperationType := 'Delete';
			select 
			xmlelement
			(
				name "BILL_OF_GOODS",
				xmlelement
				(
					name header,
					xmlforest
					(	
						_OperationType as "TYPE",
						"ID" as "ID"
					)
				)
			)
			into _Result;
		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.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.CodeEHR						as "EhrRus9a9g09e8kl"
								,RRB1.CurrVersion					as "EhrRus9a9g09e8klVer"
								,er2.IdEHR							as "EhrRusClp14fvirj"
								,RRB2.CurrVersion					as "EhrRusClp14fvirjVer"
								,er3.IdEHR							as "EhrRusH17F8UTS3N"
								,RRB3.CurrVersion					as "EhrRusH17F8UTS3NVer"
								,er4.IdEHR							as "EhrRusDsg207"
								,RRB4.CurrVersion					as "EhrRusDsg207Ver"
							)
						)
					)
				)
			)
			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
					left join EHR.T_EHR_RUS_H17F8UTS3N er3 on er3.EhrRusID = bg.EhrRusH17F8UTS3N
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB3 ON RRB3.EhrRusRefBooksID = er3.EhrRusRefBooks
					left join EHR.T_EHR_RUS_DSG207 er4 on er4.EhrRusID = bg.EhrRusDsg207
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB4 ON RRB4.EhrRusRefBooksID = er4.EhrRusRefBooks;
		end if;

        -- Обработка операции
        elsif (_OperationType <> 'Delete')
        then
			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.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.CodeEHR						as "EhrRus9a9g09e8kl"
					  ,RRB1.CurrVersion					as "EhrRus9a9g09e8klVer"
					  ,er2.IdEHR						as "EhrRusClp14fvirj"
					  ,RRB2.CurrVersion					as "EhrRusClp14fvirjVer"
					  ,er3.IdEHR						as "EhrRusH17F8UTS3N"
					  ,RRB3.CurrVersion					as "EhrRusH17F8UTS3NVer"
					  ,er4.IdEHR						as "EhrRusDsg207"
					  ,RRB4.CurrVersion					as "EhrRusDsg207Ver"
					)
				)
			)
			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
					left join EHR.T_EHR_RUS_H17F8UTS3N er3 on er3.EhrRusID = bg.EhrRusH17F8UTS3N
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB3 ON RRB3.EhrRusRefBooksID = er3.EhrRusRefBooks
					left join EHR.T_EHR_RUS_DSG207 er4 on er4.EhrRusID = bg.EhrRusDsg207
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB4 ON RRB4.EhrRusRefBooksID = er4.EhrRusRefBooks
                where bg.BillOfGoodsID = "ID"
			into _Result;
        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
		insert into Tmp_PharmDV
			select REPLACE(dv.Value, ' ', '') as value, MAX(dv.Id) as Id
		from PHARM.S_DLO_DoseView dv
		group by REPLACE(dv.Value, ' ', '');

		-- Получаем тип операции
        _OperationType := (xpath('/BILL_OF_GOODS/header[1]/TYPE[1]/text()', "Xml"))[1]::varchar(36);
        _OwnerID := (xpath('/BILL_OF_GOODS/header[1]/ID[1]/text()', "Xml"))[1]::text::uuid;
        _Result = cast("Xml" as text);

		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(1000)						as DoseView
			,REPLACE((xpath('/BILL_OF_GOODS/DoseViewRep[1]/text()', t.x))[1]::varchar(1000), ' ', '')	as DoseViewRep
			,(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]::varchar(52)					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
			,(xpath('/BILL_OF_GOODS/EhrRusH17F8UTS3N[1]/text()', t.x))[1]::text::int					as EhrRusH17F8UTS3N
			,(xpath('/BILL_OF_GOODS/EhrRusH17F8UTS3NVer[1]/text()', t.x))[1]::varchar(8)				as EhrRusH17F8UTS3NVer
			,(xpath('/BILL_OF_GOODS/EhrRusDsg207Ver[1]/text()', t.x))[1]::text::int						as EhrRusDsg207Ver
			,(xpath('/BILL_OF_GOODS/EhrRusDsg207VerVer[1]/text()', t.x))[1]::varchar(8)					as EhrRusDsg207VerVer        
			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
			,REPLACE((xpath('/body[1]/DoseViewRep[1]/text()', t.x2))[1]::varchar(1000), ' ', '')		as DoseViewRep
			,(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]/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]::varchar(52)						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
			,(xpath('/body[1]/EhrRusH17F8UTS3N[1]/text()', t.x2))[1]::text::int							as EhrRusH17F8UTS3N
			,(xpath('/body[1]/EhrRusH17F8UTS3NVer[1]/text()', t.x2))[1]::varchar(8)						as EhrRusH17F8UTS3NVer
			,(xpath('/body[1]/EhrRusDsg207[1]/text()', t.x2))[1]::text::int								as EhrRusDsg207
			,(xpath('/body[1]/EhrRusDsg207Ver[1]/text()', t.x2))[1]::varchar(8)							as EhrRusDsg207Ver
			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 max(bgl.DoseView)
            FROM Tmp_BILL_OF_GOODS_LIST bgl
            where bgl.DoseView is not null and not exists
            (
                select * from Tmp_PharmDV dv where dv.Value = bgl.DoseViewRep
            )
            group by bgl.DoseViewRep;
			
			truncate table Tmp_PharmDV;

			insert into Tmp_PharmDV(value, Id)
			select REPLACE(dv.Value, ' ', '') as value, MAX(dv.Id) as Id
			from PHARM.S_DLO_DoseView dv
			group by REPLACE(dv.Value, ' ', '');

            -- T_BILL_OF_GOODS не из справочника RDS переводим в неактивные
            update PHARM.T_BILL_OF_GOODS bf
            set bf.Active = '0'
            where not exists
            (
                select * from Tmp_BILL_OF_GOODS_LIST bgl where bgl.ID = bf.BillOfGoodsID
            );

            -- Обновляем имеющиеся TRN
            update pharm.T_BILL_OF_GOODS bf
            set 
                bf.DoseView						= dv.Id
               ,bf.OtherID						= bgl.OtherID
               ,bf.IsVital						= coalesce(bgl.IsVital, '0')
               ,bf.Active						= bgl.Active
			   ,bf.Rate							= coalesce(bgl.Rate, 1)
               ,bf.ExportCode					= bgl.ExportCode
               ,bf.PackQuantity					= bgl.PackQuantity
               ,bf.FederalID					= bgl.FederalID
               ,bf.MedicamentProducer			= bgl.MedicamentProducer
			   ,bf.CTrn							= bgl.CTrn
               ,bf.MedicationForms				= bgl.MedicationForms
               ,bf.ProductInfo					= bgl.ProductInfo
			   ,bf.Dlo							= coalesce(bgl.Dlo, '0')
			   ,bf.Gtin							= bgl.Gtin
			   ,bf.EAN							= bgl.EAN
			   ,bf.MedicamentsOfRoszdravnadzor	= bgl.MedicamentsOfRoszdravnadzor
			   ,bf.RegCode						= bgl.RegCode
			   ,bf.FedCode						= bgl.FedCode
			   ,bf.EhrRus2m69i03gtd				= ehr.EhrRusID
			   ,bf.EhrRus9a9g09e8kl				= ehr1.EhrRusID
			   ,bf.EhrRusClp14fvirj				= ehr2.EhrRusID
			   ,bf.EhrRusH17F8UTS3N				= ehr3.EhrRusID
			   ,bf.EhrRusDsg207					= ehr4.EhrRusID
            from Tmp_BILL_OF_GOODS_LIST bgl
				left join Tmp_PharmDV dv on dv.Value = bgl.DoseViewRep
				left join lateral 
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_2M69I03GTD er
						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
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.CodeEHR = 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
						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
				left join lateral 
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_H17F8UTS3N er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer limit 1
				) ehr3 on true
				left join lateral 
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_DSG207 er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver limit 1
				) ehr4 on true
				where bgl.ID = bf.BillOfGoodsID;

            -- Загружаем новые TRN
            INSERT INTO PHARM.T_BILL_OF_GOODS
            (
				 BillOfGoodsID
				,CTrn
				,Active
				,MedicamentProducer
				,OtherID
				,MedicationForms
				,DoseView  
				,Rate
				,IsVital 
				,PackQuantity 
				,ExportCode
				,FederalID
				,ProductInfo
				,Dlo
				,Gtin
				,EAN
				,MedicamentsOfRoszdravnadzor
				,RegCode
				,FedCode
				,EhrRus2m69i03gtd
				,EhrRus9a9g09e8kl
				,EhrRusClp14fvirj
				,EhrRusH17F8UTS3N
				,EhrRusDsg207
			)
            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
				,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
				,ehr3.EhrRusID
				,ehr4.EhrRusID
            from Tmp_BILL_OF_GOODS_LIST bgl
				left join Tmp_PharmDV dv on dv.Value = bgl.DoseViewRep
				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.CodeEHR = 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
				left join lateral 
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_H17F8UTS3N er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer limit 1
				) ehr3 on true
				left join lateral
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_DSG207 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver limit 1
				) ehr4 on true
            where not exists
                (
                    select *
                    from PHARM.T_BILL_OF_GOODS bg
                    where bg.BillOfGoodsID = bgl.ID
                );
        end if;
        elsif (_OperationType = 'Delete')
        then
            -- Удаление
            update PHARM.T_BILL_OF_GOODS bf
            set bf.Active = '0'
            from Tmp_BILL_OF_GOODS nbg where nbg.ID = bf.BillOfGoodsID;
        end if;
        else
        then
           -- Первичная загрузка
            -- Загружаем новые DoseView
            INSERT INTO PHARM.S_DLO_DoseView(Value)
            SELECT max(bgl.DoseView)
            FROM Tmp_BILL_OF_GOODS bgl
            where bgl.DoseView is not null and not exists
            (
                select *
                from Tmp_PharmDV dv
                where dv.Value = bgl.DoseViewRep
            )
            group by bgl.DoseViewRep;
			
			truncate table Tmp_PharmDV;
			insert into Tmp_PharmDV(value, Id)
			select REPLACE(dv.Value, ' ', '') as value, MAX(dv.Id) as Id
			from PHARM.S_DLO_DoseView dv
			group by REPLACE(dv.Value, ' ', '');

            -- Обновляем TRN
            update PHARM.T_BILL_OF_GOODS bf
            set bf.DoseView						= dv.Id
               ,bf.OtherID						= bgl.OtherID
               ,bf.IsVital						= coalesce(bgl.IsVital, '0')
               ,bf.Active						= bgl.Active
			   ,bf.Rate							= coalesce(bgl.Rate, 1)
               ,bf.ExportCode					= bgl.ExportCode
               ,bf.PackQuantity					= bgl.PackQuantity
               ,bf.FederalID					= bgl.FederalID
               ,bf.MedicamentProducer			= bgl.MedicamentProducer
			   ,bf.CTrn							= bgl.CTrn
               ,bf.MedicationForms				= bgl.MedicationForms
               ,bf.ProductInfo					= bgl.ProductInfo
			   ,bf.Dlo							= ISNULL(bgl.Dlo, '0')
			   ,bf.Gtin							= bgl.Gtin
			   ,bf.EAN							= bgl.EAN
			   ,bf.MedicamentsOfRoszdravnadzor	= bgl.MedicamentsOfRoszdravnadzor
			   ,bf.RegCode						= bgl.RegCode
			   ,bf.FedCode						= bgl.FedCode
			   ,bf.EhrRus2m69i03gtd				= ehr.EhrRusID
			   ,bf.EhrRus9a9g09e8kl				= ehr1.EhrRusID
			   ,bf.EhrRusClp14fvirj				= ehr2.EhrRusID
			   ,bf.EhrRusH17F8UTS3N				= ehr3.EhrRusID
			   ,bf.EhrRusDsg207					= ehr4.EhrRusID
            from Tmp_BILL_OF_GOODS bgl
				left join Tmp_PharmDV dv on dv.Value = bgl.DoseViewRep
				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.CodeEHR = 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
				left join lateral (
					select er.EhrRusID 
					from EHR.T_EHR_RUS_H17F8UTS3N er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer limit 1
				) ehr3 on true
				left join lateral (
					select er.EhrRusID 
					from EHR.T_EHR_RUS_DSG207 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver limit 1
				) ehr4 on true
            where _OperationType = 'Update' and bgl.ID = bf.BillOfGoodsID;

            -- Загружаем новые TRN
            INSERT INTO PHARM.T_BILL_OF_GOODS
			(
				 BillOfGoodsID
				,CTrn
				,Active
				,MedicamentProducer
				,OtherID
				,MedicationForms 
				,DoseView
				,Rate
				,IsVital
				,PackQuantity  
				,ExportCode
				,FederalID
				,ProductInfo
				,Dlo
				,Gtin
				,EAN
				,MedicamentsOfRoszdravnadzor
				,RegCode
				,FedCode
				,EhrRus2m69i03gtd
				,EhrRus9a9g09e8kl
				,EhrRusClp14fvirj
				,EhrRusH17F8UTS3N
				,EhrRusDsg207
			)
            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
				,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
				,ehr3.EhrRusID
				,ehr4.EhrRusID
            from Tmp_BILL_OF_GOODS bgl
				left join Tmp_PharmDV dv on dv.Value = bgl.DoseViewRep
				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.CodeEHR = 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
				left join lateral 
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_H17F8UTS3N er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer limit 1
				) ehr3 on true
				left join lateral 
				(
					select er.EhrRusID 
					from EHR.T_EHR_RUS_DSG207 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver limit 1
				) ehr4 on true
            where _OperationType = 'Insert' and not exists
            (
                select * from PHARM.T_BILL_OF_GOODS bg where bg.BillOfGoodsID = bgl.ID
            );
        end if;

        -- Отправка всем остальным подписанным серверам AKUZ-а и APS
        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';
           
		declare _EntId int;
		_EntId := (select EntityID from VCLib.T_ENTITIES where EntName = 'BILL_OF_GOODS' limit 1);

		if (exists (select * from rds.t_gen_numbers where EntityID = _EntId and NumberName = 'PHARM.BILL_OF_GOODS' limit 1)
		then
			update rds.t_rds_numbers gn
			set gn.NextNumber = (select max(OtherID) from PHARM.T_BILL_OF_GOODS)
			where gn.EntityID = _EntID and gn.NumberName = 'PHARM.T_BILL_OF_GOODS';
		end if;
		else 
		then
			insert into RDS.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;
End;
$$;