if exists ( select * from sys.all_objects ao where ao.name = 'sp_SchetOutIDRecalc' ) DROP PROCEDURE [dbo].[sp_SchetOutIDRecalc]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_SchetOutIDRecalc]
  @SchetID uniqueidentifier
AS
BEGIN 

   declare @SchetKind int

   select @SchetKind = SchetKind 
   from ies.T_SCHET s where s.SchetID=@SchetID

   DELETE ss
   FROM [IES].T_SCHET_ZAP t0
   JOIN [IES].T_SCHET_SLUCH_ACCOMPLISHED acc on (acc.SchetZap = t0.SchetZapID)
   JOIN [IES].T_SCHET_SLUCH ss on ss.SchetSluchAccomplished = acc.SchetSluchAccomplishedID
   WHERE t0.Schet = @SchetID
   AND ss.IsDelete = 1

   DELETE acc
   FROM [IES].T_SCHET_ZAP t0
   JOIN [IES].T_SCHET_SLUCH_ACCOMPLISHED acc on (acc.SchetZap = t0.SchetZapID)
   WHERE t0.Schet = @SchetID
   AND NOT EXISTS(SELECT * FROM [IES].T_SCHET_SLUCH t1  with(nolock) WHERE t1.SchetZap = t0.SchetZapID and t1.isdelete = 0)
   
   DELETE t0
   FROM [IES].T_SCHET_ZAP t0
   WHERE t0.Schet = @SchetID
   AND NOT EXISTS(SELECT * FROM [IES].T_SCHET_SLUCH t1  with(nolock) WHERE t1.SchetZap = t0.SchetZapID and t1.isdelete = 0)

if @SchetKind = 2
begin
	update ss
	set SL_ID = ssMain.SL_ID
		,IDCASE = ssMain.IDCASE
		,SchetSluchMain = ssMain.SchetSluchID
	from ies.T_SCHET_ZAP sz
	join ies.T_SCHET_SLUCH ss on (sz.schetzapid = ss.schetzap)
	join ies.T_SCHET s on (sz.schet = s.schetid)
	join ies.T_SCHET sMain on (s.SchetMain = sMain.SchetID and sMain.SchetKind = 0 and sMain.isdelete = 0)
	join ies.T_SCHET_ZAP szMain on (sMain.SchetID = szMain.Schet)
	join ies.T_SCHET_SLUCH ssMain on (ssMain.SchetZap = szMain.SchetzapID and ssMain.OmsSchetSluch = ss.OmsSchetSluch and ssMain.IsDelete = 0)
	where s.SchetID = @SchetID and ss.IsDelete = 0

	update sz
	set N_ZAP = szMain.N_ZAP
	from ies.T_SCHET_ZAP sz
	join ies.T_SCHET_SLUCH ss on (sz.schetzapid = ss.schetzap)
	join ies.T_SCHET s on (sz.schet = s.schetid)
	join ies.T_SCHET_SLUCH ssMain on (ssMain.SchetSluchID = ss.SchetSluchMain)
	join ies.T_SCHET_ZAP szMain on (ssMain.SchetZap = szMain.SchetZapID)
	where s.SchetID = @SchetID and ss.IsDelete = 0

	update ssa
	set IDCASE = ssaMain.IDCASE
	from ies.T_SCHET_ZAP sz
	join ies.T_SCHET_SLUCH ss on (sz.schetzapid = ss.schetzap)
	join ies.T_SCHET_SLUCH_ACCOMPLISHED ssa on (ss.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID)
	join ies.T_SCHET s on (sz.schet = s.schetid)
	join ies.T_SCHET_SLUCH ssMain on (ssMain.SchetSluchID = ss.SchetSluchMain)
	join ies.T_SCHET_SLUCH_ACCOMPLISHED ssaMain on (ssMain.SchetSluchAccomplished = ssaMain.SchetSluchAccomplishedID)
	where s.SchetID = @SchetID and ss.IsDelete = 0
end

if @SchetKind = 0
begin

    UPDATE t0 SET t0.N_ZAP = t2.ID
	FROM [IES].T_SCHET_ZAP t0 with(nolock) 
	JOIN 
	(
		Select ROW_NUMBER() OVER(ORDER BY FAM, IM, OT, DR, t1.SchetZapID) AS ID, t1.SchetZapID from [IES].T_SCHET_ZAP t1 with(nolock)  where Schet = @SchetID and t1.IsDelete = 0
	) t2 ON t2.SchetZapID = t0.SchetZapID
	where t0.IsDelete = 0 

	UPDATE t0 SET t0.IDCASE = t3.ID
	FROM [IES].T_SCHET_SLUCH_ACCOMPLISHED t0 with(nolock) 
	JOIN
	(
		Select ROW_NUMBER() OVER(ORDER BY t2.N_ZAP, t1.DATE_Z_1, t1.SchetSluchAccomplishedID) AS ID, t1.SchetSluchAccomplishedID 
		from [IES].T_SCHET_SLUCH_ACCOMPLISHED t1 with(nolock) 
		JOIN [IES].T_SCHET_ZAP t2 with(nolock)  ON t2.SchetZapID = t1.SchetZap 
		where t2.Schet = @SchetID and t1.IsDelete = 0
	) t3 ON t3.SchetSluchAccomplishedID = t0.SchetSluchAccomplishedID
	where t0.IsDelete = 0

	UPDATE t0 SET t0.SL_ID = t3.ID, t0.IDCASE = t3.ID
	FROM [IES].T_SCHET_SLUCH t0 with(nolock) 
	JOIN
	(
		Select ROW_NUMBER() OVER(ORDER BY t2.N_ZAP, t1.DATE_1, t1.SchetSluchID) AS ID, t1.SchetSluchID 
		from [IES].T_SCHET_SLUCH t1 with(nolock) 
		JOIN [IES].T_SCHET_ZAP t2 with(nolock)  ON t2.SchetZapID = t1.SchetZap 
		where t2.Schet = @SchetID and t1.isdelete = 0
	) t3 ON t3.SchetSluchID = t0.SchetSluchID
	where t0.IsDelete = 0

	--UPDATE t0 SET t0.S_CODE = t3.ID
	--FROM [IES].T_SCHET_SLUCH_SANK t0 with(nolock) 
	--JOIN
	--(
	--	Select ROW_NUMBER() OVER(ORDER BY t2.N_ZAP, t1.IDCASE, t1.DATE_1, sss.SchetSluchSankID) AS ID, sss.SchetSluchSankID 
	--	from [IES].T_SCHET_SLUCH t1 with(nolock) 
	--	JOIN [IES].T_SCHET_ZAP t2 with(nolock)  ON t2.SchetZapID = t1.SchetZap 
	--	JOIN [IES].T_SCHET_SLUCH_SANK sss ON (t1.SchetSluchID = sss.SchetSluch)
	--	where t2.Schet = @SchetID and sss.isdelete = 0 and t1.isdelete = 0
	--) t3 ON t3.SchetSluchSankID = t0.SchetSluchSankID
	--where t0.IsDelete = 0
 
	select sanks.* into #sanks
	from(
		select isnull(sss.SchetSluchSankID,newid()) as 'SchetSluchSankID'
		,ss.SchetSluchAccomplished as 'mtrSchetSluchAccomplished'
		,ss.SchetSluchID as 'mtrSchetSluchID'
		,mr.Expert
		,oms.SchetSluchID
		,f006.IDVID
		,er.MedicalErrorID
		,er.DenyReason
		,row_number() over (partition by f006.IDVID,ss.SchetSluchAccomplished order by mr.Expert desc) as id
		from ies.T_SCHET s
		join ies.T_SCHET_ZAP sz on s.SchetID = sz.Schet and sz.isDelete = 0
		join ies.T_SCHET_SLUCH ss on ss.SchetZap = sz.SchetZapID and ss.isDelete = 0
		join ies.T_SCHET_SLUCH oms on oms.SchetSluchID = ss.OmsSchetSluch and oms.isDelete = 0
		join IES.T_MEDICAL_EXPERTISE_ITEM mei on oms.SchetSluchAccomplished = mei.SchetSluchAccomplished
		join IES.T_MEDICAL_EXPERTISE_SCHET mes on mes.MedicalExpertiseSchetID = mei.MedicalExpertiseSchet
		join IES.T_MEDICAL_EXPERTISE me on me.MedicalExpertiseID = mes.MedicalExpertise
		join IES.T_F006_CONTROL_TYPE f006 on f006.F006ControlTypeID = me.F006ControlType
		outer apply (select top 1 sss.SchetSluchSankID from ies.T_SCHET_SLUCH_SANK sss where sss.SchetSluch = ss.SchetSluchID and sss.S_TIP = f006.IDVID)sss
		outer apply (select top 1 mri.* from ies.T_MEDICAL_REQUEST_ITEM mri where mri.SchetSluchAccomplished = oms.SchetSluchAccomplished)mri
		outer apply (select top 1 mr.* from ies.T_MEDICAL_REQUEST mr where mri.MedicalRequest = mr.MedicalRequestID and mr.MedicalExpertise = me.MedicalExpertiseID)mr
		outer apply (select top 1 ma.* from ies.T_MEDICAL_ACT ma where ma.MedicalRequest = mr.MedicalRequestID)ma
		outer apply (select top 1 er.* from ies.T_MEDICAL_ERROR er where er.MedicalRequest = mr.MedicalRequestID and er.IsMainError = 1)er
		where sz.Schet = @SchetID
		and ma.medicalactid is null
		and s.isDelete = 0 and s.SchetKind = 0 and ((s.[YEAR]=2022 and s.[MONTH]>=11) or s.[YEAR]>2022)	
	)sanks
	where sanks.id = 1

	INSERT INTO [IES].[T_SCHET_SLUCH_SANK]
	([SchetSluchSankID]
	,[SchetSluch]
	,[S_CODE]
	,[S_SUM]
	,[S_TIP]
	,[S_IST]
	,[MedicalError]
	,[S_OSN]
	,[SchetSluchAccomplished]
	,[S_TIP_F006]
	)
	select 
	 tt.SchetSluchSankID
	,ss.SchetSluchID
	,tt.SchetSluchSankID
	,0
	,tt.IDVID
	,5
	,tt.MedicalErrorID
	,case when tt.IDVID = '73' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='35.3')	
		  when tt.IDVID = '84' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='37.6')	
		  when tt.IDVID = '78' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='35.8')	
		  when tt.IDVID = '58' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='22.7')	
		  when tt.IDVID = '83' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='37.5')	
		  when tt.IDVID = '76' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='35.6')	
		  when tt.IDVID = '72' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='35.2')	
		  when tt.IDVID = '81' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='37.2')	
		  when tt.IDVID = '77' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='35.7')	
		  when tt.IDVID = '71' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='35.1')	
		  when tt.IDVID = '80' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='37.2')	
		  when tt.IDVID = '53' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='22.2')	
		  when tt.IDVID = '82' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='37.4')	
		  when tt.IDVID = '75' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='35.5')	
		  when tt.IDVID = '56' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='22.5')	
		  when tt.IDVID = '79' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='37.1')	
		  when tt.IDVID = '74' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='35.4')	
		  when tt.IDVID = '92' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='22.4')	
		  when tt.IDVID = '90' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='22.1')	
		  when tt.IDVID = '89' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='37.7')	
		  when tt.IDVID = '93' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='22.6')	
		  when tt.IDVID = '88' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='22.8')	
		  when tt.IDVID = '91' then (select top 1 f014.F014DenyReasonID from ies.T_F014_DENY_REASON f014 where f014.Osn ='22.3')	
		  else tt.DenyReason end
	,ss.SchetSluchAccomplished
	,(select top 1 f006.F006ControlTypeID from ies.T_F006_CONTROL_TYPE f006 where f006.IDVID = tt.IDVID and f006.MigratedEntityName is null)
	from #sanks tt
	join IES.T_SCHET_SLUCH ss on tt.mtrSchetSluchID = ss.SchetSluchID and ss.isDelete = 0
	where not exists(select * from ies.T_SCHET_SLUCH_SANK sss where sss.SchetSluchAccomplished = ss.SchetSluchAccomplished and sss.S_IST = 5 and sss.S_SUM = 0 and sss.isDelete = 0)

	INSERT INTO [IES].[T_SCHET_SLUCH_SANK_EXP]
	([SchetSluchSankExpID]
	,[SchetSluchSank]
	,[F004Expert]
	)
	select
	 tt.SchetSluchSankID
	,sss.SchetSluchSankID
	,tt.Expert
	from #sanks tt
	join IES.T_SCHET_SLUCH_SANK sss on tt.mtrSchetSluchID = sss.SchetSluch and tt.SchetSluchSankID = sss.SchetSluchSankID and sss.isDelete = 0
	where tt.Expert is not null
	and not exists(select * from ies.T_SCHET_SLUCH_SANK_EXP se where se.SchetSluchSank = sss.SchetSluchSankID)
	
	drop table #sanks
end 

END