set echo on;
/*                                                                       */
/* address.pac is copyright 1997 by Gerald J. Klaas  gklaas@sacto.com */
/* address.pac is freely distributable for non-commercial purposes       */
/* as long as credit is given to the author and comments are left intact */
/*                                                                       */
set echo off;
/* address.pac is dependent on a second package called gjk (cookie_jar).     */
/*             To compile the address application, you should first create   */
/*             tables adds and cookie_jar, then compile gjk, then address    */
/*             (This assumes that you already have Oracle Webserver up       */
/*              and running with HTF and OWA_UTIL packages)                  */
/*                                                                           */
/* History:                                                                  */
/* 10/15/96                                                                  */
/* address.pac is Gerald's first application written for Oracle Webserver    */
/* this code is available at http://www.calweb.com/~argek/oracle/address.htm */
/* 11/2/96                                                                   */
/* I plan to implement a cookie_jar for this script so that I can allow      */
/* view only to some users, update to specific users, and delete to few.     */
/* Right now, access is controlled through user/password on the WWW service, */
/* so all users have insert, update and delete rights.  -- gjk 11/2/96       */
set scan off;
create or replace package address is
procedure query_form (cookie in varchar2 default null);
procedure query      (cookie in varchar2 default null,
		      lname in varchar2 default null, 
                     fname in varchar2 DEFAULT NULL,
                     street in varchar2 DEFAULT NULL,
                     street2 in varchar2 DEFAULT NULL,
                     city_st in varchar2 DEFAULT NULL,
                     zip in varchar2 DEFAULT NULL,
                     hphone in varchar2 DEFAULT NULL,
                     wphone in varchar2 DEFAULT NULL,
                     ophone in varchar2 DEFAULT NULL,
                     kids in varchar2 DEFAULT NULL,
                     email in varchar2 DEFAULT NULL,
                     comments in varchar2 DEFAULT NULL);
procedure label      (cookie in varchar2 default null,
		      seq_no in number, 
		      i_font in number DEFAULT NULL);  
procedure buffalo    (cookie in varchar2 default null,
		      seq_no in number);
procedure netlp      (cookie in varchar2 default null,
		      seq_no in number, 
		      i_font in number DEFAULT NULL);  
procedure r_query    (cookie in varchar2 default null,
		      seq_no in number);
procedure remove_it  (cookie in varchar2 default null,
		      seq_no in number);
procedure update_form (cookie in varchar2 default null,
		       seq_no in number);
procedure update_it (cookie in varchar2 default null,
		     seq_no in number,
                     lname in varchar2 DEFAULT NULL,
                     fname in varchar2 DEFAULT NULL,
                     street in varchar2 DEFAULT NULL,
                     street2 in varchar2 DEFAULT NULL,
                     city_st in varchar2 DEFAULT NULL,
                     zip in varchar2 DEFAULT NULL,
                     hphone in varchar2 DEFAULT NULL,
                     wphone in varchar2 DEFAULT NULL,
                     ophone in varchar2 DEFAULT NULL,
                     kids in varchar2 DEFAULT NULL,
                     email in varchar2 DEFAULT NULL,
                     comments in varchar2 DEFAULT NULL);
procedure insert_form (cookie in varchar2 default null);
end;
/
create or replace package body address is

/*---------------------------------------------------------------------------*/
procedure query_form (cookie in varchar2 default null) is
    rolebuf varchar(10) := 1;
    me varchar(10);
    no_cookie exception;
begin
  rolebuf := gjk.cookie_chk(cookie);
     if ( rolebuf < 1 )
	   then raise no_cookie;
     end if;
  htp.htmlOpen;
  htp.headOpen;
  htp.htitle('Address Book Query Form');
  htp.headClose;
  htp.bodyOpen;
--      htp.header(3,'Enter a search string in one or more fields.');
      htp.p('Enter a search string in one or more fields.');
      htp.p(' (Leave blank for wildcard)');
      htp.nl;
  htp.formOpen('address.query');
  htp.formHidden('cookie',cookie);
  htp.tableOpen(1);
  htp.tableRowOpen;
  htp.tableData('First Name');
  htp.tableData(htf.formText('fname','20','30'));
  htp.tableData('Last Name');
  htp.tableData(htf.formText('lname','20','30'));
  htp.tableRowClose;
  htp.tableRowOpen;
  htp.tableData('Street Address');
  htp.tableData(htf.formText('street','20','30'));
  htp.tableData('Home Phone');
  htp.tableData(htf.formText('hphone','20','15'));
  htp.tableRowClose;
  htp.tableRowOpen;
  htp.tableData('Street 2nd Line');
  htp.tableData(htf.formText('street2','20','30'));
  htp.tableData('Work Phone');
  htp.tableData(htf.formText('wphone','20','15'));
  htp.tableRowClose;
  htp.tableRowOpen;
  htp.tableData('City and State');
  htp.tableData(htf.formText('city_st','20','30'));
  htp.tableData('Other Phone');
  htp.tableData(htf.formText('ophone','20','30'));
  htp.tableRowClose;
  htp.tableRowOpen;
  htp.tableData('Zip');
  htp.tableData(htf.formText('zip','20','10'));
  htp.tableData('E-mail');
  htp.tableData(htf.formText('email','20','50'));
  htp.tableRowClose;
  htp.tableClose;
  htp.formSubmit;
  htp.formClose;
  htp.bodyClose;
  htp.htmlClose;
  gjk.localvar('SetFocus','0','1');
EXCEPTION
  when no_cookie then gjk.login_form('address.query_form?');
  when others then htp.print('You hit an unhandled exception in query_form');
	           htp.p('<P>Error Message: '||SQLERRM);
end query_form;

/*---------------------------------------------------------------------------*/
procedure query (cookie in varchar2 default null,
		      lname in varchar2 default null, 
                     fname in varchar2 DEFAULT NULL,
                     street in varchar2 DEFAULT NULL,
                     street2 in varchar2 DEFAULT NULL,
                     city_st in varchar2 DEFAULT NULL,
                     zip in varchar2 DEFAULT NULL,
                     hphone in varchar2 DEFAULT NULL,
                     wphone in varchar2 DEFAULT NULL,
                     ophone in varchar2 DEFAULT NULL,
                     kids in varchar2 DEFAULT NULL,
                     email in varchar2 DEFAULT NULL,
                     comments in varchar2 DEFAULT NULL) is
  q_cursor      integer;
  q_sql         varchar2(400);
  q_seq_no      number;
  ignore        integer;
  c1            adds%ROWTYPE;
begin
  htp.htmlOpen;
  htp.headOpen;
  htp.htitle('Address Book Listing');
  htp.headClose;
  htp.bodyOpen;
  htp.tableOpen(1);
   q_cursor := dbms_sql.open_cursor;
   q_sql := 'select seq from adds ';
   q_sql := q_sql || 'where 1=1 ';
     if fname is not NULL then
   q_sql := q_sql || 'and upper(fname) like' || ' ''%' || upper(fname) || '%'' ';
     end if;
     if lname is not NULL then
   q_sql := q_sql || 'and upper(lname) like' || ' ''%' || upper(lname) || '%'' ';
     end if;
     if street is not NULL then
   q_sql := q_sql || 'and upper(street) like' || ' ''%' || upper(street) || '%'' ';
     end if;
     if street2 is not NULL then
   q_sql := q_sql || 'and upper(street2) like' || ' ''%' || upper(street2) || '%'' ';
     end if;
     if city_st is not NULL then
   q_sql := q_sql || 'and upper(city_st) like' || ' ''%' || upper(city_st) || '%'' ';
     end if;
     if zip is not NULL then
   q_sql := q_sql || 'and zip like' || ' ''%' || zip || '%'' ';
     end if;
     if email is not NULL then
   q_sql := q_sql || 'and upper(email) like' || ' ''%' || upper(email) || '%'' ';
     end if;
     if hphone is not NULL then
   q_sql := q_sql || 'and hphone like' || ' ''%' || hphone || '%'' ';
     end if;
     if wphone is not NULL then
   q_sql := q_sql || 'and wphone like' || ' ''%' || wphone || '%'' ';
     end if;
     if ophone is not NULL then
   q_sql := q_sql || 'and ophone like' || ' ''%' || ophone || '%'' ';
     end if;
   q_sql := q_sql || 'order by lname,fname ';
    dbms_sql.parse(q_cursor,q_sql,DBMS_SQL.V7);
    dbms_sql.define_column(q_cursor, 1, q_seq_no);
    ignore := dbms_sql.execute(q_cursor);
	loop
     if dbms_sql.fetch_rows(q_cursor)>0 then
    dbms_sql.column_value(q_cursor, 1, q_seq_no);
   select * into c1 from adds where seq=q_seq_no;
  htp.tableRowOpen;
      if c1.street2 is not null then
  htp.tableData(c1.fname||' '||c1.lname||'<BR>'||c1.street||'<BR>'
			||c1.street2||'<BR>'||c1.city_st||' '||c1.zip||'<BR>'||
                        htf.mailto(c1.email,c1.email));
  else
  htp.tableData(c1.fname||' '||c1.lname||'<BR>'||c1.street||'<BR>'
			||c1.city_st||' '||c1.zip||'<BR>'||
                        htf.mailto(c1.email,c1.email));
      end if;
  htp.tableData('Home<BR>'||c1.hphone||'<BR>Work<BR>'||c1.wphone);
  htp.tableData('Other<BR>'||c1.ophone||'<BR>LMB<BR>'||c1.last_mod_by);
  htp.tableData(
                htf.anchor('address.update_form?cookie='||cookie||
			    '&seq_no='||to_char(c1.seq),
			   'update')
	       ||'<BR>'||
                htf.anchor('address.r_query?cookie='||cookie||
			   '&seq_no='||to_char(c1.seq),'delete')
	       ||'<BR>'||
                htf.anchor('address.label?cookie='||cookie||
			   '&seq_no='||to_char(c1.seq),
			   'label')
               );
  htp.tableRowClose;
  else 
  exit;
  end if;
  end loop;
  htp.tableClose;
      htp.print('<I>No more records found</I>');
      htp.nl;
  htp.anchor('address.insert_form?cookie='||cookie,'Insert a new record');
  htp.nl;
  htp.anchor('address.query_form?cookie='||cookie,'Process another query');
  htp.bodyClose;
  htp.htmlClose;
exception
  when others then htp.print('You hit an unhandled exception query');
                   htp.p('<P>Error Message: '||SQLERRM);
end query;

/*---------------------------------------------------------------------------*/
procedure label (cookie in varchar2 default null,
		 seq_no in number,
		 i_font in number DEFAULT NULL) is
cursor label_this is
select * from adds where adds.seq=seq_no;
f_buf number;     
p_buf varchar(30);
begin
  if i_font is null
     then f_buf := 3;
     else f_buf := i_font;
     end if;
  htp.htmlOpen;
  htp.headOpen;
  htp.headClose;
  htp.bodyOpen;
for c1 in label_this
  loop
      htp.print('<H'||f_buf||'>');
      htp.print(c1.fname||' '||c1.lname||'<BR>');
      htp.print(c1.street||'<BR>');
      if c1.street2 is not null then
         htp.print(c1.street2||'<BR>');
      end if;
      htp.print(c1.city_st||' '||c1.zip||'</H'||f_buf||'><P>');
  end loop;
  htp.para;
  htp.nl;
  htp.para;
  if f_buf < 5
  then
  htp.anchor('address.label?cookie='||cookie||
	     '&seq_no='||to_char(seq_no)||'&i_font='
	     ||to_char(f_buf+1),'Smaller Font');
  end if;
  htp.br;
  if f_buf > 1
  then
  htp.anchor('address.label?cookie='||cookie||
	     '&seq_no='||to_char(seq_no)||'&i_font='
	     ||to_char(f_buf-1),'Larger Font');
  end if;
  htp.br;
  htp.anchor('address.buffalo?cookie='||cookie||
	     '&seq_no='||to_char(seq_no)
	     ,'Pass this address to the Address Checker/Envelope GIF maker');
  htp.br;
  htp.anchor('address.netlp?cookie='||cookie||
	     '&seq_no='||to_char(seq_no)||'&i_font='
	      ||to_char(f_buf),'Envelope print page');
  htp.bodyClose;
  htp.htmlClose;
EXCEPTION
  when others then htp.print('You hit an unhandled exception in label');
	           htp.p('<P>Error Message: '||SQLERRM);
end label;

/*---------------------------------------------------------------------------*/
procedure buffalo (cookie in varchar2 default null,
		   seq_no in number) is
cursor send_this is
select * from adds where adds.seq=seq_no;
p_buf varchar(30);
begin
  htp.htmlOpen;
  htp.headOpen;
  htp.headClose;
  htp.bodyOpen;
   htp.print('This page will send you to a <I>very cool</I> service ');
   htp.print('maintained by <a href="mailto:ajay@cedar.buffalo.edu">');
   htp.print('Ajay Shekhawat</a>.<P>');
   htp.print('Given a <B><I>valid U.S. postal address</I></B>, this server ');
   htp.print('attempts to rewrite the address in the proper format along ');
   htp.print('with the ZIP+4 code.  If it is successful, you can retrieve  ');
   htp.print('a <B>Postscript</B> or <B>GIF</B> image file of the address  ');
   htp.print('suitable for printing, including the POSTNET barcode!<br>  ');
   htp.print('You can also jump to a street map of the address site.<br>  ');
      htp.nl;
  htp.formOpen('http://www.cedar.buffalo.edu/htbin/nasciic');
  htp.formtextareaOpen('address',4,40);
for c1 in send_this
  loop
  p_buf := c1.lname;
  htp.print(c1.fname||' '||c1.lname);
  htp.print(c1.street);
  htp.print(c1.city_st||' '||c1.zip);
  end loop;
  htp.formtextareaClose;
  htp.formSubmit;
  htp.formClose;
  htp.bodyClose;
  htp.htmlClose;
end buffalo;

/*---------------------------------------------------------------------------*/
procedure netlp (cookie in varchar2 default null,
		 seq_no in number, 
		 i_font in number DEFAULT NULL) is
cursor label_this is
select * from adds where adds.seq=seq_no;
f_buf number;     
p_buf varchar(30);
begin
  if i_font is null
     then f_buf := 3;
     else f_buf := i_font;
     end if;
  htp.htmlOpen;
  htp.headOpen;
  htp.headClose;
  htp.bodyOpen;
for c1 in label_this
  loop
      htp.print('<H'||f_buf||'>');
      htp.print(upper(c1.fname)||' '||upper(c1.lname)||'<BR>');
      htp.print(upper(c1.street)||'<BR>');
      if c1.street2 is not null then
         htp.print(upper(c1.street2)||'<BR>');
      end if;
      htp.print(upper(c1.city_st)||' '||c1.zip||'</H'||f_buf||'>');
  end loop;
  htp.para;
  htp.nl;
  htp.bodyClose;
  htp.htmlClose;
end netlp;

/*---------------------------------------------------------------------------*/
procedure r_query (cookie in varchar2 default null,
		   seq_no in number) is
cursor rm_this is
select * from adds where adds.seq=seq_no;
    p_buf      varchar(30);
    rolebuf    varchar(10) := 0;
    me         varchar(10);
    no_cookie  exception;
    role_level exception;
begin
  rolebuf := gjk.cookie_chk(cookie);
     if ( rolebuf < 1 )
	   then raise no_cookie;
     end if;
  rolebuf := substr(rolebuf,8,1);
     if ( rolebuf < 0 )
           then raise role_level;
     end if;
  htp.htmlOpen;
  htp.headOpen;
  htp.headClose;
  htp.bodyOpen;
      htp.print('The following record will be deleted.');
      htp.nl;
  htp.tableOpen(1);
for c1 in rm_this
  loop
  p_buf := c1.lname;
  htp.tableRowOpen;
  htp.tableData(c1.fname||' '||c1.lname||'<BR>'||c1.street||'<BR>'
			||c1.city_st||' '||c1.zip);
  htp.tableData('Home<BR>'||c1.hphone);
  htp.tableData('Work<BR>'||c1.wphone);
  htp.tableData(c1.comments);
  htp.tableRowClose;
  end loop;
  htp.tableClose;
      htp.print('Are you sure?');
  htp.anchor('address.remove_it?cookie='||cookie||
	     '&seq_no='||to_char(seq_no),'Yes');
  htp.print('/');
  htp.anchor('address.query?cookie='||cookie||
	     '&lname='||p_buf,'No');
  htp.bodyClose;
  htp.htmlClose;
EXCEPTION
  when no_cookie then gjk.login_form('address.r_query?seq_no='||seq_no||'&');
  when role_level then htp.print('You do not have permission to do this.');
  when others then htp.print('You hit an unhandled exception in r_query');
	           htp.p('<P>Error Message: '||SQLERRM);
end r_query;

/*---------------------------------------------------------------------------*/
procedure remove_it (cookie in varchar2 default null,
		     seq_no in number) is
cursor rm_this is
select * from adds where adds.seq=seq_no;
    p_buf varchar(30);
    rolebuf    varchar(10) := 0;
    me         varchar(10);
    no_cookie  exception;
    role_level exception;
begin
  rolebuf := gjk.cookie_chk(cookie);
     if ( rolebuf < 1 )
	   then raise no_cookie;
     end if;
  rolebuf := substr(rolebuf,8,1);
     if ( rolebuf < 7 )
           then raise role_level;
     end if;
  htp.htmlOpen;
  htp.headOpen;
  htp.htitle('Item Deleted!');
  htp.headClose;
  htp.bodyOpen;
      htp.print('The following record was deleted.');
      htp.nl;
  htp.tableOpen(1);
for c1 in rm_this
  loop
  p_buf := c1.lname;
  htp.tableRowOpen;
  htp.tableData(c1.fname||' '||c1.lname||'<BR>'||c1.street||'<BR>'
			||c1.city_st||' '||c1.zip);
  htp.tableData('Home<BR>'||c1.hphone);
  htp.tableData('Work<BR>'||c1.wphone);
  htp.tableData(c1.comments);
  htp.tableRowClose;
  end loop;
  htp.tableClose;
  htp.anchor('address.query?cookie='||cookie||
	     '&lname='||p_buf,'Return');
  htp.bodyClose;
  htp.htmlClose;
delete from adds where adds.seq=seq_no;
EXCEPTION
  when no_cookie then gjk.login_form('address.remove_it?seq_no='||seq_no||'&');
  when role_level then htp.print('You do not have permission to do this.');
  when others then htp.print('You hit an unhandled exception in remove_it');
	           htp.p('<P>Error Message: '||SQLERRM);
end remove_it; 

/*---------------------------------------------------------------------------*/
procedure update_form (cookie in varchar2 default null,
		       seq_no in number) is
  c1      adds%ROWTYPE;
  p_buf   varchar(30);
begin
  select * into c1 from adds where adds.seq=seq_no;
  htp.htmlOpen;
  htp.headOpen;
  htp.htitle('Update the Address Book');
  htp.headClose;
  htp.bodyOpen;
  p_buf := c1.lname;
  htp.formOpen('address.update_it');
  htp.formHidden('cookie',cookie);
  htp.formHidden('seq_no',c1.seq);
  htp.tableOpen(1);
  htp.tableRowOpen;
  htp.tableData('First Name');
  htp.tableData(htf.formText('fname','20','30',c1.fname));
  htp.tableData('Last Name');
  htp.tableData(htf.formText('lname','20','30',c1.lname));
  htp.tableRowClose;
  htp.tableRowOpen;
  htp.tableData('Street Address');
  htp.tableData(htf.formText('street','20','30',c1.street));
  htp.tableData('Home Phone');
  htp.tableData(htf.formText('hphone','20','15',c1.hphone));
  htp.tableRowClose;
  htp.tableRowOpen;
  htp.tableData('Street 2nd Line');
  htp.tableData(htf.formText('street2','20','30',c1.street2));
  htp.tableData('Work Phone');
  htp.tableData(htf.formText('wphone','20','15',c1.wphone));
  htp.tableRowClose;
  htp.tableRowOpen;
  htp.tableData('City and State');
  htp.tableData(htf.formText('city_st','20','30',c1.city_st));
  htp.tableData('Other Phone');
  htp.tableData(htf.formText('ophone','20','30',c1.ophone));
  htp.tableRowClose;
  htp.tableRowOpen;
  htp.tableData('Zip');
  htp.tableData(htf.formText('zip','20','10',c1.zip));
  htp.tableData('E-mail');
  htp.tableData(htf.formText('email','20','50',c1.email));
  htp.tableRowClose;
  htp.tableRowOpen;
  htp.tableData('Kids');
  htp.tableData(htf.formText('kids','50','50',c1.kids),'','','','','3');
  htp.tableRowClose;
  htp.tableRowOpen;
  htp.tableData('Comments');
  htp.tableData(htf.formText('comments','50','50',c1.comments),'','','','','3');
  htp.tableRowClose;
  htp.tableClose;
  htp.nl;
  htp.formSubmit('','Submit changes');
  htp.formClose;
  htp.anchor('address.query?cookie='||cookie||
	     '&lname='||p_buf,'Return to Address Book Listing');
  gjk.localvar('SetFocus','0','2');
  htp.bodyClose;
  htp.htmlClose;
end update_form; 

/*---------------------------------------------------------------------------*/
procedure update_it (cookie in varchar2 default null,
		     seq_no in number,
                     lname in varchar2 DEFAULT NULL,
                     fname in varchar2 DEFAULT NULL,
                     street in varchar2 DEFAULT NULL,
                     street2 in varchar2 DEFAULT NULL,
                     city_st in varchar2 DEFAULT NULL,
                     zip in varchar2 DEFAULT NULL,
                     hphone in varchar2 DEFAULT NULL,
                     wphone in varchar2 DEFAULT NULL,
                     ophone in varchar2 DEFAULT NULL,
                     kids in varchar2 DEFAULT NULL,
                     email in varchar2 DEFAULT NULL,
                     comments in varchar2 DEFAULT NULL)
		     is
cursor update_this is
select * from adds where adds.seq=seq_no;
begin
  htp.htmlOpen;
  htp.headOpen;
  htp.htitle('Item Updated!');
  htp.headClose;
  htp.bodyOpen;
      htp.print('The record <b>was</b>:');
      htp.nl;
  htp.tableOpen(1);
for c1 in update_this
  loop
  htp.tableRowOpen;
  htp.tableData(c1.fname||' '||c1.lname||'<BR>'||c1.street||'<BR>'
	                ||c1.street2||'<BR>'
			||c1.city_st||' '||c1.zip);
  htp.tableData('Home<BR>'||c1.hphone||'<BR>Work<BR>'||c1.wphone);
  htp.tableData('Other<BR>'||c1.ophone||'<BR>email<BR>'||c1.email);
  htp.tableData(c1.comments||'<BR>'||c1.kids);
  htp.tableRowClose;
  end loop;
  htp.tableClose;
update adds set adds.lname=update_it.lname,
		adds.fname=update_it.fname, 
		adds.street=update_it.street,
		adds.street2=update_it.street2,
		adds.city_st=update_it.city_st,
		adds.zip=update_it.zip, 
		adds.hphone=update_it.hphone ,
		adds.wphone=update_it.wphone, 
		adds.ophone=update_it.ophone, 
		adds.kids=update_it.kids, 
		adds.email=update_it.email ,
		adds.comments=update_it.comments 
       	where adds.seq=seq_no; 
delete from adds where fname is null and lname is null;
      htp.print('The <b>updated record</b> is:');
      htp.nl;
  htp.tableOpen(1);
  htp.tableRowOpen;
  htp.tableData(update_it.fname||' '||update_it.lname||'<BR>'
	      ||update_it.street||'<BR>'
	      ||update_it.street2||'<BR>'||update_it.city_st
	      ||' '||update_it.zip);
  htp.tableData('Home<BR>'||update_it.hphone||'<BR>Work<BR>'||update_it.wphone);
  htp.tableData('Other<BR>'||update_it.ophone||'<BR>email<BR>'||update_it.email);
  htp.tableData(update_it.comments||'<BR>'||update_it.kids);
  htp.tableRowClose;
  htp.tableClose;
  htp.anchor('address.query?cookie='||cookie||
	     '&lname='||update_it.lname,'Return to Address Book');
  htp.bodyClose;
  htp.htmlClose;
end update_it;

/*---------------------------------------------------------------------------*/
procedure insert_form (cookie in varchar2 default null) is
  seq_buf number := 0;
begin
  select max(seq) into seq_buf from adds;
  seq_buf := seq_buf + 1;
  insert into adds(seq) values (seq_buf);
  update_form(cookie,seq_buf);
EXCEPTION
  when NO_DATA_FOUND then      
                   insert into adds(seq) values ('0');
                   update_form(cookie,'0');
  when others then htp.print('You hit an unhandled exception in insert_form');
	           htp.p('<P>Error Message: '||SQLERRM);
end insert_form;

/*---------------------------------------------------------------------------*/
end address;
/
show errors