connect_error) { throw new Exception("Error: " . $connection->connect_error); } } catch (Exception $e) { exit($e->getMessage()); } $Username = $_SESSION['Username']; // Fetch category names and IDs $query = "SELECT id_category, categoryname FROM category"; $result = $connection->query($query); $categoryNames = array(); while ($row = $result->fetch_assoc()) { $categoryNames[$row['id_category']] = $row['categoryname']; } // Fetch user records based on their Username and group by category $query = "SELECT category.categoryname AS categoryname, GROUP_CONCAT(inventory.Product) AS ProductNames, SUM(inventory.InventoryValue) AS InventoryValue, SUM(inventory.InventoryQty) AS TotalInventoryQty, SUM(inventory.StockValue) AS TotalStockValue, SUM(inventory.SupplyValue) AS TotalSupplyValue FROM inventory INNER JOIN category ON inventory.category_id = category.id_category WHERE inventory.Username = ? GROUP BY categoryname"; $stmt = $connection->prepare($query); $stmt->bind_param('s', $Username); $stmt->execute(); $result = $stmt->get_result(); // Fetch the data and store it in an associative array $productCategoryData = array(); while ($row = $result->fetch_assoc()) { $productCategoryData[] = $row; } // Fetch and store the inventory data $query = "SELECT i.Product, i.Description, c.categoryname, i.CostPrice, i.SalesPrice, SUM(InventoryQty) AS StockQty, SUM(CostPrice) AS TotalCost, SUM(SalesPrice) AS TotalSales, SUM(StockQty) AS StockValue, SUM(InventoryValue) AS Revenue FROM inventory AS i INNER JOIN category AS c ON i.category_id = c.id_category WHERE i.Username = ? GROUP BY i.Product, i.Description, c.categoryname, i.CostPrice, i.SalesPrice"; $stmt = $connection->prepare($query); $stmt->bind_param('s', $Username); $stmt->execute(); $result = $stmt->get_result(); // Initialize an array to store the fetched data $inventoryData = array(); while ($row = $result->fetch_assoc()) { $inventoryData[] = $row; } $pieChartLabels = array(); $pieChartData = array(); // Create an associative array to store category-wise total revenue $categoryRevenue = array(); foreach ($inventoryData as $row) { $category_name = $row['categoryname']; // Get the category name $total_revenue = $row['Revenue']; // Format the total revenue with dollar sign and commas $formatted_total_revenue = '$' . number_format($total_revenue, 2, '.', ','); // If the category is not in the list of labels, add it if (!in_array($category_name, $pieChartLabels)) { $pieChartLabels[] = $category_name; } // If the category is not in the list of category revenues, initialize it if (!isset($categoryRevenue[$category_name])) { $categoryRevenue[$category_name] = 0; } // Sum the InventoryValue for products in the same category $categoryRevenue[$category_name] += $total_revenue; } // Get the total revenue for each category and maintain the original order foreach ($pieChartLabels as $category_name) { $pieChartData[] = $categoryRevenue[$category_name]; } $query2 = "SELECT category.categoryname AS categoryname, SUM(inventory.InventoryQty) AS TotalInventoryQty FROM inventory INNER JOIN category ON inventory.category_id = category.id_category WHERE inventory.Username = ? GROUP BY categoryname"; $stmt2 = $connection->prepare($query2); $stmt2->bind_param('s', $Username); $stmt2->execute(); $result2 = $stmt2->get_result(); $barChartLabels = array(); $barChartData = array(); // Create an associative array to store category-wise total inventory quantity $categoryInventoryQty = array(); foreach ($result2 as $row) { // Use $result2 instead of $data to fetch the results $category_name = $row['categoryname']; // Get the category name $total_inventory_qty = $row['TotalInventoryQty']; // If the category is not in the list of labels, add it if (!in_array($category_name, $barChartLabels)) { $barChartLabels[] = $category_name; } // If the category is not in the list of category inventory quantities, initialize it if (!isset($categoryInventoryQty[$category_name])) { $categoryInventoryQty[$category_name] = 0; } // Sum the InventoryQty for products in the same category $categoryInventoryQty[$category_name] += $total_inventory_qty; } // Get the total inventory quantity for each category and maintain the original order foreach ($barChartLabels as $category_name) { $barChartData[] = $categoryInventoryQty[$category_name]; } // Fetch and store the inventory data $query = "SELECT i.Product, i.Description, c.categoryname, i.CostPrice, i.SalesPrice, SUM(InventoryQty) AS StockQty, SUM(CostPrice) AS TotalCost, SUM(SalesPrice) AS TotalSales, SUM(StockQty) AS StockValue, SUM(InventoryValue) AS Revenue, SUM(YearlySalesQty) AS YearlySalesQty FROM inventory AS i INNER JOIN category AS c ON i.category_id = c.id_category WHERE i.Username = ? GROUP BY i.Product, i.Description, c.categoryname, i.CostPrice, i.SalesPrice"; $stmt = $connection->prepare($query); // Use bind_param to bind parameters safely $stmt->bind_param('s', $Username); // Execute the query $stmt->execute(); // Get the result set $result = $stmt->get_result(); // Initialize an array to store the fetched data $inventoryData = array(); // Fetch data and store it in the array while ($row = $result->fetch_assoc()) { $inventoryData[] = $row; } // Now, let's sort the $inventoryData array based on YearlySalesQty in descending order usort($inventoryData, function ($a, $b) { return $b['YearlySalesQty'] - $a['YearlySalesQty']; }); // Now $inventoryData contains the top-selling products based on YearlySalesQty // You can use this array to display the information as needed // Fetch user records based on their Username and group by category and date $query = "SELECT category.categoryname AS categoryname, SUM(inventory.InventoryQty) AS InventoryQty, SUM(inventory.StockQty) AS StockQty, SUM(inventory.SupplyQty) AS SupplyQty FROM inventory INNER JOIN category ON inventory.category_id = category.id_category WHERE inventory.Username = ? GROUP BY categoryname"; $stmt = $connection->prepare($query); $stmt->bind_param('s', $Username); $stmt->execute(); $result = $stmt->get_result(); // Fetch the data and store it in an associative array $categoryQtyData = array(); while ($row = $result->fetch_assoc()) { $categoryQtyData[] = $row; } $lineChartLabels = array(); $inventoryQtyData = array(); $StockQtyData = array(); $supplyQtyData = array(); foreach ($categoryQtyData as $row) { $inventoryQty = $row['InventoryQty']; $StockQty = $row['StockQty']; $SupplyQty = $row['SupplyQty']; $lineChartLabels[] = $row['categoryname']; $inventoryQtyData[] = $inventoryQty; $StockQtyData[] = $StockQty; $supplyQtyData[] = $SupplyQty; } $connection->close(); ?> Inventory Reports

Stock Overview

Category Qty

Category Value ($)

Top Selling Items

in ' . htmlspecialchars($categoryNames[$item['categoryname']]) . ''; ?> - Total Sales: , Inventory Quantity:

6h ago.

Inventory Overview

"; echo ""; echo ""; // Use categoryNames array to get category name echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; } ?>
Product Category Total Stock Unit Price Sales Price Stock Value Total Worth
" . $item['Product'] . "" . $categoryNames[$item['categoryname']] . "" . $item['StockQty'] . "" . $item['TotalCost'] . "" . $item['TotalSales'] . "" . $item['StockValue'] . "" . $item['Revenue'] . "

Products & Category

"; echo ""; echo ""; echo ""; echo ""; } ?>
Category Items Product Names
" . $categoryNames[$item['categoryname']] . "" . count(explode(",", $item['ProductNames'])) . "" . $item['ProductNames'] . "