-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника Международных непатентованных наименований (МНН) в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS dbo.sp_rds_cmnn;

DROP PROCEDURE IF EXISTS sp_rds_cmnn;

CREATE OR REPLACE PROCEDURE sp_rds_cmnn(
    "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) := 'C_MNN';
        _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_c_mnn;
            CREATE TEMP TABLE temp_c_mnn
            (
                cmnnid                    uuid,
                namerus                   VARCHAR(1000),
                nameeng                   VARCHAR(1000),
                otherid                   INT,
                active                    boolean,
                isdrug                    boolean,
                usedin                    INT,
                chemicalname              VARCHAR(600),
                bruttoformula             VARCHAR(100),
                characteristic            VARCHAR(4000),
                pharmacology              text,
                practice                  VARCHAR(6000),
                contraindications         VARCHAR(4000),
                restrictions              text,
                useofpregnancy            text,
                sideeffects               text,
                interaction               text,
                injectionmethod           VARCHAR(2000),
                caution                   text,
                overdose                  VARCHAR(4000),
                cascode                   VARCHAR(50),
                indications               VARCHAR(8000),
                singledose                DECIMAL(17),
                maxdose                   DECIMAL(17),
                singledoseview            INT,
                maxdoseview               INT,
                namelatr                  VARCHAR(4000),
                fedcode                   VARCHAR(50),
                regcode                   VARCHAR(50),
                IsNarcoticAndPsychotropic boolean,
                ehrrusdvl105              INT,
                ehrrusdvl105ver           VARCHAR(8),
                ehrrusnsv101              INT,
                ehrrusnsv101ver           VARCHAR(8)
            );
            _sql := 'insert into Temp_C_MNN
				(
					CMnnID,
					NameRus,
					NameEng,
					OtherID,
					Active,
					IsDrug,
					UsedIn,
					ChemicalName,
					BruttoFormula,
					Characteristic,
					Pharmacology,
					Practice,
					Contraindications,
					Restrictions,
					UseOfPregnancy,
					SideEffects,
					Interaction,
					InjectionMethod,
					Caution,
					Overdose,
					CASCode,
					Indications,
					SingleDose,
					MaxDose,
					SingleDoseView,
					MaxDoseView,
					NameLatR,
					FedCode,
					RegCode,
                    IsNarcoticAndPsychotropic,
					EhrRusDvl105,
					EhrRusDvl105Ver,
					EhrRusNsv101,
					EhrRusNsv101Ver
				) SELECT * FROM dblink(''dbname=' || "PATH"
                || ''', SELECT CMnnID, NameRus, NameEng, OtherID, Active, IsDrug, UsedIn, ChemicalName, BruttoFormula, Characteristic, Pharmacology, Practice
				, Contraindications, Restrictions, UseOfPregnancy, SideEffects, Interaction, InjectionMethod, Caution, Overdose, CASCode, Indications
				, SingleDose, MaxDose, SingleDoseView, MaxDoseView, NameLatR, FedCode, RegCode, IsNarcoticAndPsychotropic, ehr.IdEHR, ehr.CurrVersion, ehr1.IdEHR, ehr1.CurrVersion
				FROM PHARM.T_C_MNN mnn
				left join lateral
					(
						SELECT er.IdEHR, rrb.CurrVersion
						FROM EHR.T_EHR_RUS_DVL105 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						WHERE er.EhrRusID = mnn.EhrRusDvl105
						order by rrb.CurrVersion desc
						limit 1
					) ehr on true
				left join lateral
					(
						SELECT er.IdEHR, rrb.CurrVersion
						FROM EHR.T_EHR_RUS_NSV101 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						WHERE er.EhrRusID = mnn.EhrRusNsv101
						order by rrb.CurrVersion desc
						limit 1
					) ehr1 on true
				'') AS tt(CMnnID uuid, NameRus VARCHAR, NameEng VARCHAR, OtherID INT, Active boolean, IsDrug boolean, UsedIn INT, ChemicalName VARCHAR
					, BruttoFormula VARCHAR, Characteristic VARCHAR, Pharmacology VARCHAR, Practice VARCHAR, Contraindications VARCHAR, Restrictions VARCHAR
					, UseOfPregnancy VARCHAR, SideEffects VARCHAR, Interaction VARCHAR, InjectionMethod VARCHAR, Caution VARCHAR, Overdose VARCHAR, CASCode VARCHAR
					, Indications VARCHAR, SingleDose DECIMAL, MaxDose DECIMAL, SingleDoseView INT, MaxDoseView INT, NameLatR VARCHAR, FedCode VARCHAR, RegCode VARCHAR, IsNarcoticAndPsychotropic boolean, EhrRusDvl105 INT, EhrRusDvl105Ver VARCHAR(8), EhrRusNsv101 INT, EhrRusNsv101Ver VARCHAR(8))';

            EXECUTE _sql;

            -- Первичная загрузка
            -- МНН не из справочника RDS переводим в неактивные
            UPDATE pharm.t_c_mnn c
            SET active = '0'
            WHERE NOT EXISTS
                (SELECT *
                 FROM temp_c_mnn nc
                 WHERE nc.cmnnid = c.cmnnid
                )
              AND c.usedin IN (1, 3);

            -- Обновляем имеющиеся МНН
            UPDATE pharm.t_c_mnn c
            SET nameeng                   = nc.nameeng,
                namerus                   = nc.namerus,
                namelatr                  = nc.namelatr,
                active                    = nc.active,
                isdrug                    = nc.isdrug,
                otherid                   = nc.otherid,
                chemicalname              = nc.chemicalname,
                bruttoformula             = nc.bruttoformula,
                characteristic            = nc.characteristic,
                pharmacology              = nc.pharmacology,
                practice                  = nc.practice,
                contraindications         = nc.contraindications,
                restrictions              = nc.restrictions,
                useofpregnancy            = nc.useofpregnancy,
                sideeffects               = nc.sideeffects,
                interaction               = nc.interaction,
                injectionmethod           = nc.injectionmethod,
                caution                   = nc.caution,
                overdose                  = nc.overdose,
                cascode                   = nc.cascode,
                usedin                    = nc.usedin,
                regcode                   = nc.regcode,
                fedcode                   = nc.fedcode,
                IsNarcoticAndPsychotropic = COALESCE(nc.IsNarcoticAndPsychotropic, c.IsNarcoticAndPsychotropic),
                ehrrusdvl105              = ehr.ehrrusid,
                ehrrusnsv101              = ehr1.ehrrusid
            FROM temp_c_mnn nc
            LEFT JOIN LATERAL
                          (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_dvl105         er
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = nc.ehrrusdvl105
                            AND rrb.currversion = nc.ehrrusdvl105ver
                          LIMIT 1
                          ) ehr ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_nsv101         er
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = nc.ehrrusnsv101
                            AND rrb.currversion = nc.ehrrusnsv101ver
                          LIMIT 1
                          ) ehr1 ON TRUE
            WHERE nc.cmnnid = c.cmnnid;
            --where c.UsedIn in (1,3)

            -- Загружаем новые
            INSERT INTO
                pharm.t_c_mnn
            (cmnnid,
             namerus,
             nameeng,
             active,
             isdrug,
             otherid,
             usedin,
             chemicalname,
             bruttoformula,
             characteristic,
             pharmacology,
             practice,
             contraindications,
             restrictions,
             useofpregnancy,
             sideeffects,
             interaction,
             injectionmethod,
             caution,
             overdose,
             cascode,
             regcode,
             fedcode,
             IsNarcoticAndPsychotropic,
             namelatr,
             ehrrusdvl105,
             ehrrusnsv101)
            SELECT nc.cmnnid,
                   nc.namerus,
                   nc.nameeng,
                   nc.active,
                   nc.isdrug,
                   nc.otherid,
                   nc.usedin,
                   nc.chemicalname,
                   nc.bruttoformula,
                   nc.characteristic,
                   nc.pharmacology,
                   nc.practice,
                   nc.contraindications,
                   nc.restrictions,
                   nc.useofpregnancy,
                   nc.sideeffects,
                   nc.interaction,
                   nc.injectionmethod,
                   nc.caution,
                   nc.overdose,
                   nc.cascode,
                   nc.regcode,
                   nc.fedcode,
                   COALESCE(nc.IsNarcoticAndPsychotropic, false),
                   nc.namelatr,
                   ehr.ehrrusid,
                   ehr1.ehrrusid
            FROM temp_c_mnn nc
            LEFT JOIN LATERAL
                          (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_dvl105         er
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = nc.ehrrusdvl105
                            AND rrb.currversion = nc.ehrrusdvl105ver
                          LIMIT 1
                          ) ehr ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT er.ehrrusid
                          FROM ehr.t_ehr_rus_nsv101         er
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                          WHERE er.idehr = nc.ehrrusnsv101
                            AND rrb.currversion = nc.ehrrusnsv101ver
                          LIMIT 1
                          ) ehr1 ON TRUE
            WHERE NOT EXISTS
                      (SELECT *
                       FROM pharm.t_c_mnn c
                       WHERE c.cmnnid = nc.cmnnid
                      );
        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_c_mnn c WHERE c.cmnnid = "ID")
                THEN
                    _operationtype := _update;
                ELSIF "IsNew" = 0 AND NOT EXISTS(SELECT* FROM pharm.t_c_mnn c WHERE c.cmnnid = "ID")
                THEN
                    _operationtype := _delete;
                    _result := XMLELEMENT(NAME "C_MNN",
                                          XMLELEMENT(NAME header, XMLFOREST(_operationtype AS "TYPE", "ID" AS "ID")));
                END IF;

                -- Обработка операции
                IF _operationtype = _ftload
                THEN -- Первичная загрузка
                    SELECT '<C_MNN><header><TYPE>' || _ftload || '</TYPE></header><body><C_MNNS>' ||
                           COALESCE(STRING_AGG(
                                        XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                        ''), '')
                               ||
                           '</C_MNNS></body></C_MNN>'
                    INTO _result
                    FROM (SELECT XMLELEMENT(
                                     NAME "C_MNN",
                                     XMLFOREST(
                                         c.cmnnid AS "ID",
                                         c.active AS "Active",
                                         c.isdrug AS "IsDrug",
                                         c.namerus AS "NameRus",
                                         c.nameeng AS "NameEng",
                                         c.namelatr AS "NameLatR",
                                         c.otherid AS "OtherID",
                                         c.chemicalname AS "ChemicalName",
                                         c.bruttoformula AS "BruttoFormula",
                                         c.characteristic AS "Characteristic",
                                         c.pharmacology AS "Pharmacology",
                                         c.practice AS "Practice",
                                         c.contraindications AS "Contraindications",
                                         c.restrictions AS "Restrictions",
                                         c.useofpregnancy AS "UseOfPregnancy",
                                         c.sideeffects AS "SideEffects",
                                         c.interaction AS "Interaction",
                                         c.injectionmethod AS "InjectionMethod",
                                         c.caution AS "Caution",
                                         c.overdose AS "Overdose",
                                         c.cascode AS "CASCode",
                                         c.usedin AS "UsedIn",
                                         c.regcode AS "RegCode",
                                         c.fedcode AS "FedCode",
                                         c.IsNarcoticAndPsychotropic AS "IsNarcoticAndPsychotropic",
                                         ehr.idehr AS "EhrRusDvl105",
                                         rrb.currversion AS "EhrRusDvl105Ver",
                                         ehr1.idehr AS "EhrRusNsv101",
                                         rrb1.currversion AS "EhrRusNsv101Ver"
                                     )
                                 ) AS xml_elem
                          FROM pharm.t_c_mnn                c
                          LEFT JOIN ehr.t_ehr_rus_dvl105    ehr ON ehr.ehrrusid = c.ehrrusdvl105
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = ehr.ehrrusrefbooks
                          LEFT JOIN ehr.t_ehr_rus_nsv101    ehr1 ON ehr1.ehrrusid = c.ehrrusnsv101
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb1 ON rrb1.ehrrusrefbooksid = ehr1.ehrrusrefbooks
                          WHERE c.usedin IN (1, 3)
                         ) tt;

                ELSIF _operationtype <> _delete
                THEN
                    -- собираем C_MNN
                    SELECT XMLELEMENT
                           (
                               NAME "C_MNN",
                               XMLELEMENT
                               (
                                   NAME "header",
                                   XMLFOREST
                                   (
                                       _operationtype AS "TYPE",
                                       "ID" AS "ID"
                                   )
                               ),
                               XMLELEMENT
                               (
                                   NAME "body",
                                   XMLFOREST
                                   (
                                       c.active AS "Active",
                                       c.isdrug AS "IsDrug",
                                       c.namerus AS "NameRus",
                                       c.nameeng AS "NameEng",
                                       c.namelatr AS "NameLatR",
                                       c.otherid AS "OtherID",
                                       c.chemicalname AS "ChemicalName",
                                       c.bruttoformula AS "BruttoFormula",
                                       c.characteristic AS "Characteristic",
                                       c.pharmacology AS "Pharmacology",
                                       c.practice AS "Practice",
                                       c.contraindications AS "Contraindications",
                                       c.restrictions AS "Restrictions",
                                       c.useofpregnancy AS "UseOfPregnancy",
                                       c.sideeffects AS "SideEffects",
                                       c.interaction AS "Interaction",
                                       c.injectionmethod AS "InjectionMethod",
                                       c.caution AS "Caution",
                                       c.overdose AS "Overdose",
                                       c.cascode AS "CASCode",
                                       c.usedin AS "UsedIn",
                                       c.regcode AS "RegCode",
                                       c.fedcode AS "FedCode",
                                       c.IsNarcoticAndPsychotropic AS "IsNarcoticAndPsychotropic",
                                       er.idehr AS "EhrRusDvl105",
                                       rrb.currversion AS "EhrRusDvl105Ver",
                                       er1.idehr AS "EhrRusNsv101",
                                       rrb1.currversion AS "EhrRusNsv101Ver"
                                   )
                               )
                           )
                    INTO _result
                    FROM pharm.t_c_mnn                c
                    LEFT JOIN ehr.t_ehr_rus_dvl105    er ON er.ehrrusid = c.ehrrusdvl105
                    LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                    LEFT JOIN ehr.t_ehr_rus_nsv101    er1 ON er1.ehrrusid = c.ehrrusnsv101
                    LEFT JOIN ehr.t_ehr_rus_ref_books rrb1 ON rrb1.ehrrusrefbooksid = er1.ehrrusrefbooks
                    WHERE c.cmnnid = "ID"
                      AND c.usedin IN (1, 3);

                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('/C_MNN/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);


                    -- Обработка операции
                    IF _operationtype = _ftload
                    THEN
                        -- Generate table Tmp_C_MNNS
                        DROP TABLE IF EXISTS tmp_c_mnns;
                        CREATE TEMP TABLE tmp_c_mnns
                        AS
                        SELECT (XPATH('/C_MNN/ID[1]/text()', x))[1]::text::uuid                           AS id,
                               (XPATH('/C_MNN/Active[1]/text()', x))[1]::text::boolean                    AS active,
                               (XPATH('/C_MNN/IsDrug[1]/text()', x))[1]::text::boolean                    AS isdrug,
                               (XPATH('/C_MNN/NameRus[1]/text()', x))[1]::varchar(1000)                   AS namerus,
                               (XPATH('/C_MNN/NameEng[1]/text()', x))[1]::varchar(1000)                   AS nameeng,
                               (XPATH('/C_MNN/NameLatR[1]/text()', x))[1]::varchar(4000)                  AS namelatr,
                               (XPATH('/C_MNN/OtherID[1]/text()', x))[1]::varchar(255)                    AS otherid,
                               (XPATH('/C_MNN/ChemicalName[1]/text()', x))[1]::varchar(600)               AS chemicalname,
                               (XPATH('/C_MNN/BruttoFormula[1]/text()', x))[1]::varchar(100)              AS bruttoformula,
                               (XPATH('/C_MNN/Characteristic[1]/text()', x))[1]::varchar(4000)            AS characteristic,
                               (XPATH('/C_MNN/Pharmacology[1]/text()', x))[1]::text                       AS pharmacology,
                               (XPATH('/C_MNN/Practice[1]/text()', x))[1]::varchar(6000)                  AS practice,
                               (XPATH('/C_MNN/Contraindications[1]/text()', x))[1]::varchar(4000)         AS contraindications,
                               (XPATH('/C_MNN/Restrictions[1]/text()', x))[1]::text                       AS restrictions,
                               (XPATH('/C_MNN/UseOfPregnancy[1]/text()', x))[1]::text                     AS useofpregnancy,
                               (XPATH('/C_MNN/SideEffects[1]/text()', x))[1]::text                        AS sideeffects,
                               (XPATH('/C_MNN/Interaction[1]/text()', x))[1]::text                        AS interaction,
                               (XPATH('/C_MNN/InjectionMethod[1]/text()', x))[1]::varchar(4000)           AS injectionmethod,
                               (XPATH('/C_MNM/Caution[1]/text()', x))[1]::text                            AS caution,
                               (XPATH('/C_MNN/Overdose[1]/text()', x))[1]::varchar(4000)                  AS overdose,
                               (XPATH('/C_MNN/CASCode[1]/text()', x))[1]::varchar(50)                     AS cascode,
                               (XPATH('/C_MNN/UsedIn[1]/text()', x))[1]::text::int                        AS usedin,
                               (XPATH('/C_MNN/RegCode[1]/text()', x))[1]::varchar(50)                     AS regcode,
                               (XPATH('/C_MNN/FedCode[1]/text()', x))[1]::varchar(50)                     AS fedcode,
                               (XPATH('/C_MNN/IsNarcoticAndPsychotropic[1]/text()', x))[1]::text::boolean AS IsNarcoticAndPsychotropic,
                               (XPATH('/C_MNN/EhrRusDvl105[1]/text()', x))[1]::text::int                  AS ehrrusdvl105,
                               (XPATH('/C_MNN/EhrRusDvl105Ver[1]/text()', x))[1]::varchar(8)              AS ehrrusdvl105ver,
                               (XPATH('/C_MNN/EhrRusNsv101[1]/text()', x))[1]::text::int                  AS ehrrusnsv101,
                               (XPATH('/C_MNN/EhrRusNsv101Ver[1]/text()', x))[1]::varchar(8)              AS ehrrusnsv101ver
                            FROM UNNEST(XPATH('/C_MNN/body/C_MNNS/C_MNN', "Xml"::xml)) AS x;

                        ANALYZE tmp_c_mnns;

                        -- Первичная загрузка
                        -- МНН не из справочника RDS переводим в неактивные
                        UPDATE pharm.t_c_mnn c
                        SET active = '0'
                        WHERE NOT EXISTS
                            (SELECT *
                             FROM tmp_c_mnns nc
                             WHERE nc.id = c.cmnnid
                            )
                          AND c.usedin IN (1, 3);

                        -- Обновляем имеющиеся МНН
                        UPDATE pharm.t_c_mnn c
                        SET nameeng                   = nc.nameeng,
                            namerus                   = nc.namerus,
                            namelatr                  = nc.namelatr,
                            otherid                   = nc.otherid,
                            active                    = nc.active,
                            isdrug                    = nc.isdrug,
                            chemicalname              = nc.chemicalname,
                            bruttoformula             = nc.bruttoformula,
                            characteristic            = nc.characteristic,
                            pharmacology              = nc.pharmacology,
                            practice                  = nc.practice,
                            contraindications         = nc.contraindications,
                            restrictions              = nc.restrictions,
                            useofpregnancy            = nc.useofpregnancy,
                            sideeffects               = nc.sideeffects,
                            interaction               = nc.interaction,
                            injectionmethod           = nc.injectionmethod,
                            caution                   = nc.caution,
                            overdose                  = nc.overdose,
                            cascode                   = nc.cascode,
                            usedin                    = nc.usedin,
                            regcode                   = nc.regcode,
                            fedcode                   = nc.fedcode,
                            IsNarcoticAndPsychotropic = COALESCE(nc.IsNarcoticAndPsychotropic, c.IsNarcoticAndPsychotropic),
                            ehrrusdvl105              = ehr.ehrrusid,
                            ehrrusnsv101              = ehr1.ehrrusid
                        FROM tmp_c_mnns nc
                        LEFT JOIN LATERAL
                                      (
                                      SELECT er.ehrrusid
                                      FROM ehr.t_ehr_rus_dvl105         er
                                      LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                                      WHERE er.idehr = nc.ehrrusdvl105
                                        AND rrb.currversion = nc.ehrrusdvl105ver
                                      LIMIT 1
                                      ) ehr ON TRUE
                        LEFT JOIN LATERAL
                                      (
                                      SELECT er.ehrrusid
                                      FROM ehr.t_ehr_rus_nsv101         er
                                      LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                                      WHERE er.idehr = nc.ehrrusnsv101
                                        AND rrb.currversion = nc.ehrrusnsv101ver
                                      LIMIT 1
                                      ) ehr1 ON TRUE
                        WHERE nc.id = c.cmnnid;

                        -- Загружаем новые
                        INSERT INTO
                            pharm.t_c_mnn
                        (cmnnid,
                         namerus,
                         nameeng,
                         active,
                         isdrug,
                         otherid,
                         chemicalname,
                         bruttoformula,
                         characteristic,
                         pharmacology,
                         practice,
                         contraindications,
                         restrictions,
                         useofpregnancy,
                         sideeffects,
                         interaction,
                         injectionmethod,
                         caution,
                         overdose,
                         cascode,
                         usedin,
                         regcode,
                         fedcode,
                         IsNarcoticAndPsychotropic,
                         namelatr,
                         ehrrusdvl105,
                         ehrrusnsv101)
                        SELECT nc.id,
                               nc.namerus,
                               nc.nameeng,
                               nc.active,
                               nc.isdrug,
                               nc.otherid,
                               nc.chemicalname,
                               nc.bruttoformula,
                               nc.characteristic,
                               nc.pharmacology,
                               nc.practice,
                               nc.contraindications,
                               nc.restrictions,
                               nc.useofpregnancy,
                               nc.sideeffects,
                               nc.interaction,
                               nc.injectionmethod,
                               nc.caution,
                               nc.overdose,
                               nc.cascode,
                               nc.usedin,
                               nc.regcode,
                               nc.fedcode,
                               COALESCE(nc.IsNarcoticAndPsychotropic, false),
                               nc.namelatr,
                               ehr.ehrrusid,
                               ehr1.ehrrusid
                        FROM tmp_c_mnns nc
                        LEFT JOIN LATERAL
                                      (
                                      SELECT er.ehrrusid
                                      FROM ehr.t_ehr_rus_dvl105         er
                                      LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                                      WHERE er.idehr = nc.ehrrusdvl105
                                        AND rrb.currversion = nc.ehrrusdvl105ver
                                      LIMIT 1
                                      ) ehr ON TRUE
                        LEFT JOIN LATERAL
                                      (
                                      SELECT er.ehrrusid
                                      FROM ehr.t_ehr_rus_nsv101         er
                                      LEFT JOIN ehr.t_ehr_rus_ref_books rrb1
                                                    ON rrb1.ehrrusrefbooksid = er.ehrrusrefbooks
                                      WHERE er.idehr = nc.ehrrusnsv101
                                        AND rrb1.currversion = nc.ehrrusnsv101ver
                                      LIMIT 1
                                      ) ehr1 ON TRUE
                        WHERE NOT EXISTS
                                  (SELECT *
                                   FROM pharm.t_c_mnn c
                                   WHERE c.cmnnid = nc.id
                                  );
                    ELSE
                        -- Generate table Tmp_C_MNN
                        DROP TABLE IF EXISTS tmp_c_mnn;
                        CREATE TEMP TABLE tmp_c_mnn
                        AS
                        SELECT (XPATH('/header[1]/ID[1]/text()', t.x1))[1]::text::uuid                         AS id,
                               (XPATH('/body[1]/Active[1]/text()', t.x2))[1]::text::boolean                    AS active,
                               (XPATH('/body[1]/IsDrug[1]/text()', t.x2))[1]::text::boolean                    AS isdrug,
                               (XPATH('/body[1]/NameRus[1]/text()', t.x2))[1]::varchar(1000)                   AS namerus,
                               (XPATH('/body[1]/NameEng[1]/text()', t.x2))[1]::varchar(1000)                   AS nameeng,
                               (XPATH('/body[1]/NameLatR[1]/text()', t.x2))[1]::varchar(4000)                  AS namelatr,
                               (XPATH('/body[1]/OtherID[1]/text()', t.x2))[1]::varchar(255)                    AS otherid,
                               (XPATH('/body[1]/ChemicalName[1]/text()', t.x2))[1]::varchar(600)               AS chemicalname,
                               (XPATH('/body[1]/BruttoFormula[1]/text()', t.x2))[1]::varchar(100)              AS bruttoformula,
                               (XPATH('/body[1]/Characteristic[1]/text()', t.x2))[1]::varchar(4000)            AS characteristic,
                               (XPATH('/body[1]/Pharmacology[1]/text()', t.x2))[1]::text                       AS pharmacology,
                               (XPATH('/body[1]/Practice[1]/text()', t.x2))[1]::varchar(6000)                  AS practice,
                               (XPATH('/body[1]/Contraindications[1]/text()', t.x2))[1]::varchar(4000)         AS contraindications,
                               (XPATH('/body[1]/Restrictions[1]/text()', t.x2))[1]::text                       AS restrictions,
                               (XPATH('/body[1]/UseOfPregnancy[1]/text()', t.x2))[1]::text                     AS useofpregnancy,
                               (XPATH('/body[1]/SideEffects[1]/text()', t.x2))[1]::text                        AS sideeffects,
                               (XPATH('/body[1]/Interaction[1]/text()', t.x2))[1]::text                        AS interaction,
                               (XPATH('/body[1]/InjectionMethod[1]/text()', t.x2))[1]::varchar(4000)           AS injectionmethod,
                               (XPATH('/body[1]/Caution[1]/text()', t.x2))[1]::text                            AS caution,
                               (XPATH('/body[1]/Overdose[1]/text()', t.x2))[1]::varchar(4000)                  AS overdose,
                               (XPATH('/body[1]/CASCode[1]/text()', t.x2))[1]::varchar(50)                     AS cascode,
                               (XPATH('/body[1]/UsedIn[1]/text()', t.x2))[1]::text::int                        AS usedin,
                               (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]/IsNarcoticAndPsychotropic[1]/text()', t.x2))[1]::text::boolean AS IsNarcoticAndPsychotropic,
                               (XPATH('/body[1]/EhrRusDvl105[1]/text()', t.x2))[1]::text::int                  AS ehrrusdvl105,
                               (XPATH('/body[1]/EhrRusDvl105Ver[1]/text()', t.x2))[1]::varchar(8)              AS ehrrusdvl105ver,
                               (XPATH('/body[1]/EhrRusNsv101[1]/text()', t.x2))[1]::text::int                  AS ehrrusnsv101,
                               (XPATH('/body[1]/EhrRusNsv101Ver[1]/text()', t.x2))[1]::varchar(8)              AS ehrrusnsv101ver
                            FROM (SELECT UNNEST(XPATH('/C_MNN/header', x)) AS x1,
                                         UNNEST(XPATH('/C_MNN/body', x))   AS x2
                                  FROM UNNEST(XPATH('/C_MNN', "Xml"::xml)) x
                                 ) t;

                        IF _operationtype = _delete
                        THEN
                            -- Удаление
                            UPDATE pharm.t_c_mnn c
                            SET active = '0'
                            FROM tmp_c_mnn nc
                            WHERE nc.id = c.cmnnid;
                        ELSE
                            -- update pharm.T_C_MNN
                            UPDATE pharm.t_c_mnn c
                            SET nameeng                   = nc.nameeng,
                                namerus                   = nc.namerus,
                                namelatr                  = nc.namelatr,
                                active                    = nc.active,
                                isdrug                    = nc.isdrug,
                                otherid                   = nc.otherid,
                                chemicalname              = nc.chemicalname,
                                bruttoformula             = nc.bruttoformula,
                                characteristic            = nc.characteristic,
                                pharmacology              = nc.pharmacology,
                                practice                  = nc.practice,
                                contraindications         = nc.contraindications,
                                restrictions              = nc.restrictions,
                                useofpregnancy            = nc.useofpregnancy,
                                sideeffects               = nc.sideeffects,
                                interaction               = nc.interaction,
                                injectionmethod           = nc.injectionmethod,
                                caution                   = nc.caution,
                                overdose                  = nc.overdose,
                                cascode                   = nc.cascode,
                                usedin                    = nc.usedin,
                                regcode                   = nc.regcode,
                                fedcode                   = nc.fedcode,
                                IsNarcoticAndPsychotropic = COALESCE(nc.IsNarcoticAndPsychotropic, c.IsNarcoticAndPsychotropic),
                                ehrrusdvl105              = ehr.ehrrusid,
                                ehrrusnsv101              = ehr1.ehrrusid
                            FROM tmp_c_mnn  nc
                            LEFT JOIN LATERAL
                                          (
                                          SELECT er.ehrrusid
                                          FROM ehr.t_ehr_rus_dvl105         er
                                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                                          WHERE er.idehr = nc.ehrrusdvl105
                                            AND rrb.currversion = nc.ehrrusdvl105ver
                                          LIMIT 1
                                          ) ehr ON TRUE
                            LEFT JOIN LATERAL
                                          (
                                          SELECT er.ehrrusid
                                          FROM ehr.t_ehr_rus_nsv101         er
                                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb1
                                                        ON rrb1.ehrrusrefbooksid = er.ehrrusrefbooks
                                          WHERE er.idehr = nc.ehrrusnsv101
                                            AND rrb1.currversion = nc.ehrrusnsv101ver
                                          LIMIT 1
                                          ) ehr1 ON TRUE
                            WHERE nc.id = c.cmnnid
                              AND _operationtype = _update;

                            -- insert pharm.T_C_MNN
                            INSERT INTO
                                pharm.t_c_mnn
                            (cmnnid,
                             namerus,
                             nameeng,
                             active,
                             isdrug,
                             otherid,
                             chemicalname,
                             bruttoformula,
                             characteristic,
                             pharmacology,
                             practice,
                             contraindications,
                             restrictions,
                             useofpregnancy,
                             sideeffects,
                             interaction,
                             injectionmethod,
                             caution,
                             overdose,
                             cascode,
                             usedin,
                             regcode,
                             fedcode,
                             IsNarcoticAndPsychotropic,
                             namelatr,
                             ehrrusdvl105,
                             ehrrusnsv101)
                            SELECT nc.id,
                                   nc.namerus,
                                   nc.nameeng,
                                   nc.active,
                                   nc.isdrug,
                                   nc.otherid,
                                   nc.chemicalname,
                                   nc.bruttoformula,
                                   nc.characteristic,
                                   nc.pharmacology,
                                   nc.practice,
                                   nc.contraindications,
                                   nc.restrictions,
                                   nc.useofpregnancy,
                                   nc.sideeffects,
                                   nc.interaction,
                                   nc.injectionmethod,
                                   nc.caution,
                                   nc.overdose,
                                   nc.cascode,
                                   nc.usedin,
                                   nc.regcode,
                                   nc.fedcode,
                                   COALESCE(nc.IsNarcoticAndPsychotropic, false),
                                   nc.namelatr,
                                   ehr.ehrrusid,
                                   ehr1.ehrrusid
                            FROM tmp_c_mnn  nc
                            LEFT JOIN LATERAL
                                          (
                                          SELECT er.ehrrusid
                                          FROM ehr.t_ehr_rus_dvl105         er
                                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                                          WHERE er.idehr = nc.ehrrusdvl105
                                            AND rrb.currversion = nc.ehrrusdvl105ver
                                          LIMIT 1
                                          ) ehr ON TRUE
                            LEFT JOIN LATERAL
                                          (
                                          SELECT er.ehrrusid
                                          FROM ehr.t_ehr_rus_nsv101         er
                                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                                          WHERE er.idehr = nc.ehrrusnsv101
                                            AND rrb.currversion = nc.ehrrusnsv101ver
                                          LIMIT 1
                                          ) ehr1 ON TRUE
                            WHERE NOT EXISTS
                                (SELECT *
                                 FROM pharm.t_c_mnn c
                                 WHERE c.cmnnid = nc.id
                                )
                              AND _operationtype = _insert;
                        END IF;

                        _entid := (SELECT entityid FROM vclib.t_entities WHERE entname = 'C_MNN' LIMIT 1);
                        IF EXISTS
                            (SELECT *
                             FROM vclib.t_gen_numbers
                             WHERE entityid = _entid
                               AND numbername = 'PHARM.C_MNN'
                            )
                        THEN
                            ---- генерируем след. номер
                            --perform public.sp_GenerateNumber(@EntId, 'PHARM.C_MNN');

                            UPDATE vclib.t_gen_numbers
                            SET nextnumber = (SELECT MAX(otherid) FROM pharm.t_c_mnn) --+ 1
                            WHERE entityid = _entid
                              AND numbername = 'PHARM.C_MNN';
                        ELSE
                            INSERT INTO
                                vclib.t_gen_numbers
                                (gennumbersid, entityid, numbername, nextnumber)
                            VALUES (gen_random_uuid(),
                                    _entid,
                                    'PHARM.C_MNN',
                                    COALESCE((SELECT MAX(otherid) FROM pharm.t_c_mnn), 1));
                        END IF;
                    END IF;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;