如何在销售表与销售明细表中添加采购项目总成本（销售总额）

Hey Kim, let's work through your two requirements step by step—adding a total procurement cost row and a row for the total sales of users who purchased from a specific supplier to your report table. I'll base this on common e-commerce table structures (adjust field names to match your actual database schema):

First, we need to pull core sales data, calculate the total procurement cost, and get the targeted supplier user sales total. Let's assume your tables have these fields:

• sales: sale_id, user_id, supplier_id, sale_date
• sales_detail: sale_id, product_id, quantity, unit_purchase_cost (your cost from the supplier), unit_sale_price (price sold to the user)

Example PDO Queries #

// Database connection (adjust credentials as needed)
$pdo = new PDO('mysql:host=localhost;dbname=your_db', 'username', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 1. Fetch core sales detail data
$coreQuery = "
    SELECT 
        s.user_id,
        sd.product_id,
        sd.quantity,
        sd.unit_purchase_cost,
        sd.unit_sale_price,
        (sd.quantity * sd.unit_purchase_cost) AS item_procurement_cost,
        (sd.quantity * sd.unit_sale_price) AS item_sales_amount
    FROM sales s
    JOIN sales_detail sd ON s.sale_id = sd.sale_id
";
$coreStmt = $pdo->prepare($coreQuery);
$coreStmt->execute();
$salesData = $coreStmt->fetchAll(PDO::FETCH_ASSOC);

// 2. Calculate total procurement cost (sum of all item procurement costs)
$totalProcCostQuery = "
    SELECT SUM(sd.quantity * sd.unit_purchase_cost) AS total_procurement_cost
    FROM sales s
    JOIN sales_detail sd ON s.sale_id = sd.sale_id
";
$procStmt = $pdo->prepare($totalProcCostQuery);
$procStmt->execute();
$totalProcCost = $procStmt->fetchColumn();

// 3. Calculate total sales for users who purchased from a specific supplier (replace 123 with your target supplier ID)
$targetSupplierId = 123;
$supplierUserSalesQuery = "
    SELECT SUM(sd.quantity * sd.unit_sale_price) AS supplier_user_total_sales
    FROM sales s
    JOIN sales_detail sd ON s.sale_id = sd.sale_id
    WHERE s.user_id IN (
        SELECT DISTINCT user_id 
        FROM sales 
        WHERE supplier_id = :supplier_id
    )
";
$supplierStmt = $pdo->prepare($supplierUserSalesQuery);
$supplierStmt->bindParam(':supplier_id', $targetSupplierId, PDO::PARAM_INT);
$supplierStmt->execute();
$supplierUserTotalSales = $supplierStmt->fetchColumn();

Integrate these values into your existing report table. Here's how to modify your code snippet to include the new rows:

<div class="row">
    <div class="col-lg-12">
        <center>
            <h1 class="page-header">Product Sales Report</h1>
            <table class="table table-striped table-bordered">
                <thead>
                    <tr>
                        <th>User ID</th>
                        <th>Product ID</th>
                        <th>Quantity</th>
                        <th>Unit Procurement Cost</th>
                        <th>Unit Sale Price</th>
                        <th>Item Procurement Cost</th>
                        <th>Item Sales Amount</th>
                    </tr>
                </thead>
                <tbody>
                    <!-- Render core sales data -->
                    <?php foreach ($salesData as $row): ?>
                    <tr>
                        <td><?= htmlspecialchars($row['user_id']) ?></td>
                        <td><?= htmlspecialchars($row['product_id']) ?></td>
                        <td><?= htmlspecialchars($row['quantity']) ?></td>
                        <td><?= htmlspecialchars(number_format($row['unit_purchase_cost'], 2)) ?></td>
                        <td><?= htmlspecialchars(number_format($row['unit_sale_price'], 2)) ?></td>
                        <td><?= htmlspecialchars(number_format($row['item_procurement_cost'], 2)) ?></td>
                        <td><?= htmlspecialchars(number_format($row['item_sales_amount'], 2)) ?></td>
                    </tr>
                    <?php endforeach; ?>

                    <!-- Total Procurement Cost Row -->
                    <tr class="table-info">
                        <td colspan="5"><strong>Total Procurement Cost</strong></td>
                        <td><strong><?= htmlspecialchars(number_format($totalProcCost, 2)) ?></strong></td>
                        <td></td>
                    </tr>

                    <!-- Specific Supplier User Sales Total Row -->
                    <tr class="table-warning">
                        <td colspan="5"><strong>Total Sales for Users of Supplier #<?= $targetSupplierId ?></strong></td>
                        <td></td>
                        <td><strong><?= htmlspecialchars(number_format($supplierUserTotalSales, 2)) ?></strong></td>
                    </tr>
                </tbody>
            </table>
        </center>
    </div>
</div>

• Replace table field names (like user_id, unit_purchase_cost) with your actual database column names.
• Update $targetSupplierId to match the specific supplier you're targeting.
• If you use MySQLi instead of PDO, adjust the query execution logic to fit MySQLi's syntax.
• Tweak styling classes (like Bootstrap's table classes) to match your existing page design.

内容的提问来源于stack exchange，提问作者Kim