File "view_sales.php"
Full Path: /home/quiczmwg/lightspringdigitals.com/dashboard-20260114051212/src/view_sales.php
File size: 7.1 KB
MIME-type: text/x-php
Charset: utf-8
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
include "./include/head.php";
include "sidebar.php";
include "./include/navbar.php";
if (!isset($_SESSION['userid'])) {
header("Location: login.php");
exit();
}
$userid = $_SESSION['userid'];
// Ensure product_id is provided
if (!isset($_GET['product_id'])) {
die("<div class='alert alert-danger m-4'>❌ Product ID not provided.</div>");
}
$product_id = $_GET['product_id'];
// Fetch product details (prepared)
$stmt = $conn->prepare("SELECT product_name, image FROM products WHERE product_id = ?");
if (!$stmt) {
die("<div class='alert alert-danger m-4'>❌ Query error: " . htmlspecialchars($conn->error) . "</div>");
}
$stmt->bind_param("s", $product_id);
$stmt->execute();
$product = $stmt->get_result()->fetch_assoc();
$stmt->close();
if (!$product) {
die("<div class='alert alert-danger m-4'>❌ Product not found.</div>");
}
// Check if buyer_name column exists in product_sales
$colCheck = $conn->query("SHOW COLUMNS FROM product_sales LIKE 'buyer_name'");
$hasBuyerName = ($colCheck && $colCheck->num_rows > 0);
// Build sales query dynamically and safely
$buyerSelect = $hasBuyerName ? "ps.buyer_name" : "'' AS buyer_name";
$salesSql = "
SELECT
ps.sale_amount,
ps.quantity,
ps.total_amount,
ps.sale_date,
{$buyerSelect}
FROM product_sales ps
WHERE ps.product_id = ? AND ps.userid = ?
ORDER BY ps.sale_date ASC
";
$salesStmt = $conn->prepare($salesSql);
if (!$salesStmt) {
die("<div class='alert alert-danger m-4'>❌ Failed to prepare sales query: " . htmlspecialchars($conn->error) . "</div>");
}
$salesStmt->bind_param("ss", $product_id, $userid);
$salesStmt->execute();
$res = $salesStmt->get_result();
// Collect rows
$sales_rows = [];
while ($r = $res->fetch_assoc()) {
$sales_rows[] = $r;
}
$sales_dates = [];
$quantities = [];
$total_amounts = [];
$total_sales = 0;
foreach ($sales_rows as $sale) {
$sales_dates[] = date("M d", strtotime($sale['sale_date']));
$quantities[] = (int)$sale['quantity'];
$total_amounts[] = (float)$sale['total_amount'];
$total_sales += (float)$sale['total_amount'];
}
// Handle export (CSV / Excel)
if (isset($_GET['export']) && in_array($_GET['export'], ['csv', 'excel'])) {
$filename = "sales_report_" . $product_id . "." . ($_GET['export'] === 'csv' ? 'csv' : 'xls');
header("Content-Disposition: attachment; filename=$filename");
header("Content-Type: application/vnd.ms-excel; charset=utf-8");
if ($hasBuyerName) {
echo "Buyer Name,Sale Amount (Per Unit),Quantity,Total Amount,Sale Date\n";
} else {
echo "Sale Amount (Per Unit),Quantity,Total Amount,Sale Date\n";
}
foreach ($sales_rows as $row) {
$line = [];
if ($hasBuyerName) {
$line[] = '"' . str_replace('"', '""', $row['buyer_name']) . '"';
}
$line[] = number_format($row['sale_amount'], 2);
$line[] = (int)$row['quantity'];
$line[] = number_format($row['total_amount'], 2);
$line[] = date("Y-m-d H:i:s", strtotime($row['sale_date']));
echo implode(",", $line) . "\n";
}
exit;
}
?>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<style>
body { background: #f9fafc; }
.sales-table { background: #fff; border-radius: 16px; box-shadow: 0 8px 25px rgba(0, 0, 0, 0.05); overflow: hidden; }
.table thead { background-color: #f4f6f8; font-weight: 600; color: #333; }
.table td, .table th { vertical-align: middle; }
.product-img { width: 60px; height: 60px; object-fit: cover; border-radius: 8px; }
.total-card { background: #28a745; color: white; padding: 15px 20px; border-radius: 12px; font-size: 1.25rem; font-weight: 600; margin-bottom: 20px; }
</style>
<div class="container py-5">
<div class="d-flex justify-content-between align-items-center mb-4">
<h2 class="fw-bold text-white">📊 Sales Report</h2>
<a href="my_promotions.php" class="btn btn-sm btn-outline-secondary">← Back</a>
</div>
<div class="sales-table p-4 mb-4">
<div class="d-flex align-items-center mb-3">
<img src="./uploads/<?= htmlspecialchars($product['image']) ?>" class="product-img me-3" alt="">
<h4 class="fw-bold mb-0"><?= htmlspecialchars($product['product_name']) ?></h4>
</div>
<div class="total-card">
💰 Total Trade Amount: $<?= number_format($total_sales, 2) ?>
</div>
<div class="table-responsive mt-4">
<table class="table table-striped align-middle">
<thead>
<tr>
<th>#</th>
<?php if ($hasBuyerName): ?>
<th>Buyer Name</th>
<?php endif; ?>
<th>Sale Amount (Per Unit)</th>
<th>Quantity</th>
<th>Total Amount ($)</th>
<th>Date</th>
</tr>
</thead>
<tbody>
<?php
if (count($sales_rows) > 0) {
$count = 1;
foreach ($sales_rows as $sale) {
echo "<tr>";
echo "<td>{$count}</td>";
if ($hasBuyerName) {
echo "<td>" . htmlspecialchars($sale['buyer_name']) . "</td>";
}
echo "<td>$" . number_format($sale['sale_amount'], 2) . "</td>";
echo "<td>" . (int)$sale['quantity'] . "</td>";
echo "<td>$" . number_format($sale['total_amount'], 2) . "</td>";
// Use a data attribute to pass raw UTC date to JS
echo "<td class='sale-date' data-date='" . htmlspecialchars($sale['sale_date']) . "'></td>";
echo "</tr>";
$count++;
}
} else {
$colspan = $hasBuyerName ? 6 : 5;
echo "<tr><td colspan='{$colspan}' class='text-center text-muted'>😕 No sales recorded yet.</td></tr>";
}
?>
</tbody>
</table>
</div>
</div>
</div>
<script>
// Convert all sale dates into the user's local timezone
document.querySelectorAll(".sale-date").forEach(cell => {
const utcDate = cell.dataset.date;
if (utcDate) {
const localDate = new Date(utcDate + " UTC").toLocaleString([], {
year: "numeric",
month: "short",
day: "numeric",
hour: "2-digit",
minute: "2-digit"
});
cell.textContent = localDate;
}
});
</script>
<?php include "footer.php"; ?>