select * from cmn_finder where code = 'onk.sh_lek.v024';

SELECT id, concat_ws('::', code, name) as name, code
from (select iddkk::text as code, dkkname as name, id, datebeg::date, dateend::date from billing.v024 where iddkk ~* '^sh|^mt|^нет') sh_lek
    WHERE concat(code, '::', name, '::', id) ilike '%239%'
and (datebeg is null or datebeg <= CURRENT_DATE) and (dateend is null or dateend >= CURRENT_DATE);








select * from cmn_finder where code = 'onk.gist_rslt.n007_8_result';
select * from cmn_finder where code = 'case.stepResult';
select * from cmn_finder where code = 'case.stepCareResult'


with cte as (
    SELECT id, concat_ws('::', code, name) as name, code
    from (
        select r.id_r_m::text as code, r.r_m_name || ' (' || g.mrf_name || ')' as name, r.id,
            r.datebeg::date as r_datebeg, r.dateend::date as r_dateend, g.datebeg::date as g_datebeg, g.dateend::date as g_dateend
        from billing.n008 r join billing.n007 g ON g.id=r.id_mrf) gist_rslt
        where (r_datebeg is null or r_datebeg <= CURRENT_DATE)
            and (r_dateend is null or r_dateend >= CURRENT_DATE)
            and (g_datebeg is null or g_datebeg <= CURRENT_DATE)
            and (g_dateend is null or g_dateend >= CURRENT_DATE)
)
select id, name, code from cte  --where name ilike '%' || :text || '%'
order by code like '0%', code, name;






select distinct mc_step_result.id, mc_step_result.name, mc_step_result.is_closed
            from (select * from mc_step_result ) mc_step_result
            left outer join (select * from mc_step_result_to_care_regimen ) mc_step_result_to_care_regimen
            on mc_step_result_to_care_regimen.step_result_id = mc_step_result.id
where /*lower(name) like '%'||lower(:text)||'%' and (mc_step_result_to_care_regimen.regimen_id = :regimenId or :regimenId = '-1')
and */(mc_step_result.to_dt is null or mc_step_result.to_dt > current_date)



select mc_step_care_result.id, mc_step_care_result.name, mc_step_care_result.code as code from (select * from mc_step_care_result ) mc_step_care_result
 inner join (select * from mc_step_result_care_result ) mc_step_result_care_result
            on mc_step_result_care_result.care_result_id = mc_step_care_result.id inner join (select * from mc_care_result_to_care_regimen ) mc_care_result_to_care_regimen
            on mc_care_result_to_care_regimen.care_result_id = mc_step_care_result.id
where mc_step_result_care_result.result_id = :resultId and mc_care_result_to_care_regimen.regimen_id = :regimenId and lower(mc_step_care_result.name) like '%'||lower(:text)||'%'


select * from mc_care_result_to_care_regimen where id = 1