重构考勤应用数据库查询 解决跨天打卡当日工时计算报错问题

问题根因 #

当前代码的崩溃核心来自两个逻辑缺陷：

1. 仅拉取当日0点后的打卡记录，如果用户前一天有未签退的上班卡（eventtype=0），当日的查询结果第一条会是签退记录（eventtype=1），奇偶索引两两配对的逻辑完全错位，甚至出现pair[1]为空的报错
2. 硬按记录顺序两两配对的逻辑不鲁棒，只要出现打卡记录缺漏、跨天未签退的情况，配对关系就会完全错乱

修复步骤 #

1. 调整SQL查询逻辑，保留必要数据同时避免SQL注入 #

• 不要直接拼接SQL参数，改用参数化查询避免注入风险
• 先查询用户最新的签退记录时间，将查询起始时间设置为该签退时间（没有历史签退记录则不限制起始时间），确保能拉到当前未结束的上班卡，同时不会拉太多冗余数据
• 保留按时间倒序排序的逻辑

2. 替换硬配对逻辑，按打卡类型自动配对 #

遍历所有拉取到的打卡记录，将上班卡（eventtype=0）和后续最近的签退卡（eventtype=1）绑定为合法打卡对，过滤无效的单独记录。

3. 跨天打卡时长拆分计算 #

计算单组打卡对的时长时，仅统计落在当日0点到当前时间范围内的部分，满足计算当日工作时长的需求。

修复后代码示例 #

// 先查询用户最新的签退时间
db.query('SELECT FIRST 1 DATETIME FROM EVENTS WHERE ID = ? AND EVENTTYPE = 1 ORDER BY DATETIME DESC', [req.params.id], function(err, lastSignOutRows) {
    let startTimeFilter = ''
    let queryParams = [req.params.id]
    if (lastSignOutRows.length > 0) {
        startTimeFilter = 'AND DATETIME >= ?'
        queryParams.push(lastSignOutRows[0].DATETIME)
    }
    // 拉取全量有效打卡记录，参数化查询避免注入
    db.query(`SELECT FIRST 10 * FROM EVENTS WHERE ID = ? ${startTimeFilter} ORDER BY DATETIME DESC`, queryParams, function(err, allRows) {
        const todayStart = moment().startOf('day')
        const now = moment()
        let totalMinutes = 0
        let pendingSignOutTime = null
        // 倒序遍历匹配上下班记录
        allRows.forEach(row => {
            const rowTime = moment(row.DATETIME)
            if (row.EVENTTYPE == 1) {
                // 遇到签退记录，记录待匹配的下班时间
                pendingSignOutTime = rowTime
            } else if (pendingSignOutTime && row.EVENTTYPE == 0) {
                // 匹配到对应的上班卡，仅统计当日有效时长
                const workStart = moment.max(rowTime, todayStart)
                const workEnd = moment.min(pendingSignOutTime, now)
                if (workEnd.isAfter(workStart)) {
                    totalMinutes += Math.round(moment.duration(workEnd.diff(workStart)).asMinutes())
                }
                pendingSignOutTime = null
            }
        })
        // 处理当前仍处于上班状态的情况
        if (allRows.length > 0 && allRows[0].EVENTTYPE == 0) {
            const workStart = moment.max(moment(allRows[0].DATETIME), todayStart)
            totalMinutes += Math.round(moment.duration(now.diff(workStart)).asMinutes())
        }
    })
})

以上修改适配跨天未签退场景：如果用户9月14号打了上班卡，9月16号才签退，计算9月16号当日时长时，会自动匹配到14号的上班卡，仅统计16号0点到16号20:02的时长，不会出现配对错位崩溃的问题。

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