-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника  в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS dbo.sp_rds_billofgoodslinkpt;

DROP PROCEDURE IF EXISTS aps.sp_rds_billofgoodslinkpt;

CREATE OR REPLACE PROCEDURE aps.sp_rds_billofgoodslinkpt(
    "Xml" text DEFAULT NULL
, "IsNew" integer = 1
, "ID" uuid DEFAULT NULL::uuid
, "QueueName" CHARACTER VARYING DEFAULT 'RDSSync'::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_LINK_PAYMENT_TYPE';
        _operationtype   VARCHAR(255);
        _ownerid         uuid         := "ID";
        -- Operation types
        _insert          VARCHAR(6)   := 'Insert';
        _update          VARCHAR(6)   := 'Update';
        _delete          VARCHAR(6)   := 'Delete';
        _ftload          VARCHAR(6)   := 'FTLoad';
        _sql             TEXT;
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        
        IF "PATH" IS NOT NULL
        THEN
            
            DROP TABLE IF EXISTS temp_bill_of_goods_link_payment_type;
            CREATE TEMP TABLE temp_bill_of_goods_link_payment_type
            (
                billofgoodslinkpaymenttypeid uuid,
                billofgoods                  uuid,
                paymenttype                  INT,
                datebegin                    TIMESTAMP,
                dateend                      TIMESTAMP
            );
            
            _sql := 'INSERT INTO TEMP_BILL_OF_GOODS_LINK_PAYMENT_TYPE(
                             BillOfGoodsLinkPaymentTypeID
            				,BillOfGoods
            				,PaymentType
            				,DateBegin
            				,DateEnd )
            			SELECT
            				 BillOfGoodsLinkPaymentTypeID
            				,BillOfGoods
            				,PaymentType
            				,DateBegin
            				,DateEnd
            			FROM dblink(''dbname=' || "PATH"
                        || ''','
                || '''
                        SELECT
            				 BillOfGoodsLinkPaymentTypeID
            				,BillOfGoods
            				,PaymentType
            				,DateBegin
            				,DateEnd
                        FROM PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE'')
					AS lpt( BillOfGoodsLinkPaymentTypeID	uuid
            	            ,BillOfGoods					uuid
            	            ,PaymentType					INT
            	            ,DateBegin						timestamp
            	            ,DateEnd						timestamp) ';
            
            EXECUTE _sql;
            
            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.billofgoodslinkpaymenttypeid,
                   nmf.billofgoods,
                   nmf.paymenttype,
                   nmf.datebegin,
                   nmf.dateend
            FROM temp_bill_of_goods_link_payment_type nmf;
        
        ELSE
            
            IF ("Xml" IS NULL)
            THEN
                --выясняем тип операции
                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 bg
                                             WHERE bg.billofgoodslinkpaymenttypeid = "ID"
                                            )
                THEN
                    _operationtype := _update;
                ELSIF "IsNew" = 0 AND NOT EXISTS(SELECT*
                                                 FROM pharm.t_bill_of_goods_link_payment_type bg
                                                 WHERE bg.billofgoodslinkpaymenttypeid = "ID"
                                                )
                THEN
                    _operationtype := _delete;
                    _result := XMLELEMENT(NAME BILL_OF_GOODS_LINK_PAYMENT_TYPE,
                                          XMLELEMENT(NAME header, XMLFOREST(_operationtype AS "TYPE", "ID" AS "ID")));
                END IF;
                
                -- Обработка операции
                IF (_operationtype = _ftload)
                THEN
                    -- Первичная загрузка
                    SELECT XMLELEMENT
                           (
                               NAME "BILL_OF_GOODS_LINK_PAYMENT_TYPE",
                               XMLFOREST(
                                   XMLELEMENT
                                   (
                                       NAME header,
                                       XMLFOREST(_ftload AS "TYPE")
                                   ),
                                   XMLELEMENT
                                   (
                                       NAME "body",
                                       (SELECT XMLELEMENT
                                               (
                                                   NAME BILL_OF_GOODS_LINK_PAYMENT_TYPES,
                                                   XMLAGG(
                                                       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
                                       )
                                   )
                               )
                           )::TEXT
                    INTO _result;
                ELSIF (_operationtype <> _delete)
                THEN
                    -- собираем BILL_OF_GOODS_LINK_PAYMENT_TYPE
                    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
                    aps.t_rabbit_send_object(rabbitsendobjectid,
                                             queuename,
                                             content,
                                             messageid,
                                             headers,
                                             contentencoding,
                                             type,
                                             userid,
                                             state,
                                             createdatetime,
                                             ownerid)
                SELECT gen_random_uuid(),
                       "QueueName",
                       converttexttobinaryzip(_result, ''),
                       NULL,
                       NULL,
                       _contentencoding,
                       _type,
                       "User",
                       0,
                       "dateTime",
                       _ownerid;
            ELSIF ("Xml" IS NOT NULL)
            THEN
                -- Получаем тип операции
                _operationtype :=
                    (XPATH('/BILL_OF_GOODS_LINK_PAYMENT_TYPE/header[1]/TYPE[1]/text()',
                           "Xml"::xml))[1]::TEXT::VARCHAR(36);
                
                -- Generate table Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPES
                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
                    WHERE _operationtype = _ftload;
                
                -- Generate table Tmp_BILL_OF_GOODS_LINK_PAYMENT_TYPE
                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
                          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
                    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 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_type nmf
                    WHERE _operationtype = _insert;
                END IF;
                
                -- Drop tables
                DROP TABLE tmp_bill_of_goods_link_payment_type;
                DROP TABLE tmp_bill_of_goods_link_payment_types;
            END IF;
        END IF;
    END;
END;
$$;