--set Session.Region to 7;
with _prm as (
select to_date($1,'dd.mm.yyyy') as paramBegDate,
       to_date($2,'dd.mm.yyyy') as paramEndDate,
       $3::bigint as paramLpu,
       $4::bigint as paramLpuBuilding, 
       $5::bigint as paramLpuUnit, 
       $6::bigint as paramLpuSection,
       $7::bigint as paramBillType,
       $8::bigint as paramKSDCKSG,
       $9::bigint as paramVarRep,
       coalesce($10::bigint,0) as paramByCase,
       $11::bigint as paramKlAreaVar,
       $12::bigint as paramResoTfoms,
       $13::bigint as paramPayTp,
       $14::bigint as paramMedStaffFact,
       $15::bigint as paramLpuSectionProfile)

, smo as (
select array_agg(s.orgsmo_id) as smo_agg
  from _prm 
  inner join v_orgsmo s on true
  inner join v_org o on o.org_id = s.org_id
where ((paramResoTfoms = 1 or paramResoTfoms is null) and o.org_nick ilike '%ООО "Капитал МС"%') or
      ((paramResoTfoms = 2 or paramResoTfoms is null) and o.org_nick ilike '%ООО "СМК РЕСО-МЕД"%') or
      ((paramResoTfoms = 3 or paramResoTfoms is null) and o.org_name = 'Территориальный фонд обязательного медицинского страхования Кабардино-Балкарской республики'))

, prm as (
select _prm.*,
       (select * from smo) as smo_agg
  from _prm)   
  
, tools as (
select distinct a.uslugacomplex_id
  from v_UslugaComplexAttributeType uat 
  inner join v_UslugaComplexAttribute a on a.uslugacomplexattributetype_id = uat.uslugacomplexattributetype_id
where uat.uslugacomplexattributetype_sysnick in ('us', 'dializ', 'kt', 'instr_diag', 'lab_diag', 'mrt'))  

, deps as (  
select s.lpusection_id,
       ut.lpuunittype_sysnick,
       case when ut.lpuunittype_sysnick = 'stac' then 1
            when ut.lpuunittype_sysnick in ('hstac','pstac','dstac') then 2
            else 3 end as care_type,
       lsp.lpusectionprofile_id as is_smp     
  from _prm 
  inner join v_lpuunit u on u.lpu_id = paramLpu
    and (paramLpuBuilding is null or u.lpubuilding_id = paramLpuBuilding)
    and (paramLpuUnit is null or u.lpuunit_id = paramLpuUnit)
  inner join v_lpuunittype ut on ut.lpuunittype_id = u.lpuunittype_id 
  inner join v_lpusection s on s.lpuunit_id = u.lpuunit_id
    and (paramLpuSection is null or s.lpusection_id = paramLpuSection)
  left join v_lpusectionprofile lsp on lsp.lpusectionprofile_id = s.lpusectionprofile_id --and lsp.lpusectionprofile_code = '160'
	where (paramLpuSectionProfile is null or lsp.lpusectionprofile_fedid = paramLpuSectionProfile)
)
, ps as (       
select ps.evnps_id as case_id,
       s.evnsection_id as step_id,
       s.evnsection_count as step_kolvo,
       ps.person_id,       
       max(case when coalesce(s.leavetype_id,ps.leavetype_id) is not null then 1 else 0 END)over(partition by ps.evnps_id)=1 as isfinish,
       coalesce(nullif((max(case when d.care_type = 1 then 1 else 0 END)over(partition by ps.evnps_id)=1)::int,0),2) as care_type,
       rpt.k_day_count_pg(d.care_type,ps.evnps_setdate, ps.evnps_disdate) as kd,
       smo_agg,
       paramResoTfoms,
       paramByCase,
       paramPayTp,
       ps.diag_id,
       paramBillType,
       s.htmedicalcareclass_id is not null and is_smp is not null as is_vmp,
	   concat(mes.mesold_num,' ',mes.mes_name)::text as mes_code,
		 cast(ps.evnps_setdt as date) as date
  from prm 
  inner join v_evnps ps on ps.lpu_id = paramLpu and ps.evnps_disdate between paramBegDate and paramEndDate
  inner join v_paytype pt on pt.paytype_id = ps.paytype_id and pt.paytype_sysnick = 'oms'
  inner join v_evnsection s on s.evnsection_pid = ps.evnps_id and s.Evnsection_Index = s.EvnSection_Count - 1
    and (paramMedStaffFact is null or paramMedStaffFact = s.medpersonal_id) 
  left join v_MesTariff mestariff on mestariff.MesTariff_id = s.MesTariff_id
  inner join v_MesOld mes on mes.mes_id = mestariff.mes_id
  inner join deps d on d.lpusection_id = coalesce(s.lpusection_id,ps.lpusection_id) and coalesce(s.leavetype_id,ps.leavetype_id) is not null
where (paramKSDCKSG is null or (paramKSDCKSG = 1 and d.care_type = 1 and mes.mesold_num ~ 'st') or  (paramKSDCKSG = 2 and d.care_type = 2 and mes.mesold_num ~ 'ds')) and 
      (coalesce(paramLpuBuilding, paramLpuUnit, paramLpuSection, paramLpuSectionProfile) is null or d.lpusection_id = coalesce(s.lpusection_id,ps.lpusection_id))) 

, t as (  
select case_id,step_id,step_kolvo,person_id,care_type,kd,smo_agg,paramResoTfoms,paramByCase,paramPayTp,diag_id,paramBillType,is_vmp,  mes_code, date from ps where isfinish::boolean)

, pat as (
select distinct t.person_id,
       l_a.klrgn_id       
  from t
  left join lateral (
       select paramKlAreaVar,              
              a.klrgn_id
         from _prm
         inner join v_personstate p on paramKlAreaVar is not null and p.person_id = t.person_id
         left join v_address a on a.address_id = coalesce(p.paddress_id,p.uaddress_id) limit 1) as l_a on true
where paramKlAreaVar is null or 
      (paramKlAreaVar = 1 and l_a.klrgn_id = dbo.GetRegion()) or 
      (paramKlAreaVar = 2 and l_a.klrgn_id != dbo.GetRegion()))       

, main  as (
select distinct       
       case_id,
       step_id,
       LpuSection.lpusection_name,
--       row_number() over(partition by case_id order by case_id, step_id) nn,
       step_kolvo,
       case when paramByCase = 0 then null else rd.numcard end as numcard,
       rd.RegistryData_ItogSum as sum,
       rd.registrydata_kdfact as kd,
       rd.paid_id,
		   t.mes_code,
		   case when dbo.AgeYMD(ps.person_birthday, t.date, 1) < 18 then 'Дети' else 'Взрослые' end as vozr,
			 coalesce(re.registry_id, rnp.registry_id, ret.registry_id) as regerror
  from t
  inner join pat on pat.person_id = t.person_id
  inner join r7.v_RegistryData rd on rd.evn_rid = case_id and rd.Evn_id = step_id and rd.person_id = t.person_id --and t.diag_id = rd.diag_id --and t.numcard = rd.numcard
    and (paramResoTfoms is null or rd.orgsmo_id = any (smo_agg))     
  left join r7.v_Registry r on r.registry_id = rd.registry_id
	left join r7.RegistryError re on re.evn_rid = case_id and re.Evn_id = step_id
	left join r7.registrynopolis rnp on rnp.registry_id = r.registry_id and rnp.evn_id = step_id
    and (paramBillType is null or paramBillType = Registry_IsRepeated)
	left join r7.registryerrortfoms ret on ret.registry_id = r.registry_id and ret.evn_id = step_id
  inner join LpuSection on LpuSection.LpuSection_id = rd.LpuSection_id
	left join v_personstate ps on ps.person_id=t.person_id
)

, t1_1 as (--Только стационар+
select lpusection_name,
	   mes_code,
			 m.vozr,
       sum(kd ) as g4,
       count(distinct case_id) as g5,
       string_agg(distinct numcard,',') as g5_1,
       sum(kd ) as g6,
       count(distinct case_id) as g7,
       string_agg(distinct numcard,',') as g7_1,
       sum(sum) as g8,
			 
       sum(case when regerror is null then kd end) as g9,
       count(distinct case when regerror is null then case_id end) as g10,
       string_agg(distinct case when regerror is null then numcard end,',') as g10_1,
       sum(case when regerror is null then sum end) as g11,
			 
       sum(case when (paid_id = 1 or regerror is not null) then kd end) as g12,
       count(distinct case when (paid_id = 1 or regerror is not null) then case_id end) as g13,
       string_agg(distinct case when (paid_id = 1 or regerror is not null) then numcard end,',') as g13_1,
       sum(case when (paid_id = 1 or regerror is not null) then sum end) as g14,
			 
       sum(case when paid_id = 2 then kd end) as g15,
       count(distinct case when paid_id = 2 then case_id end) as g16,
       string_agg(distinct case when paid_id = 2 then numcard end,',') as g16_1,
       sum(case when paid_id = 2 then sum end) as g17
from main m
group by 1,2,3)

, _t as (
select * from t1_1 --Стационар
)

select lpusection_name,
       mes_code,
       sum(g4) g4,
       sum(g5) g5,
       '('||string_agg(g5_1,', ')||')' as g5_1,
       sum(g6) g6,
       sum(g7) g7,
       '('||string_agg(g7_1,', ')||')' as g7_1,
       sum(g8) g8,
       sum(g9) g9,
       sum(g10) g10,
       '('||string_agg(g10_1,', ')||')' as g10_1,
       sum(g11) g11,
       sum(g12) g12,
       sum(g13) g13,
       '('||string_agg(g13_1,', ')||')' as g13_1,
       sum(g14) g14,
       sum(g15) g15,
       sum(g16) g16,
       '('||string_agg(g16_1,', ')||')' as g16_1,
       sum(g17) g17
  from _t 
group by 1,2
