按日期分组汇总金额的发票创建与数据处理技术问询

核心思路 #

通过PHP分组处理查询结果，先将同一日期的发票明细归类，累加计算当日总金额后，在页面中先渲染明细行，再插入当日汇总行。同时可复用汇总逻辑完成invoice表的总金额插入。

1. 修改数据查询与分组处理 #

替换原有的PHP查询代码，将查询结果按日期分组并计算汇总值：

include('../connect.php');
$inv = $_GET['inv'];
$result4 = $db->prepare("SELECT * FROM invoice_item where invoice_id= :a ORDER BY date");
$result4->bindParam(':a', $inv);
$result4->execute();

// 按日期分组整理数据，同时计算当日汇总
$groupedItems = [];
while ($row4 = $result4->fetch()) {
    $date = $row4['date'];
    if (!isset($groupedItems[$date])) {
        $groupedItems[$date] = [
            'items' => [],
            'total_gross' => 0,
            'total_minimum' => 0,
            'total_net' => 0
        ];
    }
    $groupedItems[$date]['items'][] = $row4;
    // 累加金额（转成浮点避免字符串拼接错误）
    $groupedItems[$date]['total_gross'] += floatval($row4['amount']);
    $groupedItems[$date]['total_minimum'] += floatval($row4['miniamount']);
    $groupedItems[$date]['total_net'] += floatval($row4['netamount']);
}

2. 渲染分组明细与汇总行 #

遍历分组后的数据，先输出当日所有明细，再输出汇总行：

<tbody>
<?php foreach ($groupedItems as $date => $group): ?>
    <!-- 输出当日明细行 -->
    <?php foreach ($group['items'] as $item): ?>
    <tr>
        <td class="tm_width_3"><?php echo $item['date']; ?></td>
        <td class="tm_width_2"><?php echo $item['radiation_source']; ?></td>
        <td class="tm_width_4"><?php echo $item['description']; ?></td>
        <td class="tm_width_1"><?php echo $item['unit']; ?></td>
        <td class="tm_width_2"><?php echo $item['unit_price']; ?></td>
        <td class="tm_width_2"><?php echo $item['qty']; ?></td>
        <td class="tm_width_2"><?php echo $item['amount']; ?></td>
        <td class="tm_width_2"><?php echo $item['miniamount']; ?></td>
        <td class="tm_width_2 tm_text_right"><?php echo $item['netamount']; ?></td>
    </tr>
    <?php endforeach; ?>

    <!-- 输出当日汇总行 -->
    <tr style="background:#f5f5f5; border-top:2px solid #ccc;">
        <td colspan="6" class="tm_semi_bold tm_text_right">当日汇总</td>
        <td class="tm_semi_bold"><?php echo number_format($group['total_gross'], 2); ?></td>
        <td class="tm_semi_bold"><?php echo number_format($group['total_minimum'], 2); ?></td>
        <td class="tm_semi_bold tm_text_right"><?php echo number_format($group['total_net'], 2); ?></td>
    </tr>
<?php endforeach; ?>
</tbody>

3. 插入invoice表时的总金额计算 #

如果需要将全发票的总金额插入invoice表，可执行以下SQL获取汇总值：

SELECT 
    SUM(amount) AS total_gross,
    SUM(miniamount) AS total_minimum,
    SUM(netamount) AS total_net
FROM invoice_item 
WHERE invoice_id = :inv_id

将查询结果对应插入invoice表的总金额字段即可。

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