-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-12-23
-- Description: Выгрузка/загрузка справочника Производитель торгового наименования в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS public.sp_rds_medicamentproducer;
DROP PROCEDURE IF EXISTS sp_rds_medicamentproducer;

CREATE PROCEDURE sp_rds_medicamentproducer(
    "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) := 'MEDICAMENT_PRODUCER';
        _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;
        _entid           int;
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        
        IF "PATH" IS NOT NULL
        THEN
            DROP TABLE IF EXISTS temp_medicament_producer;
            CREATE TEMP TABLE temp_medicament_producer
            (
                inn                  varchar(255),
                name                 varchar(4000),
                address              varchar(500),
                active               boolean,
                descr                varchar(255),
                contacts             varchar(255),
                country              varchar(13),
                fedcode              varchar(50),
                regcode              varchar(50),
                medicamentproducerid varchar,
                code                 int
            );
            
            _sql :=
                'insert into Temp_MEDICAMENT_PRODUCER(Inn, Name, Address, Active, Descr, Contacts, Country, FedCode, RegCode, MedicamentProducerID, Code); '
                    ||
                ' SELECT med.Inn, med.Name, med.Address, med.Active, med.Descr, med.Contacts, med.Country, med.FedCode, med.RegCode, med.MedicamentProducerID, med.Code '
                    || 'FROM dblink(''dbname=' || "PATH"
                    || ''','
                    || ''' SELECT mp.Inn, mp.Name, mp.Address, mp.Active, mp.Descr, mp.Contacts, mp.Country, mp.FedCode, mp.RegCode, mp.MedicamentProducerID, mp.Code
				FROM PHARM.T_MEDICAMENT_PRODUCER mp '') '
                    || ' AS med(Inn					varchar(255)
				,Name					varchar(4000)
				,Address				varchar(500)
				,Active					boolean
				,Descr					varchar(255)
				,Contacts				varchar(255)
				,Country				VARCHAR(13)
				,FedCode				varchar(50)
				,RegCode				varchar(50)
				,MedicamentProducerID	varchar
				,Code					int) ';
            
            EXECUTE _sql;
            
            -- Производителей не из справочника RDS переводим в неактивные
            UPDATE pharm.t_medicament_producer mp
            SET active = '0'
            WHERE NOT EXISTS(SELECT*
                             FROM temp_medicament_producer nmp
                             WHERE nmp.medicamentproducerid = mp.medicamentproducerid
                            );
            
            -- Обновляем имеющихся производителей
            UPDATE pharm.t_medicament_producer mp
            SET inn     = nmp.inn,
                name    = nmp.name,
                address = nmp.address,
                country = (SELECT c.countryid FROM pharm.t_country c WHERE c.code = nmp.country LIMIT 1),
                active  = nmp.active,
                regcode = nmp.regcode,
                fedcode = nmp.fedcode,
                code    = nmp.code
            FROM temp_medicament_producer nmp
            WHERE nmp.medicamentproducerid = mp.medicamentproducerid;
            
            -- Загружаем новые
            INSERT INTO
                pharm.t_medicament_producer(medicamentproducerid,
                                            name,
                                            inn,
                                            address,
                                            country,
                                            active,
                                            regcode,
                                            fedcode,
                                            code)
            SELECT nmp.medicamentproducerid,
                   nmp.name,
                   nmp.inn,
                   nmp.address,
                   (SELECT c.countryid FROM pharm.t_country c WHERE c.code = nmp.country LIMIT 1),
                   nmp.active,
                   nmp.regcode,
                   nmp.fedcode,
                   nmp.code
            FROM temp_medicament_producer nmp
            WHERE NOT EXISTS(SELECT*
                             FROM pharm.t_medicament_producer mp
                             WHERE mp.medicamentproducerid = nmp.medicamentproducerid
                            );
        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_medicament_producer mp WHERE mp.medicamentproducerid = "ID")
                THEN
                    _operationtype := _update;
                ELSIF "IsNew" = 0 AND
                      NOT EXISTS(SELECT* FROM pharm.t_medicament_producer mp WHERE mp.medicamentproducerid = "ID")
                THEN
                    _operationtype := _delete;
                    _result := XMLELEMENT(NAME "MEDICAMENT_PRODUCER",
                                          XMLELEMENT(NAME header, XMLFOREST(_operationtype AS "TYPE", "ID" AS "ID")));
                END IF;
                
                -- Обработка операции
                IF _operationtype = _ftload
                THEN -- Первичная загрузка
                    _result :=
                        XMLELEMENT
                        (
                            NAME "MEDICAMENT_PRODUCER",
                            XMLELEMENT
                            (
                                NAME header,
                                XMLFOREST
                                (
                                    _ftload AS "TYPE"
                                )
                            ),
                            XMLELEMENT
                            (
                                NAME body,
                                XMLELEMENT
                                (
                                    NAME "MEDICAMENT_PRODUCERS",
                                    (SELECT XMLAGG
                                            (
                                                XMLELEMENT
                                                (
                                                    NAME "MEDICAMENT_PRODUCER",
                                                    XMLFOREST
                                                    (
                                                        mp.medicamentproducerid AS "ID",
                                                        mp.inn AS "Inn",
                                                        mp.name AS "Name",
                                                        mp.address AS "Address",
                                                        mp.active AS "Active",
                                                        c.code AS "Country",
                                                        mp.regcode AS "RegCode",
                                                        mp.fedcode AS "FedCode",
                                                        mp.code AS "Code"
                                                    )
                                                )
                                            )
                                     FROM pharm.t_medicament_producer mp
                                     LEFT JOIN
                                     pharm.t_country                  c
                                         ON c.countryid = mp.country
                                    )
                                )
                            )
                        );
                ELSIF _operationtype <> _delete
                THEN
                    -- собираем MEDICAMENT_PRODUCER
                    SELECT XMLELEMENT
                           (
                               NAME "MEDICAMENT_PRODUCER",
                               XMLELEMENT
                               (
                                   NAME header,
                                   XMLFOREST
                                   (
                                       _operationtype AS "TYPE",
                                       "ID" AS "ID"
                                   )
                               ),
                               XMLELEMENT
                               (
                                   NAME body,
                                   XMLFOREST
                                   (
                                       mp.inn AS "Inn",
                                       mp.name AS "Name",
                                       mp.address AS "Address",
                                       mp.active AS "Active",
                                       c.code AS "Country",
                                       mp.regcode AS "RegCode",
                                       mp.fedcode AS "FedCode",
                                       mp.code AS "Code"
                                   )
                               )
                           )
                    INTO _result
                    FROM pharm.t_medicament_producer mp
                    LEFT JOIN
                    pharm.t_country                  c
                        ON c.countryid = mp.country
                    WHERE mp.medicamentproducerid = "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
                    -- Получаем тип операции
                    SELECT (XPATH('/header[1]/TYPE[1]/text()', x))[1]::varchar(36),
                           (XPATH('/header[1]/ID[1]/text()', x))[1]::text::uuid,
                           "Xml"
                    INTO _operationtype, _ownerid, _result
                    FROM (SELECT UNNEST(XPATH('/MEDICAMENT_PRODUCER/header', "Xml"::xml)) x) t;
                    
                    
                    -- Обработка операции
                    IF _operationtype = _ftload
                    THEN
                        -- Generate table Tmp_MEDICAMENT_PRODUCERS
                        DROP TABLE IF EXISTS tmp_medicament_producers;
                        CREATE TEMP TABLE tmp_medicament_producers
                        AS
                            SELECT (XPATH('/MEDICAMENT_PRODUCER[1]/ID[1]/text()', x))[1]::text::uuid        AS id,
                                   (XPATH('/MEDICAMENT_PRODUCER[1]/Inn[1]/text()', x))[1]::varchar(255)     AS inn,
                                   (XPATH('/MEDICAMENT_PRODUCER[1]/Name[1]/text()', x))[1]::varchar(255)    AS name,
                                   (XPATH('/MEDICAMENT_PRODUCER[1]/Address[1]/text()', x))[1]::varchar(500) AS address,
                                   (XPATH('/MEDICAMENT_PRODUCER[1]/Active[1]/text()', x))[1]::text::boolean AS active,
                                   (XPATH('/MEDICAMENT_PRODUCER[1]/Country[1]/text()', x))[1]::varchar(13)  AS country,
                                   (XPATH('/MEDICAMENT_PRODUCER[1]/RegCode[1]/text()', x))[1]::varchar(50)  AS regcode,
                                   (XPATH('/MEDICAMENT_PRODUCER[1]/FedCode[1]/text()', x))[1]::varchar(50)  AS fedcode,
                                   (XPATH('/MEDICAMENT_PRODUCER[1]/Code[1]/text()', x))[1]::text::int       AS code
                            FROM UNNEST(XPATH('/MEDICAMENT_PRODUCER/body/MEDICAMENT_PRODUCERS/MEDICAMENT_PRODUCER',
                                              "Xml"::xml)) AS x;
                        
                        -- Первичная загрузка
                        -- Производителей не из справочника RDS переводим в неактивные
                        UPDATE pharm.t_medicament_producer mp
                        SET active = '0'
                        WHERE NOT EXISTS(SELECT*
                                         FROM tmp_medicament_producers nmp
                                         WHERE nmp.id = mp.medicamentproducerid
                                        );
                        
                        -- Обновляем имеющихся производителей
                        UPDATE pharm.t_medicament_producer mp
                        SET inn     = nmp.inn,
                            name    = nmp.name,
                            address = nmp.address,
                            country = (SELECT c.countryid FROM pharm.t_country c WHERE c.code = nmp.country LIMIT 1),
                            active  = nmp.active,
                            regcode = nmp.regcode,
                            fedcode = nmp.fedcode,
                            code    = nmp.code
                        FROM tmp_medicament_producers nmp
                        WHERE nmp.id = mp.medicamentproducerid;
                        
                        -- Загружаем новые
                        INSERT INTO
                            pharm.t_medicament_producer(medicamentproducerid,
                                                        name,
                                                        inn,
                                                        address,
                                                        country,
                                                        active,
                                                        regcode,
                                                        fedcode,
                                                        code)
                        SELECT nmp.id,
                               nmp.name,
                               nmp.inn,
                               nmp.address,
                               (SELECT c.countryid FROM pharm.t_country c WHERE c.code = nmp.country LIMIT 1),
                               nmp.active,
                               nmp.regcode,
                               nmp.fedcode,
                               nmp.code
                        FROM tmp_medicament_producers nmp
                        WHERE NOT EXISTS(SELECT*
                                         FROM pharm.t_medicament_producer mp
                                         WHERE mp.medicamentproducerid = nmp.id
                                        );
                    ELSE
                        -- Generate table Tmp_MEDICAMENT_PRODUCER
                        DROP TABLE IF EXISTS tmp_medicament_producer;
                        CREATE TEMP TABLE tmp_medicament_producer
                        AS
                            SELECT (XPATH('/header[1]/ID[1]/text()', t.x1))[1]::text::uuid      AS id,
                                   (XPATH('/body[1]/Inn[1]/text()', t.x2))[1]::varchar(255)     AS inn,
                                   (XPATH('/body[1]/Name[1]/text()', t.x2))[1]::varchar(255)    AS name,
                                   (XPATH('/body[1]/Address[1]/text()', t.x2))[1]::varchar(500) AS address,
                                   (XPATH('/body[1]/Active[1]/text()', t.x2))[1]::text::boolean AS active,
                                   (XPATH('/body[1]/Country[1]/text()', t.x2))[1]::varchar(13)  AS country,
                                   (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,
                                   (XPATH('/body[1]/Code[1]/text()', t.x2))[1]::text::int       AS code
                            FROM (SELECT UNNEST(XPATH('/MEDICAMENT_PRODUCER/header', x)) AS x1,
                                         UNNEST(XPATH('/MEDICAMENT_PRODUCER/body', x))   AS x2
                                  FROM UNNEST(XPATH('/MEDICAMENT_PRODUCER', "Xml"::xml)) x
                                 ) t;
                        
                        IF _operationtype = _delete
                        THEN
                            -- Удаление
                            UPDATE pharm.t_medicament_producer mp
                            SET active = '0'
                            FROM tmp_medicament_producer nmp
                            WHERE nmp.id = mp.medicamentproducerid;
                        ELSE
                            UPDATE pharm.t_medicament_producer mp
                            SET inn     = nmp.inn,
                                name    = nmp.name,
                                address = nmp.address,
                                country = (SELECT c.countryid
                                           FROM pharm.t_country c
                                           WHERE c.code = nmp.country
                                           LIMIT 1
                                ),
                                active  = nmp.active,
                                regcode = nmp.regcode,
                                fedcode = nmp.fedcode,
                                code    = nmp.code
                            FROM tmp_medicament_producer nmp
                            WHERE nmp.id = mp.medicamentproducerid
                              AND _operationtype = _update;
                            
                            INSERT INTO
                                pharm.t_medicament_producer(medicamentproducerid,
                                                            name,
                                                            inn,
                                                            address,
                                                            country,
                                                            active,
                                                            regcode,
                                                            fedcode,
                                                            code)
                            SELECT nmp.id,
                                   nmp.name,
                                   nmp.inn,
                                   nmp.address,
                                   (SELECT c.countryid FROM pharm.t_country c WHERE c.code = nmp.country LIMIT 1),
                                   nmp.active,
                                   nmp.regcode,
                                   nmp.fedcode,
                                   nmp.code
                            FROM tmp_medicament_producer nmp
                            WHERE NOT EXISTS(SELECT*
                                             FROM pharm.t_medicament_producer mp
                                             WHERE mp.medicamentproducerid = nmp.id
                                            )
                              AND _operationtype = _insert;
                        END IF;
                    END IF;
                    
                    _entid := (SELECT entityid
                               FROM vclib.t_entities
                               WHERE entname = 'MEDICAMENT_PRODUCER'
                                 AND schema = 'PHARM'
                               LIMIT 1
                              );
                    IF EXISTS
                        (SELECT*
                         FROM vclib.t_gen_numbers
                         WHERE entityid = _entid
                           AND numbername = 'PHARM.MEDICAMENT_PRODUCER'
                        )
                    THEN
                        UPDATE vclib.t_gen_numbers
                        SET nextnumber = COALESCE((SELECT MAX(code) FROM pharm.t_medicament_producer), 1) --+ 1
                        WHERE entityid = _entid
                          AND numbername = 'PHARM.MEDICAMENT_PRODUCER';
                    ELSE
                        INSERT INTO
                            vclib.t_gen_numbers(gennumbersid, entityid, numbername, nextnumber)
                        VALUES (gen_random_uuid(),
                                _entid,
                                'PHARM.MEDICAMENT_PRODUCER',
                                COALESCE((SELECT MAX(code) FROM pharm.t_medicament_producer), 1));
                    END IF;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;