如何实现同角色周内更新数据、下周新增行的日报表功能

1. 先给表添加唯一索引（关键前提） #

要实现同一ROLE同一周只存在一行数据，首先需要给bocc_daily_phone表添加一个唯一复合索引，基于role和周标识（用来区分不同周，避免跨年周数重复的问题）：

ALTER TABLE bocc_daily_phone ADD UNIQUE INDEX idx_role_week (role, YEARWEEK(report_date));

2. 修改PHP代码：用INSERT ... ON DUPLICATE KEY UPDATE实现更新/新增二合一 #

原来的INSERT语句只会新增行，现在改用MySQL的INSERT ... ON DUPLICATE KEY UPDATE语法——当检测到唯一索引冲突时（即该角色当周已有记录），就更新对应工作日的数值；如果没有冲突，就自动取上周该角色的总计值作为prev_week，新增当周记录。

同时必须修复SQL注入风险，改用预处理语句：

<?php
if(isset($_POST['addphone'])){
    $date = $_POST['date'];
    $role = $_POST['role'];
    $mon = $_POST['monday'];
    $tue = $_POST['tuesday'];
    $wed = $_POST['wednesday'];
    $thur = $_POST['thursday'];
    $fri = $_POST['friday'];
    $total = $mon + $tue + $wed + $thur + $fri;

    // 计算当前日期所属的周（用于匹配唯一索引）
    $current_week = date('YW', strtotime($date));
    // 计算上周的周标识，用于获取上周总计
    $last_week = date('YW', strtotime($date . ' -1 week'));

    include '../../db_config/connection.php';

    // 先查询上周该角色的总计值，作为prev_week
    $prev_total = 0;
    $stmt_prev = $conn->prepare("SELECT total FROM bocc_daily_phone WHERE role = ? AND YEARWEEK(report_date) = ?");
    $stmt_prev->bind_param("ss", $role, $last_week);
    $stmt_prev->execute();
    $result_prev = $stmt_prev->get_result();
    if($row_prev = $result_prev->fetch_assoc()){
        $prev_total = $row_prev['total'];
    }
    $stmt_prev->close();

    // 核心逻辑：新增或更新当周数据
    $sql = "INSERT INTO bocc_daily_phone (report_date, role, prev_week, monday, tuesday, wednesday, thursday, friday, total)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE
                monday = VALUES(monday),
                tuesday = VALUES(tuesday),
                wednesday = VALUES(wednesday),
                thursday = VALUES(thursday),
                friday = VALUES(friday),
                total = VALUES(total)";

    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ssiiiiiii", $date, $role, $prev_total, $mon, $tue, $wed, $thur, $fri, $total);

    if ($stmt->execute()) {
        // 区分新增和更新的提示
        $message = $stmt->affected_rows == 1 ? "$role 本周报表已新增" : "$role 本周报表已更新";
        header("location:../bocc_automation.php?message=$message");
    } else {
        $error = $conn->error;
        header("location:../new_report_phone.php?err=$error");
    }

    $stmt->close();
    $conn->close();
}
?>

代码说明： #

• 唯一索引触发逻辑：当role+YEARWEEK(report_date)组合已存在时，自动执行UPDATE，覆盖当周的工作日数据和总计；不存在则执行INSERT，自动带入上周总计作为prev_week。
• SQL注入防护：全程使用预处理语句，避免直接拼接用户输入带来的安全漏洞。
• 友好提示：根据受影响行数区分新增/更新操作，给用户更清晰的反馈。

3. 日期筛选功能实现思路 #

要查看历史报表，你可以在报表展示页添加日期范围选择器（或周选择器），通过以下两种方式实现筛选：

方式1：按日期范围筛选 #

// 假设前端传入start_date和end_date，默认展示近1个月数据
$start_date = $_GET['start_date'] ?? date('Y-m-d', strtotime('-1 month'));
$end_date = $_GET['end_date'] ?? date('Y-m-d');

$stmt = $conn->prepare("SELECT * FROM bocc_daily_phone WHERE report_date BETWEEN ? AND ? ORDER BY report_date DESC, role");
$stmt->bind_param("ss", $start_date, $end_date);
$stmt->execute();
$result = $stmt->get_result();
// 遍历$result渲染报表列表

方式2：按周筛选（更贴合你的周报场景） #

// 假设前端传入选中的周（格式如202401，代表2024年第1周），默认展示当前周
$selected_week = $_GET['week'] ?? date('YW');

$stmt = $conn->prepare("SELECT * FROM bocc_daily_phone WHERE YEARWEEK(report_date) = ? ORDER BY role");
$stmt->bind_param("s", $selected_week);
$stmt->execute();
$result = $stmt->get_result();
// 遍历$result渲染对应周的报表

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