-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2020-03-30
-- =============================================
DROP PROCEDURE IF EXISTS sp_BSK_Register_Direction;

CREATE PROCEDURE sp_BSK_Register_Direction
(
	"Xml" xml DEFAULT NULL::xml
,	"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) := 'BSK_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 "BSK_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"
	                  		,	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(now(), 'YYYY-MM-DD HH24:MI:SS')				as "CreateDateTime"
	                  		,	pbd.BirthSurname									as "BirthSurname"
	                  		,	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 "BSK_DIRECTION_BENEFIT"
							,	xmlforest
								(
									rbd.PatientBenefitsDirectionID						as "DirectionID"
	                             ,	bbdd.PatientBenefitsDirectionDataID					as "DirectionBenID"
								 ,	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"
								 ,	ms.Name												as "ServiceName"
								 ,	ms.Code												as "ServiceCode"
								 ,	to_char(bbdd.DateBegin, 'DD.MM.YYYY')				as "DateBegin"
								 ,	to_char(bbdd.DateEnd, 'DD.MM.YYYY')					as "DateEnd"
								 ,	bbdd.ExclusionReason								as "ExclusionReason"
								 ,	to_char(bbdd.EstimatedDDate, 'DD.MM.YYYY')			as "EstimatedDDate"
								 ,	to_char(bbdd.DRegistrationDate, 'DD.MM.YYYY')		as "DRegistrationDate"
								 ,	bbdd.Place											as "Place"
								 ,	lmTo.LpuCode										as "LpuTo"
								 ,	to_char(bbdd.CloseDate, 'DD.MM.YYYY')				as "CloseDate"
								 ,	bbdd.ConcomitantMkb									as "ConcomitantMkb"
								 ,	bbdd.ConcomitantDisease								as "ConcomitantDisease"
								)
							,	(
									select
									xmlagg
									(
										xmlelement
										(
											name "BSK_BENEFITS_DIRECTION_MEDICAMENTS"
										,	xmlforest
											(
												meds.BskBenefitsDirectionData               as "DirectionBenID"
											,	mnn.OtherID                                 as "Mnn"
											,	to_char(meds.DateStart, 'DD.MM.YYYY')   	as "DateStart"
											,	to_char(meds.EndDate, 'DD.MM.YYYY')     	as "EndDate"
											,	meds.MedicationForms                        as "MedicationForms"
											,	meds.Regiment                               as "Regiment"
											,	meds.Dose                                   as "Dose"
											,	meds.Prepid                                 as "Prepid"
											,	meds.DayliCoeff                             as "DayliCoeff"
											,	dv.Name	                                    as "MeasureUnit"
											,	dv.ViewType	                                as "MeasureUnitType"
											,	meds.SingleDose                             as "SingleDose"
											,	meds.Multiplicity                           as "Multiplicity"
											,	dvdv.Name	                                as "DoseViewUnit"
											,	dvdv.ViewType	                            as "DoseViewUnitType"
											)
										)
									)
									from AKUZ.T_BSK_BENEFITS_DIRECTION_MEDICAMENTS meds
										join PHARM.T_C_MNN mnn on mnn.CMnnID = meds.CMnn
										left join PHARM.T_DOSE_VIEW dv on dv.Code = meds.MeasureUnit
										left join PHARM.T_DOSE_VIEW dvdv on dvdv.Code = meds.DoseView
									where meds.BskBenefitsDirectionData = bbdd.PatientBenefitsDirectionDataID								
								)
								,	(
									select
									xmlagg
									(
										xmlelement
										(
											name "BSK_REGISTER_COMPLICATION_LINK"
										,	xmlforest
											(
												 compl.BskRegisterComplicationLinkID					as "ComplicationID"
												,compl.BskBenefitsDirectionData							as "DirectionBenID"
												,compl.ComplicationType									as "ComplicationType"
												,to_char(compl.RegistrationDate, 'DD.MM.YYYY')			as "RegistrationDate"
												,compl.MKBCode											as "MKBCode"
												,compl.Diagnosis										as "Diagnosis"
												,class.IdEHR											as "EhrRusR6JR8PINUS"
												,rrb.CurrVersion										as "EhrRusR6JR8PINUSVer"
												,type.IdEHR												as "EhrRusIN6SHID9F6"
												,rrb1.CurrVersion										as "EhrRusIN6SHID9F6Ver"
												,stage.IdEHR											as "EhrRusJEDS208EN8"
												,rrb2.CurrVersion										as "EhrRusJEDS208EN8Ver"
												,chf.LeftVentricularEjectionFraction					as "LeftVentricularEjectionFraction"
												,to_char(chf.FractionRegistrationDate, 'DD.MM.YYYY')	as "FractionRegistrationDate"
											)
										)
									)
									from AKUZ.T_BSK_REGISTER_COMPLICATION_LINK compl
										join AKUZ.T_BSK_REGISTER_CHRONIC_HEART_FAILURE chf on chf.BskRegisterComplicationLinkID = compl.BskRegisterComplicationLinkID
                                        left join EHR.T_EHR_RUS_R6JR8PINUS class on chf.ChronicHeartFailureClass = class.EhrRusID
                                            left join EHR.T_EHR_RUS_REF_BOOKS rrb ON rrb.EhrRusRefBooksID = class.EhrRusRefBooks
                                        left join EHR.EHR_RUS_IN6SHID9F6 type on chf.ChronicHeartFailureType = type.EhrRusID
                                            left join EHR.T_EHR_RUS_REF_BOOKS rrb1 ON rrb1.EhrRusRefBooksID = type.EhrRusRefBooks
                                        left join EHR.EHR_RUS_JEDS208EN8 stage on chf.ChronicHeartFailureStage = stage.EhrRusID
                                            left join EHR.T_EHR_RUS_REF_BOOKS rrb2 ON rrb2.EhrRusRefBooksID = stage.EhrRusRefBooks
								    where compl.BskBenefitsDirectionData = bbdd.PatientBenefitsDirectionDataID							
								)
							)
						)
						from AKUZ.T_BSK_BENEFITS_DIRECTION_DATA bbdd 
							join AKUZ.T_PATIENT_BENEFITS_DIRECTION_DATA pbdd on pbdd.PatientBenefitsDirectionDataID = bbdd.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_MEDICAL_SERVICE ms on ms.MedicalServiceID = bbdd.MedicalService
							left join AKUZ.T_LPU_MAIN lmTo on lmTo.LpuID = bbdd.LpuMainTo
						where pbdd.PatientBenefitsDirection = rbd.PatientBenefitsDirectionID
					)
				)
				from akuz.T_BSK_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 = 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 #RR_DIRECTION
			Create Temp Table TmpRR_DIRECTION
			As
			select	(xpath('/BSK_REGISTER/ID[1]/text()', x))[1]::text::UUID			as ID
			,	(xpath('/BSK_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;
			
			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(), 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
			, coalesce(bsk.CloseDate
					  , bsk.DateEnd
					  , 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 AKUZ.T_BSK_BENEFITS_DIRECTION_DATA bsk on bsk.PatientBenefitsDirectionDataID = pbdd.PatientBenefitsDirectionDataID
				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 > now()::timestamp));
	
	        -- Закрытие льготы
	        update AKUZ.T_PATIENT_BENEFITS pb
	        set EndDate = pbd.Date
	        ,	Active = '0'
	        ,	Deleted = '1'
	        from AKUZ.T_PATIENT_BENEFITS_DIRECTION pbd
				join AKUZ.T_PATIENT_BENEFITS_DIRECTION_DATA pbdd on pbd.PatientBenefitsDirectionID = pbdd.PatientBenefitsDirection 
				join TmpRR_DIRECTION rd on rd.ID = pbd.PatientBenefitsDirectionID
	        where pb.Patient = pbd.Patient
			and pbdd.ChangeType = 3
			and pb.Benefits = pbdd.Benefits 
			and (pb.EndDate is null or pb.EndDate >= now()::timestamp);
	
			
			update AKUZ.T_PATIENT_BENEFITS pb
	        set EndDate = pbd.Date
	        ,	Active = '0'
	        ,	Deleted = '1'
			from AKUZ.T_PATIENT_BENEFITS_DIRECTION pbd
				join TmpRR_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 pb.Patient = pbd.Patient
			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()::timestamp)
	        and b.BenefitType in (24207);
				-- Drop tables
			DROP TABLE If Exists TmpRR_DIRECTION;
		end if;
	End;
End;
$$;