drop package wwwstats; drop index IND_WWWSTATS_TABLE; ---------------------------------------------------- create or replace package wwwstats is ---------------------------------------------------- procedure search_page; procedure search_action(isearchBy in varchar2, iday1m in varchar2, iday1d in varchar2, iday1y in varchar2, iday2m in varchar2, iday2d in varchar2, iday2y in varchar2 ); procedure convert_action; procedure show_cmd(isearchBy varchar2, ilimit varchar2, ildate1 varchar2, ildate2 varchar2); end; / show errors; -- / --------------------------------------------------------- create or replace package body wwwstats is --------------------------------------------------------- me varchar2(64); this wwwstats_table%type; this1 wwwstats_table1%type; -- id_null exception; today date; cursor nrow is select * from wwwstats_table order by day; -- --------------------------------------------------------- procedure search_page is --------------------------------------------------------- startDay date; begin htp.htmlOpen; htp.headOpen; htp.title(' Search for Access Statistics '); htp.headClose; htp.formOpen('/cgi-bin/wow/wwwstats.search_action', 'POST'); -- htp.bodyOpen(cattributes=>'bgcolor="#002288"', cattributes=>'fgcolor="red"'); htp.p(''); htp.hr; htp.p(''); htp.formReset('ReSet'); htp.p(' Search for Access Statistics '); htp.formSubmit(null, 'Search'); htp.p(''); htp.hr; -- cw_util_jguo.editpageOpen('Search for Access Statistics', 'Reset', 'Search'); -- htp.headOpen; -- htp.headClose; -- htp.bodyOpen; htp.p('

'); htp.p(''); htp.p('[Go to Real Time Update] '); today := sysdate; -- select min(day) into startDay from wwwstats_table; startDay := add_months(today, -6); htp.br; htp.tableOpen(1,'top','NOWRAP','',''); -- htp.formHidden('cookie', cookie); htp.tableRowOpen('center','center','','NOWRAP',''); htp.tableHeader('Search By', 'center', '', '', 1, 1, ''); htp.tableHeader('Start Date', 'center', '', '', 1, 1, ''); htp.tableHeader('End Date', 'center', '', '', 1, 1, ''); htp.tableRowClose; htp.tableRowOpen('center','center','','NOWRAP',''); htp.p(''); htp.formSelectOpen('isearchBy', '', 1, 'onChange=""'); htp.formSelectOption('Access date', null, 'value = "Access Date"'); htp.formSelectOption('Remote host', null, 'value = "Remote Host"'); htp.formSelectOption('Accessed URL', null, 'value = "Accessed URL"'); -- htp.formSelectOption('All wwwstats', null, 'value = "Access ID"'); htp.formSelectClose; htp.p(''); htp.p(''); cw_util_jguo.select_date('iday1', startDay, '1996', '2010'); htp.p(''); htp.p(''); cw_util_jguo.select_date('iday2', today, '1996', '2010'); htp.p(''); htp.tableRowClose; htp.tableClose; -- htp.formSubmit(null, 'Search', null); -- htp.formReset('Reset', null); htp.formClose; cw_util_jguo.pageClose; exception when others then cw_error.panic('wwwstats.search_page: ', sqlerrm); end; --------------------------------------------------------- procedure search_action(isearchBy in varchar2, iday1m in varchar2, iday1d in varchar2, iday1y in varchar2, iday2m in varchar2, iday2d in varchar2, iday2y in varchar2 ) is --------------------------------------------------------- ldate1 date := to_date(iday1m||iday1d||iday1y||'000000', 'MONDDYYYYHH24MISS'); ldate2 date := to_date(iday2m||iday2d||iday2y||'235959', 'MONDDYYYYHH24MISS'); ncol number(2) := 2; i number(2); cursor c_host is select host, count(*) from wwwstats_table where (day<=ldate2 and day>=ldate1) -- and (host not like '%.syr.edu%') -- and (host not like '%128.230.%') -- and (host not like '%janice.cts.com%') group by host order by 2 desc, 1 asc; cursor c_fname is select fname, count(*) from wwwstats_table where (day<=ldate2 and day>=ldate1) -- and (host not like '%.syr.edu%') -- and (host not like '%128.230.%') -- and (host not like '%janice.cts.com%') group by fname order by 2 desc, 1 asc; cursor c_day is select to_char(day, 'YYYY-MM'), count(*) from wwwstats_table where (day<=ldate2 and day>=ldate1) -- and (host not like '%.syr.edu%') -- and (host not like '%128.230.%') -- and (host not like '%janice.cts.com%') group by to_char(day, 'YYYY-MM') order by 1 desc, 2 desc; this_host wwwstats_table.host%TYPE; this_fname wwwstats_table.fname%TYPE; -- this_day wwwstats_table.day%TYPE; this_day varchar2(32); hits number(8); begin cw_util_jguo.pageOpen('Access statistics grouped by ' || isearchBy); htp.p(''); htp.p('Search Range: '||ldate1||' to '||ldate2||''); if (ldate1>ldate2) then htp.p('Sorry, your End Date is not more recent than Start Date'); htp.hr; end if; htp.tableOpen(cattributes=>'border=5'); htp.tableRowOpen; i := 1; -- while(wwwstats.next(this)) loop -- for this_row in c_host loop if (isearchBy like 'Remote Host') then for j in 1..ncol loop htp.tableHeader('Remote Host'); htp.tableHeader('Number of Hits'); if j < ncol then htp.tableHeader(' '); end if; end loop; htp.tableRowClose; htp.tableRowOpen; open c_host; loop fetch c_host into this_host, hits; exit when c_host%NOTFOUND; if i = 1 then htp.tableRowOpen; end if; -- htp.tableData(htf.anchor('http://kayak.npac.syr.edu:2020/cgi-bin/wow/wwwstats.show_cmd?iid='||this.id, this.id)); htp.tableData(this_host); htp.tableData(htf.anchor('http://kayak.npac.syr.edu:2020/cgi-bin/wow/wwwstats.show_cmd?isearchBy='||replace(isearchBy, ' ')||chr(ascii('&'))||'ilimit='||this_host ||chr(ascii('&'))||'ildate1='||ldate1||chr(ascii('&'))||'ildate2='||ldate2, hits)); if i < ncol then htp.tableData(''); i := i + 1; else htp.tableRowOpen; i := 1; end if; end loop; end if; if (isearchBy like 'Accessed URL') then ncol := 1; for j in 1..ncol loop htp.tableHeader('Accessed URL'); htp.tableHeader('Number of Hits'); if j < ncol then htp.tableHeader(' '); end if; end loop; htp.tableRowClose; htp.tableRowOpen; open c_fname; loop fetch c_fname into this_fname, hits; exit when c_fname%NOTFOUND; if i = 1 then htp.tableRowOpen; end if; htp.tableData(this_fname); htp.tableData(htf.anchor('http://kayak.npac.syr.edu:2020/cgi-bin/wow/wwwstats.show_cmd?isearchBy=' ||replace(isearchBy, ' ')||chr(ascii('&'))||'ilimit='||this_fname ||chr(ascii('&'))||'ildate1='||ldate1||chr(ascii('&'))||'ildate2='||ldate2, hits)); if i < ncol then htp.tableData(''); i := i + 1; else htp.tableRowOpen; i := 1; end if; end loop; end if; if (isearchBy like 'Access Date') then for j in 1..ncol loop htp.tableHeader('Access Month'); htp.tableHeader('Number of Hits'); if j < ncol then htp.tableHeader(' '); end if; end loop; htp.tableRowClose; htp.tableRowOpen; open c_day; loop fetch c_day into this_day, hits; exit when c_day%NOTFOUND; if i = 1 then htp.tableRowOpen; end if; -- htp.tableData( to_char( to_date(this_day, 'MM-DD-YYYY'), 'MON-DD-YYYY') ); htp.tableData( to_char( to_date(this_day, 'YYYY-MM'), 'MON-YYYY') ); -- htp.tableData(htf.anchor('http://kayak.npac.syr.edu:2020/cgi-bin/wow/wwwstats.show_cmd?isearchBy='||replace(isearchBy, ' ')||chr(ascii('&'))||'ilimit=' htp.tableData(htf.anchor('http://kayak.npac.syr.edu:2020/cgi-bin/wow/wwwstats.show_cmd?isearchBy=' ||replace(isearchBy, ' Date', 'Month')||chr(ascii('&'))||'ilimit=' -- ||to_char( to_date(this_day, 'MM-DD-YYYY'), 'MON-DD-YYYY') ||to_char( to_date(this_day, 'YYYY-MM'), 'MON-YYYY') ||chr(ascii('&'))||'ildate1='||ldate1||chr(ascii('&'))||'ildate2='||ldate2, hits)); if i < ncol then htp.tableData(''); i := i + 1; else htp.tableRowOpen; i := 1; end if; end loop; end if; htp.tableClose; htp.p(''); exception when others then cw_error.panic(me|| ':', sqlerrm); end; --------------------------------------------------------- procedure show_cmd(isearchBy varchar2, ilimit varchar2, ildate1 varchar2, ildate2 varchar2) is --------------------------------------------------------- ncol number(2) := 1; i number(2); this_day varchar2(32); hits number(8); cursor c_day is select * from wwwstats_table -- where (day<=ildate2 and day>=ildate1) -- where (to_char(day, 'MON-DD-YYYY')<=ildate2 and day>=ildate1) -- and (to_date(to_char(day, 'MON-DD-YYYY'), 'MON-DD-YYYY') = to_date(ilimit, 'MON-DD-YYYY')) -- where (to_date(to_char(day, 'MON-YYYY'), 'MON-YYYY') = to_date(ilimit, 'MON-YYYY')) where (to_date(to_char(day, 'MON-DD-YYYY'), 'MON-DD-YYYY') = to_date(ilimit, 'MON-DD-YYYY')) -- and (host not like '%.syr.edu%') -- and (host not like '%128.230.%') -- and (host not like '%janice.cts.com%') order by day desc; cursor c_month is select to_char(day, 'YYYY-MM-DD'), count(*) from wwwstats_table where (to_date(to_char(day, 'MON-YYYY'), 'MON-YYYY') = to_date(ilimit, 'MON-YYYY')) -- and (host not like '%.syr.edu%') -- and (host not like '%128.230.%') -- and (host not like '%janice.cts.com%') group by to_char(day, 'YYYY-MM-DD') order by to_char(day, 'YYYY-MM-DD') desc; cursor c_host is select * from wwwstats_table where (day<=ildate2 and day>=ildate1) and (host = ilimit) -- and (host not like '%.syr.edu%') -- and (host not like '%128.230.%') -- and (host not like '%janice.cts.com%') order by day desc; cursor c_fname is select * from wwwstats_table where (day<=ildate2 and day>=ildate1) and (fname = ilimit) -- and (host not like '%.syr.edu%') -- and (host not like '%128.230.%') -- and (host not like '%janice.cts.com%') order by day desc; begin cw_util_jguo.pageOpen('Accesses with ' || isearchBy ||' of '||ilimit); htp.p(''); htp.p('Search Range: '||ildate1||' to '||ildate2||''); htp.tableOpen(cattributes=>'border=5'); i := 1; if (isearchBy like 'AccessDate') then htp.tableRowOpen; for j in 1..ncol loop -- htp.tableHeader('Access ID'); htp.tableHeader('Access Time'); htp.tableHeader('Remote Host'); htp.tableHeader('Accessed URL'); if j < ncol then htp.tableHeader(' '); end if; end loop; htp.tableRowClose; htp.tableRowOpen; open c_day; loop fetch c_day into this; exit when c_day%NOTFOUND; if i = 1 then htp.tableRowOpen; end if; -- htp.tableData(this.id); htp.tableData(to_char(this.day, 'MON-DD-YYYY HH24:MI:SS')); htp.tableData(this.host); htp.tableData(this.fname); if i < ncol then htp.tableData(''); i := i + 1; else htp.tableRowOpen; i := 1; end if; end loop; end if; if (isearchBy like 'RemoteHost') then htp.tableRowOpen; for j in 1..ncol loop -- htp.tableHeader('Access ID'); htp.tableHeader('Access Date'); htp.tableHeader('Remote Host'); htp.tableHeader('Accessed URL'); if j < ncol then htp.tableHeader(' '); end if; end loop; htp.tableRowClose; htp.tableRowOpen; open c_host; loop fetch c_host into this; exit when c_host%NOTFOUND; if i = 1 then htp.tableRowOpen; end if; -- htp.tableData(this.id); htp.tableData(to_char(this.day, 'MON-DD-YYYY HH24:MI:SS')); htp.tableData(this.host); htp.tableData(this.fname); if i < ncol then htp.tableData(''); i := i + 1; else htp.tableRowOpen; i := 1; end if; end loop; end if; if (isearchBy like 'AccessedURL') then htp.tableRowOpen; for j in 1..ncol loop -- htp.tableHeader('Access ID'); htp.tableHeader('Access Date'); htp.tableHeader('Remote Host'); htp.tableHeader('Accessed URL'); if j < ncol then htp.tableHeader(' '); end if; end loop; htp.tableRowClose; htp.tableRowOpen; open c_fname; loop fetch c_fname into this; exit when c_fname%NOTFOUND; if i = 1 then htp.tableRowOpen; end if; -- htp.tableData(this.id); htp.tableData(to_char(this.day, 'MON-DD-YYYY HH24:MI:SS')); htp.tableData(this.host); htp.tableData(this.fname); if i < ncol then htp.tableData(''); i := i + 1; else htp.tableRowOpen; i := 1; end if; end loop; end if; if (isearchBy like 'AccessMonth') then ncol := 2; htp.tableRowOpen; for j in 1..ncol loop htp.tableHeader('Access Date'); htp.tableHeader('Number of Hits'); if j < ncol then htp.tableHeader(' '); end if; end loop; htp.tableRowClose; htp.tableRowOpen; open c_month; loop fetch c_month into this_day, hits; exit when c_month%NOTFOUND; -- htp.p('this_day = ' || this_day); if i = 1 then htp.tableRowOpen; end if; -- htp.tableData(to_char(this.day, 'MON-DD-YYYY HH24:MI:SS')); htp.tableData(to_char(to_date(this_day, 'YYYY-MM-DD'), 'MON-DD-YYYY') ); htp.tableData(htf.anchor('http://kayak.npac.syr.edu:2020/cgi-bin/wow/wwwstats.show_cmd?isearchBy=AccessDate' ||chr(ascii('&'))||'ilimit='||to_char( to_date(this_day, 'YYYY-MM-DD'), 'MON-DD-YYYY') -- ||chr(ascii('&'))||'ildate1='||ldate1||chr(ascii('&'))||'ildate2='||ldate2, hits)); ||chr(ascii('&'))||'ildate1='||ildate1||chr(ascii('&'))||'ildate2='||ildate2, hits)); if i < ncol then htp.tableData(''); i := i + 1; else htp.tableRowOpen; i := 1; end if; end loop; end if; htp.tableClose; htp.p(''); -- close c_day; -- close c_month; -- close c_host; -- close c_fname; end; --------------------------------------------------------- procedure convert_action is --------------------------------------------------------- lday date; cursor c_convert is select DISTINCT * from wwwstats_table1 order by timestamp desc; begin open c_convert; loop fetch c_convert into this1; exit when c_convert%NOTFOUND; this1.timestamp := replace(this1.timestamp, ' -0500', ''); this1.timestamp := replace(this1.timestamp, ' -0400', ''); -- lday := to_date(this1.timestamp, 'DD/MON/YYYY:HH:MI:SS -0500'); lday := to_date(this1.timestamp, 'DD/MON/YYYY:HH24:MI:SS'); select wwwstats_seq.nextval into this.id from dual; insert into wwwstats_table values ( this.id, this1.host, this1.rfc931, this1.authuser, lday, this1.method, this1.fname, this1.htv, this1.status, this1.bytes, this1.ident ); end loop; delete from wwwstats_table1; -- create index IND_WWWSTATS_TABLE on wwwstats_table(day,host,fname); exception when others then cw_error.panic('wwwstats.convert_action: ', sqlerrm); end; --------------------------------------------------------- end; --------------------------------------------------------- / show errors; -- /