with _prm as (
select $1::bigint as paramRefLpu,       
       $2::bigint as paramLpu,
       $3::integer as paramBegPersonAge,
       $4::integer as paramEndPersonAge,
       $5::integer as paramSex,
       $6::bigint as paramBegDiag,
       $7::bigint as paramEndDiag,
       coalesce(nullif($8,-1),0)::integer as paramYesNo,
       to_date($9,'dd.mm.yyyy') as paramBegDate,
       to_date($10,'dd.mm.yyyy') as paramEndDate,
       (select dbo.tzGetDate()) as cur_dt)
       
, _diag as (
select min(d.diag_sortid) as first_id, 
       max(d.diag_sortid) as last_id,
       max(case when d.diag_id = paramBegDiag then diag_sortid end) as beg_id,
       max(case when d.diag_id = paramEndDiag then diag_sortid end) as end_id 
from _prm 
join v_diag d on d.diag_code ~ 'C|D0' and d.diag_code not ilike '%-%')

, diags as ( 
select d.diag_id,
       d.diag_code
  from _diag
  join v_diag d on d.diag_sortid between coalesce(beg_id,first_id) and coalesce(end_id,last_id)   
   and d.diag_code not ilike '%-%')
   

, onko_lpu as (
select array_agg(ls.lpusection_id) as onko_arr
  from v_lpusection ls where ls.lpusectionprofile_code in ('60','18'))

, st_agg as (
select s.surveytype_id,
       s.surveytype_name in ('Прием (осмотр) врача-терапевта','Осмотр фельдшером (акушеркой) или врачом акушером-гинекологом','Осмотр (консультация) врачом-неврологом',
                             'Осмотр (консультация) врачом-хирургом или врачом-урологом','Осмотр (консультация) врачом-хирургом или врачом-колопроктологом',
                             'Осмотр (консультация) врачом-оториноларингологом','Осмотр (консультация) врачом-офтальмологом','Прием (осмотр) врача-дерматовенеролога') as is_dvn
  from v_SurveyType s
where s.surveytype_name in ('Прием (осмотр) врача-терапевта','Осмотр фельдшером (акушеркой) или врачом акушером-гинекологом','Осмотр (консультация) врачом-неврологом',
                            'Осмотр (консультация) врачом-хирургом или врачом-урологом','Осмотр (консультация) врачом-хирургом или врачом-колопроктологом',
                            'Осмотр (консультация) врачом-оториноларингологом','Осмотр (консультация) врачом-офтальмологом','Прием (осмотр) врача-дерматовенеролога',
                            'Прием (осмотр) врача - педиатра','Осмотр фельдшером (акушеркой) или врачом акушером-гинекологом','Осмотр (консультация) врача-невролога',
                            'Прием (осмотр) врача - детского хирурга','Прием (осмотр) врача - травматолога-ортопеда','Прием (осмотр) врача - психиатра детского',
                            'Прием (осмотр) врача - психиатра подросткового','Прием (осмотр) врача - стоматолога детского','Прием (осмотр) врача - детского эндокринолога',
                            'Осмотр (консультация) врачом-оториноларингологом','Прием (осмотр) врача - офтальмолога','Прием (осмотр, консультация) врача-детского уролога-андролога'))


, prm as (
select _prm.*,
       (select array_agg(diag_id) from diags) as diag_arr, 
       (select onko_arr from onko_lpu) as onko_arr,
       (select array_agg(surveytype_id) as dvn_agg from st_agg where is_dvn) as dvn_agg,
       (select array_agg(surveytype_id) as dvn_agg from st_agg where not is_dvn) as mon_agg
  from _prm 
)

, pl as ( 
select 'ТАП №' as flg, 
       pl.Lpu_id,
       pl.person_id,
       pl.diag_id,
       pl.lpusection_id,
       deseasetype_id,
       pl.evnpl_numcard as numcard,
       pl.evnpl_disdate as end_dt,
       pl.medpersonal_id 
  from prm 
  join v_evnpl pl on (paramLpu is null or pl.lpu_id = paramLpu) and pl.evnpl_disdate between paramBegDate and paramEndDate and pl.evnpl_isfinish = 2
   and pl.diag_id = any (diag_arr)
  --join diags on diags.diag_id = pl.diag_id
  )


, ps as ( 
select 'КВС №' as flg, 
       ps.Lpu_id,
       ps.person_id,
       ps.diag_id,
       ps.lpusection_id,
       case when ps.diag_id = es.diag_id then coalesce(ps.deseasetype_id,es.deseasetype_id) else ps.deseasetype_id end as deseasetype_id,
       ps.evnps_numcard as numcard,
       ps.evnps_disdate as end_dt,
       es.medstafffact_id
  from _prm 
  join v_evnps ps on (paramLpu is null or ps.lpu_id = paramLpu) and ps.evnps_disdate between paramBegDate and paramEndDate 
  join v_evnsection es on es.evnsection_pid = ps.evnps_id and (es.evnsection_index = es.evnsection_count - 1) and coalesce(es.LeaveType_id, es.LeaveType_prmid) is not null
  join diags on diags.diag_id = ps.diag_id)


, disp as (
select distinct case when d.dispclass_id = 1 then 'ДВН 1'
            when d.dispclass_id = 2 then 'ДВН 2'
            when d.dispclass_id in (3,7) then 'ДДС 1'
            when d.dispclass_id in (4,8) then 'ДДС 2'
            when d.dispclass_id = 10 then 'МОН 1'
            when d.dispclass_id = 12 then 'МОН 2' end as flg,
       d.Lpu_id,     
       d.person_id,
       diags.diag_id,
       coalesce(EVDD.lpusection_id,EVZDD.lpusection_id) as lpusection_id,
       coalesce(EVDD.dopdispdiagtype_id,EVZDD.dopdispdiagtype_id) as dopdispdiagtype_id,       
       d.evnpldisp_disdate as end_dt,
       coalesce(EVZDD.medstafffact_id,EVDD.medstafffact_id) as medstafffact_id
  from prm 
  join v_EvnPLDisp d on (paramLpu is null or d.lpu_id = paramLpu) and d.evnpldisp_disdate between paramBegDate and paramEndDate 
   and d.evnpldisp_isfinish = 2
   and d.dispclass_id in (1,2,3,4,7,8,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 and EvnPLDispOrp_IsFinish = 2--(ДДС) 
  left join v_EvnPLDispTeenInspection d_104 on d_104.EvnPLDispTeenInspection_id = d.evnpldisp_id and EvnPLDispTeenInspection_IsFinish = 2--(МОН)   
  join v_DopDispInfoConsent ddc on ddc.evnpldisp_id = coalesce(d_101.evnpldispdop13_id,d_104.EvnPLDispTeenInspection_id,d_9.EvnPLDispOrp_id)
  join v_SurveyTypeLink STL on STL.SurveyTypeLink_id = ddc.SurveyTypeLink_id 
   and ((d_101.evnpldispdop13_id is null and  d_104.EvnPLDispTeenInspection_id is null) or 
        (d_101.evnpldispdop13_id is not null and  STL.SurveyType_id = any (dvn_agg)) or
        (d_104.EvnPLDispTeenInspection_id is not null and  STL.SurveyType_id = any (mon_agg)))
  left join v_EvnVizitDispDop EVDD on EVDD.EvnVizitDispDop_pid = ddc.evnpldisp_id and EVDD.DopDispInfoConsent_id = ddc.DopDispInfoConsent_id
  left join v_EvnVizitDispOrp EVZDD on EVZDD.EvnVizitDispOrp_pid = d_9.EvnPLDispOrp_id and  EVZDD.dopdispinfoconsent_id = ddc.dopdispinfoconsent_id  
  join diags on diags.diag_id = coalesce(EVDD.Diag_id,EVZDD.Diag_id))
  
, master as (
select concat_ws(' ',flg,numcard) as flg,Lpu_id,person_id,diag_id,lpusection_id,deseasetype_id,null::bigint as dopdispdiagtype_id,end_dt,medpersonal_id,null::bigint as medstafffact_id from pl union all
select concat_ws(' ',flg,numcard) as flg,Lpu_id,person_id,diag_id,lpusection_id,deseasetype_id,null::bigint as dopdispdiagtype_id,end_dt,null::bigint as medpersonal_id,medstafffact_id from ps union all
select flg,Lpu_id,person_id,diag_id,lpusection_id,null as deseasetype_id,dopdispdiagtype_id,end_dt,null::bigint as medpersonal_id,medstafffact_id from disp)

, pats as (
select distinct m.person_id,
       coalesce(disp.lpusection_id = any (onko_arr),'false') as is_onko_section,
       case when disp.persondisp_id is not null then 1 else 1 end as is_disp,
       Lpu_attach,
       attach_type,
       l_card.LpuRegion_id,
       to_char(disp.persondisp_begdate,'dd.mm.yyyy') as disp_dt,
       disp.medpersonal_id,
       disp.lpusection_id,       
       reg_dt,       
       paramEndDate,
       paramLpu,
       paramBegPersonAge,
       paramEndPersonAge,
       paramSex,
       paramYesNo
  from master m
  join lateral (
       select pc.lpu_id as Lpu_attach,
              pc.lpuregiontype_name as attach_type,
              pc.LpuRegion_id,
              pc.person_id,
              paramBegDate,
              paramEndDate,
              onko_arr,
              paramLpu,
              paramBegPersonAge,
              paramEndPersonAge,
              paramSex,
              paramYesNo
         from prm 
         join v_PersonCard pc on (paramRefLpu is null or pc.lpu_id = paramRefLpu)
          and pc.person_id = m.person_id     
          and cur_dt between coalesce(PC.personcard_begdate,cur_dt) and coalesce(PC.PersonCard_endDate,cur_dt)
          and PC.LpuAttachType_id = '1' order by pc.lpuattachtype_name nulls last, PC.personcard_begdate desc limit 1) as l_card on true 
   left join v_persondisp disp on disp.person_id = l_card.person_id and disp.diag_id = m.diag_id 
    and coalesce(disp.persondisp_begdate,paramBegDate)<= paramEndDate 
    and coalesce(disp.persondisp_enddate,paramEndDate)>= paramBegDate
   left join lateral (
        select to_char(personregister_setdate,'dd.mm.yyyy') as reg_dt
          from v_PersonRegister pr 
        where pr.person_id = l_card.person_id and pr.diag_id = m.diag_id 
          and pr.PersonRegisterType_id = 3
          and coalesce(pr.personregister_disdate,paramEndDate+1) > paramEndDate
        order by pr.personregister_setdate limit 1) as l_pr on true 
) 

, t as (    
select concat_ws(' ',ps.person_surname,ps.person_firname,ps.person_secname) as person_fio,
       to_char(ps.person_birthday,'dd.mm.yyyy') as birthday,
       extract(year from age(paramEndDate,ps.person_birthday)) as age,
       padr.address_address as fact_addr,
       ps.person_snils as snils,
       ps.Polis_Num,
       osmo.orgsmo_nick as smo,
       LpuPC.lpu_nick as Lpu_attach,
       attach_type,
       LR.lpuregion_name,
       l.lpu_nick as lpu_name,
       flg,
       d.diag_code,
       d.diag_name,
       coalesce(des.deseasetype_name,dopdes.dopdispdiagtype_name) as charecter_name,
       ls.lpusection_name as dep_name,
       ls.lpusectionprofile_name as dep_prof,
       coalesce(mf.person_fio,doc_fio) as doc_fio,
       to_char(end_dt,'dd.mm.yyyy') as end_dt,
       coalesce(reg_dt,'-') as str_21,
       case when not is_onko_section then coalesce(disp_dt,'-') else '-' end as str_22,
       coalesce(str_23,'-') as str_23,
       coalesce(lss.lpusection_name,'-') as str_24,
       coalesce(lss.lpusectionprofile_name,'-') as str_25,       
       coalesce(paramBegPersonAge,0) as paramBegPersonAge,
       coalesce(paramEndPersonAge,150) as paramEndPersonAge
  from master m
  join pats p on m.person_id = p.person_id 
  join v_PersonState_all ps on ps.person_id = p.person_id
   and (paramSex is null or  ps.sex_id = paramSex)
  left join address padr on padr.address_id = ps.paddress_id 
  left join v_polis pol on pol.polis_id = ps.polis_id
  left join v_orgsmo as osmo on osmo.orgsmo_id = pol.orgsmo_id
  join v_lpu as LpuPC on LpuPC.lpu_id = p.Lpu_attach
  left join v_LpuRegion LR on LR.LpuRegion_id = p.LpuRegion_id
  join v_lpu as l on l.lpu_id = m.Lpu_id
  left join v_diag d on d.diag_id = m.diag_id
  left join v_deseasetype des on des.deseasetype_id = m.deseasetype_id
  left join v_dopdispdiagtype dopdes on dopdes.dopdispdiagtype_id = m.dopdispdiagtype_id
  left join v_lpusection ls on ls.lpusection_id = m.lpusection_id
  left join lateral (select med.person_fio as doc_fio from v_medpersonal med where med.medpersonal_id = m.medpersonal_id limit 1) as l_med on true 
  left join v_medstafffact mf on mf.medstafffact_id = m.medstafffact_id
  left join lateral (select medd.person_fio as str_23 from v_medpersonal medd where medd.medpersonal_id = p.medpersonal_id and not is_onko_section limit 1) as l_medd on true 
  left join v_lpusection lss on lss.lpusection_id = p.lpusection_id and not is_onko_section
where (paramYesNo = 0 or (paramYesNo = 1 and reg_dt is null)  or (paramYesNo = 2 and reg_dt is not null))
)
  
select row_number() OVER() as nn,
       person_fio,
       birthday,
       age,
       fact_addr,
       snils,
       Polis_Num,
       smo,
       Lpu_attach,
       attach_type,
       lpuregion_name,
       lpu_name,
       flg,
       diag_code,
       diag_name,
       charecter_name,
       dep_name,
       dep_prof,
       doc_fio,
       end_dt,
       str_21,
       
       str_22,
       str_23,
       str_24,
       str_25,
       case when str_22 = '-' then '-' else diag_code end as str_26,
       case when str_22 = '-' then '-' else diag_name end as str_27
  from t
where age between paramBegPersonAge and paramEndPersonAge
order by 1,12
