#!/usr/local/bin/oraperl
#
# example2.pl
#
# c)oraperl program to insert, search, and delete a record.
#
# Author:	Chao-Wei Ou
# Date:		3/29/1996
#
print qq|Content-type: text/html\n\n|;

# Set up the debuging flag.
$ora_debug = shift if $ARGV[0] =~ /^-#/;

$ENV{'ORACLE_HOME'} = '/usr/oracle';
$ENV{'ORACLE_SID'} = 'nodb';

read(STDIN, $incoming, $ENV{'CONTENT_LENGTH'});
&cgi_decode;

$name = ($FORM{'name'}) ? $FORM{'name'} : undef;
$last_name = ($FORM{'last_name'}) ? $FORM{'last_name'} : undef;

# print qq|ARGV:  .$name.  <>  .$last_name.<br>\n|;

# Specify the user_name-name/password and select a database.
$userid = "cps616";
$passwd = "cps616";
$database = $ENV{'ORACLE_SID'};

# Login the ORACLE SQL.
$lda = &ora_login($database, $userid, $passwd) || die $ora_errstr;

# Cursor for quering phone number by inputing the name
$phone_cursor = "SELECT last_name, first_name, title,
                        phone_no, phone_type
                 FROM person_info_table a, phone_list_table b
                 WHERE ((:1 IS NULL OR
		        last_name LIKE ('%' || LOWER(:1) || '%') OR
                        first_name LIKE ('%' || LOWER(:1) || '%')) AND
		       (:2 IS NULL OR
                        last_name = LOWER(:2)) AND
                       (a.person_id = b.person_id)) OR 
		       (:1 IS NOT NULL AND :2 IS NOT NULL AND
		        (last_name LIKE ('%' || LOWER(:1) || '%') OR
                         first_name LIKE ('%' || LOWER(:1) || '%') OR
			 last_name LIKE LOWER(:2)) AND
			 a.person_id = b.person_id)";


# Open a complicate query cursor
$csr = &ora_open($lda, $phone_cursor);

# Assign acctial values in SQL statement
&ora_bind($csr, $name, $last_name);

# Print HTML TABLE header
print qq|<H3>The following records have been successfully found!</i></H3>\n|;
print qq|<TABLE BORDER>\n|;
print qq|<TR><TD>Last Name</TD><TD>First Name</TD>\n|;
print qq|<TD>Title</TD><TD>Phone NO<TD>Phone Type</TD></TR>\n|;

# Fetch the record into array variables
while (@data = &ora_fetch($csr)) {
    print qq|<TR>\n|;
    foreach $attr (@data) {
        print qq|<TD>$attr</TD>\n|;
    }
    print qq|</TR>\n|;
}
print qq|</TABLE>\n|;

# Close the cursor
&ora_close($csr);

# Logoff.
&ora_logoff($lda);

sub cgi_decode {
    @pairs = split(/&/, $incoming);

    foreach (@pairs) {
        ($name, $value) = split(/=/, $_);

        $name  =~ tr/+/ /;
        $value =~ tr/+/ /;
        $name  =~ s/%([A-F0-9][A-F0-9])/pack("C", hex($1))/gie;
        $value =~ s/%([A-F0-9][A-F0-9])/pack("C", hex($1))/gie;

        #### Strip out semicolons unless for special character
        $value =~ s/;/$$/g;
        $value =~ s/&(\S{1,6})$$/&\1;/g;
        $value =~ s/$$/ /g;

        $value =~ s/\|/ /g;
        $value =~ s/^!/ /g; ## Allow exclamation points in sentences

        #### Skip blank text entry fields
        next if ($value eq "");

        #### Check for "assign-dynamic" field names
        #### Mainly for on-the-fly input names, especially checkboxes
        if ($name =~ /^assign-dynamic/) {
            $name = $value;
            $value = "on";
        }

        #### Allow for multiple values of a single name
        $FORM{$name} .= ", " if ($FORM{$name});

        $FORM{$name} .= $value;
    }
}