-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-12-22
-- Description: Выгрузка/загрузка справочника Торговое наименование препарата (ТРН) в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS dbo.sp_rds_ctrn;
DROP PROCEDURE IF EXISTS sp_rds_ctrn;
CREATE OR REPLACE PROCEDURE sp_rds_ctrn(
    "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_TRN';
        _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_trn;
            CREATE TEMP TABLE temp_c_trn
            (
                ctrnid              uuid,
                otherid             INT,
                namerus             VARCHAR(4000),
                nameeng             VARCHAR(4000),
                active              BOOLEAN,
                characteristic      VARCHAR(4000),
                pharmacologyaction  VARCHAR(4000),
                practice            TEXT,
                contraindications   VARCHAR(5000),
                restrictions        TEXT,
                useofpregnancy      TEXT,
                sideeffects         TEXT,
                interaction         TEXT,
                overdose            TEXT,
                injectionmethod     VARCHAR(255),
                caution             TEXT,
                composition         VARCHAR(3000),
                pharmdescription    TEXT,
                pharmacodynamics    TEXT,
                indications         VARCHAR(8000),
                expirationdate      VARCHAR(300),
                mnnname             TEXT,
                manufacturer        VARCHAR(3000),
                cmnn                uuid,
                producttype         INT,
                namelatr            VARCHAR(4000),
                fedcode             VARCHAR(50),
                regcode             VARCHAR(50),
                ehrrusp08tsh954n    VARCHAR(52),
                ehrrusp08tsh954nver VARCHAR(8),
                ehrrus575vsf88og    VARCHAR(52),
                ehrrus575vsf88ogver VARCHAR(8),
                atc                 INT,
                atcver              VARCHAR(8)
            );
            
            _sql := 'insert into Temp_C_TRN
						(
							CTrnID,
							OtherID,
							NameRus,
							NameEng,
							Active,
							Characteristic,
							PharmacologyAction,
							Practice,
							Contraindications,
							Restrictions,
							UseOfPregnancy,
							SideEffects,
							Interaction,
							Overdose,
							InjectionMethod,
							Caution,
							Composition,
							PharmDescription,
							Pharmacodynamics,
							Indications,
							ExpirationDate,
							MNNName,
							Manufacturer,
							CMnn,
							ProductType,
							NameLatR,
							FedCode,
							RegCode,
							EhrRusP08TSH954N,
							EhrRusP08TSH954NVer,
							EhrRus575VSF88OG,
							EhrRus575VSF88OGVer,
							Atc,
							AtcVer
						); '
                        || ' SELECT trn.CTrnID,
							trn.OtherID,
							trn.NameRus,
							trn.NameEng,
							trn.Active,
							trn.Characteristic,
							trn.PharmacologyAction,
							trn.Practice,
							trn.Contraindications,
							trn.Restrictions,
							trn.UseOfPregnancy,
							trn.SideEffects,
							trn.Interaction,
							trn.Overdose,
							trn.InjectionMethod,
							trn.Caution,
							trn.Composition,
							trn.PharmDescription,
							trn.Pharmacodynamics,
							trn.Indications,
							trn.ExpirationDate,
							trn.MNNName,
							trn.Manufacturer,
							trn.CMnn,
							trn.ProductType,
							trn.NameLatR,
							trn.FedCode,
							trn.RegCode,
							trn.EhrRusP08TSH954N,
							trn.EhrRusP08TSH954NVer,
							trn.EhrRus575VSF88OG,
							trn.EhrRus575VSF88OGVer,
							trn.Atc,
							trn.AtcVer '
                        || 'FROM dblink(''dbname=' || "PATH"
                        || ''','
                        || ''' SELECT nc.CTrnID, nc.OtherID, nc.NameRus, nc.NameEng, nc.Active, nc.Characteristic, nc.PharmacologyAction, nc.Practice
				, nc.Contraindications, nc.Restrictions, nc.UseOfPregnancy, nc.SideEffects, nc.Interaction, nc.Overdose, nc.InjectionMethod, nc.Caution
				, nc.Composition, nc.PharmDescription, nc.Pharmacodynamics, nc.Indications, nc.ExpirationDate, nc.MNNName, nc.Manufacturer, nc.CMnn,
				, nc.ProductType, nc.NameLatR, nc.FedCode, nc.RegCode, eh1.IdEHR, eh1.CurrVersion, eh2.IdEHR, eh2.CurrVersion, atc.IDEHR, atc.IDEHR as Atc, ac.CurrVersion as AtcVer
				FROM PHARM.T_C_TRN nc
				left join lateral
				(
					SELECT EH.IDEHR, rrb.CurrVersion
					FROM EHR.T_EHR_RUS_P08TSH954N EH
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					WHERE NC.EHRRUSP08TSH954N = EH.EHRRUSID
					limit 1
				) EH1 on true
				left join lateral
				(
					SELECT EH.IDEHR, RRB.CurrVersion
					FROM EHR.T_EHR_RUS_575VSF88OG EH
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					WHERE NC.EHRRUS575VSF88OG = EH.EHRRUSID
					limit 1
				) EH2 on true
				left join lateral
				(
					SELECT EH.IDEHR, rrb.CurrVersion
					from EHR.T_EHR_RUS_M00002 EH
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
					limit 1
				) atc on true
				'') '
                || ' AS trn(CTrnID uuid,
					OtherID					INT,
					NameRus					VARCHAR(4000),
					NameEng					VARCHAR(4000),
					Active					boolean,
					Characteristic			VARCHAR(4000),
					PharmacologyAction		VARCHAR(4000),
					Practice				text,
					Contraindications		VARCHAR(5000),
					Restrictions			text,
					UseOfPregnancy			text,
					SideEffects				text,
					Interaction				text,
					Overdose				text,
					InjectionMethod			VARCHAR(255),
					Caution					text,
					Composition				VARCHAR(3000),
					PharmDescription		text,
					Pharmacodynamics		text,
					Indications				VARCHAR(8000),
					ExpirationDate			VARCHAR(300),
					MNNName					text,
					Manufacturer			VARCHAR(3000),
					CMnn					uuid,
					ProductType				INT,
					NameLatR				VARCHAR(4000),
					FedCode					VARCHAR(50),
					RegCode					VARCHAR(50),
					EhrRusP08TSH954N		VARCHAR(52),
					EhrRusP08TSH954NVer		VARCHAR(8),
					EhrRus575VSF88OG		VARCHAR(52),
					EhrRus575VSF88OGVer		VARCHAR(8)
					,Atc					int
					,AtcVer					VARCHAR(8)) ';
            
            EXECUTE _sql;
            
            -- МНН не из справочника RDS переводим в неактивные
            UPDATE pharm.t_c_trn c
            SET active = '0'
            WHERE NOT EXISTS
                      (SELECT *
                       FROM temp_c_trn nc
                       WHERE nc.ctrnid = c.ctrnid
                      );
            
            -- Обновляем имеющиеся TRN
            UPDATE pharm.t_c_trn c
            SET nameeng            = nc.nameeng,
                namerus            = nc.namerus,
                namelatr           = nc.namelatr,
                active             = nc.active,
                otherid            = nc.otherid,
                characteristic     = nc.characteristic,
                pharmacologyaction = nc.pharmacologyaction,
                practice           = nc.practice,
                contraindications  = nc.contraindications,
                restrictions       = nc.restrictions,
                useofpregnancy     = nc.useofpregnancy,
                sideeffects        = nc.sideeffects,
                interaction        = nc.interaction,
                overdose           = nc.overdose,
                injectionmethod    = nc.injectionmethod,
                caution            = nc.caution,
                composition        = nc.composition,
                pharmdescription   = nc.pharmdescription,
                pharmacodynamics   = nc.pharmacodynamics,
                indications        = nc.indications,
                expirationdate     = nc.expirationdate,
                mnnname            = nc.mnnname,
                manufacturer       = nc.manufacturer,
                cmnn               = nc.cmnn,
                atc                = atc.ehrrusid,
                producttype        = nc.producttype,
                regcode            = nc.regcode,
                fedcode            = nc.fedcode,
                ehrrusp08tsh954n   = eh1.ehrrusid,
                ehrrus575vsf88og   = eh2.ehrrusid
            FROM temp_c_trn nc
            LEFT JOIN LATERAL
                          (
                          SELECT eh.ehrrusid
                          FROM ehr.t_ehr_rus_p08tsh954n     eh
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                          WHERE eh.codeehr = nc.ehrrusp08tsh954n
                            AND rrb.currversion = nc.ehrrusp08tsh954nver
                          LIMIT 1
                          ) eh1 ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT eh.ehrrusid
                          FROM ehr.t_ehr_rus_575vsf88og     eh
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                          WHERE eh.codeehr = nc.ehrrus575vsf88og
                            AND rrb.currversion = nc.ehrrus575vsf88ogver
                          LIMIT 1
                          ) eh2 ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT eh.ehrrusid
                          FROM ehr.t_ehr_rus_m00002         eh
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrbatc ON rrbatc.ehrrusrefbooksid = eh.ehrrusrefbooks
                          WHERE eh.idehr = nc.atc
                            AND rrbatc.currversion = nc.atcver
                          LIMIT 1
                          ) atc ON TRUE
            WHERE nc.ctrnid = c.ctrnid;
            
            -- Загружаем новые
            INSERT INTO
                pharm.t_c_trn
            (ctrnid,
             namerus,
             nameeng,
             active,
             otherid,
             characteristic,
             pharmacologyaction,
             practice,
             contraindications,
             restrictions,
             useofpregnancy,
             sideeffects,
             interaction,
             overdose,
             injectionmethod,
             caution,
             composition,
             pharmdescription,
             pharmacodynamics,
             indications,
             expirationdate,
             mnnname,
             manufacturer,
             cmnn,
             atc,
             producttype,
             regcode,
             fedcode,
             ehrrusp08tsh954n,
             ehrrus575vsf88og,
             namelatr)
            SELECT nc.ctrnid,
                   nc.namerus,
                   nc.nameeng,
                   nc.active,
                   nc.otherid,
                   nc.characteristic,
                   nc.pharmacologyaction,
                   nc.practice,
                   nc.contraindications,
                   nc.restrictions,
                   nc.useofpregnancy,
                   nc.sideeffects,
                   nc.interaction,
                   nc.overdose,
                   nc.injectionmethod,
                   nc.caution,
                   nc.composition,
                   nc.pharmdescription,
                   nc.pharmacodynamics,
                   nc.indications,
                   nc.expirationdate,
                   nc.mnnname,
                   nc.manufacturer,
                   nc.cmnn,
                   atc.ehrrusid,
                   nc.producttype,
                   nc.regcode,
                   nc.fedcode,
                   eh1.ehrrusid,
                   eh2.ehrrusid,
                   nc.namelatr
            FROM temp_c_trn nc
            LEFT JOIN LATERAL
                          (
                          SELECT eh.ehrrusid
                          FROM ehr.t_ehr_rus_p08tsh954n     eh
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                          WHERE eh.codeehr = nc.ehrrusp08tsh954n
                            AND rrb.currversion = nc.ehrrusp08tsh954nver
                          LIMIT 1
                          ) eh1 ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT eh.ehrrusid
                          FROM ehr.t_ehr_rus_575vsf88og     eh
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                          WHERE eh.codeehr = nc.ehrrus575vsf88og
                            AND rrb.currversion = nc.ehrrus575vsf88ogver
                          LIMIT 1
                          ) eh2 ON TRUE
            LEFT JOIN LATERAL
                          (
                          SELECT eh.ehrrusid
                          FROM ehr.t_ehr_rus_m00002         eh
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrbatc ON rrbatc.ehrrusrefbooksid = eh.ehrrusrefbooks
                          WHERE eh.idehr = nc.atc
                            AND rrbatc.currversion = nc.atcver
                          LIMIT 1
                          ) atc ON TRUE
            WHERE NOT EXISTS
                      (SELECT *
                       FROM pharm.t_c_trn c
                       WHERE c.ctrnid = nc.ctrnid
                      );
        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_trn c
                     WHERE c.ctrnid = "ID"
                    )
                THEN
                    _operationtype := _update;
                ELSIF "IsNew" = 0 AND NOT EXISTS
                    (SELECT*
                     FROM pharm.t_c_trn c
                     WHERE c.ctrnid = "ID"
                    )
                THEN
                    _operationtype := _delete;
                    _result := XMLELEMENT(NAME "C_TRN",
                                          XMLELEMENT(NAME header, XMLFOREST(_operationtype AS "TYPE", "ID" AS "ID")));
                END IF;
                
                -- Обработка операции
                IF _operationtype = _ftload
                THEN
                    -- Первичная загрузка
                    SELECT '<C_TRN><header><TYPE>' || _ftload || '</TYPE></header><body><C_TRNS>' ||
                           COALESCE(STRING_AGG(
                                        XMLSERIALIZE(CONTENT
                                                     REGEXP_REPLACE(tt.xml_elem::text, '<([^a-zA-Z/!?])', '&lt;\1', 'g')::xml AS TEXT),
                                        ''
                                    ), '') ||
                           '</C_TRNS></body></C_TRN>'
                    INTO _result
                    FROM (SELECT XMLELEMENT
                                 (
                                     NAME "C_TRN",
                                     XMLFOREST
                                     (
                                         c.ctrnid AS "ID",
                                         c.active AS "Active",
                                         c.namerus AS "NameRus",
                                         c.nameeng AS "NameEng",
                                         c.namelatr AS "NameLatR",
                                         c.otherid AS "OtherID",
                                         c.characteristic AS "Characteristic",
                                         c.pharmacologyaction AS "PharmacologyAction",
                                         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.overdose AS "Overdose",
                                         c.injectionmethod AS "InjectionMethod",
                                         c.caution AS "Caution",
                                         c.composition AS "Composition",
                                         c.pharmdescription AS "PharmDescription",
                                         c.pharmacodynamics AS "Pharmacodynamics",
                                         c.indications AS "Indications",
                                         c.expirationdate AS "ExpirationDate",
                                         c.mnnname AS "MNNName",
                                         c.manufacturer AS "Manufacturer",
                                         c.cmnn AS "CMnn",
                                         c.producttype AS "ProductType",
                                         c.regcode AS "RegCode",
                                         c.fedcode AS "FedCode",
                                         ehr1.codeehr AS "EhrRusP08TSH954N",
                                         rrb.currversion AS "EhrRusP08TSH954NVer",
                                         ehr2.codeehr AS "EhrRus575VSF88OG",
                                         rrb1.currversion AS "EhrRus575VSF88OGVer",
                                         atc.idehr AS "Atc",
                                         rrbatc.currversion AS "AtcVer"
                                     )
                                 ) AS xml_elem
                          FROM pharm.t_c_trn                 c
                          LEFT JOIN ehr.t_ehr_rus_p08tsh954n ehr1 ON ehr1.ehrrusid = c.ehrrusp08tsh954n
                          LEFT JOIN ehr.t_ehr_rus_ref_books  rrb ON rrb.ehrrusrefbooksid = ehr1.ehrrusrefbooks
                          LEFT JOIN ehr.t_ehr_rus_575vsf88og ehr2 ON ehr2.ehrrusid = c.ehrrus575vsf88og
                          LEFT JOIN ehr.t_ehr_rus_ref_books  rrb1 ON rrb1.ehrrusrefbooksid = ehr2.ehrrusrefbooks
                          LEFT JOIN ehr.t_ehr_rus_m00002     atc ON atc.ehrrusid = c.atc
                          LEFT JOIN ehr.t_ehr_rus_ref_books  rrbatc ON rrbatc.ehrrusrefbooksid = atc.ehrrusrefbooks
                         ) tt;
                
                ELSIF _operationtype <> _delete
                THEN
                    -- собираем C_TRN
                    SELECT XMLELEMENT
                           (
                               NAME "C_TRN",
                               XMLELEMENT
                               (
                                   NAME header,
                                   XMLFOREST
                                   (
                                       _operationtype AS "TYPE",
                                       "ID" AS "ID"
                                   )
                               ),
                               XMLELEMENT
                               (
                                   NAME body,
                                   XMLFOREST
                                   (
                                       c.active AS "Active",
                                       c.namerus AS "NameRus",
                                       c.nameeng AS "NameEng",
                                       c.namelatr AS "NameLatR",
                                       c.otherid AS "OtherID",
                                       c.characteristic AS "Characteristic",
                                       c.pharmacologyaction AS "PharmacologyAction",
                                       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.overdose AS "Overdose",
                                       c.injectionmethod AS "InjectionMethod",
                                       c.caution AS "Caution",
                                       c.composition AS "Composition",
                                       c.pharmdescription AS "PharmDescription",
                                       c.pharmacodynamics AS "Pharmacodynamics",
                                       c.indications AS "Indications",
                                       c.expirationdate AS "ExpirationDate",
                                       c.mnnname AS "MNNName",
                                       c.manufacturer AS "Manufacturer",
                                       c.cmnn AS "CMnn",
                                       c.producttype AS "ProductType",
                                       c.regcode AS "RegCode",
                                       c.fedcode AS "FedCode",
                                       ehr1.codeehr AS "EhrRusP08TSH954N",
                                       rrb.currversion AS "EhrRusP08TSH954NVer",
                                       ehr2.codeehr AS "EhrRus575VSF88OG",
                                       rrb1.currversion AS "EhrRus575VSF88OGVer",
                                       atc.idehr AS "Atc",
                                       rrbatc.currversion AS "AtcVer"
                                   )
                               )
                           )
                    INTO _result
                    FROM pharm.t_c_trn                 c
                    LEFT JOIN ehr.t_ehr_rus_p08tsh954n ehr1 ON ehr1.ehrrusid = c.ehrrusp08tsh954n
                    LEFT JOIN ehr.t_ehr_rus_ref_books  rrb ON rrb.ehrrusrefbooksid = ehr1.ehrrusrefbooks
                    LEFT JOIN ehr.t_ehr_rus_575vsf88og ehr2 ON ehr2.ehrrusid = c.ehrrus575vsf88og
                    LEFT JOIN ehr.t_ehr_rus_ref_books  rrb1 ON rrb1.ehrrusrefbooksid = ehr2.ehrrusrefbooks
                    LEFT JOIN ehr.t_ehr_rus_m00002     atc ON atc.ehrrusid = c.atc
                    LEFT JOIN ehr.t_ehr_rus_ref_books  rrbatc ON rrbatc.ehrrusrefbooksid = atc.ehrrusrefbooks
                    WHERE c.ctrnid = "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('/C_TRN/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::VARCHAR(36);
                    
                    
                    -- Обработка операции
                    IF _operationtype = _ftload
                    THEN
                        -- Generate table Tmp_C_TRNS
                        DROP TABLE IF EXISTS tmp_c_trns;
                        CREATE TEMP TABLE tmp_c_trns
                        AS
                            SELECT (XPATH('/C_TRN/ID[1]/text()', x))[1]::TEXT::uuid                    AS id,
                                   (XPATH('/C_TRN/Active[1]/text()', x))[1]::TEXT::BOOLEAN             AS active,
                                   (XPATH('/C_TRN/NameRus[1]/text()', x))[1]::VARCHAR(4000)            AS namerus,
                                   (XPATH('/C_TRN/NameEng[1]/text()', x))[1]::VARCHAR(4000)            AS nameeng,
                                   (XPATH('/C_TRN/NameLatR[1]/text()', x))[1]::VARCHAR(4000)           AS namelatr,
                                   (XPATH('/C_TRN/OtherID[1]/text()', x))[1]::TEXT::INT                AS otherid,
                                   (XPATH('/C_TRN/Characteristic[1]/text()', x))[1]::VARCHAR(4000)     AS characteristic,
                                   (XPATH('/C_TRN/PharmacologyAction[1]/text()', x))[1]::VARCHAR(4000) AS pharmacologyaction,
                                   (XPATH('/C_TRN/Practice[1]/text()', x))[1]::TEXT                    AS practice,
                                   (XPATH('/C_TRN/Contraindications[1]/text()', x))[1]::VARCHAR(4000)  AS contraindications,
                                   (XPATH('/C_TRN/Restrictions[1]/text()', x))[1]::TEXT                AS restrictions,
                                   (XPATH('/C_TRN/UseOfPregnancy[1]/text()', x))[1]::TEXT              AS useofpregnancy,
                                   (XPATH('/C_TRN/SideEffects[1]/text()', x))[1]::TEXT                 AS sideeffects,
                                   (XPATH('/C_TRN/Interaction[1]/text()', x))[1]::TEXT                 AS interaction,
                                   (XPATH('/C_TRN/Overdose[1]/text()', x))[1]::VARCHAR(1000)           AS overdose,
                                   (XPATH('/C_TRN/InjectionMethod[1]/text()', x))[1]::VARCHAR(255)     AS injectionmethod,
                                   (XPATH('/C_TRN/Caution[1]/text()', x))[1]::TEXT                     AS caution,
                                   (XPATH('/C_TRN/Composition[1]/text()', x))[1]::VARCHAR(255)         AS composition,
                                   (XPATH('/C_TRN/PharmDescription[1]/text()', x))[1]::TEXT            AS pharmdescription,
                                   (XPATH('/C_TRN/Pharmacodynamics[1]/text()', x))[1]::TEXT            AS pharmacodynamics,
                                   (XPATH('/C_TRN/Indications[1]/text()', x))[1]::VARCHAR(8000)        AS indications,
                                   (XPATH('/C_TRN/ExpirationDate[1]/text()', x))[1]::VARCHAR(300)      AS expirationdate,
                                   (XPATH('/C_TRN/MNNName[1]/text()', x))[1]::VARCHAR(8000)            AS mnnname,
                                   (XPATH('/C_TRN/Manufacturer[1]/text()', x))[1]::VARCHAR(3000)       AS manufacturer,
                                   (XPATH('/C_TRN/CMnn[1]/text()', x))[1]::TEXT::uuid                  AS cmnn,
                                   (XPATH('/C_TRN/Atc[1]/text()', x))[1]::TEXT::INTEGER                AS atc,
                                   (XPATH('/C_TRN/AtcVer[1]/text()', x))[1]::VARCHAR(52)               AS atcver,
                                   (XPATH('/C_TRN/ProductType[1]/text()', x))[1]::TEXT::INT            AS producttype,
                                   (XPATH('/C_TRN/RegCode[1]/text()', x))[1]::VARCHAR(50)              AS regcode,
                                   (XPATH('/C_TRN/FedCode[1]/text()', x))[1]::VARCHAR(50)              AS fedcode,
                                   (XPATH('/C_TRN/EhrRusP08TSH954N[1]/text()', x))[1]::VARCHAR(52)     AS ehrrusp08tsh954n,
                                   (XPATH('/C_TRN/EhrRusP08TSH954NVer[1]/text()', x))[1]::VARCHAR(52)  AS ehrrusp08tsh954nver,
                                   (XPATH('/C_TRN/EhrRus575VSF88OG[1]/text()', x))[1]::VARCHAR(52)     AS ehrrus575vsf88og,
                                   (XPATH('/C_TRN/EhrRus575VSF88OGVer[1]/text()', x))[1]::VARCHAR(52)  AS ehrrus575vsf88ogver
                            FROM UNNEST(XPATH('/C_TRN/body/C_TRNS/C_TRN', "Xml"::xml)) AS x;
                        
                        -- Первичная загрузка
                        -- МНН не из справочника RDS переводим в неактивные
                        UPDATE pharm.t_c_trn c
                        SET active = '0'
                        WHERE NOT EXISTS
                                  (SELECT*
                                   FROM tmp_c_trns nc
                                   WHERE nc.id = c.ctrnid
                                  );
                        
                        -- Обновляем имеющиеся TRN
                        UPDATE pharm.t_c_trn c
                        SET nameeng            = nc.nameeng,
                            namerus            = nc.namerus,
                            namelatr           = nc.namelatr,
                            active             = nc.active,
                            otherid            = nc.otherid,
                            characteristic     = nc.characteristic,
                            pharmacologyaction = nc.pharmacologyaction,
                            practice           = nc.practice,
                            contraindications  = nc.contraindications,
                            restrictions       = nc.restrictions,
                            useofpregnancy     = nc.useofpregnancy,
                            sideeffects        = nc.sideeffects,
                            interaction        = nc.interaction,
                            overdose           = nc.overdose,
                            injectionmethod    = nc.injectionmethod,
                            caution            = nc.caution,
                            composition        = nc.composition,
                            pharmdescription   = nc.pharmdescription,
                            pharmacodynamics   = nc.pharmacodynamics,
                            indications        = nc.indications,
                            expirationdate     = nc.expirationdate,
                            mnnname            = nc.mnnname,
                            manufacturer       = nc.manufacturer,
                            cmnn               = nc.cmnn,
                            atc                = atc.ehrrusid,
                            producttype        = nc.producttype,
                            regcode            = nc.regcode,
                            fedcode            = nc.fedcode,
                            ehrrusp08tsh954n   = eh1.ehrrusid,
                            ehrrus575vsf88og   = eh2.ehrrusid
                        FROM tmp_c_trns nc
                        LEFT JOIN LATERAL
                                      (
                                      SELECT eh.ehrrusid
                                      FROM ehr.t_ehr_rus_p08tsh954n     eh
                                      LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                                      WHERE eh.codeehr = nc.ehrrusp08tsh954n
                                        AND rrb.currversion = nc.ehrrusp08tsh954nver
                                      LIMIT 1
                                      ) eh1 ON TRUE
                        LEFT JOIN LATERAL
                                      (
                                      SELECT eh.ehrrusid
                                      FROM ehr.t_ehr_rus_575vsf88og     eh
                                      LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                                      WHERE eh.codeehr = nc.ehrrus575vsf88og
                                        AND rrb.currversion = nc.ehrrus575vsf88ogver
                                      LIMIT 1
                                      ) eh2 ON TRUE
                        LEFT JOIN LATERAL
                                      (
                                      SELECT eh.ehrrusid
                                      FROM ehr.t_ehr_rus_m00002         eh
                                      LEFT JOIN ehr.t_ehr_rus_ref_books rrbatc
                                                    ON rrbatc.ehrrusrefbooksid = eh.ehrrusrefbooks
                                      WHERE eh.idehr = nc.atc
                                        AND rrbatc.currversion = nc.atcver
                                      LIMIT 1
                                      ) atc ON TRUE
                        WHERE nc.id = c.ctrnid;
                        
                        -- Загружаем новые
                        INSERT INTO
                            pharm.t_c_trn
                        (ctrnid,
                         namerus,
                         nameeng,
                         active,
                         otherid,
                         characteristic,
                         pharmacologyaction,
                         practice,
                         contraindications,
                         restrictions,
                         useofpregnancy,
                         sideeffects,
                         interaction,
                         overdose,
                         injectionmethod,
                         caution,
                         composition,
                         pharmdescription,
                         pharmacodynamics,
                         indications,
                         expirationdate,
                         mnnname,
                         manufacturer,
                         cmnn,
                         atc,
                         producttype,
                         regcode,
                         fedcode,
                         ehrrusp08tsh954n,
                         ehrrus575vsf88og,
                         namelatr)
                        SELECT nc.id,
                               nc.namerus,
                               nc.nameeng,
                               nc.active,
                               nc.otherid,
                               nc.characteristic,
                               nc.pharmacologyaction,
                               nc.practice,
                               nc.contraindications,
                               nc.restrictions,
                               nc.useofpregnancy,
                               nc.sideeffects,
                               nc.interaction,
                               nc.overdose,
                               nc.injectionmethod,
                               nc.caution,
                               nc.composition,
                               nc.pharmdescription,
                               nc.pharmacodynamics,
                               nc.indications,
                               nc.expirationdate,
                               nc.mnnname,
                               nc.manufacturer,
                               nc.cmnn,
                               atc.ehrrusid,
                               nc.producttype,
                               nc.regcode,
                               nc.fedcode,
                               eh1.ehrrusid,
                               eh2.ehrrusid,
                               nc.namelatr
                        FROM tmp_c_trns nc
                        LEFT JOIN LATERAL
                                      (
                                      SELECT eh.ehrrusid
                                      FROM ehr.t_ehr_rus_p08tsh954n     eh
                                      LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                                      WHERE eh.codeehr = nc.ehrrusp08tsh954n
                                        AND rrb.currversion = nc.ehrrusp08tsh954nver
                                      LIMIT 1
                                      ) eh1 ON TRUE
                        LEFT JOIN LATERAL
                                      (
                                      SELECT eh.ehrrusid
                                      FROM ehr.t_ehr_rus_575vsf88og     eh
                                      LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                                      WHERE eh.codeehr = nc.ehrrus575vsf88og
                                        AND rrb.currversion = nc.ehrrus575vsf88ogver
                                      LIMIT 1
                                      ) eh2 ON TRUE
                        LEFT JOIN LATERAL
                                      (
                                      SELECT eh.ehrrusid
                                      FROM ehr.t_ehr_rus_m00002         eh
                                      LEFT JOIN ehr.t_ehr_rus_ref_books rrbatc
                                                    ON rrbatc.ehrrusrefbooksid = eh.ehrrusrefbooks
                                      WHERE eh.idehr = nc.atc
                                        AND rrbatc.currversion = nc.atcver
                                      LIMIT 1
                                      ) atc ON TRUE
                        WHERE NOT EXISTS
                                  (SELECT *
                                   FROM pharm.t_c_trn c
                                   WHERE c.ctrnid = nc.id
                                  );
                    ELSE
                        -- Generate table Tmp_C_TRN
                        DROP TABLE IF EXISTS tmp_c_trn;
                        CREATE TEMP TABLE tmp_c_trn
                        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]/NameRus[1]/text()', t.x2))[1]::VARCHAR(4000)            AS namerus,
                                   (XPATH('/body[1]/NameEng[1]/text()', t.x2))[1]::VARCHAR(4000)            AS nameeng,
                                   (XPATH('/body[1]/NameLatR[1]/text()', t.x2))[1]::VARCHAR(4000)           AS namelatr,
                                   (XPATH('/body[1]/OtherID[1]/text()', t.x2))[1]::TEXT::INT                AS otherid,
                                   (XPATH('/body[1]/Characteristic[1]/text()', t.x2))[1]::VARCHAR(4000)     AS characteristic,
                                   (XPATH('/body[1]/PharmacologyAction[1]/text()', t.x2))[1]::VARCHAR(4000) AS pharmacologyaction,
                                   (XPATH('/body[1]/Practice[1]/text()', t.x2))[1]::TEXT                    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]/Overdose[1]/text()', t.x2))[1]::VARCHAR(1000)           AS overdose,
                                   (XPATH('/body[1]/InjectionMethod[1]/text()', t.x2))[1]::VARCHAR(255)     AS injectionmethod,
                                   (XPATH('/body[1]/Caution[1]/text()', t.x2))[1]::TEXT                     AS caution,
                                   (XPATH('/body[1]/Composition[1]/text()', t.x2))[1]::VARCHAR(255)         AS composition,
                                   (XPATH('/body[1]/PharmDescription[1]/text()', t.x2))[1]::TEXT            AS pharmdescription,
                                   (XPATH('/body[1]/Pharmacodynamics[1]/text()', t.x2))[1]::TEXT            AS pharmacodynamics,
                                   (XPATH('/body[1]/Indications[1]/text()', t.x2))[1]::VARCHAR(8000)        AS indications,
                                   (XPATH('/body[1]/ExpirationDate[1]/text()', t.x2))[1]::VARCHAR(300)      AS expirationdate,
                                   (XPATH('/body[1]/MNNName[1]/text()', t.x2))[1]::TEXT::VARCHAR(8000)      AS mnnname,
                                   (XPATH('/body[1]/Manufacturer[1]/text()', t.x2))[1]::VARCHAR(3000)       AS manufacturer,
                                   (XPATH('/body[1]/CMnn[1]/text()', t.x2))[1]::TEXT::uuid                  AS cmnn,
                                   (XPATH('/body[1]/Atc[1]/text()', t.x2))[1]::TEXT::INT                    AS atc,
                                   (XPATH('/body[1]/AtcVer[1]/text()', t.x2))[1]::VARCHAR(52)               AS atcver,
                                   (XPATH('/body[1]/ProductType[1]/text()', t.x2))[1]::TEXT::INT            AS producttype,
                                   (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]/EhrRusP08TSH954N[1]/text()', t.x2))[1]::VARCHAR(52)     AS ehrrusp08tsh954n,
                                   (XPATH('/body[1]/EhrRusP08TSH954NVer[1]/text()', t.x2))[1]::VARCHAR(8)   AS ehrrusp08tsh954nver,
                                   (XPATH('/body[1]/EhrRus575VSF88OG[1]/text()', t.x2))[1]::VARCHAR(52)     AS ehrrus575vsf88og,
                                   (XPATH('/body[1]/EhrRus575VSF88OGVer[1]/text()', t.x2))[1]::VARCHAR(8)   AS ehrrus575vsf88ogver
                            FROM (SELECT UNNEST(XPATH('/C_TRN/header', x)) AS x1,
                                         UNNEST(XPATH('/C_TRN/body', x))   AS x2
                                  FROM UNNEST(XPATH('/C_TRN', "Xml"::xml)) x
                                 ) t;
                        
                        IF _operationtype = _delete
                        THEN
                            -- Удаление
                            UPDATE pharm.t_c_trn c
                            SET active = '0'
                            FROM tmp_c_trn nc
                            WHERE nc.id = c.ctrnid;
                        ELSE
                            -- update pharm.T_C_TRN
                            UPDATE pharm.t_c_trn c
                            SET nameeng            = nc.nameeng,
                                namerus            = nc.namerus,
                                namelatr           = nc.namelatr,
                                otherid            = nc.otherid,
                                characteristic     = nc.characteristic,
                                pharmacologyaction = nc.pharmacologyaction,
                                practice           = nc.practice,
                                contraindications  = nc.contraindications,
                                restrictions       = nc.restrictions,
                                useofpregnancy     = nc.useofpregnancy,
                                sideeffects        = nc.sideeffects,
                                interaction        = nc.interaction,
                                overdose           = nc.overdose,
                                injectionmethod    = nc.injectionmethod,
                                caution            = nc.caution,
                                composition        = nc.composition,
                                pharmdescription   = nc.pharmdescription,
                                pharmacodynamics   = nc.pharmacodynamics,
                                indications        = nc.indications,
                                expirationdate     = nc.expirationdate,
                                mnnname            = nc.mnnname,
                                manufacturer       = nc.manufacturer,
                                cmnn               = nc.cmnn,
                                atc                = atc.ehrrusid,
                                producttype        = nc.producttype,
                                regcode            = nc.regcode,
                                fedcode            = nc.fedcode,
                                ehrrusp08tsh954n   = eh1.ehrrusid,
                                ehrrus575vsf88og   = eh2.ehrrusid
                            FROM tmp_c_trn  nc
                            LEFT JOIN LATERAL
                                          (
                                          SELECT eh.ehrrusid
                                          FROM ehr.t_ehr_rus_p08tsh954n     eh
                                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                                          WHERE eh.codeehr = nc.ehrrusp08tsh954n
                                            AND rrb.currversion = nc.ehrrusp08tsh954nver
                                          LIMIT 1
                                          ) eh1 ON TRUE
                            LEFT JOIN LATERAL
                                          (
                                          SELECT eh.ehrrusid
                                          FROM ehr.t_ehr_rus_575vsf88og     eh
                                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                                          WHERE eh.codeehr = nc.ehrrus575vsf88og
                                            AND rrb.currversion = nc.ehrrus575vsf88ogver
                                          LIMIT 1
                                          ) eh2 ON TRUE
                            LEFT JOIN LATERAL
                                          (
                                          SELECT eh.ehrrusid
                                          FROM ehr.t_ehr_rus_m00002         eh
                                          LEFT JOIN ehr.t_ehr_rus_ref_books rrbatc
                                                        ON rrbatc.ehrrusrefbooksid = eh.ehrrusrefbooks
                                          WHERE eh.idehr = nc.atc
                                            AND rrbatc.currversion = nc.atcver
                                          LIMIT 1
                                          ) atc ON TRUE
                            WHERE nc.id = c.ctrnid
                              AND _operationtype = _update;
                            
                            -- insert pharm.T_C_TRN
                            INSERT INTO
                                pharm.t_c_trn
                            (ctrnid,
                             namerus,
                             nameeng,
                             active,
                             otherid,
                             characteristic,
                             pharmacologyaction,
                             practice,
                             contraindications,
                             restrictions,
                             useofpregnancy,
                             sideeffects,
                             interaction,
                             overdose,
                             injectionmethod,
                             caution,
                             composition,
                             pharmdescription,
                             pharmacodynamics,
                             indications,
                             expirationdate,
                             mnnname,
                             manufacturer,
                             cmnn,
                             atc,
                             producttype,
                             regcode,
                             fedcode,
                             ehrrusp08tsh954n,
                             ehrrus575vsf88og,
                             namelatr)
                            
                            SELECT nc.id,
                                   nc.namerus,
                                   nc.nameeng,
                                   nc.active,
                                   nc.otherid,
                                   nc.characteristic,
                                   nc.pharmacologyaction,
                                   nc.practice,
                                   nc.contraindications,
                                   nc.restrictions,
                                   nc.useofpregnancy,
                                   nc.sideeffects,
                                   nc.interaction,
                                   nc.overdose,
                                   nc.injectionmethod,
                                   nc.caution,
                                   nc.composition,
                                   nc.pharmdescription,
                                   nc.pharmacodynamics,
                                   nc.indications,
                                   nc.expirationdate,
                                   nc.mnnname,
                                   nc.manufacturer,
                                   nc.cmnn,
                                   atc.ehrrusid,
                                   nc.producttype,
                                   nc.regcode,
                                   nc.fedcode,
                                   eh1.ehrrusid,
                                   eh2.ehrrusid,
                                   nc.namelatr
                            FROM tmp_c_trn  nc
                            LEFT JOIN LATERAL
                                          (
                                          SELECT eh.ehrrusid
                                          FROM ehr.t_ehr_rus_p08tsh954n     eh
                                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                                          WHERE eh.codeehr = nc.ehrrusp08tsh954n
                                            AND rrb.currversion = nc.ehrrusp08tsh954nver
                                          LIMIT 1
                                          ) eh1 ON TRUE
                            LEFT JOIN LATERAL
                                          (
                                          SELECT eh.ehrrusid
                                          FROM ehr.t_ehr_rus_575vsf88og     eh
                                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = eh.ehrrusrefbooks
                                          WHERE eh.codeehr = nc.ehrrus575vsf88og
                                            AND rrb.currversion = nc.ehrrus575vsf88ogver
                                          LIMIT 1
                                          ) eh2 ON TRUE
                            LEFT JOIN LATERAL
                                          (
                                          SELECT eh.ehrrusid
                                          FROM ehr.t_ehr_rus_m00002         eh
                                          LEFT JOIN ehr.t_ehr_rus_ref_books rrbatc
                                                        ON rrbatc.ehrrusrefbooksid = eh.ehrrusrefbooks
                                          WHERE eh.idehr = nc.atc
                                            AND rrbatc.currversion = nc.atcver
                                          LIMIT 1
                                          ) atc ON TRUE
                            WHERE NOT EXISTS
                                (SELECT*
                                 FROM pharm.t_c_trn c
                                 WHERE c.ctrnid = nc.id
                                )
                              AND _operationtype = _insert;
                        END IF;
                        
                        _entid := (SELECT entityid
                                   FROM vclib.t_entities
                                   WHERE entname = 'C_TRN'
                                   LIMIT 1
                                  );
                        IF EXISTS
                            (SELECT*
                             FROM vclib.t_gen_numbers
                             WHERE entityid = _entid
                               AND numbername = 'PHARM.C_TRN'
                            )
                        THEN
                            ---- генерируем след. номер
                            --perform public.sp_GenerateNumber(@EntId, 'PHARM.C_TRN');
                            
                            UPDATE vclib.t_gen_numbers
                            SET nextnumber = (SELECT MAX(otherid)
                                              FROM pharm.t_c_trn
                                             ) --+ 1
                            WHERE entityid = _entid
                              AND numbername = 'PHARM.C_TRN';
                        ELSE
                            INSERT INTO
                                vclib.t_gen_numbers(gennumbersid, entityid, numbername, nextnumber)
                            VALUES (gen_random_uuid(), _entid, 'PHARM.C_TRN', COALESCE((SELECT MAX(otherid)
                                                                                        FROM pharm.t_c_trn
                                                                                       ), 1));
                        END IF;
                    END IF;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;