考勤报表生成
需要：根据原始打卡记录生成考勤报表
配置：
班次-> 由开始时间点，结束时间点， 开始时间区间，结束时间区间组成
考勤检查方案->由多个班次组成,（属于这个考勤检查方案的人员使用这些班次规则进行统计）
业务:一天多个班次的需要考虑班次之间的连续打卡情况（允许），两次打卡时间间隔需要大于3分钟
代码:
1.加载前一天全部打卡记录,按时间排序后，过滤掉短时间内的重复打卡记录
2.遍历每个考勤检测方案。
2.1.加载该方案下的全部人员，加载员工的当天的全部打卡记录，对方案内的班次配置与员工的打卡记录进行匹配
2.2.班次间的连续打卡问题需要前溯或后溯进行处理
2.3.根据匹配情况形成报表
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; using System.Collections; using F.Studio.Util; namespace Checking_In.UI { public partial class frmMain : Form { #region Excel相关操作 const string C_ExcelConnTmp = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= {0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"; public string ExcelFile { get { return @"E:\Net\HR_KQ\9月质量部.xls"; } } public string Sheet { get { return "9月质量部$"; } } public string ExcelConnectString { get { return string.Format(C_ExcelConnTmp, ExcelFile); } } public new List<TableInfo> Tables { get; private set; } public ArrayList ExcelSheetName() { ArrayList al = new ArrayList(); string strConn; strConn = ExcelConnectString; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataTable sheetNames = conn.GetOleDbSchemaTable (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); conn.Close(); foreach (DataRow dr in sheetNames.Rows) { al.Add(dr[2]); } return al; } private void LoadExcelTableInfo() { Tables = new List<TableInfo>(); var ds = LoadExcelData(); TableInfo curTable = null; for (int i = 1; i < ds.Tables[0].Rows.Count; i++) { DataRow dr = ds.Tables[0].Rows[i]; if (string.IsNullOrWhiteSpace(dr["新加"].ToString()))//空表示表信息 { curTable = new TableInfo(); curTable.Name = dr["F2"].ToString().Trim(); curTable.Code = dr["p_bs"].ToString().Trim(); Tables.Add(curTable); continue; } var column = new ColumnInfo(); column.Code = dr["新加"].ToString().Trim(); string tmp = dr["删除"].ToString(); if (!string.IsNullOrWhiteSpace(tmp)) { column.Name = tmp.Trim().Split(" ".ToArray(), StringSplitOptions.RemoveEmptyEntries)[0]; column.Comment = tmp.Trim(); if (column.Name.Length >= 10) { column.Name = column.Name.Substring(0, 10); } column.NeedSync = true; } curTable.Columns.Add(column); } } private DataSet LoadExcelData() { DataSet dsExcelSheet = new DataSet(); //打开并读取Excel表信息 OleDbConnection connExecel = new OleDbConnection(ExcelConnectString); OleDbDataAdapter dpQualityLevel = new OleDbDataAdapter("SELECT * FROM [" + Sheet + "]", connExecel); connExecel.Open(); dpQualityLevel.Fill(dsExcelSheet); connExecel.Close(); return dsExcelSheet; } #region Class public class TableInfo { public TableInfo() { Columns = new List<ColumnInfo>(); } public string Name { get; set; } public string Code { get; set; } public List<ColumnInfo> Columns { get; set; } } public class ColumnInfo { public string Name { get; set; } public string Code { get; set; } public string Comment { get; set; } /// <summary> /// 是否需要同步 /// </summary> public bool NeedSync { get; set; } } #endregion #endregion private List<CheckingRule> CheckingRules = new List<CheckingRule>(); private List<ShiftInfo> ShiftList = new List<ShiftInfo>(); private List< CheckingInfo> CheckingRecords=null; private List<EmpInfo> Emps = new List<EmpInfo>(); private List<DateTime> Days = new List<DateTime>(); private List<CheckingRstInfo> Rpt = new List<CheckingRstInfo>(); public frmMain() { InitializeComponent(); #region 上班时间配置配置 ShiftList.Add( new ShiftInfo(){ InRule=new CheckingRule() { CheckPoint = "07:00", CheckType = 1, RangBegin = "00:00", RangEnd = "09:00",Title="质量上午进" }, OutRule=new CheckingRule() { CheckPoint = "11:00", CheckType = 2, RangBegin = "09:00", RangEnd = "12:00", Title = "质量上午出" }, Name="质量早班" }); ShiftList.Add( new ShiftInfo(){ InRule=new CheckingRule() { CheckPoint = "13:00", CheckType = 1, RangBegin = "12:00", RangEnd = "15:30", Title = "质量下午进" }, OutRule=new CheckingRule() { CheckPoint = "17:00", CheckType = 2, RangBegin = "15:30", RangEnd = "17:30", Title = "质量下午出" }, Name="质量中班" }); ShiftList.Add( new ShiftInfo() { InRule = new CheckingRule() { CheckPoint = "18:00", CheckType = 1, RangBegin = "17:30", RangEnd = "19:30", Title = "质量晚上进" }, OutRule = new CheckingRule() { CheckPoint = "21:00", CheckType = 2, RangBegin = "19:30", RangEnd = "23:59", Title = "质量晚上出" }, Name = "质量晚班" }); #endregion } private void LoadData() { var ds = LoadExcelData(); var list=new List< CheckingInfo>(); var index = 0; foreach (DataRow item in ds.Tables[0].Rows) { index++; DateTime addtime; var dtStr = item["日期时间"].ToString(); if (!DateTime.TryParse(dtStr, out addtime)) { Console.WriteLine("异常记录!"+index); continue; } var it=new CheckingInfo(); it.Dept = item["部门"].ToString(); it.EmpNo = item["编号"].ToString(); it.Name = item["姓名"].ToString(); it.AddTime = addtime; it.RawAddTime = addtime; it.MachineNo = item["机器号"].ToString(); it.CheckType = item["比对方式"].ToString(); list.Add(it); } #region 排除间隔过短的记录 //时间 2分钟 int count = 0; int intervalSeconds = 1800; list = list.OrderBy(ent => ent.AddTime).ToList(); for (int i = 0; i < list.Count;i++ ) { var it = list[i]; if (it.DelFlag) continue; for (var j = i + 1; j < list.Count; j++) { var next = list[j]; if (next.DelFlag)continue; if( (next.AddTime - it.AddTime).TotalSeconds >intervalSeconds)break; if (next.EmpNo==it.EmpNo) { next.DelFlag = true; Console.WriteLine(next.Name + "," + next.AddTime + "," + next.EmpNo); count++; } } } Console.WriteLine(count); #endregion CheckingRecords =list.Where(ent=>ent.DelFlag==false).OrderBy(ent=>ent.AddTime).ToList(); } private void ExtractEmpAndDate() { var list = CheckingRecords.GroupBy(ent => ent.EmpNo).ToList(); foreach (var it in list) { var emp=new EmpInfo(); emp.EmpNo = it.Key; emp.Name = it.FirstOrDefault().Name; emp.Dept = it.FirstOrDefault().Dept; Emps.Add(emp); } var list2 = CheckingRecords.GroupBy(ent => ent.AddTime.Date); foreach(var it in list2) { Days.Add(it.Key); } Days= Days.OrderBy(ent => ent.Date).ToList(); } private void Analyze() { var rpts = new List<CheckingRstInfo>(); foreach (var day in Days) { foreach (var emp in Emps) { var checklist= CheckingRecords.Where(ent => ent.AddTime >= day && ent.AddTime <= day.AddHours(23.999) && ent.EmpNo == emp.EmpNo).OrderBy(ent => ent.AddTime).ToList(); for(int index=0;index< ShiftList.Count;index++) { var shift=ShiftList[index]; #region 按配置进行分析 var rpt = new CheckingRstInfo() { Date=day,Dept=emp.Dept,EmpNo=emp.EmpNo, ShowInReport=false,Name=emp.Name, IsAbsenteeism=false,InTime="",OutTime="" }; rpts.Add(rpt); rpt.RuleTitle = shift.Name; foreach (var rule in new List<CheckingRule>() { shift.InRule, shift.OutRule }) { #region 上下班检测 DateTime eventTime; var btime = rule.GetTime(day, rule.RangBegin); var etime = rule.GetTime(day, rule.RangEnd); var ptime = rule.GetTime(day, rule.CheckPoint); if (rule.CheckType == 1)//上班 { var inTimeList = checklist.Where(ent => ent.AddTime > btime && ent.AddTime <= etime).ToList(); if (inTimeList.Count <= 0) { #region 向前寻找 if (index == 1) { var prevTime = rule.GetTime(day, ShiftList[0].OutRule.CheckPoint); var lista = checklist.Where(ent => ent.AddTime > prevTime && ent.AddTime <= etime).ToList(); if (lista.Count >= 2) { var item = lista.OrderBy(ent => ent.AddTime).ToList().LastOrDefault(); inTimeList.Add(item); checklist.Remove(item); } } else if (index == 2) { var prevTime = rule.GetTime(day, ShiftList[1].OutRule.CheckPoint); var lista = checklist.Where(ent => ent.AddTime > prevTime && ent.AddTime <= etime).ToList(); if (lista.Count >= 2) { var item = lista.OrderBy(ent => ent.AddTime).ToList().LastOrDefault(); inTimeList.Add(item); checklist.Remove(item); } } #endregion if (inTimeList.Count <= 0) { rpt.ShowInReport = true; rpt.IsAbsenteeism = true; continue; } } eventTime = inTimeList.Min(ent => ent.AddTime); rpt.InTime = eventTime.ToString("yyyy-MM-dd HH:mm:ss"); var lateMinute = (eventTime - ptime).TotalMinutes; if (lateMinute >= 1) { rpt.LateMinute = (int)lateMinute; rpt.ShowInReport = true; } } else if (rule.CheckType == 2)//下班 { var outTimeList = checklist.Where(ent => ent.AddTime > btime && ent.AddTime <= etime).ToList(); if (outTimeList.Count() <= 0) { #region 向后寻找 if (index == 0 && ShiftList.Count >= 2) { var nextTime = rule.GetTime(day, ShiftList[1].InRule.CheckPoint); var lista = checklist.Where(ent => ent.AddTime > btime && ent.AddTime <= nextTime).ToList(); if (lista.Count >= 2) { var item = lista.OrderBy(ent => ent.AddTime).ToList().FirstOrDefault(); outTimeList.Add(item); checklist.Remove(item); } } else if (index == 1 && ShiftList.Count >= 3) { var nextTime = rule.GetTime(day, ShiftList[2].InRule.CheckPoint); var lista = checklist.Where(ent => ent.AddTime > btime && ent.AddTime <= nextTime).ToList(); if (lista.Count >= 2) { var item = lista.OrderBy(ent => ent.AddTime).ToList().FirstOrDefault(); outTimeList.Add(item); checklist.Remove(item); } } #endregion if (outTimeList.Count <= 0) { rpt.ShowInReport = true; rpt.IsAbsenteeism = true; continue; } } eventTime = outTimeList.Min(ent => ent.AddTime); rpt.OutTime = eventTime.ToString("yyyy-MM-dd HH:mm:ss"); var leaveEarlyMinute = (ptime - eventTime).TotalMinutes; if (leaveEarlyMinute >= 1) { rpt.LeaveEarlyMinute = (int)leaveEarlyMinute; rpt.ShowInReport = true; } } #endregion } #endregion } } } var list = rpts.Where(ent => ent.ShowInReport == true).ToList(); dataGridView1.DataSource = list; Rpt = list; } private void button1_Click(object sender, EventArgs e) { LoadData(); ExtractEmpAndDate(); Analyze(); button1.Enabled = false; //saveFileDialog1.FileName = DateTime.Now.ToString("考勤分析yyMMddHHmmss") + ".xls"; //if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) //{ // return; //} //if (string.IsNullOrWhiteSpace(saveFileDialog1.FileName)) return; //var filename = saveFileDialog1.FileName; //NPOIUtil.Export(Rpt.ToDataTable(), "打卡记录分析", filename); } } }
数据结构
class CheckingRule { /// <summary> /// 格式00:00 /// </summary> public String CheckPoint { get; set; } public String RangBegin { get; set; } public String RangEnd { get; set; } /// <summary> /// 上班:1 /// 下班:2 /// /// </summary> public int CheckType { get; set; } public String Title { get; set; } public DateTime GetTime(DateTime day,string v) { var arr= v.Split(":".ToCharArray()); return day.Date.AddHours(double.Parse(arr[0])).AddMinutes(double.Parse(arr[1])); } }
class CheckingRstInfo { public String Name { get; set; } public String EmpNo { get; set; } public String Dept { get; set; } public DateTime Date { get; set; } public String InTime { get; set; } public String OutTime { get; set; } public bool? IsAbsenteeism { get; set; } public int LateMinute { get; set; } public int LeaveEarlyMinute { get; set; } public bool? ShowInReport { get; set; } public String RuleTitle { get; set; } }
using System.Data; using System.Collections.Generic; using System.Reflection; using System; using System.Collections; namespace F.Studio.Util { public static class DataTableExtensions { /// <summary> /// 转化一个DataTable /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list"></param> /// <returns></returns> public static DataTable ToDataTable<T>(this IEnumerable<T> list, params string[] tableName) { //创建属性的集合 List<PropertyInfo> pList = new List<PropertyInfo>(); //获得反射的入口 Type type = typeof(T); string tname = "Table1"; if (tableName.Length >= 1) { tname = tableName[0]; } DataTable dt = new DataTable(tname); //把所有的public属性加入到集合 并添加DataTable的列 Array.ForEach<PropertyInfo>(type.GetProperties(), p => { pList.Add(p); var theType = p.PropertyType; //处理可空类型 if (theType.IsGenericType && theType.GetGenericTypeDefinition().Equals(typeof(Nullable<>))) { dt.Columns.Add(p.Name, Nullable.GetUnderlyingType(theType)); } else { dt.Columns.Add(p.Name, theType); } }); foreach (var item in list) { //创建一个DataRow实例 DataRow row = dt.NewRow(); //给row 赋值 pList.ForEach(p => { var v = p.GetValue(item, null); row[p.Name] = v == null ? DBNull.Value : v; }); //加入到DataTable dt.Rows.Add(row); } return dt; } } }
public class CheckingInfo { public CheckingInfo() { DelFlag = false; } public String Dept { get; set; } public String Name { get; set; } public DateTime AddTime { get; set; } public DateTime RawAddTime { get; set; } public String MachineNo { get; set; } public String EmpNo { get; set; } public String CheckType { get; set; } public bool DelFlag { get; set; } }
