with params as( 
select
	cast($1 as int8) as p_lpu_id, 
	cast($2 as int8) as p_lpubuilding_id, 
	cast($3 as int8) as p_lpusection_id,
	cast($4 as int8) as p_medpersonal_id, 
	cast($5 as timestamp) as p_begdate, 
	cast($6 as timestamp) as p_enddate
),
Res_ult as
(
select	
	LS.LpuSection_id,
	LS.LpuSection_Name,
	--EPL.EvnPL_id,
	Evn.Person_id,
	--PS.Person_SurName as Person_Surname,
	--PS.Person_FirName as Person_Firname,
	--PS.Person_SecName as Person_Secname,
	--MP.MedPersonal_id,
	CAST(COALESCE(MP.Person_Fio,' ') AS varchar) AS doctor_fio,
	CAST(COALESCE(MP.Dolgnost_Name, ' ') AS varchar) AS doctor_speciality,
	EVPLD.Diag_id,
	CAST(EVPLD.Diag_Code || '. ' || EVPLD.Diag_Name AS varchar) as diagnoz,
	Evn.Evn_setDT,
	Evn.Evn_disDT,
  EPL.Deseasetype_id
				
from params
	left join EvnClass on true
	inner join Evn on Evn.EvnClass_id = EvnClass.EvnClass_id 
		and EvnClass.EvnClass_id = 3
		and Evn.Evn_deleted = 1
		and (Evn.Lpu_id = p_lpu_id OR p_lpu_id IS NULL)	
    AND CAST(Evn.evn_disdt AS DATE) between p_begdate and p_enddate
	inner join v_EvnPL EPL on EPL.EvnPL_id = Evn.Evn_id 
		and EPL.EvnPL_isFinish = 2
	inner join v_PersonState PS on Evn.Person_id = PS.Person_id						
	left join v_Diag EVPLD on EVPLD.Diag_id = EPL.Diag_id
	left join v_LpuSection LS on LS.LpuSection_id = EPL.LpuSection_id 
	  

	INNER JOIN LATERAL 
	(
		select 
			MP.MedPersonal_id,
			MP.Person_Fio,
			MP.Dolgnost_Name
		from v_MedPersonal MP
		where MP.MedPersonal_id = EPL.MedPersonal_id
			and MP.Lpu_id = EPL.Lpu_id
			--and (MP.MedPersonal_id = p_medpersonal_id or p_medpersonal_id is null)
	) MP ON TRUE 

where CAST(Evn.evn_disdt AS DATE) between p_begdate and p_enddate
  and (LS.lpubuilding_id = p_lpubuilding_id or p_lpubuilding_id is null)
  and (LS.LPuSection_id = p_LpuSection_id or p_LpuSection_id is null)
  and (MP.MedPersonal_id = p_medpersonal_id or p_medpersonal_id is null)

)

--select * from result

select --*
	--R.namelpu,
	R.doctor_fio,
	R.doctor_speciality,
	R.diagnoz,
	Count(Diag_id) as Total_Cnt,
	Count(case when deseaseType_id = 1 then Diag_id end) as Cnt_First,
	Count(case when deseaseType_id = 3 then Diag_id end) as Cnt_Strong

from Res_ult R
group by R.doctor_fio, R.doctor_speciality, R.diagnoz
ORDER BY R.doctor_fio, R.doctor_speciality, R.diagnoz

