with param as (select 
$1::text as p_lpulist, 
$2::text as p_lpufilialslist, 
cast($3 as int8) as p_lpubuilding_id, 
cast($4 as int8) as p_lpuunit_id, 
cast($5 as int8) as p_lpusection_id, 
cast($6 as int8) as p_lpusectionprofile_id, 
cast(to_timestamp($7,'dd.mm.yyyy') as timestamp) as p_begdate, 
cast(to_timestamp($8,'dd.mm.yyyy') as timestamp) as p_enddate, 
cast($9 as int8) as p_order413, 
cast($10 as int8) as p_isguzonly
  ),
LpuP as(
	select
		L.Lpu_id,
		dateadd('hour',
			case when dbo.GetRegion() in (58,50) then 8 else 9 end,
			dateadd('day', (1 - coalesce(p_Order413, 2)), p_begdate)) as Stac_begDT,
		dateadd('hour', 
			case when dbo.GetRegion() in (58,50) then 8 else 9 end,
			dateadd('day', (2 - coalesce(p_Order413, 2)), p_enddate)) as Stac_endDT,
		cast(case when dbo.GetRegion() in (58,50) then '08:00' else '09:00' end as time) as StacTime
	from param 
   inner join rpt.getlpu_successionl_pg(p_LpuList,
		dateadd('day', case when coalesce(p_Order413, 2) = 2 then -1 else 0 end, p_begdate), 
		dateadd('day', case when coalesce(p_Order413, 2) = 1 then 1 else 0 end, p_enddate)
	) as L on true
		inner join Org as O on L.Org_id = O.Org_id
			and case
					when dbo.GetRegion() = 59 and L.Lpu_id in (13002556,10011362) or O.Okfs_id in (2,4,5)
						then 2 else 1 end >=  coalesce(p_IsGUZOnly,1)
),

LpuSection_W as(
	select 
		LS.LpuSection_id
	from param 
   inner join LpuP on true
		inner join v_LpuSection LS on LS.Lpu_id = LpuP.Lpu_id
		inner join v_LpuUnit LU on LU.LpuUnit_id = LS.LpuUnit_id
		inner join v_LpuBuilding LB on LB.LpuBuilding_id = LU.LpuBuilding_id 
		left join v_LpuSectionProfile LSProf on LS.LpuSectionProfile_id = LSProf.LpuSectionProfile_id
	where (LS.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
		and (LS.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
		and (LS.LpuBuilding_id = p_lpubuilding_id or p_lpubuilding_id is null)
		and LS.LpuSectionProfile_id != 75
		and LU.LpuUnitType_SysNick = 'stac'
		and (LB.LpuFilial_id in(
			select id::int8 as id from unnest(string_to_array(p_lpufilialslist, ',')) as id)
			or coalesce(p_lpufilialslist,'') = '')
),

UK as(
  select distinct
            r.RepTableRows_Code    
           ,rl.RepTableLinks_UslCode
           ,rl.RepTableLinks_FromDiag
           ,rl.RepTableLinks_ToDiag 
  from rpt.v_reptablerows r
  inner join rpt.v_reptablelinks rl on r.RepTableRows_id = rl.RepTableRows_id
  where r.RepTableRows_SysNick = 'frm14_4000_2020'
      and (strpos(r.RepTableRows_Code,'.') = 0 
	  or r.RepTableRows_Code in ('2.4','7','8.1', '2.7','2.8','15.1','15.4','15.5','15.6','15.8'))
),

EPS as(
	select
		EPS.EvnPS_setDate,
		dbo.AgeYMD(PS.Person_BirthDay, EPS.EvnPS_setDate, 1) as Person_Age,
		EUO.Person_id,
		PS.Sex_id,
		UC.UslugaComplex_id,
		UC.UslugaComplex_Code,
		D.Diag_Code,
		D.Diag_id,
		lsp.LpuSectionProfile_Name
	from param 
   inner join LpuP on true
		inner join v_EvnPS as EPS on EPS.Lpu_id = LpuP.Lpu_id
			and EPS.PrehospWaifRefuseCause_id is null
		inner join v_EvnSection as ES on ES.EvnSection_pid = EPS.EvnPS_id
			and ES.EvnSection_Index = ES.EvnSection_count - 1
    left join v_LpuSectionProfile lsp on lsp.LpuSectionProfile_id=es.LpuSectionProfile_id
		inner join LpuSection_W as LS on LS.LpuSection_id = ES.LpuSection_id
		inner join v_EvnDirection_all as ED on ED.EvnDirection_pid = ES.EvnSection_id
		inner join v_LeaveType as LT on LT.LeaveType_id = ES.LeaveType_id
		inner join v_Person_all as PS on PS.PersonEvn_id = EPS.PersonEvn_id
			and PS.Server_id = EPS.Server_id
		inner join v_EvnUslugaOper as EUO on EUO.EvnUslugaOper_rid = EPS.EvnPS_id
			and EUO.Lpu_id = LpuP.Lpu_id 
		inner join v_Diag as D on D.Diag_id = ES.Diag_id
		inner join v_UslugaComplex as UC on EUO.UslugaComplex_id = UC.UslugaComplex_id
	where 1=1
		and PS.Person_BirthDay < dateadd('day', 2, p_enddate)
		and coalesce(PS.Person_deadDT, PS.Person_closeDT, p_enddate) >=  dateadd('day', -1, p_begdate)
		and EPS.EvnPS_disDT >=  LpuP.Stac_begDT 
		and EPS.EvnPS_disDT < LpuP.Stac_endDT
		and ES.EvnSection_disDT >=  LpuP.Stac_begDT 
		and ES.EvnSection_disDT < LpuP.Stac_endDT
		and ED.DirType_id = 4 -- 4. На восстановительное лечение
		and LT.LeaveType_SysNick not in ('die','psumer','ksdie','dsdie','kslet','ksletitar') 
    and (ES.LpuSectionProfile_id=p_lpusectionprofile_id or p_lpusectionprofile_id is null)
)

select 
  COUNT(distinct EPS.Person_id) as v1, -- всего оперировано
  
  COUNT(case when UK.RepTableRows_Code = '9'
    and EPS.Diag_Code between 'K25' and 'K26.9'
    then EPS.Person_id end) as v2, -- язвенная болезнь желудка
    
  COUNT(case when EPS.UslugaComplex_Code in ('A16.14.009','A16.14.009.001','A16.14.009.002','A16.14.009.003')
    then EPS.Person_id end) as v3, -- удаленя желчного пузыря
    
  COUNT(case when UK.RepTableRows_Code in ('2.4','7','8.1')
    then EPS.Person_id end) as v4, -- операции на сердце
    
  COUNT(case when UK.RepTableRows_Code = '9'
    and left(EPS.Diag_Code, 3) = 'K85'
    then EPS.Person_id end) as v5, -- панкреатит
    
  COUNT(case when UK.RepTableRows_Code in ('2.7','2.8','15.1','15.4','15.5','15.6','15.8')
    or EPS.UslugaComplex_Code in ('A16.03.037','A16.03.038','A16.03.039','A16.03.040','A16.03.041','A16.03.042')
    then EPS.Person_id end) as v6 -- операций ортопедических, травматолог. при дефектах и попроках развития

from EPS 
inner join UK on UK.RepTableLinks_UslCode = EPS.UslugaComplex_Code
  and (EPS.Diag_Code between UK.RepTableLinks_FromDiag and UK.RepTableLinks_ToDiag or UK.RepTableLinks_FromDiag is null)
  and (dbo.GetRegion() = 2 or (UK.RepTableRows_Code not in ('2','2.2','2.2.1','2.2.1.1','2.4','2.4.1','2.4.1.1','2.4.1.2','2.4.2','2.4.2.1',
      '2.4.2.2','2.7','2.8',
      '8','8.1','8.1.1','8.1.1.1','8.1.1.2','8.1.1.3','8.1.1.3.1','15','15.5','15.8',
	  '7.2','7.3') --#106438

    or EPS.UslugaComplex_Code not in ('A16.12.008.001','A16.12.008.002','A16.12.008.003','A16.12.028.007',
      'A16.12.041.004','A16.03.035.001','A16.03.051.003','A16.04.008','A16.04.008.001','A16.04.010','A16.04.010.001',
      'A16.04.029','A16.04.032','A16.04.032.001','A16.04.040','A16.04.041','A16.04.043','A16.12.008.004',
      'A16.12.011.001','A16.12.011.005','A16.12.011.006','A16.12.011.009','A16.12.026.005','A16.12.026.006',
      'A16.12.026.007','A16.12.028.007','A16.12.038.008','A16.12.038.009','A16.12.041.004','A16.12.048',
      'A16.12.054.001','A22.04.005','A22.04.007','A16.23.085','A16.23.085.001',
	  'A16.12.026.015','A16.12.026.017','A16.12.026.020','A16.12.026.021', --#106438
	  'A16.10.003.028', 'A16.10.003.029','A16.10.003.030','A16.10.003.031','A16.10.003.032','A16.10.035.001','A16.10.037','A16.10.038','A16.12.042.001','A16.12.042.002') --#106438
	  --A16.12.026.021
    or (EPS.UslugaComplex_Code in ('A16.12.008.001','A16.12.008.002','A16.12.008.003','A16.12.028.007',
      'A16.12.041.004','A16.03.035.001','A16.03.051.003','A16.04.008','A16.04.008.001','A16.04.010','A16.04.010.001',
      'A16.04.029','A16.04.032','A16.04.032.001','A16.04.040','A16.04.041','A16.04.043','A16.12.008.004',
      'A16.12.011.001','A16.12.011.005','A16.12.011.006','A16.12.011.009','A16.12.026.005','A16.12.026.006',
      'A16.12.026.007','A16.12.028.007','A16.12.038.008','A16.12.038.009','A16.12.041.004','A16.12.048',
      'A16.12.054.001','A22.04.005','A22.04.007','A16.23.085','A16.23.085.001',
	  'A16.12.026.015','A16.12.026.017','A16.12.026.020','A16.12.026.021', --#106438
	  'A16.10.003.028', 'A16.10.003.029','A16.10.003.030','A16.10.003.031','A16.10.003.032','A16.10.035.001','A16.10.037','A16.10.038','A16.12.042.001','A16.12.042.002')--#106438
      and (
		(UK.RepTableRows_Code in ('2','2.2','2.2.1','2.2.1.1','2.4','2.4.1','2.4.1.1','2.4.1.2','2.4.2','2.4.2.1','2.4.2.2','2.7','2.8')  
			and coalesce(EPS.LpuSectionProfile_Name, '') like '%нейрохир%')
        or (UK.RepTableRows_Code in ('8','8.1','8.1.1','8.1.1.1','8.1.1.2','8.1.1.3','8.1.1.3.1','15','15.5','15.8')
			and coalesce(EPS.LpuSectionProfile_Name, '') not like '%нейрохир%')
		  ))
		)) 

