drop package test;
/
create or replace package test is
procedure search(keyword varchar2,op integer);
procedure view_article(keyword varchar2,fid number,cid number);
usr_query_id number;
  key varchar2(200);
root_url varchar2(200) := '/cgi-bin/yuzhu/wow';
  end;
/
show errors;
/
create or replace package body test is


procedure search(keyword varchar2,op integer) is

  cursor cresult(cid number) is select *
		from article_hitlist where conid=cid order by score desc;
  result_rec article_hitlist%ROWTYPE;
  url varchar2(200) := root_url||'/test.view_article?keyword=';
  front boolean := true;
  more boolean := false;
  ch char(1);
  

  begin
	--htp.p('keyword=###'||keyword||'###'); return;
	if ((instr(keyword,'&') = 0) and (instr(keyword,'|') = 0) and 
		(instr(keyword,'-') = 0) and (instr(keyword,',') = 0)) then
	     for n in 1 .. length(keyword) loop
		ch := substr(keyword,n,1);
		if (ch != ' ') then
			if (front) then	
				key := ch;
				front := false;
			elsif (more) then 
				more := false; 
				key := key||'&'||ch;
			else key := key||ch;
			end if;
		else 
			if (not (front or more)) then more := true; end if;
		end if;
	     end loop;
	else
		key := replace(keyword,' ');
	end if;

	if (key IS NULL) then 
		htp.p('Please type one or more keywords to start your search');
		wow.sig;
		return;
	end if;

	if (op=1) then key := '$('||key||')';
	elsif (op=2) then key := '?('||key||')';
	elsif (op=3) then key := '!('||key||')';
	end if;

	select query_id.nextval into usr_query_id from dual;
	--select userenv('sessionid') into usr_query_id from dual;
	ctx_query.contains('DEMO_POLICY',key,'QUERY_TEMP',1,usr_query_id,NULL);
	htp.p('<h1>Search Results</h1><hr>');
	htp.p('<center><table border=1><th>Score<th>ID<th>Author<th>Section');
	htp.p('<th>Date<th>Title');
	
	key := replace(key,'&','~');
   	open cresult(usr_query_id);
	loop 
		fetch cresult into result_rec;
		exit when cresult%NOTFOUND;
		htp.p('<tr><td>'||result_rec.score||
			'<td>'||htf.url(url||key||
			'&fid='||result_rec.article_id||'&cid='||usr_query_id,result_rec.article_id)||
			'<td>'||result_rec.author||'<td>'||result_rec.section);
		htp.p('<td>'||to_char(result_rec.pub_date,'mm/dd/yy')||
			'<td><b>'||result_rec.title||'</b>');
	end loop;
	htp.p('</table></center>');
	close cresult;
	wow.sig;
  end;
	
procedure view_article(keyword varchar2,fid number,cid number) is
article_rec articles%ROWTYPE;
cursor highcur is select offset, length
                  from highlight_temp where id = cid
                  order by offset desc;
body_all long;
begin
	key := replace(keyword,'~','&');
        ctx_query.highlight(
                'DEMO_POLICY',
                fid,
                key,
                cid,
                null,
                null,   
                'highlight_temp',
                null,
                null);
	select * into article_rec from articles where article_id=fid;
	htp.p('<center><h1>'||article_rec.title||'</h1></center>');
	htp.p('<center><table border=1><tr><td align=right><b>Author</b>:<td>'||article_rec.author);
	htp.p('<tr><td align=right><b>Section</b>:<td>'||article_rec.section);
	htp.p('<tr><td align=right><b>Publication Date</b>:<td>'||to_char(article_rec.pub_date,'mm/dd/yy'));
	htp.p('</table></center>');
	--htp.p('<tr><td align=right><b>Text</b>:<td>');
	body_all := article_rec.text;
        for hc in highcur loop
 		body_all := substr(body_all,1,hc.offset - 1) ||
			'~'||
			substr(body_all,hc.offset,hc.length) ||
			'^'||
			substr(body_all,hc.offset + hc.length);
    	end loop;

	body_all := replace(body_all,'''','''''');

        body_all := replace(body_all,'&','&amp');
        body_all := replace(body_all,'<','&lt');
        body_all := replace(body_all,'>','&gt');
        body_all := replace(body_all,'"','&quot ');
        body_all := replace(body_all,'~','<B><font color=#ff0000>');
	body_all := replace(body_all,'^','</font></B>');

	htp.p('<hr><table><tr><td>'); 
	htp.bp(body_all);
--	htp.p(ascii(substr(body_all,1505,1)));
--	htp.p('####');
--	htp.p(ascii(substr(body_all,1506,1)));
--	htp.bp(substr(body_all,1506,2000)||'###');	
--htp.bp('###'||substr(body_all,1500,100)||'###');
	htp.p('</table>');
	wow.sig;
end;

end test;
/
show errors;
/