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

DROP PROCEDURE IF EXISTS sp_rds_medicationforms;

CREATE OR REPLACE PROCEDURE 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
                    -- Первичная загрузка
                    SELECT '<MEDICATION_FORM><header><TYPE>' || _ftload || '</TYPE></header><body><MEDICATION_FORMS>' ||
                           COALESCE(STRING_AGG(
                                        XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                        ''), '')
                               ||
                           '</MEDICATION_FORMS></body></MEDICATION_FORM>'
                    INTO _result
                    FROM (SELECT XMLELEMENT(
                                     NAME "MEDICATION_FORM",
                                     XMLFOREST(
                                         mf.medicationformsid AS "ID",
                                         mf.code AS "Code",
                                         mf.active AS "Active",
                                         mf.name AS "Name",
                                         mf.formtype AS "FormType",
                                         mf.regcode AS "RegCode",
                                         mf.fedcode AS "FedCode",
                                         mf.namelat AS "NameLat",
                                         ehr.idehr AS "EhrRusVzn431",
                                         rrb.currversion AS "EhrRusVzn431Ver",
                                         mf.packagetype AS "PackageType"
                                     )
                                 ) AS xml_elem
                          FROM pharm.t_medication_forms     mf
                          LEFT JOIN ehr.t_ehr_rus_vzn431    ehr ON ehr.ehrrusid = mf.ehrrusvzn431
                          LEFT JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = ehr.ehrrusrefbooks
                         ) tt;
                
                ELSIF _operationtype <> _delete
                THEN
                    -- собираем MEDICATION_FORMS
                    SELECT XMLELEMENT
                           (
                               NAME "MEDICATION_FORMS",
                               XMLELEMENT
                               (
                                   NAME "header",
                                   XMLFOREST
                                   (
                                       _operationtype AS "TYPE",
                                       "ID" AS "ID"
                                   )
                               ),
                               XMLELEMENT
                               (
                                   NAME "body",
                                   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"
                                   )
                               )
                           )
                    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
                    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('/MEDICATION_FORMS/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(6);
                    
                    
                    -- Обработка операции
                    IF _operationtype = _ftload
                    THEN
                        -- 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;
                        
                        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
                                  );
                    ELSE
                        -- 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;
                        
                        IF _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;
                    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'
                        )
                    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;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;