-- =============================================
-- 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" 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
, "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",
                               XMLELEMENT(
                                   NAME "body",
                                   (SELECT XMLELEMENT(
                                               NAME "BILL_OF_GOODS_LINK_DOSE_VIEWS",
                                               XMLAGG(
                                                   XMLELEMENT(
                                                       NAME "BILL_OF_GOODS_LINK_DOSE_VIEW",
                                                       XMLFOREST(
                                                           dgdv.billofgoodslinkdoseviewid AS "ID",
                                                           dgdv.doseview AS "DoseView",
                                                           dgdv.billofgoods AS "BillOfGoods",
                                                           dgdv.val AS "Val",
                                                           dgdv.valtext AS "ValText",
                                                           dgdv.ordernum AS "OrderNum",
                                                           dgdv.additionalsymbol AS "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;
$$;
