123

WITH outside_d AS (
    SELECT NULLIF(diagnos_id, '')::INTEGER AS diagnos_id, NULLIF(type_id,'')::INTEGER AS type_id
    FROM billing.get_element_fj(:diagnosis::TEXT, 'mkb_id;type_id'::TEXT) AS t (diagnos_id TEXT, type_id TEXT)
    ),
outside_u AS (
    SELECT NULLIF(service_id, '')::INTEGER AS service_id
    FROM billing.get_element_fj(:services::TEXT, 'service_id'::TEXT) AS t (service_id TEXT)
    ),
outside_da AS (
    SELECT NULLIF(admission_date, '')::DATE AS admission_date, NULLIF(outcome_date, '')::DATE AS outcome_date
    FROM billing.get_element_fj(:visits::TEXT, 'admission_date;outcome_date'::TEXT) AS t (admission_date TEXT, outcome_date TEXT)
    ),
cli AS(
SELECT :organizationId::INTEGER as clinic_id,:careRegimenId::INTEGER as care_regimen_id,array_agg(oc.code) as org_code_arr
  FROM pim_org_code oc
 where oc.org_id =:organizationId::INTEGER
 GROUP BY oc.org_id
),
cl AS (
SELECT c.clinic_id, c.care_regimen_id, c.org_code_arr, d.diagnos_id AS "diagnosisId", dd.add_diagnos_id_arr, u.service_id_arr, da.adm_date AS "admissionDate", da.out_date AS "outcomeDate", da.open_date AS "openDate", :patientId::INTEGER AS "patientId"
FROM cli AS c
LEFT JOIN LATERAL (SELECT diagnos_id FROM outside_d WHERE type_id = 1 LIMIT 1) AS d ON TRUE
LEFT JOIN LATERAL (SELECT ARRAY_AGG(diagnos_id) AS add_diagnos_id_arr FROM outside_d WHERE type_id <> 1) AS dd ON TRUE
LEFT JOIN LATERAL (SELECT ARRAY_AGG(service_id) AS service_id_arr FROM outside_u) AS u ON TRUE
LEFT JOIN LATERAL (SELECT MAX(admission_date) AS adm_date, MAX(outcome_date) AS out_date, MIN(admission_date) AS open_date FROM outside_da) AS da ON TRUE
),
tb1 AS(SELECT CASE WHEN :organizationId::INTEGER IN (33656)
            THEN NULL
            ELSE case when "outcomeDate" < '2017-01-01'::date then typing.fin_get_ksg_typing(:diagnosis::JSON,:visits::JSON,:services::JSON,:patientId::INTEGER,:organizationId::INTEGER,:careRegimenId::INTEGER)
                                 when "outcomeDate" between '2017-01-01'::date and '2017-12-31'::date then typing.fin_get_ksg_typing_2017(:diagnosis::JSON,:visits::JSON,:services::JSON,:patientId::INTEGER,:organizationId::INTEGER,:careRegimenId::INTEGER)
                 when "outcomeDate" between '2018-01-01'::date and '2018-12-31'::date then typing.fin_get_ksg_typing_2018(:diagnosis::JSON,:visits::JSON,:services::JSON,:patientId::INTEGER,:organizationId::INTEGER,:careRegimenId::INTEGER)
                                 ELSE (SELECT billing.get_csg_by_params_2019(NULL, hstore(cl)) -> 'csg_id')::int end
       END AS "id", cl.org_code_arr
  FROM cl
),tb AS(
SELECT tb1.id AS "id"
FROM tb1
LEFT JOIN billing.billing_csg_to_bed_profile b on b.code_mo = ANY(tb1.org_code_arr) and b.ksg_id = tb1.id
 WHERE
   (b.bed_profile_id in (select to_int((json_array_elements(:visits::JSON) ->>'bed_profile_id'))) or not exists  (select 1 from json_array_elements(:visits::JSON) x where typing.to_int(x->>'bed_profile_id') is not null) OR b.bed_profile_id IS NULL)
   AND coalesce( (select typing.to_dt(max(x.outcome_date)) from (select json_array_elements(:visits::JSON)->> 'outcome_date' outcome_date )x),(select typing.to_dt(max(x.admission_date)) from (select json_array_elements(:visits::JSON)->> 'admission_date' admission_date )x)) BETWEEN COALESCE(b.from_dt, DATE '1900-01-01') AND COALESCE(b.to_dt, DATE '4000-01-01')
),sg as(
SELECT sg.id
  FROM sr_res_group g
    LEFT JOIN LATERAL (SELECT array_agg(DISTINCT pbp.bed_profile_id) bed_profile_id_arr FROM md_profile_to_bed_profile pbp WHERE pbp.profile_id in (select to_int((json_array_elements(:visits::JSON) ->>'profile_id')))) AS pbp ON TRUE
   LEFT JOIN LATERAL (SELECT b.bed_profile_id
                       FROM sr_res_group_relationship rgr
                       JOIN sr_resource r ON r.id = rgr.resource_id
                       JOIN md_bed_resource br ON br.id = r.id
                       JOIN md_bed b ON b.id = br.bed_id
                      WHERE rgr.group_id = g.id
                        AND rgr.role_id = 6) AS b ON TRUE
  LEFT JOIN md_bed_profile bp ON (bp.id = b.bed_profile_id OR bp.id = ANY(pbp.bed_profile_id_arr))
  LEFT JOIN md_clinical_statistical_group sg ON sg.bed_profile_id = bp.id
  LEFT JOIN LATERAL (SELECT id, :careRegimenId::INTEGER FROM tb LIMIT 1) AS tb ON TRUE
 WHERE g.id = coalesce(:resGroupId::INTEGER,(select to_int((json_array_elements(:visits::JSON) ->>'res_group_id')  )
order by 1 desc limit 1))
      AND sg.type_id = 3
   AND sg.to_dt IS NULL
   AND tb.id IS NULL
--union
--select sg.id from md_clinical_statistical_group sg where sg.bed_profile_id in (select to_int((json_array_elements(:visits::JSON) ->>'bed_profile_id')))
),csg AS(
SELECT csg.id
  FROM md_clinical_statistical_group csg
  JOIN billing.billing_csg_to_bed_profile b ON csg.id = b.ksg_id
  JOIN cl ON b.code_mo = ANY(cl.org_code_arr)
  LEFT JOIN LATERAL (SELECT id FROM tb LIMIT 1) AS tb ON TRUE
  LEFT JOIN LATERAL (SELECT id FROM sg LIMIT 1) AS sg ON TRUE
 WHERE csg.to_dt IS NULL
   AND csg.code LIKE '%'|| CASE WHEN cl.care_regimen_id IN (3,4,5) THEN '1' WHEN cl.care_regimen_id = 2 THEN '0' ELSE '' END
   AND csg.type_id IN (3, 4)
   AND tb.id IS NULL
   AND sg.id IS NULL)
, q as (SELECT g.id, g.code, g.code||' '||g.name as name
  FROM md_clinical_statistical_group g
  JOIN (SELECT id FROM tb
        UNION ALL
        SELECT id FROM sg
        UNION ALL
        SELECT id FROM csg
       ) AS s ON s.id = g.id
)
,q1 as (
select case q.code
when '1500030' then regexp_split_to_table('1504030,1564030', ',')
when '1500040' then regexp_split_to_table('1504040,1564040,1564030', ',')
when '1500050' then regexp_split_to_table('1504050,1564050', ',')
when '1500010' then regexp_split_to_table('1560010', ',')
when '1500170' then regexp_split_to_table('1564030', ',')
  end as code_dop , *
from q,cl
where case when cl.clinic_id = 33664  then q.code in ('1500030','1500040','1500050','1500010','1500170')  end
)
select distinct id, name||' '||COALESCE(p.price, pp.price, 'Тариф не найден') as name from (
select distinct id, name from q
union all
select g.id, g.code||' '||g.name as name
from md_clinical_statistical_group g
join q1 a on g.code = a.code_dop
union all
select distinct g.id, g.code||' '||g.name as name
from md_clinical_statistical_group g
join q on g.code like substring (q.code,1,2)||'_'||substring (q.code,4,4) and q.id <> g.id and (select max( typing.to_dt(x->>'outcome_date')) from json_array_elements(:visits::json) x) between coalesce (g.from_dt, DATE '1900-01-01') AND coalesce (g.to_dt, DATE '4000-01-01')
)qall
left join lateral (select p.price::text from fin_price_list l,fin_pl_position_to_csg c,fin_pl_position p where l.clinic_id=:organizationId::INTEGER
and coalesce( (select typing.to_dt(max(x.outcome_date)) from (select json_array_elements(:visits::JSON)->> 'outcome_date' outcome_date )x),(select typing.to_dt(max(x.admission_date)) from (select json_array_elements(:visits::JSON)->> 'admission_date' admission_date )x))between coalesce (l.from_dt, DATE '1900-01-01') AND coalesce (l.to_dt, DATE '4000-01-01')
and l.id=p.price_list_id and coalesce( (select typing.to_dt(max(x.outcome_date)) from (select json_array_elements(:visits::JSON)->> 'outcome_date' outcome_date )x),(select typing.to_dt(max(x.admission_date)) from (select json_array_elements(:visits::JSON)->> 'admission_date' admission_date )x)) between coalesce (p.from_dt, DATE '1900-01-01') AND coalesce (p.to_dt, DATE '4000-01-01')
and p.id=c.position_id and c.csg_id=qall.id limit 1)p on true left join lateral (select p.price::text from fin_price_list l,billing.fin_pl_position_to_csg c,billing.fin_pl_position_polyclinic p where l.clinic_id=:organizationId::INTEGER
and coalesce( (select typing.to_dt(max(x.outcome_date)) from (select json_array_elements(:visits::JSON)->> 'outcome_date' outcome_date )x),(select typing.to_dt(max(x.admission_date)) from (select json_array_elements(:visits::JSON)->> 'admission_date' admission_date )x))between coalesce (l.from_dt, DATE '1900-01-01') AND coalesce (l.to_dt, DATE '4000-01-01')
and l.id=p.price_list_id and coalesce( (select typing.to_dt(max(x.outcome_date)) from (select json_array_elements(:visits::JSON)->> 'outcome_date' outcome_date )x),(select typing.to_dt(max(x.admission_date)) from (select json_array_elements(:visits::JSON)->> 'admission_date' admission_date )x)) between coalesce (p.from_dt, DATE '1900-01-01') AND coalesce (p.to_dt, DATE '4000-01-01')
and p.id=c.position_id and c.csg_id=qall.id limit 1)pp on true