DROP PROCEDURE if exists sp_GetSetMedicationForms;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2016-02-08
-- Description: Выгрузка/загрузка справочника Лекарственных форм в(из) RDS
-- =============================================
CREATE PROCEDURE sp_GetSetMedicationForms
(
    "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) := 'MEDICATION_FORMS';
        ,_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_MEDICATION_FORMS where MedicationFormsID = "ID"))
		    then
		    	_OperationType = 'Update';
		    end if;
		    elsif ("IsNew" = '0' and not exists (select * from PHARM.T_MEDICATION_FORMS where MedicationFormsID = "ID"))
            then 
		    	_OperationType := 'Delete';
		    	select 
		    	xmlelement
		    	(
		    		name "MEDICATION_FORMS",
		    		xmlelement
		    		(
		    			name header,
		    			xmlforest
		    			(	
		    				_OperationType as "TYPE",
		    				"ID" as "ID"
		    			)
		    		)
		    	)
		    	into _Result;
            end if;

            -- Обработка операции
            if (_OperationType = 'FTLoad')
            then
                select
                xmlelement
                (
                    name "MEDICATION_FORMS",
                    xmlelement
                    (
                        name header,
                        xmlforest('FTLoad' as "TYPE")
                    ),
                    xmlelement
                    (
                        name body,
                        xmlelement
                        (
                            name "MEDICATION_FORMS",
                            xmlelement
                            (
                                name "MEDICATION_FORM",
                                xmlforest
                                (
                                     mf.MedicationFormsID	    as "ID"
						            ,mf.Code					as "Code"
                                    ,mf.Active				    as "Active"
                                    ,mf.Name					as "Name"
						            ,mf.FormType				as "FormType"
						            ,mf.RegCode				    as "RegCode"
						            ,mf.FedCode				    as "FedCode"
						            ,mf.NameLat				    as "NameLat"
						            ,ehr.IdEHR				    as "EhrRusVzn431"
						            ,RRB.CurrVersion			as "EhrRusVzn431Ver"
						            ,mf.PackageType				as "PackageType"
                                )
                            )
                        )
                    )
                )
                from PHARM.T_MEDICATION_FORMS mf
                    left join EHR.T_EHR_RUS_VZN431 ehr on ehr.EhrRusID = mf.EhrRusVzn431
                    left join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr.EhrRusRefBooks
                into _Result;
            end if;
            elsif (_OperationType <> 'Delete')
            then
                select
                xmlelement
                (
                    name "MEDICATION_FORMS",
                    xmlelement
                    (
                        name header,
                        xmlforest 
                        (
                            _OperationType as "TYPE",
                            "ID" as "ID"
                        )
                    ),
                    xmlelement
                    (
                        name body,
                        xmlforest
                        (
                             mf.Active		    as "Active"
                            ,mf.Name			as "Name"
					        ,mf.FormType		as "FormType"
					        ,mf.RegCode		    as "RegCode"
					        ,mf.FedCode		    as "FedCode"
					        ,mf.NameLat		    as "NameLat"
					        ,mf.Code			as "Code"
					        ,ehr.IdEHR			as "EhrRusVzn431"
					        ,RRB.CurrVersion	as "EhrRusVzn431Ver"
						    ,mf.PackageType		as "PackageType"
                        )
                    )
                )
                from PHARM.T_MEDICATION_FORMS mf
                    left join EHR.T_EHR_RUS_VZN431 ehr on ehr.EhrRusID = mf.EhrRusVzn431
				    left join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr.EhrRusRefBooks
                into _Result
                where mf.MedicationFormsID = "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('/MEDICATION_FORMS/header[1]/TYPE[1]/text()', "Xml"))[1]::varchar(6);
            _OwnerID := (xpath('/MEDICATION_FORMS/header[1]/ID[1]/text()', "Xml"))[1]::text::uuid;
            _Result = cast("Xml" as text); 

            drop table if exists Tmp_MEDICATION_FORMSS;
            create temp table Tmp_MEDICATION_FORMSS
            as
            select
                (xpath('/MEDICATION_FORMS/ID[1]/text()', x))[1]::text::uuid                as ID
			   ,(xpath('/MEDICATION_FORMS/Active[1]/text()', x))[1]::text::boolean         as Active
			   ,(xpath('/MEDICATION_FORMS/Name[1]/text()', x))[1]::varchar(255)            as Name
			   ,(xpath('/MEDICATION_FORMS/NameLat[1]/text()', x))[1]::varchar(255)         as NameLat
               ,(xpath('/MEDICATION_FORMS/FormType[1/text()', x))[1]::text::int		    as FormType
               ,(xpath('/MEDICATION_FORMS/RegCode[1/text()', x))[1]::varchar(50)		    as RegCode
               ,(xpath('/MEDICATION_FORMS/FedCode[1/text()', x))[1]::varchar(50)		    as FedCode
               ,(xpath('/MEDICATION_FORMS/Code[1/text()', x))[1]::text::int			    as Code
               ,(xpath('/MEDICATION_FORMS/EhrRusVzn431[1/text()', x))[1]::text::int		as EhrRusVzn431
               ,(xpath('/MEDICATION_FORMS/EhrRusVzn431Ver[1/text()', x))[1]::varchar(8) as EhrRusVzn431Ver
               ,(xpath('/MEDICATION_FORMS/PackageType[1]/text()', x))[1]::text::uuid    as PackageType
               from unnest(xpath('MEDICATION_FORMS/body/MEDICATION_FORMS/MEDICATION_FORM', "Xml"::xml)) x
            where _OperationType = 'FTLoad';

            drop table if exists Tmp_MEDICATION_FORMS;
            create temp table Tmp_MEDICATION_FORMS
            (
                 (xpath('/header[1]/ID[1]/text()', t.x1))[1]::text::uuid               as ID
			   ,(xpath('/body[1]/Active[1]/text()', t.x2))[1]::text::boolean           as Active
			   ,(xpath('/body[1]/Name[1]/text()', t.x2))[1]::varchar(255)              as Name
			   ,(xpath('/body[1]/NameLat[1]/text()', t.x2))[1]::varchar(255)           as NameLat
               ,(xpath('/body[1]/FormType[1/text()', t.x2))[1]::text::int		        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
               ,(xpath('/body[1]/Code[1/text()', t.x2))[1]::text::int			        as Code
               ,(xpath('/body[1]/EhrRusVzn431[1/text()', t.x2))[1]::text::int		    as EhrRusVzn431
               ,(xpath('/body[1]/EhrRusVzn431Ver[1/text()', t.x2))[1]::varchar(8)      as EhrRusVzn431Ver
               ,(xpath('/body[1]/PackageType[1]/text()', t.x2))[1]::text::uuid          as PackageType
            )
            from
			(
				select	 unnest(xpath('/MEDICATION_FORMS/header', x)) as x1
				        ,unnest(xpath('/MEDICATION_FORMS/body', x)) as x2
				from unnest(xpath('/MEDICATION_FORMS', "Xml"::xml)) x
				where _OperationType <> 'FTLoad'
			) t;

            if (_OperationType = 'FTLoad')
            then
                update PHARM.T_MEDICATION_FORMS mf
                set mf.Active = '0'
                where not exists
                (
                    select *
                    from Tmp_MEDICATION_FORMSS nmf
                    where nmf.ID = mf.MedicationFormsID
                );

                update PHARM.T_MEDICATION_FORMS mf
                set  
                     mf.Name			= nmf.Name
                    ,mf.Active		    = nmf.Active
			        ,mf.FormType		= coalesce(nmf.FormType,0)
			        ,mf.RegCode		    = nmf.RegCode
			        ,mf.FedCode		    = nmf.FedCode
			        ,mf.NameLat		    = nmf.NameLat
			        ,mf.Code			= nmf.Code
			        ,mf.EhrRusVzn431	= eh1.EhrRusID
			        ,mf.PackageType		= nmf.PackageType
                from Tmp_MEDICATION_FORMSS nmf 
                where nmf.ID = mf.MedicationFormsID
                left join lateral 
                (
				    select eh.EhrRusID 
				    from EHR.T_EHR_RUS_VZN431 eh
				    	join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
				    where eh.IdEHR = nmf.EhrRusVzn431 and RRB.CurrVersion = nmf.EhrRusVzn431Ver limit 1
				) eh1 on true;
                INSERT INTO PHARM.T_MEDICATION_FORMS
                       (MedicationFormsID, Name, Active, FormType, RegCode, FedCode, NameLat, Code, EhrRusVzn431,PackageType)
                SELECT
			    	 nmf.ID--<MedicationFormsID, uniqueidentifier,>
			    	,nmf.Name--<Name, varchar(255),>
			    	,nmf.Active--<Active, bit,>
			    	,coalesce(nmf.FormType,0)
			    	,nmf.RegCode
			    	,nmf.FedCode
			    	,nmf.NameLat
			    	,nmf.Code
			        ,eh1.EhrRusID
			    	,nmf.PackageType
                from Tmp_MEDICATION_FORMSS nmf
                left join lateral 
                (
			    	select eh.EhrRusID 
			    	from EHR.T_EHR_RUS_VZN431 eh
			    	    join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
			    	where eh.IdEHR = nmf.EhrRusVzn431 and RRB.CurrVersion = nmf.EhrRusVzn431Ver limit 1
			    ) eh1 on true
                where not exists
                (
                    select *
                    from PHARM.T_MEDICATION_FORMS mf
                    where mf.MedicationFormsID = nmf.ID
                );
            end if;

            elsif (_OperationType = 'Delete')
            then
                -- Удаление
                update PHARM.T_MEDICATION_FORMS mf
                set mf.Active = '0'
                from Tmp_MEDICATION_FORMS nmf on nmf.ID = mf.MedicationFormsID;
            end if;
            else
            then
                update mf
                     set mf.Name     = nmf.Name
                    ,mf.Active       = nmf.Active
			        ,mf.FormType     = coalesce(nmf.FormType,0)
			        ,mf.RegCode      = nmf.RegCode
			        ,mf.FedCode      = nmf.FedCode
			        ,mf.NameLat      = nmf.NameLat
			        ,mf.Code         = nmf.Code
			        ,mf.EhrRusVzn431 = eh1.EhrRusID
			        ,mf.PackageType         = nmf.PackageType
                from PHARM.T_MEDICATION_FORMS mf
                join Tmp_MEDICATION_FORMS nmf on nmf.ID = mf.MedicationFormsID
                left join lateral
                (
                    select eh.EhrRusID 
					from EHR.T_EHR_RUS_VZN431 eh
					    join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nmf.EhrRusVzn431 and RRB.CurrVersion = nmf.EhrRusVzn431Ver limit 1
				) eh1 on true
                where _OperationType = 'Update';
                
                -- insert [PHARM].T_MEDICATION_FORMS
                INSERT INTO PHARM.T_MEDICATION_FORMS
                    (MedicationFormsID , Name, Active, FormType, RegCode, FedCode, NameLat, [Code], EhrRusVzn431,PackageType)
                SELECT
			    	 nmf.ID--<MedicationFormsID, uniqueidentifier,>
			    	,nmf.Name--<Name, varchar(255),>
			    	,nmf.Active--<Active, bit,>
			    	,coalesce(nmf.FormType, 0)
			    	,nmf.RegCode
			    	,nmf.FedCode
			    	,nmf.NameLat
			    	,nmf.Code
			    	,eh1.EhrRusID
			    	,nmf.PackageType
                from Tmp_MEDICATION_FORMS nmf
                left join lateral
                (
			        select eh.EhrRusID 
			        from EHR.T_EHR_RUS_VZN431 eh
			        	join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
			        where eh.IdEHR = nmf.EhrRusVzn431 and RRB.CurrVersion = nmf.EhrRusVzn431Ver limit 1
			    ) eh1 on true
                where 
			    _OperationType = 'Insert'
			    and not exists
                (
                    select *
                    from PHARM.T_MEDICATION_FORMS mf
                    where mf.MedicationFormsID = 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';

            declare _EntId int;
            _EntId := (select EntityID from VCLib.T_ENTITIES where EntName = 'MEDICATION_FORMS' limit 1);
			if exists (select * from VCLIB.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.MEDICATION_FORMS' Limit 1)
			then
				update VCLIB.T_GEN_NUMBERS
				set NextNumber = (select MAX(Code) from PHARM.T_MEDICATION_FORMS) --+ 1
				where EntityID = _EntId and NumberName = 'PHARM.MEDICATION_FORMS';
			end if;
			else
			then
				INSERT INTO VCLIB.T_GEN_NUMBERS (GenNumbersID, EntityID, NumberName, NextNumber)
				VALUES (uuid_generate_v4(), _EntId, 'PHARM.MEDICATION_FORMS', coalesce((select MAX(Code) from PHARM.T_MEDICATION_FORMS), 1));
			end if;
			
			DROP TABLE if exists Tmp_MEDICATION_FORMS;
			DROP TABLE if exists Tmp_MEDICATION_FORMSS;
        end if; --"Xml" is not null
    End;
End;
$$;