with prm as (select cast($1 as timestamp) as paramBegDate, 
     	            cast($2 as timestamp)+ interval '1 day' - interval '1 sec' as paramEndDate, 
                    $3::bigint as paramLpu, 
                    $4::bigint as paramLpuBuilding, 
                    $5::bigint as paramLpuUnit, 
                    $6::bigint as paramLpuSection,
                    $7::bigint as paramMedStaffFact,                    
                    $8::bigint as paramLpuRegion,
                    $9::bigint as paramPrivilegeFed,
                    $10::bigint as paramPrivilegeType,                    
                    $11::bigint as paramIsDisp,                    
                    $12::bigint as paramPayType,
                    $13::bigint as paramIsSelo,
                    $14::bigint as paramBegPersonAge,
                    $15::bigint as paramEndPersonAge,  
                    $16::bigint as paramSex,
                    $17::bigint as paramBegDiag,
                    $18::bigint as paramEndDiag,
                    $19::bigint as paramUslugaComplex,
                    $20::bigint as paramLpuSectionProfile,
                    $21::bigint as paramVizitType,
                    $22::bigint as paramDeseaseType,
                    $23::bigint as paramServiceType,
                    (select * from GetRegion()) as reg_num)
                    
, m_diag as (
select d.diag_id
  from prm 
  join v_diag d on coalesce(paramBegDiag,paramEndDiag) is not null and d.diag_id between coalesce(paramBegDiag,0) and coalesce(paramEndDiag,999999))
                    
, main as (
select e.evnpl_id as cas,
       e.evnpl_numcard,
       pl.evnvizitpl_id as viz,
       pl.evnvizitpl_setdate::date as step_dt,
       pl.person_id,       
       pl.vizittype_id,
       to_char(e.evnpl_setdate,'dd.mm.yyyy') as open_dt,
       to_char(e.evnpl_disdate,'dd.mm.yyyy') as close_dt,
       pl.diag_id,
       pl.lpusectionprofile_id,
       pl.medstafffact_id,       
       to_char(pl.evnvizitpl_setdate,'dd.mm.yyyy') as sign_dt,
       (pl.evnvizitpl_index = pl.evnvizitpl_count - 1) is_last_viz,
       e.evnpl_isfinish,
       pl.paytype_id,
       pl.deseasetype_id
  from prm 
  join v_EvnVizitPL pl on pl.lpu_id = paramLpu and pl.evnvizitpl_setdt between paramBegDate and paramEndDate 
   and pl.evnclass_id!=13  
    and (prm.paramLpuSection is null or pl.lpusection_id = prm.paramLpuSection)
    and (prm.paramMedStaffFact is null or pl.medpersonal_id = prm.paramMedStaffFact)    
    and (prm.paramPayType is null or pl.paytype_id = prm.paramPayType)    
    and (prm.paramDeseaseType is null or pl.deseasetype_id = prm.paramDeseaseType)    
    and (prm.paramVizitType is null or pl.vizittype_id = prm.paramVizitType)    
    and (prm.paramServiceType is null or pl.servicetype_id = prm.paramServiceType)
  join v_EvnPL e on e.evnpl_id = pl.evnvizitpl_pid 
  join v_LpuSection s on s.lpusection_id = pl.lpusection_id
    and (prm.paramLpuBuilding is null or s.LpuBuilding_id = prm.paramLpuBuilding)
    and (prm.paramLpuUnit is null or s.lpuunit_id = prm.paramLpuUnit)
    and (prm.paramLpuSectionProfile is null or s.lpusectionprofile_id = prm.paramLpuSectionProfile)   
where (
      (prm.paramLpuRegion is null or (exists (select from v_PersonCard pc where pc.person_id = e.person_id and paramLpuRegion = pc.lpuregion_id))) and 
      (prm.paramSex is null or (exists (select from v_PersonState p where p.person_id = e.person_id and paramSex = p.sex_id))) and      
      (prm.paramUslugaComplex is null or (exists (select from v_EvnUsluga u where u.evnusluga_pid = pl.evnvizitpl_id and paramUslugaComplex = u.uslugacomplex_id))) and
      (coalesce(paramBegPersonAge,paramEndPersonAge) is null or (exists (select from v_PersonState pp where pp.person_id = e.person_id and extract(year from age(pl.evnvizitpl_setdate,pp.person_birthday)) between coalesce(paramBegPersonAge,0) and coalesce(paramEndPersonAge,150)))) and
      (coalesce(paramBegDiag,paramEndDiag) is null or (exists (select from m_diag d where d.diag_id = pl.diag_id))) and   
      (coalesce(paramPrivilegeType,paramPrivilegeFed) is null or (exists (select from v_personprivilege v where v.person_id = e.person_id and pl.evnvizitpl_setdate between coalesce(v.personprivilege_begdate,pl.evnvizitpl_setdate) and coalesce(v.personprivilege_enddate,pl.evnvizitpl_setdate)
           and ((paramPrivilegeType is null or v.privilegetype_id = paramPrivilegeType) and (nullif(paramPrivilegeFed,3) is null or (paramPrivilegeFed=1 and privilegetype_code between '10' and '150') or (paramPrivilegeFed=2 and privilegetype_code between '250' and '332')))))) and            
      (prm.paramIsSelo is null or (exists (select from v_PersonState pr join v_Address adr on pr.person_id = e.person_id and adr.address_id = pr.paddress_id and adr.klareatype_id = paramIsSelo ))) and
      (prm.paramIsDisp is null or 
      (prm.paramIsDisp = 2 and exists (select from v_persondisp disp where disp.person_id = e.person_id and pl.evnvizitpl_setdate between coalesce(disp.persondisp_begdate,pl.evnvizitpl_setdate) and coalesce(persondisp_enddate,pl.evnvizitpl_setdate))) or
      (prm.paramIsDisp = 1 and not exists (select from v_persondisp disp where disp.person_id = e.person_id and pl.evnvizitpl_setdate between coalesce(disp.persondisp_begdate,pl.evnvizitpl_setdate) and coalesce(persondisp_enddate,pl.evnvizitpl_setdate))) ))
)

, pats as (
select distinct person_id,
       (select reg_num from prm) as reg_num 
  from main m) 

, pat as (
select p.person_id,
       concat_ws(' ',p.person_surname,p.person_firname,p.person_secname) as pat_fio,       
       p.person_birthday as birth_dt,
       adr.address_address as addr,
       case when p.sex_id = 1 then 'М' 
            when p.sex_id = 2 then 'Ж' end as sex,
       reg_num      
  from pats
  join v_PersonState p on p.person_id = pats.person_id 
  left join v_Address adr on adr.address_id = coalesce(p.paddress_id,p.uaddress_id))
  
, cas as (
select distinct cas as cas_id,sign_dt
  from main m where is_last_viz)  
  
, viz as (
select viz,
       person_id,
       step_dt 
  from main m)    

, reg as (  
select viz,       
       string_agg(distinct rt.personregistertype_name, ', ') as regists
  from viz v 
  join v_personregister r on r.person_id = v.person_id and step_dt between coalesce(r.personregister_setdate,step_dt) and coalesce(r.personregister_disdate,step_dt)
  join v_personregistertype rt on rt.personregistertype_id = r.personregistertype_id
group by 1)   

select to_char(step_dt,'dd.mm.yyyy') as step_dt,
       pat_fio,
       addr,
       to_char(birth_dt,'dd.mm.yyyy') as birth_dt,
       extract(year from age(step_dt,birth_dt)) as age,
       sex,
       v.vizittype_name as viz_name,
       cas,
	   m.evnpl_numcard,
       open_dt,
       close_dt,
       concat_ws(' ',d.diag_code,d.diag_name) as diag,
       ds.deseasetype_name,
       pr.lpusectionprofile_name as prof_name,
       med.person_fio as doc_fio,  
       srvs,       
       cas.sign_dt,
       case when evnpl_isfinish = 2 then 'Да' 
            when evnpl_isfinish = 1 then 'Нет' end as stat,
       pt.paytype_name as pay_name,
       regists
  from main m
  join pat p on p.person_id = m.person_id
  left join reg on reg.viz = m.viz
  left join v_vizittype v on v.vizittype_id = m.vizittype_id
  left join v_diag d on d.diag_id = m.diag_id
  left join v_medstafffact med on med.medstafffact_id = m.medstafffact_id
  left join v_lpusectionprofile pr on pr.lpusectionprofile_id = m.lpusectionprofile_id
  left join v_paytype pt on pt.paytype_id = m.paytype_id
  left join v_deseasetype ds on ds.deseasetype_id = m.deseasetype_id
  left join cas on cas.cas_id = m.cas
  left join lateral (
       select string_agg(distinct concat_ws(' ',usl.uslugacomplex_code,usl.uslugacomplex_name),', ') as srvs
              --string_agg(distinct concat_ws(' ',coalesce(uu.uslugacomplex_code,usl.uslugacomplex_code),coalesce(uu.uslugacomplex_name,usl.uslugacomplex_name)),', ') as srvs  
         from v_EvnUsluga u 
         join v_UslugaComplex usl on u.evnusluga_pid = m.viz and usl.uslugacomplex_id = u.uslugacomplex_id 
         /*left join v_UslugaComplex uu on reg_num = '12' and usl.uslugacomplex_2011id = uu.uslugacomplex_id*/) as us on true
