EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
--select * from v_lpuunit where lpu_id = 2821
with params as(
	select
		cast(2821 as bigint) as p_lpu_id,
		cast(null as varchar) as p_lpufilialslist,
		cast(null as bigint) as p_lpubuilding_id,
		cast(null as bigint) as p_LpuUnit_id,
		cast(null as bigint) as p_LpuSectionProfile_id,
		cast(null as bigint) as p_LpuSection_id,
		cast(null as bigint) as p_LpuRegionType_id,
		cast(null as bigint) as p_LpuRegion_id,
		cast(null as bigint) as p_MedPersonal_id,
		cast(null as bigint) as p_PayType_id,
		cast(1 as bigint) as p_onlypersoncard_yn,
		cast(null as bigint) as p_DiagFrom_id,
		cast(null as bigint) as p_DiagTo_id,
		cast('2021-10-01' as timestamp) as p_begdate,
		cast('2021-12-31' as timestamp) as p_enddate,
		cast(1 as bigint) as p_Ymd_id,
		cast(18 as bigint) as p_BegAge,
		cast(120 as bigint) as p_EndAge,
		cast(null as bigint) as p_IncludeDopDisp,
		cast(null as bigint) as p_Sex_id,
		cast(1 as bigint) as p_isguzonly,
		cast(null as bigint) as p_isselo
),

Lpu_W as 
(
  select 
    L.Lpu_id,
    coalesce(L.Lpu_pid, L.Lpu_id) as Lpu_pid,
	dbo.GetRegion() as Region_id
  from params
	inner join rpt.GetLpu_succession(p_lpu_id,p_begdate,p_enddate) L on 1=1
	left join v_lpu L1 on l1.lpu_id=l.lpu_id
  inner join dbo.Org O on L1.Org_id = O.Org_id
    and (l.Lpu_id = p_Lpu_id 
	  or (p_Lpu_id is null or p_OnlyPersonCard_YN = 2)
	  or l.Lpu_pid = p_Lpu_id and p_Lpu_id > 0)
    and (case when dbo.GetRegion() = 59 and L.Lpu_id in (13002556,10011362) or O.Okfs_id in (2,4,5) -- 11. Государственная собственность, 13. Собственность субъектов Российской Федерации, 14. Муниципальная собственность
           then 2 else 1 end >= p_IsGUZOnly or p_OnlyPersonCard_YN = 2)
),

LpuSection_W as 
(
  select 
    L.Lpu_id,
    LS.LpuSection_id,
    LS.LpuSection_Name,
    LS.LpuSection_Code
  from dbo.LpuSection LS
	left join params on 1=1
  left join dbo.LpuUnit LU on LS.LpuUnit_id = LU.LpuUnit_id
  left join dbo.LpuBuilding LB on LU.LpuBuilding_id = LB.LpuBuilding_id
  left join Lpu_W as L on LB.Lpu_id = L.Lpu_id 
  where 
    1 = 1
	and (LS.LpuSection_Deleted = 1 or LS.LpuSection_Deleted is null)
	and (LB.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null or p_OnlyPersonCard_YN = 2)
	and (',' || p_LpuFilialsList || ',' like '%,' || cast(LB.LpuFilial_id as varchar(30)) || ',%' or p_LpuFilialsList is null or p_OnlyPersonCard_YN = 2) 
	and (L.Lpu_id > 0 or p_OnlyPersonCard_YN = 2)
	and (ls.lpuSection_id=p_LpuSection_id or p_LpuSection_id is null)
	and (ls.LpuSectionProfile_id=p_LpuSectionProfile_id or p_LpuSectionProfile_id is null)
	and (ls.LpuUnit_id=p_LpuUnit_id or p_LpuUnit_id is null)
),

LpuRegion_W as 
(
  select 
    L.Lpu_id,
    LR.LpuRegion_id
  from dbo.v_Lpu L 
	left join params on 1=1
  inner join dbo.Org O on L.Org_id = O.Org_id
    and p_OnlyPersonCard_YN = 2
    and (Lpu_id = p_Lpu_id 
	  or p_Lpu_id is null and coalesce(L.Lpu_IsTest, 1) = 1
	  or Lpu_pid = p_Lpu_id and p_Lpu_id > 0)
    and (case when dbo.GetRegion() = 59 and L.Lpu_id in (13002556,10011362) or O.Okfs_id in (2,4,5) -- 11. Государственная собственность, 13. Собственность субъектов Российской Федерации, 14. Муниципальная собственность
           then 2 else 1 end >= p_IsGUZOnly)
    and L.Lpu_begDate <= p_EndDate + interval '1 year'
    and coalesce(L.Lpu_endDate, p_BegDate) >= p_BegDate 
  inner join dbo.v_LpuRegion LR on LR.Lpu_id = L.Lpu_id
  left join dbo.LpuSection LS on LS.LpuSection_id = LR.lpusection_id
  left join dbo.LpuUnit LU on LU.LpuUnit_id = LS.LpuUnit_id
  left join dbo.LpuBuilding LB on LB.LpuBuilding_id = LU.LpuBuilding_id
  where 
    (LB.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
    and (',' || p_LpuFilialsList || ',' like '%,' || cast(LB.LpuFilial_id as varchar(30)) || ',%' or p_LpuFilialsList is null)
	and (lr.LpuRegion_id=p_LpuRegion_id or p_LpuRegion_id is null)
	and (lr.LpuRegionType_id=p_LpuRegionType_id or p_LpuRegionType_id is null)
),

PersonCard_W as
(
  select
    PC.Person_id,
	PC.Lpu_id,
	PC.LpuAttachType_id,
	PC.LpuRegion_id,
	cast(cast(PC.PersonCard_begDate as date) as timestamp) as PersonCard_begDate,
	cast(cast(PC.PersonCard_endDate as date) as timestamp) as PersonCard_endDate,
	PC.CardCloseCause_id
  from LpuRegion_W as LR
	left join params on 1=1
  inner join dbo.v_PersonCard_all PC on PC.LpuRegion_id = LR.LpuRegion_id
    and PC.LpuAttachType_id = 1
    and PC.PersonCard_begDate < p_EndDate + interval '1 day'
    and (coalesce(PC.PersonCard_endDate, p_EndDate + interval '1 day') >= p_EndDate + interval '1 day' 
      or PC.CardCloseCause_id = 2 and PC.PersonCard_endDate >= p_BegDate and PC.PersonCard_endDate < p_EndDate + interval '1 day') -- 2. Смерть 
),

PersonState_W as 
(
  select
    PS.Person_id,
    PS.Person_Age,
    PS.Person_BirthDay,
    PS.Sex_id
  from
  (
    select 
      PS.Person_id,
      cast(cast(PS.Person_BirthDay as date) as timestamp) as Person_BirthDay,
      PS.Sex_id,
      date_part('year', p_EndDate) - date_part('year', PS.Person_BirthDay) as Person_Age
    from dbo.v_PersonState PS
	left join params on 1=1
	left join dbo.Address Addr1 on Addr1.Address_id = PS.PAddress_id
	left join dbo.Address Addr2 on Addr2.Address_id = PS.UAddress_id
    where (coalesce(Addr1.KLAreaType_id, Addr2.KLAreaType_id) = p_IsSelo or p_IsSelo is null)
  ) PS 
	left join params on 1=1
  left join PersonCard_W as PC on PC.Person_id = PS.Person_id
	and (coalesce(PC.PersonCard_endDate, p_EndDate + interval '1 day') > p_EndDate or PC.CardCloseCause_id = 2)
  where
    (PC.LpuRegion_id >= -1 or p_OnlyPersonCard_YN = 1)
),

EvnVizitDispDopOrp_W as -- dbo.v_EvnVizitDispDop и dbo.v_EvnVizitDispOrp
(
  select
    CAST(CAST(EVD.Evn_setDT AS DATE) AS timestamp) as EvnVizitDispDopOrp_setDate,
    EVD.Evn_id as EvnVizitDispDopOrp_id, 
		EVD.Evn_pid as EvnVizitDispDopOrp_pid,
		EVD.Lpu_id,
    EVD.MedStaffFact_id,
    EVD.MedPersonal_id,
    EVD.LpuSection_id,
		EVD.DopDispInfoConsent_id,
		EVD.Diag_id,
		EVD.DopDispDiagType_id
  from dbo.EvnVizitDisp EVD 
	left join params on 1=1
  where 
    EVD.EvnClass_id between 14 and 15
	and EVD.Evn_setDT between date_trunc('year', p_BegDate) and p_EndDate + interval '1 day'
	and EVD.DopDispDiagType_id between 1 and 2
	and (EVD.Evn_deleted = 1 or EVD.Evn_deleted is null)
	and (EVD.MedPersonal_id=p_MedPersonal_id or p_MedPersonal_id is null)
	and (EVD.PayType_id=p_PayType_id or p_PayType_id is null)
),

EPLDisp as
(
  select --distinct
  - EPLD.Person_id as cntValue, -- домножение на -1 чтобы ид человека случайно не пересеклось с ид случая                                      
	EPLD.Person_id, 
  PS.Sex_id,
	PS.Person_BirthDay,

	D.Diag_Code,
	D.Diag_id,
  case when EVD.DopDispDiagType_id = 1 then 3 else 2 end::int4 as DeseaseType_Code,-- select * from DopDispDiagType
  LpuP.Lpu_id,
  LpuP.Lpu_pid,
	EPLD.DispClass_id
  from Lpu_W as LpuP
	left join params on 1=1
	inner join dbo.EvnPLDisp EPLD on EPLD.Lpu_id = LpuP.Lpu_id and EPLD.Evn_disDT between p_BegDate and p_EndDate
  inner join v_PersonState as PS on PS.Person_id = EPLD.Person_id  
	  and (dbo.AgeYMD_pg(PS.Person_BirthDay,  cast(cast(year(p_enddate) || '.12.31' as varchar) as timestamp) , cast(p_Ymd_id as int4)) between coalesce(p_BegAge,0) and coalesce(p_EndAge,150))
  left join PersonCard_W as PC on PC.Person_id = EPLD.Person_id and (coalesce(PC.PersonCard_endDate, p_EndDate + interval '1 day') > p_EndDate or PC.CardCloseCause_id = 2)
	left join dbo.Address Addr1 on Addr1.Address_id = PS.PAddress_id
	left join dbo.Address Addr2 on Addr2.Address_id = PS.UAddress_id
  inner join EvnVizitDispDopOrp_W as EVD on EVD.EvnVizitDispDopOrp_pid = EPLD.Evn_id
    and EVD.EvnVizitDispDopOrp_setDate between date_trunc('year', p_BegDate) and p_EndDate
	and EVD.Diag_id not between 10880 and 11502
    and EVD.Diag_id not in (11720, 11721, 15398, 15399, 15400, 15401, 15402, 15519, 16930)
	and EVD.DopDispDiagType_id between 1 and 2
  inner join LpuSection_W as LS on LS.LpuSection_id = EVD.LpuSection_id and EVD.Lpu_id = LS.Lpu_id
  inner join dbo.Diag D on D.Diag_id = EVD.Diag_id
    and (D.Diag_Code between 'A00.0' and 'J99.9' or D.Diag_Code between 'K15' and 'T98.9' or D.Diag_Code between 'U07.1' and 'U07.2') -- #178812 исключаем все K00'-'K14'
  left join dbo.v_DopDispInfoConsent DDIC on DDIC.DopDispInfoConsent_id = EVD.DopDispInfoConsent_id
  left join dbo.v_SurveyTypeLink STL on STL.SurveyTypeLink_id = DDIC.SurveyTypeLink_id
  left join dbo.v_SurveyType ST on ST.SurveyType_id = STL.SurveyType_id
  where
    (lower(ST.SurveyType_Name) like '%осмотр%врач%' or EPLD.DispClass_id in (4,8,11,12)) -- select * from dbo.v_DispClass 
		and (coalesce(Addr1.KLAreaType_id,Addr2.KLAreaType_id) = p_IsSelo or p_IsSelo is null)
		and (PC.LpuRegion_id >= -1 or p_OnlyPersonCard_YN = 1)
		
	  and EPLD.EvnClass_id IN (7,8,9,101,103,104)
	  and EPLD.Evn_disDT between p_BegDate and p_EndDate + interval '1 day'
	  and EPLD.EvnPLBase_IsFinish = 2
	  and (coalesce(p_EndAge, 150) >= 18 and EPLD.DispClass_id in (1,2, 5) 
	    or coalesce(p_BegAge, 0) <= 18 and EPLD.DispClass_id in (6,9,10,11,12, 3,4,7,8))
	  and (EPLD.Evn_Deleted = 1 or EPLD.Evn_Deleted is null)  
),
EPL as
(
  select --distinct
    case when DT.DeseaseType_Code = 1 
	then EPL.Evn_id 
	else - PS.Person_id end as cntValue, -- домножение на -1 чтобы ид человека случайно не пересеклось с ид случая
      
    PS.Person_id,
    PS.Sex_id,

		D.Diag_Code,
		D.Diag_id,
    DT.DeseaseType_Code,
    LpuP.Lpu_id,
    LpuP.Lpu_pid,
    null::int8 as DispClass_id,
    left(DConcid.Diag_Code, 3) ::varchar as DConcid_Code
  from dbo.EvnPL EPL
	left join params on 1=1
	left join dbo.EvnVizit ev on ev.Evn_pid=epl.Evn_id
  inner join LpuSection_W as LS on LS.LpuSection_id = EPL.LpuSection_id	and EPL.Lpu_id = LS.Lpu_id
  left join dbo.Diag DConcid on DConcid.Diag_id = EPL.Diag_concid

  inner join dbo.Diag D on D.Diag_id = EPL.Diag_id
    and (D.Diag_Code between 'A00.0' and 'J99.9' or D.Diag_Code between 'K15' and 'T98.9' or D.Diag_Code between 'U07.1' and 'U07.2') -- #178812 исключаем все K00'-'K14'
  inner join dbo.v_DeseaseType DT on DT.DeseaseType_id = EPL.DeseaseType_id 
	inner join Lpu_W as LpuP on EPL.Lpu_id = LpuP.Lpu_id
  inner join v_PersonState as PS on PS.Person_id = EPL.Person_id
	  and (dbo.AgeYMD_pg(PS.Person_BirthDay,  cast(cast(year(p_enddate) || '.12.31' as varchar) as timestamp) , cast(p_Ymd_id as int4)) between coalesce(p_BegAge,0) and coalesce(p_EndAge,150))
  left join PersonCard_W as PC on PC.Person_id = PS.Person_id and (coalesce(PC.PersonCard_endDate, p_EndDate + interval '1 day') > p_EndDate or PC.CardCloseCause_id = 2)
	left join dbo.Address Addr1 on Addr1.Address_id = PS.PAddress_id
	left join dbo.Address Addr2 on Addr2.Address_id = PS.UAddress_id
	where (coalesce(Addr1.KLAreaType_id, Addr2.KLAreaType_id) = p_IsSelo or p_IsSelo is null)
		and (PC.LpuRegion_id >= -1 or p_OnlyPersonCard_YN = 1)
		and EPL.EvnClass_id IN (3,6) 
	  and EPL.Evn_disDT between p_BegDate and p_EndDate + interval '1 day'
	  and EPL.EvnPLBase_IsFinish = 2
	  and (EPL.Evn_deleted = 1 or EPL.Evn_deleted is null)
	and EPL.Diag_id not between 10880 and 11502
	and EPL.Diag_id not in (11720, 11721, 15398, 15399, 15400, 15401, 15402, 15519, 16930)
	and (EPL.MedPersonal_id=p_MedPersonal_id or p_MedPersonal_id is null)
	and (ev.PayType_id=p_PayType_id or p_PayType_id is null)
	
	union all
	
  select --distinct
    case when DT.DeseaseType_Code = 1 
	then EPL.Evn_id 
	else - PS.Person_id end as cntValue, -- домножение на -1 чтобы ид человека случайно не пересеклось с ид случая
      
    PS.Person_id,
    PS.Sex_id,

		D.Diag_Code,
		D.Diag_id,
    DT.DeseaseType_Code,
    LpuP.Lpu_id,
    LpuP.Lpu_pid,
    null::int8 as DispClass_id,
    left(DConcid.Diag_Code, 3) ::varchar as DConcid_Code
  from dbo.EvnPL EPL
	left join params on 1=1
  left join dbo.Diag DConcid on DConcid.Diag_id = EPL.Diag_concid

	left join lateral
	(
		select *
		from dbo.EvnVizitPL as EVPLLast
		where EVPLLast.Evn_pid = EPL.Evn_id
		and EVPLLast.Evn_Index = EVPLLast.Evn_Count - 1
		and (EVPLLast.Evn_deleted = 1 or EVPLLast.Evn_deleted is null)
		and (EVPLLast.PayType_id=p_PayType_id or p_PayType_id is null)
		limit 1
	)EVPLLast on true

	left join lateral
  (
	select --top 2
	  EDPLSop.Diag_id, EDPLSop.DeseaseType_id 
    from dbo.EvnDiagPLSop EDPLSop
    where 
      EDPLSop.Evn_pid = EVPLLast.Evn_id
	  and (EDPLSop.Evn_deleted = 1 or EDPLSop.Evn_deleted is null)
  ) Diags on true

  inner join dbo.Diag D on D.Diag_id = Diags.Diag_id
    and (D.Diag_Code between 'A00.0' and 'J99.9' or D.Diag_Code between 'K15' and 'T98.9' or D.Diag_Code between 'U07.1' and 'U07.2') -- #178812 исключаем все K00'-'K14'
  inner join dbo.v_DeseaseType DT on DT.DeseaseType_id = Diags.DeseaseType_id 
	inner join Lpu_W as LpuP on EPL.Lpu_id = LpuP.Lpu_id
  inner join v_PersonState as PS on PS.Person_id = EPL.Person_id
	  and (dbo.AgeYMD_pg(PS.Person_BirthDay,  cast(cast(year(p_enddate) || '.12.31' as varchar) as timestamp) , cast(p_Ymd_id as int4)) between coalesce(p_BegAge,0) and coalesce(p_EndAge,150))
  left join PersonCard_W as PC on PC.Person_id = PS.Person_id and (coalesce(PC.PersonCard_endDate, p_EndDate + interval '1 day') > p_EndDate or PC.CardCloseCause_id = 2)
	left join dbo.Address Addr1 on Addr1.Address_id = PS.PAddress_id
	left join dbo.Address Addr2 on Addr2.Address_id = PS.UAddress_id
  inner join LpuSection_W as LS on LS.LpuSection_id = EPL.LpuSection_id and EPL.Lpu_id = LS.Lpu_id
	where (coalesce(Addr1.KLAreaType_id, Addr2.KLAreaType_id) = p_IsSelo or p_IsSelo is null)
		and (PC.LpuRegion_id >= -1 or p_OnlyPersonCard_YN = 1)
		and EPL.EvnClass_id IN (3,6) 
	  and EPL.Evn_disDT between p_BegDate and p_EndDate + interval '1 day'
	  and EPL.EvnPLBase_IsFinish = 2
	  and (EPL.Evn_deleted = 1 or EPL.Evn_deleted is null)
	and EPL.Diag_id not between 10880 and 11502
	and EPL.Diag_id not in (11720, 11721, 15398, 15399, 15400, 15401, 15402, 15519, 16930)
	and (EPL.MedPersonal_id=p_MedPersonal_id or p_MedPersonal_id is null)
	
),

d346 as 
(
  select
    EPL_All.Diag_id,
	COUNT(distinct EPL_all.cntValue) as v3, --v4 из 3000-й
    COUNT(distinct case when (PDisp_Open > 0 or PDisp_Close > 0) then EPL_all.Person_id end) as v4, --v8
    COUNT(distinct case when coalesce(DeseaseType_Code, 1) in (1,2) then EPL_all.cntValue end) as v5, --v9
    COUNT(distinct case when coalesce(DeseaseType_Code, 1) in (1,2) and PDisp_Fir > 0 then EPL_all.Person_id end) as v6, --v10
    COUNT(distinct case when PDisp_Open > 0 then EPL_all.Person_id end) as v7 --v15

  from
  (
    select 
	  cntValue, Person_id, Sex_id, Diag_Code, Diag_id, DispClass_id, Lpu_id, Lpu_pid, DeseaseType_Code
	from EPL 

    union all

	select 
	  cntValue, Person_id, Sex_id, Diag_Code, Diag_id, DispClass_id, Lpu_id, Lpu_pid, DeseaseType_Code
	from EPLDisp 
  ) EPL_all 
	left join params on 1=1
  -- #131953 ищем карты Д-Учета
  left join lateral
  (
    select
	  COUNT(case when PersonDisp_begDate >= p_BegDate
	    and cast(coalesce(PersonDisp_DiagDate, PersonDisp_begDate) as date) between p_BegDate and p_EndDate
	    then PDisp.PersonDisp_id end) as PDisp_Fir,

	  COUNT(case when PersonDisp_endDate >= p_BegDate
	    and PersonDisp_endDate < DATEADD('DAY', 1, p_EndDate)
	    then PDisp.PersonDisp_id end) as PDisp_Close,

	  COUNT(case when PersonDisp_endDate >= DATEADD('DAY', 1, p_EndDate) 
	    or PersonDisp_endDate is null
	    then PDisp.PersonDisp_id end) as PDisp_Open
    
    from dbo.v_PersonDisp PDisp 
    where
	  EPL_all.Person_id = PDisp.Person_id 
	  and PDisp.Diag_id = EPL_All.Diag_id
	  and PDisp.Lpu_id = EPL_all.Lpu_pid
	  and PersonDisp_begDate < DATEADD('DAY', 1, p_EndDate)
	  and (PersonDisp_endDate >= DATEADD('DAY', 1, p_BegDate) or PersonDisp_endDate is null) 
  ) PDisp on true

  group by EPL_All.Diag_id
)

select 
  ROW_NUMBER()OVER(order by D1.Diag_Code, D2.Diag_Code, D3.Diag_Code, D4.Diag_Code) as rnum,
  D1.Diag_id as "DiagL1_id", 
  D1.Diag_Name as "DiagL1_Name", 
  D1.Diag_Code as "DiagL1_Code",
  D2.Diag_id as "DiagL2_id", 
  D2.Diag_Name as "DiagL2_Name", 
  D2.Diag_Code as "DiagL2_Code",
  D3.Diag_id as "DiagL3_id", 
  D3.Diag_Name as "DiagL3_Name", 
  D3.Diag_Code as "DiagL3_Code",
  D4.Diag_id as "DiagL4_id", 
  D4.Diag_Name as "DiagL4_Name", 
  D4.Diag_Code as "DiagL4_Code",
  coalesce(d346.v3, 0) as v3,
  coalesce(d346.v4, 0) as v4,
  coalesce(d346.v5, 0) as v5,
  coalesce(d346.v6, 0) as v6,
  coalesce(d346.v7, 0) as v7
from d346
left join params on 1=1
inner join dbo.v_Diag D4  on D4.Diag_id = d346.Diag_id
inner join dbo.v_Diag D3  on D3.Diag_id = D4.Diag_pid
inner join dbo.v_Diag D2  on D2.Diag_id = D3.Diag_pid
right join
(
  select 
    D1.Diag_id, D1.Diag_Code, D1.Diag_Name
  from dbo.v_Diag D1
  where 
    D1.DiagLevel_id = 1
) D1 on D1.Diag_id = d2.Diag_pid
inner join v_Diag D on D.Diag_id=d4.Diag_id
where d.Diag_Code between coalesce((select Diag_Code from v_Diag where Diag_id = p_Diagfrom_id), 'A00') and coalesce((select Diag_Code from v_Diag where Diag_id = p_diagto_id), 'U85.9')
