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,
       $16::bigint as paramTarifCalc)

, 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'
	inner join fed.LpuSectionProfile flsp on flsp.LpuSectionProfile_id = lsp.LpuSectionProfile_fedid
    and (paramLpuSectionProfile is null or flsp.lpusectionprofile_id = paramLpuSectionProfile)
		)
  
, disp as (
select disp.dispclass_id,       
       case when disp.dispclass_code = '1' then dispclass_name
            when disp.dispclass_code = '2' then dispclass_name
            when disp.dispclass_code = '5' then dispclass_name
            when disp.dispclass_code in ('3','4') then 'Диспансеризация детей-сирот пребывающих в стационарных учреждениях'
            when disp.dispclass_code in ('7','8') then 'Диспансеризация детей-сирот усыновленных'
            when disp.dispclass_code = '10' then dispclass_name
            when disp.dispclass_code = '12' then dispclass_name end as disp_name,
       case when disp.dispclass_code in ('10','12','7','8','3','4') then 1 else 2 end as age_gr
  from v_dispclass disp 
where disp.dispclass_code in ('1','2','5','3','4','7','8','10','12')) 

, 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,
			 coalesce(s.lpusection_id,ps.lpusection_id) as lpusection_id,
			 s.medpersonal_id,
			 cast(ps.evnps_setdt as date) as date,
       case when is_tools then true else false END as is_tools,
			 paramTarifCalc
  from prm 
  inner join v_evnps ps on ps.lpu_id = paramLpu and ps.evnps_disdate between paramBegDate and paramEndDate + interval '1 day' - interval '1 minute'
  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 (paramMedStaffFact is null or paramMedStaffFact = s.medpersonal_id)    
	left join v_evnusluga eu on eu.Evnusluga_pid=s.evnsection_id
  left join lateral (select 1::boolean as is_tools from tools tls where tls.uslugacomplex_id = eu.uslugacomplex_id limit 1) as l_tls on true
  left 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) or (paramKSDCKSG = 2 and d.care_type = 2)) and 
      (coalesce(paramLpuBuilding, paramLpuUnit, paramLpuSection, paramLpuSectionProfile) is null or d.lpusection_id = coalesce(s.lpusection_id,ps.lpusection_id))) 
  
, pl as (       
select pl.evnpl_id as case_id ,
       s.evnvizitpl_id as step_id,
       s.evnvizitpl_count as step_kolvo,
       pl.person_id,      
--       case when coalesce(tc.treatmentclass_id,is_smp) is null then 3 else 4 end as care_type,
       3 as care_type,
       disp_name,
       pl.evnclass_id,
       smo_agg,
       paramResoTfoms,
       paramByCase,
       paramPayTp,
       pl.diag_id,
       paramBillType,
       null::boolean as is_vmp,
			 pl.lpusection_id,
			 s.medpersonal_id,
 			 cast(pl.evnpl_disdt as date) as date,
			 sum(eu.evnusluga_summa) as uet,
			 paramTarifCalc			 
  from prm 
  inner join v_evnpl pl on paramKSDCKSG is null and pl.lpu_id = paramLpu and pl.evnpl_disdate between paramBegDate and paramEndDate + interval '1 day' - interval '1 minute' and pl.evnpl_isfinish = 2     
  inner join v_evnvizitpl s on s.evnvizitpl_pid = pl.evnpl_id and (paramMedStaffFact is null or paramMedStaffFact = s.medpersonal_id)
	left join v_evnusluga eu on eu.Evnusluga_pid=s.evnvizitpl_id
  inner join v_paytype pt on pt.paytype_id = s.paytype_id and pt.paytype_sysnick = 'oms'    
  left join v_treatmentclass tc on tc.treatmentclass_id = s.treatmentclass_id and tc.treatmentclass_code = '1.1' --СМП
  left join deps d on d.lpusection_id = pl.lpusection_id  
  left join lateral (
       select disp_name              
         from disp where disp.dispclass_id = s.dispclass_id limit 1) as l_sd on true
where (coalesce(paramLpuBuilding, paramLpuUnit, paramLpuSection, paramLpuSectionProfile) is null or d.lpusection_id = s.lpusection_id)
group by pl.evnpl_id, s.evnvizitpl_id, s.evnvizitpl_count, pl.person_id,      
--       case when coalesce(tc.treatmentclass_id,is_smp) is null then 3 else 4 end,
       disp_name, pl.evnclass_id, smo_agg, paramResoTfoms, paramByCase, paramPayTp,
       pl.diag_id, paramBillType, null::boolean, pl.lpusection_id, s.medpersonal_id, cast(pl.evnpl_disdt as date), paramTarifCalc
)

, dis as (
select disp.evnpldisp_id as case_id,
       disp.evnpldisp_id as step_id,
       1 as step_kolvo,
       disp.person_id,
       3 as care_type,
       d.disp_name,
       disp.evnclass_id,
       smo_agg,
       paramResoTfoms,
       paramByCase,
       paramPayTp,
       null::int as diag_id,
       paramBillType,
       null::boolean as is_vmp,
			 disp.EvnPLDisp_consDT,
			 1 as is_dis,
			 eu.lpusection_id,
			 eu.medpersonal_id,
			 cast(disp.evnpldisp_disdt as date) as date,
			 case when cast(disp.EvnPLDisp_consDT as date) != cast(eu.evnusluga_setdt as date) then 1 else 0 end as twice,
			 disp.dispclass_id,
			 paramTarifCalc
       --Добавить параметр paramMedStaffFact и заджойнить deps        
  from prm 
  inner join v_evnpldisp disp on paramKSDCKSG is null and disp.lpu_id = paramLpu and disp.evnpldisp_disdate between paramBegDate and paramEndDate + interval '1 day' - interval '1 minute'
    and disp.evnclass_id in (1,2,5,3,4,7,8,10,12,31,32)
  inner join disp as d on d.dispclass_id = disp.dispclass_id  
  inner join v_paytype pt on pt.paytype_id = disp.paytype_id and pt.paytype_sysnick = 'oms'    
  left join v_EvnPLDispDop13 as d_101 on d_101.evnpldispdop13_id = disp.evnpldisp_id
  left join v_EvnPLDispProf as d_103 on d_103.EvnPLDispProf_id = disp.evnpldisp_id
  left join v_EvnPLDispOrp as d_9 on d_9.EvnPLDispOrp_id = disp.evnpldisp_id 
  left join v_EvnPLDispTeenInspection as d_104 on d_104.EvnPLDispTeenInspection_id = disp.evnpldisp_id  
	left join lateral
	(
		select eu.MedStaffFact_id, msf.lpusection_id, eu.medpersonal_id, eu.evnusluga_setdt from v_Evnusluga eu
		left join v_UslugaComplex uc on uc.Uslugacomplex_id=eu.Uslugacomplex_id
		left join v_MedStaffFact msf on msf.MedStaffFact_id=eu.MedStaffFact_id
		where eu.evnusluga_rid=coalesce(disp.evnpldisp_id, d_101.EvnPLDispDop13_id, d_103.EvnPLDispProf_id, d_9.EvnPLDispOrp_id, d_104.EvnPLDispTeenInspection_id)
		and uc.uslugacomplex_code in ('B04.047.002', 'B01.031.001', 'B01.031.002', 'B01.031.003', 'B01.031.004')
		limit 1
	)eu on true
)

, smp as (
select ccc.cmpclosecard_id as case_id,
       ccc.cmpcallcard_id as step_id,
       1::bigint as step_kolvo,
       ccc.person_id,       
--       coalesce(nullif((max(case when d.care_type = 1 then 1 else 0 END)over(partition by ccc.cmpcallcard_id)=1)::int,0),2) as care_type,
	   4 as care_type,
       smo_agg,
       paramResoTfoms,
       paramByCase,
       paramPayTp,
       ccc.diag_id,
       paramBillType,
			 ccc.lpusection_id as lpusection_id,
			 ccc.medpersonal_id,
			 cast(ccc.AcceptTime as date) as date,
			 paramTarifCalc			 
  from prm
  inner join v_cmpclosecard ccc on ccc.lpu_id = paramLpu and ccc.AcceptTime between paramBegDate and paramEndDate + interval '1 day' - interval '1 minute'
    and (paramMedStaffFact is null or paramMedStaffFact = ccc.medpersonal_id)    
  inner join v_paytype pt on pt.paytype_id = ccc.paytype_id and pt.paytype_sysnick = 'oms'
  left join deps d on d.lpusection_id = ccc.lpusection_id
where (coalesce(paramLpuBuilding, paramLpuUnit, paramLpuSection, paramLpuSectionProfile) is null or d.lpusection_id = ccc.lpusection_id)
)

, t as (  
select case_id,step_id,step_kolvo,person_id,care_type,null as disp_name,kd, null as evnclass_id,smo_agg,paramResoTfoms,
paramByCase,paramPayTp,diag_id,paramBillType,is_vmp, cast(null as timestamp) as EvnPLDisp_consDT, cast(null as bigint) as is_dis, lpusection_id,
medpersonal_id, date, is_tools, cast(null as bigint) as twice, cast(null as float) as uet, null::bigint as dispclass_id, paramTarifCalc
from ps where isfinish::boolean union all  

select case_id,step_id,step_kolvo,person_id,care_type,disp_name,null as kd,evnclass_id,smo_agg,paramResoTfoms,
paramByCase,paramPayTp,diag_id,paramBillType,is_vmp, null, null, lpusection_id, medpersonal_id, date, null, null, uet, null::bigint as dispclass_id, paramTarifCalc
from pl union all 

select case_id,step_id,step_kolvo,person_id,care_type,disp_name,null as kd,evnclass_id,smo_agg,paramResoTfoms,
paramByCase,paramPayTp,diag_id,paramBillType,is_vmp, EvnPLDisp_consDT, is_dis, lpusection_id, medpersonal_id, date, null, twice, null, dispclass_id, paramTarifCalc
from dis union all

select case_id,step_id,step_kolvo,person_id,care_type,null,null as kd,null,smo_agg,paramResoTfoms,
paramByCase,paramPayTp,diag_id,paramBillType,null, null, null, lpusection_id, medpersonal_id, date, null, null, null, null, paramTarifCalc
from smp
)

, 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())) 

, pmt as (
select p.paymedtype_id, p.paymedtype_code,
       case 
            when p.paymedtype_code = '25' then 'По подушевому нормативу финансирования на прикрепившихся лиц'
--            when p.paymedtype_code = '26' then 'По подушевому нормативу (посещение)'
--            when p.paymedtype_code = '27' then 'По подушевому нормативу (обращение)'
            when p.paymedtype_code = '29' then 'За посещение'
            when p.paymedtype_code = '30' then 'За обращение по заболеванию'
            when p.paymedtype_code = '28' then 'За медицинскую услугу'
            when p.paymedtype_code = '32' then 'За законченный случай лечения заболевания ВМП'
            when p.paymedtype_code = '33' then 'За законченный случай лечения заболевания КСГ'
            when p.paymedtype_code = '36' then 'По подушевому нормативу финансирования в сочетании с оплатой за вызов скорой медицинской помощи'
            when p.paymedtype_code = '24' then 'За вызовы скорой медицинской помощи'
						when p.paymedtype_code = '31' then 'По подушевому нормативу финансирования на прикрепившихся лиц с учетом показателей результативности деятельности медицинской организации'
 end as pay_type
  from v_paymedtype p
where p.paymedtype_code in ('25','29','30','28','32','33','36','24','31'))

, main  as (
select distinct
       case when is_vmp then 1 else care_type end as ump,
       case_id,
       step_id,
       row_number() over(partition by case_id order by case_id, step_id) nn,
       step_kolvo,
       is_tools,
       case when paramByCase = 0 then null else rd.numcard end as numcard,
			 
			 case when paramTarifCalc = 1 then rd.RegistryData_ItogSum else
						case when paramTarifCalc = 2 and (t.dispclass_id in (31, 32) or t.dispclass_id is null) then rd.RegistryData_ItogSum else rd.registrydata_tariff end
					end as sum,
			 case when t.evnclass_id=6 and step_kolvo = 1 then 'За посещение (стоматология)'
			      when t.evnclass_id=6 and step_kolvo > 1 then 'За обращение по заболеванию (стоматология)'
			      when rd.vizittype_id = 70101000000033 then 'За посещение (неотложная помощь)'
			      when t.dispclass_id = 31 then 'За посещение (углубленная диспансеризация 1 этап)'
			      when t.dispclass_id = 32 then 'За посещение (углубленная диспансеризация 2 этап)'
			      when pmt.paymedtype_code = '25' and step_kolvo = 1 then 'Подушевое - за посещение'
			      when pmt.paymedtype_code = '25' and step_kolvo > 1 then 'Подушевое - за обращение'
			 else pmt.pay_type end as pay_type,
			 
       pmt.paymedtype_code,						
						
       max(case when disp_name is not null then disp_name else null END)over(partition by case_id) as disp_name,
       kd,       
       rd.paid_id,
       t.evnclass_id=6 as is_stom,
       t.uet,
			 t.EvnPLDisp_consDT,
			 t.is_dis,
			 ls.Lpusection_Name,
			 mp.Person_fio as Medpersonal_fio,
			 lsp.lpusectionprofile_name,
			 case when dbo.AgeYMD(ps.person_birthday, t.date, 1) < 18 then 'Дети' else 'Взрослые' end as vozr,
			 t.twice,
			 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.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))    
  inner join pmt on pmt.paymedtype_id = rd.paymedtype_id
  left join r7.v_Registry r on r.registry_id = rd.registry_id
    and (paramBillType is null or paramBillType = Registry_IsRepeated)
	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
	left join r7.registryerrortfoms ret on ret.registry_id = r.registry_id and ret.evn_id = step_id
	
	left join v_LpuSection ls on ls.LpuSection_id=t.lpusection_id
	left join v_LpuSectionProfile lsp on lsp.lpusectionprofile_id=ls.lpusectionprofile_id
	left join lateral
	(
		select * from v_Medpersonal mp where mp.medpersonal_id=t.medpersonal_id limit 1
	)mp on true
	left join v_personstate ps on ps.person_id=t.person_id
	where (paramPayTp is null or pmt.paymedtype_id = paramPayTp)--способ оплаты       
)

, t1_1 as (--Только профосмотры+
select 1 as flg,
       ump,
       disp_name as pay_type,
			 m.paymedType_code,
			 m.lpusection_Name,
			 m.Medpersonal_fio,
			 m.lpusectionprofile_name,
			 m.vozr,
       sum(case when nn = 1 then step_kolvo end) +
			 count(case when m.twice=1 then case_id end) as g4,
			 -- для профосмотров считаем посещения ректально: если дата подписания согласия и дата осмотра терапевтом совпадают, то это одно посещение, если разные то это два посещения.
			 -- поэтому пришлось протащить дату подписания. флаг is_dis добавил так как такой же care_type может и ТАПы означать
			 
       count(distinct case_id) as g5,
       string_agg(distinct numcard,',') as g5_1,

			 sum(case when nn = 1 then step_kolvo end) +
 			 count(case when m.twice=1 then case_id end) as g6,

       count(distinct case_id) as g7,
       string_agg(distinct numcard,',') as g7_1,
       sum(case when nn = 1 then sum end) as g8,
			 
       sum(case when nn = 1 and regerror is null then step_kolvo end) +
 			 count(case when m.twice=1 and regerror is null then case_id 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 and nn = 1 then sum end) as g11,
       
       sum(case when (paid_id = 1 or regerror is not null) and nn = 1 then step_kolvo end) +
 			 count(case when m.twice=1 then case_id 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 and nn = 1 then step_kolvo end) +
 			 count(case when m.twice=1 then case_id 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 and nn = 1 then sum end) as g17
from main m
where ump = 3 and disp_name is not null 
group by 1,2,3,4,5,6,7,8)

, t1_2 as (--Только стационар+
select 2 as flg,
       ump,
       pay_type,
			 m.paymedType_code,
			 m.lpusection_Name,
			 m.Medpersonal_fio,
			 m.lpusectionprofile_name,
			 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
where ump in (1,2) and not is_tools --и не подушевое
group by 1,2,3,4,5,6,7,8)

, t1_3 as (--Только пол-ка+
select 3 as flg,
       ump,
       pay_type,
			 m.paymedType_code,
			 m.lpusection_Name,
			 m.Medpersonal_fio,
			 m.lpusectionprofile_name,
			 m.vozr,
       sum(case when nn = 1 then step_kolvo end) as g4,
       count(distinct case_id) as g5,
       string_agg(distinct numcard,',') as g5_1,       
       sum(case when nn = 1 then step_kolvo end) as g6,
       count(distinct case_id) as g7,
       string_agg(distinct numcard,',') as g7_1,
       sum(case when nn = 1 then sum end) as g8, 
			 
       sum(case when nn = 1 and regerror is null then step_kolvo 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 and nn = 1 then sum end) as g11,
			 
       sum(case when (paid_id = 1 or regerror is not null) and nn = 1 then step_kolvo 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) and nn = 1 then sum end) as g14,
			 
       sum(case when paid_id = 2 and nn = 1 then step_kolvo 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 and nn = 1 then sum end) as g17
from main m
where ump = 3 and not is_tools and disp_name is null and not is_stom  --и не подушевое
group by 1,2,3,4,5,6,7,8)

, t1_3_1 as (--Только пол-ка подушевое
select 3 as flg,
       ump,
       pay_type,
			 m.paymedType_code,
			 m.lpusection_Name,
			 m.Medpersonal_fio,
			 m.lpusectionprofile_name,
			 m.vozr,
       sum(case when nn = 1 then step_kolvo end) as g4,
       count(distinct case_id) as g5,
       string_agg(distinct numcard,',') as g5_1,       
       sum(case when nn = 1 then step_kolvo end) as g6,
       count(distinct case_id) as g7,
       string_agg(distinct numcard,',') as g7_1,
       sum(case when nn = 1 then sum end) as g8,
			 
       sum(case when nn = 1 and regerror is null then step_kolvo 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 and nn = 1 then sum end) as g11,
			 
       sum(case when (paid_id = 1 or regerror is not null) and nn = 1 then step_kolvo 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) and nn = 1 then sum end) as g14,
			 
       sum(case when paid_id = 2 and nn = 1 then step_kolvo 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 and nn = 1 then sum end) as g17
from main m
where ump = 3 and is_tools is null and disp_name is null and not is_stom  --и не подушевое
group by 1,2,3,4,5,6,7,8)

, t1_4 as (--Только стоматология+
select 3 as flg,
       ump,
       pay_type,
			 m.paymedType_code,
			 m.lpusection_Name,
			 m.Medpersonal_fio,
			 m.lpusectionprofile_name,
			 m.vozr,
       sum(uet) as g4,
       count(distinct case_id) as g5,
       string_agg(distinct numcard,',') as g5_1,    
       sum(uet) 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 uet 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 uet 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 uet 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
where ump in (3,6) and is_tools is null and disp_name is null and is_stom  --и не подушевое
group by 1,2,3,4,5,6,7,8)


, t1_5 as (--Только КТ МРТ+
select case when ump in (1,2) then 1 else 3 end as flg,
       ump,
       pay_type,
			 m.paymedType_code,
			 m.lpusection_Name,
			 m.Medpersonal_fio,
			 m.lpusectionprofile_name,
			 m.vozr,
       count(distinct case_id) as g4,
       count(distinct case_id) as g5,
       string_agg(distinct numcard,',') as g5_1,       
       count(distinct case_id) as g6,
       count(distinct case_id) as g7,
       string_agg(distinct numcard,',') as g7_1,
       sum(sum) as g8,
			 
       count(distinct case when regerror is null then case_id 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,
			 
       count(distinct case when (paid_id = 1 or regerror is not null) then case_id 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,
			 
       count(distinct case when paid_id = 2 then case_id 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
where disp_name is null and is_tools and not is_stom and ump != 4
group by 1,2,3,4,5,6,7,8)
/*
, t1_6 as (--Только ВМП
select case when ump in (1,2) then 2 else 3 end as flg,
       ump,
       pay_type,
			 m.paymedType_code,
			 m.lpusection_Name,
			 m.Medpersonal_fio,
			 m.lpusectionprofile_name,
			 m.vozr,
       sum(case when nn = 1 then step_kolvo end) as g4,
       count(distinct case_id) as g5,
       string_agg(distinct numcard,',') as g5_1,       
       sum(case when nn = 1 then step_kolvo end) as g6,
       count(distinct case_id) as g7,
       string_agg(distinct numcard,',') as g7_1,
       sum(case when nn = 1 then sum end) as g8,
			 
       sum(case when nn = 1 and regerror is null then step_kolvo 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 and nn = 1 then sum end) as g11,
			 
       sum(case when (paid_id = 1 or regerror is not null) and nn = 1 then step_kolvo 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) and nn = 1 then sum end) as g14,
			 
       sum(case when paid_id = 2 and nn = 1 then step_kolvo 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 and nn = 1 then sum end) as g17
from main m
where ump = 4
group by 1,2,3,4,5,6,7,8)
*/
, t1_7 as (--Только CМП
select case when ump in (1,2) then 2 else 3 end as flg,
       ump,
       pay_type,
			 m.paymedType_code,
			 m.lpusection_Name,
			 m.Medpersonal_fio,
			 m.lpusectionprofile_name,
			 m.vozr,
       sum(case when nn = 1 then step_kolvo end) as g4,
       count(distinct case_id) as g5,
       string_agg(distinct numcard,',') as g5_1,       
       sum(case when nn = 1 then step_kolvo end) as g6,
       count(distinct case_id) as g7,
       string_agg(distinct numcard,',') as g7_1,
       sum(case when nn = 1 then sum end) as g8,
			 
       sum(case when nn = 1 and regerror is null then step_kolvo 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 and nn = 1 then sum end) as g11,
			 
       sum(case when (paid_id = 1 or regerror is not null) and nn = 1 then step_kolvo 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) and nn = 1 then sum end) as g14,
			 
       sum(case when paid_id = 2 and nn = 1 then step_kolvo 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 and nn = 1 then sum end) as g17
from main m
where ump in (4,5)
group by 1,2,3,4,5,6,7,8)

, _t as (
select * from t1_1 union all--Профосмотры
select * from t1_2 union all--Стационар
select * from t1_3 union all--Пол-ка без зуб
select * from t1_3_1 union all--Пол-ка подушевое
select * from t1_4 union all--Пол-ка зуб
select * from t1_5 union all--Пол-ка/Стац-р с КТ МРТ 
--select * from t1_6 union all--ВМП
select * from t1_7
)

select flg,
       ump,
       pay_type,
			 paymedtype_code,
	   coalesce(medpersonal_fio, '(не указано)') as medpersonal_fio,
       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,
       case when paymedtype_code not in ('25', '36', '31') then sum(g8) end g8,
       sum(g9) g9,
       sum(g10) g10,
       '('||string_agg(g10_1,', ')||')' as g10_1,
       case when paymedtype_code not in ('25', '36', '31') then sum(g11) end g11,
       sum(g12) g12,
       sum(g13) g13,
       '('||string_agg(g13_1,', ')||')' as g13_1,
       case when paymedtype_code not in ('25', '36', '31') then sum(g14) end g14,
       sum(g15) g15,
       sum(g16) g16,
       '('||string_agg(g16_1,', ')||')' as g16_1,
       case when paymedtype_code not in ('25', '36', '31') then sum(g17) end g17
  from _t 
group by 1,2,3,4,5
order by pay_type
