#!/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(
Login Again
You need to login before using this page!!!
);
}
$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(
PO form
Purchase Order
Supplier code
$row[0]
Supplier phone
$row[3]
PO number
$po_number
Supplier name
$row[1]
Supplier e-mail
$row[4]
Date of issue
$row[5]
Supplier address
$row[2]
Staff in charge
$row[6]
Details
Product code
Product name
Price
Quantity
Total
);
# 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(
$row[0]
$row[4]
$row[1]
$row[2]
$row[3]
);
}
# 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(
Grand Total
$row[0]
);
}
sub show_head {
# show the top of this PO editing page
my ($statement,@row);
print qq(
PO Editing
PO Editing
);
}
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 (
PO number
Supplier Code
Issue Date
Staff in Charge
voided
);
$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(
);
}
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();
# 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(
PO number : $row[0] Supplier : $row[1]);
$voided=$row[2];
if ($voided==1) {
print qq( voided
);
} else {
print qq();
}
print qq(
delete?
Product_code
Price
Quantity
Total
);
# 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(
$row[1]
$row[2]
$row[3]
$row[4]
);
} else {
print qq(
$row[1]
$row[2]
$row[3]
$row[4]
);
}
}
# 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(
);
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(
Add an PO item
product code of quantity
);
}
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(
Can't create new po
);
}
}
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";
}
}