with params as(
	select
		cast($1 as bigint) as p_lpu_id,
		cast($2 as bigint) as p_lpubuilding_id,
		cast($3 as bigint) as p_lpuunit_id,
		cast($4 as bigint) as p_lpusection_id,
		cast($5 as bigint) as p_medpersonal_id,
		cast($6 as timestamp) as p_begdate,
		cast($7 as timestamp) as p_enddate,
		cast($8 as bigint) as p_isorp,
		cast($9 as bigint) as p_isteen,
		cast($10 as bigint) as p_paytype_id		
)
select --MedPost_id=124 Врач-стоматолог-ортопед
count(distinct case when EU.v>0 and mp.MedPost_id=124 then Person.Person_id end) as v2701_1, 
count(distinct case when EU.v>0 and KLAreaType_id=2 and mp.MedPost_id=124 then Person.Person_id end) as v2701_2,
coalesce(sum(case when mp.MedPost_id=124 then coalesce(EU.v3,0) end),0) as v2701_3,

--MedPost_id=73 Врач-ортодонт
count(distinct case when Usluga.v3>0 and EU.v2>0 and mp.MedPost_id=73 then Person.Person_id end) as v2702_1,
count(distinct case when Usluga.v3>0 and EU.v2>0 and dbo.ageymd(Person.Person_BirthDay, EvnPL_disDate,1) <=14 and mp.MedPost_id=73 then Person.Person_id end) as v2702_2,
count(distinct case when Usluga.v3>0 and EU.v2>0 and dbo.ageymd(Person.Person_BirthDay, EvnPL_disDate,1) between 15 and 17 and mp.MedPost_id=73 then Person.Person_id end) as v2702_3,

--MedPost_id=197 Гигиенист стоматологический
count(distinct case when mp.MedPost_id=197 and Usluga_gig.v4>0 then Person.Person_id end) as v2704

From (
select
  Evn.Evn_id as EvnPLStom_id, 
  EvnVizit.Evn_id as EvnVizitPlStom_id,
  PS.Person_id,
  Evn.Server_id as Server_id,
  Evn.PersonEvn_id as PersonEvn_id,
  dbo.AgeYMD (PS.PersonBirthday_BirthDay, Evn.Evn_setDT, 1) as Person_Age,
  EvnVizit.Evn_setDT as EvnPL_SetDate,
  Evn.Evn_disDT as EvnPL_disDate,
  Evn.Diag_id,
  Diag.Diag_Code,
  Evn.DeseaseType_id,
  Evn.Lpu_id,
  1 as IsDopDisp,
  EvnVizit.MedPersonal_id,
  EvnVizit.MedStaffFact_id,
  LpuSection.LpuSection_id,
  EvnVizit.VizitType_id,
  null as dispclass_id,
  TreatmentClass_Code,
  EvnVizit.EvnVizitPLStom_IsPrimaryVizit,
  Evn.EvnPLStom_IsSan,
  Evn.SanationStatus_id,
  eu.uslugacomplex_id,
  PS.Uaddress_id as address_id
from EvnPLStom Evn 
left join params on 1=1
inner join (
  select
    Lpu.Lpu_id, Org.Org_Nick as Lpu_Nick, case when Lpu.Lpu_pid is not null and cast(Lpu.Lpu_endDate as date) between p_begdate and p_enddate then Lpu.Lpu_pid else Lpu.Lpu_id end as Lpu_pid
      from Lpu Lpu 
			left join params on 1=1
  inner join Lpu LpuP  on LpuP.Lpu_id = case when Lpu.Lpu_pid is not null and cast(Lpu.Lpu_endDate as date) between p_begdate and p_enddate then Lpu.Lpu_pid else Lpu.Lpu_id end
	inner join Org on Org.Org_id=LpuP.Org_id
  where
    (Lpu.Lpu_id = p_Lpu_id or (p_Lpu_id is null and Lpu.Lpu_id not IN (100,101,150308)))
	or (Lpu.Lpu_pid = p_Lpu_id
      and p_Lpu_id is not null
      and cast(Lpu.Lpu_endDate as date) between p_begdate and p_enddate
      and dbo.GetRegion() = 59)
)LpuP on LpuP.Lpu_id=Evn.Lpu_id
inner join EvnVizitPLStom EvnVizit on EvnVizit.Evn_pid = Evn.Evn_id
left join PersonState PS on PS.Person_id = Evn.Person_id
inner join v_EvnUsluga eu on eu.EvnUsluga_pid=EvnVizit.Evn_id
left join Diag  on Diag.Diag_id = Evn.Diag_id
left join LpuSection  on LpuSection.LpuSection_id = EvnVizit.LpuSection_id
left join LpuUnit  on LpuSection.LpuUnit_id = LpuUnit.LpuUnit_id
left  join v_TreatmentClass TC  on TC.TreatmentClass_id = EvnVizit.TreatmentClass_id
where cast(EvnVizit.Evn_setDT as date) between p_begDate and p_enddate
and Evn.Evn_deleted = 1
and (LpuP.Lpu_pid = p_Lpu_id or p_Lpu_id is null)
and (EvnVizit.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
and (EvnVizit.PayType_id =p_PayType_id or p_PayType_id is null)
and (LpuSection.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
and (LpuSection.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
and (LpuUnit.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null) 

union all

select 
  EvnPLDispOrp_id, 
  EvnVizitDispOrp_id,
  PS.Person_id,
  EvnVizit.Server_id,
  EvnVizit.PersonEvn_id,
  dbo.AgeYMD (PS.Person_BirthDay, EvnPLDispOrp_setdate, 1) as Person_Age,
  EvnPLDispOrp_setDate as EvnPL_SetDate,
  EvnPLDispOrp_disDate as EvnPL_disDate,
  EvnVizit.Diag_id,
  Diag.Diag_Code,
  EvnVizit.DopDispDiagType_id,
  EvnPLDispOrp.Lpu_id,
  1 as IsDopDisp,
  EvnVizit.MedPersonal_id,
  EvnVizit.MedStaffFact_id,
  LpuSection.LpuSection_id,
  null as VizitType_id ,
  dispclass_id as dispclass_id,
  null as TreatmentClass_Code,
  null as EvnVizitPLStom_IsPrimaryVizit,
  null as EvnPLStom_IsSan,
  null as SanationStatus_id,
  EvnVizit.uslugacomplex_id,
  PS.Uaddress_id as address_id 
from v_EvnPLDispOrp EvnPLDispOrp 
left join params on 1=1
inner join v_EvnVizitDispOrp EvnVizit  on EvnPLDispOrp.EvnPLDispOrp_id=EvnVizitDispOrp_pid 
	and OrpDispSpec_id=7
	and (EvnPLDispOrp.PayType_id =p_PayType_id or p_PayType_id is null)
inner join v_PersonState PS  on PS.Person_id = EvnPLDispOrp.Person_id 
inner join LpuSection  on LpuSection.LpuSection_id = EvnVizit.LpuSection_id 
  and (EvnVizit.Medpersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
  and EvnVizitDispOrp_SetDate between p_begDate and p_enddate
  and(EvnVizit.Lpu_id = p_Lpu_id or p_Lpu_id is null )
  and (EvnVizit.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
  and (LpuSection.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
  and 2 = p_IsOrp
inner join LpuUnit  on LpuSection.LpuUnit_id = LpuUnit.LpuUnit_id
  and (LpuUnit.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
inner join Diag  on Diag.Diag_id = EvnVizit.Diag_id

union all

select 
  EvnPLDispTeenInspection_id, 
  EvnVizitDispDop_id ,
  PS.Person_id,
  EvnVizit.Server_id,
  EvnVizit.PersonEvn_id,
  dbo.AgeYMD (PS.Person_BirthDay, EvnPLDispTeenInspection_setdate, 1) as Person_Age,
  EvnPLDispTeenInspection_setDate as EvnPL_SetDate,
  EvnPLDispTeenInspection_disDate as EvnPL_disDate,
  EvnVizit.Diag_id,
  Diag.Diag_Code,
  EvnVizit.DopDispDiagType_id,
  EvnPLDispTeen.Lpu_id,
  1 as IsDopDisp,
  EvnVizit.MedPersonal_id,
  EvnVizit.MedStaffFact_id,
  LpuSection.LpuSection_id,
  null as VizitType_id ,
  dispclass_id as dispclass_id,
  null as TreatmentClass_Code,
  null as EvnVizitPLStom_IsPrimaryVizit,
  null as EvnPLStom_IsSan,
  null as SanationStatus_id ,
   EvnVizit.uslugacomplex_id,
   PS.Uaddress_id as address_id 
from v_EvnPLDispTeenInspection EvnPLDispTeen 
left join params on 1=1
inner join v_EvnVizitDispDop EvnVizit  on EvnPLDispTeenInspection_id=EvnVizitDispDop_pid 
inner join DopDispInfoConsent DopDispInfoConsent  on DopDispInfoConsent.DopDispInfoConsent_id=EvnVizit.DopDispInfoConsent_id
	and coalesce(DopDispInfoConsent_deleted,1) = 1
	and DopDispInfoConsent.SurveyType_id = 35
	and (EvnPLDispTeen.PayType_id =p_PayType_id or p_PayType_id is null)
inner join v_PersonState PS  on PS.Person_id = EvnPLDispTeen.Person_id 
inner join LpuSection  on LpuSection.LpuSection_id = EvnVizit.LpuSection_id 
  and (EvnVizit.Medpersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
  and (EvnVizit.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
  and (LpuSection.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
  and 2 = p_IsTeen
inner join LpuUnit  on LpuSection.LpuUnit_id = LpuUnit.LpuUnit_id
  and (LpuUnit.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
inner join Diag  on Diag.Diag_id = EvnVizit.Diag_id
where  (EvnVizit.Lpu_id = p_Lpu_id or p_Lpu_id is null )
and EvnVizitDispDop_SetDate between p_begDate and p_enddate
)EvnVizit
inner join v_Person_all as Person on Person.PersonEvn_id = EvnVizit.PersonEvn_id
left join v_MedStaffFact MSF  on MSF.MedStaffFact_id=EvnVizit.MedStaffFact_id
inner join persis.Post p  on p.id=msf.post_id
inner join nsi.MedPost mp  on mp.Medpost_id=p.MedPost_id and mp.MedPost_id in (124,73,197)

left join lateral (
select
count( case when uslugacomplex_code in ('A16.07.005','A16.07.006','A16.07.023','A16.07.034','A16.07.035','A16.07.036','A16.07.056','A16.07.003','A16.07.004',
'A16.07.032','A16.07.033','A16.07.054') then EvnUslugaStom_id end ) as v,  --2701 строка 1
count( case when uslugacomplex_code in ('A16.07.005','A16.07.006','A16.07.023','A16.07.034','A16.07.035','A16.07.036','A16.07.056') then EvnUslugaStom_id end ) as v3,  --2701 строка 3
count( case when uslugacomplex_code in ('A16.07.018','A16.07.019','A16.07.021','A16.07.021.003','A16.07.021.004','A16.07.021.005','A16.07.028',
'A16.07.028.001','A16.07.028.002','A16.07.028.003','A16.07.028.005','A16.07.046','A16.07.047','A16.07.047.001','A16.07.047.002','A16.07.047.008',
'A16.07.047.012','A16.07.048','A16.07.049') then EvnUslugaStom_id end) as v2  --2702
From v_EvnUsluga EvnUsluga  
left join v_EvnUslugaStom EU  on EU.EvnUslugaStom_id = EvnUsluga.EvnUsluga_id  
inner join v_uslugacomplex UC   on   EU.uslugacomplex_id=UC.uslugacomplex_id
where EvnUsluga.EvnUsluga_rid=EvnPLStom_id
)EU on true

--B01.065.005, B01.065.006
left join lateral (
select
count( case when uslugacomplex_code in ('B01.063.001','B01.063.002','B04.063.001','B04.063.002') then uslugacomplex_id end) as v3
From  v_uslugacomplex UC   
where EvnVizit.uslugacomplex_id=UC.uslugacomplex_id
)Usluga on true
left join lateral (
select
count( case when uslugacomplex_code in ('B01.065.005','B01.065.006') then uslugacomplex_id end) as v4
From  v_uslugacomplex UC   
where EvnVizit.uslugacomplex_id=UC.uslugacomplex_id
)Usluga_gig on true
left join address   on Person.Uaddress_id=address.address_id
