with params as (
select cast (null as bigint) as Lpu_id,
      /* cast ('01.11.2021' as timestamp) as begdate,
       cast ('02.11.2021' as timestamp) as enddate     */      
     cast(CAST('2022-03-29 00:00:00' AS date) as timestamp) as begdate,
     cast(CAST('2022-03-31 23:59:59' AS date) as timestamp) as enddate    
),
Dir as 
(
  select 
     --cast (null as bigint) as Lpu_id,
    -- '01.11.2021'::date as begdate,
    -- '01.12.2021'::date as enddate,
     --cast(CAST('2022-03-29 00:00:00' AS date) as timestamp) as begdate,
     --cast(CAST('2022-03-31 23:59:59' AS date) as timestamp) as enddate
    L.Lpu_id,
	COUNT(distinct (ED.EvnDirection_id)) as cnt_all,
	COUNT(distinct(case when L.Lpu_id = coalesce(ED.Lpu_sid, ED.Lpu_id)
						then ED.EvnDirection_id end)) as cnt_toself
  from dbo.v_Lpu L
  inner join dbo.v_EvnDirection_all ED on L.Lpu_id = coalesce(ED.Lpu_did, ED.Lpu_id)
    and (--L.Lpu_id = Lpu_id or Lpu_id is null and 
    coalesce(L.Lpu_IsTest, 1) = 1)
    and ED.EvnDirection_setDate between (select begdate from params) and (select enddate from params)
    and coalesce(ED.EvnDirection_failDT, (select enddate from params)::date + INTERVAL '1 day') >= (select enddate from params)::date + INTERVAL '1 day'
  LEFT JOIN lateral (
   	 select 
	   COUNT(ELR.EvnLabRequest_id) as cntLabReq
	 from dbo.v_EvnLabRequest ELR
	 where ELR.EvnDirection_id = ED.EvnDirection_id) as ELR 
	 on true
  LEFT JOIN lateral(
    select 
	  COUNT(UT.UslugaTest_id) as cntTest
	from dbo.v_EvnUslugaPar EUP
	inner join dbo.v_UslugaTest UT 
	on UT.UslugaTest_pid = EUP.EvnUslugaPar_id
	where EUP.EvnDirection_id = ED.EvnDirection_id
	) EUP on true
	where ELR.cntLabReq > 0 or EUP.cntTest > 0
    group by L.Lpu_id), 
Lab as (
  select L.Lpu_id,
	COUNT(distinct EUP.EvnUslugaPar_id) as cntIssledov_All,
	COUNT(distinct analyzer.Analyzer_id) as cntOborudov,
	COUNT(distinct case when analyzer.Analyzer_id is not null then EUP.EvnUslugaPar_id end) as cntIssledov_Analyz,
	COUNT(distinct UT.UslugaTest_id) as cntTest_All,
	COUNT(distinct case when UT.UslugaTest_ResultApproved = 2 and coalesce(ELS.LabSampleStatus_id, -1) <> 5 -- select * from dbo.v_LabSampleStatus
	  then UT.UslugaTest_id end) as cntTest_Valid,
	COUNT(distinct case when ELS.LabSampleStatus_id = 5 -- select * from dbo.v_LabSampleStatus
	  then UT.UslugaTest_id end) as cntTest_Invalid
  from dbo.v_Lpu L
  inner join dbo.v_EvnUslugaPar EUP on EUP.Lpu_id = L.Lpu_id 
    and (coalesce(L.Lpu_IsTest, 1) = 1)
    and EUP.EvnUslugaPar_setDate  between (select begdate from params) and (select EndDate from params)
  inner join dbo.v_UslugaTest UT on UT.UslugaTest_pid = EUP.EvnUslugaPar_id
  left join dbo.v_EvnLabSample ELS on ELS.EvnLabSample_id = UT.EvnLabSample_id
  LEFT JOIN LATERAL
  ( 
    select 
	  a.Analyzer_id, a.Analyzer_Name, atrv.RefValues_id  
    from lis.v_Analyzer a 
    inner join lis.v_AnalyzerTest at on at.Analyzer_id = a.Analyzer_id 
    inner join lis.v_AnalyzerTestRefValues atrv on atrv.AnalyzerTest_id = at.AnalyzerTest_id 
    where atrv.RefValues_id = UT.RefValues_id) analyzer on true
  group by L.Lpu_id)
select 
  L.Lpu_id,
  L.Lpu_Name as "Юридическое лицо",
  (select begdate from params) ::text || ' - ' || (select enddate from params) ::text as "Период выгрузки",
  coalesce(Dir.cnt_all, 0) as "Всего направлений",
  coalesce(Dir.cnt_toself, 0) as Всего_созданных_в_лаб,
  cast(case when coalesce(Dir.cnt_all, 0) = 0 
  then 0.00
  else ROUND((1.00 * coalesce(Dir.cnt_toself, 0)) / coalesce(Dir.cnt_all, 0), 2) end as numeric(8,2)) as Доля_созданных_направлений,
  coalesce(Lab.cntIssledov_All, 0) as Всего_исследований, 
  coalesce(Lab.cntOborudov, 0) as Всего_используется_анализаторов,
  coalesce(Lab.cntIssledov_Analyz, 0) as Всего_исследований_на_анализат,
  coalesce(Lab.cntTest_All, 0) as Всего_показателей,
  coalesce(Lab.cntTest_Valid, 0) as Всего_валидированных_результатов,
  coalesce(Lab.cntTest_Invalid, 0) as Всего_выбракованных_исследований
from Dir
full join Lab on Lab.Lpu_id = Dir.Lpu_id
inner join dbo.v_Lpu L on L.Lpu_id = coalesce(Dir.Lpu_id, Lab.Lpu_id)
order by L.Lpu_Name
