#!/usr/local/bin/perl5 -w

use CGI qw(:standard);   # use CGI module for CGI programming
use DBI;                 # use DBI modulde for database connectivity
use DBI::DBD;

# in order to use this CGI, your database should have the following table
# use the following sql statement to create a table
# create table product_1 (
# 	id char(6),
# 	category varchar2(50),
# 	description varchar2(50),
# 	price numeric(10,2),
#	onhand int,
#	primary key(id)
# );

# global variable
#	$statement
#	$dbh
#	$qfield
#	$qpattern

# set environment variable for connecting to oracle database
$ENV{'ORACLE_HOME'}='/opt5/oracle8i/app';

# connection to the database
# replace cprjxxxx with your project acount and password as your account password
$dbh=DBI->connect("dbi:Oracle:db00","cprjxxxx","password") or die "Can't connect to database";

# below is the main part of this CGI program
# refer to lecture notes 3-18 to 3-22

# display upper part of HTML document (the form for you to query and add new product
showmenu();

# below is a request dispatcher to see which button the user clicked and
# call the corresponding function
if (param('lookup') || (param('cancel') && !param('new')) ) { 
	# lookup or cancel button is clicked
	do_query();       # query one or more records 
	display_result(); # show the result of query
} elsif (param('insert')) { 
	# insert button is clicked
	my $key=param('ins_key'); # retrieve the id input by user in the form
	if (check_insert($key)) { # check whether it is duplicate key or not
		edit_item($key);      # show a page for editing single record
	} else {                  
		# show error message
		print qq(<p>The key is duplicated or longer than 6 character</p>);
	}
} elsif (param('editall')) {
	# editall button is clicked
	do_query();    # query the records that need to edit
	edit_all();    # show a page for editing all of the selected records
} elsif (param('edit')) { 
	# edit button is clicked
	do_query();	   # query the specific items that need to edit
	edit_item();   # show a page for editing a single element
} elsif (param('delete') || (param('new') && param('cancel'))) {
	# delete button is clicked
	do_delete();   		# delete a record
	do_query();    		# query and show the updated result
	display_result();
} elsif (param('update')) { 
	# update/updateall button is clicked
	update_data();   	# update record(s)
	do_query();      	# query the updated result
	display_result();	# display the updated result
}

# disconnect the connection to the database
$dbh->disconnect or warn "Error disconnecting\n";

# show the lower part of HTML document
print "</form>";
print end_html;

# function to show the menu at the upper part
sub showmenu {

   my @fields = qw(ID Description Category); # array storing different fields
   print header();                           # print the content-type header

   # print the head of html page
   print qq(<html>
		<head>
		<title>Product information</title>
		</head>
		<body>
		<h1>Product Table</h1>
		<form method="POST" action="product.cgi">
		<p>);

   if (!param('field')) {
	   # user visits the page first time
	   print popup_menu("field", \@fields);
	   print qq(matches <input type="text" name="query" size="20">);
   } else {
	   # user revisit the page
	   $qfield = param('field');
	   $qpattern = param('query');
	   print popup_menu("field", \@fields, $qfield);
	   print qq(matches <input type="text" name="query" value="$qpattern" size="20">);
   }
   print qq(<input type="submit" value="Lookup" name="lookup"></p>
            <p><input type="text" name="ins_key" size="20">
            <input type="submit" value="Insert" name="insert"></p>);
}

# function to query database based on user's input
sub do_query {
   my $field;
   my $pattern;

   if (param('edit')) {   # a link is clickced
      $field = "ID";
      $pattern = param('key');
   } elsif ( param('lookup') || param('cancel') || param('update') || param('delete') || param('editall') ) {
      $field = $qfield;
      $pattern = $qpattern;
   }

   # prepare SQL statement, selecting records that match the search criteria
   $statement=$dbh->prepare("select * from product_1 where $field like '\%$pattern\%'") or die "Can't prepare SQL statment $DBI::errstr\n";
   
   # execute SQL statement
   $statement->execute or die "Can't execute SQL statment $DBI::errstr\n";
}

#function to display result in table based on data in $statement object
sub display_result {

   # show the header line of the query result table
   print qq(<HR>
		<table border="2" cellpadding="2" cellspacing="4" width="70%">
		<tr>
		<th width="17%">ID</th>
		<th width="20%">Category</th>
		<th width="28%">Description</th>
		<th width="19%">Price</th>
		<th width="16%">Onhand</th>
		</tr>);

   # show the content of the table
   # the $statement variable contains records selected from the database, it is executed by the do_query function
   my @row;
   # for each record, we print it in the table
   while (@row = $statement->fetchrow_array()) {
      print qq(<tr> <td width="17%">
			<a href="product.cgi?edit=edit&key=$row[0]&field=$qfield&query=$qpattern">$row[0]</a>
			</td>
			<td width="20%">$row[1]</td>
			<td width="28%">$row[2]</td>
			<td width="19%">$row[3]</td>
			<td width="16%">$row[4]</td>
			</tr>);
   }   
   print "</table>";

   # display Edit All button
   print qq(<p><input type="submit" value="Edit All" name="editall"></p>);
}

# function to allow edit of the row given a key
# this function will show a table containing the record to be edit
sub edit_item {

   my @editrow;
   my @category = qw(printer scanner modem);

   # display header line of query result table
   print qq(<b>Edit Single Item</b>
		<table border="2" cellpadding="2" cellspacing="4" width="70%">
		<tr> 
		<th width="17%">ID</th>
		<th width="20%">Category</th>
		<th width="28%">Description</th>
		<th width="19%">Price</th>
		<th width="16%">Onhand</th>
		</tr>);

   # display the content of table
   if (param('insert')) {
	   # user go the edit page becuase he click the insert button
	   # $_[0] is the input parameter of this function, it contains the ID of the product that need to edit
      print qq(<tr><td width="17%">$_[0]</td><td width="20%">);
      print popup_menu("category1", \@category);
      
      print qq(</td>
               <td width="21%"><input type="text" name="description1"></td>
               <td width="21%"><input type="text" name="price1"></td>
               <td width="21%"><input type="text" name="onhand1"></td>
               </tr>);
      print "</table>";

      # hidden field storing the id of a record
      # this is used when user click update, and the program need to check which records to update
      print qq(<input type="hidden" name="id1" value="$_[0]">);
      print qq(<input type="hidden" name="new" value="new">);
      
   } elsif ( param('edit') && (@editrow = $statement->fetchrow_array())) {
	   # user go to edit page becuase he click the link in the query result table
	   # show a table for editing a record, with default values shown
	   # the @editrow variable is retrieved in the condition statement
      print qq(<tr> <td width="17%">$editrow[0]</td> <td width="20%">);

		# show the popup menu with default value
		# remove trailing space by regular expression
		$editrow[1] =~ s/\s+$//;
       # show a popup menu with default value as the third parameter
       # first parameter is the name of that popup menu input field
       # second parameter is the values inside this popup menu
       print popup_menu("category1", \@category, $editrow[1]);

      # display default values of remaining fields
      print qq(</td>
				<td width="21%"><input type="text" name="description1" value="$editrow[2]"></td>
				<td width="21%"><input type="text" name="price1" value="$editrow[3]"></td>
				<td width="21%"><input type="text" name="onhand1" value="$editrow[4]"></td>
				</tr>);
      print "</table>";
      # hidden field needed, as user click update, the form can tell user are editing which record
      print qq(<input type="hidden" name="id1" value="$editrow[0]">);
   }

   # display the "Update", "Cancel" and "Delete" buttons
   print qq(<p> <input type="submit" value="Update" name="update">
                <input type="submit" value="Cancel" name="cancel">
                <input type="submit" value="Delete" name="delete">
            </p>);
}

# function to edit all of the selected rows
# this function is called, when user click edit all button in the query result page
# this function will display a table showing all the records that need to be edit in a table format
sub edit_all {

   my @editall;
   my @category = qw(printer scanner modem);
   my $i = 1;

   # display header line of the table
   print qq(<b>Edit All Selected Item(s)</b>
		<table border="2" cellpadding="2" cellspacing="4" width="70%">
		<tr>             
		<th width="17%">ID</th>
		<th width="20%">Category</th>
		<th width="28%">Description</th>
		<th width="19%">Price</th>
		<th width="16%">Onhand</th>
		</tr>);

	# $statement variable already contain all the records that need to be edited, this is done by do_query function
	# for each records, we display it in a table, that allow user to edit
   while (@editall = $statement->fetchrow_array()) {
      print qq(<tr> <td width="17%">$editall[0]</td> <td width="20%">);

      # remove the trailing spaces by regular expression
      $editall[1] =~ s/\s+$//;

      # show a popup menu with default valus indicated in the database record
      print popup_menu("category$i", \@category, $editall[1]);

      # display default values of remaining fields
      # the input field name ends with a counter $i, it allows the CGI program later to get all
      # records to update
      print qq(</td>
               <td width="21%"><input type="text" name="description$i" value="$editall[2]"></td>
               <td width="21%"><input type="text" name="price$i" value="$editall[3]"></td>
               <td width="21%"><input type="text" name="onhand$i" value="$editall[4]"></td>
               </tr>);
      print qq(<input type="hidden" name="id$i" value="$editall[0]">); # hidden field
      $i++;
   }
   print "</table>";

   # display the "Update All" and "Cancel" button
   print qq(<p> <input type="submit" value="Update All" name="update">
                <input type="submit" value="Cancel" name="cancel">
            </p>);

}

# function to update records
# this function is called when user press the update/ update all button
# this function will retrieve the values containd in a form and
# execute update statment to update the record
sub update_data {
   my $id; my $category; my $price;
   my $description;
   my $onhand;
   my $i;

   # this for loop will get all the records update
   for ($i=1; param("category$i"); $i++) {
      $id = param("id$i");  # retrieve the values of id1, id2, ...
      $category = param("category$i");
      $description = param("description$i");
      $price = param("price$i");
      $onhand = param("onhand$i");
      # update a record
      $statement = $dbh->prepare("update product_1 set category = '$category',".
         "description = '$description', price = $price, onhand = $onhand".
         "where id = '$id'"
      ) or die "Can't prepare SQL statement $DBI::errstr\n";
      $statement->execute or die "Cannot update $id - $DBI::errstr\n";
   }
}

# function to delete a record
# this function will be called when user press the delete button
# this function will perform delete in the database
sub do_delete {
   my $id;
   $id = param('id1');
   $statement = $dbh->prepare("delete product_1 where id = '$id'")
                or die "Can't prepare SQL statement $DBI::errstr\n";
   $statement->execute or die "Can't execute SQL statement $DBI::errstr\n";
}

# function to validate the user input id has duplicat key or not
# we assume the product table has set primary key as ID
sub check_insert {
   $statement=$dbh->prepare("insert into product_1(ID) values ('$_[0]')")
              or die "Can't prepare SQL statement $DBI::errstr\n";
   if ($statement->execute()) { 
	  # return true if there is no duplicated record
      return 1;
   } else {
	  # return false if there is duplicated record (execution will fail when DB check that a statement violate the constraint)
      return 0;
   }
}
