C#收集中控ZKTeco考勤数据
0.前言
项目背景：全公司有近千员工分布在全市各处，一个考勤机只能收录500名员工，且由于人事培训不足导致分公司人事录入的员工操作时，录入数据并不在同一个数据库（mdb），导致同一个员工在不同分公司的中控数据库中使用不同的【数据库id】和【工号】
加上中控软件目前的局限性，不能简单的将各个考勤机同时使用收录到同一个数据库（mdb）中，引起用户id相互覆盖的导致考勤数据错乱，因此要做到自动下载、更新并收集所有考勤信息到本地或者其他平台的程序思路流程设计如下：
PS.用钩子貌似也行~
1.事前准备
1.ZKTeco软件
1.新建一个存放各个子公司的mdb文件夹
实际可按需选择新建的地址，或者直接在编程里再分别指定子公司mdb，文件夹名称也可以随看客自己决定，这里为了方便展示就直接新建个【mdbs】到安装目录
2.新建一个中控软件所指定的mdb文件夹
同样按需设置
3.在中控设置自动下载
这里的时间间隔【2min】按照当前实际项目所涉及考勤机收录的人员数量设置，也可以按实际一次（选中全部机器）【从设备下载记录数据】的时间设置
PS.旧版本会有时间间隔过短可能会导致一个没下完，下一次又开始导致中断
4.调出日志文件
记得在修改【Option.dat】前需要等软件完全关闭！
连接失败样例
连接成功样例
5*.查看mdb主表字段意义
在安装目录用文档软件打开【sqlserver.sql】文件
2.C#程序
1.项目中增加一个 Application Manifest File
在项目中增加一个 Application Manifest File
用于C#程序使用【管理员】权限运行/关闭中控软件
2.项目配置x86
设置好后可能会有下图提示，点第一个就行~
3.添加考勤目标配置文件
这里项目要求只收集某几个人的考勤所以设计如下：
2.C#代码
1.引用
using Fx_SynTool.Syn;
using Fx_SynTool.Utils;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Threading;
2.实体类
存放考勤数据信息的实体类，可按需调整
人名（【USERINFO】表）基本都是唯一的，有重名的话可以在重名后面加个数字区分，数据源在ZKT数据库mdb里的，然而一个人只能在一个时间点对一台机器（【MACHINES】表）打一次卡（【CHECKINOUT】表），对象构造就是【某人在某段时间里分别对N个机器的N次打卡】；
/// <summary>
/// 考勤设备信息
/// </summary>
public class ZKTimeMachineMsg
{
public ZKTimeMachineMsg(string machineAlias, string ip, string sn)
{
this.machineAlias = machineAlias;
this.ip = ip;
this.sn = sn;
}
/// <summary>
/// 录入设备名称
/// </summary>
public string machineAlias {
get; set; }
/// <summary>
/// 录入设备IP
/// </summary>
public string ip {
get; set; }
/// <summary>
/// 录入设备号
/// </summary>
public string sn {
get; set; }
public override string ToString()
{
return ip + ":" + sn + "(" + machineAlias + ")";
}
}
/// <summary>
/// 考勤机用户（信息）
/// </summary>
public class ZKTimeUserMsg
{
public ZKTimeUserMsg(string userId, string name)
{
this.Id = userId;
this.Name = name;
this.time_machines = new SortedDictionary<DateTime, ZKTimeMachineMsg>();
}
/// <summary>
/// 姓名
/// </summary>
public string Name {
get; set; }
/// <summary>
/// id
/// </summary>
public string Id {
get; set; }
/// <summary>
/// 部门名
/// </summary>
public string DeptName {
get; set; }
/// <summary>
/// 考勤打卡记录（主键）【打卡日期时间-考勤机器】
/// </summary>
public SortedDictionary<DateTime, ZKTimeMachineMsg> time_machines {
get; set; }
public override string ToString()
{
return Name + "(" + time_machines.Count + ")";
}
}
3.工具类
/// <summary>
/// 线程锁
/// </summary>
private readonly static object obj = new object();
/// <summary>
/// 中控指定mdb文件名
/// </summary>
private const string activityMdb = "activity.mdb";
/// <summary>
/// 考勤机连接失败标志
/// </summary>
private const string connectFailSign = "Connect Result:Fail";
/// <summary>
/// 中控软件考勤机连接情况截图横轴大小
/// </summary>
private const int ZKTShotcutSizeX = 1330;
/// <summary>
/// 中控软件考勤机连接情况截图纵轴大小
/// </summary>
private const int ZKTShotcutSizeY = 185;
/// <summary>
/// 蓝色
/// </summary>
private readonly static Color blue = Color.FromArgb(0, 0, 255);
/// <summary>
/// 考勤数据库文件密码
/// </summary>
private readonly static string ZKTimeMdbPassWord = @SettingHelper.GetString("ZKTimeMdbPassWord");
/// <summary>
/// 考勤查询目标配置文件（一行一个人名）
/// </summary>
public readonly static string ZKTimeMdbTargets = @SettingHelper.GetString("ZKTimeMdbTargets");
/// <summary>
/// 早班分割时点(例如此值为4，则凌晨4点前的考勤归前一天)
/// </summary>
public static readonly int ZKTimeDivisionMorningHour = int.Parse(@SettingHelper.GetString("ZKTimeDivisionMorningHour"));
/// <summary>
/// 午班班分割时点(例如此值为14，则下午两点(14点)前的考勤早班打卡)
/// </summary>
public static readonly int ZKTimeDivisionAfternoonHour = int.Parse(@SettingHelper.GetString("ZKTimeDivisionAfternoonHour"));
/// <summary>
/// 中控软件的安装目录
/// </summary>
public static readonly string ZKTecoPath = @SettingHelper.GetString("ZKTecoPath");
/// <summary>
/// 中控软件考勤机连接情况保留日数
/// </summary>
public static readonly int ZKTLogsKeepDays = int.Parse(@SettingHelper.GetString("ZKTLogsKeepDays"));
/// <summary>
/// 中控软件考勤机连接情况截图目录
/// </summary>
public static readonly string ZKTshotcutPath = Path.Combine(Environment.CurrentDirectory, "ZKTshotcut");
/// <summary>
/// 用户考勤信息缓存
/// </summary>
private readonly static List<ZKTimeUserMsg> zkTimeUsers = new List<ZKTimeUserMsg>();
/// <summary>
/// 是否暂停数据收集
/// </summary>
public static bool isCollectingFromMachine = false;
/// <summary>
/// 是否只上传
/// </summary>
public static bool isOnlyUpload = false;
/// <summary>
/// 获取MDB表格信息
/// </summary>
/// <param name="tableName"></param>
/// <param name="fields"></param>
/// <param name="conditionString"></param>
/// <returns></returns>
public static DataTable GetMDBMsg(string sqlString, string dataSourcePath)
{
DataTable dataTable = new DataTable();
//string connString =
// @"Provider=Microsoft.Jet.OLEDB.4.0;" +
// "Data Source=" + dataSourcePath + ";" +
// "Jet OLEDB:Database Password=" + ZKTimeMdbPassWord;
// oleDbConnectionStringBuilder.Keys:
//File Name
//Provider
//Data Source
//Persist Security Info
//OLE DB Services
//Cache Authentication
//Encrypt Password
//Mask Password
//Password
//User ID
//Mode
//Extended Properties
//Locale Identifier
//Jet OLEDB:System database
//Jet OLEDB:Registry Path
//Jet OLEDB:Database Password
//Jet OLEDB:Engine Type
//Jet OLEDB:Database Locking Mode
//Jet OLEDB:Global Partial Bulk Ops
//Jet OLEDB:Global Bulk Transactions
//Jet OLEDB:New Database Password
//Jet OLEDB:Create System Database
//Jet OLEDB:Encrypt Database
//Jet OLEDB:Don't Copy Locale on Compact
//Jet OLEDB:Compact Without Replica Repair
//Jet OLEDB:SFP
//Jet OLEDB:Compact Reclaimed Space Amount
OleDbConnectionStringBuilder oleDbConnectionStringBuilder = new OleDbConnectionStringBuilder
{
Provider = "Microsoft.Jet.OLEDB.4.0",
DataSource = dataSourcePath,
};
oleDbConnectionStringBuilder["Jet OLEDB:Database Password"] = ZKTimeMdbPassWord;
using (OleDbConnection oleDbConnection = new OleDbConnection(oleDbConnectionStringBuilder.ConnectionString)) //1、建立连接
{
oleDbConnection.Open();//2、打开连接
using (OleDbCommand oleDbCommand = oleDbConnection.CreateCommand())//建立SQL查询
{
oleDbCommand.CommandText = sqlString;
LogHelper.logger.Info(oleDbCommand.CommandText);
using (OleDbDataReader oleDbDataReader = oleDbCommand.ExecuteReader())//建立读取
{
//查询并显示数据
int size = oleDbDataReader.FieldCount;
for (int i = 0; i < size; i++)
{
DataColumn dataColumn;
dataColumn = new DataColumn(oleDbDataReader.GetName(i));
dataTable.Columns.Add(dataColumn);
}
while (oleDbDataReader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < size; i++)
{
string str = oleDbDataReader.GetName(i);
dataRow[str] = oleDbDataReader[str].ToString();
}
dataTable.Rows.Add(dataRow);
}
}
}
}
return dataTable;
}
/// <summary>
/// 获取中控考勤数据
/// </summary>
public static bool GetZKTimeCheckoutData()
{
zkTimeUsers.Clear();// 清空原数据
string targetNames = "";
try
{
foreach (string ZKTimeMdbTarget in File.ReadAllLines(ZKTimeMdbTargets))
if (!string.IsNullOrWhiteSpace(ZKTimeMdbTarget))
targetNames += "'" + ZKTimeMdbTarget + "',";
string sqlString =
"select " +
" USERINFO.USERID," +// 0.用户id
" USERINFO.NAME," +// 1.姓名
" CHECKINOUT.CHECKTIME," +// 2.录入时间
" CHECKINOUT.SN," +// 3.录入设备号
" MACHINES.MACHINEALIAS," +// 4.录入设备名称
" MACHINES.IP " +// 5.录入设备IP
//" DEPARTMENTS.DEPTNAME," +// 6.部门名称
"from " +
//" (" +
"(CHECKINOUT " +// 签到表
" left join USERINFO on USERINFO.USERID = CHECKINOUT.USERID )" +// 关联用户表
//" left join DEPARTMENTS on DEPARTMENTS.DEPTID = USERINFO.DEFAULTDEPTID ) " +// 关联部门表
" left join MACHINES on MACHINES.SN = CHECKINOUT.SN " + // 设备表
"where " +
" 1 = 1 " +
" and " +
" USERINFO.NAME IN ( " + targetNames.TrimEnd(',') + " )" +// 查询目标
" and " +
" MACHINES.MACHINEALIAS NOT LIKE ( '%门禁%' )" +// 排除门禁
" and " +
" CHECKINOUT.CHECKTIME BETWEEN DATE()-14 AND NOW() " +// 限定上周一到周日数据
"";
string mdbDirectoty = Path.Combine(ZKTecoPath, "mdbs");
FileInfo[] fileInfos = new DirectoryInfo(mdbDirectoty).GetFiles("*.mdb");
foreach (FileInfo fileInfo in fileInfos)
if (fileInfo.Exists)
foreach (DataRow dataRow in GetMDBMsg(sqlString, fileInfo.FullName).Rows)
{
// *号为非录入字段
string userId = dataRow[0].ToString();// 0.用户id *
string name = dataRow[1].ToString();// 1.姓名
string checkTime = dataRow[2].ToString();// 2.录入时间
string sn = dataRow[3].ToString();// 3.录入设备号
string machineAlias = dataRow[4].ToString();// 4.录入设备名称 *
string ip = dataRow[5].ToString();// 5.录入设备IP *
//string deptName = dataRow[6].ToString();// 6.部门名称
if (!zkTimeUsers.Any((user) => {
return user.Name == name; }))
zkTimeUsers.Add(new ZKTimeUserMsg(userId, name));
ZKTimeUserMsg zkTimeUser = zkTimeUsers.First((user) => {
return user.Name == name; });
if (!string.IsNullOrWhiteSpace(checkTime))
zkTimeUser.time_machines.Add(Convert.ToDateTime(checkTime), new ZKTimeMachineMsg(machineAlias, ip, sn));
}
else
throw new Exception("考勤数据库文件【" + fileInfo.FullName + "】不存在！");
LogHelper.logger.Info("获取考勤信息成功！");
}
catch (Exception ex)
{
LogHelper.logger.Error("获取考勤信息异常！", ex);
return false;
}
return true;
}
/// <summary>
/// 判断是否上班/下班没打卡
/// </summary>
/// <param name="isSaturday">是否在周六</param>
/// <param name="startHour">最早打卡点的小时数</param>
/// <param name="endHour">最晚打卡点的小时数</param>
/// <param name="isLackStart">是否上班没打卡</param>
/// <param name="isLackEnd">是否下班没打卡</param>
private static void IsLack(bool isSaturday, int startHour, int endHour, ref bool isLackStart, ref bool isLackEnd)
{
isLackStart = false;
isLackEnd = false;
// 最早打卡点
if ((isSaturday && startHour > 12)// 周六12点前不打=上班没打卡
|| // 非周六4-14点间不打=上班没打卡
(!isSaturday && (startHour < ZKTimeDivisionMorningHour || startHour >= ZKTimeDivisionAfternoonHour)))
isLackStart = true;
// 最晚打卡点
if ((isSaturday && endHour < 12 && endHour > 4)// 周六12点之后不打=下班没打卡
||// 非周六在上班打卡时段打卡=下班没打卡
(!isSaturday && endHour >= ZKTimeDivisionMorningHour && endHour < ZKTimeDivisionAfternoonHour)
)
isLackEnd = true;
}
/// <summary>
/// 收集考勤机数据
/// </summary>
public static bool GetZKTimeDatas()
{
if (isOnlyUpload)
return true;
string logsDirectoty = Path.Combine(ZKTecoPath, "Logs");
string mdbDirectoty = Path.Combine(ZKTecoPath, "mdbs");
string activityPath = Path.Combine(mdbDirectoty, "activity", activityMdb);
try
{
LogHelper.logger.Info("正在获取中控数据库集...");
FileInfo[] fileInfos = new DirectoryInfo(mdbDirectoty).GetFiles("*.mdb");
LogHelper.logger.Info("获取【" + mdbDirectoty + "】目录下的中控数据库集成功！");
Array.Reverse(fileInfos);
Process process = new Process();
process.StartInfo.FileName = Path.Combine(ZKTecoPath, "Att.exe");
process.StartInfo.UseShellExecute = false;
process.StartInfo.RedirectStandardInput = false;
process.StartInfo.RedirectStandardOutput = false;
process.StartInfo.RedirectStandardError = false;
process.StartInfo.CreateNoWindow = true; //不弹出窗口，改为后台运行
ShutDownAtt(process);
DateTime startTime = DateTime.Now;
foreach (FileInfo fileInfo in fileInfos)
{
LogHelper.logger.Info("正在复制中控数据库【" + fileInfo.Name + "】到活动数据库【" + activityMdb + "】...");
File.Copy(fileInfo.FullName, activityPath, true);
LogHelper.logger.Info("复制中控数据库【" + fileInfo.Name + "】到活动数据库【" + activityMdb + "】成功！准备打开指向【" + fileInfo.Name + "】的中控软件...");
process.Start();// 打开中控软件
LogHelper.logger.Info("打开指向【" + fileInfo.Name + "】的中控软件成功，等待中控软件自动获取考勤数据！");
Thread.Sleep(5 * 60 * 1000);// 等待中控自动下载
#region 这里是想用程序句柄获取运行截屏，但虚拟机不支持
//LogHelper.logger.Info("正在检查【" + fileInfo.Name + "】的各个考勤机连接情况！");
//IntPtr intPtr = SystemHelper.GetWnd(process.Id, "TListView", "with ad");
//SystemHelper.GetWindowCapture(intPtr).Save(Path.Combine(ZKTshotcutPath, Path.GetFileNameWithoutExtension(fileInfo.Name) + DateTime.Now.ToString("yyyyMMddHHmmss") + ".jpg"));
//Thread.Sleep(1000);
//intPtr = SystemHelper.GetWnd(process.Id, "TListView", "考勤管理程序");
//SystemHelper.GetWindowCapture(intPtr).Save(Path.Combine(ZKTshotcutPath, Path.GetFileNameWithoutExtension(fileInfo.Name) + DateTime.Now.ToString("yyyyMMddHHmmss") + ".jpg"));
//Thread.Sleep(1000);
//intPtr = SystemHelper.GetWnd(process.Id, "TListView", "考勤管理程序 - [ - 2022/1/14]");
//SystemHelper.GetWindowCapture(intPtr).Save(Path.Combine(ZKTshotcutPath, Path.GetFileNameWithoutExtension(fileInfo.Name) + DateTime.Now.ToString("yyyyMMddHHmmss") + ".jpg"));
//SystemHelper.GetWindowCaptureByName("考勤管理程序 - [ - 2022/1/14]", "TFormAttMain").Save(Path.Combine(ZKTshotcutPath, Path.GetFileNameWithoutExtension(fileInfo.Name) + DateTime.Now.ToString("yyyyMMddHHmmss") + ".jpg"));
//SystemHelper.GetWindowCapture(process.MainWindowHandle).Save(Path.Combine(ZKTshotcutPath, Path.GetFileNameWithoutExtension(fileInfo.Name) + DateTime.Now.ToString("yyyyMMddHHmmss") + ".jpg"));
#endregion
#region 这里想用截屏方式获取连接情况，不过虚拟机不支持
//if (!Directory.Exists(ZKTshotcutPath))
// Directory.CreateDirectory(ZKTshotcutPath);
//List<bool> isLinkings = new List<bool>();// 自上而下的连接情况-是否已连接
//using (Bitmap bitmap = new Bitmap(ZKTShotcutSizeX, ZKTShotcutSizeY))// 画布大小
//using (Graphics graphics = Graphics.FromImage(bitmap))
//{
// graphics.CopyFromScreen(new Point(249, 88), new Point(0, 0), new Size(ZKTShotcutSizeX, ZKTShotcutSizeY));// 截图开始坐标，截图范围内的开始坐标，截图范围
// // 检索【未连接】字符
// for (int i = 0; i < ZKTShotcutSizeX - 11; i++)
// for (int j = 0; j < ZKTShotcutSizeY - 11; j++)
// if (IsUnlink(bitmap, i, j))
// isLinkings.Add(false);
// else if (Islinked(bitmap, i, j))
// isLinkings.Add(true);
// if (isLinkings.Any((isLinking) => { return !isLinking; }))// 如有未连接状态的考勤机
// bitmap.Save(Path.Combine(ZKTshotcutPath, Path.GetFileNameWithoutExtension(fileInfo.Name) + DateTime.Now.ToString("yyyyMMddHHmmss") + ".jpg"));// 保存截图
//}
#endregion
Thread.Sleep(1 * 60 * 1000);// 等待中控自动下载
LogHelper.logger.Info("正在关闭指向【" + fileInfo.Name + "】的中控软件...");
ShutDownAtt(process);
Thread.Sleep(10 * 1000);// 等待内存资源释放
LogHelper.logger.Info("已关闭指向【" + fileInfo.Name + "】的中控软件，正在更新考勤数据到【" + fileInfo.Name + "】...");
File.Copy(activityPath, fileInfo.FullName, true);// 将更新数据覆盖原数据
LogHelper.logger.Info("更新考勤数据到【" + fileInfo.Name + "】成功！");
Thread.Sleep(10 * 1000);// 等待内存资源释放
}
LogHelper.logger.Info("从考勤机下载考勤数据到【" + mdbDirectoty + "】成功！");
//foreach (FileInfo fileInfo in new DirectoryInfo(ZKTshotcutPath).GetFiles("*.jpg"))//删除N个月前的考勤机连接情况记录
// if (fileInfo.CreationTime.AddMonths(ZKTShotcutKeepDays).Ticks <= DateTime.Now.Ticks)
// fileInfo.Delete();
List<string> connectFailMachineNames = new List<string>();
foreach (FileInfo fileInfo in new DirectoryInfo(logsDirectoty).GetFiles("*.txt"))//删除N个月前的考勤机连接情况记录
if (fileInfo.CreationTime.AddMonths(ZKTLogsKeepDays).Ticks <= DateTime.Now.Ticks)
fileInfo.Delete();
else if (fileInfo.CreationTime.Ticks > startTime.Ticks)
foreach (string log in File.ReadAllLines(fileInfo.FullName))
if (log.Contains(connectFailSign))
{
string machineName = fileInfo.Name.Split('_')[0]
最低0.47元/天 解锁文章
扫一扫
794
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
