DROP PROCEDURE IF EXISTS sp_importevents;

CREATE PROCEDURE sp_importevents
(
	"VisitXml" text
,	"CaseFlag" boolean
,   INOUT Result_Record Record
)
    language plpgsql
as
$$
Begin

    DECLARE	    xmlVar xml;
    			CodeLpuVar varchar(6);
    			CaseID UUID;
    			EnableUpdate boolean;
    			VisitIDVar UUID;
    			temp1 varchar(36);
    			LpuErrors varchar(100);
    			di_num int;
    			Act xml;
    			LpuCodeVar varchar(16);
    			tempVar UUID;
    			ourLpuCode varchar(4);
    			cntVar int;
    			num_ev int;
    			num_di int;
    			LpuErr varchar(100);
    Begin
    
        if "CaseFlag" = '1'	-- Новая интеграция с Волга Мед.
        then
        	xmlVar := "VisitXml" ;
        
        	CodeLpuVar := nullif(trim(xpath('/import_talons_flagman/@cod_LPU',xmlVar)::text,'{}'),'');
        	--получение идентификатора случая--
        	caseId := nullif(trim(xpath('/import_talons_flagman/diags/@CaseID',xmlVar)::text,'{}'),'')::UUID;
        
        	EnableUpdate := '0';
        	--получение идентификатора посещения--
        	temp1 := nullif(trim(xpath('/import_talons_flagman/diags/first_visit/@VisitID',xmlVar)::text,'{}'),'');
        	
         	if temp1 <> '' -- Редактировать посещение 
         	then
         		VisitIdVar := nullif(trim(xpath('/import_talons_flagman/diags/first_visit/@VisitID',xmlVar)::text,'{}'),'')::UUID;
         		EnableUpdate := '1';
         	else -- Создавать новое
         		VisitIDVar := gen_random_uuid();
         	end if;
        ---------------------------------------------
        	drop table if exists Visit;	
         	create temp table Visit
         	(   event_id uuid,
                fv_id uuid,
                MedRec_id uuid,
                d_cel int,
                d_amb_kart varchar(32),
                d_diag_date TimeStamp,
                d_closing_date TimeStamp,
                fv_cel int,
                fv_ishod int,
                fv_date_tal TimeStamp,
                fv_cod_vr varchar(7),
                fv_MKB varchar(15),
                fv_harak varchar(1),
                fv_UET decimal(15,2),
                fv_result int,
                fv_rslt int,
                num int GENERATED ALWAYS AS IDENTITY);
         	--as
         	insert into Visit(event_id, fv_id, MedRec_id, d_cel, d_amb_kart, d_diag_date, d_closing_date, fv_cel, fv_ishod, fv_date_tal, fv_cod_vr, fv_MKB, fv_harak, fv_UET, fv_result, fv_rslt)
         	select	CaseID as event_id
         	,	VisitIDVar as fv_id
         	,	gen_random_uuid() as MedRec_id
         	,	nullif(trim(xpath('./@cel', root1)::text,'{}'),'')::int AS d_cel
         	,	nullif(trim(xpath('./@amb_kart', root1)::text,'{}'),'')::varchar(32) AS d_amb_kart
         	,	to_timestamp(nullif(trim(xpath('./@diag_date', root1)::text,'{}'),''),'YYYYMMDD') AS d_diag_date
         	,	to_timestamp(nullif(trim(xpath('./@closing_date', root1)::text,'{}'),''),'YYYYMMDD') AS d_closing_date
         	,	nullif(trim(xpath('./@cel', root2)::text,'{}'),'')::int AS fv_cel
         	,	nullif(trim(xpath('./@ishod', root2)::text,'{}'),'')::int AS fv_ishod
         	,	to_timestamp(nullif(trim(xpath('./@date_tal', root2)::text,'{}'),''),'YYYYMMDD') AS fv_date_tal
         	,	nullif(trim(xpath('./@cod_vr', root2)::text,'{}'),'')::varchar(7) AS fv_cod_vr
         	,	nullif(trim(xpath('./@MKB', root2)::text,'{}'),'')::varchar(15) AS fv_MKB
         	,	nullif(trim(xpath('./@harak', root2)::text,'{}'),'')::int AS fv_harak
         	,	nullif(trim(xpath('./@UET', root2)::text,'{}'),'')::decimal(15,2) AS fv_UET
         	,	nullif(trim(xpath('./@result', root2)::text,'{}'),'')::int AS fv_result
         	,	nullif(trim(xpath('./@rslt', root2)::text,'{}'),'')::int AS fv_rslt
         	from
         	(
         		select	root1,
         			unnest(xpath('./*', root1)) as root2
         		from 
         		(	
         			select unnest(xpath('/import_talons_flagman/*',x)) as root1
         			from
         			(
         				select xmlVar as x
         			) q
         		) q2
         		where trim(xpath('local-name(.)',root1)::text,'{}') = 'diags'
         	) q3
         	where trim(xpath('local-name(.)',root2)::text,'{}') = 'first_visit'
         	Order by d_amb_kart;
        
         	drop table if exists Events1;	
         	create temp table Events1(id UUID, cel int, amb_kart varchar(32), diag_date TimeStamp, closing_date TimeStamp);
         	insert into Events1(id, cel, amb_kart, diag_date, closing_date)
         	select event_id, d_cel, d_amb_kart, d_diag_date, d_closing_date
         	from Visit;
         
         	drop table if exists EventErrors;	
         	create temp table EventErrors(d_id UUID, errorText varchar(100));
         	drop table if exists VisitErrors;	
         	create temp table VisitErrors(d_id UUID, fv_id UUID, errorText varchar(100));
         	
         	if not exists (select lm.LpuCode from akuz.T_LPU_MAIN lm where lm.LpuCode = CodeLpuVar)
         	then
         		LpuErrors := '<Error>Неизвестный код cod_LPU</Error>' ;
         	else
         		LpuErrors := '';
         	end if;
        
         	if CodeLpuVar is null
         	then
         		LpuErrors := '<Error>Не найден cod_LPU</Error>';
         	end if;
         
         	insert into EventErrors(d_id, errorText)
          	select
          		E.id
          	,	'Такого CaseID не существует.'
          	from Events1 E
          	where not exists
          	(
          		select EventID 
          		from akuz.T_EVENT_BASE eb 
          		join Events1 I on I.id = eb.EventID
          	);
          
          	insert into EventErrors(d_id, errorText)
          	select
          		E.id
          	,	'Поле cel должно быть в диапазоне от 1 до 7'
          	from Events1 E
          	where E.cel < 1 and E.cel > 7;
          
          	insert into EventErrors(d_id, errorText)
          	select
          		E.id
          	,	'В этом ЛПУ нет карты с таким номером'
          	from Events1 E
          	where not exists 
          	(
          		select ac.AmbulanceCardID 
          		from akuz.T_AMBULANCE_CARD ac 
          		join 
          			akuz.T_LPU_MAIN lm 
          			on lm.LpuID = ac.CardLpu  
          		where E.amb_kart = ac.Number 
          		and lm.LpuCode = CodeLpuVar
          	);
          
          	insert into EventErrors(d_id, errorText)
          	select
          		E.id
          	,	'Дата посещения должна быть больше или равна дате случая'
          	from Events1 E
          	where exists 
          	(
          		select I.diag_date, F.fv_date_tal 
          		from Events1 I 
          		join 
          			Visit F 
          			on F.event_id = I.id  
          		where I.diag_date > F.fv_date_tal
          	);
          
          	insert into VisitErrors(d_id, fv_id, errorText)
          	select
          		fv.event_id
          	,	fv.fv_id
          	,	'Для поля cel нет соответствующей записи в таблице akuz.T_VISIT_PURPOSE'
          	from Visit fv 
          	where not exists 
          	(
          		select VisitPurposeID from akuz.T_VISIT_PURPOSE vp where vp.Code = fv.fv_cel
          	);
          
          	insert into VisitErrors(d_id, fv_id, errorText)
          	select
          		fv.event_id
          	,	fv.fv_id
          	,	'В этом ЛПУ не найден врач с таким кодом'
          	from Visit fv 
          	where not exists 
          	(
          		select p.ResourceID 
          		from 
          		(
          			(
          				(
          					akuz.T_POST p 
          					join 
          						akuz.T_RESOURCE r 
          						on p.ResourceID=r.ResourceID
          				)
          				join 
          					akuz.T_DIVISION d 
          					on d.DivisionID = Division
          			) 
          			join 
          				akuz.T_BRANCH b 
          				on Branch = b.LpuID
          		)
          		join 
          			akuz.T_LPU_MAIN L 
          			on LpuMain = l.LpuID 
          		where l.LpuCode = CodeLpuVar 
          		and fv.fv_cod_vr = p.Code 
          		and DateBegin <= fv.fv_date_tal 
          		and (fv.fv_date_tal<=DateEnd or DateEnd is NULL)
          	);
          
          	insert into VisitErrors(d_id, fv_id, errorText)
          	select
          		fv.event_id
          	,	fv.fv_id
          	,	'Поле harak должно быть в диапазоне от 1 до 7'
          	from Visit fv 
          	where fv.fv_harak not between 1 and 7;
          
          	insert into VisitErrors(d_id, fv_id, errorText)
          	select
          		fv.event_id
          	,	fv.fv_id
          	,	'Поле ishod должно быть в диапазоне от 301 до 305'
          	from Visit fv 
          	where fv.fv_ishod not between 301 and 305;
          
          	insert into VisitErrors(d_id, fv_id, errorText)
          	select
          		fv.event_id
          	,	fv.fv_id
          	,	'Поле rslt должно быть в диапазоне от 301 до 313'
          	from Visit fv 
          	where fv.fv_rslt not between 301 and 313;
          
          	insert into VisitErrors(d_id, fv_id, errorText)
          	select
          		fv.event_id
          	,	fv.fv_id
          	,	'Поле result должно быть в диапазоне от 10 до 24'
          	from Visit fv 
          	where fv.fv_result not between 10 and 24;
          
          	insert into VisitErrors(d_id, fv_id, errorText)
          	select
          		fv.event_id
          	,	fv.fv_id
          	,	'такой код MKB не найден'
          	from Visit fv 
          	where not exists 
          	(
          		select m.MkbID from akuz.T_MKB m where m.MCBCode = fv.fv_MKB
          	);
        
        	--xml'ка--
        	if (select count(*) from EventErrors) + (select count(*) from VisitErrors) > 0 or LpuErrors <> ''
         	then
         		if (select count(*) from EventErrors) + (select count(*) from VisitErrors) = 0
          		then	
          			select into 
          				Result_Record.lit, Result_Record.Errors
          				VisitIDVar, '<?xml version="1.0" encoding="Windows-1251"?>' || LpuErrors;
          			Return;
          		else
          			select into 
          				Result_Record.lit, Result_Record.Errors
          				VisitIDVar
          			,	'<?xml version="1.0" encoding="Windows-1251"?>' || LpuErrors || 
          			(
          				select
          				xmlElement
          				(
          					name errorlist
          				,	xmlagg
          					(
          						xmlelement
          						(
          							name diag
          						,	xmlattributes
          							(
          								E.cel as cel
          							,	E.amb_kart as amb_kart
          							,	E.diag_date as diag_date
          							)
          						,	(
          								select 
          								xmlforest
          								(
          									xmlagg(xmlforest(ED.errorText as "Error")) as "Errors"
          								)
          								From EventErrors ED
          								where ED.d_id = E.id
          							)
          						,	(
          							select 
          							xmlagg
          							(
          								XMLELEMENT
          								(
          									name "First_visit"
          								,	XMLATTRIBUTES
          									(
          										F.fv_cel as cel 
          									, 	F.fv_ishod as ishod
          									, 	F.fv_rslt as rslt
          									, 	F.fv_result as result
          									, 	F.fv_UET as "UET"
          									, 	F.fv_harak as harak
          									, 	F.fv_MKB as "MKB"
          									, 	F.fv_cod_vr as cod_vr
          									, 	F.fv_date_tal as date_tal
          									)
          								,	(
          										select 
          										xmlforest
          										(
          											xmlagg(xmlforest(EF.errorText as "Error")) as "Errors"
          										)
          										from VisitErrors EF
          										where EF.fv_id = F.fv_id
          									)
          								)
          							)
          							From Visit F 
          							where F.event_id = E.id
          						)
          						)
          					)
          				)
          				from Events1 e
          				where exists 
          				(
          					select * From EventErrors e0 where e0.d_id = e.id
          				)
          				or
          				exists 
          				(
          					select * From VisitErrors e1 where e1.d_id = e.id
          				) 
          			);			
          			Return;
          		end if;	
         ---------------------------------------------
         	else
          		--количество первых посещений--
          		--declare @cnt int
          		--select @cnt = count(*) from @Event
          		--Number для event
          		--declare @ev_num int
          		--exec @ev_num = [dbo].[public.sp_GenerateNumber] 524, @CodeLpu, 1
          		--select @num
          		---------------------------------------------
          		--select @cnt = count(*) from @Visit
          		--Number для event
          		--exec @di_num = [dbo].[public.sp_GenerateNumber] 545, @CodeLpu, 1
          		di_num := public.sp_GenerateNumber(545, CodeLpuVar, 1);
          ---------------------------------------------
          		if EnableUpdate = '0'
          		then
          			if (select V.d_closing_date from Visit V) is not null -- если случай закрыт
          			then
          				UPDATE akuz.T_EVENT_BASE EB
          				SET  CloseDate = I.closing_date
          				FROM Events1 I
          				WHERE EB.EventID = I.id;
          		
          				insert into akuz.T_MEDICAL_RECORD(MedicalRecordID, Post, type_,  RecordTime, AmbulanceCard, AmbulanceEvent, PostingDate)
          				select	I.MedRec_id
          				, 
          				(
          					select p.ResourceID 
          					from 
          					(
          						(
          							(
          								akuz.T_POST p 
          								join akuz.T_RESOURCE r on p.ResourceID = r.ResourceID
          							) 
          							join akuz.T_DIVISION d on d.DivisionID = Division
          						) 
          						join akuz.T_BRANCH b on Branch = b.LpuID
          					)
          					join akuz.T_LPU_MAIN L on LpuMain = l.LpuID 
          					where l.LpuCode = CodeLpuVar 
          					and fv_cod_vr = p.Code 
          					and r.DateBegin <= i.fv_date_tal 
          					and 
          					(
          						i.fv_date_tal<=r.DateEnd or r.DateEnd is NULL
          					)
          					order by DateBegin desc
          					limit 1
          				)
          				,	667
          				,	
          				(
          					select d_closing_date from Visit
          				),
          				(
          					SELECT ac.AmbulanceCardID 
          					FROM akuz.T_AMBULANCE_CARD ac
          					where ac.Number = I.d_amb_kart 
          					and (Select LpuCode From akuz.T_LPU_MAIN Where LpuID = ac.CardLpu limit 1) = CodeLpuVar
          					limit 1
          				)
          				,	I.event_id
          				,	I.d_diag_date
          				from Visit I;
          
          				insert into akuz.T_OUTCOME_OF_INSPECTION(MedicalRecordID , ActiveFindDiagnosis, MKBCode, SicknessMode)
          				SELECT	I.MedRec_id
                        , '0'
          				,	I.fv_MKB
          				,	(select e.EnumID from vclib.T_ENUMS e where e.Code = I.fv_harak and e.EnumTypeID = 118)--правильно ли сработано с Enums
          				FROM Visit I;
          
          				insert into akuz.T_OUTCOM_OF_INSPECTION( MedicalRecordID, Outcome)
          				select
                            I.MedRec_id
          				,	(select e.EnumID from vclib.T_ENUMS e where e.Code = I.fv_result and e.EnumTypeID = 191)
          				FROM Visit I;
          
          				insert into akuz.T_VISIT(VisitID, VisitDate, Quantity, Inputdate, Post, AmbulanceEvent, VisitMark, VisitLocation, Rslt, Ishod, VisitPurpose, OutcomeRecord, type_, PaymentType, MedicalService)
          				SELECT	I.fv_id
          				,	I.fv_date_tal
          				,	I.fv_UET
          				,	Current_TimeStamp::TIMESTAMP
          				,
          				(
          					select p.ResourceID 
          					from --тут бы join проверить
          					(
          						(
          							(
          								akuz.T_POST p 
          								join akuz.T_RESOURCE r on p.ResourceID=r.ResourceID
          							) 
          							join akuz.T_DIVISION d on d.DivisionID = Division
          						) 
          						join akuz.T_BRANCH b on Branch = b.LpuID
          					)
          					join akuz.T_LPU_MAIN L on LpuMain = l.LpuID
          					where l.LpuCode = CodeLpuVar 
          					and fv_cod_vr = p.Code 
          					and r.DateBegin <= i.fv_date_tal 
          					and 
          					(
          						i.fv_date_tal <= r.DateEnd or r.DateEnd is NULL
          					)
          					order by DateBegin desc
          					limit 1
          				)
          				,	I.event_id
          				,	1
          				,	1
          				,	I.fv_rslt
          				,	I.fv_ishod
          				,	
          				(
          					select VisitPurposeID from akuz.T_VISIT_PURPOSE vp where vp.Code = I.fv_cel order by vp.PurposeType limit 1
          				)
          				,	I.MedRec_id
          				,	505
          				,	12601			
          				,	case	when	DATE_PART
         							(	'year'
         							,	
         								(
          									SELECT p.Birthday 
          									FROM akuz.T_AMBULANCE_CARD ac
          									join 
          										akuz.T_PATIENT p 
          										on ac.Patient=p.PatientID 
          									where ac.Number = I.d_amb_kart 
          									and ( Select LpuCode From akuz.T_LPU_MAIN Where LpuID = ac.CardLpu limit 1) = CodeLpuVar
          									limit 1
         								)
         							)					
         							-
         							DATE_PART
         							(	'year'
         							,	Current_TimeStamp::TIMESTAMP
         							) < 18 
          						and exists
          						(
          							select * from akuz.T_LPU_MAIN l 
          							join 
          								akuz.T_BRANCH br 
          								on l.LpuID = br.LpuMain 
          							where br.Category = 4 
          							and l.LpuCode = CodeLpuVar
          						)
          						then 'B69E3D1C-A50C-4AD6-A756-8B1409E2C481'::UUID --  U8905
          						when	DATE_PART
         							(	'year'
         							,	(
          									SELECT p.Birthday 
          									FROM akuz.T_AMBULANCE_CARD ac
          									join 
          										akuz.T_PATIENT p 
          										on ac.Patient = p.PatientID 
          									where ac.Number = I.d_amb_kart 
          									and ( Select LpuCode From akuz.T_LPU_MAIN Where LpuID = ac.CardLpu limit 1) = CodeLpuVar
          									limit 1 
          								)
         							)
         							- 
         							DATE_PART
         							(	'year'
         							,	Current_TimeStamp::TIMESTAMP
         							) >= 18
          						and exists
          						(
          							select * from akuz.T_LPU_MAIN l 
          							join 
          								akuz.T_BRANCH br 
          								on l.LpuID = br.LpuMain 
          							where br.Category = 4 
          							and l.LpuCode = CodeLpuVar
          						) 
          						then '1E2F2F5A-5257-4C25-88FB-FE2E05FEAB42'::UUID --U8901
          						else null
          					end 
          				From Visit I; 
          			else -- если случай не закрыт 			
          				insert into akuz.T_VISIT(VisitID, VisitDate, Quantity, Inputdate, Post, AmbulanceEvent, VisitMark, VisitLocation, Rslt, Ishod, VisitPurpose, type_, PaymentType, MedicalService)
          				SELECT	I.fv_id
          				,	I.fv_date_tal
          				,	I.fv_UET
          				,	Current_TimeStamp::TIMESTAMP
          				,
          				(
          					select p.ResourceID 
          					from 
          					(
          						(
          							(
          								akuz.T_POST p 
          								join akuz.T_RESOURCE r on p.ResourceID=r.ResourceID
          							) 
          							join akuz.T_DIVISION d on d.DivisionID = Division
          						) 
          						join akuz.T_BRANCH b on Branch = b.LpuID
          					)
          					join akuz.T_LPU_MAIN L on LpuMain = l.LpuID 
          					where l.LpuCode = CodeLpuVar 
          					and fv_cod_vr = p.Code 
          					and r.DateBegin <= i.fv_date_tal 
          					and 
          					(
          						i.fv_date_tal<=r.DateEnd or r.DateEnd is NULL
          					)
          					order by DateBegin desc
          					limit 1
          				)
          				,	I.event_id
          				, 	1
          				, 	1
          				, 	I.fv_rslt
          				, 	I.fv_ishod
          				, 	( select VisitPurposeID from akuz.T_VISIT_PURPOSE vp where vp.Code = I.fv_cel order by vp.PurposeType limit 1)
          				, 	505
          				, 	12601
          				,	case	when	DATE_PART
         							(	'year'
         							,	(
          									SELECT p.Birthday 
          									FROM akuz.T_AMBULANCE_CARD ac
          									join 
          										akuz.T_PATIENT p 
          										on ac.Patient=p.PatientID 
          									where ac.Number = I.d_amb_kart 
          									and ( Select LpuCode From akuz.T_LPU_MAIN Where LpuID = ac.CardLpu limit 1) = CodeLpuVar
          									limit 1
          								)
         							)
         							-
         							DATE_PART
         							(	'year'
         							,	Current_TimeStamp::TIMESTAMP
         							) < 18
          						and exists
          						(
          							select * from akuz.T_LPU_MAIN l 
          							join 
          								akuz.T_BRANCH br 
          								on l.LpuID = br.LpuMain 
          							where br.Category = 4 
          							and l.LpuCode = CodeLpuVar
          						)
          						then	'B69E3D1C-A50C-4AD6-A756-8B1409E2C481'::UUID --  U8905
          						when	DATE_PART
         							(	'year'
         							,	(
          									SELECT p.Birthday 
          									FROM akuz.T_AMBULANCE_CARD ac
          									join 
          										akuz.T_PATIENT p 
          										on ac.Patient=p.PatientID 
          									where ac.Number = I.d_amb_kart 
          									and ( Select LpuCode From akuz.T_LPU_MAIN Where LpuID = ac.CardLpu limit 1) = CodeLpuVar
          									limit 1
          								)
         							)
         							- 
         							DATE_PART
         							(	'year'
         							,	Current_TimeStamp::TIMESTAMP
         							) >= 18 
          						and exists
          						(
          							select * from akuz.T_LPU_MAIN l 
          							join 
          								akuz.T_BRANCH br 
          								on l.LpuID = br.LpuMain 
          							where br.Category = 4 
          							and l.LpuCode = CodeLpuVar
          						) 
          						then	'1E2F2F5A-5257-4C25-88FB-FE2E05FEAB42'::UUID --U8901
          						else	null
          					end 
          				From Visit I;
          			end if;	
          			insert into akuz.T_DOCTOR_INSPECTION(
                    VisitID, 
                    Number
                    , LastVisit
                    , VisitCount)
          			SELECT	I.fv_id
          			,	I.num + di_num - 1
                    , I.fv_id
                    , 1
          			FROM Visit I;
          
          			insert into akuz.T_AMBULANCE_DOCTOR_INSPECTION(VisitID, EntryType)
          			select	I.fv_id
          			,	12201
          			from Visit I;

                    declare
                    viz_cur CURSOR FOR SELECT fv_id FROM Visit;
                    _DoctorInspection uuid;
                    begin
                        OPEN viz_cur;
                        LOOP 
                        FETCH viz_cur INTO _DoctorInspection;
                        IF NOT FOUND THEN EXIT;END IF;
                        
                        call sp_UpdateLastVisit(_DoctorInspection);

                        END LOOP;
                        CLOSE viz_cur;
                    end;

          		else
          			UPDATE akuz.T_EVENT_BASE EB
          			SET	EventPerpose = (select e.EnumID from vclib.T_ENUMS e where e.Code = I.cel and e.EnumTypeID = 108)
          			,	EventDate = I.diag_date
          			,	CloseDate = I.closing_date
          			FROM Events1 I
          			WHERE EB.EventID = I.id;
          	
          			UPDATE akuz.T_MEDICAL_RECORD MR
          			SET	Post = (
          						select p.ResourceID 
          						from 
          						(
          							(
          								(
          									akuz.T_POST p 
          									join akuz.T_RESOURCE r on p.ResourceID = r.ResourceID
          								) 
          								join akuz.T_DIVISION d on d.DivisionID = Division
          							) 
          							join akuz.T_BRANCH b on Branch = b.LpuID
          						)
          						join akuz.T_LPU_MAIN L on LpuMain = l.LpuID 
          						where l.LpuCode = CodeLpuVar 
          						and fv_cod_vr = p.Code 
          						and r.DateBegin <= i.fv_date_tal 
          						and 
          						(
          							i.fv_date_tal<=r.DateEnd or rDateEnd is NULL
          						)
          						order by r.DateBegin desc
          						limit 1
          					)
          			,	RecordTime = (select d_closing_date from Visit)
                    ,   PostingDate = I.d_diag_date
          			FROM Visit I  
          			    join akuz.T_VISIT V on V.OutcomeRecord = MR.MedicalRecordID
          			WHERE MR.MedicalRecordID = V.OutcomeRecord
          			and I.MedRec_id = MR.MedicalRecordID;
          
          			UPDATE	akuz.T_OUTCOME_OF_INSPECTION OI
          			SET	OI.MKBCode = I.fv_MKB
          			,	OI.SicknessMode = (select e.EnumID from vclib.T_ENUMS e where e.Code = I.fv_harak and e.EnumTypeID = 118)
          			FROM Visit I 				
          			    join akuz.T_VISIT V on V.OutcomeRecord = OI.MedicalRecordID
          			WHERE I.MedRec_id = OI.MedicalRecordID  
          			and OI.MedicalRecordID = V.OutcomeRecord;
          
          			UPDATE akuz.T_OUTCOM_OF_INSPECTION OI 
          			SET Outcome = (select e.EnumID from vclib.T_ENUMS e where e.Code = I.fv_result and e.EnumTypeID = 191)
          			FROM Visit I
          			    join akuz.T_VISIT V on V.OutcomeRecord = OI.MedicalRecordID
          			WHERE I.MedRec_id = OI.MedicalRecordID 
          			and OI.MedicalRecordID = V.OutcomeRecord;
          
          			UPDATE akuz.T_VISIT V
          			SET	VisitDate = I.fv_date_tal
          			,	Quantity = I.fv_UET
          			,	Inputdate = Current_TimeStamp::TIMESTAMP
          			,	Post = (
          						select p.ResourceID 
          						from 
          						(
          							(
          								(
          									akuz.T_POST p 
          									join akuz.T_RESOURCE r on p.ResourceID=r.ResourceID
          								) 
          								join akuz.T_DIVISION d on d.DivisionID = Division
          							)
          							join akuz.T_BRANCH b on Branch = b.LpuID
          						)
          						join akuz.T_LPU_MAIN L on LpuMain = l.LpuID
          						where l.LpuCode = CodeLpuVar 
          						and fv_cod_vr = p.Code 
          						and r.DateBegin <= i.fv_date_tal 
          						and 
          						(
          							i.fv_date_tal<=r.DateEnd or r.DateEnd is NULL
          						)
          						order by r.DateBegin desc
          						limit 1
          					)
          			,	Rslt = I.fv_rslt
          			,	Ishod = I.fv_ishod
          			,	VisitPurpose = (select VisitPurposeID from akuz.T_VISIT_PURPOSE vp where vp.Code=I.fv_cel order by vp.PurposeType limit 1)
          			FROM Visit I 
          			WHERE I.fv_id = V.VisitID; 
          		end if;
         	end if;
        
        	select into 
        		Result_Record.lit, Result_Record.Errors
        		VisitIDVar, '';
        --RAISE NOTICE 'a2 = a2, %', Result_Record.lit; 		
        	Return;
        else				-- Старая интеграция с Волга Мед.
          	Act := "VisitXml" ;
          
          	--получение кода лпу--
          	CodeLpuVar := nullif(trim(xpath('/import_talons_flagman/@cod_LPU',Act)::text,'{}'),'');	
          	---------------------------------------------
          	--случаи--
          	drop table if exists Events2;	
          	create temp table Events2(
                VitaCase uuid,
                FlagmanCase int,
                id uuid,
                IsOld boolean,
                cel int,
                amb_kart varchar(32),
                diag_date datetime,
                num int GENERATED ALWAYS AS IDENTITY,
                cod_vr varchar(7),
                diag_date_zak datetime);	

          	--первые посещения--
          	drop table if exists FirstVisits;	
          	create temp table FirstVisits(
                event_id uuid,
                fv_id uuid,
                MedRec_id uuid,
                FlagmanCase int,
                d_cel int,
                d_amb_kart varchar(32),
                d_diag_date datetime,
                d_cod_vr varchar(7),
                id int,
                fv_cel int,
                fv_ishod int,
                fv_date_tal datetime,
                fv_cod_vr varchar(7),
                fv_MKB varchar(15),
                fv_harak varchar(1),
                fv_UET decimal(15,2),
                fv_result int,
                fv_rslt int,
                num int GENERATED ALWAYS AS IDENTITY,
                fv_usls text,
                place_ob int);

          	drop table if exists Usls;	
          	create temp table Usls(
                 id int
                ,Code varchar(64)
                ,Uet decimal(12,2)
                ,Cnt int
                ,VisitID uuid
                ,MedicalServiceID uuid
                ,MedicalRecordID uuid
                ,ToothNumber int);
          
          	INSERT INTO FIRSTVISITS(EVENT_ID, FV_ID, MEDREC_ID,FlagmanCase, D_CEL, D_AMB_KART, D_DIAG_DATE, D_COD_VR, ID, FV_CEL, FV_ISHOD, FV_DATE_TAL, FV_COD_VR, FV_MKB, FV_HARAK, FV_UET, FV_RESULT, FV_RSLT, FV_USLS, PLACE_OB)
           	SELECT	NULL AS EVENT_ID
           	,	gen_random_uuid() AS FV_ID
           	,	gen_random_uuid() AS MEDREC_ID
           	,	NULLIF(TRIM(XPATH('./@FlagmanCase', ROOT1)::TEXT,'{}'),'')::INT AS FlagmanCase
           	,	NULLIF(TRIM(XPATH('./@CEL', ROOT1)::TEXT,'{}'),'')::INT AS D_CEL
           	,	NULLIF(TRIM(XPATH('./@AMB_KART', ROOT1)::TEXT,'{}'),'')::INT AS D_AMB_KART
           	,	TO_TIMESTAMP(NULLIF(TRIM(XPATH('./@DIAG_DATE', ROOT1)::TEXT,'{}'),''),'YYYYMMDD') AS D_DIAG_DATE	
           	,	NULLIF(TRIM(XPATH('./@COD_VR_ZAK', ROOT1)::TEXT,'{}'),'')::VARCHAR(7) AS D_COD_VR
           	,	NULLIF(TRIM(XPATH('./@ID', ROOT2)::TEXT,'{}'),'')::INT AS ID	
           	,	NULLIF(TRIM(XPATH('./@CEL', ROOT2)::TEXT,'{}'),'')::INT AS FV_CEL	
           	,	NULLIF(TRIM(XPATH('./@ISHOD', ROOT2)::TEXT,'{}'),'')::INT AS FV_ISHOD	
           	,	TO_TIMESTAMP(NULLIF(TRIM(XPATH('./@DATE_TAL', ROOT2)::TEXT,'{}'),''),'YYYYMMDD') AS FV_DATE_TAL	
           	,	NULLIF(TRIM(XPATH('./@COD_VR', ROOT2)::TEXT,'{}'),'')::VARCHAR(7) AS FV_COD_VR	
           	,	NULLIF(TRIM(XPATH('./@MKB', ROOT2)::TEXT,'{}'),'')::VARCHAR(15) AS FV_MKB	
           	,	NULLIF(TRIM(XPATH('./@HARAK', ROOT2)::TEXT,'{}'),'')::Int AS FV_HARAK
           	,	NULLIF(TRIM(XPATH('./@UET', ROOT2)::TEXT,'{}'),'')::DECIMAL(15,2) AS FV_UET
           	,	NULLIF(TRIM(XPATH('./@RESULT', ROOT2)::TEXT,'{}'),'')::INT AS FV_RESULT
           	,	NULLIF(TRIM(XPATH('./@RSLT', ROOT2)::TEXT,'{}'),'')::INT AS FV_RSLT
           	,	NULLIF(TRIM(XPATH('./@USLS', ROOT2)::TEXT,'{}'),'') AS FV_USLS
           	,	NULLIF(TRIM(XPATH('./@PLACE_OB', ROOT2)::TEXT,'{}'),'')::INT AS PLACE_OB
           	FROM
           	(
           		SELECT	ROOT1,
           			UNNEST(XPATH('./*', ROOT1)) AS ROOT2
           		FROM 
           		(	
           			SELECT UNNEST(XPATH('/IMPORT_TALONS_FLAGMAN/*',X)) AS ROOT1
           			FROM
           			(
           				SELECT ACT AS X
           			) Q
           		) Q2
           		WHERE TRIM(XPATH('LOCAL-NAME(.)',ROOT1)::TEXT,'{}') = 'DIAGS'		
           	) Q3
           	WHERE TRIM(XPATH('LOCAL-NAME(.)',ROOT2)::TEXT,'{}') = 'FIRST_VISIT'
           	ORDER BY D_AMB_KART;
          
           	insert into Usls(id, Code, Uet, Cnt, MedicalRecordID,ToothNumber)
           	select	nullif(trim(xpath('./@id', root1)::text,'{}'),'')::int AS id
           	,	nullif(trim(xpath('./uls_first_visit/@cod_usl', root1)::text,'{}'),'')::varchar(64) AS code
           	,	nullif(trim(xpath('./uls_first_visit/@usl_uet', root1)::text,'{}'),'')::decimal(12,2) AS uet
           	,	nullif(trim(xpath('./uls_first_visit/@usl_cnt', root1)::text,'{}'),'')::int AS cnt
           	,	nullif(trim(xpath('./uls_first_visit/@zub', root1)::text,'{}'),'')::int AS ToothNumber
           	,	gen_random_uuid() as MedRec_id
           	from
           	(
           		select unnest(xpath('/import_talons_flagman/diags/*',x)) as root1
           		from
           		(
           			select Act as x
           		) q		
           	) q3
           	where trim(xpath('local-name(.)',root1)::text,'{}') = 'first_visit';
          	
            ---------------------------------------------
            insert into Events2(id, IsOld, VitaCase, FlagmanCase, cel, amb_kart, diag_date, cod_vr, diag_date_zak)
           	select	
           		gen_random_uuid() as id
            ,   1 as IsOld
            ,	case when len(nullif(trim(xpath('./@VitaCase', root1)::text,'{}'),'')::varchar(255)) > 0 then nullif(trim(xpath('./@VitaCase', root1)::text,'{}'),'')::varchar(255) else null end as VitaCase
            ,   nullif(trim(xpath('./@FlagmanCase', root1)::text,'{}'),'')::int     AS FlagmanCase
           	,	nullif(trim(xpath('./@cel', root1)::text,'{}'),'')::int             AS cel
           	,	nullif(trim(xpath('./@amb_kart', root1)::text,'{}'),'')::int        AS amb_kart
           	,	TO_TIMESTAMP(NULLIF(TRIM(XPATH('./@diag_date', ROOT1)::TEXT,'{}'),''),'YYYYMMDD') AS diag_date	
           	,	nullif(trim(xpath('./@cod_vr', root1)::text,'{}'),'')::varchar(7)          AS cod_vr
           	,	TO_TIMESTAMP(NULLIF(TRIM(XPATH('./@diag_date_zak', ROOT1)::TEXT,'{}'),''),'YYYYMMDD') AS diag_date_zak	
           	from
           	(
           		select	root1,
           			unnest(xpath('./*', root1)) as root2
           		from 
           		(	
           			select unnest(xpath('/import_talons_flagman/*',x)) as root1
           			from
           			(
           				select Act as x
           			) q
           		) q2
           		where trim(xpath('local-name(.)',root1)::text,'{}') = 'diags'		
           	) q3;
          	
            update Events2 e
            set VitaCase = gen_random_uuid(), 
                IsOld ='0' -- случаи, созданные во Флагмане
            where e.VitaCase is null;
            
            update FirstVisits fv
            set event_id = e.id
            from Events2 e 
            where e.FlagmanCase = fv.FlagmanCase;

          	drop table if exists ReVisits;	
          	create temp table ReVisits(d_id UUID, fv_id UUID, re_id UUID, FlagmanCase int, id int, re_date_tal TimeStamp, re_cod_vr varchar(7), re_cel int, re_UET decimal(15,2), re_rslt int, re_ishod int,
          		re_usls text, p_cel int, p_diag_date TimeStamp, p_amb_cart varchar(32), place_ob int);	
           	insert into ReVisits(d_id, fv_id, re_id, id, re_date_tal, re_cod_vr, re_cel, re_UET, re_rslt, re_ishod,	re_usls, p_cel, p_diag_date, p_amb_cart, place_ob)
           	select	null as d_id
           	,	null as fv_id
           	, 	gen_random_uuid() as re_id
           	,	nullif(trim(xpath('./../@FlagmanCase', root2)::text,'{}'),'')::int AS FlagmanCase
           	,	nullif(trim(xpath('./@id', root2)::text,'{}'),'')::int AS id
           	,	to_timestamp(nullif(trim(xpath('./@date_tal', root2)::text,'{}'),''),'YYYYMMDD') AS re_date_tal		
           	,	nullif(trim(xpath('./@cod_vr', root2)::text,'{}'),'')::varchar(7) AS re_cod_vr	
           	,	nullif(trim(xpath('./@cel', root2)::text,'{}'),'')::int AS re_cel
           	,	nullif(trim(xpath('./@UET', root2)::text,'{}'),'')::decimal(15,2) AS re_UET
           	,	nullif(trim(xpath('./@rslt', root2)::text,'{}'),'')::int AS re_rslt
           	,	nullif(trim(xpath('./@ishod', root2)::text,'{}'),'')::int AS re_ishod
           	,	nullif(trim(xpath('./@usls', root2)::text,'{}'),'') AS re_usls
           	,	nullif(trim(xpath('./@cel', root1)::text,'{}'),'')::int AS p_cel
           	,	to_timestamp(nullif(trim(xpath('./@diag_date', root1)::text,'{}'),''),'YYYYMMDD') AS p_diag_date
           	,	nullif(trim(xpath('./@amb_kart', root1)::text,'{}'),'')::int AS p_amb_cart
           	,	nullif(trim(xpath('./@place_ob', root2)::text,'{}'),'')::int AS place_ob
           	from
           	(
           		select	root1,
           			unnest(xpath('./*', root1)) as root2
           		from 
           		(	
           			select unnest(xpath('/import_talons_flagman/*',x)) as root1
           			from
           			(
           				select Act as x
           			) q
           		) q2
           		where trim(xpath('local-name(.)',root1)::text,'{}') = 'diags'		
           	) q3
           	where trim(xpath('local-name(.)',root2)::text,'{}') = 'revisit'
           	and nullif(trim(xpath('./@date_tal', root1)::text,'{}'),'') is not null;
          
          	update ReVisits re
          	set	fv_id = fv.fv_id
          	,	d_id = fv.event_id
          	from FirstVisits fv  
          	where re.FlagmanCase = fv.FlagmanCase;
          
           	insert into Usls(id, Code, Uet, Cnt, MedicalRecordID, ToothNumber)
           	select	nullif(trim(xpath('./@id', root1)::text,'{}'),'')::int AS id
           	,	nullif(trim(xpath('./uls_revisit/@cod_usl', root1)::text,'{}'),'')::varchar(64) AS code
           	,	nullif(trim(xpath('./uls_revisit/@usl_uet', root1)::text,'{}'),'')::decimal(12,2) AS uet
           	,	nullif(trim(xpath('./uls_revisit/@usl_cnt', root1)::text,'{}'),'')::int AS cnt
           	,	gen_random_uuid() as MedRec_id
           	,	nullif(trim(xpath('./uls_revisit/@zub', root1)::text,'{}'),'')::int AS ToothNumber
           	from
           	(
           		select unnest(xpath('/import_talons_flagman/diags/*',x)) as root1
           		from
           		(
           			select Act as x
           		) q		
           	) q3
           	where trim(xpath('local-name(.)',root1)::text,'{}') = 'revisit';
          
          	drop table if exists ErrorsDiag;	
          	create temp table ErrorsDiag(d_id UUID, errorText varchar(100));
          	drop table if exists ErrorsFV;	
          	create temp table ErrorsFV(d_id UUID, fv_id UUID, errorText varchar(100));
          	drop table if exists ErrorsRe;	
          	create temp table ErrorsRe(d_id UUID, fv_id UUID, re_id UUID, errorText varchar(100));
          	
          	if not exists (select lm.TFomsCode from akuz.T_LPU_MAIN lm where lm.TFomsCode = LpuCodeVar) 
          	then
          		LpuErr := '<Error>Неизвестный код cod_LPU</Error>';
          	else
          		LpuErr := '';
          	end if;
          
          	if LpuCodeVar is null
          	then
          		LpuErr := '<Error>Не найден cod_LPU</Error>';
          	end if;

          	insert into ErrorsDiag
          	select	E.id
          	,	'Не найден случай обращения, открытый в Витакарте'
          	from Events2 E
          	where E.IsOld = '1' and not exists (select * from AKUZ.T_EVENT_BASE eb where eb.EventID = E.VitaCase);
          
          	insert into ErrorsDiag
          	select	E.id
          	,	'Поле cel должно быть в диапазоне от 1 до 7'
          	from Events2 E
          	where E.cel < 1 
          	and E.cel > 7;
          
          	insert into ErrorsDiag
          	select	E.id
          	,	'В этом ЛПУ нет карты с таким номером'
          	from Events2 E
          	where not exists 
          	(
          		select ac.AmbulanceCardID 
          		from akuz.T_AMBULANCE_CARD ac 
          		    join akuz.T_LPU_MAIN lm on lm.LpuID = ac.CardLpu  
          		where E.amb_kart = ac.Number and lm.TFomsCode = LpuCodeVar
          	);
          
          	insert into ErrorsFV
          	select	fv.event_id		   
          	,	fv.fv_id
          	,	'Дубликат FirstVisit'
          	from FirstVisits fv 
          	where exists
          	(
          		select v.VisitID 
          		from akuz.T_VISIT v 
          		where fv.event_id = v.AmbulanceEvent 
          		and v.VisitDate = fv.fv_date_tal 
          		and v.Rslt = fv.fv_rslt 
          		and v.Ishod = fv.fv_ishod 
          		and v.VisitPurpose  = 
          		(
          			select VisitPurposeID from akuz.T_VISIT_PURPOSE vp where vp.Code = fv.fv_cel order by vp.PurposeType limit 1
          		) 
          		and fv.fv_cod_vr = 
          		(
          			select p.Code from akuz.T_POST p where p.ResourceID = v.Post limit 1
          		) 
          		limit 1
          	);
          
          	insert into ErrorsFV
          	select	fv.event_id
          	,	fv.fv_id
          	,	'Для поля cel нет соответствующей записи в таблице akuz.T_VISIT_PURPOSE'
          	from FirstVisits fv 
          	where not exists 
          	(
          		select VisitPurposeID from akuz.T_VISIT_PURPOSE vp where vp.Code = fv.fv_cel
          	);
          
          	insert into ErrorsFV
          	select	fv.event_id
          	, 	fv.fv_id
          	,	'В этом ЛПУ не найден врач с таким кодом'
          	from FirstVisits fv 
          	where not exists 
          	(
          		select p.ResourceID 
          		from 
          		(
          			(
          				(
          					akuz.T_POST p 
          					join akuz.T_RESOURCE r on p.ResourceID = r.ResourceID
          				) 
          				join akuz.T_DIVISION d on d.DivisionID = Division
          			) 
          			join akuz.T_BRANCH b on Branch = b.LpuID
          		)
          		join akuz.T_LPU_MAIN L on LpuMain = l.LpuID
          		where l.TFomsCode = LpuCodeVar 
          		and fv.fv_cod_vr = p.Code 
          		and r.DateBegin <= fv.fv_date_tal 
          		and 
          		(
          			fv.fv_date_tal<=r.DateEnd or r.DateEnd is NULL
          		)
          	);
          
          	insert into ErrorsFV
          	select	fv.event_id
          	, 	fv.fv_id
          	,	'В этом ЛПУ не найден врач (закрывающий случай) с таким кодом'
          	from FirstVisits fv 
          	where not exists 
          	(
          		select p.ResourceID 
          		from 
          		(
          			(
          				(
          					akuz.T_POST p 
          					join akuz.T_RESOURCE r on p.ResourceID=r.ResourceID
          				) 
          				join akuz.T_DIVISION d on d.DivisionID = Division
          			) 
          			join akuz.T_BRANCH b on Branch = b.LpuID
          		)
          		join akuz.T_LPU_MAIN L on LpuMain = l.LpuID 
          		where l.TFomsCode = LpuCodeVar 
          		and fv.d_cod_vr = p.Code 
          		and r.DateBegin <= fv.fv_date_tal 
          		and 
          		(
          			fv.fv_date_tal <= r.DateEnd or r.DateEnd is NULL
          		)
          	);
          
          	insert into ErrorsFV
          	select	fv.event_id
          	, 	fv.fv_id
          	,	'Поле harak должно быть в диапазоне от 1 до 7'
          	from FirstVisits fv 
          	where fv.fv_harak not between 1 and 7;
          
          	insert into ErrorsFV
          	select	fv.event_id
          	, 	fv.fv_id
          	,	'Поле ishod должно быть в диапазоне от 301 до 305'
          	from FirstVisits fv 
          	where fv.fv_ishod  not between 301 and 305;
          
          	insert into ErrorsFV
          	select	fv.event_id
          	, 	fv.fv_id
          	,	'Поле rslt должно быть в диапазоне от 301 до 313'
          	from FirstVisits fv 
          	where fv.fv_rslt not between 301 and 313;
          
          	insert into ErrorsFV
          	select	fv.event_id
          	, 	fv.fv_id
          	,	'Поле result должно быть в диапазоне от 10 до 24'
          	from FirstVisits fv 
          	where fv.fv_result not between 10 and 24;
          
          	insert into ErrorsFV
          	select	fv.event_id
          	, 	fv.fv_id
          	,	'такой код MKB не найден'
          	from FirstVisits fv 
          	where not exists 
          	(
          		select m.MkbID from akuz.T_MKB m where m.MCBCode = fv.fv_MKB
          	);
          
          	insert into ErrorsFV
          	select	fv.event_id
          	, 	fv.fv_id
          	,	'Поле place_ob должно быть в диапазоне от 1 до 4'
          	from FirstVisits fv 
          	where fv.place_ob is null or fv.place_ob not between 1 and 4;
          
          	insert into ErrorsRe
          	select	re.d_id
          	, 	re.fv_id
          	, 	re.re_id
          	, 	'Поле rslt должно быть в диапазоне от 301 до 313'
          	from ReVisits re
          	where re.re_rslt < 301 and re.re_rslt > 313;
          
          	insert into ErrorsRe
          	select	re.d_id
          	, 	re.fv_id
          	, 	re.re_id
          	, 	'В этом ЛПУ не найден врач с таким кодом'
          	from ReVisits re
          	where not exists 
          	(
          		select p.ResourceID 
          		from 
          		(
          			(
          				(
          					akuz.T_POST p 
          					join akuz.T_RESOURCE r on p.ResourceID=r.ResourceID
          				) 
          				join akuz.T_DIVISION d on d.DivisionID = Division
          			) 
          			join akuz.T_BRANCH b on Branch = b.LpuID
          		)
          		join akuz.T_LPU_MAIN L on LpuMain = l.LpuID
          		where l.TFomsCode = LpuCodeVar 
          		and re_cod_vr = p.Code 
          		and r.DateBegin <= re.re_date_tal 
          		and 
          		(
          			re.re_date_tal <= r.DateEnd or r.DateEnd is NULL
          		)
          	);
          
          	insert into ErrorsRe
          	select	re.d_id
          	, 	re.fv_id
          	, 	re.re_id
          	, 	'Для поля cel нет соответствующей записи в таблице akuz.T_VISIT_PURPOSE'
          	from ReVisits re
          	where not exists 
          	(
          		select VisitPurposeID from akuz.T_VISIT_PURPOSE vp where vp.Code=re.re_cel
          	);
          
          	insert into ErrorsRe
          	select	re.d_id
          	, 	re.fv_id
          	, 	re.re_id
          	, 	'Поле ishod должно быть в диапазоне от 301 до 305'
          	from ReVisits re
          	where re.re_ishod not between 301 and 305;
          
          	insert into ErrorsRe
          	select	re.d_id
          	, 	re.fv_id
          	, 	re.re_id
          	,	'Поле place_ob должно быть в диапазоне от 1 до 4'
          	from ReVisits re
          	where re.place_ob is null 
          	or re.place_ob not between 1 and 4;
          
          	---------------------------------------------
          	--Услуги
          	update Usls
          	set VisitID = fv.fv_id
          	from FirstVisits fv 
          	where fv.id = usls.id;
          
          	update Usls
          	set VisitID = rv.re_id
          	from ReVisits rv 
          	where rv.id = usls.id;
          
          	update Usls
          	set MedicalServiceID = ms.MedicalServiceID
          	from akuz.T_MEDICAL_SERVICE ms 
          	where ms.Code = usls.Code;

            update Usls usls
            set usls.MedicalServiceID = ms.MedicalServiceID
            from Usls usls1
            left join FirstVisits fv on fv.id = usls.id
	        left join ReVisits rv on rv.id = usls.id
	        join lateral
	        	(
	        		select ms.MedicalServiceID
	        		from AKUZ.T_MEDICAL_SERVICE ms
	        		where ms.Code = usls.Code and ms.type_ =588 
	        			and ms.DateBegin <= now() and (ms.DateEnd >= coalesce(fv.fv_date_tal,rv.re_date_tal) or ms.DateEnd is null)
	        		order by ms.DateBegin desc, ms.DateEnd desc limit 1
	        	) ms on true
             where usls1.id = usls.id;
          
          	insert into ErrorsFV 
          	select	fv.event_id
          	, 	fv.fv_id
          	,	'код услуги первичного посещения не найден'
          	from FirstVisits fv 
          	join 
          		Usls 
          		on usls.VisitID = fv.fv_id
          	where usls.MedicalServiceID is null;
          
          	insert into ErrorsRe 
          	select	re.d_id
          	, 	re.fv_id
          	, 	re.re_id
          	,	'код услуги повторного посещения не найден'
          	from ReVisits re
          	join 
          		Usls 
          		on usls.VisitID = re.re_id
          	where usls.MedicalServiceID is null;

         	drop table if exists ErrorsUsls;	
         	create temp table ErrorsUsls
            (
            	eid uuid,
            	vid uuid,
            	Code varchar(255),
            	errorText varchar(100)
            );
            
            insert into ErrorsUsls 
            select
            	fv.event_id
            	,fv.fv_id
            	,usls.Code
            	,'код услуги первичного посещения не найден'
            from FirstVisits fv 
            	join Usls usls on usls.VisitID = fv.fv_id
            where usls.MedicalServiceID is null;
            
            insert into ErrorsUsls 
            select
            	re.d_id
            	,re.re_id
            	,usls.Code
            	,'код услуги повторного посещения не найден'
            from ReVisits re
            	join Usls usls on usls.VisitID = re.re_id
            where usls.MedicalServiceID is null;
          
          	--xml'ка--
          	if (select count(*) from ErrorsDiag) + (select count(*) from ErrorsFV) + (select count(*) from ErrorsRe)+(select count(*) from ErrorsUsls) > 0 or LpuErr <> ''
          	then
          		if (select count(*) from ErrorsDiag) + (select count(*) from ErrorsFV) + (select count(*) from ErrorsRe)+(select count(*) from ErrorsUsls) = 0
          		then
          			select into 
          				Result_Record.lit, Result_Record.Errors
          				'EventErrors', '<?xml version="1.0" encoding="Windows-1251"?>' || LpuErr;
          			Return;
          		else
          			select into 
          				Result_Record.lit, Result_Record.Errors
          				'EventErrors', '<?xml version="1.0" encoding="Windows-1251"?>' || LpuErr || 
          			(
          				select
          				xmlElement
          				(
          					name errorlist
          				,	xmlagg
          					(
          						xmlelement
          						(
          							name diag
          						,	xmlattributes
          							(
          								E.VitaCase as VitaCase
          								,E.FlagmanCase as FlagmanCase
          								,E.cel as cel
          							,	E.amb_kart as amb_kart
          							,	E.diag_date as diag_date
          							,	E.cod_vr as cod_vr_zak
          							)
          						,	(
          								select 
          								xmlforest
          								(
          									xmlagg(xmlforest(ED.errorText as "Error")) as "Errors"
          								)
          								From ErrorsDiag ED
          								where ED.d_id = E.id
          							),
                                    (
                                    
          								select 
          									XMLELEMENT(
                                            name "Usl"
                                            ,XMLFOREST
											   (
                                               xmlforest(
                                                     usls.Code as "@Code"
					                		        ,usls.Cnt as "@Cnt"
					                		        ,usls.Uet as "@Uet"
					                		        ,usls.ToothNumber as "@ToothNumer"
                                                    )as "UslsList"
                                                    )
                                                  )
					                	from Usls usls
					                	where usls.VisitID = F.fv_id
					                  )
					                ,(
                                        select 
          									XMLELEMENT(
                                            name "UslError"
                                            ,XMLFOREST
											   (
                                                xmlforest(
					                		        eu.Code as "@Code"
					                		        ,eu.errorText as "@Error"
                                                    )
                                                  as "UslsErrors"
                                                    )
                                                  )
					                	from ErrorsUsls eu
					                	where eu.vid = F.fv_id 
					                )
          						,	(
          							select 
          							xmlagg
          							(
          								XMLELEMENT
          								(
          									name "First_visit"
          								,	XMLATTRIBUTES
          									(
          										F.fv_cel as cel 
          									, 	F.fv_ishod as ishod
          									, 	F.fv_rslt as rslt
          									, 	F.fv_result as result
          									, 	F.fv_UET as "UET"
          									, 	F.fv_harak as harak
          									, 	F.fv_MKB as "MKB"
          									, 	F.fv_cod_vr as cod_vr
          									, 	F.fv_date_tal as date_tal
          									, 	F.fv_usls as usls
          									, 	F.place_ob as place_ob
          									)
          								,	(
          										select 
          										xmlforest
          										(
          											xmlagg(xmlforest(EF.errorText as "Error")) as "Errors"
          										)
          										From ErrorsFV EF
          										where EF.fv_id = F.fv_id
          									),
                                            (
                                    
          							        	select 
          							        		XMLELEMENT(
                                                    name "Usl"
                                                    ,XMLFOREST
									        		   (
                                                       xmlforest(
                                                             usls.Code as "@Code"
					                        		        ,usls.Cnt as "@Cnt"
					                        		        ,usls.Uet as "@Uet"
					                        		        ,usls.ToothNumber as "@ToothNumer"
                                                            )as "UslsList"
                                                            )
                                                          )
					                        	from Usls usls
					                        	where usls.VisitID = R.re_id
					                          )
					                        ,(
                                                select 
          							        		XMLELEMENT(
                                                    name "UslError"
                                                    ,XMLFOREST
									        		   (
                                                        xmlforest(
					                        		        eu.Code as "@Code"
					                        		        ,eu.errorText as "@Error"
                                                            )
                                                          as "UslsErrors"
                                                            )
                                                          )
					                        	from ErrorsUsls eu
					                        	where eu.vid = R.re_id 
					                        )
          								,	(
          										select
          										xmlagg
          										(
          											xmlElement
          											(
          												name "Revisit"
          											,  	xmlAttributes
          												(
          													R.re_cel as cel
          												,	R.re_ishod as ishod
          												, 	R.re_rslt as rslt
          												, 	R.re_UET as "UET"
          												, 	R.re_cod_vr as cod_vr
          												, 	R.re_date_tal as date_tal
          												, 	R.re_usls as usls
          												,	R.place_ob as place_ob
          												)
          											, 	(
          													select 
          													xmlforest
          													(
          														xmlagg(xmlforest(ER.errorText as "Error")) as "Errors"
          													)
          													From ErrorsRe ER
          													where ER.re_id = R.re_id
          												)
          											 )
          										)
          										From ReVisits R 
          										where R.fv_id = F.fv_id
          									  )
          								)
          							)
          							From FirstVisits F 
          							where F.event_id = E.id
          							)
          						)
          					)
          				)
          				from Events2 e
          				where exists 
          				(
          					select * From ErrorsDiag e0 where e0.d_id = e.id
          				)
          				or
          				exists 
          				(
          					select * From ErrorsFV e1 where e1.d_id = e.id
          				)
          				or
          				exists 
          				(
          					select * From ErrorsRe e2 where e2.d_id = e.id
          				) 
          				or
          				exists 
          				(
			                select * From ErrorsUsls e3 where e3.eid=e.id
          				) 
          			);			
          			Return;
          		end if;
          ---------------------------------------------
          	else
          		--количество первых посещений--		
          		select lm.Code from akuz.T_LPU_MAIN lm 
                into ourLpuCode 
                where lm.TFomsCode = LpuCodeVar;
          
          		select count(*) from Events2 into cntVar;
          		--Number для event
          		call sp_GenerateNumber(524, ourLpuCode, cntVar, num_ev);
          		---------------------------------------------
          		select count(*) from FirstVisits into cntVar;
          		--Number для event		
          		call sp_GenerateNumber(545, ourLpuCode, cntVar, num_di);
          		--select @num
          		---------------------------------------------
          
          		INSERT into akuz.T_EVENT_BASE(EventID, EventPerpose, EventDate, AmbulanceCard, Number, type_, CloseDate)
          		SELECT	I.VitaCase
          		,	(select e.EnumID from vclib.T_ENUMS e where e.Code = I.cel and e.EnumTypeID = 108)
          		,	I.diag_date
          		,	
          		(
          			SELECT AmbulanceCardID
          			FROM akuz.T_AMBULANCE_CARD ac
          			join 
          				akuz.T_LPU_MAIN lm 
          				on lm.LpuID = ac.CardLpu
          			where ac.Number = I.amb_kart 
          			and lm.TFomsCode = LpuCodeVar
          			limit 1
          		)
          		,	i.num + num_ev - 1
          		,	571
          		,	I.diag_date_zak
          		FROM  Events2 I 
          		where I.IsOld = '0';

                UPDATE AKUZ.T_EVENT_BASE eb
	            SET CloseDate = e.diag_date_zak
	            FROM Events2 e 
	            where e.VitaCase = eb.EventID and e.IsOld = '1';
          
          		insert into akuz.T_MEDICAL_RECORD(MedicalRecordID, Post, type_, RecordTime)
          		select	I.MedRec_id
          		, 
          		(
          			select p.ResourceID 
          			from 
          			(
          				(
          					(
          						akuz.T_POST p 
          						join akuz.T_RESOURCE r on p.ResourceID=r.ResourceID
          					) 
          					join akuz.T_DIVISION d on d.DivisionID = Division
          				) 
          				join akuz.T_BRANCH b on Branch = b.LpuID
          			)
          			join akuz.T_LPU_MAIN L on LpuMain = l.LpuID
          			where l.TFomsCode = LpuCodeVar 
          			and d_cod_vr = p.Code 
          			and r.DateBegin <= i.fv_date_tal 
          			and 
          			(
          				i.fv_date_tal<=r.DateEnd or r.DateEnd is NULL
          			)
          			order by r.DateBegin desc
          			limit 1
          		)
          		,	667
          		,	Current_TimeStamp::TIMESTAMP
                ,(
          			SELECT AmbulanceCardID 
          			FROM akuz.T_AMBULANCE_CARD ac 
          			where ac.Number = I.d_amb_kart 
          			and (Select TFomsCode From akuz.T_LPU_MAIN Where LpuID = ac.CardLpu limit 1) = LpuCodeVar
          			limit 1
          		)
                ,(
		                SELECT Patient FROM AKUZ.T_AMBULANCE_CARD ac                                     ---- изм. от 30.03.2023: добавлен ID пациента
		                where 
		                (ac.Number=I.d_amb_kart and (Select TFomsCode From AKUZ.T_LPU_MAIN Where LpuID = ac.CardLpu limit 1)=LpuCodeVar)  
                        limit 1
	             )
          		,	e.VitaCase
          		,	I.d_diag_date
          		from FirstVisits I
                    join Events2 e on e.id = I.event_id;
          
          		insert into akuz.T_OUTCOME_OF_INSPECTION(MedicalRecordID, ActiveFindDiagnosis, MKBCode, SicknessMode)
          		SELECT	I.MedRec_id, '0'
          		, 	I.fv_MKB
          		,	(select e.EnumID from vclib.T_ENUMS e where e.Code = I.fv_harak and e.EnumTypeID = 118)--правильно ли сработано с Enums
          		FROM FirstVisits I;
          
          		insert into akuz.T_OUTCOM_OF_INSPECTION(MedicalRecordID, Outcome)
          		select I.MedRec_id
          		,	(select e.EnumID from vclib.T_ENUMS e where e.Code = I.fv_result and e.EnumTypeID = 191)
          		FROM FirstVisits I;
          
          		INSERT into akuz.T_VISIT(VisitID, VisitDate, Quantity, Inputdate, Post, AmbulanceEvent, VisitMark, VisitLocation, Rslt, Ishod, VisitPurpose, OutcomeRecord, type_, PaymentType)
          		SELECT	I.fv_id
          		, 	I.fv_date_tal
          		, 	I.fv_UET
          		, 	Current_TimeStamp::TIMESTAMP
          		, 
          		(
          			select p.ResourceID 
          			from 
          			(
          				(
          					(
          						akuz.T_POST p 
          						join akuz.T_RESOURCE r on p.ResourceID = r.ResourceID
          					) 
          					join akuz.T_DIVISION d on d.DivisionID = Division
          				) 
          				join akuz.T_BRANCH b on Branch = b.LpuID
          			)
          			join akuz.T_LPU_MAIN L on LpuMain = l.LpuID
          			where l.TFomsCode = LpuCodeVar 
          			and fv_cod_vr = p.Code 
          			and r.DateBegin <= i.fv_date_tal 
          			and 
          			(
          				i.fv_date_tal <= r.DateEnd or r.DateEnd is NULL
          			)
          			order by p.ResourceID
          			limit 1
          		)
          		, 	e.VitaCase
          		, 	1
          		, 	I.place_ob
          		, 	I.fv_rslt
          		, 	I.fv_ishod
          		, 	(select VisitPurposeID from akuz.T_VISIT_PURPOSE vp where vp.Code = I.fv_cel order by vp.PurposeType limit 1)
          		, 	I.MedRec_id
          		, 	505
          		, 	12601
          		From FirstVisits I;
          
          		insert into akuz.T_DOCTOR_INSPECTION(VisitID, Number, LastVisit , VisitCount)
          		SELECT	
	              I.fv_id
	            , i.num + num_DI - 1
                , I.fv_id
                , 1
          		FROM FirstVisits I;
          
          		insert into akuz.T_AMBULANCE_DOCTOR_INSPECTION(VisitID, EntryType)
          		select	I.fv_id
          		,	12201
          		from FirstVisits I;

                declare
                viz_cur1 CURSOR FOR SELECT fv_id FROM FirstVisits;
                _DoctorInspection1 uuid;
                begin
                    OPEN viz_cur1;
                    LOOP 
                    FETCH viz_cur1 INTO _DoctorInspection1;
                    IF NOT FOUND THEN EXIT;END IF;
                    
                    call sp_UpdateLastVisit(_DoctorInspection1);
                    END LOOP;
                    CLOSE viz_cur1;
                end;
          
          		insert into akuz.T_MEDICAL_RECORD(MedicalRecordID, Post, type_, RecordTime, AmbulanceCard, PatientBase, AmbulanceEvent, PostingDate)
          		select	usls.MedicalRecordID
          		,	
          		(
          			select p.ResourceID 
          			from 
          			(
          				(
          					(
          						akuz.T_POST p 
          						join akuz.T_RESOURCE r on p.ResourceID=r.ResourceID
          					)
          					join akuz.T_DIVISION d on d.DivisionID = Division
          				) 
          				join akuz.T_BRANCH b on Branch = b.LpuID
          			)
          			join akuz.T_LPU_MAIN L on LpuMain = l.LpuID
          			where l.TFomsCode = LpuCodeVar 
          			and fv_cod_vr = p.Code 
          			and r.DateBegin <= i.fv_date_tal 
          			and 
          			(
          				i.fv_date_tal <= r.DateEnd or r.DateEnd is NULL
          			)
          			order by p.ResourceID
          			limit 1
          		)
          		, 	578
          		, 	Current_TimeStamp::TIMESTAMP
                ,(
	             	    SELECT AmbulanceCardID FROM AKUZ.T_AMBULANCE_CARD ac 
	             	    where (ac.Number=I.d_amb_kart and (Select TFomsCode From AKUZ.T_LPU_MAIN Where LpuID = ac.CardLpu limit 1)=LpuCodeVar) 
                        limit 1
	                 )
	             	,(
	             	    SELECT Patient FROM AKUZ.T_AMBULANCE_CARD ac 
	             	    where (ac.Number=I.d_amb_kart and (Select TFomsCode From AKUZ.T_LPU_MAIN Where LpuID = ac.CardLpu limit 1)=LpuCodeVar) 
                        limit 1
	                 )
                  ,e.VitaCase
	             	,Max(I.d_diag_date)
          		from FirstVisits I
          		    join Usls on usls.VisitID = I.fv_id
                    join Events2 e on e.id = I.event_id
                group by usls.MedicalRecordID, I.d_amb_kart, e.VitaCase;
          
          		INSERT into akuz.T_AMBULANCE_SURGICAL_OPERATION(MedicalRecordID, Ratio, OperationDate, MedicalService, Uet
		        ,ToothNumber
		        ,PerformedServicePost) 
          		select	usls.MedicalRecordID
          		,	usls.Cnt
          		,	I.fv_date_tal
          		,	usls.MedicalServiceID
          		,	usls.Uet
		        ,   usls.ToothNumber
                ,(
	                select p.ResourceID from 
	                  (((
	                  AKUZ.T_POST p join AKUZ.T_RESOURCE r on (p.ResourceID=r.ResourceID)) 
	                		   join AKUZ.T_DIVISION d on (d.DivisionID = Division)) 
	                		   join AKUZ.T_BRANCH b on (Branch = b.LpuID))
	                		   join AKUZ.T_LPU_MAIN L on (LpuMain=l.LpuID) 
	                		   where (l.TFomsCode=LpuCodeVar and fv_cod_vr=p.Code and r.DateBegin<=i.fv_date_tal and (i.fv_date_tal<=r.DateEnd or r.DateEnd is NULL)) 
	                		   order by p.ResourceID
                        limit 1
	              )
          		from FirstVisits I	
          		join Usls on usls.VisitID = I.fv_id;
          
          		------------------------------------------------------
          		--ревизиты--
          		INSERT INTO akuz.T_VISIT(VisitID, VisitDate, Quantity, InputDate, Post, AmbulanceEvent, VisitMark, VisitLocation, Rslt, Ishod, VisitPurpose, OutcomeRecord, type_, PaymentType)
          		SELECT	re.re_id
          		, 	re.re_date_tal
          		, 	re.re_UET
          		, 	Current_TimeStamp::TIMESTAMP
          		, 
          		(
          			select p.ResourceID 
          			from 
          			(
          				(
          					(
          						akuz.T_POST p 
          						join akuz.T_RESOURCE r on p.ResourceID = r.ResourceID
          					) 
          					join akuz.T_DIVISION d on d.DivisionID = Division
          				) 
          				join akuz.T_BRANCH b on Branch = b.LpuID
          			)
          			join akuz.T_LPU_MAIN L on LpuMain = l.LpuID
          			where l.TFomsCode = LpuCodeVar 
          			and re.re_cod_vr = p.Code 
          			and r.DateBegin <= re.re_date_tal 
          			and 
          			(
          				re.re_date_tal <= r.DateEnd or r.DateEnd is NULL
          			)
          			order by p.ResourceID
          			limit 1
          		)
          		, 	e.VitaCase
          		, 	2
          		, 	re.place_ob
          		, 	re.re_rslt
          		, 	re.re_ishod
          		, 	(select VisitPurposeID from akuz.T_VISIT_PURPOSE vp where vp.Code=re.re_cel)
          		, 	fv.MedRec_id
          		, 	664
          		, 	12601
          		From FirstVisits fv  
          		join ReVisits re ON fv.fv_id = re.fv_id
		        join Events2 e on e.id = re.d_id;
          
          		INSERT INTO akuz.T_DOCTOR_INSPECTION_RE(VisitID, DoctorInspection)
          		select	re.re_id
          		,	re.fv_id
          		from FirstVisits fv  
          		join ReVisits re ON fv.fv_id = re.fv_id;
          
          		insert into akuz.T_MEDICAL_RECORD(MedicalRecordID, Post, type_, RecordTime)
          		select	usls.MedicalRecordID
          		,
          		(
          			select p.ResourceID 
          			from 
          			(
          				(
          					(
          						akuz.T_POST p 
          						join akuz.T_RESOURCE r on p.ResourceID = r.ResourceID
          					) 
          					join akuz.T_DIVISION d on d.DivisionID = Division
          				) 
          				join akuz.T_BRANCH b on Branch = b.LpuID
          			)
          			join 
          				akuz.T_LPU_MAIN L 
          				on LpuMain = l.LpuID
          			where l.TFomsCode = LpuCodeVar
          			and rv.re_cod_vr = p.Code 
          			and r.DateBegin <= rv.re_date_tal 
          			and 
          			(
          				rv.re_date_tal <= r.DateEnd or r.DateEnd is NULL
          			)
          			order by p.ResourceID
          			limit 1
          		    )
          		    , 	578
          		    , 	Current_TimeStamp::TIMESTAMP
                    ,(
	                	SELECT AmbulanceCardID FROM AKUZ.T_AMBULANCE_CARD ac 
	                	where (ac.Number=fv.d_amb_kart and (Select TFomsCode From AKUZ.T_LPU_MAIN Where LpuID = ac.CardLpu limit 1)=LpuCodeVar) 
                        limit 1
	                )
	                ,(
	                	SELECT Patient FROM AKUZ.T_AMBULANCE_CARD ac 
	                	where (ac.Number=fv.d_amb_kart and (Select TFomsCode From AKUZ.T_LPU_MAIN Where LpuID = ac.CardLpu limit 1)=LpuCodeVar) 
                        
	                )
                    ,e.VitaCase
	                ,Max(fv.d_diag_date)
          		from ReVisits rv
          		join Usls on usls.VisitID = rv.re_id
                join FirstVisits fv ON (fv.fv_id = rv.fv_id)
                join Events2 e on e.id = fv.event_id
                group by usls.MedicalRecordID, fv.d_amb_kart, e.VitaCase;

	            INSERT INTO AKUZ.T_AMBULANCE_SURGICAL_OPERATION
	            	(MedicalRecordID
	            	,Ratio
	            	,OperationDate
	            	,MedicalService
	            	,Uet
	            	,ToothNumber
	            	,PerformedServicePost) 
	            select
	            	usls.MedicalRecordID
	            	,usls.Cnt
	            	,rv.re_date_tal
	            	,usls.MedicalServiceID
	            	,usls.Uet
	            	,usls.ToothNumber
	            	,(
	            select p.ResourceID from 
	              (((
	              AKUZ.T_POST p join AKUZ.T_RESOURCE r on (p.ResourceID=r.ResourceID)) 
	            		   join AKUZ.T_DIVISION d on (d.DivisionID = Division)) 
	            		   join AKUZ.T_BRANCH b on (Branch = b.LpuID))
	            		   join AKUZ.T_LPU_MAIN L on (LpuMain=l.LpuID) 
	            		   where (l.TFomsCode=LpuCodeVar and rv.re_cod_vr=p.Code and r.DateBegin<=rv.re_date_tal and (rv.re_date_tal<=r.DateEnd or r.DateEnd is NULL)) 
	            		   order by p.ResourceID
                    limit 1
	              )
	            from ReVisits rv	
	            	join Usls usls on usls.VisitID = rv.re_id;
             -----------------------------------------------------
          	end if;
        end if;
	End;
End;
$$;