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,
  @ProcessName varchar(100) = null
AS 
BEGIN
   		
declare @version varchar(10), @type int, @filename varchar(50), @IsRecalcOplataMedicalExpertise bit, @RecalculateMtrSchetsHeaderByMek bit
select top 1 @version = t1.[Version], @filename = t1.[FILENAME], @type = t1.type_ from ies.T_SCHET t1 with(nolock) where t1.SchetID = @SchetID

set @IsRecalcOplataMedicalExpertise = case when (select top 1 ParamValue from ies.T_COMPANY_SETTINGS_PARAMETER where ParamKey = 'IsRecalcOplataMedicalExpertise') = 'False' then 0 else 1 end
set @RecalculateMtrSchetsHeaderByMek = case when (select top 1 ParamValue from ies.T_COMPANY_SETTINGS_PARAMETER where ParamKey = 'RecalculateMtrSchetsHeaderByMek') = 'False' then 0 else 1 end

--заполням законченный случай, для отчетности и соответсвия формату 79 приказа редакции 2018 года
update ssa set  
	   ssa.SUMV = isnull(sluch.SUMV,0)
	  ,ssa.SUMP = case when actitem.IsAddToAct is not null then 0
				  when @RecalculateMtrSchetsHeaderByMek = 0 and actitem.IsAddToAct is null then isnull(sluch.SUMV,0)
				  else sluch.SUMV - isnull(sss.SANK_MEK_POVT,0) - isnull(sss.SANK_MEE,0) - isnull(sss.SANK_EKMP,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 @IsRecalcOplataMedicalExpertise = 0 and @type = 693 and actitem.IsAddToAct is null then 1 
					when @IsRecalcOplataMedicalExpertise = 0 and @type = 693 and actitem.IsAddToAct is not null then 2
					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
	  --,ssa.SANK_MEE_R = isnull(sss.SANK_MEE_R,0)
   --   ,ssa.SANK_EKMP_R = isnull(sss.SANK_EKMP_R,0)
	  --,ssa.SANK_MEK_R = isnull(sss.SANK_MEK_POVT_R,0)
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
				 --, SUM(CASE WHEN f006.ControlType = 0 and sank.SmoActItem is not null THEN ISNULL(sank.S_SUM, 0) ELSE 0 END) AS SANK_MEK_POVT_R
				 --, SUM(CASE WHEN f006.ControlType = 1 and sank.SmoActItem is not null THEN ISNULL(sank.S_SUM, 0) ELSE 0 END) AS SANK_MEE_R
				 --, SUM(CASE WHEN f006.ControlType = 2 and sank.SmoActItem is not null THEN ISNULL(sank.S_SUM, 0) ELSE 0 END) AS SANK_EKMP_R
			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 not null then 0
				  when @RecalculateMtrSchetsHeaderByMek = 0 and actitem.IsAddToAct is null then isnull(ss.SUMV,0)
				  else ss.SUMV - isnull(sss.SANK_MEK_POVT,0) - isnull(sss.SANK_MEE,0) - isnull(sss.SANK_EKMP,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 @IsRecalcOplataMedicalExpertise = 0 and @type = 693 and actitem.IsAddToAct is null then 1 
				   when @IsRecalcOplataMedicalExpertise = 0 and @type = 693 and actitem.IsAddToAct is not null then 2
				   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
	  --,ss.SANK_MEE_R = isnull(sss.SANK_MEE_R,0)
	  --,ss.SANK_EKMP_R = isnull(sss.SANK_EKMP_R,0)
	  --,ss.SANK_MEK_R = isnull(sss.SANK_MEK_POVT_R,0)
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
				 --, SUM(CASE WHEN f006.ControlType = 0 and sank.SmoActItem is not null THEN ISNULL(sank.S_SUM, 0) ELSE 0 END) AS SANK_MEK_POVT_R
				 --, SUM(CASE WHEN f006.ControlType = 1 and sank.SmoActItem is not null THEN ISNULL(sank.S_SUM, 0) ELSE 0 END) AS SANK_MEE_R
				 --, SUM(CASE WHEN f006.ControlType = 2 and sank.SmoActItem is not null THEN ISNULL(sank.S_SUM, 0) ELSE 0 END) AS SANK_EKMP_R
			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
	  --,ss.SANK_MEK_R = ROUND(ss.SUMV / ssa.SUMV * ssa.SANK_MEK_R,2)
	  --,ss.SANK_MEE_R = ROUND(ss.SUMV / ssa.SUMV * ssa.SANK_MEE_R,2)
	  --,ss.SANK_EKMP_R = ROUND(ss.SUMV / ssa.SUMV * ssa.SANK_EKMP_R,2)
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 = isnull(sss.SUMV,0)
	  ,t1.SUMMAP = isnull(sss.SUMP,0)
	  ,t1.SANK_MEK = isnull(sss.SANK_MEK,0)
	  ,t1.SANK_MEE = isnull(sss.SANK_MEE,0)
	  ,t1.SANK_EKMP = isnull(sss.SANK_EKMP,0)
	  ,t1.SANK_MEK_POVT = isnull(sss.SANK_MEK_POVT,0)
	  --,t1.SANK_MEK_R = isnull(sss.SANK_MEK_R,0)
	  --,t1.SANK_MEE_R = isnull(sss.SANK_MEE_R,0)
	  --,t1.SANK_EKMP_R = isnull(sss.SANK_EKMP_R,0)
	  ,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(ssa.SUMV) AS SUMV
		 , SUM(ssa.SUMP) AS SUMP
		 , SUM(ssa.SANK_MEK) AS SANK_MEK
		 , SUM(ssa.SANK_MEE) AS SANK_MEE
		 , SUM(ssa.SANK_EKMP) AS SANK_EKMP
		 , SUM(ssa.SANK_MEK_POVT) AS SANK_MEK_POVT
		 --, SUM(ssa.SANK_MEK_R) AS SANK_MEK_R
		 --, SUM(ssa.SANK_MEE_R) AS SANK_MEE_R
		 --, SUM(ssa.SANK_EKMP_R) AS SANK_EKMP_R
	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