if OBJECT_ID('sp_ImportEvents', 'P') is not null
	drop procedure sp_ImportEvents
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_ImportEvents]
(
	@VisitXml varchar(max),
	@CaseFlag bit
)
AS 
BEGIN

/*	
select @VisitXml  visit, @CaseFlag flag
into vcimport.dbo.otlstom
*/

if(@CaseFlag = 1)	-- Новая интеграция с Волга Мед.
begin
DECLARE @Xml xml
set @Xml = @VisitXml 

declare @CodeLpu varchar(6)
SELECT @CodeLpu = T.c.value('./@cod_LPU','varchar(16)')
FROM @Xml.nodes('./import_talons_flagman') T(c)
--получение идентификатора случая--
declare @CaseID uniqueidentifier
SELECT @CaseID =  T.c.value('./../@CaseID','uniqueidentifier') 
FROM @Xml.nodes('./import_talons_flagman/diags/first_visit') T(c)

declare @EnableUpdate BIT
select @EnableUpdate = 0
--получение идентификатора посещения--
declare @VisitID uniqueidentifier
declare @temp1 varchar(32)
SELECT @temp1 = T.c.value('./@VisitID','varchar(32)')
FROM @Xml.nodes('./import_talons_flagman/diags/first_visit') T(c)
if(@temp1 <> '') -- Редактировать посещение 
	begin
		SELECT @VisitID = T.c.value('./@VisitID','uniqueidentifier')
	    FROM @Xml.nodes('./import_talons_flagman/diags/first_visit') T(c)
		select @EnableUpdate = 1
	end
  else -- Создавать новое
	begin
		select @VisitID = NEWID()
	end
---------------------------------------------

--declare @MedrecID uniqueidentifier
--set @MedrecID = NEWID()

declare @Visit table 
(
 [event_id] uniqueidentifier,
 [fv_id] uniqueidentifier,
 [MedRec_id] uniqueidentifier,
 [d_cel] int,
 [d_amb_kart] varchar(32),
 [d_diag_date] datetime,
 [d_closing_date] datetime,
 [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 identity
)

insert into @Visit
SELECT
	@CaseID
	, @VisitID as [fv_id]
	, NewId() as [MedRec_id]
	, T.c.value('./../@cel', 'int') AS [d_cel]
	, T.c.value('./../@amb_kart', 'int') AS [d_amb_kart]
	, T.c.value('./../@diag_date', 'datetime') AS [d_diag_date]
	, T.c.value('./../@closing_date', 'datetime') AS [d_closing_date]
	, T.c.value('./@cel', 'int') AS [fv_cel]
	, T.c.value('./@ishod', 'int') AS [fv_ishod]
	, T.c.value('./@date_tal', 'datetime') AS [fv_date_tal]
	, T.c.value('./@cod_vr', 'varchar(7)') AS [fv_cod_vr]
	, T.c.value('./@MKB', 'varchar(15)') AS [fv_MKB]
	, T.c.value('./@harak', 'varchar(1)') AS [fv_harak]
	, T.c.value('./@UET', 'decimal(15,2)') AS [fv_UET]
	, T.c.value('./@result', 'int') AS [fv_result]
	, T.c.value('./@rslt', 'int') AS [fv_rslt]		
FROM @Xml.nodes('./import_talons_flagman/diags/first_visit') T(c) Order by d_amb_kart

declare @Event table 
(
 [id] uniqueidentifier,
 [cel] int,
 [amb_kart] varchar(32),
 [diag_date] datetime,
 [closing_date] datetime
)

insert @Event
select [event_id], [d_cel], [d_amb_kart], [d_diag_date], [d_closing_date]
from @Visit

declare @EventErrors table
(
	[d_id] uniqueidentifier,
	[errorText] varchar(100)
)

declare @VisitErrors table
(
	[d_id] uniqueidentifier,
	[fv_id] uniqueidentifier,
	[errorText] varchar(100)
)

declare @LpuErrors varchar(100)
if not exists (select lm.LpuCode from [AKUZ].T_LPU_MAIN lm where lm.LpuCode=@CodeLpu) 
set @LpuErrors = '<Error>Неизвестный код cod_LPU</Error>' 
else
set @LpuErrors = ''

if @CodeLpu = null
set @LpuErrors = '<Error>Не найден cod_LPU</Error>' 

insert into @EventErrors
select
  E.id
,'Такого CaseID не существует.'
from @Event E
where (not exists(select EventID from [AKUZ].T_EVENT_BASE eb join @Event I on I.id = eb.EventID))

insert into @EventErrors
select
  E.id
,'Поле cel должно быть в диапазоне от 1 до 7'
from @Event E
where (E.cel<1 and E.cel>7)

insert into @EventErrors
select
  E.id
,'В этом ЛПУ нет карты с таким номером'
from @Event 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 = @CodeLpu)))

insert into @EventErrors
select
  E.id
,'Дата посещения должна быть больше или равна дате случая'
from @Event E
where (exists (select I.diag_date, F.fv_date_tal from @Event I join @Visit F on F.event_id = I.id  where I.diag_date > F.fv_date_tal))

insert into @VisitErrors
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
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=@CodeLpu 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
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
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
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
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
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 <> ''
begin
if (select count(*) from @EventErrors)+(select count(*) from @VisitErrors) = 0
begin
	
	select @VisitID as lit, '<?xml version="1.0" encoding="Windows-1251"?>' +@LpuErrors as Errors
end
else
begin
	select @VisitID as lit, '<?xml version="1.0" encoding="Windows-1251"?>' +@LpuErrors +(
	select  (
	select 
	E.cel as '@cel',
	E.amb_kart as '@amb_kart',
	E.diag_date as '@diag_date'
	,(
		Select 
		ED.errorText as 'Error'	
		From @EventErrors ED
		where (ED.d_id = E.id)
		FOR XML PATH(''), root('Errors'), type
	 )
	,(
		select
			  F.fv_cel as '@cel' 
			, F.fv_ishod as '@ishod'
			, F.fv_rslt as '@rslt'
			, F.fv_result '@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 
				EF.errorText as 'Error'
				from @VisitErrors EF
				where (EF.fv_id = F.fv_id)
				FOR XML PATH(''), root('Errors'), type
			  )
			--, (
			--select
			--	  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'
			--	, (
			--		select
			--		ER.errorText as 'Error'
			--		from @ErrorsRe ER
			--		where (ER.re_id = R.re_id)
			--		FOR XML PATH(''), root('Errors'), type
			--	  )
			--	From @ReVisits R where (R.fv_id = F.fv_id)
			--	FOR
			--	XML path('Revisit'), type
			
		  --)
		From @Visit F where (F.event_id = E.id) 
		FOR
		XML path('First_visit'), type
	)
	from @Event 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
		) 
		--or
		--exists (
		--select * From @ErrorsRe e2 where e2.d_id=e.id
		--)

	)
	FOR
	XML path('diag'), root('errorlist')
	)) as Errors

end

end
---------------------------------------------
else
begin

--количество первых посещений--
--declare @cnt int
--select @cnt = count(*) from @Event
--Number для event
--declare @ev_num int
--exec @ev_num = [dbo].[sp_GenerateNumber] 524, @CodeLpu, 1
--select @num
---------------------------------------------
--select @cnt = count(*) from @Visit
--Number для event
declare @di_num int
exec @di_num = [dbo].[sp_GenerateNumber] 545, @CodeLpu, 1
---------------------------------------------

if(@EnableUpdate = 0)
begin
	--INSERT INTO [AKUZ].[T_EVENT_BASE] 
	--	([EventID]
	--	,[EventPerpose]
	--	,[EventDate]
	--	,[AmbulanceCard]
	--	,[Number]
	--	,[type_]
	--	,[Status????])
	--	SELECT
	--	  I.id
	--	, (select e.EnumID from [VCLib].T_ENUMS e where e.Code = I.cel and e.EnumTypeID = 108)
	--	, I.diag_date
	--	, (SELECT top 1 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.Code=@CodeLpu)
	--	, i.num + @num_ev - 1
	--	, 571
	--	, ???????????
	--	FROM  @Event I where I.IsOld=0

	if((select V.d_closing_date from @Visit V) is not null) -- если случай закрыт
	begin

		UPDATE EB
			SET  eb.CloseDate = I.closing_date
			FROM [AKUZ].[T_EVENT_BASE] EB join @Event I on I.id = EB.EventID
			WHERE EB.EventID = I.id
		
		insert into [AKUZ].[T_MEDICAL_RECORD]
		(
		  MedicalRecordID
		 ,Post
		 ,type_
		 ,RecordDate 
		 ,RecordTime
		)
		select
		  I.MedRec_id
		, (
		select top 1 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=@CodeLpu and fv_cod_vr=p.Code and DateBegin<=i.fv_date_tal and (i.fv_date_tal<=DateEnd or DateEnd is NULL)) order by DateBegin desc
		  )
		, 667
		, (select cast (round (cast (d_closing_date as float), 0, 1) as datetime) from @Visit)
		, (select d_closing_date from @Visit)
		from @Visit I

		insert into [AKUZ].[T_AMBULANCE_CARD_RECORD]
		(
		  MedicalRecordID
		, AmbulanceCard
		, AmbulanceEvent
		, PostingDate
		)
		SELECT
		  I.MedRec_id
		, (
			SELECT top 1 AmbulanceCardID FROM [AKUZ].T_AMBULANCE_CARD ac
			where 
			(ac.Number=I.d_amb_kart and (Select top 1 LpuCode From [AKUZ].T_LPU_MAIN Where LpuID = ac.CardLpu)=@CodeLpu) 
		  )
		, 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
		, GETDATE()
		, (
		  select top 1 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=@CodeLpu and fv_cod_vr=p.Code and DateBegin<=i.fv_date_tal and (i.fv_date_tal<=DateEnd or DateEnd is NULL)) order by DateBegin desc
		  )
		, I.event_id
		, 1
		, 1
		, I.fv_rslt
		, I.fv_ishod
		, (select top 1 VisitPurposeID from [AKUZ].T_VISIT_PURPOSE vp where vp.Code=I.fv_cel order by vp.PurposeType)
		, I.MedRec_id
		, 505
		, 12601
		, case 
		when DATEDIFF(year, (SELECT top 1 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 top 1 LpuCode From [AKUZ].T_LPU_MAIN Where LpuID = ac.CardLpu)=@CodeLpu)),GETDATE())<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=@CodeLpu)
		 then 'B69E3D1C-A50C-4AD6-A756-8B1409E2C481' --  U8905
		when DATEDIFF(year, (SELECT top 1 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 top 1 LpuCode From [AKUZ].T_LPU_MAIN Where LpuID = ac.CardLpu)=@CodeLpu)),GETDATE())>=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=@CodeLpu) then '1E2F2F5A-5257-4C25-88FB-FE2E05FEAB42' --U8901
		else null
		end 
		From @Visit I 
	end
	else -- если случай не закрыт
	begin
		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
		, GETDATE()
		, (
		  select top 1 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=@CodeLpu and fv_cod_vr=p.Code and DateBegin<=i.fv_date_tal and (i.fv_date_tal<=DateEnd or DateEnd is NULL)) order by DateBegin desc
		  )
		, I.event_id
		, 1
		, 1
		, I.fv_rslt
		, I.fv_ishod
		, (select top 1 VisitPurposeID from [AKUZ].T_VISIT_PURPOSE vp where vp.Code=I.fv_cel order by vp.PurposeType)
		--, @MedrecID --I.MedRec_id
		, 505
		, 12601
		, case 
		when DATEDIFF(year, (SELECT top 1 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 top 1 LpuCode From [AKUZ].T_LPU_MAIN Where LpuID = ac.CardLpu)=@CodeLpu)),GETDATE())<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=@CodeLpu)
		 then 'B69E3D1C-A50C-4AD6-A756-8B1409E2C481' --  U8905
		when DATEDIFF(year, (SELECT top 1 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 top 1 LpuCode From [AKUZ].T_LPU_MAIN Where LpuID = ac.CardLpu)=@CodeLpu)),GETDATE())>=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=@CodeLpu) then '1E2F2F5A-5257-4C25-88FB-FE2E05FEAB42' --U8901
		else null
		end 
		From @Visit I
	end
	
	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 @DoctorInspection uniqueidentifier
    DECLARE viz_cur CURSOR FOR SELECT fv_id FROM @Visit
    OPEN viz_cur
    FETCH NEXT FROM viz_cur INTO @DoctorInspection
    WHILE @@FETCH_STATUS = 0
    BEGIN
	    EXECUTE [dbo].[sp_UpdateLastVisit] @DoctorInspection

	    FETCH NEXT FROM viz_cur INTO @DoctorInspection
    END
    CLOSE viz_cur
    DEALLOCATE viz_cur

end
else
begin
	UPDATE EB
	SET  EB.EventPerpose = (select e.EnumID from [VCLib].T_ENUMS e where e.Code = I.cel and e.EnumTypeID = 108)
		,EB.EventDate = I.diag_date
		,eb.CloseDate = I.closing_date
	FROM [AKUZ].[T_EVENT_BASE] EB join @Event I on I.id = EB.EventID
	WHERE EB.EventID = I.id
	
	--if((select V.d_closing_date from @Visit V) is not null) -- если случай закрыт
	--begin	
		UPDATE MR
		SET MR.Post = (select top 1 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=@CodeLpu and fv_cod_vr=p.Code and DateBegin<=i.fv_date_tal and (i.fv_date_tal<=DateEnd or DateEnd is NULL)) order by DateBegin desc)
			,MR.RecordDate = (select cast (round (cast (d_closing_date as float), 0, 1) as datetime) from @Visit)
			,MR.RecordTime = (select d_closing_date from @Visit)
		FROM [AKUZ].[T_MEDICAL_RECORD] MR join @Visit I on I.MedRec_id = MR.MedicalRecordID join [AKUZ].T_VISIT V on V.OutcomeRecord = MR.MedicalRecordID
		WHERE MR.MedicalRecordID = V.OutcomeRecord

		UPDATE ACR
		SET ACR.PostingDate = I.d_diag_date
		FROM [AKUZ].[T_AMBULANCE_CARD_RECORD] ACR 
			join @Visit I on I.MedRec_id = ACR.MedicalRecordID 
			join [AKUZ].T_VISIT V on V.OutcomeRecord = ACR.MedicalRecordID
		WHERE ACR.MedicalRecordID = V.OutcomeRecord

		UPDATE 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 [AKUZ].[T_OUTCOME_OF_INSPECTION] OI
			join @Visit I on I.MedRec_id = OI.MedicalRecordID 
			join [AKUZ].T_VISIT V on V.OutcomeRecord = OI.MedicalRecordID
		WHERE OI.MedicalRecordID = V.OutcomeRecord

		UPDATE OI 
		SET OI.Outcome = (select e.EnumID from [VCLib].T_ENUMS e where e.Code = I.fv_result and e.EnumTypeID = 191)
		FROM [AKUZ].[T_OUTCOM_OF_INSPECTION] OI
			join @Visit I on I.MedRec_id = OI.MedicalRecordID
			join [AKUZ].T_VISIT V on V.OutcomeRecord = OI.MedicalRecordID
		WHERE OI.MedicalRecordID = V.OutcomeRecord 
	--end

	UPDATE V
	SET V.VisitDate = I.fv_date_tal,
		V.Quantity = I.fv_UET,
		V.Inputdate = GETDATE(),
		V.Post = 	(select top 1 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=@CodeLpu and fv_cod_vr=p.Code and DateBegin<=i.fv_date_tal and (i.fv_date_tal<=DateEnd or DateEnd is NULL)) order by DateBegin desc),
		V.Rslt = I.fv_rslt,
		V.Ishod = I.fv_ishod,
		V.VisitPurpose = (select top 1 VisitPurposeID from [AKUZ].T_VISIT_PURPOSE vp where vp.Code=I.fv_cel order by vp.PurposeType)
	FROM [AKUZ].[T_VISIT] V join @Visit I on I.fv_id = V.VisitID
	WHERE V.VisitID = I.fv_id; 

end
end

select @VisitID as lit, '' as Errors
end
else				-- Старая интеграция с Волга Мед.
begin

DECLARE @Act xml
set @Act = @VisitXml 

--получение кода лпу--
declare @LpuCode varchar(16)
SELECT @LpuCode = T.c.value('./@cod_LPU','varchar(16)')
FROM @Act.nodes('./import_talons_flagman') T(c)
---------------------------------------------
--случаи--
declare @Events table 
(
 [VitaCase] uniqueidentifier,
 [FlagmanCase] int,
 [id] uniqueidentifier,
 [IsOld] bit,
 [cel] int,
 [amb_kart] varchar(32),
 [diag_date] datetime,
 [num] int identity,
 [cod_vr] varchar(7),
 [diag_date_zak] datetime
)
--первые посещения--
declare @FirstVisits table 
(
 [event_id] uniqueidentifier,
 [fv_id] uniqueidentifier,
 [MedRec_id] uniqueidentifier,
 [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 identity,
 [fv_usls] varchar(max),
 [place_ob] int
)

declare @Usls table
(
    [id] int
   ,[Code] varchar(64)
   ,[Uet] decimal(12,2)
   ,[Cnt] int
   ,[VisitID] uniqueidentifier
   ,[MedicalServiceID] uniqueidentifier
   ,[MedicalRecordID] uniqueidentifier
   ,[ToothNumber] int
)

insert into @FirstVisits
SELECT
		  Null as [event_id]
		, NewId() as [fv_id]
		, NewId() as [MedRec_id]
		, T.c.value('./../@FlagmanCase', 'int') AS [FlagmanCase]
		, T.c.value('./../@cel', 'int') AS [d_cel]
		, T.c.value('./../@amb_kart', 'int') AS [d_amb_kart]
		, T.c.value('./../@diag_date', 'datetime') AS [d_diag_date]
		, T.c.value('./../@cod_vr_zak', 'varchar(7)') AS [d_cod_vr] -- код врача закрывающий случай
		, T.c.value('./@id', 'int') AS [id]
		, T.c.value('./@cel', 'int') AS [fv_cel]
		, T.c.value('./@ishod', 'int') AS [fv_ishod]
		, T.c.value('./@date_tal', 'datetime') AS [fv_date_tal]
		, T.c.value('./@cod_vr', 'varchar(7)') AS [fv_cod_vr]
		, T.c.value('./@MKB', 'varchar(15)') AS [fv_MKB]
		, T.c.value('./@harak', 'varchar(1)') AS [fv_harak]
		, T.c.value('./@UET', 'decimal(15,2)') AS [fv_UET]
		, T.c.value('./@result', 'int') AS [fv_result]
		, T.c.value('./@rslt', 'int') AS [fv_rslt]		
		, T.c.value('./@usls', 'varchar(max)') AS [fv_usls]		
		, T.c.value('./@place_ob', 'int') AS [place_ob]		
		FROM @Act.nodes('./import_talons_flagman/diags/first_visit') T(c) Order by d_amb_kart

insert into @Usls (id, Code, Uet, Cnt, MedicalRecordID, ToothNumber)
SELECT
	  T.c.value('./../@id', 'int') AS [id]
	 ,T.c.value('./@cod_usl', 'varchar(64)') AS [code]
	 ,T.c.value('./@usl_uet', 'decimal(12,2)') AS [uet]
	 ,T.c.value('./@usl_cnt', 'int') AS [cnt]
	 ,newid()
	 ,T.c.value('./@zub', 'int') AS [ToothNumber] 
FROM @Act.nodes('./import_talons_flagman/diags/first_visit/uls_first_visit') T(c)
---------------------------------------------
insert @Events ([id], [IsOld], [VitaCase], [FlagmanCase], [cel], [amb_kart], [diag_date], [cod_vr], [diag_date_zak])
select 
	NEWID(), 
	1, -- случаи обращения, созданные в Витакарте
	case when len(T.c.value('./@VitaCase', 'varchar(255)')) > 0 then T.c.value('./@VitaCase', 'varchar(255)') else null end as [VitaCase],
	T.c.value('./@FlagmanCase', 'int') AS [FlagmanCase],
	T.c.value('./@cel', 'int') AS [cel],
	T.c.value('./@amb_kart', 'int') AS [amb_kart],
	T.c.value('./@diag_date', 'datetime') AS [diag_date],
	T.c.value('./@cod_vr', 'varchar(7)') AS [cod_vr],
	T.c.value('./@diag_date_zak', 'datetime') AS [diag_date_zak]
FROM @Act.nodes('./import_talons_flagman/diags') T(c) Order by amb_kart

update e
set e.[VitaCase] = newid(), e.[IsOld] = 0 -- случаи, созданные во Флагмане
from @Events e
where e.[VitaCase] is null

update fv
set fv.[event_id] = e.[id]
from @FirstVisits fv
	join @Events e on e.[FlagmanCase] = fv.[FlagmanCase]

--declare @OldId table (
--[gen_id] uniqueidentifier,
--[old_id] uniqueidentifier
--)

--insert into @OldId
--select E.[id] as [gen_id],
--(
--	select top 1 eb.EventID from [AKUZ].T_EVENT_BASE eb where
--		( 
--			eb.EventDate = E.diag_date and
--			eb.EventPerpose = (select s.EnumID from [VCLib].T_ENUMS s where s.Code = E.cel and s.EnumTypeID = 108) and
--			eb.AmbulanceCard = (SELECT top 1 AmbulanceCardID
--		   FROM [AKUZ].T_AMBULANCE_CARD ac
--		   join [AKUZ].T_LPU_MAIN lm on lm.LpuID = ac.CardLpu
--		   where ac.Number=E.amb_kart and lm.TFomsCode=@LpuCode)			
--		)
--)
--from @Events E


--declare @temp uniqueidentifier
--select @temp = old_id from @OldId

--update e 
--set 
--e.[id] = o.old_id,
--e.[IsOld] = 1
--from @Events e join @OldId o on e.id=o.gen_id where o.old_id is not null

--update v
-- set v.[event_id] = e.id
--from @FirstVisits v
--join @Events e on e.cel = v.d_cel and e.amb_kart = v.d_amb_kart and e.diag_date = v.d_diag_date

declare @ReVisits table
(
	[d_id] uniqueidentifier,
	[fv_id] uniqueidentifier,
	[re_id] uniqueidentifier,
	[FlagmanCase] int,
	[id] int,
	[re_date_tal] datetime,
	[re_cod_vr] varchar(7),
	[re_cel] int,
	[re_UET] decimal(15,2),
	[re_rslt] int,
	[re_ishod] int,
	[re_usls] varchar(max),
	[p_cel] int,
	[p_diag_date] datetime,
	[p_amb_cart] varchar(32),
	[place_ob] int
)

insert into @ReVisits
SELECT	 
		  null as [d_id]
		, null as [fv_id]
		, NEWID() as [re_id]
		, T.c.value('./../@FlagmanCase', 'int') AS [FlagmanCase]
		, T.c.value('./@id', 'int') AS [id]
		, T.c.value('./@date_tal', 'datetime') AS [re_date_tal]
		, T.c.value('./@cod_vr', 'varchar(7)') AS [re_cod_vr]
		, T.c.value('./@cel', 'int') AS [re_cel]
		, T.c.value('./@UET', 'decimal(15,2)') AS [re_UET]
		, T.c.value('./@rslt', 'int') AS [re_rslt]
		, T.c.value('./@ishod', 'int') AS [re_ishod]
		, T.c.value('./@usls', 'varchar(max)') AS [re_usls]
		, T.c.value('./../@cel', 'int') AS [p_cel]
		, T.c.value('./../@diag_date', 'datetime') AS [p_diag_date]
		, T.c.value('./../@amb_kart', 'int') AS [p_amb_cart]
		, T.c.value('./@place_ob', 'int') AS [place_ob]
		FROM @Act.nodes('./import_talons_flagman/diags/revisit') T(c) where T.c.value('./@date_tal', 'datetime') is not null	

update re
set re.[fv_id] = fv.[fv_id],
    re.[d_id] = fv.[event_id]
from @ReVisits re 
	join @FirstVisits fv on fv.[FlagmanCase] = re.[FlagmanCase]

insert into @Usls (id, Code, Uet, Cnt, MedicalRecordID, ToothNumber)
SELECT
	  T.c.value('./../@id', 'int') AS [id]
	 ,T.c.value('./@cod_usl', 'varchar(64)') AS [code]
	 ,T.c.value('./@usl_uet', 'decimal(12,2)') AS [uet]
	 ,T.c.value('./@usl_cnt', 'int') AS [cnt]
	 ,newid()
	 ,T.c.value('./@zub', 'int') as [ToothNumber]
FROM @Act.nodes('./import_talons_flagman/diags/revisit/uls_revisit') T(c)

declare @ErrorsDiag table
(
	[d_id] uniqueidentifier,
	[errorText] varchar(100)
)

declare @ErrorsFV table
(
	[d_id] uniqueidentifier,
	[fv_id] uniqueidentifier,
	[errorText] varchar(100)
)

declare @ErrorsRe table
(
	[d_id] uniqueidentifier,
	[fv_id] uniqueidentifier,
	[re_id] uniqueidentifier,
	[errorText] varchar(100)
)

declare @LpuErr varchar(100)
if not exists (select lm.TFomsCode from [AKUZ].T_LPU_MAIN lm where lm.TFomsCode=@LpuCode) 
set @LpuErr = '<Error>Неизвестный код cod_LPU</Error>' 
else
set @LpuErr = ''

if @LpuCode = null
set @LpuErr = '<Error>Не найден cod_LPU</Error>' 

insert into @ErrorsDiag
select
  E.id
,'Не найден случай обращения, открытый в Витакарте'
from @Events 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 @Events E
where (E.cel<1 and E.cel>7)

insert into @ErrorsDiag
select
  E.id
,'В этом ЛПУ нет карты с таким номером'
from @Events 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 = @LpuCode)))

insert into @ErrorsFV
select				   
  fv.event_id		   
, fv.fv_id
,'Дубликат FirstVisit'
from @FirstVisits fv 
	join @Events e on e.id = fv.[event_id]
where exists (
select top 1 v.VisitID from [AKUZ].T_VISIT v where e.[VitaCase]=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 top 1 VisitPurposeID from [AKUZ].T_VISIT_PURPOSE vp where vp.Code=fv.fv_cel order by vp.PurposeType) and
	fv.fv_cod_vr = (select top 1 p.Code from [AKUZ].T_POST p where p.ResourceID = v.Post) )



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=@LpuCode 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 @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=@LpuCode and fv.d_cod_vr=p.Code and DateBegin<=fv.fv_date_tal and (fv.fv_date_tal<=DateEnd or 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=@LpuCode and re_cod_vr=p.Code and DateBegin<=re.re_date_tal and (re.re_date_tal<=DateEnd or 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 usls.VisitID = fv.fv_id
from @Usls usls
	join @FirstVisits fv on fv.id = usls.id

update usls
set usls.VisitID = rv.re_id
from @Usls usls
	join @ReVisits rv on rv.id = usls.id

update usls
set usls.MedicalServiceID = ms.MedicalServiceID
from @Usls usls
    left join @FirstVisits fv on fv.id = usls.id
	left join @ReVisits rv on rv.id = usls.id
	cross apply
		(
			select top 1 ms.MedicalServiceID
			from [AKUZ].T_MEDICAL_SERVICE ms
			where ms.Code = usls.Code and ms.type_ =588 
				and ms.DateBegin <= getdate() and (ms.DateEnd >= isnull(fv.fv_date_tal,rv.re_date_tal) or ms.DateEnd is null)
			order by ms.DateBegin desc, ms.DateEnd desc
		) ms

insert into @ErrorsFV 
select
 fv.event_id
, fv.fv_id
,'код услуги первичного посещения не найден'
from @FirstVisits fv 
	join @Usls 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 usls on usls.VisitID = re.re_id
where usls.MedicalServiceID is null

declare @ErrorsUsls table
(
	[eid] uniqueidentifier,
	[vid] uniqueidentifier,
	[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 <> ''
begin
	if (select count(*) from @ErrorsDiag)+(select count(*) from @ErrorsFV)+(select count(*) from @ErrorsRe)+(select count(*) from @ErrorsUsls) = 0
		begin
	
			select 'EventErrors' as lit, '<?xml version="1.0" encoding="Windows-1251"?>' +@LpuErr as Errors
		end
	else
	begin
		select 'EventErrors' as lit, '<?xml version="1.0" encoding="Windows-1251"?>' +@LpuErr +(
		select  (
		select 
		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 
			ED.errorText as 'Error'	
			From @ErrorsDiag ED
			where (ED.d_id = E.id)
			FOR XML PATH(''), root('Errors'), type
		 )
		,(
			select
				  F.fv_cel as '@cel' 
				, F.fv_ishod as '@ishod'
				, F.fv_rslt as '@rslt'
				, F.fv_result '@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 
					EF.errorText as 'Error'
					from @ErrorsFV EF
					where (EF.fv_id = F.fv_id)
					FOR XML PATH(''), root('Errors'), type
				  )
				 ,(
					select 
						 usls.Code as '@Code'
						,usls.Cnt as '@Cnt'
						,usls.Uet as '@Uet'
						,usls.ToothNumber as '@ToothNumber'
					from @Usls usls
					where usls.VisitID = F.fv_id
					FOR XML PATH('Usl'), root('UslsList'), type
				  )
				,(
						select 
							eu.Code as '@Code'
						   ,eu.errorText as '@Error'
						from @ErrorsUsls eu
						where eu.vid = F.fv_id 
						FOR XML PATH('UslError'), root('UslsErrors'), type
				)
				,(
				select
					  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 
							 usls.Code as '@Code'
							,usls.Cnt as '@Cnt'
							,usls.Uet as '@Uet'
							,usls.ToothNumber as '@ToothNumer'
						from @Usls usls
						where usls.VisitID = R.re_id
						FOR XML PATH('Usl'), root('UslsList'), type
					  )
					,(
						select 
							eu.Code as '@Code'
							,eu.errorText as '@Error'
						from @ErrorsUsls eu
						where eu.vid = R.re_id 
						FOR XML PATH('UslError'), root('UslsErrors'), type
					)
					, (
						select
						ER.errorText as 'Error'
						from @ErrorsRe ER
						where (ER.re_id = R.re_id)
						FOR XML PATH(''), root('Errors'), type
					  )
					From @ReVisits R where (R.fv_id = F.fv_id)
					FOR
					XML path('Revisit'), type
			
			  )
			From @FirstVisits F where (F.event_id = E.id) 
			FOR
			XML path('First_visit'), type
		)
		from @Events 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
			)
		)
		FOR
		XML path('diag'), root('errorlist')
		)) as Errors
	end
end
---------------------------------------------
else
begin
--количество первых посещений--
declare @ourLpuCode varchar(4)
select @ourLpuCode = lm.Code from [AKUZ].T_LPU_MAIN lm where lm.TFomsCode=@LpuCode

declare @cnt int
select @cnt = count(*) from @Events
--Number для event
declare @num_ev int
if object_id(N'#gnt', N'U') is not null drop table #gnt
create table #gnt (cnt int)
insert #gnt
exec @num_ev = [dbo].[sp_GenerateNumber] 524, @ourLpuCode, @cnt

--select @num
---------------------------------------------
select @cnt = count(*) from @FirstVisits
--Number для event
declare @num_di int
insert #gnt
exec @num_di = [dbo].[sp_GenerateNumber] 545, @ourLpuCode, @cnt
--select @num
---------------------------------------------

	INSERT [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 top 1 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=@LpuCode)
		, i.num + @num_ev - 1
		, 571
		,I.diag_date_zak
		FROM  @Events I where I.IsOld=0

	UPDATE eb
	SET eb.CloseDate = e.diag_date_zak
	FROM [AKUZ].[T_EVENT_BASE] eb
		join @Events e on e.VitaCase = eb.EventID
	where e.IsOld = 1

	insert [AKUZ].[T_MEDICAL_RECORD]
	(
	  MedicalRecordID
	 ,Post
	 ,type_
	 ,RecordDate
	 ,RecordTime
	)
	select
	  I.MedRec_id
	, (
	select top 1 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=@LpuCode and d_cod_vr=p.Code and DateBegin<=i.fv_date_tal and (i.fv_date_tal<=DateEnd or DateEnd is NULL)) order by DateBegin desc
	  )
	, 667
	, (select DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
    , GETDATE()
	from @FirstVisits I

	insert [AKUZ].[T_AMBULANCE_CARD_RECORD]
	(
	  MedicalRecordID
	, AmbulanceCard
	, PatientBase                           -- изм. от 30.03.2023
	, AmbulanceEvent
	, PostingDate
	)
	SELECT
	  I.MedRec_id
	, (
		SELECT top 1 AmbulanceCardID FROM [AKUZ].T_AMBULANCE_CARD ac                                    
		where 
		(ac.Number=I.d_amb_kart and (Select top 1 TFomsCode From [AKUZ].T_LPU_MAIN Where LpuID = ac.CardLpu)=@LpuCode) 
	  )
  	, (
		SELECT top 1 [Patient] FROM [AKUZ].T_AMBULANCE_CARD ac                                     ---- изм. от 30.03.2023: добавлен ID пациента
		where 
		(ac.Number=I.d_amb_kart and (Select top 1 TFomsCode From [AKUZ].T_LPU_MAIN Where LpuID = ac.CardLpu)=@LpuCode) 
	  )
	, e.[VitaCase]
	, I.d_diag_date
	FROM @FirstVisits I
		join @Events e on e.[id] = I.[event_id]

	insert [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 [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 [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
	, GETDATE()
	, (
	  select top 1 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.TFomsCode=@LpuCode and fv_cod_vr=p.Code and DateBegin<=i.fv_date_tal and (i.fv_date_tal<=DateEnd or DateEnd is NULL)) order by p.ResourceID
	  )
	, e.[VitaCase]
	, 1
	, I.place_ob
	, I.fv_rslt
	, I.fv_ishod
	, (select top 1 VisitPurposeID from [AKUZ].T_VISIT_PURPOSE vp where vp.Code=I.fv_cel order by vp.PurposeType)
	, I.MedRec_id
	, 505
	, 12601
	--, case 
	--when DATEDIFF(year, (SELECT top 1 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 top 1 TFomsCode From [AKUZ].T_LPU_MAIN Where LpuID = ac.CardLpu)=@LpuCode)),GETDATE())<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.TFomsCode=@LpuCode)
	-- then 'B69E3D1C-A50C-4AD6-A756-8B1409E2C481' --  U8905
	--when DATEDIFF(year, (SELECT top 1 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 top 1 TFomsCode From [AKUZ].T_LPU_MAIN Where LpuID = ac.CardLpu)=@LpuCode)),GETDATE())>=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.TFomsCode=@LpuCode) then '1E2F2F5A-5257-4C25-88FB-FE2E05FEAB42' --U8901
	--else null
	--end 
	From @FirstVisits I
		join @Events e on e.[id] = I.[event_id]

	insert [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 [AKUZ].[T_AMBULANCE_DOCTOR_INSPECTION]
	(
	 [VisitID]
	,[EntryType]
	)
	select
	I.fv_id
	,12201
	from @FirstVisits I	

    DECLARE viz_cur CURSOR FOR SELECT fv_id FROM @FirstVisits
    OPEN viz_cur
    FETCH NEXT FROM viz_cur INTO @DoctorInspection
    WHILE @@FETCH_STATUS = 0
    BEGIN
	    EXECUTE [dbo].[sp_UpdateLastVisit] @DoctorInspection

	    FETCH NEXT FROM viz_cur INTO @DoctorInspection
    END
    CLOSE viz_cur
    DEALLOCATE viz_cur

--AMBULANCE_SURGICAL_OPERATION @type=578
--AKUZ.T_MEDICAL_RECORD
--AKUZ.T_AMBULANCE_CARD_RECORD
--AKUZ.T_AMBULANCE_SURGICAL_OPERATION

	insert [AKUZ].[T_MEDICAL_RECORD]
	(
	  MedicalRecordID
	 ,Post
	 ,type_
	 ,RecordDate
	 ,RecordTime
	)
	select
	  usls.MedicalRecordID
	, (
	select top 1 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=@LpuCode and fv_cod_vr=p.Code and DateBegin<=i.fv_date_tal and (i.fv_date_tal<=DateEnd or DateEnd is NULL)) order by p.ResourceID
	  )
	, 578
	, (select DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
    , GETDATE()
	from @FirstVisits I
		join @Usls usls on usls.VisitID = I.fv_id

    INSERT AKUZ.T_AMBULANCE_CARD_RECORD(MedicalRecordID,AmbulanceCard,[PatientBase],AmbulanceEvent, PostingDate)                        -- изм. 30.03.2023 [PatientBase]
	select
		usls.MedicalRecordID
		,(
		SELECT top 1 AmbulanceCardID FROM [AKUZ].T_AMBULANCE_CARD ac 
		where 
		(ac.Number=I.d_amb_kart and (Select top 1 TFomsCode From [AKUZ].T_LPU_MAIN Where LpuID = ac.CardLpu)=@LpuCode) 
	    )
		,(
		SELECT top 1 [Patient] FROM [AKUZ].T_AMBULANCE_CARD ac 
		where 
		(ac.Number=I.d_amb_kart and (Select top 1 TFomsCode From [AKUZ].T_LPU_MAIN Where LpuID = ac.CardLpu)=@LpuCode) 
	    )
        ,e.[VitaCase]
		,Min(I.d_diag_date)
	from @FirstVisits I	
		join @Usls usls on usls.VisitID = I.fv_id
        join @Events e on e.[id] = I.[event_id]
    group by usls.MedicalRecordID, I.d_amb_kart, e.[VitaCase]

	INSERT [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 top 1 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=@LpuCode and fv_cod_vr=p.Code and DateBegin<=i.fv_date_tal and (i.fv_date_tal<=DateEnd or DateEnd is NULL)) order by p.ResourceID
	  )
	from @FirstVisits I	
		join @Usls 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
	--, MedicalService
	)
	SELECT
	  re.re_id
	, re.re_date_tal
	, re.re_UET
	, GetDate()
	, (
	  select top 1 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=@LpuCode and re.re_cod_vr=p.Code and DateBegin<=re.re_date_tal and (re.re_date_tal<=DateEnd or DateEnd is NULL)) order by p.ResourceID
	  )
	, 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
	--, case 
	--when DATEDIFF(year, (SELECT top 1 p.Birthday 
	--                     FROM [AKUZ].T_AMBULANCE_CARD ac
	--	                 join [AKUZ].T_PATIENT p on ac.Patient=p.PatientID 
	--	                 where (ac.Number=fv.d_amb_kart and (Select top 1 TFomsCode From [AKUZ].T_LPU_MAIN Where LpuID = ac.CardLpu)=@LpuCode)),GETDATE())<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.TFomsCode=@LpuCode)
	-- then 'B69E3D1C-A50C-4AD6-A756-8B1409E2C481' --  U8905
	--when DATEDIFF(year, (SELECT top 1 p.Birthday 
	--                     FROM [AKUZ].T_AMBULANCE_CARD ac
	--	                 join [AKUZ].T_PATIENT p on ac.Patient=p.PatientID 
	--	                 where (ac.Number=fv.d_amb_kart and (Select top 1 TFomsCode From [AKUZ].T_LPU_MAIN Where LpuID = ac.CardLpu)=@LpuCode)),GETDATE())>=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.TFomsCode=@LpuCode) then '1E2F2F5A-5257-4C25-88FB-FE2E05FEAB42' --U8901
	--else null
	--end 
	From @FirstVisits fv  
		join @ReVisits re ON (fv.fv_id = re.fv_id)
		join @Events 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)

--AMBULANCE_SURGICAL_OPERATION @type=578
--AKUZ.T_MEDICAL_RECORD
--AKUZ.T_AMBULANCE_CARD_RECORD
--AKUZ.T_AMBULANCE_SURGICAL_OPERATION

	insert [AKUZ].[T_MEDICAL_RECORD]
	(
	  MedicalRecordID
	 ,Post
	 ,type_
	 ,RecordDate
	 ,RecordTime
	)
	select
	  usls.MedicalRecordID
	, (
	select top 1 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=@LpuCode and rv.re_cod_vr=p.Code and DateBegin<=rv.re_date_tal and (rv.re_date_tal<=DateEnd or DateEnd is NULL)) order by p.ResourceID
	  )
	, 578
	, (select DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
    , GETDATE()
	from @ReVisits rv
		join @Usls usls on usls.VisitID = rv.re_id

    INSERT AKUZ.T_AMBULANCE_CARD_RECORD(MedicalRecordID,AmbulanceCard,[PatientBase],AmbulanceEvent, PostingDate)     -- изм. от 30.03.2023  [PatientBase],
	select
		usls.MedicalRecordID
		,(
		SELECT top 1 AmbulanceCardID FROM [AKUZ].T_AMBULANCE_CARD ac 
		where 
		(ac.Number=fv.d_amb_kart and (Select top 1 TFomsCode From [AKUZ].T_LPU_MAIN Where LpuID = ac.CardLpu)=@LpuCode) 
	    )
		,(
		SELECT top 1 [Patient] FROM [AKUZ].T_AMBULANCE_CARD ac 
		where 
		(ac.Number=fv.d_amb_kart and (Select top 1 TFomsCode From [AKUZ].T_LPU_MAIN Where LpuID = ac.CardLpu)=@LpuCode) 
	    )
        ,e.[VitaCase]
		,Min(fv.d_diag_date)
	from @ReVisits rv	
		join @Usls usls on usls.VisitID = rv.re_id
        join @FirstVisits fv ON (fv.fv_id = rv.fv_id)
        join @Events e on e.[id] = fv.[event_id]
    group by usls.MedicalRecordID, fv.d_amb_kart, e.[VitaCase]

	INSERT [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 top 1 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=@LpuCode and rv.re_cod_vr=p.Code and DateBegin<=rv.re_date_tal and (rv.re_date_tal<=DateEnd or DateEnd is NULL)) order by p.ResourceID
	  )
	from @ReVisits rv	
		join @Usls usls on usls.VisitID = rv.re_id

-----------------------------------------------------

end

select 'Success' as lit, '' as Errors

end

END