drop package agway; / create or replace package agway is procedure search; procedure search(typein varchar2,op integer,fid varchar2); procedure search(op integer,fid varchar2); procedure search_button(op integer,fid varchar2); procedure by_pca(fid varchar2,p varchar2); procedure by_pca(fid varchar2); procedure keyword(p varchar2,fid varchar2,op integer); procedure notyet; procedure list(p varchar2,c varchar2, n varchar2, m varchar2,pr varchar2,d varchar2,t varchar2); procedure on_sale; procedure show(p varchar2); function tr(name varchar2) return varchar2; function th(name varchar2) return varchar2; function td(name varchar2) return varchar2; html constant varchar2(60) := '/agway/'; images constant varchar2(60) := html||'images/'; cgi_bin constant varchar2(60) := '/cgi-bin/agway/wow/'; username constant varchar2(10) := 'agway'; password constant varchar2(10) := 'demo'; -- client varchar2(30) := owa.remote_addr; time_out number(8,5) := 5./1440.; help_page constant varchar2(100) := htf.url('http://naos.npac.syr.edu:1963/demo/help.html','Help'); end; / show errors; create or replace package body agway is total_visitors number(7); total number(8); hh_time date; two prod%ROWTYPE; h_time varchar2(50) := '
'; h_desc varchar2(2000); h_caption varchar2(600); -- for date TYPE wordstype IS TABLE OF varchar2(50) INDEX BY BINARY_INTEGER; word_tab wordstype; w_count BINARY_INTEGER; date_tag char(1); function td(name varchar2) return varchar2 is begin return (''||name||''); end; function th(name varchar2) return varchar2 is begin return (''||name||''); end; function tr(name varchar2) return varchar2 is begin return (''||name||''); end; procedure search_button(op integer,fid varchar2) is cursor cfolder is select distinct category from prod order by category; begin htp.p(''); htp.p('
'); htp.p(''); htp.p(''); htp.p(htf.formClose); htp.p('
'); htp.formOpen(cgi_bin||'agway.search'); --htp.p(htf.url_top('http://www.npac.syr.edu/','')); htp.p('Find Agway Products: '); htp.p(' '); htp.p(' '); htp.p(help_page); htp.p('
By: '); htp.formSelectOpen('op'); if (op = 0) then htp.p('Category:'); htp.p('
'); end; procedure search is begin --search(6,'0'); search_button(6,'0'); on_sale; end; procedure on_sale is cursor c is select * from prod where price = (select min(price) from prod); begin htp.p('
'); htp.p('
'); h_caption := 'On-Sale Agway Products This Week'; htp.p(''); htp.p(tr(th('Part No.')||th('Category')||th('Product Name')||th('Manufacturer')||th('Price')|| th('Description')||th('Tips'))); open c; loop fetch c into two; exit when c%NOTFOUND; list(htf.url(cgi_bin||'agway.show?p='||two.part_no,two.part_no), two.category,two.name,two.manufacturer,two.price, two.description,htf.url(html||two.tips,'How to use '||two.name)); end loop; close c; htp.p('
'); htp.p(h_caption||'
'); htp.p('
'); wow.sig; end; procedure search(op integer,fid varchar2) is begin -- search_button(op,fid); search(NULL,op,fid); end; procedure search(typein varchar2,op integer,fid varchar2) is begin search_button(op,fid); if ((op = 6 ) and fid='0') then by_pca(fid); return; end if; if (op=6) then by_pca(fid,NULL); return; end if; if (op=1) then by_pca(fid,lower(typein)); return; end if; if (op=2) then on_sale; return; end if; if (op=0 or op=3 or op=4 or op=5) then keyword(lower(typein),fid,op); return; end if; notyet; wow.sig; end; procedure keyword(p varchar2,fid varchar2,op integer) is cursor cuser(p varchar2) is select * from prod where (lower(description) like p) or (lower(category) like p) or (lower(name) like p) or (lower(manufacturer) like p) order by part_no asc, category asc; cursor cuser1(fid varchar2,p varchar2) is select * from prod where (category=fid) and ( (lower(description) like p) or (lower(name) like p) or (lower(manufacturer) like p)) order by part_no asc,category asc; cursor cuser3(p varchar2) is select * from prod where (lower(name) like p) order by part_no asc, category asc; cursor cuser31(fid varchar2,p varchar2) is select * from prod where (category=fid) and (lower(name) like p) order by part_no asc,category asc; cursor cuser4(p varchar2) is select * from prod where (lower(manufacturer) like p) order by part_no asc, category asc; cursor cuser41(fid varchar2,p varchar2) is select * from prod where (category=fid) and (lower(manufacturer) like p) order by part_no asc,category asc; cursor c1(n number) is select * from prod where price >= n order by part_no asc,category asc; cursor c2(n number) is select * from prod where price <= n order by part_no asc,category asc; cursor c3(n number) is select * from prod where price = n order by part_no asc,category asc; cursor c11(n number,fid varchar2) is select * from prod where (category=fid) and price >= n order by part_no asc,category asc; cursor c22(n number,fid varchar2) is select * from prod where (category=fid) and price <= n order by part_no asc,category asc; cursor c33(n number,fid varchar2) is select * from prod where (category=fid) and price = n order by part_no asc,category asc; n prod.price%TYPE; np varchar2(40); begin htp.p('
'); htp.p('
'); if fid = '0' then if (op = 0) then open cuser('%'||lower(p)||'%'); h_caption := 'Agway Products with Keyword '||htf.bold(p); elsif (op=3) then open cuser3('%'||lower(p)||'%'); h_caption := 'Agway Products with Keyword '||htf.bold(p)||' in Product Names'; elsif (op=4) then open cuser4('%'||lower(p)||'%'); h_caption := 'Agway Products with Keyword '||htf.bold(p)||' in Manufactures'; else if (p like '%>%') then np := replace(p,'>'); n := replace(np,'='); open c1(n); h_caption := 'Agway Products with Price >= '||htf.bold(n); elsif (p like '%<%') then np := replace(p,'<'); n := replace(np,'='); open c2(n); h_caption := 'Agway Products with Price <= '||htf.bold(n); else n := replace(p,'='); open c3(n); h_caption := 'Agway Products with Price = '||htf.bold(n); end if; end if; else if (op = 0) then open cuser1(fid,'%'||lower(p)||'%'); h_caption := 'Agway Products in Category '||htf.bold(fid)||' with Keyword '||htf.bold(p); elsif (op=3) then open cuser31(fid,'%'||lower(p)||'%'); h_caption := 'Agway Products in Category '||htf.bold(fid)||' with Keyword '||htf.bold(p)|| ' in Product Names'; elsif (op=4) then open cuser41(fid,'%'||lower(p)||'%'); h_caption := 'Agway Products in Category '||htf.bold(fid)||' with Keyword '||htf.bold(p)|| ' in Manufactures'; else if (p like '%>%') then np := replace(p,'>'); n := replace(np,'='); open c11(n,fid); h_caption := 'Agway Products in Category '||htf.bold(fid)|| ' with Price >= '||htf.bold(n); elsif (p like '%<%') then np := replace(p,'<'); n := replace(np,'='); open c22(n,fid); h_caption := 'Agway Products in Category '||htf.bold(fid)|| ' with Price <= '||htf.bold(n); else n := replace(p,'='); open c33(n,fid); h_caption := 'Agway Products in Category '||htf.bold(fid)|| ' with Price = '||htf.bold(n); end if; end if; end if; htp.p(''); htp.p(tr(th('Part No.')||th('Category')||th('Product Name')||th('Manufacturer')||th('Price')|| th('Description')||th('Tips'))); loop if fid = '0' then if (op = 0) then fetch cuser into two; exit when cuser%NOTFOUND; elsif (op=3) then fetch cuser3 into two; exit when cuser3%NOTFOUND; elsif (op=4) then fetch cuser4 into two; exit when cuser4%NOTFOUND; else if (p like '%>%') then fetch c1 into two; exit when c1%NOTFOUND; elsif (p like '%<%') then fetch c2 into two; exit when c2%NOTFOUND; else fetch c3 into two; exit when c3%NOTFOUND; end if; end if; else if (op = 0) then fetch cuser1 into two; exit when cuser1%NOTFOUND; elsif (op=3) then fetch cuser31 into two; exit when cuser31%NOTFOUND; elsif (op = 4) then fetch cuser41 into two; exit when cuser41%NOTFOUND; else if (p like '%>%') then fetch c1 into two; exit when c1%NOTFOUND; elsif (p like '%<%') then fetch c2 into two; exit when c2%NOTFOUND; else fetch c3 into two; exit when c3%NOTFOUND; end if; end if; end if; list(htf.url(cgi_bin||'agway.show?p='||two.part_no,two.part_no), two.category,two.name,two.manufacturer,two.price, two.description,htf.url(html||two.tips,'How to use '||two.name)); end loop; htp.p('
'); htp.p(h_caption||'
'); htp.p('
'); wow.sig; end; procedure by_pca(fid varchar2) is begin by_pca(fid,NULL); end; procedure by_pca(fid varchar2,p varchar2) is cursor cuser(fid varchar2) is select * from prod where (category=fid) order by part_no asc,category asc; cursor cuser1(fid varchar2,p varchar2) is select * from prod where (category=fid) and (lower(part_no) like '%'||p||'%') order by part_no asc,category asc; cursor cuser2(p varchar2) is select * from prod where (lower(part_no) like '%'||p||'%') order by part_no asc,category asc; cursor cuser3 is select * from prod order by part_no asc,category asc; tag integer := 0; begin -- search_button(1,fid); if p IS NULL then if (fid = '0') then open cuser3; tag := 3; h_caption := 'List of All Agway Products'; else open cuser(fid); tag := 0; h_caption := 'List of All Agway Products in Category '||htf.bold(fid); end if; elsif (fid = '0') then open cuser2(p); tag := 2; h_caption := 'List of All Agway Products with Product No. contains '||htf.bold(p); else open cuser1(fid,p); tag := 1; h_caption := 'List of All Agway Products with Product No. contains '|| htf.bold(p)||' in Category '||htf.bold(fid); end if; htp.p('
'); htp.p('
'); htp.p(''); htp.p(tr(th('Part No.')||th('Category')||th('Product Name')||th('Manufacturer')||th('Price')|| th('Description')||th('Tips'))); loop if tag=0 then fetch cuser into two; exit when cuser%NOTFOUND; elsif tag=2 then fetch cuser2 into two; exit when cuser2%NOTFOUND; elsif tag=1 then fetch cuser1 into two; exit when cuser1%NOTFOUND; else fetch cuser3 into two; exit when cuser3%NOTFOUND; end if; if (two.description IS NULL) then h_desc := '
'; else h_desc := two.description; end if; list(htf.url(cgi_bin||'agway.show?p='||two.part_no,two.part_no), two.category,two.name,two.manufacturer,two.price, two.description,htf.url(html||two.tips,'How to use '||two.name)); end loop; if tag=0 then close cuser; elsif tag=2 then close cuser2; elsif tag=1 then close cuser1; else close cuser3; end if; htp.p('
'||h_caption||'
'); htp.p('
'); wow.sig; end; procedure list(p varchar2,c varchar2, n varchar2, m varchar2,pr varchar2,d varchar2,t varchar2) is begin htp.p(''||td(p)||td(c)|| td(n)||td(m)); htp.p(td(pr)||''||d||''||''|| t||''); end; procedure show(p varchar2) is begin htp.p(''); select * into two from prod where part_no=p; htp.p('

'); htp.p('
'); htp.p('

'||two.description||''); htp.p('

'); htp.p('

'); htp.p('

'||htf.bold('Product Name: ')||td(two.name)); htp.p('

'||htf.bold('Category: ')||td(two.category)); htp.p('

'||htf.bold('Manufacturer: ')||td(two.manufacturer)); htp.p('

'||htf.bold('Price: ')||td(two.price)); htp.p('

'||htf.bold('Part No.: ')||td(two.part_no)); htp.p('

'||htf.bold('Tips: ')||td(htf.url(html||two.tips,'How to use '||two.name))); -- htp.p(''); htp.p('

'); htp.p('
'); wow.sig; end; procedure notyet is begin --search_button(6,0); htp.p('
Sorry, this function has not implemented yet in the demo. Stay tuned!'); end; end agway; / show errors; /