drop package env; ---------------------------------------------------- create or replace package env is ---------------------------------------------------- procedure rm (iid in number); function get(iid in number) return env_table%rowtype; procedure put(iorow in out env_table%rowtype); function new(iorow in out env_table%rowtype) return env_table.id%type; function next(ionrow in out env_table%rowtype) return boolean; procedure search_page; procedure insert_action; 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 show_cmd(isearchBy varchar2, ilimit varchar2, ildate1 varchar2, ildate2 varchar2); end; / show errors; -- / --------------------------------------------------------- create or replace package body env is --------------------------------------------------------- me varchar2(64); this env_table%type; -- id_null exception; today date; cgi_url varchar2(32) := '/pulsar-stat/cgi-bin/'; cursor nrow is select * from env_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_url||'wow/env.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; -- pulsar_util.editpageOpen('Search for Access Statistics', 'Reset', 'Search'); -- htp.headOpen; -- htp.headClose; -- htp.bodyOpen; htp.p('

'); htp.p(''); htp.p('[Go to Full Statistics] '); today := sysdate; select min(day) into startDay from env_table; 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 access', null, 'value = "Access ID"'); htp.formSelectClose; htp.p(''); htp.p(''); pulsar_util.select_date('iday1', startDay, '1996', '2010'); htp.p(''); htp.p(''); pulsar_util.select_date('iday2', today, '1996', '2010'); htp.p(''); htp.tableRowClose; htp.tableClose; -- htp.formSubmit(null, 'Search', null); -- htp.formReset('Reset', null); htp.formClose; pulsar_util.pageClose; exception when others then cw_error.panic('env.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_id is select * from env_table where day<=ldate2 and day>=ldate1 order by id desc; cursor c_remote_host is select remote_host, count(*) from env_table where day<=ldate2 and day>=ldate1 group by remote_host order by 2 desc, 1 asc; cursor c_http_referer is select http_referer, count(*) from env_table where day<=ldate2 and day>=ldate1 group by http_referer order by 2 desc, 1 asc; cursor c_day is -- select to_char(day, 'MON-DD-YYYY'), count(*) -- select to_char(day, 'MM-DD-YYYY'), count(*) select to_char(day, 'YYYY-MM'), count(*) -- select to_char(day, 'MM'), to_char(day, 'YYYY'), count(*) from env_table -- where day<=ldate2 and day>=ldate1 -- where day<=ldate2 and day>=ldate1 where day<=ldate2 and day>=ldate1 -- group by to_char(day, 'MON-DD-YYYY') -- group by to_char(day, 'MM-DD-YYYY') group by to_char(day, 'YYYY-MM') order by 1 desc, 2 desc; this_remote_host env_table.remote_host%TYPE; this_http_referer env_table.http_referer%TYPE; -- this_day env_table.day%TYPE; this_day varchar2(32); this_id env_table.id%TYPE; hits number(8); begin pulsar_util.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(env.next(this)) loop -- for this_row in c_remote_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_remote_host; loop fetch c_remote_host into this_remote_host, hits; exit when c_remote_host%NOTFOUND; if i = 1 then htp.tableRowOpen; end if; -- htp.tableData(htf.anchor('http://kayak.npac.syr.edu:2020/cgi-bin/wow/env.show_cmd?iid='||this.id, this.id)); htp.tableData(this_remote_host); htp.tableData(htf.anchor(cgi_url||'wow/env.show_cmd?isearchBy='||replace(isearchBy, ' ')||chr(ascii('&'))||'ilimit='||this_remote_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_http_referer; loop fetch c_http_referer into this_http_referer, hits; exit when c_http_referer%NOTFOUND; if i = 1 then htp.tableRowOpen; end if; htp.tableData(this_http_referer); htp.tableData(htf.anchor(cgi_url||'wow/env.show_cmd?isearchBy='||replace(isearchBy, ' ')||chr(ascii('&'))||'ilimit='||this_http_referer ||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/env.show_cmd?isearchBy='||replace(isearchBy, ' ')||chr(ascii('&'))||'ilimit=' htp.tableData(htf.anchor(cgi_url||'wow/env.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; if (isearchBy like 'Access ID') then 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_id; loop fetch c_id into this; exit when c_id%NOTFOUND; if i = 1 then htp.tableRowOpen; end if; -- htp.tableData(htf.anchor('http://kayak.npac.syr.edu:2020/cgi-bin/wow/env.show_cmd?iid='||this.id, this.id)); htp.tableData(this.id); htp.tableData(to_char(this.day, 'MON-DD-YYYY HH24:MI:SS')); htp.tableData(this.remote_host); htp.tableData(this.http_referer); 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 env_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')) -- where (day = to_date(ilimit, 'MON-DD-YYYY')) order by day desc; cursor c_month is select to_char(day, 'YYYY-MM-DD'), count(*) from env_table where (to_date(to_char(day, 'MON-YYYY'), 'MON-YYYY') = to_date(ilimit, 'MON-YYYY')) group by to_char(day, 'YYYY-MM-DD') order by to_char(day, 'YYYY-MM-DD') desc; cursor c_remote_host is select * from env_table where (day<=ildate2 and day>=ildate1) and (remote_host = ilimit) order by day desc; cursor c_http_referer is select * from env_table where (day<=ildate2 and day>=ildate1) and (http_referer = ilimit) order by day desc; begin pulsar_util.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.remote_host); htp.tableData(this.http_referer); 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_remote_host; loop fetch c_remote_host into this; exit when c_remote_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.remote_host); htp.tableData(this.http_referer); 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_http_referer; loop fetch c_http_referer into this; exit when c_http_referer%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.remote_host); htp.tableData(this.http_referer); 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(cgi_url||'wow/env.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_remote_host; -- close c_http_referer; end; --------------------------------------------------------- procedure insert_action is --------------------------------------------------------- begin put(owa.current_env); exception when others then cw_error.panic('env.insert_action: ', sqlerrm); end; -- --------------------------------------------------------- procedure rm (iid in number) is --------------------------------------------------------- begin delete from env_table where id = iid; -- htp.p('rm:iid='||to_char(iid)); exception when others then cw_error.panic('env.rm iid=' || to_char(iid),sqlerrm); end; -- --------------------------------------------------------- function get(iid in number) return env_table%rowtype is --------------------------------------------------------- cursor c (this_id number) is select * from env_table the where the.id = this_id; begin open c (iid); fetch c into this; if c%notfound then raise no_data_found; end if; close c; return this; exception when no_data_found then raise; when others then cw_error.panic('env.get iid=' || to_char(iid),sqlerrm); end; -- --------------------------------------------------------- procedure put(iorow in out env_table%rowtype) is --------------------------------------------------------- cursor c (this_id number) is select * from env_table where id = this_id for update of day; begin if (iorow.id is null) then select env_seq.nextval into iorow.id from dual; insert into env_table values --( iorow.id, iorow.day, iorow.auth_type, iorow.gateway_interface, ( iorow.id, sysdate, iorow.auth_type, iorow.gateway_interface, iorow.http_user_agent, iorow.remote_host, iorow.remote_addr, iorow.remote_user,iorow.remote_ident,iorow.server_protocol, iorow.server_software,iorow.server_name,iorow.server_port, iorow.request_method,iorow.http_accept, iorow.http_referer,iorow.path_info,iorow.path_translated, iorow.script_name,iorow.query_string,iorow.content_type, iorow.content_length,iorow.annotation_server ); else open c (iorow.id); fetch c into this; if c%found then update env_table set id = iorow.id, day = iorow.day, auth_type = iorow.auth_type, gateway_interface = iorow.gateway_interface, http_user_agent = iorow.http_user_agent, remote_host = iorow.remote_host, remote_addr = iorow.remote_addr, remote_user = iorow.remote_user, remote_ident = iorow.remote_ident, server_protocol = iorow.server_protocol, server_software = iorow.server_software, server_name = iorow.server_name, server_port = iorow.server_port, request_method = iorow.request_method, http_accept = iorow.http_accept, http_referer = iorow.http_referer, path_info = iorow.path_info, path_translated = iorow.path_translated, script_name = iorow.script_name, query_string = iorow.query_string, content_type = iorow.content_type, content_length = iorow.content_length, annotation_server = iorow.annotation_server where id = iorow.id; else raise no_data_found; end if; close c; end if; exception when others then cw_error.panic('env.put iorow.id=' ||iorow.id, sqlerrm); end; -- --------------------------------------------------------- function new(iorow in out env_table%rowtype) return env_table.id%type is --------------------------------------------------------- begin iorow.id := null; put(iorow); return iorow.id; exception when others then cw_error.panic('env.new iorow.id=' ||iorow.id, sqlerrm); end; -- --------------------------------------------------------- function next(ionrow in out env_table%rowtype) return boolean is --------------------------------------------------------- begin if not nrow%isopen then open nrow; end if; fetch nrow into ionrow; if nrow%notfound then close nrow; return false; else return true; end if; exception when others then cw_error.panic('env.next',sqlerrm); end; --------------------------------------------------------- -- begin -- this.id := 0; -- end; --------------------------------------------------------- --------------------------------------------------------- end; --------------------------------------------------------- / show errors; -- /