with params as (
select 
cast( $1 as bigint) p_lpu_id , 
cast( $2 as bigint) p_lpubuilging_id , 
cast( $3 as bigint)p_lpuunit_id , 
cast( $4 as bigint)p_lpusection_id , 
cast( $5 as bigint)p_lpuregion_id , 
cast( $6 as bigint)p_medpersonal_id , 
cast( $7 as int8)p_isfinished , 
cast( $8 as int8)p_paytype_id , 
cast(to_timestamp($9,'dd.mm.yyyy') as timestamp) p_begdate ,
cast(to_timestamp($10,'dd.mm.yyyy') as timestamp) p_enddate 
)

select distinct 
	ROW_NUMBER() OVER (ORDER BY epl.EvnPl_DisDate desc) v1,
	epl.EvnPL_NumCard::varchar v2,
	--ltrim(PS.Person_surname)||' '||ltrim(PS.Person_Firname)||' '||ltrim(PS.Person_Secname)::text v3,
	ltrim(coalesce(PS.Person_surname,''))||' '||ltrim(coalesce(PS.Person_Firname,''))||' '||ltrim(coalesce(PS.Person_Secname,''))::text v3,
	cast(PS.Person_BirthDay as date)::timestamp v4,
	--convert(date,PS.Person_BirthDay) v4,
	LR.LpuRegion_Name::varchar v5,
	cast(epl.evnPL_setDate as date)::timestamp v6,
	cast(epl.evnPL_disDate as date)::timestamp v7,	
	Diag.Diag_Code::varchar v8,
	count(case when asd.UslugaComplex_Name like '%первичн%' then evpl.EvnvizitPL_id end)::bigint v9,
	count (case when TC.TreatmentClass_Code in ('1','1.1','1.2','1.3') then evpl.EvnvizitPL_id end)::bigint v10,
	count (case when evpl.VizitClass_id =1  then evpl.EvnvizitPL_id end)::bigint v11
	
from params 
inner join v_evnvizitpl evpl on true
inner join v_evnPL epl  on	epl.EvnPL_id=evpl.EvnVizitPL_pid
	and epl.EvnPL_VizitCount>1
	and (epl.lpu_id =p_lpu_id or p_lpu_id is null)
	and (epl.LpuSection_id=p_LpuSection_id or p_LpuSection_id is null)
	and (epl.EvnPL_IsFinish=p_IsFinished or p_IsFinished is null)
	and epl.EvnPL_disDate between p_begdate and p_enddate
	and (Epl.MedPersonal_id=p_MedPersonal_id or p_MedPersonal_id is null)


inner join v_TreatmentClass TC  on TC.TreatmentClass_id=evpl.TreatmentClass_id
inner join v_LpuUnit LU  on	lu.Lpu_id=epl.Lpu_id
	and (lu.lpuunit_id=p_LpuUnit_id or p_LpuUnit_id is null)
	and (lu.LpuBuilding_id=p_Lpubuilging_id or p_Lpubuilging_id is null)

inner join v_Diag Diag  on	epl.Diag_id=Diag.Diag_id
inner join v_PersonState PS  on PS.Person_id=evpl.Person_id
	and PS.server_id=epl.Server_id
--inner join v_UslugaComplex UC  on UC.UslugaComplex_id=evpl.UslugaComplex_id
left join lateral 
  (
    select 
      UslugaComplex_Name
    from v_evnusluga evnusluga 
    inner join v_UslugaComplex UC  on 
		UC.UslugaComplex_id=evnusluga.UslugaComplex_id
		and UslugaComplex_Name like '%первичн%'
    where evnusluga.evnusluga_rid=epl.EvnPL_id
  --- having  count(evnusluga_id) >2
) asd on true

left join lateral
	(
	select lr.LpuRegion_Name,lr.LpuRegion_id
	from v_PersonCard pc
	left join v_LpuRegion lr  on lr.LpuRegion_id=pc.LpuRegion_id
		and (lr.Lpu_id=p_lpu_id or p_lpu_id is null)
	where
	pc.Person_id=epl.Person_id
	and (pc.Lpu_id=p_lpu_id or p_lpu_id is null)
	and (pc.PersonCard_endDate>p_enddate or pc.PersonCard_endDate is null)
	and pc.PersonCard_begDate<p_enddate
	and (lr.LpuRegion_id=p_LpuRegion_id or p_LpuRegion_id is null)
	order by pc.PersonCard_begDate desc limit 1
	) LR on true


where  (evpl.lpu_id =p_lpu_id or p_lpu_id is null)
	and (evpl.paytype_id=p_PayType_id or p_PayType_id is null)
	and (lr.LpuRegion_id=p_LpuRegion_id or p_LpuRegion_id is null)
	and evpl.EvnVizitPL_setDate<=p_enddate
	and (evpl.MedPersonal_id=p_MedPersonal_id or p_MedPersonal_id is null)
group by 
PS.Person_surname,PS.Person_Firname, PS.Person_Secname,PS.Person_BirthDay,
epl.evnPL_setDate,epl.evnPL_disDate, epl.EvnPL_NumCard,epl.evnPL_id,
LR.LpuRegion_Name,
Diag.Diag_Code
having count(case when asd.UslugaComplex_Name like '%первич%' then evpl.EvnvizitPL_id end)=0
and  count (case when evpl.VizitClass_id =1  then evpl.EvnvizitPL_id end)=0
	and count (case when TC.TreatmentClass_Code in ('1','1.1','1.2','1.3') then evpl.EvnvizitPL_id end)>0
