connect_error) { throw new Exception("Error: " . $connection->connect_error); } } catch (Exception $e) { exit($e->getMessage()); } $Username = $_SESSION['Username']; function fetchInventoryDataByMonth($connection, $Username, $month) { $stmt = $connection->prepare(' SELECT inventory.Product, category.categoryname, inventory.InventoryQty, inventory.StockQty, inventory.SupplyQty, inventory.Date FROM inventory JOIN category ON inventory.category_id = category.id_category WHERE inventory.Username = ? AND MONTH(inventory.Date) = ? '); $stmt->bind_param('ss', $Username, $month); $stmt->execute(); $result = $stmt->get_result(); return $result->fetch_all(MYSQLI_ASSOC); } // Function to fetch data from inventory table based on date function fetchInventoryDataByDate($connection, $Username, $date) { $stmt = $connection->prepare(' SELECT inventory.Product, category.categoryname, inventory.InventoryQty, inventory.StockQty, inventory.SupplyQty, inventory.Date FROM inventory JOIN category ON inventory.category_id = category.id_category WHERE inventory.Username = ? AND inventory.Date = ? '); $stmt->bind_param('ss', $Username, $date); $stmt->execute(); $result = $stmt->get_result(); return $result->fetch_all(MYSQLI_ASSOC); } // Define the fetchCategoryName function function fetchCategoryName($connection, $categoryName) { $stmt = $connection->prepare("SELECT categoryname FROM category WHERE id_category = ?"); $stmt->bind_param('s', $categoryName); $stmt->execute(); $result = $stmt->get_result(); if ($result->num_rows > 0) { $row = $result->fetch_assoc(); return $row['categoryname']; } else { return 'Category not found'; } } // Function to fetch and calculate totals function fetchAndCalculateTotals($connection, $Username) { $stmt = $connection->prepare('SELECT COUNT(*) AS TotalProduct, SUM(InventoryQty) AS TotalInventoryQty 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'], 'TotalInventoryQty' => $row['TotalInventoryQty'] ]; } else { return [ 'TotalProduct' => 0, 'TotalInventoryQty' => 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.WeeklyStockQty) AS WeeklyStockQty, 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'); // 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(), 'WeeklyStockQty' => array(), 'InventoryQty' => array(), 'StockQty' => array(), 'SupplyQty' => array(), ); while ($row = $result->fetch_assoc()) { $data['categoryname'][] = $row['categoryname']; $data['ProductNames'][] = $row['ProductNames']; $data['WeeklyStockQty'][] = $row['WeeklyStockQty']; $data['InventoryQty'][] = $row['InventoryQty']; $data['StockQty'][] = $row['StockQty']; $data['SupplyQty'][] = $row['SupplyQty']; } // Initialize an array to store aggregated data $aggregatedData = []; // Fetch and aggregate data for each month and week for ($month = 1; $month <= 12; $month++) { // Fetch data for the month $monthlyData = fetchInventoryDataByMonth($connection, $Username, $month); // Key to identify the month $key = $month; // If the key doesn't exist, initialize the aggregated data if (!isset($aggregatedData[$key])) { $aggregatedData[$key] = [ 'Month' => date('F', mktime(0, 0, 0, $month, 1)), 'Weeks' => [], // Initialize the 'Weeks' array 'Categories' => [], 'Products' => [], 'StockQty' => 0, 'SupplyQty' => 0, 'InventoryQty' => 0, ]; } if (!empty($monthlyData)) { foreach ($monthlyData as $rowMonth) { // Fetch category and product names $categoryName = fetchCategoryName($connection, $rowMonth['categoryname']); $productName = $rowMonth['Product']; // Check if the category and product combination already exists $keyCombination = $categoryName . '|' . $productName; if (!in_array($keyCombination, $aggregatedData[$key]['Categories'])) { $aggregatedData[$key]['Categories'][] = $keyCombination; $aggregatedData[$key]['Products'][] = $productName; // Update the aggregated data $aggregatedData[$key]['StockQty'] += $rowMonth['StockQty']; $aggregatedData[$key]['SupplyQty'] += $rowMonth['SupplyQty']; $aggregatedData[$key]['InventoryQty'] += $rowMonth['InventoryQty']; } // Store the week number if not already present $weekNumber = date('W', strtotime($rowMonth['Date'])); if (!in_array($weekNumber, $aggregatedData[$key]['Weeks'])) { $aggregatedData[$key]['Weeks'][] = $weekNumber; } } } } ?> Inventory - Weekly

Weekly Overview

Category: 10

Total Products:

Total Inventory Qty:

Date:

Weekly Inventory

date('F', mktime(0, 0, 0, $month, 1)), 'Weeks' => [], // Initialize the 'Weeks' array 'Categories' => [], 'Products' => [], 'StockQty' => 0, 'SupplyQty' => 0, 'InventoryQty' => 0, ]; } if (!empty($monthlyData)) { foreach ($monthlyData as $rowMonth) { // Fetch category and product names $categoryName = fetchCategoryName($connection, $rowMonth['categoryname']); $productName = $rowMonth['Product']; // Check if the category and product combination already exists $keyCombination = $categoryName . '|' . $productName; if (!in_array($keyCombination, $aggregatedData[$key]['Categories'])) { $aggregatedData[$key]['Categories'][] = $keyCombination; $aggregatedData[$key]['Products'][] = $productName; // Update the aggregated data $aggregatedData[$key]['StockQty'] += $rowMonth['StockQty']; $aggregatedData[$key]['SupplyQty'] += $rowMonth['SupplyQty']; $aggregatedData[$key]['InventoryQty'] += $rowMonth['InventoryQty']; } } } } // Display the aggregated data in the table foreach ($aggregatedData as $row) { echo ""; echo ""; echo ""; // Display actual week numbers echo ""; echo ""; echo ""; echo ""; echo ""; } ?>
Month Week Number Product Stock Qty Supply Qty Inventory Qty
{$row['Month']}" . implode(', ', $row['Weeks']) . "" . implode(', ', $row['Products']) . "{$row['StockQty']}{$row['SupplyQty']}{$row['InventoryQty']}