--загрузка нового группировщика
--выполнять только после решения вопроса с возможно отсутствующими диагнозами или услугами 
insert into mesolduslugacomplex (
mesolduslugacomplex_id,uslugacomplex_id,mes_id,sex_id,mesagegroup_id,diag_id,diag_nid,diag_oid,mesolduslugacomplex_isdiag,
   mesolduslugacomplex_begdt,mesolduslugacomplex_duration,mesolduslugacomplex_durationto,drugtherapyscheme_id,rehabscale_id,
   mesolduslugacomplex_sofascalepoints,mesolduslugacomplex_ivlhours,uslugacomplex_aid,uslugacomplex_bid,mesolduslugacomplex_fracfrom,
   mesolduslugacomplex_fracto,mesdop_id,pmuser_insid,pmuser_updid,mesolduslugacomplex_insdt,mesolduslugacomplex_upddt
)
select 
   nextval('mesolduslugacomplex_mesolduslugacomplex_id_seq') as mesolduslugacomplex_id,
   cx.uslugacomplex_id,
   mes.mes_id,
   nullif(g.gender_code,'')::bigint as sex_id,
   case g.age_group_code when '1' then 4 when '2' then 9 when '3' then 10 when '4' then 11 when '5' then 5 when '6' then 6 end as mesagegroup_id,
   diag.diag_id,
   diag_n.diag_id as diag_nid,
   diag_o.diag_id as diag_oid,
   case when coalesce(diag.diag_id,diag_n.diag_id,diag_o.diag_id) is not null then 2 end as mesolduslugacomplex_isdiag,
   g.from_dt as mesolduslugacomplex_begdt,
   case g.duration_code when '2' then 4 when '3' then 11 when '4' then 21 end as mesolduslugacomplex_duration,
   case g.duration_code when '1' then 3 when '2' then 10 when '3' then 20 when '4' then 30 end as mesolduslugacomplex_durationto,
   dts.drugtherapyscheme_id,   
   case when g.criteria_code ~ '^rb\d{1}$' then replace(g.criteria_code,'rb','')::bigint end as rehabscale_id,   
   case when g.criteria_code = 'it1' then case when g.age_group_code = '5' then 4 else 5 end end as mesolduslugacomplex_sofascalepoints,
   case g.criteria_code when 'it1' then 72 when 'it2' then 480 end as mesolduslugacomplex_ivlhours,
   case when g.criteria_code = 'rbs' then (select uslugacomplex_id from v_uslugacomplex cxa where cxa.uslugacomplex_code = 'B05.069.005' and cxa.uslugacomplex_enddt is null) end as uslugacomplex_aid,
   case when g.criteria_code = 'rbs' then (select uslugacomplex_id from v_uslugacomplex cxb where cxb.uslugacomplex_code = 'B05.069.006' and cxb.uslugacomplex_enddt is null) end as uslugacomplex_bid,
   case when g.fr_code ~ '^fr' then substr(g.fr_code,3,2)::bigint end as mesolduslugacomplex_fracfrom,
   case when g.fr_code ~ '^fr' then right(g.fr_code,2)::bigint end as mesolduslugacomplex_fracto,
   md.mesdop_id,   
   1 as pmuser_insid,1 as pmuser_updid,now() as mesolduslugacomplex_insdt,now() as mesolduslugacomplex_upddt   
from tmp.csg_group_data_89  g
 join v_mesold mes on g.csg_code = mes.mesold_num
 left join v_uslugacomplex cx on g.service_code = cx.uslugacomplex_code and cx.uslugacomplex_enddt is null
   
 left join v_diag diag on
    case
        when nullif(g.diagnosis_code,'') is null then false
        when g.diagnosis_code = 'C00-C80' then diag.diaglevel_id = 4 and left(diag.diag_code,3) between 'C00' and 'C80'
        when g.diagnosis_code = 'C81-C96' then diag.diaglevel_id = 4 and left(diag.diag_code,3) between 'C81' and 'C96'
        when g.diagnosis_code = 'D00-D09' then diag.diaglevel_id = 4 and left(diag.diag_code,3) between 'D00' and 'D09'
        when g.diagnosis_code = 'D45-D47' then diag.diaglevel_id = 4 and left(diag.diag_code,3) between 'D45' and 'D47'
        when g.diagnosis_code = 'Q20-Q28' then diag.diaglevel_id = 4 and left(diag.diag_code,3) between 'Q20' and 'Q28'
        when g.diagnosis_code = 'C.' then diag.diaglevel_id = 4 and left(diag.diag_code,1) = 'C'
        when g.diagnosis_code = 'I.' then diag.diaglevel_id = 4 and left(diag.diag_code,1) = 'I'     
        else (
                diag.diaglevel_id = 4 and (g.diagnosis_code = diag.diag_code or g.diagnosis_code = diag.diag_code||'.')
        )
    end
    and (diag.diag_enddate is null or diag.diag_enddate > g.from_dt)
      
      
  left join v_diag diag_n on
    case
        when nullif(g.alt_diagnosis_code,'') is null then false
        when g.alt_diagnosis_code = 'C.' then diag_n.diaglevel_id = 4 and left(diag_n.diag_code,1) = 'C'
        else (
            diag_n.diaglevel_id = 4 and (g.alt_diagnosis_code = diag_n.diag_code or g.alt_diagnosis_code = diag_n.diag_code||'.')
        )
    end
    and (diag_n.diag_enddate is null or diag_n.diag_enddate > g.from_dt)
      
      
  left join v_diag diag_o on
    case
        when nullif(g.osl_diagnosis_code,'') is null then false      
        else (
            diag_o.diaglevel_id = 4 and (g.osl_diagnosis_code = diag_o.diag_code or g.osl_diagnosis_code = diag_o.diag_code||'.')
        )
    end
    and (diag_o.diag_enddate is null or diag_o.diag_enddate > g.from_dt)
      
  left join v_drugtherapyscheme dts on dts.drugtherapyscheme_code = g.criteria_code and dts.drugtherapyscheme_enddate is null
      
  left join v_mesdop md on md.mesdop_code = g.criteria_code and md.region_id = (select dbo.getregion())
   
where
    mes.mes_begdt::date = '01.01.2022'
    and mes.mes_enddt is null
    and mes.mesold_num <> 'st29.007' --политравма не расчитывается по группировщику
      
    --проверка наличия услуги
    and case when nullif(g.service_code,'') is not null then cx.uslugacomplex_id is not null else true end
      
    --проверки наличия диагноза
    and case when nullif(g.diagnosis_code,'') is not null then diag.diag_id is not null else true end
    and case when nullif(g.alt_diagnosis_code,'') is not null then diag_n.diag_id is not null else true end
    and case when nullif(g.osl_diagnosis_code,'') is not null then diag_o.diag_id is not null else true end
      
    ----выполнять блоками, запрос тяжелый
    and                                  -- PL  
      --g.id between 1 and 4999          -- 4523
      --g.id between 5000 and 9999       -- 10472
      --g.id between 10000 and 12499     -- 296253
      --g.id between 12500 and 14999     -- 46619
      --g.id between 15000 and 19999     -- 10435
      --g.id between 20000 and 24999     -- 4773 
      --g.id between 25000 and 29999     -- 4557
      --g.id between 30000 and 32499     -- 135517
      --g.id between 32500 and 34999     -- 2500
      --g.id between 35000 and 39999     -- 63727 
      g.id between 40000 and 44999     -- 216677
      --g.id between 45000 and 49999     -- 4512
      --g.id between 50000 and 54999     -- 5135
      --g.id >= 55000                    -- 617   

