drop procedure if exists sp_GetSetBGDoseViews;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-24
-- Description: Выгрузка/загрузка справочника дозировок в(из) RDS
-- =============================================
CREATE PROCEDURE sp_GetSetBGDoseViews
(
	"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
)
language plpgsql
as
$$
begin
    declare _Result text;
           ,_ContentEncoding varchar(255);
           ,_Type varchar(255);
           ,_OperationType varchar(6);
           ,_OwnerID uuid;
    begin
        "dateTime" := coalesce("dateTime", Current_TimeStamp::TIMESTAMP); 
        _ContentEncoding := 'utf-8';
        _Type := 'BILL_OF_GOODS_LINK_DOSE_VIEW';
        
        IF ("Xml" is null)
        then
            -- Инициализация
            _OwnerID := "ID";  

            --выясняем тип операции
            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_LINK_DOSE_VIEW s where s.BillOfGoodsLinkDoseViewID = "ID"))
            then
                _OperationType := 'Update';
            end if;
            elsif ("IsNew" = '0' and not exists (select * from PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s where s.BillOfGoodsLinkDoseViewID = "ID"))
            then
                _OperationType := 'Delete';
                select 
                xmlelement
                (
                    name "BILL_OF_GOODS_LINK_DOSE_VIEW",
                    xmlelement
                    (
                        name header,
                        xmlforest
                        (
                          _OperationType as "TYPE",
                          "ID" as "ID"
                        )
                    )
                )
                into _Result;
            end if;

            -- Обработка операции
            IF (_OperationType = 'FTLoad')
            then
                -- Первичная загрузка
                select 
                xmlelement
                (
                    name "BILL_OF_GOODS_LINK_DOSE_VIEW",
                    xmlelement
                    (
                        name header,
                        xmlforest('FTLoad' as "Type")
                    ),
                    xmlelement
                    (
                        name body,
                        xmlelement
                        (
                            name "BILL_OF_GOODS_LINK_DOSE_VIEWS",
                            xmlforest
                            (
                                name ""BILL_OF_GOODS_LINK_DOSE_VIEW"",
                                 dgdv.BillOfGoodsLinkDoseViewID			as "ID"
	    						,dgdv.DoseView							as "DoseView"
	    						,dgdv.BillOfGoods						as "BillOfGoods"
	    						,dgdv.Val								as "Val"
	    						,dgdv.ValText							as "ValText"
	    						,dgdv.OrderNum							as "OrderNum"
	    						,dgdv.AdditionalSymbol					as "AdditionalSymbol"
                            )
                        )
                    )
                )
                from PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW dgdv
                into _Result;
            end if;
            elsif (_OperationType <> 'Delete')
            then
                select 
                xmlelement
                (
                    name "BILL_OF_GOODS_LINK_DOSE_VIEW",
                    xmlelement
                    (
                        name header,
                        xmlforest
                        (
                            _OperationType as "TYPE",
                            "ID" as "ID"
                        )
                    ),
                    xmlelement
                    (
                        name body,
                        xmlforest
                        (
                            dgdv.DoseView				as "DoseView"
	    				    ,dgdv.BillOfGoods			as "BillOfGoods"
	    				    ,dgdv.Val					as "Val"
	    				    ,dgdv.ValText				as "ValText"
	    				    ,dgdv.OrderNum				as "OrderNum"
	    				    ,dgdv.AdditionalSymbol		as "AdditionalSymbol"
                        )
                    )
                )
                from PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW dgdv
                into _Result
                where dgdv.BillOfGoodsLinkDoseViewID = "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('/BILL_OF_GOODS_LINK_DOSE_VIEW/header[1]/TYPE[1]/text()', "Xml"))[1]::varchar(6);
            _OwnerID := (xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/header[1]/ID[1]/text()', "Xml"))[1]::text::uuid;
            _Result = cast("Xml" as text);

            -- Generate table #BILL_OF_GOODS_LINK_DOSE_VIEWS
            drop table if exists Tmp_BILL_OF_GOODS_LINK_DOSE_VIEWS;
					Create Temp Table Tmp_BILL_OF_GOODS_LINK_DOSE_VIEWS
					as
                    select	
                         (xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/ID[1]/text()', x))[1]::text::uuid as ID
					    ,(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/DoseView[1]/text()', x))[1]::text::int as DoseView
					    ,(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/BillOfGoods[1]/text()', x))[1]::text::uuid as BillOfGoods
					    ,(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/Val[1]/text()', x))[1]::text::decimal(31,10) as Val
					    ,(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/ValText[1]/text()', x))[1]::varchar(1000) as ValText
					    ,(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/OrderNum[1]/text()', x))[1]::text::int as OrderNum
					    ,(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/AdditionalSymbol[1]/text()', x))[1]::varchar(1) as AdditionalSymbol
					from unnest(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/body/BILL_OF_GOODS_LINK_DOSE_VIEWS/BILL_OF_GOODS_LINK_DOSE_VIEW', "Xml"::xml)) x
					where _OperationType = 'FTLoad';

            -- Generate table #BILL_OF_GOODS_LINK_DOSE_VIEW
            drop table if exists Tmp_BILL_OF_GOODS_LINK_DOSE_VIEW;
				Create Temp Table Tmp_BILL_OF_GOODS_LINK_DOSE_VIEW
				as
				select	
                         (xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::uuid as ID
				        ,(xpath('/body[1]/DoseView[1]/text()', t.x2))[1]::text::int as DoseView
				        ,(xpath('/body[1]/BillOfGoods[1]/text()', t.x2))[1]::text::uuid as BillOfGoods
				        ,(xpath('/body[1]/Val[1]/text()', t.x2))[1]::text::decimal(31,10) as Val
				        ,(xpath('/body[1]/ValText[1]/text()', t.x2))[1]::varchar(1000) as ValText
				        ,(xpath('/body[1]/OrderNum[1]/text()', t.x2))[1]::text::int as OrderNum
				        ,(xpath('/body[1]/AdditionalSymbol[1]/text()', t.x2))[1]::varchar(1) as AdditionalSymbol
				from
					(
						select	unnest(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/header', x)) as x1
						,	unnest(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW/body', x)) as x2
						from unnest(xpath('/BILL_OF_GOODS_LINK_DOSE_VIEW', "Xml"::xml)) x
						where _OperationType <> _FTLOAD
					) t;

            -- Обработка операции
            if (_OperationType = 'FTLoad')
            then
                delete from PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                where not exists
                (
                    select * 
                    from Tmp_BILL_OF_GOODS_LINK_DOSE_VIEWS nmf
                    where nmf.ID = s.BillOfGoodsLinkDoseViewID
                );

                -- Обновляем имеющиеся 
                update PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                set 
                     s.DoseView = nmf.DoseView
	    			,s.BillOfGoods = nmf.BillOfGoods
	    			,s.Val = nmf.Val
	    			,s.OrderNum = nmf.OrderNum
	    			,s.AdditionalSymbol = nmf.AdditionalSymbol
	    			,s.ValText = nmf.ValText
                    from Tmp_BILL_OF_GOODS_LINK_DOSE_VIEWS nmf on nmf.ID = s.BillOfGoodsLinkDoseViewID;

                -- Загружаем новые
                INSERT INTO PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW
                           (BillOfGoodsLinkDoseViewID, DoseView, BillOfGoods, Val, OrderNum, AdditionalSymbol,ValText)
                SELECT
                           nmf.ID,
                           nmf.DoseView,
	    				   nmf.BillOfGoods,
	    				   nmf.Val,
	    				   nmf.OrderNum,
	    				   nmf.AdditionalSymbol
                           ,nmf.ValText
                from Tmp_BILL_OF_GOODS_LINK_DOSE_VIEWS nmf
                where not exists
                    (
                        select *
                        from PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                        where s.BillOfGoodsLinkDoseViewID = nmf.ID
                    );
            end if;
            elseif (_OperationType = 'Delete')
            then 
                -- Удаление
                delete from PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                    join #BILL_OF_GOODS_LINK_DOSE_VIEW nmf on nmf.ID = s.BillOfGoodsLinkDoseViewID;
            end if;
            else
            then
                update PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                set s.DoseView = nmf.DoseView
	    			,s.BillOfGoods = nmf.BillOfGoods
	    			,s.Val = nmf.Val
	    			,s.OrderNum = nmf.OrderNum
	    			,s.AdditionalSymbol = nmf.AdditionalSymbol
	    			,s.ValText = nmf.ValText
                from Tmp_BILL_OF_GOODS_LINK_DOSE_VIEW nmf on nmf.ID = s.BillOfGoodsLinkDoseViewID
                where _OperationType = 'Update';

                INSERT INTO PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW
                           (BillOfGoodsLinkDoseViewID, DoseView, BillOfGoods, Val, OrderNum, AdditionalSymbol,ValText)
                SELECT
                           nmf.ID,
                           nmf.DoseView,
	    				   nmf.BillOfGoods,
	    				   nmf.Val,
	    				   nmf.OrderNum,
	    				   nmf.AdditionalSymbol
                           ,nmf.ValText
                from Tmp_BILL_OF_GOODS_LINK_DOSE_VIEW nmf
                where not exists
                    (
                        select *
                        from PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                        where s.BillOfGoodsLinkDoseViewID = nmf.ID
                    )
                        and
                    _OperationType = 'Insert';
            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';

            -- Drop tables
            DROP TABLE if exists Tmp_BILL_OF_GOODS_LINK_DOSE_VIEW;
            DROP TABLE if exists Tmp_BILL_OF_GOODS_LINK_DOSE_VIEWS;

        end if;
    End;
End;
$$;