,(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_common(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_conditions IN VARCHAR2

,p_sortfield IN VARCHAR2

,p_status IN NUMBER

,p_lufilter IN NUMBER

,p_rc IN OUT pkg_global.ref_cursor_type) AS

p_cmd varchar2(32000) := '';

p_cmd_num 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(1000) := '';

p_cmd_iinum varchar2(1000) := '';

BEGIN

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 p_polser is not null or p_polnum is not null then

p_cmd_police := ' and EXISTS (SELECT keyid

FROM police

WHERE patientid = p.keyid

AND (UPPER(ser) like UPPER( nvl('''||p_polser||''','''') ) OR trim( nvl('''||p_polser||''','''') ) IS NULL)

AND (UPPER(code) like UPPER( nvl('''||p_polnum||''','''') ) OR trim( nvl('''||p_polnum||''','''') ) IS NULL)

) ';

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 = nvl('''||p_histnum||''','''' ) )';

end if;

if p_phone is not null then

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

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

OR REGEXP_REPLACE (p.relphone, ''[^[:digit:]]'' ) like ''%'' ||REGEXP_REPLACE (TO_CHAR(nvl('''||p_phone||''','''')),''[^[:digit:]]'' ) || ''%''

)';

end if;

/* AND (p.snils = nvl('''||p_iinum||''','''') OR nvl('''||p_iinum||''','''') IS NULL OR nvl('''||p_iinum||''','''') = -1)*/

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_lufilter = 1 then /* < 18 лет */

p_cmd_filter := ' and Floor(Months_Between(sysdate, p.birthdate) / 12) < 18 ';

elsif p_lufilter = 2 then /* >= 18 лет*/

p_cmd_filter := ' and Floor(Months_Between(sysdate, p.birthdate) / 12) >= 18 ';

elsif p_lufilter = 3 then /* госпитализированные */

p_cmd_filter := ' and exists (select v.keyid from visit v where v.patientid = p.keyid and v.vistype = 101) ';

end if;

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) 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

,NVL(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 <= nvl('''||p_rownum||''','''') OR nvl('''||p_rownum||''','''') IS NULL)

AND ROWNUM < 101 '

|| p_cmd_num

|| p_cmd_police

|| p_cmd_typ

|| p_cmd_histnum

|| p_cmd_phone

|| p_conditions

|| p_cmd_filter

|| p_cmd_iinum

|| ' ORDER BY ' || p_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) 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_num

|| p_cmd_police

|| p_cmd_typ

|| p_cmd_histnum

|| p_cmd_phone

|| p_conditions

|| p_cmd_filter

|| p_cmd_iinum

||'

UNION all

SELECT 0 AS keyid

,pa.snils AS 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

,'' '' AS note

,0 AS agr

,'' '' AS polser

,'' '' 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_conditions

||'

) t

WHERE ROWNUM <= NVL( nvl('''||p_rownum||''','''') ,1000)

ORDER BY '|| p_sortfield;

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

p_cmd := 'SELECT 0 AS keyid

,at.snils AS 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

(nvl('''||p_num||''','''') < 0

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

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

OR nvl('''||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 (ROWNUM <= nvl('''||p_rownum||''','''') OR nvl('''||p_rownum||''','''') IS NULL)

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_conditions||'

ORDER BY ' || p_sortfield;

END IF;

dbms_output.put_line(p_cmd);

OPEN p_rc FOR p_cmd;

end patient_search_common;