
with params as(
	select
		cast($1 as int8) as p_lpu_id,
		cast($2 as bigint) as p_LpuRegionType_id,
		cast($3 as bigint) as p_Post_id,
		cast(to_timestamp($4,'dd.mm.yyyy') as timestamp) as p_begdate, 
		cast(to_timestamp($5,'dd.mm.yyyy') as timestamp) as p_enddate
		),

 tableperson as
(select  msr.MedPersonal_id,msf.person_fio,count(distinct pc.person_id) person_id
		from v_PersonCard PC  
		left join params on true
		left join v_LpuRegion LR  on LR.LpuRegion_id = PC.LpuRegion_id 
		left join v_MedStaffRegion msr  on  msr.lpuregion_id=PC.LpuRegion_id 
		left join v_MedStaffFact MSF  on MSF.MedPersonal_id = msr.MedPersonal_id
		left join persis.v_Post PostMed  on  (MSF.Post_id = PostMed.id)
		where (pc.Lpu_id=p_Lpu_id or p_lpu_id is null)
		and (pc.LpuAttachType_id = 1) 
		and (pc.LpuRegionType_id=p_LpuRegionType_id or p_LpuRegionType_id is null)
		and (PC.PersonCard_endDate is null or cast(PC.PersonCard_endDate as timestamp) > p_enddate)
		and  PostMed.Code in ('40','47','74','111','117','4510209','4510210')
		and (PostMed.id=p_Post_id or p_Post_id is null)
		group by msr.MedPersonal_id,msf.person_fio
--having count(distinct pc.person_id)>0

union all
		select  msr.MedPersonal_id,msf.person_fio,count(distinct pc.person_id) person_id		
		from v_PersonCard PC
		left join params on true
		left join v_LpuRegion LR  on LR.LpuRegion_id = PC.LpuRegion_fapid
		left join v_MedStaffRegion msr  on  msr.lpuregion_id=PC.LpuRegion_fapid 
		left join v_MedStaffFact MSF  on MSF.MedPersonal_id = msr.MedPersonal_id
        left join persis.v_Post PostMed  on  (MSF.Post_id = PostMed.id)		
		where (pc.Lpu_id=p_Lpu_id or p_lpu_id is null)
		and (pc.LpuAttachType_id = 1) 
		and (pc.LpuRegionType_id=p_LpuRegionType_id or p_LpuRegionType_id is null)
		and (PC.PersonCard_endDate is null or cast(PC.PersonCard_endDate as timestamp) > p_enddate)
		and  PostMed.Code in ('47','74','40','117','111')
		and (PostMed.id=p_Post_id or p_Post_id is null)
		group by msr.MedPersonal_id,msf.person_fio
--having count(distinct pc.person_id)>0
)
select 
EvnVizitPL.EvnVizitPL_setDate as vdate,
--convert(varchar(20),EvnVizitPL.EvnVizitPL_setDate,104) as vdate,
LpuSection.LpuSection_Code as "LpuSection_Code",
LpuSection.LpuSection_Name as "LpuSection_Name",
person.Person_Fio as "Person_Fio",
pc.LpuRegion_Name as "LpuRegion_Name",
count(distinct case when st.ServiceType_SysNick = 'polka' then EvnVizitPL_id end) as pol_vizit,
count(distinct case when st.ServiceType_SysNick = 'polka'and vt.VizitType_SysNick in ('prof','soc','patron','dopdisp','priziv','dopriziv') then EvnVizitPL_id end) as pol_prof_vizit,
count(distinct case when st.ServiceType_SysNick in ('home','ahome','neotl') then EvnVizitPL_id end) as dom_vizit,
count(distinct case when pt.Paytype_sysnick = 'oms' then RegistryData.Evn_id end) as oms_pay,
count(distinct RegistryData.Evn_id) as is_paid 
from  
v_EvnPL as EvnPL  
left join params on true
inner join v_EvnVizitPL as EvnVizitPL on  EvnPL.EvnPL_id = EvnVizitPL.EvnVizitPL_pid
inner join v_LpuSection LpuSection  on EvnVizitPL.LpuSection_id = LpuSection.LpuSection_id
inner join v_LpuUnit LpuUnit on LpuSection.LpuUnit_id = LpuUnit.LpuUnit_id
inner join lateral
	(
	select person.Person_Fio
	from tableperson person
	where person.medpersonal_id=EvnVizitPL.MedPersonal_id 
	and person_id>0
	) person on true
left join lateral (
select 
	case when pc.Lpu_id is not null and pc.LpuRegion_Name is null then 'Без участка'
	when pc.Lpu_id is  null and pc.LpuRegion_Name is null then 'Нет прикрепления к МО'
	when pc.Lpu_id=EvnVizitPL.Lpu_id then pc.LpuRegion_Name
	when pc.Lpu_id<>EvnVizitPL.Lpu_id then 'другое ЛПУ' end as LpuRegion_Name
	 from v_PersonCard PC   
	 where  pc.person_id=EvnVizitPL.person_id limit 1
) pc on true
--left join v_PersonCard PC with (nolock)  on pc.person_id=EvnVizitPL.person_id
left join dbo.v_RegistryData RegistryData  on RegistryData.Evn_id = EvnVizitPL.EvnVizitPL_id
left join v_ServiceType st   on EvnVizitPL.ServiceType_id=st.ServiceType_id
left join v_VizitType vt   on EvnVizitPL.VizitType_id=vt.VizitType_id
left join v_Paytype pt   on pt.Paytype_id=EvnVizitPL.Paytype_id
where EvnVizitPL.EvnVizitPL_setDate between p_begdate and p_enddate
and  (EvnPL.lpu_id=p_Lpu_id or p_Lpu_id is null)
group by 
EvnVizitPL.EvnVizitPL_setDate,
LpuSection.LpuSection_Code,
LpuSection.LpuSection_Name,
person.Person_Fio,
pc.LpuRegion_Name
