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

CREATE PROCEDURE sp_rr_direction
(
	"Xml" text DEFAULT NULL
,	"ID" uuid DEFAULT NULL::uuid
,	"QueueName" character varying DEFAULT 'rds.registry'::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) := 'RR_DIRECTION';
		_OwnerID UUID := "ID";
	Begin
		"dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);
	
		IF "Xml" is null
		then
			-- Обработка операции
			select
			XMLELEMENT
			(
				name "RR_DIRECTION"
			,	XMLFOREST
				(
					pbd.PatientBenefitsDirectionID					as "ID"
				,	case when ls.RegionCode =  '1600000000000' then '16' || lm.Code else lm.LpuCode end as "LPU"
				,	to_char(pbd.EnterDate, 'DD.MM.YYYY')			as "EnterDate"
				,	pbd.LastName									as "LastName"
				,	pbd.FirstName									as "FirstName"
				,	pbd.FatherName									as "FatherName"
				,	to_char(pbd.Birthday, 'DD.MM.YYYY')				as "Birthday"
				,	e.Code											as "Sex"
				,	pbd.Kod											as "Kod"
	            ,	case when pbd.DocSeries = '0' then null else pbd.DocSeries end as "DocSeries"
	            ,	case when pbd.DocNumber = '0' then null else pbd.DocNumber end as "DocNumber"
	            ,	pbd.PAC_ID										as "RrPatient"
	            ,	case when TRIM(REPLACE(REPLACE(pbd.SNILS, '0', ''), '-', '')) = '' then null else pbd.SNILS end as "SNILS"
				,	eDt.Code										as "DocType"
				,	pbd.ChangeType					        		as "ChangeType"
				,	rbd.ExcludeReason                        		as "ExcludeReason"
				,	rbd.KodAdr					            		as "KodAdr"
				,	pbd.BirthSurname					        	as "BirthSurname"
				,	lmF.LpuCode										as "LpuFirst"
				,	rbd.SOST_R										as "SOST_R"
				,	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 "RR_DIRECTION_BENEFIT"
						,	xmlforest
							(
	                        	rbd.PatientBenefitsDirectionID				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"
						    ,	rbdd.Dop                                    as "Dop"
						    ,	pDop.Code                                   as "DivisionManager"
							)
						)
					)
					from AKUZ.T_REGIONAL_BENEFITS_DIRECTION_DATA rbdd 
					join AKUZ.T_PATIENT_BENEFITS_DIRECTION_DATA pbdd on pbdd.PatientBenefitsDirectionDataID = rbdd.PatientBenefitsDirectionDataID
					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
					left join AKUZ.T_POST pDop on pDop.ResourceID = rbdd.DivisionManager
					where pbdd.PatientBenefitsDirection = rbd.PatientBenefitsDirectionID
				) 
			)
			from akuz.T_REGIONAL_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 AKUZ.T_LPU_MAIN lmF on lmF.LpuID = rbd.LpuMainFirst
			left join EHR.T_EHR_RUS_O00015 cs on cs.EhrRusID = pbd.CitizenShip
			into _Result
			where pbd.PatientBenefitsDirectionID = "ID";			
	
			-- Отправка
			INSERT INTO akuz.T_RABBIT_SEND_OBJECT(RabbitSendObjectID, QueueName, Content, MessageID, Headers, ContentEncoding, Type, UserID, State, CreateDateTime, OwnerID)
			select gen_random_uuid(), "QueueName", ConvertTextToBinaryZip(_Result, ''), null, null, _ContentEncoding, _Type, "User", 0, "dateTime", _OwnerID;
		ELSE
			-- Generate table Tmp_RR_DIRECTION
			Drop Table If Exists Tmp_RR_DIRECTION;
			Create Temp Table Tmp_RR_DIRECTION
			as
			SELECT	
                (xpath('RR_DIRECTION/ID[1]/text()', x))[1]::text::uuid as ID,
                (xpath('RR_DIRECTION/RrPatient[1]/text()', x))[1]::text::int as RrPatient
            FROM UNNEST(XPATH('//RR_DIRECTION', "Xml"::xml)) AS x;
			
			if not exists
			(
				select * from AKUZ.T_PATIENT_BENEFITS_DIRECTION pbd 
				join
					Tmp_RR_DIRECTION rd 
					on rd.ID = pbd.PatientBenefitsDirectionID
			)
			then
				Raise Exception Using Message = 'Не найдены данные по направлению на региональную льготу';
				Return;
			end if;
				
			-- Устанавливаем идентификатор пациента из RDS в направлении
			update akuz.T_PATIENT_BENEFITS_DIRECTION pbd
			set PAC_ID = rd.RrPatient
			from Tmp_RR_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 gen_random_uuid(), pbd.Patient, pbdd.Benefits, pbdd.Document, pbd.Date
			, (select m.MkbID from AKUZ.T_MKB m where m.MCBCode = pbdd.Mkb limit 1)
			, '1', '0', 2, lm.LpuCode, "dateTime", pbd.PAC_ID
			, case when ls.RegionCode = '1600000000000' and (b.BenefitCode = '057' or b.BenefitCode = '57') and b.BenefitViews = 19002  
					then case when pbd.Snils is not null and pbd.Snils <> '' 
						  		then pbd.Birthday + 3*interval '1 Year' - interval '1 Day'
						  		else pbd.Birthday + 6*interval '1 Month'  - interval '1 Day'
						  end
					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 + b.Duration*interval '1 DAY'
								when b.CloseType = 0 and b.PeriodOfAct = 1 then  pbd.Birthday + b.Duration*interval '1 MONTH'
								when b.CloseType = 0 and b.PeriodOfAct = 2 then  pbd.Birthday + b.Duration*interval '1 YEAR'
								when b.CloseType = 1 and b.PeriodOfAct = 0 then  pbd.Date + b.Duration*interval '1 DAY'
								when b.CloseType = 1 and b.PeriodOfAct = 1 then  pbd.Date + b.Duration*interval '1 MONTH'
								when b.CloseType = 1 and b.PeriodOfAct = 2 then  pbd.Date + b.Duration*interval '1 YEAR'
							end
				end
	        from AKUZ.T_PATIENT_BENEFITS_DIRECTION_DATA pbdd
	        join AKUZ.T_PATIENT_BENEFITS_DIRECTION pbd on pbd.PatientBenefitsDirectionID = pbdd.PatientBenefitsDirection
	        join Tmp_RR_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
					join AKUZ.T_BENEFITS b on b.BenefitsID = pb.Benefits
				where pb.Patient = pbd.Patient 
				and pb.Benefits = pbdd.Benefits
				and (pb.EndDate is null or pb.EndDate > now()::timestamp)
				and (b.Private = '0' or (b.Private = '1' and (lm.LpuCode = pb.LPU_INF or lm.TFomsCode = pb.LPU_INF)))
			);
	
	        -- Закрытие льготы
            UPDATE akuz.t_patient_benefits pb2
            SET enddate = pbd.date,
                active  = '0',
                deleted = '1'
            FROM akuz.t_patient_benefits pb
            JOIN akuz.t_patient_benefits_direction_data pbdd
                ON pb.benefits = pbdd.benefits
            JOIN akuz.t_patient_benefits_direction pbd
                ON pbd.patientbenefitsdirectionid = pbdd.patientbenefitsdirection AND pb.patient = pbd.patient
            JOIN tmp_rr_direction rd ON rd.id = pbd.patientbenefitsdirectionid
            JOIN akuz.t_benefits b ON b.benefitsid = pb.benefits
            JOIN akuz.t_lpu_main lp ON lp.lpuid = pbd.lpumain
            WHERE pb2.patientbenefitsid = pb.patientbenefitsid
              AND pbdd.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 >= NOW());

			 
            UPDATE akuz.t_patient_benefits pb2
            SET enddate = pbd.enterdate,
                active  = false,
                deleted = true
            FROM akuz.t_patient_benefits pb
            JOIN akuz.t_patient_benefits_direction pbd
                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
            JOIN tmp_rr_direction rd ON rd.id = pbd.patientbenefitsdirectionid
            WHERE pb2.patientbenefitsid = pb.patientbenefitsid
              AND 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 >= NOW())
              AND b.benefittype IN (24202, 24203);

		END if;
	End;
End;
$$;