***** Chem, MAILS package body *****

 is
total_visitors number(7);
score number(8);
h_id header.id%TYPE;
h_msg_date header.msg_date%TYPE;
h_subject header.subject%TYPE;
h_sender header.sender%TYPE;
h_bytes header.bytes%TYPE;
up int := 0; -- ~1: by header; ~2: by date; ~3: by subject; ~4 by sender; ~5: by people
session_id number(6);
key varchar2(200);
usr_query_id number;
 procedure listfolders(action_cgi in varchar2) is
   total_folder number := 0;
 begin
	select count(*) into total_folder from folder;
	htp.p(htf.line);
	htp.p('There are total <b>'||total_folder||
	'</b> folders in your mailbox. Which folder would you like to choose ?'||htf.para);
	htp.formOpen(cgi_bin||'MAILS.'||action_cgi);
	list_folder(12);
 end;
 procedure list_folder(m integer) is
   total number := 0;
   n integer := 1;
   cursor cfolder is select id,name,n_msg from folder order by name;
 begin
	select sum(n_msg),count(*) into total,n from folder;
	if (n > m) then n := m; end if;
	htp.p('<select name="fid" size="'||n||'">');
	htp.p('<option value="0" selected> All folders ('||total||')');
	for folder in cfolder loop
	   htp.p('<option value="'||folder.id||'">'||folder.name||' ('||folder.n_msg||'
)');
	end loop;
	htp.p(htf.formSelectClose);
 end;
 procedure list_folder1(m integer) is
   n integer := 1;
   cursor cfolder is select id,name,n_msg from folder order by name;
 begin
	select n_folder into n from mailbox;
	if (n > m) then n := m; end if;
	htp.p('<select name="fid" size="'||n||'">');
	for folder in cfolder loop
	   htp.p('<option value="'||folder.id||'">'||folder.name||' ('||folder.n_msg||'
)');
	end loop;
	htp.p(htf.formSelectClose);
 end;
/* with multiple choice */
 procedure list_folder2(m integer) is
   n integer := 1;
   total number := 0;
   cursor cfolder is select id,name,n_msg from folder order by name;
 begin
	select sum(n_msg),count(*) into total,n from folder;
	if (n > m) then n := m; end if;
	htp.p('<select name="cfid" size="'||n||'" multiple>');
	for folder in cfolder loop
	   htp.p('<option value="'||folder.id||'">'||folder.name||' ('||folder.n_msg||'
)');
	end loop;
	htp.p(htf.formSelectClose);
 end;
  procedure submit is
	begin
	htp.p('Max hits:<select name="hit">');
	htp.p('<option value="0">all');
	htp.p('<option value="10">10<option value="25">25');
 	htp.p('<option value="50" selected>50<option value="100">100');
	htp.p('<option value="500">500<option value="1000">1000');
	htp.p(htf.formSelectClose||htf.formDo('Submit'));
	end;
 procedure showheader is
    total_folder number(5);
    begin
    	button;
    	htp.htitle('Query By Mail Header');
    	select n_folder into total_folder from mailbox;
    	htp.p(htf.line);
	htp.p('There are total <b>'||total_folder||
	'</b> folders in your mailbox. Which folder you would like to choose ?'||htf.para);
--	htp.formOpen(cgi_bin||'MAILS2.listheader');
	htp.formOpen(cgi_bin||'MAILS.frame_listheader');
	list_folder1(22);
    	htp.p(htf.para);
    	htp.p('Sort By<select name="op">');
   	htp.p('<option value="1">thread');
	htp.p('<option value="2">date');
	htp.p('<option value="3">subject');
	htp.p('<option value="4">author');
	htp.p(htf.formSelectClose);
	htp.p('Frame<select name="frame">');
	htp.p('<option value="1">Yes');
	htp.p('<option value="0">No');
	htp.p(htf.formSelectClose);
    	submit;
    	htp.p(htf.formClose);
    wow.sig;
   end;
 procedure frame_listheader(fid number,op integer,frame integer,hit number) is
    begin
	if (frame = 1) then
	    htp.p('<frameset rows="50%,50%">');
	htp.p('<frame src="'||cgi_bin||'MAILS2.listheader?fid='||fid||'&op='||op||'&hit=50"');
		htp.p(' name="left">');
		htp.p('<frame src="'||cgi_bin||'MAILS.frame_prompt" name="right">');
		htp.p('</frameset>');
		htp.p('<noframes> If your web browser does not support <b>Frame</b>,<br>');
		htp.p('go back and choose Frame: No option.</noframes>');
		htp.p('</html>');
	else
		MAILS2.listheader(fid,op,hit);
	end if;
    end;
procedure frame_prompt is
begin
htp.p('<center>Article Text Frame');
htp.p('<p>It may take a while to load the buffer, please be patient ... </center></html>');
end;
procedure header_list(score number,user_query_id in number,id number,
	 	      bytes number,sender varchar2,subject in out varchar2,
		      msg_date date,word varchar2) is
	h_name varchar2(100);
	h_email varchar2(100);
	begin
		subject := replace(subject,'&','&amp');
		subject := replace(subject,'<','&lt');
		subject := replace(subject,'>','&gt');
		subject := replace(subject,'"','&quot');
		if (replace(subject,' ') IS NULL) then
			subject := '(no subject)';
		end if;
		SELECT name,email into h_name,h_email from people where email=sender;
		if (h_name IS NULL) then
			h_name := h_email;
		end if;
                htp.p('<tr><td align="center">');
                htp.p(score||'</td>');
                htp.p('<td><font size="2">');
		htp.p(htf.url_right(cgi_bin||'MAILS.showbody?bid='||id||'&word='||replace(word
,'&','~')||'&cid='||user_query_id,htf.bold(subject)));
                htp.p('</font></td>');
		htp.p('<td><font>'||substr(h_name,1,25));
                htp.p('</font></td><td><font><i>'||
                        to_char(msg_date,'Mon dd yy hh24:mi')||
                        '</i></font></td><td align="center">'||
                        to_char(bytes/1024.,'FM99D9')||'</font></td><tr>');
/*
	htp.p(' - '||htf.bold(h_name)||' - <i>'||to_char(msg_date,'Dy, dd Mon yy hh24:mi')||
			'</i> ('||to_char(bytes/1024.,'FM99D9')||'K)');
  exception
	when NO_DATA_FOUND then
		htp.p('<li>'||htf.url_right(cgi_bin||'MAILS.showbody?bid='||id||'&word='||
replace(word,'&','~')||'&cid='||user_query_id,htf.bold(subject)));
	htp.p(' - '||htf.bold(sender)||' - <i>'||to_char(msg_date,'Dy, dd Mon yy hh24:mi')||
			'</i> ('||to_char(bytes/1024.,'FM99D9')||'K)');		*/
	end;
   procedure showbody(bid in number,word in varchar2,cid in number) is
	fid header.id%TYPE;
	fpath varchar2(200);
	gname varchar2(200);
	n header.id%TYPE;
	i header.id%TYPE;
	j header.id%TYPE;
	k header.id%TYPE;
	body_all long;
	tmp long;
	out_string varchar2(254);
	h_msg_date date;
	h_cc varchar2(1000);
	h_receiver varchar2(2000);
	h_replied_id varchar2(200);
        replied_id varchar2(200);
	creply_id varchar2(200);
	h_msg_id varchar2(200);
	h_name varchar2(100);
	h_email varchar2(100);
	url varchar2(254);
	n1 header.id%TYPE;
	m header.id%TYPE;
	highlight varchar2(200);
	in_url integer;
	rid header.id%TYPE;
	cursor reply(low_id number,high_id number,repid char) is
		select rowid from header where (id between low_id and high_id)
		and (msg_id = repid);
	cursor highcur(sid  number) is select offset, length
                  from highlight_temp where id = sid
                  order by offset desc;
	cursor creply(low_id number,high_id number,repid varchar2) is
                select id,sender from header where (id between low_id and high_id)
                and (replied_id = repid) order by msg_date asc;
        cursor cname(csender varchar2) is SELECT name from people where email=csender;
	last_word boolean := false;
	a varchar2(100);
	body_rec body%ROWTYPE;
	client int;
	next_id header.id%TYPE;
	next_subject header.subject%TYPE;
	prev_id header.id%TYPE;
	prev_subject header.subject%TYPE;
	min_id header.id%TYPE;
	user_query_id number(6);
	hword varchar2(200);
	wword varchar2(200);
	urowid ROWID;
	lln integer;
	begin
	htp.p('<title>article '||bid||'</title>');
	fid := floor(bid/maxmsg) + 1;
	select name,path into gname,fpath from folder where id = fid;
	select * into body_rec from body where id=bid;
        body_all := body_rec.msg_body;
	session_id := cid;
	user_query_id := cid;
        highlight := word;
       if (word IS NOT NULL) then
                ---- highlight keyword in hightlight
                key := replace(word,'~','&');
              ctx_query.highlight(
                'CHEM_POLICY',
                body_rec.id,
                key,
                session_id,
                null,
                null,
                'highlight_temp',
                null,
                null);
         for hc in highcur(session_id) 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;
        end if;
	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,'&ltbr&gt','');
	body_all := replace(body_all,'\$','$'); --- left by the perl parser
	-- highlighting
        body_all := replace(body_all,'^^','<B><font color=#ff0000>');
        body_all := replace(body_all,'~~','</font></B>');
	i := 1;
	m := instr(body_all,'http://',i,1);
	in_url := 0;
	while ( m != 0) loop
			tmp := substr(body_all,m);
			k := instr(tmp,' ');
			--- for both &gt and &quot
			n1 := instr(tmp,'&');
			if ((k = 0) or ((n1 != 0) and (n1 < k)) ) then k := n1; end if;
			n1 := instr(tmp,')');
			if ((k = 0) or ((n1 != 0) and (n1 < k)) ) then k := n1; end if;
			n1 := instr(tmp,',');
			if ((k = 0) or ((n1 != 0) and (n1 < k)) ) then k := n1; end if;
			n1 := instr(tmp,'|');
			if ((k = 0) or ((n1 != 0) and (n1 < k)) ) then k := n1; end if;
			n1 := instr(tmp,';');
			if ((k = 0) or ((n1 != 0) and (n1 < k)) ) then k := n1; end if;
			n1 := instr(tmp,'''');
			if ((k = 0) or ((n1 != 0) and (n1 < k)) ) then k := n1; end if;
			n1 := instr(tmp,chr(10));
			if ((k = 0) or ((n1 != 0) and (n1 < k)) ) then k := n1; end if;
			if (k != 0) then
				url := substr(tmp,1,k-1);
				body_all := substr(body_all,1,m-1)||htf.url(url,url)||substr(tmp,k);
				i := m + 2*length(url)+11;
				if (instr(url,word) != 0) then in_url := 1; end if;
			else
				i := m + 5;
			end if;
			m := instr(body_all,'http://',i,1);
	end loop;
--- hightlight .and. words
   	SELECT msg_date,subject,sender,receiver,cc,msg_id,replied_id into
		h_msg_date,h_subject,h_sender,h_receiver,h_cc,h_msg_id,h_replied_id
		from header where id = bid;
	SELECT name,email into h_name,h_email from people where email=h_sender;
   	htp.p('<body bgcolor="#FFFFFF" background="/images/notebook.gif"><BLOCKQUOTE>');
	h_receiver := substr(h_receiver,1,150);
 	h_receiver := replace(h_receiver,'<','&lt');
        h_receiver := replace(h_receiver,'>','&gt');
        h_receiver := replace(h_receiver,'\$','$');
	if (h_cc IS NOT NULL) then
	h_cc := replace(h_cc,'<','&lt');
        h_cc := replace(h_cc,'>','&gt');
        h_cc := replace(h_cc,'\$','$');
        end if;
	 h_subject := substr(h_subject,1,150);
	h_subject := replace(h_subject,'\$','$');
	h_subject := replace(h_subject,'&','&amp');
	h_subject := replace(h_subject,'<','&lt');
	h_subject := replace(h_subject,'>','&gt');
	h_subject := replace(h_subject,'"','&quot');
	htp.p('<center><font size="+2">');
        m := (fid-1)*maxmsg + 1;
        n := m + maxmsg - 1;
        if (h_replied_id IS NULL) then
                htp.p(h_subject);
        else
                h_replied_id := replace(h_replied_id,chr(10));
                open reply(m,n,h_replied_id);
                fetch reply into urowid;
                if (reply%NOTFOUND) then
                        htp.p(h_subject);
                else
                   htp.p(htf.url(cgi_bin||'MAILS5.showbody?rid='||urowid,h_subject));
                end if;
                close reply;
        end if;
        htp.p('</font></center>');
        if (h_name IS NULL) then
                h_name := h_email;
        end if;
        htp.p('<table border=0>');
        htp.p('<tr><td align="right">From:</td>');
        htp.p('<td>'||htf.url(cgi_bin||'MAILS5.by_people?uid='||
                h_email,h_email)||' ('||h_name||')</td></tr>');
        htp.p('<tr><td align="right">To:</td>');
        htp.p('<td>'||h_receiver||'</td></tr>');
        if (h_cc IS NOT NULL) then
                htp.p('<tr><td align="right">Cc:</td>');
                htp.p('<td>'||h_cc||'</td></tr>');
        end if;
        htp.p('<tr><td align="right">Date:</td>');
        htp.p('<td>'||to_char(h_msg_date,'Mon dd yyyy hh24:mi')||'</td></tr>');
        open creply(m,n,h_msg_id);
      fetch creply into creply_id,h_sender;
        if (creply%FOUND) then
                htp.p('<tr><td align="right">Replied-by:</td><td>');
                loop
                        open cname(h_sender);
                        fetch cname into h_name;
                        if (cname%NOTFOUND) then
                                h_name := h_sender;
                        end if;
                        close cname;
                        if (creply%ROWCOUNT > 1) then
                                htp.p(', ');
                        end if;
                        htp.p(htf.url_right(cgi_bin||'MAILS.showbody?rid='||creply_id||
			'&word='||replace(word,'&','~')||'&cid='||cid,h_name));
                        fetch creply into creply_id,h_name;
                        exit when creply%NOTFOUND;
                end loop;
                close creply;
                htp.p('</td></tr>');
        end if;
        htp.p('</table>'||htf.line||'<pre>');
        -- highlight keywords
       /* if (highlight IS NOT NULL) then
                loop
                        k := instr(highlight,' ');
                        exit when k=0;
                        wword := substr(highlight,1,k-1);
                        highlight := substr(highlight,k+1);
                        hword := '<font color="#ff000">'||wword||'</font>';
                        body_all := replace(body_all,wword,hword);
                        body_all := replace(body_all,upper(wword),hword);
                        body_all := replace(body_all,INITCAP(wword),hword);
                end loop;
                hword := '<font color="#ff000">'||highlight||'</font>';
                body_all := replace(body_all,highlight,hword);
              body_all := replace(body_all,upper(highlight),hword);
                body_all := replace(body_all,INITCAP(highlight),hword);
        end if;
	*/
       -- htp.bp(body_all);
lln := length(body_all);
        i := 1;
        while ( (i+253) <= lln) loop
                out_string := substr(body_all,i,254);
                k := instr(out_string,chr(10),-1,1);
                if (k = 0) then
                        htp.p(out_string);
                        i := i + 254;
                else
                        htp.p(substr(out_string,1,k-1));
                        i := i + k;
                end if;
        end loop;
        if (i <= lln) then
                htp.p(substr(body_all,i));
        end if;
        htp.p('</pre><center>');
        htp.p(htf.line);
        if (instr(gname,'Chat-') != 0) then /* Chat board */
           htp.p(htf.url(cgi_bin||'MAILS3.index_chat1?email=&host=&name=&uid=&subject=Re:+'||
           replace(h_subject,' ','+')||'&body=','<IMG BORDER=0 SRC="/images/reply.gif">'));
           htp.p(htf.url(cgi_bin1||'index_chat','<IMG ALT="Post" BORDER=0 SRC="/images/post.gif">'));
        else /* MH */
                h_subject := 'Re: '||h_subject;
                htp.p('<a href="mailto:'||h_email||'?subject='||h_subject||'">');
                htp.p('<IMG BORDER=0 SRC="/images/reply.gif"></A>');
                htp.p('<a href="mailto:'||h_receiver||'?subject='||h_subject||'">');
                htp.p('<IMG BORDER=0 SRC="/images/preply.gif"></A>');
                if (h_cc IS NOT NULL) then
                        htp.p('<a href="mailto:'||h_receiver||','||h_email||','||
                         h_cc||'?subject='||h_subject||'">');
                else
                        htp.p('<a href="mailto:'||h_receiver||','||h_email||
                        '?subject='||h_subject||'">');
                end if;
                htp.p('<IMG BORDER=0 SRC="/images/mpreply.gif"> </A>');
                htp.p('<a href="mailto:">');
                htp.p('<IMG BORDER=0 SRC="/images/post.gif"></a>');
        end if;
        htp.p('<HR WIDTH="50%" ALIGN="CENTER">');
        htp.p('</center></BLOCKQUOTE>');
        wow.sig;
  exception
        when NO_DATA_FOUND then
                htp.p(htf.line);
                wow.sig;
end;
 procedure index_subject is
   cursor csubject is select word from subject order by lower(word);
	begin
	button;
	htp.htitle('Query By Keyword');
	htp.p(htf.line);
	--htp.formOpen(cgi_bin||'MAILS.by_subject');
	htp.formOpen(cgi_bin||'MAILS.by_fsubject');
	htp.p('Enter your keyword(s): <input name="typein" size="33" value=""> ');
	htp.p(htf.formDo||' '||htf.formUndo);
	htp.p('<h2>Search Options</h2>');
	htp.ulistOpen;
		htp.p('<li>Max hits:<select name="hit">');
		htp.p('<option value="0">all');
		htp.p('<option value="10" selected>10<option value="25" >25');
	 	htp.p('<option value="50" >50<option value="100">100');
		htp.p('<option value="500">500<option value="1000">1000');
		htp.p(htf.formSelectClose);
		htp.p(' Frame:<select name="frame">');
                htp.p('<option value="1" selected>Yes');
                htp.p('<option value="0">No');
                htp.p(htf.formSelectClose);
		htp.p('<input type="radio" name="full" value="1" checked> All folders');
		htp.p('<li><input type="radio" name="full" value="0" > In folder(s):');
		list_folder2(8);
		htp.p('<input type="hidden" name="cfid" value="0"><br>');
		--htp.p('<input type="radio" name="full" value="1" > All folders');
--		sysdate_est := new_time(sysdate,'EST','EST');
		sysdate_est := sysdate;
		htp.p('<li>Time domain: <select name="day" size="1">');
		htp.p('<option value="0" selected>All days');
		htp.p('<option value="1">Today''s '||
		  to_char(sysdate_est,'"("Dy,Mon dd yy")"'));
		htp.p('<option value="3">This week''s '||
		  to_char(sysdate_est+1-(to_char(sysdate_est,'D')),'"("Dy,Mon dd')||' : '||
		  to_char(sysdate_est,'Dy,Mon dd yy")"'));
		htp.p('<option value="5">This month''s '||
	  	  to_char(sysdate_est,'"("Month,yy")"'));
		htp.p('<option value="7">This year''s '||
		  to_char(sysdate_est,'"("yyyy")"'));
		htp.p('<option value="2">Since yesterday '||
		  to_char((sysdate_est-1),'"("Dy,Mon dd yy")"'));
		htp.p('<option value="4">Since last week '||
		  to_char(sysdate_est-6-(to_char(sysdate_est,'D')),'"("Dy,Mon dd')||' : '||
		  to_char(sysdate_est,'Dy,Mon dd yy")"'));
 		htp.p('<option value="6">Since last month '||
		  to_char(add_months(sysdate_est,-1),'"("Month')||' : '||
		  to_char(sysdate_est,'Month,yy")"'));
		htp.p('<option value="8">Since last year '||
		  to_char(sysdate_est-365,'"("yyyy')||' : '||
		  to_char(sysdate_est,'yyyy")"'));
		htp.p('<option value="9">Since 3 months ago '||
		  to_char(add_months(sysdate_est,-3),'"("Month')||' : '||
		  to_char(sysdate_est,'Month,yy")"'));
		htp.p('<option value="10">Since 6 months ago '||
		  to_char(add_months(sysdate_est,-6),'"("Month')||' : '||
		  to_char(sysdate_est,'Month,yy")"'));
		htp.p(htf.formSelectClose);
	HTP.ulistClose;
	htp.p(htf.formClose);
	wow.sig;
        end;
procedure by_fsubject(typein in varchar2,hit number,full integer,cfid varchar2,
		frame integer,day integer) is
wordd varchar2(200);
begin
       wordd := replace(typein,'&','~');
       if (frame = 1) then
               htp.p('<frameset rows="40%,60%">');
                htp.p('<frame src="'||cgi_bin||'MAILS.by_subject?typein='||
			replace(wordd,' ','+')||'&hit='||hit);
		htp.p('&full='||full||'&cfid='||replace(cfid,'&','&cfid='));
		htp.p('&day='||day||'"');
                htp.p(' name="left">');
                htp.p('<frame src="'||cgi_bin||'MAILS.frame_prompt" name="right">');
                htp.p('</frameset>');
                htp.p('<noframes> If your web browser does not support <b>Frame</b>,<br>');
                htp.p('go back and choose Frame: No option.</noframes>');
                htp.p('</html>');
        else
                by_subject(wordd,hit,full,cfid,day);
        end if;
    end;
procedure by_subject(typein in varchar2,hit number,full integer,cfid varchar2,
		day integer) is
	TYPE wordstype IS TABLE
		OF varchar2(20) INDEX BY BINARY_INTEGER;
	word_tab wordstype;
   	cursor cheader(wordin varchar2,lowerid number,upperid number,sday date) is
		SELECT id,msg_date,subject,sender,bytes
		from header where (id between lowerid and upperid) and (msg_date >= sday)
		and (lower(subject) like wordin) order by id desc;
    	cursor cbody(cid number,lowerid number,upperid number,sday date) is
		SELECT body_hitlist.id,body_hitlist.score from body_hitlist,header
		where (header.id between lowerid and upperid) and
		(msg_date >= sday) and (header.id=body_hitlist.id) and (body_hitlist.conid=cid)
		 order by body_hitlist.score desc;
    	cursor cbody_all(cid number) is
		select id,score from body_hitlist
		where conid = cid
	 	order by score desc;
    	cursor cheader1(wordin varchar2,lowerid number,upperid number,sday date) is
		select id,msg_date,subject,sender,bytes
		from header where (id between lowerid and upperid) and (msg_date >= sday)
		and (subject like wordin) order by id desc;
	cursor csubject(wordin varchar2) is select word from subject where word=wordin;
	b_header boolean := false;
	b_body boolean := true;
	all_folder boolean := false;
	upper_id header.id%TYPE := 0;
	lower_id header.id%TYPE := 0;
	f_id header.id%TYPE;
	typeinword varchar2(40);
	wordshow varchar2(200);
	word_junk varchar2(100);
	total header.id%TYPE := 0;
	words varchar2(200);
	i integer;
	k integer;
	ii BINARY_INTEGER;
	w_count BINARY_INTEGER;
	last_word boolean := false;
	tmp varchar2(200);
	body_all long;
	body_rec body%ROWTYPE;
	limit header.id%TYPE;
	id_list long :='';
	tot header.id%TYPE := 0;
	time_domain date;
	time_show varchar2(50);
	fname folder.name%TYPE;
	fid folder.id%TYPE;
	cfid1 varchar2(100);
	nn integer;
        n number(8);
	m number(8);
	ch char(1);
        end_num number(8);
 front boolean := true;
  more boolean := false;
query_word varchar2(200);
	begin
	htp.p('<title>results of query by subject</title>');
	button;
	tmp := replace(typein,' ','');
        tmp := replace(tmp,'~','&');
	if (tmp IS NULL) then
	   htp.p('<p>You must choose a word to start the query. Try again.');
	   wow.sig;
	   return;
	else
	   words :=tmp;
	end if;
	if ((instr(tmp,'&') = 0) and (instr(tmp,'|') = 0) and
                (instr(tmp,'-') = 0) and (instr(tmp,',') = 0)) then
             for n in 1 .. length(typein) loop
                ch := substr(typein,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(tmp,' ');
        end if;
--	highlight := key;
        tmp := replace(key,'&','~');
        query_word := '('||key ||'):1000';
        select query_id.nextval into usr_query_id from dual;
        ctx_query.contains('CHEM_POLICY',query_word,'QUERY_TEMP',1,usr_query_id,NULL);
	sysdate_est := new_time(sysdate,'EST','EST');
	if (day = 0) then /* any day */
		time_domain := to_date('011980','mmyyyy');
	elsif (day = 1) then /* today */
		time_domain := to_date(to_char(sysdate_est,'mmddyyyy'),'mmddyyyy');
	elsif (day = 2) then /* yesterday */
		time_domain := to_date(to_char(sysdate_est-1,'mmddyyyy'),'mmddyyyy');
	elsif (day = 3) then /* this week */
		time_domain := to_date(to_char(sysdate_est+1-(to_char(sysdate_est,'D')),
		'mmddyyyy'),'mmddyyyy');
	elsif (day = 4) then /* last week */
		time_domain := to_date(to_char(sysdate_est-6-(to_char(sysdate_est,'D')),
		'mmddyyyy'),'mmddyyyy');
	elsif (day = 5) then /* this month */
		time_domain := to_date(to_char(sysdate_est,'mmyyyy'),'mmyyyy');
	elsif (day = 6) then /* last month */
		time_domain := to_date(to_char(add_months(sysdate_est,-1),'mmyyyy'),'mmyyyy');
	elsif (day = 7) then /* this year */
		time_domain := to_date('01'||to_char(sysdate_est,'yyyy'),'mmyyyy');
	elsif (day = 8) then /* last year */
		time_domain := to_date('01'||to_char(sysdate_est-365,'yyyy'),'mmyyyy');
	elsif (day = 9) then /* 3 months ago */
		time_domain := to_date(to_char(add_months(sysdate_est,-3),'mmyyyy'),'mmyyyy');
	elsif (day = 10) then /* 6 months ago */
		time_domain := to_date(to_char(add_months(sysdate_est,-6),'mmyyyy'),'mmyyyy');
	end if;
	time_show := to_char(time_domain,'Month dd, yyyy Day');
	if (full = 0) then
		if (instr(cfid,'&',1,1)!=0) then
			cfid1 := replace(cfid,'&0',NULL);
		else
			cfid1 := '0';
		end if;
	else
		cfid1 := '0';
	end if;
n := 0;
tot := 0;
loop
                nn := instr(cfid1,'&',1,1);
                if (nn = 0) then
                        fid := cfid1;
                else
                        fid := substr(cfid1,1,nn-1);
                        cfid1 := substr(cfid1,nn+1);
                end if;
        if (fid = 0) then
                select max(id)*maxmsg into upper_id from folder;
                lower_id := 1;
                fname := 'all folders';
                if (day=0) then
			all_folder := true;
		end if;
        else
                upper_id := fid*maxmsg;
                lower_id := upper_id - maxmsg + 1;
		fname := 'multi_folders';
                select n_msg into m from folder where id=fid;
                tot := tot + m;
        end if;
        if (all_folder) then
	   open cbody_all(usr_query_id);
           loop
           	fetch cbody_all into h_id,score;
           	exit when cbody_all%NOTFOUND;
		total := total + 1;
           end loop;
        else
	   open cbody(usr_query_id,lower_id,upper_id,time_domain);
           loop
           	fetch cbody into h_id,score;
           	exit when cbody%NOTFOUND;
                total := total + 1;
           end loop;
        end if;
    if (all_folder) then
       close cbody_all;
    else
       close cbody;
    end if;
    n := n + total;
    total := 0;
    exit when (nn=0);
end loop;
	if (n=0) then
	   htp.p('<p>No mail body found !');
    	end if;
        total := 0;
        htp.p('<p>Key words: '||htf.bold(key)||' Folder: '||
        htf.bold(fname)||' Time domain: since '||htf.bold(time_show));
htp.p('<center><table border=1 cellpadding=0 cellspacing=0 width=100%>');
    htp.p('<tr align=center><th>Score</th><th>Subject</th><th>Sender</th><th>Date
</th><th>Size(kb)</th></tr>');
if (full = 0) then
   if (instr(cfid,'&',1,1)!=0) then
      cfid1 := replace(cfid,'&0',NULL);
   else
       cfid1 := '0';
   end if;
else
    cfid1 := '0';
end if;
	loop
		nn := instr(cfid1,'&',1,1);
		if (nn = 0) then
			fid := cfid1;
		else
			fid := substr(cfid1,1,nn-1);
			cfid1 := substr(cfid1,nn+1);
		end if;
	if (fid = 0) then
		select max(id)*maxmsg into upper_id from folder;
		lower_id := 1;
		fname := 'all folders';                                                         select sum(n_msg) into tot from folder;
		if (day=0) then all_folder := true; end if;
	else
		upper_id := fid*maxmsg;
		lower_id := upper_id - maxmsg + 1;
--		select n_msg into m from folder where id=fid;
 --               tot := tot + m;
		fname := 'multi_folders';
	end if;
		if (hit = 0) then limit := 10;
		else limit := hit; end if;
	if (b_body) then
			if (all_folder) then
				open cbody_all(usr_query_id);
				fetch cbody_all into h_id,score;
		  		if (cbody_all%NOTFOUND) then
					close cbody_all;
					goto end_if2;
				end if;
			else
                           open cbody(usr_query_id,lower_id,upper_id,time_domain);
				fetch cbody into h_id,score;
                                if (cbody%NOTFOUND) then
                                        close cbody;
                                        goto end_if2;
				end if;
			end if;
		loop
			select id,msg_date,subject,sender,bytes into
			h_id,h_msg_date,h_subject,h_sender,h_bytes
			from header where id = h_id;
                        header_list(score,usr_query_id,h_id,h_bytes,h_sender,
                                h_subject,h_msg_date,tmp);
		    if  (length(id_list) < 32750 or id_list IS NULL) then
 				id_list := id_list||h_id||',';
		    end if;
		    total := total + 1;
   		    if (total >= limit) then exit; end if;
<<no_if2>>
			if (all_folder) then
				fetch cbody_all into h_id,score;
                		exit when cbody_all%NOTFOUND;
			else
				fetch cbody into h_id,score;
                		exit when cbody%NOTFOUND;
			end if;
		end loop;
	        if (all_folder) then
		    close cbody_all;
		else
		   close cbody;
		end if;
	end if;
<<end_if2>>
	exit when ((nn = 0) or (total >= limit));
  end loop;
  htp.p('<tr align=center><td>Found: <b>'||n||'</b></td><td>Keyword: <i>'
	||htf.bold(key)||'</i></td><td>Searched: <b>'||tot||
	'</b></td>'||'<td colspan="2">When: <i>'||
	to_char(sysdate,'Mon dd yy hh24:mi')||' EST</i></td></tr>');
  htp.p('</table></center>');
  if ( total = limit) and (total < n) then
     if (total + limit < n ) then
	end_num := total + limit;
     else
        end_num := n;
     end if;
     htp.p(htf.url(cgi_bin||'MAILS.more_url?key='||replace(key,'&','~')||
	'&user_id='||usr_query_id||'&seed='||total||'&hit='||limit||
	'&full='||full||'&cfid='||replace(cfid,'&','~')||'&day='||day||
	'&total='||n||'&alldoc='||tot||'&fname='||fname,
	'Next '||(total+1)||'-'||end_num)||' ( of '||n||')');
 end if;
wow.sig;
end;
procedure more_url(key varchar2, user_id number,seed number,hit number,
		   full integer,cfid varchar2,day integer,total number,
		   alldoc number,fname varchar2) is
cursor cbody(cid number,lowerid number,upperid number,sday date) is
	SELECT body_hitlist.id,body_hitlist.score
	from body_hitlist,header
	where (header.id between lowerid and upperid)
	and (msg_date >= sday) and (header.id=body_hitlist.id)
	and (body_hitlist.conid=cid)
	order by body_hitlist.score desc;
cursor cbody_all(cid number) is
	select id,score from body_hitlist
	where conid = cid
	order by score desc;
cursor cheader1(wordin varchar2,lowerid number,upperid number,sday date) is
	select id,msg_date,subject,sender,bytes
	from header where (id between lowerid and upperid)
	and (msg_date >= sday) and (subject like wordin)
	order by id desc;
cursor csubject(wordin varchar2) is
	select word from subject
	where word=wordin;
all_folder boolean := false;
upper_id header.id%TYPE := 0;
lower_id header.id%TYPE := 0;
f_id header.id%TYPE;
limit header.id%TYPE;
last_end_num number(8);
end_num number(8);
num header.id%TYPE := 0;
tot header.id%TYPE := 0;
time_domain date;
time_show varchar2(50);
--fname folder.name%TYPE;
fid folder.id%TYPE;
cfid1 varchar2(100);
nn integer;
tmp varchar2(200);
ccfid varchar2(200);
begin
   htp.p('<title>results of query by subject</title>');
   button;
   tmp := replace(key, '~', '&');
   ccfid := replace(cfid,'~','&');
   sysdate_est := new_time(sysdate,'EST','EST');
        if (day = 0) then /* any day */
                time_domain := to_date('011980','mmyyyy');
        elsif (day = 1) then /* today */
                time_domain := to_date(to_char(sysdate_est,'mmddyyyy'),'mmddyyyy');
        elsif (day = 2) then /* yesterday */
                time_domain := to_date(to_char(sysdate_est-1,'mmddyyyy'),'mmddyyyy');
        elsif (day = 3) then /* this week */
                time_domain := to_date(to_char(sysdate_est+1-(to_char(sysdate_est,'D')),
                'mmddyyyy'),'mmddyyyy');
        elsif (day = 4) then /* last week */
                time_domain := to_date(to_char(sysdate_est-6-(to_char(sysdate_est,'D')),
                'mmddyyyy'),'mmddyyyy');
        elsif (day = 5) then /* this month */
                time_domain := to_date(to_char(sysdate_est,'mmyyyy'),'mmyyyy');
        elsif (day = 6) then /* last month */
                time_domain := to_date(to_char(add_months(sysdate_est,-1),'mmyyyy'),
			'mmyyyy');
        elsif (day = 7) then /* this year */
                time_domain := to_date('01'||to_char(sysdate_est,'yyyy'),'mmyyyy');
        elsif (day = 8) then /* last year */
                time_domain := to_date('01'||to_char(sysdate_est-365,'yyyy'),'mmyyyy');
        elsif (day = 9) then /* 3 months ago */
                time_domain := to_date(to_char(add_months(sysdate_est,-3),'mmyyyy'),'mmyyyy');
        elsif (day = 10) then /* 6 months ago */
                time_domain := to_date(to_char(add_months(sysdate_est,-6),'mmyyyy'),'mmyyyy');
        end if;
        time_show := to_char(time_domain,'Month dd, yyyy Day');
if (full = 0) then
                if (instr(ccfid,'&',1,1)!=0) then
                        cfid1 := replace(ccfid,'&0',NULL);
                else
                        cfid1 := '0';
                end if;
        else
                cfid1 := '0';
        end if;
htp.p('<p>Key words: '||htf.bold(tmp)||' Folder: '||
htf.bold(fname)||' Time domain: since '||htf.bold(time_show));
htp.p('<center><table border=1 cellpadding=0 cellspacing=0 width=100%>');
htp.p('<tr align=center><th>Score</th><th>Subject</th><th>Sender</th><th>Date
	</th><th>Size(kb)</th></tr>');
--select sum(n_msg) into alldoc from folder;
tot := 0;
last_end_num:=seed;
loop
    nn := instr(cfid1,'&',1,1);
    if (nn = 0) then
       fid := cfid1;
    else
       fid := substr(cfid1,1,nn-1);
       cfid1 := substr(cfid1,nn+1);
    end if;
    if (fid = 0) then
       select max(id)*maxmsg into upper_id from folder;
       lower_id := 1;
 --      fname := 'all folders';
       if (day=0) then all_folder := true; end if;
    else
       upper_id := fid*maxmsg;
       lower_id := upper_id - maxmsg + 1;
       --select name into fname from folder where id=fid;                         	--fname := 'mutil_folders';
    end if;
    if (hit = 0) then limit :=10 ;
    else limit := hit; end if;
    if (all_folder) then
       open cbody_all(user_id);
       fetch cbody_all into h_id,score;
       if (cbody_all%NOTFOUND) then
          close cbody_all;
          goto mend_if2;
       end if;
    else
       open cbody(user_id,lower_id,upper_id,time_domain);
       fetch cbody into h_id,score;
       if (cbody%NOTFOUND) then
          close cbody;
          goto mend_if2;
       end if;
    end if;
    loop
       select id,msg_date,subject,sender,bytes into
       h_id,h_msg_date,h_subject,h_sender,h_bytes
       from header where id = h_id;
       if ( tot >= seed) then
		header_list(score,user_id,h_id,h_bytes,h_sender,                    			h_subject,h_msg_date,tmp);
       end if;
       tot := tot + 1;
       --if (num >= limit) then exit; end if;
       if (tot >= limit + seed) then exit; end if;
<<no_if2>>
        if (all_folder) then
            fetch cbody_all into h_id,score;
            exit when cbody_all%NOTFOUND;
        else
           fetch cbody into h_id,score;
           exit when cbody%NOTFOUND;
        end if;
     end loop;
     if (all_folder) then
        close cbody_all;
     else
        close cbody;
     end if;
<<mend_if2>>
     exit when ((nn = 0) or (tot >= limit+seed));
     --exit when ((nn = 0) or (num >= limit));
  end loop;
   htp.p('<tr align=center><td>Found: <b>'||total||'</b></td><td>Keyword: <i>'
        ||htf.bold(tmp)||'</i></td><td>Searched: <b>'||alldoc||
        '</b></td>'||'<td colspan="2">When: <i>'||
        to_char(sysdate,'Mon dd yy hh24:mi')||' EST</i></td></tr>');
  htp.p('</table></center>');
  if (tot  = limit+seed) and (tot < total) then
     if ((tot + limit) < total) then
        end_num := tot + limit;
     else
        end_num := total;
     end if;
  htp.p('<table width="100%"><tr>');
     htp.p('<td align="left" width="35%">');
     htp.p(htf.url(cgi_bin||'MAILS.more_url?key='||replace(tmp,'&','~')||
	'&user_id='||user_id||'&seed='||tot||'&hit='||limit||
	'&full='||full||'&cfid='||cfid||'&day='||day||'&total='||total
	||'&alldoc='||alldoc||'&fname='||fname,
	'Next '||(tot+1)||'-'||end_num)||' ( of '||total||')');
  end if;
  if (tot > limit) then
	htp.p('<td align="right">');
         htp.p(htf.url(cgi_bin||'MAILS.more_url?key='||replace(tmp,'&','~')||
              '&user_id='||user_id||'&seed='||(seed-hit)||'&hit='||limit||
	      '&full='||full||'&cfid='||cfid||'&day='||day||'&total='||total
		||'&alldoc='||alldoc||'&fname='||fname,
 		'Prev'||(last_end_num - hit + 1)||'-'||last_end_num)||' (of '||total||')');
end if;
  htp.p('</table>');
wow.sig;
end;
  procedure index_subject1(id_list long,keyword varchar2) is
	i integer;
	n integer;
	k integer;
	out_string varchar2(270);
	begin
	htp.p(htf.line);
	htp.p('<h2>Refine your search with the current search results</h2>');
	htp.formOpen(cgi_bin||'MAILS.by_subject1');
	htp.p('Enter your keyword(s): <input name="typein" size=35 value=""> ');
	htp.p(htf.formDo||' '||htf.formUndo);
	htp.p('<h2>Search Options</h2>');
	htp.ulistOpen;
	htp.p('<li>Max hits:<select name="hit">');
	htp.p('<option value="0">all');
	htp.p('<option value="10">10<option value="25" >25');
	 htp.p('<option value="50" selected>50<option value="100">100');
	htp.p('<option value="500">500<option value="1000">1000');
	htp.p(htf.formSelectClose);
	HTP.ulistClose;
 	htp.p('<input type="hidden" name="list" value="');
	n := length(id_list);
	i := 1;
	while ( (i+253) <= n) loop
		out_string := substr(id_list,i,254);
		k := instr(out_string,',',-1,1);
		if (k = 0) then
			htp.p(out_string);
			i := i + 254;
		else
		 	htp.p(substr(out_string,1,k));
			i := i + k;
		end if;
	end loop;
	if (i <= n) then
		htp.p(substr(id_list,i));
	end if;
	htp.p('">');
 	htp.p('<input type="hidden" name="keyword" value="'||keyword||'">');
	htp.p(htf.formClose);
        end;
procedure by_subject1(typein in varchar2,hit number,
		list long,keyword varchar2) is
	TYPE wordstype IS TABLE
		OF varchar2(20) INDEX BY BINARY_INTEGER;
	word_tab wordstype;
	cursor cbody(cid number,lid number) is
                SELECT id,score from body_hitlist
		where (id = lid) and (conid=cid)
		order by score desc;
	lower_id header.id%TYPE := 0;
	f_id header.id%TYPE;
	typeinword varchar2(40);
	wordshow varchar2(200);
	word_junk varchar2(100);
	total header.id%TYPE := 0;
	words varchar2(200);
	i integer;
	k integer;
	ii BINARY_INTEGER;
	w_count BINARY_INTEGER;
	last_word boolean := false;
	tmp varchar2(200);
	body_all long;
	body_rec body%ROWTYPE;
	limit header.id%TYPE;
	id_list long :='';
	ilist long :='';
	tot header.id%TYPE := 0;
	kk integer;
	ch char(1);
	 front boolean := true;
	  more boolean := false;
query_word varchar2(200);
	begin
	htp.p('<title>results of query by subject</title>');
	button;
	tmp := replace(typein,' ','');
	if (tmp IS NULL) then
		htp.p('<p>You must choose a word to start the query. Try again.');
		return;
	else
		words := typein;
	end if;
	if ((instr(typein,'&') = 0) and (instr(typein,'|') = 0) and
                (instr(typein,'-') = 0) and (instr(typein,',') = 0)) then
             for n in 1 .. length(typein) loop
                ch := substr(typein,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(typein,' ');
        end if;
      --  highlight := key;
        query_word := '('||key ||'):3000';
        select query_id.nextval into usr_query_id from dual;
        ctx_query.contains('CHEM_POLICY',query_word,'QUERY_TEMP',1,usr_query_id,NULL);
     		i := 1;
     		last_word := false;
    		w_count := 0;
		tmp := NULL;
     		while (1 = 1) loop
			k := instr(words,' ',i);
			if (k = i) then
				i := i + 1;
			else
				if (k = 0) then
					last_word := true;
					typeinword := words;
				else
					typeinword := substr(words,1,k);
					words := substr(words,k+1);
					i := 1;
				end if;
				typeinword := replace(typeinword,' ','');
				if (typeinword IS NULL) then
					last_word := true;
				else
					w_count := w_count + 1;
					word_tab(w_count) := typeinword;
					if (tmp IS NULL) then
						tmp := typeinword;
					else
						tmp := tmp||'+'||typeinword;
					end if;
				end if;
				if (last_word) then exit; end if;
			end if;
		end loop;
		last_word := false;
		tmp := keyword||'+'||tmp;
		i := 1;
	htp.p(htf.line);
	while (1 = 1) loop
			typeinword := word_tab(i);
			i := i+1;
			if (i > w_count) then last_word := true; end if;
			typeinword := lower(typeinword);
		if   (not last_word) then wordshow := tmp;
		else wordshow := keyword||' '||typeinword; end if;
		typeinword := '%'||typeinword||'%';
		if (hit = 0) then limit := maxmsg;
		else limit := hit; end if;
        	total := 0;
		htp.p('<p>Keywords: '||htf.bold(replace(wordshow,' ','+')));
		htp.p('<ul>');
		ilist := replace(list,chr(10));
		kk := instr(ilist,',');
		while (kk != 0) loop
			h_id := substr(ilist,1,kk-1);
			ilist := substr(ilist,kk+1);
				open cbody(usr_query_id,h_id);
				fetch cbody into h_id,score;
		  		if (cbody%NOTFOUND) then
					close cbody;
					goto end_if2;
				end if;
				close cbody;
			if  (not last_word) then
				select * into body_rec
					from body where id=h_id;
				ii := 2;
				body_all := body_rec.msg_body;
					while (ii <= w_count) loop
						k := instr(lower(body_all),lower(word_tab(ii)));
						if (k = 0) then exit; end if;
						ii := ii + 1;
					end loop;
				if (ii <= w_count) then goto end_if2; end if;
			end if;
			select id,msg_date,subject,sender,bytes into
			 	h_id,h_msg_date,h_subject,h_sender,h_bytes
				from header where id = h_id;
			if  (not last_word) then
	header_list(score,usr_query_id,h_id,h_bytes,h_sender,h_subject,h_msg_date,tmp);
			else
	header_list(score,usr_query_id,h_id,h_bytes,h_sender,h_subject,h_msg_date,
		replace(wordshow,' ','+'));
			end if;
	        	if (length(id_list) < 32750 or id_list IS NULL) then
				id_list := id_list||h_id||',';
			end if;
	        	total := total + 1;
   			if (total >= limit) then exit; end if;
<<end_if2>>
			kk := instr(ilist,',');
		end loop;
	        tot := tot + total;
		htp.p('</ul>');
		htp.p('<p>Total '||htf.bold(total)||' mail bodies found.');
		htp.p(htf.line);
		if (last_word ) then exit; end if;
		htp.p(htf.line);
  	end loop;
  	if  (tot > 5) then
		index_subject1(id_list,tmp);
 	end if;
	wow.sig;
        end;
procedure index_sender is
	begin
		MAILS1.index_sender_main(0);
	end;
  procedure index_people is
 begin
		MAILS1.index_sender_main(1);
 end;
procedure button is
	begin
		htp.p('['||htf.url_top(root_url,'Asknpac')||'] ');
		htp.p('['||htf.url_top(cgi_bin||'MAILS0.home','Home')||'] ');
		htp.p('['||htf.url_top(cgi_bin||'MAILS.showheader','Header')||'] ');
		htp.p('['||htf.url_top(cgi_bin||'MAILS1.index_date','Date')||'] ');
		htp.p('['||htf.url_top(cgi_bin||'MAILS.index_subject','Keyword')||'] ');
		htp.p('['||htf.url_top(cgi_bin||'MAILS.index_sender','Sender')||'] ');
		htp.p('['||htf.url_top(cgi_bin||'hypermail.list_folder','Hypermail')||'] ');
		htp.p('['||htf.url_top(cgi_bin||'MAILS4.index_url','URL')||'] ');
		htp.p('['||htf.url_top(cgi_bin||'MAILS.index_people','People')||'] ');
		htp.p('['||htf.url_top(cgi_bin||'MAILS0.showfolders','Folder')||'] ');
		htp.p('['||htf.url(cgi_bin||'MAILS0.showmailbox','Mailbox')||'] ');
		htp.p('['||htf.url_top(cgi_bin1||'index_chat','Feedback')||'] ');
		htp.p('['||htf.url('/'||owner_uid||'/help.html','Help')||'] ');
	end;
end MAILS;


***** Error Messages *****

Line # = 222 Column # = 25 Error Text = PLS-00302: component 'HIGHLIGHT' must be declared
Line # = 222 Column # = 15 Error Text = PL/SQL: Statement ignored
Line # = 599 Column # = 19 Error Text = PLS-00302: component 'CONTAINS' must be declared
Line # = 599 Column # = 9 Error Text = PL/SQL: Statement ignored
Line # = 1077 Column # = 19 Error Text = PLS-00302: component 'CONTAINS' must be declared
Line # = 1077 Column # = 9 Error Text = PL/SQL: Statement ignored