***** chem, MAILS5 package body *****

          is
usr_query_id number;
key varchar2(200);
total 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;
h_name people.name%TYPE;
h_email people.email%TYPE;
h_msg_id header.msg_id%TYPE;
urowid rowid;
highlight varchar2(200) := NULL;
session_id number(6);
TYPE wordstype IS TABLE	OF varchar2(100) INDEX BY BINARY_INTEGER;
word_tab wordstype;
out_string varchar2(254);
w_count BINARY_INTEGER;
date_tag char(1);
procedure asknpac(host in varchar2,address in varchar2,user in varchar2,name varchar2) is
begin
	addvisitor(host,address,user,name);
	default_folder := name;
	home;
end;
procedure addvisitor(host in varchar2,address in varchar2,user in varchar2,name varchar2) is
	begin
		insert into visitor
			values (sysdate,host,address,user);
		update mailbox set visitors = (visitors + 1);
		commit;
	end;
procedure home is
begin
	htp.title('Welcome to AskNPAC !');
	htp.p('<frameset rows="50%,50%">');
--	select id into h_id from folder where rownum = 1 and
--		name like '%'||default_folder||'%';
--	htp.p('<frame src="'||cgi_bin||'MAILS5.entry?fid='||h_id||'" name="left">');
	htp.p('<frame src="'||cgi_bin||'MAILS5.search_button?typein=&op=0&fid=0" name="left">');
	htp.p('<frame src="'||help_page_url||'" name="right">');
	htp.p('</frameset>');
	htp.p('<noframes> You must use Netscape Navigator 2.0+ to use this system.</noframes>');
	htp.p('</html>');
end;
procedure entry(fid integer) is
begin
	search_button(NULL,1,fid);
	htp.p('<HR WIDTH="60%" ALIGN="CENTER">');
	more(fid,per_page,NULL,0);
end;
procedure entry_sub(fid integer) is
begin
	search_button(NULL,1,fid);
	htp.p('<HR WIDTH="60%" ALIGN="CENTER">');
	more_sub(fid,per_page,NULL,0);
end;
procedure entry_lsender(fid integer) is
begin
	search_button(NULL,1,fid);
	htp.p('<HR WIDTH="60%" ALIGN="CENTER">');
	lauthor(fid,per_page,NULL,0);
end;
procedure next(fid integer, hit integer, curr rowid, seed number) is
begin
	search_button(NULL,1,fid);
	htp.p('<HR WIDTH="80%" ALIGN="CENTER">');
	more(fid,hit,curr,seed);
end;
procedure next_sub(fid integer, hit integer, curr rowid, seed number) is
begin
	search_button(NULL,1,fid);
	htp.p('<HR WIDTH="80%" ALIGN="CENTER">');
	more_sub(fid,hit,curr,seed);
end;
procedure next_author(fid integer, hit integer, curr rowid, seed number) is
begin
	search_button(NULL,1,fid);
	htp.p('<HR WIDTH="80%" ALIGN="CENTER">');
	lauthor(fid,hit,curr,seed);
end;
procedure search_button(typein varchar2,op integer,fid integer) is
	cursor cfolder is select id,name,n_msg from folder order by name;
begin
	htp.p('<CENTER>');
	htp.p('<table border=0><tr><td colspan="2">');
	htp.formOpen(cgi_bin||'MAILS5.search');
	htp.p(htf.url_top(root_url,'<img src="/icons/npaclogo.gif" border=0 align="middle">'));
	htp.p('<input name="typein" size="40" value="'||typein||'"> ');
	htp.p('<input type="submit" value="Submit"> ');
	htp.p(help_page);
	htp.p('</td></tr><tr><td><font size="2">By: ');
	htp.formSelectOpen('op');
	if (op = 0) then
		htp.p('<option value="0" selected>Keyword');
	else
		htp.p('<option value="0">Keyword');
	end if;
	if (op = 1) then
		htp.p('<option value="1" selected>Header List');
	else
		htp.p('<option value="1">Header List');
	end if;
	if ( op = 2) then
		htp.p('<option value="2" selected>Date');
	else
		htp.p('<option value="2">Date');
	end if;
	if ( op = 3) then
		htp.p('<option value="3" selected>Sender(Name)');
	else
		htp.p('<option value="3">Sender(Name)');
	end if;
	if ( op = 4) then
		htp.p('<option value="4" selected>Sender(Email)');
	else
		htp.p('<option value="4">Sender(Email)');
	end if;
	if ( op = 5) then
		htp.p('<option value="5" selected>URL');
	else
		htp.p('<option value="5">URL');
	end if;
	htp.p(htf.formSelectClose);
	htp.p('</font></td>');
	htp.p('<td><font size="2">Folder:');
	select n_folder into total from mailbox;
	if (total > 20) then
		htp.p('<select name="fid" size="2">');
	else
		htp.p('<select name="fid">');
	end if;
	select sum(n_msg) into total from folder;
	if (fid = 0) then
		htp.p('<option value="0" selected>All ('||total||')');
	else
		htp.p('<option value="0">All ('||total||')');
	end if;
	for folder in cfolder loop
		if (fid = folder.id) then
	   		htp.p('<option value="'||folder.id||'" selected>'||folder.name||' ('||folder.n_msg||')');
		else
	   		htp.p('<option value="'||folder.id||'">'||folder.name||' ('||folder.n_msg||')');
		end if;
	end loop;
	htp.p(htf.formSelectClose);
	htp.p('</font></td></tr>');
	htp.p(htf.formClose);
	htp.p('</table></CENTER>');
end;
procedure search(typein varchar2,op integer) is
begin
	search(typein,op,1);
end;
procedure search(typein varchar2,op integer,fid integer) is
begin
	if (op = 1) then
		if (fid = 0) then
		      select id into h_id from folder where rownum = 1 and
			name like '%'||default_folder||'%';
--select id into h_id from folder where rownum = 1;
			entry(h_id);
		else
			next(fid, per_page, NULL, 0);
		end if;
	elsif (op = 0) then
		by_keyword(typein,fid);
	elsif (op = 2) then
		by_date(typein,fid);
	elsif (op = 3) then
		by_sender_name(typein,fid);
	elsif (op = 4) then
		by_sender_email(typein,fid);
	elsif (op = 5) THEN
		by_url(typein,fid);
	else
		search_button(typein,3,fid);
		htp.p('<HR WIDTH="60%" ALIGN="CENTER">');
		htp.p('Sorry, this search option has not been implemented yet ! Please check it back later.');
		htp.p('<p>'||htf.url(cgi_bin||'MAILS5.entry?fid=1','Go back')||' ...');
	end if;
end;
procedure more(fid integer, hit integer, curr rowid, seed number) is
    cursor cname2(lo number,hi number, cur date) is
	SELECT rowid,msg_date,subject,sender,bytes
	from header where (id between lo and hi) and (msg_date < cur)
	order by msg_date desc, sender asc;
    f_name folder.name%TYPE;
    n number;
    next boolean := false;
    minid folder.min_msgid%TYPE;
    end_down header.id%TYPE;
    hi header.id%TYPE;
    lo header.id%TYPE;
    curr_date date := to_date('1999','yyyy');
    next_rowid rowid;
begin
	SELECT name,n_msg into f_name,total
		from folder where id = fid;
	htp.p('<center><table border=1 cellpadding=0 cellspacing=0 width=100%>');
	htp.p('<caption><font size="+1">Mail Header List By Date in ');
	htp.p(htf.url_right(cgi_bin||'MAILS5.folderinfo?fid='||fid,f_name)||'</font></caption>');
	htp.p('<tr align=center><th>No.</th><th>'||htf.url(cgi_bin||'MAILS5.entry_sub?fid='||fid,'Subject')||'</th><th>');
	htp.p(htf.url(cgi_bin||'MAILS5.entry_lsender?fid='||fid,'Sender')||'</th><th>Date</th><th>Size(kb)</th></tr>');
	if (curr IS NOT NULL) then
		select msg_date into curr_date from header where rowid=curr;
	end if;
	lo := (fid-1)*maxmsg + 1;
	hi := lo + maxmsg - 1;
	open cname2(lo,hi,curr_date);
	n := seed;
	loop
		fetch cname2 into urowid,h_msg_date,h_subject,h_sender,h_bytes;
	        exit when cname2%NOTFOUND;
		n := n + 1;
		header_list(n,urowid,h_bytes,h_sender,h_subject,h_msg_date);
		if (cname2%ROWCOUNT >= hit) then
			next_rowid := urowid;
			next := true;
			end_down := n + hit;
			if (end_down > total) then
				end_down := total;
			end if;
			exit;
		end if;
	end loop;
	close cname2;
	htp.p('</table></center>');
	if (next) then
		htp.p(htf.url(cgi_bin||'Mails5.next?fid='||fid||'&hit='||hit||
			'&curr='||next_rowid||'&seed='||n,'Next '||(n+1)||'-'||end_down)||' (of '||total||')');
	end if;
end;
procedure more_sub(fid integer, hit integer, curr rowid, seed number) is
    cursor cname2(lo number,hi number, cur varchar2) is
	SELECT rowid,msg_date,subject,sender,bytes
	from header where (id between lo and hi) and (replace(lower(subject),'re: ') > cur)
	order by replace(lower(subject),'re: ') asc,msg_date desc, sender asc;
    f_name folder.name%TYPE;
    n number;
    next boolean := false;
    minid folder.min_msgid%TYPE;
    end_down header.id%TYPE;
    hi header.id%TYPE;
    lo header.id%TYPE;
    curr_sub header.subject%TYPE := ' ';
    next_rowid rowid;
    prev header.subject%TYPE := ' ';
    k integer := 0;
    tmp header.subject%TYPE;
begin
	SELECT name,n_msg into f_name,total
		from folder where id = fid;
	htp.p('<center><table border=1 cellpadding=0 cellspacing=0 width=100%>');
	htp.p('<caption><font size="+1">Mail Header List By Subject in ');
	htp.p(htf.url_right(cgi_bin||'MAILS5.folderinfo?fid='||fid,f_name)||'</font></caption>');
	htp.p('<tr align=center><th>No.</th><th>Subject</th><th>');
	htp.p(htf.url(cgi_bin||'MAILS5.entry_lsender?fid='||fid,'Sender')||'</th><th>');
	htp.p(htf.url(cgi_bin||'MAILS5.entry?fid='||fid,'Date')||'</th><th>Size(kb)</th></tr>');
	if (curr IS NOT NULL) then
		select replace(lower(subject),'re: ') into curr_sub from header where rowid=curr;
	end if;
	lo := (fid-1)*maxmsg + 1;
	hi := lo + maxmsg - 1;
	open cname2(lo,hi,curr_sub);
	n := seed;
	loop
		fetch cname2 into urowid,h_msg_date,h_subject,h_sender,h_bytes;
	        exit when cname2%NOTFOUND;
		tmp := replace(lower(h_subject),'re: ');
		if (tmp != prev) then
			prev := tmp;
			n := n + 1;
			h_subject := replace(h_subject,'Re: ');
			header_list_sub(n,urowid,h_bytes,h_sender,h_subject,h_msg_date);
			k := k + 1;
			if (k >= hit) then
				next_rowid := urowid;
				next := true;
				end_down := n + hit;
				if (end_down > total) then
					end_down := total;
				end if;
				exit;
			end if;
		else
			h_subject := NULL;
			header_list_sub(n,urowid,h_bytes,h_sender,h_subject,h_msg_date);
		end if;
	end loop;
	close cname2;
	htp.p('</table></center>');
	if (next) then
		htp.p(htf.url(cgi_bin||'Mails5.next_sub?fid='||fid||'&hit='||hit||
			'&curr='||next_rowid||'&seed='||n,'Next '||(n+1)||'-'||end_down)||' (of '||total||')');
	end if;
end;
procedure lauthor(fid integer, hit integer,curr rowid, seed number) is
    cursor cname1(lo number,hi number, cur varchar2) is
	SELECT rowid,msg_date,subject,sender,bytes
	from header where (id between lo and hi) and (sender > cur)
	order by sender asc, msg_date desc;
    f_name folder.name%TYPE;
    n number;
    next boolean := false;
    minid folder.min_msgid%TYPE;
    end_down header.id%TYPE;
    hi header.id%TYPE;
    lo header.id%TYPE;
    curr_sender header.sender%TYPE := ' ';
    next_rowid rowid;
begin
	SELECT name,n_msg into f_name,total
		from folder where id = fid;
	htp.p('<center><table border=1 cellpadding=0 cellspacing=0 width=100%>');
	htp.p('<caption><font size="+1">Mail Header List By Sender in ');
	htp.p(htf.url_right(cgi_bin||'MAILS5.folderinfo?fid='||fid,f_name)||'</font></caption>');
	htp.p('<tr align=center><th>No.</th><th>'||htf.url(cgi_bin||'MAILS5.entry_sub?fid='||fid,'Subject')||
		'</th><th>Sender</th><th>');
	htp.p(htf.url(cgi_bin||'MAILS5.entry?fid='||fid,'Date')||'</th><th>Size(kb)</th></tr>');
	if (curr IS NOT NULL) then
		select sender into curr_sender from header where rowid=curr;
	end if;
	lo := (fid-1)*maxmsg + 1;
	hi := lo + maxmsg - 1;
	open cname1(lo,hi,curr_sender);
	n := seed;
	loop
		fetch cname1 into urowid,h_msg_date,h_subject,h_sender,h_bytes;
	        exit when cname1%NOTFOUND;
		n := n + 1;
		header_list(n,urowid,h_bytes,h_sender,h_subject,h_msg_date);
		if (cname1%ROWCOUNT >= hit) then
			next_rowid := urowid;
			next := true;
			end_down := n + hit;
			if (end_down > total) then
				end_down := total;
			end if;
			exit;
		end if;
	end loop;
	close cname1;
	htp.p('</table></center>');
	if (next) then
		htp.p(htf.url(cgi_bin||'Mails5.next_author?fid='||fid||'&hit='||hit||
			'&curr='||next_rowid||'&seed='||n,'Next '||(n+1)||'-'||end_down)||' (of '||total||')');
	end if;
end;
/*procedure prompt is
	begin
		htp.p('<center>Article Text Frame');
		htp.p('<p>It may take a while to load, please be patient ... </center></html>');
	end; */
procedure header_list(n number,urowid rowid,bytes number,
	sender varchar2,subject in out varchar2,msg_date date) is
cursor cname(csender varchar2) is SELECT name,email from people where email=csender;
begin
		open cname(sender);
		fetch cname into h_name,h_email;
		if (cname%NOTFOUND) then
			h_name := h_email;
		end if;
		close cname;
		if (h_name IS NULL) then
			h_name := h_email;
		end if;
		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;
		htp.p('<tr><td align="center">');
		htp.p(n||'</td>');
		htp.p('<td><font size="2">');
		if (highlight IS NULL) then
			htp.p(htf.url_right(cgi_bin||'MAILS5.showbody?rid='||
			urowid,substr(subject,1,40)));
		else
			htp.p(htf.url_right(cgi_bin||'MAILS5.showbody?rid='||
			urowid||'&word='||highlight,
			substr(subject,1,40)));
		end if;
		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>');
end;
procedure header_list(n number,score number,user_query_id number,urowid rowid,bytes number,
        sender varchar2,subject in out varchar2,msg_date date,word varchar2) is
cursor cname(csender varchar2) is SELECT name,email from people where email=csender;
begin
                open cname(sender);
                fetch cname into h_name,h_email;
                if (cname%NOTFOUND) then
                        h_name := h_email;
                end if;
                close cname;
                if (h_name IS NULL) then
                        h_name := h_email;
                end if;
                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;
                htp.p('<tr><td align="center">');
                htp.p(score||'</td>');
                htp.p('<td><font size="2">');
                htp.p(htf.url_right(cgi_bin||'MAILS5.showbody?rid='||
                urowid||'&word='||word||'&cid='||user_query_id,substr(subject,1,40)));
                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>');
end;
procedure header_list_sub(n number,urowid rowid,bytes number,
	sender varchar2,subject in out varchar2,msg_date date) is
cursor cname(csender varchar2) is SELECT name,email from people where email=csender;
begin
		open cname(sender);
		fetch cname into h_name,h_email;
		if (cname%NOTFOUND) then
			h_name := h_email;
		end if;
		close cname;
		if (h_name IS NULL) then
			h_name := h_email;
		end if;
		htp.p('<tr><td align="center">');
		if (subject IS NOT NULL) then
			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;
			htp.p(n||'</td>');
			htp.p('<td><font size="2">');
			htp.p(substr(subject,1,40));
			htp.p('</font></td>');
		else
			htp.p('<br></td>');
			htp.p('<td><br></td>');
		end if;
		htp.p('<td><font>');
		if (highlight IS NULL) then
			htp.p(htf.url_right(cgi_bin||'MAILS5.showbody?rid='||
			urowid,substr(h_name,1,25)));
		else
			htp.p(htf.url_right(cgi_bin||'MAILS5.showbody?rid='||
			urowid||'&word='||highlight,substr(h_name,1,25)));
		end if;
		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>');
end;
procedure showbody(rid in rowid, word in varchar2, cid in number) is
begin
	highlight := word;
	session_id := cid;
--        select rowid into rid from header where id=fid;
	showbody(rid);
end;
procedure showbody(rid in rowid) is
	bid header.id%TYPE;
	fid header.id%TYPE;
	fpath folder.path%TYPE;
	gname folder.name%TYPE;
	n header.id%TYPE;
	i header.id%TYPE;
	j header.id%TYPE;
	k header.id%TYPE;
	body_all long;
	tmp long;
	h_cc header.cc%TYPE;
	h_receiver header.receiver%TYPE;
	h_replied_id header.replied_id%TYPE;
	url varchar2(254);
	n1 header.id%TYPE;
	m header.id%TYPE;
	cursor reply(low_id number,high_id number,repid varchar2) is
		select rowid from header where (id between low_id and high_id)
		and (msg_id = repid);
--* highlight_temp -> hightab (Changed for upgrading, Jake) 
	cursor highcur is select offset, length
                  from hightab where query_id = session_id
                  order by offset desc;
--	cursor cbody(rid number) is
--		select id,BODY_TYPE,CHARS,WORDS,LINES,msg_body1,msg_body2,
--			msg_body3,msg_body4,msg_body5,msg_body6,
--			msg_body7,msg_body8,msg_body9,msg_body10,msg_body11
--			from body where id=rid;
	cursor creply(low_id number,high_id number,repid varchar2) is
		select rowid,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;
	body_rec body%ROWTYPE;
	min_id header.id%TYPE;
	replied_rid rowid;
	hword varchar2(200);
	word varchar2(200);
	key varchar2(200);
begin
        SELECT id,msg_date,subject,sender,receiver,cc,msg_id,replied_id into
                bid,h_msg_date,h_subject,h_sender,h_receiver,h_cc,h_msg_id,h_replied_id
                from header where rowid = rid;
        SELECT name,email into h_name,h_email from people where email=h_sender;
        htp.p('<title>article body</title>');
        htp.p('<body bgcolor="#FFFFFF" background="/icons/notebook.gif"><BLOCKQUOTE>');
        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;
       if (highlight IS NOT NULL) then
                ---- highlight keyword in hightlight
                key := replace(highlight,'~','&');
              ctx_doc.highlight(
                'msg_index',
                body_rec.id,
                key,
                'hightab',
                session_id,
                FALSE);
/**              ctx_query.highlight(
                'CHEM_POLICY',
                body_rec.id,
                key,
                session_id,
                null,
                null,
                'highlight_temp',
                null,
                null);
         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;*/
        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);
        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;
                        else
                                i := m + 5;
                        end if;
                        m := instr(body_all,'http://',i,1);
        end loop;
        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 := substr(h_cc,1,150);
                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 replied_rid;
                if (reply%NOTFOUND) then
                        htp.p(h_subject);
                else
                        htp.p(htf.url(cgi_bin||'MAILS5.showbody?rid='||replied_rid,
                        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 urowid,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||'MAILS5.showbody?rid='||urowid,h_name));
                        fetch creply into urowid,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;
                        word := substr(highlight,1,k-1);
                        highlight := substr(highlight,k+1);
                        hword := '<font color="#ff000">'||word||'</font>';
                        body_all := replace(body_all,word,hword);
                        body_all := replace(body_all,upper(word),hword);
                        body_all := replace(body_all,INITCAP(word),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);
n := length(body_all);
        i := 1;
        while ( (i+253) <= n) 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 <= n) 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="/icons/reply.gif">'));
                htp.p(htf.url(cgi_bin1||'index_chat','<IMG ALT="Post" BORDER=0 SRC="/icons/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="/icons/reply.gif"></A>');
		htp.p('<a href="mailto:'||h_receiver||'?subject='||h_subject||'">');
                htp.p('<IMG BORDER=0 SRC="/icons/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="/icons/mpreply.gif"> </A>');
                htp.p('<a href="mailto:">');
                htp.p('<IMG BORDER=0 SRC="/icons/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 by_people(uid varchar2) is
 	p_userid people.userid%TYPE;
 	p_name people.name%TYPE;
	p_email people.email%TYPE;
	p_msg_received header.id%TYPE;
	p_html people.html%TYPE;
	myid url.id%TYPE;
	myhtml url.html%TYPE;
	n integer;
 begin
	select userid,name,email,msg_received,html
		into p_userid,p_name,p_email,p_msg_received,p_html
		from people where email=uid;
  	if (p_name IS NULL) then
		p_name := p_userid;
	end if;
	htp.p('<center><table border=1><caption><font size=+1>About '||htf.bold(p_name)||'</font></caption>');
	htp.p('<tr><td align="right">Name: </td><td>'||htf.bold(p_name)||'</td></tr>');
	htp.p('<tr><td align="right">Email Address: </td><td>'||htf.bold(p_email)||'</td></tr>');
	htp.p('<tr><td align="right">User Id: </td><td>'||htf.bold(p_userid)||'</td></tr>');
	select rowid,msg_date,subject into
			urowid,h_msg_date,h_subject from header
			where id=p_msg_received;
	htp.p('<tr><td align="right">Most Recent Mail: </td><td>');
	htp.p(htf.url_right(cgi_bin||'MAILS5.showbody?rid='||urowid,h_subject));
	htp.p(' at <i>'||to_char(h_msg_date,'Mon dd yy hh24:mi')||'</i></td></tr>');
	htp.p('<tr><td align="right">All Mails: </td><td>'||
		htf.url(cgi_bin||'MAILS5.by_sender?semail='||uid,'Click Here')||'</td></tr>');
	if (p_html IS NOT NULL) then
		htp.p('<tr><td align="right">Related HTML Page: </td><td><table border=0><tr>');
			n := instr(p_html,',');
			while (n != 0) loop
				myid := substr(p_html,1,n-1);
				p_html := substr(p_html,n+1);
				select html into myhtml from url where id=myid;
				htp.p('<td>'||htf.url(myhtml,myhtml)||'</td>');
				n := instr(p_html,',');
			end loop;
			htp.p('</tr></table></td></tr>');
	end if;
	htp.p('</table>');
   end;
procedure by_keyword(typein varchar2,fid number) is
begin
	search_button(typein,0,fid);
	htp.p('<HR WIDTH="60%" ALIGN="CENTER">');
	keyword(typein,fid);
end;
procedure keyword(typein varchar2,fid number) is
cursor cresult(cid number,lo number, hi number) is select *
                from body_hitlist where conid=cid
				and (id between lo and hi)
				order by score desc;
  result_rec body_hitlist%ROWTYPE;
  front boolean := true;
  more boolean := false;
  ch char(1);
k integer;
query_word varchar2(200);
ii BINARY_INTEGER;
body_all long;
body_rec body%ROWTYPE;
n number(6) := 0;
hit integer := 10;
next_rowid rowid;
last_rowid rowid;
end_num integer;
lo header.id%TYPE := 1;
hi header.id%TYPE := 100*maxmsg;
f_name folder.name%TYPE := htf.url_right(cgi_bin||'MAILS5.showmailbox','All folders');
browid rowid;
found boolean := true;
prev header.msg_id%TYPE := ' ';
begin
	parse_typein(typein);
	if (w_count = 0) then
		htp.p('<p>You must choose a word to start the keyword search. Try again.');
		return;
	end if;
	if (fid != 0) then
		lo := (fid-1)*maxmsg + 1;
		hi := lo + maxmsg - 1;
		SELECT name,n_msg into f_name,total from folder where id = fid;
		f_name := htf.url_right(cgi_bin||'MAILS5.folderinfo?fid='||fid,f_name);
	else
		select sum(n_msg) into total from folder;
	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;
        if (key IS NULL) then
                htp.p('Please type one or more keywords to start your search');
                wow.sig;
                return;
        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);
--        select count(*) into n from query_temp where conid=usr_query_id
select count(*) into n from body_hitlist  where conid=usr_query_id
           				     and (id between lo and hi);
	htp.p('<center><table border=1 cellpadding=0 cellspacing=0 width=100%>');
	htp.p('<caption><font size="+1">Keyword (<i>'||htf.bold(highlight)||'</i>) Search in ');
	htp.p(f_name||'</font></caption>');
	htp.p('<tr align=center><th>Score</th><th>Subject</th><th>Sender</th><th>Date</th><th>Size(kb)</th></tr>');
        key := replace(key,'&','~');
		open cresult(usr_query_id, lo, hi);
		k := 0;
    	for i in 1..10 loop
				fetch cresult into result_rec;
                exit when cresult%NOTFOUND;
                select rowid into urowid from header where id=result_rec.id;
                select subject,sender,msg_date,bytes into
                        h_subject,h_sender,h_msg_date,h_bytes
                        from header where id=result_rec.id;
		header_list(n,result_rec.score,usr_query_id,urowid,h_bytes,h_sender,h_subject,h_msg_date,key);
		next_rowid := urowid;
		k := k+1;
        end loop;
	htp.p('<tr align=center><td>Found: <b>'||n||'</b></td><td>Keyword: <i>'||htf.bold(highlight)||
		'</i></td><td>Searched: <b>'||total||'</b></td>'||'<td colspan="2">When: <i>'||
		to_char(sysdate,'Mon dd yy hh24:mi')||' EST</i></td></tr>');
		 htp.p('</table></center>');
		if (k = 10) and (k < n ) then
			if ( k+10 < n ) then
				end_num := k + 10;
			else
				end_num := n;
			end if;
		htp.p(htf.url(cgi_bin||'Mails5.more_url?fid='||fid||'&key='||key||
				   '&user_id='||usr_query_id||
					'&seed='||k,'Next '||(k+1)||'-'||
				end_num)||' (of '||n||')');
		end if;
--        htp.p('</table></center>');
        close cresult;
        wow.sig;
end;
procedure more_url(fid integer, key varchar2,user_id number,seed number) is
cursor cresult(cid number, lo number, hi number) is select *
                from body_hitlist where conid=cid
				and ( id between lo and hi)
				order by score desc;
  result_rec body_hitlist%ROWTYPE;
  url varchar2(200) := root_url||cgi_bin||'test.view_body?keyword=';
k integer;
n number(6) := 0;
--hit integer :10;
last_end_num integer;
end_num integer;
next_rowid rowid;
lo header.id%TYPE := 1;
hi header.id%TYPE := 100*maxmsg;
f_name folder.name%TYPE := htf.url_right(cgi_bin||'MAILS5.showmailbox','All folders');
typein1 varchar2(200);
begin
	typein1 := replace(key,'~','&');
	search_button(typein1,0,fid);
	htp.p('<HR WIDTH="60%" ALIGN="CENTER">');
    if (fid != 0) then
        lo := (fid-1)*maxmsg + 1;
        hi := lo + maxmsg - 1;
        SELECT name,n_msg into f_name,total from folder where id = fid;
        f_name := htf.url_right(cgi_bin||'MAILS5.folderinfo?fid='||fid,f_name);
	select count(*) into n from body_hitlist  where conid=user_id
                             and (id between lo and hi);
    else
        select sum(n_msg) into total from folder;
	select count(*) into n from body_hitlist  where conid=user_id;
    end if;
    htp.p('<center><table border=1 cellpadding=0 cellspacing=0 width=100%>');
    htp.p('<caption><font size="+1">Keyword(<i>'||htf.bold(typein1)||'</i>) Search in ');
    htp.p(f_name||'</font></caption>');
    htp.p('<tr align=center><th>Score</th><th>Subject</th><th>Sender</th><th>Date</th><th>Size(kb)</th></tr>');
        open cresult(user_id, lo, hi);
		for i in 1..seed loop
			fetch cresult into result_rec;
		end loop;
		k := seed;
		last_end_num := k ;
		for i in 1..10 loop
			fetch cresult into result_rec;
			exit when cresult%NOTFOUND;
			select rowid into urowid from header where id=result_rec.id;
                select subject,sender,msg_date,bytes into
                        h_subject,h_sender,h_msg_date,h_bytes
                        from header where id=result_rec.id;
		header_list(n,result_rec.score,user_id,urowid,h_bytes,h_sender,
				h_subject,h_msg_date,key);
			next_rowid := urowid;
			k := k+1;
		end loop;
        htp.p('<tr align=center><td>Found: <b>'||n||'</b></td><td>Keyword:<i>'||htf.bold(typein1)||
                '</i></td><td>Searched: <b>'||total||'</b></td>'||
				'<td colspan="2">When: <i>'||
                to_char(sysdate,'Mon dd yy hh24:mi')||'EST</i></td></tr>');
                 htp.p('</table></center>');
                if (k = seed + 10 ) and (k < n ) then
                        if ( k+10 < n ) then
                                end_num := k + 10;
       			else
                                end_num := n;
                        end if;
			htp.p('<table width="100%"><tr>');
			htp.p('<td align="left" width="35%">');
			htp.p(htf.url(cgi_bin||'Mails5.more_url?fid='||fid||'&key='||key||
                       '&user_id='||user_id||
			'&seed='||k,'Next '||(k+1)||'-'||end_num)||' (of '||n||')');
                end if;
		if k > 10 then
			htp.p('<td align="right">');
			htp.p(htf.url(cgi_bin||'Mails5.more_url?fid='||fid||'&key='||key||
             		'&user_id='||user_id||
			'&seed='|| (seed - 10), 'Prev'||(last_end_num - 9)||'-'||
                        last_end_num )||' (of '||n||')');
		end if;
		htp.p('</table>');
        close cresult;
        wow.sig;
end;
procedure by_sender_name(typein varchar2,fid number) is
begin
	parse_typein(typein);
	search_button(typein,3,fid);
	htp.p('<HR WIDTH="60%" ALIGN="CENTER">');
	if (w_count = 0) then
		if (fid = 0) then
			lauthor(1,per_page,NULL,0);
		else
			lauthor(fid,per_page,NULL,0);
		end if;
	else
		sender_email(fid,0);
	end if;
end;
procedure by_sender_email(typein varchar2,fid number) is
begin
	parse_typein(typein);
	search_button(typein,4,fid);
	htp.p('<HR WIDTH="60%" ALIGN="CENTER">');
	if (w_count = 0) then
		if (fid = 0) then
			lauthor(1,per_page,NULL,0);
		else
			lauthor(fid,per_page,NULL,0);
		end if;
	else
		sender_email(fid,1);
	end if;
end;
procedure by_sender(semail varchar2) is
begin
	w_count := 1;
	word_tab(1) := semail;
	highlight := semail;
	sender_email(0,1);
end;
procedure sender_email(fid number,op integer) is
lo header.id%TYPE := 1;
hi header.id%TYPE := 100*maxmsg;
f_name folder.name%TYPE := htf.url_right(cgi_bin||'MAILS5.showmailbox','All folders');
word varchar2(150);
i integer;
cursor csender(lo number,hi number, cname varchar2) is
	SELECT h.rowid,h.msg_date,h.subject,h.sender,h.bytes
	from header h,people p where (h.id between lo and hi) and
	(h.sender=p.email) and lower(p.name) like '%'||cname||'%'
	order by p.name;
cursor cemail(lo number,hi number, cname varchar2) is
	SELECT rowid,msg_date,subject,sender,bytes
	from header where (id between lo and hi) and
	lower(sender) like '%'||cname||'%'
	order by sender;
begin
	highlight := NULL;
	word := word_tab(1);
	if (fid != 0) then
		lo := (fid-1)*maxmsg + 1;
		hi := lo + maxmsg - 1;
		SELECT name,n_msg into f_name,total from folder where id = fid;
		f_name := htf.url_right(cgi_bin||'MAILS5.folderinfo?fid='||fid,f_name);
	else
		select sum(n_msg) into total from folder;
	end if;
	htp.p('<center><table border=1 cellpadding=0 cellspacing=0 width=100%>');
	htp.p('<caption><font size="+1">Mails By Sender (<i>*'||htf.bold(word)||'*</i>) in '||htf.bold(f_name)||'</font></caption>');
	htp.p('<tr align=center><th>No.</th><th>Subject</th><th>Sender</th><th>Date</th><th>Size(kb)</th></tr>');
	if (op = 0) then
		open csender(lo,hi,lower(word));
		loop
			fetch csender into urowid,h_msg_date,h_subject,h_sender,h_bytes;
				exit when csender%NOTFOUND;
				header_list(csender%ROWCOUNT,urowid,h_bytes,h_sender,h_subject,h_msg_date);
		end loop;
		htp.p('<tr align=center><td>Found: <b>'||csender%ROWCOUNT||'</b></td><td>Keyword: <i>'||htf.bold(word)||
		'</i></td><td>Searched: <b>'||total||'</b></td>'||'<td colspan="2">When: <i>'||
		to_char(sysdate,'Mon dd yy hh24:mi')||' EST</i></td></tr>');
		close csender;
	else
		open cemail(lo,hi,lower(word));
		loop
			fetch cemail into urowid,h_msg_date,h_subject,h_sender,h_bytes;
				exit when cemail%NOTFOUND;
				header_list(cemail%ROWCOUNT,urowid,h_bytes,h_sender,h_subject,h_msg_date);
		end loop;
		htp.p('<tr align=center><td>Found: <b>'||cemail%ROWCOUNT||'</b></td><td>Keyword: <i>'||htf.bold(word)||
		'</i></td><td>Searched: <b>'||total||'</b></td>'||'<td colspan="2">When: <i>'||
		to_char(sysdate,'Mon dd yy hh24:mi')||' EST</i></td></tr>');
		close cemail;
	end if;
	htp.p('</table>');
end;
procedure folderinfo (fid in number) is
	f_name folder.name%TYPE;
	f_creation_time date;
	f_logo folder.logo%TYPE;
	f_desc folder.description%TYPE;
	maxid header.id%TYPE := 0;
	minid header.id%TYPE := 0;
	mdate date;
   begin
        SELECT name,n_msg,creation_time,logo,description,
		(max_msgid+(id-1)*maxmsg),(min_msgid+(id-1)*maxmsg)
		into f_name,total,f_creation_time,f_logo,f_desc,maxid,minid
		from folder where id = fid;
	htp.p('<center><table border=1><caption>'||htf.gif(f_logo));
	htp.p(' Folder '||htf.bold(f_name)||' Summary </caption>');
	htp.p('<tr><td>Name: </td><td>'||htf.bold(f_name)||'</td></tr>');
	htp.p('<tr><td>Total Mails Archived: </td><td>'||htf.bold(total)||'</td></tr>');
	select ROWID,subject,msg_date into UROWID,h_subject,mdate
		from header where id = maxid and (msg_date-sysdate) <=2;
	htp.p('<tr><td>The Most Recent Mail: </td><td>');
	htp.p(htf.url_right(cgi_bin||'MAILS5.showbody?rid='||urowid,substr(h_subject,1,40)));
	htp.p(' - '||to_char(mdate,'Mon dd, yy hh24:mi')||'</td></tr>');
	select ROWID,subject,msg_date into UROWID,h_subject,mdate
		from header where id = minid;
	htp.p('<tr><td>The Least Recent Mail: </td><td>');
	htp.p(htf.url_right(cgi_bin||'MAILS5.showbody?rid='||urowid,substr(h_subject,1,40)));
	htp.p(' - '||to_char(mdate,'Mon dd, yy hh24:mi')||'</td></tr>');
	htp.p('<tr><td>Archive Started at: </td><td>'||
		htf.bold(to_char(f_creation_time,'Mon dd, yy hh24:mi'))||'</td></tr>');
	if (f_desc IS NOT NULL) then
		htp.p('<tr><td>Newsgroup Topic and Statistics:</td><td>');
		htp.bp(f_desc||'</td></tr>');
	end if;
	htp.p('</table></center>');
   end;
procedure showmailbox is
	m_name mailbox.name%TYPE;
	m_n_msg number(8);
	m_creation_time date;
	m_logo mailbox.logo%TYPE;
	m_n_folder number(6);
	mdate date;
   begin
	select name,n_folder,creation_time,logo into
		m_name,m_n_folder,m_creation_time,m_logo from mailbox;
	htp.p('<center><table border=1><caption>');
	htp.p(htf.gif(replace(m_logo,' ')));
	htp.p(' Mailbox '||htf.bold(m_name)||' Summary </caption>');
	htp.p('<tr><td>Name: </td><td>'||htf.bold(m_name)||'</td></tr>');
	select sum(n_msg) into total from folder;
	htp.p('<tr><td>Total Mails Archived: </td><td>'||htf.bold(total)||'</td></tr>');
	htp.p('<tr><td>Total Folders: </td><td>'||htf.bold(m_n_folder)||'</td></tr>');
	select rowid,subject,msg_date into urowid,h_subject,mdate from header where
			msg_date=(select max(msg_date) from header where
			id in (select max_msgid+(id-1)*maxmsg from folder)
			and (msg_date-sysdate) <=2) and rownum=1;
	htp.p('<tr><td>The Most Recent Mail: </td><td>');
	htp.p(htf.url_right(cgi_bin||'MAILS5.showbody?rid='||urowid,substr(h_subject,1,40)));
	htp.p(' - '||to_char(mdate,'Mon dd, yy hh24:mi')||'</td></tr>');
	select rowid,subject,msg_date into urowid,h_subject,mdate from header where
			msg_date=(select min(msg_date) from header where
			id in (select min_msgid+(id-1)*maxmsg from folder)) and rownum=1;
	htp.p('<tr><td>The Least Recent Mail: </td><td>');
	htp.p(htf.url_right(cgi_bin||'MAILS5.showbody?rid='||urowid,substr(h_subject,1,40)));
	htp.p(' - '||to_char(mdate,'Mon dd, yy hh24:mi')||'</td></tr>');
	htp.p('<tr><td>Archive Started at: </td><td>'||
		htf.bold(to_char(m_creation_time,'Mon dd, yy hh24:mi'))||'</td></tr>');
	htp.p('</table></center>');
   end;
procedure by_date(typein varchar2,fid integer) is
begin
	parse_date(typein);
	if (w_count = 0) then
		if (fid = 0) then
			--select id into h_id from folder where rownum = 1;
			select id into h_id from folder where rownum = 1 and
				name like '%'||default_folder||'%';
			entry(h_id);
		else
			entry(fid);
		end if;
	else
		search_button(typein,2,fid);
		htp.p('<HR WIDTH="60%" ALIGN="CENTER">');
		date_search(fid);
		--htp.p('Sorry, this search option has not been implemented yet ! Please check it back later.');
		--htp.p('<p>'||htf.url(cgi_bin||'MAILS5.entry?fid=1','Go back')||' ...');
	end if;
end;
procedure parse_typein(typein varchar2) is
words varchar2(300);
i integer;
begin
	w_count := 0;
	words := typein;
	loop
		while ((words IS NOT NULL) and (substr(words,1,1) = ' ')) loop
			words := substr(words,2);
		end loop;
		if (words IS NULL) then
			exit;
		end if;
		w_count := w_count + 1;
		i := instr(words,' ');
		if (i = 0) then
			word_tab(w_count) := lower(words);
			if (highlight IS NULL) then
				highlight := word_tab(w_count);
			else
				highlight := highlight||'+'||word_tab(w_count);
			end if;
			exit;
		else
			word_tab(w_count) := lower(substr(words,1,i-1));
			if (highlight IS NULL) then
				highlight := word_tab(w_count);
			else
				highlight := highlight||'+'||word_tab(w_count);
			end if;
			words := substr(words,i+1);
		end if;
	end loop;
end;
procedure parse_date(typein varchar2) is
words varchar2(300);
i integer := 1;
j integer;
n integer;
begin
	w_count := 0;
	words := typein;
	while ((words IS NOT NULL) and (substr(words,1,1) = ' ')) loop
			words := substr(words,2);
	end loop;
	if (words IS NULL) then
		return;
	end if;
	n := length(words);
	-- 47 = '/'; 48-57 = '0'-'9'
	while (i<=n and (ascii(substr(words,i,1)) between 47 and 57)) loop
		i := i + 1;
	end loop;
	if (i = 1) then
		words := substr(words,2);
		while ((words IS NOT NULL) and not (ascii(substr(words,1,1)) between 47 and 57)) loop
			words := substr(words,2);
		end loop;
		-- when i>n w_count=0 is used to indicate the null typein
		if (words IS NOT NULL) then
			date_tag := '<'; -- before one date
			n := length(words);
			i := 1;
			while (i<=n and (ascii(substr(words,i,1)) between 47 and 57)) loop
				i := i + 1;
			end loop;
			w_count := w_count + 1;
			word_tab(w_count) := substr(words,1,i-1);
		end if;
	elsif (i > n) then
		date_tag := 'o';	-- one date item only
		w_count := w_count + 1;
		word_tab(w_count) := words;
	else
		w_count := w_count + 1;
		word_tab(w_count) := substr(words,1,i-1);
		words := substr(words,i);
		n := length(words);
		i := 1;
		while (i<=n and not (ascii(substr(words,i,1)) between 47 and 57)) loop
			i := i + 1;
		end loop;
		if (i > n) then
			date_tag := '>';	-- after one date
		else
			date_tag := 'b';	-- between two date items
			words := substr(words,i);
			n := length(words);
			i := 1;
			while (i<=n and (ascii(substr(words,i,1)) between 47 and 57)) loop
				i := i + 1;
			end loop;
			w_count := w_count + 1;
			word_tab(w_count) := substr(words,1,i-1);
		end if;
	end if;
	/*for i in 1..w_count loop
		htp.p(i||':'||word_tab(i)||'<br>');
	end loop;
	htp.p('date_tag ='||date_tag); */
end;
procedure by_url(typein varchar2,fid integer) is
begin
	search_button(typein,5,fid);
	htp.p('<HR WIDTH="60%" ALIGN="CENTER">');
	parse_typein(typein);
	if (w_count=0) then
		if (fid=0) then
			url_more(1,per_page, NULL, 0);
		else
			url_more(fid,per_page, NULL, 0);
		end if;
	else
		url_keyword(fid);
	end if;
end;
procedure url_list(n number,urowid rowid,link varchar2,
	sender varchar2,subject in out varchar2,msg_date date) is
cursor cname(csender varchar2) is SELECT name,email from people where email=csender;
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;
		open cname(sender);
		fetch cname into h_name,h_email;
		if (cname%NOTFOUND) then
			h_name := h_email;
		end if;
		if (h_name IS NULL) then
			h_name := h_email;
		end if;
		close cname;
		htp.p('<tr><td align="center">');
		if (link IS NOT NULL) then
			htp.p(n);
		end if;
		htp.p('</td><td><font size="2">');
		if (link IS NOT NULL) then
			htp.p(htf.url_right(link,substr(link,1,40)));
		end if;
		htp.p('</font></td><td><font size="2">');
		htp.p(htf.url_right(cgi_bin||'MAILS5.showbody?rid='||
			urowid,substr(subject,1,30)));
		htp.p('</font></td>');
		htp.p('<td><font>'||substr(h_name,1,20)||'</font></td><td><font><i>'||to_char(msg_date,'Mon dd yy')||
			'</i></font></td><tr>');
end;
procedure url_more(fid integer,hit integer, curr rowid, seed number) is
    cursor cname2(hid number) is
	SELECT rowid,msg_date,subject,sender
	from header where (id = hid);
    cursor curl(uid number) is select rowid,html,head_id from url where id > uid order by id asc;
    f_name folder.name%TYPE;
    n number;
    next boolean := false;
    minid folder.min_msgid%TYPE;
    end_down header.id%TYPE;
    hi header.id%TYPE;
    lo header.id%TYPE;
    curr_urlid url.id%TYPE := 0;
    next_rowid rowid;
    uhtml url.html%TYPE;
    uhid url.head_id%TYPE;
    m integer;
    k integer := 0;
    browid rowid;
    found boolean := false;
begin
	SELECT name into f_name	from folder where id = fid;
	select count(*) into total from url;
	htp.p('<center><table border=1 cellpadding=0 cellspacing=0 width=100%>');
	htp.p('<caption><font size="+1">URLs in ');
	htp.p(htf.url_right(cgi_bin||'MAILS5.folderinfo?fid='||fid,f_name)||'</font></caption>');
	htp.p('<tr align=center><th>No.</th><th>URL</th><th>');
	htp.p('Subject</th><th>Sender</th><th>Date</th></tr>');
	if (curr IS NOT NULL) then
		select id into curr_urlid from url where rowid=curr;
	end if;
	lo := (fid-1)*maxmsg + 1;
	hi := lo + maxmsg - 1;
	open curl(curr_urlid);
	n := seed;
	loop
		fetch curl into browid,uhtml,uhid;
	        exit when curl%NOTFOUND;
		m:=instr(uhid,',',1);
		found := false;
		while(m!=0) loop
			h_id:=to_number(substr(uhid, 1, m-1));
			uhid := substr(uhid,m+1);
			m := instr(uhid,',',1);
			if (h_id between lo and hi) then
				open cname2(h_id);
				fetch cname2 into urowid,h_msg_date,h_subject,h_sender;
				if (cname2%FOUND) then
					if (not found) then
						n := n + 1;
						k := k + 1;
						found := true;
					else
						uhtml := NULL;
					end if;
					url_list(n,urowid,uhtml,h_sender,h_subject,h_msg_date);
				end if;
				close cname2;
			end if;
		end loop;
		if (k >= hit) then
			next_rowid := browid;
			next := true;
			end_down := n + hit;
			if (end_down > total) then
				end_down := total;
			end if;
			exit;
		end if;
	end loop;
	close curl;
	htp.p('</table></center>');
	if (next) then
		htp.p(htf.url(cgi_bin||'Mails5.next_url?fid='||fid||'&hit='||hit||
			'&curr='||next_rowid||'&seed='||n,'Next '||(n+1)||'-'||end_down));
	end if;
end;
procedure next_url(fid integer, hit integer, curr rowid, seed number) is
begin
	search_button(NULL,5,fid);
	htp.p('<HR WIDTH="80%" ALIGN="CENTER">');
	url_more(fid,hit,curr,seed);
end;
procedure url_keyword(fid number) is
cursor curl(uword varchar2) is select html,head_id from url
	where lower(html) like '%'||uword||'%';
cursor cname2(hid number) is
	SELECT rowid,msg_date,subject,sender,msg_id
	from header where (id = hid);
k integer;
ii BINARY_INTEGER;
n number(6) := 0;
lo header.id%TYPE := 1;
hi header.id%TYPE := 100*maxmsg;
f_name folder.name%TYPE := htf.url_right(cgi_bin||'MAILS5.showmailbox','All folders');
found boolean := true;
uhtml url.html%TYPE;
uhid url.head_id%TYPE;
m integer;
prev header.msg_id%TYPE := ' ';
begin
	if (fid != 0) then
		lo := (fid-1)*maxmsg + 1;
		hi := lo + maxmsg - 1;
		SELECT name,n_msg into f_name,total from folder where id = fid;
		f_name := htf.url_right(cgi_bin||'MAILS5.folderinfo?fid='||fid,f_name);
	else
		select sum(n_msg) into total from folder;
	end if;
	htp.p('<center><table border=1 cellpadding=0 cellspacing=0 width=100%>');
	htp.p('<caption><font size="+1">URLs of (<i>'||htf.bold(highlight)||'</i>) Search in ');
	htp.p(f_name||'</font></caption>');
	htp.p('<tr align=center><th>No.</th><th>URL</th><th>');
	htp.p('Subject</th><th>Sender</th><th>Date</th></tr>');
	open curl(word_tab(1));
	loop
		fetch curl into uhtml,uhid;
	        exit when curl%NOTFOUND;
		if (w_count != 1) then
			found := true;
			for ii in 2..w_count loop
				k := instr(lower(uhtml),word_tab(ii));
				if (k = 0) then
					found := false;
					exit;
				end if;
			end loop;
			if (not found) then
				goto nnn;
			end if;
		end if;
		m:=instr(uhid,',',1);
		found := false;
		while(m!=0) loop
			h_id:=to_number(substr(uhid, 1, m-1));
			uhid := substr(uhid,m+1);
			m := instr(uhid,',',1);
			if (h_id between lo and hi) then
				open cname2(h_id);
				fetch cname2 into urowid,h_msg_date,h_subject,h_sender,h_msg_id;
				if (cname2%FOUND) then
					if (h_msg_id != prev) then
						prev := h_msg_id;
						if (not found) then
							n := n + 1;
							found := true;
						else
							uhtml := NULL;
						end if;
						url_list(n,urowid,uhtml,h_sender,h_subject,h_msg_date);
					end if;
				end if;
				close cname2;
			end if;
		end loop;
<<nnn>>		null;
	end loop;
	close curl;
	htp.p('<tr align=center><td>Found: <b>'||n||'</b></td><td>Keyword: <i>'||htf.bold(highlight)||
		'</i></td><td>Searched: <b>'||total||'</b></td>'||'<td colspan="2">When: <i>'||
		to_char(sysdate,'Mon dd yy hh24:mi')||' EST</i></td></tr>');
	htp.p('</table>');
end;
procedure date_search(fid integer) is
cursor cdate(lo number,hi number,date1 date,date2 date) is
	SELECT rowid,msg_date,subject,sender,bytes from header
	where (id between lo and hi) and (msg_date between date1 and date2)
	order by msg_date desc;
day1 date;
day2 date;
tag char(1);
lo header.id%TYPE := 1;
hi header.id%TYPE := 100*maxmsg;
f_name folder.name%TYPE := htf.url_right(cgi_bin||'MAILS5.showmailbox','All folders');
words varchar2(100);
begin
	day(word_tab(1),day1,tag);
	if (day1 = NULL) then return; end if;
	if (date_tag = 'b') then
		day(word_tab(2),day2,tag);
		if (day2 = NULL) then return; end if;
		if (tag = 'y') then
			day2 := to_date('3112'||to_char(day2,'yy'),'ddmmyy');
		elsif (tag = 'm') then
			day2 := to_date(to_char(last_day(day2),'dd')||to_char(day2,'mmyy'),'ddmmyy');
		end if;
		words := 'Between <b>'||to_char(day1,'mm/dd/yy')||'</b> And <b>'||
			to_char(day2,'mm/dd/yy')||'</b>';
	elsif (date_tag = 'o') then
		if (tag = 'y') then
			day2 := to_date('3112'||to_char(day1,'yy'),'ddmmyy');
			words := 'in <b>'||to_char(day1,'yyyy')||'</b>';
		elsif (tag = 'm') then
			day2 := to_date(to_char(last_day(day1),'dd')||to_char(day1,'mmyy'),'ddmmyy');
			words := 'in <b>'||to_char(day1,'Month yy')||'</b>';
		else
			day2 := day1 + 1;
			words := 'on <b>'||to_char(day1,'mm/dd/yy')||'</b>';
		end if;
	elsif (date_tag = '>') then
		day2 := to_date('2050','yyyy');
		words := 'After <b>'||to_char(day1,'mm/dd/yy')||'</b>';
	else
		day2 := day1;
		day1 := to_date('1990','yyyy');
		words := 'Before <b>'||to_char(day2,'mm/dd/yy')||'</b>';
	end if;
	if (fid != 0) then
		lo := (fid-1)*maxmsg + 1;
		hi := lo + maxmsg - 1;
		SELECT name,n_msg into f_name,total from folder where id = fid;
		f_name := htf.url_right(cgi_bin||'MAILS5.folderinfo?fid='||fid,f_name);
	else
		select sum(n_msg) into total from folder;
	end if;
	htp.p('<center><table border=1 cellpadding=0 cellspacing=0 width=100%>');
	htp.p('<caption><font size="+1">Mails '||words||' in '||htf.bold(f_name)||'</font></caption>');
	htp.p('<tr align=center><th>No.</th><th>Subject</th><th>Sender</th><th>Date</th><th>Size(kb)</th></tr>');
	open cdate(lo,hi,day1,day2);
	loop
		fetch cdate into urowid,h_msg_date,h_subject,h_sender,h_bytes;
	        exit when cdate%NOTFOUND;
		header_list(cdate%ROWCOUNT,urowid,h_bytes,h_sender,h_subject,h_msg_date);
	end loop;
	htp.p('<tr align=center><td>Found: <b>'||cdate%ROWCOUNT||'</b></td><td>Keyword: <i>'||words||
		'</i></td><td>Searched: <b>'||total||'</b></td>'||'<td colspan="2">When: <i>'||
		to_char(sysdate,'Mon dd yy hh24:mi')||' EST</i></td></tr>');
	close cdate;
	htp.p('</table></center>');
end;
procedure day(str varchar2,result out date,tag in out char) is
i integer := 1;
one varchar2(8) := null;
two varchar2(8) := null;
three varchar2(8) := null;
n integer;
j integer := 1;
string varchar2(100);
begin
	string := str;
	while ( string IS NOT NULL and (substr(string,1,1) = '/')) loop
		string := substr(string,2);
	end loop;
	n := length(string);
	while ((i <= n) and (substr(string,i,1) != '/')) loop
		i := i + 1;
	end loop;
	one := substr(string,1,i-1);
	if (i > n) then
		tag := 'y';
	else
		string := substr(string,i+1);
		n := length(string);
		i := 1;
		while (i <= n and (substr(string,i,1) != '/')) loop
			i := i + 1;
		end loop;
		two := substr(string,1,i-1);
		if (i > n) then
			tag := 'm';
		else
			string := substr(string,i+1);
			n := length(string);
			i := 1;
			while (i <= n and (substr(string,i,1) != '/')) loop
				i := i + 1;
			end loop;
			three := substr(string,1,i-1);
			tag := 'd';
		end if;
	end if;
	-- check the validated date format
	if (tag = 'y') then
		i := length(one);
		one := substr(one,-2,2);
		result := to_date('0101'||one,'ddmmyy');
	else
		one := lpad(one,2,'0');
		if ((to_number(one) < 1) or (to_number(one) > 12)) then
			htp.p('<center>Date format <b>error</b>2: <b>'||str||'</b></center><p>');
			result := NULL;
		else
		 	if (tag = 'm') then
				two := substr(two,-2,2);
				result := to_date(one||two,'FXFMmmyy');
			else
				two := lpad(two,2,'0');
				if (to_number(two) > to_char(last_day(to_date(one,'mm')),'dd')) then
					htp.p('<center>Date format <b>error</b>2: <b>'||str||'</b></center><p>');
					result := NULL;
					return;
				end if;
				i := length(three);
				if (i <= 4) then
					three := substr(three,-2,2);
					result := to_date(one||two||three,'FXFMmmddyy');
				end if;
			end if;
		end if;
	end if;
end;
end MAILS5;