drop function if exists sp_schetrecalc;
drop procedure if exists sp_schetrecalc;

create procedure public.sp_schetrecalc(
	in "SchetID" uuid,
	in "ProcessName" text default null,
	in "datetime" date default null)
language 'plpgsql'
as $body$

declare _version text;
declare _filename text;
declare _type int;
declare _isrecalcoplatamedicalexpertise boolean;
declare _recalculatemtrdchetsheaderbymek boolean;

begin

select s.version, s.filename, s.type_ into _version, _filename, _type from ies.t_schet s where s.schetid = "SchetID" limit 1;
select case when paramvalue = 'False' then 0 else 1 end into _isrecalcoplatamedicalexpertise from ies.t_company_settings_parameter where paramkey = 'IsRecalcOplataMedicalExpertise' limit 1;
select case when paramvalue = 'False' then 0 else 1 end into _recalculatemtrdchetsheaderbymek from ies.t_company_settings_parameter where paramkey = 'RecalculateMtrSchetsHeaderByMek' limit 1;

	--заполням законченный случай, для отчетности и соответсвия формату 79 приказа редакции 2018 года
	update ies.t_schet_sluch_accomplished ssa 
	set  sumv = coalesce(ss.sumv,0)
		,sump = case when actitem.isaddtoact is not null then 0
				    when _recalculatemtrdchetsheaderbymek = 0 and actitem.isaddtoact is null then coalesce(ss.sumv,0)
				    else ss.sumv - coalesce(sss.sank_mek_povt,0) - coalesce(sss.sank_mee,0) - coalesce(sss.sank_ekmp,0) end
		,sank_mek = case when actitem.isaddtoact is null then 0 else ss.sumv - coalesce(sss.sank_mek_povt,0) - coalesce(sss.sank_mee,0) - coalesce(sss.sank_ekmp,0) end
		,sank_mee = coalesce(sss.sank_mee,0)
		,sank_ekmp = coalesce(sss.sank_ekmp,0)
		,sank_mek_povt = coalesce(sss.sank_mek_povt,0)
		,sank_it = case when actitem.isaddtoact is not null then ss.sumv else coalesce(sss.sank_mek_povt,0) + coalesce(sss.sank_mee,0) + coalesce(sss.sank_ekmp,0) end
		,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 (coalesce(sss.sank_mek_povt,0) + coalesce(sss.sank_mee,0) + coalesce(sss.sank_ekmp,0) > 0 and ss.sumv <= coalesce(sss.sank_mek_povt,0) + coalesce(sss.sank_mee,0) + coalesce(sss.sank_ekmp,0)) then 2
					when coalesce(sss.sank_mek_povt,0) + coalesce(sss.sank_mee,0) + coalesce(sss.sank_ekmp,0) = 0 then 1
					else 3 end
		--,sank_mek_r = coalesce(sss.sank_mek_povt_r,0)
		--,sank_mee_r = coalesce(sss.sank_mee_r,0)
		--,sank_ekmp_r = coalesce(sss.sank_ekmp_r,0)
	from ies.t_schet_zap sz
	join lateral (select coalesce(sum(sl.sumv), 0) sumv, sl.schetsluchaccomplished from ies.t_schet_sluch sl where sz.schetzapid = sl.schetzap group by sl.schetsluchaccomplished) ss on true
	left join lateral (select sai.isaddtoact from ies.t_schet_act_item sai where sai.schetsluchaccomplished = ss.schetsluchaccomplished and sai.isaddtoact = true limit 1) actitem on true
	left join lateral (select sum(case when f006.controltype = 0 then coalesce(sss.s_sum, 0) else 0 end) as sank_mek_povt
							, sum(case when f006.controltype = 1 then coalesce(sss.s_sum, 0) else 0 end) as sank_mee
							, sum(case when f006.controltype = 2 then coalesce(sss.s_sum, 0) else 0 end) as sank_ekmp
						from ies.t_schet_sluch_sank sss 
						join lateral (select f006.controltype from ies.t_f006_control_type f006 where f006.f006controltypeid = sss.s_tip_f006 limit 1) f006 on true
						where sss.schetsluchaccomplished = ss.schetsluchaccomplished) sss on true
	where ssa.schetsluchaccomplishedid = ss.schetsluchaccomplished and sz.schet = "SchetID" and ssa.isdelete = false and sz.isdelete = false;

	--заполняем инфу на случае, для себя, мээ, экмп, пмэк выставляются в коде
	update ies.t_schet_sluch ss 
	set  sump = case when actitem.isaddtoact is not null then 0
				when _recalculatemtrdchetsheaderbymek = 0 and actitem.isaddtoact is null then coalesce(ss.sumv,0)
				else ss.sumv - coalesce(sss.sank_mek_povt,0) - coalesce(sss.sank_mee,0) - coalesce(sss.sank_ekmp,0) end
		,sank_mek = case when actitem.isaddtoact is null then 0 else ss.sumv - coalesce(sss.sank_mek_povt,0) - coalesce(sss.sank_mee,0) - coalesce(sss.sank_ekmp,0) end
		,sank_mee = coalesce(sss.sank_mee,0)
		,sank_ekmp = coalesce(sss.sank_ekmp,0)
		,sank_mek_povt = coalesce(sss.sank_mek_povt,0)
		,sank_it = case when actitem.isaddtoact is not null then ss.sumv else coalesce(sss.sank_mek_povt,0) + coalesce(sss.sank_mee,0) + coalesce(sss.sank_ekmp,0) end
		,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 <= coalesce(sss.sank_mek_povt,0) + coalesce(sss.sank_mee,0) + coalesce(sss.sank_ekmp,0) then 2
						when coalesce(sss.sank_mek_povt,0) + coalesce(sss.sank_mee,0) + coalesce(sss.sank_ekmp,0) = 0 then 1
						else 3 end
		--,sank_mek_r = coalesce(sss.sank_mek_povt_r,0)
		--,sank_mee_r = coalesce(sss.sank_mee_r,0)
		--,sank_ekmp_r = coalesce(sss.sank_ekmp_r,0)
	from ies.t_schet_zap sz, ies.t_schet_sluch_accomplished ssa
	left join lateral (select sai.isaddtoact from ies.t_schet_act_item sai where sai.schetsluchaccomplished = ssa.schetsluchaccomplishedid and sai.isaddtoact = true limit 1) actitem on true
	left join lateral (select sum(case when f006.controltype = 0 then coalesce(sss.s_sum, 0) else 0 end) as sank_mek_povt
							, sum(case when f006.controltype = 1 then coalesce(sss.s_sum, 0) else 0 end) as sank_mee
							, sum(case when f006.controltype = 2 then coalesce(sss.s_sum, 0) else 0 end) as sank_ekmp
						from ies.t_schet_sluch_sank sss 
						join lateral (select f006.controltype from ies.t_f006_control_type f006 where f006.f006controltypeid = sss.s_tip_f006 limit 1) f006 on true
						where sss.schetsluchaccomplished = ssa.schetsluchaccomplishedid) sss on true
	where sz.schetzapid = ss.schetzap and ssa.schetsluchaccomplishedid = ss.schetsluchaccomplished and sz.schet = "SchetID" and ss.isdelete = false and sz.isdelete = false;

	--собираем зак случаи у которых больше 1 случая и сумма санкции случая больше суммы выставленной случая
	create temp table sluchs as
	select distinct ss.schetsluchaccomplished
	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
	join lateral (select sum(sss.s_sum) as s_sum from ies.t_schet_sluch_sank sss where sss.schetsluchaccomplished = ssa.schetsluchaccomplishedid limit 1) sss on true
	where sz.schet = "SchetID"
	and sss.s_sum > ss.sumv 
	and exists(select sl.schetsluchaccomplished from ies.t_schet_sluch sl where sl.schetsluchaccomplished = ssa.schetsluchaccomplishedid and sl.schetsluchid <> ss.schetsluchid);

	--разделяем суммы на все случаи в 1 зак случае
	update ies.t_schet_sluch 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_mek_povt = round(ss.sumv / ssa.sumv * ssa.sank_mek_povt,2)
		,sank_it = round(ss.sumv / ssa.sumv * ssa.sank_it,2)
		,oplata = ssa.oplata
		--,sank_mek_r = round(ss.sumv / ssa.sumv * ssa.sank_mek_r,2)
		--,sank_mee_r = round(ss.sumv / ssa.sumv * ssa.sank_mee_r,2)
		--,sank_ekmp_r = round(ss.sumv / ssa.sumv * ssa.sank_ekmp_r,2)
	from ies.t_schet_zap sz, ies.t_schet_sluch_accomplished ssa
	join sluchs sl on sl.schetsluchaccomplished = ssa.schetsluchaccomplishedid
	where sl.schetsluchaccomplished = ss.schetsluchaccomplished and sz.schetzapid = ss.schetzap and sz.schet = "SchetID" and ss.sumv <> 0 and ssa.sumv <> 0;

	--обновляем шапку счета по данным из законченного случая
	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)
		--,sank_mek_r = coalesce(sss.sank_mek_r,0)
		--,sank_mee_r = coalesce(sss.sank_mee_r,0)
		--,sank_ekmp_r = coalesce(sss.sank_ekmp_r,0)
		,expertisedate = case when s.expertisedate is not null then s.expertisedate else coalesce(a.createdate:: date, "datetime":: date) end
		,expertisetime = case when s.expertisetime is not null then s.expertisetime else coalesce(a.createdate:: timestamp, "datetime":: timestamp) 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
			 --, 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 
		join ies.t_schet_sluch_accomplished ssa on ssa.schetzap = sz.schetzapid
		where sz.schet = "SchetID"
		) sss
		left join ies.t_schet_act a on a.schet = "SchetID"
	where s.schetid = "SchetID";

	--устанавливаем шапку акта, теперь делаем это так, чтобы была 1 в 1 с шапкой счета, а не как раньше
	update ies.t_schet_act sa 
	set  totalcost = s.sank_mek
		,payout = s.summav - s.sank_mek
	from ies.t_schet s
	where s.schetid = sa.schet and s.schetid = "SchetID";

drop table if exists sluchs;

end
$body$;