/*
select * from rpt.f39u2aSV_PayType_pg
(
cast(? as bigint),
cast(? as varchar),
cast(? as bigint),
cast(? as bigint),
cast(? as bigint),
cast(? as bigint),
cast(? as bigint),
cast(? as bigint),
CAST(to_timestamp(?, 'DD.MM.YYYY') as timestamp),
CAST(to_timestamp(?, 'DD.MM.YYYY') as timestamp),
cast(? as bigint),
cast(? as bigint),
cast(? as bigint),
cast(? as bigint),
cast(? as bigint)
) order by 1, 2*/


with prm as (
select cast ($1 as bigint) as p_lpu_id,
cast ($2 as varchar) as p_lpufiliallist,
cast ($3 as bigint) as p_lpubuilding_id,
cast ($4 as bigint) as p_lpuunit_id,
cast ($5 as bigint) as p_lpusection_id,
cast ($6 as bigint) as p_medpersonal_id,
cast ($7 as int) as p_PostKind, 
cast ($8 as bigint) as p_treatmentclass_id,
cast ($9 as bigint) as p_paytype_id,
cast(to_timestamp($10, 'DD.MM.YYYY') as timestamp) as p_begdate,
cast(to_timestamp($11, 'DD.MM.YYYY') as timestamp) as p_enddate,
cast ($12 as bigint) as p_includedopdisp,
cast ($13 as bigint) as p_includeprofosm,
cast ($14 as bigint) as p_includedisporp,
cast ($15 as bigint) as p_includeteendisp,
cast ($16 as bigint) as p_ispaid,
dbo.GetRegion() as reg_num

)

,Lpu_W as (
  select 
    Lpu_id,
    p_lpufiliallist,
    p_lpubuilding_id,
    p_lpuunit_id,
    p_lpusection_id,
    p_medpersonal_id,
    p_PostKind,
    p_paytype_id,
    p_treatmentclass_id,
    p_begdate,
    p_enddate,
    p_includedopdisp,
    p_includeprofosm,
    p_includedisporp,
    p_includeteendisp,
    p_ispaid,
    reg_num
  from prm 
  join rpt.GetLpuList_pg(p_Lpu_id, p_BegDate, p_EndDate, 1, 2) L on true)

,  LpuSection_W as (
select
    LS.LpuSection_id,
	LS.LpuSection_Code,
	LS.LpuSection_Name,
	--RTRIM(LS.LpuSection_Code) + '. ' + RTRIM(LS.LpuSection_Name) AS LpuSection_FullName--,
	concat_ws('. ',
				LS.LpuSection_Code,
				LS.LpuSection_Name) as LpuSection_FullName
--	LS.LpuSectionType_id,
--  LS.LpuSectionProfile_Code,
--	LU.LpuUnitType_SysNick,
--	LU.LpuUnit_Name,
--	LU.LpuUnitProfile_fid
  from prm 
  join LpuSection LS on (LS.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
	LEFT JOIN LpuUnit LU  ON LU.LpuUnit_id = LS.LpuUnit_id 
	LEFT JOIN LpuBuilding LB  ON LU.LpuBuilding_id = LB.LpuBuilding_id
	LEFT JOIN Lpu_W L  ON L.Lpu_id = LB.Lpu_id
  where coalesce(LS.LpuSection_Deleted,1) = 1	
	and (LU.LpuUnit_id = prm.p_LpuUnit_id or prm.p_LpuUnit_id is null)
	and (LB.LpuBuilding_id = prm.p_LpuBuilding_id or prm.p_LpuBuilding_id is null)
	and (LB.LpuFilial_id in (select ID from rpt.GetIDs_FromStrList(prm.p_LpuFilialList)) or coalesce(rtrim(prm.p_LpuFilialList), '') = '')
	
	)
    
, 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
	--EU.UslugaComplex_id,
  from prm 
  join EvnVizitDisp on EvnVizitDisp.Evn_setDT between p_BegDate and dateadd('DAY', 1, p_EndDate)
    and (EvnVizitDisp.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
    and EvnVizitDisp.EvnClass_id in (14,15) -- 14 - ДВН, ПОВН, МОН; 15 - ДДС
	and EvnVizitDisp.Evn_deleted = 1	
--	and (EvnVizit.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
)

, 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
--	EvnPLBase.Person_Age
  from prm 
  join EvnPLDisp on EvnPLDisp.Evn_setDT < dateadd('DAY', 1, p_EndDate) and coalesce(EvnPLDisp.Evn_disDT, p_BegDate) >= p_BegDate
    and EvnPLDisp.EvnClass_id IN (7,8,9,101,103,104) -- 9 - ДДС, остальное - МОН, ПОВН, ДВН
	and (coalesce(EvnPLDisp.PayType_id,-2) = p_PayType_id or p_PayType_id is null)
	and(p_IsPaid=coalesce(EvnPLDisp.EvnPLDisp_IsPaid,1) or p_IsPaid is null)
    and EvnPLDisp.Evn_deleted = 1)

, EvnPL_W as (-- v_EvnPL
  select
    EvnPL.EvnClass_id, 
	EvnPL.Evn_id AS EvnPL_id, 
	CAST(CAST(EvnPL.Evn_setDT AS DATE) AS timestamp) AS EvnPL_setDate,
	CAST(CAST(EvnPL.Evn_disDT AS DATE) AS timestamp) AS EvnPL_disDate,
	EvnPL.Lpu_id,
	EvnPL.MedPersonal_id,
	EvnPL.LpuSection_id,
	EvnPL.Person_id,
	EvnPL.Diag_id,
	EvnPL.DeseaseType_id,
--	EvnPLBase.Person_Age,
	EvnPL.EvnPLBase_IsFinish,
	coalesce(EvnPL.EvnPL_IsPaid,1) EvnPL_IsPaid
  from prm 
  join EvnPL on coalesce(EvnPL.Evn_disDT, p_BegDate) between p_BegDate and dateadd('YEAR', 1, p_EndDate) 
    and EvnPL.EvnClass_id IN (3,6) -- 3 - Пол-ка, 6 - Стоматология
    and EvnPL.Evn_deleted = 1
	--and Evn.Evn_setDT between dateadd(YEAR, -1, p_BegDate) and dateadd(DAY, 1, p_EndDate) 
)

, EvnVizitPL_W as (-- v_EvnVizitPL
  select
    EvnVizitPL.Evn_id as EvnVizitPL_id, 
	EvnVizitPL.Evn_pid as EvnVizitPL_pid,
	cast(cast(EvnVizitPL.Evn_setDT as date) as timestamp) as EvnVizitPL_setDate,
	EvnVizitPL.Lpu_id,
	EvnVizitPL.Server_id,
	EvnVizitPL.PersonEvn_id,
	EvnVizitPL.Person_id,
	EvnVizitPL.MedStaffFact_id,
	EvnVizitPL.MedPersonal_id,
	EvnVizitPL.LpuSection_id,
	EvnVizitPL.Diag_id,
	EvnVizitPL.PayType_id,
	EvnVizitPL.TreatmentClass_id,
	EvnVizitPL.ServiceType_id,
	EvnVizitPL.UslugaComplex_id,
	EvnVizitPL.VizitClass_id,
	EvnVizitPL.VizitType_id,
	coalesce(EvnVizitPL.EvnVizit_IsPaid,1) as EvnVizit_IsPaid
  from prm 
  join EvnVizitPL on EvnVizitPL.Evn_setDT between p_BegDate and dateadd('DAY', 1, p_EndDate)
    and EvnVizitPL.EvnClass_id in (11,13,36,61)
	and EvnVizitPL.Evn_deleted = 1
	and (EvnVizitPL.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 (coalesce(EvnVizitPL.PayType_id,-2) = p_PayType_id or p_PayType_id is null)
	and (coalesce(EvnVizitPL.TreatmentClass_id,-2) = p_TreatmentClass_id or p_TreatmentClass_id is null) )

,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 prm 
  join EvnPS on EvnPS.Evn_setDT < dateadd('DAY', 1, p_EndDate)	and EvnPS.EvnPS_OutcomeDT between p_BegDate and dateadd('DAY', 1, p_EndDate) 
    and EvnPS.EvnClass_id = 30	
	and EvnPS.Evn_deleted = 1
	and (EvnPS.PrehospWaifRefuseCause_id > 0 or dbo.GetRegion() in (3,60)) -- для Бурятии и Пскова есть вариант не с отказом
    and (coalesce(EvnPS.PayType_id,-2) = p_PayType_id or p_PayType_id is null)
    and (EvnPS.MedPersonal_pid = p_MedPersonal_id or p_MedPersonal_id is null)
--    and (EvnPS.LpuSection_pid = p_LpuSection_id or p_LpuSection_id is null)
)

, 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 prm 
  join EvnSection on EvnSection.Evn_disDT between p_BegDate and dateadd('DAY', 1, p_EndDate) 
	where EvnSection.EvnClass_id = 32
	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 or p_LpuSection_id is null))

, All_Vizits as (
select 
  EVPL.EvnVizitPL_id as EvnVizit_id,
  EVPL.EvnVizitPL_setDate as EvnVizit_setDate,
  EVPL.Lpu_id,
--  EVPL.Person_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,
  case when dbo.GetRegion() in (59,19,3,91,66) then EPL.EvnPL_IsPaid else EVPL.EvnVizit_IsPaid end  IsPaid,
  reg_num 
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 and dateadd('YEAR', 1, p_EndDate)
inner join EvnVizitPL_W as EVPL  
  on EVPL.EvnVizitPL_pid = EPL.EvnPL_id
  and EVPL.EvnVizitPL_setDate between p_BegDate and p_EndDate
--  and (EVPL.PayType_id = p_PayType_id or p_PayType_id is null)
  and (EVPL.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
--  and (EVPL.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)

inner join LpuSection_W LS  on EVPL.LpuSection_id = LS.LpuSection_id
inner join v_MedStaffFact MSF  on MSF.MedStaffFact_id = EVPL.MedStaffFact_id
inner join persis.Post as Post  on MSF.Post_id = Post.id
--and MSF.PostKind_id = 1
left join v_ServiceType ST  on ST.ServiceType_id = EVPL.ServiceType_id
where (1=1)
 --AND EPL.EvnPL_IsPaiddbo.GetRegion() in (59,19,3,91,66)
 and(p_IsPaid=case when dbo.GetRegion() in (59,19,3,91,66) then EPL.EvnPL_IsPaid else EVPL.EvnVizit_IsPaid end 
	or p_IsPaid is null)
and p_PostKind = case when p_PostKind=2 and post.PostKind_id=6 then 2  else 1 end 

union
-- KVS отказы
select
  EPS.EvnPS_id as EvnVizit_id,
  EPS.EvnPS_OutcomeDate as EvnVizit_setDate,
  EPS.Lpu_id,
  --EPS.Person_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,
  case when dbo.GetRegion() =66 then EPS.EvnPS_IsPaid else ES.EvnSection_IsPaid end   IsPaid,
  reg_num 
from 
Lpu_W as L 
inner join EvnPS_W as EPS  on EPS.Lpu_id = L.Lpu_id
 -- and p_IncludeEvnPS = 2
  and EPS.EvnPS_OutcomeDate between p_BegDate and p_EndDate
--  and (EPS.PayType_id = p_PayType_id or p_PayType_id is null)
inner join LpuSection_W LS  on EPS.LpuSection_pid = LS.LpuSection_id
inner join v_MedStaffFact MSF  on MSF.MedStaffFact_id = EPS.MedStaffFact_pid
--  and MSF.PostKind_id = 1
inner join persis.Post as Post  on MSF.Post_id = Post.id
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) --Причина отказа от госпитализации
	and (2 = p_TreatmentClass_id or p_TreatmentClass_id is null) 

 --AND EPL.EvnPL_IsPaiddbo.GetRegion() in (59,19,3,91,66)
   and(p_IsPaid=case when dbo.GetRegion() =66 then EPS.EvnPS_IsPaid else ES.EvnSection_IsPaid end 
	or p_IsPaid is null)
	and p_PostKind = case when p_PostKind=2 and post.PostKind_id=6 then 2  else 1 end 

union
-- ДВН, ДДС, ПОВН, МОН
select 
  EVD.EvnVizitDispDop_id as EvnVizit_id,
  EVD.EvnVizitDispDop_setDate as EvnVizit_setDate,
  EVD.Lpu_id,
--  EPLD.Person_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 ,
  EPLD.EvnPLDisp_IsPaid,
  reg_num


from Lpu_W as L 
inner join EvnPLDisp_W as EPLD  on EPLD.Lpu_id = L.Lpu_id
  and EPLD.EvnPLDisp_setDate <= p_EndDate
  and coalesce(EPLD.EvnPLDisp_disDate, p_BegDate) >= p_BegDate
  and (p_IncludeDopDisp = 2 and EPLD.DispClass_id in (1,2) 
    or p_IncludeProfOsm = 2 and EPLD.DispClass_id = 5
	or p_IncludeTeenDisp = 2 and EPLD.DispClass_id in (6,9,10,11,12) 
	or p_IncludeDispOrp = 2 and EPLD.DispClass_id in (3,4,7,8)) 
--  and (EPLD.PayType_id = p_PayType_id or p_PayType_id is null)
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 and p_EndDate
  and EVD.EvnVizitDispDop_setDate >= coalesce(EPLD.EvnPLDisp_consDate, p_BegDate)
  --and (EVD.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
  and (EVD.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
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 as Post  on MSF.Post_id = Post.id
  --and p.PostKind_id = 1
where 1=1
	and (case when EPLD.DispClass_id in (1,2, 3,4,7,8) then 7 else 6 end = p_TreatmentClass_id or p_TreatmentClass_id is null)
	and p_PostKind = case when p_PostKind=2 and post.PostKind_id=6 then 2  else 1 end 

)


, Vizit as (
select
    -- Отделение
 --   coalesce(EvnVizit.LpuSection_FullName, 'ИТОГО') AS LPuSection_FullName, 
  --  EvnVizit.LpuSection_id,
    -- ФИО врача
  --  case when (GROUPING(EvnVizit.Person_FIO) = 1 and GROUPING(EvnVizit.LpuSection_FullName) <> 1) 
  --    THEN 'ИТОГО' ELSE EvnVizit.Person_FIO END AS Person_FIO,
  --  EvnVizit.MedPersonal_id,  
    EvnVizit.LpuSection_FullName, 
    EvnVizit.Person_FIO, 
    
    -- Число посещений в поликлинике
    -- всего
	coalesce(count(case when (EvnVizit.ServiceType_SysNick not in ('home','ahome','neotl')) 
	  then EvnVizit_id end), 0) as val3,  --EvnVizitPL_id на EvnVizit_id #25327
	-- из них сельских жителей
	coalesce(count(case when (EvnVizit.ServiceType_SysNick not in ('home','ahome','neotl')) and 
	  (PersonAddress.KlareaType_id = 2) then EvnVizit_id end),0) as val4,
	  
	-- в том числе в возрасте (из графы 3)
	-- 0-17 лет  
	coalesce(count(case when (EvnVizit.ServiceType_SysNick not in ('home','ahome','neotl')) and 
	  (dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) between 0 and 17) 
	  then EvnVizit_id end),0) as val5,

	coalesce(count(case when (EvnVizit.ServiceType_SysNick not in ('home','ahome','neotl')) and 
	  (dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) between 18 and 59) 
	  then EvnVizit_id end),0) as val6,

	-- 60 лет и старше   
	coalesce(count(case when (EvnVizit.ServiceType_SysNick not in ('home','ahome','neotl')) and 
	  (dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) >= 60) 
	  then EvnVizit_id end),0) as val7,	
	  
	-- из общего числа посещений по поводу заболеваний 
	-- Всего 
	coalesce(count(case when(EvnVizit.ServiceType_SysNick not in ('home','ahome','neotl')) and 
	  (EvnVizit.TreatmentClass_id between 1 and 4) then EvnVizit_id end),0) as val8,
	-- 0-17 лет   
    coalesce(count(case when (EvnVizit.ServiceType_SysNick not in ('home','ahome','neotl')) and 
      (dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) between 0 and 17) and 
      (EvnVizit.TreatmentClass_id between 1 and 4) then EvnVizit_id end),0) as val9,

	  coalesce(count(case when (EvnVizit.ServiceType_SysNick not in ('home','ahome','neotl')) and 
      (dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) between 18 and 59) and 
      (EvnVizit.TreatmentClass_id between 1 and 4) then EvnVizit_id end),0) as val10,

    -- 60 лет и старше  
    coalesce(count(case when (EvnVizit.ServiceType_SysNick not in ('home','ahome','neotl')) and 
      (dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) >= 60) and 
      (EvnVizit.TreatmentClass_id between 1 and 4) then EvnVizit_id end),0) as val11,    
     
    -- профилактических 
    -- Всего  
	coalesce(count (case when (EvnVizit.ServiceType_SysNick not in ('home','ahome','neotl')) and 
	  (EvnVizit.TreatmentClass_id is null or EvnVizit.TreatmentClass_id between 5 and 12)
	  then EvnVizit_id end),0) as val12,
  
	-- Число посеще-ний на дому  
	-- Всего
	coalesce(count(case when (EvnVizit.ServiceType_SysNick in ('home','ahome','neotl')) 
	  then EvnVizit_id end),0) as val13,
	
	-- из общего числа посещений на дому
	-- по поводу заболеваний
	-- Всего
	coalesce(count(case when (EvnVizit.ServiceType_SysNick in ('home','ahome','neotl')) and 
	  (EvnVizit.TreatmentClass_id between 1 and 4) then EvnVizit_id end),0) as val14,
	  
	-- в возрасте
	-- 0-17 лет  
	coalesce(count(case when (EvnVizit.ServiceType_SysNick in ('home','ahome','neotl')) and 
	  (EvnVizit.TreatmentClass_id between 1 and 4) and 
	  (dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) between 0 and 17) 
	  then EvnVizit_id end),0) as val15,


	-- из них 0-1 год (вкл)  
	coalesce(count(case when (EvnVizit.ServiceType_SysNick in ('home','ahome','neotl')) and 
	  (EvnVizit.TreatmentClass_id between 1 and 4) and 
	  (dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) between 0 and 1) 
	  then EvnVizit_id end),0) as val16,

		coalesce(count(case when (EvnVizit.ServiceType_SysNick in ('home','ahome','neotl')) and 
	  (EvnVizit.TreatmentClass_id between 1 and 4) and 
	  (dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) between 18 and 59) 
	  then EvnVizit_id end),0) as val17,
    -- 60 лет и старше  
	coalesce(count(case when (EvnVizit.ServiceType_SysNick in ('home','ahome','neotl')) and 
	  (EvnVizit.TreatmentClass_id between 1 and 4) and 
	  (dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) >= 60) 
	  then EvnVizit_id end),0) as val18,	  
	  
	-- из числа профилактических  
	-- 0-17 лет 
	coalesce(count(case when (EvnVizit.ServiceType_SysNick in ('home','ahome','neotl')) and 
	  (EvnVizit.TreatmentClass_id is null or EvnVizit.TreatmentClass_id between 5 and 12) and 
	  (dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) between 0 and 17) 
	  then EvnVizit_id end),0) as val19,
	-- из них 0-1 год (вкл)   
	coalesce(count(case when (EvnVizit.ServiceType_SysNick in ('home','ahome','neotl')) and 
	  (EvnVizit.TreatmentClass_id is null or EvnVizit.TreatmentClass_id between 5 and 12) and 
	  (dbo.AgeYMD(Person.Person_BirthDay, EvnVizit_setDate, 1) between 0 and 1) 
	  then EvnVizit_id end),0) as val20,
	  
	-- Виды оплаты посещений
	-- ОМС  
  coalesce(count (case when reg_num = 45 and PayType.PayType_SysNick in ('oms','vzaimoms') then EvnVizit_id
                       when reg_num!= 45 and PayType.PayType_SysNick = 'oms' then EvnVizit_id end),0) as val21,
	-- бюджет
	-- местный
  coalesce(count (case when reg_num = 45 and PayType.PayType_SysNick in ('municipal','regional') then EvnVizit_id
                       when reg_num!= 45 and PayType.PayType_SysNick in ('bud','subrf') then EvnVizit_id end),0) as val22,
	-- федеральный
  coalesce(count (case when reg_num = 45 and PayType.PayType_SysNick = 'federal' then EvnVizit_id
                       when reg_num!= 45 and PayType.PayType_SysNick = 'fbud' then EvnVizit_id end),0) as val23,
  --PROMEDWEB-4531
  --МБТ (незастрахованные) только  Пермь 
  coalesce(count (case when reg_num = 45 and PayType.PayType_SysNick = '999' then EvnVizit_id
                       when reg_num!= 45 and PayType.PayType_SysNick = 'mbudtrans' then EvnVizit_id end),0) as mbt,
  --МБТ (СЗЗ)  только  Пермь
  coalesce(count (case when reg_num = 45 and PayType.PayType_SysNick = '999' then EvnVizit_id
                       when reg_num!= 45 and PayType.PayType_SysNick = 'mbudtrans_mbud' then EvnVizit_id end),0) as ssz,
	-- платные
  coalesce(count (case when reg_num = 45 and PayType.PayType_SysNick = 'citizen' then EvnVizit_id
                       when reg_num!= 45 and PayType.PayType_SysNick = 'money' then EvnVizit_id end),0) as val24,
	-- ДМС
  coalesce(count (case when reg_num = 45 and PayType.PayType_SysNick in ('dms','dms_mvd','dms_ufsin','dms_rosgvardiya','dms_fssp') then EvnVizit_id
                       when reg_num!= 45 and PayType.PayType_SysNick = 'dms' then EvnVizit_id end),0) as val25,
    --МВД
  coalesce(count (case when reg_num = 45 and PayType.PayType_SysNick = '999' then EvnVizit_id
                       when reg_num!= 45 and PayType.PayType_SysNick in ('ovd', 'osobkatgr') then EvnVizit_id end),0) as val26,
	-- Другое 
  coalesce(count (case when reg_num = 45 and PayType.PayType_SysNick in ('contract','free','voeb','vzaimdd','employer') then EvnVizit_id
                       when reg_num!= 45 and PayType.PayType_id is null or PayType.PayType_SysNick not in ('oms','bud','fbud','mbudtrans','mbudtrans_mbud','subrf','money','dms','ovd','osobkatgr') then EvnVizit_id end),0) as val27

  from All_Vizits EvnVizit 
    
 -- inner join v_LpuSection LpuSection  on LpuSection.LpuSection_id = EvnVizit.LpuSection_id 
--  inner join v_medPersonal MedPersonal  on EvnVizit.MedPersonal_id = MedPersonal.MedPersonal_id
--   and EvnVizit.Lpu_id = MedPersonal.Lpu_id-- and MedPersonal.Lpu_id = p_Lpu_id
--  inner join v_MedStaffFact MSF  on MSF.MedStaffFact_id = EvnVizit.MedStaffFact_id
   
--  inner join v_PersonState Person  on EvnVizit.Person_id = Person.Person_id 
--  inner join v_Person_reg Person  on EvnVizit.PersonEvn_id = Person.PersonEvn_id
--     and Person.Server_id = EvnVizit.Server_id   

  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
  --inner join ServiceType  on ServiceType.ServiceType_id = EvnVizit.ServiceType_id
  --inner join VizitType  on VizitType.VizitType_id = EvnVizit.OurVizitType_id
  --left join v_TreatmentClass tc  on tc.TreatmentClass_id = EvnVizit.TreatmentClass_id
  left join PayType  on PayType.PayType_id = EvnVizit.PayType_id
                  
  --Адрес пациента выбирается в приорете фактический, если не заполнен, то по прописке
--  left outer join v_Address PersonAddress_P  on PersonAddress_P.Address_id = Person.PAddress_id
--  left outer join v_Address PersonAddress_U  on PersonAddress_U.Address_id = Person.UAddress_id
  left outer join v_Address PersonAddress  on PersonAddress.Address_id = coalesce(Person.PAddress_id,Person.UAddress_id)
  where 1=1
  --and (PayType.PayType_id = p_PayType_id or p_PayType_id is null)
  group by 
--    EvnVizit.LpuSection_id, 
--    EvnVizit.MedPersonal_id
    EvnVizit.LpuSection_FullName, 
    EvnVizit.Person_FIO 
)

select 
--coalesce(LPuSection_FullName,'ИТОГО') as LPuSection_FullName,
--case when LPuSection_FullName is null then ' ' else coalesce(Person_FIO,'ИТОГО')
-- end as Person_FIO, 
  -- LS.LpuSection_Code +'. '+ LS.LpuSection_Name as LPuSection_FullName,
  --msf.Person_Fio as Person_FIO,
  ROW_NUMBER() over (order by  Vizit.LpuSection_FullName,Vizit.Person_Fio)::integer as row_num,
  Vizit.LpuSection_FullName::text as "LPuSection_FullName",
  Vizit.Person_Fio::text as "Person_FIO",
  coalesce(Vizit.val3, 0)::bigint as val3,
  coalesce(Vizit.val4, 0)::bigint as val4,
  coalesce(Vizit.val5, 0)::bigint as val5,
  coalesce(Vizit.val6, 0)::bigint as val6,
  coalesce(Vizit.val7, 0)::bigint as val7,
  coalesce(Vizit.val8, 0)::bigint as val8,
  coalesce(Vizit.val9, 0)::bigint as val9,
  coalesce(Vizit.val10, 0)::bigint as val10,
  coalesce(Vizit.val11, 0)::bigint as val11,
  coalesce(Vizit.val12, 0)::bigint as val12,
  coalesce(Vizit.val13, 0)::bigint as val13,
  coalesce(Vizit.val14, 0)::bigint as val14,
  coalesce(Vizit.val15, 0)::bigint as val15,
  coalesce(Vizit.val16, 0)::bigint as val16,
  coalesce(Vizit.val17, 0)::bigint as val17,
  coalesce(Vizit.val18, 0)::bigint as val18,
  coalesce(Vizit.val19, 0)::bigint as val19,
  coalesce(Vizit.mbt, 0)::bigint as mbt, --PROMEDWEB-4531 
  coalesce(Vizit.ssz, 0)::bigint as ssz,
  coalesce(Vizit.val20, 0)::bigint as val20,
  coalesce(Vizit.val21, 0)::bigint as val21,
  coalesce(Vizit.val22, 0)::bigint as val22,
  coalesce(Vizit.val23, 0)::bigint as val23,
  coalesce(Vizit.val24, 0)::bigint as val24,
  coalesce(Vizit.val25, 0)::bigint as val25,
  coalesce(Vizit.val26, 0)::bigint as val26,
  coalesce(Vizit.val27, 0)::bigint as val27
  from Vizit 

order by 1, 2

