-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2016-02-08
-- Description: Выгрузка/загрузка справочника Лекартсвенных форм в(из) RDS
-- =============================================
DROP PROCEDURE IF EXISTS dbo.sp_rds_medicationforms;

DROP PROCEDURE IF EXISTS aps.sp_rds_medicationforms;

CREATE OR REPLACE PROCEDURE aps.sp_rds_medicationforms(
    "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) := 'MEDICATION_FORMS';
        _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_medication_forms;
            CREATE TEMP TABLE temp_medication_forms
            (
                name              VARCHAR(255),
                code              INT,
                active            boolean,
                formtype          INT,
                namelat           VARCHAR(4000),
                fedcode           VARCHAR(50),
                regcode           VARCHAR(50),
                medicationformsid uuid,
                ehrrusvzn431      INT,
                ehrrusvzn431ver   VARCHAR(8),
                packagetype       uuid
            );
            
            _sql := 'insert into Temp_MEDICATION_FORMS
					(
						Name,
						Code,
						Active,
						FormType,
						NameLat,
						FedCode,
						RegCode,
						MedicationFormsID,
						ehr.IdEHR,
						RRB.CurrVersion
						,PackageType
					);'
                        || ' SELECT
						med.Name,
						med.Code,
						med.Active,
						med.FormType,
						med.NameLat,
						med.FedCode,
						med.RegCode,
						med.MedicationFormsID,
						EhrRusVzn431,
						EhrRusVzn431Ver
						,PackageType'
                        || 'FROM dblink(''dbname=' || "PATH"
                        || ''','
                        || ''' SELECT
						mp.Name,
						mp.Code,
						mp.Active,
						mp.FormType,
						mp.NameLat,
						mp.FedCode,
						mp.RegCode,
						mp.MedicationFormsID,
						ehr.IdEHR,
						ehr.CurrVersion
						,mp.PackageType
				FROM PHARM.T_MEDICATION_FORMS mp
				left join lateral
					(
						SELECT er.IdEHR, rrb.CurrVersion
						FROM EHR.T_EHR_RUS_VZN431 er
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
						WHERE er.EhrRusID = mp.EhrRusVzn431
						order by rrb.CurrVersion desc
						limit 1
					) ehr on true
				'') '
                || ' AS med(Name				VARCHAR(255)
					,Code				INT
					,Active				boolean
					,FormType			INT
					,NameLat			VARCHAR(4000)
					,FedCode			VARCHAR(50)
					,RegCode			VARCHAR(50)
					,MedicationFormsID	uuid
					,EhrRusVzn431       INT
					,EhrRusVzn431Ver    VARCHAR(8))
					,PackageType		uuid ';
            
            EXECUTE _sql;
            
            -- Лек. формы не из справочника RDS переводим в неактивные
            UPDATE pharm.t_medication_forms mf
            SET active = '0'
            WHERE NOT EXISTS(SELECT* FROM temp_medication_forms nmf WHERE nmf.medicationformsid = mf.medicationformsid);
            
            -- Обновляем имеющиеся лек. формы
            UPDATE pharm.t_medication_forms mf
            SET name         = nmf.name
              , active       = nmf.active
              , formtype     = COALESCE(nmf.formtype, 0)
              , regcode      = nmf.regcode
              , fedcode      = nmf.fedcode
              , namelat      = nmf.namelat
              , code         = nmf.code
              , ehrrusvzn431 = ehr.ehrrusid
              , packagetype  = nmf.packagetype
            FROM temp_medication_forms nmf
            LEFT JOIN LATERAL
                (
                SELECT er.ehrrusid
                FROM ehr.t_ehr_rus_vzn431 er
                LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE er.idehr = nmf.ehrrusvzn431
                  AND rrb.currversion = nmf.ehrrusvzn431ver
                LIMIT 1
                ) ehr ON TRUE
            WHERE nmf.medicationformsid = mf.medicationformsid;
            
            -- Загружаем новые
            INSERT INTO
                pharm.t_medication_forms
            (medicationformsid, name, active, formtype, regcode, fedcode, namelat, code, ehrrusvzn431, packagetype)
            SELECT nmf.medicationformsid,
                   nmf.name,
                   nmf.active,
                   COALESCE(nmf.formtype, 0),
                   nmf.regcode,
                   nmf.fedcode,
                   nmf.namelat,
                   nmf.code,
                   ehr.ehrrusid,
                   nmf.packagetype
            FROM temp_medication_forms nmf
            LEFT JOIN LATERAL
                (
                SELECT er.ehrrusid
                FROM ehr.t_ehr_rus_vzn431 er
                LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                WHERE er.idehr = nmf.ehrrusvzn431
                  AND rrb.currversion = nmf.ehrrusvzn431ver
                LIMIT 1
                ) ehr ON TRUE
            WHERE NOT EXISTS
                      (SELECT *
                       FROM pharm.t_medication_forms mf
                       WHERE mf.medicationformsid = nmf.medicationformsid
                      );
        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_medication_forms mf WHERE mf.medicationformsid = "ID")
                THEN
                    _operationtype := _update;
                ELSIF "IsNew" = 0 AND
                      NOT EXISTS(SELECT* FROM pharm.t_medication_forms mf WHERE mf.medicationformsid = "ID")
                THEN
                    _operationtype := _delete;
                    _result := XMLELEMENT(NAME "MEDICATION_FORMS",
                                          XMLELEMENT(NAME "header", XMLFOREST(_operationtype AS "TYPE", "ID" AS "ID")));
                END IF;
                
                -- Обработка операции
                IF _operationtype = _ftload
                THEN
                    -- Первичная загрузка
                    _result := XMLELEMENT
                               (
                        NAME "MEDICATION_FORMS"
                        , XMLFOREST
                          (
                            XMLFOREST
                            (
                                _ftload AS "TYPE"
                            ) AS header
                          )
                        , XMLELEMENT
                          (
                            NAME body
                            , XMLELEMENT
                              (
                                NAME "MEDICATION_FORMS"
                                , (SELECT XMLAGG
                                          (
                                              XMLELEMENT
                                              (
                                                  NAME "MEDICATION_FORM"
                                                  , XMLFOREST
                                                    (
                                                      mf.medicationformsid AS "ID"
                                                      , mf.active AS "Active"
                                                      , mf.name AS "Name"
                                                      , mf.formtype AS "FormType"
                                                      , mf.regcode AS "RegCode"
                                                      , mf.fedcode AS "FedCode"
                                                      , mf.namelat AS "NameLat"
                                                      , mf.code AS "Code"
                                                      , er.idehr AS "EhrRusVzn431"
                                                      , rrb.currversion AS "EhrRusVzn431Ver"
                                                      , mf.packagetype AS "PackageType"
                                                    )
                                              )
                                          )
                                   FROM pharm.t_medication_forms mf
                                   LEFT JOIN ehr.t_ehr_rus_vzn431 er ON er.ehrrusid = mf.ehrrusvzn431
                                   LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                                  )
                              )
                          )
                               );
                ELSIF _operationtype <> _delete
                THEN
                    -- собираем MEDICATION_FORMS
                    SELECT XMLELEMENT
                           (
                               NAME "MEDICATION_FORMS"
                               , XMLFOREST
                                 (
                                   XMLFOREST
                                   (
                                       _operationtype AS "TYPE"
                                       , "ID" AS "ID"
                                   ) AS header
                                 )
                               , XMLFOREST
                                 (
                                   XMLFOREST
                                   (
                                       mf.active AS "Active"
                                       , mf.name AS "Name"
                                       , mf.formtype AS "FormType"
                                       , mf.regcode AS "RegCode"
                                       , mf.fedcode AS "FedCode"
                                       , mf.namelat AS "NameLat"
                                       , mf.code AS "Code"
                                       , er.idehr AS "EhrRusVzn431"
                                       , rrb.currversion AS "EhrRusVzn431Ver"
                                       , mf.packagetype AS "PackageType"
                                   ) AS body
                                 )
                           )
                    INTO _result
                    FROM pharm.t_medication_forms mf
                    LEFT JOIN ehr.t_ehr_rus_vzn431 er ON er.ehrrusid = mf.ehrrusvzn431
                    LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                    WHERE mf.medicationformsid = "ID";
                END IF;
                
                -- Отправка
                INSERT INTO
                    aps.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('/MEDICATION_FORMS/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);
                    
                    -- Generate table Tmp_MEDICATION_FORMSS
                    DROP TABLE IF EXISTS tmp_medication_formss;
                    CREATE TEMP TABLE tmp_medication_formss
                    AS
                        SELECT (XPATH('/MEDICATION_FORM/ID[1]/text()', x))[1]::text::uuid              AS id
                             , (XPATH('/MEDICATION_FORM/Active[1]/text()', x))[1]::text::boolean       AS active
                             , (XPATH('/MEDICATION_FORM/Name[1]/text()', x))[1]::varchar(255)          AS name
                             , (XPATH('/MEDICATION_FORM/NameLat[1]/text()', x))[1]::varchar(255)       AS namelat
                             , (XPATH('/MEDICATION_FORM/FormType[1]/text()', x))[1]::text::int         AS formtype
                             , (XPATH('/MEDICATION_FORM/RegCode[1]/text()', x))[1]::varchar(50)        AS regcode
                             , (XPATH('/MEDICATION_FORM/FedCode[1]/text()', x))[1]::varchar(50)        AS fedcode
                             , (XPATH('/MEDICATION_FORM/Code[1]/text()', x))[1]::text::int             AS code
                             , (XPATH('/MEDICATION_FORM/EhrRusVzn431[1]/text()', x))[1]::text::int     AS ehrrusvzn431
                             , (XPATH('/MEDICATION_FORM/EhrRusVzn431Ver[1]/text()', x))[1]::varchar(8) AS ehrrusvzn431ver
                             , (XPATH('/MEDICATION_FORM/PackageType[1]/text()', x))[1]::text::uuid     AS packagetype
                        FROM UNNEST(XPATH('/MEDICATION_FORMS/body/MEDICATION_FORMS/MEDICATION_FORM', "Xml"::xml)) AS x
                        WHERE _operationtype = _ftload;
                    
                    -- Generate table #MEDICATION_FORMS
                    DROP TABLE IF EXISTS tmp_medication_forms;
                    CREATE TEMP TABLE tmp_medication_forms
                    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]/Name[1]/text()', t.x2))[1]::varchar(255)          AS name
                             , (XPATH('/body[1]/NameLat[1]/text()', t.x2))[1]::varchar(255)       AS namelat
                             , (XPATH('/body[1]/FormType[1]/text()', t.x2))[1]::text::int         AS formtype
                             , (XPATH('/body[1]/RegCode[1]/text()', t.x2))[1]::varchar(50)        AS regcode
                             , (XPATH('/body[1]/FedCode[1]/text()', t.x2))[1]::varchar(50)        AS fedcode
                             , (XPATH('/body[1]/Code[1]/text()', t.x2))[1]::text::int             AS code
                             , (XPATH('/body[1]/EhrRusVzn431[1]/text()', t.x2))[1]::text::int     AS ehrrusvzn431
                             , (XPATH('/body[1]/EhrRusVzn431Ver[1]/text()', t.x2))[1]::varchar(8) AS ehrrusvzn431ver
                             , (XPATH('/body[1]/PackageType[1]/text()', t.x2))[1]::text::uuid     AS packagetype
                        FROM (SELECT UNNEST(XPATH('/MEDICATION_FORMS/header', x)) AS x1
                                   , UNNEST(XPATH('/MEDICATION_FORMS/body', x))   AS x2
                              FROM UNNEST(XPATH('/MEDICATION_FORMS', "Xml"::xml)) x
                             ) t
                        WHERE _operationtype <> _ftload;
                    
                    -- Обработка операции
                    IF _operationtype = _ftload
                    THEN
                        
                        UPDATE pharm.t_medication_forms mf
                        SET active = '0'
                        WHERE NOT EXISTS(SELECT* FROM tmp_medication_formss nmf WHERE nmf.id = mf.medicationformsid);
                        
                        -- Обновляем имеющиеся лек. формы
                        UPDATE pharm.t_medication_forms mf
                        SET name         = nmf.name
                          , active       = nmf.active
                          , formtype     = COALESCE(nmf.formtype, 0)
                          , regcode      = nmf.regcode
                          , fedcode      = nmf.fedcode
                          , namelat      = nmf.namelat
                          , code         = nmf.code
                          , ehrrusvzn431 = ehr.ehrrusid
                          , packagetype  = nmf.packagetype
                        FROM tmp_medication_formss nmf
                        LEFT JOIN LATERAL
                            (
                            SELECT er.ehrrusid
                            FROM ehr.t_ehr_rus_vzn431 er
                            LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                            WHERE er.idehr = nmf.ehrrusvzn431
                              AND rrb.currversion = nmf.ehrrusvzn431ver
                            LIMIT 1
                            ) ehr ON TRUE
                        WHERE nmf.id = mf.medicationformsid;
                        
                        -- Загружаем новые
                        INSERT INTO
                            pharm.t_medication_forms
                        (medicationformsid,
                         name,
                         active,
                         formtype,
                         regcode,
                         fedcode,
                         namelat,
                         code,
                         ehrrusvzn431,
                         packagetype)
                        SELECT nmf.id,
                               nmf.name,
                               nmf.active,
                               COALESCE(nmf.formtype, 0),
                               nmf.regcode,
                               nmf.fedcode,
                               nmf.namelat,
                               nmf.code,
                               ehr.ehrrusid,
                               nmf.packagetype
                        FROM tmp_medication_formss nmf
                        LEFT JOIN LATERAL
                            (
                            SELECT er.ehrrusid
                            FROM ehr.t_ehr_rus_vzn431 er
                            LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                            WHERE er.idehr = nmf.ehrrusvzn431
                              AND rrb.currversion = nmf.ehrrusvzn431ver
                            LIMIT 1
                            ) ehr ON TRUE
                        WHERE NOT EXISTS
                                  (SELECT *
                                   FROM pharm.t_medication_forms mf
                                   WHERE mf.medicationformsid = nmf.id
                                  );
                    ELSIF _operationtype = _delete
                    THEN
                        -- Удаление
                        UPDATE pharm.t_medication_forms mf
                        SET active = '0'
                        FROM tmp_medication_forms nmf
                        WHERE nmf.id = mf.medicationformsid;
                    ELSE
                        -- update pharm.T_MEDICATION_FORMS
                        UPDATE pharm.t_medication_forms mf
                        SET name         = nmf.name
                          , active       = nmf.active
                          , formtype     = COALESCE(nmf.formtype, 0)
                          , regcode      = nmf.regcode
                          , fedcode      = nmf.fedcode
                          , namelat      = nmf.namelat
                          , code         = nmf.code
                          , ehrrusvzn431 = ehr.ehrrusid
                          , packagetype  = nmf.packagetype
                        FROM tmp_medication_forms nmf
                        LEFT JOIN LATERAL
                            (
                            SELECT er.ehrrusid
                            FROM ehr.t_ehr_rus_vzn431 er
                            LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                            WHERE er.idehr = nmf.ehrrusvzn431
                              AND rrb.currversion = nmf.ehrrusvzn431ver
                            LIMIT 1
                            ) ehr ON TRUE
                        WHERE nmf.id = mf.medicationformsid
                          AND _operationtype = _update;
                        
                        -- insert pharm.T_MEDICATION_FORMS
                        INSERT INTO
                            pharm.t_medication_forms
                        (medicationformsid,
                         name,
                         active,
                         formtype,
                         regcode,
                         fedcode,
                         namelat,
                         code,
                         ehrrusvzn431,
                         packagetype)
                        SELECT nmf.id,
                               nmf.name,
                               nmf.active,
                               COALESCE(nmf.formtype, 0),
                               nmf.regcode,
                               nmf.fedcode,
                               nmf.namelat,
                               nmf.code,
                               ehr.ehrrusid,
                               nmf.packagetype
                        FROM tmp_medication_forms nmf
                        LEFT JOIN LATERAL
                            (
                            SELECT er.ehrrusid
                            FROM ehr.t_ehr_rus_vzn431 er
                            LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = er.ehrrusrefbooks
                            WHERE er.idehr = nmf.ehrrusvzn431
                              AND rrb.currversion = nmf.ehrrusvzn431ver
                            LIMIT 1
                            ) ehr ON TRUE
                        WHERE NOT EXISTS
                            (SELECT *
                             FROM pharm.t_medication_forms mf
                             WHERE mf.medicationformsid = nmf.id
                            )
                          AND _operationtype = _insert;
                    END IF;
                    
                    _entid := (SELECT entityid
                               FROM vclib.t_entities
                               WHERE entname = 'MEDICATION_FORMS'
                                 AND schema = 'PHARM'
                               LIMIT 1
                              );
                    IF EXISTS
                        (SELECT*
                         FROM vclib.t_gen_numbers
                         WHERE entityid = _entid
                           AND numbername = 'PHARM.MEDICATION_FORMS'
                         LIMIT 1
                        )
                    THEN
                        UPDATE vclib.t_gen_numbers
                        SET nextnumber = COALESCE((SELECT MAX(code) FROM pharm.t_medication_forms), 1) --+ 1
                        WHERE entityid = _entid
                          AND numbername = 'PHARM.MEDICATION_FORMS';
                    ELSE
                        INSERT INTO
                            vclib.t_gen_numbers
                            (gennumbersid, entityid, numbername, nextnumber)
                        VALUES (gen_random_uuid(),
                                _entid,
                                'PHARM.MEDICATION_FORMS',
                                COALESCE((SELECT MAX(code) FROM pharm.t_medication_forms), 1));
                    END IF;
                    
                    DROP TABLE IF EXISTS tmp_medication_forms;
                    DROP TABLE IF EXISTS tmp_medication_formss;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;