CREATE OR REPLACE PACKAGE phone_pkg AS

    TYPE person_rec_type IS RECORD              -- person-info record
       (person_id       NUMBER(6),
        last_name       VARCHAR2(15),
        first_name      VARCHAR2(15),
        title           VARCHAR2(8),
        user_name       VARCHAR2(8),
        address         VARCHAR2(12),
        url             VARCHAR2(20));

    TYPE phone_rec_type IS RECORD               -- phone-list record
       (person_id       NUMBER(6),
        phone_no        NUMBER(10),
        phone_type      VARCHAR2(10));

    TYPE mix_rec_type IS RECORD
       (person_id       NUMBER(6),
        last_name       VARCHAR2(15),
        first_name      VARCHAR2(15),
        title           VARCHAR2(8),
        user_name       VARCHAR2(8),
        address         VARCHAR2(12),
        url             VARCHAR2(20),
        phone_no        NUMBER(10),
        phone_type      VARCHAR2(10));

    PROCEDURE get_phone_by_id(id IN NUMBER);

    PROCEDURE get_phone_by_no(no IN NUMBER);

    PROCEDURE get_phone_by_type(ptype IN VARCHAR2);

    PROCEDURE get_person_by_id(id IN NUMBER);

    PROCEDURE get_person_by_name(name IN VARCHAR2);

    PROCEDURE get_person_by_title(pos IN VARCHAR2);

    PROCEDURE get_person_by_user(uname IN VARCHAR2);

    PROCEDURE get_person_phone_by_name(name IN VARCHAR2);

    PROCEDURE get_person_phone_by_any(name IN VARCHAR2,
		pos IN VARCHAR2, uname IN VARCHAR2, 
		no IN NUMBER, ptype IN VARCHAR2);

    PROCEDURE display_phone_rec(phone_rec IN phone_rec_type);

    PROCEDURE display_person_rec(person_rec IN person_rec_type);

    PROCEDURE display_mix_rec(mix_rec IN mix_rec_type);

    PROCEDURE display_phone_head(ctitle IN VARCHAR2);

    PROCEDURE display_phone_end;

    PROCEDURE display_person_head(ctitle IN VARCHAR2);

    PROCEDURE display_person_end;

    PROCEDURE display_mix_head(ctitle IN VARCHAR2);

    PROCEDURE display_mix_end;
END phone_pkg;
/

CREATE OR REPLACE PACKAGE BODY phone_pkg AS


/*  Get the phone number by requesting "person_id", and return tables of
    phone numbers and phone types. */

    PROCEDURE get_phone_by_id(id IN NUMBER)	-- match key
    IS

        CURSOR phone_cur(id IN NUMBER) 		-- cursor of phone_list
	IS					-- selected from
            SELECT *				-- "phone_list" table
            FROM phone_list_table
            WHERE id IS NULL OR 		-- if id is NULL,
		  person_id = id;  		-- wild-card selection
						-- is performed

	phone_rec	phone_rec_type;

    BEGIN

   	display_phone_head(id);

	IF NOT phone_cur%ISOPEN THEN	        -- open the cursor if 
	    OPEN phone_cur(id);			-- not open yet
	END IF;

	-- Fetch rows into PL/SQL table

	LOOP
	    FETCH phone_cur INTO phone_rec;
	    EXIT WHEN phone_cur%NOTFOUND;
	    display_phone_rec(phone_rec);
	END LOOP;

	CLOSE phone_cur;			-- close cursor

	display_phone_end;

    END get_phone_by_id;			-- end of procedure


    PROCEDURE get_phone_by_no(no IN NUMBER)
    IS

        CURSOR phone_cur(no IN NUMBER)          -- cursor of phone_list
        IS                                      -- selected from
            SELECT *                            -- "phone_list" table
            FROM phone_list_table
	 -- if id is NULL, wild-card selection
            WHERE no IS NULL OR              
                  TO_CHAR(phone_no) LIKE ('%' || TO_CHAR(no) || '%');

        phone_rec       phone_rec_type;

    BEGIN

  	display_phone_head(no);

        IF NOT phone_cur%ISOPEN THEN            -- open the cursor if
            OPEN phone_cur(no);                 -- not open yet
        END IF;

        -- Fetch rows into PL/SQL table

        LOOP
            FETCH phone_cur INTO phone_rec;
	    EXIT WHEN phone_cur%NOTFOUND;
	    display_phone_rec(phone_rec);
        END LOOP;

	CLOSE phone_cur;

	display_phone_end;

    END get_phone_by_no;

    PROCEDURE get_phone_by_type(ptype IN VARCHAR2)
    IS

        CURSOR phone_cur(ptype IN VARCHAR2)     -- cursor of phone_list
        IS                                      -- selected from
            SELECT *                            -- "phone_list" table
            FROM phone_list_table
	 -- if id is NULL, wild-card sele ction
            WHERE ptype IS NULL OR                       
                  phone_type LIKE ('%' || LOWER(ptype) || '%');

        phone_rec       phone_rec_type;

    BEGIN

	display_phone_head(ptype);

        IF NOT phone_cur%ISOPEN THEN            -- open the cursor if
            OPEN phone_cur(ptype);              -- not open yet
        END IF;

        -- Fetch rows into PL/SQL table

        LOOP
            FETCH phone_cur INTO phone_rec;
            EXIT WHEN phone_cur%NOTFOUND;
            display_phone_rec(phone_rec);
        END LOOP;

        CLOSE phone_cur;

	display_phone_end;

    END get_phone_by_type;

    PROCEDURE get_person_by_id(id IN NUMBER)    -- match key
    IS

        CURSOR person_cur(id IN NUMBER)
        IS
            SELECT *
            FROM person_info_table
            WHERE id IS NULL OR person_id = id;

        person_rec      person_rec_type;

    BEGIN

    	display_person_head(id);

        IF NOT person_cur%ISOPEN THEN           -- open the cursor if
            OPEN person_cur(id);                -- not open yet
        END IF;

        -- Fetch rows into PL/SQL table

        LOOP
            FETCH person_cur INTO person_rec;
            EXIT WHEN person_cur%NOTFOUND;
            display_person_rec(person_rec);
        END LOOP;

        CLOSE person_cur;

    	display_person_end;

    END get_person_by_id;

    PROCEDURE get_person_by_name(name IN VARCHAR2)
    IS
        CURSOR person_cur(name IN VARCHAR2)
        IS
            SELECT *
            FROM person_info_table
            WHERE name IS NULL OR
                  last_name LIKE ('%' || LOWER(name) || '%') OR
		  first_name LIKE ('%' || LOWER(name) || '%');

	person_rec	person_rec_type;

    BEGIN

	display_person_head(name);

        IF NOT person_cur%ISOPEN THEN           -- open the cursor if
            OPEN person_cur(name);              -- not open yet
        END IF;

        -- Fetch rows into PL/SQL table

        LOOP
            FETCH person_cur INTO person_rec;
            EXIT WHEN person_cur%NOTFOUND;
            display_person_rec(person_rec);
        END LOOP;

        CLOSE person_cur;

	display_person_end;

    END get_person_by_name;

    PROCEDURE get_person_by_title(pos IN VARCHAR2)
    IS
        CURSOR person_cur(pos IN VARCHAR2)
        IS
            SELECT *
            FROM person_info_table
            WHERE pos IS NULL OR
                  title LIKE ('%' || LOWER(pos) || '%');

        person_rec      person_rec_type;

    BEGIN

	display_person_head(pos);

        IF NOT person_cur%ISOPEN THEN           -- open the cursor if
            OPEN person_cur(pos);               -- not open yet
        END IF;

        -- Fetch rows into PL/SQL table

        LOOP
            FETCH person_cur INTO person_rec;
            EXIT WHEN person_cur%NOTFOUND;
            display_person_rec(person_rec);
        END LOOP;

        CLOSE person_cur;

	display_person_end;

    END get_person_by_title;

    PROCEDURE get_person_by_user(uname IN VARCHAR2)
    IS

        CURSOR person_cur(uname IN VARCHAR2)
        IS
            SELECT *
            FROM person_info_table
            WHERE uname IS NULL OR
                  user_name LIKE ('%' || LOWER(uname) || '%');

        person_rec  	person_rec_type;

    BEGIN

	display_person_head(uname);

        IF NOT person_cur%ISOPEN THEN           -- open the cursor if
            OPEN person_cur(uname);             -- not open yet
        END IF;

        -- Fetch rows into PL/SQL table

        LOOP
            FETCH person_cur INTO person_rec;
            EXIT WHEN person_cur%NOTFOUND;
            display_person_rec(person_rec);
        END LOOP;

        CLOSE person_cur;

	display_person_end;

    END get_person_by_user;

    PROCEDURE get_person_phone_by_name(name IN VARCHAR2)
    IS
        CURSOR mix_cur(name IN VARCHAR2)
        IS
            SELECT a.person_id, last_name, first_name, title,
		   user_name, address, url,
		   phone_no, phone_type
            FROM person_info_table a, phone_list_table b
            WHERE (name IS NULL OR 
		   last_name LIKE ('%' || LOWER(name) || '%') OR
		   first_name LIKE ('%' || LOWER(name) || '%')) AND
		  (a.person_id = b.person_id);

        mix_rec   mix_rec_type;

    BEGIN

	display_mix_head(name);

        IF NOT mix_cur%ISOPEN THEN          -- open the cursor if
            OPEN mix_cur(name);             -- not open yet
        END IF;

        -- Fetch rows into PL/SQL table

        LOOP
            FETCH mix_cur INTO mix_rec;
            EXIT WHEN mix_cur%NOTFOUND;
            display_mix_rec(mix_rec);
        END LOOP;

        CLOSE mix_cur;

	display_mix_end;

    END get_person_phone_by_name;

    PROCEDURE get_person_phone_by_any(name IN VARCHAR2,
                pos IN VARCHAR2, uname IN VARCHAR2, 
		no IN NUMBER, ptype IN VARCHAR2)
    IS
        CURSOR mix_cur(name IN VARCHAR2,
                pos IN VARCHAR2, uname IN VARCHAR2,
		no IN NUMBER, ptype IN VARCHAR2)
        IS
            SELECT a.person_id, last_name, first_name, title,
                   user_name, address, url,
                   phone_no, phone_type
            FROM person_info_table a, phone_list_table b
            WHERE (name IS NULL OR
                   last_name LIKE ('%' || LOWER(name) || '%') OR
                   first_name LIKE ('%' || LOWER(name) || '%')) AND
                  (pos IS NULL OR
                   title LIKE ('%' || LOWER(pos) || '%')) AND
                  (uname IS NULL OR
                   user_name LIKE ('%' || LOWER(uname) || '%')) AND
		  (no IS NULL OR 
		   TO_CHAR(phone_no) LIKE ('%' || TO_CHAR(no) || '%')) AND
                  (ptype IS NULL OR
                   phone_type LIKE ('%' || LOWER(ptype) || '%')) AND
                  (a.person_id = b.person_id);

        mix_rec   mix_rec_type;
	q_string  VARCHAR2(255) := name || '-' || pos || '-' ||
			      uname || '-' || no || '-' || ptype;
    BEGIN

	display_mix_head(q_string);

	    IF NOT mix_cur%ISOPEN THEN
	        OPEN mix_cur(name, pos, uname, no, ptype);
	    END IF;

            LOOP
                FETCH mix_cur INTO mix_rec;
		EXIT WHEN mix_cur%NOTFOUND;
		display_mix_rec(mix_rec);
	    END LOOP;
	  
	    CLOSE mix_cur;

	display_mix_end;

    END get_person_phone_by_any;

    PROCEDURE display_phone_rec(phone_rec IN phone_rec_type)
    IS
    BEGIN
        htp.p('<TR>');
        htp.p('<TD>' || phone_rec.phone_no || '</TD>');
        htp.p('<TD>' || INITCAP(phone_rec.phone_type) || '</TD>');
        htp.p('</TR>');
    END display_phone_rec;

    PROCEDURE display_person_rec(person_rec IN person_rec_type)
    IS
	url_string	VARCHAR2(120);
    BEGIN
	htp.p('<TR>');
        htp.p('<TD>' || INITCAP(person_rec.last_name) || '</TD>');
        htp.p('<TD>' || INITCAP(person_rec.first_name) || '</TD>');
	htp.p('<TD>' || INITCAP(person_rec.title) || '</TD>');
        htp.p('<TD>' || person_rec.user_name || '</TD>');
        htp.p('<TD>' || UPPER(person_rec.address) || '</TD>');
        url_string := '<a HREF=http://www.npac.syr.edu/users/' ||
                     person_rec.user_name ||
                     '/homepage/ TARGET="_parent">';
        htp.p('<TD>' || url_string || htf.italic(person_rec.first_name) ||
                '</a>' || '</TD>');
        htp.p('</TR>');
    END display_person_rec;

    PROCEDURE display_mix_rec(mix_rec IN mix_rec_type)
    IS
        url_string      VARCHAR2(120);
    BEGIN
	htp.p('<TR>');
        htp.p('<TD>' || INITCAP(mix_rec.last_name) || '</TD>');
        htp.p('<TD>' || INITCAP(mix_rec.first_name) || '</TD>');
        htp.p('<TD>' || INITCAP(mix_rec.title) || '</TD>');
        htp.p('<TD>' || mix_rec.user_name || '</TD>');
        htp.p('<TD>' || UPPER(mix_rec.address) || '</TD>');
	url_string := '<a HREF=http://www.npac.syr.edu/users/' || 
                     mix_rec.user_name ||
                     '/homepage/ TARGET=ows_display>';
        htp.p('<TD>' || url_string || htf.italic(mix_rec.first_name) ||
		'</a>' || '</TD>');
        htp.p('<TD>' || mix_rec.phone_no || '</TD>');
        htp.p('<TD>' || INITCAP(mix_rec.phone_type) || '</TD>');
        htp.p('</TR>');
    END display_mix_rec;

    PROCEDURE display_phone_head(ctitle IN VARCHAR2)
    IS
    BEGIN
        htp.title('Query Results by Searching "' || ctitle || '"');
        htp.p('<H1>Query Results by Searching "' || ctitle || '"</H1>');
        htp.p('<BR>');
	htp.p('<TABLE BORDER ALIGN="CENTER">');
        htp.p('<TR>');
        htp.p('<TD>Phone NO</TD>');
        htp.p('<TD>Phone Type</TD>');
        htp.p('</TR>');
    END display_phone_head;

    PROCEDURE display_phone_end
    IS
    BEGIN
        htp.p('</TABLE>');
    END display_phone_end;

    PROCEDURE display_person_head(ctitle IN VARCHAR2)
    IS
    BEGIN
        htp.title('Query Results by Searching "' || ctitle || '"');
        htp.p('<H1>Query Results by Searching "' || ctitle || '"</H1>');
        htp.p('<BR>');
        htp.p('<TABLE BORDER ALIGN="CENTER">');
        htp.p('<TR>');
        htp.p('<TD>Last Name</TD>');
        htp.p('<TD>First Name</TD>');
	htp.p('<TD>Title</TD>');
	htp.p('<TD>User Name</TD>');
	htp.p('<TD>Address</TD>');
	htp.p('<TD>URL</TD>');
        htp.p('</TR>');
    END display_person_head;

    PROCEDURE display_person_end
    IS
    BEGIN
        htp.p('</TABLE>');
    END display_person_end;

    PROCEDURE display_mix_head(ctitle IN VARCHAR2)
    IS
    BEGIN
        htp.title('Query Results by Searching "' || ctitle || '"');
        htp.p('<H1>Query Results by Searching "' || ctitle || '"</H1>');
        htp.p('<BR>');
        htp.p('<TABLE BORDER ALIGN="CENTER">');
        htp.p('<TR>');
        htp.p('<TD>Last Name</TD>');
        htp.p('<TD>First Name</TD>');
        htp.p('<TD>Title</TD>');
        htp.p('<TD>User Name</TD>');
        htp.p('<TD>Address</TD>');
        htp.p('<TD>URL</TD>');
	htp.p('<TD>Phone NO</TD>');
	htp.p('<TD>Phone Type</TD>');
        htp.p('</TR>');
    END display_mix_head;

    PROCEDURE display_mix_end
    IS
    BEGIN
        htp.p('</TABLE>');
    END display_mix_end;

END phone_pkg;
/