drop procedure if exists sp_GetSetPackageType;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника Вид упаковки в(из) RDS
-- =============================================
CREATE PROCEDURE sp_GetSetPackageType
(
    "Xml" xml DEFAULT NULL::xml
    ,"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
)
    language plpgsql
as
$$
Begin
    declare
         _Result text;
        ,_ContentEncoding varchar(255) := 'utf-8';
        ,_Type varchar(255) := 'PACKAGE_TYPE';
        ,_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_PACKAGE_TYPE where PackageTypeID = "ID"))
		    then
		    	_OperationType = 'Update';
		    end if;
		    elsif ("IsNew" = '0' and not exists (select * from PHARM.T_PACKAGE_TYPE where PackageTypeID = "ID"))
            then
                _OperationType := 'Delete';
		    	select 
		    	xmlelement
		    	(
		    		name "PACKAGE_TYPE",
		    		xmlelement
		    		(
		    			name header,
		    			xmlforest
		    			(	
		    				_OperationType as "TYPE",
		    				"ID" as "ID"
		    			)
		    		)
		    	)
		    	into _Result;
            end if;

            if (_OperationType = 'FTLoad')
            then
                select
                xmlelement
                (
                    name "PACKAGE_TYPE",
                    xmlelement
                    (
                        name header,
                        xmlforest('FTLoad' as "TYPE")
                    ),
                    xmlelement
                    (
                        name body,
                        xmlelement
                        (
                            name "PACKAGE_TYPES",
                            xmlelement
                            (
                                name "PACKAGE_TYPE",
                                xmlforest
                                (
                                     pt.PackageTypeID		as "ID"
                                    ,pt.Code				as "Code"
                                    ,pt.Name				as "Name"
								    ,pt.ShortName			as "FormType"
								    ,pt.RegCode			    as "RegCode"
								    ,pt.FedCode			    as "FedCode"
                                )
                            )
                        )
                    )
                )
                from PHARM.T_PACKAGE_TYPE pt
                into _Result;
            end if;
            elsif(_OperationType <> 'Delete')
            then
                select
                xmlelement
                (
                    name "PACKAGE_TYPE",
                    xmlelement
                    (
                        name header,
                        xmlforest
                        (
                            _OperationType as "TYPE",
                            "ID" as "ID"
                        )
                    ),
                    xmlelement
                    (
                        name body,
                        xmlforest
                        (
                             pt.Code			as "Code"
                            ,pt.Name			as "Name"
					        ,pt.ShortName		as "ShortName"
        				    ,pt.RegCode		    as "RegCode"
					        ,pt.FedCode		    as "FedCode"
                        )
                    )
                )
                from PHARM.T_PACKAGE_TYPE pt
                into _Result
                where pt.PackageTypeID = "ID";
            end if; --_OperationType <> 'Delete'

            insert into RDS.T_RABBIT_SEND_OBJECT
            ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
            select
                 uuid_generate_v4(),
                ,s.ServerName--,<QueueName, varchar(255),>
                ,dbo.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; 
        elsif("Xml" is not null)
        then
            _OperationType := (xpath('/PACKAGE_TYPE/header[1]/TYPE[1]/text()', "Xml"))[1]::varchar(6);
            _OwnerID := (xpath('/PACKAGE_TYPE/header[1]/ID[1]/text()', "Xml"))[1]::text::uuid;
            _Result = cast("Xml" as text); 
            
            drop table if exists Tmp_PACKAGE_TYPES;
            create temp table Tmp_PACKAGE_TYPES
            as
            select
                 (xpath('/PACKAGE_TYPE/ID[1]/text()', x))[1]::text::uuid                as ID
			    ,(xpath('/PACKAGE_TYPE/Code[1]/text()', x))[1]::varchar(255)            as Code
			    ,(xpath('/PACKAGE_TYPE/Name[1]/text()', x))[1]::varchar(500)            as Name
                ,(xpath('/PACKAGE_TYPE/ShortName[1/text()', x))[1]::varchar(500)		as FormType
                ,(xpath('/PACKAGE_TYPE/RegCode[1/text()', x))[1]::varchar(50)		    as RegCode
                ,(xpath('/PACKAGE_TYPE/FedCode[1/text()', x))[1]::varchar(50)		    as FedCode
               from unnest(xpath('PACKAGE_TYPE/body/PACKAGE_TYPES/PACKAGE_TYPE', "Xml"::xml)) x
            where _OperationType = 'FTLoad';

            drop table if exists Tmp_PACKAGE_TYPE;
            create temp table Tmp_PACKAGE_TYPE
            as
            select
                 (xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::uuid                as ID
			    ,(xpath('/body[1]/Code[1]/text()', t.x2))[1]::varchar(255)              as Code
			    ,(xpath('/body[1]/Name[1]/text()', t.x2))[1]::varchar(500)              as Name
                ,(xpath('/body[1]/ShortName[1/text()', t.x2))[1]::varchar(500)		    as FormType
                ,(xpath('/body[1]/RegCode[1/text()', t.x2))[1]::varchar(50)		        as RegCode
                ,(xpath('/body[1]/FedCode[1/text()', t.x2))[1]::varchar(50)		        as FedCode
            select	
                 unnest(xpath('/PACKAGE_TYPE/header', x)) as x1
				,unnest(xpath('/PACKAGE_TYPE/body', x)) as x2
		    from unnest(xpath('/PACKAGE_TYPE', "Xml"::xml)) x
			where _OperationType <> 'FTLoad';

            if (_OperationType = 'FTLoad')
            then
                -- Обновляем имеющиеся
                update PHARM.T_PACKAGE_TYPE
                set
                     pt.Name = nmf.Name
                    ,pt.Code = nmf.Code
			        ,pt.ShortName = nmf.ShortName
			        ,pt.RegCode = nmf.RegCode
			        ,pt.FedCode = nmf.FedCode
                from Tmp_PACKAGE_TYPES nmf 
                where nmf.ID = pt.PackageTypeID;

                -- Загружаем новые
                INSERT INTO PHARM.T_PACKAGE_TYPE
                       (PackageTypeID, Name, Code, ShortName, RegCode, FedCode)
                 SELECT
                     nmf.ID--<Code, int,>
                    ,nmf.Name--<Name, varchar(255),>
                    ,nmf.Code--<Active, bit,>
			        ,nmf.ShortName
			        ,nmf.RegCode
			        ,nmf.FedCode
                 from Tmp_PACKAGE_TYPES nmf
                 where not exists
                 (
                     select *
                     from PHARM.T_PACKAGE_TYPE pt
                     where pt.PackageTypeID = nmf.ID
                 );
            end if;
            elsif (_OperationType = 'Delete')
            then
                delete from PHARM.T_PACKAGE_TYPE pt
		        where exists (select * from Tmp_PACKAGE_TYPE nmf where nmf.ID = pt.PackageTypeID);   
            end if;
            else 
            then
                update PHARM.T_PACKAGE_TYPE pt
		    	set	Name = nmf.Name
				,	Code = nmf.Code
				,	ShortName = nmf.ShortName
				,	RegCode = nmf.RegCode
				,	FedCode = nmf.FedCode
		    	from Tmp_PACKAGE_TYPE nmf 
		    	where nmf.ID = pt.PackageTypeID and _OperationType = _UPDATE;
			
		    	INSERT INTO PHARM.T_PACKAGE_TYPE
                    (PackageTypeID, Name, Code, ShortName, RegCode, FedCode)
		    	SELECT 
                    nmf.ID, 
                    nmf.Name, 
                    nmf.Code, 
                    nmf.ShortName, 
                    nmf.RegCode, 
                    nmf.FedCode
		    	from Tmp_PACKAGE_TYPE nmf
		    	where not exists (select * from PHARM.T_PACKAGE_TYPE pt where pt.PackageTypeID = nmf.ID) and _OperationType = 'Insert';
            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 s.ServerName <> "QueueName" and _OperationType <> 'FTLoad';

        -- Drop tables
        DROP TABLE if exists Tmp_PACKAGE_TYPE;
        DROP TABLE if exists Tmp_PACKAGE_TYPES;

        end if;
    End;
End;
$$;