,(SELECT text

FROM lu

WHERE tag = 5

AND lcode = (SELECT finance

FROM agr

WHERE agr.companyid = (SELECT company_code

FROM v_patient_attach va

WHERE pin = va.pin

AND ROWNUM = 1))

AND ROWNUM = 1) AS finance

------------------------

PROCEDURE patient_search_common2(p_keyid IN NUMBER

,p_num IN NUMBER

,p_iinum IN VARCHAR2

,p_rownum IN NUMBER

,p_polser IN VARCHAR2

,p_polnum IN VARCHAR2

,p_typ IN VARCHAR2

,p_histnum IN VARCHAR2

,p_phone IN VARCHAR2

,p_lastname IN VARCHAR2

,p_firstname IN VARCHAR2

,p_secondname IN VARCHAR2

,p_birthdat IN DATE

,p_birthyearonly IN NUMBER

,p_showdead IN NUMBER

,p_status IN NUMBER

,p_lufilter IN NUMBER

,p_rc IN OUT pkg_global.ref_cursor_type) AS

p_cmd VARCHAR2(32000);

p_cmd_keyid VARCHAR2(1000);

p_cmd_num VARCHAR2(1000);

p_cmd_fio VARCHAR2(1000);

p_cmd_phone VARCHAR2(1000);

p_cmd_police VARCHAR2(1000);

p_cmd_typ VARCHAR2(1000);

p_cmd_histnum VARCHAR2(1000);

p_cmd_filter VARCHAR2(4000);

p_cmd_iinum VARCHAR2(1000);

p_cmd_birthdate VARCHAR2(1000);

p_cmd_death VARCHAR2(1000);

p_cmd_rownum VARCHAR2(1000);

l_sortfield VARCHAR2(1000);

BEGIN

/* Правила сортировки */

IF p_lastname IS NOT NULL THEN

l_sortfield := 'lastname, firstname, secondname';

IF p_num > 0 THEN

l_sortfield := l_sortfield || ', num';

END IF;

IF p_phone IS NOT NULL THEN

l_sortfield := l_sortfield || ', phone';

END IF;

IF p_birthdat IS NOT NULL THEN

l_sortfield := l_sortfield || ', birthdate';

END IF;

ELSIF p_num > 0 THEN

l_sortfield := 'num';

IF p_phone IS NOT NULL THEN

l_sortfield := l_sortfield || ', phone';

END IF;

IF p_birthdat IS NOT NULL THEN

l_sortfield := l_sortfield || ', birthdate';

END IF;

l_sortfield := l_sortfield || ', lastname, firstname, secondname';

ELSIF p_phone IS NOT NULL THEN

l_sortfield := 'phone';

IF p_birthdat IS NOT NULL THEN

l_sortfield := l_sortfield || ', birthdate';

END IF;

l_sortfield := l_sortfield || ', lastname, firstname, secondname';

ELSIF p_birthdat IS NOT NULL THEN

l_sortfield := 'birthdate';

ELSE

l_sortfield := '1';

END IF;

IF p_keyid IS NOT NULL THEN

p_cmd_keyid := ' AND keyid = ' || p_keyid;

END IF;

IF p_birthdat IS NOT NULL THEN

IF p_birthyearonly = 1 THEN

p_cmd_birthdate := ' AND trunc(birthdate, ''yyyy'') = gsp_char_2date(''' || gsp_date_2char(trunc(p_birthdat, 'yyyy')) || ''')';

ELSE

p_cmd_birthdate := ' AND birthdate = gsp_char_2date(''' || gsp_date_2char(p_birthdat) || ''')';

END IF;

END IF;

IF nvl(p_showdead, 0) = 0 THEN

p_cmd_death := ' AND death_dat IS NULL';

END IF;

IF p_lastname IS NOT NULL THEN

p_cmd_fio := p_cmd_fio || ' AND lastname like ''' || p_lastname || '%''';

END IF;

IF p_firstname IS NOT NULL THEN

p_cmd_fio := p_cmd_fio || ' AND firstname like ''' || p_firstname || '%''';

END IF;

IF p_secondname IS NOT NULL THEN

p_cmd_fio := p_cmd_fio || ' AND secondname like ''' || p_secondname || '%''';

END IF;

IF p_num IS NOT NULL AND p_num NOT IN (-1, 0) THEN

p_cmd_num := ' AND p.num = ' || p_num;

END IF;

IF trim(p_polser) IS NOT NULL OR trim(p_polnum) IS NOT NULL THEN

p_cmd_police := ' AND EXISTS (SELECT keyid

FROM police

WHERE patientid = p.keyid '

|| CASE WHEN trim(p_polser) IS NOT NULL THEN ' AND UPPER(ser) LIKE UPPER(''' || trim(p_polser) || ''')' END

|| CASE WHEN trim(p_polnum) IS NOT NULL THEN ' AND UPPER(code) LIKE UPPER(''' || trim(p_polnum) || ''')' END

|| ')';

END IF;

IF p_typ IS NOT NULL THEN

p_cmd_typ := ' AND EXISTS (SELECT a.keyid

FROM agr a

,agrpat ap

WHERE ap.patientid = p.keyid

AND a.keyid = ap.agrid

AND a.typ like (''' || p_typ || '''))';

END IF;

IF p_histnum IS NOT NULL THEN

p_cmd_histnum := ' AND p.keyid IN (SELECT patientid

FROM visit

WHERE vistype = 101

AND num > 0

AND num = ' || p_histnum || ')';

END IF;

IF trim(p_phone) IS NOT NULL THEN

p_cmd_phone := ' AND (REGEXP_REPLACE(p.phone, ''[^[:digit:]]'' ) LIKE ''%'' || REGEXP_REPLACE(''' || p_phone || ''', ''[^[:digit:]]'') || ''%''

OR REGEXP_REPLACE(p.cellular, ''[^[:digit:]]'' ) LIKE ''%'' || REGEXP_REPLACE(''' || p_phone || ''', ''[^[:digit:]]'') || ''%''

OR REGEXP_REPLACE(p.relphone, ''[^[:digit:]]'' ) LIKE ''%'' || REGEXP_REPLACE(''' || p_phone || ''', ''[^[:digit:]]'') || ''%'')';

END IF;

IF p_iinum IS NOT NULL AND p_iinum NOT IN ('-1', '0') THEN

p_cmd_iinum := ' AND p.snils = ''' || p_iinum || '''';

END IF;

IF p_rownum IS NOT NULL THEN

p_cmd_rownum := ' AND ROWNUM <= ' || p_rownum;

END IF;

p_cmd_filter := pkg_unique_patient.get_lufilter_cmd(p_keyid

,p_num

,p_iinum

,p_rownum

,p_polser

,p_polnum

,p_typ

,p_histnum

,p_phone

,p_lastname

,p_firstname

,p_secondname

,p_birthdat

,p_birthyearonly

,p_showdead

,p_status

,p_lufilter);

IF p_status = 0 THEN /* поиск без учета прикрепленного населения */

p_cmd :=

'SELECT p.keyid

,p.snils

,p.num

,p.typ

,p.lastname

,p.firstname

,p.secondname

,p.sex

,nvl(p.cellular, p.phone) AS phone

,p.birthdate

,p.region

,p.street

,p.house

,p.corp

,p.flat

,p.region1

,p.street1

,p.house1

,p.corp1

,p.flat1

,p.pin

,fn_get_agr_whopays_text_only(p.agrid) AS finance

,fn_pat_police_info(p.policeid) AS police

,fn_agr_text_by_id_gddc(p.agrid) AS company

,NULL AS company_code

,NULL AS enddat

,NULL AS bgndat

,p.cardplace

,gsp_get_pat_address(p.keyid, 1) AS address

,gsp_get_pat_address(p.keyid, 2) AS address_proj

,fn_get_privs(p.keyid) AS lgoty

,fn_get_lu_lcode(p.categ_lu_id) AS categ_code

,fn_get_lu_name(p.categ_lu_id) AS category

,p.categ_lu_id

,p.areanum_lu_id

,fn_get_lu_name(p.areanum_lu_id) AS areanum

,p.knows_from_note AS commentar

,p.num AS agr

,pol.ser AS polser

,pol.code AS polnum

,fn_is_police_active(pol.keyid, p.agrid) AS active_pol

,p_patient.get_pat_colorlabel(p.keyid) AS rgb

,fn_get_patattach(p.keyid) AS attach_categ

,p.death_dat

,fn_pat_doc_by_id(p.keyid) as document

,pkg_unique_color.get_pat_bdate_colorlabel(p.keyid) as rgb1

,pkg_unique_icons.patient_search_common(p.keyid) as custom_icons

FROM patient p

,police pol

WHERE p.num >= 0

AND p.policeid = pol.keyid (+)

AND ROWNUM < 101 '

|| p_cmd_keyid

|| p_cmd_num

|| p_cmd_fio

|| p_cmd_birthdate

|| p_cmd_death

|| p_cmd_police

|| p_cmd_typ

|| p_cmd_histnum

|| p_cmd_phone

|| p_cmd_filter

|| p_cmd_iinum

|| p_cmd_rownum

|| ' ORDER BY ' || l_sortfield;

ELSIF p_status = 2 THEN /* поиск по картотеке + прикрепленное население */

p_cmd :=

'SELECT t.keyid

,t.snils

,t.num

,t.typ

,t.lastname

,t.firstname

,t.secondname

,t.sex

,t.phone

,t.birthdate

,t.region

,t.street

,t.house

,t.corp

,t.flat

,t.region1

,t.street1

,t.house1

,t.corp1

,t.flat1

,t.pin

,t.cardplace

,t.finance

,t.police

,t.company

,NULL AS company_code

,NULL AS enddat

,NULL AS bgndat

,gsp_get_pat_address(t.keyid, 1) AS address

,gsp_get_pat_address(t.keyid, 2) AS address_proj

,t.lgoty

,fn_get_lu_lcode(t.categ_lu_id) AS categ_code

,fn_get_lu_name(t.categ_lu_id) AS category

,t.categ_lu_id

,t.areanum_lu_id

,fn_get_lu_name(t.areanum_lu_id) AS areanum

,t.note AS commentar

,t.agr

,t.polser

,t.polnum

,t.active_pol

,t.rgb

,t.attach_categ

,t.death_dat

,t.document

,t.rgb1

,t.custom_icons

FROM (SELECT p.keyid

,p.snils

,p.num

,p.typ

,p.lastname

,p.firstname

,p.secondname

,p.sex

,nvl(p.cellular, p.phone) AS phone

,p.birthdate

,p.region

,p.street

,TO_CHAR(p.house) AS house

,TO_CHAR(p.corp) AS corp

,TO_CHAR(p.flat) AS flat

,p.region1

,p.street1

,TO_CHAR(p.house1) AS house1

,TO_CHAR(p.corp1) AS corp1

,TO_CHAR(p.flat1) AS flat1

,p.pin

,fn_get_agr_whopays_text_only(p.agrid) AS finance

,fn_pat_police_info(p.policeid) AS police

,fn_agr_text_by_id_gddc(p.agrid) AS company

,p.cardplace

,fn_get_privs(p.keyid) AS lgoty

,p.categ_lu_id

,p.areanum_lu_id

,p.note

,p.num AS agr

,pol.ser AS polser

,pol.code AS polnum

,fn_is_police_active(pol.keyid,p.agrid) AS active_pol

,p_patient.get_pat_colorlabel(p.keyid) AS rgb

,fn_get_patattach(p.keyid) AS attach_categ

,p.death_dat

,fn_pat_doc_by_id(p.keyid) as document

,pkg_unique_color.get_pat_bdate_colorlabel(p.keyid) as rgb1

,pkg_unique_icons.patient_search_common(p.keyid) as custom_icons

FROM patient p

,police pol

WHERE p.num >= 0

AND p.policeid = pol.keyid (+) '

|| p_cmd_keyid

|| p_cmd_num

|| p_cmd_fio

|| p_cmd_birthdate

|| p_cmd_death

|| p_cmd_police

|| p_cmd_typ

|| p_cmd_histnum

|| p_cmd_phone

|| p_cmd_filter

|| p_cmd_iinum

||'

UNION ALL

SELECT 0 AS keyid

,pa.snils

,0 AS num

,NULL AS typ

,pa.lastname

,pa.firstname

,pa.secondname

,pa.sex

,NULL AS phone

,pa.birthdate

,pa.region

,pa.street

,TO_CHAR(pa.house) AS house

,TO_CHAR(pa.corp) AS corp

,TO_CHAR(pa.flat) AS flat

,NULL AS region1

,NULL AS street1

,NULL AS house1

,NULL AS corp1

,NULL AS flat1

,pa.pin

--,''ОМС'' AS finance

-- new version

,(SELECT text

FROM lu

WHERE tag = 5

AND lcode = (SELECT finance

FROM agr

WHERE agr.companyid = (SELECT company_code

FROM v_patient_attach va

WHERE pin = va.pin

AND ROWNUM = 1)

AND ROWNUM = 1)) AS finance

,''Сер.:'' || pa.polser || '',№:'' || pa.polnum || '',от:'' || TRUNC(pa.bgndat) || '' до:'' || pa.enddat AS police

,nvl(pa.smo_name, (SELECT c.text FROM company c WHERE substr(c.stext, 5, 2) = to_char(pa.company_code))) AS company

,NULL AS cardplace

,NULL AS lgoty

,0 AS categ_lu_id

,0 AS areanum_lu_id

,NULL AS note

,0 AS agr

,NULL AS polser

,NULL AS polnum

,0 AS active_pol

,NULL AS rgb

,NULL AS attach_categ

,NULL AS death_dat

,NULL as document

,NULL as rgb1

,NULL as custom_icons

FROM v_patient_attach pa

WHERE (' || p_num || ' < 0

OR ' || p_num || ' IS NULL

OR ' || p_num || ' = -1

OR ' || p_num || ' = 0)

AND (nvl(''' || p_iinum || ''', '''') < 0

OR nvl(''' || p_iinum || ''', '''') IS NULL

OR nvl(''' || p_iinum || ''', '''') = -1

OR nvl(''' || p_iinum || ''', '''') = 0)

AND (pa.polser like nvl(''' || p_polser || ''','''') OR nvl(''' || p_polser || ''','''') IS NULL)

AND (pa.polnum like nvl(''' || p_polnum || ''','''') OR nvl(''' || p_polnum || ''','''') IS NULL)

AND nvl(''' || p_typ || ''', '''') IS NULL

AND nvl(''' || p_histnum || ''', '''') IS NULL

AND NOT EXISTS (SELECT keyid FROM patient WHERE patient.pin = pa.pin) '

|| p_cmd_keyid

|| p_cmd_fio

|| p_cmd_birthdate

|| p_cmd_death

|| ') t

WHERE ROWNUM <= 1000 '

|| p_cmd_rownum

|| '

ORDER BY ' || l_sortfield;

ELSIF p_status = 1 THEN /* только по базе прикрепленного населения */

p_cmd := 'SELECT 0 AS keyid

,at.snils

,NULL AS num

,NULL AS typ

,at.lastname

,at.firstname

,at.secondname

,at.sex

,NULL AS phone

,at.birthdate

,at.region

,at.street

,TO_CHAR(at.house) AS house

,TO_CHAR(at.corp) AS corp

,TO_CHAR(at.flat) AS flat

,NULL AS region1

,NULL AS street1

,NULL AS house1

,NULL AS corp1

,NULL AS flat1

,at.pin

--,''ОМС'' AS finance

-- new version

,(SELECT text

FROM lu

WHERE tag = 5

AND lcode = (SELECT finance

FROM agr

WHERE agr.companyid = (SELECT company_code

FROM v_patient_attach va

WHERE pin = va.pin

AND ROWNUM = 1))

AND ROWNUM = 1) AS finance

,''Сер.:''|| at.polser || '',№'' || at.polnum || '',от'' || TRUNC(at.bgndat) || ''до'' || at.enddat AS police

,nvl(at.smo_name, (SELECT c.text FROM company c WHERE substr(c.stext, 5, 2) = to_char(at.company_code))) AS company

,at.company_code AS company_code

,NULL AS cardplace

,NULL AS lgoty

,0 AS categ_lu_id

,0 AS area_lu_id

,NULL AS note

,0 AS agr

,at.polser AS polser

,at.polnum AS polnum

,at.bgndat AS bgndat

,at.enddat AS enddat

,0 AS active_pol

,NULL AS rgb

,NULL AS attach_categ

,NULL AS death_dat

,NULL as document

,NULL as rgb1

,NULL as custom_icons

FROM v_patient_attach at

WHERE (' || p_num || ' < 0

OR ' || p_num || ' IS NULL

OR ' || p_num || ' = -1

OR ' || p_num || ' = 0)

AND (nvl(''' || p_iinum || ''', '''') < 0

OR nvl(''' || p_iinum || ''', '''') IS NULL

OR nvl(''' || p_iinum || ''', '''') = -1

OR nvl(''' || p_iinum || ''', '''') = 0)

AND (at.polser like nvl(''' || p_polser || ''', '''') OR nvl(''' || p_polser || ''', '''') IS NULL)

AND (at.polnum like nvl(''' || p_polnum || ''', '''') OR nvl(''' || p_polnum || ''', '''') IS NULL)

AND nvl(''' || p_typ || ''', '''') IS NULL

AND nvl(''' || p_histnum || ''', '''') IS NULL '

|| p_cmd_fio

|| p_cmd_birthdate

|| p_cmd_death

|| p_cmd_rownum

|| '

ORDER BY ' || l_sortfield;

END IF;

-- dbms_output.put_line(p_cmd);

OPEN p_rc FOR p_cmd;

END patient_search_common2;