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(sales.Product) AS ProductNames, SUM(sales.InventoryQty) AS TotalInventoryQty, SUM(sales.DailySalesQty) AS TotalDailySalesQty, SUM(sales.MonthlySalesQty) AS TotalMonthlySalesQty, SUM(sales.WeeklySalesQty) AS TotalWeeklySalesQty, SUM(sales.YearlySalesQty) AS TotalYearlySalesQty, SUM(sales.AnnualRevenue) AS AnnualSales FROM sales INNER JOIN category ON sales.sales_categoryid = category.id_category WHERE sales.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 $data = array(); while ($row = $result->fetch_assoc()) { $data[] = $row; } // Fetch and store the sales data $query = "SELECT s.Product, c.categoryname, SUM(s.DailySalesQty) AS DailySalesQty, SUM(s.WeeklySalesQty) AS WeeklySalesQty, SUM(s.MonthlySalesQty) AS MonthlySalesQty, SUM(s.YearlySalesQty) AS YearlySalesQty, SUM(s.InventoryQty) AS 'Total Stock' FROM sales AS s INNER JOIN category AS c ON s.sales_categoryid = c.id_category WHERE s.Username = ? GROUP BY s.Product, c.categoryname"; $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 $salesData = array(); // Fetch data and store it in the array while ($row = $result->fetch_assoc()) { $salesData[] = $row; } $pieChartLabels = array(); $pieChartData = array(); // Create an associative array to store category-wise total revenue $categoryTotalSales = array(); foreach ($data as $row) { $category_name = $row['categoryname']; // Get the category name $total_revenue = $row['AnnualSales']; // 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($categoryTotalSales[$category_name])) { $categoryTotalSales[$category_name] = 0; } // Sum the TotalInventoryQty for products in the same category $categoryTotalSales[$category_name] += $total_revenue; } // Get the total revenue for each category and maintain the original order foreach ($pieChartLabels as $category_name) { $pieChartData[] = $categoryTotalSales[$category_name]; } $query2 = "SELECT category.categoryname AS categoryname, SUM(sales.YearlySalesQty) AS TotalYearlySalesQty FROM sales INNER JOIN category ON sales.sales_categoryid = category.id_category WHERE sales.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 sales quantity $categoryDailySalesQty = array(); foreach ($result2 as $row) { // Use $result2 instead of $data to fetch the results $category_name = $row['categoryname']; // Get the category name $total_sales_qty = $row['TotalYearlySalesQty']; // 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 sales quantities, initialize it if (!isset($categoryDailySalesQty[$category_name])) { $categoryDailySalesQty[$category_name] = 0; } // Sum the DailySalesQty for products in the same category $categoryDailySalesQty[$category_name] += $total_sales_qty; } // Get the total sales quantity for each category and maintain the original order foreach ($barChartLabels as $category_name) { $barChartData[] = $categoryDailySalesQty[$category_name]; } $connection->close(); ?> Sales - Analytics

Total Revenue by Category ($)

Products & Category

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

Total Sales by Category

Products Sold

"; echo ""; echo ""; // Use categoryNames array to get category name echo ""; echo ""; echo ""; echo ""; echo ""; } ?>
Product Category Daily Sales Weekly Sales Monthly Sales Yearly Sales
" . $item['Product'] . "" . $categoryNames[$item['categoryname']] . "" . $item['DailySalesQty'] . "" . $item['WeeklySalesQty'] . "" . $item['MonthlySalesQty'] . "" . $item['YearlySalesQty'] . "