DROP PROCEDURE if exists sp_GetSetMedicamentsOfRoszdravnadzor;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2019-10-29
-- Description: Выгрузка/загрузка справочника Медикаменты Росздравнадзора в(из) RDS
-- =============================================
CREATE PROCEDURE sp_GetSetMedicamentsOfRoszdravnadzor
(
     "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) := 'MEDICAMENT_OF_ROSZDRAVNADZOR';
        ,_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_MEDICAMENTS_OF_ROSZDRAVNADZOR where MedicamentsOfRoszdravnadzorID = "ID"))
		    then
		    	_OperationType = 'Update';
		    end if;
		    elsif ("IsNew" = 0 and not exists (select * from PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR where MedicamentsOfRoszdravnadzorID = "ID"))
            then 
		    	_OperationType := 'Delete';
		    	select 
		    	xmlelement
		    	(
		    		name "MEDICAMENT_OF_ROSZDRAVNADZOR",
		    		xmlelement
		    		(
		    			name header,
		    			xmlforest
		    			(	
		    				_OperationType as "TYPE",
		    				"ID" as "ID"
		    			)
		    		)
		    	)
		    	into _Result;
		    end if;

            if (_OperationType = 'FTLoad')
            then
                select
                xmlelement
                (
                    name "",
                    xmlelement
                    (
                        name header,
                        xmlforest('FTLoad' as "TYPE")
                    ),
                    xmlelement
                    (
                        name body,
                        xmlelement
                        (
                            name "MEDICAMENTS_OF_ROSZDRAVNADZOR",
                            xmlelement
                            (
                                name "MEDICAMENT_OF_ROSZDRAVNADZOR",
                                xmlforest
                                (
                                     m.MedicamentsOfRoszdravnadzorID		    as "ID"
		        					,m.DrugID									as "DrugID"
		        					,m.TradeNmR									as "TradeNmR"
		        					,m.InnR										as "InnR"
		        					,m.PackNx									as "PackNx"
		        					,m.DosageR									as "DosageR"
		        					,m.PackQn									as "PackQn"
		        					,m.Pack										as "Pack"
		        					,m.DrugFmNmRS								as "DrugFmNmRS"
		        					,m.Segment									as "Segment"
		        					,m.Year									    as "Year"
		        					,m.Month									as "Month"
		        					,m.Series									as "Series"
		        					,m.TotDrugQn								as "TotDrugQn"
		        					,m.MnfPrice									as "MnfPrice"
		        					,m.PrcPrice									as "PrcPrice"
		        					,m.RtlPrice									as "RtlPrice"
		        					,m.Funds									as "Funds"
		        					,m.VendorID									as "VendorID"
		        					,m.Remark									as "Remark"
		        					,m.SrcOrg									as "SrcOrg"
		        					,m.EAN										as "EAN"
		        					,m.ExpiTermR								as "ExpiTermR"
		        					,m.ClNm										as "ClNm"
		        					,m.MnfNm									as "MnfNm"
		        					,m.PckNm									as "PckNm"
		        					,m.RegNr									as "RegNr"
		        					,m.MaxMnfPrice								as "MaxMnfPrice"
		        					,to_char(m.RegDate, 'DD.MM.YYYY')   		as "RegDate"
		        					,m.Active									as "Active"
                                )
                            )
                        )
                    )
                )
                from PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                into _Result
                where m.Active = '1';
            end if;
            elsif (_OperationType <> 'Delete')
            then
                select
                xmlelement
                (
                    name "MEDICAMENT_OF_ROSZDRAVNADZOR",
                    xmlelement
                    (
                        name header,
                        xmlforest
                        (
                            _OperationType as "TYPE",
                            "ID" as "ID"
                        )
                    ),
                    xmlelement
                    (
                        name body,
                        xmlforest
                        (
                            ,m.DrugID								as "DrugID"
		    			    ,m.TradeNmR								as "TradeNmR"
		    			    ,m.InnR									as "InnR"
		    			    ,m.PackNx								as "PackNx"
		    			    ,m.DosageR								as "DosageR"
		    			    ,m.PackQn								as "PackQn"
		    			    ,m.Pack									as "Pack"
		    			    ,m.DrugFmNmRS							as "DrugFmNmRS"
		    			    ,m.Segment								as "Segment"
		    			    ,m.Year 								as "Year"
		    			    ,m.Month								as "Month"
		    			    ,m.Series								as "Series"
		    			    ,m.TotDrugQn							as "TotDrugQn"
		    			    ,m.MnfPrice								as "MnfPrice"
		    			    ,m.PrcPrice								as "PrcPrice"
		    			    ,m.RtlPrice								as "RtlPrice"
		    			    ,m.Funds								as "Funds"
		    			    ,m.VendorID								as "VendorID"
		    			    ,m.Remark								as "Remark"
		    			    ,m.SrcOrg								as "SrcOrg"
		    			    ,m.EAN									as "EAN"
		    			    ,m.ExpiTermR							as "ExpiTermR"
		    			    ,m.ClNm									as "ClNm"
		    			    ,m.MnfNm								as "MnfNm"
		    			    ,m.PckNm								as "PckNm"
		    			    ,m.RegNr								as "RegNr"
		    			    ,m.MaxMnfPrice							as "MaxMnfPrice"
		    			    ,to_char(m.RegDate, 'DD.MM.YYYY')	    as "RegDate"
		    			    ,m.Active								as "Active"
                        )
                    )

                )
                from PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                into _Result
                where m.MedicamentsOfRoszdravnadzorID = "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; --xml is null
        elsif ("Xml" is not null)
        then
            -- Получаем тип операции
            _OperationType := (xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR/header[1]/TYPE[1]/text()', "Xml"))[1]::varchar(6);
            _OwnerID := (xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR/header[1]/ID[1]/text()', "Xml"))[1]::text::uuid;
            _Result = cast("Xml" as text); 
            
             -- Generate table #MEDICAMENTS_OF_ROSZDRAVNADZOR
             drop table if exists Tmp_MEDICAMENTS_OF_ROSZDRAVNADZOR;
             create temp table Tmp_MEDICAMENTS_OF_ROSZDRAVNADZOR
             as
             select
                 (xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR/ClNm[1]/text()', x))[1]::varchar(255)                         as ClNm
			    ,(xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR/MnfNm[1]/text()', x))[1]::varchar(1000)                       as MnfNm
			    ,(xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR/PckNm[1]/text()', x))[1]::varchar(1000)                       as PckNm
			    ,(xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR/RegNr[1]/text()', x))[1]::varchar(100)                        as RegNr
        	    ,to_timestamp((xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR/RegDate[1]/text()', x))[1]::text, 'YYYY-MM-DD')  as RegDate
                ,(xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR/Active[1/text()', x))[1]::text::boolean					    as Active
                from unnest(xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR/body/MEDICAMENTS_OF_ROSZDRAVNADZOR/MEDICAMENT_OF_ROSZDRAVNADZOR', "Xml"::xml)) x
            where _OperationType = 'FTLoad';
            
            -- Generate table #MEDICAMENT_OF_ROSZDRAVNADZOR
            drop table if exists Tmp_MEDICAMENT_OF_ROSZDRAVNADZOR;
			Create Temp Table Tmp_MEDICAMENT_OF_ROSZDRAVNADZOR
			as
            select	
                 (xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::uuid         as ID
			    ,(xpath('./body[1]/DrugID[1]/text()', t.x2))[1]::text::int                          as DrugID
			    ,(xpath('./body[1]/TradeNmR[1]/text()', t.x2))[1]::varchar(1000)                    as TradeNmR
			    ,(xpath('./body[1]/InnR[1]/text()', t.x2))[1]::varchar(1000)                        as InnR
			    ,(xpath('./body[1]/PackNx[1]/text()', t.x2))[1]::text::bigint                       as PackNx
			    ,(xpath('./body[1]/DosageR[1]/text()', t.x2))[1]::varchar(255)                      as DosageR
			    ,(xpath('./body[1]/PackQn[1]/text()', t.x2))[1]::text::int                          as PackQn
			    ,(xpath('./body[1]/Pack[1]/text()', t.x2))[1]::varchar(1000)                        as Pack
			    ,(xpath('./body[1]/DrugFmNmRS[1]/text()', t.x2))[1]::varchar(1000)                  as DrugFmNmRS
			    ,(xpath('./body[1]/Segment[1]/text()'t.x2 x))[1]::varchar(255)                      as Segment
			    ,(xpath('./body[1]/Year[1]/text()', t.x2))[1]::text::int                            as Year
			    ,(xpath('./body[1]/Month[1]/text()', t.x2))[1]::text::int                           as Month
			    ,(xpath('./body[1]/Series[1]/text()', t.x2))[1]::varchar(255)                       as Series
                ,(xpath('./body[1]/TotDrugQn[1]/text()', t.x2))[1]::text::decimal(12,2)		        as TotDrugQn
                ,(xpath('./body[1]/MnfPrice[1]/text()', t.x2))[1]::text::decimal(12,2)		        as MnfPrice
                ,(xpath('./body[1]/PrcPrice[1]/text()', t.x2))[1]::text::decimal(12,2)		        as PrcPrice
                ,(xpath('./body[1]/RtlPrice[1]/text()', t.x2))[1]::text::decimal(12,2)		        as RtlPrice
                ,(xpath('./body[1]/Funds[1]/text()', t.x2))[1]::text::decimal(12,2)		            as Funds
			    ,(xpath('./body[1]/VendorID[1]/text()', t.x2))[1]::varchar(255)                     as VendorID
			    ,(xpath('./body[1]/Remark[1]/text()', t.x2))[1]::varchar(1000)                      as Remark
			    ,(xpath('./body[1]/SrcOrg[1]/text()', t.x2))[1]::varchar(255)                       as SrcOrg
			    ,(xpath('./body[1]/EAN[1]/text()', t.x2))[1]::varchar(14)                           as EAN
                ,(xpath('./body[1]/MaxMnfPrice[1]/text()', t.x))[1]::text::decimal(12,2)	        as MaxMnfPrice
			    ,(xpath('./body[1]/ExpiTermR[1]/text()', t.x2))[1]::varchar(500)                    as ExpiTermR
                ,(xpath('./body[1]/ClNm[1]/text()', t.x2))[1]::varchar(255)						    as ClNm
                ,(xpath('./body[1]/MnfNm[1]/text()', t.x2))[1]::varchar(1000)						as MnfNm
                ,(xpath('./body[1]/PckNm[1]/text()', t.x2))[1]::varchar(1000)						as PckNm
                ,(xpath('./body[1]/RegNr[1]/text()', t.x2))[1]::varchar(100)						as RegNr
        	    ,to_timestamp((xpath('./body[1]/RegDate[1]/text()', t.x2))[1]::text, 'YYYY-MM-DD')  as RegDate
                ,(xpath('./body[1]/Active[1/text()', t.x2))[1]::text::boolean					    as Active
            from
			(
				select	 unnest(xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR/header', x)) as x1
				        ,unnest(xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR/body', x)) as x2
				from unnest(xpath('/MEDICAMENT_OF_ROSZDRAVNADZOR', "Xml"::xml)) x
			) t
			where _OperationType = 'FTLoad';

            if (_OperationType = 'FTLoad')
            then
                update PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR s
                set s.Active = '0'
                where not exists
                (
                    select *
                    from Tmp_MEDICAMENTS_OF_ROSZDRAVNADZOR nmf
                    where nmf.ID = s.MedicamentsOfRoszdravnadzorID
                );

                update PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                set  m.DrugID			= nmf.DrugID
                    ,m.TradeNmR			= nmf.TradeNmR
                    ,m.InnR				= nmf.InnR
                    ,m.PackNx			= nmf.PackNx
                    ,m.DosageR		    = nmf.DosageR
                    ,m.PackQn			= nmf.PackQn
                    ,m.Pack				= nmf.Pack
                    ,m.DrugFmNmRS		= nmf.DrugFmNmRS
                    ,m.Segment			= nmf.Segment
                    ,m.Year				= nmf.Year
                    ,m.Month			= nmf.Month
                    ,m.Series			= nmf.Series
                    ,m.TotDrugQn		= nmf.TotDrugQn
                    ,m.MnfPrice			= nmf.MnfPrice
                    ,m.PrcPrice			= nmf.PrcPrice
                    ,m.RtlPrice			= nmf.RtlPrice
                    ,m.Funds			= nmf.Funds
                    ,m.VendorID			= nmf.VendorID
                    ,m.Remark			= nmf.Remark
                    ,m.SrcOrg			= nmf.SrcOrg
                    ,m.EAN			    = nmf.EAN
                    ,m.MaxMnfPrice		= nmf.MaxMnfPrice
                    ,m.ExpiTermR		= nmf.ExpiTermR
                    ,m.ClNm				= nmf.ClNm
                    ,m.MnfNm			= nmf.MnfNm
                    ,m.PckNm			= nmf.PckNm
                    ,m.RegNr			= nmf.RegNr
                    ,m.RegDate			= nmf.RegDate
                    ,m.Active			= coalesce(nmf.Active, '1')
                from Tmp_MEDICAMENTS_OF_ROSZDRAVNADZOR nmf 
                where nmf.ID = m.MedicamentsOfRoszdravnadzorID and m.Active = '1';

                INSERT INTO PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR
                (
                     MedicamentsOfRoszdravnadzorID
                    ,DrugID
                    ,TradeNmR
                    ,InnR
                    ,PackNx
                    ,DosageR
                    ,PackQn
                    ,Pack
                    ,DrugFmNmRS
                    ,Segment
                    ,Year
                    ,Month
                    ,Series
                    ,TotDrugQn
                    ,MnfPrice
                    ,PrcPrice
                    ,RtlPrice
                    ,Funds
                    ,VendorID
                    ,Remark
                    ,SrcOrg
                    ,EAN
                    ,MaxMnfPrice
                    ,ExpiTermR
                    ,ClNm
                    ,MnfNm
                    ,PckNm
                    ,RegNr
                    ,RegDate
		            ,Active
                )
		        select
		             nmf.ID
                    ,nmf.DrugID
                    ,nmf.TradeNmR
                    ,nmf.InnR
                    ,nmf.PackNx
                    ,nmf.DosageR
                    ,nmf.PackQn
                    ,nmf.Pack
                    ,nmf.DrugFmNmRS
                    ,nmf.Segment
                    ,nmf.Year
                    ,nmf.Month
                    ,nmf.Series
                    ,nmf.TotDrugQn
                    ,nmf.MnfPrice
                    ,nmf.PrcPrice
                    ,nmf.RtlPrice
                    ,nmf.Funds
                    ,nmf.VendorID
                    ,nmf.Remark
                    ,nmf.SrcOrg
                    ,nmf.EAN
                    ,nmf.MaxMnfPrice
                    ,nmf.ExpiTermR
                    ,nmf.ClNm
                    ,nmf.MnfNm
                    ,nmf.PckNm
                    ,nmf.RegNr
                    ,nmf.RegDate
		            ,coalesce(nmf.Active, '1')
                from Tmp_MEDICAMENTS_OF_ROSZDRAVNADZOR nmf
                where not exists
                (
                    select *
                    from PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                    where m.MedicamentsOfRoszdravnadzorID = nmf.ID
                );
            end if;
            elsif (_OperationType = 'Delete')
            then
                update PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                set m.Active = 0
                from Tmp_MEDICAMENT_OF_ROSZDRAVNADZOR nmf 
                where m.MedicamentsOfRoszdravnadzorID = nmf.ID;
            end if;
            else
            then
                update PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                set  m.DrugID					= nmf.DrugID
                    ,m.TradeNmR				    = nmf.TradeNmR
                    ,m.InnR					    = nmf.InnR
                    ,m.PackNx					= nmf.PackNx
                    ,m.DosageR				    = nmf.DosageR
                    ,m.PackQn					= nmf.PackQn
                    ,m.Pack					    = nmf.Pack
                    ,m.DrugFmNmRS				= nmf.DrugFmNmRS
                    ,m.Segment				    = nmf.Segment
                    ,m.Year					    = nmf.Year
                    ,m.Month					= nmf.Month
                    ,m.Series					= nmf.Series
                    ,m.TotDrugQn				= nmf.TotDrugQn
                    ,m.MnfPrice				    = nmf.MnfPrice
                    ,m.PrcPrice				    = nmf.PrcPrice
                    ,m.RtlPrice				    = nmf.RtlPrice
                    ,m.Funds					= nmf.Funds
                    ,m.VendorID				    = nmf.VendorID
                    ,m.Remark					= nmf.Remark
                    ,m.SrcOrg					= nmf.SrcOrg
                    ,m.EAN					    = nmf.EAN
                    ,m.MaxMnfPrice		        = nmf.MaxMnfPrice
                    ,m.ExpiTermR				= nmf.ExpiTermR
                    ,m.ClNm					    = nmf.ClNm
                    ,m.MnfNm					= nmf.MnfNm
                    ,m.PckNm					= nmf.PckNm
                    ,m.RegNr					= nmf.RegNr
                    ,m.RegDate				    = nmf.RegDate
                    ,m.Active					= coalesce(nmf.Active, '1')
                from Tmp_MEDICAMENT_OF_ROSZDRAVNADZOR nmf 
                where nmf.ID = m.MedicamentsOfRoszdravnadzorID and _OperationType = 'Update' and m.Active = '1';
               
                INSERT INTO PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR
                (   
                    MedicamentsOfRoszdravnadzorID
                    ,DrugID
                    ,TradeNmR
                    ,InnR
                    ,PackNx
                    ,DosageR
                    ,PackQn
                    ,Pack
                    ,DrugFmNmRS
                    ,Segment
                    ,Year
                    ,Month
                    ,Series
                    ,TotDrugQn
                    ,MnfPrice
                    ,PrcPrice
                    ,RtlPrice
                    ,Funds
                    ,VendorID
                    ,Remark
                    ,SrcOrg
                    ,EAN
                    ,MaxMnfPrice
                    ,ExpiTermR
                    ,ClNm
                    ,MnfNm
                    ,PckNm
                    ,RegNr
                    ,RegDate
		            ,Active
                )
		        select
		        	nmf.ID
                   ,nmf.DrugID
                   ,nmf.TradeNmR
                   ,nmf.InnR
                   ,nmf.PackNx
                   ,nmf.DosageR
                   ,nmf.PackQn
                   ,nmf.Pack
                   ,nmf.DrugFmNmRS
                   ,nmf.Segment
                   ,nmf.Year
                   ,nmf.Month
                   ,nmf.Series
                   ,nmf.TotDrugQn
                   ,nmf.MnfPrice
                   ,nmf.PrcPrice
                   ,nmf.RtlPrice
                   ,nmf.Funds
                   ,nmf.VendorID
                   ,nmf.Remark
                   ,nmf.SrcOrg
                   ,nmf.EAN
                   ,nmf.MaxMnfPrice
                   ,nmf.ExpiTermR
                   ,nmf.ClNm
                   ,nmf.MnfNm
                   ,nmf.PckNm
                   ,nmf.RegNr
                   ,nmf.RegDate
		           ,coalesce(nmf.Active, '1')
                from Tmp_MEDICAMENT_OF_ROSZDRAVNADZOR nmf
                where 
		        _OperationType = 'Insert'
		        and not exists
                (
                    select *
                    from PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR m
                    where m.MedicamentsOfRoszdravnadzorID = nmf.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 s.ServerName <> "QueueName" and _OperationType <> 'FTLoad';

            update PHARM.T_BILL_OF_GOODS bg
            set bg.MedicamentsOfRoszdravnadzor = null
            from PHARM.T_MEDICAMENTS_OF_ROSZDRAVNADZOR mor 
            where mor.MedicamentsOfRoszdravnadzorID = bg.MedicamentsOfRoszdravnadzor and mor.Active = '0';

            drop table if exists Tmp_MEDICAMENT_OF_ROSZDRAVNADZOR;
            drop table if exists Tmp_MEDICAMENTS_OF_ROSZDRAVNADZOR;
        end if;
    End;
End;
$$;