drop procedure if exists sp_GetSetBillOfGoodsLinkPT;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-24
-- Description: Выгрузка/загрузка справочника  в(из) RDS
-- =============================================
CREATE PROCEDURE sp_GetSetBillOfGoodsLinkPT
(
    "Xml" text DEFAULT NULL
    ,"IsNew" integer = 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';    
        elsif ("IsNew" = 1)
        then
            _OperationType := 'Insert';
        elsif ("IsNew" = 0 and exists (select * from PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE s where s.BillOfGoodsLinkPaymentTypeID = "ID"))
        then
            _OperationType := 'Update';
        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
										(
											name BILL_OF_GOODS_LINK_PAYMENT_TYPE
										,	XMLFOREST
											(
											     s.BillOfGoodsLinkPaymentTypeID				as "ID"
				  	    						    ,s.BillOfGoods								as "BillOfGoods"
				 				            ,to_char(s.DateBegin, 'YYYY-MM-DD')	        AS "DateBegin"
				 				            ,to_char(s.DateEnd, 'YYYY-MM-DD')		    AS "DateEnd"
				  	    						    ,s.PaymentType								as "PaymentType"
											)
										)
									)
								)
				)
				from PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE s
				into _Result;	
        elsif (_OperationType <> 'Delete')
        then
            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 "BillOfGoods"
				    		  ,to_char(s.DateBegin, 'YYYY-MM-DD')	AS "DateBegin"
				    		  ,to_char(s.DateEnd, 'YYYY-MM-DD')		AS "DateEnd"
				    		  ,s.PaymentType						as "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 gen_random_uuid()--(<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"::xml))[1]::text::varchar(36);
		_OwnerID := (xpath('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/header[1]/ID[1]/text()', "Xml"::xml))[1]::text::uuid;
        _Result := "Xml";

        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;
        elsif (_OperationType = 'Delete')
        then
            -- Удаление
			delete from PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE s
			    using Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPE nmf 
			where nmf.ID = s.BillOfGoodsLinkPaymentTypeID;
        else 
            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 gen_random_uuid()--(<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';

    end if;
    End;
End;
$$;