-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-11-13
-- Description: Загрузка регистра региональных льготников.
-- =============================================
DROP PROCEDURE IF EXISTS sp_rr_patientbenefits;

CREATE PROCEDURE sp_rr_patientbenefits
(
	"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	
		_OperationType varchar(255);
	BEGIN
		"dateTime" := Coalesce("dateTime", Current_TimeStamp::TIMESTAMP);
	
		IF "Xml" is not null
		then 
			-- Получаем тип операции
			_OperationType := nullif(trim(xpath('/RR_PATIENT_BENEFIT/header[1]/TYPE[1]/text()', "Xml")::text,'{}'),'');
	
			-- Generate table #RR_PATIENT_BENEFITS
			drop table if exists Tmp_RR_PATIENT_BENEFITS;
			create temp table Tmp_RR_PATIENT_BENEFITS
			--(RrPatient int, DiagDate TimeStamp , BenefitCode varchar(255), BenefitViews int, Document varchar(16), BeginDate TimeStamp, EndDate TimeStamp, Mkb varchar(8), Lpu varchar(6));
			as
			SELECT	
				nullif(trim(xpath('/RR_PATIENT_BENEFIT/RrPatient[1]/text()', x)::text,'{}'),'')::int							as RrPatient
			,	to_timestamp(nullif(trim(xpath('/RR_PATIENT_BENEFIT/DiagDate[1]/text()', x)::text,'{}'),''),'DD.MM.YYYY')		as DiagDate
			,	nullif(trim(xpath('/RR_PATIENT_BENEFIT/Document[1]/text()', x)::text,'{}'),'')::varchar(16)						as Document
			,	to_timestamp(nullif(trim(xpath('/RR_PATIENT_BENEFIT/DocumentDate[1]/text()', x)::text,'{}'),''),'DD.MM.YYYY')	as DocumentDate
			,	nullif(trim(xpath('/RR_PATIENT_BENEFIT/Dop[1]/text()', x)::text,'{}'),'')::varchar(1)							as Dop
			,	nullif(trim(xpath('/RR_PATIENT_BENEFIT/Mkb[1]/text()', x)::text,'{}'),'')::varchar(8)							as Mkb
			,	to_timestamp(nullif(trim(xpath('/RR_PATIENT_BENEFIT/BeginDate[1]/text()', x)::text,'{}'),''),'DD.MM.YYYY')		as BeginDate
			,	to_timestamp(nullif(trim(xpath('/RR_PATIENT_BENEFIT/EndDate[1]/text()', x)::text,'{}'),''),'DD.MM.YYYY')		as EndDate
			,	nullif(trim(xpath('/RR_PATIENT_BENEFIT/BenefitCode[1]/text()', x)::text,'{}'),'')::varchar(255)					as BenefitCode
			,	nullif(trim(xpath('/RR_PATIENT_BENEFIT/BenefitViews[1]/text()', x)::text,'{}'),'')::int							as BenefitViews
			,	nullif(trim(xpath('/RR_PATIENT_BENEFIT/Lpu[1]/text()', x)::text,'{}'),'')::varchar(6)							as Lpu
			,	to_timestamp(nullif(trim(xpath('/RR_PATIENT_BENEFIT/LastEditDate[1]/text()', x)::text,'{}'),''),'DD.MM.YYYY')	as LastEditDate
			,	nullif(trim(xpath('/RR_PATIENT_BENEFIT/Post[1]/text()', x)::text,'{}'),'')::varchar(10)							as Post
			,	nullif(trim(xpath('/RR_PATIENT_BENEFIT/DivisionManager[1]/text()', x)::text,'{}'),'')::varchar(10)				as DivisionManager
			from
			(
				select	unnest(xpath('/RR_PATIENT_BENEFIT/body/RR_PATIENT_BENEFITS/RR_PATIENT_BENEFIT', x)) as x
				from 
				(
					select "Xml" as x
				) root
			) RR_PATIENT_BENEFIT
			where _OperationType = 'FTLoad';
			
			-- Обработка операции	
			if _OperationType = 'FTLoad'
			then		
	            delete from akuz.T_PATIENT_BENEFITS pb 
					USING akuz.T_BENEFITS b
				where b.BenefitsID = pb.Benefits and b.BenefitViews in (19002,19003,19005);
	
				INSERT INTO AKUZ.T_PATIENT_BENEFITS
                       (PatientBenefitsID ,Patient ,Benefits ,BenefitDocumentNumber ,BeginDate ,EndDate ,Mkb ,Active
                       ,Deleted ,CreateType ,LPU_INF ,RegDate, PAC_ID)
				SELECT
				           uuid_generate_v4()--<PatientBenefitsID, uniqueidentifier,>
				           ,p.PatientID--<Patient, uniqueidentifier,>
				           ,b.BenefitsID--<Benefits, uniqueidentifier,>
				           ,npb.Document--<BenefitDocumentNumber, varchar(255),>
				           ,npb.BeginDate--<BeginDate, datetime,>
				           ,npb.EndDate--<EndDate, datetime,>
				           ,(select m.MkbID from AKUZ.T_MKB m where m.MCBCode = npb.Mkb limit 1)--<Mkb, uniqueidentifier,>
				           ,'1'--<Active, bit,>
				           ,'0'--<Deleted, bit,>
				           ,2--<CreateType, int,>
				           ,npb.Lpu--<LPU_INF, varchar(6),>
				           ,"dateTime"--<RegDate, datetime,>)
						   ,npb.RrPatient
				from Tmp_RR_PATIENT_BENEFITS npb
				    join AKUZ.T_RR_PATIENT rp on rp.PAC_ID = npb.RrPatient
				    join AKUZ.T_PATIENT p on p.PatientID = rp.Patient
				    join VCLib.T_ENUMS eBv on eBv.Code = npb.BenefitViews and eBv.EnumTypeID = 190
				    join AKUZ.T_BENEFITS b on b.BenefitCode = npb.BenefitCode and b.BenefitViews = eBv.EnumID;

			END if;

	        DROP TABLE if exists Tmp_RR_PATIENT_BENEFITS;
		END if;
	End;
End;
$$;