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

CREATE PROCEDURE [dbo].[sp_SchetRecalc]
  @SchetID uniqueidentifier,
  @DateTime datetime = null
AS 
BEGIN
   		
declare @version varchar(10), @type_ int, @filename varchar(50)
select top 1 @version = t1.[Version], @filename = t1.[FILENAME], @type_ = t1.type_ from ies.T_SCHET t1 with(nolock) where t1.SchetID = @SchetID

--заполням законченный случай, для отчетности и соответсвия формату 79 приказа редакции 2018 года
update ssa set  
	   ssa.SUMV = sluch.SUMV
	  ,ssa.SUMP = case when actitem.IsAddToAct is null then sluch.SUMV - isnull(sss.SANK_MEK_POVT,0) - isnull(sss.SANK_MEE,0) - isnull(sss.SANK_EKMP,0) else 0 end
	  ,ssa.SANK_MEK = case when actitem.IsAddToAct is null then 0 else sluch.SUMV - isnull(sss.SANK_MEK_POVT,0) - isnull(sss.SANK_MEE,0) - isnull(sss.SANK_EKMP,0) end
      ,ssa.SANK_MEE = isnull(sss.SANK_MEE,0)
      ,ssa.SANK_EKMP = isnull(sss.SANK_EKMP,0)
	  ,ssa.SANK_MEK_POVT = isnull(sss.SANK_MEK_POVT,0)
	  ,ssa.SANK_IT = case when actitem.IsAddToAct is not null then sluch.SUMV else isnull(sss.SANK_MEK_POVT,0) + isnull(sss.SANK_MEE,0) + isnull(sss.SANK_EKMP,0) end
	  ,ssa.OPLATA = case when actitem.IsAddToAct is not null or (isnull(sss.SANK_MEK_POVT,0) + isnull(sss.SANK_MEE,0) + isnull(sss.SANK_EKMP,0) > 0 and sluch.SUMV <= isnull(sss.SANK_MEK_POVT,0) + isnull(sss.SANK_MEE,0) + isnull(sss.SANK_EKMP,0)) then 2
			        when isnull(sss.SANK_MEK_POVT,0) + isnull(sss.SANK_MEE,0) + isnull(sss.SANK_EKMP,0) = 0 then 1
				    else 3 end 
from IES.[T_SCHET_ZAP] sz
cross apply(select SUM(ISNULL(ss.SUMV,0)) as SUMV, ss.SchetSluchAccomplished from IES.[T_SCHET_SLUCH] ss with (NOLOCK) where (sz.SchetZapID = ss.SchetZap) group by ss.SchetSluchAccomplished)sluch
join IES.[T_SCHET_SLUCH_ACCOMPLISHED] ssa on (ssa.SchetSluchAccomplishedID = sluch.SchetSluchAccomplished)
outer apply (select top 1 sai.IsAddToAct from IES.T_SCHET_ACT_ITEM sai with (NOLOCK) where sai.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID and sai.IsAddToAct = 1) actitem
outer apply (
			SELECT SUM(CASE WHEN f006.ControlType = 0 THEN ISNULL(sank.S_SUM, 0) ELSE 0 END) AS SANK_MEK_POVT
				 , SUM(CASE WHEN f006.ControlType = 1 THEN ISNULL(sank.S_SUM, 0) ELSE 0 END) AS SANK_MEE
				 , SUM(CASE WHEN f006.ControlType = 2 THEN ISNULL(sank.S_SUM, 0) ELSE 0 END) AS SANK_EKMP
			FROM IES.T_SCHET_SLUCH_SANK sank WITH(NOLOCK)
			CROSS APPLY (
				SELECT TOP 1 f006.ControlType
				FROM ies.t_f006_control_type f006 WITH(NOLOCK)
				WHERE f006.F006ControlTypeID = sank.S_TIP_F006
				) f006
			WHERE sank.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID
			) sss
where sz.Schet = @SchetID

--заполняем инфу на случае, для себя, МЭЭ, ЭКМП, пМЭК выставляются в коде
update ss set 
	   ss.SUMP = case when actitem.IsAddToAct is null then ss.SUMV - isnull(sss.SANK_MEK_POVT,0) - isnull(sss.SANK_MEE,0) - isnull(sss.SANK_EKMP,0) else 0 end
	  ,ss.SANK_MEK = case when actitem.IsAddToAct is null then 0 else ss.SUMV - isnull(sss.SANK_MEK_POVT,0) - isnull(sss.SANK_MEE,0) - isnull(sss.SANK_EKMP,0) end
      ,ss.SANK_MEE = isnull(sss.SANK_MEE,0)
      ,ss.SANK_EKMP = isnull(sss.SANK_EKMP,0)
	  ,ss.SANK_MEK_POVT = isnull(sss.SANK_MEK_POVT,0)
	  ,ss.SANK_IT = case when actitem.IsAddToAct is not null then ss.SUMV else isnull(sss.SANK_MEK_POVT,0) + isnull(sss.SANK_MEE,0) + isnull(sss.SANK_EKMP,0) end
	  ,ss.OPLATA = case when actitem.IsAddToAct is not null or ss.SUMV <= isnull(sss.SANK_MEK_POVT,0) + isnull(sss.SANK_MEE,0) + isnull(sss.SANK_EKMP,0) then 2
				   when isnull(sss.SANK_MEK_POVT,0) + isnull(sss.SANK_MEE,0) + isnull(sss.SANK_EKMP,0) = 0 then 1
				   else 3 end
from IES.[T_SCHET_SLUCH] ss
join IES.[T_SCHET_ZAP] sz on (ss.SchetZap = sz.SchetZapID)
join IES.[T_SCHET_SLUCH_ACCOMPLISHED] ssa on (ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished)
outer apply (select top 1 sai.IsAddToAct from IES.T_SCHET_ACT_ITEM sai with (NOLOCK) where sai.SchetSluchAccomplished = ss.SchetSluchAccomplished and sai.IsAddToAct = 1) actitem
outer apply (
			SELECT SUM(CASE WHEN f006.ControlType = 0 THEN ISNULL(sank.S_SUM, 0) ELSE 0 END) AS SANK_MEK_POVT
				 , SUM(CASE WHEN f006.ControlType = 1 THEN ISNULL(sank.S_SUM, 0) ELSE 0 END) AS SANK_MEE
				 , SUM(CASE WHEN f006.ControlType = 2 THEN ISNULL(sank.S_SUM, 0) ELSE 0 END) AS SANK_EKMP
			FROM IES.T_SCHET_SLUCH_SANK sank WITH(NOLOCK)
			CROSS APPLY (
				SELECT TOP 1 f006.ControlType
				FROM ies.t_f006_control_type f006 WITH(NOLOCK)
				WHERE f006.F006ControlTypeID = sank.S_TIP_F006
				) f006
			WHERE sank.SchetSluch = ss.SchetSluchID
			) sss
where sz.Schet = @SchetID

--собираем зак случаи у которых больше 1 случая и сумма санкции случая больше суммы выставленной случая
select distinct ss.SchetSluchAccomplished
into #sluchs
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
cross apply(select sum(sss.S_SUM) as S_SUM from ies.T_SCHET_SLUCH_SANK sss with(nolock) where sss.SchetSluch = ss.SchetSluchID)sanks 
WHERE sz.Schet = @SchetID
and sanks.S_SUM > ss.SUMV 
and exists(select top 1 1 from ies.T_SCHET_SLUCH sl with(nolock) where sl.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID and sl.SchetSluchID <> ss.SchetSluchID)

--разделяем суммы на все случаи в 1 зак случае
update ss set
	   ss.SUMP = ROUND(ss.SUMV / ssa.SUMV * ssa.SUMP,2)
	  ,ss.SANK_MEK = ROUND(ss.SUMV / ssa.SUMV * ssa.SANK_MEK,2)
	  ,ss.SANK_MEE = ROUND(ss.SUMV / ssa.SUMV * ssa.SANK_MEE,2)
	  ,ss.SANK_EKMP = ROUND(ss.SUMV / ssa.SUMV * ssa.SANK_EKMP,2)
	  ,ss.SANK_MEK_POVT = ROUND(ss.SUMV / ssa.SUMV * ssa.SANK_MEK_POVT,2)
	  ,ss.SANK_IT = ROUND(ss.SUMV / ssa.SUMV * ssa.SANK_IT,2)
	  ,ss.OPLATA = ssa.OPLATA
from #sluchs sl
join ies.T_SCHET_SLUCH ss on sl.SchetSluchAccomplished = ss.SchetSluchAccomplished
join ies.T_SCHET_ZAP sz on sz.SchetZapID = ss.SchetZap		
join ies.T_SCHET_SLUCH_ACCOMPLISHED ssa on ss.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID
WHERE sz.Schet = @SchetID and ss.SUMV <> 0 and ssa.SUMV <> 0

--пересчитываем шапку счета,  учитывая только свои санкции, но сумма принятая зависит от сторонних санкций
UPDATE t1 SET    
	   t1.SUMMAV = sss.SUMV
	  ,t1.SUMMAP = sss.SUMP
	  ,t1.SANK_MEK = sss.SANK_MEK
	  ,t1.SANK_MEE = sss.SANK_MEE
	  ,t1.SANK_EKMP = sss.SANK_EKMP
	  ,t1.SANK_MEK_POVT = sss.SANK_MEK_POVT
	  ,ExpertiseDate = case when ExpertiseDate is not null  then ExpertiseDate else cast(@DateTime as date) end
	  ,ExpertiseTime = case when ExpertiseTime is not null  then ExpertiseTime else @DateTime end
FROM [IES].T_SCHET t1
OUTER APPLY (
	SELECT SUM(isnull(ssa.SUMV,0)) AS SUMV
		 , SUM(isnull(ssa.SUMP,0)) AS SUMP
		 , SUM(isnull(ssa.SANK_MEK,0)) AS SANK_MEK
		 , SUM(isnull(ssa.SANK_MEE,0)) AS SANK_MEE
		 , SUM(isnull(ssa.SANK_EKMP,0)) AS SANK_EKMP
		 , SUM(isnull(ssa.SANK_MEK_POVT, 0)) AS SANK_MEK_POVT
	FROM [IES].T_SCHET_ZAP sz
	cross apply(select top 1 ss.SchetSluchAccomplished from IES.[T_SCHET_SLUCH] ss with (NOLOCK) where (sz.SchetZapID = ss.SchetZap))ss
	JOIN ies.T_SCHET_SLUCH_ACCOMPLISHED ssa ON (ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished)
	WHERE sz.Schet = t1.SchetID
	) sss
WHERE SchetID = @SchetID

--устанавливаем шапку акта, теперь делаем это так, чтобы была 1 в 1 с шапкой счета, а не как раньше
UPDATE t1 SET 
	   t1.TotalCost = t2.SANK_MEK
	  ,t1.PayOut = t2.SUMMAV - t2.SANK_MEK
FROM [IES].T_SCHET_ACT t1 
JOIN [IES].T_SCHET t2 ON  t2.SchetID = t1.Schet
WHERE t2.SchetID = @SchetID

drop table #sluchs
	
END