C#winform上下班打卡系统Demo

C# winform上下班打卡系统Demo

系统效果如图所示

7个label控件(lblUsername、lblLoggedInEmployeeId、lab_IP、lblCheckOutTime、lblCheckInTime、lab_starttime、lab_endtime)、3个按钮、1个dataGridView控件、2个groupBox控件

C#代码实现

using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Net;
using System.Net.Sockets;
using System.Runtime.InteropServices;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
    public partial class 员工打卡 : Form
    {
        private string loggedInUsername;
        private string loggedInEmployeeId;
        private string connectionString = "server=127.0.0.1;uid=sa;pwd=xyz@0123456;database=test";

        public 员工打卡(string username, string employeeId)
        {
            InitializeComponent();
            loggedInUsername = username;
            loggedInEmployeeId = employeeId;
            CheckTodaysPunchInRecord();
        }
        [DllImport("user32.dll")]
        public static extern IntPtr GetSystemMenu(IntPtr hWnd, bool bRevert);

        [DllImport("user32.dll")]
        public static extern bool EnableMenuItem(IntPtr hMenu, uint uIDEnableItem, uint uEnable);

        // 禁用窗口大小改变
        private const uint SC_SIZE = 0xF000;
        private const uint MF_BYCOMMAND = 0x0000;
        private const uint MF_GRAYED = 0x0001;

        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);
            IntPtr hMenu = GetSystemMenu(this.Handle, false);
            if (hMenu != IntPtr.Zero)
            {
                EnableMenuItem(hMenu, SC_SIZE, MF_BYCOMMAND | MF_GRAYED);
            }
        }
        private void 员工打卡_Load(object sender, EventArgs e)
        {

            lblUsername.Text = "当前登录用户：" + loggedInUsername;
            lblLoggedInEmployeeId.Text = "工号：" + loggedInEmployeeId.ToString();
            // 设置日期控件的显示格式为年-月-日
            startTime.Format = DateTimePickerFormat.Custom;
            startTime.CustomFormat = "yyyy-MM-dd";
            // 设置日期控件的显示格式为年-月-日
            endTime.Format = DateTimePickerFormat.Custom;
            endTime.CustomFormat = "yyyy-MM-dd";
            //不显示出dataGridView1的最后一行空白
            dataGridView1_Result.AllowUserToAddRows = false;
            // 设置数据和列名居中对齐
            dataGridView1_Result.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dataGridView1_Result.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            // 设置列名加粗
            dataGridView1_Result.ColumnHeadersDefaultCellStyle.Font = new System.Drawing.Font(dataGridView1_Result.ColumnHeadersDefaultCellStyle.Font, FontStyle.Bold);
            // 设置列宽自适应
            dataGridView1_Result.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            LoadData();
            GetIP();

        }
        private void GetIP()
        {
            // 获取本机IP地址
            IPHostEntry ipHost = Dns.GetHostEntry(Dns.GetHostName());
            foreach (IPAddress ip in ipHost.AddressList)
            {
                if (ip.AddressFamily == AddressFamily.InterNetwork)
                {
                    lab_IP.Text = "IP地址：" + ip.ToString(); // 添加到label1的Text属性中
                }
            }
        }
        private void btnCheckIn_Click(object sender, EventArgs e)
        {
            if (IsPunchInRecordExists())
            {
                MessageBox.Show("你已打过上班卡。", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk);
                return;
            }

            DateTime currentTime = DateTime.Now;
            string punchInTime = currentTime.ToString("yyyy-MM-dd HH:mm:ss");
            string status = currentTime.TimeOfDay < new TimeSpan(8, 30, 0) ? "正常" : "迟到";

            InsertPunchInRecord(punchInTime, status);
            lblCheckInTime.Text = punchInTime;
            lblCheckInTime.Visible = true;
            // 刷新DataGridView显示最新打卡记录
            RefreshDataGridView();
        }

        private bool IsPunchInRecordExists()
        {
            DateTime currentDate = DateTime.Now.Date;
            string query = $"SELECT COUNT(*) FROM PunchIn WHERE emp_code='{loggedInEmployeeId}' AND punch_in_time >= '{currentDate}'";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    int count = (int)command.ExecuteScalar();
                    return count > 0;
                }
            }
        }

        private void RefreshDataGridView()
        {
            // 执行查询语句
           string query = $@"
                    SELECT 
                        a.id,
                        a1.username AS 用户名,
                        a.emp_code AS 工号,
                        CONVERT(VARCHAR(19), a.punch_in_time, 120) AS 上班打卡时间,
                        a.status AS 上班打卡状态,
                        CONVERT(VARCHAR(19), b.punch_out_time, 120) AS 下班打卡时间,
                        b.status AS 下班打卡状态
                    FROM 
                        (SELECT * FROM Employee) a1
                    LEFT JOIN 
                        PunchIn a ON a1.emp_code = a.emp_code
                    LEFT JOIN 
                        PunchOut b ON a.emp_code = b.emp_code 
                                 AND CONVERT(DATE, a.punch_in_time) = CONVERT(DATE, b.punch_out_time)
                                 AND b.punch_out_time = (
                                     SELECT MAX(punch_out_time)
                                     FROM PunchOut
                                     WHERE emp_code = a.emp_code 
                                       AND CONVERT(DATE, punch_out_time) = CONVERT(DATE, a.punch_in_time)
                                 )
                    WHERE 
                        a.emp_code = '{loggedInEmployeeId}' AND MONTH(a.punch_in_time) = MONTH(GETDATE()) AND YEAR(a.punch_in_time) = YEAR(GETDATE())
                    ORDER BY 
                        a.id, a.emp_code, a.punch_in_time";
            Console.WriteLine("执行的SQL语句是:" + query);
            // 执行查询并获取结果
            // 你可以使用适合你数据库的查询方法
            DataTable dataTable = ExecuteQuery(query);

            // 将查询结果绑定到DataGridView的数据源
            dataGridView1_Result.DataSource = dataTable;
        }

        private DataTable ExecuteQuery(string query)
        {
            // 创建连接和命令对象并执行查询
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    // 创建适配器并填充数据到DataTable
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;
                }
            }
        }

        private void InsertPunchInRecord(string punchInTime, string status)
        {
            string query = $"INSERT INTO PunchIn (emp_code, punch_in_time, status) VALUES ('{loggedInEmployeeId}', '{punchInTime}', '{status}')";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }
        private void CheckTodaysPunchInRecord()
        {
            DateTime currentDate = DateTime.Now.Date;
            string query = $"SELECT punch_in_time FROM PunchIn WHERE emp_code='{loggedInEmployeeId}' AND punch_in_time >= '{currentDate}'";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    object result = command.ExecuteScalar();
                    if (result != null)
                    {
                        string punchInTime = ((DateTime)result).ToString("yyyy-MM-dd HH:mm:ss");
                        lblCheckInTime.Text = punchInTime;
                        lblCheckInTime.Visible = true;
                    }
                }
            }
        }
        private void btnCheckOut_Click(object sender, EventArgs e)
        {
            DateTime currentTime = DateTime.Now;
            string punchOutTime = currentTime.ToString("yyyy-MM-dd HH:mm:ss");

            if (IsInvalidPunchOutTime(currentTime))
            {
                MessageBox.Show("21点30到23:59:59点打下班卡无效。");
                return;
            }

            string status = currentTime.TimeOfDay < new TimeSpan(18, 0, 0) ? "早退" : "正常"; // 判断下班打卡时间是否在18:00之前

            InsertPunchOutRecord(punchOutTime, status);
            lblCheckOutTime.Text = punchOutTime;
            lblCheckOutTime.Visible = true;
            // 刷新DataGridView显示最新打卡记录
            RefreshDataGridView();
        }

        private bool IsInvalidPunchOutTime(DateTime currentTime)
        {
            TimeSpan startTime = new TimeSpan(21, 30, 0);
            TimeSpan endTime = new TimeSpan(23, 59, 59);
            TimeSpan currentTimeOfDay = currentTime.TimeOfDay;

            return currentTimeOfDay >= startTime && currentTimeOfDay <= endTime;
        }

        private void InsertPunchOutRecord(string punchOutTime, string status)
        {
            string query = $"INSERT INTO PunchOut (emp_code, punch_out_time, status) VALUES ('{loggedInEmployeeId}', '{punchOutTime}', '{status}')";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        private void btn_Serch_Click(object sender, EventArgs e)
        {
            // 获取所选时间范围
            DateTime startDate = startTime.Value.Date;
            DateTime endDate = endTime.Value.Date.AddDays(1).AddSeconds(-1);
            // 构建 SQL 查询语句
            string query = $@"
                    SELECT 
                        a.id,
                        a1.username AS 用户名,
                        a.emp_code AS 工号,
                        CONVERT(VARCHAR(19), a.punch_in_time, 120) AS 上班打卡时间,
                        a.status AS 上班打卡状态,
                        CONVERT(VARCHAR(19), b.punch_out_time, 120) AS 下班打卡时间,
                        b.status AS 下班打卡状态
                    FROM 
                        (SELECT * FROM Employee) a1
                    LEFT JOIN 
                        PunchIn a ON a1.emp_code = a.emp_code
                    LEFT JOIN 
                        PunchOut b ON a.emp_code = b.emp_code 
                                 AND CONVERT(DATE, a.punch_in_time) = CONVERT(DATE, b.punch_out_time)
                                 AND b.punch_out_time = (
                                     SELECT MAX(punch_out_time)
                                     FROM PunchOut
                                     WHERE emp_code = a.emp_code 
                                       AND CONVERT(DATE, punch_out_time) = CONVERT(DATE, a.punch_in_time)
                                 )
                    WHERE 
                        a.punch_in_time BETWEEN @StartDate AND @EndDate
                        AND a.emp_code = '{loggedInEmployeeId}'
                    ORDER BY 
                        a.id, a.emp_code, a.punch_in_time";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    // 添加查询参数
                    command.Parameters.AddWithValue("@StartDate", startDate);
                    command.Parameters.AddWithValue("@EndDate", endDate);
                    Console.WriteLine("查询的SQL语句：" + query);
                    // 打开数据库连接
                    connection.Open();

                    // 创建数据适配器和数据表
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    DataTable table = new DataTable();

                    // 填充数据表
                    adapter.Fill(table);

                    // 关闭数据库连接
                    connection.Close();

                    // 绑定数据表到 DataGridView 控件
                    dataGridView1_Result.DataSource = table;
                }
            }
        }

        private void LoadData()
        {
            // 获取所选时间范围
            DateTime startDate = startTime.Value.Date;
            DateTime endDate = endTime.Value.Date.AddDays(1).AddSeconds(-1);
            string query = $@"
                    SELECT 
                        a.id,
                        a1.username AS 用户名,
                        a.emp_code AS 工号,
                        CONVERT(VARCHAR(19), a.punch_in_time, 120) AS 上班打卡时间,
                        a.status AS 上班打卡状态,
                        CONVERT(VARCHAR(19), b.punch_out_time, 120) AS 下班打卡时间,
                        b.status AS 下班打卡状态
                    FROM 
                        (SELECT * FROM Employee) a1
                    LEFT JOIN 
                        PunchIn a ON a1.emp_code = a.emp_code
                    LEFT JOIN 
                        PunchOut b ON a.emp_code = b.emp_code 
                                 AND CONVERT(DATE, a.punch_in_time) = CONVERT(DATE, b.punch_out_time)
                                 AND b.punch_out_time = (
                                     SELECT MAX(punch_out_time)
                                     FROM PunchOut
                                     WHERE emp_code = a.emp_code 
                                       AND CONVERT(DATE, punch_out_time) = CONVERT(DATE, a.punch_in_time)
                                 )
                    WHERE 
                        a.punch_in_time BETWEEN @StartDate AND @EndDate
                        AND a.emp_code = '{loggedInEmployeeId}'
                    ORDER BY 
                        a.id, a.emp_code, a.punch_in_time";
            Console.WriteLine("开始时间：" + startDate);
            Console.WriteLine("结束时间：" + endDate);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    // 添加查询参数
                    command.Parameters.AddWithValue("@StartDate", startDate);
                    command.Parameters.AddWithValue("@EndDate", endDate);
                    Console.WriteLine("一加载时获取数据查询的SQL语句：" + query);
                    // 打开数据库连接
                    connection.Open();

                    // 创建数据适配器和数据表
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    DataTable table = new DataTable();

                    // 填充数据表
                    adapter.Fill(table);

                    // 关闭数据库连接
                    connection.Close();

                    // 绑定数据表到 DataGridView 控件
                    dataGridView1_Result.DataSource = table;
                }
            }
        }




    }
}