connect_error) {
throw new Exception("Error: " . $connection->connect_error);
}
} catch (Exception $e) {
exit($e->getMessage());
}
$Username = $_SESSION['Username'];
// 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);
// Fetch category names and IDs
$categoryNames = array();
$stmt = $connection->prepare('SELECT id_category, categoryname FROM category');
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
$categoryNames[$row['id_category']] = $row['categoryname'];
}
// Fetch user records based on their Username and group by category
$stmt = $connection->prepare('SELECT category.categoryname AS categoryname, GROUP_CONCAT(inventory.Product) AS ProductNames, SUM(inventory.TotalRevenue) AS TotalRevenue, SUM(inventory.InventoryValue) AS InventoryValue, SUM(inventory.StockValue) AS StockValue, SUM(inventory.SupplyValue) AS SupplyValue
FROM inventory
INNER JOIN category ON inventory.category_id = category.id_category
WHERE inventory.Username = ?
GROUP BY categoryname'); // Use categoryname instead of category.categoryname
$stmt->bind_param('s', $Username);
$stmt->execute();
$result = $stmt->get_result();
// Fetch data for the chart (e.g., products and net profit)
$data = array(
'categoryname' => array(),
'ProductNames' => array(),
'TotalRevenue' => array(),
'InventoryValue' => array(),
'StockValue' => array(),
'SupplyValue' => array(),
);
while ($row = $result->fetch_assoc()) {
$data['categoryname'][] = $row['categoryname'];
$data['ProductNames'][] = $row['ProductNames'];
$data['TotalRevenue'][] = $row['TotalRevenue'];
$data['InventoryValue'][] = $row['InventoryValue'];
$data['StockValue'][] = $row['StockValue'];
$data['SupplyValue'][] = $row['SupplyValue'];
}
?>
Inventory - Analytics
Analytics Overview
Category: 10
Total Products:
Total Stock Value($):
Date:
