spool ora_ex5 
set echo on
set pagesize 66
set linesize 80
/* NANNETTE THACKER  HOMEWORK 8 TABLES */
/* ora_ex5.sql */
/* May 8 1997 12:42 pm altered */

/* drop stuff in case has already been created and doing over again */
drop trigger nameupper;
drop table company cascade constraints ;
drop table contact cascade constraints ;
drop table call cascade constraints ;
drop procedure addcus   ;
drop procedure add_cust2   ;
drop procedure add_cust3   ;
drop procedure delete_contacts ;
drop sequence new_company ;
drop sequence new_contact ;
drop sequence new_call ;
drop trigger add_call ;

/* create the company table -- nkt 4/23/97 */
/* stores company information */
create table company
(
	companyid		number,
	companyname		varchar2(40) constraint unique_name
UNIQUE,
	address		varchar2(30),
	city			varchar2(30),
	state			varchar2(2),
	zip			varchar2(9),
	country		varchar2(25),      
  CONSTRAINT pk_companyid PRIMARY KEY(companyid)
                using index TABLESPACE users
)
        STORAGE(INITIAL 5K
                NEXT 3K
                PCTINCREASE 0)
        TABLESPACE USERS;

comment on table company is
'created 4/23/97 by NKT. Primary key is companyid.
 Also uses contact and call tables.' ;


/* ========================================================*/

/* create the contact table -- nkt 4/23/97 */
/* store information for each contact */
create table contact
(
	companyid		number,
	contactid		number,
	contactlast		varchar2(25),
	contactfirst	varchar2(20),
	phone			varchar2(10),
	fax			varchar2(10),
	email			varchar2(50),
        CONSTRAINT pk_contactid PRIMARY KEY(contactid)
                using index TABLESPACE users,
	  CONSTRAINT fk_companyid FOREIGN KEY(companyid)
			references company(companyid)
)
        STORAGE(INITIAL 5K
                NEXT 3K
                PCTINCREASE 0)
        TABLESPACE USERS;

comment on table contact is
'created 4/23/97 by NKT. Primary key is contactid.
 Also uses company and call tables.' ;



/* ========================================================*/

/* create the call table -- nkt 4/23/97 */
/* store the information pertaining to a call */
create table call
(
	contactid		number,
	callid		number,
	topic 		varchar2(50),
	calldate		date,
	note			varchar2(2000),
        CONSTRAINT pk_callid PRIMARY KEY(callid)
                using index TABLESPACE users,
	  CONSTRAINT fk_contactid FOREIGN KEY(contactid)
			references contact(contactid)
)
        STORAGE(INITIAL 5K
                NEXT 3K
                PCTINCREASE 0)
        TABLESPACE USERS;

comment on table call is
'created 4/23/97 by NKT. Primary key is callid.
 Also uses company and contact tables.' ;


/* ========================================================*/

/* when insert or update a contact record, make sure the last and
	first name fields are converted to all CAPS */
create trigger nameupper
        before insert or update on contact
	  for each row 
BEGIN
	  :new.contactlast := UPPER(:new.contactlast) ;
	  :new.contactfirst := UPPER(:new.contactfirst) ;
EXCEPTION
        WHEN OTHERS THEN
                RAISE_APPLICATION_ERROR(-20002,
                   'Unknown error. You did something wrong!');
END ;
/

/* ========================================================*/

/* procedure to delete all calls that are greater or equal to
	a year ago from today's date */
create or replace procedure delete_contacts
as
BEGIN
	-- DELETE ALL RECORDS WHERE CALLDATE IS <= A YEAR AGO TODAY
	delete from call where calldate <= ADD_MONTHS(sysdate,-12) ;
	EXCEPTION
		WHEN OTHERS THEN
			raise_application_error(-20026,
				'An unknown error occurred.');
END ;
/
show errors ;

/* ========================================================*/

/* create a sequence generator for the new company ids */
create sequence new_company
	increment by 1
	start with 1
	maxvalue 9999
	nocycle
	nocache
	order ;

/* create a sequence generator for the new contact ids */
create sequence new_contact
	increment by 1
	start with 1
	maxvalue 9999
	nocycle
	nocache
	order ;

/* create a sequence generator for the new topic ids */
create sequence new_call
	increment by 1
	start with 1
	maxvalue 9999
	nocycle
	nocache
	order ;

/* ========================================================*/

create or replace trigger add_call
	before insert on call
	for each row
declare
	call_id call.callid%type ;
	cursor newid is 
		select new_call.nextval from dual ;
begin
	open newid ;
	fetch newid into call_id ;
	close newid ;
	:new.callid := call_id ;	
EXCEPTION
        WHEN OTHERS THEN
                RAISE_APPLICATION_ERROR(-20002,
                   'Unknown error. You did something wrong!');
end ;
/

/* ========================================================*/

/* this was the original routine to add the calls... now
	put the sequence generator in a trigger.
*/
create or replace procedure add_cust3
 (	gcontactid in number,
	gtopic in varchar2,
	gdate in date,
	gnote in varchar2,
	gcallid in number)
as
ishere NUMBER ;
call_id call.callid%type ;
cursor newid is 
	select new_call.nextval from dual ;
BEGIN
	IF gcallid = 0 or gcallid = NULL THEN
	-- USE TRIGGER NOW INSTEAD for when SAVE from screen call
	--	open newid ;
	--	fetch newid into call_id ;
	--	close newid ;
          	INSERT INTO call(contactid,topic,calldate,note)
                VALUES(gcontactid,gtopic,gdate,gnote);
	--   	INSERT INTO call VALUES
	--        (gcontactid,call_id,gtopic,gdate,gnote);
		commit ;
	ELSE
		UPDATE call set 
			topic = gtopic,
			calldate = gdate,
			note = gnote
			where callid = gcallid ;
		commit ;
	END IF ;
	EXCEPTION
		WHEN CURSOR_ALREADY_OPEN THEN
			raise_application_error(-20025,
                                'The cursor is already opened.');
		WHEN OTHERS THEN
			raise_application_error(-20026,
				'An unknown error occurred.');
END ;
/
show errors ;
/* ========================================================*/

/* procedure to save the contact information,
called by addcus which saves the company information */

create or replace procedure add_cust2
 (	gcompanyid in number,
	gcontactlast in varchar2,
	gcontactfirst in varchar2,
	gphone in varchar2,
	gfax in varchar2,
	gemail in varchar2,
	gtopic in varchar2,
	gdate in date,
	gnote in varchar2,
	gcallid in number)
as
ishere NUMBER ;
contact_id contact.contactid%type ;
cursor newid is 
	select new_contact.nextval from dual ;
cursor checkexist is
	select contactid from contact
		where UPPER(contactlast) = UPPER(gcontactlast)
		and UPPER(contactfirst) = UPPER(gcontactfirst)
		and gcompanyid = companyid;
BEGIN
	open checkexist ;
	fetch checkexist into contact_id ;
	close checkexist ;
	-- see if already exists
	 select count(contactid) INTO ishere 
		from contact 
		where UPPER(contactlast) = UPPER(gcontactlast)
		and UPPER(contactfirst) = UPPER(gcontactfirst)
		and gcompanyid = companyid;
 	 if ishere = 0 THEN
		open newid ;
		fetch newid into contact_id ;
		close newid ;
          	INSERT INTO contact VALUES
                (gcompanyid,contact_id,gcontactlast, gcontactfirst,
			gphone,gfax,gemail );
		commit ;
	--	add_cust3(contact_id,gtopic,gdate,gnote,gcallid);
	ELSE
	-- once you know it's here, then store the id into ishere
	 select contactid INTO ishere 
		from contact 
		where UPPER(contactlast) = UPPER(gcontactlast)
		and UPPER(contactfirst) = UPPER(gcontactfirst)
		and gcompanyid = companyid;
		UPDATE contact set 
			phone = UPPER(gphone),
			fax = UPPER(gfax),
			email = UPPER(gemail)
			where contactid = ishere ;
		commit ;
	--	add_cust3(contact_id,gtopic,gdate,gnote,gcallid);
	END IF ;
	EXCEPTION
		WHEN CURSOR_ALREADY_OPEN THEN
			raise_application_error(-20025,
                                'The cursor is already opened.');
/*		WHEN OTHERS THEN
			raise_application_error(-20026,
				'An unknown error occurred.');
-- don't include WHEN OTHERS, so will use the TRIGGER ERROR!  */
END ;
/
show errors ;

/* ========================================================*/
/* original procedure: named addcus to keep the name SHORT
so I could test it from sqlplus. If it were longer, the tests
would fail as it broke at the end of the line.
ALSO: This routine was originally created to call add_cust2 to
store the contact information, which
then called add_cust3 to store the calls.
*/

the contact information 
create or replace procedure addcus
 (	gcompanyname in varchar2, 
	gaddress in varchar2,
	gcity in varchar2,
	gstate in varchar2,
	gzip in varchar2,
	gcountry in varchar2,
	glast in varchar2,
	gfirst in varchar2,
	gph in varchar2,
	gfax in varchar2,
	gemail in varchar2,
	gtopic in varchar2,
	gdate in date,
	gnote in varchar2,
	gcallid in number)
as
ishere NUMBER ;
co_id company.companyid%type ;
cursor newid is 
	select new_company.nextval from dual ;
cursor checkexist is
	select companyid from company
		where UPPER(companyname) = UPPER(gcompanyname);
BEGIN
	open checkexist ;
	fetch checkexist into co_id ;
	close checkexist ;
	-- see if already exists
	 select count(companyid) INTO ishere 
		from company where UPPER(companyname) =
UPPER(gcompanyname) ;	  
 	 if ishere = 0 THEN
		open newid ;
		fetch newid into co_id ;
		close newid ;
          	INSERT INTO company VALUES
                (co_id, gcompanyname, gaddress,
gcity,gstate,gzip,gcountry);
		commit ;
-- keep long
add_cust2(co_id,glast,gfirst,gph,gfax,gemail,gtopic,gdate,gnote,gcallid);
        ELSE
	 select companyid INTO ishere 
		from company where UPPER(companyname) =
UPPER(gcompanyname) ;	  
          UPDATE company set 
		address = gaddress,
		city = gcity,
		state = gstate,
		zip = gzip,
		country = gcountry
                where companyid = ishere ;
		commit ;
-- keep long
add_cust2(co_id,glast,gfirst,gph,gfax,gemail,gtopic,gdate,gnote,gcallid);
        END IF ;	
	EXCEPTION
		WHEN CURSOR_ALREADY_OPEN THEN
			raise_application_error(-20025,
                                'The cursor is already opened.');
		WHEN OTHERS THEN
			raise_application_error(-20026,
				'An unknown error occurred.');
END ;
/
show errors ;





/*	gcompanyname in varchar2, 
	gaddress in varchar2,
	gcity in varchar2,
	gstate in varchar2,
	gzip in varchar2,
	gcountry in varchar2,
	gcontactlast in varchar2,
	gcontactfirst in varchar2,
	gphone in varchar2,
	gfax in varchar2,
	gemail in varchar2,
	gtopic in varchar2,
	gcalldate in date,
	gnotes in varchar2
*/

/*

exec addcus('1','a','c','s','z','','c','c','p','f','e','t',sysdate,'n');
exec addcus('2','a','c','s','z','','c','c','p','f','e','t',sysdate,'n');
exec addcus('3','a','c','s','z','','c','c','p','f','e','t',sysdate,'n');
exec addcus('4','a','c','s','z','','c','c','p','f','e','t',sysdate,'n');
exec addcus('5','a','c','s','z','','c','c','p','f','e','t',sysdate,'n');
exec addcus('1','a','c','s','z','','b','b','p','f','e','t',sysdate,'n');
exec addcus('2','a','c','s','z','','b','b','p','f','e','t',sysdate,'n');
exec addcus('1','a','c','s','z','','c','c','p','f','e','t',sysdate,'n');

exec
addcus('6','a','c','s','z','','b','b','','','','t','01-JAN-95','n');
exec
addcus('7','a','c','s','z','','b','b','','','','t','01-JAN-96','n');

*/

/*   comment out remainder of this file, test and drop portions 

drop trigger nameupper;
drop table company cascade constraints ;
drop table contact cascade constraints ;
drop table call cascade constraints ;
drop procedure add_cust   ;
drop procedure add_cust2   ;
drop procedure add_cust3   ;
drop procedure delete_contacts ;
drop sequence new_company ;
drop sequence new_contact ;
drop sequence new_call ;
drop trigger add_call ;

--                add,c,st,zip,us,name,
exec addcus('Fx','','','','','','Jo','J','','','','','01-JAN-96','j',0);
select * from company ;
select * from contact ;
select * from call ;

*/

select * from company ;
select * from contact ;
select * from call ;
-- exec delete_contacts ;
-- select * from call ;

spool off