DROP PROCEDURE IF EXISTS sp_getsetcmnnclassification;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-24
-- Description: Выгрузка/загрузка классификации Международных непатентованных наименований (МНН) в(из) RDS
-- =============================================
CREATE PROCEDURE sp_getsetcmnnclassification(
    "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
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    DECLARE
        _result          text;
        _contentencoding varchar(255) := 'utf-8';
        _type            varchar(255) := 'C_MNN_CLASSIFICATION';
        _operationtype   varchar(255);
        _ownerid         uuid         := "ID";
    BEGIN
        "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
        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';
                SELECT XMLELEMENT
                       (
                           NAME "C_MNN_CLASSIFICATION",
                           XMLFOREST
                           (
                               _operationtype AS "TYPE",
                               "ID" AS "ID"
                           )
                       )
                INTO _result;
            END IF;
            
            -- Обработка операции
            IF (_operationtype = 'FTLoad')
            THEN
                
                SELECT '<C_MNN_CLASSIFICATION><header><TYPE>' || 'FTLoad' ||
                       '</TYPE></header><body><C_MNN_CLASSIFICATIONS>' ||
                       COALESCE(STRING_AGG(
                                    XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                    ''), '')
                           ||
                       '</C_MNN_CLASSIFICATIONS></body></C_MNN_CLASSIFICATION>'
                INTO _result
                FROM (SELECT XMLELEMENT(
                                 NAME "C_MNN_CLASSIFICATION",
                                 XMLFOREST(
                                     mc.cmnnfordrugclassificationlinkid AS "ID",
                                     mc.cmnn AS "Cmnn",
                                     mc.groupsofdrugsclassification AS "Classification"
                                 )
                             ) AS xml_elem
                      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
                     ) tt;
            
            ELSIF (_operationtype <> 'Delete')
            THEN
                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
                rds.t_rabbit_send_object
            (rabbitsendobjectid,
             queuename,
             content,
             messageid,
             headers,
             contentencoding,
             type,
             userid,
             state,
             createdatetime,
             ownerid)
            SELECT gen_random_uuid(),--(<RabbitSendObjectID, uniqueidentifier,>
                   s.servername,--,<QueueName, varchar(255),>
                   converttexttobinaryzip(_result, ''),--,<Content, text,>
                   NULL,--,<MessageID, varchar(255),>
                   NULL,--,<Headers, varchar(255),>
                   _contentencoding,--,<ContentEncoding, varchar(255),>
                   _type,--,<Type, varchar(255),>
                   "User",--,<UserID, varchar(255),>
                   0,--,<State, int,>)
                   "dateTime",--,<CreateDateTime, datetime,>)
                   _ownerid--,<OwnerID, varchar(255),>
            FROM rds.t_sync_servers s
            WHERE ("QueueName" IS NOT NULL AND s.servername = "QueueName")
               OR ("QueueName" IS NULL AND s.servertype IN (0, 1)); -- Рассылаем пакет всем подписанным серверам AKUZ-а и APS, если не указана очередь
        ELSIF ("Xml" IS NOT NULL)
        THEN
            -- Получаем тип операции
            _operationtype =
                NULLIF(TRIM(XPATH('/C_MNN_CLASSIFICATION//header[1]/TYPE[1]/text()', "Xml"::xml)::text, '{}'),
                       '')::varchar(6);
            _ownerid =
                NULLIF(TRIM(XPATH('/C_MNN_CLASSIFICATION//header[1]/ID[1]/text()', "Xml"::xml)::text, '{}'), '')::uuid;
            _result := "Xml";
            
            
            IF (_operationtype = 'FTLoad')
            THEN
                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;
                
                ANALYZE tmp_c_mnn_classifications;
                
                -- Первичная загрузка
                -- Удаляем несуществующие связки
                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
                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
                        USING tmp_c_mnn_classification nmc
                    WHERE nmc.id = mc.cmnnfordrugclassificationlinkid;
                ELSE
                    UPDATE pharm.t_c_mnn_for_drug_classification_link mc
                    SET cmnn = nmc.cmnn
                    FROM tmp_c_mnn_classification nmc
                    WHERE nmc.id = mc.cmnnfordrugclassificationlinkid
                      AND _operationtype = 'Update';
                    
                    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 _operationtype = 'Insert'
                      AND NOT EXISTS
                        (SELECT *
                         FROM pharm.t_c_mnn_for_drug_classification_link mc
                         WHERE mc.cmnn = nmc.cmnn
                           AND mc.groupsofdrugsclassification = nmc.classification
                        );
                END IF;
                
                -- Отправка всем остальным подписанным серверам AKUZ-а и APS
                INSERT INTO
                    rds.t_rabbit_send_object
                (rabbitsendobjectid,
                 queuename,
                 content,
                 messageid,
                 headers,
                 contentencoding,
                 type,
                 userid,
                 state,
                 createdatetime,
                 ownerid)
                SELECT gen_random_uuid(),--(<RabbitSendObjectID, uniqueidentifier,>
                       s.servername,--,<QueueName, varchar(255),>
                       converttexttobinaryzip(_result, ''),--,<Content, text,>
                       NULL,--,<MessageID, varchar(255),>
                       NULL,--,<Headers, varchar(255),>
                       _contentencoding,--,<ContentEncoding, varchar(255),>
                       _type,--,<Type, varchar(255),>
                       "User",--,<UserID, varchar(255),>
                       0,--,<State, int,>)
                       "dateTime",--,<CreateDateTime, datetime,>)
                       _ownerid--,<OwnerID, varchar(255),>
                FROM rds.t_sync_servers s
                WHERE s.servername <> "QueueName"
                  AND _operationtype <> 'FTLoad';
            
            END IF;
        END IF;
    END;
END;
$$;