Fulfillment

This project was part of the ICS 321 Database Systems I course at the University of Hawai’i. The goal was to create a database functionality that automates a task of ordering sold items from a vendor to re-stock storage space that can lead to a faster end client delivery and reduce human errors, the process also called fulfillment.

The objective of the project is utilize triggers and stored procedures learned earlier in the course. The first part of the system was designed to monitor sale of items insertion in real-time and automatically generate purchase orders. The second part is to create a confirmation procedure that would place the order and update the purchase date.

The system was implemented using MySQL and PHP, and it included features such as sale parameters, and order confirmation.

To place new sale I wrote PHP script https://dmitryg.ics321.org/app_sale.php The script can be called with parameters to insert new sale.

ini_set('display_errors', '1');
ini_set('display_startup_errors', '1');
error_reporting(E_ALL);

header('Content-Type: application/json');

$user = '';  // username you use to log into cPanel
$pass = '';  // password you use to log into cPanel
$host = "localhost";
$database = $user."_QACS";

/*
SET @customer_id='10';
SET @employee_id='5';
SET @sale_date='2019-02-13';
SET @item_id='13';
SET @tax_rate='4.712';
SET @sale_id='0';
CALL ADD_SALE(@customer_id,@employee_id,@sale_date,@item_id,@tax_rate,@sale_id,@sub_total,@total,@err,@msg);
*/
$customer_id = 10;
$employee_id = 5;
$sale_date = '2019-02-13';
$item_id = '13';
$tax_rate = '4.712';
$sale_id = '0';
$sub_total = 0;
$total = 0;

if(isset($_REQUEST['customer_id'])) $customer_id=$_REQUEST['customer_id'];
if(isset($_REQUEST['employee_id'])) $employee_id=$_REQUEST['employee_id'];
if(isset($_REQUEST['sale_date'])) $sale_date=$_REQUEST['sale_date'];
if(isset($_REQUEST['item_id'])) $item_id=$_REQUEST['item_id'];
if(isset($_REQUEST['tax_rate'])) $tax_rate=$_REQUEST['tax_rate'];

$dsn = "mysql:host=$host;dbname=$database";
$pdo = new PDO($dsn, $user, $pass);

/* customer_id, employee_id, sale_date, item_id, tax_rate, sale_id, sub_total, total, err, msg */
$sql = "CALL ADD_SALE(:customer_id,:employee_id,:sale_date,:item_id,:tax_rate,@sale_id,@sub_total,@total,@err,@msg);";

$statement = $pdo->prepare($sql);
$statement->bindValue(":customer_id", $customer_id);
$statement->bindValue(":employee_id", $employee_id);
$statement->bindValue(":sale_date", $sale_date);
$statement->bindValue(":item_id", $item_id);
$statement->bindValue(":tax_rate", $tax_rate);
$statement->execute();

$sql = "SELECT @err AS err, @msg AS msg2";

$statement = $pdo->query($sql);

if ($rec = $statement->fetch()) {
    $err = $rec['err'];
    $msg = $rec['msg2'];
}
$out= json_encode(
[   "msg" => "$msg",
"error" => "$err",
"customer_id" => "$customer_id",
"employee_id" => "$employee_id",
"sale_date" => "$sale_date",
"item_id" => "$item_id",
"tax_rate" => "$tax_rate",
"sale_id" => "$sale_id",
"sub_total" => "$sub_total",
"total" => "$total"
]);
echo $out;

Next step was to create a trigger that would automatically generate a purchase order when a sale of an item is inserted.

CREATE TRIGGER ADD_OrderVendor AFTER INSERT ON SALE_ITEM
FOR EACH ROW BEGIN

SET @EmployeeID = (SELECT EmployeeID FROM SALE WHERE SaleID = NEW.SaleID);

SET @VendorID = (SELECT VendorID FROM ITEM WHERE ItemID = NEW.ItemID);

INSERT INTO ORDER_VENDOR(SaleID, SaleItemID, ItemID, EmployeeID, VendorID) VALUES (NEW.SaleID, NEW.SaleItemID, NEW.ItemID, @EmployeeID, @VendorID);

END

It basically inserts a new record into the ORDER_VENDOR table with the SaleID, SaleItemID, ItemID from the SALE_ITEM table. In addition EmployeeID, and VendorID are retrieved from the SALE and ITEM tables respectively.

The final step was to create a stored procedure that would confirm the order and update the purchase date.

DELIMITER $$
CREATE DEFINER=dmitr321@localhost PROCEDURE ACCEPT_ORDER_VENDOR()
BEGIN

SET @order_id = (SELECT MAX(OrderID) AS last_id FROM ORDER_VENDOR);
SET @current_date = CURDATE();

SET @item_id = (SELECT ItemID FROM ORDER_VENDOR WHERE OrderID=@order_id);

UPDATE ORDER_VENDOR SET OrderDate=@current_date WHERE OrderID = @order_id;

UPDATE ITEM SET PurchaseDate=@current_date WHERE ItemID = @item_id;

END$$
DELIMITER;

This stored procedure retrieves the maximum OrderID from the ORDER_VENDOR table, sets the current date, and updates the OrderDate in the ORDER_VENDOR table and the PurchaseDate in the ITEM table.

PHP script to call the stored procedure: https://dmitryg.ics321.org/confirm_order.php It exicutes the stored procedure to confirm the order and update the purchase date. Return in JSON format the result of operation.

ini_set('display_errors', '1');
ini_set('display_startup_errors', '1');
error_reporting(E_ALL);

header('Content-Type: application/json');

$user = '';  // username you use to log into cPanel
$pass = '';  // password you use to log into cPanel
$host = "localhost";
$database = $user."_QACS";

$dsn = "mysql:host=$host;dbname=$database";
$pdo = new PDO($dsn, $user, $pass);

$sql = "CALL ACCEPT_ORDER_VENDOR();";

$statement = $pdo->prepare($sql);
$statement->execute();

$sql = "SELECT @err AS err, @msg AS msg2";

$statement = $pdo->query($sql);

if ($rec = $statement->fetch()) {
$err = $rec['err'];
$msg = $rec['msg2'];
}

$out= json_encode(
[   "msg" => "$msg",
"error" => "$err"
]);
echo $out;

The project was a great opportunity to apply the concepts learned in class and gain hands-on experience with database design and implementation. There is a room for improvement in the design of the database and the implementation of the stored procedures. I learned a lot about triggers, stored procedures, and how to work with MySQL and PHP to create a functional database system.

Demo Video