drop procedure if exists sp_GetSetCmnnClassification;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024-06-24
-- Description: Выгрузка/загрузка классификации Международных непатентованных наименований (МНН) в(из) RDS
-- =============================================
CREATE PROCEDURE sp_GetSetCmnnClassification
(
     "Xml" xml DEFAULT NULL::xml
    ,"IsNew" boolean DEFAULT '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
)
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';       
            end if;
            elsif ("IsNew" = '1')
            then
                _OperationType := 'Insert';
            end if;
            elsif ("IsNew" = '0' and exists (select * from PHARM.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc where mc.CMnnForDrugClassificationLinkID = "ID"))
            then 
                _OperationType := 'Update'
            end if;
            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
                xmlelement
                (
                    name "C_MNN_CLASSIFICATION",
                    xmlelement
                    (
                        name header,
                        xmlforest ('FTLoad' as "TYPE")
                    ),
                    xmlelement
                    (
                        name body,
                        xmlelement
                        (
                            name "C_MNN_CLASSIFICATIONS",
                            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;
            end if;
            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"
                            )
                        )
                    )
                )
                from PHARM.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
                into _Result
                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 
                uuid_generate_v4()--(<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, если не указана очередь
        end if; -- if xml is null
        elsif ("Xml" is not null)
        then
            -- Получаем тип операции
            _OperationType= nullif(trim(xpath('/C_MNN_CLASSIFICATION//header[1]/TYPE[1]/text()',"Xml")::text,'{}'),'')::varchar(6);
            _OwnerID = nullif(trim(xpath('/C_MNN_CLASSIFICATION//header[1]/ID[1]/text()',"Xml")::text,'{}'),'')::uuid;
            _Result := cast("Xml" as text);
            
            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" as x
				) root
			) C_MNN_CLASSIFICATION
			where _operationType= 'FTLoad';

            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")::text,'{}'),'')::UUID           as ID
			    ,nullif(trim(xpath('/C_MNN_CLASSIFICATION/body[1]/Cmnn[1]/text()',"Xml")::text,'{}'),'')::UUID          as Cmnn
			    ,nullif(trim(xpath('/C_MNN_CLASSIFICATION/body[1]/Classification[1]/text()',"Xml")::text,'{}'),'')::int as Classification
            select 
                t.c.value('./header[1]/ID[1]', 'uniqueidentifier')      as [ID]
                ,t.c.value('./body[1]/Cmnn[1]', 'uniqueidentifier')     as [Cmnn]
                ,t.c.value('./body[1]/Classification[1]', 'int')        as [Classification]
            where _operationType<> 'FTLoad';

            if (_OperationType = 'FTLoad')
            then
                -- Первичная загрузка
                -- Удаляем несуществующие связки
                delete from PHARM.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mdc
                where not exists
                (
                    select * from Tmp_C_MNN_CLASSIFICATION 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
                );
            end if;
            elsif (_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;
            end if;
            else
            then
                update PHARM.T_C_MNN_FOR_DRUG_CLASSIFICATION_LINK mc
                set 
                    mc.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 uuid_generate_v4()--(<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';
             -- Drop tables
             DROP TABLE if exists Tmp_C_MNN_CLASSIFICATION;
             DROP TABLE if exists Tmp_C_MNN_CLASSIFICATIONS;
        end if;
    End;
End;
$$;