-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2022-03-16
-- Description: Отправка информации по направлениям в молочный регистр в RDS
-- =============================================
DROP PROCEDURE IF EXISTS sp_Milk_Register_Direction;

CREATE PROCEDURE sp_Milk_Register_Direction
(
    "Xml" xml = null    
,	"ID" uuid DEFAULT NULL::uuid
,   "QueueName" varchar(100) = 'RDSSync'
,   "DateTime" TimeStamp = null
,	"User" uuid DEFAULT NULL::uuid
)
    language plpgsql
as
$$
 Begin

    declare _Result text;
            _ContentEncoding varchar(255) := 'utf-8';
            _Type varchar(255) := 'MILK_DIRECTION';
            _OwnerID uuid := "ID";
    begin
            
		"DateTime" := coalesce("DateTime", Current_TimeStamp::TIMESTAMP);

        IF ("Xml" is null)
        Then
            if(not exists(select * from akuz.T_PATIENT_BENEFITS_DIRECTION where PatientBenefitsDirectionID = "ID"))
            then
			    RAISE EXCEPTION 'Направление не найдено!';
	        end if;

            -- Обработка операции
            _Result := 
			(
				select
				XMLELEMENT
				(
					name "MILK_DIRECTION"
				,	(
						xmlagg
						(
							xmlforest
							(
                                 pbd.PatientBenefitsDirectionID					as "ID"
                                ,lm.LpuCode										as "LPU"							
                                ,to_char(pbd.EnterDate, 'DD.MM.YYYY')				as "EnterDate"
                                ,pbd.LastName										as "LastName"
                                ,pbd.FirstName										as "FirstName"
                                ,pbd.FatherName									as "FatherName"   
	    			            ,pbd.BirthSurname									as "BirthSurname"
                                ,to_char(pbd.Birthday, 'DD.MM.YYYY')				as "Birthday"
                                ,to_char(pbd.Date, 'DD.MM.YYYY')				    as "Date"
                                ,e.Code											as "Sex"
                                ,pbd.Kod											as "Kod"
                                ,pbd.DocSeries										as "DocSeries"
                                ,pbd.DocNumber										as "DocNumber"
	    			            ,eDt.Code											as "DocType"
                                ,pbd.PAC_ID										as "RegisterID"
                                ,pbd.SNILS											as "SNILS"
                                ,pbd.ChangeType									as "ChangeType"
                                ,rbd.ExcludeReason									as "ExcludeReason"
                                ,to_char(pbd.DocIssueDate, 'DD.MM.YYYY')			as "DocIssueDate"
                                ,pbd.DocIssueOrg									as "DocIssueOrg"
                                ,pbd.Enp											as "Enp"
                                ,pbd.RealFiasAddress								as "RealFiasAddress"
                                ,pbd.RealFiasHome									as "RealFiasHome"
                                ,pbd.RealCity										as "RealCity"
                                ,pbd.RealCityPrefix								as "RealCityPrefix"
                                ,pbd.RealStreet									as "RealStreet"
                                ,pbd.RealStreetPrefix								as "RealStreetPrefix"
                                ,pbd.RealHouse										as "RealHouse"
                                ,pbd.RealBuild										as "RealBuild"
                                ,pbd.RealStruc										as "RealStruc"
                                ,pbd.RealRoom										as "RealRoom"
                                ,to_char(pbd.RealLiveStartDate, 'DD.MM.YYYY')		as "RealLiveStartDate"
                                ,to_char(pbd.RealLiveEndDate, 'DD.MM.YYYY')		   as "RealLiveEndDate"
                                ,pbd.LegalFiasAddress								as "LegalFiasAddress"
                                ,pbd.LegalFiasHome									as "LegalFiasHome"
                                ,pbd.LegalCity										as "LegalCity"
                                ,pbd.LegalCityPrefix								as "LegalCityPrefix"
                                ,pbd.LegalStreet									as "LegalStreet"
                                ,pbd.LegalStreetPrefix								as "LegalStreetPrefix"
                                ,pbd.LegalHouse									as "LegalHouse"
                                ,pbd.LegalBuild									as "LegalBuild"
                                ,pbd.LegalStruc									as "LegalStruc"
                                ,pbd.LegalRoom										as "LegalRoom"
                                ,to_char(pbd.LegalLiveStartDate, 'DD.MM.YYYY')	    as "LegalLiveStartDate"
                                ,to_char(pbd.LegalLiveEndDate, 'DD.MM.YYYY')		as "LegalLiveEndDate"
                                ,cs.CODE											as "CitizenShip"
                                )
                            )
                        )
	    			            -- собираем льготы
                                ,(
						        select
						        xmlagg
						        (
						        	xmlelement
						        	(
						        		name "MILK_DIRECTION_BENEFIT"
						        	,	xmlforest
	    			                    ( 
                                               pbdd.PatientBenefitsDirection				  as "DirectionID"
	    				            		   ,to_char(pbdd.DiagDate, 'DD.MM.YYYY')         as "DiagDate"
	    				                       ,pbdd.Document                                as "Document"
	    				                       ,to_char(pbdd.DocumentDate, 'DD.MM.YYYY')     as "DocumentDate"
	    				                       ,pbdd.Mkb                                     as "Mkb"
	    				                       ,b.BenefitCode                                as "BenefitCode"
	    				                       ,ebb.Code                                     as "BenefitViews"
	    				            		   ,post.Code									  as "PostCode"
	    				            		   ,pbdd.ChangeType								  as "ChangeType"
                                          )
                                   )
                                )
	    				                 from AKUZ.T_PATIENT_BENEFITS_DIRECTION_DATA pbdd 
	    				                     join AKUZ.T_BENEFITS b on b.BenefitsID = pbdd.Benefits
                                             join VCLib.T_ENUMS ebb on ebb.EnumID = b.BenefitViews
	    				            		 left join AKUZ.T_POST post on post.ResourceID = pbdd.Doctor
	    				                 where pbdd.PatientBenefitsDirection = rbd.PatientBenefitsDirectionID
                                )
					)
                from akuz.T_MILK_BENEFITS_DIRECTION rbd
                    join AKUZ.T_PATIENT_BENEFITS_DIRECTION pbd on pbd.PatientBenefitsDirectionID = rbd.PatientBenefitsDirectionID
                    join VCLib.T_ENUMS e on e.EnumID = pbd.Sex
                    join AKUZ.T_LPU_MAIN lm on lm.LpuID = pbd.LpuMain
	    			join AKUZ.T_LPU_SETTINGS ls on ls.LpuMain = lm.LpuID
                    left join AKUZ.T_POLICY p on p.PolicyID = dbo.fn_GetOmsPolicy(pbd.Patient, "DateTime")
                    join VCLib.T_ENUMS eDt on eDt.EnumID = pbd.DocType
	    			left join EHR.T_EHR_RUS_O00015 cs on cs.EhrRusID = pbd.CitizenShip
                where pbd.PatientBenefitsDirectionID = "ID"
            );
            
			-- Отправка
			INSERT INTO akuz.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
			select uuid_generate_v4(), "QueueName", dbo.ConvertTextToBinaryZip(_Result,''), null, null, _ContentEncoding, _Type, "User"::text, 0, "DateTime", _OwnerID;
        ELSE

			Drop Table If Exists TmpRR_DIRECTION;
			-- Generate table TmpRR_DIRECTION
			Create Temp Table TmpRR_DIRECTION
			As
			select	(xpath('/MILK_REGISTER/ID[1]/text()', x))[1]::text::UUID		as ID
			,	(xpath('/MILK_REGISTER/RegisterID[1]/text()', x))[1]::text::int		as RegisterID
			from
			(
				select "Xml" as x
			) t;

            if not exists(select * 
	    					from AKUZ.T_PATIENT_BENEFITS_DIRECTION pbd 
	    					join TmpRR_DIRECTION rd on rd.ID = pbd.PatientBenefitsDirectionID)
			then
				Raise Exception Using Message = 'Не найдены данные по направлению';
			end if;

            -- Устанавливаем идентификатор пациента из RDS в направлении
            update AKUZ.T_PATIENT_BENEFITS_DIRECTION pbd
            set PAC_ID = rd.RegisterID
            from TmpRR_DIRECTION rd 
            where rd.ID = pbd.PatientBenefitsDirectionID;

            -- Добавление льготы
            INSERT INTO AKUZ.T_PATIENT_BENEFITS
                       (PatientBenefitsID ,Patient ,Benefits ,BenefitDocumentNumber ,BeginDate ,Mkb ,Active ,Deleted
                       ,CreateType ,LPU_INF ,RegDate, PAC_ID,EndDate)
            SELECT
                       uuid_generate_v4()--<PatientBenefitsID, uniqueidentifier,>
                       ,pbd.Patient--<Patient, uniqueidentifier,>
                       ,pbdd.Benefits--<Benefits, uniqueidentifier,>
                       ,pbdd.Document--<BenefitDocumentNumber, varchar(255),>
                       ,pbd.Date--<BeginDate, datetime,>
                       ,(select m.MkbID from AKUZ.T_MKB m where m.MCBCode = pbdd.Mkb limit 1)--<Mkb, uniqueidentifier,>
                       ,'1'--<Active, bit,>
                       ,'0'--<Deleted, bit,>
                       ,2--<CreateType, int,>
                       ,lm.LpuCode--<LPU_INF, varchar(4),>
                       ,"DateTime"--<RegDate, datetime,>
                       ,pbd.PAC_ID
	    			   ,(case when b.Duration is not null and b.PeriodOfAct is not null and b.CloseType is not null
							then case when b.CloseType = 0 and b.PeriodOfAct = 0 then pbd.Birthday + interval '1 day' * b.Duration
										when b.CloseType = 0 and b.PeriodOfAct = 1 then pbd.Birthday + interval '1 month' * b.Duration
										when b.CloseType = 0 and b.PeriodOfAct = 2 then pbd.Birthday + interval '1 year' * b.Duration
										when b.CloseType = 1 and b.PeriodOfAct = 0 then pbd.Date + interval '1 day' * b.Duration
										when b.CloseType = 1 and b.PeriodOfAct = 1 then pbd.Date + interval '1 month' * b.Duration
										when b.CloseType = 1 and b.PeriodOfAct = 2 then pbd.Date + interval '1 year' * b.Duration
					  				end
							end)
            from AKUZ.T_PATIENT_BENEFITS_DIRECTION_DATA pbdd
                join AKUZ.T_PATIENT_BENEFITS_DIRECTION pbd on pbd.PatientBenefitsDirectionID = pbdd.PatientBenefitsDirection
                join TmpRR_DIRECTION rd on rd.ID = pbd.PatientBenefitsDirectionID
                join AKUZ.T_LPU_MAIN lm on lm.LpuID = pbd.LpuMain
	    		join AKUZ.T_LPU_SETTINGS ls on ls.LpuMain = lm.LpuID
                join AKUZ.T_BENEFITS b on b.BenefitsID = pbdd.Benefits
            where pbdd.ChangeType = 1
                    and
                not exists
                (
                    select *
                    from AKUZ.T_PATIENT_BENEFITS pb
                    where pb.Patient = pbd.Patient and pb.Benefits = pbdd.Benefits and (pb.EndDate is null or pb.EndDate > Current_TimeStamp::TIMESTAMP)
                );

            -- Закрытие льготы
            update AKUZ.T_PATIENT_BENEFITS_DIRECTION_DATA pbdd
            set EndDate = pbd.Date
                ,Active = '0'
                ,Deleted ='1'
            from TmpRR_DIRECTION rd 
                left join AKUZ.T_MKB mkb on mkb.MCBCode = pbdd.Mkb
                join AKUZ.T_PATIENT_BENEFITS_DIRECTION pbd on pbd.PatientBenefitsDirectionID = pbdd.PatientBenefitsDirection
                join AKUZ.T_PATIENT_BENEFITS pb on pb.Patient = pbd.Patient
                        and pb.Benefits = pbdd.Benefits and (pb.EndDate is null or pb.EndDate >= Current_TimeStamp::TIMESTAMP)
            where rd.ID = pbd.PatientBenefitsDirectionID and pbdd.ChangeType = 3;

	    	update AKUZ.T_PATIENT_BENEFITS_DIRECTION pbd
            set EndDate = pbd.Date
                ,Active = '0'
                ,Deleted = '1'
	    	from TmpRR_DIRECTION rd
                join AKUZ.T_PATIENT_BENEFITS pb on pb.Patient = pbd.Patient
	    		join AKUZ.T_BENEFITS b on b.BenefitsID = pb.Benefits
	    		join AKUZ.T_LPU_MAIN lp on lp.LpuID = pbd.LpuMain
            where rd.ID = pbd.PatientBenefitsDirectionID and pbd.ChangeType = 3 and (b.Private = '0' or (b.Private = '1' and (lp.LpuCode = pb.LPU_INF or lp.TFomsCode = pb.LPU_INF))) and (pb.EndDate is null or pb.EndDate >= Current_TimeStamp::TIMESTAMP)
            and b.BenefitType = 24299;

        END if;
    End;
End;
$$;