/*select * from v_lpu l
where l.lpu_name='Медицинская организация "ТЕСТ"'*/

with params as (
		select
		cast($1 as bigint) as p_lpu_id,
		cast($2 as bigint) as p_lpubuilding_id,
		cast($3 as bigint) as p_lpuunit_id,
		cast($4 as bigint) as p_lpusectionprof,
		cast($5 as bigint) as p_lpusectionprofile,
		cast($6 as bigint) as p_treatmentclass,
		cast($7 as int) as p_isfinish,
		cast($8 as bigint) as p_paytype,
		cast($9 as bigint) as p_citizenship,
		CAST(TO_TIMESTAMP($10,'DD.MM.YYYY') AS TIMESTAMP) as p_begdate,
		CAST(TO_TIMESTAMP($11,'DD.MM.YYYY') AS TIMESTAMP) as p_enddate
),

Lpu_W as (
select  
lp.Lpu_id,
lp.Lpu_Nick,
params.*
from params
inner join v_lpu lp on true
where (lp.Lpu_id=p_lpu_id   or p_lpu_id  is null and coalesce(lp.Lpu_IsTest,1)=1) 

),
EvnPL_W as (
select 
coalesce(ls.lpusectionprofile_name,'Прочие') as lpusectionprofile_name,
coalesce(lastInfo.paytype_name,'') as paytype_name,
coalesce(klc.klcountry_name,'') as klcountry_name,
epl.person_id,
epl.evnpl_id,
evpl.evnvizitpl_id,
--rd.RegistryData_itogsum
case when p_isfinish =2 or p_isfinish is null then coalesce(rd.RegistryData_itogsum,0) else 0 end as RegistryData_itogsum
from Lpu_W lp
inner join v_EvnPL epl on epl.lpu_id=lp.lpu_id 
and epl.EvnPL_disDate   between p_begdate and p_enddate and epl.EvnClass_id in (3,6)

inner join v_EvnVizitPL evpl  on evpl.EvnVizitPL_pid=epl.EvnPL_id 
inner join v_personstate ps on ps.person_id=epl.Person_id
inner join v_NationalityStatus ns  on ns.NationalityStatus_id = ps.NationalityStatus_id
inner join v_KLCountry klc on klc.klcountry_id=ns.klcountry_id and klc.klcountry_name!='РОССИЯ'
left join v_TreatmentClass tc  on tc.TreatmentClass_id=evpl.TreatmentClass_id 
and tc.TreatmentClass_Code in ('1', '1.1', '1.2', '1.3','2.1','2.2','2.3','2.4','2.5','2.6')

left join v_LpuSection ls  on ls.lpusection_id = epl.lpusection_id
left join r89.v_RegistryData rd  on rd.Evn_id = evpl.EvnVizitPL_id and rd.evnclass_id=11
left join lateral (
	select 
	pt.paytype_name ,e.PayType_id
	from v_EvnVizitPL e
	left join PayType pt  on pt.PayType_id = e.paytype_id 
	where e.evnvizitpl_pid=epl.EvnPL_id and e.evnvizitpl_index=e.evnvizitpl_count-1 
	--and (e.PayType_id = cast (p_paytype as bigint) or cast (p_paytype as bigint) is null)
	limit 1
) lastInfo on true
where  (LS.LpuSection_id = p_lpusectionprof  or p_lpusectionprof   is null)
  and (LS.LpuBuilding_id = p_lpubuilding_id  or p_lpubuilding_id  is null) 
  and (LS.LpuUnit_id = p_lpuunit_id or p_lpuunit_id   is null)
and (klc.klcountry_id=p_citizenship  or p_citizenship  is null)
and (tc.TreatmentClass_id = p_treatmentclass  or p_treatmentclass is null)
--and klc.klcountry_name!='РОССИЯ'
and (LS.LpuSectionProfile_id = p_lpusectionprofile or p_lpusectionprofile is null)
and (lastInfo.PayType_id = p_paytype or p_paytype  is null)
and (epl.EvnPL_IsFinish=p_isfinish  or p_isfinish is null)
),

fiz_pazient_W as (
select 
count(distinct e.person_id) as fiz_p_itog 
from EvnPL_W e
),

results_W as (
select  
coalesce(e.lpusectionprofile_name,'Прочие') as lpusectionprofile_name,
coalesce(e.paytype_name,'') as paytype_name,
coalesce(e.klcountry_name,'') as klcountry_name,
count(distinct e.person_id) as v0,
count(distinct e.evnpl_id) as v1,
count(distinct e.evnvizitpl_id) as v2,
sum(e.RegistryData_itogsum) as v3
from EvnPL_W e

group by e.lpusectionprofile_name, e.paytype_name, e.klcountry_name

)

select 
r.*,
f.fiz_p_itog
 
from results_W r
inner join fiz_pazient_W f on true 
