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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

declare @version varchar(10)
select top 1 @version = t1.[VERSION] from ies.T_SCHET t1 with(nolock) where t1.SchetID = @SchetID

IF (@version like '2%' or @version = '3.0')
BEGIN
	UPDATE ss 
	SET ss.SANK_MEK = case when outerSank.S_SUM_MEK > ss.SUMV then ss.SUMV else outerSank.S_SUM_MEK end
	,ss.SANK_MEE = isnull(sanks.S_SUM_MEE, isnull(outerSank.S_SUM_MEE, 0))
	,ss.SANK_EKMP = isnull(sanks.S_SUM_EKMP, isnull(outerSank.S_SUM_EKMP, 0))
	,ss.SANK_IT = ISNULL(t2.SANK_IT, ISNULL(sanks.S_SUM_MEE + sanks.S_SUM_EKMP, 0))
	,ss.SUMP = case when ss.SUMV - isnull(outerSank.S_SUM_MEK, 0) - isnull(sanks.S_SUM_MEE, isnull(outerSank.S_SUM_MEE, 0)) - isnull(sanks.S_SUM_EKMP, isnull(outerSank.S_SUM_EKMP, 0)) < 0 then 0 
	else ss.SUMV - isnull(outerSank.S_SUM_MEK, 0) - isnull(sanks.S_SUM_MEE, isnull(outerSank.S_SUM_MEE, 0)) - isnull(sanks.S_SUM_EKMP, isnull(outerSank.S_SUM_EKMP, 0)) end
	,ss.OPLATA = case when outerSank.S_SUM_MEK + isnull(outerSank.S_SUM_MEE, 0) + isnull(outerSank.S_SUM_EKMP, 0) = 0 then 1
				 when outerSank.S_SUM_MEK <> 0 then 2 
			     else 3 end 
	FROM [IES].T_SCHET_SLUCH ss with(nolock) 
	JOIN [IES].T_SCHET_ZAP sz with(nolock)  ON sz.SchetZapID = ss.SchetZap
	LEFT JOIN [IES].T_OUTER_ACT_ITEM t2 with(nolock) ON t2.SchetSluchAccomplished = ss.SchetSluchAccomplished
	outer apply (select SUM(case when aa.ControlType = 0 then ISNULL(sank.S_SUM, 0) else 0 end) as S_SUM_MEK,
						SUM(case when aa.ControlType = 1 then ISNULL(sank.S_SUM, 0) else 0 end) as S_SUM_MEE,
						SUM(case when aa.ControlType = 2 then ISNULL(sank.S_SUM, 0) else 0 end) as S_SUM_EKMP 
				from IES.T_OUTER_ERROR sank with (NOLOCK)
				cross apply(select top 1 * from ies.T_F006_CONTROL_TYPE f006 with(nolock) where sank.[SanctionType] = f006.F006ControlTypeID) aa
	where sank.OuterActItem = t2.OuterActItemID) outerSank
	outer apply (select SUM(case when aa.ControlType = 0 then ISNULL(sank.S_SUM, 0) else 0 end) as S_SUM_MEK,
						SUM(case when aa.ControlType = 1 then ISNULL(sank.S_SUM, 0) else 0 end) as S_SUM_MEE,
						SUM(case when aa.ControlType = 2 then ISNULL(sank.S_SUM, 0) else 0 end) as S_SUM_EKMP 
				from IES.T_SCHET_SLUCH_SANK sank with (NOLOCK)
				cross apply(select top 1 * from ies.T_F006_CONTROL_TYPE f006 with(nolock) where sank.S_TIP = f006.IDVID 
				--and f006.DATEBEG<=isnull(sank.DATE_ACT, ss.DATE_2) and (f006.DATEEND is null or f006.DATEEND>=isnull(sank.DATE_ACT, ss.DATE_2))
				) aa
	where sank.SchetSluch = ss.SchetSluchID) sanks
	WHERE sz.Schet = @SchetID and ss.IsDelete = 0

	UPDATE ssa
	SET
		 ssa.SUMP = case when sluch.SUMP <= 0 then 0 else sluch.SUMP - Isnull(outerSank.S_SUM_MEK, ISNULL(sluch.SANK_MEK, 0)) end
		,ssa.SANK_MEK = isnull(outerSank.S_SUM_MEK, sluch.SANK_MEK)
		,ssa.SANK_MEE = sluch.SANK_MEE
		,ssa.SANK_EKMP= sluch.SANK_EKMP
		,ssa.SANK_IT = isnull(ssa.SANK_IT, sluch.SANK_IT)
		,ssa.OPLATA = case when (sluch.SUMP = 0 and sluch.SUMV <> sluch.SUMP) or (ssa.SUMV - isnull(ssa.SANK_MEE, 0) - isnull(ssa.SANK_EKMP, 0) <= 0) then 2
			            when sluch.SUMP <> 0 and sluch.SUMV <> sluch.SUMP then 3
				        else 1 end
	FROM [IES].T_SCHET_ZAP sz with(nolock)
	join [ies].T_SCHET_SLUCH_ACCOMPLISHED ssa WITH(nolock) ON ssa.SchetZap =sz.SchetZapID 
	cross apply (select SUM(ss.SUMP) as SUMP
						, SUM(ss.SUMV) as SUMV
						, SUM(ss.SANK_MEK) as SANK_MEK
						, SUM(ss.SANK_MEE) as SANK_MEE
						, SUM(ss.SANK_EKMP) as SANK_EKMP
						, SUM(ss.SANK_IT) as SANK_IT
				from [IES].T_SCHET_SLUCH ss with(nolock) where ss.SchetZap = sz.SchetZapID and ss.IsDelete = 0) sluch
	left join [IES].T_OUTER_ACT_ITEM oai with(nolock) ON oai.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID
	outer apply (select SUM(case when aa.ControlType = 0 then ISNULL(sank.S_SUM, 0) else 0 end) as S_SUM_MEK,
						SUM(case when aa.ControlType = 1 then ISNULL(sank.S_SUM, 0) else 0 end) as S_SUM_MEE,
						SUM(case when aa.ControlType = 2 then ISNULL(sank.S_SUM, 0) else 0 end) as S_SUM_EKMP 
				from IES.T_OUTER_ERROR sank with (NOLOCK)
				cross apply(select top 1 * from ies.T_F006_CONTROL_TYPE f006 with(nolock) where sank.[SanctionType] = f006.F006ControlTypeID) aa
				where sank.OuterActItem = oai.OuterActItemID) outerSank
	WHERE sz.Schet = @SchetID and sz.IsDelete = 0

    UPDATE [IES].T_SCHET SET SUMMAV = ISNULL(					
										(SELECT SUM(t1.SUMV)
										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)
										,0
										)
					, SUMMAP = ISNULL(					
										(SELECT SUM(t1.SUMP)
										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)
										,0
										)
					,SANK_MEK= ISNULL(					
										--ISNULL(					
										--(SELECT SUM(t1.SANK_IT)
										--FROM [IES].T_SCHET_SLUCH t1 with(nolock) 
										--JOIN [IES].T_SCHET_ZAP t2 with(nolock)  ON t2.SchetZapID = t1.SchetZap
										--LEFT JOIN [IES].T_SCHET_SLUCH_SANK sss with(nolock) on t1.SchetSluchID = sss.SchetSluch
										--WHERE t2.Schet = @SchetID and t1.SANK_IT is not null and ISNULL(sss.S_TIP, 1) = 1)
										--,0
										--)
										--+
										ISNULL(		
										(SELECT  SUM(t1.SANK_MEK)
										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)
										,0)
										,0)
					, SANK_MEE = ISNULL(					
										(SELECT SUM(t1.SANK_MEE)
										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)
										,0
										)
					, SANK_EKMP = ISNULL(					
										(SELECT SUM(t1.SANK_EKMP)
										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)
										,0
										)
	WHERE SchetID = @SchetID
END
ELSE
BEGIN
	UPDATE ssa
	SET  ssa.SUMP = case when oai.SchetSluchAccomplished is not null 
			then ssa.SUMV - isnull(oai.SANK_IT, isnull(sanks.S_SUM_MEK, 0) + isnull(sanks.S_SUM_MEE, 0) + isnull(sanks.S_SUM_EKMP, 0))
			else ssa.SUMV - isnull(sanks.S_SUM_MEK, 0) - isnull(sanks.S_SUM_MEE, 0) - isnull(sanks.S_SUM_EKMP, 0) end
		,ssa.SANK_MEK =case when oai.SchetSluchAccomplished is not null then 
			case when sanks.S_SUM_MEK <> 0 then isnull(sanks.S_SUM_MEK,0) else isnull(outerSank.S_SUM_MEK,0) end
		else isnull(sanks.S_SUM_MEK,0) end
		,ssa.SANK_MEE = case when oai.SchetSluchAccomplished is not null then 
			case when sanks.S_SUM_MEE <> 0 then isnull(sanks.S_SUM_MEE,0) else isnull(outerSank.S_SUM_MEE,0) end
		else isnull(sanks.S_SUM_MEE,0) end
		,ssa.SANK_EKMP =  case when oai.SchetSluchAccomplished is not null then 
			case when sanks.S_SUM_EKMP <> 0 then isnull(sanks.S_SUM_EKMP,0) else isnull(outerSank.S_SUM_EKMP,0) end
		else isnull(sanks.S_SUM_EKMP,0) end
		,ssa.SANK_IT = case when oai.SchetSluchAccomplished is not null then ISNULL(oai.SANK_IT, outerSank.S_SUM_MEK + outerSank.S_SUM_MEE + outerSank.S_SUM_EKMP) else sanks.S_SUM_MEK + sanks.S_SUM_MEE + sanks.S_SUM_EKMP end
		,ssa.OPLATA = case when (sluch.SUMP = 0 and sluch.SUMV <> sluch.SUMP) or (ssa.SUMV - isnull(ssa.SANK_MEE, 0) - isnull(ssa.SANK_EKMP, 0) <= 0) then 2
			            when sluch.SUMP <> 0 and sluch.SUMV <> sluch.SUMP then 3
				        else 1 end
		FROM [IES].T_SCHET_ZAP sz with(nolock)
		join [IES].T_SCHET_SLUCH_ACCOMPLISHED ssa with(nolock) ON ssa.SchetZap = sz.SchetZapID 
		cross apply (select SUM(ss.SUMP) as SUMP
						  , SUM(ss.SUMV) as SUMV
						  , SUM(ss.SANK_MEK) as SANK_MEK
						  , SUM(ss.SANK_MEE) as SANK_MEE
						  , SUM(ss.SANK_EKMP) as SANK_EKMP 
						  , SUM(ss.SANK_IT) as SANK_IT 
					from [IES].T_SCHET_SLUCH ss with(nolock) where ss.SchetZap = sz.SchetZapID) sluch
		LEFT JOIN [IES].T_OUTER_ACT_ITEM oai with(nolock) ON oai.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID
		outer apply (select SUM(case when aa.ControlType = 0 then ISNULL(sank.S_SUM, 0) else 0 end) as S_SUM_MEK,
							SUM(case when aa.ControlType = 1 then ISNULL(sank.S_SUM, 0) else 0 end) as S_SUM_MEE,
							SUM(case when aa.ControlType = 2 then ISNULL(sank.S_SUM, 0) else 0 end) as S_SUM_EKMP 
					from IES.T_OUTER_ERROR sank with (NOLOCK)
					cross apply(select top 1 * from ies.T_F006_CONTROL_TYPE f006 with(nolock) where sank.[SanctionType] = f006.F006ControlTypeID) aa
					where sank.OuterActItem = oai.OuterActItemID) outerSank
		outer apply (select SUM(case when aa.ControlType = 0 then ISNULL(sank.S_SUM, 0) else 0 end) as S_SUM_MEK,
							SUM(case when aa.ControlType = 1 then ISNULL(sank.S_SUM, 0) else 0 end) as S_SUM_MEE,
							SUM(case when aa.ControlType = 2 then ISNULL(sank.S_SUM, 0) else 0 end) as S_SUM_EKMP 
					from IES.T_SCHET_SLUCH_SANK sank with (NOLOCK)
					cross apply(select top 1 * from ies.T_F006_CONTROL_TYPE f006 with(nolock) where sank.S_TIP = f006.IDVID 
					--and f006.DATEBEG<=isnull(sank.DATE_ACT, ssa.DATE_Z_2) and (f006.DATEEND is null or f006.DATEEND>=isnull(sank.DATE_ACT, ssa.DATE_Z_2))
					) aa
					where sank.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID) sanks
		WHERE sz.Schet = @SchetID and ssa.IsDelete = 0

		update ssa
		set 
		 SUMV = 0
		,SUMP = 0
		,SANK_MEK = 0
		,SANK_MEE = 0
		,SANK_EKMP = 0
		,SANK_IT = 0
		from ies.T_SCHET_ZAP sz  with(nolock)
		join ies.T_SCHET_SLUCH_ACCOMPLISHED ssa with(nolock) on sz.SchetZapID = ssa.SchetZap
		WHERE sz.Schet = @SchetID and sz.IsDelete = 0 AND NOT EXISTS(SELECT * FROM [IES].T_SCHET_SLUCH t1  with(nolock) WHERE t1.SchetZap = sz.SchetZapID)

		--ssa.SUMV
		update ss
		set 
		 SUMP = 0
		,SANK_MEK = 0
		,SANK_MEE = 0
		,SANK_EKMP = 0
		,SANK_IT = 0
		,OPLATA = case
			when oes.OuterErrorSlID is not null then 2
			else 1
		end
		from ies.T_SCHET_SLUCH ss with(nolock)
		join ies.T_SCHET_ZAP sz  with(nolock) on sz.SchetZapID = ss.SchetZap		
		join ies.T_SCHET_SLUCH_ACCOMPLISHED ssa with(nolock) on ss.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID
		left join ies.T_OUTER_ACT_ITEM oai with(nolock) on oai.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID
		left join ies.T_OUTER_ERROR oe with(nolock) on oe.OuterActItem = oai.OuterActItemID
		left join ies.T_OUTER_ERROR_SL oes with(nolock) on oes.OuterError = oe.OuterErrorID and ss.SchetSluchID = oes.SchetSluch
		WHERE sz.Schet = @SchetID and ss.IsDelete = 0 and ssa.SUMV = 0
		
		--ssa.SUMV
		update ss
		set 
		SUMP = ROUND(ss.SUMV / ssa.SUMV * ssa.SUMP,2)
		,SANK_MEK = ROUND(ss.SUMV / ssa.SUMV * ssa.SANK_MEK,2) 
		,SANK_MEE = ROUND(ss.SUMV / ssa.SUMV * ssa.SANK_MEE,2) 
		,SANK_EKMP = ROUND(ss.SUMV / ssa.SUMV * ssa.SANK_EKMP,2)
		,SANK_IT = ROUND(ss.SUMV / ssa.SUMV * ssa.SANK_IT,2)
		from ies.T_SCHET_SLUCH ss with(nolock)
		join ies.T_SCHET_ZAP sz  with(nolock) on sz.SchetZapID = ss.SchetZap		
		join ies.T_SCHET_SLUCH_ACCOMPLISHED ssa with(nolock) on ss.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID
		left join ies.T_OUTER_ACT_ITEM oai with(nolock) on oai.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID
		left join ies.T_OUTER_ERROR oe with(nolock) on oe.OuterActItem = oai.OuterActItemID
		left join ies.T_OUTER_ERROR_SL oes with(nolock) on oes.OuterError = oe.OuterErrorID and ss.SchetSluchID = oes.SchetSluch
		WHERE sz.Schet = @SchetID and ss.IsDelete = 0 and ssa.SUMV != 0

		update ss
		set ss.OPLATA = case
			when isnull(ss.SUMP,0) = 0 then 2
			when ss.SUMP + 0.01 >= ss.SUMV then 1
			when ss.SUMP + 0.01 < ss.SUMV then 3
			else null
		end
		from ies.T_SCHET_SLUCH ss with(nolock)
		join ies.T_SCHET_ZAP sz  with(nolock) on sz.SchetZapID = ss.SchetZap
		WHERE sz.Schet = @SchetID and ss.IsDelete = 0
				
		UPDATE t1 SET 
			   SUMMAV = ISNULL(aa.S_SUMV, 0)
			  ,SUMMAP = ISNULL(aa.S_SUMP, 0)
			  ,SANK_MEK= ISNULL(aa.S_SANK_MEK,0)
			  ,SANK_MEE= ISNULL(aa.S_SANK_MEE,0)
			  ,SANK_EKMP= ISNULL(aa.S_SANK_EKMP,0)
			  ,[VERSION] = case when ([YEAR] = 2022 and [MONTH] = 12) or [YEAR] > 2022 then '3.3'
								when ([YEAR]=2019 and [MONTH]>=11) or [YEAR]>2019 then '3.2'
								when ([YEAR]=2019 and [MONTH]<11) then '3.1' 
								else t1.[VERSION] end
		from [IES].T_SCHET t1
		outer apply(SELECT SUM(isnull(ssa.SUMV,0)) as S_SUMV
						 , SUM(isnull(ssa.SUMP,0)) as S_SUMP
						 , SUM(isnull(ssa.SANK_MEK,0)) as S_SANK_MEK
						 , SUM(isnull(ssa.SANK_MEE,0)) as S_SANK_MEE
						 , SUM(isnull(ssa.SANK_EKMP,0)) as S_SANK_EKMP
						FROM [IES].T_SCHET_SLUCH_ACCOMPLISHED ssa with(nolock) 
						JOIN [IES].T_SCHET_ZAP sz with(nolock) ON sz.SchetZapID = ssa.SchetZap
						WHERE sz.Schet = t1.SchetID and ssa.isdelete = 0 and sz.isdelete = 0) aa
		WHERE t1.SchetID = @SchetID
END
END
