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 '<BILL_OF_GOODS_LINK_PAYMENT_TYPE><header><TYPE>' || 'FTLoad' ||
                       '</TYPE></header><body><BILL_OF_GOODS_LINK_PAYMENT_TYPES>' ||
                       COALESCE(STRING_AGG(
                                    XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                    ''), '')
                           ||
                       '</BILL_OF_GOODS_LINK_PAYMENT_TYPES></body></BILL_OF_GOODS_LINK_PAYMENT_TYPE>'
                INTO _result
                FROM (SELECT 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"
                                 )
                             ) AS xml_elem
                      FROM pharm.t_bill_of_goods_link_payment_type s
                     ) tt;
            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"
                                 )
                             )
                       )
                INTO _result
                FROM pharm.t_bill_of_goods_link_payment_type s
                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";
            
            
            IF (_operationtype = 'FTLoad')
            THEN
                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 dateend
                         , (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;
                
                ANALYZE tmp_bill_of_goods_link_payment_types;
                
                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;
            ELSE
                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.x2))[1]::text::uuid                   AS billofgoods
                         , TO_TIMESTAMP((XPATH('/body[1]/DateBegin[1]/text()', t.x2))[1]::text,
                                        'YYYY-MM-DD')                                                       AS datebegin
                         , TO_TIMESTAMP((XPATH('/body[1]/DateEnd[1]/text()', t.x2))[1]::text, 'YYYY-MM-DD') AS dateend
                         , (XPATH('./body[1]/CertificateNumber[1]/text()', t.x2))[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
                         ) t;
                
                IF (_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 IF;
    END;
END;
$$;