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

# We assume you are using these tables in your database

#create table staff (
#	code varchar2(10) not null,
#	name varchar2(50),
#	phone varchar2(20),
#	email varchar2(50),
#	password varchar2(16),
#	rank varchar2(50),
#	primary key (code)
#);
#
#create table customer (
#	code varchar2(10) not null,
#	name varchar2(50),
#	address varchar2(100),
#	phone varchar2(20),
#	primary key (code)
#);
#
#create table supplier (
#	code varchar2(10) not null,
#	name varchar2(50),
#	address varchar2(100),
#	phone varchar2(20),
#	email varchar2(50),
#	primary key (code)
#);
#
#create table product (
#	code varchar2(10) not null,
#	category varchar2(50),
#	name varchar2(50),
#	on_hand integer,
#	low_limit integer,
#	primary key (code),
#	check (on_hand >= 0)
#);
#
#create table supplying (
#	supplier_code varchar2(10) not null,
#	product_code varchar2(10) not null,
#	price float(2),
#	primary key (supplier_code,product_code),
#	foreign key (supplier_code) references supplier(code),
#	foreign key (product_code) references product(code)
#);
#
#create table invoice (
#	invoice_number varchar2(10) not null,
#	customer_code varchar2(10),
#	issue_date date,
#	staff_in_charge varchar(10),
#	voided integer,
#	primary key (invoice_number)
#);
#			
#create table invoice_item (
#	unique_id varchar2(20) not null,
#	invoice_number varchar2(10),
#	product_code varchar2(10),
#	price float(2),
#	qty integer,
#	primary key (unique_id),
#	foreign key (invoice_number) references invoice,
#	foreign key (product_code) references product(code)
#);
#
#create table po (
#	po_number varchar2(10) not null,
#	supplier_code varchar2(10),
#	issue_date date,
#	staff_in_charge varchar2(10),
#	voided integer,
#	primary key (po_number),
#	foreign key (supplier_code) references supplier(code)
#);
#
#create table po_item (
#	unique_id varchar2(10),
#	po_number varchar2(10),
#	product_code varchar2(10),
#	price float(2),
#	qty integer,
#	primary key (unique_id),
#	foreign key (po_number) references po,
#	foreign key (product_code) references product(code)
#);

use CGI qw(:standard);
use DBI;
use DBI::DBD;

#global variable used in this CGI program
my ($Staff_ID,$query,$new,$insert,$delete,$void,$print,$dbh);

#set environment variable
$ENV{'ORACLE_HOME'}='/opt5/oracle8i/app';

#connect to database
$dbh=DBI->connect('dbi:Oracle:db00','username','password') or die "Can't connect to database";

print header();

# get all the necssary input from the form
$Staff_ID=cookie('code');
$query=param('query');
$insert=param('insert');
$new=param('new');
$delete=param('delete');
$print=param('print');
$void=param('void');


if ($Staff_ID) {
	#user has logged in
	if ($query) {
		# user click the query button
		show_head();
		show_query();
		show_end();
	} elsif ($new) {
		# user click the add po button
		show_head();
		do_new();
		show_query();
		show_end();
	} elsif ($delete) {
		# user click the delete button when editing a PO
		show_head();
		do_delete();
		show_query();
		show_end();
	} elsif ($void) {
		# user click the void button when editing a PO
		show_head();
		do_void();
		show_query();
		show_end();
	} elsif ($print) {
		# user click the print link when editing a PO
		do_print();
	} elsif ($insert) {
		# user click the add button when editing a PO to insert an item
		show_head();
		do_insert();
		show_query();
		show_end();
	} else {
		# user just click a link to come in, print header is ok
		show_head();
		show_list();
		show_end();
	}
} else {
	#prompt user to login again
	print qq(<html>
		<head>
		<meta http-equiv="Content-Language" content="en-us">
		<meta http-equiv="Content-Type" content="text/html; charset=big5">
		<title>Login Again</title>
		</head>
		<body>
		<p>You need to login before using this page!!!</p>
		</body>
		</html>);
}

$dbh->disconnect;

sub do_print {
	my ($statement,@row,$po_number);
	$po_number=param('id');
	
	# a complex query to select the supplier's information to display at the top
	$statement=$dbh->prepare("select S.code,S.name,S.address,S.phone,S.email,P.issue_date,P.staff_in_charge from supplier S,po P where S.code=P.supplier_code and P.po_number='$po_number'") or die "Can't prepare sql statement";
	$statement->execute or die "Can't execute statement";
	@row=$statement->fetchrow_array();
	print qq(<html>
		<head>
		<meta http-equiv="Content-Language" content="en-us">
		<meta http-equiv="Content-Type" content="text/html; charset=big5">
		<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
		<meta name="ProgId" content="FrontPage.Editor.Document">
		<title>PO form</title>
		</head>
		<body>
		<h1 align="center">Purchase Order</h1>
		<table border="0" cellpadding="2" cellspacing="4" width="100%">
		<tr>
		<td width="16%" align="left"><b>Supplier code</b></td>
		<td width="17%">$row[0]</td>
		<td width="17%" align="left"><b>Supplier phone</b></td>
		<td width="17%">$row[3]</td>
		<td width="17%" align="left"><b>PO number</b></td>
		<td width="17%">$po_number</td>
		</tr>
		<tr>
		<td width="16%" align="left"><b>Supplier name</b></td>
		<td width="17%">$row[1]</td>
		<td width="17%" align="left"><b>Supplier e-mail</b></td>
		<td width="17%">$row[4]</td>
		<td width="17%" align="left"><b>Date of issue</b></td>
		<td width="17%">$row[5]</td>
		</tr>
		<tr>
		<td width="16%" align="left"><b>Supplier address</b></td>
		<td width="17%">$row[2]</td>
		<td width="17%" align="left"></td>
		<td width="17%"></td>
		<td width="17%" align="left"><b>Staff in charge</b></td>
		<td width="17%">$row[6]</td>
		</tr>
		</table>
		<p>Details</p>
		<table border="1" cellpadding="2" cellspacing="4" width="100%">
		<tr>
		<th width="20%">Product code</th>
		<th width="20%">Product name</th>
		<th width="20%">Price</th>
		<th width="20%">Quantity</th>
		<th width="20%">Total</th>
		</tr>);
		
	# a query to find the items of a specific PO
	$statement=$dbh->prepare("select PI.product_code,PI.price,PI.qty,PI.price*PI.qty,P.name from po_item PI,product P where PI.po_number='$po_number' and PI.product_code=P.code") or die "Can't prepare sql statement";
	$statement->execute or die "Can't execute";
	while (@row=$statement->fetchrow_array()) {
		print qq(<tr>
			<td width="20%">$row[0]</td>
			<td width="20%">$row[4]</td>
			<td width="20%" align="right">$row[1]</td>
			<td width="20%" align="right">$row[2]</td>
			<td width="20%" align="right">$row[3]</td>
			</tr>);
	}
	
	# a complex query to calculate the grand total of a PO
	$statement=$dbh->prepare("select sum(qty*price) from po_item where po_number='$po_number' group by po_number") or die "Can't prepare statement";
	$statement->execute or die "Can't execute statement";
	@row=$statement->fetchrow_array();
	print qq(<tr>
			<td width="20%">  </td>
			<td width="20%">  </td>
			<td width="20%">  </td>
			<td width="20%" align="right">Grand Total</td>
			<td width="20%" align="right">$row[0]</td>
			</tr></table></body></html>);
}

sub show_head {
	# show the top of this PO editing page
	my ($statement,@row);
	print qq(<html>
		<head>
		<meta http-equiv="Content-Language" content="en-us">
		<meta http-equiv="Content-Type" content="text/html; charset=big5">
		<title>PO Editing</title>
		</head>
		<body>
		<p><H1>PO Editing</H1></p>
		<form method="GET" action="po.cgi">
		<table border="0" cellpadding="0" width="87%">
		<tr>
		<td width="20%">PO number query</td>
		<td width="25%"><input type="text" name="q_po_number" size="20"></td>
		<td width="15%"></td>
		<td width="25%"></td>
		<td width="15%"><input type="submit" value="query" name="query"> <input type="reset" value="reset" name="reset"></td>
		</tr>
		<tr>
		<td width="20%">New PO number</td>
		<td width="25%"><input type="text" name="new_po_number" size="20"></td>
		<td width="15%">Supplier</td>
		<td width="25%"><select size="1" name="supplier_choice">);
	$statement=$dbh->prepare("select distinct code from supplier") or die "Can't prepare statement";
	$statement->execute or die "Can't execute statement";
	while (@row=$statement->fetchrow_array()) {
		print qq(<option>$row[0]</option>);
	}
	print qq(</select></td>
		<td width="15%"><input type="submit" value="new" name="new"></td>
		</tr>
		</table>);
}

sub show_end {
	# display end of html
	print qq(</form>
		<p></p>
		</body>
		</html>);
}

sub show_list {
	# a function to show all the PO in database, it is called when user come in by just clicking the link
	my ($statement,@row);
	print qq (<HR><table border="1" cellpadding="0" width="70%">
		<tr>
		<th width="30%">PO number</th>
		<th width="20%">Supplier Code</th>
		<th width="20%">Issue Date</th>
		<th width="20%">Staff in Charge</th>
		<th width="10%">voided</th>
		</tr>);
	$statement=$dbh->prepare("select * from po");
	$statement->execute or die "Can't execute statement";
	while (@row=$statement->fetchrow_array()) {
		if ($row[4]==0) {
			print qq(<tr><td width="30%"><a href="po.cgi?query=yes&q_po_number=$row[0]">$row[0]</a></td>
					<td width="20%">$row[1]</td>
					<td width="20%">$row[2]</td>
					<td width="20%">$row[3]</td>
					<td width="10%">No</td></tr>);
		} else {
			print qq(<tr><td width="30%">$row[0]</td>
					<td width="20%">$row[1]</td>
					<td width="20%">$row[2]</td>
					<td width="20%">$row[3]</td>
					<td width="10%">Yes</td></tr>);
		}
	}
	print qq(</table>);
}

sub show_query {
	# when user click the query button or follow a link in the list
	# he will come to here to edit a PO
	my ($po_number,$statement,$statement1,@row,$voided);
	if ($query) {
		$po_number=param('q_po_number');
	} elsif ($new) {
		$po_number=param('new_po_number');
	} else {
		$po_number=param('h_po_number');
	}
	print qq(<input type="hidden" name="h_po_number" value="$po_number">);
	
	# display information of this PO
	$statement=$dbh->prepare("select po_number,supplier_code,voided from po where po_number = '$po_number'") or die "Can't prepare sql statement";
	$statement->execute or die "Can't execute statement";
	@row=$statement->fetchrow_array();
	print qq(<hr><p>PO number : $row[0] Supplier : $row[1]);
	$voided=$row[2];
	if ($voided==1) {
		print qq(<b> voided</b></p>);
	} else {
		print qq(</p>);
	}
	print qq(<table border="1" cellpadding="0" width="70%">
		<tr>
		<th width="10%">delete?</th>
		<th width="30%">Product_code</th>
		<th width="20%">Price</th>
		<th width="20%">Quantity</th>
		<th width="20%">Total</th>
		</tr>);
		
	# display detail information of this PO
	$statement1=$dbh->prepare("select unique_id,product_code,price,qty,price*qty from po_item where po_number = '$po_number'") or die "Can't prepare sql statement";
	$statement1->execute or die "Can't execute statement";
	while (@row=$statement1->fetchrow_array()) {
		if ($voided==0) {
			print qq(<tr>
				<td width="10%"><input type="radio" name="delete_label" value="$row[0]"></td>
				<td width="30%">$row[1]</td>
				<td width="20%" align="right">$row[2]</td>
				<td width="20%" align="right">$row[3]</td>
				<td width="20%" align="right">$row[4]</td>
				</tr>);
		} else {
			print qq(<tr>
				<td width="10%">  </td>
				<td width="30%">$row[1]</td>
				<td width="20%" align="right">$row[2]</td>
				<td width="20%" align="right">$row[3]</td>
				<td width="20%" align="right">$row[4]</td>
				</tr>);
		}
	}
	
	# display the grand total value for this PO
	$statement1=$dbh->prepare("select sum(price*qty) from po_item where po_number = '$po_number' group by po_number") or die "Can't prepare sql statement";
	$statement1->execute or die "Can't execute statement";
	@row=$statement1->fetchrow_array();
	print qq(<tr>
			<td width="10%">  </td>
			<td width="30%">  </td>
			<td width="20%">  </td>
			<td width="20%">Grand total</td>
			<td width="20%" align="right">$row[0]</td>
			</tr>);
	print qq(</table>
		<p><input type="submit" value="delete" name="delete"> <input type="submit" value="void this po" name="void"> </p>);
	if ($voided==0) {
		print qq(<p><a href="po.cgi?print=print&id=$po_number" target="_blank">print this po</a></p>);
		show_insert();
	}
}

sub show_insert {
	# display the bottom part for user to insert an item to a PO
	my ($po_number,$statement,@row);
	if ($query) {
		$po_number=param('q_po_number');
	} elsif ($new) {
		$po_number=param('new_po_number');
	} else {
		$po_number=param('h_po_number');
	}
	$statement=$dbh->prepare("select SU.product_code from supplying SU, po P where SU.supplier_code=P.supplier_code and P.po_number='$po_number'") or die "Can't prepare SQL statement";
	$statement->execute or die "Can't execute statement";
	print qq(<hr>
		<p>Add an PO item</p>  
		<p>product code <select size="1" name="product_code">);
	while (@row=$statement->fetchrow_array()) {
		print qq(<option>$row[0]</option>);
	}
	print qq(</select> of quantity <input type="text" name="qty" size="20"><input type="submit" value="insert" name="insert"></p>);

}

sub do_new {
	# procedure to add a new PO
	my ($po_number,$supplier_code,$statement);
	$po_number=param('new_po_number');
	$supplier_code=param('supplier_choice');
	if ($dbh->do("insert into po(po_number,supplier_code,issue_date,staff_in_charge,voided) values ('$po_number','$supplier_code',sysdate,'$Staff_ID',0)")) {
		#ok
	} else {
		#failed
		print qq(<p><b>Can't create new po</b></p>);
	}
}

sub do_insert {
	# procedure to add an item in a PO
	my ($po_number,$product_code,$qty,$statement,$price);
	$po_number=param('h_po_number');
	$product_code=param('product_code');
	$qty=param('qty');
	$statement=$dbh->prepare("select SU.price from supplying SU,po P where SU.product_code='$product_code' and SU.supplier_code=P.supplier_code and P.po_number='$po_number'") or die "Can't prepare statement";
	$statement->execute or die "Can't execute statement";
	($price)=$statement->fetchrow_array();
	$dbh->do("insert into po_item(unique_id,po_number,product_code,price,qty) values (id_generate.nextval,'$po_number','$product_code',$price,$qty)");
	$dbh->do("update product set on_hand=on_hand+$qty where code='$product_code'");
}

sub do_delete {
	# procedure to delete an item in a PO
	my ($unique_id,$product_code,$qty,$statement,@row);
	$unique_id=param('delete_label');
	if ($unique_id) {
		$statement=$dbh->prepare("select product_code,qty from po_item where unique_id='$unique_id'") or die "Can't prepare sql statement";
		$statement->execute or die "Can't execut statement";
		($product_code,$qty)=$statement->fetchrow_array();
		$dbh->do("update product set on_hand=on_hand-$qty where code='$product_code'") or die "Can't prepare sql statement";
		$dbh->do("delete from po_item where unique_id='$unique_id'") or die "Can't prepare sql statement";
	}
}

sub do_void {
	# procedure to void the PO
	my ($po_number,$statement,$statement1,$product_code,$qty,@row);
	$po_number=param('h_po_number');
	$dbh->do("update po set voided=1 where po_number='$po_number'") or die "Can't prepare sql statement";
	$statement=$dbh->prepare("select product_code,qty from po_item where po_number='$po_number'") or die "Can't prepare sql statement";
	$statement->execute or die "Can't execut statement";
	while (@row=$statement->fetchrow_array()) {
		$product_code=$row[0];
		$qty=$row[1];
		$dbh->do("update product set on_hand=on_hand-$qty where code='$product_code'") or die "Can't prepare sql statement";
	}
}

