DROP PROCEDURE sp_GetSetProductForm;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-12-21
-- Description: Выгрузка/загрузка справочника форм выпуска в(из) RDS
-- =============================================
CREATE PROCEDURE sp_GetSetProductForm
(
     "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) := 'PRODUCT_FORM';
        ,_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_PRODUCT_FORM where ProductFormID = "ID"))
		    then
		    	_OperationType = 'Update';
		    end if;
		    elsif ("IsNew" = '0' and not exists (select * from PHARM.T_PRODUCT_FORM where ProductFormID = "ID"))
            then
                _OperationType := 'Delete';
		    	select 
		    	xmlelement
		    	(
		    		name "PRODUCT_FORM",
		    		xmlelement
		    		(
		    			name header,
		    			xmlforest
		    			(	
		    				_OperationType as "TYPE",
		    				"ID" as "ID"
		    			)
		    		)
		    	)
		    	into _Result;
            end if;

            if (_OperationType = 'FTLoad')
            then
                select
                xmlelement
                (
                    name "PRODUCT_FORM",
                    xmlelement
                    (
                        name header,
                        xmlforest('FTLoad' as "TYPE")
                    ),
                    xmllelement
                    (
                        name body,
                        xmlelement
                        (
                            name "PRODUCT_FORMS",
                            xmlelement
                            (
                                name "PRODUCT_FORM",
                                xmlforest
                                (
                                     pf.ProductFormID		as "ID"
                                    ,pf.OrdinalNumber		as "OrdinalNumber"
                                    ,pf.Amount			    as "Amount"
								    ,pf.UnitPrice			as "UnitPrice"
								    ,pf.WriteOff			as "WriteOff"
								    ,pf.PackageType		    as "PackageType"
								    ,pf.BillOfGoods		    as "BillOfGoods"
								    ,pf.Descr				as "Descr"
								    ,pf.NotUseInPackage	    as "NotUseInPackage"
                                )
                            )
                        )
                    )
                )
                from PHARM.T_PRODUCT_FORM pf
                into _Result;
            end if;
            elsif (_OperationType <> 'Delete')
            then
                select
                xmlelement
                (
                    name "PRODUCT_FORM",
                    xmlelement
                    (
                        name header,
                        xmlforest
                        (
                            _OperationType as "TYPE",
                            "ID" as "ID"
                        )
                    ),
                    xmlelement
                    (
                        name body,
                        xmlforest
                        (
                             pf.OrdinalNumber		as "OrdinalNumber"
                            ,pf.Amount			    as "Amount"
					        ,pf.UnitPrice			as "UnitPrice"
					        ,pf.WriteOff			as "WriteOff"
					        ,pf.PackageType		    as "PackageType"
					        ,pf.BillOfGoods		    as "BillOfGoods"
					        ,pf.Descr				as "Descr"
            		        ,pf.NotUseInPackage	    as "NotUseInPackage"
                        )
                    )
                )
                from PHARM.T_PRODUCT_FORM pt
                into _Result
                where pt.ProductFormID = "ID";
            end if; --_OperationType <> 'Delete'

            insert into RDS.T_RABBIT_SEND_OBJECT
            (RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)

        end if; --xml is null

        elsif ("Xml" is not null)
        then
            _OperationType := (xpath('/PRODUCT_FORM/header[1]/TYPE[1]/text()', "Xml"))[1]::varchar(6);
            _OwnerID := (xpath('/PRODUCT_FORM/header[1]/ID[1]/text()', "Xml"))[1]::text::uuid;
            _Result = cast("Xml" as text); 

            drop table if exists Tmp_PRODUCT_FORMS;
            create temp table Tmp_PRODUCT_FORMS
            as
            select
                 (xpath('/PRODUCT_FORM/ID[1]/text()', x))[1]::text::uuid                        as ID
			    ,(xpath('/PRODUCT_FORM/OrdinalNumber[1]/text()', x))[1]::text::int              as OrdinalNumber
			    ,(xpath('/PRODUCT_FORM/Amount[1]/text()', x))[1]::text::decimal(12,2)           as Amount
                ,(xpath('/PRODUCT_FORM/UnitPrice[1/text()', x))[1]::text::boolean		        as UnitPrice
                ,(xpath('/PRODUCT_FORM/WriteOff[1/text()', x))[1]::text::boolean		        as WriteOff
                ,(xpath('/PRODUCT_FORM/PackageType[1/text()', x))[1]::text::uuid		        as PackageType
                ,(xpath('/PRODUCT_FORM/BillOfGoods[1/text()', x))[1]::text::uuid		        as BillOfGoods
				,(xpath('/PRODUCT_FORM/Descr[1]/text()', x))[1]::varchar(500)			        as Descr
				,(xpath('/PRODUCT_FORM/NotUseInPackage[1]/text()', x))[1]::text::boolean	    as NotUseInPackage
            from unnest(xpath('PRODUCT_FORM/body/PRODUCT_FORMS/PRODUCT_FORM', "Xml"::xml)) x
            where _OperationType = 'FTLoad';

            drop table if exists Tmp_PRODUCT_FORM;
            Create Temp Table TmpPRODUCT_FORM
			as
			select
				(xpath('/header[1]/ID[1]/text()', x1))[1]::text::uuid				            as ID
			    ,(xpath('/body[1]/OrdinalNumber[1]/text()', x2))[1]::text::int		            as OrdinalNumber
			    ,(xpath('/body[1]/Amount[1]/text()', x2))[1]::text::decimal(12,2)	            as Amount
			    ,(xpath('/body[1]/UnitPrice[1]/text()', x2))[1]::text::boolean		            as UnitPrice
			    ,(xpath('/body[1]/WriteOff[1]/text()', x2))[1]::text::boolean		            as WriteOff
			    ,(xpath('/body[1]/PackageType[1]/text()', x2))[1]::text::uuid    	            as PackageType
			    ,(xpath('/body[1]/BillOfGoods[1]/text()', x2))[1]::text::uuid    	            as BillOfGoods
			    ,(xpath('/body[1]/Descr[1]/text()', x2))[1]::varchar(500)			            as Descr
			    ,(xpath('/body[1]/NotUseInPackage[1]/text()', x))[1]::text::boolean     	    as NotUseInPackage
			from
			(
				select 
					(xpath('/PRODUCT_FORM/header', "Xml"))[1] as x1
				,	(xpath('/PRODUCT_FORM/body', "Xml"))[1] as x2
			) t1
			where _OperationType <> 'FTLoad';

            if (_OperationType = 'FTLoad')
            then
                delete from PHARM.T_PRODUCT_FORM f
                where not exists (select * from Tmp_PRODUCT_FORMS nmf where nmf.ID = pf.ProductFormID);

                update PHARM.T_PRODUCT_FORM pf
		        set OrdinalNumber = nmf.OrdinalNumber
				    ,Amount = nmf.Amount
				    ,UnitPrice = nmf.UnitPrice
				    ,WriteOff = nmf.WriteOff
				    ,PackageType = nmf.PackageType
				    ,BillOfGoods = nmf.BillOfGoods
				    ,Descr = nmf.Descr
				    ,NotUseInPackage = coalesce(nmf.NotUseInPackage, '0')
		        from Tmp_PRODUCT_FORMS nmf 
				where nmf.ID = pf.ProductFormID;
				
				-- Загружаем новые
				INSERT INTO PHARM.T_PRODUCT_FORM
                    (ProductFormID, OrdinalNumber, Amount, UnitPrice, WriteOff, PackageType, BillOfGoods, Descr, otUseInPackage)
		        SELECT 
                    nmf.ID, 
                    nmf.OrdinalNumber, 
                    nmf.Amount, 
                    nmf.UnitPrice, 
                    nmf.WriteOff, 
                    nmf.PackageType, 
                    nmf.BillOfGoods, 
                    nmf.Descr, 
                    coalesce(nmf.NotUseInPackage, '0')
		        from Tmp_PRODUCT_FORMS nmf
		        where not exists(select * from PHARM.T_PRODUCT_FORM pf where pf.ProductFormID = nmf.ID);
            end if;
            elsif (_OperationType = 'Delete')
            then
                delete from PHARM.T_PRODUCT_FORM pf
                where exists (select * from Tmp_PRODUCT_FORM nmf where nmf.ID = pf.ProductFormID);
            end if;
            else
            then
                update PHARM.T_PRODUCT_FORM pf
		        set	
                     OrdinalNumber = nmf.OrdinalNumber
				    ,Amount = nmf.Amount
				    ,UnitPrice = nmf.UnitPrice
				    ,WriteOff = nmf.WriteOff
				    ,PackageType = nmf.PackageType
				    ,BillOfGoods = nmf.BillOfGoods
				    ,Descr = nmf.Descr
				    ,NotUseInPackage = coalesce(nmf.NotUseInPackage, '0')
		        from TmpPRODUCT_FORM nmf 
				where nmf.ID = pf.ProductFormID and _OperationType = 'Update';

                INSERT INTO PHARM.T_PRODUCT_FORM
                    (ProductFormID, OrdinalNumber, Amount, UnitPrice, WriteOff, PackageType, BillOfGoods, Descr, NotUseInPackage)
		        SELECT 
                    nmf.ID, 
                    nmf.OrdinalNumber, 
                    nmf.Amount, 
                    nmf.UnitPrice, 
                    nmf.WriteOff, 
                    nmf.PackageType, 
                    nmf.BillOfGoods, 
                    nmf.Descr, 
                    coalesce(nmf.NotUseInPackage, '0')
		        from Tmp_PRODUCT_FORM nmf
		        where not exists (select * from PHARM.T_PRODUCT_FORM pf where pf.ProductFormID = 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 table if exists Tmp_PRODUCT_FORM;
			drop table if exists Tmp_PRODUCT_FORMS;
        end if;

    End;
End;
$$;