with Lpu_W as
(
  select 
    L.Lpu_id,
    L.Lpu_Nick
  from v_Lpu L 
  where
    (L.Lpu_id = /*p_Lpu_id*/cast($1 as bigint)
      or /*p_Lpu_id*/cast($2 as bigint) is null and coalesce(L.Lpu_IsTest, 1) = 1
      or L.Lpu_pid = /*p_Lpu_id*/cast($3 as bigint) and /*p_Lpu_id*/cast($4 as bigint)> 0)
    and L.Lpu_begDate <= /*p_EndDate*/'30.11.2021'::timestamp + INTERVAL '1 year'
    and coalesce(L.Lpu_endDate,/*p_BegDate*/'01.12.2020'::timestamp) >= /*p_BegDate*/'01.12.2020'::timestamp
),


LpuSection_W as
(
  select 
    L.Lpu_id,
    L.Lpu_Nick,
    LS.LpuSection_id,
    LS.LpuSection_FullName,
    case when upper(coalesce(LB.LpuBuilding_Name, '')) ilike ('ЦЕНТР ЗДОРОВЬЯ%') 
      or upper(coalesce(LU.LpuUnit_Name, '')) ilike ('ЦЕНТР ЗДОРОВЬЯ%')
      then 2 else 1 end as Is_HealthCenter,
    LU.LpuUnitType_SysNick
  from Lpu_W as L 
  inner join v_LpuSection LS  on LS.Lpu_id = L.Lpu_id
    and (LS.LpuBuilding_id = /*p_LpuBuilding_id*/cast($5 as bigint) or /*p_LpuBuilding_id*/cast($6 as bigint) is null)
    and (LS.LpuUnit_id = /*p_LpuUnit_id*/cast($7 as bigint) or /*p_LpuUnit_id*/cast($8 as bigint)  is null)
    and (LS.LpuSection_id = /*p_LpuSection_id*/cast($9 as bigint) or /*p_LpuSection_id*/cast($10 as bigint)  is null)
  inner join v_LpuUnit LU  on LU.LpuUnit_id = LS.LpuUnit_id
  inner join v_LpuBuilding LB  on LB.LpuBuilding_id = LU.LpuBuilding_id
  
  union all

  select 
    L.Lpu_id,
    L.Lpu_Nick,
    -1 as LpuSection_id,
    'Без отделения' as LpuSection_FullName,
    1 as Is_HealthCenter,
    '' as LpuUnitType_SysNick 
  from Lpu_W as L 
),



EvnVizitDispDopOrp_W as -- v_EvnVizitDispDop и v_EvnVizitDispOrp
(
  select
    EvnVizitDisp.EvnClass_id,
    EvnVizitDisp.Evn_id as EvnVizitDispDop_id, 
	EvnVizitDisp.Evn_pid as EvnVizitDispDop_pid,
	cast(cast(EvnVizitDisp.Evn_setDT as date) as timestamp) as EvnVizitDispDop_setDate,
	EvnVizitDisp.Lpu_id,
	EvnVizitDisp.Server_id,
	EvnVizitDisp.PersonEvn_id,
	EvnVizitDisp.MedStaffFact_id,
	EvnVizitDisp.MedPersonal_id,
	EvnVizitDisp.LpuSection_id,
	EvnVizitDisp.DopDispInfoConsent_id,
	EvnVizitDisp.Evn_id as EvnVizitDisp_id
  from EvnVizitDisp
  where 1=1
    and EvnVizitDisp.EvnClass_id in (14,15) -- 14 - ДВН, ПОВН, МОН; 15 - ДДС
	and EvnVizitDisp.Evn_setDT between /*p_BegDate*/'01.12.2020'::timestamp and dateadd('DAY', 1, /*p_EndDate*/'30.11.2021'::timestamp)
	and EvnVizitDisp.Evn_deleted = 1



),



EvnPLDisp_W as -- v_EvnPLDisp
(
  select
    EvnPLDisp.EvnClass_id, 
    EvnPLDisp.Evn_id AS EvnPLDisp_id, 
	CAST(CAST(EvnPLDisp.Evn_setDT AS DATE) AS timestamp) AS EvnPLDisp_setDate,
    CAST(CAST(EvnPLDisp.EvnPLDisp_consDT AS DATE) AS timestamp) AS EvnPLDisp_consDate,
	CAST(CAST(EvnPLDisp.Evn_disDT AS DATE) AS timestamp) AS EvnPLDisp_disDate,
	EvnPLDisp.Lpu_id,
	EvnPLDisp.DispClass_id,
	EvnPLDisp.Person_id,
    EvnPLDisp.PayType_id,
	EvnPLDisp.EvnPLBase_IsFinish,
	coalesce(EvnPLDisp.EvnPLDisp_IsPaid,1)  EvnPLDisp_IsPaid
  from EvnPLDisp
  where 1=1
    and EvnPLDisp.EvnClass_id IN (7,8,9,101,103,104) -- 9 - ДДС, остальное - МОН, ПОВН, ДВН
	and EvnPLDisp.Evn_setDT < dateadd('DAY', 1, /*p_EndDate*/'30.11.2021'::timestamp) 
	and coalesce(EvnPLDisp.Evn_disDT, /*p_BegDate*/'01.12.2020'::timestamp) >= /*p_BegDate*/'01.12.2020'::timestamp
	and EvnPLDisp.Evn_deleted = 1

),

EvnPL_W as -- v_EvnPL
(
  select
    Evn.EvnClass_id, 
    Evn.Evn_id AS EvnPL_id, 
    CAST(CAST(Evn.Evn_setDT AS DATE) AS timestamp) AS EvnPL_setDate,
    CAST(CAST(Evn.Evn_disDT AS DATE) AS timestamp) AS EvnPL_disDate,
    Evn.Lpu_id,
    EvnPL.MedPersonal_id,
    EvnPL.LpuSection_id,
    Evn.Person_id,
    EvnPL.Diag_id,
    EvnPL.DeseaseType_id,
    EvnPLBase.Person_Age,
    EvnPLBase.EvnPLBase_IsFinish as EvnPL_IsFinish,
    EvnPL.EvnPL_IsPaid   
  from Evn 
  inner join EvnPLBase  ON Evn.Evn_id = EvnPLBase.Evn_id
    and Evn.EvnClass_id IN (3,6) -- 3 - Пол-ка, 6 - Стоматология
    and Evn.Evn_disDT between /*p_BegDate*/'01.12.2020'::timestamp and /*p_EndDate*/'30.11.2021'::timestamp + INTERVAL '1 year'
    and Evn.Evn_deleted = 1
  inner join EvnPL  ON EvnPLBase.Evn_id = EvnPL.Evn_id
),




EvnVizitPL_W as -- v_EvnVizitPL
(
  select
    Evn.Evn_id as EvnVizitPL_id, 
    Evn.Evn_pid as EvnVizitPL_pid,
    cast(cast(Evn.Evn_setDT as date) as timestamp) as EvnVizitPL_setDate,
    Evn.Lpu_id,
    Evn.Evn_Index as EvnVizitPL_Index,
    Evn.Evn_Count as EvnVizitPL_Count,
    Evn.Server_id,
    Evn.PersonEvn_id,
    Evn.Person_id,
    EvnVizit.MedStaffFact_id,
    EvnVizit.MedPersonal_id,
    EvnVizit.LpuSection_id,
    EvnVizit.EvnVizit_IsPaid as EvnVizitPL_IsPaid,
    EvnVizitPL.Diag_id,
    EvnVizit.PayType_id,
    EvnVizitPL.TreatmentClass_id,
    EvnVizitPL.ServiceType_id,
    EvnVizitPL.UslugaComplex_id,
    EvnVizitPL.VizitClass_id,
    coalesce(EvnVizitPL.EvnVizit_IsPaid,1) as EvnVizit_IsPaid
  from Evn 
  inner join EvnVizit  on Evn.Evn_id = EvnVizit.Evn_id
    and Evn.EvnClass_id in (11,13,36,61)
    and Evn.Evn_setDT between /*p_BegDate*/'01.12.2020'::timestamp  and /*p_EndDate*/'30.11.2021'::timestamp + INTERVAL '1 day'
    and Evn.Evn_deleted = 1
  inner join EvnVizitPL  on EvnVizit.Evn_id = EvnVizitPL.Evn_id
),


EvnPS_W as
(
  select
    EvnPS.Evn_id as EvnPS_id, 
    cast(cast(EvnPS.Evn_setDT as date) as timestamp) as EvnPS_setDate,
	cast(cast(EvnPS.Evn_disDT as date) as timestamp) as EvnPS_disDate,
	EvnPS.Lpu_id,
	EvnPS.Person_id,
    EvnPS.Server_id,
	EvnPS.PersonEvn_id,
	EvnPS.Person_Age,
	EvnPS.LpuSection_pid,
	EvnPS.MedPersonal_pid,
	EvnPS.MedStaffFact_pid,
	EvnPS.PayType_id,
	cast(cast(EvnPS.EvnPS_OutcomeDT as date) as timestamp) as EvnPS_OutcomeDate,
	EvnPS.Diag_pid,
	EvnPS.PrehospWaifRefuseCause_id,
	coalesce(EvnPS.EvnPS_IsPaid,1) as EvnPS_IsPaid
  from EvnPS 
  where EvnPS.EvnClass_id = 30
	and EvnPS.Evn_setDT < /*p_EndDate*/'30.11.2021'::timestamp + INTERVAL '1 day'
	and EvnPS.EvnPS_OutcomeDT between /*p_BegDate*/'01.12.2020'::timestamp  and /*p_EndDate*/'30.11.2021'::timestamp + INTERVAL '1 day'
	and EvnPS.Evn_deleted = 1
	and (EvnPS.PrehospWaifRefuseCause_id > 0 or dbo.GetRegion() in (3,60)) -- для Бурятии и Пскова есть вариант не с отказом
   

),

--Для бурятии и пскова (отказ)
EvnSection_W as
(
  select
    EvnSection.Evn_id as EvnSection_id,
	EvnSection.Evn_Index as EvnSection_Index,
	EvnSection.Evn_Count as EvnSection_Count,
	cast(cast(EvnSection.Evn_setDT as date) as timestamp) as EvnSection_setDate,
	cast(cast(EvnSection.Evn_disDT as date) as timestamp) as EvnSection_disDate,
	EvnSection.Evn_setDT as EvnSection_setDT,
	EvnSection.Evn_disDT as EvnSection_disDT,
	EvnSection.Lpu_id,
	EvnSection.Evn_pid as EvnSection_pid,
	EvnSection.PayType_id,
	EvnSection.LeaveType_id,
	EvnSection.LeaveType_fedid,
	EvnSection.LeaveType_prmid,
	EvnSection.Diag_id,
	EvnSection.MedPersonal_id,
	EvnSection.MedStaffFact_id,
	EvnSection.LpuSection_id,
	EvnSection.LpuSectionProfile_id,
	EvnSection.EvnSection_IsPriem,
	EvnSection.HTMedicalCareClass_id,
	EvnSection.Mes_id,
	EvnSection.EvnSection_IndexNum,
	EvnSection.MesTariff_id,
	EvnSection.MedicalCareBudgType_id,
	EvnSection.UslugaComplex_id,
	EvnSection.EvnSection_IsPaid as EvnSection_IsPaid

  from  EvnSection  
	where EvnSection.EvnClass_id = 32
	and EvnSection.Evn_disDT between /*p_BegDate*/'01.12.2020'::timestamp  and /*p_EndDate*/'30.11.2021'::timestamp + INTERVAL '1 day'
	and EvnSection.Evn_deleted = 1
	and EvnSection.EvnSection_IsPriem = 2
	and EvnSection.LeaveType_prmid in (358,463) -- для Бурятии "Осмотрен в приемном отделении", для Пскова  'Неотложная помощь в приемном отделении'
	and dbo.GetRegion() in (3,60) 
	and (EvnSection.LpuSection_id = /*p_LpuSection_id*/cast($11 as bigint) or /*p_LpuSection_id*/cast($12 as bigint) is null)

),



All_Vizits as(
select 
  EVPL.EvnVizitPL_id as EvnVizit_id,
  EVPL.EvnVizitPL_setDate as EvnVizit_setDate,
  EVPL.Lpu_id,
  EVPL.Server_id,
  EVPL.PersonEvn_id,
  EVPL.LpuSection_id,
  LS.LpuSection_FullName,
  EVPL.MedPersonal_id,
  EVPL.MedStaffFact_id,
  MSF.Person_Fio,
  EVPL.PayType_id,
  EVPL.ServiceType_id,
  coalesce(ST.ServiceType_SysNick,'nodef') as ServiceType_SysNick,
  EVPL.TreatmentClass_id, 
  'TAP' as EvnType,
  eu.EvnUsluga_id
from Lpu_W as L 
inner join EvnPL_W as EPL  on EPL.Lpu_id = L.Lpu_id   and EPL.EvnPL_disDate between /*p_BegDate*/'01.12.2020'::timestamp  and /*p_EndDate*/'30.11.2021'::timestamp + INTERVAL '1 day'
inner join EvnVizitPL_W as EVPL   on EVPL.EvnVizitPL_pid = EPL.EvnPL_id   and EVPL.EvnVizitPL_setDate between /*p_BegDate*/'01.12.2020'::timestamp  and /*p_EndDate*/'30.11.2021'::timestamp + INTERVAL '1 day'

inner join LpuSection_W LS  on EVPL.LpuSection_id = LS.LpuSection_id
inner join v_MedStaffFact MSF  on MSF.MedStaffFact_id = EVPL.MedStaffFact_id
left join v_ServiceType ST  on ST.ServiceType_id = EVPL.ServiceType_id

  left join lateral
  (
    select
      EU.EvnUsluga_id
    from v_EvnUsluga EU 
    inner join v_UslugaComplex UC  on UC.UslugaComplex_id = EU.UslugaComplex_id and UC.UslugaComplex_Code in ('A16.01.004', 'A16.01.005')
    where EU.EvnUsluga_pid = EVPL.EvnVizitPL_id and EU.Lpu_id = EPL.Lpu_id
        limit 1
  ) EU on true
where (1=1)


union
-- KVS отказы
select
  EPS.EvnPS_id as EvnVizit_id,
  EPS.EvnPS_OutcomeDate as EvnVizit_setDate,
  EPS.Lpu_id,
  EPS.Server_id,
  EPS.PersonEvn_id,
  EPS.LpuSection_pid as LpuSection_id,
  LS.LpuSection_FullName,
  EPS.MedPersonal_pid as MedPersonal_id,
  EPS.MedStaffFact_pid as MedStaffFact_id,
  MSF.Person_Fio,
  EPS.PayType_id,
  1 as ServiceType_id,
  'polka' as ServiceType_SysNick,
  2 as TreatmentClass_id, 
  'KVS' as EvnType,
  null as EvnUsluga_id
from 
Lpu_W as L 
inner join EvnPS_W as EPS  on EPS.Lpu_id = L.Lpu_id   and EPS.EvnPS_OutcomeDate between /*p_BegDate*/'01.12.2020'::timestamp  and /*p_EndDate*/'30.11.2021'::timestamp + INTERVAL '1 day'
inner join LpuSection_W LS  on EPS.LpuSection_pid = LS.LpuSection_id
inner join v_MedStaffFact MSF  on MSF.MedStaffFact_id = EPS.MedStaffFact_pid
left join EvnSection_W as ES  on ES.EvnSection_pid = EPS.EvnPS_id
where 1 = 1 and (ES.LeaveType_prmid in (358,463) or EPS.PrehospWaifRefuseCause_id > 0) --Причина отказа от госпитализации



union
-- ДВН, ДДС, ПОВН, МОН
select 
  EVD.EvnVizitDispDop_id as EvnVizit_id,
  EVD.EvnVizitDispDop_setDate as EvnVizit_setDate,
  EVD.Lpu_id,
  EVD.Server_id,
  EVD.PersonEvn_id,

  EVD.LpuSection_id,
  LS.LpuSection_FullName,
  EVD.MedPersonal_id,
  EVD.MedStaffFact_id,
  MSF.Person_Fio,
  EPLD.PayType_id,
  1 as ServiceType_id,
  'polka' as ServiceType_SysNick,
  case when EPLD.DispClass_id in (1,2, 3,4,7,8) then 7 else 6 end as TreatmentClass_id,
  case when EPLD.DispClass_id = 5 then 'POVN'
       when EPLD.DispClass_id in (1,2) then 'DVN'
	   when EPLD.DispClass_id in (6,9,10,11,12) then 'MON'
	   when EPLD.DispClass_id in (3,4,7,8) then 'DDS'
  end as EvnType ,
  null as EvnUsluga_id

from Lpu_W as L 
inner join EvnPLDisp_W as EPLD  on EPLD.Lpu_id = L.Lpu_id
  and EPLD.EvnPLDisp_setDate <= '30.11.2021'::timestamp
  and coalesce(EPLD.EvnPLDisp_disDate, '01.12.2020'::timestamp) >= '01.12.2020'::timestamp
  and (/*p_IncludeDopDisp*/cast ($13 as int) = 2 and EPLD.DispClass_id in (1,2) 
    or /*p_IncludeProfOsm*/cast ($14 as int) = 2 and EPLD.DispClass_id = 5
	or /*p_IncludeTeenDisp*/cast ($15 as int) = 2 and EPLD.DispClass_id in (6,9,10,11,12) 
	or /*p_IncludeDispOrp*/cast ($16 as int) = 2 and EPLD.DispClass_id in (3,4,7,8)) 
inner join EvnVizitDispDopOrp_W as EVD  on EPLD.EvnPLDisp_id = EVD.EvnVizitDispDop_pid 
  and EVD.Lpu_id = L.Lpu_id              
  and EVD.EvnVizitDispDop_setDate between /*p_BegDate*/'01.12.2020'::timestamp  and /*p_EndDate*/'30.11.2021'::timestamp + INTERVAL '1 day'
  and EVD.EvnVizitDispDop_setDate >= coalesce(EPLD.EvnPLDisp_consDate, '01.12.2020'::timestamp)
inner join LpuSection_W as LS  on EVD.LpuSection_id = LS.LpuSection_id
left join v_DopDispInfoConsent DDIC  on DDIC.DopDispInfoConsent_id = EVD.DopDispInfoConsent_id
left join v_SurveyTypeLink STL  on STL.SurveyTypeLink_id = DDIC.SurveyTypeLink_id
left join v_SurveyType ST  on ST.SurveyType_id = STL.SurveyType_id
inner join v_MedStaffFact MSF  on MSF.MedStaffFact_id = EVD.MedStaffFact_id
  and (coalesce(ST.SurveyType_Name, '') ilike '%осмотр%' or EPLD.DispClass_id in (4,8,11,12)) -- select * from v_DispClass
inner join persis.Post p  on p.id = MSF.Post_id

where 1=1

),


Vizit as (
  select PayType.paytype_name,
    
    -- Число посещений в поликлинике
    -- всего
	coalesce(count(EvnVizit_id), 0) as val1,  --EvnVizitPL_id на EvnVizit_id #25327
	-- из них сельских жителей
	coalesce(count(case when PersonAddress.KlareaType_id = 2 then EvnVizit_id end),0) as val2,
	  
	-- в том числе в возрасте (из графы 3)
	-- 0-17 лет  
	coalesce(count(case when  dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1)<=17  then EvnVizit_id end),0) as val3,



	-- 60 лет и старше   
	coalesce(count(case when dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) >= 60  then EvnVizit_id end),0) as val4,	
	  
  
	-- Число посеще-ний на дому  
	-- Всего
	coalesce(count(case when (EvnVizit.ServiceType_SysNick in ('home','ahome','neotl')) 
	  then EvnVizit_id end),0) as val5,
	
	  
	  coalesce(count(case when (EvnVizit.ServiceType_SysNick in ('home','ahome','neotl'))  and 
	  (dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) <= 17) 
	  then EvnVizit_id end),0) as val6,
	  
	   coalesce(count(case when (EvnVizit.ServiceType_SysNick in ('home','ahome','neotl'))  and 
	  (dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) >= 60) 
	  then EvnVizit_id end),0) as val7,
	  
	  coalesce(count(EvnVizit.EvnUsluga_id),0) as val8,
	  
	  coalesce(count(case when dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) <= 17 then  EvnVizit.EvnUsluga_id end),0) as val9,
	  
	  coalesce(count(case when dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) >= 60 then  EvnVizit.EvnUsluga_id end),0) as val10

  from All_Vizits EvnVizit  

  inner join lateral (select 
    ps.Person_BirthDay
	,ps.sex_id
	,ps.PAddress_id
	,ps.UAddress_id
  from v_Person_reg PS  
  where EvnVizit.PersonEvn_id = PS.PersonEvn_id
     and PS.Server_id = EvnVizit.Server_id
     limit 1
  ) Person on true
  left outer join v_Address PersonAddress  on PersonAddress.Address_id = coalesce(Person.PAddress_id,Person.UAddress_id)
  left join PayType  on PayType.PayType_id = EvnVizit.PayType_id
                  
  where 1=1   and PayType.paytype_name in (
'ОМС',
'ДМС',
'Средства граждан',
'По договору с организацией',
'Бюджет федеральный',
'Бюджет региональный',
'Безвозмездно',
'Военкоматы',
'Взаимозачеты ОМС',
'Взаимозачеты ДД',
'Бюджет муниципальный',
'За счет средств работодателя',
'ДМС (МВД)',
'ДМС (УФСИН)',
'ДМС (Росгвардия)',
'ДМС (ФССП)'
  )
 group by PayType.paytype_name 
 order by PayType.paytype_name 

)

select 
  coalesce(Vizit.paytype_name,'') as finance, 
  coalesce(Vizit.val1, 0) as val1,
  coalesce(Vizit.val2, 0) as val2,
  coalesce(Vizit.val3, 0) as val3,
  coalesce(Vizit.val4, 0) as val4,
  coalesce(Vizit.val5, 0) as val5,
  coalesce(Vizit.val6, 0) as val6,
  coalesce(Vizit.val7, 0) as val7,
  coalesce(Vizit.val8, 0) as val8,
  coalesce(Vizit.val9, 0) as val9,
  coalesce(Vizit.val10, 0) as val10
  
  from Vizit 
  
  
 
