connect_error) {
throw new Exception("Error: " . $connection->connect_error);
}
} catch (Exception $e) {
exit($e->getMessage());
}
// Disable safe update mode (for this operation)
$disableSafeUpdateQuery = "SET SQL_SAFE_UPDATES = 0;";
$connection->query($disableSafeUpdateQuery);
$Username = $_SESSION['Username'];
// Use the sales_pilot database
$useDatabaseQuery = "USE sales_pilot";
$connection->query($useDatabaseQuery);
// Get the current date
$currentDate = date('Y-m-d');
// Calculate week start date as last Sunday
$weekStartDate = date('Y-m-d', strtotime('last Sunday', strtotime($currentDate)));
$sql = "CREATE TEMPORARY TABLE temp_weekly_sales AS
SELECT Product, SUM(DailySalesQty) AS WeeklySalesQty
FROM sales
WHERE Date >= '$weekStartDate' AND Date <= '$currentDate'
GROUP BY Product";
$connection->query($sql);
$sql = "UPDATE sales AS sr
JOIN temp_weekly_sales AS temp
ON sr.Product = temp.Product
SET sr.WeeklySalesQty = temp.WeeklySalesQty";
$connection->query($sql);
$sql = "DROP TEMPORARY TABLE IF EXISTS temp_weekly_sales";
$connection->query($sql);
// Get the current date
$currentDate = date('Y-m-d');
// Calculate week start date as last Sunday
$weekStartDate = date('Y-m-d', strtotime('last Sunday', strtotime($currentDate)));
// Function to fetch and calculate totals
function fetchAndCalculateTotals($connection, $Username) {
$stmt = $connection->prepare('SELECT COUNT(*) AS TotalProduct, SUM(TotalRevenue) AS TotalRevenue, SUM(InventoryValue) AS TotalInventoryValue FROM sales 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'],
'TotalRevenue' => $row['TotalRevenue'],
'TotalInventoryValue' => $row['TotalInventoryValue'],
];
} else {
return [
'TotalProduct' => 0,
'TotalRevenue' => 0,
'TotalInventoryValue' => 0,
];
}
}
// Fetch and calculate totals
$totalData = fetchAndCalculateTotals($connection, $Username);
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['update'])) {
// Handle the form submission for updating inventory
$salesrecordIds = $_POST['id_salesrecords'];
$products = $_POST['Product'];
$costPrices = $_POST['CostPrice'];
$inventoryValues = $_POST['InventoryValue'];
$salesPrices = $_POST['SalesPrice'];
$totalRevenues = $_POST['TotalRevenue'];
for ($i = 0; $i < count($salesrecordIds); $i++) {
$stmt = $connection->prepare("UPDATE sales SET Product = ?, CostPrice = ?, InventoryValue = ?, SalesPrice = ?, TotalRevenue = ? WHERE id_salesrecords = ?");
$stmt->bind_param('ssdddi', $products[$i], $costPrices[$i], $inventoryValues[$i], $salesPrices[$i], $totalRevenues[$i], $salesrecordIds[$i]);
$stmt->execute();
}
// Redirect to the same page to avoid form resubmission
header('Location: ' . $_SERVER['PHP_SELF']);
exit;
}
// Fetch user records based on their Username
$stmt = $connection->prepare('SELECT * FROM sales WHERE Username = ?');
$stmt->bind_param('s', $Username);
$stmt->execute();
$result = $stmt->get_result();
// Function to fetch products with highest values
function fetchProductWithHighestValue($connection, $Username, $valueType) {
$valueColumn = ($valueType === 'sales') ? 'TotalRevenue' : 'InventoryValue';
$stmt = $connection->prepare("SELECT Product FROM sales WHERE Username = ? ORDER BY $valueColumn 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';
}
}
// Fetch products with highest values
$HighestSalesValue = fetchProductWithHighestValue($connection, $Username, 'sales');
$HighestInventoryValue = fetchProductWithLowestValue($connection, $Username, 'inventory');
// Function to fetch products with lowest values
function fetchProductWithLowestValue($connection, $Username, $valueType) {
$valueColumn = ($valueType === 'sales') ? 'TotalRevenue' : 'InventoryValue';
$stmt = $connection->prepare("SELECT Product FROM sales WHERE Username = ? ORDER BY $valueColumn 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';
}
}
// Fetch products with lowest sales values
$LowestSalesValue = fetchProductWithLowestValue($connection, $Username, 'sales');
$LowestInventoryValue = fetchProductWithLowestValue($connection, $Username, 'inventory');
// Initialize start and end date variables
$startDate = isset($_GET['startDate']) ? $_GET['startDate'] : '';
$endDate = isset($_GET['endDate']) ? $_GET['endDate'] : '';
// Define SQL date range condition
$dateCondition = '';
if (!empty($startDate) && !empty($endDate)) {
$dateCondition = "AND Date BETWEEN '$startDate' AND '$endDate'";
}
// Fetch user records based on their Username
$stmt2 = $connection->prepare('SELECT * FROM sales WHERE Username = ?');
$stmt2->bind_param('s', $Username);
$stmt2->execute();
$result2 = $stmt2->get_result();
// Fetch data for the chart (e.g., products and net profit)
$data = array();
while ($row = $result2->fetch_assoc()) {
$data['Products'][] = $row['Product'];
$data['GrossProfitMargin'][] = $row['GrossProfitMargin'];
$data['NetProfitMargin'][] = $row['NetProfitMargin'];
$data['InventoryToSalesRatio'][] = $row['InventoryToSalesRatio'];
$data['ReturnOnInvestment'][] = $row['ReturnOnInvestment'];
}
// Re-enable safe update mode (recommended for security)
$enableSafeUpdateQuery = "SET SQL_SAFE_UPDATES = 1;";
$connection->query($enableSafeUpdateQuery);
?>
Product Analytics Dashboard
