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

DROP PROCEDURE IF EXISTS aps.sp_rds_cmnnclassification;

CREATE OR REPLACE PROCEDURE aps.sp_rds_cmnnclassification(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 1,
    "ID" uuid DEFAULT NULL::uuid,
    "QueueName" character varying DEFAULT 'RDSSync'::character varying,
    "dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone,
    "User" uuid DEFAULT NULL::uuid,
    "PATH" character varying DEFAULT NULL::character varying
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    
    DECLARE
        _result          text;
        _contentencoding varchar(255) := 'utf-8';
        _type            varchar(255) := 'C_MNN_CLASSIFICATION';
        _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;
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        
        IF "PATH" IS NOT NULL
        THEN
            DROP TABLE IF EXISTS temp_c_mnn_for_drug_classification_link;
            CREATE TEMP TABLE temp_c_mnn_for_drug_classification_link
            (
                cmnnfordrugclassificationlinkid uuid,
                cmnn                            uuid,
                groupsofdrugsclassification     int
            );
            _sql := 'INSERT INTO Temp_C_MNN_FOR_DRUG_CLASSIFICATION_LINK(
							 CMnnForDrugClassificationLinkID
							,CMnn
							,GroupsOfDrugsClassification)
							SELECT CMnnForDrugClassificationLinkID
							,CMnn
							,GroupsOfDrugsClassification
							FROM dblink(''dbname=' || "PATH"
                || ''',
							SELECT CMnnForDrugClassificationLinkID
							,CMnn
							,GroupsOfDrugsClassification
						FROM PHARM.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK '') AS tt(
							CMnnForDrugClassificationLinkID		uuid
							,CMnn									uuid
							,GroupsOfDrugsClassification			int)';
            EXECUTE _sql;
            
            -- Первичная загрузка
            
            DELETE
            FROM pharm.t_c_mnn_for_drug_classification_link mdc
            WHERE NOT EXISTS
                      (SELECT *
                       FROM temp_c_mnn_for_drug_classification_link mc
                       WHERE mdc.cmnn = mc.cmnn
                         AND mdc.groupsofdrugsclassification = mc.groupsofdrugsclassification
                      );
            
            -- Загружаем новые
            INSERT INTO
                pharm.t_c_mnn_for_drug_classification_link
                (cmnnfordrugclassificationlinkid, cmnn, groupsofdrugsclassification)
            SELECT mc.cmnnfordrugclassificationlinkid,
                   mc.cmnn,
                   mc.groupsofdrugsclassification
            FROM temp_c_mnn_for_drug_classification_link mc
            WHERE NOT EXISTS
                      (SELECT 1
                       FROM pharm.t_c_mnn_for_drug_classification_link mdc
                       WHERE mdc.cmnn = mc.cmnn
                         AND mdc.groupsofdrugsclassification = mc.groupsofdrugsclassification
                      );
        ELSE
            IF "Xml" IS NULL
            THEN
                --выясняем тип операции
                IF "ID" IS NULL
                THEN
                    _operationtype = _ftload;
                ELSIF "IsNew" = 1
                THEN
                    _operationtype = _insert;
                ELSIF "IsNew" = 0 AND EXISTS (SELECT *
                                              FROM pharm.t_c_mnn_for_drug_classification_link mc
                                              WHERE mc.cmnnfordrugclassificationlinkid = "ID"
                                             )
                THEN
                    _operationtype = _update;
                ELSIF "IsNew" = 0 AND NOT EXISTS (SELECT *
                                                  FROM pharm.t_c_mnn_for_drug_classification_link mc
                                                  WHERE mc.cmnnfordrugclassificationlinkid = "ID"
                                                 )
                THEN
                    _operationtype = _delete;
                    _result := XMLELEMENT
                               (
                        NAME "C_MNN_CLASSIFICATION",
                        XMLELEMENT
                        (
                            NAME "header",
                            XMLFOREST
                            (
                                _operationtype AS "TYPE",
                                "ID" AS "ID"
                            )
                        )
                               );
                END IF;
                -- Обработка операции
                IF _operationtype = _ftload
                THEN
                    -- Первичная загрузка
                    _result := XMLELEMENT
                               (
                        NAME "C_MNN_CLASSIFICATION",
                        XMLELEMENT
                        (
                            NAME "header",
                            XMLFOREST
                            (
                                _ftload AS "TYPE"
                            )
                        ),
                        XMLELEMENT
                        (
                            NAME "body",
                            XMLELEMENT
                            (
                                NAME "C_MNN_CLASSIFICATIONS",
                                (SELECT XMLAGG
                                        (
                                            XMLELEMENT
                                            (
                                                NAME "C_MNN_CLASSIFICATION",
                                                XMLFOREST
                                                (
                                                    mc.cmnnfordrugclassificationlinkid AS "ID",
                                                    mc.cmnn AS "Cmnn",
                                                    mc.groupsofdrugsclassification AS
                                                    "Classification"
                                                )
                                            )
                                        )
                                 FROM pharm.t_c_mnn_for_drug_classification_link mc
                                 INNER JOIN
                                 pharm.t_c_mnn                                   m
                                     ON m.cmnnid = mc.cmnn
                                 WHERE m.usedin = 1
                                )
                            )
                        )
                               );
                ELSIF _operationtype <> _delete
                THEN
                    -- собираем C_MNN_CLASSIFICATION
                    SELECT XMLELEMENT
                           (
                               NAME "C_MNN_CLASSIFICATION",
                               XMLELEMENT
                               (
                                   NAME "header",
                                   XMLFOREST
                                   (
                                       _operationtype AS "TYPE",
                                       "ID" AS "ID"
                                   )
                               ),
                               XMLELEMENT
                               (
                                   NAME "body",
                                   XMLFOREST
                                   (
                                       mc.cmnn AS "Cmnn",
                                       mc.groupsofdrugsclassification AS "Classification"
                                   )
                               )
                           )
                    INTO _result
                    FROM pharm.t_c_mnn_for_drug_classification_link mc
                    WHERE mc.cmnnfordrugclassificationlinkid = "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",
                       _result,
                       NULL,
                       NULL,
                       _contentencoding,
                       _type,
                       "User",
                       0,
                       "dateTime",
                       _ownerid;
            ELSE
                IF "Xml" IS NOT NULL
                THEN
                    -- Получаем тип операции
                    _operationtype = NULLIF(
                        TRIM(XPATH('/C_MNN_CLASSIFICATION//header[1]/TYPE[1]/text()', "Xml"::xml)::text, '{}'),
                        '')::varchar(6);
                    
                    
                    -- Обработка операции
                    IF _operationtype = _ftload
                    THEN
                        -- Generate table Tmp_C_MNN_CLASSIFICATIONS
                        DROP TABLE IF EXISTS tmp_c_mnn_classifications;
                        CREATE TEMP TABLE tmp_c_mnn_classifications
                        AS
                            SELECT NULLIF(TRIM(XPATH('/C_MNN_CLASSIFICATION/ID[1]/text()', x)::text, '{}'),
                                          '')::uuid AS id,
                                   NULLIF(TRIM(XPATH('/C_MNN_CLASSIFICATION/Cmnn[1]/text()', x)::text, '{}'),
                                          '')::uuid AS cmnn,
                                   NULLIF(TRIM(XPATH('/C_MNN_CLASSIFICATION/Classification[1]/text()', x)::text, '{}'),
                                          '')::int  AS classification
                            FROM (SELECT UNNEST(XPATH(
                                '/C_MNN_CLASSIFICATION/body/C_MNN_CLASSIFICATIONS/C_MNN_CLASSIFICATION', x)) AS x
                                  FROM (SELECT "Xml"::xml AS x) root
                                 ) c_mnn_classification;
                        
                        -- Первичная загрузка
                        -- Удаляем несуществующие связки
                        DELETE
                        FROM pharm.t_c_mnn_for_drug_classification_link mdc
                        WHERE NOT EXISTS
                                  (SELECT *
                                   FROM tmp_c_mnn_classifications mc
                                   WHERE mdc.cmnn = mc.cmnn
                                     AND mdc.groupsofdrugsclassification = mc.classification
                                  );
                        -- Загружаем новые
                        INSERT INTO
                            pharm.t_c_mnn_for_drug_classification_link(cmnnfordrugclassificationlinkid, cmnn, groupsofdrugsclassification)
                        SELECT mc.id, mc.cmnn, mc.classification
                        FROM tmp_c_mnn_classifications mc
                        WHERE NOT EXISTS
                                  (SELECT *
                                   FROM pharm.t_c_mnn_for_drug_classification_link mdc
                                   WHERE mdc.cmnn = mc.cmnn
                                     AND mdc.groupsofdrugsclassification = mc.classification
                                  );
                    ELSE
                        -- Generate table Tmp_C_MNN_CLASSIFICATION
                        DROP TABLE IF EXISTS tmp_c_mnn_classification;
                        CREATE TEMP TABLE tmp_c_mnn_classification
                        AS
                            SELECT NULLIF(
                                       TRIM(XPATH('/C_MNN_CLASSIFICATION/header[1]/ID[1]/text()', "Xml"::xml)::text,
                                            '{}'), '')::uuid                            AS id,
                                   NULLIF(
                                       TRIM(XPATH('/C_MNN_CLASSIFICATION/body[1]/Cmnn[1]/text()', "Xml"::xml)::text,
                                            '{}'), '')::uuid                            AS cmnn,
                                   NULLIF(TRIM(XPATH('/C_MNN_CLASSIFICATION/body[1]/Classification[1]/text()',
                                                     "Xml"::xml)::text, '{}'), '')::int AS classification;
                        
                        IF _operationtype = _delete
                        THEN
                            -- Удаление
                            DELETE
                            FROM pharm.t_c_mnn_for_drug_classification_link mc
                            WHERE mc.cmnnfordrugclassificationlinkid IN
                                  (SELECT nmc.id
                                   FROM tmp_c_mnn_classification nmc
                                  );
                        ELSE
                            -- update T_C_MNN_CLASSIFICATION
                            UPDATE pharm.t_c_mnn_for_drug_classification_link mc
                            SET cmnn                        = nmc.cmnn,
                                groupsofdrugsclassification = nmc.classification
                            FROM tmp_c_mnn_classification nmc
                            WHERE nmc.id = mc.cmnnfordrugclassificationlinkid
                              AND _operationtype = _update;
                            
                            -- insert T_C_MNN_CLASSIFICATION
                            INSERT INTO
                                pharm.t_c_mnn_for_drug_classification_link(cmnnfordrugclassificationlinkid, cmnn, groupsofdrugsclassification)
                            SELECT nmc.id, nmc.cmnn, nmc.classification
                            FROM tmp_c_mnn_classification nmc
                            WHERE NOT EXISTS
                                (SELECT *
                                 FROM pharm.t_c_mnn_for_drug_classification_link mc
                                 WHERE mc.cmnn = nmc.cmnn
                                   AND mc.groupsofdrugsclassification = nmc.classification
                                )
                              AND _operationtype = _insert;
                        END IF;
                    END IF;
                END IF;
            END IF;
        END IF;
    END;
END;
$$;