connect_error) {
throw new Exception("Error: " . $connection->connect_error);
}
} catch (Exception $e) {
exit($e->getMessage());
}
if (!isset($_SESSION['Username'])) {
header('Location: loginpage.php');
exit();
}
$Username = $_SESSION['Username'];
if (isset($_POST['update'])) {
// Handle the "Update" button click
$products = $_POST['Product'];
$stockQty = $_POST['StockQty'];
$supplyQty = $_POST['SupplyQty'];
$costPrice = $_POST['CostPrice'];
$salesPrice = $_POST['SalesPrice'];
// Loop through the form data and update the database
$stmt = $connection->prepare('UPDATE inventory SET StockQty = ?, SupplyQty = ?, CostPrice = ?, SalesPrice = ? WHERE Product = ?');
$stmt->bind_param('dddds', $stockQtyValue, $supplyQtyValue, $costPriceValue, $salesPriceValue, $product);
foreach ($products as $key => $product) {
$stockQtyValue = $stockQty[$key];
$supplyQtyValue = $supplyQty[$key];
$costPriceValue = $costPrice[$key];
$salesPriceValue = $salesPrice[$key];
$stmt->execute();
}
// Redirect to the current page to refresh the data
header('Location: ' . $_SERVER['PHP_SELF']);
exit();
}
if (isset($_POST['delete_metrics'])) {
// Handle the "Delete" button click
$selectedMetrics = $_POST['selected_metrics'];
// Loop through the selected metrics and delete them from the database
$stmt = $connection->prepare('DELETE FROM inventory WHERE id_inventory = ?');
$stmt->bind_param('d', $idInventory);
foreach ($selectedMetrics as $idInventory) {
$stmt->execute();
}
// Redirect to the current page to refresh the data
header('Location: ' . $_SERVER['PHP_SELF']);
exit();
}
// Function to fetch and calculate totals
function fetchAndCalculateTotals($connection, $Username)
{
$stmt = $connection->prepare('SELECT COUNT(*) AS TotalProduct, SUM(InventoryValue) AS TotalInventoryValue FROM inventory WHERE Username = ?');
$stmt->bind_param('s', $Username);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
$row = $result->fetch_assoc();
return [
'TotalProduct' => $row['TotalProduct'],
'TotalInventoryValue' => $row['TotalInventoryValue']
];
} else {
return [
'TotalProduct' => 0,
'TotalInventoryValue' => 0
];
}
}
// Fetch and calculate totals
$totalData = fetchAndCalculateTotals($connection, $Username);
// Function to fetch the category name based on category ID
function fetchCategoryName($connection, $categoryId)
{
$stmt_category = $connection->prepare("SELECT categoryname FROM category WHERE id_category = ?");
$stmt_category->bind_param('i', $categoryId);
$stmt_category->execute();
$result_category = $stmt_category->get_result();
if ($result_category->num_rows > 0) {
$row = $result_category->fetch_assoc();
return $row['categoryname'];
} else {
return 'Uncategorized'; // Provide a default value if no category is found
}
}
// Fetch data for product metrics with category information
$stmt_metrics = $connection->prepare('
SELECT inventory.*, category.categoryname
FROM inventory
JOIN category ON inventory.category_id = category.id_category
WHERE inventory.Username = ?
');
$stmt_metrics->bind_param('s', $Username);
$stmt_metrics->execute();
$result_metrics = $stmt_metrics->get_result();
// Function to fetch the product with the highest inventory value
function fetchProductWithHighestTotalRevenue($connection, $Username)
{
$stmt = $connection->prepare("SELECT Product FROM inventory WHERE Username = ? ORDER BY InventoryValue DESC LIMIT 1");
$stmt->bind_param('s', $Username);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
$row = $result->fetch_assoc();
return $row['Product'];
} else {
return 'No products found';
}
}
$HighestTotalRevenue = fetchProductWithHighestTotalRevenue($connection, $Username);
// Function to fetch the product with the lowest inventory value
function fetchProductWithLowestTotalRevenue($connection, $Username)
{
$stmt = $connection->prepare("SELECT Product FROM inventory WHERE Username = ? ORDER BY InventoryValue ASC LIMIT 1");
$stmt->bind_param('s', $Username);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
$row = $result->fetch_assoc();
return $row['Product'];
} else {
return 'No products found';
}
}
$LowestTotalRevenue = fetchProductWithLowestTotalRevenue($connection, $Username);
?>
Product
