物联网应用系统设计与维护实训
任务一 考勤系统需求分析
（1）需求概述
项目背景：随着信息技术的高速发展，各行各业都充分利用信息平台提高自己的管理效率。当今国内各公司已纷纷建立公司内部网络，依托公司的内部网络使用各种信息管理系统，使管理效率显著提高。
项目目的：员工的考勤情况直接影响员工是否能参加评选各种奖金、是否完成了应尽的工作，意义十分重大。我公司员工考勤的记录、统计、修改以及考勤清查都由人工完成，需要花费大量时间和精力，过程繁琐，操作极为不便。同时，考勤上报需要一定的时间，管理部门不能及时了解员工的考勤情况。针对上述不足之处，我们现准备开发一套考勤管理系统。应用该系统，将使我司考勤管理系统化、信息化、高效化、准确化，让我们公司的管理更上一层台阶，同时也将对我司的工作风纪建设带来积极的影响。
（2）功能需求
image
（3）非功能需求
需要较强的安全性，防止有人破解肆意篡改。
（4）软件与硬件或其他外部系统接口
image
（5）设计与实现的限制
需要从公司的实际情况出发。
任务二 考勤系统数据库设计
（1）识别实体
员工，打卡机
（2）ER图
image
（3）数据表
image
（4）数据库表
image
image
任务三 考勤系统功能实现
1.登录
实现截图：
image.png
具体描述:
通过登录界面与数据库员工信息表的连接，员工可以输入账号和密码进行登陆。
关键代码：
private void loginButton_Click(object sender, EventArgs e)
{
String username = this.unTextBox.Text.Trim();
String password = this.pwTextBox.Text.Trim();
String connStr = ConfigurationManager.ConnectionStrings["Attendance"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(connStr);
try
{
sqlConn.Open();
String sqlStr = "select * from info where (Name=@name or Number=@name) and Password=@pwd";
SqlCommand sCmd = new SqlCommand(sqlStr, sqlConn);
sCmd.Parameters.Add(new SqlParameter("@name", username));
sCmd.Parameters.Add(new SqlParameter("@pwd", password));
SqlDataReader dr = sCmd.ExecuteReader();
if(dr.HasRows)
{
dr.Read();
UserInfo.number = dr["Number"].ToString();
UserInfo.username = dr["Name"].ToString();
UserInfo.permission = Convert.ToBoolean(dr["Permission"].ToString());
MessageBox.Show("登陆成功，欢迎" + UserInfo.username);
if (UserInfo.permission)
{
AdminForm adminForm = new AdminForm();
adminForm.Show();
this.Hide();
}
else
{
UserForm userForm = new UserForm();
userForm.Show();
this.Hide();
}
}
else
{
MessageBox.Show("用户名或密码错误", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch(Exception exp)
{
MessageBox.Show("数据库访问失败：" + exp.Message);
}
}
2.注册
实现截图：
注册工号.png
具体描述：
在管理员界面上，点击录入员工信息，实现新员工信息在数据库中的录入。
关键代码：
private void recordButton_Click(object sender, EventArgs e)
{
String number = this.numberTextBox.Text.Trim();
String name = this.nameTextBox.Text.Trim();
String pwd = this.pwdTextBox.Text.Trim();
String sex = this.sexComboBox.Text.Trim();
String age = this.ageTextBox.Text.Trim();
String apar = this.aparComboBox.Text.Trim();
String mark = this.markTextBox.Text.Trim();
String per = "";
if ("".Equals(this.perComboBox.Text.Trim()))
{
MessageBox.Show(
"除了备注以外请勿留空！",
"警告",
MessageBoxButtons.OK,
MessageBoxIcon.Warning
);
return;
}
else
{
per = this.perComboBox.Text.Trim() == "管理员" ? "true" : "false";
}
if ("".Equals(number) || "".Equals(name) || "".Equals(pwd) ||
"".Equals(sex) || "".Equals(age) || "".Equals(apar) ||
"".Equals(number))
{
MessageBox.Show(
"除了备注以外请勿留空！",
"警告",
MessageBoxButtons.OK,
MessageBoxIcon.Warning
);
return;
}
String connStr = ConfigurationManager.ConnectionStrings["Attendance"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(connStr);
try
{
sqlConn.Open();
String sqlStr =
"insert into " +
"info(Number, Name, Password, Sex, Age, Apartment, Permission, Mark) " +
"values(@number, @name, @pwd, @sex, @age, @apar, @per, @mark)";
SqlCommand sCmd = new SqlCommand(sqlStr, sqlConn);
sCmd.Parameters.Add(new SqlParameter("@number", number));
sCmd.Parameters.Add(new SqlParameter("@name", name));
sCmd.Parameters.Add(new SqlParameter("@pwd", pwd));
sCmd.Parameters.Add(new SqlParameter("@sex", sex));
sCmd.Parameters.Add(new SqlParameter("@age", age));
sCmd.Parameters.Add(new SqlParameter("@apar", apar));
sCmd.Parameters.Add(new SqlParameter("@per", per));
sCmd.Parameters.Add(new SqlParameter("@mark", mark));
int len = sCmd.ExecuteNonQuery();
MessageBox.Show(len + "条数据录入成功！");
}
catch (Exception exp)
{
MessageBox.Show(
"连接数据库失败：" + exp.Message,
"错误",
MessageBoxButtons.OK,
MessageBoxIcon.Error
);
return;
}
setNumber();
this.numberTextBox.Text = this.nameTextBox.Text = this.pwdTextBox.Text
= this.sexComboBox.Text = this.ageTextBox.Text
= this.aparComboBox.Text = this.markTextBox.Text
= "";
}
3.打卡
实现截图：
打卡.png
具体描述：
读取卡片的信息，并将员工的打卡信息录入到数据库中。
关键代码：
初始化串口及参数
/// <summary>
/// 程序加载时，自动获取串口列表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ServiceForm_Load(object sender, EventArgs e)
{
// 自动获取串口列表
String[] ports = SerialPort.GetPortNames();
if(ports.Length > 0)
{
// 遍历添加
foreach(String port in ports)
{
comboBoxCOMList.Items.Add(port);
}
comboBoxCOMList.SelectedItem = ports[0];
}
}
/// <summary>
/// 打开或关闭串口
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonOpenCOM_Click(object sender, EventArgs e)
{
// 串口已打开，此时需要关闭
if (serialPort1.IsOpen)
{
serialPort1.Close();
this.toolStripStatusLabel1.Text = "已关闭串口" + serialPort1.PortName.ToString();
buttonOpenCOM.Text = "打开";
return;
}
// 否则打开串口
else
{
serialPort1.PortName = comboBoxCOMList.Text;
// 初始化串口参数
serialPort1.DataBits = 8;
serialPort1.BaudRate = 115200;
serialPort1.Parity = Parity.None;
serialPort1.StopBits = StopBits.One;
try
{
serialPort1.Open();
this.toolStripStatusLabel1.Text = "已打开串口" + serialPort1.PortName.ToString();
buttonOpenCOM.Text = "关闭";
}
catch (Exception ex)
{
this.toolStripStatusLabel1.Text = "打开串口失败，原因：" + ex.Message;
return;
}
}
}
读取卡号，写入数据（员工信息）
/// <summary>
/// 写入数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bt_Writer_Click(object sender, EventArgs e)
{
// 检测端口是否开启
if (!serialPort1.IsOpen)
{
MessageBox.Show("串口未打开！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 检测编码长度是否正确
if (tbNumber.Text.Trim().Length != 3)
{
MessageBox.Show("编码长度不正确！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
cards = SearchCards();
// 判断到的卡片数量
if (!(cards.Count > 0))
{
MessageBox.Show("未寻到卡！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string number = "00000" + tbNumber.Text.Trim();
WriteSingleBlock(
ISO15693CardHandler.CovertEndian(cards[0].ID),
"00",
ISO15693CardHandler.CovertEndian(number)
);
}
/// <summary>
/// 读取卡号
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bt_Reader_Click(object sender, EventArgs e)
{
// 检测端口是否开启
if (!serialPort1.IsOpen)
{
MessageBox.Show("串口未打开！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 寻卡
cards = SearchCards();
// 判断到的卡片数量
if (!(cards.Count > 0))
{
MessageBox.Show("未寻到卡！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string number = ReadSingleBlock(
ISO15693CardHandler.CovertEndian(cards[0].ID), "00"
);
// 判断是否读取成功
if (number.Equals("-1"))
{
MessageBox.Show("读取失败！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 记录
recordSQL(number);
}
寻卡、读块、写入到块
private List<ISO15693Card> SearchCards()
{
string response = "";
serialPort1.Write(ISO15693Card.COMMAND_WRITE_REG);
Thread.Sleep(MILLISECOND_IN_SLEEP);
if (serialPort1.BytesToRead > 0) response = serialPort1.ReadExisting();
serialPort1.Write(ISO15693Card.COMMAND_SET_AGC);
Thread.Sleep(MILLISECOND_IN_SLEEP);
if (serialPort1.BytesToRead > 0) response = serialPort1.ReadExisting();
serialPort1.Write(ISO15693Card.COMMAND_SET_RECV_MODE);
Thread.Sleep(MILLISECOND_IN_SLEEP);
if (serialPort1.BytesToRead > 0) response = serialPort1.ReadExisting();
serialPort1.Write(ISO15693Card.COMMAND_INVEN_CARD);// 寻卡
Thread.Sleep(MILLISECOND_IN_SLEEP);
if (serialPort1.BytesToRead > 0) response = serialPort1.ReadExisting();
return ISO15693CardHandler.InventoryCard(response);
}
private string ReadSingleBlock(string UID, string strBlockIndex)
{
//格式化读写命令
string command = string.Format(
ISO15693Card.COMMAND_READ_SINGLE_BLOCK, UID, strBlockIndex
);
serialPort1.Write(command);//写命令
Thread.Sleep(MILLISECOND_IN_SLEEP);// 睡眠200毫秒
if (serialPort1.BytesToRead > 0)
{
string number = ISO15693CardHandler.GenerateBlockData(
serialPort1.ReadExisting()
).Substring(5);
return number;
}
return "-1";
}
private void WriteSingleBlock(string UID, string strBlockIndex, string hexData)
{
//格式化命令
string command = string.Format(ISO15693Card.COMMAND_WRITE_SINGLE_BLOCK, UID, strBlockIndex, hexData);
serialPort1.Write(command);
Thread.Sleep(MILLISECOND_IN_SLEEP);
if (serialPort1.BytesToRead > 0) MessageBox.Show("执行完毕");
}
插入数据库记录
private void recordSQL(string number)
{
// 数据库操作
String connStr = ConfigurationManager.ConnectionStrings["Attendance"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(connStr);
try
{
sqlConn.Open();
String sqlStr =
"insert into " +
"record(Number, CheckDate, CheckTime, MachineNumber) " +
"values(@number, @date, @time, '001')";
SqlCommand sCmd = new SqlCommand(sqlStr, sqlConn);
string date = DateTime.Now.ToShortDateString();
string time = DateTime.Now.ToLongTimeString();
sCmd.Parameters.Add(new SqlParameter("@number", number));
sCmd.Parameters.Add(new SqlParameter("@date", date));
sCmd.Parameters.Add(new SqlParameter("@time", time));
int len = sCmd.ExecuteNonQuery();
MessageBox.Show(len + "条打卡记录成功！");
}
catch (Exception exp)
{
MessageBox.Show(
"连接数据库失败：" + exp.Message,
"错误",
MessageBoxButtons.OK,
MessageBoxIcon.Error
);
return;
}
}
private void timer_Checker_Tick(object sender, EventArgs e)
{
// 检测端口是否开启
if (!serialPort1.IsOpen)
{
bt_OpenTimer.Text = "开启打卡";
timer_Checker.Enabled = false;
return;
}
// 寻卡
timerCards = SearchCards();
// 判断是否寻到卡
if (!(timerCards.Count > 0))
{
lastNumber = "";
return;
}
string number = ReadSingleBlock(
ISO15693CardHandler.CovertEndian(timerCards[0].ID), "00"
);
// 判断是否与上一个卡一致
if (lastNumber.Equals(number))
{
return;
}
lastNumber = number;
// 记录
recordSQL(number);
}
4.查询
实现截图：
打卡信息.png
具体描述：
通过查询打卡界面，可以提取数据库中的打卡信息进行查询。
关键代码：
private void bt_Query_Click(object sender, EventArgs e)
{
string startDate = this.dtp_StartDate.Value.Date.ToShortDateString();
string endDate = this.dtp_EndDate.Value.Date.ToShortDateString();
String sqlStr = @"select Name as 员工姓名, CheckDate as 打卡日期, CheckTime as 打卡时间,
cardmachine.Location as 机器位置, record.Mark as 备注
from record, info, cardmachine
where record.Number=info.Number and record.MachineNumber=cardmachine.Number";
// 查询条件
if (!startDate.Equals(""))
{
sqlStr += " and CheckDate>='" + startDate + "'";
}
if (!endDate.Equals(""))
{
sqlStr += " and CheckDate<='" + endDate + "'";
}
this.dgv_Result.DataSource = SearchData(sqlStr);
MessageBox.Show("查询成功！");
}
5.统计
实现截图：
统计信息.png
具体描述：
先把员工编号写入卡中，每次打卡都会记录一次，并将打卡信息传入数据库中的打卡记录表中。
关键代码：
private void bt_Query_Click(object sender, EventArgs e)
{
string startDate = this.dtp_StartDate.Value.Date.ToShortDateString();
string endDate = this.dtp_EndDate.Value.Date.ToShortDateString();
String sqlStr = @"select Name as 员工姓名, CheckDate as 打卡日期, CheckTime as 打卡时间,
cardmachine.Location as 机器位置, record.Mark as 备注
from record, info, cardmachine
where record.Number=info.Number and record.MachineNumber=cardmachine.Number";
// 查询条件
if (!startDate.Equals(""))
{
sqlStr += " and CheckDate>='" + startDate + "'";
}
if (!endDate.Equals(""))
{
sqlStr += " and CheckDate<='" + endDate + "'";
}
this.dgv_Result.DataSource = SearchData(sqlStr);
MessageBox.Show("查询成功！");
}
任务四 串口编程
实现截图：
打卡.png
关键代码：
初始化串口及参数
/// <summary>
/// 程序加载时，自动获取串口列表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ServiceForm_Load(object sender, EventArgs e)
{
// 自动获取串口列表
String[] ports = SerialPort.GetPortNames();
if(ports.Length > 0)
{
// 遍历添加
foreach(String port in ports)
{
comboBoxCOMList.Items.Add(port);
}
comboBoxCOMList.SelectedItem = ports[0];
}
}
/// <summary>
/// 打开或关闭串口
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonOpenCOM_Click(object sender, EventArgs e)
{
// 串口已打开，此时需要关闭
if (serialPort1.IsOpen)
{
serialPort1.Close();
this.toolStripStatusLabel1.Text = "已关闭串口" + serialPort1.PortName.ToString();
buttonOpenCOM.Text = "打开";
return;
}
// 否则打开串口
else
{
serialPort1.PortName = comboBoxCOMList.Text;
// 初始化串口参数
serialPort1.DataBits = 8;
serialPort1.BaudRate = 115200;
serialPort1.Parity = Parity.None;
serialPort1.StopBits = StopBits.One;
try
{
serialPort1.Open();
this.toolStripStatusLabel1.Text = "已打开串口" + serialPort1.PortName.ToString();
buttonOpenCOM.Text = "关闭";
}
catch (Exception ex)
{
this.toolStripStatusLabel1.Text = "打开串口失败，原因：" + ex.Message;
return;
}
}
}
读取卡号，写入数据（员工信息）
/// <summary>
/// 写入数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bt_Writer_Click(object sender, EventArgs e)
{
// 检测端口是否开启
if (!serialPort1.IsOpen)
{
MessageBox.Show("串口未打开！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 检测编码长度是否正确
if (tbNumber.Text.Trim().Length != 3)
{
MessageBox.Show("编码长度不正确！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
cards = SearchCards();
// 判断到的卡片数量
if (!(cards.Count > 0))
{
MessageBox.Show("未寻到卡！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string number = "00000" + tbNumber.Text.Trim();
WriteSingleBlock(
ISO15693CardHandler.CovertEndian(cards[0].ID),
"00",
ISO15693CardHandler.CovertEndian(number)
);
}
/// <summary>
/// 读取卡号
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bt_Reader_Click(object sender, EventArgs e)
{
// 检测端口是否开启
if (!serialPort1.IsOpen)
{
MessageBox.Show("串口未打开！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 寻卡
cards = SearchCards();
// 判断到的卡片数量
if (!(cards.Count > 0))
{
MessageBox.Show("未寻到卡！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string number = ReadSingleBlock(
ISO15693CardHandler.CovertEndian(cards[0].ID), "00"
);
// 判断是否读取成功
if (number.Equals("-1"))
{
MessageBox.Show("读取失败！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 记录
recordSQL(number);
}
寻卡、读块、写入到块
private List<ISO15693Card> SearchCards()
{
string response = "";
serialPort1.Write(ISO15693Card.COMMAND_WRITE_REG);
Thread.Sleep(MILLISECOND_IN_SLEEP);
if (serialPort1.BytesToRead > 0) response = serialPort1.ReadExisting();
serialPort1.Write(ISO15693Card.COMMAND_SET_AGC);
Thread.Sleep(MILLISECOND_IN_SLEEP);
if (serialPort1.BytesToRead > 0) response = serialPort1.ReadExisting();
serialPort1.Write(ISO15693Card.COMMAND_SET_RECV_MODE);
Thread.Sleep(MILLISECOND_IN_SLEEP);
if (serialPort1.BytesToRead > 0) response = serialPort1.ReadExisting();
serialPort1.Write(ISO15693Card.COMMAND_INVEN_CARD);// 寻卡
Thread.Sleep(MILLISECOND_IN_SLEEP);
if (serialPort1.BytesToRead > 0) response = serialPort1.ReadExisting();
return ISO15693CardHandler.InventoryCard(response);
}
private string ReadSingleBlock(string UID, string strBlockIndex)
{
//格式化读写命令
string command = string.Format(
ISO15693Card.COMMAND_READ_SINGLE_BLOCK, UID, strBlockIndex
);
serialPort1.Write(command);//写命令
Thread.Sleep(MILLISECOND_IN_SLEEP);// 睡眠200毫秒
if (serialPort1.BytesToRead > 0)
{
string number = ISO15693CardHandler.GenerateBlockData(
serialPort1.ReadExisting()
).Substring(5);
return number;
}
return "-1";
}
private void WriteSingleBlock(string UID, string strBlockIndex, string hexData)
{
//格式化命令
string command = string.Format(ISO15693Card.COMMAND_WRITE_SINGLE_BLOCK, UID, strBlockIndex, hexData);
serialPort1.Write(command);
Thread.Sleep(MILLISECOND_IN_SLEEP);
if (serialPort1.BytesToRead > 0) MessageBox.Show("执行完毕");
}
插入数据库记录
private void recordSQL(string number)
{
// 数据库操作
String connStr = ConfigurationManager.ConnectionStrings["Attendance"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(connStr);
try
{
sqlConn.Open();
String sqlStr =
"insert into " +
"record(Number, CheckDate, CheckTime, MachineNumber) " +
"values(@number, @date, @time, '001')";
SqlCommand sCmd = new SqlCommand(sqlStr, sqlConn);
string date = DateTime.Now.ToShortDateString();
string time = DateTime.Now.ToLongTimeString();
sCmd.Parameters.Add(new SqlParameter("@number", number));
sCmd.Parameters.Add(new SqlParameter("@date", date));
sCmd.Parameters.Add(new SqlParameter("@time", time));
int len = sCmd.ExecuteNonQuery();
MessageBox.Show(len + "条打卡记录成功！");
}
catch (Exception exp)
{
MessageBox.Show(
"连接数据库失败：" + exp.Message,
"错误",
MessageBoxButtons.OK,
MessageBoxIcon.Error
);
return;
}
}
private void timer_Checker_Tick(object sender, EventArgs e)
{
// 检测端口是否开启
if (!serialPort1.IsOpen)
{
bt_OpenTimer.Text = "开启打卡";
timer_Checker.Enabled = false;
return;
}
// 寻卡
timerCards = SearchCards();
// 判断是否寻到卡
if (!(timerCards.Count > 0))
{
lastNumber = "";
return;
}
string number = ReadSingleBlock(
ISO15693CardHandler.CovertEndian(timerCards[0].ID), "00"
);
// 判断是否与上一个卡一致
if (lastNumber.Equals(number))
{
return;
}
lastNumber = number;
// 记录
recordSQL(number);
}
任务五 考勤系统测试
实现截图：
任务六 考勤系统发布安装
实现截图：
安装.jpg
登录框.png
具体描述：
选中C#工程文件，点击右键，选择属性，然后选择发布文件夹的位置（ftp服务器或文件路径），最后点击立即发布。在发布文件夹的路径下，选择后缀名为application的文件进行安装。
