with prm as (
select to_date($1,'dd.mm.yyyy') as paramBegDate,
       to_date($2,'dd.mm.yyyy') as paramEndDate,
       $3::bigint as paramLpu,
       $4::bigint as paramDispFlg,       
       $5::bigint as paramLpuBuilding,
       $6::bigint as paramLpuUnit,
       $7::bigint as paramLpuSection,
       $8::bigint as paramMedStaffFact,
       $9::bigint as paramPayType)
      
, st as (
select st.surveytype_id
  from v_SurveyType st 
where st.surveytype_name ilike 'Прием (осмотр)%' and st.surveytype_name ~ '-терапевт|- терапевт|педиат')
      
, pl as (
select 1 as flg, 
       pl.evnpl_numcard as numcard,
       pl.person_id,
       pl.evnpl_disdate as dt,
       registry_id,
       viz.lpusection_id,
       viz.medstafffact_id,
       viz.paytype_id
  from prm 
  join v_evnpl pl on  (paramLpu is null or paramLpu = pl.lpu_id) and pl.evnpl_disdate between paramBegDate and paramEndDate and pl.evnpl_isfinish = 2 and nullif(paramDispFlg,1) is null
  join v_Evnvizitpl viz on viz.Evnvizitpl_pid = pl.evnpl_id and (viz.Evnvizitpl_index = viz.Evnvizitpl_count - 1) 
  left join lateral (
       select case when RET.Evn_id is null then rd.registry_id end as registry_id                            
         from r89.v_RegistryData rd 
         left join r89.v_RegistryErrorTFOMS RET on RET.Evn_id = RD.Evn_id and RET.Registry_id = RD.Registry_id and coalesce(RET.RegistryErrorTfomsLevel_id, 1) = 1         
      where rd.evn_rid = pl.evnpl_id) as l_r on true
)


, ps as (
select 2 as flg, 
       ps.evnps_numcard as numcard,
       ps.person_id,
       ps.evnps_disdate as dt,
       registry_id,
       s.lpusection_id,
       s.medstafffact_id,
       s.paytype_id
  from prm 
  join v_evnps ps on (paramLpu is null or paramLpu = ps.lpu_id) and ps.evnps_disdate between paramBegDate and paramEndDate and nullif(paramDispFlg,2) is null
  join v_EvnSection s on s.evnsection_pid = ps.evnps_id and (s.evnsection_index = s.evnsection_count - 1) and coalesce(s.leavetype_id,s.LeaveType_prmid,ps.PrehospWaifRefuseCause_id) is not null
  left join lateral (
       select case when RET.Evn_id is null then rd.registry_id end as registry_id              
         from r89.v_RegistryData rd 
         left join r89.v_RegistryErrorTFOMS RET on RET.Evn_id = RD.Evn_id and RET.Registry_id = RD.Registry_id and coalesce(RET.RegistryErrorTfomsLevel_id, 1) = 1         
       where rd.evn_rid = ps.evnps_id) as l_r on true)


, disp as (
select case when d.dispclass_id in (1,2) then 4 
            when d.dispclass_id in (3,4,7,8) then 5 
            when d.dispclass_id = 5 then 6 
            when d.dispclass_id in (10,12) then 7 end as flg, 
       d.evnpldisp_id::text as numcard,
       d.person_id,
       d.evnpldisp_disdate as dt,
       registry_id,
       l_viz.lpusection_id,
       l_viz.medstafffact_id,
       coalesce(d_101.paytype_id,d_9.paytype_id,d_104.paytype_id,d_103.paytype_id) as paytype_id
       
  from prm 
  join v_EvnPLDisp d on (paramLpu is null or paramLpu = d.lpu_id) and d.evnpldisp_disdate between paramBegDate and paramEndDate and d.evnpldisp_isfinish = 2   
    --and d.evnpldisp_id != any (agg_id)
    and d.dispclass_id in (1,2,3,4,5,7,8,10,12)
    and (paramDispFlg is null or 
        (paramDispFlg = 4 and d.dispclass_id in (1,2)) or
        (paramDispFlg = 5 and d.dispclass_id in (3,4,7,8)) or
        (paramDispFlg = 6 and d.dispclass_id  = 5) or
        (paramDispFlg = 7 and d.dispclass_id in (10,12))) 
  left join v_EvnPLDispDop13 as d_101 on d_101.evnpldispdop13_id = d.evnpldisp_id --(ДВН) 
  left join v_EvnPLDispOrp d_9 on d_9.EvnPLDispOrp_id = d.evnpldisp_id--(ДДС) 
  left join v_EvnPLDispTeenInspection d_104 on d_104.EvnPLDispTeenInspection_id = d.evnpldisp_id --(МОН)   
  left join v_EvnPLDispProf d_103 on d_103.evnpldispprof_id = d.evnpldisp_id--(ПОВН)  
  left join lateral (
       select viz.lpusection_id,
              viz.medstafffact_id
         from prm 
         join v_EvnVizit viz on d.EvnPLDisp_id= viz.EvnVizit_pid 
         join v_evnvizitdisp vd on vd.evnvizitdisp_id = viz.evnvizit_id
         left join v_EvnVizitDispDop vdd on vdd.EvnVizitDispDop_pid = vd.EvnVizitdisp_pid and vdd.EvnVizitDispDop_id = vd.EvnVizitdisp_id and vdd.dopdispinfoconsent_id = vd.dopdispinfoconsent_id--случай/шаг/осмотр
         left join v_EvnVizitDispOrp vdo on vdo.EvnVizitDispOrp_pid = vd.EvnVizitdisp_pid and vdo.EvnVizitDispOrp_id = vd.EvnVizitdisp_id and vdo.dopdispinfoconsent_id = vd.dopdispinfoconsent_id--случай/шаг/осмотр
         join v_DopDispInfoConsent ddc on coalesce(vdd.EvnVizitDispDop_pid,vdo.EvnVizitDispOrp_pid) = ddc.evnpldisp_id and coalesce(vdd.DopDispInfoConsent_id,vdo.DopDispInfoConsent_id) = ddc.DopDispInfoConsent_id
         join v_SurveyTypeLink STL on STL.SurveyTypeLink_id = ddc.SurveyTypeLink_id 
         join st on st.surveytype_id = stl.surveytype_id 
       order by viz.EvnVizit_count desc limit 1) as l_viz on true
  left join lateral (
       select case when RET.Evn_id is null then rd.registry_id end as registry_id              
         from r89.v_RegistryData rd 
         left join r89.v_RegistryErrorTFOMS RET on RET.Evn_id = RD.Evn_id and RET.Registry_id = RD.Registry_id and coalesce(RET.RegistryErrorTfomsLevel_id, 1) = 1         
       where rd.evn_rid = d.evnpldisp_id) as l_r on true)

, par as (
select 8 as flg, 
       coalesce(us.evndirection_num,dir.evndirection_num)as numcard,
       us.person_id,       
       us.evnuslugapar_setdate as dt,
       registry_id,
       null as lpusection_id,
       us.medstafffact_id,
       us.paytype_id
  from prm 
  join v_EvnUslugaPar us on (paramLpu is null or paramLpu = us.lpu_id) and us.evnuslugapar_setdate between paramBegDate and paramEndDate --and us.evnuslugapar_id = us.evnuslugapar_rid --and us.evndirection_num is not null 
    and nullif(paramDispFlg,8) is null
  left join v_evndirection dir on dir.evndirection_id = us.evndirection_id and us.evndirection_num is null  
  left join lateral (
       select case when RET.Evn_id is null then rd.registry_id end as registry_id              
         from r89.v_RegistryData rd 
         left join r89.v_RegistryErrorTFOMS RET on RET.Evn_id = RD.Evn_id and RET.Registry_id = RD.Registry_id and coalesce(RET.RegistryErrorTfomsLevel_id, 1) = 1         
       where rd.evn_rid = us.evnuslugapar_id) as l_r on true    

)

, _main as (
select flg,numcard,person_id,dt,registry_id,lpusection_id::bigint,medstafffact_id,paytype_id,paramLpuBuilding,paramLpuUnit,paramLpuSection,paramMedStaffFact,paramPayType from par join prm on true union all
select flg,numcard,person_id,dt,registry_id,lpusection_id,medstafffact_id,paytype_id,paramLpuBuilding,paramLpuUnit,paramLpuSection,paramMedStaffFact,paramPayType from disp join prm on true union all
select flg,numcard,person_id,dt,registry_id,lpusection_id,medstafffact_id,paytype_id,paramLpuBuilding,paramLpuUnit,paramLpuSection,paramMedStaffFact,paramPayType from ps join prm on true union all
select flg,numcard,person_id,dt,registry_id,lpusection_id,medstafffact_id,paytype_id,paramLpuBuilding,paramLpuUnit,paramLpuSection,paramMedStaffFact,paramPayType from pl join prm on true ) 

, main_ as (
select m.*,
       max(case when r.registry_id = m.registry_id then 1 else 0 END)over(partition by m.numcard)=1 as is_bill_1,
       r.registry_id is not null as is_bill_2
  from _main m
  left join r89.v_Registry r on r.registry_id = m.registry_id)  
  
, main as (  
select distinct flg, 
       numcard,
       m.person_id,       
       dt,
       medstafffact_id,
       paytype_id,
       lpusection_id,
       paramLpuBuilding,
       paramLpuUnit,
       paramLpuSection,
       paramMedStaffFact,
       paramPayType
  from main_ m 
where not is_bill_1 and not is_bill_2
)  

, pats as (select distinct person_id from main) 

, p as ( 
select p.person_id,
       concat_ws(' ',p.person_surname,p.person_firname,p.person_secname) as fio,
       to_char(p.person_birthday,'dd.mm.yyyy') as birthday,
       padr.address_address as address,
       pol.polis_num,
       os.orgsmo_nick as smo
  from pats
  join v_personstate p on p.person_id = pats.person_id  
  left join v_polis pol on pol.polis_id = p.polis_id
  left join v_orgsmo os on os.orgsmo_id = pol.orgsmo_id
  left join address padr on padr.address_id = coalesce(p.paddress_id,p.uaddress_id))
  
select fio,
       birthday,
       address,
       case when flg = 1 then 'ТАП'
            when flg = 2 then 'КВС'            
            when flg = 4 then 'ДВН'
            when flg = 5 then 'ДДС'
            when flg = 6 then 'ПОВН'
            when flg = 7 then 'МОН' 
            when flg = 8 then 'Параклиника' end as flg ,
       numcard,
       to_char(dt,'dd.mm.yyyy') as dt,
       polis_num,
       smo,
       lpusection_name,
       med.person_fio as doc_fio,
       pt.paytype_name as pay_type
  from main m 
  join p on p.person_id = m.person_id
  left join v_medstafffact med on med.medstafffact_id = m.medstafffact_id
  left join v_paytype pt on pt.paytype_id = m.paytype_id
  left join v_lpusection ls on ls.lpusection_id = coalesce(m.lpusection_id,med.lpusection_id)
  left join v_lpuunit lu on lu.lpuunit_id = ls.lpuunit_id
  left join v_lpubuilding lb on lb.lpubuilding_id = ls.lpubuilding_id
where (paramLpuSection is null or coalesce(m.lpusection_id,med.lpusection_id) = paramLpuSection)
and (paramMedStaffFact is null or med.medpersonal_id = paramMedStaffFact)
and (paramPayType is null or m.paytype_id = paramPayType)
and (paramLpuUnit is null or ls.lpuunit_id = paramLpuUnit)
and (paramLpuBuilding is null or ls.lpubuilding_id = paramLpuBuilding)  
order by 4
