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

DROP PROCEDURE IF EXISTS aps.sp_RDS_BGDoseViews;

CREATE OR REPLACE PROCEDURE APS.sp_RDS_BGDoseViews(
    "Xml" text DEFAULT null
, "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
, "PATH" TEXT DEFAULT NULL)
    LANGUAGE plpgsql
AS
$$
BEGIN

    DECLARE
        _Result          TEXT;
        _ContentEncoding VARCHAR(255) := 'utf-8';
        _Type            VARCHAR(255) := 'BILL_OF_GOODS_LINK_DOSE_VIEW';
        _OperationType   VARCHAR(6);
        _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_T_BILL_OF_GOODS_LINK_DOSE_VIEW;
            CREATE TEMP TABLE TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW
            (
                BillOfGoodsLinkDoseViewID uuid,
                DoseView                  INT,
                BillOfGoods               uuid,
                Val                       DECIMAL,
                OrderNum                  INT,
                AdditionalSymbol          VARCHAR(10),
                ValText                   VARCHAR(1000)
            );

            _sql :=
                'insert into TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW(BillOfGoodsLinkDoseViewID, DoseView, BillOfGoods, Val, OrderNum, AdditionalSymbol, ValText) '
                    || ' SELECT * FROM dblink(''dbname=' || "PATH"
                    || ''','
                    ||
                'SELECT BillOfGoodsLinkDoseViewID, DoseView, BillOfGoods, Val, OrderNum, AdditionalSymbol, ValText FROM PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW'') '
                    ||
                ' AS tt(BillOfGoodsLinkDoseViewID uuid, DoseView INT, BillOfGoods uuid, Val DECIMAL, OrderNum INT, AdditionalSymbol VARCHAR, ValText VARCHAR)';

            EXECUTE _SQL;

            DELETE
            FROM PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
            WHERE
                NOT EXISTS
                    (SELECT *
                     FROM TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW nmf
                     WHERE
                         nmf.BillOfGoodsLinkDoseViewID = s.BillOfGoodsLinkDoseViewID);

            -- Обновляем имеющиеся
            UPDATE PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
            SET
                DoseView         = nmf.DoseView
                ,
                BillOfGoods      = nmf.BillOfGoods
                ,
                Val              = nmf.Val
                ,
                OrderNum         = nmf.OrderNum
                ,
                AdditionalSymbol = nmf.AdditionalSymbol
                ,
                ValText          = nmf.ValText
            FROM TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW nmf
            WHERE
                nmf.BillOfGoodsLinkDoseViewID = s.BillOfGoodsLinkDoseViewID;

            -- Загружаем новые
            INSERT INTO
                PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW
            (BillOfGoodsLinkDoseViewID, DoseView, BillOfGoods, Val, OrderNum, AdditionalSymbol, ValText)
            SELECT
                nmf.BillOfGoodsLinkDoseViewID,
                nmf.DoseView,
                nmf.BillOfGoods,
                nmf.Val,
                nmf.OrderNum,
                nmf.AdditionalSymbol,
                nmf.ValText
            FROM TEMP_T_BILL_OF_GOODS_LINK_DOSE_VIEW nmf
            WHERE
                NOT EXISTS
                    (SELECT *
                     FROM PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                     WHERE
                         s.BillOfGoodsLinkDoseViewID = nmf.BillOfGoodsLinkDoseViewID);
        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_DOSE_VIEW bg
                                                WHERE
                                                    bg.BillOfGoodsLinkDoseViewID = "ID")
                THEN
                    _OperationType := _UPDATE;
                ELSIF "IsNew" = '0' AND NOT EXISTS (SELECT *
                                                    FROM Pharm.T_BILL_OF_GOODS_LINK_DOSE_VIEW bg
                                                    WHERE
                                                        bg.BillOfGoodsLinkDoseViewID = "ID")
                THEN
                    _OperationType := _DELETE;
                    _Result := XMLELEMENT(NAME "BILL_OF_GOODS_LINK_DOSE_VIEW",
                                          XMLELEMENT(NAME header, XMLFOREST(_OperationType AS "TYPE", "ID" AS "ID")));
                END IF;

                -- Обработка операции
                IF _OperationType = _FTLOAD
                THEN
                    -- Первичная загрузка

                    SELECT
                        XMLELEMENT(
                            NAME "BILL_OF_GOODS_LINK_DOSE_VIEW",
                            XMLFOREST(
                                XMLELEMENT(
                                    NAME "body",
                                    (SELECT
                                         XMLELEMENT(
                                             NAME "BILL_OF_GOODS_LINK_DOSE_VIEWS",
                                             XMLAGG(
                                                 XMLELEMENT(
                                                     NAME "BILL_OF_GOODS_LINK_DOSE_VIEW",
                                                     XMLFOREST(
                                                         XMLELEMENT(NAME "ID", dgdv.BillOfGoodsLinkDoseViewID),
                                                         XMLELEMENT(NAME "DoseView", dgdv.DoseView),
                                                         XMLELEMENT(NAME "BillOfGoods", dgdv.BillOfGoods),
                                                         XMLELEMENT(NAME "Val", dgdv.Val),
                                                         XMLELEMENT(NAME "ValText", dgdv.ValText),
                                                         XMLELEMENT(NAME "OrderNum", dgdv.OrderNum),
                                                         XMLELEMENT(NAME "AdditionalSymbol", dgdv.AdditionalSymbol)
                                                     )
                                                 )
                                             )
                                         )
                                     FROM PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW dgdv)
                                )
                            )
                        )::TEXT
                    INTO _Result;

                ELSIF _OperationType <> _DELETE
                THEN
                    -- собираем BILL_OF_GOODS_LINK_DOSE_VIEW
                    SELECT
                        XMLELEMENT
                        (
                            NAME "BILL_OF_GOODS_LINK_DOSE_VIEW"
                            , XMLELEMENT
                              (
                                NAME header
                                , XMLFOREST
                                  (
                                    _OperationType AS "TYPE"
                                    , "ID" AS "ID"
                                  )
                              )
                            , XMLELEMENT
                              (
                                NAME body
                                , XMLFOREST
                                  (
                                    dgdv.DoseView AS "DoseView"
                                    , dgdv.BillOfGoods AS "BillOfGoods"
                                    , dgdv.Val AS "Val"
                                    , dgdv.ValText AS "ValText"
                                    , dgdv.OrderNum AS "OrderNum"
                                    , dgdv.AdditionalSymbol AS "AdditionalSymbol"
                                  )
                              )
                        )
                    INTO _Result
                    FROM Pharm.T_BILL_OF_GOODS_LINK_DOSE_VIEW dgdv
                    WHERE
                        dgdv.BillOfGoodsLinkDoseViewID = "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;
            ELSE
                IF "Xml" IS NOT NULL
                THEN

                    -- Получаем тип операции
                    _OperationType := (XPATH('/BILL_OF_GOODS/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::VARCHAR(6);

                    -- Generate table #BILL_OF_GOODS_LINK_DOSE_VIEWS
                    DROP TABLE IF EXISTS TmpBILL_OF_GOODS_LINK_DOSE_VIEWS;
                    CREATE TEMP TABLE TmpBILL_OF_GOODS_LINK_DOSE_VIEWS
                    AS
                    SELECT
                        (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/ID[1]/text()', x))[1]::TEXT::uuid               AS ID
                        ,
                        (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/DoseView[1]/text()', x))[1]::TEXT::INT          AS DoseView
                        ,
                        (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/BillOfGoods[1]/text()', x))[1]::TEXT::uuid      AS BillOfGoods
                        ,
                        (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/Val[1]/text()', x))[1]::TEXT::DECIMAL(31, 10)   AS Val
                        ,
                        (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/ValText[1]/text()', x))[1]::VARCHAR(1000)       AS ValText
                        ,
                        (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/OrderNum[1]/text()', x))[1]::TEXT::INT          AS OrderNum
                        ,
                        (XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/AdditionalSymbol[1]/text()', x))[1]::VARCHAR(1) AS AdditionalSymbol
                    FROM UNNEST(XPATH(
                        '/BILL_OF_GOODS_LINK_DOSE_VIEW/body/BILL_OF_GOODS_LINK_DOSE_VIEWS/BILL_OF_GOODS_LINK_DOSE_VIEW',
                        "Xml"::xml)) x
                    WHERE
                        _OperationType = _FTLOAD;

                    -- Generate table #BILL_OF_GOODS_LINK_DOSE_VIEW
                    DROP TABLE IF EXISTS TmpBILL_OF_GOODS_LINK_DOSE_VIEW;
                    CREATE TEMP TABLE TmpBILL_OF_GOODS_LINK_DOSE_VIEW
                    AS
                    SELECT
                        (XPATH('/header[1]/ID[1]/text()', t.x1))[1]::TEXT::uuid             AS ID,
                        (XPATH('/body[1]/DoseView[1]/text()', t.x2))[1]::TEXT::INT          AS DoseView,
                        (XPATH('/body[1]/BillOfGoods[1]/text()', t.x2))[1]::TEXT::uuid      AS BillOfGoods,
                        (XPATH('/body[1]/Val[1]/text()', t.x2))[1]::TEXT::DECIMAL(31, 10)   AS Val,
                        (XPATH('/body[1]/ValText[1]/text()', t.x2))[1]::VARCHAR(1000)       AS ValText,
                        (XPATH('/body[1]/OrderNum[1]/text()', t.x2))[1]::TEXT::INT          AS OrderNum,
                        (XPATH('/body[1]/AdditionalSymbol[1]/text()', t.x2))[1]::VARCHAR(1) AS AdditionalSymbol
                    FROM (SELECT
                              UNNEST(XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/header', x)) AS x1,
                              UNNEST(XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW/body', x))   AS x2
                          FROM UNNEST(XPATH('/BILL_OF_GOODS_LINK_DOSE_VIEW', "Xml"::xml)) x
                          WHERE
                              _OperationType <> _FTLOAD) t;

                    -- Обработка операции
                    IF _OperationType = _FTLOAD
                    THEN
                        DELETE
                        FROM PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                        WHERE
                            NOT EXISTS
                                (SELECT*
                                 FROM TmpBILL_OF_GOODS_LINK_DOSE_VIEWS nmf
                                 WHERE
                                     nmf.ID = s.BillOfGoodsLinkDoseViewID);

                        -- Обновляем имеющиеся
                        UPDATE PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                        SET
                            DoseView         = nmf.DoseView,
                            BillOfGoods      = nmf.BillOfGoods,
                            Val              = nmf.Val,
                            OrderNum         = nmf.OrderNum,
                            AdditionalSymbol = nmf.AdditionalSymbol,
                            ValText          = nmf.ValText
                        FROM TmpBILL_OF_GOODS_LINK_DOSE_VIEWS nmf
                        WHERE
                            nmf.ID = s.BillOfGoodsLinkDoseViewID;

                        -- Загружаем новые
                        INSERT INTO
                            PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW(BillOfGoodsLinkDoseViewID,
                                                                 DoseView,
                                                                 BillOfGoods,
                                                                 Val,
                                                                 OrderNum,
                                                                 AdditionalSymbol,
                                                                 ValText)
                        SELECT
                            nmf.ID,
                            nmf.DoseView,
                            nmf.BillOfGoods,
                            nmf.Val,
                            nmf.OrderNum,
                            nmf.AdditionalSymbol,
                            nmf.ValText
                        FROM TmpBILL_OF_GOODS_LINK_DOSE_VIEWS nmf
                        WHERE
                            NOT EXISTS(SELECT*
                                       FROM PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                                       WHERE
                                           s.BillOfGoodsLinkDoseViewID = nmf.ID);
                    ELSIF _OperationType = _DELETE
                    THEN
                        -- Удаление
                        DELETE
                        FROM PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                        WHERE
                            EXISTS(SELECT*
                                   FROM TmpBILL_OF_GOODS_LINK_DOSE_VIEW nmf
                                   WHERE
                                       nmf.ID = s.BillOfGoodsLinkDoseViewID);
                    ELSE
                        UPDATE PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                        SET
                            DoseView         = nmf.DoseView
                            ,
                            BillOfGoods      = nmf.BillOfGoods
                            ,
                            Val              = nmf.Val
                            ,
                            OrderNum         = nmf.OrderNum
                            ,
                            AdditionalSymbol = nmf.AdditionalSymbol
                            ,
                            ValText          = nmf.ValText
                        FROM TmpBILL_OF_GOODS_LINK_DOSE_VIEW nmf
                        WHERE
                              nmf.ID = s.BillOfGoodsLinkDoseViewID
                          AND _OperationType = _UPDATE;

                        INSERT INTO
                            PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW(BillOfGoodsLinkDoseViewID,
                                                                 DoseView,
                                                                 BillOfGoods,
                                                                 Val,
                                                                 OrderNum,
                                                                 AdditionalSymbol,
                                                                 ValText)
                        SELECT
                            nmf.ID,
                            nmf.DoseView,
                            nmf.BillOfGoods,
                            nmf.Val,
                            nmf.OrderNum,
                            nmf.AdditionalSymbol,
                            nmf.ValText
                        FROM TmpBILL_OF_GOODS_LINK_DOSE_VIEW nmf
                        WHERE
                              NOT EXISTS(SELECT*
                                         FROM PHARM.T_BILL_OF_GOODS_LINK_DOSE_VIEW s
                                         WHERE
                                             s.BillOfGoodsLinkDoseViewID = nmf.ID)
                          AND _OperationType = _INSERT;
                    END IF;

                    DROP TABLE IF EXISTS TmpBILL_OF_GOODS_LINK_DOSE_VIEWS;
                    DROP TABLE IF EXISTS TmpBILL_OF_GOODS_LINK_DOSE_VIEW;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;
