Viewing File: /home/quiczmwg/lightspringdigitals.com/dashboard-20260114051212/pages/view_sales.php

<?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"; ?>