drop procedure if exists sp_schetwithouteractrecalc;
drop function if exists sp_schetwithouteractrecalc;

create procedure public.sp_schetwithouteractrecalc(
	"SchetID" uuid)
language 'plpgsql'
as $body$

declare _version text;
declare _year int;
declare _month int;
declare _schetkind int;

begin
select s.version, s.year, s.month, s.schetkind into _version, _year, _month, _schetkind from ies.t_schet s where s.schetid = "SchetID" limit 1;

if (left(_version, 1) = '2' or left(_version, 3) = '3.0')
then

	update ies.t_schet_sluch sl
	set  sump = case when oai.schetsluch is not null then ss.sumv - outersank.outermek - outersank.outermee - outersank.outerekmp else ss.sumv - sanks.sankmek  - sanks.sankmee - sanks.sankekmp end
	    ,sank_mek = case when oai.schetsluch is not null then outersank.outermek else sanks.sankmek  end
		,sank_mee = case when oai.schetsluch is not null then outersank.outermee else sanks.sankmee end
		,sank_ekmp = case when oai.schetsluch is not null then outersank.outerekmp else sanks.sankekmp end
		,sank_it = case when oai.schetsluch is not null then coalesce(oai.sank_it, outersank.outermek + outersank.outermee + outersank.outerekmp) else sanks.sankmek  + sanks.sankmee + sanks.sankekmp end
		,oplata = case when ss.sumv = outersank.outermek + outersank.outermee + outersank.outerekmp + sanks.sankmek  + sanks.sankmee + sanks.sankekmp then 2
					   when outersank.outermek + outersank.outermee + outersank.outerekmp + sanks.sankmek  + sanks.sankmee + sanks.sankekmp = 0 then 1
					   else 3 end	 
	from ies.t_schet_sluch ss
	join ies.t_schet_zap sz on sz.schetzapid = ss.schetzap
	left join ies.t_outer_act_item oai on oai.schetsluch = ss.schetsluchid
	left join lateral (select sum(case when aa.controltype = 0 then coalesce(sank.s_sum, 0) else 0 end) outermek,
	                          sum(case when aa.controltype = 1 then coalesce(sank.s_sum, 0) else 0 end) outermee,
	                          sum(case when aa.controltype = 2 then coalesce(sank.s_sum, 0) else 0 end) outerekmp
           from ies.t_outer_error sank
		   join lateral (select f006.controltype,f006.idvid from ies.t_f006_control_type f006 where f006.f006controltypeid = sank.sanctiontype limit 1) aa on true
		   where sank.outeractitem = oai.outeractitemid
		   ) outersank  on true
	left join lateral (select sum(case when aa.controltype = 0 then coalesce(sank.s_sum, 0) else 0 end) sankmek,
	                          sum(case when aa.controltype = 1 then coalesce(sank.s_sum, 0) else 0 end) sankmee,
	                          sum(case when aa.controltype = 2 then coalesce(sank.s_sum, 0) else 0 end) sankekmp
						from ies.t_schet_sluch_sank sank
						join lateral (select f006.controltype,f006.idvid from ies.t_f006_control_type f006 where f006.f006controltypeid = sank.s_tip_f006 limit 1) aa on true
						where sank.schetsluch = ss.schetsluchid) sanks on true
	where sl.schetsluchid = ss.schetsluchid and sz.schet = "SchetID" and ss.isdelete = false and sz.isdelete = false;

	update ies.t_schet_sluch_accomplished zsl 
	set  sump = case when sluch.sump <= 0 then 0 else sluch.sump - coalesce(outersank.s_sum_mek, coalesce(sluch.sank_mek, 0)) end 
		,sank_mek = coalesce(outersank.s_sum_mek, sluch.sank_mek)
		,sank_mee = sluch.sank_mee
		,sank_ekmp = sluch.sank_ekmp
		,sank_it = coalesce(ssa.sank_it, sluch.sank_it)
		,oplata = case when sluch.sump = 0 and sluch.sumv <> sluch.sump or (ssa.sumv - coalesce(ssa.sank_mee, 0) - coalesce(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
	join ies.t_schet_sluch_accomplished ssa on ssa.schetzap = sz.schetzapid 
	cross join lateral (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 where ss.schetzap=sz.schetzapid) sluch
	left join ies.t_outer_act_item oai on oai.schetsluchaccomplished = ssa.schetsluchaccomplishedid
	left join lateral (select sum(case when aa.controltype = 0 then coalesce(sank.s_sum, 0) else 0 end) as s_sum_mek,
							sum(case when aa.controltype = 1 then coalesce(sank.s_sum, 0) else 0 end) as s_sum_mee,
							sum(case when aa.controltype = 2 then coalesce(sank.s_sum, 0) else 0 end) as s_sum_ekmp 
					from ies.t_outer_error sank
					left join lateral (select * from ies.t_f006_control_type f006  where sank.sanctiontype = f006.f006controltypeid limit 1) aa on true
					where sank.outeractitem = oai.outeractitemid) outersank on true
	where zsl.schetsluchaccomplishedid = ssa.schetsluchaccomplishedid and sz.schet = "SchetID" and ssa.isdelete = false and sz.isdelete = false;

	update ies.t_schet s
	set  summav = sss.sumv
		,summap = sss.sump
		,sank_mek = sss.sank_mek
		,sank_mee = sss.sank_mee
		,sank_ekmp = sss.sank_ekmp
		,sank_mek_povt = sss.sank_mek_povt
		,expertisedate = case when s.expertisedate is not null then s.expertisedate else "datetime" :: date end
		,expertisetime = case when s.expertisetime is not null then s.expertisetime else "datetime" :: timestamp end
	from (
		select coalesce(sum(ssa.sumv),0) as sumv
			 , coalesce(sum(ssa.sump),0) as sump
			 , coalesce(sum(ssa.sank_mek),0) as sank_mek
			 , coalesce(sum(ssa.sank_mee),0) as sank_mee
			 , coalesce(sum(ssa.sank_ekmp),0) as sank_ekmp
			 , coalesce(sum(ssa.sank_mek_povt), 0) as sank_mek_povt
		from ies.t_schet_zap sz 
		join ies.t_schet_sluch_accomplished ssa on ssa.schetzap = sz.schetzapid
		where sz.schet = "SchetID"
		) sss
	where s.schetid = "SchetID";
	
end if;
	
if (((_year = 2022 and _month >= 11) or _year >= 2023) and _schetkind = 2)
then

	update ies.t_schet_sluch_accomplished zsl 
	set  sump = 0 - coalesce(sanks.s_sum_mek,0) - coalesce(sanks.s_sum_mee,0) - coalesce(sanks.s_sum_ekmp,0)
	    ,sumv = 0
		,sank_mee = coalesce(sanks.s_sum_mee,0)
		,sank_ekmp = coalesce(sanks.s_sum_ekmp,0)
		,sank_it = coalesce(sanks.s_sum_mek,0) + coalesce(sanks.s_sum_mee,0) + coalesce(sanks.s_sum_ekmp,0)
		,oplata = case when coalesce(sanks.s_sum_mek,0) + coalesce(sanks.s_sum_mee,0) + coalesce(sanks.s_sum_ekmp,0) = 0 then 1
							when (coalesce(sanks.s_sum_mek,0) + coalesce(sanks.s_sum_mee,0) + coalesce(sanks.s_sum_ekmp,0) = sluch.sumv) then 2
							else 3 end
		,sank_mek_povt = coalesce(sanks.s_sum_mek,0)
	from ies.t_schet_zap sz
	join ies.t_schet_sluch_accomplished ssa on ssa.schetzap = sz.schetzapid 
	left join lateral   (select sum(ssoms.sump) as sump
							  , sum(ssoms.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
						join ies.t_schet_sluch ssoms on ss.omsschetsluch = ssoms.schetsluchid
						where ss.schetzap = sz.schetzapid) sluch on true
	left join lateral   (select sum(case when aa.controltype = 0 then coalesce(sank.s_sum, 0) else 0 end) as s_sum_mek,
								sum(case when aa.controltype = 1 then coalesce(sank.s_sum, 0) else 0 end) as s_sum_mee,
								sum(case when aa.controltype = 2 then coalesce(sank.s_sum, 0) else 0 end) as s_sum_ekmp 
						from ies.t_schet_sluch_sank sank
						left join lateral (select * from ies.t_f006_control_type f006  where sank.s_tip_f006 = f006.f006controltypeid limit 1) aa on true
						where sank.outeractitem = oai.outeractitemid) sanks on true
	where zsl.schetsluchaccomplishedid = ssa.schetsluchaccomplishedid and sz.schet = "SchetID" and ssa.isdelete = false and sz.isdelete = false;

	update ies.t_schet_sluch sl
	set  sump = 0 - coalesce(sanks.s_sum_mek,0) - coalesce(sanks.s_sum_mee,0) - coalesce(sanks.s_sum_ekmp,0)
	    ,sumv = 0
		,sank_mee = coalesce(sanks.s_sum_mee,0)
		,sank_ekmp = coalesce(sanks.s_sum_ekmp,0)
		,sank_it = coalesce(sanks.s_sum_mek,0) + coalesce(sanks.s_sum_mee,0) + coalesce(sanks.s_sum_ekmp,0)
		,oplata = case when coalesce(sanks.s_sum_mek,0) + coalesce(sanks.s_sum_mee,0) + coalesce(sanks.s_sum_ekmp,0) = 0 then 1
							when (coalesce(sanks.s_sum_mek,0) + coalesce(sanks.s_sum_mee,0) + coalesce(sanks.s_sum_ekmp,0) = sluch.sumv) then 2
							else 3 end
		,sank_mek_povt = coalesce(sanks.s_sum_mek,0)	 
	from ies.t_schet_sluch ss
	join ies.t_schet_zap sz on sz.schetzapid = ss.schetzap
	join ies.t_schet_sluch ssoms on ssa.omsschetsluch = ssoms.schetsluchid
	left join lateral (select sum(case when aa.controltype = 0 then coalesce(sank.s_sum, 0) else 0 end) s_sum_mek,
	                          sum(case when aa.controltype = 0 then coalesce(sank.s_sum, 0) else 0 end) s_sum_mee,
	                          sum(case when aa.controltype = 0 then coalesce(sank.s_sum, 0) else 0 end) s_sum_ekmp
						from ies.t_schet_sluch_sank sank
						join lateral (select f006.controltype,f006.idvid from ies.t_f006_control_type f006 where f006.f006controltypeid = sank.s_tip_f006 limit 1) aa on true
						where sank.schetsluch = ss.schetsluchid) sanks on true
	where sl.schetsluchid = ss.schetsluchid and sz.schet = "SchetID" and ss.isdelete = false and sz.isdelete = false;

	update ies.t_schet s 
	set  summav = coalesce(aa.s_sumv, 0)
		,summap = coalesce(aa.s_sump, 0)
		,sank_mek = coalesce(aa.s_sank_mek,0)
		,sank_mee = coalesce(aa.s_sank_mee,0)
		,sank_ekmp = coalesce(aa.s_sank_ekmp,0)
		,sank_mek_povt = coalesce(aa.sank_mek_povt,0)
		,version = case when _year >= 2026 then '5.0'
						when (_year = 2025 and _month >= 7) or _year > 2025 then '4.2'
						when _year >= 2025 then '4.1'
						when _year = 2024 then '4.0'
						when (_year = 2022 and _month >= 11) 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' end
	from ies.t_schet t1
	left join lateral   (select sum(coalesce(ssa.sumv,0)) as s_sumv
						, sum(coalesce(ssa.sump,0)) as s_sump
						, sum(coalesce(ssa.sank_mek,0)) as s_sank_mek
						, sum(coalesce(ssa.sank_mee,0)) as s_sank_mee
						, sum(coalesce(ssa.sank_ekmp,0)) as s_sank_ekmp
						, sum(coalesce(ssa.sank_mek_povt,0)) as sank_mek_povt
						from ies.t_schet_sluch_accomplished ssa  
						join ies.t_schet_zap sz on sz.schetzapid = ssa.schetzap
						where sz.schet = t1.schetid and ssa.isdelete = false and sz.isdelete = false) aa on true
	where t1.schetid = "SchetID" and t1.schetid = s.schetid;
	
else

	update ies.t_schet_sluch_accomplished zsl
	set  sump = case when oai.schetsluchaccomplished is not null then ssa.sumv - coalesce(oai.sank_it,sanks.s_sum_mek, 0) + coalesce(sanks.s_sum_mee,0) + coalesce(sanks.s_sum_ekmp,0) 
	            else ssa.sumv - coalesce(sanks.s_sum_mek, 0) - coalesce(sanks.s_sum_mee,0) - coalesce(sanks.s_sum_ekmp,0)  end
		,sank_mek = coalesce(sanks.s_sum_mek, 0)
		,sank_mee = case when oai.schetsluchaccomplished is not null then 
								case when coalesce(sanks.s_sum_mee,0) <> 0 then coalesce(sanks.s_sum_mee,0) else coalesce(outersank.s_sum_mee,0) end
							else coalesce(sanks.s_sum_mee,0) end
		,sank_ekmp = case when oai.schetsluchaccomplished is not null then 
								case when coalesce(sanks.s_sum_ekmp,0) <> 0 then coalesce(sanks.s_sum_ekmp,0) else coalesce(outersank.s_sum_ekmp,0) end
							  else coalesce(sanks.s_sum_ekmp,0) end
		,sank_it = case when oai.schetsluchaccomplished is not null then coalesce(oai.sank_it, coalesce(sanks.s_sum_mek,0) + coalesce(sanks.s_sum_mee,0) + coalesce(sanks.s_sum_ekmp,0)) 
								else coalesce(sanks.s_sum_mek,0) + coalesce(sanks.s_sum_mee,0) + coalesce(sanks.s_sum_ekmp,0) end
		,oplata = case when (sluch.sump = 0 and sluch.sumv <> sluch.sump) or (ssa.sumv - coalesce(ssa.sank_mee, 0) - coalesce(ssa.sank_ekmp, 0) <= 0) then 2
							   when sluch.sump <> 0 and sluch.sumv <> sluch.sump then 3
							   else 1 end
		,sank_mek_povt = coalesce(sanks.s_sum_mek,0)
	from ies.t_schet_sluch_accomplished ssa
	join ies.t_schet_zap sz on sz.schetzapid = ssa.schetzap
	left join ies.t_outer_act_item oai on oai.schetsluchaccomplished = ssa.schetsluchaccomplishedid
    cross join lateral (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 where ss.schetzap=sz.schetzapid) sluch
	left join lateral  (select  sum(case when aa.controltype = 0 then coalesce(sank.s_sum, 0) else 0 end) as s_sum_mek,
								sum(case when aa.controltype = 1 then coalesce(sank.s_sum, 0) else 0 end) as s_sum_mee,
								sum(case when aa.controltype = 2 then coalesce(sank.s_sum, 0) else 0 end) as s_sum_ekmp 
						from ies.t_outer_error sank
						left join lateral (select * from ies.t_f006_control_type f006  where sank.sanctiontype = f006.f006controltypeid limit 1) aa on true
						where sank.outeractitem = oai.outeractitemid) outersank on true
	left join lateral   (select sum(case when aa.controltype = 0 then coalesce(sank.s_sum, 0) else 0 end) s_sum_mek,
								sum(case when aa.controltype = 1 then coalesce(sank.s_sum, 0) else 0 end) s_sum_mee,
								sum(case when aa.controltype = 2 then coalesce(sank.s_sum, 0) else 0 end) s_sum_ekmp
						from ies.t_schet_sluch_sank sank
						join lateral (select f006.controltype,f006.idvid from ies.t_f006_control_type f006 where f006.f006controltypeid = sank.s_tip_f006 limit 1) aa on true
						where sank.schetsluchaccomplished = ssa.schetsluchaccomplishedid) sanks on true
	where zsl.schetsluchaccomplishedid = ssa.schetsluchaccomplishedid and sz.schet = "SchetID" and ssa.isdelete = false and sz.isdelete = false;
	
	update ies.t_schet_sluch_accomplished zsl
	set  sumv = 0
		,sump = 0
		,sank_mek = 0
		,sank_mee = 0
		,sank_ekmp = 0
		,sank_it = 0
		,sank_mek_povt = 0
	from ies.t_schet_zap sz
	where sz.schetzapid = zsl.schetzap and sz.schet = "SchetID" and sz.isdelete = false 
	and not exists(select * from ies.t_schet_sluch t1 where t1.schetzap = sz.schetzapid);

	update ies.t_schet_sluch sl 
	set  sump = 0
		,sank_mek = 0
		,sank_mee = 0
		,sank_ekmp = 0
		,sank_mek_povt = 0
		,sank_it = 0
		,oplata = case when oes.outererrorslid is not null then 2 else 1 end
	from ies.t_schet_sluch ss 
	join ies.t_schet_zap sz  on sz.schetzapid = ss.schetzap and sz.isdelete = false
	join ies.t_schet_sluch_accomplished ssa on ss.schetsluchaccomplished = ssa.schetsluchaccomplishedid and ssa.isdelete = false
	left join ies.t_outer_act_item oai on oai.schetsluchaccomplished = ssa.schetsluchaccomplishedid
	left join ies.t_outer_error oe on oe.outeractitem = oai.outeractitemid
	left join ies.t_outer_error_sl oes on oes.outererror = oe.outererrorid and ss.schetsluchid = oes.schetsluch
	where sz.schet = "SchetID" and sl.schetsluchid = ss.schetsluchid and ss.isdelete = false and ssa.sumv = 0;

	update ies.t_schet_sluch sl 
	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_mek_povt = round(ss.sumv / ssa.sumv * ssa.sank_mek_povt,2)
		,sank_it = round(ss.sumv / ssa.sumv * ssa.sank_it,2)
	from ies.t_schet_sluch ss 
	join ies.t_schet_zap sz on sz.schetzapid = ss.schetzap		
	join ies.t_schet_sluch_accomplished ssa on ss.schetsluchaccomplished = ssa.schetsluchaccomplishedid
	left join ies.t_outer_act_item oai on oai.schetsluchaccomplished = ssa.schetsluchaccomplishedid
	left join ies.t_outer_error oe on oe.outeractitem = oai.outeractitemid
	left join ies.t_outer_error_sl oes on oes.outererror = oe.outererrorid and ss.schetsluchid = oes.schetsluch
	where sz.schet = "SchetID" and sl.schetsluchid = ss.schetsluchid and ss.isdelete = false and ssa.sumv != 0;

	update ies.t_schet_sluch sl 
	set oplata = case   when coalesce(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 
	join ies.t_schet_zap sz on sz.schetzapid = ss.schetzap
	where sz.schet = "SchetID" and sl.schetsluchid = ss.schetsluchid and ss.isdelete = false;
	
	update ies.t_schet s
	set  summav = coalesce(sss.sumv,0)
		,summap = coalesce(sss.sump,0)
		,sank_mek = coalesce(sss.sank_mek,0)
		,sank_mee = coalesce(sss.sank_mee,0)
		,sank_ekmp = coalesce(sss.sank_ekmp,0)
		,sank_mek_povt = coalesce(sss.sank_mek_povt,0)
		,expertisedate = case when s.expertisedate is not null then s.expertisedate else "datetime" :: date end
		,expertisetime = case when s.expertisetime is not null then s.expertisetime else "datetime" :: timestamp end
		,version = case when _year >= 2026 then '5.0'
						when (_year = 2025 and _month >= 7) or _year > 2025 then '4.2'
						when _year >= 2025 then '4.1'
						when _year = 2024 then '4.0'
						when (_year = 2022 and _month >= 11) 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' end
	from (
		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
		from ies.t_schet_zap sz 
		join ies.t_schet_sluch_accomplished ssa on ssa.schetzap = sz.schetzapid
		where sz.schet = "SchetID"
		) sss
	where s.schetid = "SchetID";

end if;

end
$body$;