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

CREATE OR REPLACE PROCEDURE APS.sp_RDS_BillOfGoodsLinkPT(
    "Xml" xml DEFAULT NULL::xml
, "IsNew" BOOLEAN DEFAULT '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
                                                     (
                                                         XMLELEMENT(NAME "ID", s.BillOfGoodsLinkPaymentTypeID),
                                                         XMLELEMENT(NAME "BillOfGoods", s.BillOfGoods),
                                                         XMLELEMENT(NAME "DateBegin", TO_CHAR(s.DateBegin, 'YYYY-MM-DD')),
                                                         XMLELEMENT(NAME "DateEnd", TO_CHAR(s.DateEnd, 'YYYY-MM-DD')),
                                                         XMLELEMENT(NAME "PaymentType", s.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"))[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;
$$;