drop procedure if exists sp_GetSetBillOfGoodsLinkPT;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-24
-- Description: Выгрузка/загрузка справочника  в(из) RDS
-- =============================================
CREATE PROCEDURE sp_GetSetBillOfGoodsLinkPT
(
    "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) := 'utf-8';
		_Type varchar(255) := 'BILL_OF_GOODS_LINK_PAYMENT_TYPE';
		_OperationType varchar(255);
		_OwnerID UUID := "ID";
    begin
        "dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);
    
    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_PAYMENT_TYPE s where s.BillOfGoodsLinkPaymentTypeID = "ID"))
        then
            _OperationType := 'Update';
        end if;
        elsif ("IsNew" = '0' and not exists (select * from PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE s where s.BillOfGoodsLinkPaymentTypeID = "ID")))
        then
            _OperationType := 'Delete';
            select 
            xmlelement
            (
                name "BILL_OF_GOODS_LINK_PAYMENT_TYPE",
                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_PAYMENT_TYPE",
                    xmlelement
                    (
                        name header,
                        xmlforest('FTLoad' as "Type")
                    ),
                    xmlelement
                    (
                        name body,
                        xmlelement
                        (
                            name BILL_OF_GOODS_LINK_PAYMENT_TYPES,
                            xmlelement
                            (
                                s.BillOfGoodsLinkPaymentTypeID		        as 'BILL_OF_GOODS_LINK_PAYMENT_TYPE/ID'
			    			    ,s.BillOfGoods								as 'BILL_OF_GOODS_LINK_PAYMENT_TYPE/BillOfGoods'
			    			    ,convert(varchar(10), s.DateBegin, 104)		as 'BILL_OF_GOODS_LINK_PAYMENT_TYPE/DateBegin'
    		    				,convert(varchar(10), s.DateEnd, 104)		as 'BILL_OF_GOODS_LINK_PAYMENT_TYPE/DateEnd'
	    	    				,s.PaymentType								as 'BILL_OF_GOODS_LINK_PAYMENT_TYPE/PaymentType'
                            )
                        )
                    )
                )
                from PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE s
                into _Result;
        end if;
        elsif (_OperationType <> 'Delete')
        then
            select 
            xmlelement
            (
                select 
                xmlelement
                (
                    name "BILL_OF_GOODS_LINK_PAYMENT_TYPE",
                    xmlelement
                    (
                        name header,
                        xmlforest
                        (
                            _OperationType as "TYPE",
                            "ID" as "ID"
                        )
                    ),
                    xmlelement
                    (
                        name body,
                        xmlforest
                        (
                            ,s.BillOfGoods							as 'body/BillOfGoods'
					        ,to_char(s.DateBegin, 'YYYY-MM-DD')	    as 'body/DateBegin'
					        ,to_char(s.DateEnd, 'YYYY-MM-DD')	    as 'body/DateEnd'
					        ,s.PaymentType							as 'body/PaymentType'
                        )
                    )
                )
            )
            from PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE s
            into _Result
            where s.BillOfGoodsLinkPaymentTypeID = "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;

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

        drop table if exists Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPES;
        create temp table Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPES
        as
        select
            (xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/ID[1]/text()', t.x))[1]::text::UUID								    as ID
            ,(xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/BillOfGoods[1]/text()', t.x))[1]::text::UUID					    as BillOfGoods
			,to_timestamp((xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/DateBegin[1]/text()', x))[1]::text, 'YYYY-MM-DD')    as DateBegin
			,to_timestamp((xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/DateEnd[1]/text()', x))[1]::text, 'YYYY-MM-DD')      as BeginPeriod
    	    ,(xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/PaymentType[1]/text()', t.x))[1]::text::int						as PaymentType
        from
		(
			select unnest(xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/body/BILL_OF_GOODS_LINK_PAYMENT_TYPES/BILL_OF_GOODS_LINK_PAYMENT_TYPE', "Xml"::xml)) as x
		) t
		where _OperationType = 'FTLoad';

        drop table if exists Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPE;
        create temp table Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPE
        as
        select 
            (xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::UUID							    as ID
            ,(xpath('/body[1]/BillOfGoods[1]/text()', t.x))[1]::text::UUID					    as BillOfGoods
			,to_timestamp((xpath('/body[1]/DateBegin[1]/text()', x))[1]::text, 'YYYY-MM-DD')    as DateBegin
			,to_timestamp((xpath('/body[1]/DateEnd[1]/text()', x))[1]::text, 'YYYY-MM-DD')      as BeginPeriod
    	    ,(xpath('./body[1]/CertificateNumber[1]/text()', t.x))[1]::text::int				as PaymentType
        from
		(
			select	
            unnest(xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/header', x)) as x1
			,unnest(xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/body', x)) as x2
			from unnest(xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE', "Xml"::xml)) x
			where _OperationType <> 'FTLoad'
		) t;

        if (_OperationType = 'FTLoad')
        then
            delete from PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE;
            insert into pharm.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE
            (BillOfGoodsLinkPaymentTypeID, BillOfGoods, PaymentType, DateBegin, DateEnd)
            select
                nmf.ID,
                nmf.BillOfGoods,
    	    	nmf.PaymentType,
    	    	nmf.DateBegin,
    	    	nmf.DateEnd
            from Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPES nmf;
        end if;
        elsif (_OperationType = 'Delete')
        then
            delete PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE s
                using Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPE nmf on nmf.ID = s.BillOfGoodsLinkPaymentTypeID;
        end if;
        else 
        then
            update PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE s
            set 
                BillOfGoods = nmf.BillOfGoods
    	        ,PaymentType = nmf.PaymentType
    	        ,DateBegin = nmf.DateBegin
    	        ,DateEnd = nmf.DateEnd
            from Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPE nmf
            where nmf.ID = s.BillOfGoodsLinkPaymentTypeID and _OperationType = 'Update';

            insert into PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE
                (BillOfGoodsLinkPaymentTypeID, BillOfGoods, PaymentType, DateBegin, DateEnd)
            select
                nmf.ID,
                nmf.BillOfGoods,
			    nmf.PaymentType,
			    nmf.DateBegin,
			    nmf.DateEnd
            from Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPE nmf
            where  @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_PAYMENT_TYPE;
        DROP TABLE if exists Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPES
    end if;
    End;
End;
$$;