create or replace package pkg_unique_address is

/*Возвращает курсор с адресом + patient_id по patient_id и tag*/

FUNCTION get_pat_addr(pat_id NUMBER, tag addrs.tag%TYPE) RETURN SYS_REFCURSOR;

/*Возвращает курсор участок + адрес*/

FUNCTION get_pat_sect_addr RETURN SYS_REFCURSOR;

/*Обновление значения номера участка пациента

при наличии прикрепления данного адреса к участку*/

PROCEDURE update_sect_pat;

end pkg_unique_address;

/

create or replace package body pkg_unique_address is

/*==========================================================================*/

/*Возвращает курсор с адресом + patient_id по patient_id и tag*/

/*==========================================================================*/

FUNCTION get_pat_addr(pat_id NUMBER

, tag addrs.tag%TYPE) -- 1 - прописка

-- 2 - проживание

RETURN SYS_REFCURSOR

AS

c_return SYS_REFCURSOR;

BEGIN

OPEN c_return FOR

SELECT p.keyid

, a.region

, a.region_code

, a.area

, a.city

, a.town

, a.street

, a.house

, a.corp

, a.flat

FROM patient p

, addrs a

WHERE a.patientid = p.keyid

AND p.keyid = pat_id;

RETURN c_return;

END get_pat_addr;

/*==========================================================================*/

/*Возвращает курсор участок + адрес*/

/*==========================================================================*/

FUNCTION get_pat_sect_addr

RETURN SYS_REFCURSOR

AS

c_return SYS_REFCURSOR;

BEGIN

OPEN c_return FOR

SELECT s.text

, s.code

, sos.region

, sos.area

, sos.city

, sos.town

, sos.street_name

, sos.house

, sos.corp

, sos.flat_from

, sos.flat_to

-- , sos.addr

FROM SECTION s

, (SELECT sad.*

--, sa.region || ' ' || sa.area || ' ' || sa.city || ' ' || sa.town || ' ' || sa.street_name || ' ' || sa.house || ' ' || sa.corp addr

FROM (select sa.keyid

, sa.sectionid said

, 1 as status

, (select k.name || ' ' || k.socr

from solution_kladr.kladr k

where k.code = rpad(substr(sa.area_code, 1, 2), 13, '0')) as region

, (select k.name || ' ' || k.socr

from solution_kladr.kladr k

where k.code = rpad(substr(sa.area_code, 1, 5), 13, '0')

and k.code <> rpad(substr(sa.area_code, 1, 2), 13, '0')) as area

, (select k.name || ' ' || k.socr

from solution_kladr.kladr k

where k.code = rpad(substr(sa.city_code, 1, 8), 13, '0')

and k.code <> rpad(substr(sa.city_code, 1, 5), 13, '0')) as city

, (select k.name || ' ' || k.socr

from solution_kladr.kladr k

where k.code = rpad(substr(sa.town_code, 1, 11), 13, '0')

and k.code <> rpad(substr(sa.town_code, 1, 8), 13, '0')) as town

, sa.street_name

, sa.house

, sa.corp

, sa.flat_from

, sa.flat_to

--, decode(trim(sa.flat_from || sa.flat_to), null, '', sa.flat_from || ' - ' || sa.flat_to) as flats

, sa.bgndat

, sa.enddat

, sa.note

from section_addr sa

where 1=1 --sa.sectionid = p_section_id

and (sa.bgndat is null or sa.bgndat <= trunc(sysdate))

and (sa.enddat is null or sa.enddat >= trunc(sysdate))

and sa.region_id is null and sa.street_id is null

union

select sa.keyid

, sa.sectionid said

, 1 as status

, (select r.text

from region r

, region a

, region t

where r.root_id = 0

and a.root_id = r.keyid

and t.root_id = a.keyid

and (r.keyid = sa.region_id or a.keyid = sa.region_id or t.keyid = sa.region_id)) as region

, (select a.text

from region r

, region a

, region t

where r.root_id = 0

and a.root_id = r.keyid

and t.root_id = a.keyid

and (r.keyid = sa.region_id or a.keyid = sa.region_id or t.keyid = sa.region_id)) as area

, (select t.text

from region r

, region a

, region t

where r.root_id = 0

and a.root_id = r.keyid

and t.root_id = a.keyid

and nvl(t.city_status, 1) = 1

and (r.keyid = sa.region_id or a.keyid = sa.region_id or t.keyid = sa.region_id)) as city

, (select t.text

from region r

, region a

, region t

where r.root_id = 0

and a.root_id = r.keyid

and t.root_id = a.keyid

and t.city_status = 2

and (r.keyid = sa.region_id or a.keyid = sa.region_id or t.keyid = sa.region_id)) as town

, sa.street_name

, sa.house

, sa.corp

, sa.flat_from

, sa.flat_to

-- , decode(trim(sa.flat_from || sa.flat_to), null, '', sa.flat_from || ' - ' || sa.flat_to) as flats

, sa.bgndat

, sa.enddat

, sa.note

from section_addr sa

where 1=1 --sa.sectionid = p_section_id

and (sa.bgndat is null or sa.bgndat <= trunc(sysdate))

and (sa.enddat is null or sa.enddat >= trunc(sysdate))

and (sa.region_id is not null or sa.street_id is not null)

order by bgndat desc nulls last, region, area, city, town) sad) sos

WHERE s.Keyid = sos.SAID;

RETURN c_return;

END get_pat_sect_addr;

/*==========================================================================*/

/*Обновление значения номера участка пациента

при наличии прикрепления данного адреса к участку*/

/*==========================================================================*/

PROCEDURE update_sect_pat

AS

BEGIN

-- нужно ли обновлять значение участка при наличии значения для пациента?

-- проверять ли наличие прикрепления данного адреса к участку?

-- осуществлять ли укрупненный анализ или по точному совпадению?

-- прописка или проживание или при отсутствии одного смотреть на другое?

NULL;

END update_sect_pat;

end pkg_unique_address;

/