DROP PROCEDURE IF EXISTS sp_getsetgroupsofdrugsclassification;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-24
-- Description: Выгрузка/загрузка справочника GROUPS_OF_DRUGS_CLASSIFICATION
-- =============================================
CREATE PROCEDURE sp_getsetgroupsofdrugsclassification(
    "Xml" text DEFAULT NULL,
    "IsNew" integer = 1,
    "ID" uuid DEFAULT NULL::uuid,
    "QueueName" character varying DEFAULT NULL::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) := 'GROUPS_OF_DRUGS_CLASSIFICATION';
        _operationtype   varchar(6);
        _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_groups_of_drugs_classification c WHERE c.code = "ID"))
            THEN
                _operationtype = 'Update';
            ELSIF ("IsNew" = 0 AND
                   NOT EXISTS (SELECT * FROM pharm.t_groups_of_drugs_classification c WHERE c.code = "ID"))
            THEN
                _operationtype = 'Delete';
                SELECT XMLELEMENT
                       (
                           NAME GROUPS_OF_DRUGS_CLASSIFICATION,
                           XMLFOREST
                           (
                               _operationtype AS "TYPE",
                               "ID" AS "Code"
                           )
                       )
                INTO _result;
            END IF;
            
            IF (_operationtype = 'FTLoad')
            THEN
                
                SELECT '<GROUP_OF_DRUGS_CLASSIFICATION><header><TYPE>' || 'FTLoad' ||
                       '</TYPE></header><body><GROUPS_OF_DRUGS_CLASSIFICATION>' ||
                       COALESCE(STRING_AGG(
                                    XMLSERIALIZE(CONTENT tt.xml_elem AS TEXT),
                                    ''), '')
                           ||
                       '</GROUPS_OF_DRUGS_CLASSIFICATION></body></GROUP_OF_DRUGS_CLASSIFICATION>'
                INTO _result
                FROM (SELECT XMLELEMENT(
                                 NAME "GROUP_OF_DRUGS_CLASSIFICATION",
                                 XMLFOREST(
                                     c.code AS "Code",
                                     c.name AS "Name"
                                 )
                             ) AS xml_elem
                      FROM pharm.t_groups_of_drugs_classification c
                     ) tt;
            
            ELSIF (_operationtype = 'Delete')
            THEN
                SELECT XMLELEMENT
                       (
                           NAME "GROUPS_OF_DRUGS_CLASSIFICATION",
                           XMLELEMENT
                           (
                               NAME header,
                               XMLFOREST
                               (
                                   _operationtype AS "TYPE",
                                   c.code AS "Code"
                               )
                           )
                       )
                INTO _result
                FROM pharm.t_groups_of_drugs_classification c;
            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,>)
                   gen_random_uuid()--,<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, если не указана очередь
        
        END IF; --xml is null
        IF ("Xml" IS NULL)
        THEN
            -- Получаем тип операции
            _operationtype :=
                (XPATH('/GROUPS_OF_DRUGS_CLASSIFICATION/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);
            _ownerid := (XPATH('/GROUPS_OF_DRUGS_CLASSIFICATION/header[1]/ID[1]/text()', "Xml"::xml))[1]::text::uuid;
            _result := "Xml";
            
            -- Generate table #GROUPS_OF_DRUGS_CLASSIFICATIONS
            DROP TABLE IF EXISTS tmp_groups_of_drugs_classifications;
            CREATE TABLE tmp_groups_of_drugs_classifications
            AS
                SELECT (XPATH('/GROUP_OF_DRUGS_CLASSIFICATION/Code[1]/text()', x))[1]::text::int    AS code,
                       (XPATH('/GROUP_OF_DRUGS_CLASSIFICATION/Name[1]/text()', x))[1]::varchar(255) AS name
                FROM UNNEST(XPATH('/GROUPS_OF_DRUGS_CLASSIFICATION/body/GROUP_OF_DRUGS_CLASSIFICATION', "Xml"::xml)) x
                WHERE _operationtype = 'FTLoad';
            
            -- Generate table #GROUPS_OF_DRUGS_CLASSIFICATION
            DROP TABLE IF EXISTS tmp_groups_of_drugs_classification;
            CREATE TABLE tmp_groups_of_drugs_classification
            AS
                SELECT (XPATH('/header[1]/Code[1]/text()', t.x1))[1]::text::int  AS code,
                       (XPATH('/body[1]/Name[1]/text()', t.x2))[1]::varchar(255) AS name
                FROM (SELECT UNNEST(XPATH('/GROUPS_OF_DRUGS_CLASSIFICATION/header', x)) AS x1,
                             UNNEST(XPATH('/GROUPS_OF_DRUGS_CLASSIFICATION/body', x))   AS x2
                      FROM UNNEST(XPATH('/GROUPS_OF_DRUGS_CLASSIFICATION', "Xml"::xml)) x
                      WHERE _operationtype <> 'FTLoad'
                     ) t;
            
            IF (_operationtype = 'FTLoad')
            THEN
                UPDATE pharm.t_groups_of_drugs_classification c
                SET name = nc.name
                FROM tmp_groups_of_drugs_classifications nc
                WHERE nc.code = c.code;
                
                INSERT INTO pharm.t_groups_of_drugs_classification (code, name)
                SELECT nc.code,
                       nc.name
                FROM tmp_groups_of_drugs_classifications nc
                WHERE NOT EXISTS
                          (SELECT *
                           FROM pharm.t_groups_of_drugs_classification c
                           WHERE c.code = nc.code
                          );
            ELSIF (_operationtype = 'Delete')
            THEN
                DELETE
                FROM pharm.t_groups_of_drugs_classification c
                    USING tmp_groups_of_drugs_classification nc
                WHERE nc.code = c.code;
            ELSE
                UPDATE pharm.t_groups_of_drugs_classification c
                SET name = nc.name
                FROM tmp_groups_of_drugs_classification nc
                WHERE nc.code = c.code;
                
                INSERT INTO pharm.t_groups_of_drugs_classification (code, name)
                SELECT nc.code,
                       nc.name
                FROM tmp_groups_of_drugs_classification nc
                WHERE NOT EXISTS
                          (SELECT *
                           FROM pharm.t_groups_of_drugs_classification c
                           WHERE c.code = nc.code
                          );
            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,>)
                   gen_random_uuid()--,<OwnerID, varchar(255),>
            FROM rds.t_sync_servers s
            WHERE s.servername <> "QueueName"
              AND _operationtype <> 'FTLoad';
        
        END IF;
    END;
END;
$$