with  _params as  
(
	select
		cast($1 as int8) as p_lpu_id,
		cast($2 as int8) as p_paytype_id,
		cast($3 as int8) as paramYesNo,
		cast(to_timestamp($4,'dd.mm.yyyy') as timestamp) as p_begdate,
		cast(to_timestamp($5,'dd.mm.yyyy') as timestamp) as p_enddate 
)


 , params as (
select case when paramYesNo = 2 then orgsmo_id else 1 end as smo_id,
       _params.*
  from _params 
  left join lateral(select o.orgsmo_id from v_orgsmo o where paramYesNo = 2 and org_id = 691629 limit 1)  as l_o on true)

,result5 as(
select distinct
	RD.LpuSection_Name || ' / ' || LS.lpuSectionProfile_name || ' / ' || MP.Person_Fio as LpuSection_Name,
	ES.*,
	RD.evn_id,
	RD.Person_id,
	R.Lpu_id,
	R.PayType_id,
	PT.PayType_Name,
	RD.NumCard,
	PS.Person_Surname,
	PS.Person_Firname,
	PS.Person_secname,
	RD.Person_BirthDay,
	rpt.workage(RD.Person_BirthDay, Sex_id, p_begdate) as pens,
	RD.Evn_setDate::date,
	RD.Evn_disDate::date,
	RD.RegistryData_KDFact,
	R.Registry_Sum,
	RD.RegistryData_ItogSum
from params
	inner join r1.Registry R on true
	inner join r1.v_RegistryData RD on RD.Registry_id = R.Registry_id
	inner join v_PersonState PS on PS.Person_id = RD.Person_id
	inner join v_EvnPS EPS on EPS.EvnPS_Rid = RD.Evn_Rid

	inner join lateral
	(
		select 
			ES.HTMedicalCareClass_id,
			ES.EvnSection_disDate
		from v_EvnSection ES
		where ES.personevn_id = EPS.personevn_id
			and ES.EvnSection_disDate between p_begdate and p_enddate
--		order by ES.EvnSection_setDate 
		limit 1
	)ES on true

	left join v_MedPersonal MP on MP.Medpersonal_id = RD.Medpersonal_id
	left join v_PayType PT on PT.Paytype_id = R.PAyType_id
	left join v_LpuSection LS on LS.LpuSection_id = RD.LpuSection_id
	left join v_LpuUnitType lut on lut.LpuUnitType_id = RD.LpuUnitType_id

where (1=1)
	and RD.Evn_disDate between p_begdate and p_enddate
	and R.Lpu_id = p_lpu_id
	and (R.PayType_id = p_paytype_id or p_paytype_id is null)
	and lut.lpuunittype_sysnick like '%stac%'
	and ES.HTMedicalCareClass_id is not null
	and coalesce(R.Registry_deleted,1) <> 2
    and (smo_id = 1 or (RD.orgsmo_id = smo_id and smo_id != 1)) 
	and not exists 
		(
			select *
			from r1.Registry Reg
				inner join r1.RegistryData RegD on RegD.Registry_id = Reg.Registry_id
			where (1=1)
				and Reg.Registry_deleted = 2
				and RegD.evn_rid = RD.evn_rid
				and RegD.Evn_disDate between p_begdate and p_enddate
		)
)

--	select * from result5
--where person_surname like '%КРОКОДИЛОВ%'

SELECT 
  LpuSection_Name AS v01_lpusection_name,
  COUNT (DISTINCT Person_id) AS v02_Cnt_Pers,
  COUNT (Evn_id) AS v03_CntEvn,
  COALESCE(sum (RegistryData_KdFact),0) AS v04_koikoDni,
  COALESCE(SUM(RegistryData_ItogSum),0)  AS v05_sum,
-- нетрудоспособный возраст (rpt.workage=0)  
  COUNT (DISTINCT CASE WHEN pens = 0 THEN person_id END) AS v06_WorkingPeople,
  COUNT (CASE WHEN pens = 0 THEN Evn_id END) AS v07_evn_WorkingPeople,
  COALESCE(SUM (CASE WHEN pens = 0 THEN RegistryData_KdFact END),0) AS v08_evn_KoikoDni,
  COALESCE(SUM(CASE WHEN pens = 0 THEN RegistryData_ItogSum END),0) AS v09_sum,
-- старше трудоспособного возраста (rpt.workage=1)  
  COUNT (DISTINCT CASE WHEN pens = 1 THEN person_id END) AS v10_Pensionair,
  COUNT (CASE WHEN pens = 1 THEN Evn_id END) AS v11_Pens_Evn,
  COALESCE(SUM (DISTINCT CASE WHEN pens = 1 THEN RegistryData_KdFact END),0) AS v12_Pens_koikodni,
  COALESCE(SUM(CASE WHEN pens = 1 THEN RegistryData_ItogSum END),0) AS v13_sum

FROM result5
GROUP BY lpusection_name      
ORDER BY lpusection_name   

