--f30r6_5401_2021_pg.rptdesign
with param as (select
  $1::int8 as	p_Lpu_id,
  $2::text as	p_LpuFilialList,
	$3::int8 as	p_LpuBuilding_id,
	$4::int8 as	p_LpuUnit_id,
	$5::int8 as	p_LpuSection_id,
	$6::int8 as	p_MedPersonal_id,
	$7::int8 as	p_PayType_id,
	$8::int8 as	p_IsGUZ,
	to_timestamp($9,'dd.mm.yyyy')::timestamp as p_BegDate,
	to_timestamp($10,'dd.mm.yyyy')::timestamp as p_EndDate
),  
 tLpu as (select l.lpu_id 
   from param inner join v_lpu l on true
   inner join v_Org o on o.Org_id=l.Org_id
   left join v_Okfs okfs on okfs.Okfs_id=o.Okfs_id
   where 
     l.Lpu_id = p_Lpu_id or
	 (p_Lpu_id is null 
	   and (coalesce(p_IsGUZ, 1) = 2 and okfs.Okfs_Code in (11, 13, 14) or coalesce(p_IsGUZ, 1) = 1)
	   and coalesce(l.Lpu_istest, 1) = 1
	 )   
 ),
UC_Row as (select 
    rt.RepTableRows_id,
	rt.RepTableRows_Code,
    UC.UslugaComplex_id,
	UC_G.UslugaComplex_Code
	from rpt.v_RepTableRows rt
	inner join rpt.v_RepTableLinks rtl on rtl.RepTableRows_id=rt.RepTableRows_id
    inner join v_UslugaComplex UC_G on rtl.RepTableLinks_UslCode=UC_G.UslugaComplex_Code
    inner join v_UslugaComplex UC on UC.UslugaComplex_2011id = UC_G.UslugaComplex_id 
	where rt.RepTableRows_SysNick='f30r6_5401_5402_2021'
),

eu as (
select
	EU.EvnUsluga_id,
	UC_G.RepTableRows_id,
	UC_G.RepTableRows_Code,
	EU.EvnUsluga_setDT,
	EU.LpuSection_uid,
	LSUslTo.lpusection_disdate,
	LSUslTo.lpusection_setdate
from param inner join v_EvnUsluga EU on true
inner join UC_Row UC_G on EU.UslugaComplex_id = UC_G.UslugaComplex_id
left join v_LpuSection LSUslTo on EU.LpuSection_uid = LSUslTo.LpuSection_id
left join v_LpuUnit LUUslTo on LSUslTo.LpuUnit_id = LUUslTo.LpuUnit_id
left join v_LpuSection LSUslFrom on LSUslFrom.LpuSection_id = EU.LpuSection_uid 
left join v_LpuUnit LUUslFrom on LSUslFrom.LpuUnit_id = LUUslFrom.LpuUnit_id
left join v_EvnDirection_all Directions on EU.EvnDirection_id = Directions.EvnDirection_id
left join v_LpuSection LSDirFrom on LSDirFrom.LpuSection_id = Directions.LpuSection_id  
left join v_LpuUnit LUDirFrom on LSDirFrom.LpuUnit_id = LUDirFrom.LpuUnit_id
inner join v_LpuBuilding LB on LB.LpuBuilding_id = LSUslTo.LpuBuilding_id
    and (LB.LpuFilial_id = any(('{'||p_LpuFilialList||'}')::int8[])
      or coalesce(rtrim(p_LpuFilialList), '') = '')

left join lateral (select
     sEU.EvnUsluga_id
  from v_EvnUsluga sEU
   inner join v_UslugaComplex sUC on sEU.UslugaComplex_id = sUC.UslugaComplex_id 
   inner join v_UslugaComplex sUC_G on sUC.UslugaComplex_2011id = sUC_G.UslugaComplex_id 
  where sEU.EvnUsluga_rid = EU.EvnUsluga_rid
    and sUC_G.UslugaComplex_Code in ('A05.10.006', 'A05.10.006.001', 'A05.10.010', 'A05.10.011', 'A05.10.007.001', 'A05.10.007.002', 'A05.10.012')
  	and UC_G.UslugaComplex_Code in ('A05.10.004', 'A05.10.004.001')
  limit 1
) oEU on true
where 1=1
		and (EU.Lpu_id in (select l.Lpu_id from tLpu l))
		and EU.EvnUsluga_setDT between (select p_BegDate from param) and (select p_EndDate + interval '1 day' - interval '1 minute' from param)
		and (eu.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
		and (eu.PayType_id=p_PayType_id or p_PayType_id is null)
and (LSUslTo.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
and (LUUslTo.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
and (LUUslTo.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
and oEU.EvnUsluga_id is null --Отсекаем лишние ЭКГ
),

d_group as 
(
select eu.RepTableRows_id,
	count(distinct case when eu.RepTableRows_code='3'
	   and attr58.AttributeValue_ValueIdent=2 
	   and (eu.lpusection_disdate>cast(EvnUsluga_setDT as date) or eu.lpusection_disdate is null) 
	   and eu.lpusection_setdate<=cast(EvnUsluga_setDT as date) then EU.EvnUsluga_id
	                    when eu.RepTableRows_code<>'3' then EU.EvnUsluga_id end) v3
from eu
	left join lateral
	(
	select atr.AttributeValue_ValueIdent,
					ASV.AttributeSignValue_id,
					ASV.AttributeSignValue_TablePKey			
				from
					v_AttributeSignValue ASV
					inner join v_AttributeSign vAS on vAS.AttributeSign_id = ASV.AttributeSign_id
					left join lateral
					(
					select AttributeValue_ValueIdent,A.Attribute_id
					from
					v_AttributeValue AV
					inner join v_Attribute A on A.Attribute_id = AV.Attribute_id --and A.Attribute_id=344
					where AV.AttributeSignValue_id=ASV.AttributeSignValue_id --and AttributeValue_ValueIdent in (11,13)
					) atr on true
					where
					vAS.AttributeSign_TableName like 'dbo.LpuSection'
					and ASV.AttributeSignValue_TablePKey=eu.LpuSection_uid
					and vAS.AttributeSign_Code='58'
					and atr.AttributeValue_ValueIdent=2
    limit 1 
	) attr58 on true
group by eu.RepTableRows_id
)

select UKind.RepTableRows_row as "RepTableRows_row",
  UKind.RepTableRows_code as v2, 
UKind.RepTableRows_Text1 as v1,
coalesce(v3, 0) as v3
from rpt.RepTableRows UKind 
left join d_group d on d.RepTableRows_id = UKind.RepTableRows_id
  where UKind.RepTableRows_SysNick='f30r6_5401_5402_2021'
order by 1



