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" boolean DEFAULT '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
)
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';
			end if;
			elsif ("IsNew" = '1')
			then
				_OperationType := 'Insert';
			end if;
			elsif ("IsNew" = '0' and exists (select * from PHARM.T_GROUPS_OF_DRUGS_CLASSIFICATION c where c.Code = "ID"))
			then
			    _OperationType = 'Update';
			end if;
			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 
                xmlelement
                (
                    name "GROUPS_OF_DRUGS_CLASSIFICATION",
                    xmlelement
                    (
                        name header,
                        xmlforest ('FTLoad' as "TYPE")
                    ),
                    xmlelement
                    (
                        name body,
                        xmlelement
                        (
                            name "GROUP_OF_DRUGS_CLASSIFICATION",
                            xmlforest
                            (
                                 c.Code	as 'Code'
                                ,c.Name	as 'Name'
                            )
                        )
                    )
                )
                into _Result
                from PHARM.T_GROUPS_OF_DRUGS_CLASSIFICATION c;
            end if;
            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 
                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,>)
                ,uuid_generate_v4()--,<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 c.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
                );
            end if;
            elsif (_OperationType = 'Delete')
            then 
                delete PHARM.T_GROUPS_OF_DRUGS_CLASSIFICATION c
                using Tmp_GROUPS_OF_DRUGS_CLASSIFICATION nc
                where nc.Code = c.Code;
            end if;
            else
            then
                update PHARM.T_GROUPS_OF_DRUGS_CLASSIFICATION c
                set c.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 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,>)
              ,uuid_generate_v4()--,<OwnerID, varchar(255),>
        from RDS.T_SYNC_SERVERS s 
        where s.ServerName <> "QueueName" and _OperationType <> 'FTLoad';

        -- Drop tables
        DROP TABLE if exists Tmp_GROUPS_OF_DRUGS_CLASSIFICATION;
        DROP TABLE if exists Tmp_GROUPS_OF_DRUGS_CLASSIFICATIONS;

        end if;
    end;
end;
$$