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

DROP PROCEDURE IF EXISTS sp_rds_packagetype;

CREATE OR REPLACE PROCEDURE sp_rds_packagetype(
    "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) := '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          integer,
                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			integer
								,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.fedcode
            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
                    -- Первичная загрузка
                    SELECT '<PACKAGE_TYPE><header><TYPE>' || _ftload || '</TYPE></header><body><PACKAGE_TYPES>' ||
                           COALESCE(STRING_AGG(
                                        XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                        ''), '')
                               ||
                           '</PACKAGE_TYPES></body></PACKAGE_TYPE>'
                    INTO _result
                    FROM (SELECT 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"
                                     )
                                 ) AS xml_elem
                          FROM pharm.t_package_type pt
                         ) tt;
                
                ELSIF _operationtype <> _delete
                THEN
                    -- собираем PACKAGE_TYPE
                    SELECT XMLELEMENT
                           (
                               NAME "MEDICATION_FORMS",
                               XMLELEMENT
                               (
                                   NAME "header",
                                   XMLFOREST
                                   (
                                       _operationtype AS "TYPE",
                                       "ID" AS "ID"
                                   )
                               ),
                               XMLELEMENT
                               (
                                   NAME "body",
                                   XMLFOREST
                                   (
                                       pt.code AS "Code",
                                       pt.name AS "Name",
                                       pt.shortname AS "ShortName",
                                       pt.regcode AS "RegCode",
                                       pt.fedcode AS "FedCode"
                                   )
                               )
                           )
                    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);
                    
                    
                    -- Обработка операции
                    IF _operationtype = _ftload
                    THEN
                        -- 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]::text::integer     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;
                        
                        -- Первичная загрузка
                        
                        -- Обновляем имеющиеся
                        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);
                    ELSE
                        -- 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]::text::integer     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;
                        
                        IF _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;
                    END IF;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;