finder_get_service_type_id_dispensary_test

CREATE OR REPLACE FUNCTION "typing"."finder_get_service_type_id_dispensary_test"("patient_id" int4, "org_id" int4, "goal_id" int4)
  RETURNS SETOF "pg_catalog"."int4" AS $BODY$
declare
_age int;
begin



_age =  (SELECT case when date_part('year',age(birth_dt)) <4 or  date_part('year',age(birth_dt))=17 then public.get_age (birth_dt, current_date )
else date_part('year',current_date) - date_part('year',birth_dt) end
from pim_individual where id =$1);
    /*(SELECT case when date_part('year',age(birth_dt)) <4 then date_part('year',age(birth_dt)) else date_part('year',current_date) - date_part('year',birth_dt) end
 from pim_individual where id =$1)*/

if not exists(select 1  FROM md_standard s
        LEFT JOIN md_standard_patient_age sa ON sa.standard_id = s.id
WHERE s.clinic_id = $2
and case when  $3 in( 18, 19, 20) then  lower(s.name) like 'предварительный осмотр несовершеннолетних%'
when  $3 in(21, 22, 23) then lower(s.name) like 'периодические осмотры несовершеннолетних%' or  lower(s.name) like 'периодический осмотр несовершеннолетних%'
when $3 in (17) then lower(s.name) like 'профилактический медицинский осмотр%' or  lower(s.name) like 'профилактический осмотр несовершеннолетних%'
when $3 = 13  then lower(s.name) like 'диспансеризация%2%этап%' or lower(s.name) like 'диспансеризация%II%этап%'
when $3 in (4) then lower(s.name) like 'диспансеризация%' and lower(s.name) not like '%2 этап%' and lower(s.name) not like lower('%II%')
when $3 in (15) then lower(s.name) like 'диспансеризация%' and lower(s.name) not like '%2 этап%' and lower(s.name) not like lower('%II%') and lower(s.name) not like '%приемные%' and lower(s.name) not like '%под надзором%'
when $3 in (16) then lower(s.name) like 'диспансеризация%приемные%' and lower(s.name) not like '%2 этап%' and lower(s.name) not like lower('%II%') and lower(s.name) not like '%под надзором%'
when $3 in (26) then lower(s.name) like 'диспансеризация%под надзором%' and lower(s.name) not like '%2 этап%' and lower(s.name) not like lower('%II%')
When $3 = 14 then  lower(s.name) like 'профилактический медицинский осмотр (взрослые)%'
    end

AND (CASE WHEN s.gender_restriction_id IS NULL
                THEN 0
                ELSE COALESCE((SELECT gender_id from pim_individual where id =$1),0)
                END = COALESCE (s.gender_restriction_id,0))


and _age BETWEEN  COALESCE(sa.from_age,0) AND COALESCE(sa.to_age, 999)
) and exists
(SELECT 1 FROM pci_benefit b JOIN pci_benefit_type bt ON b.benefit_type_id = bt.id JOIN PCI_BENEFIT_DEFINITION bd

on bd.id = b.benefit_def_id WHERE bt.id in ( 1,5) AND bd.id IN (1,2,3,14,51,8) AND
                                                                                       b.patient_id = $1)
then _age=
(select sa.from_age
from
  md_standard s
        LEFT JOIN md_standard_patient_age sa ON sa.standard_id = s.id

where
 case when  $3 in( 18, 19, 20) then  lower(s.name) like 'предварительный осмотр несовершеннолетних%'
when  $3 in(21, 22, 23) then lower(s.name) like 'периодические осмотры несовершеннолетних%' or  lower(s.name) like 'периодический осмотр несовершеннолетних%'
when $3 in (17) then lower(s.name) like 'профилактический медицинский осмотр%' or  lower(s.name) like 'профилактический осмотр несовершеннолетних%'
when $3 = 13  then lower(s.name) like 'диспансеризация%2%этап%' or lower(s.name) like 'диспансеризация%II%этап%'
when $3 in (4) then lower(s.name) like 'диспансеризация%' and lower(s.name) not like '%2 этап%' and lower(s.name) not like lower('%II%')
when $3 in (15) then lower(s.name) like 'диспансеризация%' and lower(s.name) not like '%2 этап%' and lower(s.name) not like lower('%II%') and lower(s.name) not like '%приемные%' and lower(s.name) not like '%под надзором%'
when $3 in (16) then lower(s.name) like 'диспансеризация%приемные%' and lower(s.name) not like '%2 этап%' and lower(s.name) not like lower('%II%') and lower(s.name) not like '%под надзором%'
when $3 in (26) then lower(s.name) like 'диспансеризация%под надзором%' and lower(s.name) not like '%2 этап%' and lower(s.name) not like lower('%II%')
When $3 = 14 then  lower(s.name) like 'профилактический медицинский осмотр (взрослые)%'
    end

group by sa.from_age
order by min(@(sa.from_age - _age ))
limit 1);





end if;

return query(

SELECT p.service_type_id
        FROM md_standard s
        LEFT JOIN md_standard_patient_age sa ON sa.standard_id = s.id
        LEFT JOIN md_standard_prescription sp ON sp.standard_id = s.id
        LEFT JOIN md_prescription p ON p.id = sp.id
        WHERE s.clinic_id = $2
        AND (CASE WHEN s.gender_restriction_id IS NULL
                THEN 0
                ELSE COALESCE((SELECT gender_id from pim_individual where id =$1),0)
                END = COALESCE (s.gender_restriction_id,0)
        AND
    _age

 BETWEEN  COALESCE(sa.from_age,0) AND COALESCE(sa.to_age, 999)


and case when  $3 in( 18, 19, 20) then  lower(s.name) like 'предварительный осмотр несовершеннолетних%'
when  $3 in(21, 22, 23) then lower(s.name) like 'периодические осмотры несовершеннолетних%' or  lower(s.name) like 'периодический осмотр несовершеннолетних%'
when $3 in (17) then lower(s.name) like 'профилактический медицинский осмотр%' or  lower(s.name) like 'профилактический осмотр несовершеннолетних%'
when $3 = 13  then lower(s.name) like 'диспансеризация%2%этап%' or lower(s.name) like 'диспансеризация%II%этап%'
when $3 in (4) then lower(s.name) like 'диспансеризация%' and lower(s.name) not like '%2 этап%' and lower(s.name) not like lower('%II%')
when $3 in (15) then lower(s.name) like 'диспансеризация%' and lower(s.name) not like '%2 этап%' and lower(s.name) not like lower('%II%') and lower(s.name) not like '%приемные%' and lower(s.name) not like '%под надзором%'
when $3 in (16) then lower(s.name) like 'диспансеризация%приемные%' and lower(s.name) not like '%2 этап%' and lower(s.name) not like lower('%II%') and lower(s.name) not like '%под надзором%'
when $3 in (26) then lower(s.name) like 'диспансеризация%под надзором%' and lower(s.name) not like '%2 этап%' and lower(s.name) not like lower('%II%')
When $3 = 14 then  lower(s.name) like 'профилактический медицинский осмотр (взрослые)%'
    end


     ));
--and case when (SELECT date_part('year',age(birth_dt)) from pim_individual where id =$1)>17 and ($3 = 14)   then s.name = 'Профилактический медицинский осмотр (взрослые)' end

END;
 $BODY$
  LANGUAGE 'plpgsql' IMMUTABLE STRICT  COST 100
 ROWS 1000
;

ALTER FUNCTION "typing"."finder_get_service_type_id_dispensary_test"("patient_id" int4, "org_id" int4, "goal_id" int4) OWNER TO "app_group_master";