***** CSE, stats package *****
                                  is
procedure stats;
procedure summary;
procedure showvisitors;
procedure visitor_today;
procedure visitors;
procedure who(host varchar2,tot number);
procedure announce(hostname varchar2,name varchar2);
procedure special(host varchar2,tot number);
procedure by_domain(domain varchar2);
procedure by_date(tot number);
procedure browser(tot number);
  owner_uid constant varchar2(10) := MAILS.owner_uid;
  cgi_bin1 constant varchar2(30) := MAILS.cgi_bin1;
  cgi_bin constant varchar2(30) := MAILS.cgi_bin;
  owner_name constant varchar2(20) := MAILS.owner_name;
  username constant varchar2(10) := owner_uid;
  password constant varchar2(20) := owner_uid;
  maxmsg constant number(8) := MAILS.maxmsg;
  end;

***** CSE, stats package body *****

                                  is
procedure summary is
total header.id%TYPE;
min_time date;
now date;
today number(5);
begin
--	wow.capture;
	select visitors into total from mailbox;
	select min(visit_time) into min_time from visitor;
	htp.title('Summary of Vistors Access Information');
	htp.p('<h1>Summary of Vistors Access Information</h1>');
	htp.p('<ul>');
	htp.p('<li>Accounting Time Period: <i>'||to_char(min_time,'dd Mon yyyy hh:miam')||
		' - now ('||to_char(new_time(sysdate,'EST','EST'),'dd Mon yyyy hh:miam')||' EST)</i>');
	htp.p('<li>Total Visitors: '||htf.url(cgi_bin||'stats.showvisitors',htf.bold(total)));
	select count(*) into today from visitor where visit_time >=to_date(to_char(sysdate,'ddmmyy'),'ddmmyy');
	htp.p('<li>Total Today''s Visitors: '||htf.url(cgi_bin||'stats.visitor_today',htf.bold(today)));
	htp.p('<li>Visitor counts by '||
		htf.url(cgi_bin||'stats.by_date?tot='||total,'day'));
	select count(distinct remote_host) into total from visitor;
	htp.p('<li>Total Unique Visitors (counted by IP address): '||
		htf.url(cgi_bin||'stats.visitors',htf.bold(total)));
	select count(distinct substr(REMOTE_USER,1,4)) into total from visitor;
	htp.p('<li>Total Web Browser Types: '||htf.url(cgi_bin||'stats.browser?tot='||total,htf.bold(total)));
	htp.p('</ul>');
--	wow.show_elapsed;
	wow.sig;
end;
procedure browser(tot number) is
	cursor cbrowser is select substr(REMOTE_USER,1,4),count(*) from visitor
	   group by substr(REMOTE_USER,1,4) order by 2 desc, 1 asc;
	btype varchar2(10);
	browser varchar2(100);
	total header.id%TYPE;
begin
	htp.htitle('Web Browser Types of the User Community');
	htp.p('<ul>');
	htp.p('<li>Total Browsers: '||htf.bold(tot));
	htp.p('<ol>');
	open cbrowser;
	loop
		fetch cbrowser into btype,total;
		exit when cbrowser%NOTFOUND;
		if (replace(btype,' ') IS NULL) then
			browser := 'Unknown';
		else
			select REMOTE_USER into browser from visitor where
			REMOTE_USER like btype||'%' and rownum=1;
			if (instr(browser,'/',1,1) >0) then
				browser := substr(browser,1,instr(browser,'/',1,1)-1);
			end if;
			if (instr(browser,' ',1,2) >0) then
				browser := substr(browser,1,instr(browser,' ',1,2));
			end if;
		end if;
		htp.p('<li>'||htf.bold(browser)||': '||total);
	end loop;
	htp.p('</ol>');
	close cbrowser;
 	select count(*) into total from visitor where REMOTE_USER like '%X%';
	htp.p('<li>Platforms Types: UNIX ('||total||') ');
	select count(*) into total from visitor where (lower(REMOTE_USER) like '%win%'
		or lower(REMOTE_USER) like '%mac%') and REMOTE_USER not like '%X%';
	htp.p('PC ('||total||')');
	htp.p('<ul>');
	select count(*) into total from visitor where lower(REMOTE_USER) like '%sun%';
	htp.p('<li>SUN (SunOS): '||total);
	select count(*) into total from visitor where lower(REMOTE_USER) like '%irix%';
	htp.p('<li>SGI (IRIX): '||total);
	select count(*) into total from visitor where lower(REMOTE_USER) like '%aix%';
	htp.p('<li>IBM (AIX): '||total);
	select count(*) into total from visitor where lower(REMOTE_USER) like '%hp%';
	htp.p('<li>HP (UX): '||total);
	select count(*) into total from visitor where lower(REMOTE_USER) like '%osf%';
	htp.p('<li>DEC alpha (OSF): '||total);
	select count(*) into total from visitor where lower(REMOTE_USER) like '%linux%';
	htp.p('<li>Linux: '||total);
	select count(*) into total from visitor where lower(REMOTE_USER) like '%win%'
		and REMOTE_USER not like '%X%';
	htp.p('<li>IBM PC (MS Window): '||total);
	select count(*) into total from visitor where lower(REMOTE_USER) like '%mac%';
	htp.p('<li>Macintosh: '||total);
	select count(*) into total from visitor where not (lower(REMOTE_USER) like '%win%'
		or lower(REMOTE_USER) like '%mac%' or REMOTE_USER like '%X%');
	htp.p('<li>Unknown platforms: '||total);
	htp.p('</ul>');
	select count(*) into total from visitor where lower(REMOTE_USER) like '%proxy%';
	htp.p('<li>Total browsers/machines through a firewall: '||htf.bold(total));
	htp.p('</ul>');
	wow.sig;
end;
  procedure showvisitors is
	cursor c_visitor is select visit_time,remote_host,remote_addr from visitor order by visit_time desc;
	n number(7) := 0;
	begin
		select visitors into n from mailbox;
		htp.htitle('Visitors to this archive system');
		htp.p('Total: '||htf.bold(n));
	 	htp.p('<p><b>No.</b>........<b>Access Time</b>.....................<b>Remote Host Machine</b>............<b>Remote Machine Address</b>....');
 		htp.p(htf.line);
		n := n + 1;
		for visitor in c_visitor loop
		htp.p('<tt>'||to_char(n - c_visitor%ROWCOUNT)||'.....'||to_char(visitor.visit_time,'hh24:mi mm/dd/yy')||'.....'||visitor.remote_host||'.......'||visitor.remote_addr||'</tt><br>');
		end loop;
		wow.sig;
	end;
 procedure visitor_today is
	cursor c_visitor is select visit_time,remote_host,REMOTE_USER from visitor
		where visit_time >=to_date(to_char(sysdate,'ddmmyy'),'ddmmyy') order by visit_time desc;
	n number(7) := 0;
	begin
		select count(*) into n from visitor
		where visit_time >=to_date(to_char(sysdate,'ddmmyy'),'ddmmyy');
		htp.htitle('Today''s Visitors to this archive system');
		htp.p('Total: '||htf.bold(n));
	 	htp.p('<p><b>No.</b>........<b>Access Time</b>...............<b>Remote Host Machine</b>..................<b>Web Browser</b>....');
 		htp.p(htf.line);
		n := n + 1;
		for visitor in c_visitor loop
		htp.p('<tt>'||to_char(n - c_visitor%ROWCOUNT)||'...'||
			to_char(visitor.visit_time,'hh24:mi mm/dd/yy')||'...'||
			visitor.remote_host||'......'||substr(visitor.REMOTE_USER,1,
			instr(visitor.REMOTE_USER,' ',1,2)-1)||'</tt><br>');
		end loop;
		wow.sig;
	end;
procedure visitors is
	cursor cvisitor is select remote_host,count(*) from visitor
		group by remote_host order by 2 desc, 1 asc;
	total header.id%TYPE;
	host visitor.remote_host%TYPE;
	begin
	select count(distinct remote_host) into total from visitor;
		htp.htitle('Unique visitors to this archive system');
		htp.p('Total: '||htf.bold(total));
	 	htp.p('<p><b>Remote Host Machine (Visiting Times)</b>');
 		htp.p(htf.line);
		open cvisitor;
		loop
			fetch cvisitor into host,total;
			exit when cvisitor%NOTFOUND;
			htp.p('<tt>'||host||' ('||htf.url(cgi_bin||'stats.who?host='||host||'&tot='||total,
				htf.bold(total))||')</tt><br>');
		end loop;
		close cvisitor;
		wow.sig;
	end;
procedure who(host varchar2,tot number) is
	cursor c_visitor(ahost varchar2) is select visit_time,REMOTE_USER from visitor
		where remote_host = ahost order by visit_time desc;
	n number(7) := 0;
	address visitor.remote_addr%TYPE;
	begin
		n := tot;
		htp.htitle('Visiting Information from Host ('||host||')');
		select remote_addr into address from visitor where remote_host = host and rownum=1;
		htp.p('Total Visits: '||htf.bold(n));
		htp.p('<br>IP Address: '||htf.bold(address));
	 	htp.p('<p><b>No.</b>...........<b>Access Time</b>..................<b>Web Browser</b>....');
 		htp.p(htf.line);
		n := n + 1;
		for visitor in c_visitor(host) loop
		htp.p('<tt>'||to_char(n - c_visitor%ROWCOUNT)||'.....'||
			to_char(visitor.visit_time,'hh24:mi mm/dd/yy')||'.....'||
			visitor.REMOTE_USER||'</tt><br>');
		end loop;
		wow.sig;
	end;
procedure announce(hostname varchar2,name varchar2) is
	total header.id%TYPE;
	today number(5);
	netter varchar2(30);
	begin
		select visitors into total from mailbox;
		select count(*) into today from visitor where visit_time >=to_date(to_char(sysdate,'ddmmyy'),'ddmmyy');
		if ((name='unknown') or (name IS NULL)) then
			netter := 'netter';
		else
			netter := '<em>'||name||'</em>';
		end if;
	        htp.p('Welcome, '||netter||' from <i>'||hostname||'</i>, you are the '||
			htf.url(cgi_bin||'stats.visitor_today',htf.bold(today))
			||'''th visitor today (total: '||
			htf.url(cgi_bin||'stats.showvisitors',htf.bold(total))||').');
		htp.p(' See all '||
			htf.url(cgi_bin||'stats.summary','visitors history')||'.');
		select count(*) into total from visitor where remote_host=hostname;
		if (total = 1) then
			htp.p('It is your first time visiting us, please read our '||
				htf.url('/disclaim.html','Disclaimer')||'.');
		else
			htp.p('You visited us '||htf.url(cgi_bin||'stats.who?host='||hostname||
			'&tot='||total,total)||' times. ');
			htp.p(htf.url(cgi_bin||'stats.special?host='||hostname||'&tot='||total,'here')||
			' is a piece of special news customized for '||
			htf.url(cgi_bin||'stats.who?host='||hostname||
			'&tot='||total,'you')||' !');
		end if;
	        htp.p(htf.line);
	end;
procedure special(host varchar2,tot number) is
	last date;
	domain varchar2(30);
	cursor c_visitor(ahost varchar2) is select visit_time from visitor
		where remote_host = ahost order by visit_time desc;
	begin
		MAILS.button;
		open c_visitor(host);
		fetch c_visitor into last;
		fetch c_visitor into last;
		close c_visitor;
		htp.p(htf.line);
		htp.p('<p>This service is currently under development. '||
			'We will soon provide you with a customized query based on'||
			' the search history of your '||htf.url
			(cgi_bin||'stats.who?host='||host||'&tot='||tot,'previous visits'));
		htp.p(' and news items incorporated into the database since your last visit (<i>'||
			to_char(new_time(last,'EST','EST'),'dd Mon yyyy hh24:mi')||' EST)</i>'
			||' up to now (<i>'||to_char(new_time(sysdate,'EST','EST'),
			'dd Mon yyyy hh24:mi')||' EST)</i>.');
		htp.p('Please watch this space and visit us again.');
		-- domain := lower(substr(host,instr(host,'.',-1,2)));
		domain := lower(substr(host,instr(host,'.',1,1)+1));
		htp.p('<p>For the time being, '||htf.url(cgi_bin||'stats.by_domain?domain='||domain,'this query')||
		' will show you all the newsgroup mails sent by you or your colleagues (<i>*'||
		domain||'</i>) which are currently archived in the database.');
		wow.sig;
	end;
procedure by_domain(domain varchar2) is
	cursor csender(do varchar2) is select userid,name,email from people
		where lower(email) like '%'||do order by lower(email);
  	total header.id%TYPE;
	s_email people.email%TYPE;
	s_userid people.userid%TYPE;
	s_name people.name%TYPE;
	begin
	MAILS.button;
	htp.htitle('Query By Sender');
	htp.p(htf.line);
	open csender(domain);
	fetch csender into s_userid,s_name,s_email;
	if (csender%NOTFOUND) then
		htp.p('No sender with domain name ended with '||htf.bold(domain)||' found.');
		close csender;
		wow.sig;
		return;
	end if;
	htp.p('<h2>Choose one from the sender list</h2>');
	htp.formOpen(cgi_bin||'MAILS1.by_sender');
	htp.p('<select name="uid" size="5">');
	total := 1;
	loop
	   	if (s_name IS NULL) then
			htp.p('<option value="'||s_email||'">'||s_email);
	   	else
			htp.p('<option value="'||s_email||'">'||
			s_email||' ('||substr(s_name,1,30)||')');
	   	end if;
		fetch csender into s_userid,s_name,s_email;
		exit when csender%NOTFOUND;
		total := total + 1;
	end loop;
	close csender;
	htp.p(htf.formSelectClose);
	htp.p('<br>In folder: ');
	MAILS.list_folder(10);
	htp.p(htf.para||htf.formDo('Submit Your Choice'));
	htp.p(htf.formClose);
	htp.p('Total senders with domain name ended with <i>'||domain||'</i>: '||htf.bold(total));
	wow.sig;
	end;
procedure by_date(tot number) is
	cursor cdate is select to_char(new_time(visit_time,'EST','EST'),'mm-dd, Day'),
		count(*) from visitor group by to_char(
		new_time(visit_time,'EST','EST'),'mm-dd, Day') order by 1 desc;
	dday varchar2(30);
	total number(6);
	n integer;
	dmax number(6);
	dmin number(6);
	maxday varchar2(30);
	minday varchar2(30);
begin
	htp.htitle('Visitor Counts By Date');
	htp.p('<ul>');
	n := 0;
	dmax := 0;
	dmin := 100000;
	open cdate;
	loop
		fetch cdate into dday,total;
		exit when cdate%NOTFOUND;
		n := n + 1;
		htp.p('<li>'||dday||': '||htf.bold(total));
		if (total > dmax) then
			dmax := total;
			maxday := dday;
		end if;
		if (total < dmin) then
			dmin := total;
			minday := dday;
		end if;
	end loop;
	close cdate;
	htp.p('</ul>');
	htp.p('<p>Total: '||tot||'<br>Average: '||ceil(tot/n)||'/day');
	htp.p('<br>Maximum: '||dmax||' ('||maxday||')<br>Minimum: '||dmin||' ('||minday||')');
	wow.sig;
end;
procedure stats is
	begin
		summary;
	end;
end stats;