如何为单按钮实习考勤系统添加下午签到签退功能

我开发了一套实习考勤管理系统，仅通过一个按钮实现考勤操作，现有代码仅支持上午（8点签到、12点签退）的考勤功能。现在需要扩展午间休息后的下午（1点签到、5点签退）功能，通过在TextBox1中输入PracticumID完成考勤，请问该如何实现？

现有代码 #

Dim query2 As String = "UPDATE attendance att, practicum prac SET att.LastName = prac.LastName, att.FirstName = prac.FirstName WHERE att.PracticumID = prac.PracticumID"

Try
    If TextBox1.Text = "" Then
        MessageBox.Show("retry")
    Else
            reloadtxt("SELECT * FROM practicum WHERE PracticumID = '" & TextBox1.Text & "'")
        If dt.Rows.Count > 0 Then
                reloadtxt("SELECT * FROM attendance WHERE PracticumID = '" & TextBox1.Text & "' AND DATE = '" & logdate _
                & "' AND AM_Status = 'OUT'")
            If dt.Rows.Count > 0 Then
                    MessageBox.Show("you've had enough for this day", "attention", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Else
                    reloadtxt("SELECT * FROM attendance WHERE PracticumID = '" & TextBox1.Text & "' AND AM_Status = 'IN'")
                    If dt.Rows.Count > 0 Then
                        Dim amout As String = "UPDATE attendance SET TimeLogOut_AM = '" & timeout_am & "', AM_Status = 'Out' WHERE PracticumID = '" & TextBox1.Text & "'"
                        Dim QueryString2 As String = String.Concat(amout, ";", query2)
                        updates(QueryString2)
                        load_PracticumAttendance()
                        MessageBox.Show("AM TimeOut")
                    Else
                        Dim amin As String = "INSERT INTO attendance (PracticumID, Date, TimeLogIn_AM, AM_Status) " _
                                      & " VALUES ('" & TextBox1.Text & "', '" & logdate & "', '" & timein_am & "', 'IN')"
                        Dim QueryString As String = String.Concat(amin, ";", query2)
                        create(QueryString)
                        load_PracticumAttendance()
                        MessageBox.Show("AM TimeIn")
                    End If
                End If
            Else
            End If
            End If

实现步骤 #

1. 数据库表结构调整 #

首先在attendance表中添加3个下午考勤相关字段：

• TimeLogIn_PM：存储下午签到时间
• TimeLogOut_PM：存储下午签退时间
• PM_Status：标记下午考勤状态（可选值IN/OUT）

2. 修改考勤逻辑 #

在原按钮点击事件代码中，扩展下午考勤的判断流程，核心是先验证上午是否已完成签退，再执行下午的签到/签退操作，同时完善异常处理和提示信息：

修改后的完整代码：

Dim query2 As String = "UPDATE attendance att, practicum prac SET att.LastName = prac.LastName, att.FirstName = prac.FirstName WHERE att.PracticumID = prac.PracticumID"

Try
    If TextBox1.Text = "" Then
        MessageBox.Show("请输入实习ID")
        Return
    End If

    ' 验证实习ID有效性
    reloadtxt("SELECT * FROM practicum WHERE PracticumID = '" & TextBox1.Text & "'")
    If dt.Rows.Count = 0 Then
        MessageBox.Show("无效的实习ID，请重新输入")
        Return
    End If

    ' 检查当天是否已完成全天考勤
    reloadtxt("SELECT * FROM attendance WHERE PracticumID = '" & TextBox1.Text & "' AND DATE = '" & logdate & "' AND AM_Status = 'OUT' AND PM_Status = 'OUT'")
    If dt.Rows.Count > 0 Then
        MessageBox.Show("今日考勤已完成", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Return
    End If

    ' 判断是否进入下午考勤流程（上午已签退）
    reloadtxt("SELECT * FROM attendance WHERE PracticumID = '" & TextBox1.Text & "' AND DATE = '" & logdate & "' AND AM_Status = 'OUT'")
    If dt.Rows.Count > 0 Then
        ' 下午考勤逻辑
        reloadtxt("SELECT * FROM attendance WHERE PracticumID = '" & TextBox1.Text & "' AND DATE = '" & logdate & "' AND PM_Status = 'IN'")
        If dt.Rows.Count > 0 Then
            ' 执行下午签退
            Dim pmout As String = "UPDATE attendance SET TimeLogOut_PM = '" & timeout_pm & "', PM_Status = 'OUT' WHERE PracticumID = '" & TextBox1.Text & "' AND DATE = '" & logdate & "'"
            Dim QueryString3 As String = String.Concat(pmout, ";", query2)
            updates(QueryString3)
            load_PracticumAttendance()
            MessageBox.Show("下午签退成功")
        Else
            ' 执行下午签到
            Dim pmin As String = "UPDATE attendance SET TimeLogIn_PM = '" & timein_pm & "', PM_Status = 'IN' WHERE PracticumID = '" & TextBox1.Text & "' AND DATE = '" & logdate & "'"
            Dim QueryString4 As String = String.Concat(pmin, ";", query2)
            updates(QueryString4)
            load_PracticumAttendance()
            MessageBox.Show("下午签到成功")
        End If
    Else
        ' 原有上午考勤逻辑
        reloadtxt("SELECT * FROM attendance WHERE PracticumID = '" & TextBox1.Text & "' AND DATE = '" & logdate & "' AND AM_Status = 'IN'")
        If dt.Rows.Count > 0 Then
            ' 上午签退
            Dim amout As String = "UPDATE attendance SET TimeLogOut_AM = '" & timeout_am & "', AM_Status = 'OUT' WHERE PracticumID = '" & TextBox1.Text & "' AND DATE = '" & logdate & "'"
            Dim QueryString2 As String = String.Concat(amout, ";", query2)
            updates(QueryString2)
            load_PracticumAttendance()
            MessageBox.Show("上午签退成功")
        Else
            ' 上午签到，同时初始化下午考勤状态为未签到
            Dim amin As String = "INSERT INTO attendance (PracticumID, Date, TimeLogIn_AM, AM_Status, PM_Status) " _
                                  & " VALUES ('" & TextBox1.Text & "', '" & logdate & "', '" & timein_am & "', 'IN', 'OUT')"
            Dim QueryString As String = String.Concat(amin, ";", query2)
            create(QueryString)
            load_PracticumAttendance()
            MessageBox.Show("上午签到成功")
        End If
    End If
Catch ex As Exception
    MessageBox.Show("考勤操作失败：" & ex.Message)
End Try

3. 关键补充说明 #

• 确保timein_pm（下午签到时间）和timeout_pm（下午签退时间）变量已正确赋值，格式与timein_am、timeout_am保持一致
• 原代码存在SQL注入风险，建议替换为参数化查询，示例：

  ' 参数化查询示例（下午签到）
  Dim pmin As String = "UPDATE attendance SET TimeLogIn_PM = @TimeInPM, PM_Status = @PMStatus WHERE PracticumID = @PracticumID AND DATE = @LogDate"
  ' 后续通过SqlCommand添加参数，避免注入漏洞
  

• 新增的PM_Status字段可设置默认值为OUT，确保新插入的考勤记录初始状态正确

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