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

CREATE PROCEDURE sp_rds_packagetype(
    "Xml" text DEFAULT NULL
, "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) := 'PACKAGE_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_package_type;
            CREATE TEMP TABLE temp_package_type
            (
                packagetypeid uuid,
                name          VARCHAR(500),
                code          VARCHAR(255),
                shortname     VARCHAR(500),
                fedcode       VARCHAR(50),
                regcode       VARCHAR(50)
            );
            
            _sql := 'insert into Temp_PACKAGE_TYPE(PackageTypeID, Name, Code, ShortName, FedCode, RegCode); '
                        ||
                    ' SELECT pack.PackageTypeID, pack.Name, pack.Code, pack.ShortName, pack.FedCode, pack.RegCode '
                        || 'FROM dblink(''dbname=' || "PATH"
                        || ''','
                        ||
                    ''' SELECT pt.PackageTypeID, pt.Name, pt.Code, pt.ShortName, pt.FedCode, pt.RegCode FROM PHARM.T_PACKAGE_TYPE pt '') '
                || ' AS pack(PackageTypeID	uuid
								,Name			VARCHAR(500)
								,Code			VARCHAR(255)
								,ShortName		VARCHAR(500)
								,FedCode		VARCHAR(50)
								,RegCode		VARCHAR(50)) ';
            
            EXECUTE _sql;
            
            -- Обновляем имеющиеся
            UPDATE pharm.t_package_type pt
            SET name      = nmf.name
              , code      = nmf.code
              , shortname = nmf.shortname
              , regcode   = nmf.regcode
              , fedcode   = nmf.fedcode
            FROM temp_package_type nmf
            WHERE nmf.packagetypeid = pt.packagetypeid;
            
            -- Загружаем новые
            INSERT INTO
                pharm.t_package_type(packagetypeid, name, code, shortname, regcode, fedcode)
            SELECT nmf.packagetypeid, nmf.name, nmf.code, nmf.shortname, nmf.regcode, nmf.fedcod
            FROM temp_package_type nmf
            WHERE NOT EXISTS(SELECT* FROM pharm.t_package_type pt WHERE pt.packagetypeid = nmf.packagetypeid);
        ELSE
            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_package_type pt WHERE pt.packagetypeid = "ID")
                THEN
                    _operationtype := _update;
                ELSIF "IsNew" = '0' AND NOT EXISTS(SELECT * FROM pharm.t_package_type pt WHERE pt.packagetypeid = "ID")
                THEN
                    _operationtype := _delete;
                    _result := XMLELEMENT(NAME "PACKAGE_TYPE",
                                          XMLFOREST(XMLFOREST(_operationtype AS "TYPE", "ID" AS "ID") AS header));
                END IF;
                
                -- Обработка операции
                IF _operationtype = _ftload
                THEN
                    -- Первичная загрузка
                    _result := XMLELEMENT
                               (
                        NAME "PACKAGE_TYPE"
                        , XMLFOREST
                          (
                            XMLFOREST
                            (
                                _ftload AS "TYPE"
                            ) AS header
                          )
                        , XMLELEMENT
                          (
                            NAME body
                            , XMLELEMENT
                              (
                                NAME "PACKAGE_TYPES"
                                , (SELECT XMLAGG
                                          (
                                              XMLELEMENT
                                              (
                                                  NAME "PACKAGE_TYPE"
                                                  , XMLFOREST
                                                    (
                                                      pt.packagetypeid AS "ID"
                                                      , pt.code AS "Code"
                                                      , pt.name AS "Name"
                                                      , pt.shortname AS "ShortName"
                                                      , pt.regcode AS "RegCode"
                                                      , pt.fedcode AS "FedCode"
                                                    )
                                              )
                                          )
                                   FROM pharm.t_package_type pt
                                  )
                              )
                          )
                               );
                ELSIF _operationtype <> _delete
                THEN
                    -- собираем PACKAGE_TYPE
                    SELECT XMLELEMENT
                           (
                               NAME "MEDICATION_FORMS"
                               , XMLFOREST
                                 (
                                   XMLFOREST
                                   (
                                       _operationtype AS "TYPE"
                                       , "ID" AS "ID"
                                   ) AS header
                                 )
                               , XMLFOREST
                                 (
                                   XMLFOREST
                                   (
                                       pt.code AS "Code"
                                       , pt.name AS "Name"
                                       , pt.shortname AS "ShortName"
                                       , pt.regcode AS "RegCode"
                                       , pt.fedcode AS "FedCode"
                                   ) AS body
                                 )
                           )
                    INTO _result
                    FROM pharm.t_package_type pt
                    WHERE pt.packagetypeid = "ID";
                END IF;
                
                -- Отправка
                INSERT INTO
                    akuz.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('/PACKAGE_TYPE/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);
                    
                    -- Generate table Tmp_MEDICATION_FORMSS
                    DROP TABLE IF EXISTS tmp_package_types;
                    CREATE TEMP TABLE tmp_package_types
                    AS
                        SELECT (XPATH('/PACKAGE_TYPE/ID[1]/text()', x))[1]::text::uuid          AS id
                             , (XPATH('/PACKAGE_TYPE/Code[1]/text()', x))[1]::varchar(255)      AS code
                             , (XPATH('/PACKAGE_TYPE/Name[1]/text()', x))[1]::varchar(500)      AS name
                             , (XPATH('/PACKAGE_TYPE/ShortName[1]/text()', x))[1]::varchar(500) AS shortname
                             , (XPATH('/PACKAGE_TYPE/RegCode[1]/text()', x))[1]::varchar(50)    AS regcode
                             , (XPATH('/PACKAGE_TYPE/FedCode[1]/text()', x))[1]::varchar(50)    AS fedcode
                        FROM UNNEST(XPATH('/PACKAGE_TYPE/body/PACKAGE_TYPES/PACKAGE_TYPE', "Xml"::xml)) AS x
                        WHERE _operationtype = _ftload;
                    
                    -- Generate table #PACKAGE_TYPE
                    DROP TABLE IF EXISTS tmp_package_type;
                    CREATE TEMP TABLE tmp_package_type
                    AS
                        SELECT (XPATH('/header[1]/ID[1]/text()', t.x1))[1]::text::uuid        AS id
                             , (XPATH('/body[1]/Code[1]/text()', t.x2))[1]::varchar(255)      AS code
                             , (XPATH('/body[1]/Name[1]/text()', t.x2))[1]::varchar(500)      AS name
                             , (XPATH('/body[1]/ShortName[1]/text()', t.x2))[1]::varchar(500) AS shortname
                             , (XPATH('/body[1]/RegCode[1]/text()', t.x2))[1]::varchar(50)    AS regcode
                             , (XPATH('/body[1]/FedCode[1]/text()', t.x2))[1]::varchar(50)    AS fedcode
                        FROM (SELECT UNNEST(XPATH('/PACKAGE_TYPE/header', x)) AS x1
                                   , UNNEST(XPATH('/PACKAGE_TYPE/body', x))   AS x2
                              FROM UNNEST(XPATH('/PACKAGE_TYPE', "Xml"::xml)) x
                             ) t
                        WHERE _operationtype <> _ftload;
                    
                    -- Обработка операции
                    IF _operationtype = _ftload
                    THEN
                        -- Первичная загрузка
                        
                        -- Обновляем имеющиеся
                        UPDATE pharm.t_package_type pt
                        SET name      = nmf.name
                          , code      = nmf.code
                          , shortname = nmf.shortname
                          , regcode   = nmf.regcode
                          , fedcode   = nmf.fedcode
                        FROM tmp_package_types nmf
                        WHERE nmf.id = pt.packagetypeid;
                        
                        -- Загружаем новые
                        INSERT INTO
                            pharm.t_package_type(packagetypeid, name, code, shortname, regcode, fedcode)
                        SELECT nmf.id, nmf.name, nmf.code, nmf.shortname, nmf.regcode, nmf.fedcode
                        FROM tmp_package_types nmf
                        WHERE NOT EXISTS(SELECT* FROM pharm.t_package_type pt WHERE pt.packagetypeid = nmf.id);
                    ELSIF _operationtype = _delete
                    THEN
                        -- Удаление
                        DELETE
                        FROM pharm.t_package_type pt
                        WHERE EXISTS(SELECT* FROM tmp_package_type nmf WHERE nmf.id = pt.packagetypeid);
                    ELSE
                        UPDATE pharm.t_package_type pt
                        SET name      = nmf.name
                          , code      = nmf.code
                          , shortname = nmf.shortname
                          , regcode   = nmf.regcode
                          , fedcode   = nmf.fedcode
                        FROM tmp_package_type nmf
                        WHERE nmf.id = pt.packagetypeid
                          AND _operationtype = _update;
                        
                        INSERT INTO
                            pharm.t_package_type(packagetypeid, name, code, shortname, regcode, fedcode)
                        SELECT nmf.id, nmf.name, nmf.code, nmf.shortname, nmf.regcode, nmf.fedcode
                        FROM tmp_package_type nmf
                        WHERE NOT EXISTS(SELECT* FROM pharm.t_package_type pt WHERE pt.packagetypeid = nmf.id)
                          AND _operationtype = _insert;
                    END IF;
                    
                    DROP TABLE IF EXISTS tmp_package_type;
                    DROP TABLE IF EXISTS tmp_package_types;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;