with params as(
	select
		to_timestamp($1, 'dd.mm.yyyy') as p_begdate,
        to_timestamp($2, 'dd.mm.yyyy') as p_enddate,
		--cast(? as timestamp) as p_begdate,
		--cast(? as timestamp) as p_enddate,		
		cast($3 as bigint) as p_Lpu_id,
		cast($4 as bigint) as p_LpuBuilding_id,
		cast($5 as bigint) as p_LpuUnit_id,
		cast($6 as bigint) as p_lpusection_id,
		cast($7 as bigint) as p_MedPersonal_id,
		cast($8 as bigint) as p_PayType_id,
		cast($9 as bigint) as p_Org_id
		
		
)	
select 
row_number() over (order by 
        v2,
		Person_id,
		evnpl_id, 
		v15
    ) as RecAll_Num,evnpl_id,v2,v3,v4,v5,v6,v7,v8,v9,v10,v11,v12,v13,v14,v15,v16,v17,v18,v19
from (
select distinct
epl.evnpl_id,epl.person_id,
concat_ws(' ',ps.person_surname,ps.person_firname,ps.person_secname) v2,
to_char(ps.person_birthday,'dd.mm.yyyy') v3,
coalesce(PersonAddress1.address_address,PersonAddress2.address_address) v4,
ps.person_phone v5,
ps.person_snils v6,
o.Org_Name v7,
epl.evnpl_numcard v8,
to_char(epl.EvnPL_setDate,'dd.mm.yyyy' ) v9,
to_char(epl.EvnPL_disDate ,'dd.mm.yyyy' ) v10,
vizit.evcount v11,
ls.lpusection_name v12,
dg.person_fio v13,
dg.Diag_FullName v14,
uc.UslugaComplex_code v15,
uc.UslugaComplex_name v16,
SUM(eu.EvnUsluga_Kolvo) OVER(PARTITION BY epl.Evnpl_id,uc.UslugaComplex_code) v17,
--eu.EvnUsluga_Kolvo v17,
pt.paytype_name v18,
 RD.RegistryData_ItogSum v19
from v_evnpl epl
inner join params on true
inner join v_lpu lpu on lpu.lpu_id=epl.lpu_id and (lpu.lpu_id=p_Lpu_id or p_Lpu_id is null)
inner join v_personstate ps on ps.person_id=epl.person_id
--inner join v_evnvizitpl ev	on epl.EvnPL_id = ev.EvnVizitPL_pid 
left join Job pjob  on ps.Job_id = pjob.Job_id
left join v_Org o  on pjob.org_id=o.org_id
left join lateral (
        select  
            dg.Diag_FullName,ev.PayType_id,dg.Diag_Code,
            msf.MedPersonal_id,msf.person_fio ,msf.person_fin,ev.lpusection_id 
		from v_evnvizitpl ev 
		join v_diag dg  on dg.Diag_id = ev.Diag_id
		left join v_medstafffact msf on msf.medstafffact_id =ev.medstafffact_id
		where epl.EvnPL_id = ev.EvnVizitPL_pid 
		    and (ev.EvnVizitPL_Count - 1) = ev.EvnVizitPL_Index limit 1
    ) dg on true
left join lateral (
        select 
            count(distinct ev.evnvizitpl_id) evcount
        from v_evnvizitpl ev               
        where epl.EvnPL_id = ev.EvnVizitPL_pid 
    ) vizit on true
left join v_PayType pt on pt.paytype_id =dg.PayType_id
left join r89.v_RegistryData RD on epl.evnpl_id  = RD.Evn_rid and RD.Paid_id=2
left join v_EvnUsluga as eu  on epl.EvnPL_id = eu.EvnUsluga_rid
left join v_UslugaComplex as uc on eu.UslugaComplex_id = uc.UslugaComplex_id 
--left join v_MedPersonal as mp on mp.MedPersonal_id = epl.MedPersonal_id
	--	and mp.Lpu_id = epl.Lpu_id 
left join dbo.Address PersonAddress1 on PersonAddress1.Address_id = ps.PAddress_id
left join dbo.Address PersonAddress2 on PersonAddress2.Address_id = ps.UAddress_id
left join v_lpusection ls on ls.lpusection_id =dg.lpusection_id
left join v_lpuunit lu on lu.lpuunit_id=ls.lpuunit_id 
where epl.EvnPL_disDate between p_begdate and p_enddate
and epl.EvnPL_IsFinish=2
and epl.EvnClass_id=3
and (ls.lpusection_id =p_lpusection_id or p_lpusection_id is null)
and (lu.lpuunit_id=p_LpuUnit_id or p_LpuUnit_id is null) 
and (lu.lpubuilding_id =p_lpubuilding_id or p_lpubuilding_id is null)
and (dg.MedPersonal_id=p_MedPersonal_id or p_MedPersonal_id is null)
and (dg.PayType_id =p_PayType_id or p_PayType_id is null)
and (o.org_id=p_Org_id or p_org_id is null)
and  (o.Org_Name like '%ОМВД%'
				or o.Org_Name like '%МВД%'
				or o.Org_Name like '%Росгвардия%'
				or o.Org_Name like '%МЧС%'
				or o.Org_Name like '%РУФСБ%'
				or o.Org_Name like '%ОВД%'
				or o.Org_Name like '%ФЕДЕРАЛЬНАЯ СЛУЖБА БЕЗОПАСНОСТИ%'
				or o.Org_Name like '%Внутренних Дел%'
				or o.Org_Name like '%ФСБ%'
				or o.Org_Name like '%РОВД%'
				or o.Org_Name like '%РУВД%'
				or o.Org_Name like '%УВД%'
				)
and EU.EvnClass_SysNick in ('EvnUslugaCommon', 'EvnUslugaOper')				
) q		
