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"
                                )
                            )
                        )
                    )
                )
                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 ()
            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"
                        )
                    )
                )
                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
               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()', x))[1]::text::uuid               as ID
			   ,(xpath('/body[1]/Active[1]/text()', x))[1]::text::boolean           as Active
			   ,(xpath('/body[1]/Name[1]/text()', x))[1]::varchar(255)              as Name
			   ,(xpath('/body[1]/NameLat[1]/text()', x))[1]::varchar(255)           as NameLat
               ,(xpath('/body[1]/FormType[1/text()', x))[1]::text::int		        as FormType
               ,(xpath('/body[1]/RegCode[1/text()', x))[1]::varchar(50)		        as RegCode
               ,(xpath('/body[1]/FedCode[1/text()', x))[1]::varchar(50)		        as FedCode
               ,(xpath('/body[1]/Code[1/text()', x))[1]::text::int			        as Code
               ,(xpath('/body[1]/EhrRusVzn431[1/text()', x))[1]::text::int		    as EhrRusVzn431
               ,(xpath('/body[1]/EhrRusVzn431Ver[1/text()', x))[1]::varchar(8)      as EhrRusVzn431Ver
            )
            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
                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)
                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
                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
                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)
                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
                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 AKUZ.T_GEN_NUMBERS where EntityID = _EntId and NumberName = 'PHARM.MEDICATION_FORMS' Limit 1)
			then
				update AKUZ.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 AKUZ.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;
$$;