drop procedure if exists sp_GetSetMedicamentProducer;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-12-23
-- Description: Выгрузка/загрузка справочника Производитель торгового наименования RDS
-- =============================================
create procedure sp_GetSetMedicamentProducer
(
    "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
)
    language plpgsql
as
$$
Begin
    declare 
        _Result text;
		_ContentEncoding varchar(255) := 'utf-8';
		_Type varchar(255) := 'MEDICAMENT_PRODUCER';
		_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_MEDICAMENT_PRODUCER mp where mp.MedicamentProducerID = "ID"))
        then
            _OperationType := 'Update';
        end if;
        elsif ("IsNew" = 0 and not exists (select * from PHARM.T_MEDICAMENT_PRODUCER mp where mp.MedicamentProducerID = "ID")
        then
            _OperationType := 'Delete';
                select 
                xmlelement
                (
                    name "MEDICAMENT_PRODUCER",
                    xmlelement
                    (
                        name header,
                        xmlforest
                        (
                          _OperationType as "TYPE",
                          "ID" as "ID"
                        )
                    )
                )
                into _Result;
        end if;

        if (_OperationType = 'FTLoad')
        then
            select 
            xmlelement
            (
                name "MEDICAMENT_PRODUCER",
                xmlelement
                (
                    name header,
                    xmlforest('FTLoad' as "TYPE")
                ),
                xmlelement
                (
                    name body,
                    xmlelement
                    (
                        name "MEDICAMENT_PRODUCERS",
                        xmlelement
                        (
                            name "MEDICAMENT_PRODUCER",
                            xmlforest
                            (
                                mp.MedicamentProducerID		as "ID"
                                ,mp.Inn						as "Inn"
                                ,mp.Name					as "Name"
                                ,mp.Address					as "Address"
                                ,mp.Active					as "Active"
                                ,c.Code						as "Country"
								,mp.RegCode					as "RegCode"
								,mp.FedCode					as "FedCode"
								,mp.Code					as "Code"
                            )
                        )

                    )
                )
            )
            from PHARM.T_MEDICAMENT_PRODUCER mp
                left join PHARM.T_COUNTRY c on c.CountryID = mp.Country
            into _Result;
        end if;
        elsif (_OperationType <> 'Delete')
        then
            select
            xmlelement
            (
                name "MEDICAMENT_PRODUCER",
                xmlelement
                (
                    name header,
                    xmlforest 
                    (
                        _OperationType as "TYPE",
                        "ID" as "ID"
                    ),
                ),
                xmlelement
                (
                    name body,
                    xmlforest
                    (
                         mp.Inn			    as "Inn"
                        ,mp.Name			as "Name"
                        ,mp.Address 		as "Address"
                        ,mp.Active		    as "Active"
                        ,c.Code			    as "Country"
					    ,mp.RegCode		    as "RegCode"
					    ,mp.FedCode		    as "FedCode"
					    ,mp.Code			as "Code"
                    )
                )
            )
            from PHARM.T_MEDICAMENT_PRODUCER mp
            left join PHARM.T_COUNTRY c on c.CountryID = mp.Country
            into _Result;
        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_PRODUCER/header[1]/TYPE[1]/text()', "Xml"::xml))[1]::varchar(36);
        _OwnerID := (xpath('/MEDICAMENT_PRODUCER/header[1]/ID[1]/text()', "Xml"::xml))[1]::text::uuid;
        _Result = "Xml"::xml;

        -- Generate table Tmp_MEDICAMENT_PRODUCERS

        drop table if exists Tmp_MEDICAMENT_PRODUCERS;
        Create Temp Table Tmp_MEDICAMENT_PRODUCERS
	    as
        select	
             (xpath('/MEDICAMENT_PRODUCER/ID[1]/text()', x))[1]::text::uuid                 as ID
	        ,(xpath('/MEDICAMENT_PRODUCER/Inn[1]/text()', x))[1]::varchar(255)              as Inn
	        ,(xpath('/MEDICAMENT_PRODUCER/Name[1]/text()', x))[1]::varchar(255)             as Name
	        ,(xpath('/MEDICAMENT_PRODUCER/Address[1]/text()', x))[1]::varchar(500)          as Address
	        ,(xpath('/MEDICAMENT_PRODUCER/Active[1]/text()', x))[1]::text::boolean          as Active
	        ,(xpath('/MEDICAMENT_PRODUCER/Country[1]/text()', x))[1]::varchar(13)           as Country
	        ,(xpath('/MEDICAMENT_PRODUCER/RegCode[1]/text()', x))[1]::varchar(50)           as RegCode
	        ,(xpath('/MEDICAMENT_PRODUCER/FedCode[1]/text()', x))[1]::varchar(50)           as FedCode
	        ,(xpath('/MEDICAMENT_PRODUCER/Code[1]/text()', x))[1]::text::int                as Code
	    from unnest(xpath('/MEDICAMENT_PRODUCER/body/MEDICAMENT_PRODUCERS/MEDICAMENT_PRODUCER', "Xml"::xml)) x
	    where _OperationType = 'FTLoad';

        drop table if exists Tmp_MEDICAMENT_PRODUCER;
        create temp table MEDICAMENT_PRODUCER
        as
        select
             (xpath('/header/ID[1]/text()', t.x1))[1]::text::uuid               as ID
	        ,(xpath('/body/Inn[1]/text()', t.x2))[1]::varchar(255)              as Inn
	        ,(xpath('/body/Name[1]/text()', t.x2))[1]::varchar(255)             as Name
	        ,(xpath('/body/Address[1]/text()', t.x2))[1]::varchar(500)          as Address
	        ,(xpath('/body/Active[1]/text()', t.x2))[1]::text::boolean          as Active
	        ,(xpath('/body/Country[1]/text()', t.x2))[1]::varchar(13)           as Country
	        ,(xpath('/body/RegCode[1]/text()', t.x2))[1]::varchar(50)           as RegCode
	        ,(xpath('/body/FedCode[1]/text()', t.x2))[1]::varchar(50)           as FedCode
	        ,(xpath('/body/Code[1]/text()', t.x2))[1]::text::int                as Code
	    from 
        (
            select unnest(xpath('/MEDICAMENT_PRODUCER/header', x)) as x1
			      ,unnest(xpath('/MEDICAMENT_PRODUCER/body', x)) as x2
			from unnest(xpath('/MEDICAMENT_PRODUCER', "Xml"::xml)) x
			where _OperationType <> 'FTLoad'
		) t;

        if (_OperationType = 'FTLoad')
        then
            -- Первичная загрузка
            -- Производителей не из справочника RDS переводим в неактивные
            update PHARM.T_MEDICAMENT_PRODUCER mp
            set mp.Active = '0'
            where not exists
            (
                select * 
                from Tmp_MEDICAMENT_PRODUCERS nmp
                where nmp.ID = mp.MedicamentProducerID
            );
            
            -- Обновляем имеющихся производителей
            update PHARM.T_MEDICAMENT_PRODUCER mp
            set mp.Inn = nmp.Inn
               ,mp.Name = nmp.Name
               ,mp.Address = nmp.Address
               ,mp.Country = (select c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country limit 1)
               ,mp.Active = nmp.Active
			   ,mp.RegCode = nmp.RegCode
			   ,mp.FedCode = nmp.FedCode
			   ,mp.Code = nmp.Code
            from Tmp_MEDICAMENT_PRODUCERS nmp
            where nmp.ID = mp.MedicamentProducerID;
            
            -- Загружаем новые
            INSERT INTO PHARM.T_MEDICAMENT_PRODUCER
                (MedicamentProducerID, Name, Inn, Address, Country, Active, RegCode, FedCode, Code)
            SELECT
                nmp.ID--<MedicamentProducerID, uniqueidentifier,>
                ,nmp.Name--<Name, varchar(255),>
                ,nmp.Inn--<Inn, varchar(255),>
                ,nmp.Address--<Address, varchar(500),>
                ,(select c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country limit 1)
                ,nmp.Active--<Active, bit,>)
				,nmp.RegCode
				,nmp.FedCode
				,nmp.Code
            from Tmp_MEDICAMENT_PRODUCERS nmp
            where not exists
            (
                select *
                from PHARM.T_MEDICAMENT_PRODUCER mp
                where mp.MedicamentProducerID = nmp.ID
            );
            -- Удаляем неактивных, непривязанных производителей
            delete from PHARM.T_MEDICAMENT_PRODUCER mp
            where mp.Active = '0'
            and not exists
            (
                select *
                from PHARM.T_BILL_OF_GOODS bg
                where bg.MedicamentProducer = mp.MedicamentProducerID
            );
        end if;
        elsif (_Operation = 'Delete')
        then
            update PHARM.T_MEDICAMENT_PRODUCER mp
            set mp.Active = '0'
            join Tmp_MEDICAMENT_PRODUCER nmp on nmp.ID = mp.MedicamentProducerID;
        end if;
        else
        then
            update PHARM.T_MEDICAMENT_PRODUCER mp
            set mp.Inn = nmp.Inn
               ,mp.Name = nmp.Name
               ,mp.Address = nmp.Address
               ,mp.Country = (select c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country limit 1)
               ,mp.Active = nmp.Active
			   ,mp.RegCode = nmp.RegCode
			   ,mp.FedCode = nmp.FedCode
			   ,mp.Code = nmp.Code
            from Tmp_MEDICAMENT_PRODUCER nmp 
            where nmp.ID = mp.MedicationProducerID
            and _OperationType = 'Update';

            INSERT INTO PHARM.T_MEDICAMENT_PRODUCER
                (MedicamentProducerID, Name, Inn, Address, Country, Active, RegCode, FedCode, Code)
            SELECT
                     nmp.ID--<MedicamentProducerID, uniqueidentifier,>
                    ,nmp.Name--<Name, varchar(255),>
                    ,nmp.Inn--<Inn, varchar(255),>
                    ,nmp.Address--<Address, varchar(500),>
                    ,(select c.CountryId from PHARM.T_COUNTRY c where c.Code = nmp.Country limit 1)
                    ,nmp.Active--<Active, bit,>)
					,nmp.RegCode
					,nmp.FedCode
					,nmp.Code
            from Tmp_MEDICAMENT_PRODUCER nmp
            where _OperationType = 'Insert'
			and not exists
            (
               select *
               from PHARM.T_MEDICAMENT_PRODUCER mp
               where mp.MedicamentProducerID = nmp.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';

        declare _EntId int;
		_EntId := (select EntityID from VCLib.T_ENTITIES where EntName = 'MEDICAMENT_PRODUCER' limit 1);

        if (exists (select * from RDS.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.MEDICAMENT_PRODUCER' limit 1)
		then
			update RDS.T_GEN_NUMBERS gn
			set gn.NextNumber = (select max(Code) from PHARM.T_MEDICAMENT_PRODUCER)
			where gn.EntityID = _EntId and gn.NumberName = 'PHARM.MEDICAMENT_PRODUCER';
		end if;
		else 
		then
			insert into RDS.T_GEN_NUMBERS (GenNumbersID, EntityID, NumberName, NextNumber)
			values 
			(
				uuid_generate_v4(),
				_EntId,
				'PHARM.MEDICAMENT_PRODUCER',
				coalesce((select max(Code) from PHARM.T_MEDICAMENT_PRODUCER), 1)
			);
						
		end if;
        
    end if; --"Xml" is not null
    End;
End;
$$; 